User-Defined Functions
All 281 scalar and table-valued functions — signatures, purpose, and implementation notes from actual SQL source
281
Total Functions
17
APS Narrative
17
Fairplay Format
13
Date Conversion
14
Company/Owner At Date
Date Conversion Functions
The LR database uses a compact date format throughout: 6-char YYMMDD (e.g. 260503 = 3 May 2026) or 8-char YYYYMMDD (e.g. 20260503). The sentinel value 000000 means "unknown/not set"; 999912 means "no end date / still current". These functions convert between LR dates and SQL DATETIME.
| Function | Signature | Purpose / Notes |
|---|---|---|
fnLRDateToRealDate |
(@LRDATE varchar(8)) → DATETIME |
Converts 6-char YYMMDD or 8-char YYYYMMDD to DATETIME. Returns NULL for '000000', '00000000', '19809999'. Handles MONTH>12 by using December of prior year (catches legacy bad data). Clamps day-of-month overflows (31 Apr → 30 Apr etc). Most widely used date function. |
fnRealDateToLRDate |
(@REALDATE DATETIME) → varchar(8) |
DATETIME → YYYYMMDD string (zero-padded). NULL → NULL. Inverse of fnLRDateToRealDate. |
fnLRDateToRealDate2 |
(@LRDATE varchar(8)) → DATETIME |
Variant of fnLRDateToRealDate with alternative edge-case handling. |
fnLRDateToRealDate2022 |
(@LRDATE varchar(8)) → DATETIME |
2022-vintage update of the core date converter; handles century boundary issues for dates near 2000. |
fnLRDateToSeawebDate |
(@LRDATE varchar(8), @includeDay int) → varchar(20) |
Formats LR date as human-readable string (e.g. "May 2026" or "03 May 2026"). Used in narrative output — @includeDay=1 includes the day. |
fnRealDateToSeawebDate |
(@REALDATE DATETIME) → varchar(20) |
DATETIME → Seaweb-format readable date string. |
fnLRDateToBackfillDueDate |
(@LRDATE varchar(6)) → DATETIME |
Converts LR date to a backfill due date (adds a buffer period for survey scheduling). |
fnToDateFromDDMMYY |
(@input varchar(8)) → DATETIME |
Parses the dd:mm:yy format used in SUPPLEMENTAL_ABSD_NCON.RELEASEDATE (set by ABSD_HIST trigger on delivery). |
fnAPSLRDateToAltDate |
(@LRDATE varchar(8)) → varchar(30) |
APS output format date — e.g. "2026-05-03" for publication output. |
fnAPSAltDateQualifier |
(@LRDATE varchar(8)) → varchar(5) |
Returns date qualifier string (e.g. "Est.", "Approx.") based on APS date precision rules. |
fnDateIsInvalid |
(@dateStr varchar(8)) → bit |
Returns 1 if the date string is not a valid LR date; 0 if valid. Used for data quality checks. |
fnTIPMISLRDateToRealDate |
(@LRDATE varchar(8)) → DATETIME |
TIP/MIS-specific variant of fnLRDateToRealDate with TIP-specific edge case handling. |
fnTIPMISRealDateToLRDate |
(@REALDATE DATETIME) → varchar(8) |
TIP/MIS reverse date conversion. |
fnTIPMISRealDateToSeawebDate |
(@REALDATE DATETIME) → varchar(20) |
TIP/MIS Seaweb date string. |
Text & String Utility Functions
| Function | Signature | Purpose |
|---|---|---|
APSProper |
(@tcString varchar(255)) → varchar(255) |
Title-case converter. Capitalises the first letter after space, '.', '-', '"', '(', '/', '&', ','. Checks FPLNAMEEXCEPTIONS table and uppercases words that should stay upper (e.g. "LLC", "SA"). Returns NULL for empty string. |
fn_MixCase |
(@s varchar(2000)) → varchar(2000) |
Alternative proper-case function. Used in contexts where APSProper is not called. |
fnTidyShipname |
(@tcString varchar(255)) → varchar(255) |
Strips ship name prefixes using the shipnameexceptions table (e.g. removes "MV ", "MT ", "SS "). Also replaces '.', '?', "'" with space and trims double spaces. Used in TIP/PSC data loading to normalise incoming vessel names. |
fnTIPMISTidyShipname |
(@tcString varchar(255)) → varchar(255) |
TIP/MIS variant of fnTidyShipname. |
fnReplaceSpecialCharacters |
(@input varchar(4000)) → varchar(4000) |
Replaces accented/special characters with ASCII equivalents (é→e, ü→u etc). Used in matching and export pipelines. |
fnTIPMISReplaceSpecialCharacters |
(@input varchar(4000)) → varchar(4000) |
TIP/MIS variant with extended character replacement table. |
fnFindSpecialCharacters |
(@input varchar(4000)) → bit |
Returns 1 if string contains non-ASCII characters. |
fnTIPMISFindSpecialCharacters |
(@input varchar(4000)) → bit |
TIP/MIS variant of the above. |
fnCBTDStripChars |
(@input varchar(500)) → varchar(500) |
Strips specific characters from codebook title/description fields for clean display. |
fnEndCommaStrip |
(@input varchar(4000)) → varchar(4000) |
Removes trailing comma (and any trailing space) from a string. Used in narrative concatenation. |
fnFormat78Char |
(@input varchar(4000)) → varchar(4000) |
Word-wraps a string to 78-character lines. Used for telex/legacy text format output. |
fnFormatAnnoNarrative |
(@lrno varchar(7), ...) → varchar(max) |
Formats annotation narrative combining field change notes for annotation display. |
fn_StringBreakonComma |
(@input varchar(8000)) → TABLE |
Table-valued function — splits a comma-delimited string into rows. |
fn_StringBreakonSpace |
(@input varchar(8000)) → TABLE |
Table-valued function — splits a space-delimited string into rows. |
fn_BreakStringonSpace |
(@input varchar(8000), @pos int) → varchar(200) |
Scalar version — returns the Nth word when splitting on space. |
fn_StringDelimCt |
(@str varchar(8000), @delim varchar(10)) → int |
Counts occurrences of a delimiter in a string. |
fn_StringLineCompress |
(@input varchar(8000)) → varchar(8000) |
Removes blank lines and compresses multiple whitespace runs. |
fnTextSplit |
(@input varchar(max), @delim varchar(10)) → TABLE |
General-purpose table-valued string splitter. |
fnExtractNumbersFromString |
(@input varchar(500)) → varchar(500) |
Returns only the numeric characters from a string. |
fnTIPMISNonNumericPart |
(@input varchar(100)) → varchar(100) |
Returns the non-numeric portion of a mixed alphanumeric string (e.g. confidence codes). |
fnTIPMISNumericPart |
(@input varchar(100)) → varchar(100) |
Returns the numeric portion of a mixed alphanumeric string. |
fnRomanvalextract |
(@input varchar(20)) → int |
Extracts a Roman numeral from a string and returns its integer value. |
fn_ComplimentaryTextTidyStrip |
(@input varchar(4000)) → varchar(4000) |
Strips complimentary/courtesy text prefixes from company names for clean matching. |
Name Matching & Phonetic Functions
| Function | Signature | Purpose |
|---|---|---|
edit_distance |
(@s1 nvarchar(3999), @s2 nvarchar(3999)) → int |
Levenshtein edit distance — full Wagner-Fischer dynamic programming implementation stored in binary VARBINARY columns for efficiency. Returns minimum edit operations (insert, delete, substitute) to transform s1 into s2. Used for fuzzy vessel name and company name matching. |
fnTIPedit_distance |
(@s1 nvarchar(3999), @s2 nvarchar(3999)) → int |
TIP-specific edit distance variant (same algorithm, different collation context). |
fnTIPMISedit_distance |
(@s1 nvarchar(3999), @s2 nvarchar(3999)) → int |
TIP/MIS edit distance variant. |
AA_fn_phon_nysiis |
(@string varchar(8000)) → varchar(8) |
NYSIIS phonetic encoding — wraps extended stored procedure xp_phon_nysiis. Returns an 8-char phonetic code used for vessel name deduplication and fuzzy search. NYSIIS handles name variants better than Soundex for maritime names (handles 'Y', 'H' consonants). |
AA_fnUSCGFuzzy |
(@input varchar(200)) → varchar(200) |
USCG fuzzy name normalisation — applies USCG-specific name standardisation rules for matching against US Coast Guard data feeds. |
CleanCompanyNameForMatch |
(@s1 varchar(4000)) → varchar(4000) |
Strips legal entity suffixes before matching: removes SA, SL, Ltd, pte, co, srl, llc, ltda, bv, gmbh, spa, wll, a/s, inc, oy, as (case-insensitive, from both middle and end of string). Used in duplicate company detection. |
fnCompanyNameFuzzy |
(@name varchar(200)) → varchar(200) |
Composite normalisation for company matching: strips legal suffixes, applies special character replacement, converts to lower case. |
fnCoMatchAddress |
(@owcode varchar(7)) → varchar(500) |
Returns a normalised single-line address string for matching — strips punctuation and standardises format. |
fnCoMatchLineAddress |
(@owcode varchar(7)) → varchar(500) |
Multi-line variant of fnCoMatchAddress. |
fnCoMatchViewableAddress |
(@owcode varchar(7)) → varchar(500) |
Human-viewable formatted address for match review UI. |
fnCoMatchAbbreviateAddressAdd |
(@input varchar(200)) → varchar(200) |
Abbreviates address line (Street→St, Avenue→Ave etc). |
fnCoMatchAbbreviateAddressBuilding |
(@input varchar(200)) → varchar(200) |
Abbreviates building descriptor fields. |
fnCoMatchAbbreviateAddressCountry |
(@input varchar(200)) → varchar(200) |
Normalises country name to standard form for matching. |
fnCoMatchAbbreviateAddressPOB |
(@input varchar(200)) → varchar(200) |
Normalises PO Box variations ("Post Office Box", "P.O.B." → "POB"). |
fnCoMatchAbbreviateAddressTown |
(@input varchar(200)) → varchar(200) |
Normalises town/city name for matching. |
APS Narrative Generator Functions
The fnAPSNarrative* family produces human-readable technical description text for vessel records, used in the APS (Automated Publication System) pipeline that generates Register Books and online vessel pages. All return varchar(7000) and take @LRNO varchar(7), @High int as minimum parameters — @High=1 wraps highlights in HTML span tags.
TVP-based functions: The most complex APS functions (PrimeMoverGrouped, AuxEnginesGrouped) accept pre-grouped engine data as Table-Valued Parameters (user-defined type
PrimeMoverGrouped) to avoid re-querying the database per call. The calling SP groups the data once and passes it in.| Function | Returns | Builds narrative from |
|---|---|---|
fnAPSNarrativePrimeMoverGrouped | varchar(7000) | Main propulsion engines — grouped by type, designer, builder, model, stroke, cycle, power. Includes status, bore, stroke, MCR, BHP. TVP parameter: PrimeMoverGrouped user type. |
fnAPSNarrativePrimeMoverOverview | varchar(7000) | Summary overview line for prime mover (engine count, type, total power). |
fnAPSNarrativeAuxEnginesGrouped | varchar(7000) | Auxiliary diesel engines grouped narrative — similar structure to PrimeMoverGrouped. |
fnAPSNarrativeAuxGens | varchar(7000) | Alternators/generators narrative from ABSD_HIGE. |
fnAPSNarrativeBoilers | varchar(7000) | Boiler specifications from boiler tables. |
fnAPSNarrativeBunkers | varchar(7000) | Bunker/fuel capacity narrative from ABSD_FUCA. |
fnAPSNarrativeCapacities | varchar(7000) | Cargo capacity narrative (grain, bale, container, lane metres, etc.). |
fnAPSNarrativeClassDetail2 | varchar(7000) | Detailed class notation narrative. |
fnAPSNarrativeConstruction | varchar(7000) | Hull construction details (material, framing, decks, hull shape). |
fnAPSNarrativeDoor | varchar(7000) | Cargo door narrative from ABSD_FUDO. |
fnAPSNarrativeGear | varchar(7000) | Cargo gear (cranes, derricks) narrative from ABSD_FUGE. |
fnAPSNarrativeHatch | varchar(7000) | Hatch dimensions and count from ABSD_FUHA. |
fnAPSNarrativeHold | varchar(7000) | Hold dimensions and capacities from ABSD_FULI. |
fnAPSNarrativeIceCapability | varchar(7000) | Ice class and capability narrative. |
fnAPSNarrativeLanes | varchar(7000) | Lane metres and lane capacity (RoRo) narrative. |
fnAPSNarrativeRamp | varchar(7000) | Ramp specifications from ABSD_FURO. |
fnAPSNarrativeShipTypeGroup | varchar(7000) | Ship type and specialist type narrative. Multiple dated versions (29052014, 310514, NEW) exist as the schema evolved. |
fnAPSNarrativeSpecialistTanker | varchar(7000) | Specialist tanker features (heating coils, coating, IMO class). |
fnAPSNarrativeSpecialTanks | varchar(7000) | Special tank narrative (wing, deep, slop). |
fnAPSNarrativeTank | varchar(7000) | Main cargo tank narrative from ABSD_FUST. |
fnAPSNarrativeTankCoatings | varchar(7000) | Tank coating types and areas. |
fnAPSNarrativeThrusters | varchar(7000) | Thruster narrative from ABSD_HITP. |
fnAPSNarrativeAlterationsConversions | varchar(7000) | Ship alteration and conversion history narrative. |
TIP Narrative Functions
| Function | Purpose |
|---|---|
fnTIPNarrativeBunkers | TIP-format bunker text (simplified version of APS bunker narrative). |
fnTIPNarrativePrimeMoverGrouped | TIP-format propulsion narrative. |
fnTIPNarrativePrimeMoverOverview | TIP-format propulsion overview. |
fnTIPNarrativeThrusters | TIP-format thruster text. |
Fairplay Format Functions (fnFP*)
The fnFP* family formats data for the Fairplay data exchange pipeline. Fairplay was the predecessor data product; these functions remain for backward compatibility with downstream consumers.
| Function | Purpose |
|---|---|
fnFPBuilderEquivBuilderID | Resolves the Fairplay equivalent builder ID from LR builder code. |
fnFPExtractBuilderEquiv | Extracts Fairplay builder equivalence mapping. |
fnFPFormatCargoGearDescription | Fairplay-format cargo gear text. |
fnFPFormatCargoPumpDescription | Fairplay-format cargo pump text. |
fnFPFormatClassedBy | Formats the "Classed by" string for Fairplay output. |
fnFPFormatConstruction | Fairplay construction details text. |
fnFPFormatContainerArrangement | Container stowage arrangement description. |
fnFPFormatConversion | Ship conversion history text for Fairplay. |
fnFPFormatDemo_price | Formats demolition (scrapping) price for Fairplay output. |
fnFPFormatEngDetails | Engine detail text in Fairplay format. |
fnFPFormatHatchSizes | Hatch size dimensions text. |
fnFPFormatIMOChemClass | IMO chemical class code text. |
fnFPFormatOrigName | Formats original vessel name with date. |
fnFPFormatScrap_loss_date | Formats scrapping/total-loss date for Fairplay. |
fnFPFormatTankerCoatsText | Tanker coating types text. |
fnFPFormatThrusters | Thruster description text. |
fnFPNBPrice | Formats newbuilding price for Fairplay output using SUPPLEMENTAL_ABSD_NCON data. |
Ownership & Status at Date Functions
These functions return the state of ownership or status fields as of a specified date, by querying history tables for the record effective at that point in time.
| Function | Signature | Purpose |
|---|---|---|
fnGetRegOwnerAtDate | (@lrno varchar(7), @date datetime) → varchar(7) | Returns the OWCODE of the registered owner as at a given date (queries ABSD_HIOW history). |
fnGetShipmanagerAtDate | (@lrno varchar(7), @date datetime) → varchar(7) | Returns OWCODE of the ship manager as at a given date (queries ABSD_HIMA history). |
fnGetOperatorAtDate | (@lrno varchar(7), @date datetime) → varchar(7) | Returns the operator OWCODE as at a given date. |
fnGetGROUPOWNERAtDate | (@lrno varchar(7), @date datetime) → varchar(7) | Returns the group owner OWCODE as at a given date. |
fnGetDOCAtDate | (@lrno varchar(7), @date datetime) → varchar(7) | Returns the DOC (Document of Compliance) company as at a given date. |
fnGetPreviousOperator | (@lrno varchar(7)) → varchar(7) | Returns the immediately previous operator OWCODE. |
fnGetPreviousRegOwner | (@lrno varchar(7)) → varchar(7) | Returns the previous registered owner OWCODE. |
fnGetPreviousShipManager | (@lrno varchar(7)) → varchar(7) | Returns the previous ship manager OWCODE. |
fnGetPreviousTechManager | (@lrno varchar(7)) → varchar(7) | Returns the previous technical manager OWCODE. |
fnGetPreviousVesselName | (@lrno varchar(7)) → varchar(40) | Returns the immediately previous vessel name from ABSD_OVNA. |
fnCurrStatatDate | (@lrno varchar(7), @date datetime) → varchar(2) | Returns the vessel status code (A02_STS) as at a given date. |
fnCurrStatatDate1 | (@lrno varchar(7), @date datetime) → varchar(2) | Variant with alternative date boundary logic. |
fnCurrStatatDateMaster | (@lrno varchar(7), @date datetime) → varchar(2) | Master variant — used as the authoritative "status at date" function. |
fnTechManPrevSeqno | (@lrno varchar(7), @seqno varchar(2)) → varchar(2) | Returns the previous sequence number for a technical manager record (for SEQNO navigation). |
Codebook & Classification Lookup Functions
| Function | Signature | Purpose |
|---|---|---|
fnCBSBShipbuilder | (@code varchar(7)) → varchar(100) | Looks up shipbuilder full name from ABSD_CBSB code. |
fnCBSTShiptype | (@code varchar(7)) → varchar(100) | Looks up ship type description from ABSD_CBUB1. |
fnBuilderName | (@lrno varchar(7)) → varchar(100) | Returns the builder name for a vessel from ABSD_CBCOROOT via the vessel's builder code. |
fnCFD04ShipbuilderDetails | (@lrno varchar(7)) → varchar(500) | Returns formatted CFD04 builder details string. |
fnClass | (@LRNO varchar(7), @High int) → varchar(7000) | Returns current class status string combining LR class (from ABSD_HILC) and foreign class entries (from ABSD_FOR_CLASS + ABSD_CBUB1 FLDI='25'). Handles LRCL_IND: Y=normal, C=Contemplated, W=Disclassed, S=Suspended, T=Transferred. |
fnCLASSLIST | (@LRNO varchar(7)) → varchar(7000) | Comma-separated list of all current and non-disclassed class societies for a vessel. |
fnCLASSIDLIST | (@LRNO varchar(7)) → varchar(7000) | Comma-separated list of class society IDs. |
fnCLASSLISTCasualty | (@LRNO varchar(7)) → varchar(7000) | Class list at time of casualty. |
fnCLASSLISTCURRENT | (@LRNO varchar(7)) → varchar(7000) | Current class list only (SEQNO='00' records). |
fnCLASSLISTFull | (@LRNO varchar(7)) → varchar(7000) | Full historical class list. |
fnCLASSLISTHistoricalCasualty | (@LRNO varchar(7)) → varchar(7000) | Historical class list for casualty reporting. |
fnCLASSLISTSimple | (@LRNO varchar(7)) → varchar(7000) | Simple abbreviated class list. |
fnGetClass1 | (@LRNO varchar(7)) → varchar(100) | Returns the first (primary) class society for a vessel. |
fnGetClass2 | (@LRNO varchar(7)) → varchar(100) | Returns the second class society if dual-classed. |
fnGetClassDecode | (@code varchar(5)) → varchar(100) | Decodes a class society code to full name. |
fnGetStatType | (@LRNO varchar(7)) → varchar(50) | Returns the ship type description from SUPPLEMENTAL_ABSD_OVTY.STAT5CODE via tblStatCode5. |
fnGetHelicopterInd | (@LRNO varchar(7)) → varchar(1) | Returns Y/N helicopter landing capability indicator. |
fnGetIMOClass | (@LRNO varchar(7)) → varchar(20) | Returns IMO chemical class code for tankers. |
fnGetExxonCoilCode | (@LRNO varchar(7)) → varchar(10) | Returns Exxon-specific heating coil code for tankers. |
fnGetExxonOperator | (@LRNO varchar(7)) → varchar(7) | Returns the Exxon-designated operator OWCODE. |
fnGetExxonOperatorEfd | (@LRNO varchar(7)) → varchar(8) | Returns the effective date of the Exxon operator designation. |
fnWROSCLASSES | (@LRNO varchar(7)) → varchar(7000) | WROS (World Register of Ships) formatted class list. |
Annotation Helper Functions
| Function | Purpose |
|---|---|
fnGetAnnoDecode | Decodes annotation code to human-readable description. |
fnGetAnnoEffectiveDate | Returns effective date from annotation record. |
fnGetAnnoLookupValue | Looks up the decoded value for an annotation field (e.g. flag code → country name). |
fnGetAnnoLookupValueTest | Test variant of fnGetAnnoLookupValue. |
fnGetAnnoNewValue | Returns the new (after-change) value from a tblChanges annotation record. |
fnGetAnnoPreviousValue | Returns the old (before-change) value from a tblChanges annotation record. |
fnGetDateFromtblChangesNotes | Extracts a date from the Notes column of tblChanges (parses formatted date strings in notes). |
fnGetFlagFromtblChangesNotes | Extracts flag code from tblChanges Notes column. |
fnGetOWCodeFromtblChangesNotes | Extracts owner code from tblChanges Notes column. |
fnFormatAnno1 | Formats annotation entry type 1 for display. |
fnFormatAnno2 | Formats annotation entry type 2 for display. |
fnGetLookupValue | Generic codebook lookup — given a table name, key, and return column, returns the decoded value. |
Company, Address & Contact Functions
| Function | Purpose |
|---|---|
fnCoFullAddress | Returns formatted full postal address for a company (OWCODE) from ABSD_OWGE address fields. |
fnCoFullAddressCR | Full address with carriage returns for multi-line display. |
fnCoFullAddressCRAnnotation | Full address formatted for annotation context. |
fnCoAddressLiveShip | Returns company address specifically for live (in-service) ships context. |
fn_WSDFullAddress | WSD (World Ship Directory) formatted full address. |
fnAddressComm1 / fnAddressComm2 | Communications number formatters for address lines 1 and 2. |
fnAddressNotes | Returns address annotation notes. |
fnCommsNumbers | Returns formatted phone/fax/telex numbers for a company. |
fnCommsNumbersWSD | WSD-formatted communications numbers. |
fnCompanyURL | Returns the company website URL from ABSD_OWGE. |
fnRSGCompanyAssocAssoc | Returns associated companies (associate relationship) from RSG (Related Ship Groups) data. |
fnRSGCompanyAssocControl | Returns controlling company from RSG data. |
fnRSGCompanyAssocFormJt | Returns formal joint venture partners from RSG data. |
fnRSGCompanyAssocSubsid | Returns subsidiaries from RSG data. |
fnRSGCompanyHistory | Returns company name history from RSG ownership data. |
fnRSGPersonnelGrouping | Returns personnel grouping for company from RSG data. |
fn_QH_CompanyBusinessActivity | Quick Hub: returns business activity codes for a company. |
fn_QH_CompanyBusinessArea | Quick Hub: returns business area codes for a company. |
fn_QH_HeadOfficeCount | Quick Hub: returns head office vessel count for a company. |
TIP/MIS Utility Functions
| Function | Purpose |
|---|---|
AA_fnTIPMISRetrieveDMOrder | Returns the data-manager ordering sequence for a TIP/MIS source — controls processing priority when multiple sources supply the same field. |
fnTIPSelectDataType | Returns the data type (Varchar/Int/Decimal/DateTime) for a TIP field name — used by the TIP validation pipeline to route values to the correct column in tblThirdPartyData. |
fnTIPValidationD22_MANBOW_LEN | Validates a manbow length value for TIP data — equivalent of spValidateManbow_Len but as a scalar function returning a message string. |
fnCheckFlexDataBucketFieldName | Validates that a given field name exists in the FlexTemp bucket schema for a source — called by spTIPMISFlexLoad to reject unknown fields. |
Technical Summary Functions
| Function | Purpose |
|---|---|
fnDefectSummary | Returns a formatted defect summary for PSC inspections. |
fnDoorSummary | Returns summary text for cargo doors (count, type, dimensions). |
fnGearSummary | Returns cargo gear summary (crane/derrick counts and capacities). |
fnRampSummary | Returns ramp summary text (type, width, capacity). |
fnTankSummary | Returns tank summary text (count, total capacity, coating). |
fnContainerTextString | Returns container arrangement text string (TEU, FEU, under-deck/on-deck breakdown). |
fnLastInspection | Returns the date and port of last PSC inspection. |
fnRBBuilderFullName | Returns builder full name for Register Books output. |
fnRBBuilderFullNameUscore | Returns builder name with underscores for legacy Register Books format. |
fnRBTextContDeck | Container on-deck capacity text for Register Books. |
fnRBTextContHold | Container hold capacity text for Register Books. |
fnEUMRVGetTechnicalEfficiency | Returns technical efficiency indicators for EU MRV (Maritime Reporting and Verification) compliance reporting. |
Utility & Schema Inspection Functions
| Function | Signature | Purpose |
|---|---|---|
fn_GetPrimaryKey | (@table_name varchar(100)) → varchar(200) | Returns the primary key column(s) for a given table name. |
fn_GetPrimaryKeyInTable | (@table_name varchar(100)) → TABLE | Table-valued version — returns each PK column as a row. |
fnReturnColumnLen | (@table varchar(100), @col varchar(100)) → int | Returns the defined column length for a given table.column (queries sys.columns). |
fn_SQLServerBackupDir | () → varchar(500) | Returns the SQL Server default backup directory path via xp_regread. |
fn_getnext | (@current int) → int | Returns the next integer in a sequence (simple increment helper). |
fnCallsignCheck | (@Callsign varchar(20), @Flag varchar(3)) → char(1) | Legacy callsign range validator — checks against local Callsign_range table. Superseded in ABSD_OVGE trigger by EDM.DBO.T_REF_COUNTRY_CALL_SIGN_RANGES query (March 2026), but function still exists. |
fnDistanceCalc | (@lat1 decimal, @lon1 decimal, @lat2 decimal, @lon2 decimal) → decimal | Great-circle distance calculation between two lat/lon points (haversine formula). |
fnConvertDegreesMinsToDecimal | (@deg varchar(20)) → decimal(10,6) | Converts degrees-minutes (DDD.MM) to decimal degrees. |
fnGetUserInitials | () → varchar(10) | Returns the 3-char user initials for the current connection (same logic as J06_AUTHOR: UPPER(LEFT(RIGHT(SYSTEM_USER, LEN-CHARINDEX('\', …)), 3))). |
Function Count by Category
| Category | Count | Prefix / Pattern |
|---|---|---|
| APS Narrative generators | 21 | fnAPSNarrative*, fnTIPNarrative* |
| Fairplay format | 17 | fnFP* |
| Class/classification lookups | 13 | fnClass, fnCLASS*, fnGetClass*, fnWROSCLASSES |
| Company address/matching | 15 | fnCo*, fnCoMatch* |
| Date conversion | 14 | fnLRDate*, fnRealDate*, fnToDate*, fnAPS*Date* |
| Ownership at date | 14 | fnGet*AtDate, fnGetPrevious*, fnCurrStatat* |
| String utilities | 20 | fn_String*, fnReplace*, fnFind*, APSProper, fnTidyShipname |
| Name matching / phonetic | 9 | edit_distance, AA_fn*, CleanCompany*, fnCompanyNameFuzzy |
| TIP/MIS functions | 12 | fnTIPMIS*, fnTIP*, AA_fnTIPMIS* |
| Annotation helpers | 12 | fnGetAnno*, fnFormatAnno*, fnGetLookupValue |
| RSG company structure | 6 | fnRSG* |
| Technical summaries | 11 | fnDefectSummary, fnDoorSummary, fnGear*, fnRamp*, fnTank*, fnContainer*, fnRB*, fnEUMRV* |
| Codebook lookups | 10 | fnCBSB*, fnCBST*, fnGetStat*, fnGetExxon*, fnGetIMOClass, fnGetHeli* |
| Utility / schema | 10 | fn_Get*, fnReturn*, fnCallsign*, fnDistance*, fnConvert*, fnGetUser* |
| Register Books (RB*) | 4 | fnRBBuilderFullName*, fnRBText* |
| Quick Hub (QH) | 3 | fn_QH_* |
| Other / miscellaneous | 30 | fnCheckFlex*, fnCFD04*, fnBuilderName, fnLastInspection, fnToDate*, fnRoman*, etc. |
| Total | 281 |