Section 14: Classification, Survey & Inspection Tables

External classification society staging (DNV, BV, IRS, IACS), flag-state supplementary feeds (Panama monthly, Jane's), Port State Control inspection data (PSC daily tables, MOU, AMSA), and Data Source Management

DNV Tables

BV Tables

IRS Tables

tblPSC Tables

MOU Tables

ⓘ

These tables are read-only reference staging: Unlike the TIP/MIS pipeline tables (Section 08/13), most tables in this section are not processed through the analyst review workflow. They are used directly by stored procedures (Section 12e) and views for enrichment, cross-reference, and regulatory reporting. Data in these tables does not flow through triggers into ABSD_ master tables.

DNV Machinery Narrative Tables

DNV (Det Norske Veritas) supplies machinery narrative data as free-text survey summaries. The DNV tables hold propulsion and machinery descriptions keyed on LRNO — they are used by narrative generation functions (fnAPSNarrative*) to populate the machinery description fields in VSL_ output tables.

DNV_machinerysummary_M21_PropulsionThr (and variants)

DNV Narrative

Each DNV machinery table corresponds to one machinery category code (M21 = propulsion/thruster, M22 = auxiliary engines, etc.). The table holds one row per vessel per supply cycle, with a long-form narrative string describing the surveyed machinery.

Column	Type	Description
`LRNO`	varchar(10)	Lloyd's Register vessel number — key for joining to ABSD_ master tables. Note: 10 chars here vs char(7) in ABSD_ — left-pad with zeros if needed.
`Narrative`	varchar(8000)	Full machinery survey narrative text as supplied by DNV. May contain multiple engine entries delimited by semicolons or newlines.
`SupplyDate`	datetime	Date DNV supplied this record (where present — some variants omit this)
`MachineryCode`	varchar(10)	DNV category code matching the table name suffix (M21, M22, etc.)

DNV Table Inventory (16 tables)

Table Name	Category	Description
`DNV_machinerysummary_M21_PropulsionThr`	M21	Main propulsion engine + thruster narratives
`DNV_machinerysummary_M22_AuxEngine`	M22	Auxiliary engine (generator set) narratives
`DNV_machinerysummary_M23_Boiler`	M23	Boiler and steam system narratives
`DNV_machinerysummary_M24_Pump`	M24	Cargo and ballast pump narratives
`DNV_machinerysummary_M25_Compressor`	M25	Compressor and refrigeration plant narratives
`DNV_machinerysummary_M26_Crane`	M26	Deck crane and lifting gear narratives
`DNV_machinerysummary_M27_Windlass`	M27	Windlass, winch, and mooring equipment narratives
`DNV_machinerysummary_M28_Hatch`	M28	Hatch cover and closing appliance narratives
`DNV_machinerysummary_M29_Electrical`	M29	Main electrical installation narratives
`DNV_machinerysummary_M30_Emergency`	M30	Emergency generator and power narratives
`DNV_machinerysummary_M31_Steering`	M31	Steering gear narratives
`DNV_machinerysummary_M32_FireFighting`	M32	Fire-fighting and safety systems narratives
`DNV_machinerysummary_M33_NavEquip`	M33	Navigation equipment narratives
`DNV_machinerysummary_M34_Communication`	M34	Communication equipment narratives
`DNV_machinerysummary_M35_Gearbox`	M35	Reduction gear and shaft line narratives
`DNV_machinerysummary_M99_Other`	M99	Miscellaneous/other machinery narratives

⚠

LRNO padding: DNV stores LRNO as varchar(10); ABSD_ uses char(7). The join condition is CAST(LTRIM(RTRIM(dnv.LRNO)) AS char(7)) = absd.LRNO — or equivalently match on the rightmost 7 characters after stripping leading zeros.

Bureau Veritas Amendment Tables

BV (Bureau Veritas) supplies machinery amendment records as structured tabular data rather than narratives. Each row in a BV table represents one engine or machinery item with its technical specification details. BV data is used to cross-check and update ABSD_HIMO (main engine) and ABSD_MAEM1 (auxiliary engine) records.

BV_AMEND (and variants)

Bureau Veritas Engine Specs

Primary BV amendment staging table. Contains one row per engine or machinery amendment supplied by BV. The SEQNO column identifies which specific engine on the vessel (1 = main engine, 2+ = auxiliary engines, matching ABSD_ SEQNO convention).

Column	Type	Description
`LRNO`	char(7)	Lloyd's Register vessel number
`SEQNO`	char(2)	Engine sequence: `00` = main engine, `01`+ = auxiliary engines (matches ABSD_HIMO.SEQNO)
`ENGINE BUILDER`	nvarchar(100)	Engine manufacturer name as supplied by BV
`COB`	varchar(4)	Country of build code for the engine manufacturer
`DESIGN`	nvarchar(100)	Engine design/series designation (e.g. `MAN B&W 6S60MC-C7`)
`DESIGNATION`	nvarchar(100)	Full designation string combining type code and design mark
`NO OF CYLS`	int	Number of cylinders
`Bore`	decimal	Cylinder bore diameter (millimetres)
`Stroke`	decimal	Piston stroke (millimetres)
`Max. Power`	decimal	Maximum continuous rating (MCR) in kilowatts
`RPM`	decimal	Rated shaft RPM at MCR
`ENGINE TYPE`	varchar(10)	BV engine type code (maps to ABSD_ engine type codebook)
`SUPPLY_DATE`	datetime	Date BV supplied this amendment record

BV Table Family (24 tables)

BV supplies amendments across 24 specialised tables covering different machinery categories and supply cycles. Common variants include:

Table Pattern	Description
`BV_AMEND`	Current main amendment staging table
`BV_AMEND_HIST`	Historical amendment archive (prior supply cycles)
`BV_AMEND_NEW`	New-record amendments not yet processed into ABSD_
`BV_ENGINE_SPEC`	Detailed engine specification records with full test-bed data
`BV_PROPULSION`	Propulsion arrangement records (shaft lines, gearboxes)
`BV_AUXENGINE`	Auxiliary engine amendments separate from main engine feed
`BV_UNMATCHED`	Rows where LRNO matching failed — require manual resolution
`BV_REJECTED`	Rows rejected by validation rules during pipeline processing

IRS Survey Data Tables

IRS (Indian Register of Shipping) supplies survey schedule and class notation data. The 25 IRS tables hold current and historical survey records keyed on IMO number, which the pipeline resolves to LRNO before comparing against ABSD_OVSMC (Safety Management Certificate) and survey schedule fields.

IRS_data (and variants)

Indian Register Survey Schedules

Core IRS survey data table. Holds the current survey schedule for IRS-classed vessels, including special survey and intermediate survey due dates.

Column	Type	Description
`IMO Ship No`	varchar(10)	IMO number (= LRNO for LR-registered vessels; used as join key)
`IRS Number`	varchar(20)	IRS internal registration number
`Vessel Name`	nvarchar(150)	Vessel name as recorded by IRS
`Flag`	varchar(3)	Flag state at time of survey record
`Class Notation`	varchar(200)	Full IRS class notation string (e.g. `+100A1 Bulk Carrier ESP BC-A AHL NS`)
`Last Special Survey Date`	varchar(10)	Date of most recent Special Survey (DD/MM/YYYY format)
`Next Special Survey Date`	varchar(10)	Due date for next Special Survey (DD/MM/YYYY format)
`Last Intermediate Survey Date`	varchar(10)	Date of most recent Intermediate Survey
`Next Intermediate Survey Date`	varchar(10)	Due date for next Intermediate Survey
`Class Status`	varchar(30)	Current class status: `In Class`, `Class Suspended`, `Class Withdrawn`, etc.
`Load Date`	datetime	Timestamp this row was loaded into staging

ⓘ

Date format note: IRS supplies dates as DD/MM/YYYY strings, unlike ABSD_'s YYYYMMDD convention. Conversion uses CONVERT(datetime, IRS_date, 103) before any comparison with fnLRDateToRealDate() output.

IRS Table Family (25 tables)

Table Pattern	Description
`IRS_data`	Current supply cycle survey data
`IRS_data_PREV`	Previous supply cycle — used for delta comparison
`IRS_data_HIST_YYYYMMDD`	Point-in-time snapshots (one per supply cycle, retained for 12 months)
`IRS_UNMATCHED`	IMO numbers supplied by IRS that failed LRNO resolution
`IRS_NEW_VESSELS`	Vessels in IRS supply not yet in ABSD_ — candidate new-vessel queue
`IRS_CLASS_CHANGES`	Delta table: vessels where class status changed between supply cycles
`IRS_SURVEY_OVERDUE`	Vessels where Next Special Survey Date has passed with no updated record

IACS Classification XML Tables

IACS (International Association of Classification Societies) provides a common XML data exchange format used by all 12 IACS member societies. The IACS_CLASS_XML table holds the parsed and normalised content of IACS XML survey returns.

IACS_CLASS_XML

IACS XML Survey Data

Parsed IACS common XML format records. Each row represents one survey record for one vessel from one IACS member society. The Provider column identifies which of the 12 member societies supplied the record, enabling cross-society survey tracking.

Column	Type	Description
`LRNO`	char(7)	Lloyd's Register vessel number — primary join key to ABSD_
`dSurvey`	datetime	Date the survey was conducted
`dNextSurvey`	datetime	Next survey due date as stated in the survey record
`cStatus`	varchar(10)	Class status code: `IC`=In Class, `CS`=Class Suspended, `CW`=Class Withdrawn, `CD`=Class Deleted
`dStatus`	datetime	Date the status in `cStatus` was assigned
`cReason`	varchar(200)	Reason code or free-text reason for the status (mandatory for CS/CW/CD)
`Provider`	char(2)	Two-character IACS member society code: `LR`, `NK`, `BV`, `GL`, `DNV`, `AB`, `RI`, `KR`, `CCS`, `RS`, `IRS`, `CRS`
`SurveyType`	varchar(20)	Type of survey: `Special`, `Intermediate`, `Annual`, `Continuous`
`ClassNotation`	varchar(500)	Full class notation string as supplied in the XML record
`LoadDate`	datetime	Timestamp this XML record was parsed and loaded

ⓘ

IACS XML format: The IACS common data exchange format (CDF) is a standardised XML schema agreed across all IACS members. LR's IACS_CLASS_XML table reflects parsed content from the <SurveyRecord> elements. The XML parsing stored procedure is spLoadIACSClassXML.

tblDSM_ClassSource — Data Source Management

tblDSM_ClassSource

DSM Source Registry

Master registry of external data sources managed by the Data Source Management (DSM) team. Each row represents one classification society or data provider that LR receives data from. Used primarily for supplier contact management and visit scheduling.

Column	Type	Description
`Class_Code`	char(2) PK	Two-character classification society / source code (matches ABSD_ convention)
`Source`	nvarchar(100)	Full organisation name
`Source_Contact`	nvarchar(200)	Named contact person at the source organisation
`Visited`	bit	1 = DSM team has conducted an on-site visit to this source
`Date_Visited`	datetime	Most recent visit date
`Notes`	nvarchar(2000)	Free-text notes on the data supply relationship, known data quality issues, or pending agreements
`Address1` – `Address5`	nvarchar(200) each	Five-line postal address of the source organisation
`Country`	varchar(3)	ISO country code of the source organisation's registered address
`Telephone`	varchar(30)	Primary telephone number
`Fax`	varchar(30)	Fax number (legacy — may be empty for modern sources)

Panama Monthly Amendments Feed

Panama_NewData_MonthlyAmends

Panama Flag Monthly Batch

Dedicated staging table for the Panama Maritime Authority (AMP) bulk monthly amendment file — distinct from the API-based tblTIP_PAN3_NEW feed. The AMP sends a complete delta extract once per month covering all vessel amendments since the previous supply. This table receives the raw extract before the TIP pipeline processes it.

Column	Type	Description
`Secnaves`	varchar(20)	Panama registry number (AMP internal identifier)
`LRNO`	char(7)	Matched LRNO (populated by pipeline after IMO-to-LRNO resolution)
`ShipName`	nvarchar(150)	Vessel name per Panama registry
`ExName`	nvarchar(150)	Previous name (most recent name change only)
`OffNum`	varchar(20)	Official number — Panama's internal vessel identifier used in official certificates
`Call_Sign`	varchar(10)	Radio callsign assigned by Panama
`Nt`	decimal	Net tonnage per Panama measurement
`Gt`	decimal	Gross tonnage per Panama measurement
`Shiptype_description`	nvarchar(100)	Ship type in AMP's classification system (not aligned to ABSD_ STATCODE)
`RegistryDate`	varchar(10)	Panama registration date (DD/MM/YYYY)
`Build`	varchar(4)	Year of build (YYYY)
`IMO`	varchar(10)	IMO number used for LRNO matching
`LoadDate`	datetime	Batch load timestamp for this monthly extract
`AmendType`	varchar(1)	`N`=New registration, `A`=Amendment, `D`=Deletion from register

Jane's Fighting Ships — janes_all

janes_all

Naval Vessels Jane's

Staging table for data supplied from Jane's Fighting Ships — the standard reference for naval and military vessel intelligence. This feed covers warships, auxiliaries, and government vessels not typically registered with flag-state authorities. The data is used to cross-check and update records for military vessels in the ABSD_ fleet.

Column	Type	Description
`idno`	int IDENTITY PK	Auto-incrementing row identifier
`lrno`	char(7)	LR vessel number — populated after pipeline matching
`shipname`	nvarchar(150)	Vessel name as listed in Jane's (may include hull/pennant number for naval vessels)
`exname`	nvarchar(150)	Previous name or class name
`dob`	varchar(4)	Year of build (YYYY)
`gross`	decimal	Gross tonnage (or displacement tonnes for warships)
`dwt`	decimal	Deadweight or full load displacement
`shiptype`	varchar(10)	Jane's vessel type code (frigates, destroyers, submarines, etc.)
`LoadDate`	datetime	Date this Jane's record was loaded
`NavyCountry`	varchar(3)	ISO country code of the naval or coast guard operating this vessel
`PennantNumber`	varchar(10)	NATO pennant/hull number (where applicable)

⚠

IMO number absence: Military vessels generally do not hold IMO numbers. LRNO matching for janes_all uses name and year-of-build matching via spJanesLRNOMatch rather than IMO lookup. Match confidence is lower than for commercial vessels.

Port State Control Inspection Tables

PSC (Port State Control) inspections are conducted by regional MOUs (Memoranda of Understanding) — international agreements between port states to inspect foreign vessels calling at their ports. LR receives PSC data from 11 regional MOU sources, each with its own staging table structure. The data is used for risk scoring, flag-state performance metrics, and operational intelligence products.

MOU Source
Paris / Tokyo / etc.

→

tblPSC_* Staging
Raw inspection data

→

spInsertPSC_*
Normalisation SP

→

Risk Score Tables
tblRiskScreeningData

tblPSC_Daily_Paris

Paris MOU Daily Feed

Daily PSC inspection data from the Paris MOU — the largest and most active regional PSC organisation, covering European and North Atlantic ports. This table receives fresh inspection records each day, including detention decisions within hours of the inspection concluding.

Column	Type	Description
`InspectionDate`	datetime	Date the PSC inspection was conducted
`Port`	nvarchar(100)	Name of the port where inspection occurred
`Country`	varchar(3)	ISO country code of the inspection port
`IMO`	varchar(10)	Vessel IMO number (= LRNO)
`VesselName`	nvarchar(150)	Vessel name at time of inspection
`Flag`	varchar(3)	Flag state at time of inspection
`CallSign`	varchar(10)	Radio callsign
`ShipType`	varchar(50)	Paris MOU ship type description
`GrossTonnage`	decimal	Gross tonnage
`YearOfBuild`	int	Year of construction
`ClassSociety`	varchar(10)	Classification society code
`DeficiencyCount`	int	Total number of deficiencies recorded during the inspection
`Detained`	bit	1 = vessel was detained (not allowed to sail until deficiencies remedied)
`DetentionDate`	datetime	Date detention order issued (where Detained=1)
`ReleaseDate`	datetime	Date vessel released from detention (NULL while still detained)
`Deficiency1` – `Deficiency20`	varchar(10) each	Up to 20 individual deficiency code columns (Paris MOU deficiency code system)
`LoadDate`	datetime	Timestamp this inspection record was loaded into the staging table

Other tblPSC_* Tables (26 total)

All MOU Regions

Each of the 11 PSC MOU regions has at least one staging table. The Paris MOU receives daily data; most others receive weekly or monthly bulk extracts. All tables follow the same broad column structure as tblPSC_Daily_Paris with minor variations per MOU reporting format.

Table	MOU	Region	Feed Frequency
`tblPSC_Daily_Paris`	Paris MOU	Europe / North Atlantic	Daily
`tblPSC_Tokyo`	Tokyo MOU	Asia Pacific	Weekly
`tblPSC_USCG`	USCG (US Port State)	United States	Weekly
`tblPSC_AMSA`	AMSA (Australia)	Australia / Pacific	Weekly
`tblPSC_BlackSea`	Black Sea MOU	Black Sea / Danube	Monthly
`tblPSC_Caribbean`	Caribbean MOU	Caribbean	Monthly
`tblPSC_IndianOcean`	Indian Ocean MOU	Indian Ocean	Monthly
`tblPSC_MediterraneanMOU`	Mediterranean MOU	Mediterranean	Monthly
`tblPSC_Riyadh`	Riyadh MOU	Gulf / Middle East	Monthly
`tblPSC_VDM`	Vina del Mar Agreement	South America	Monthly
`tblPSC_Abuja`	Abuja MOU	West / Central Africa	Monthly

ⓘ

26 tables, 11 MOUs: Several MOUs have both a current staging table and a historical archive table (e.g. tblPSC_Tokyo + tblPSC_Tokyo_HIST). The stored procedures in Section 12e (spInsertPSC_*) handle deduplication when re-loading a supply.

MOU_INSPECTION

Legacy PSC Consolidated

Consolidated legacy PSC inspection table — a pre-rationalisation table that held cross-MOU inspection data before the individual tblPSC_* tables were created. Now used primarily for historical lookups and for MOU sources that do not yet have their own dedicated staging table.

Column	Type	Description
`repauth`	varchar(10)	Reporting authority code (MOU identifier: `PARIS`, `TOKYO`, `USCG`, etc.)
`lrno`	char(7)	LR vessel number
`name`	nvarchar(150)	Vessel name at time of inspection
`callsign`	varchar(10)	Radio callsign
`type`	varchar(50)	Ship type description per reporting MOU
`gross`	decimal	Gross tonnage
`yob`	int	Year of build
`flag`	varchar(3)	Flag state at time of inspection
`st_insp`	datetime	Start date of the inspection period
`pl_insp`	datetime	Place of inspection (port) — stored as varchar in some variants
`date_det_ord`	datetime	Date detention order issued (NULL if not detained)

tblAMSA_Inspections

AMSA Australia PSC

Dedicated staging table for AMSA (Australian Maritime Safety Authority) PSC inspection data. AMSA provides more granular deficiency coding than most other MOUs — using specific statutory instrument codes rather than generic deficiency descriptions. Each CODE_* column holds one of AMSA's inspection outcome codes.

Column	Type	Description
`IMO`	varchar(10)	IMO number (= LRNO)
`VesselName`	nvarchar(150)	Vessel name
`Flag`	varchar(3)	Flag state at inspection
`SHIP_DETAINED`	bit	1 = vessel detained by AMSA officer
`DETENTION_DATE`	datetime	Date detention commenced
`RELEASE_DATE`	datetime	Date detention lifted (NULL while ongoing)
`CODE_40`	varchar(20)	AMSA inspection code 40 — Fire safety systems
`CODE_50`	varchar(20)	AMSA inspection code 50 — Lifesaving appliances
`CODE_60`	varchar(20)	AMSA inspection code 60 — Load lines and stability
`CODE_70`	varchar(20)	AMSA inspection code 70 — MARPOL / pollution prevention
`CODE_80`	varchar(20)	AMSA inspection code 80 — Crew and accommodation
`INITIAL_INSPECTION_VISIT_EVENT_ID`	varchar(50)	AMSA inspection event ID — unique identifier in AMSA's National Inspection Database (NID)
`InspectionDate`	datetime	Date inspection conducted
`Port`	nvarchar(100)	Australian port where inspected
`LoadDate`	datetime	Timestamp row loaded into staging

ⓘ

AMSA deficiency codes: AMSA uses the PSC 2000 deficiency code system where each code corresponds to a specific IMO instrument and regulation chapter. The CODE_* values are the relevant code numbers (e.g. 0401 = fire safety — structural, 0402 = fire safety — detection and alarm). NULL = no deficiency in that category.