Section 12d

Overview spAnnotateChange spWriteAnnotation* spSearchAnnotations* spBackfill* spCleanAnnotations spGetEnglishFieldName spRecomputeABSD_OWST spRecomputeABSD_OWST_Diffs spUpdateOWNA spFairplay* spFPL* spCoMatch* spBestAddress spAddressTRF Overview sp_GROUP_FLEET_p_parallel Pipeline Steps (7 SPs) spUpdateOwnerSearch spABSD_SHIP_SEARCH_SYNC 12a Validation 12b Ship Lifecycle 12c Record Lifecycle 12e External & System ← Documentation Hub

12d Stored Procedures — Ownership, Annotation & Fleet

SPs covering the annotation/change-tracking system, ownership statistics recompute, Fairplay data integration, GROUP_FLEET pipeline, and the ABSD_SHIP_SEARCH/owner-search rebuild infrastructure.

Annotation System Overview

The ABSD database has two annotation tables (see Section 10 for trigger-level detail). The SP layer provides read/write/search/cleanup access to them:

spAnnotateChange — Field-Level Change Logging

Inserts one row into tblChanges for a single field change. Key implementation detail: the ChangeType column is not the raw field name but the English human-readable name looked up via spGetEnglishFieldName(@Tablename, @Fieldname). Special case: when ChangeType='Name' and @IsNew=1, ChangeType becomes 'New Name'; when @IsNew=0, ChangeType becomes 'Name should be'.

The INSERT is built dynamically with sp_executesql to handle embedded single-quotes in values (double-single-quote escaping applied to all varchar parameters).

spWriteAnnotation* Family

Wrapper family around spAnnotateChange and direct tblChanges/tblAnnotationLogGeneral inserts:

SP	Purpose
spWriteAnnotation	Thin wrapper around spAnnotateChange — same params, simpler call signature
spWriteAnnotationNewType	Bypasses spGetEnglishFieldName: passes ChangeType directly. Used when the "English name" is contextual (e.g. 'Scrap Price Calculated')
spWriteAnnotationNewTypeForLrno	Minimal signature — only LRNO, type, old/new value, initials, source. For trigger-context writes
spWriteNarrativeAnnotation	Inserts a long free-text narrative into tblChanges as ChangeType='Narrative'
spWriteNarrativeStyleAnnos	Inserts into tblAnnotationLogGeneral with a GroupingName heading (for secondary annotation style output)
spInsertAnnotationNarrative	Inserts APS narrative text as a tblChanges 'APS Narrative' entry

spSearchAnnotations* Family

Retrieves annotation history for the annotation viewer UI. All variants return ordered by EffDate DESC. The _BR3Migration variants are transition versions used during the BR3 system migration (kept for reference).

SP	Source table	Filter
spSearchAnnotations_Primary	tblChanges	By LRNO ± date range ± change type
spSearchAnnotations_PrimarySG	tblChanges	LRNO, Seaweb/Global output filter
spSearchAnnotations_General	tblAnnotationLogGeneral	By LRNO ± date range
spSearchAnnotations_GeneralOwnership	tblAnnotationLogGeneral	By OWCODE
spSearchAnnotationsOwners_Primary	tblChanges	By OWCODE (CompanyNo column)
spSecondaryAnnotations	tblAnnotationLogGeneral	All secondary annotations for LRNO, most recent first

Companion read SPs: spGetOldValue (retrieves OldValue for a specific ChangeType from tblChanges), spGetAnnoLookupValue (lookup from annotation audit for field-level revert).

spBackfillAnnotations / spBackfillSpanFromAnnos

spBackfillAnnotations — Retrospectively creates tblChanges entries from the current ABSD_ field values for a vessel where annotation records are missing (typically after data imports that bypassed the annotation system). Takes optional @LRNO; if NULL, processes all vessels. Slow batch SP — not for real-time use.

spBackfillSpanFromAnnos — Rebuilds ABSD_SPAN (annotation span records that group related changes) from the existing tblChanges history. Used after bulk data corrections to restore the span structure.

spCleanAnnotations / spCleanSecondaryAnnotations

Purge SPs for annotation table maintenance. spCleanAnnotations removes tblChanges rows older than @DaysToKeep (default 180 days) for the given LRNO (or all LRNOs if NULL). spCleanSecondaryAnnotations purges tblAnnotationLogGeneral older than @DaysToKeep. Run by scheduled maintenance jobs. spTidyShipAnnos / spTidyNarrativeAnnoForLrno are variants that deduplicate and tidy annotation text.

spGetEnglishFieldName

Looks up the human-readable field label from tblEnglishFieldNames (keyed by Tablename + Fieldname). Returns NULL if no mapping exists. Used by spAnnotateChange to translate technical column names (e.g. 'B01_CALLSIGN') to UI-visible labels (e.g. 'Callsign'). tblEnglishFieldNames must be maintained when new fields are added to the schema.

spRecomputeABSD_OWST — Ownership Statistics

Rebuilds the entire ABSD_OWST table from scratch. ABSD_OWST stores aggregate fleet statistics per OWCODE: vessel counts by ownership role and vessel status. The computation joins ABSD_HIOW (registered owner), ABSD_HISM (ISM manager), ABSD_HIMA (ship manager), ABSD_OWSH (ownership-ship links), and ABSD_HIST (status) using the vwABSD_OWXR_EDM view as the ownership role resolution layer.

Five-part OWST calculation (from source):

Column	Definition
OWNCOUNT_CNT	Count where ABSD_OWSH.REL1_1/2/3 = 'RC' (Registered Owner) AND vessel status is active (S,L,T,R,B,C,V,J,P,O,U,A,F,E)
MANCOUNT_CNT	Count where company appears in ABSD_HISM (ISM ship manager) AND status active
PARTONE_CNT	Count where REL1_1='IRP' (Indirect Registered P&I) relationship
PARTTWO_CNT	Count where REL1_2='RP' (Registered P&I)
PARTTHREE_CNT	Count where REL1_3='P' (P&I)
PARTFOUR_CNT	Fourth-tier party count (charterer/operator role)
PARTFIVE_CNT	Fifth-tier party count (agent role)
IRP_AGENTFOR	Sum of PART1+2+3+4+5 — total "indirect responsible parties"
RP_AGENTFOR	Sum of PART2+3+4+5
P_AGENTFOR	Sum of PART3+5 — P&I related

Implementation: builds a #ABSD_OWST_TEMP temp table, LEFT OUTER JOINs each count as a subquery keyed by OWCODE, then does a final MERGE/UPDATE into ABSD_OWST. Takes several minutes to run on the full ~280,000 company dataset.

The ABSD_HIST trigger calls a single-LRNO variant of this logic; spRecomputeABSD_OWST is for full rebuild (e.g. after bulk ownership imports).

spRecomputeABSD_OWST_Diffs

Compares the newly computed OWST (from a temp computation) against the current ABSD_OWST and writes a diff report. Used to verify the correctness of OWST after bulk imports without immediately committing changes. Returns rows where any count differs between computed and stored values.

spUpdateOWNA / spOWNAFullNameWithIndex

spUpdateOWNA — Updates the company name in ABSD_OWNA (current record, SEQNO='00') and writes a tblChanges annotation. Triggers ABSD_OWNA_Update which cascades to update ABSD_SHIP_SEARCH for all vessels linked to this company.

spOWNAFullNameWithIndex — Returns the full company name plus OWCODE for a given company, formatted for display in the UI owner search index. Used by the quick-search type-ahead in the company lookup field.

spFairplay* — Fairplay Data Integration

SP	Purpose
spFairplayCurrentOwnership	Returns current ownership data in Fairplay output format (for FP data exchange)
spFairplayCurrentOwnershipEmulate	Emulates FP ownership format output for testing/comparison
spFairplayCurrentOwnershipEmulateSL	Same but filtered to Status=S/L (active fleet)
spFairplayOwnershipEmulate	Historical ownership in FP format
spFairplayShipnameCompare	Compares ABSD ship names against Fairplay names using fnTidyShipname + similarity scoring
spFairplayTblShipSelect	Selects vessels for inclusion in the FP tblShip export table
spFPABFleetCompare	Compares ABSD fleet data against FP Absolute Register for discrepancy reporting
spFPAutoUpdateOWGEBatch	Batch-updates ABSD_OWGE from Fairplay company data feed
spFPAutoUpdateOWNABatch	Batch-updates ABSD_OWNA names from Fairplay

spFPL* — Fairplay Link Owner Update Pipeline

7-SP pipeline for importing Fairplay owner data and merging it into ABSD company records:

SP	Stage
spFPLPrepareFPLOWNTEMP	1 — Loads raw FP owner records into FPLOWNTEMP staging table
spFPLAddressPreCompact	2 — Normalises addresses in FPLOWNTEMP (remove double-spaces, clean special chars)
spFPLAddressPreCompactFP	3 — FP-specific address normalization rules
spFPLNewOwners	4 — Identifies FP records with no ABSD match → creates new ABSD company records
spFPLNewOwnersFP	4b — FP variant of new-owner creation
spFPLOwnerUpdates	5 — Applies FP owner data updates to existing ABSD records
spFPLOwnerUpdatesFP	5b — FP variant of owner updates
spFPLUpdateOwnersPersonnel	6 — Updates personnel records linked to FP-sourced companies
spFPLKludgeShipNames	Cleanup — corrects ship name mismatches introduced by FP import

spCoMatch* — Company Matching

Fleet overlap analysis SPs used when merging duplicate company records:

• spCoMatchFleetList — Returns all vessels where OWCODE appears in any ownership role (owner/manager/operator)
• spCoMatchFleetPercent — Returns the % fleet overlap between two companies (used to detect likely duplicates)
• spCoMatchABSDFleetOwnerSummary/ManagerSummary/ParentSummary — Fleet count summaries by ownership role for a given OWCODE

Supporting SP: spReplaceandDeleteOwcode — merges all references from one OWCODE to another then deletes the source OWCODE. Used after confirming two records are duplicates.

spBestAddress / spFiveLinesofAddress

spBestAddress — Returns the "best" address for a company by applying a priority ranking to available ABSD_OWAD1/2 records (prefer registered office, then head office, then first available address). Returns the top-ranked address as a structured result set.

spFiveLinesofAddress — Formats the best address into exactly 5 display lines (standard postal format for print output). Handles country code → country name expansion, concatenation of address fields, and trimming. Used by the APS narrative and register book output.

spAddressTRF / spAddressTRFJapAudit

spAddressTRF — Transfers/copies address data between ABSD_OWAD1 records for a company, typically when an address type changes (e.g. registered office becomes correspondence address). Uses spCopyAddressComms for the comms record copy.

spAddressTRFJapAudit — Japan-specific audit variant that logs address transfers to a separate audit table for regulatory compliance.

GROUP_FLEET Pipeline — Overview

The GROUP_FLEET system computes fleet ownership relationships for the Seaweb/ISR group-fleet product. It identifies which vessels belong to the same corporate group by traversing ownership and management chains. The pipeline runs as a scheduled job and writes results to the GROUP_FLEET table.

sp_GROUP_FLEET_p_parallel — Master Runner

Orchestrates the full GROUP_FLEET pipeline by calling the 7 sub-SPs in order. Called by the SQL Agent job. Runtime: typically 2–4 hours on the full fleet.

GROUP_FLEET Pipeline Steps

• 1
  sp_GROUP_FLEET_p_wsg — Builds the initial work_owners record set: all vessels with status in active fleet, including their registered owner, manager, and P&I party codes from ABSD_HIOW/HISM/HIPP.
• 2
  sp_GROUP_FLEET_p_wsg_update — Expands the work set: traverses ABSD_OWGE/OWNA to find parent company relationships (CBCOROOT/CBPPROOT codebook entries). Sets group_owner = ultimate parent OWCODE.
• 3
  sp_GROUP_FLEET_p_date_update — For each vessel/company pair in work_owners, looks up the effective date the company first appeared in the ownership chain (from ABSD_HIOW, ABSD_HISM, ABSD_HIPP in priority order).
• 4
  sp_GROUP_FLEET_p_date1_update — Second date pass: fills in EFD where the primary date lookup returned null (checks alternative ownership roles).
• 5
  sp_GROUP_FLEET_p_fleet_update — For each vessel, updates work_owners with the fleet_manager OWCODE and the EFD from which that company was fleet manager (checks ABSD_HISM, ABSD_HIOW, ABSD_HIPP in that order for the EFD).
• 6
  sp_GROUP_FLEET_p_dispow_update — Resolves the "disponent owner" (the entity in operational control, distinct from registered owner). Updates disponent_owner in work_owners.
• 7
  sp_GROUP_FLEET_remove_groups_dups — Deduplicates the work_owners result set (removes rows where the same vessel appears under multiple group paths), then writes final output to GROUP_FLEET table.

Companion: sp_GROUP_FLEET_p_date_update_temp / sp_GROUP_FLEET_p_date1_update_temp / sp_GROUP_FLEET_p_wsg_update — temporary/debug variants used for incremental testing.

spUpdateOwnerSearch

Rebuilds the ABSD owner search index table (used by the company quick-search UI). Joins ABSD_OWGE + ABSD_OWNA + ABSD_OWST to produce a denormalized search-friendly record per company. The _PreEDMEntityChanges variant is a frozen pre-migration snapshot kept for rollback reference.

spABSD_SHIP_SEARCH_SYNC — Full Search Index Rebuild

9-step full rebuild of the ABSD_SHIP_SEARCH denormalized index table (one row per vessel, ~200 columns). Called after bulk data imports or when search results are suspected to be out of sync. Steps:

1. Populate LRNO and name fields from ABSD_OVNA (SEQNO='00')
2. Join ABSD_OVTY for type codes (INTBASIC, TYPA-E)
3. Join ABSD_HIFL for flag state and home port
4. Join ABSD_HIOW, HIMA, HISM, HIOP for current ownership parties
5. Join ABSD_HITL for gross/net tonnage and LDT
6. Join ABSD_HIDR for DWT and draft
7. Join ABSD_HIST for status code
8. Join ABSD_HIMO for main engine type and kW
9. Join SUPPLEMENTAL_ABSD_OVGE for MMSI

Runtime: 15–30 minutes. Companion SP: spPostUpdateABSD_SHIP_SEARCH (incremental post-update sync for a specific LRNO), spUpdateABSD_SHIP_SEARCH (single-vessel targeted update).