F0008

-- ============================================================
-- Table  : F0008 Defines each fiscal date pattern: the fiscal-year start date and the ending date of every accounting period, used to map calendar dates to fiscal periods.
-- Purpose: Column-selected read of F0008 — auto-generated from field metadata
-- Grain  : One row per CDDTPN + CDDFYJ + CDFQ
-- 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.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).",
  f.CDFY AS "Two-digit fiscal year the pattern row covers.",
  CASE WHEN f.CDD01J IS NULL OR f.CDD01J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CDD01J AS INT) DIV 1000, 1, 1), CAST(f.CDD01J AS INT) % 1000 - 1) END AS "Last day of fiscal period 1.",  -- CYYDDD Julian → DATE
  CASE WHEN f.CDD02J IS NULL OR f.CDD02J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CDD02J AS INT) DIV 1000, 1, 1), CAST(f.CDD02J AS INT) % 1000 - 1) END AS "Last day of fiscal period 2.",  -- CYYDDD Julian → DATE
  CASE WHEN f.CDD03J IS NULL OR f.CDD03J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CDD03J AS INT) DIV 1000, 1, 1), CAST(f.CDD03J AS INT) % 1000 - 1) END AS "Last day of fiscal period 3.",  -- CYYDDD Julian → DATE
  CASE WHEN f.CDD04J IS NULL OR f.CDD04J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CDD04J AS INT) DIV 1000, 1, 1), CAST(f.CDD04J AS INT) % 1000 - 1) END AS "Last day of fiscal period 4.",  -- CYYDDD Julian → DATE
  CASE WHEN f.CDD05J IS NULL OR f.CDD05J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CDD05J AS INT) DIV 1000, 1, 1), CAST(f.CDD05J AS INT) % 1000 - 1) END AS "Last day of fiscal period 5.",  -- CYYDDD Julian → DATE
  CASE WHEN f.CDD06J IS NULL OR f.CDD06J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CDD06J AS INT) DIV 1000, 1, 1), CAST(f.CDD06J AS INT) % 1000 - 1) END AS "Last day of fiscal period 6.",  -- CYYDDD Julian → DATE
  CASE WHEN f.CDD07J IS NULL OR f.CDD07J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CDD07J AS INT) DIV 1000, 1, 1), CAST(f.CDD07J AS INT) % 1000 - 1) END AS "Last day of fiscal period 7.",  -- CYYDDD Julian → DATE
  CASE WHEN f.CDD08J IS NULL OR f.CDD08J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CDD08J AS INT) DIV 1000, 1, 1), CAST(f.CDD08J AS INT) % 1000 - 1) END AS "Last day of fiscal period 8.",  -- CYYDDD Julian → DATE
  CASE WHEN f.CDD09J IS NULL OR f.CDD09J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CDD09J AS INT) DIV 1000, 1, 1), CAST(f.CDD09J AS INT) % 1000 - 1) END AS "Last day of fiscal period 9.",  -- CYYDDD Julian → DATE
  CASE WHEN f.CDD10J IS NULL OR f.CDD10J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CDD10J AS INT) DIV 1000, 1, 1), CAST(f.CDD10J AS INT) % 1000 - 1) END AS "Last day of fiscal period 10.",  -- CYYDDD Julian → DATE
  CASE WHEN f.CDD11J IS NULL OR f.CDD11J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CDD11J AS INT) DIV 1000, 1, 1), CAST(f.CDD11J AS INT) % 1000 - 1) END AS "Last day of fiscal period 11.",  -- CYYDDD Julian → DATE
  CASE WHEN f.CDD12J IS NULL OR f.CDD12J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CDD12J AS INT) DIV 1000, 1, 1), CAST(f.CDD12J AS INT) % 1000 - 1) END AS "Last day of fiscal period 12.",  -- CYYDDD Julian → DATE
  CASE WHEN f.CDD13J IS NULL OR f.CDD13J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CDD13J AS INT) DIV 1000, 1, 1), CAST(f.CDD13J AS INT) % 1000 - 1) END AS "Last day of fiscal period 13 (13-period calendars).",  -- CYYDDD Julian → DATE
  CASE WHEN f.CDD14J IS NULL OR f.CDD14J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CDD14J AS INT) DIV 1000, 1, 1), CAST(f.CDD14J AS INT) % 1000 - 1) END AS "Last day of fiscal period 14 (14-period calendars).",  -- CYYDDD Julian → DATE
  f.CDPNT AS "Pattern type classifier for the date pattern.",
  f.CDCTRY AS "Century component paired with the two-digit fiscal year."
FROM <catalog>.<schema_data>.f0008 f
ORDER BY f.CDDTPN;