P01012

-- ============================================================
-- Program: P01012 Address Book Revisions — create and maintain address book records, who's who contacts, phones, and addresses
-- Purpose: Address Book Revisions — create and maintain address book records, who's who contacts, phones, and addresses — auto-generated boilerplate from program-table-map
-- Grain  : F0101 × F0111, F0115, F01151, F0116 — 1:N joins yield one row per detail line
-- Tables : F0101, F0111, F0115, F01151, F0116
-- Notes  : Auto-generated skeleton. Julian dates are converted to DATE and implied-decimal amounts are scaled (verify decimals in F9210). 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
  ab.ABAN8 AS "Address book number - the master key for any entity",
  ab.ABALPH AS "Alpha name - the entity display name",
  f.WWIDLN AS "Line number distinguishing multiple contacts under one address; line 0 mirrors the entity itself.",
  f0.WPIDLN AS "Who's Who line the phone is attached to; 0 means the entity itself.",
  f0.WPCNLN AS "Related-person line identifier within the contact.",
  f0.WPRCK7 AS "Sequence number distinguishing multiple phones on the same line.",
  f01.EAIDLN AS "Who's Who line the address is attached to; 0 means the entity itself.",
  f01.EARCK7 AS "Sequence number distinguishing multiple electronic addresses per line.",
  CASE WHEN f011.ALEFTB IS NULL OR f011.ALEFTB = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f011.ALEFTB AS INT) DIV 1000, 1, 1), CAST(f011.ALEFTB AS INT) % 1000 - 1) END AS "Date this address becomes effective; part of the key, enabling address history."  -- CYYDDD Julian → DATE
FROM <catalog>.<schema_data>.f0101 ab
LEFT JOIN <catalog>.<schema_data>.f0111 f
  ON f.WWAN8 = ab.ABAN8
LEFT JOIN <catalog>.<schema_data>.f0115 f0
  ON f0.WPAN8 = ab.ABAN8
LEFT JOIN <catalog>.<schema_data>.f01151 f01
  ON f01.EAAN8 = ab.ABAN8
LEFT JOIN <catalog>.<schema_data>.f0116 f011
  ON f011.ALAN8 = ab.ABAN8
WHERE
  ab.ABAN8 = <AN8>
ORDER BY ab.ABAN8;