My Oracle Support Banner

Accounts Payable Trial Balance (APTBRPT) Report runs long for more than 24 hours (Doc ID 2013078.1)

Last updated on JANUARY 16, 2018

Applies to:

Oracle Payables - Version 12.2.2 and later
Information in this document applies to any platform.
Accounts Payable Trial Balance (APTBRPT) Report runs long for more than 24 hours

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   [https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=8409806]    bug9133956   [https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=9133956]    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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.