R12: Accounts Payable Trial Balance (APTBRPT) Report Runs Long for More Than 24 Hours (Doc ID 2013078.1)

Last updated on APRIL 04, 2025

Applies to:

Symptoms

SQLID: 94zcp9rz1w76c   

INSERT INTO xla_trial_balances_gt (definition_code , ledger_id , ledger_name , ledger_short_name , ledger_currency_code , record_type_code , source_application_id , code_combination_id , acctd_unrounded_orig_amount , acctd_rounded_orig_amount , entered_unrounded_rem_amount , entered_rounded_rem_amount , acctd_unrounded_rem_amount , acctd_rounded_rem_amount , third_party_name , third_party_number , balancing_segment_value , natural_account_segment_value , cost_center_segment_value , intercompany_segment_value , management_segment_value , trx_currency_code) 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_round ed_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_cen ter_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 AND xte.security_id_int_1 = 765 GROUP BY tb.definition_code, tb.ledger_id, tb.source_application_id, tb.code_combination_id, tiv.party_name, tb.party_id, tb.ba lancing_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

Changes

 NONE

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.