Section 13: EDM, TIP & MIS Staging Tables

External Data Management backfill tables, flag-state raw staging (TIP_*), structured feed variants (tblTIP_*), and Maritime Intelligence Reporting System sanctions-match tables (tblMIRS_*)

EDM Tables

TIP_* Tables

tblTIP_* Tables

tblMIRS_* Tables

ⓘ

Relationship to Section 08: Section 08 documents the TIP/MIS processing pipeline — the configuration tables (tblThirdPartySource, tblThirdPartyData, tblTIPMISFlexTemp, etc.) and the stored procedures that move data through stages. This section documents the raw staging landing tables where external suppliers deliver data before pipeline processing begins.

EDM Backfill Tables

The EDM (External Data Management) platform is a partial successor to the legacy ABSD_ database, managing structured ingest from classification societies and flag-state registries via SSIS packages. The tblEDM_BF_* family handles backfill — loading historical and bulk records from EDM into the ABSD_ master tables via SSIS pipelines coordinated by a control table.

EDM Platform
External source

→

tblEDM_BF_*_TEMP
SSIS landing

→

SSIS Package
spEDM_BF_*

→

ABSD_ Tables
Master records

tblEDM_BF_SSIS_CONTROL

SSIS Control Coordinator

Master control table that SSIS packages read before executing. A package checks its own row for the DISABLED flag before running, and checks RERUN to decide whether to clear its staging table before re-loading. Only rows that exist in this table represent registered EDM backfill packages.

Column	Type	Notes
`PACKAGENAME`	varchar PK	SSIS package identifier, e.g. `EDM_BF_PSC_INSPECTION`
`RERUN`	bit	1 = truncate staging table before loading; 0 = append/delta
`DISABLED`	bit	1 = package skips execution entirely; used during maintenance windows

⚠

Minimal schema by design: This table is intentionally simple — the complexity lives in the SSIS package logic itself. Setting DISABLED=1 is the standard way to pause EDM ingestion without modifying the SSIS package.

tblEDM_BF_AUDIT

Audit Log

Execution audit log written by each EDM backfill SSIS package at each processing stage. Provides a complete trace of when packages ran, what stages executed, and how many rows were affected.

Column	Type	Notes
`StepID`	int IDENTITY PK	Auto-incrementing row identifier
`Rundate`	datetime	Date the package started (date portion only, normalised)
`RunTime`	datetime	Exact timestamp the log row was written
`Process`	varchar(100)	Package name, matching `tblEDM_BF_SSIS_CONTROL.PACKAGENAME`
`ProcessStage`	varchar(100)	Stage label, e.g. `LOAD_STAGING`, `MATCH_LRNO`, `INSERT_ABSD`
`Comment`	varchar(500)	Human-readable description of the stage result
`DMLCount`	int	Rows inserted/updated/deleted in this stage; NULL if not applicable

tblEDM_BF_ABSD_INSPECTION_TEMP

PSC Staging EDM Backfill

Temporary landing table for PSC (Port State Control) inspection records arriving from the EDM platform. The SSIS package loads this table from the EDM source, then spEDM_BF_ABSD_INSPECTION reconciles records against the ABSD_ master tables. The INDICATOR column drives upsert logic.

Column	Type	Description
`INDICATOR`	varchar(1)	`I`=Insert new, `U`=Update existing, `D`=Delete — drives stored procedure branching
`INSPECTION_ID`	varchar(50)	Source system's unique inspection reference
`LRNO`	char(7)	Lloyd's Register vessel number — may be NULL if not yet matched
`SHIPNAME`	varchar(100)	Vessel name at time of inspection
`FLAG`	varchar(3)	Flag state code at time of inspection
`CLASS`	varchar(10)	Classification society code
`SHIPTYPE_CODE`	varchar(10)	Ship type from EDM taxonomy (may differ from ABSD_ STATCODE)
`INSPECTION_DATE`	varchar(8)	YYYYMMDD format per LR date convention
`PORT`	varchar(50)	Port of inspection
`COUNTRY`	varchar(3)	ISO country code of inspection port
`MOU`	varchar(10)	Memorandum of Understanding region (PARIS, TOKYO, USCG, AMSA, etc.)
`DETAINED`	bit	1 = vessel detained at this inspection
`DEFICIENCY_COUNT`	int	Total number of deficiencies recorded
`IMO`	varchar(10)	IMO number (= LRNO for LR-registered vessels)

tblEDM_BF_ABSD_PSC_CERTIFICATE_TEMP

Certificate Staging EDM Backfill

Staging table for statutory certificate records linked to PSC inspections. Each row represents one certificate examined during an inspection. Loaded by the same EDM pipeline as tblEDM_BF_ABSD_INSPECTION_TEMP; the EDM_INSP_ID joins the two tables.

Column	Type	Description
`INDICATOR`	varchar(1)	I/U/D upsert directive
`EDM_INSP_ID`	varchar(50)	Foreign key to `tblEDM_BF_ABSD_INSPECTION_TEMP.INSPECTION_ID`
`CERTIFICATE_SEQUENCE_NUMBER`	int	Position within the inspection (1-based)
`CERTIFICATE_TITLE_CODE`	varchar(10)	IMO or PSC certificate type code (SOLAS, MARPOL, etc.)
`ISSUING_AUTHORITY`	varchar(100)	Body that issued the certificate — flag administration or classification society
`CLASS_SOC_OF_ISSUER`	varchar(10)	Two-character classification society code of issuing body (where applicable)
`EXPIRY_DATE`	varchar(8)	YYYYMMDD certificate expiry date
`ENDORSED_DATE`	varchar(8)	YYYYMMDD date the certificate was endorsed/renewed

EDMClassRO

Reference Mapping

Maps EDM-supplied SMC (Safety Management Certificate) issuer codes to ABSD_ recognised values. The EDM platform uses its own classification society and flag-state codes that do not always match the two-character codes used in ABSD_OVSMC. This table is the translation dictionary.

Column	Type	Description
`ABSD_OVSMC#SMC_ISSUER`	varchar	The canonical code as stored in `ABSD_OVSMC.SMC_ISSUER`
`New Value`	varchar	Intermediate or transitional code value (used during EDM migration)
`EDM VALUE`	varchar	The code as supplied by the EDM source system

ⓘ

Migration artefact: This table exists specifically to bridge the gap between EDM's classification codes and the legacy ABSD_ two-character codes. During the EDM backfill stored procedures, EDM values are looked up here before being written to ABSD_OVSMC.SMC_ISSUER.

EDMPortAlias

Port Reference

Reference table mapping EDM port name variations (aliases) to canonical UN/LOCODE port codes. PSC inspection data from EDM often contains free-text port names; this table normalises them to the UNLOCODE standard used in ABSD_ and VSL_ tables.

Column	Type	Description
`PortAliasName`	varchar	Free-text port name as supplied by EDM source (may include misspellings, regional variants)
`ReportAuthCountry`	varchar(3)	ISO country code of the reporting PSC authority
`UNLOCODE`	varchar(5)	5-character UN/LOCODE (2-char country + 3-char location), e.g. `GBLON`
`PortCountry`	varchar(3)	ISO country code of the physical port location
`Is Active`	bit	0 = mapping deprecated (port closed or merged with another LOCODE)

TIP_* Raw Staging Tables

The TIP (Third-party Information Processing) system ingests vessel data from 45+ external flag-state registers, classification societies, and data providers. Each external source has its own dedicated staging table named TIP_<SourceName> — for example TIP_Belize, TIP_IOM, TIP_Panama.

These raw staging tables are the first landing point for incoming data. A loader stored procedure populates the appropriate TIP_* table from the raw feed file (CSV, XML, or database extract), after which the TIP/MIS pipeline (Section 08) processes the records through matching, comparison, validation, and analyst review before any changes reach the ABSD_ master tables.

External Feed
CSV / XML / DB

→

TIP_* Staging
Raw landing table

→

tblThirdPartyData
Pipeline queue

→

ABSD_ Tables
After analyst review

Common TIP_* Column Schema

All TIP Sources

All TIP raw staging tables share a common core set of vessel registry columns. Individual sources may omit columns they do not supply (NULLable) and may add source-specific columns for fields not in the common schema. Column names follow the source system's original naming conventions.

Column	Type	Description
`regnum`	varchar	Flag-state registration number (source primary key)
`regnumseq`	int/varchar	Sequence within registration number (for vessels with multiple entries)
`immarbeno`	varchar	IMO number as supplied by flag state (used for LRNO derivation)
`imo`	varchar	IMO number (synonym — some sources use this, some use `immarbeno`)
`mmsi`	varchar	Maritime Mobile Service Identity
`vesselname`	varchar	Vessel name in the registry
`owner`	varchar	Registered owner name
`builder`	varchar	Shipyard / builder name
`yearbuilt`	varchar/int	Year of construction (YYYY)
`grosston`	decimal	Gross tonnage (GT)
`netton`	decimal	Net tonnage (NT)
`deadweight`	decimal	Deadweight tonnage (DWT)
`enginetype`	varchar	Engine type / propulsion description
`enginepower`	decimal	Main engine power (kW or BHP depending on source)
`loa`	decimal	Length overall (metres)
`breadth`	decimal	Vessel breadth / beam (metres)
`draught`	decimal	Moulded draught (metres)
`callsign`	varchar	Radio callsign
`flag`	varchar	Flag state code (source-specific, may not match ABSD_ codes)
`portofregistry`	varchar	Port of registry name
`shiptype`	varchar	Ship type description in source classification (not ABSD_ STATCODE)
`class`	varchar	Classification society code
`status`	varchar	Vessel status in source registry (Active, Deleted, Suspended, etc.)
`loaddate`	datetime	Timestamp this row was loaded into the staging table

✓

LRNO derivation: The TIP pipeline derives LRNO from immarbeno or imo using spGetLRNOfromIMO(). Vessels not yet in the LR register are held in a "new vessel" queue for manual review before any ABSD_ records are created.

TIP_* Source Table Inventory (45+ sources)

Flag-State Registers Classification Societies

Each row in the table below represents one TIP_* staging table. The Type column indicates whether the source is a full flag-state registry (Flag), classification society data (Class), or another external authority (Other).

Table Name	Source	Type	Key Columns / Notes
`TIP_Belize`	Belize Maritime Authority	Flag	~80 columns; includes `immarbeno`, builder detail, engine spec
`TIP_IOM`	Isle of Man Ship Registry	Flag	IoM flag state; feeds `tblTIP_IOM_NEW` structured variant
`TIP_Panama`	Panama Maritime Authority (AMP)	Flag	Largest flag state; monthly bulk supply
`TIP_PAN3`	Panama — third-party supplementary	Flag	Supplementary AMP data; feeds `tblTIP_PAN3_NEW`
`TIP_RS`	Russian Maritime Register of Shipping	Class	RS classification and survey data
`TIP_NK`	Nippon Kaiji Kyokai (ClassNK)	Class	NK classification data
`TIP_Rina`	RINA (Registro Italiano Navale)	Class	RINA class and survey supply
`TIP_BKI`	Biro Klasifikasi Indonesia	Class	BKI classification data
`TIP_CCS`	China Classification Society	Class	Feeds `tblTIP_CCS_NEW` structured variant
`TIP_KR`	Korean Register of Shipping	Class	KR classification data
`TIP_IRS`	Indian Register of Shipping	Class	IRS survey data; also has dedicated `IRS_*` tables (see Section 14)
`TIP_IR2`	Iranian Register	Class	Feeds `tblTIP_IR2_NEW` structured variant
`TIP_TL2`	Turk Loydu	Class	Turkish classification; feeds `tblTIP_TL2_NEW`
`TIP_Malta`	Malta Flag Administration	Flag	Malta merchant fleet registry data
`TIP_Marshall`	Marshall Islands Maritime Administrator	Flag	Marshall Islands flag state
`TIP_Liberia`	Liberian International Ship & Corporate Registry (LISCR)	Flag	LISCR registry data
`TIP_Bahamas`	Bahamas Maritime Authority	Flag	BMA vessel registry
`TIP_Cyprus`	Cyprus Department of Merchant Shipping	Flag	Cyprus flag state supply
`TIP_Singapore`	Maritime and Port Authority of Singapore (MPA)	Flag	MPA vessel registry
`TIP_Hong_Kong`	Hong Kong Marine Department	Flag	HK flag registry supply
`TIP_China`	China MSA	Flag	Chinese maritime authority data
`TIP_Japan`	Japan Coast Guard / Ministry of Land	Flag	Japanese vessel registry
`TIP_Korea`	Republic of Korea flag administration	Flag	Korean flag vessel data
`TIP_Norway`	Norwegian Maritime Authority / NIS	Flag	NOR and NIS register data
`TIP_Greece`	Hellenic Coast Guard	Flag	Greek flag vessel registry
`TIP_Turkey`	Turkish Directorate General of Maritime Affairs	Flag	Turkish national registry
`TIP_Germany`	German Federal Maritime and Hydrographic Agency (BSH)	Flag	German flag state data
`TIP_India`	Directorate General of Shipping, India	Flag	Indian flag registry
`TIP_Cayman`	Cayman Islands Shipping Registry	Flag	CISRKY data supply
`TIP_Gibraltar`	Gibraltar Maritime Administration	Flag	GIB flag state supply
`TIP_St_Kitts`	St Kitts and Nevis International Ship Registry	Flag	SKN flag state
`TIP_Vanuatu`	Vanuatu Maritime Services Ltd	Flag	VUT flag state
`TIP_Antigua`	Antigua and Barbuda flag administration	Flag	ATG registry supply
`TIP_Samoa`	Samoa Shipping Corporation	Flag	WSM registry
`TIP_Tuvalu`	Tuvalu Ship Registry (TSR)	Flag	TUV flag state
`TIP_Portugal`	Instituto Portuário e dos Transportes Marítimos (IPTM)	Flag	Portuguese mainland + MAR registry
`TIP_Denmark`	Danish Maritime Authority	Flag	DIS and national register
`TIP_Netherlands`	Dutch flag administration (ILT)	Flag	NLD registry supply
`TIP_France`	Direction des Affaires Maritimes (DAM)	Flag	FRA flag state data
`TIP_Italy`	Ministero delle Infrastrutture e dei Trasporti	Flag	ITA registry supply
`TIP_Spain`	Dirección General de la Marina Mercante	Flag	ESP registry supply
`TIP_UK`	Maritime and Coastguard Agency (MCA) — Part I	Flag	GBR national registry; Part I ships >24m
`TIP_USA`	United States Coast Guard National Vessel Documentation Center	Flag	NVDC Abstract of Title data
`TIP_Canada`	Transport Canada Ship Registration	Flag	Canadian registry supply
`TIP_Australia`	Australian Maritime Safety Authority (AMSA)	Flag	AUS national registry
`TIP_Ukraine`	Ukrainian State Inspection for Safety on Maritime and River Transport	Flag	UKR registry supply
`TIP_SBR`	Shipbuilder Returns (multiple yards)	Other	Newbuild data directly from shipyards; no flag-state affiliation

tblTIP_* Structured Feed Tables

A subset of flag-state and classification society feeds are delivered in structured XML or API formats that provide richer, more granular data than the flat-file TIP_ staging tables. These are stored in tblTIP_* tables with schemas tailored to the source's data model rather than the common TIP flat schema.

The five current structured feed variants correspond to: Isle of Man (IOM), Panama third-party (PAN3), Iranian Register (IR2), China Classification Society (CCS), and Turk Loydu (TL2).

tblTIP_IOM_NEW

Isle of Man Structured Feed

Structured landing table for Isle of Man Ship Registry data delivered via the IoM's online registry system. The IoM provides richer ownership and registration detail than the flat TIP_IOM feed, including precise registration timestamps and registered port subdivision.

Column	Type	Description
`Vessel_ID`	numeric	IoM registry system vessel identifier (source PK)
`Name`	nvarchar(200)	Vessel name as registered with IoM
`Created_Date_and_Time`	datetime	Timestamp the vessel record was created in the IoM registry system
`Register_Part`	varchar(50)	Part of register (Part I, Part II, Bareboat, etc.)
`Registered_Date_and_Time`	datetime	Precise registration timestamp — more granular than ABSD_ date strings
`Port_of_Registry`	nvarchar(100)	IoM port of registry (Douglas is primary)
`Previous_Port_of_Registry`	nvarchar(100)	Prior port before any transfer of registry
`Owner`	nvarchar(500)	Full registered owner name and address as held by IoM registry
`IMO`	varchar(10)	IMO number for LRNO matching
`Gross_Tonnage`	decimal	GT per IoM registry
`Net_Tonnage`	decimal	NT per IoM registry
`LoadDate`	datetime	ETL pipeline load timestamp

tblTIP_PAN3_NEW

Panama Structured Feed

Structured Panama third-party supplementary feed. Panama is the world's largest flag state and supplies data through multiple channels; PAN3 is the API-based supplementary feed providing details that the bulk monthly flat file (TIP_Panama / Panama_NewData_MonthlyAmends) does not include — particularly ownership sub-structure and certificate data.

Column	Type	Description
`Secnaves`	varchar(20)	Panama Secnaves vessel number (primary registry identifier)
`LRNO`	char(7)	Matched Lloyd's Register number (NULL until pipeline matching runs)
`ShipName`	nvarchar(150)	Vessel name as registered with AMP
`IMO`	varchar(10)	IMO number
`CallSign`	varchar(10)	Panama-assigned callsign
`RegisteredOwner`	nvarchar(200)	Registered owner name per AMP records
`OwnerAddress`	nvarchar(500)	Owner address
`OwnerCountry`	varchar(3)	ISO country code of registered owner
`GrossTonnage`	decimal	GT per Panama registry
`NetTonnage`	decimal	NT per Panama registry
`Deadweight`	decimal	DWT
`RegistrationDate`	datetime	Date vessel first registered under Panama flag
`ExpiryDate`	datetime	Registration expiry / renewal date
`LoadDate`	datetime	ETL load timestamp

Other tblTIP_* Variants

Classification Sources

Three further structured feed variants exist for classification societies that provide API or XML-format supply:

Table	Source	Key Additional Columns
`tblTIP_IR2_NEW`	Iranian Register of Shipping (IRS/IR2)	Certificate validity dates, survey due dates, class notation, Iranian flag-state registration number
`tblTIP_CCS_NEW`	China Classification Society (CCS)	Chinese ship type code, CCS class notation, survey dates, Zhenjiang registration number, owner Chinese name
`tblTIP_TL2_NEW`	Turk Loydu (Turkish Lloyd)	Turkish flag registration number, TL class notation, hull material code, survey schedule dates

ⓘ

Schema variation: Each tblTIP_*_NEW table has a fully custom schema reflecting the source's own data model. They share the pattern of having LRNO (NULL until matched), IMO, LoadDate, and the source system's own primary key column alongside vessel identity and technical fields.

tblMIRS_* — Sanctions Matching Tables

The MIS (Maritime Intelligence System) / MIRS (Maritime Intelligence Reporting System) sanctions tables hold the output of automated name-matching algorithms that compare vessels and companies in the ABSD_ database against international sanctions lists — OFAC SDN, UN, EU, Australia DFAT, and others.

Each table follows the naming pattern tblMIRS_<SanctionsAuthority><EntityType>Sanctions_MASTERLOAD, where EntityType is either OW (company/owner match) or VS (vessel match).

Sanctions List
OFAC, EU, UN…

→

Levenshtein Match
Name similarity scoring

→

tblMIRS_*
MASTERLOAD table

→

Analyst Review
SanctionAgreed=1

Common tblMIRS_* MASTERLOAD Schema

Sanctions Name Matching

All MIRS MASTERLOAD tables share the same column structure — illustrated here using tblMIRS_AustraliaOWSanctions_MASTERLOAD. The prefix columns (e.g. AUS_) carry the sanctions list data; the suffix columns carry the matched ABSD_ entity.

Column	Type	Description
`{SRC}_REFERENCE`	varchar	Sanctions list unique identifier (e.g. `AUS_REFERENCE`, `OFAC_IDENTIFIER`, `EU_REFERENCE`)
`{SRC}_NAMEOFENTITY`	nvarchar	Name on the sanctions list (the string being matched against)
`{SRC}_TYPE`	varchar	Entity type on sanctions list: `Individual`, `Entity`, `Vessel`
`{SRC}_PROGRAM`	varchar	Sanctions program or legal basis (e.g. `UKRAINE-EO13661`, `IRAN`)
`{SRC}_ALIAS`	nvarchar	Additional name variants / aliases from the sanctions list
`{SRC}_COUNTRY`	varchar	Country of listing or nationality of entity
`OWCODE`	char(7)	ABSD_ company code matched — joins to `ABSD_OWGE.OW_CODE`
`OWNA_NAME`	nvarchar	Company name in ABSD_ at time of match
`LEVENSHTEIN`	int	Edit distance between sanctions name and ABSD_ company name; lower = closer match
`SANCTIONAGREED`	bit	1 = analyst has confirmed this is a true match; 0 = candidate pending review
`AutoID`	int IDENTITY	Row identifier
`MATCHDATE`	datetime	Timestamp the matching algorithm produced this candidate row
`REVIEWDATE`	datetime	Timestamp analyst confirmed or rejected the match
`REVIEWEDBY`	varchar(20)	Analyst Windows login who performed review

⚠

LEVENSHTEIN threshold: The matching stored procedures typically load all candidates with LEVENSHTEIN ≤ 3 (exact or near-exact matches). Candidates with scores 4–10 are loaded for completeness but are expected to be rejected by the analyst. Scores above 10 are not loaded.

tblMIRS_* Table Inventory

Sanctions Sources

77 MIRS tables cover owner-sanctions matches (OW suffix) and vessel-sanctions matches (VS suffix) across multiple international sanctions authorities. Each authority typically generates two tables — one for the full MASTERLOAD and one for the current confirmed sanctions list view.

Table Pattern	Sanctions Authority	Entity Type
`tblMIRS_AustraliaOWSanctions_MASTERLOAD`	Australia DFAT (Autonomous Sanctions)	Owner/Company
`tblMIRS_AustraliaVSSanctions_MASTERLOAD`	Australia DFAT	Vessel
`tblMIRS_EUOWSanctions_MASTERLOAD`	European Union (EU Regulation 2580/2001 and subsequent)	Owner/Company
`tblMIRS_EUVSSanctions_MASTERLOAD`	European Union	Vessel
`tblMIRS_OFACOWSanctions_MASTERLOAD`	US OFAC SDN (Specially Designated Nationals)	Owner/Company
`tblMIRS_OFACVSSanctions_MASTERLOAD`	US OFAC SDN	Vessel
`tblMIRS_UNOWSanctions_MASTERLOAD`	United Nations Security Council Consolidated List	Owner/Company
`tblMIRS_UNVSSanctions_MASTERLOAD`	United Nations	Vessel
`tblMIRS_UKOWSanctions_MASTERLOAD`	UK OFSI (Office of Financial Sanctions Implementation)	Owner/Company
`tblMIRS_UKVSSanctions_MASTERLOAD`	UK OFSI	Vessel
`tblMIRS_CanadaOWSanctions_MASTERLOAD`	Canada SEMA (Special Economic Measures Act)	Owner/Company
`tblMIRS_SwitzerlandOWSanctions_MASTERLOAD`	Switzerland SECO (State Secretariat for Economic Affairs)	Owner/Company
`tblMIRS_JapanOWSanctions_MASTERLOAD`	Japan METI sanctions	Owner/Company

ⓘ

77 tables: In addition to the MASTERLOAD tables listed above, each authority has corresponding _CURRENT or _CONFIRMED view-like tables that contain only rows where SANCTIONAGREED=1. These drive the sanctions screening flags visible in downstream products. Historical load snapshots are retained in dated tables (_YYYYMMDD suffix variants).

Sanctions Coverage Summary

Authority	Region	Primary Program Focus
OFAC SDN	United States	Iran, Russia, North Korea, Venezuela, Cuba, Syria, narcotics, terrorism
OFAC Non-SDN	United States	Sectoral sanctions (SSI), non-SDN Palestinian Legislative Council list
EU	European Union	Russia/Ukraine, Iran, Syria, Belarus, Myanmar, Libya, Venezuela
UN	United Nations	DPRK, Al-Qaeda/ISIS, Taliban, Libya, Somalia, Sudan
UK OFSI	United Kingdom	Post-Brexit autonomous regime — largely mirrors EU pre-2021 + new Russia packages
Australia DFAT	Australia	Russia/Ukraine, Iran, DPRK, Libya, Syria, Myanmar
Canada SEMA	Canada	Russia, Iran, Libya, Syria, Belarus
Switzerland SECO	Switzerland	Russia/Ukraine, Iran, Belarus, Libya
Japan METI	Japan	Russia sanctions, DPRK measures