Business Logic Functions Reference
All 281 scalar and table-valued functions across 11 categories. Functions exist in both Functions/ and UserDefinedFunctions/ directories (duplicates). TIP and TIPMIS prefixed variants are system-specific copies for the TIP/MIS data pipeline.
281
Total Functions
11
Categories
200+
Uses of fnLRDateToRealDate
01 Date Conversion Functions
Handle Lloyd's Register's proprietary NVARCHAR date formats. All ABSD_ dates are stored as NVARCHAR (not SQL date types).
fnLRDateToRealDate(@LRDATE varchar 8) → DATETIME Core — used 200+ times
Core LR date converter. Converts LR 8-digit or 6-digit format to SQL DATETIME.
- Month '00' → treated as January (01); Month '99' → treated as December (12)
- Day '00' → treated as 1st of month; Day '99' → last day of month
- Validates day values for 30-day months (April, June, September, November)
- Returns NULL for year '0000' or '9999'
fnLRDateToRealDate2022(@LRDATE varchar 8) → DATE Enhanced — post-2022 standard
Enhanced version with proper leap year handling. Detects invalid days >31 and rolls to previous month. Handles February 28/29 correctly. Returns DATE type (not DATETIME). Use for post-2022 data quality standards.
| Function | Returns | Purpose |
|---|---|---|
fnLRDateToRealDate | DATETIME | Core LR date converter (most widely used) |
fnLRDateToRealDate2 | DATETIME | Identical with minor implementation diff (assigns to local var first) |
fnLRDateToRealDate2022 | DATE | Enhanced with leap year handling — post-2022 standard |
fnRealDateToLRDate | varchar(8) | Reverse: SQL DATE → LR YYYYMMDD format |
fnLRDateToSeawebDate | varchar(24) | Seaweb display format: YYYY-MM-DD, optional HTML NOBR wrap |
fnAPSLRDateToAltDate | varchar(24) | APS compatibility: replaces 00/99 month with 01 |
fnAPSAltDateQualifier | varchar(8) | Returns 'A'=Approximate or 'B'=Bad for date quality marking |
fnLRDateToBackfillDueDate | DATETIME | Backfill deadline: new vessels (P/O/U/F/E status) get year-end deadline |
fnDateIsInvalid | bit | Validates DDMMYY format (1=invalid, 0=valid) |
fnToDateFromDDMMYY | DATETIME | DD/MM/YY → DATETIME, NULL on failure (TRY/CATCH) |
02 Point-in-Time Ownership Queries
Answer "who owned/managed vessel X on date Y?" by querying history tables.
fnGetRegOwnerAtDate(@LRNO char 7, @DATEIN nvarchar 8) → varchar 7
Returns registered owner code effective at @DATEIN:
SELECT TOP 1 H01_OWNER_CODE FROM ABSD_HIOW
WHERE LRNO = @LRNO AND H01_EFD < @DATEIN
ORDER BY H01_EFD DESC
Returns owner code from most recent record before query date. Used in casualty views to identify ownership at time of incident.
fnOwnerhierarchy(@LRNO varchar 7) → TABLE (OWCODE varchar 7) Table-valued
Returns complete ownership tree. Queries ABSD_SHIP_SEARCH for OWNERCODE, GBOCODE, TECHMANCODE, SHIPMANAGERCODE, DOCCODE, OPERATORCODE, BBC_CODE. Recursively traverses ABSD_OWXR for "BO" (beneficial owner) relationships — follows chain 2 levels deep.
| Function | Source Table | Description |
|---|---|---|
fnGetRegOwnerAtDate | ABSD_HIOW | Registered owner at specified date |
fnGetOperatorAtDate | ABSD_HIOP | Commercial operator at specified date |
fnGetShipmanagerAtDate | ABSD_HISM | Ship manager at specified date |
fnGetGROUPOWNERAtDate | ABSD_HIGBO | Group/beneficial owner at specified date |
fnGetDOCAtDate | ABSD_OVDOC | DOC company at specified date (converts EffectiveDate to YYYYMMDD) |
fnGetPreviousRegOwner | ABSD_HIOW | Most recent historical owner (immediately before current) |
fnGetPreviousOperator | ABSD_HIOP | Previous operator |
fnGetPreviousShipManager | ABSD_HISM | Previous ship manager |
fnGetPreviousTechManager | ABSD_TECH_MAN | Previous technical manager |
fnGetPreviousVesselName | ABSD_OVNA | Immediately previous vessel name (SEQNO <> '00') |
fnCurrStatatDate | ABSD_HIST | Vessel status code at specific date |
fnCurrStatatDateMaster | ABSD_HIST | Advanced: groups statuses, validates no intervening changes |
fnOwnerhierarchy | Multiple | Table-valued: full ownership tree (OWNERCODE, GBOCODE, TECHMANCODE, etc.) |
03 String Normalization Functions
| Function | Description | Use Case |
|---|---|---|
fnTidyShipname | Removes punctuation (.?'), collapses spaces, strips exceptions from shipnameexceptions table (cursor-based) | Ship name pre-processing before matching |
fnStripName | Aggressive: Removes !;:[]+ {} & . , ? / _ - () '" and all spaces. Replaces "LTD"→"Limited" | Maximum-aggressiveness matching |
fnStripJunk | Moderate: Replaces special chars with spaces (not removal). Removes & entirely. | General text cleanup preserving word boundaries |
fnProperNameConversion | Intelligent title-casing: Uppercase first letter per word (boundaries: space . - : ( / & ,). Special handling for Yacht, Ice, Class, Baltic, Bermuda, Arabian. Uses FPLNAMEEXCEPTIONS table. | Publication-ready company name display |
fnProperNameConversion_Shipname | Ship-name title-casing + Roman numerals (II,III,IV,VI,VII,VIII,IX,XI,XX) + particles (of, de, del, du, d'U, L'A, L'E, d'I, 'B') | Publication-ready vessel name display |
fnReplaceSpecialCharacters | Character set normalization via tblTranslateSpecialCharacters lookup. Preserves ASCII 32-126. | Non-ASCII character substitution |
fnStripSpanishChars | Removes Spanish/French diacritics: á é í ñ ó ú ü à Ô and uppercase variants. Also « » ¿ ¡ | Latin-script name normalization |
fnStripBVXMLJunk | Bureau Veritas XML payload cleaning: ü→u, õ→a, ö→o, etc. Removes & | BV data feed import |
fnCBTDStripChars | CBTD format: uppercase, removes spaces/periods/commas/parentheses | CBTD format normalization |
fn_ComplimentaryTextTidyStrip | ltrim/rtrim, collapse multiple spaces, replace ? with - | General text cleanup |
fnEndCommaStrip | Removes trailing space or comma | Address/text cleanup |
fnSBRNormalise | Table-valued: SBR normalization. Returns 50+ columns of vessel attributes from TblSBRPages (3 ships per page) | Shipbuilder Registry data processing |
04 Fuzzy Matching Functions
LevenshteinEditDistance(@s, @t nvarchar 4000, @max int) → INT
Core Levenshtein edit distance algorithm. Returns minimum edit operations (insert/delete/substitute) to transform @s into @t. Optimized with prefix/suffix trimming. Returns NULL if distance exceeds @max. The base algorithm for all name matching in the system.
HowSimilar(@s1, @s2 varchar 4000) → INT (0-100)
Company name similarity score. Preprocessing steps:
- Removes periods from both strings
- Removes common suffixes: SA, Ltd, co, srl, llc, ltda, bv, sa, gmbh, spa, inc, oy
- Removes " co " and " ltd " substrings
- Removes all spaces and "Company" word
- Returns
(edit_distance / s1_length) * 100— lower = more similar
| Function | Description |
|---|---|
LevenshteinEditDistance | Core Levenshtein algorithm. Base for all matching. @max parameter stops early if distance exceeds threshold. |
edit_distance | Alternative core implementation using binary vector encoding. Nested loop iteration. |
HowSimilar | Preprocessed similarity score (0-100). Strips company suffixes before computing distance. |
HowSimilarClean | Direct edit_distance percentage without preprocessing. |
fnCompanyNameFuzzy | Fuzzy normalization: limited→ltd, company→co, incorporated→inc. Removes Scandinavian chars (Ø→o, Ö→o, Å→a). |
AA_fn_phon_nysiis | NYSIIS phonetic algorithm (8-char code). Calls xp_phon_nysiis extended stored procedure. |
AA_fnUSCGFuzzy | USCG-compliant normalization using tblUSCGRefFuzzyRules* tables. Rule types: Company, Address, ShipType, Country, Name, General. |
fnSBRPOSBuilderMatch | Geographic builder selection: matches shipbuilder country against POS code. Priority: builder1 → builder2 → builder3. |
fnTIPedit_distance / fnTIPMISedit_distance | TIP and TIP/MIS pipeline variants of edit distance. Identical algorithms. |
05 Address Handling Functions
fnCoFullAddress(@OwCode varchar 7) → varchar 512
Complete company address builder. Queries ABSD_OWIN and ABSD_OWAD1 for multi-source address data. Format: Care-of company → Building → PO Box → Street → Postcode → Town → Country. Decodes town/country from ABSD_CBTO* and EDM.dbo.T_REF_COUNTRY. Returns pipe-delimited result (|| separator).
| Function | Description |
|---|---|
fnCoFullAddress | Complete address builder. Queries ABSD_OWIN + ABSD_OWAD1. || separated. |
fnFullBestAddress | Single "best" address from EDM.DBO.T_ENT_OWIN with LEFT JOINs to ABSD_CBTO1/2 and T_REF_COUNTRY. Prefers EDM over legacy. |
fnCoMatchAddress | Address with CR/LF line breaks. Building, POB, Street, Postcode, Town, Country. Removes trailing commas. |
fnCoMatchLineAddress | Word-wrapped address with max-length enforcement. Calls all abbreviation functions. @Lineout to return specific line. | delimiter. |
fnCoMatchAbbreviateAddressAdd | Street→St, Road→Rd, Avenue→Av, Boulevard→Bvd, Place→Pl, Building→Bldg, Centre→Ctr, House→Ho, Square→Sq, Tower→Twr |
fnCoMatchAbbreviateAddressBuilding | Floor→Fl, Room→Rm, Centre→Ctr, House→Ho, Square→Sq, Tower→Twr; Spanish Apartado→apdo |
fnCoMatchAbbreviateAddressCountry | United States of America→USA; Democratic Republic of Congo→Dem Republic of Congo; special territories |
fnCoMatchAbbreviateAddressTown | Province→Prov, Prefecture→Pref |
fnCoMatchAbbreviateAddressPOB | Boite Postale→BP, Caixa Postal→CP, Casella Postale→CP, P O Box→PO Box, Apartado→Apdo |
fnAddressComm1 | Tel, Fax, Telex from vwRSGAddresscomm1. Format: ", Tel: XXX, Fax: XXX, Telex: XXX" |
fnAddressComm2 | Email (type='E') and Web (type='F') from vwRSGAddressComm2. |
06 Narrative Generation Functions
Build human-readable technical specification text from structured vessel data. Output is formatted prose suitable for publication.
fnAPSNarrativePrimeMoverGrouped(@LRNO, @High, @PrimeMoverGrouped) → varchar 7000 Most complex
Generates engine specification narrative. Engine types: 01=Diesel, 02=Steam turbine, 03=Turboelectric, 04=Steam reciprocating. Outputs: Design, Builder, Model (appends 'Tier 3' suffix where applicable), Cylinder arrangement, Bore × Stroke, MCR in kW and hp, RPM. Special handling for re-engined vessels and new engines with dates. Table-valued input allows pre-grouped engine data.
| Function | Content | Source Tables |
|---|---|---|
fnAPSNarrativePrimeMoverGrouped | Engine specification narrative: type, builder, model, Tier 3, bore×stroke, MCR, RPM | ABSD_MAEM1/2/3, ABSD_HIGE |
fnAPSNarrativeCapacities | Capacity summary: passengers, crew, grain, bale, insulated, liquid, liquid gas, oil, ore, hopper, asphalt, ballast, TEU | ABSD_FUGE, ABSD_TANKER, ABSD_FURO1, ABSD_FUUN1/2 |
fnAPSNarrativeTank | Tank inventory: type, position, material, cargo segregations, heating coil material | vwd09_* tables, ABSD_FUST1, ABSD_TANKER |
fnAPSNarrativeTankCoatings | Tank coating specifications from ABSD_TANKER coating flags | ABSD_TANKER |
fnAPSNarrativeAuxEnginesGrouped | Auxiliary engine specifications | ABSD_HIGE |
fnAPSNarrativeBoilers | Boiler specifications | ABSD_MABO |
fnAPSNarrativeBunkers | Bunker fuel capacity narrative | ABSD_FUUN1/2 |
fnAPSNarrativeConstruction | Hull and construction material/method | ABSD_STSE, design tables |
fnAPSNarrativeGear | Cargo gear (cranes, derricks) narrative | ABSD_FULI1/2/3 |
fnAPSNarrativeHatch | Hatch configuration specifications | ABSD_FUHA1/2 |
fnAPSNarrativeRamp | Ro-Ro ramp specifications | ABSD_FURO1/2 |
fnNarrativeTanker | Legacy tanker specification narrative | ABSD_TANKER |
fnNarrativeSpecialist | Specialist vessel characteristics (DP, helipad, crane capacity) | Multiple FUSF tables |
07 Classification Functions
| Function | Description |
|---|---|
fnCLASSLIST | Comma-separated list of current classifications with status tags: (disc)=discontinued, (susp)=suspended, (cont)=continuous, (toc)=transfer of class. Queries ABSD_FOR_CLASS and ABSD_HILC. |
fnCLASSLISTCURRENT | Only verified current classifications |
fnCLASSIDLIST | Classification identifier codes (not decoded names) |
fnGetIMOClass | IMO-assigned class code |
fnClass | Primary/lead classification society code |
fnGetClass1 / fnGetClass2 | First and second classification codes respectively |
fnCBSTShiptype | CBS ship type lookup from CBST |
fnSBRRegBookShiptype | Shipbuilder Registry book ship type |
08 Geographic / Coordinate Functions
fnConvertDegreesMinsToDecimal(@direction, @degrees, @minutes, @seconds) → decimal(18,15)
DMS to decimal degrees conversion. Formula: degrees + (minutes×60 + seconds) / 3600. Returns positive for N/E, negative for S/W. Returns 181 or 91 for invalid input (sentinel values). Used in port coordinate processing within CBPPROOT trigger.
fnMarsdenGrid(@LATDEG, @LATIND, @LONGDEG, @LONGIND) → VARCHAR 3
Marine meteorological Marsden grid calculation. Valid ranges: 70°N max, 60°S max, 179° longitude max. Quadrant base numbers: NW=1, NE=36, SW=300, SE=335. Divides by 10 and rounds down for 10°×10° grid cells. Formula: WSNUM + (LATDEG × 36) ± LONGDEG. Returns zero-padded 3-digit grid code.
| Function | Description |
|---|---|
fnConvertDegreesMinsToDecimal | DMS → decimal degrees. Sentinel: 181 (long invalid) or 91 (lat invalid). |
fnMarsdenGrid | Marsden grid code from lat/long. Used in port record CBPD.MARSDEN field. |
fnDistanceCalc | Great-circle distance between two coordinate pairs (Haversine formula) |
fnSISZone | Calculates Statistical Inventory of the Sea (SIS) zone from port coordinates |
09 Validation Functions (Callsign, MMSI, etc.)
| Function | Returns | Description |
|---|---|---|
fnCallsignCheck(@Callsign, @Flag) | bit (1=valid, 0=invalid) | Validates callsign against flag country call sign prefix ranges in EDM.DBO.T_REF_COUNTRY_CALL_SIGN_RANGES. For SZI/FIJ/EGY/SUD flags: checks first 3 chars; all others: first 2 chars. |
fnMMSICheck(@MMSI) | bit | Validates MMSI format (9 digits, first 3 = MID country code). Checks MID against valid Maritime Identification Digits table. |
valStandard9Non99 | bit | Date format validation: not '99' month sentinel, not '00' year. Used in CAGE1 trigger for STD/ENDD validation. |
fnIsValidLRNO(@LRNO) | bit | Validates 7-character LR number format: digits only, not null. |
fnOWCODEValid(@OWCODE) | bit | Validates 7-character owner code format. |
10 Company / Owner Functions
| Function | Description |
|---|---|
fnGetCompanyShortName(@OWCODE) | Returns SHNAME from ABSD_OWGE / EDM for the given owner code |
fnGetCompanyCountry(@OWCODE) | Returns NATY1 (primary nationality) for the company |
fnGetCompanyISOCountry(@OWCODE) | Returns ISO2 country code via CBCY join |
fnOWGEGroupType(@OWCODE) | Returns GROUP_OWGE value (A=Group, M=Management, B=Both) |
fnGetOwnerShipCount(@OWCODE) | Current fleet count from ABSD_OWST.IRP_OWNED |
fnGetBeneficialOwner(@OWCODE) | Traverses ABSD_OWXR for BO relationship — same logic as HIOW trigger dual certification path |
11 Annotation / Audit Functions
| Function | Description |
|---|---|
fnGetEnglishFieldName(@TableName, @ColName) | Translates coded column name (e.g., "B01_CALLSIGN") to human-readable label (e.g., "Call Sign"). Used by spAnnotateChange when writing tblChanges. |
fnAnnotationSource(@SRCE) | Decodes 9-char source system code to readable description |
fnAnnotationLastChange(@LRNO) | Returns formatted last-change timestamp string from J06_LNCHDATE + J06_LNCHTIME |
fnGetFieldEnglishNameForExport(@table, @col) | Export-version of English field name lookup with additional metadata |
★ Key Functions Quick Reference
| Function | Category | Returns | Notes |
|---|---|---|---|
fnLRDateToRealDate | Date | DATETIME | Used 200+ times. Core date converter. |
fnLRDateToRealDate2022 | Date | DATE | Enhanced with leap years. New standard. |
fnGetRegOwnerAtDate | Ownership | varchar(7) | Registered owner on specific date. |
fnOwnerhierarchy | Ownership | TABLE | Complete ownership tree per vessel. |
LevenshteinEditDistance | Fuzzy | INT | Core edit distance algorithm. |
HowSimilar | Fuzzy | INT (0-100) | Company name similarity with preprocessing. |
fnCoFullAddress | Address | varchar(512) | Full address with EDM/ABSD source. |
fnAPSNarrativePrimeMoverGrouped | Narrative | varchar(7000) | Engine specification narrative. |
fnCallsignCheck | Validation | bit | Called by OVGE trigger for callsign validation. |
fnMarsdenGrid | Geographic | VARCHAR(3) | Called by CBPPROOT trigger. |
fnConvertDegreesMinsToDecimal | Geographic | decimal(18,15) | DMS → decimal. Called by CBPPROOT trigger. |
fnProperNameConversion | String | varchar(1000) | Title-case for company name publication. |
fnCLASSLIST | Classification | varchar(70) | Current classifications with status tags. |
fnGetEnglishFieldName | Annotation | varchar | Column code → readable label for audit log. |