Stored Procedures & Views Reference

Builds the initial work_wsg table with 3-level fleet hierarchies using three nested cursor operations.

1. c_parent cursor: Finds top-level parent groups (ABSD_OWGE.group_owge='A') with active ships having principal/part-owner relationships
2. c_level1 cursor: Finds management companies (group_owge='M' or 'B') under the parent groups
3. c_level2 cursor: Finds 2-level deep hierarchies (M company under another M, with B-level ships)

Enriches work_wsg with EFD (Effective From Date) values. Priority lookup: ABSD_HIPP (principal) → ABSD_HIOW (owner) → ABSD_HISM (ship manager). All at seqno='00'. Only updates if EFD found and not NULL.

Populates fleet_manager_efd in work_owners. Priority: ABSD_HISM → ABSD_HIOW → ABSD_HIPP. Filters out EFD '198099' (null sentinel).

Populates disponent_owner_efd — the commercial/chartering party's effective date. Same lookup hierarchy as fleet_update (HISM → HIOW → HIPP).

Propagates EFD across ownership roles. If the same entity code appears in multiple roles and one has an EFD but the other does not, the known EFD is copied across.

Fills missing fleet_manager_efd and group_owner_efd using regd_owner_efd as default when manager/owner exist but EFD is null.

Eliminates duplicate rows in work_owners when the same LRNO appears multiple times. Deduplication priority per LRNO:

1. Look up registered owner from ABSD_OWSH (Direct Owner relationship)
2. From the registered owner, look up parent group from ABSD_OWXR (Beneficial Owner 'BO')
3. If no BO found, look up group from ABSD_OWGE where title1='BO' and group_owge='A'
4. Fallback: use h03_ppc (principal performance controller from ABSD_HIPP)
5. Updates current row's group_owner; deletes previous duplicate row

Parameters: @Tablename, @Fieldname, @IDNo (LRNO/OWCODE), @IDFieldname, @IsNew (bit), @NewValue, @OldValue, @Initials, @Source, @SourceRef, @FilingRef, @ExtraData

• Calls spGetEnglishFieldName to translate column name to human-readable field name
• Special handling for Name fields: marks as 'New Name' or 'Name should be'
• Handles quote escaping in values (' → '')
• Constructs and executes dynamic SQL INSERT into tblChanges

Maintains the relationship between LR class status and special feature codes in ABSD_FUSF2.

Generates formatted amendment notices for company address changes and appends them to ABSD_OWAN (owner annotations).

1. Check if address has care-of company reference (COCODE): if yes use ABSD_OWIN, if no use ABSD_OWAD1
2. Construct formatted notice with PO Box, street, postcode, town, country, phone, fax, telex, email, web — with CR/LF line breaks
3. Decode street/town/country codes via vwStreetDecode, vwTownDecode, vwCountryDecode
4. If existing note has space (<1600 chars): append; if note is full: create new note sequence in ABSD_OWAN

The most comprehensive fleet view. Joins ABSD_OVGE (base) to all current-record history tables via SEQNO='00'. SEQNO joins are INNER JOINs; ownership company joins are LEFT JOINs.

Filter criteria: B07_GROSS >= 299 AND A02_STS IN ('P','O','U','F','E','S','C','R','L','T','J','V') (excludes 'K') OR status changed within last 12 months.

Joins ABSD_OVGE to ABSD_HIPP, ABSD_HIOW, ABSD_HISM, ABSD_HIOP, ABSD_OVSMC, ABSD_HITL, ABSD_HIST — all at SEQNO='00'.

Prioritizes EDM data; falls back to ABSD_ for unmigrated entities only.

Cross-reference view using EDM for relationship data where available, with ABSD_OWXR as fallback. Used in the HIOW trigger for beneficial owner resolution.

Single-table lookup returning OWCODE → short name. Replaces ABSD_OWGE in most current views. The LTRIM is for performance.

DISP decode: CASE on DISP field: C=Casualty, D=Demolition, X=Both.

Identifies ownership at the time of the casualty, not current ownership:

Current (active) foreign classification society assignments only.

PIVOT transformation on ABSD_FUSF2 D07_CODE column. Pivots ice class notation codes (FS1A, FS1B, FS1C, FS1S, FSII, 3019, 4001) into separate boolean columns per vessel.

ITC-69 tonnage is preferred over national tonnage in the COALESCE logic.