GL Detail by Budget Center with Project Detail

General Ledger

General-ledger detail by budget center with the project behind each line — every journal line by budget center, account, and project, so budget owners can see actuals and trace them to the project that drove the cost.

Run note · BIP run High-volume GL extracts can exceed BI Publisher's online output limit and time out. Run it as a scheduled process (ESS) with output bursted to file or email rather than online preview, and bound it by ledger and period.

Sample build of the GL Detail by Budget Center with Project Detail — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.

Sample build · illustrative

Filters

Ledger

US Primary

Period

JAN-2026

Budget

FY26 Plan

Currency

USD

Budget centers

$24.00M

Actuals

$1.10M

No project

Date	Journal / Source	Account	Project	Actual	Budget	Variance
05-JAN-26	Payables / INV-88231	5200-300 Travel	PRJ-1042 Rollout	12,400	15,000	(2,600)
09-JAN-26	Projects / Cost Dist	5210-300 Contractor	PRJ-1042 Rollout	86,200	80,000	6,200
14-JAN-26	GL / Manual	5600-300 Software	PRJ-1090 Platform	41,000	38,000	3,000
22-JAN-26	Payables / INV-88455	5200-300 Travel	PRJ-1090 Platform	7,850	9,000	(1,150)
28-JAN-26	Projects / Cost Dist	5210-300 Contractor	PRJ-1042 Rollout	54,300	60,000	(5,700)
			Total	201,750	202,000	(250)

AI Analyst · active

reading

The report reads GL_JE_LINES by the budget-center segment and joins the project reference on each line.

flag

$1.1M of actuals carry no project — they hit a budget center but can't be traced to a project, which breaks project-level cost reporting.

root cause & next step

Require the project segment on those budget centers' entries; untagged actuals are the gap that makes project margins look better than they are.

Illustrative data. The live interactive version — drill-through, filters, export, and the AI Analyst — runs on your warehouse. See it live →

This is the report's BI Publisher data model — the SQL data set BI Publisher runs against Oracle tables to produce the output. The same SQL becomes a dbt model in your warehouse, so one definition drives both the formatted report and the analytics layer.

Data sources

How it interconnects: this data set reads the physical tables above. Those same tables surface in OTBI as subject areas and in BICC as PVOs — three lenses on one source. Open any table to trace its subject areas and View Objects.

SQL data set · genericized · parameterized · no hardcoded segments

Show / hide SQL

WITH
/* ============================================================
   Segment4 (Natural Account) value-set lookup
   ============================================================ */
seg4 AS (
  SELECT /*+ MATERIALIZE */
         s.id_flex_num        AS chart_of_accounts_id,
         s.flex_value_set_id
  FROM fnd_id_flex_segments s
  WHERE s.id_flex_code = 'GL#'
    AND s.application_id = 101
    AND s.application_column_name = 'SEGMENT4'
    AND s.enabled_flag = 'Y'
),

/* ============================================================
   Base GL lines (one row per GL JE line)
   ============================================================ */
gl_base AS (
  SELECT
      gjh.je_header_id,
      gjl.je_line_num,

      gjb.name                                AS batch_name,
      gjh.name                                AS journal_name,
      gjh.description                         AS journal_description,

      /* UPDATED: resolve Source whether stored as NAME or KEY */
      COALESCE(gjs.user_je_source_name, gjs.je_source_name, gjh.je_source)
                                              AS je_source,
      COALESCE(gjs.user_je_source_name, gjs.je_source_name, gjh.je_source)
                                              AS user_je_source_name,

      /* UPDATED: resolve Category whether stored as NAME or KEY */
      COALESCE(gjc.user_je_category_name, gjc.je_category_name, gjh.je_category)
                                              AS je_category,

      /* FIXED: protect TO_DATE() from ADJ / non-month / non-English period names */
      CASE
        WHEN REGEXP_LIKE(gjh.period_name, '^\d{2}-\d{2}$') THEN gjh.period_name

        WHEN REGEXP_LIKE(
               gjh.period_name,
               '^(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)-\d{2}$',
               'i'
             )
        THEN
          TO_CHAR(
            TO_DATE(gjh.period_name, 'Mon-RR', 'NLS_DATE_LANGUAGE=English'),
            'Mon-YY',
            'NLS_DATE_LANGUAGE=English'
          )

        ELSE
          gjh.period_name   -- keeps ADJ-25, ADJ2-25, ΚΛΕΙΣ-25, etc.
      END                                     AS gl_period,

      gjl.effective_date                      AS accounting_date_dt,
      gjh.posted_date                         AS posted_date_dt,

      gl.name                                 AS ledger_name,
      gl.currency_code                        AS functional_currency,
      gjl.currency_code                       AS entered_currency,

      gjh.posting_acct_seq_value              AS voucher_number,

      gjl.description                         AS line_description,
      gjl.entered_dr,
      gjl.entered_cr,
      gjl.accounted_dr,
      gjl.accounted_cr,

      gcc.code_combination_id,
      gcc.chart_of_accounts_id,

      gcc.segment1                            AS balancing_segment,
      TO_CHAR(gcc.segment2)                   AS selling_method,
      TO_CHAR(gcc.segment5)                   AS location_code,
      TO_CHAR(gcc.segment3)                   AS cost_center_code,
      TO_CHAR(gcc.segment6)                   AS brand,
      TO_CHAR(gcc.segment7)                   AS intercompany,
      TO_CHAR(gcc.segment4)                   AS natural_account,

      NVL(ffvv_na.flex_value_meaning, ffvv_na.description) AS natural_account_name,
      ffvv_na.description                     AS natural_account_description,

      TRIM(BOTH '.' FROM REGEXP_REPLACE(
        NVL(TO_CHAR(gcc.segment1),'') || '.' || NVL(TO_CHAR(gcc.segment2),'') || '.' ||
        NVL(TO_CHAR(gcc.segment3),'') || '.' || NVL(TO_CHAR(gcc.segment4),'') || '.' ||
        NVL(TO_CHAR(gcc.segment5),'') || '.' || NVL(TO_CHAR(gcc.segment6),'') || '.' ||
        NVL(TO_CHAR(gcc.segment7),'') || '.' || NVL(TO_CHAR(gcc.segment8),'') || '.' ||
        NVL(TO_CHAR(gcc.segment9),'') || '.' || NVL(TO_CHAR(gcc.segment10),''), '(\.)+', '.'
      )) AS account_combination,

      (
        TO_CHAR(gcc.segment1) || '.' ||
        TO_CHAR(gcc.segment2) || '.' ||
        TO_CHAR(gcc.segment3) || '.' ||
        TO_CHAR(gcc.segment5) || '.' ||
        TO_CHAR(gcc.segment6)
      ) AS budget_center

  FROM gl_je_headers gjh
  JOIN gl_je_lines   gjl ON gjl.je_header_id = gjh.je_header_id
  JOIN gl_je_batches gjb ON gjb.je_batch_id  = gjh.je_batch_id
  JOIN gl_ledgers    gl  ON gl.ledger_id     = gjh.ledger_id
  JOIN gl_code_combinations gcc ON gcc.code_combination_id = gjl.code_combination_id

  /* UPDATED: join using NAME or KEY (avoid invalid-number by casting KEY to char) */
  LEFT JOIN gl_je_sources gjs
    ON (gjs.je_source_name = gjh.je_source OR TO_CHAR(gjs.je_source_key) = gjh.je_source)

  LEFT JOIN gl_je_categories gjc
    ON (gjc.je_category_name = gjh.je_category OR TO_CHAR(gjc.je_category_key) = gjh.je_category)

  LEFT JOIN seg4 s4
    ON s4.chart_of_accounts_id = gcc.chart_of_accounts_id
  LEFT JOIN fnd_flex_values_vl ffvv_na
    ON ffvv_na.flex_value_set_id = s4.flex_value_set_id
   AND ffvv_na.flex_value        = TO_CHAR(gcc.segment4)

  WHERE 1=1
    AND gjh.actual_flag = 'A'

    AND ( :P_FROM_ACCOUNTING_DATE IS NULL OR gjl.effective_date >= :P_FROM_ACCOUNTING_DATE )
    AND ( :P_TO_ACCOUNTING_DATE   IS NULL OR gjl.effective_date <  :P_TO_ACCOUNTING_DATE + 1 )

    AND ( gl.name IN (:P_LEDGER_NAME) OR 'All' IN ('All'||:P_LEDGER_NAME))

    AND ( :P_BAL_FROM IS NULL OR TO_NUMBER(TRIM(gcc.segment1)) >= :P_BAL_FROM )
    AND ( :P_BAL_TO   IS NULL OR TO_NUMBER(TRIM(gcc.segment1)) <= :P_BAL_TO )

    AND ( :P_SELLING_METHOD_FROM IS NULL OR TO_NUMBER(TRIM(gcc.segment2)) >= :P_SELLING_METHOD_FROM )
    AND ( :P_SELLING_METHOD_TO   IS NULL OR TO_NUMBER(TRIM(gcc.segment2)) <= :P_SELLING_METHOD_TO )

    AND ( :P_CC_FROM IS NULL OR TO_NUMBER(TRIM(gcc.segment3)) >= :P_CC_FROM )
    AND ( :P_CC_TO   IS NULL OR TO_NUMBER(TRIM(gcc.segment3)) <= :P_CC_TO )

    AND ( :P_NA_FROM IS NULL OR TO_NUMBER(TRIM(gcc.segment4)) >= :P_NA_FROM )
    AND ( :P_NA_TO   IS NULL OR TO_NUMBER(TRIM(gcc.segment4)) <= :P_NA_TO )

    AND ( :P_LOCATION_FROM IS NULL OR TO_NUMBER(TRIM(gcc.segment5)) >= :P_LOCATION_FROM )
    AND ( :P_LOCATION_TO   IS NULL OR TO_NUMBER(TRIM(gcc.segment5)) <= :P_LOCATION_TO )

    AND ( :P_BRAND_FROM IS NULL OR TO_NUMBER(TRIM(gcc.segment6)) >= :P_BRAND_FROM )
    AND ( :P_BRAND_TO   IS NULL OR TO_NUMBER(TRIM(gcc.segment6)) <= :P_BRAND_TO )

    AND ( :P_IC_FROM IS NULL OR TO_NUMBER(TRIM(gcc.segment7)) >= :P_IC_FROM )
    AND ( :P_IC_TO   IS NULL OR TO_NUMBER(TRIM(gcc.segment7)) <= :P_IC_TO )
),

/* ============================================================
   DO NOT DEDUP GL_IMPORT_REFERENCES:
   keep all link rows so we can get true split XLA lines
   ============================================================ */
gir_ranked AS (
  SELECT
    gir.je_header_id,
    gir.je_line_num,
    gir.gl_sl_link_id,
    gir.gl_sl_link_table,
    ROW_NUMBER() OVER (
      PARTITION BY gir.je_header_id, gir.je_line_num
      ORDER BY gir.gl_sl_link_id
    ) AS gir_rn
  FROM gl_import_references gir
  JOIN gl_base gb
    ON gb.je_header_id = gir.je_header_id
   AND gb.je_line_num  = gir.je_line_num
),

/* ============================================================
   DO NOT DEDUP XLA_AE_LINES blindly:
   keep all XAL rows (we’ll filter later using split logic)
   ============================================================ */
xal_ranked AS (
  SELECT
    gb.je_header_id,
    gb.je_line_num,

    xal.application_id,
    xal.ae_header_id,
    xal.ae_line_num,
    xal.accounting_class_code,
    xal.gl_sl_link_id,
    xal.gl_sl_link_table,

    xal.entered_dr,
    xal.entered_cr,
    xal.accounted_dr,
    xal.accounted_cr,

    ROW_NUMBER() OVER (
      PARTITION BY gb.je_header_id, gb.je_line_num
      ORDER BY xal.application_id, xal.ae_header_id, xal.ae_line_num
    ) AS xal_rn

  FROM gl_base gb
  LEFT JOIN gir_ranked gir
    ON gir.je_header_id = gb.je_header_id
   AND gir.je_line_num  = gb.je_line_num
  LEFT JOIN xla_ae_lines xal
    ON xal.gl_sl_link_id    = gir.gl_sl_link_id
   AND xal.gl_sl_link_table = gir.gl_sl_link_table
),

/* ============================================================
   Flag XLA lines that participate in "split-worthy" distributions
   ============================================================ */
xla_split_flag AS (
  SELECT
    xdl.application_id,
    xdl.ae_header_id,
    xdl.ae_line_num,
    MAX(CASE
          WHEN xdl.source_distribution_type IN ('AP_INV_DIST','PJC_COST','PJC_XLA_LINES')
          THEN 1 ELSE 0
        END) AS has_split
  FROM xla_distribution_links xdl
  GROUP BY
    xdl.application_id,
    xdl.ae_header_id,
    xdl.ae_line_num
),

/* ============================================================
   XLA context (header/event/entity) + AMOUNTS
   Keep:
   - ALL split-worthy XLA lines
   - ELSE only the first XLA line per GL line (prevents runaway multiply)
   ============================================================ */
xla_ctx AS (
  SELECT *
  FROM (
    SELECT
      xr.je_header_id,
      xr.je_line_num,

      xr.application_id,
      xr.ae_header_id,
      xr.ae_line_num,
      xr.accounting_class_code,

      xr.entered_dr,
      xr.entered_cr,
      xr.accounted_dr,
      xr.accounted_cr,

      xah.description AS sla_header_description,
      xe.event_id,
      xte.entity_code,
      xte.source_id_int_1,

      NVL(xsf.has_split, 0) AS has_split,

      ROW_NUMBER() OVER (
        PARTITION BY xr.je_header_id, xr.je_line_num
        ORDER BY xr.application_id, xr.ae_header_id, xr.ae_line_num
      ) AS gb_xla_rn

    FROM xal_ranked xr
    LEFT JOIN xla_ae_headers xah
      ON xah.application_id = xr.application_id
     AND xah.ae_header_id   = xr.ae_header_id
    LEFT JOIN xla_events xe
      ON xe.application_id  = xah.application_id
     AND xe.event_id        = xah.event_id
    LEFT JOIN xla_transaction_entities xte
      ON xte.application_id = xe.application_id
     AND xte.entity_id      = xe.entity_id
    LEFT JOIN xla_split_flag xsf
      ON xsf.application_id = xr.application_id
     AND xsf.ae_header_id   = xr.ae_header_id
     AND xsf.ae_line_num    = xr.ae_line_num
  )
  WHERE has_split = 1 OR gb_xla_rn = 1
),

/* ============================================================
   Rank XLA_DISTRIBUTION_LINKS per XLA line
   - Keep ALL rows for AP_INV_DIST + PJC_* (split)
   - Keep only rn=1 for everything else
   ============================================================ */
xdl_ranked AS (
  SELECT
    xdl.application_id,
    xdl.ae_header_id,
    xdl.ae_line_num,
    xdl.event_id,
    xdl.source_distribution_type,
    xdl.source_distribution_id_num_1,
    xdl.source_distribution_id_num_2,
    ROW_NUMBER() OVER (
      PARTITION BY xdl.application_id, xdl.ae_header_id, xdl.ae_line_num
      ORDER BY
        CASE
          WHEN xdl.source_distribution_type IN ('PJC_COST','PJC_XLA_LINES') THEN 1
          WHEN xdl.source_distribution_type = 'AP_INV_DIST' THEN 2
          WHEN xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL' THEN 3
          WHEN xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL' THEN 4
          WHEN xdl.source_distribution_type = 'PO_DISTRIBUTIONS_ALL' THEN 5
          WHEN xdl.source_distribution_type = 'RCV_RECEIVING' THEN 6
          WHEN xdl.source_distribution_type LIKE 'FA%' THEN 7
          ELSE 99
        END
    ) rn
  FROM xla_distribution_links xdl
  JOIN xla_ctx xc
    ON xc.application_id = xdl.application_id
   AND xc.ae_header_id   = xdl.ae_header_id
   AND xc.ae_line_num    = xdl.ae_line_num
),

/* ============================================================
   AP Payments fallback (keep)
   ============================================================ */
ap_pay_best AS (
  SELECT *
  FROM (
    SELECT
      xc.je_header_id,
      xc.je_line_num,
      hp_pay.party_name     AS pay_supplier,
      ai_pay.invoice_num    AS pay_invoice_number,
      ai_pay.invoice_date   AS pay_invoice_date,
      ROW_NUMBER() OVER (
        PARTITION BY xc.je_header_id, xc.je_line_num
        ORDER BY ai_pay.invoice_num
      ) rn
    FROM xla_ctx xc
    JOIN ap_checks_all ac_pay
      ON xc.application_id = 200
     AND xc.entity_code    = 'AP_PAYMENTS'
     AND ac_pay.check_id   = xc.source_id_int_1
    JOIN poz_suppliers ps_pay
      ON ac_pay.vendor_id = ps_pay.vendor_id
    JOIN hz_parties hp_pay
      ON ps_pay.party_id = hp_pay.party_id
    JOIN ap_invoice_payments_all aip_pay
      ON aip_pay.check_id            = ac_pay.check_id
     AND aip_pay.accounting_event_id = xc.event_id
    JOIN ap_invoices_all ai_pay
      ON ai_pay.invoice_id = aip_pay.invoice_id
  )
  WHERE rn = 1
),

ap_pay_gl_best AS (
  SELECT *
  FROM (
    SELECT
      gb.je_header_id,
      gb.je_line_num,
      hp_pay.party_name   AS pay_supplier_gl,
      ai_pay.invoice_num  AS pay_invoice_number_gl,
      ai_pay.invoice_date AS pay_invoice_date_gl,
      ROW_NUMBER() OVER (
        PARTITION BY gb.je_header_id, gb.je_line_num
        ORDER BY ai_pay.invoice_num
      ) rn
    FROM gl_base gb
    JOIN gl_import_references gir
      ON gir.je_header_id = gb.je_header_id
     AND gir.je_line_num  = gb.je_line_num
    JOIN xla_ae_lines xal
      ON xal.gl_sl_link_id    = gir.gl_sl_link_id
     AND xal.gl_sl_link_table = gir.gl_sl_link_table
    JOIN xla_ae_headers xah
      ON xah.application_id = xal.application_id
     AND xah.ae_header_id   = xal.ae_header_id
    JOIN xla_transaction_entities xte
      ON xte.application_id = xah.application_id
     AND xte.entity_id      = xah.entity_id
     AND xte.application_id = 200
     AND xte.entity_code    = 'AP_PAYMENTS'
    JOIN ap_checks_all ac_pay
      ON ac_pay.check_id = xte.source_id_int_1
    JOIN poz_suppliers ps_pay
      ON ps_pay.vendor_id = ac_pay.vendor_id
    JOIN hz_parties hp_pay
      ON hp_pay.party_id = ps_pay.party_id
    JOIN ap_invoice_payments_all aip_pay
      ON aip_pay.check_id            = ac_pay.check_id
     AND aip_pay.accounting_event_id = xah.event_id
    JOIN ap_invoices_all ai_pay
      ON ai_pay.invoice_id = aip_pay.invoice_id
  )
  WHERE rn = 1
),

ap_pay_gl_check_best AS (
  SELECT *
  FROM (
    SELECT
      gb.je_header_id,
      gb.je_line_num,
      hp_pay.party_name   AS pay_supplier_chk,
      ai_pay.invoice_num  AS pay_invoice_number_chk,
      ai_pay.invoice_date AS pay_invoice_date_chk,
      ROW_NUMBER() OVER (
        PARTITION BY gb.je_header_id, gb.je_line_num
        ORDER BY ai_pay.invoice_num
      ) rn
    FROM gl_base gb
    JOIN gl_import_references gir
      ON gir.je_header_id = gb.je_header_id
     AND gir.je_line_num  = gb.je_line_num
    JOIN xla_ae_lines xal
      ON xal.gl_sl_link_id    = gir.gl_sl_link_id
     AND xal.gl_sl_link_table = gir.gl_sl_link_table
    JOIN xla_ae_headers xah
      ON xah.application_id = xal.application_id
     AND xah.ae_header_id   = xal.ae_header_id
    JOIN xla_transaction_entities xte
      ON xte.application_id = xah.application_id
     AND xte.entity_id      = xah.entity_id
     AND xte.application_id = 200
     AND xte.entity_code    = 'AP_PAYMENTS'
    JOIN ap_checks_all ac_pay
      ON ac_pay.check_id = xte.source_id_int_1
    JOIN poz_suppliers ps_pay
      ON ps_pay.vendor_id = ac_pay.vendor_id
    JOIN hz_parties hp_pay
      ON hp_pay.party_id = ps_pay.party_id
    JOIN ap_invoice_payments_all aip_pay
      ON aip_pay.check_id = ac_pay.check_id
    JOIN ap_invoices_all ai_pay
      ON ai_pay.invoice_id = aip_pay.invoice_id
  )
  WHERE rn = 1
),

/* ============================================================
   IBY fallback anchored on SLA Transaction Number (Payables Payments)
   ============================================================ */
iby_gl_txn_best AS (
  SELECT *
  FROM (
    SELECT
      gb.je_header_id,
      gb.je_line_num,
      ip.payee_party_name            AS iby_supplier,
      idp.calling_app_doc_ref_number AS iby_invoice_number,
      idp.document_date              AS iby_invoice_date,
      ROW_NUMBER() OVER (
        PARTITION BY gb.je_header_id, gb.je_line_num
        ORDER BY
          CASE WHEN TO_CHAR(ip.paper_document_number) = xte.transaction_number THEN 1 ELSE 2 END,
          idp.document_date DESC,
          idp.calling_app_doc_ref_number
      ) rn
    FROM gl_base gb
    JOIN gl_import_references gir
      ON gir.je_header_id = gb.je_header_id
     AND gir.je_line_num  = gb.je_line_num
    JOIN xla_ae_lines xal
      ON xal.gl_sl_link_id    = gir.gl_sl_link_id
     AND xal.gl_sl_link_table = gir.gl_sl_link_table
    JOIN xla_ae_headers xah
      ON xah.application_id = xal.application_id
     AND xah.ae_header_id   = xal.ae_header_id
    JOIN xla_events xe
      ON xe.application_id = xah.application_id
     AND xe.event_id       = xah.event_id
    JOIN xla_transaction_entities xte
      ON xte.application_id = xe.application_id
     AND xte.entity_id      = xe.entity_id
    JOIN iby_payments_all ip
      ON gb.je_source   = 'Payables'
     AND gb.je_category = 'Payments'
     AND xte.transaction_number IS NOT NULL
     AND REGEXP_LIKE(xte.transaction_number,'^[0-9]+$')
     AND (
          TO_CHAR(ip.paper_document_number)     = xte.transaction_number
       OR TO_CHAR(ip.payment_reference_number) = xte.transaction_number
     )
    JOIN iby_docs_payable_all idp
      ON idp.payment_id     = ip.payment_id
     AND idp.calling_app_id = 200
  )
  WHERE rn = 1
),

/* ============================================================
   Receivables Receipt fallback (AR Cash Receipts)
   ============================================================ */
ar_receipt_best AS (
  SELECT *
  FROM (
    SELECT
      gb.je_header_id,
      gb.je_line_num,
      acr.receipt_number AS receipt_number,
      acr.receipt_date   AS receipt_date,
      hp_rcpt.party_name AS receipt_customer,
      ROW_NUMBER() OVER (
        PARTITION BY gb.je_header_id, gb.je_line_num
        ORDER BY acr.receipt_date DESC, acr.receipt_number
      ) rn
    FROM gl_base gb
    JOIN gl_import_references gir
      ON gir.je_header_id = gb.je_header_id
     AND gir.je_line_num  = gb.je_line_num
    JOIN xla_ae_lines xal
      ON xal.gl_sl_link_id    = gir.gl_sl_link_id
     AND xal.gl_sl_link_table = gir.gl_sl_link_table
    JOIN xla_ae_headers xah
      ON xah.application_id = xal.application_id
     AND xah.ae_header_id   = xal.ae_header_id
    JOIN xla_events xe
      ON xe.application_id = xah.application_id
     AND xe.event_id       = xah.event_id
    JOIN xla_transaction_entities xte
      ON xte.application_id = xe.application_id
     AND xte.entity_id      = xe.entity_id
    JOIN ar_cash_receipts_all acr
      ON gb.je_source = 'Receivables'
     AND (
          acr.cash_receipt_id = xte.source_id_int_1
       OR acr.receipt_number  = xte.transaction_number
     )
    LEFT JOIN hz_cust_accounts hca_rcpt
      ON hca_rcpt.cust_account_id = acr.pay_from_customer
    LEFT JOIN hz_parties hp_rcpt
      ON hp_rcpt.party_id = hca_rcpt.party_id
  )
  WHERE rn = 1
)

SELECT *
FROM (
  SELECT
    gb.batch_name,
    gb.journal_name,
    gb.journal_description,
    gb.je_source,
    gb.user_je_source_name,
    gb.je_category,
    gb.gl_period,

    TO_CHAR(gb.accounting_date_dt, 'MM-DD-YYYY') AS accounting_date,
    TO_CHAR(gb.posted_date_dt, 'Mon DD, YYYY', 'NLS_DATE_LANGUAGE=English') AS posted_date,

    gb.ledger_name,
    gb.functional_currency,
    gb.entered_currency,

    /* Exchange rate: use XLA nets when present (divisor cancels out) */
    TO_CHAR(ROUND(
      CASE
        WHEN (NVL(COALESCE(xc.entered_dr, gb.entered_dr),0) - NVL(COALESCE(xc.entered_cr, gb.entered_cr),0)) <> 0 THEN
          (NVL(COALESCE(xc.accounted_dr, gb.accounted_dr),0) - NVL(COALESCE(xc.accounted_cr, gb.accounted_cr),0)) /
          (NVL(COALESCE(xc.entered_dr, gb.entered_dr),0) - NVL(COALESCE(xc.entered_cr, gb.entered_cr),0))
      END, 2
    ), 'FM999,999,999,990.000000') AS exchange_rate,

    gb.je_line_num AS line_number,
    gb.line_description,

    /* ---------- Amount allocation guard (only matters if 1 XAL links to many XDL rows) ---------- */
    CAST(NVL(ROUND(
      (
        (NVL(COALESCE(xc.accounted_dr, gb.accounted_dr),0) - NVL(COALESCE(xc.accounted_cr, gb.accounted_cr),0))
        /
        CASE
          WHEN xc.application_id IS NOT NULL
           AND xdl.source_distribution_type IN ('AP_INV_DIST','PJC_COST','PJC_XLA_LINES')
           AND COUNT(*) OVER (PARTITION BY xc.application_id, xc.ae_header_id, xc.ae_line_num) > 0
          THEN COUNT(*) OVER (PARTITION BY xc.application_id, xc.ae_header_id, xc.ae_line_num)
          ELSE 1
        END
      ), 2
    ),0) AS DECIMAL(38,2)) AS functional_amount,

    CAST(NVL(ROUND(
      (
        (NVL(COALESCE(xc.entered_dr, gb.entered_dr),0) - NVL(COALESCE(xc.entered_cr, gb.entered_cr),0))
        /
        CASE
          WHEN xc.application_id IS NOT NULL
           AND xdl.source_distribution_type IN ('AP_INV_DIST','PJC_COST','PJC_XLA_LINES')
           AND COUNT(*) OVER (PARTITION BY xc.application_id, xc.ae_header_id, xc.ae_line_num) > 0
          THEN COUNT(*) OVER (PARTITION BY xc.application_id, xc.ae_header_id, xc.ae_line_num)
          ELSE 1
        END
      ), 2
    ),0) AS DECIMAL(38,2)) AS entered_amount,

    /* Optional: expose allocated DR/CR too (prevents “sum DR” inflation) */
    (NVL(COALESCE(xc.accounted_dr, gb.accounted_dr),0) /
      CASE
        WHEN xc.application_id IS NOT NULL
         AND xdl.source_distribution_type IN ('AP_INV_DIST','PJC_COST','PJC_XLA_LINES')
         AND COUNT(*) OVER (PARTITION BY xc.application_id, xc.ae_header_id, xc.ae_line_num) > 0
        THEN COUNT(*) OVER (PARTITION BY xc.application_id, xc.ae_header_id, xc.ae_line_num)
        ELSE 1
      END
    ) AS accounted_dr,

    (NVL(COALESCE(xc.accounted_cr, gb.accounted_cr),0) /
      CASE
        WHEN xc.application_id IS NOT NULL
         AND xdl.source_distribution_type IN ('AP_INV_DIST','PJC_COST','PJC_XLA_LINES')
         AND COUNT(*) OVER (PARTITION BY xc.application_id, xc.ae_header_id, xc.ae_line_num) > 0
        THEN COUNT(*) OVER (PARTITION BY xc.application_id, xc.ae_header_id, xc.ae_line_num)
        ELSE 1
      END
    ) AS accounted_cr,

    gb.balancing_segment,
    gb.selling_method,
    gb.location_code,
    gb.cost_center_code,
    gb.brand,
    gb.intercompany,
    gb.natural_account,

    gb.natural_account_name,
    gb.natural_account_description,

    gb.account_combination,
    gb.budget_center,

    /* invoice_number */
    COALESCE(
      CASE
        WHEN gb.je_source = 'Receivables'
          THEN COALESCE(rcta.trx_number, rcta_b.trx_number)
        WHEN xdl.source_distribution_type = 'AP_INV_DIST'
          THEN COALESCE(ai.invoice_num, ai_hdr.invoice_num)
        WHEN xdl.source_distribution_type = 'PO_DISTRIBUTIONS_ALL'
          THEN poh.segment1
        WHEN xdl.source_distribution_type = 'RCV_RECEIVING'
          THEN rsh.receipt_num
        WHEN xc.entity_code = 'AP_INVOICES'
          THEN COALESCE(ai_hdr.invoice_num, ai.invoice_num)
        WHEN xc.entity_code IN ('PJC_COST','PJC_XLA_LINES')
          THEN TO_CHAR(peia.expenditure_item_id)
        WHEN xc.entity_code LIKE 'FA%'
          THEN fab.asset_number
        ELSE NULL
      END,
      apf.pay_invoice_number,
      apg.pay_invoice_number_gl,
      apc.pay_invoice_number_chk,
      iby.iby_invoice_number,
      arcr.receipt_number
    ) AS invoice_number,

    CASE
      WHEN COALESCE(ai.payment_status_flag, ai_hdr.payment_status_flag) = 'Y' THEN 'Yes'
      ELSE 'No'
    END AS "Paid?",

    COALESCE(ai.payment_method_code, ai_hdr.payment_method_code) AS payment_method_code,

    /* PO number (UPDATED #1) */
    COALESCE(
      poh.segment1,
      poh_aila.segment1,
      aila.attribute2
    ) AS po_number,

    gb.voucher_number,
    xc.sla_header_description,

    /* invoice_date */
    COALESCE(
      CASE
        WHEN gb.je_source = 'Receivables' THEN
          TO_CHAR(COALESCE(rcta.trx_date, rcta_b.trx_date),'Mon DD, YYYY','NLS_DATE_LANGUAGE=English')
        ELSE
          TO_CHAR(COALESCE(ai.invoice_date, ai_hdr.invoice_date),'Mon DD, YYYY','NLS_DATE_LANGUAGE=English')
      END,
      TO_CHAR(apf.pay_invoice_date,'Mon DD, YYYY','NLS_DATE_LANGUAGE=English'),
      TO_CHAR(apg.pay_invoice_date_gl,'Mon DD, YYYY','NLS_DATE_LANGUAGE=English'),
      TO_CHAR(apc.pay_invoice_date_chk,'Mon DD, YYYY','NLS_DATE_LANGUAGE=English'),
      TO_CHAR(iby.iby_invoice_date,'Mon DD, YYYY','NLS_DATE_LANGUAGE=English'),
      TO_CHAR(arcr.receipt_date,'Mon DD, YYYY','NLS_DATE_LANGUAGE=English')
    ) AS invoice_date,

    CASE
      WHEN gb.je_source = 'Receivables' THEN COALESCE(rctla.description, rctla_b.description)
      ELSE aila.description
    END AS invoice_line_description,

    /* supplier */
    COALESCE(
      CASE
        WHEN gb.je_source = 'Receivables' THEN hp_ar.party_name
        ELSE COALESCE(sup.vendor_name, hp.party_name)
      END,
      apf.pay_supplier,
      apg.pay_supplier_gl,
      apc.pay_supplier_chk,
      iby.iby_supplier,
      arcr.receipt_customer
    ) AS supplier,

    CASE
      WHEN gb.je_source = 'Receivables' THEN hca_ar.account_number
      ELSE COALESCE(sup.segment1, hp.party_number)
    END AS supplier_number,

    COALESCE(proj_pjc.segment1, proj_ap.segment1) AS project_number,
    COALESCE(pjtl_pjc.name,     pjtl_ap.name)     AS project_name,
    COALESCE(ptv_pjc.task_number, ptv_ap.task_number) AS task_number,
    COALESCE(ptv_pjc.task_name,   ptv_ap.task_name)   AS task_name,
    COALESCE(pettl_pjc.expenditure_type_name, pet_ap.expenditure_type_name) AS expenditure_type,

    COALESCE(
      TO_CHAR(peia.expenditure_item_date, 'YYYY-MM-DD'),
      TO_CHAR(pei_ap.expenditure_item_date, 'YYYY-MM-DD'),
      TO_CHAR(aid.pjc_expenditure_item_date, 'YYYY-MM-DD')
    ) AS expenditure_item_date,

    COALESCE(haotl_pjc.name, haotl_ap.name) AS exp_organization_name

  FROM gl_base gb

  LEFT JOIN xla_ctx xc
    ON xc.je_header_id = gb.je_header_id
   AND xc.je_line_num  = gb.je_line_num

  /* UPDATED #2: join ranked XDL with key logic */
  LEFT JOIN xdl_ranked xdl
    ON xdl.application_id = xc.application_id
   AND xdl.ae_header_id   = xc.ae_header_id
   AND xdl.ae_line_num    = xc.ae_line_num
   AND (
        xdl.source_distribution_type IN ('AP_INV_DIST','PJC_COST','PJC_XLA_LINES')
        OR xdl.rn = 1
   )

  /* AP fallbacks */
  LEFT JOIN ap_pay_best apf
    ON apf.je_header_id = gb.je_header_id
   AND apf.je_line_num  = gb.je_line_num
  LEFT JOIN ap_pay_gl_best apg
    ON apg.je_header_id = gb.je_header_id
   AND apg.je_line_num  = gb.je_line_num
  LEFT JOIN ap_pay_gl_check_best apc
    ON apc.je_header_id = gb.je_header_id
   AND apc.je_line_num  = gb.je_line_num

  /* IBY fallback */
  LEFT JOIN iby_gl_txn_best iby
    ON iby.je_header_id = gb.je_header_id
   AND iby.je_line_num  = gb.je_line_num

  /* Receivables receipt fallback */
  LEFT JOIN ar_receipt_best arcr
    ON arcr.je_header_id = gb.je_header_id
   AND arcr.je_line_num  = gb.je_line_num

  /* Receivables joins */
  LEFT JOIN ar_distributions_all ardist
    ON xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
   AND ardist.line_id = xdl.source_distribution_id_num_1

  LEFT JOIN ra_cust_trx_line_gl_dist_all rctlgd
    ON xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
   AND rctlgd.cust_trx_line_gl_dist_id = xdl.source_distribution_id_num_1

  LEFT JOIN ra_cust_trx_line_gl_dist_all rctlgd_ad
    ON xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
   AND rctlgd_ad.cust_trx_line_gl_dist_id = ardist.ref_cust_trx_line_gl_dist_id

  LEFT JOIN ra_customer_trx_all rcta
    ON gb.je_source = 'Receivables'
   AND rcta.customer_trx_id = COALESCE(
        rctlgd.customer_trx_id,
        rctlgd_ad.customer_trx_id,
        CASE WHEN xc.entity_code = 'TRANSACTIONS' THEN xc.source_id_int_1 END
      )

  LEFT JOIN ra_customer_trx_all rcta_b
    ON gb.je_source = 'Receivables'
   AND rcta_b.customer_trx_id =
      CASE
        WHEN REGEXP_LIKE(gb.batch_name, 'Receivables A [0-9]+')
        THEN TO_NUMBER(REGEXP_SUBSTR(gb.batch_name, 'Receivables A ([0-9]+)', 1, 1, NULL, 1))
      END

  LEFT JOIN ra_customer_trx_lines_all rctla
    ON rctla.customer_trx_id = COALESCE(rcta.customer_trx_id, rcta_b.customer_trx_id)
   AND rctla.customer_trx_line_id = COALESCE(
        rctlgd.customer_trx_line_id,
        rctlgd_ad.customer_trx_line_id,
        ardist.ref_customer_trx_line_id
      )

  LEFT JOIN ra_customer_trx_lines_all rctla_b
    ON rctla_b.customer_trx_id = rcta_b.customer_trx_id
   AND rctla_b.line_type = 'LINE'
   AND rctla_b.line_number = 1

  LEFT JOIN hz_cust_accounts hca_ar
    ON hca_ar.cust_account_id = COALESCE(
        rcta.bill_to_customer_id,
        rcta_b.bill_to_customer_id,
        ardist.third_party_id
      )

  LEFT JOIN hz_parties hp_ar
    ON hp_ar.party_id = hca_ar.party_id

  /* Payables / AP joins */
  LEFT JOIN ap_invoices_all ai_hdr
    ON xc.entity_code = 'AP_INVOICES'
   AND ai_hdr.invoice_id = xc.source_id_int_1

  LEFT JOIN ap_invoice_distributions_all aid
    ON xdl.source_distribution_type = 'AP_INV_DIST'
   AND aid.invoice_distribution_id = xdl.source_distribution_id_num_1

  LEFT JOIN ap_invoices_all ai
    ON ai.invoice_id = aid.invoice_id

  LEFT JOIN ap_invoice_lines_all aila
    ON aila.invoice_id = ai.invoice_id
   AND aila.line_number = aid.invoice_line_number

  LEFT JOIN po_headers_all poh_aila
    ON poh_aila.po_header_id = aila.po_header_id

  /* UPDATED #3: AP -> PEI bridge */
  LEFT JOIN pjc_exp_items_all pei_ap
    ON pei_ap.original_header_id = COALESCE(ai.invoice_id, ai_hdr.invoice_id)
   AND pei_ap.original_dist_id   = aid.invoice_distribution_id

  /* PO */
  LEFT JOIN po_distributions_all pod
    ON xdl.source_distribution_type = 'PO_DISTRIBUTIONS_ALL'
   AND pod.po_distribution_id = xdl.source_distribution_id_num_1
  LEFT JOIN po_headers_all poh
    ON poh.po_header_id = pod.po_header_id

  /* Receiving */
  LEFT JOIN rcv_transactions rt
    ON xdl.source_distribution_type = 'RCV_RECEIVING'
   AND rt.transaction_id = xdl.source_distribution_id_num_1
  LEFT JOIN rcv_shipment_headers rsh
    ON rsh.shipment_header_id = rt.shipment_header_id

  /* Fixed Assets */
  LEFT JOIN fa_additions_b fab
    ON xc.entity_code LIKE 'FA%'
   AND fab.asset_id = xc.source_id_int_1

  /* Supplier / party */
  LEFT JOIN hz_parties hp
    ON hp.party_id = COALESCE(ai.party_id, ai_hdr.party_id)
  LEFT JOIN poz_suppliers_v sup
    ON sup.vendor_id = COALESCE(ai.vendor_id, ai_hdr.vendor_id, poh.vendor_id)

  /* PJC cost distribution path */
  LEFT JOIN pjc_cost_dist_lines_all pcdl
    ON pcdl.acct_event_id = xdl.event_id
   AND pcdl.expenditure_item_id = xdl.source_distribution_id_num_1
   AND pcdl.line_num = xdl.source_distribution_id_num_2

  LEFT JOIN pjc_exp_items_all peia
    ON peia.expenditure_item_id = pcdl.expenditure_item_id

  LEFT JOIN pjf_exp_types_tl pettl_pjc
    ON pettl_pjc.expenditure_type_id = peia.expenditure_type_id
   AND pettl_pjc.language = USERENV('LANG')

  LEFT JOIN hr_all_organization_units_tl haotl_pjc
    ON haotl_pjc.organization_id = peia.expenditure_organization_id
   AND haotl_pjc.language = USERENV('LANG')

  LEFT JOIN pjf_projects_all_b proj_pjc
    ON proj_pjc.project_id = pcdl.project_id
  LEFT JOIN pjf_projects_all_tl pjtl_pjc
    ON pjtl_pjc.project_id = proj_pjc.project_id
   AND pjtl_pjc.language = USERENV('LANG')
  LEFT JOIN pjf_tasks_v ptv_pjc
    ON ptv_pjc.task_id = pcdl.task_id
   AND ptv_pjc.project_id = proj_pjc.project_id

  /* AP project path (COALESCE(pei_ap.*, aid.*)) */
  LEFT JOIN pjf_projects_all_b proj_ap
    ON proj_ap.project_id = COALESCE(pei_ap.project_id, aid.pjc_project_id)

  LEFT JOIN pjf_projects_all_tl pjtl_ap
    ON pjtl_ap.project_id = proj_ap.project_id
   AND pjtl_ap.language = USERENV('LANG')

  LEFT JOIN pjf_tasks_v ptv_ap
    ON ptv_ap.task_id    = COALESCE(pei_ap.task_id, aid.pjc_task_id)
   AND ptv_ap.project_id = proj_ap.project_id

  LEFT JOIN pjf_exp_types_tl pet_ap
    ON pet_ap.expenditure_type_id = COALESCE(pei_ap.expenditure_type_id, aid.pjc_expenditure_type_id)
   AND pet_ap.language = USERENV('LANG')

  LEFT JOIN hr_all_organization_units_tl haotl_ap
    ON haotl_ap.organization_id = COALESCE(pei_ap.expenditure_organization_id, aid.pjc_organization_id)
   AND haotl_ap.language = USERENV('LANG')

) master_query
ORDER BY
  LPAD(NVL(TRIM(master_query.natural_account), '0'), 30, '0'),
  master_query.account_combination,
  master_query.batch_name ASC NULLS LAST,
  master_query.line_number ASC

:P_BAL_FROM :P_BAL_TO :P_BRAND_FROM :P_BRAND_TO :P_CC_FROM :P_CC_TO :P_FROM_ACCOUNTING_DATE :P_IC_FROM :P_IC_TO :P_LEDGER_NAME :P_LOCATION_FROM :P_LOCATION_TO :P_NA_FROM :P_NA_TO :P_SELLING_METHOD_FROM :P_SELLING_METHOD_TO :P_TO_ACCOUNTING_DATE

The data-warehouse model — one fact surrounded by conformed dimensions (what you slice by) and measures (what you aggregate), expressed as dbt so it migrates with you. Grain: one row per source transaction.

●— fact → dimension join

Element	Type	Definition
GL_JE_HEADERS	dimension	dimension
GL_CODE_COMBINATIONS	dimension	dimension
GL_LEDGERS	dimension	dimension
Amount	measure	measure

Runs on your cloud warehouse — Snowflake, BigQuery, Redshift, or Synapse on AWS, Google Cloud, Azure, or any provider. Reconciled to the source control total — 0% variance by design. You own the code, the model, and the data.

How the data gets here: a BICC bulk extract of the source tables above, on the same pattern for every report. See the extraction pattern & data flow →

See the complete model

How this report's fact and dimensions fit the full picture, via conformed keys.

General Ledger data model →Enterprise model →

Every source object behind this report. Each linked table has its own page with full column descriptions, drawn from the Oracle BICC lineage and articulated for practitioners.

Table	Reporting columns	Subject areas
GL_JE_LINES	26	2
GL_JE_HEADERS	34	2
GL_CODE_COMBINATIONS	7	61
GL_LEDGERS	10	104

Reporting columns = fields the report selects that are exposed as analytics attributes; subject areas = the OTBI subject areas the table appears in. Setup and configuration tables (master data, ledger and book setup, lookups) are referenced by the report's joins but aren't exposed as analytics columns or subject areas — that's expected, not a gap.