LR Maritime Database Documentation

Database statistics, architecture, key identifiers, LR field coding system, SEQNO history model, date conventions, trigger disable mechanism, change tracking, status codes, and reference code tables.

Complete column-by-column catalog for vessel identity (OVGE, OVNA, OVTY, OVCA, OVCS, OVDOC, OVSMC, OVISSC), ownership history (HIOW, HIMA, HISM, HIOP, HIPP, HIGBO, HIFL, HITL, HIST, and others), and fuel/equipment (FUGE, FUDI1, FUSF/FUSF2).

CA* casualty tables, CB* codebook/reference tables (CBUB1, CBCY, CBCOROOT, CBPPROOT), OW* company/owner tables, standalone tables (TANKER, GROUP_FLEET, DEFECT, SALE), SUPPLEMENTAL_ external feed tables, and VSL_ output tables.

All 282 triggers documented — universal patterns (J06 update, cursor iteration, annotation), OVGE/OVNA/HIOW/HIMA/HIPP validations, CAGE1 casualty cascade, CBCOROOT/CBPPROOT rules, FUGE equipment validation, HIGE electrical rules, TANKER coating and hull-type rules.

All 281 functions across 11 categories: date conversion, ownership point-in-time, string normalization, fuzzy matching (Levenshtein, HowSimilar), address assembly, narrative generation, callsign/MMSI validation, geographic (DMS, Marsden Grid), classification, company/owner, and annotation/audit.

GROUP_FLEET suite (7 procedures), spABSD_SHIP_SEARCH_SYNC (9-step rebuild), spAnnotateChange (field-level audit), spAddDeleteFUSF2 (feature codes), spAddressTRF. Views: vwFleetDetails, vwISROwnerSelection, vwABSD_OWGE_EDM, vwCasualty, standard Current views, and the implicit relationship model.

LRNO→IMO mapping, date conversion matrix with BigQuery UDFs, SEQNO→bi-temporal conversion, trigger→BigQuery mapping (18 triggers, 4 patterns), function migration, GROUP_FLEET as BigQuery procedure, data quality rules, SHIP_SEARCH options, CDC pipeline (Debezium → Pub/Sub → Dataflow → BQ).

Comprehensive coverage of both processing pipelines (Classic tblThirdPartyData and FlexTemp tblTIPMISFlexTemp), all 10 configuration tables with full schemas, 40+ TIP_* source tables, BV XML supply, PSC and SBR loaders, LRNO derivation, previous supply comparison, LRF comparison, validation, auto-processing, action processing (Keep/Learn/Overwrite), business rules engine, manipulation rules engine, SUPPLEMENTAL tables and their triggers, and a full SP reference index.

All 19 SUPPLEMENTAL_ extension tables: full column schemas, trigger logic step-by-step, cross-table relationships, and guard tables. Covers OVGE (MMSI/MMSI_EFD/YARD_NO/FISHNO20), HIFL (Callsign/MMSI history), HITL (LDT/scrap price triangle), CADI (scrap value), CBCY (sanctions), FUDI1/FUDI2, FUGE, FUUN1, STDE, STSE_COMPDATE, STSE, HIFC1, OVTY (STAT5CODE/HULL_SHAPE), and the OVGE_UPDATES staging table.

12-part comprehensive trigger reference built line-by-line from SQL source. Universal pattern: TRIGGER_DISABLE guard → cursor → validation SPs → cascade → J06 audit → tblChanges → tblAnnotationLogGeneral → SHIP_SEARCH. Covers all 282 triggers. See individual parts in the Trigger Series section below, or start with the overview for the universal pattern and guard table architecture.

All 281 UDFs in 17 categories with full signatures and implementation notes derived from source. Key functions: fnLRDateToRealDate (YYMMDD/YYYYMMDD→DATETIME with sentinel and leap-year handling), fnRealDateToLRDate (reverse), APSProper (title-case+FPLNAMEEXCEPTIONS), edit_distance (Wagner-Fischer Levenshtein), AA_fn_phon_nysiis (NYSIIS phonetic), CleanCompanyNameForMatch (16+ legal suffix strips), fnTidyShipname (prefix removal via shipnameexceptions), fnAPSNarrative* family (17 narrative generators using TVP PrimeMoverGrouped), fnClass, fnCallsignCheck.

All val* and spValidate* SPs: universal 0/1 return convention, NULL-always-passes rule, valStandard1 (conf codes), valStandard2/NoNull (2-char alpha), valStandard3/3Num (3-char + numeric+conf), valStandard4/_new (YYMMDD), valStandard8 (numeric string), valStandard9new (YYYYMMDD with full calendar logic including leap year and 30/31-day months), valStandard10/_new (flexible 6/8-char), valStandardYN, valStandardAlpha/Normal, valOwner* family, spValidateMMSI (format + EDM range), spValidateLengths (LOA≥LBP≥REG), spValidateCallsign, spValidateManbow_Len, spValidateC02_BBOW, spValidateHIMO*/spValidateMAAU*.

spCreateNewShipWithStatus (62-table creation in 7 transactions, vessel lock mechanism, post-creation integrity check), spCreateNewShipWithNCON_CONFI, spPreLoadNewShipWithStatusA, spDeleteShip (irreversible 62-table deletion), spUpdateShipStatus/WithStatus, spStatusChangeToSS, spNEWCONDateRollForward Phase 1/2/3 (newbuild date advance), spMakeNewCas (casualty SEQNO re-sequence), spCreateNewCasualty, spShipHeaderQuick, spShipTimeline.

SEQNO history model (Pattern A sequential re-sequence vs Pattern B HIFL 95-slot). All 40+ spMake*Historical SPs (HIFL mirrors SUPPLEMENTAL_, HIOW triggers OWST cascade, HIPP vs HIPP_DATED dual model, MakeSALEHistoricalWithPrice scrap triangle). spCreateNew* sub-record SPs (OVSMC, DOC, ISSC, FUSF2/spAddDeleteFUSF2, Sale, Company, AuxEngine, LRSC/SU/WD, BBC, Breaker, Builder, CA*/casualty events, OW* company sub-records). 70+ spDelete* SPs with standard pattern.

Annotation system: spAnnotateChange (dynamic SQL + spGetEnglishFieldName lookup), spWriteAnnotation* family (5 SPs), spSearchAnnotations* (primary vs secondary, ship vs company), spBackfillAnnotations, spCleanAnnotations. Ownership: spRecomputeABSD_OWST (5-part fleet statistics), spUpdateOWNA. Fairplay integration: spFairplay* + spFPL* 7-SP pipeline. Company matching: spCoMatch* + spReplaceandDeleteOwcode. Address: spBestAddress, spFiveLinesofAddress. GROUP_FLEET 7-step pipeline. spABSD_SHIP_SEARCH_SYNC (9-step rebuild).

PSC port-state-control: 11 MOU sources (Paris/Tokyo/USCG/AMSA/Black Sea/Caribbean/Indian Ocean/Med/Riyadh/VDM/Paris-Daily), Insert/Update/Delete upsert pattern, XML and flat-file variants, daily delta loaders. Sanctions: OFAC SDN load + company matching, UN sanctions. Data loading: Janes (7 SPs), spLOAD_ABSD_*, SBR processing. Production/APS: spProduction*, NB Email builders, spEDM_BF_* backfill. 80+ RowSource SPs for UI dropdowns. 60 Spot_* diagnostics + 5 Fog_* performance monitors + DBA misc.

EDM backfill SSIS control tables (tblEDM_BF_SSIS_CONTROL, tblEDM_BF_AUDIT, inspection/certificate staging, EDMClassRO, EDMPortAlias), TIP_* raw flag-state staging (45+ sources, common schema), tblTIP_* structured feeds (IOM, PAN3, IR2, CCS, TL2), and tblMIRS_* sanctions-matching MASTERLOAD tables (77 tables across 9 international authorities with Levenshtein scoring).

DNV machinery narrative tables (16, M21–M99 categories), BV amendment data (24 tables, engine spec by LRNO/SEQNO), IRS survey data (25 tables, special/intermediate survey schedules), IACS common XML format (12 member societies, cStatus codes), tblDSM_ClassSource (source registry), Panama_NewData_MonthlyAmends, janes_all (Jane's Fighting Ships naval vessels), tblPSC_* daily and weekly PSC data (26 tables, 11 MOU regions), MOU_INSPECTION, tblAMSA_Inspections.

ofac_sdn2 / ofac_nonsdn2 / ofac_owsdn1 (OFAC SDN and Non-SDN staging), 20 tblOFAC_*_MASTERLOAD match tables, SDDB salvage and demolition database (13 tables — transactions, yards, LDT price series feeding SUPPLEMENTAL_ABSD_HITL), tblChanges (field-level change history with Source/SourceRef/FollowUp columns), tblAnnotationLogGeneral (analyst annotation workflow), tblFSWSalesPage (FSW commercial listing tracker).

Production_Casualties (denormalised output with ShipTypeLevel1–5, NameAtTimeOfIncident, FlagAtTimeOfIncident) and 22 Production_* sibling tables, tblFlexible_Bucket (EAV extension store) + tblFlexible_Bucket_CB (field codebook), tblStatCode5 (5-level ship type hierarchy with Level1–5 codes/decodes + Lev5Hull/Sub), TRIGGER_DISABLE and 12 table-specific guard tables, tblRiskScreeningData/Score/DataNarrative (AIS behaviour + PSC + Class dimensions), tblSustainabilityData/Narrative (ESG indicators, EEDI/EEXI, CO₂ estimates), tblVDM_AuditLog.

Interactive reference combining an SVG entity-relationship diagram (21 core ABSD_ entities, LRNO/OW_CODE join keys, domain clusters), a SEQNO history model timeline ('00' current → 'N' oldest), and a 7-layer CSS data flow architecture (External Sources → Staging → TIP/MIS Pipeline → Core ABSD_ → Triggers → Output Layer → Client Products).

Universal 10-step trigger pattern, 4 guard tables, J06 audit fields, annotation system, TRIGGER_DISABLE mechanism, validation SP catalogue.

HIST, OVGE, OVTY, HILE, HISE, OVVN — vessel identity, type, flag, and SHIP_SEARCH maintenance.

TRIGGER_DISABLE_NEWOOM workflow — 62-table initialisation sequence for new vessel records.

HIOW, HIMA, HISM, HIOP, HIGBO, HIPP, HITP, HITL, HIDR — 9 ownership-history triggers. HIGBO guard anomaly, HITL GT/NT rule, GRT_L cascade.

HIMO, HIGE, HIMT, MATH, MAAU, MAEM1-3, MASP — main engine, aux engines, gearbox. EDM migration status. Cursor patterns. HIPU/HIPS/HIPM auto-creation.

FU* series: FUCA1/2, FUCO1/2, FUDI1-4, FUDO1/2, FUGE, FUHA1/2, FULC, FULI1-3, FUOF, FURO1/2, FUSF/2, FUSP, FUST1/2, FUTO, FUUN1/2. FUUN1 EDM Apr 2026. LOC H→R→D ordering.

HI* physical/flag/classification/propulsion/electronics/tonnage/status, ST* construction triggers, LR* survey triggers (LREQ, LRGE, LRHI, LRSC, LRSU, LRWD).

CADI, CACO, CACT, CAGC, CAGE1/2, CALA, CAPO, CAPR, HAAD, HACO — casualty event recording and cascade rules.

15 OW* company trigger families. tblChanges architecture, spGetUserSettingsOwners, dual guard, OWIN INSTEAD OF pattern, EDM migrations, OWNA 7-step best-address, OWCO↔OWAD1 bidirectional sync.

CBUB1, CBCA, CBCONOTE, CBCOROOT (3-type dispatch: F/T/S), CBFT, CBPPNAME (word-wrap), CBPPROOT, CBPPSTRE, tblStatCode5, tblFlexible_Bucket.

All 20 SUPPLEMENTAL_ABSD_* triggers. LDT→scrap price chain, CBCY sanctions lifecycle, SuppOVGEUpdate dual guard, SUPP_OVTY passenger rules, SUPP_STSE yard number cascade. SuppHITL no-guard anomaly.

ABSD_NCON (OWNC rebuild, 6 satellite cascades, future-date block, EDM COB/PCOB), ABSD_NCON_CONFI (dimensions), ABSD_SALE, tblChanges/tblAnnotationLogGeneral self-triggers, QH questionnaire, ofac_sdn2 OFAC sanctions.