Report name: Accounts Payable Trial Balance
The standard summary report for AP Trial Balance is based on below query.
SELECT
summary_dat.definition_code,
summary_dat.ledger_id,
gl.name,
gl.short_name,
gl.currency_code,
''SUMMARY'',
summary_dat.source_application_id,
summary_dat.code_combination_id,
decode(gl.ledger_category_code,''PRIMARY'',summary_dat.SUM_acctd_unrounded_orig_amt ,0),
decode(gl.ledger_category_code,''PRIMARY'',summary_dat.SUM_acctd_rounded_orig_amt,0),
summary_dat.sum_entd_unrounded_rem_amount,
summary_dat.sum_entd_rounded_rem_amount,
summary_dat.sum_acctd_unrounded_rem_amount,
summary_dat.sum_acctd_rounded_rem_amount,
summary_dat.party_name,
summary_dat.party_id,
summary_dat.balancing_segment_value,
summary_dat.natural_account_segment_value,
summary_dat.cost_center_segment_value,
summary_dat.intercompany_segment_value,
summary_dat.management_segment_value,
gl.currency_code
FROM
(
SELECT
tb.definition_code,
tb.ledger_id,
tb.source_application_id,
tb.code_combination_id,
SUM(tb.entered_unrounded_rem_amount) SUM_ENTD_UNROUNDED_REM_AMOUNT,
SUM(tb.entered_rounded_rem_amount) SUM_entd_rounded_rem_amount ,
SUM(tb.acctd_unrounded_rem_amount) SUM_acctd_unrounded_rem_amount ,
SUM(tb.acctd_rounded_rem_amount) SUM_acctd_rounded_rem_amount ,
SUM(nvl(tiv.base_amount,tiv.invoice_amount)) SUM_acctd_unrounded_orig_amt,
SUM(nvl(tiv.base_amount,tiv.invoice_amount)) SUM_acctd_rounded_orig_amt,
tiv.party_name,
tb.party_id,
tb.balancing_segment_value,
tb.natural_account_segment_value,
tb.cost_center_segment_value,
tb.intercompany_segment_value,
tb.management_segment_value
FROM
AP_SLA_INVOICES_TRANSACTION_V tiv,
xla_transaction_entities xte,
-- inline view
( SELECT /*+ parallel(xtb) leading(xtb) NO_MERGE */ --added hint bug#8409806 bug9133956
xtb.definition_code,
nvl(xtb.applied_to_entity_id,xtb.source_entity_id) entity_id,
xtb.code_combination_id ,
xtb.source_application_id,
SUM (Nvl(xtb.entered_unrounded_cr,0)) - SUM (Nvl(xtb.entered_unrounded_dr,0)) entered_unrounded_rem_amount,
SUM (Nvl(xtb.entered_rounded_cr,0)) - SUM (Nvl(xtb.entered_rounded_dr,0)) entered_rounded_rem_amount,
SUM (Nvl(xtb.acctd_unrounded_cr,0)) - SUM (Nvl(xtb.acctd_unrounded_dr,0)) acctd_unrounded_rem_amount,
SUM (Nvl(xtb.acctd_rounded_cr,0)) - SUM (Nvl(xtb.acctd_rounded_dr,0)) acctd_rounded_rem_amount,
xtb.ledger_id,
xtb.party_id,
xtb.balancing_segment_value,
xtb.natural_account_segment_value,
xtb.cost_center_segment_value,
xtb.intercompany_segment_value,
xtb.management_segment_value
FROM xla_trial_balances xtb
where xtb.definition_code = :1
and xtb.source_application_id=200
and xtb.gl_date between :2 and :3
AND NVL(xtb.party_id,-99) = NVL(:4,NVL(xtb.party_id,-99))
GROUP BY xtb.definition_code,
nvl(xtb.applied_to_entity_id,xtb.source_entity_id) ,
xtb.code_combination_id ,
xtb.source_application_id,
xtb.ledger_id,
xtb.party_id,
xtb.balancing_segment_value,
xtb.natural_account_segment_value,
xtb.cost_center_segment_value,
xtb.intercompany_segment_value,
xtb.management_segment_value
HAVING SUM (Nvl(xtb.acctd_rounded_cr,0)) <> SUM (Nvl(xtb.acctd_rounded_dr,0))
) tb
--end of inline view
WHERE tb.entity_id=xte.entity_id
AND tb.source_application_id=200
AND xte.entity_code=''AP_INVOICES''
AND xte.application_id=tb.source_application_id
AND nvl(xte.source_id_int_1,-99)=tiv.invoice_id
GROUP BY
tb.definition_code, tb.ledger_id, tb.source_application_id,
tb.code_combination_id, tiv.party_name,tb.party_id,
tb.balancing_segment_value, tb.natural_account_segment_value,
tb.cost_center_segment_value, tb.intercompany_segment_value,
tb.management_segment_value
) summary_dat ,
gl_ledgers gl
WHERE summary_dat.ledger_id=gl.ledger_id