Section 12c

SEQNO Model Make*Historical Pattern Make*Historical SPs spMakeHIFLHistorical spMakeHIOWHistorical spMakeHIPPHistorical / Dated Other HI* Historical OV* Historical Sale Historical All Make*Historical spCreateNewOVSMC / DOC spCreateNewSale* spCreateNewCompany* spCreateNewAuxEngine spCreateNewFUSF2 / spAddDeleteFUSF2 spCreateNewISSC / DOC Other spCreateNew* Delete Pattern Ownership Deletes FU* Deletes All spDelete* Reference 12a Validation 12b Ship Lifecycle 12d Ownership & Fleet 12e External & System ← Documentation Hub

12c Stored Procedures — Record Lifecycle

~200 SPs that manage sub-record creation, SEQNO history management (Make*Historical), and sub-record deletion across all ABSD_ table families.

SEQNO History Model

Every ABSD_ history table uses a SEQNO (sequence number) to distinguish the current record from historical ones. The convention is:

Universal Make*Historical Pattern

All spMake*Historical SPs follow one of two patterns depending on the table's SEQNO convention:

Pattern A — Sequential (most tables)

-- 1. Re-sequence: shift all existing history rows up by 1
DECLARE cursor FOR SELECT SEQNO FROM TABLE WHERE LRNO=@L ORDER BY SEQNO DESC
WHILE @@FETCH_STATUS = 0
    UPDATE TABLE SET SEQNO = SEQNO+1 WHERE LRNO=@L AND SEQNO=@CURRSEQNO

-- 2. Copy current '00' → '01'
INSERT INTO TABLE SELECT LRNO, '01', ... FROM TABLE WHERE LRNO=@L AND SEQNO='00'

-- Caller then updates SEQNO='00' in place with new values

Pattern B — HIFL (95-slot)

-- If '95' exists → use MIN(SEQNO)-1 as new slot
-- Else → copy '00' to '95'
-- Always mirror to SUPPLEMENTAL_ABSD_HIFL

spMakeHIFLHistorical — Flag History

Archives the current flag (SEQNO='00') to SEQNO='95' (or next available below '95' if '95' exists). Always copies both ABSD_HIFL and SUPPLEMENTAL_ABSD_HIFL rows together (the callsign+MMSI history task added November 2017). The ABSD_HIFL trigger then updates the '00' row in-place with the new flag data.

spMakeHIOWHistorical — Ownership History

Archives current owner (ABSD_HIOW SEQNO='00') using Pattern A. Triggers the cascade chain: ABSD_HIOW trigger fires on the update of SEQNO='00', which triggers ABSD_HIPP auto-update, ABSD_OWSH update, and ABSD_OWST recalculation.

spMakeHIPPHistorical / spMakeHIPPDatedHistorical

ABSD_HIPP (P&I club) has two archiving SPs because of the dual-table design: ABSD_HIPP (current data, simple SEQNO model) and ABSD_HIPP_DATED (dated P&I intervals with start/end date columns). The dated variant preserves the full insurance period dates for historical reporting. spUpdateHIPPDated and spUpdateHIPPHITP are companion SPs for the dated update workflow.

Other HI* Historical SPs

All follow Pattern A unless noted. Each SP handles exactly one table:

SP	Table	Archives
spMakeHIBBCHistorical	ABSD_HIBBC	Bareboat charter history
spMakeHIBRHistorical	ABSD_HIBR	Survey/rebuild history
spMakeHIDEHistorical	ABSD_HIDE	Deletion history (soft archiving)
spMakeHIDRHistorical	ABSD_HIDR	Draft/freeboard history
spMakeHIFC2Historical	ABSD_HIFC2	Class notation history
spMakeHIGBOHistorical	ABSD_HIGBO	Gross/beneficial owner history
spMakeHIGEHistorical	ABSD_HIGE	Electrical generation history
spMakeHILCHistorical	ABSD_HILC	LR class history
spMakeHILEHistorical	ABSD_HILE	Load-line history
spMakeHIMAHistorical	ABSD_HIMA	Ship manager history
spMakeHIMOHistorical	ABSD_HIMO	Main engine history
spMakeHIOPHistorical	ABSD_HIOP	Operator history
spMakeHIPRHistorical	ABSD_HIPR	Port registry history
spMakeHISMHistorical	ABSD_HISM	Ship manager (ISM) history
spMakeHISTHistorical	ABSD_HIST	Status history
spMakeHITLHistorical	ABSD_HITL	Tonnage/LDT history
spMakeHITPHistorical	ABSD_HITP	Technical manager history
spMakeHITSHistorical	ABSD_HITS	Survey status history
spMakeHITTHistorical	ABSD_HITT	Technical type history

OV* Historical SPs

SP	Table	Notes
spMakeOVDOCArchived	ABSD_OVDOC	Archives DOC certificate to historical state. Companion: spUpdateCurrentDOC to update active record.
spMakeOVISSCArchived	ABSD_OVISSC	Archives ISM Safety Management Certificate (ISSC). Companion: spUpdateCurrentISSC.
spMakeOVNAHistorical	ABSD_OVNA	Archives current name record. The ABSD_OVNA_Update trigger fires on the '00' update that follows.
spMakeOVSMCArchived / spMakeOVSMCHistorical	ABSD_OVSMC	Two variants: Archived marks for removal from live output; Historical preserves in history chain.
spMakeOVTYHistorical	ABSD_OVTY	Archives ship type record when type changes. Triggers APS type narrative regeneration.
spUpdateOVNAHistorical	ABSD_OVNA	Updates historical OVNA records (e.g. to correct a past name entry).

spMakeSALEHistorical / spMakeSALEHistoricalWithPrice

Archives the current ABSD_Sale record (SEQNO='00') to SEQNO='01'. The WithPrice variant additionally inserts the computed scrap price into SUPPLEMENTAL_ABSD_CADI before archiving — this is part of the scrap price triangle (SUPPLEMENTAL_ABSD_HITL LDT change → SUPPLEMENTAL_ABSD_CADI.TOTAL_SCRAP_PRICE update → new ABSD_Sale record creation). Called by the SUPPLEMENTAL_ABSD_CADI trigger.

All Make*Historical — Quick Reference

spCreateNewOVSMC / spCreateNewOVDOC / spCreateNewISSC

Creates a placeholder safety management certificate record with defaults:

• OVSMC: SEQNO='00', DOC_COMPANY='Unknown', OWCODE='9991001', SOURCE='LRF'. The ABSD_OVSMC_Update trigger fires to auto-link to EDM DOC company records.
• OVDOC: Creates DOC (Document of Compliance) record. Used when a vessel acquires ISM certification for the first time or changes DOC holder.
• ISSC: Creates ISSC (International Ship Security Certificate) placeholder. Companion: spISSC_ReplaceExpiredIntrim handles interim-to-full transitions.

Related update SPs: spSMC_ReplaceExpiredIntrim, spSMCToArchive, spSMCBlank_AfterHist, spDOCToArchive, spOVDOC_ReplaceExpiredIntrim, spOVDOC_UpdateFromExpiredSMC, spISSCToArchive, spISSCBlank_AfterHist.

spCreateNewSale / spCreateNewSaleWithPrice

spCreateNewSale — Inserts a blank ABSD_Sale record (SEQNO='00', SALE_EFD=today in YYYYMMDD, SALE_PRICE_USD=null).

spCreateNewSaleWithPrice — Same but also sets SALE_PRICE_USD=@TOTAL_SCRAP_PRICE. This is the endpoint of the scrap price triangle: called by the SUPPLEMENTAL_ABSD_CADI trigger when LDT × (AMNT/100) has been computed.

spCreateNewCompany / spCreateNewCompanyEDM

spCreateNewCompany — Creates the full company record suite:

• Generates new OWCODE (7-char, auto-incremented from max existing code in the country block)
• Inserts ABSD_OWGE (owner general), ABSD_OWNA (owner name), ABSD_OWAD1/2 (addresses), ABSD_OWCN (comms)
• Inserts ABSD_OWST (owner statistics, all zeros initially)
• Writes tblChanges 'New Company' annotation

spCreateNewCompanyEDM — Variant that links the new ABSD company record to an existing EDM (Entity Data Master) entity by EDM entity ID. Used during the partial EDM migration to ensure ABSD and EDM company records stay in sync.

spCreateNewAuxEngine

Creates a new auxiliary engine entry in ABSD_MAAU. Assigns the next available SEQ_NO for the given LRNO (auxiliary engines are multi-row, keyed by LRNO+ENG_TYPE+SEQ_NO). Inserts a placeholder row with all unknown/default values. The ABSD_MAAU_Update trigger fires to validate engine type codes and update ABSD_SHIP_SEARCH.

spCreateNewFUSF2 / spAddDeleteFUSF2 / spDeleteFUSF2

ABSD_FUSF2 stores special feature codes (e.g. 'DP2' for Dynamic Positioning class 2, 'HS' for helipad). Each feature is a separate row keyed by LRNO+FEATURECODE.

• spCreateNewFUSF2 — Inserts a new feature code row if it does not already exist
• spAddDeleteFUSF2 — Toggle: @Action='A' adds, @Action='D' deletes. Used by the UI checkbox feature list
• spDeleteFUSF2 — Removes a specific feature code row for a vessel
• Companion: spAddressSearchType for feature-based ship search, spSpecialFeaturesGroup* for group management

spCreateNewLRSC / spCreateNewLRSU / spCreateNewLRWD

LR-specific certificate management:

• LRSC — LR Survey Certificate. New entry in ABSD_LRSC with sequential SEQ_NO. Companion: spDeleteLRSC, spMakeNewLRSC.
• LRSU — LR Survey record. New entry in ABSD_LRSU.
• LRWD — LR Working Document. New entry in ABSD_LRWD with YYYYMMDD EFD.

Other spCreateNew* — Quick Reference

SP Name	Creates
spCreateNewBBC	ABSD_HIBBC bareboat charter record
spCreateNewBreaker	Breaker (demolition yard) company record
spCreateNewBuilder / spCreateNewBuilderYard	Shipbuilder company + yard records
spCreateNewCACO	ABSD_CACO casualty code entry
spCreateNewCACT	ABSD_CACT casualty certificate entry
spCreateNewCADI	ABSD_CADI casualty detail record
spCreateNewCAGE2	ABSD_CAGE2 casualty event sub-record
spCreateNewCALA	ABSD_CALA casualty location record
spCreateNewCAPR	ABSD_CAPR casualty person record
spCreateNewCombinationCode	ABSD_CBEB combination type code entry
spCreateNewFlexBucketCBEntry	ABSD_CBEB flex bucket entry
spCreateNewFlexBucketEntry	Generic flex bucket table entry
spCreateNewForClass	ABSD_FOR_CLASS foreign classification entry
spCreateNewHIFC2	ABSD_HIFC2 LR class notation row
spCreateNewHullSection	ABSD_STSE hull construction section row
spCreateNewMetaData	tblMetaData record for vessel metadata
spCreateNewNEWCON	ABSD_NCON newbuild order record
spCreateNewOWAD1 / spCreateNewOWAD2	Company address records (ABSD_OWAD1/2)
spCreateNewOWCN	ABSD_OWCN company comms record
spCreateNewOWCO	ABSD_OWCO company officer record
spCreateNewOWDC	ABSD_OWDC company document/certificate
spCreateNewPersonnel	Company personnel record
spCreateNewPLAN	Vessel plan/drawing record
spCreateNewSBRPOS	SBR position record for AIS data
spCreateNewSMC	ABSD_OVSMC safety management certificate
spCreateNewSomething	Generic/stub create SP for testing
spCreateOWSH	ABSD_OWSH ownership-ship relationship record
spCreateOVSMCAnno / spCreateOVSMCEmailSentAnno	OVSMC-specific annotation entries
spCreateBlankOVISSC	ABSD_OVISSC blank ISSC record
spCreateNewOVSMC_Captiva	OVSMC from Captiva document scanning workflow

spDelete* Pattern

All sub-record delete SPs follow this pattern:

CREATE PROCEDURE spDeleteXxx
    @PASSEDLRNO VARCHAR(7),
    @PASSEDSEQNO VARCHAR(2) = NULL  -- or INNO/other key
AS
    BEGIN TRANSACTION
    DELETE FROM ABSD_Xxx
        WHERE LRNO = @PASSEDLRNO
        AND  SEQNO = @PASSEDSEQNO   -- or other key condition
    -- For SUPPLEMENTAL_ mirrored tables: also DELETE from SUPPLEMENTAL_
    -- Write tblChanges annotation
    COMMIT TRANSACTION

Historical records (non-'00' SEQNOs) can be deleted by their specific SEQNO. The current ('00') record is generally not deleted directly — instead spMake*Historical is called first and then the '00' is updated with new values.

Ownership Delete SPs

SP	Deletes	Notes
spDeleteHIOWHistorical	ABSD_HIOW historical row	Only deletes non-'00' SEQNOs
spDeleteHIMAHistorical	ABSD_HIMA historical row
spDeleteHIPPHistorical	ABSD_HIPP historical row
spDeleteHIPP_DATEDHistorical	ABSD_HIPP_DATED historical row	Also updates ABSD_OWSH timeline
spDeleteHITPHistorical	ABSD_HITP historical row
spDeleteHISMHistorical	ABSD_HISM historical row
spDeleteHIGBOHistorical	ABSD_HIGBO historical row
spDeleteOWan	ABSD_OWAN owner annotation
spDeleteOWAD1 / spDeleteOWAD2	Company address records	Also clears ABSD_OWSH address links
spDeleteOWCN	Company comms record
spDeleteOWCO	Company officer record
spDeleteOWDC / spDeleteOWDCHistory	Company document records
spDeleteOWNA	Company name record
spDeleteOWSH	ABSD_OWSH ownership-ship link	Re-runs OWST recompute
spDeleteOWXR	ABSD_OWXR cross-reference record
spDeleteCompany	All company records for an OWCODE	Full company deletion (irreversible)

FU* Delete SPs

SP	Deletes
spDeleteFUCA2	ABSD_FUCA2 cargo capacity row
spDeleteFUCO2	ABSD_FUCO2 consumable/fuel row
spDeleteFUDI2	ABSD_FUDI2 dimension row
spDeleteFUDO2	ABSD_FUDO2 door/ramp row (BOW/SID/STN)
spDeleteFUHA2 / spDeleteFUHA2C / spDeleteFUHA2W	ABSD_FUHA2 hatch rows (container/weather types)
spDeleteFULI2	ABSD_FULI2 lifting equipment row
spDeleteFURO2	ABSD_FURO2 propulsion row
spDeleteFUSF2	ABSD_FUSF2 special feature code
spDeleteFUSP	ABSD_FUSP speed/power data row
spDeleteFUST2	ABSD_FUST2 stability row
spDeleteFUUN2	ABSD_FUUN2 unit capacity row

All spDelete* — Complete Reference

Alphabetical listing of all 70+ delete SPs. All follow the standard pattern: DELETE the specific row, then write a tblChanges annotation. Many also clean up SUPPLEMENTAL_ mirror tables.