12b Stored Procedures — Ship Lifecycle

SPs that manage the complete lifecycle of a vessel record: creation (62 table inserts in 7 transactions), deletion, status transitions, newbuild NCON date roll-forward, and casualty record management.

Lifecycle Stages

Vessel Lock Mechanism

All creation and deletion SPs use tblVesselLock to prevent concurrent edits. The table has one row: (Username, LockTime). A lock older than 5 minutes is auto-expired.

-- At start of create/delete:
DELETE tblVesselLock WHERE DATEDIFF(minute, Locktime, GetDate()) > 5
IF (SELECT COUNT(*) FROM tblVesselLock) = 0
BEGIN
    INSERT INTO tblVesselLock (Username, LockTime) VALUES (@Initials, GetDate())
    -- ... perform work ...
    DELETE tblVesselLock WHERE Username = @Initials
END
ELSE
    RAISERROR('Unable to create — deadlock would occur. Try again in a few minutes.', 16, 1)

spCreateNewShipWithStatus — Full Vessel Creation

Creates a complete, immediately valid vessel record across 62 tables in 7 sequential transactions. The comment at the top of the SP states: "Changes made to this procedure NEED to be made to spPreLoadNewShipWithStatusA and spCreateNewShipWithNCON_CONFI."

Key Parameters

Transaction Sequence (7 transactions)

• 1
  Identity tables: ABSD_SHIP_SEARCH (seed row), ABSD_OVGE, SUPPLEMENTAL_ABSD_OVGE, ABSD_OVNA, ABSD_HIFL, SUPPLEMENTAL_ABSD_HIFL, then UPDATE callsign/MMSI. OVNA must exist before HIFL — comment in source.
• 2
  Type + cargo structure: SUPPLEMENTAL_ABSD_OVTY, ABSD_OVTY, ABSD_OVCA (casualty placeholder '01'), ABSD_FTME, ABSD_FTNT, ABSD_FTQY, ABSD_FUCA1, ABSD_FUCA2, ABSD_FUCO1, ABSD_FUCO2, ABSD_FUDI1, SUPPLEMENTAL_ABSD_FUDI1.
• 3
  FU dimensions & equipment: ABSD_FUDI2/3/4, ABSD_FUDO1/2 (BOW/SID/STN rows), ABSD_FUGE + SUPPLEMENTAL_ABSD_FUGE, ABSD_FUHA1/2.
• 4
  FU continued: ABSD_FULC, ABSD_FULI1/2/3, ABSD_FURO1/2, ABSD_FUSF, ABSD_FUST1/2, ABSD_FUTO, ABSD_FUUN1 + SUPPLEMENTAL_ABSD_FUUN1, ABSD_FUUN2.
• 5
  HI history/classification: ABSD_HIBR, ABSD_HIDE, ABSD_HIDR, ABSD_HIFC1 + SUPPLEMENTAL_ABSD_HIFC1, ABSD_FOR_CLASS, ABSD_HIFC2, ABSD_HIGE, ABSD_HILC, ABSD_HILE, ABSD_HIMO (triggers HIPU/HIPS checks), ABSD_HIMT.
• 6
  Ownership: ABSD_HIOW (triggers HIPP auto-create), ABSD_HIPP (if not already created by trigger), ABSD_HIMA, ABSD_HISM, ABSD_HIOP, ABSD_HIPP_DATED, ABSD_HIGBO, ABSD_HITP, ABSD_HIPR, ABSD_HISE.
• 7
  Status, LR survey, structure: ABSD_HIST, ABSD_HITL + SUPPLEMENTAL_ABSD_HITL, ABSD_HITM, ABSD_HITS, ABSD_HITT, ABSD_LREQ1/2/3/4, ABSD_LRGE, ABSD_LRHI, ABSD_LRHN, ABSD_LRSC/SU/WD, ABSD_MAAU/BO/BU/EM1/EM2(×2)/EM3(×2)/GR/PR/SP/ST, ABSD_MATH, ABSD_STAL, ABSD_STDE + SUPPLEMENTAL, ABSD_STGE/KE/SE + SUPPLEMENTAL_ABSD_STSE + SUPPLEMENTAL_ABSD_STSE_COMPDATE, ABSD_STST, ABSD_TANKER, ABSD_TECH_MAN, ABSD_UPDATE_FP_MACHINERY, ABSD_PANDI, tblEquasisTargets, tblStars. Then: DELETE all annotations for the LRNO (clean slate), INSERT tblChanges 'New Ship' entry.

Post-creation Integrity Check (62 tables verified)

After all 7 transactions, the SP tests existence of every inserted row. If any table is missing a row, @RETERR = 1 and EXEC spDeleteShip @PASSEDLRNO is called to clean up the partial record.

spCreateNewShipWithNCON_CONFI — Confidential Newbuild

Identical to spCreateNewShipWithStatus but additionally:

• Sets ABSD_HIST status to 'B' (newbuild on order, confidential)
• Inserts an ABSD_NCON row with the confidential flag set
• Sets ABSD_OVNA name to a placeholder that triggers the confidential display path in APS output
• Must be updated in sync with spCreateNewShipWithStatus whenever the table list changes

spPreLoadNewShipWithStatusA — Pre-load Creation

Creates a minimal vessel record for pre-load use (before confirmed IMO allocation). Inserts the same 62 tables as spCreateNewShipWithStatus but with 'PRE' source markers and leaves many fields at unknowns ('UNK'). Used when a provisional LRNO is needed before full data is available. The spPreLoadLRNOs / spGetPreLoadedLRNOs SPs manage the pre-load LRNO pool.

spDeleteShip — Full Vessel Deletion

Deletes all rows for the given LRNO from all ~62 ABSD_ tables plus SUPPLEMENTAL_ and tbl* tables. Called in two contexts:

1. Integrity rollback: Called by spCreateNewShipWithStatus when the post-creation integrity check fails.
2. User-initiated deletion: Called from the ship administration UI after all history has been confirmed cleared.

spShipDeleteReset — Pre-deletion Cleanup

Resets all history table records to SEQNO='00' and clears cascade-created secondary records before spDeleteShip is called. Ensures the ship has no dangling HIPP/HITP/OWSH rows that would survive deletion due to foreign-key-like dependencies managed by triggers.

spUpdateShipStatus — Status Code Change

Updates ABSD_HIST.A02_STS for a vessel. The ABSD_HIST trigger fires on this change and drives extensive cascade logic (see Section 10 ABSD_HIST trigger for full detail). Key cascades triggered:

• Status → P/O/U/E/F: activates newbuild path (ABSD_NCON quarter markers, SUPPLEMENTAL_ABSD_NCON RELEASEDATE)
• Status → D/Q/T/W: triggers spMakeNewCas (casualty creation) and notation auto-update
• Status → S/L: triggers ABSD_OWNC rebuild and ABSD_OWST 5-part recalculation
• Any status change: updates ABSD_SHIP_SEARCH.A02_STS

spUpdateShipWithStatus

Extended version of spUpdateShipStatus that additionally accepts a new name and/or new flag state. Used when a status change coincides with a name or registration change (common at newbuild delivery: O→S transition). Updates ABSD_HIST, ABSD_OVNA, and ABSD_HIFL in one operation.

spStatusChangeToSS — Split/Salvage Status

Handles the special case of a vessel transitioning to 'SS' (Split/Salvage) status. Unlike a normal status update, this procedure:

• Creates a new ABSD_OVCA casualty record (via spMakeNewCas)
• Updates the ABSD_OVNA name field to append " (SPLIT)" notation
• Clears the ABSD_OVGE B01_CALLSIGN field (callsign released)
• Writes annotation entries for all changes

spNEWCONDateRollForward Phase 1/2/3

Three-phase process for advancing newbuild (NCON) estimated delivery dates when a vessel misses a quarter. Run on a scheduled basis:

• Phase 1: Builds temp table of LRNO + current quarter markers for all NCON vessels where COMMQTR/CQTR is in the past but status is still in (P/O/U/E/F)
• Phase 2: Advances the quarter markers forward by one quarter in SUPPLEMENTAL_ABSD_NCON
• Phase 3: Writes tblChanges annotations for each vessel updated, with old/new quarter values

Companion report SPs: spNEWCONDateRollForwardPhase1Rpt, spNEWCONDateRollForwardPhase2Rpt, spNEWCONDateRollForwardPhase3Rpt — return the affected vessel set without making changes (dry-run mode).

spNewConOrdersUpdate

Bulk SP that refreshes the newbuild orders summary data from the current ABSD_HIST + SUPPLEMENTAL_ABSD_NCON state. Called by the scheduled job that feeds the newbuild orders reporting tables. Does not take parameters — processes all active newbuild records in one batch.

spMakeNewCas — Create New Casualty Record

Creates a new ABSD_OVCA (casualty) record for a vessel by:

1. Re-sequence existing records: Cursor over existing ABSD_OVCA rows for this LRNO ordered DESC. Each row's SEQ_NO is incremented by 1 (the REPLICATE pad-to-2 pattern handles leading zero).
2. Insert new '01' record: INSERT INTO ABSD_OVCA VALUES (@PASSEDLRNO,'01','000000','RB CONV ',null, NULL). The new current record is SEQ_NO='01'; all prior records have been pushed to '02', '03', etc.

spCreateNewCasualty / spCreateNewCasualtyExistingINNO

spCreateNewCasualty — Creates the full casualty record suite for a vessel:

• Calls spMakeNewCas to create the ABSD_OVCA header
• Inserts ABSD_CAGE1 (casualty event) with a new INNO (incident number, auto-generated)
• Inserts ABSD_CADI (casualty detail), ABSD_CACO (casualty code), ABSD_CALA (casualty location)
• Writes tblChanges annotation 'New Casualty'

spCreateNewCasualtyExisitingINNO — Creates sub-records for a casualty that already has a CAGE1/INNO (used when adding a follow-on event to an existing incident).

spCasualtyOtherShips

Returns all vessels involved in the same casualty incident (same INNO across ABSD_CAGE1). Used by the casualty UI to show "other ships in this incident" for collision/grounding events with multiple vessels.

spShipExists

Quick existence check. Returns 0 if ABSD_OVGE has a row for the LRNO, 1 if not. Used as a guard before any create operation to prevent duplicate LRNO inserts. Also used by TIP/MIS processing to skip vessels that have been deleted.

spShipHeaderQuick

Returns a minimal vessel header record for UI display: LRNO, current name (ABSD_OVNA), current flag (ABSD_HIFL), current status (ABSD_HIST), gross tonnage (ABSD_HITL), year of build (ABSD_STSE), and ship type (ABSD_OVTY). Single SELECT joining 6 tables filtered by SEQNO='00'. Companion SP spFleetDetails / spFleetDetailsQuick return fleet-level summaries with similar field sets.

spShipTimeline

Returns the complete chronological timeline of key events for a vessel, unioning records from ABSD_HIST (status changes), ABSD_HIOW (ownership changes), ABSD_HIFL (flag changes), ABSD_OVNA (name changes), and ABSD_HILC (class changes). Each row includes the effective date (converted via fnLRDateToRealDate), event type, and old/new value. Used by the vessel history viewer in the UI and by the APS narrative generator for timeline-based text.