R12800

-- ============================================================
-- Program: R12800 Post G/L Entries to Assets — the batch job that carries posted G/L transactions on asset cost and expense accounts into the fixed asset balance records.
-- Purpose: Post G/L Entries to Assets — the batch job that carries posted G/L transactions on asset cost and expense accounts into the fixed asset balance records. — auto-generated boilerplate from program-table-map
-- Grain  : F1202 × F0911, F1201 — 1:N joins yield one row per detail line
-- Tables : F1202, F0911, F1201
-- 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.FLAID AS "Short account ID of the cost, accumulated depreciation, or expense account",
  h.FLCTRY AS "Century of the fiscal year",
  h.FLFY AS "Fiscal year of the balance row",
  h.FLFQ AS "Fiscal quarter — obsolete but still part of the primary key, normally blank",
  h.FLLT AS "Ledger type (AA actual, plus tax and alternate depreciation ledgers like D1-D9)",
  h.FLSBL AS "Subledger value, part of the key; usually blank for asset balances",
  h.FLNUMB AS "Asset item number — joins to F1201",
  h.FLSBLT AS "Subledger type qualifying the subledger value",
  f.GLDCT AS "Document type of the journal entry (JE, PV, RI, etc.).",
  f.GLDOC AS "Document number of the journal entry.",
  f.GLKCO AS "Company that assigned the document number (key company).",
  CASE WHEN f.GLDGJ IS NULL OR f.GLDGJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.GLDGJ AS INT) DIV 1000, 1, 1), CAST(f.GLDGJ AS INT) % 1000 - 1) END AS "G/L date driving the fiscal period the line posts to.",  -- CYYDDD Julian → DATE
  f.GLJELN AS "Line number within the journal entry.",
  f.GLEXTL AS "Line extension code completing the unique key."
FROM <catalog>.<schema_data>.f1202 h
LEFT JOIN <catalog>.<schema_data>.f0911 f
  ON f.GLLT = h.FLLT
LEFT JOIN <catalog>.<schema_data>.f1201 f1
  ON f1.FANUMB = h.FLNUMB
ORDER BY h.FLAID;