Section 12a

Validation Architecture Return Pattern spGetUserSettings valStandard1 valStandard2 / 2NoNull valStandard3 / 3Num valStandard4 / 4_new valStandard8 valStandard9 family valStandard10 / 10_new valStandardYN valStandardAlpha / Normal valStandardYMD valOwner* family spValidateMMSI spValidateLengths spValidateCallsign spValidateManbow_Len spValidateC02_BBOW spValidateHIMO* spValidateMAAU* spValidateD05 / D12 / D22 All Validation SPs 12b Ship Lifecycle 12c Record Lifecycle 12d Ownership & Fleet 12e External & System ← Documentation Hub

12a Stored Procedures — Validation

40 SPs that enforce data quality across all ABSD_ triggers and UI entry points. All share a common RETURN pattern and are called inline from trigger cursors.

Validation Architecture

The validation layer sits between raw user/feed input and the ABSD_ tables. There are two tiers:

Universal Return Pattern

Every validation SP follows this skeleton:

CREATE PROCEDURE valXxx
    @PASSEDDATA  varchar(N) = NULL,
    @RETMESSAGE  VARCHAR(254) = NULL OUTPUT
AS
    DECLARE @RETERR INT
    SELECT  @RETERR = 0

    IF NOT(@PASSEDDATA IS NULL)
    BEGIN
        -- check logic: set @RETERR=1 and @RETMESSAGE on failure
    END
    ELSE
        SELECT @RETERR = 0   -- NULL always passes

    RETURN @RETERR

Caller pattern inside a trigger cursor:

DECLARE @VALERR INT, @VALMSG VARCHAR(254)
EXEC @VALERR = valStandard9new @newdate, @VALMSG OUTPUT
IF @VALERR <> 0
    BEGIN
        RAISERROR('Field X: %s', 16, 1, @VALMSG)
        ROLLBACK TRANSACTION
        RETURN
    END

spGetUserSettings / spSetUserSettings

Not a validator, but called at the top of every create/update SP to stamp the annotation trail.

Parameter	Direction	Description
@Initials	OUTPUT	3-char user initials written to J06_AUTHOR on every ABSD_ row update
@Source	OUTPUT	Current data source code (e.g. 'LR', 'AIS', 'BV') stamped in tblChanges
@SourceRef	OUTPUT	Source reference string for tblChanges
@FilingRef	OUTPUT	Filing reference for tblChanges

Behaviour: Reads tblUserSettings using SYSTEM_USER (Windows login). If no row exists, inserts a placeholder row with initials 'XXX' and returns. Companion SPs: spGetUserSettingsOwners (same pattern for the ownership data-entry context), spSetUserSettings / spSetUserSettingsOwners (write updated preferences back).

valStandard1 — 1-char Classification Code

Validates single-character LR classification confidence codes.

Valid values	Meaning
C	Confirmed (sourced from LR survey/document)
D	Derived (computed from other confirmed fields)
E	Estimated
G	Given (self-reported by owner/manager)
L	Legacy (historical, source unknown)
X	Unverified third-party data

Used on every confidence-code field in the ABSD_ schema (column suffix _VER or _C in most tables).

valStandard2 / valStandard2NoNull — 2-char Type Code

Validates 2-character alpha-only codes (used for flag state ISO2, hull construction code, etc.). valStandard2 allows NULL; valStandard2NoNull additionally rejects NULL and empty string.

Check: LEN(@PASSEDDATA) = 2 AND all chars are alphabetic (A-Z, a-z). Rejects digits, spaces, special chars.

valStandard3 / valStandard3Num / valStandard3NumRev — 3-char Codes

valStandard3 — 3-char alpha code (flag state ISO3, port, etc.). LEN=3, alpha chars.

valStandard3Num — 3-char code where the value is a number plus a confidence code appended (e.g. draft stored as numeric string + 'C'). Checks numeric range and confidence code validity.

valStandard3NumRev — same as 3Num but confidence code is the first character (legacy field layout variant).

valStandard4 / valStandard4_new — YYMMDD Date

Validates LR 6-character YYMMDD date format.

Rule	Detail
Length	Must be exactly 6 characters
All numeric	ISNUMERIC check
Month	01–12 or 00 (unknown month) or 99 (sentinel)
Day	01–31 or 00 (unknown day) or 99 (sentinel)
Sentinel '000000'	PASSES — means "date unknown"
Sentinel '999999'	PASSES — means "open-ended / still current"

_new variant: Added stricter checks — rejects impossible month/day combinations (e.g. month 13, day 32). The original valStandard4 only checks format, not calendar validity.

valStandard8 — Numeric String

Validates that the value is a pure numeric string (used for tonnage, measurement, and dimension fields stored as varchar). Uses ISNUMERIC() plus checks that no letters or special chars are present. Does not validate range (range checks are field-specific and done in the trigger itself).

valStandard9 Family — YYYYMMDD Date

The most-used date validator. All variants check 8-char YYYYMMDD format with year range 1850–2050.

Variant	Key Difference
valStandard9	Strict — rejects 00 day/month, requires real calendar date
valStandard9new	Most-used. Allows 00 in day/month fields (meaning unknown). Allows '19809999' and '00000000' as pass-through sentinels. Full leap-year and 30/31-day month checks.
valStandard9_old	Legacy version without 00-month/day support — kept for backward compat
valStandard9Non99	Rejects '9999' year — used where sentinel '99' end date is not allowed
valStandard9Owner	Owner-record date variant: allows '00000000' but applies stricter future-date ceiling
valStandard9Owner_new	As Owner but with 00 day/month support
valStandard9Owner_NODATE	Accepts empty/null date field specifically for ownership records with no known date

valStandard9new calendar logic (from source):

-- Leap year: Feb <= 29
IF YEAR % 4 = 0 AND MONTH = '02' AND DAY > 29 → error
-- Non-leap: Feb <= 28
IF YEAR % 4 <> 0 AND MONTH = '02' AND DAY > 28 → error
-- 30-day months (04,06,09,11)
IF MONTH IN ('04','06','09','11') AND DAY > 30 → error

valStandard10 / valStandard10_new — Flexible Date

Accepts either 6-char YYMMDD or 8-char YYYYMMDD format. Used for fields where legacy data may be in the short format and newer data uses the long format. Internally checks the length first, then delegates to the appropriate Standard4 or Standard9 validator logic.

valStandardYN — Y/N Boolean

Accepts only 'Y' or 'N' (case-sensitive). Used on boolean flag fields throughout ABSD_ (e.g. ABSD_OVGE.B14_NAVAIDS, ABSD_HIST.A02_CONV). Returns error for any other value including 'y', 'n', '1', '0', or empty string.

valStandardAlpha / valStandardNormal — String Quality

valStandardAlpha — Checks that the string contains only alphabetic characters (A-Z, a-z) and spaces. Rejects digits and special characters. Used for name fields where numeric contamination indicates a data error.

valStandardNormal — Looser check: rejects control characters (ASCII < 32) and known problematic characters that break downstream XML/APS output. Allows alphanumeric plus common punctuation.

valStandardYMD / valStandardYMD_OLD — Year-Month-Day Components

Validates year, month, and day passed as separate components rather than a concatenated date string. Used in UI contexts where the three parts are entered in separate fields. Applies the same calendar logic as valStandard9new (leap year, 30/31-day months). The _OLD variant is kept for backward compatibility without the calendar refinements.

valOwner* Family — Ownership Role Codes

SP	Validates
valOwnerCode	7-char OWCODE format (numeric + alpha suffix). Checks against ABSD_OWGE existence.
valOwnerRelationship	Relationship code IN ('RC','RP','RM','IRP') — primary ownership categories
valOwnerRelationshipAny	Broader set including secondary relationships (agent, manager, operator, P&I)
valOwnerRelationshipFXCODE	Validates FXCODE field (fleet/group relationship code)
valOwnerRelationshipOWCODE	Validates that the given OWCODE is a live company (not deleted) in ABSD_OWGE

spValidateMMSI — Maritime Mobile Service Identity

Two-stage validation:

1. Format: Exactly 9 numeric digits; first digit in 2–7 (valid MID range); not all zeros.
2. EDM duplicate check: Queries EDM.DBO.T_REF_COUNTRY_CALL_SIGN_RANGES to verify the MMSI falls within the country's assigned EDM range. Returns warning (not error) if the MMSI is outside the known allocation for the vessel's flag state.

Called from the SuppOVGEUpdate trigger on SUPPLEMENTAL_ABSD_OVGE and from the UI MMSI entry field.

spValidateLengths — LOA / LBP / REG Cross-check

Validates the three length fields of a vessel record against each other:

• LOA (Length Overall) ≥ LBP (Length Between Perpendiculars) — always required
• LBP ≥ REG (Registered Length) — always required when both are non-zero
• All values must be > 0 when supplied (zero = not recorded, exempt from check)
• Maximum practical limit: LOA ≤ 500m for non-exceptional vessels (warning, not block)

Called from the ABSD_HIDR_Update trigger and the dimensions entry screen.

spValidateCallsign — Radio Callsign

Validates a callsign against the country's allocated ITU callsign prefix ranges. Queries Callsign_range (local table of ITU allocations). The trigger-level validation in ABSD_OVGE_Update (added March 2026) supersedes this with a live EDM lookup, but the SP remains available for batch validation jobs. Returns 0 (valid), 1 (invalid format), or 2 (valid format but outside country's allocation — treated as warning).

spValidateManbow_Len / spTIPValidateD22_MANBOW_LEN

Validates moulded breadth (D22_MANBOW_LEN) against LOA: breadth must not exceed LOA (implausible hull). The TIP variant receives both values directly (no LRNO lookup needed for external data feeds).

spValidateC02_BBOW — Breadth at Bow

Validates breadth at bow against the vessel's moulded breadth (D22_MANBOW_LEN from ABSD_MABU). Rule: C02_BBOW must be ≤ D22_MANBOW_LEN. Returns error with specific message identifying the current moulded breadth value for context.

spValidateHIMO* — Main Engine Numeric Fields

Validates ABSD_HIMO (main engine) field values. The smallint variant checks that numeric engine fields (kW, RPM, cylinders) are within physically plausible ranges. The varchar variant checks that coded fields (engine type, stroke type) are in the valid codebook values. @FieldName is passed so the error message can identify which specific engine field failed.

spValidateMAAU* — Auxiliary Engine Numeric Fields

Same pattern as spValidateHIMOsmallint but applies to ABSD_MAAU (auxiliary/emergency engine) numeric fields. Range limits differ from main engine (lower kW/RPM ceilings for generator sets).

spValidateD05_PRTYP / spValidateD12_CARGO_SEG / spValidateD22_MANBOW_LEN

Codebook validators for specific ABSD field codes:

SP	Field	Validates
spValidateD05_PRTYP	ABSD_FURO1.D05_PRTYP	Propeller type code is in ABSD_CBUB1 (FLDI='D05')
spValidateD12_CARGO_SEG	ABSD_FUCA2.D12_CARGO_SEG	Cargo segment code is valid for the vessel's type group
spValidateD22_MANBOW_LEN	ABSD_MABU.D22_MANBOW_LEN	Moulded breadth numeric + range check (0–100m)

All Validation SPs — Quick Reference