Oracle Fusion · Receivables
Greece Customer Balance by Account Report
Receivables
The Greek customer balance by general ledger account — each customer's receivable balance broken out by the account it posts to, the customer-side statutory subsidiary ledger that supports the Greek books.
Related Consolidates the customer-balance and subledger-balance-by-account views into one.
Run note · Performance The subledger (XLA) join — especially Projects-to-XLA — is a known BI Publisher performance trap on large ledgers. Pre-join it in the warehouse, or apply tight ledger, period, and source filters before running online.
Sample build of the Greece Customer Balance by Account Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
Greece Customer Balance by Account Report
Sample build · illustrativeFilters
Cust Name
Globex Holdings
From Acc
Sample
From Brand
Sample
From Cc
Sample
From Entity
Globex Holdings
From Future1
Sample
680
Customers
$9.40M
Open AR
$0 diff
Ties to GL
| Customer | Gl Account | Open Balance | Period | Currency |
|---|---|---|---|---|
| Acme Industrial | 1000-2100-000 | $1,240,500.00 | APR-26 | USD |
| Northwind Trading | 1000-5400-000 | $842,150.75 | MAR-26 | USD |
| Globex Holdings | 1000-1410-000 | $96,400.00 | FEB-26 | USD |
| Initech LLC | 2000-2100-000 | $1,005,233.10 | JAN-26 | USD |
| Umbrella Corp | 1000-6300-000 | $58,720.40 | DEC-25 | USD |
| Acme Industrial | 1000-2100-000 | $1,240,500.00 | APR-26 | USD |
AI Analyst · active
reading
The report rolls each customer's open balance to its GL account in the Greek statutory subsidiary-ledger layout.
flag
It ties to GL except where a manual journal hit the receivable control account directly, bypassing AR.
root cause & next step
Keep manual journals off the AR control account — let Receivables post it — so the customer subledger ties exactly.
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.
Show / hide SQL
-- CUSTOM Customer Balances by Account Greece Report
WITH BEG_BAL AS
(
SELECT
GLL.ledger_id
, hca.cust_account_id
, GCC.segment4 NAT_ACCOUNT
, SUM(XAL.accounted_DR) BEG_BAL_DR
, SUM(XAL.accounted_CR) BEG_BAL_CR
, HCA.account_number c_NUM
, HP.party_name c_NAME
, FT.description ACC_DESC
FROM
gl_ledgers GLL
, xla_ae_lines XAL
, xla_ae_headers XAH
, xla_transaction_entities XTE
, xla_subledgers XS
, gl_code_combinations GCC
, gl_periods GLP
, hz_cust_accounts hca
, hz_parties HP
, fnd_flex_values FFV
, fnd_flex_values_tl FT
WHERE
1 =1
AND GLL.ledger_id =XAH.ledger_id
AND XAL.code_combination_id =GCC.code_combination_id
AND GLP.period_name =XAH.period_name
AND GLP.period_type =GLL.accounted_period_type
AND XAL.ae_header_id =XAH.ae_header_id
AND XAH.application_id =XS.application_id
AND xal.party_id =hca.cust_account_id
AND XAH.application_id =XTE.application_id
AND XAH.entity_id =XTE.entity_id
--And XAL.ACCOUNTING_CLASS_CODE = 'RECEIVABLE'
AND (XTE.entity_code ='TRANSACTIONS' or XTE.entity_code ='RECEIPTS' or XTE.entity_code = 'ADJUSTMENTS')
AND XS.JE_Source_name ='Receivables'
AND XAH.accounting_entry_status_code='F'
AND XAH.GL_TRANSFER_STATUS_CODE ='Y'
--AND xal.control_balance_flag = 'Y'
AND GLP.start_date < (select distinct start_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_FROM_PERIOD AND adjustment_period_flag ='N')
AND HCA.party_id =HP.party_id
and gll.name = 'PL GREECE EUR LOCAL'
AND GCC.segment4 =FFV.flex_value
AND FFV.value_category ='GREECE ACCOUNT VALUE SET'
AND FFV.flex_value_id =FT.flex_value_id
AND UPPER(FT.language) ='EL'
AND CAST(GCC.segment9 AS NUMERIC) BETWEEN :P_FROM_FUTURE2 AND :P_TO_FUTURE2
AND CAST(GCC.segment8 AS NUMERIC) BETWEEN :P_FROM_FUTURE1 AND :P_TO_FUTURE1
AND CAST(GCC.segment7 AS NUMERIC) BETWEEN :P_FROM_IC AND :P_TO_IC
AND CAST(GCC.segment6 AS NUMERIC) BETWEEN :P_FROM_BRAND AND :P_TO_BRAND
AND CAST(GCC.segment5 AS NUMERIC) BETWEEN :P_FROM_LOC AND :P_TO_LOC
AND CAST(GCC.segment4 AS NUMERIC) BETWEEN CAST(:P_FROM_ACC AS NUMERIC) AND CAST(:P_TO_ACC AS NUMERIC)
AND CAST(GCC.segment3 AS NUMERIC) BETWEEN :P_FROM_CC AND :P_TO_CC
AND CAST(GCC.segment2 AS NUMERIC) BETWEEN :P_FROM_SM AND :P_TO_SM
AND CAST(GCC.segment1 AS NUMERIC) BETWEEN :P_FROM_ENTITY AND :P_TO_ENTITY
AND (HCA.account_number BETWEEN NVL(:P_CUST_NAME,HCA.account_number) AND NVL(:P_CUST_NAME,HCA.account_number))
--AND (HCA.account_number = :P_CUST_NAME)
GROUP BY
GLL.ledger_id
, hca.cust_account_id
, GCC.segment4
, HCA.account_number
, HP.party_name
, FT.description
)
, SECURITY_TBL AS
(
SELECT
DISTINCT HOU.organization_id
FROM
hr_operating_units HOU,
fun_user_role_data_asgnmnts FURDA,
per_roles_dn PRD,
per_user_roles PUR,
per_users PU
,fusion.per_roles_dn_vl pr
,fusion.per_roles_dn_tl prtl
WHERE
1 =1
AND FURDA.org_id =HOU.organization_id
AND UPPER(FURDA.active_flag) ='Y'
AND UPPER(FURDA.role_name) =UPPER(PRD.role_common_name)
AND PRD.role_id =PUR.role_id
AND PUR.user_id =PU.user_id
AND upper(pu.username) =upper(:xdo_user_name)
AND pu.user_guid =FURDA.user_guid
and pr.role_common_name = FURDA.ROLE_NAME
and pr.role_id=prtl.role_id
and prtl.LANGUAGE='US'
and prtl.ROLE_NAME in ('XXC Accounts Receivable Analyst'
,'XXC Accounts Receivable Manager'
,'XXC Receivables Inquiry'
,'XXC Billing Analyst'
,'XXC Billing Manager')
and HOU.NAME='GR BU EUR'
)
SELECT
ACCOUNT
, DESCRIPTION
, C_NUM
, C_NAME
, PP_DR
, PP_CR
, EPPBD
, EPPBC
, CP_DR
, CP_CR
, TSBD
, TSBC
, TEPBD
, TEPBC
, T_PP_DR
, T_PP_CR
, T_CP_DR
, T_CP_CR
FROM
(
SELECT /*+parallel(12)*/
ACCOUNT
, DESCRIPTION
, C_NUM
, C_NAME
, PP_DR
, PP_CR
, EPPBD
, EPPBC
, CP_DR
, CP_CR
, TSBD
, TSBC
, TEPBD
, TEPBC
, SUM(PP_DR) OVER(PARTITION BY ACCOUNT) T_PP_DR
, SUM(PP_CR) OVER(PARTITION BY ACCOUNT) T_PP_CR
, SUM(CP_DR) OVER(PARTITION BY ACCOUNT) T_CP_DR
, SUM(CP_CR) OVER(PARTITION BY ACCOUNT) T_CP_CR
FROM
(
select
nvl(a.NAT_ACCOUNT , bb.NAT_ACCOUNT) ACCOUNT
, nvl(a.description , bb.ACC_DESC) description
, nvl(a.C_NUM , bb.C_NUM ) C_NUM
, nvl(a.C_NAME , bb.C_NAME) C_NAME
, nvl(BB.BEG_BAL_DR,0) PP_DR
, nvl(BB.BEG_BAL_CR,0) PP_CR
, CASE WHEN nvl(bb.BEG_BAL_DR,0) - nvl(bb.BEG_BAL_CR,0) >0 THEN nvl(bb.BEG_BAL_DR,0) - nvl(BEG_BAL_CR,0) ELSE 0 END EPPBD
, CASE WHEN nvl(bb.BEG_BAL_DR,0) - nvl(bb.BEG_BAL_CR,0) <0 THEN nvl(bb.BEG_BAL_DR,0) - nvl(BEG_BAL_CR,0) ELSE 0 END EPPBC
, nvl(a.CP_DR,0) CP_DR
, nvl(a.CP_CR,0) CP_CR
, nvl(bb.BEG_BAL_DR,0) + nvl(a.CP_DR,0) TSBD
, nvl(bb.BEG_BAL_CR,0) + nvl(a.CP_CR,0) TSBC
, CASE WHEN (nvl(bb.BEG_BAL_DR,0) + nvl(a.CP_DR,0))-(nvl(bb.BEG_BAL_CR,0) + nvl(a.CP_CR,0))>0 THEN (nvl(bb.BEG_BAL_DR,0) + nvl(a.CP_DR,0))-(nvl(bb.BEG_BAL_CR,0) + nvl(a.CP_CR,0)) ELSE 0 END TEPBD
, CASE WHEN (nvl(bb.BEG_BAL_DR,0) + nvl(a.CP_DR,0))-(nvl(bb.BEG_BAL_CR,0) + nvl(a.CP_CR,0))<0 THEN (nvl(bb.BEG_BAL_DR,0) + nvl(a.CP_DR,0))-(nvl(bb.BEG_BAL_CR,0) + nvl(a.CP_CR,0)) ELSE 0 END TEPBC
from
(
select
C_NUM
, C_NAME
, cust_account_id
, LID
, NAT_ACCOUNT
, description
, SUM(CURR_PER_DR) CP_DR
, SUM(CURR_PER_CR) CP_CR
from
(
SELECT
XEP.name LE_NAME
, GLL.ledger_id LID
, HCA.account_number C_NUM
, HP.party_name C_NAME
, hca.cust_account_id
, GCC.segment1 ENITY
, GCC.segment2 SM
, GCC.segment3 CC
, GCC.segment4 NAT_ACCOUNT
, FT.description
, GCC.segment5 LOCATION
, GCC.segment6 BRAND
, GCC.segment7 INTERCOMP
, GCC.segment8 FUTURE1
, GCC.segment9 FUTURE2
, NVL(XAL.accounted_DR,0) CURR_PER_DR
, NVL(XAL.accounted_CR,0) CURR_PER_CR
FROM
gl_ledgers GLL
, gl_je_headers GJH
, gl_je_lines GJL
, gl_je_batches GJB
--, gl_je_sources_tl GJS
--, gl_je_categories_tl GJC
, gl_code_combinations GCC
, gl_periods GLP
, fnd_flex_values FFV
, fnd_flex_values_tl FT
, gl_import_references GIR
, xla_ae_lines XAL
, xla_ae_headers XAH
, xla_transaction_entities XTE
, ra_customer_trx_all rcta
, hz_cust_accounts hca
, hz_parties HP
, hr_operating_units HOU
, xle_entity_profiles XEP
, security_tbl ST
WHERE
1 =1
AND GLL.ledger_id =GJH.ledger_id
AND GJH.je_batch_id =GJB.je_batch_id
AND GJH.je_header_id =GJL.je_header_id
AND GJL.code_combination_id =GCC.code_combination_id
AND GLP.period_name =GJB.default_period_name
AND GLP.period_type =GLL.accounted_period_type
AND GCC.segment4 =FFV.flex_value
AND FFV.value_category ='GREECE ACCOUNT VALUE SET'
AND FFV.flex_value_id =FT.flex_value_id
AND UPPER(FT.language) ='EL'
AND GJL.je_header_id =GIR.je_header_id
AND GJL.je_line_num =GIR.je_line_num
AND GIR.gl_sl_link_id =XAL.gl_sl_link_id
AND GIR.gl_sl_link_table =XAL.gl_sl_link_table
AND XAL.ae_header_id =XAH.ae_header_id
AND XAH.application_id =XTE.application_id
AND XAH.entity_id =XTE.entity_id
AND XTE.source_id_int_1 = rcta.customer_trx_id
AND hca.cust_account_id = rcta.bill_to_customer_id
AND HCA.party_id =HP.party_id
AND HP.PARTY_TYPE ='ORGANIZATION'
and rcta.ORG_ID =HOU.organization_id
and rcta.legal_entity_id =XEP.legal_entity_id
AND ST.organization_id =HOU.organization_id
AND GJH.je_source ='Receivables'
AND XTE.entity_code ='TRANSACTIONS'
--And XAL.ACCOUNTING_CLASS_CODE = 'RECEIVABLE'
AND XAH.accounting_entry_status_code='F'
AND XAH.GL_TRANSFER_STATUS_CODE ='Y'
AND HOU.NAME ='GR BU EUR'
AND GLP.start_date >= (select distinct start_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_FROM_PERIOD)
AND GLP.end_date <= (select distinct end_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_TO_PERIOD)
AND CAST(GCC.segment9 AS NUMERIC) BETWEEN :P_FROM_FUTURE2 AND :P_TO_FUTURE2
AND CAST(GCC.segment8 AS NUMERIC) BETWEEN :P_FROM_FUTURE1 AND :P_TO_FUTURE1
AND CAST(GCC.segment7 AS NUMERIC) BETWEEN :P_FROM_IC AND :P_TO_IC
AND CAST(GCC.segment6 AS NUMERIC) BETWEEN :P_FROM_BRAND AND :P_TO_BRAND
AND CAST(GCC.segment5 AS NUMERIC) BETWEEN :P_FROM_LOC AND :P_TO_LOC
AND CAST(GCC.segment4 AS NUMERIC) BETWEEN CAST(:P_FROM_ACC AS NUMERIC) AND CAST(:P_TO_ACC AS NUMERIC)
AND CAST(GCC.segment3 AS NUMERIC) BETWEEN :P_FROM_CC AND :P_TO_CC
AND CAST(GCC.segment2 AS NUMERIC) BETWEEN :P_FROM_SM AND :P_TO_SM
AND CAST(GCC.segment1 AS NUMERIC) BETWEEN :P_FROM_ENTITY AND :P_TO_ENTITY
AND (HCA.account_number BETWEEN NVL(:P_CUST_NAME,HCA.account_number) AND NVL(:P_CUST_NAME,HCA.account_number))
AND (XEP.name IN (:P_LE_NAME) OR 'All' IN (:P_LE_NAME||'All'))
-- and rcta.trx_number='5100365'
UNION ALL -- Invoice_adjustments
SELECT
null LE_NAME
, GLL.ledger_id LID
, HCA.account_number C_NUM
, HP.party_name C_NAME
, hca.cust_account_id
, GCC.segment1 ENITY
, GCC.segment2 SM
, GCC.segment3 CC
, GCC.segment4 NAT_ACCOUNT
, FT.description ACC_DESC
, GCC.segment5 LOCATION
, GCC.segment6 BRAND
, GCC.segment7 INTERCOMP
, GCC.segment8 FUTURE1
, GCC.segment9 FUTURE2
, nvl(XAL.accounted_DR,0) accounted_DR
, nvl(XAL.accounted_CR,0) accounted_CR
FROM
gl_ledgers GLL
, gl_je_headers GJH
, gl_je_lines GJL
, gl_je_batches GJB
--, gl_je_sources GJS
--, gl_je_categories_tl GJC
, gl_code_combinations GCC
, gl_periods GLP
, fnd_flex_values FFV
, fnd_flex_values_tl FT
, gl_import_references GIR
, xla_ae_lines XAL
, xla_ae_headers XAH
, xla_transaction_entities XTE
, ar_adjustments_all adj
, hz_cust_accounts hca
, hz_parties HP
, hr_operating_units HOU
--, xle_entity_profiles XEP
, xla_event_types_tl xet
, xla_event_classes_tl xect
, security_tbl ST
WHERE
1 =1
AND GLL.ledger_id =GJH.ledger_id
AND GJH.je_batch_id =GJB.je_batch_id
AND GJH.je_header_id =GJL.je_header_id
AND GJL.code_combination_id =GCC.code_combination_id
AND GLP.period_name =GJB.default_period_name
AND GLP.period_type =GLL.accounted_period_type
AND GCC.segment4 =FFV.flex_value
AND FFV.value_category ='GREECE ACCOUNT VALUE SET'
AND FFV.flex_value_id =FT.flex_value_id
AND UPPER(FT.language) ='EL'
AND GJL.je_header_id =GIR.je_header_id
AND GJL.je_line_num =GIR.je_line_num
AND GIR.gl_sl_link_id =XAL.gl_sl_link_id
AND GIR.gl_sl_link_table =XAL.gl_sl_link_table
AND XAL.ae_header_id =XAH.ae_header_id
AND XAH.application_id =XTE.application_id
AND XAH.entity_id =XTE.entity_id
AND XTE.source_id_int_1 = adj.adjustment_id
AND hca.cust_account_id = xal.party_id
--AND hca.status = 'A'
AND HCA.party_id =HP.party_id
AND HP.PARTY_TYPE ='ORGANIZATION'
and adj.ORG_ID = HOU.organization_id
--and rcta.legal_entity_id =XEP.legal_entity_id
AND xet.application_id =xah.application_id
AND xet.event_type_code =xah.event_type_code
AND xet.LANGUAGE ='EL'
AND xect.application_id = xet.application_id
AND xect.entity_code = xet.entity_code
AND xect.event_class_code = xet.event_class_code
AND xect.LANGUAGE = 'EL'
AND ST.organization_id =HOU.organization_id
--AND XAL.ACCOUNTING_CLASS_CODE = 'RECEIVABLE'
AND GJH.je_source ='Receivables'
AND XTE.entity_code ='ADJUSTMENTS'
AND XAH.accounting_entry_status_code='F'
AND XAH.GL_TRANSFER_STATUS_CODE ='Y'
--AND xal.control_balance_flag = 'Y'
AND HOU.NAME ='GR BU EUR'
AND GLP.start_date >= (select distinct start_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_FROM_PERIOD)
AND GLP.end_date <= (select distinct end_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_TO_PERIOD)
-- AND (XEP.name IN (:P_LE_NAME) OR 'All' IN (:P_LE_NAME||'All'))
AND CAST(GCC.segment9 AS NUMERIC) BETWEEN :P_FROM_FUTURE2 AND :P_TO_FUTURE2
AND CAST(GCC.segment8 AS NUMERIC) BETWEEN :P_FROM_FUTURE1 AND :P_TO_FUTURE1
AND CAST(GCC.segment7 AS NUMERIC) BETWEEN :P_FROM_IC AND :P_TO_IC
AND CAST(GCC.segment6 AS NUMERIC) BETWEEN :P_FROM_BRAND AND :P_TO_BRAND
AND CAST(GCC.segment5 AS NUMERIC) BETWEEN :P_FROM_LOC AND :P_TO_LOC
AND CAST(GCC.segment4 AS NUMERIC) BETWEEN CAST(:P_FROM_ACC AS NUMERIC) AND CAST(:P_TO_ACC AS NUMERIC)
AND CAST(GCC.segment3 AS NUMERIC) BETWEEN :P_FROM_CC AND :P_TO_CC
AND CAST(GCC.segment2 AS NUMERIC) BETWEEN :P_FROM_SM AND :P_TO_SM
AND CAST(GCC.segment1 AS NUMERIC) BETWEEN :P_FROM_ENTITY AND :P_TO_ENTITY
AND (HCA.account_number BETWEEN NVL(:P_CUST_NAME,HCA.account_number) AND NVL(:P_CUST_NAME,HCA.account_number))
--AND (HCA.account_number = :P_CUST_NAME)
-- and adj.ADJUSTMENT_NUMBER='20002'
-- Receipts
UNION ALL
SELECT
XEP.name LE_NAME
, GLL.ledger_id LID
, HCA.account_number C_NUM
, HP.party_name C_NAME
, hca.cust_account_id
, GCC.segment1 ENITY
, GCC.segment2 SM
, GCC.segment3 CC
, GCC.segment4 NAT_ACCOUNT
, FT.description
, GCC.segment5 LOCATION
, GCC.segment6 BRAND
, GCC.segment7 INTERCOMP
, GCC.segment8 FUTURE1
, GCC.segment9 FUTURE2
, NVL(XAL.accounted_DR,0) CURR_PER_DR
, NVL(XAL.accounted_CR,0) CURR_PER_CR
FROM
gl_ledgers GLL
, gl_je_headers GJH
, gl_je_lines GJL
, gl_je_batches GJB
--, gl_je_sources_tl GJS
--, gl_je_categories_tl GJC
, gl_code_combinations GCC
, gl_periods GLP
, fnd_flex_values FFV
, fnd_flex_values_tl FT
, gl_import_references GIR
, xla_ae_lines XAL
, xla_ae_headers XAH
, xla_transaction_entities XTE
, ar_cash_receipts_all ACRA
, hz_cust_accounts HCA
, hz_parties HP
, hr_operating_units HOU
, xle_entity_profiles XEP
, security_tbl ST
WHERE
1 =1
AND GLL.ledger_id =GJH.ledger_id
AND GJH.je_batch_id =GJB.je_batch_id
AND GJH.je_header_id =GJL.je_header_id
AND GJL.code_combination_id =GCC.code_combination_id
AND GLP.period_name =GJB.default_period_name
AND GLP.period_type =GLL.accounted_period_type
AND GCC.segment4 =FFV.flex_value
AND FFV.value_category ='GREECE ACCOUNT VALUE SET'
AND FFV.flex_value_id =FT.flex_value_id
AND UPPER(FT.language) ='EL'
AND GJL.je_header_id =GIR.je_header_id
AND GJL.je_line_num =GIR.je_line_num
AND GIR.gl_sl_link_id =XAL.gl_sl_link_id
AND GIR.gl_sl_link_table =XAL.gl_sl_link_table
AND XAL.ae_header_id =XAH.ae_header_id
AND XAH.application_id =XTE.application_id
AND XAH.entity_id =XTE.entity_id
AND XAL.party_id =HCA.cust_account_id
AND xte.source_id_int_1 =ACRA.cash_receipt_id
AND ACRA.PAY_FROM_CUSTOMER =HCA.cust_account_id
AND HCA.party_id =HP.party_id
AND HP.PARTY_TYPE ='ORGANIZATION'
--AND ACRA.org_id =HCSU.org_id
AND ACRA.org_id =HOU.organization_id
AND ACRA.legal_entity_id =XEP.legal_entity_id
AND ST.organization_id =HOU.organization_id
AND GJH.je_source ='Receivables'
AND XTE.entity_code = 'RECEIPTS'
AND HOU.NAME ='GR BU EUR'
AND XAH.accounting_entry_status_code='F'
AND XAH.GL_TRANSFER_STATUS_CODE ='Y'
AND GLP.start_date >= (select distinct start_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_FROM_PERIOD)
AND GLP.end_date <= (select distinct end_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_TO_PERIOD)
AND CAST(GCC.segment9 AS NUMERIC) BETWEEN :P_FROM_FUTURE2 AND :P_TO_FUTURE2
AND CAST(GCC.segment8 AS NUMERIC) BETWEEN :P_FROM_FUTURE1 AND :P_TO_FUTURE1
AND CAST(GCC.segment7 AS NUMERIC) BETWEEN :P_FROM_IC AND :P_TO_IC
AND CAST(GCC.segment6 AS NUMERIC) BETWEEN :P_FROM_BRAND AND :P_TO_BRAND
AND CAST(GCC.segment5 AS NUMERIC) BETWEEN :P_FROM_LOC AND :P_TO_LOC
AND CAST(GCC.segment4 AS NUMERIC) BETWEEN CAST(:P_FROM_ACC AS NUMERIC) AND CAST(:P_TO_ACC AS NUMERIC)
AND CAST(GCC.segment3 AS NUMERIC) BETWEEN :P_FROM_CC AND :P_TO_CC
AND CAST(GCC.segment2 AS NUMERIC) BETWEEN :P_FROM_SM AND :P_TO_SM
AND CAST(GCC.segment1 AS NUMERIC) BETWEEN :P_FROM_ENTITY AND :P_TO_ENTITY
AND (HCA.account_number BETWEEN NVL(:P_CUST_NAME,HCA.account_number) AND NVL(:P_CUST_NAME,HCA.account_number))
AND (XEP.name IN (:P_LE_NAME) OR 'All' IN (:P_LE_NAME||'All'))
)
group by
C_NUM
,C_NAME
,cust_account_id
,LID
,NAT_ACCOUNT
,description
) a
FULL OUTER JOIN BEG_BAL BB ON a.cust_account_id = BB.cust_account_id
AND a.LID = BB.ledger_id
AND a.NAT_ACCOUNT = BB.NAT_ACCOUNT
)
)
WHERE
(CASE WHEN :P_INC_BAL='Yes' THEN 'Yes'
WHEN TSBD-TSBC<>0 AND :P_INC_BAL='No' THEN 'No' END)=NVL(:P_INC_BAL,'Yes')
ORDER BY
CAST(ACCOUNT AS INT),C_NAME:P_CUST_NAME :P_FROM_ACC :P_FROM_BRAND :P_FROM_CC :P_FROM_ENTITY :P_FROM_FUTURE1 :P_FROM_FUTURE2 :P_FROM_IC :P_FROM_LOC :P_FROM_PERIOD :P_FROM_SM :P_INC_BAL :P_LE_NAME :P_TO_ACC :P_TO_BRAND :P_TO_CC :P_TO_ENTITY :P_TO_FUTURE1 :P_TO_FUTURE2 :P_TO_IC :P_TO_LOC :P_TO_PERIOD :P_TO_SM :xdo_user_name
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 |
|---|---|---|
| RA_CUSTOMER_TRX_ALL | dimension | dimension |
| XLA_AE_LINES | dimension | dimension |
| GL_CODE_COMBINATIONS | dimension | dimension |
| HZ_CUST_ACCOUNTS | dimension | dimension |
| Open Balance | 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.
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 |
|---|---|---|
| AR_PAYMENT_SCHEDULES_ALL | 32 | 6 |
| RA_CUSTOMER_TRX_ALL | 58 | 16 |
| XLA_AE_LINES | 23 | 17 |
| GL_CODE_COMBINATIONS | 7 | 61 |
| HZ_CUST_ACCOUNTS | 14 | 43 |
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.