R12: Performance issue with Open Account Balances Data Manager (XLATBDMG) (Doc ID 1299378.1)

Last updated on AUGUST 25, 2017

Applies to:

Oracle Payables - Version 12.1.1 and later
Information in this document applies to any platform.
Performance issue with Open Account Balances Data Manager (XLATBDMG)
***Checked for relevance on 22-Dec-2015***

Symptoms

Performance issue with Open Account Balances Data Manager (XLATBDMG)

Followed Note 553484.1 - "How to Rebuild the Open Account AP Balances Listing (Trial Balance) Data" and set number of processors to 64, but the following processes were taking 12 hours to process 100k records:

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) no_index(xal MIS_XLA_AE_LINES_N1) */
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.application_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)
cost_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(SEGMENT_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.ae_header_id IS NOT NULL
AND alb.ae_line_id IS NOT NULL
AND alb.ae_header_id = xah.completion_acct_seq_value
AND 200 = xah.completion_acct_seq_version_id
AND alb.ae_line_id = xal.ae_line_num
AND xah.upg_source_application_id = 200
AND xah.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, '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.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.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)
,xah.ledger_id
,xtd.definition_code
,xal.party_id
,xal.party_site_id
,xal.party_type_code
,xah.ae_header_id;

Changes

NONE

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