Performance Issue: Open Account Balances Data Manager (XLATBDMG) Does Not Finish (Doc ID 1412915.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Payables - Version: 12.0.6 and later   [Release: 12.0 and later ]
Information in this document applies to any platform.

Symptoms

On : 12.0.6 version,

ACTUAL BEHAVIOR
---------------
Open Account Balances Data Manager does not complete.

When trying to change the Liability Account setup in the Open Account Balance Listing Setups page,
the system automatically kicks off the 'Open Account Balances Data Manager' program.
However, this program is a Runaway program and does not complete.


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Navigate to SubLedger Accounting Setups
2. Open Account Balances Listing Definitions Page
3. Query for Liability Account Setup
4. Add a new account
5. Click Apply.
6. Open Account Balances Data Manager program is kicked off.
   it also spans 32 worker processes, none of them finish.


The AWR report generated shows that these SQLs are running for long time:


BEGIN XLA_TB_DATA_MANAGER_PVT.WORKER_PROCESS(:errbuf, :rc, :A0, :A1, :A2, :A3, :A4); END;


INSERT INTO xla_trial_balances xtb( record_type_code , source_entity_id , event_class_code , source_application_id ,
applied_to_entity_id , applied_to_application_id , gl_date , trx_currency_code , entered_rounded_dr , entered_rounded_cr , entered_unrounded_dr , entered_unrounded_cr , acctd_rounded_dr , acctd_rounded_cr , acctd_unrounded_dr , acctd_unrounded_cr , code_combination_id , balancing_segment_value , natural_account_segment_value , cost_center_segment_value , intercompany_segment_value , management_segment_value , ledger_id , definition_code , party_id , party_site_id , party_type_code , ae_header_id , generated_by_code , creation_date , created_by , last_update_date , last_updated_by , last_update_login , request_id , program_application_id , program_id , program_update_date) SELECT /*+ index(xah XLA_AE_HEADERS_U1) */ DECODE(xet.event_class_code, 'PREPAYMENT APPLICATIONS', 'APPLIED', DECODE(xteu.entity_id, xah.entity_id, 'SOURCE', 'APPLIED')) record_type_code , xah.entity_id source_entity_id , xet.event_class_code event_class_code , xah.applicatio n_id source_application_id , DECODE(xet.event_class_code, 'PREPAYMENT APPLICATIONS', xteu.entity_id, DECODE(xteu.entity_id, xah.entity_id, NULL, xteu.entity_id)) applied_to_entity_id , 200 applied_to_application_id , trunc(xah.accounting_date) gl_date , xal.currency_code trx_currency_code , SUM(NVL(xal.entered_dr, 0)) entered_rounded_dr , SUM(NVL(xal.entered_cr, 0)) entered_rounded_cr , SUM(NVL(xal.entered_dr, 0)) entered_unrounded_dr , SUM(NVL(xal.entered_cr, 0)) entered_unrounded_cr , SUM(NVL(alb.accounted_dr, 0)) acctd_rounded_dr , SUM(NVL(alb.accounted_cr, 0)) acctd_rounded_cr , SUM(NVL(alb.accounted_dr, 0)) acctd_unrounded_dr , SUM(NVL(alb.accounted_cr, 0)) acctd_unrounded_cr , xal.code_combination_id code_combination_id , DECODE(fsav.balancing_segment, 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3, 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6, 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9, 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12, 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15, 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18, 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21, 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24, 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27, 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30, null) balancing_segment_value , DECODE(fsav.account_segment, 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3, 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6, 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9, 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12, 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15, 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18, 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21, 'SEGMENT22', gcc.segment22, ' SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24, 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27, 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30, null) natural_account_segment_value , DECODE(fsav.cost_crt_segment, 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3, 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6, 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9, 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12, 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15, 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18, 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21, 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24, 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27, 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30, null) c ost_center_segment_value , DECODE(fsav.intercompany_segment, 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3, 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6, 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9, 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12, 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15, 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18, 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21, 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24, 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27, 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30, null) intercompany_segment_value , DECODE(fsav.management_segment, 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3, 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6, 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, ' SEGMENT9', gcc.segment9, 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12, 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15, 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18, 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21, 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24, 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27, 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30, null) management_segment_value , xah.ledger_id ledger_id , xtd.definition_code DEFINITION_code , xal.party_id party_id , xal.party_site_id party_site_id , xal.party_type_code party_type_code , xah.ae_header_id ae_header_id , 'SYSTEM' generated_by_code , SYSDATE creation_date , -1 created_by , SYSDATE last_update_date , -1 last_updated_by , -1 last_update_login , -1 request_id , -1 program_application_id , -1 program_id , SYSDATE program_update_date FROM ap_liability_balance alb , xla_ae_headers PARTITION (AP) xah , xla_event_types_b xet , xla_tb_definitions_b xtd , xla_subledgers xsu , xla_tb_defn_je_sources xjs , xla_tb_defn_details xdd , xla_transaction_entities_upg PARTITION (AP) xteu , xla_ae_lines PARTITION (AP) xal , gl_code_combinations gcc , ( SELECT /*+ NO_MERGE*/ id_flex_num , MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, 'GL_BALANCING', application_column_name, NULL)) balancing_segment , MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, 'GL_ACCOUNT', application_column_name, NULL)) account_segment , MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, 'FA_COST_CTR', application_column_name, NULL)) cost_crt_segment , MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, 'GL_INTERCOMPANY', application_column_name, NULL)) intercompany_segment , MAX(DECODE(S EGMENT_ATTRIBUTE_TYPE, 'GL_MANAGEMENT', application_column_name, NULL)) management_segment FROM fnd_segment_attribute_values fsav1 -- Need alias here also. WHERE application_id = 101 AND id_flex_code = 'GL#' AND attribute_value = 'Y' GROUP BY id_flex_num) fsav WHERE xah.gl_transfer_status_code IN ('Y', 'NT') AND xah.application_id = xal.application_id AND xah.ae_header_id BETWEEN :1 AND :2 AND xah.application_id = 200 AND xah.ledger_id = :3 AND xah.upg_batch_id IS NOT NULL AND xah.ae_header_id = xal.ae_header_id AND xal.code_combination_id = gcc.code_combination_id AND xal.code_combination_id = alb.code_combination_id AND xah.application_id = xet.application_id AND xteu.application_id = 200 AND xteu.entity_code = 'AP_INVOICES' AND NVL(xteu.source_id_int_1, -99) = alb.invoice_id --AND xteu.ledger_id = alb.set_of_books_id AND xteu.ledger_id = 1 AND alb.sle_header_id IS NOT NULL AND alb.sle_line_num IS NOT NULL AND alb.sle_header_id = xah.completion_acct_seq_value AND alb.journal_sequence_id = xah.completion_acct_seq_version_id AND alb.sle_line_num = xal.ae_line_num AND xah.upg_source_application_id = 600 AND xah.upg_batch_id = -5672 AND xa h.event_type_code = xet.event_type_code AND gcc.chart_of_accounts_id = fsav.id_flex_num AND xtd.definition_code = xdd.definition_code AND xtd.definition_code = :4 AND xtd.definition_code = xjs.definition_code AND xtd.enabled_flag = 'Y' AND xjs.je_source_name = xsu.je_source_name AND xsu.application_id = 200 AND alb.code_combination_id = xdd.code_combination_id AND xtd.ledger_id = alb.set_of_books_id AND alb.code_combination_id = xal.code_combination_id --- remodeling AND xal.accounting_class_code = 'LIABILITY' AND xah.event_type_code <> 'MANUAL' --- remodeling GROUP BY DECODE(xet.event_class_code, 'PREPAYMENT APPLICATIONS', 'APPLIED', DECODE(xteu.entity_id, xah.entity_id, 'SOURCE', 'APPLIED')) , xah.entity_id , xet.event_class_code , xah.application_id , DECODE(xet.event_class_code, 'PREPAYMENT APPLICATIONS', xteu.entity_id, DECODE(xteu.entity_id, xah.entity_id, NULL, xteu.entity_id)) , xah.accounting_date , xal.currency_code , xal.code_combination_id , DECODE(fsav.balancing_segment, 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3, 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SE GMENT6', gcc.segment6, 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9, 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12, 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15, 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18, 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21, 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24, 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27, 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30, null) , DECODE(fsav.account_segment, 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3, 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6, 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9, 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12, 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15, 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18, 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21, 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24, 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27, 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30, null) , DECODE(fsav.cost_crt_segment, 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3, 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6, 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9, 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12, 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15, 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18, 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21, 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24, 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27, 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30, null) , DECODE(fsav.intercompany_segment, 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3, 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6, 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9, 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12, 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15, 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18, 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21, 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24, 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27, 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30, null) , DECODE(fsav.management_segment, 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3, 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6, 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9, 'SEGMENT10', gcc.segm ent10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12, 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15, 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18, 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21, 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24, 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27, 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30, null) , xah.ledger_id , xtd.definition_code , xal.party_id , xal.party_site_id , xal.party_type_code , xah.ae_header_id






Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms