Oracle Cloud: query to identify Tax Rate Variance (TRV) originating from FDC

Business Unit
Ledger
Start date
End date

Invoice
FDC
Purchase Order
Invoice total
FDC total
TRV accounted amount
Accounting combination
Payables status

SOURCE = ORA_CMF

AND ai.source = 'ORA_CMF'

WITH bu AS
(
    SELECT bu_id,
           bu_name,
           TO_NUMBER(primary_ledger_id) primary_ledger_id
      FROM fun_all_business_units_v
     WHERE bu_name = :P_BU_NAME
),
ledger AS
(
    SELECT DISTINCT
           gl.ledger_id,
           gl.name ledger_name
      FROM gl_ledgers gl
      JOIN
           (
               SELECT primary_ledger_id ledger_id
                 FROM bu

               UNION

               SELECT glr.target_ledger_id ledger_id
                 FROM gl_ledger_relationships glr
                 JOIN bu
                   ON bu.primary_ledger_id = glr.primary_ledger_id
           ) bu_ledgers
        ON bu_ledgers.ledger_id = gl.ledger_id
     WHERE gl.name = :P_LEDGER_NAME
),
po_base AS
(
    SELECT DISTINCT
           ail.invoice_id,
           poh.segment1 po_number
      FROM ap_invoice_lines_all ail
      JOIN po_headers_all poh
        ON poh.po_header_id = ail.po_header_id
     WHERE ail.po_header_id IS NOT NULL
),
po_info AS
(
    SELECT invoice_id,
           LISTAGG(po_number, ', ')
             WITHIN GROUP (ORDER BY po_number) po_number
      FROM po_base
     GROUP BY invoice_id
),
fdc_base AS
(
    /* Preferred relationship when the FDC header already stores the AP invoice. */
    SELECT DISTINCT
           fdh.invoice_id,
           fdh.document_header_id,
           fdh.document_number,
           fdh.access_key_number,
           fdh.total_amount
      FROM cmf_fiscal_doc_headers fdh
     WHERE fdh.invoice_id IS NOT NULL

    UNION

    /* Fallback through the source transaction carried to AP invoice lines. */
    SELECT DISTINCT
           ail.invoice_id,
           fdh.document_header_id,
           fdh.document_number,
           fdh.access_key_number,
           fdh.total_amount
      FROM ap_invoice_lines_all ail
      JOIN cmf_fiscal_doc_headers fdh
        ON fdh.document_header_id = ail.source_trx_id
     WHERE ail.source_trx_id IS NOT NULL
),
fdc_info AS
(
    SELECT invoice_id,
           LISTAGG(document_number, ', ')
             WITHIN GROUP (ORDER BY document_number) fdc_number,
           LISTAGG(access_key_number, ', ')
             WITHIN GROUP (ORDER BY access_key_number) fdc_access_key,
           SUM(total_amount) fdc_total_amount
      FROM fdc_base
     GROUP BY invoice_id
),
trv_accounting AS
(
    SELECT xah.event_id,
           xah.accounting_date,
           xah.period_name,
           xah.accounting_entry_status_code,
           xah.gl_transfer_status_code,
           xal.ae_line_num,
           xal.code_combination_id,
           xal.accounting_class_code,
           xal.accounted_dr,
           xal.accounted_cr
      FROM xla_ae_headers xah
      JOIN ledger
        ON ledger.ledger_id = xah.ledger_id
      JOIN xla_ae_lines xal
        ON xal.application_id = xah.application_id
       AND xal.ae_header_id   = xah.ae_header_id
     WHERE xah.application_id = 200
       AND xal.accounting_class_code = 'TRV'
       AND xah.accounting_date >= :P_DATE_FROM
       AND xah.accounting_date <  :P_DATE_TO + 1
)
SELECT TO_CHAR(trv.accounting_date, 'DD/MM/RRRR') accounting_date,
       trv.period_name,
       ai.invoice_num,
       TO_CHAR(ai.invoice_date, 'DD/MM/RRRR') invoice_date,
       ai.invoice_currency_code currency,
       ai.invoice_amount ap_invoice_amount,
       ap_invoices_utility_pkg.get_approval_status
       (
           ai.invoice_id,
           ai.invoice_amount,
           ai.payment_status_flag,
           ai.invoice_type_lookup_code
       ) ap_status,
       ai.wfapproval_status ap_workflow_status,
       ai.payment_status_flag ap_payment_status,
       po.po_number,
       fdc.fdc_number,
       fdc.fdc_access_key,
       fdc.fdc_total_amount,
       NVL(trv.accounted_dr, 0)
         - NVL(trv.accounted_cr, 0) trv_amount,
       gcc.segment1 || '.' ||
       gcc.segment2 || '.' ||
       gcc.segment3 || '.' ||
       gcc.segment4 || '.' ||
       gcc.segment5 || '.' ||
       gcc.segment6 || '.' ||
       gcc.segment7 account_combination,
       trv.accounting_entry_status_code sla_status,
       trv.gl_transfer_status_code gl_transfer_status
  FROM trv_accounting trv
  JOIN ap_invoices_all ai
    ON EXISTS
       (
           SELECT 1
             FROM ap_invoice_distributions_all aid
            WHERE aid.invoice_id = ai.invoice_id
              AND aid.accounting_event_id = trv.event_id
       )
  JOIN bu
    ON bu.bu_id = ai.org_id
  JOIN gl_code_combinations gcc
    ON gcc.code_combination_id = trv.code_combination_id
  LEFT JOIN po_info po
    ON po.invoice_id = ai.invoice_id
  LEFT JOIN fdc_info fdc
    ON fdc.invoice_id = ai.invoice_id
 WHERE ai.source = 'ORA_CMF'
 ORDER BY trv.accounting_date,
          ai.invoice_num,
          trv.ae_line_num

P_BU_NAME
P_LEDGER_NAME
P_DATE_FROM
P_DATE_TO

SELECT bu_name display_value,
       bu_name return_value
  FROM fun_all_business_units_v
 WHERE status = 'A'
 ORDER BY bu_name

SELECT DISTINCT
       gl.name display_value,
       gl.name return_value
  FROM gl_ledgers gl
  JOIN
       (
           SELECT TO_NUMBER(fbu.primary_ledger_id) ledger_id
             FROM fun_all_business_units_v fbu
            WHERE fbu.bu_name = :P_BU_NAME

           UNION

           SELECT glr.target_ledger_id ledger_id
             FROM fun_all_business_units_v fbu
             JOIN gl_ledger_relationships glr
               ON glr.primary_ledger_id = TO_NUMBER(fbu.primary_ledger_id)
            WHERE fbu.bu_name = :P_BU_NAME
       ) bu_ledgers
    ON bu_ledgers.ledger_id = gl.ledger_id
 ORDER BY gl.name

aid.accounting_event_id = trv.event_id

xal.accounting_class_code = 'TRV'

NVL(accounted_dr, 0) - NVL(accounted_cr, 0)

Which FDC-originated invoices
generated Tax Rate Variance
for a specific BU and date range?