12e Stored Procedures — External Data & System

PSC port-state-control loaders (11 MOU sources), OFAC/UN sanctions integrations, data loading pipelines (Janes, SBR, AIS, MSC), production output SPs (APS, NB Email, EDM), codebook row-source SPs (80+), and DBA tooling (Spot_* diagnostics, Fog_* performance monitors).

PSC Pipeline Overview

Port State Control (PSC) inspection data from 11 MOU regional bodies is loaded into ABSD_INSPECTION_VISITS, ABSD_DEFECT, and ABSD_DETENTION. Each source has a three-SP set: Insert, Update, Delete — totalling ~60 PSC SPs.

PSC Insert / Update / Delete Pattern

-- Insert pattern
CREATE PROCEDURE spPSC_Insert_ParisMOU
AS
    INSERT INTO ABSD_INSPECTION_VISITS (LRNO, SOURCE, INSPECTION_DATE, PORT_CODE, RESULT, ...)
    SELECT d.LRNO, 'PARIS', d.InspDate, d.Port, d.Result, ...
    FROM tblDetentionsTempParis d  -- staging table pre-loaded from extract
    LEFT JOIN ABSD_INSPECTION_VISITS v
        ON v.SOURCE = 'PARIS' AND v.SOURCE_REF = d.SourceRef
    WHERE v.SOURCE_REF IS NULL  -- no existing record → new

The staging table (e.g. tblDetentionsTempParis) is populated by a preceding spCreateDetentionsTemp* SP which parses the raw file, resolves LRNOs via ABSD_SHIP_SEARCH, and applies basic format normalisation. The spPSC_Delete_* SPs then remove any ABSD records for the source that were NOT in the latest extract (indicates the MOU has corrected/removed them).

PSC Sources (11 MOU Bodies)

PSC Daily Load SPs

Three daily-delta loader SPs for sources that publish incremental updates:

All three: resolve LRNO via ABSD_SHIP_SEARCH, insert new defect records, skip duplicates (SOURCE + SOURCE_REF uniqueness check), write count of inserted rows to job log.

OFAC Sanctions Integration

OFAC (US Office of Foreign Assets Control) sanctions data is loaded and matched against ABSD company and vessel records.

The sanctions status for company records is stored in SUPPLEMENTAL_ABSD_CBCY (see Section 09) via the country-of-registration flag. Vessel-level sanctions screening updates tblRiskScreening which feeds the Seaweb risk product.

UN Sanctions

Loads UN Security Council consolidated sanctions list into tblUNSanctions staging. Matches against ABSD company records by name (using CleanCompanyNameForMatch + phonetic matching) and IMO number. Matched records are flagged in tblRiskScreening. Run after each UN update (typically weekly).

Janes Data Load Pipeline

Janes Defence supplies naval vessel data (warships, auxiliaries) not covered by LR survey. Seven SPs load different Janes data types:

spLOAD_ABSD_* — Bulk Data Load SPs

SBR Processing

SBR (Survey/Build Record) data comes from external survey bodies.

TIP/MIS SP Reference

The TIP/MIS pipeline (~100 SPs) is documented in full in Section 08. Quick reference of the key SPs by phase:

spProduction* — APS Output Generation

The Production SPs generate the data feeds consumed by the APS (Annual Publication System) for Register Book and online output. Each SP runs a large SELECT across multiple ABSD_ tables, applies the APS field formatting rules, and writes to a production output staging table.

Companion: spAPSRELBUILDERHISTORY — APS relationship builder history (generates the "formerly owned by" ownership history section). spPopulateAPSRelSurveyDates — populates APS survey date fields from ABSD_LRSC/LRSU.

NB Email Builder SPs

Generates email notifications for newbuild status changes sent to subscribers:

FSW (Fairplay Seaweb) NB page SPs: spFSWSalePageExName, spFSWSalePageLastSale, spFSWSalePageSameOperator — build the FP newbuild page sections for the online Register.

spEDM_BF_* — EDM Backfill

EDM (Entity Data Master) backfill SPs populate ABSD records from the EDM platform during the partial migration:

sp*RowSource* — UI Codebook Lookup SPs (80+)

RowSource SPs power the dropdown lists and lookup fields in the Captiva data-entry UI. Each SP returns a two-column result set: (Code, Description) ordered for display. They query the ABSD_CBUB1 codebook table filtered by FLDI (field discriminator) code, or in some cases query dedicated reference tables directly.

Codebook Helper SPs

Spot_* — SQL Server Diagnostics (60 SPs)

The Spot_* family (approximately 60 SPs) are SQL Server administration and diagnostic tools, not maritime business logic. They are installed in LRUpdate but are generic DBA tooling. Key categories:

Fog_* — Performance Monitoring SPs

DBA / Utility Miscellaneous