F0116

-- ============================================================
-- Table  : F0116 Effective-dated postal addresses for address book entries; the row with the latest effective date at or before a given date is the current address.
-- Purpose: Column-selected read of F0116 — auto-generated from field metadata
-- Grain  : One row per ALAN8 + ALEFTB
-- Notes  : Auto-generated skeleton. Julian dates (CYYDDD) are converted to DATE and implied-decimal amounts are scaled (verify decimals in F9210); padded business-unit/UDC keys are TRIMmed. Audit columns (…USER/…PID/…UPMJ) are omitted — see the quirks guide. Set catalog/schema to your Unity Catalog location; fill remaining placeholders (<...>) for your tenant.
-- ============================================================
SELECT
  f.ALAN8 AS "Address book number the address belongs to; joins to F0101.",
  CASE WHEN f.ALEFTB IS NULL OR f.ALEFTB = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.ALEFTB AS INT) DIV 1000, 1, 1), CAST(f.ALEFTB AS INT) % 1000 - 1) END AS "Date this address becomes effective; part of the key, enabling address history.",  -- CYYDDD Julian → DATE
  f.ALEFTF AS "Flag marking whether effective-date logic applies.",
  f.ALADD1 AS "Street address line 1.",
  f.ALADD2 AS "Street address line 2.",
  f.ALADD3 AS "Street address line 3.",
  f.ALADD4 AS "Street address line 4.",
  f.ALADDZ AS "Postal or ZIP code.",
  f.ALCTY1 AS "City.",
  f.ALCOUN AS "County.",
  f.ALADDS AS "State or province code.",
  f.ALCTR AS "Country code."
FROM <catalog>.<schema_data>.f0116 f
WHERE
  f.ALAN8 = <AN8>
ORDER BY f.ALAN8;