F0911

-- ============================================================
-- Table  : F0911 General ledger transaction detail: every journal entry line with its account, G/L date, amount, batch, subledger, and source-document references.
-- Purpose: Column-selected read of F0911 — auto-generated from field metadata
-- Grain  : One row per GLDCT + GLDOC + GLKCO + GLDGJ + GLJELN + GLEXTL + GLLT
-- 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.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.",
  f.GLLT AS "Ledger type the line belongs to (AA actual, CA foreign currency, etc.).",
  f.GLPOST AS "Posted status flag; P means posted to F0902.",
  f.GLICU AS "Batch number the line was entered under; joins to F0011.",
  f.GLICUT AS "Batch type identifying the source application (G, V, IB, etc.).",
  CASE WHEN f.GLDICJ IS NULL OR f.GLDICJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.GLDICJ AS INT) DIV 1000, 1, 1), CAST(f.GLDICJ AS INT) % 1000 - 1) END AS "Date of the batch.",  -- CYYDDD Julian → DATE
  f.GLCO AS "Company of the account being posted.",
  f.GLANI AS "Account number as entered, usually in BU.Object.Subsidiary format.",
  f.GLAM AS "Mode in which the account number was entered.",
  f.GLAID AS "Account ID; the reliable join to F0901 and F0902.",
  TRIM(f.GLMCU) AS "Business unit of the line (right-justified); the cost-center dimension.",
  f.GLOBJ AS "Object account of the line.",
  f.GLSUB AS "Subsidiary of the line.",
  f.GLSBL AS "Subledger value carrying transaction-level detail below the account.",
  f.GLSBLT AS "Type of the subledger value.",
  f.GLPN AS "Fiscal period number derived from the G/L date.",
  f.GLCTRY AS "Century component of the fiscal year.",
  f.GLFY AS "Two-digit fiscal year of the posting.",
  f.GLCRCD AS "Transaction currency of the line.",
  f.GLCRR AS "Exchange rate applied to convert the transaction currency. Implied decimals vary by setup, so no fixed scaling is applied.",
  f.GLAA / POWER(10, 2) AS "Ledger amount in the ledger currency, with implied decimals.",  -- implied decimals: 2 (verify in F9210)
  f.GLU / POWER(10, 2) AS "Units quantity for unit-ledger analysis.",  -- implied decimals: 2 (verify in F9210)
  f.GLUM AS "Unit of measure for the units amount.",
  f.GLGLC AS "G/L offset code that determined the automatic offset account.",
  f.GLRE AS "Marks reversing or voided entries.",
  f.GLEXA AS "Explanation line 1 (name/alpha text).",
  f.GLEXR AS "Explanation line 2 (remark text).",
  f.GLR1 AS "Reference 1 cross-reference from the source system.",
  f.GLODOC AS "Original document number the line traces back to.",
  f.GLODCT AS "Original document type.",
  f.GLAN8 AS "Address number (customer, supplier, employee) tied to the line; joins to F0101.",
  f.GLPO AS "Purchase order number referenced by the line.",
  f.GLDCTO AS "Order type of the referenced purchase order.",
  f.GLACR / POWER(10, 2) AS "Amount in the foreign (transaction) currency.",  -- implied decimals: 2 (verify in F9210)
  f.GLBCRC AS "Base currency of the company at posting time."
FROM <catalog>.<schema_data>.f0911 f
WHERE
  f.GLDCT = '<DCT>'
  -- AND f.GLDOC = <DOC>
  -- AND f.GLKCO = '<KCO>'
  -- AND f.GLDGJ >= <DGJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.GLDGJ <= <DGJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.GLDCT;