TIP/MIS External Feed Processing

Comprehensive documentation of the TIP (Third-party Information Processing) and MIS external data feed pipelines — including all configuration tables, staging tables, stored procedures, validation logic, and SUPPLEMENTAL table integration.

1. Overview

TIP/MIS is the system that ingests data supplied by external classification societies, flag state registers, PSC inspection databases, shipbuilder return feeds, and other third-party maritime data providers. Incoming data is staged, compared against the live Lloyd's Register Fleet (LRF) database, validated, and — subject to analyst review or automatic rules — applied to the master ABSD_ tables.

The system serves two distinct consumer workflows:

• Analyst review workflow — A data analyst sees flagged differences between incoming external data and the current LR record, and chooses to Keep, Learn, or Overwrite each field value.
• Automated nightly processing — Scheduled jobs automatically apply validated external data for pre-approved field/source combinations without manual intervention.

2. Architecture — Two Processing Pipelines

TIP/MIS runs two distinct technical pipelines that serve the same logical purpose but operate differently:

Both pipelines share the same downstream action vocabulary (Keep / Learn / Overwrite / Insert) and the same configuration tables for validation rules and auto-process settings.

End-to-End Flow Diagram

EXTERNAL SOURCE FILE / TABLE
         │
         ▼
┌─────────────────────────────────────────────────────────────┐
│  PIPELINE A — Classic                                       │
│  TIP_*/BV_Xml/PSC source table  ──► spTIPPSCLoad           │
│                                      spImportSBRIntoTIP     │
│                                      (source-specific SPs)  │
│         ▼ INSERT rows                                       │
│  tblThirdPartyData                                          │
│  (LRNO, TableName, FieldName, Source, VarcharValue, ...)    │
└─────────────────────────────────────────────────────────────┘
         │
         ▼ (same for both pipelines after staging)
         │
┌─────────────────────────────────────────────────────────────┐
│  PIPELINE B — FlexTemp                                      │
│  CSV/Delimited file  ──► spTIPMISProcessSource (DataLoad)   │
│                           tblTIPMISMappings (field mapping) │
│         ▼ INSERT rows                                       │
│  tblTIPMISFlexTemp                                          │
│  (KEY=LRNO, Source, SourceColumnName, ColumnValue, ...)     │
│  spTIPMISHandleOnetoManyMappings                            │
│  spTIPMISManipulationRulesEngine  (DM transforms)          │
│  spTIPMISBusinessRule_Flextemp_Update  (BR validation)      │
└─────────────────────────────────────────────────────────────┘
         │
         ▼ (both pipelines converge)
         │
┌─────────────────────────────────────────────────────────────┐
│  LRNO DERIVATION (if needed)                                │
│  spTIPDeriveLRNO  ──► tblThirdPartyDeriveLRNO config        │
└─────────────────────────────────────────────────────────────┘
         │
         ▼
┌─────────────────────────────────────────────────────────────┐
│  PREVIOUS SUPPLY COMPARISON                                 │
│  spTIPMISProcessPreviousSupply                              │
│  — Marks Processed=2 if matches prior manual supply        │
│  — Marks Processed=1 if matches prior auto supply          │
│  — Marks older records for same field Processed=1 (superseded)│
└─────────────────────────────────────────────────────────────┘
         │
         ▼
┌─────────────────────────────────────────────────────────────┐
│  LRF CURRENT COMPARISON                                     │
│  spTIPMISProcessCurrentVarcharAgainstLRF                    │
│  — Dynamic SQL JOIN against live ABSD_ table               │
│  — Levenshtein distance OR exact collation match           │
│  — Marks Processed=1 if value already matches LRF          │
└─────────────────────────────────────────────────────────────┘
         │
         ▼
┌─────────────────────────────────────────────────────────────┐
│  VALIDATION                                                 │
│  spTIPMISValidateVarchar                                    │
│  — Min/Max length checks                                   │
│  — Regex pattern match (dbo.find_regular_expression)       │
│  — Sets Validated=0 (fail) or Validated=1 (pass)           │
└─────────────────────────────────────────────────────────────┘
         │
         ├──────────────────────────────────────────────────────┐
         ▼ (Processed=0, Validated=1: needs action)             ▼ (Auto)
┌─────────────────────────────┐        ┌──────────────────────────────┐
│  ANALYST REVIEW UI          │        │  AUTO PROCESSING             │
│  spTIPLRDataByField         │        │  spTIPAutoProcessVarcharValues│
│  — Shows current LRF value  │        │  — AutoOverwrite=ZeroOrNull   │
│  — Shows change history     │        │    or All per tblThirdParty   │
│  — Decode view lookup       │        │    AutoProcess                │
│  — TIPInd flag (pending)    │        │  — Calls spTIPProcessUpdate  │
└──────────────┬──────────────┘        └──────────────────────────────┘
               │ Analyst action
               ▼
┌─────────────────────────────────────────────────────────────┐
│  ACTION PROCESSING — spTIPProcessUpdate                     │
│  Keep    ► UPDATE tblThirdPartyData SET Processed=2         │
│  Learn   ► INSERT tblThirdPartyMatchedValues                │
│  Overwrite/Insert ► Dynamic UPDATE on ABSD_ table          │
│           EFD special case for G01_NAME / new-con vessels  │
│           B04_CNTY adds B04_TOWN from vwPortDecode          │
│           Marks Processed=2 on success                     │
└─────────────────────────────────────────────────────────────┘
         │
         ▼
ABSD_ MASTER TABLES UPDATED

3. Configuration Tables

tblThirdPartySource — Source Registry

Master registry of all external data sources. Every row in tblThirdPartyData has a FK to this table via Source.

tblThirdPartyData — Classic Pipeline Staging

Central staging table for the Classic pipeline. One row per field value per supply. Supports four data types in typed columns.

Key Indexes

• IX_PreviousSupplyVarchar — COVERING index on (LRNO, TableName, FieldName, Source, DateReceived, Processed) INCLUDE (VarcharValue) — critical for spTIPMISProcessPreviousSupply cursor performance
• Source index — (Source, LRNO, Validated, Processed, TableName, FieldName) — supports per-source analyst queries

tblTIPMISFlexTemp — FlexTemp Pipeline Staging

Key-value staging table for the modern FlexTemp pipeline. Each row holds one source column value for one vessel in one supply batch.

Key Indexes

• idxDMRuleProcessing — (KEY, Source, SourceMonth, SourceColumnName) — used by manipulation rules engine
• idxKey — KEY alone, for per-vessel lookup
• idxSource, idxSourceColumnName — supporting analyst queries

tblTIPMISMappings — FlexTemp Field Mapping

Maps source column names to LR target field names for each FlexTemp source. The Mapped flag controls processing behaviour.

tblThirdPartyValidation — Validation Rules per Field

One row per (TableName, FieldName) combination defining all validation and processing rules for that field in the Classic pipeline.

tblThirdPartyAutoProcess — Auto-Update Rules

Defines which (Source, TableName, FieldName) combinations are eligible for nightly automated processing, and the overwrite mode.

tblThirdPartyMatchedValues — Learned Value Mappings

Stores "learned" mappings between source values and the LR canonical values that analysts have accepted. Used to automatically match future identical incoming values.

Business Rules & Manipulation Rules Engine Tables

tblTIPMISBrHdr / tblTIPMISBrDtl — Business Rules

Define validation rules that operate on FlexTemp data. Rules are evaluated per field per source; records failing a rule have their Validated set to 2 with RuleErrorMsg.

tblTIPMISManipulationHdr / tblTIPMISManipulationDtl — Data Manipulation Rules

Define field transformation rules applied before validation in the FlexTemp pipeline. Supports concatenation, replacement, and custom SQL transforms.

tblThirdPartyDeriveLRNO and supporting tables

Configuration for the LRNO derivation process when a source does not supply LRNO directly.

4. External Source Tables

TIP_* Staging Tables (~40 tables)

Each external classification society or flag register that feeds the Classic pipeline has a dedicated raw staging table. Data is loaded from source files into these tables, then source-specific SPs or generic loading procedures translate them into tblThirdPartyData rows.

Known TIP_* tables:

TIP_NK_Ships — Example Column Structure (Nippon Kaiji Kyokai)

Illustrates the structure of a typical classification society supply table. All data columns are varchar to accommodate raw input without transformation.

BV XML Supply Tables — Bureau Veritas

Bureau Veritas supplies data as XML files which are parsed into a family of related tables. All columns are varchar(1000) to accommodate the XML-extracted raw values.

PSC & SBR — Internal-Source Loaders

spTIPPSCLoad — Port State Control Weekly Import

Loads inspection data from ABSD_INSPECTION (the internal PSC inspection table) into tblThirdPartyData. Pulls the most recent inspection per vessel within the last 2 weeks and extracts 7 field values per vessel.

Fields loaded per vessel from PSC:

After loading, spTIPPSCLoad calls the full processing chain: spTIPProcessPreviousSupply, spTIPProcessCurrentAgainstLRF (for each field), spTIPValidate (for each field), and spTIPProcessLowerImportanceData.

spImportSBRIntoTIP — Shipbuilder Returns Import

Loads data from TIP_ShipBuilderReturns (shipyards' return submissions) into tblThirdPartyData. Extracts 21 distinct field values per vessel.

Fields loaded per vessel from SBR:

5. Classic Pipeline — Detailed Processing

5.1 Data Loading

The Classic pipeline loads data via source-specific stored procedures that SELECT from the appropriate TIP_* or BV_* staging table and INSERT normalised rows into tblThirdPartyData. The pattern used by PSC and SBR (above) is representative. Key characteristics:

• All INSERT statements explicitly name all columns — no INSERT INTO ... SELECT *
• Loaded rows always start with Processed=0, Validated=NULL (validation happens in a subsequent step)
• Duplicate guards use anti-join (LEFT JOIN WHERE IS NULL) on (LRNO + DateOriginated)
• Type casting is done at load time: numeric fields use CAST/ROUND before placing in IntValue or DecimalValue
• Name cleaning functions applied: dbo.fnTidyShipname, dbo.proper

5.2 LRNO Derivation — spTIPDeriveLRNO

Some sources (e.g. certain flag registers) do not supply the LRNO (Lloyd's Register Ship Number) directly. They supply an IMO number or other identifier that must be matched against LR data to derive the LRNO.

spTIPDeriveLRNO — Configuration-driven LRNO lookup

5.3 Previous Supply Comparison — spTIPMISProcessPreviousSupply

Compares each new (Processed=0) supply record against all previous supply records for the same field from the same source. This avoids sending duplicate data to the analyst review queue.

-- Determine the date of the current new supply batch
SET @DATERECD = (SELECT MAX(datereceived)
                 FROM tblThirdPartyData
                 WHERE source = @SOURCE AND processed = 0)

-- 4 cursors iterate over each (LRNO, TableName, FieldName) combination
-- for each datatype: varchar, int, decimal, datetime

-- Rule 1: matches previous MANUALLY processed (Processed=2) supply
--         → cross-source allowed (source not compared)
--         → set Processed=2 (treat as already manually reviewed)
UPDATE tblThirdPartyData SET Processed=2
WHERE LRNO=@LRNO AND TableName=@TABLENAME AND FieldName=@FIELDNAME
  AND Source=@SOURCE AND DateReceived=@DATERECD AND Processed=0
  AND VarcharValue = (prior manual value)

-- Rule 2: matches previous AUTO processed (Processed=1) supply
--         → same source required
--         → set Processed=1 (treat as already auto-matched)
UPDATE tblThirdPartyData SET Processed=1
WHERE ... AND b.Processed=1 AND b.Source=@SOURCE

-- 5th cursor: mark ALL older records for same LRNO+Table+Field+Source+MPK
--             as Processed=1 (superseded by newer supply)
UPDATE tblThirdPartyData SET Processed=1
WHERE LRNO=@LRNO AND ... AND DateReceived < @DATERECD AND Processed=0

5.4 LRF Comparison — spTIPMISProcessCurrentVarcharAgainstLRF

Compares staged source values against the current value in the live LRF database. If the source value already matches what LR holds, it is marked as processed (no action needed).

-- Example generated SQL structure (varchar field with SEQNO)
UPDATE tblThirdPartyData
SET Processed=1, Validated=1
WHERE EXISTS (
    SELECT 1 FROM ABSD_HIFL
    WHERE ABSD_HIFL.LRNO = tblThirdPartyData.LRNO
      AND ABSD_HIFL.SEQNO = '00'
      AND ABSD_HIFL.B04_CNTY COLLATE Latin1_General_CI_AI
          = tblThirdPartyData.VarcharValue COLLATE Latin1_General_CI_AI
)
AND tblThirdPartyData.Source = @SOURCE
AND tblThirdPartyData.TableName = @TABLENAME
AND tblThirdPartyData.FieldName = @FIELDNAME
AND tblThirdPartyData.Processed = 0

5.5 Validation — spTIPMISValidateVarchar

Validates unprocessed varchar values against the rules in tblThirdPartyValidation.

5.6 Auto Processing — spTIPAutoProcessVarcharValues

Nightly batch that automatically applies validated external data without analyst intervention for pre-approved field/source combinations.

5.7 Review UI Support — spTIPLRDataByField

Called by the analyst UI to show the current LRF value alongside the staged TIP value and change history for a given vessel+field. Returns a unified result set with both current data and annotation history.

Result set structure

Four rendering branches based on field configuration

1. DecodeView + SEQNO — JOIN to decode view for code→text, include SEQ label and EFD, UNION with tblChanges history using same decode view
2. DecodeView, no SEQNO — JOIN to decode view, no SEQ label, UNION with history
3. No DecodeView + SEQNO — Raw value with SEQ label and EFD display, UNION with history
4. No DecodeView, no SEQNO — Plain raw value, optional EFD, UNION with history if tblEnglishFieldNames has an entry for this field (indicating annotation tracking is active)

5.8 Action Processing — spTIPProcessUpdate

Executes the analyst's chosen action for a specific staged record (@DataID).

Action: Keep

UPDATE tblThirdPartyData
SET Processed = 2
WHERE DataID = @DataID
-- Marks record as manually reviewed and kept (no LRF change needed)

Action: Learn

1. Fetches current LRF value via dynamic SELECT on the target ABSD_ table for this LRNO+Field
2. Inserts mapping into tblThirdPartyMatchedValues: (TableName, FieldName, Source, VarcharValue=incoming, LRFValue=current)
3. Calls spTIPProcessedMatchedValues to immediately re-process any other staged records with the same source value using this learned mapping

Action: Overwrite / Insert

6. FlexTemp Pipeline — Detailed Processing

6.1 Data Loading & Mapping — spTIPMISProcessSource

spTIPMISProcessSource is a multi-mode stored procedure (controlled by the @status parameter) that manages both source metadata and the initial data loading into tblTIPMISFlexTemp.

Key @status modes

6.2 One-to-Many Mappings — spTIPMISHandleOnetoManyMappings

Handles the case where a single source column is mapped to multiple LR target fields (e.g. a single "owner name" column maps to both RegisteredOwner and ShipManager in different contexts).

6.3 Manipulation Rules Engine — spTIPMISManipulationRulesEngine

Executes data transformation rules defined in tblTIPMISManipulationHdr/Dtl before validation. Supports two execution modes:

Mode: ExecuteSelectQuery

-- Builds full SQL from RuleSQLScript and executes directly
EXEC (@ConcatFullScript)
-- Returns transformed rows to be reviewed or applied

Mode: ConcatExistUpdate

-- Executes dynamic SQL into a temp table
SELECT * INTO #tempConcatUpdate FROM (EXEC(@ConcatFullScript))

-- Then iterates over temp table results, updating FlexTemp ColumnValue
UPDATE tblTIPMISFlexTemp
SET ColumnValue = #tempConcatUpdate.ConcatenatedResult
WHERE AutoID = #tempConcatUpdate.AutoID
-- Applies concatenation or transform results back to the staging table

6.4 Business Rules Engine — spTIPMISBusinessRule_Flextemp_Update

Validates FlexTemp data against configurable business rules. Each rule defines what constitutes a valid value; records that fail are marked with an error message.

6.5 New Field Derivation — spTIPMISInsertNewFieldData

Derives a new computed field from existing FlexTemp data and inserts it as additional rows in tblTIPMISFlexTemp. Used to create derived fields (e.g. a combined value from two source columns) that do not exist directly in the source data.

6.6 Pivot / UnPivot for Display

spTIPMISPivotFlexTemp

Transforms FlexTemp data from its key-value row structure into a pivot (wide) format for display — one row per vessel with source columns as column headers.

• Filters: Mapped IN ('K','Y'), FieldType IS NOT NULL, Validated IN (0,2,3,4,5)
• Joins to vwTIPMISFieldGlossary for DisplayFieldName as column headers
• Uses dynamic PIVOT: STUFF((SELECT DISTINCT ',[' + sourcecolumnname + ']' ... FOR XML PATH('')),1,1,'') to build the column list
• Executes via EXECUTE (@FinalQuery)

spTIPMISUnPivotFlexTemp

Two-branch PIVOT+UNPIVOT that shows rejection detail for a specific vessel in a specific source batch.

• Branch A (no change history for vessel): includes all mapped columns with ValidationFailureReason IS NOT NULL
• Branch B (vessel has change history in tblChanges): additionally filters to only columns in tblTIPMISMappings for this source, providing a tighter result set
• Both branches apply PIVOT then UNPIVOT to restore the original column-per-field layout while retaining ValidationFailureReason and Validated code per row

6.7 Rejection Management — spTIPMISFlexTempRejection

Multi-mode SP that supports the analyst rejection review UI for FlexTemp pipeline failures (Validated IN (0,2,3,4,5) = various failure codes).

7. Field Glossary — tblTIPMISFieldGlossary / spTIPMISFieldGlossary

The Field Glossary is the central registry of LR field definitions for the FlexTemp pipeline. It maps LR internal field names to human-readable display names and provides metadata for the TIP/MIS UI.

tblTIPMISFieldGlossary — Columns

spTIPMISFieldGlossary — @status modes

8. SUPPLEMENTAL Tables

SUPPLEMENTAL tables store additional vessel data fields that do not fit in the primary ABSD_ schema, or that require a separate update cycle. Each SUPPLEMENTAL table is paired with an ABSD_ master table and has a trigger that propagates changes back to the master.

Known SUPPLEMENTAL tables

SUPPLEMENTAL_ABSD_OVGE Schema

SUPPLEMENTAL_ABSD_OVGE Trigger — SuppOVGEUpdate

FOR INSERT, UPDATE trigger on SUPPLEMENTAL_ABSD_OVGE. Fires on every change and propagates data to the master ABSD_ tables with full validation, annotation, and cross-table synchronisation.

Trigger Guard Checks

IF NOT EXISTS(SELECT * FROM TRIGGER_DISABLE)
BEGIN
  IF NOT EXISTS(SELECT * FROM TRIGGER_DISABLE_SUPPOVGE)
  BEGIN
    -- actual trigger body
  END
END

TRIGGER_DISABLE = global trigger halt (used during bulk loads). TRIGGER_DISABLE_SUPPOVGE = SUPPLEMENTAL-specific halt (used to prevent recursion when the trigger itself updates related SUPPLEMENTAL tables).

Per-row processing (cursor over inserted)

9. Processing Flags Reference

tblThirdPartyData.Processed values

tblThirdPartyData.Validated values

tblTIPMISFlexTemp.Validated values

10. Stored Procedure Reference Index

Generated from actual SQL source files — StoredProcedures/, Functions/, Tables/ — LR Maritime Database. 2026-05-02.