P0010

-- ============================================================
-- Program: P0010 Company Names & Numbers — set up companies and their fiscal date patterns for financial reporting
-- Purpose: Company Names & Numbers — set up companies and their fiscal date patterns for financial reporting — auto-generated boilerplate from program-table-map
-- Grain  : F0010 × F0008 — 1:N joins yield one row per detail line
-- Tables : F0010, F0008
-- 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
  h.CCCO AS "Company code; the key every financial table carries as CO or KCO.",
  h.CCNAME AS "Company name as printed on reports.",
  f.CDDTPN AS "Fiscal date pattern code this row defines; companies point to a pattern via F0010.",
  CASE WHEN f.CDDFYJ IS NULL OR f.CDDFYJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CDDFYJ AS INT) DIV 1000, 1, 1), CAST(f.CDDFYJ AS INT) % 1000 - 1) END AS "First day of the fiscal year for this pattern.",  -- CYYDDD Julian → DATE
  f.CDFQ AS "Fiscal quarter code (flagged obsolete by Oracle but still part of the unique key)."
FROM <catalog>.<schema_data>.f0010 h
LEFT JOIN <catalog>.<schema_data>.f0008 f
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
WHERE
  h.CCCO = '<CO>'
ORDER BY h.CCCO;