My Oracle Support Banner

Xlaaxuar_rcpt.sql takes Hours To Complete during 12.2 Upgrade Driver (Doc ID 2269975.1)

Last updated on FEBRUARY 08, 2018

Applies to:

Oracle Receivables - Version 12.2.6 and later
Information in this document applies to any platform.

Symptoms

During upgrading EBS from 11.5.10.2 to R12.2.6, the following steps in the 12.2 upgrade driver Xlaaxuar_rcpt.sql is taking extensive time to complete:

adpatch options=nocopyportion,nogenerateportion workers=64
driver=u_merged.drv logfile=u_merged_1031.log

xlaaxuar_rcpt.sql took long time like 11 hrs to complete
The activity "INSERT INTO XLA_DISTRIBUTION_LINKS" was running with read by other session

These are the queryies with issues

MERGE /*+ LEADING(DAT) INDEX(XAL XLA_AE_LINES_U1) */ INTO
XLA_AE_LINES XAL USING (SELECT /*+ index(xte XLA_TRANSACTION_ENTITIES_N1)
index(xah XLA_AE_HEADERS_N3) index(xdl XLA_DISTRIBUTION_LINKS_N3) */
XDL.AE_HEADER_ID, XDL.AE_LINE_NUM , MIN(XDL.ACCOUNTING_LINE_CODE) JLT FROM
XLA_DISTRIBUTION_LINKS XDL, XLA_AE_HEADERS XAH, XLA_TRANSACTION_ENTITIES_UPG
XTE WHERE XDL.ACCOUNTING_LINE_CODE IN ('FACTOR', 'SHORT_TERM_DEBT',
'CONFIRMATION', 'REMITTANCE') AND XDL.UPG_BATCH_ID =-5672 AND
XDL.APPLICATION_ID =222 AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID AND
XDL.EVENT_ID = XAH.EVENT_ID AND XAH.UPG_BATCH_ID =-5672 AND
XAH.APPLICATION_ID =222 AND XAH.ACCOUNTING_ENTRY_STATUS_CODE='F' AND
XAH.ENTITY_ID = XTE.ENTITY_ID AND XTE.APPLICATION_ID =222 AND XTE.ENTITY_CODE
= 'RECEIPTS' AND (NVL(XTE.SOURCE_ID_INT_1, -99), XTE.LEDGER_ID ) IN (SELECT
PRIMARY_HEADER_ID "cash_receipt_id", NEW_HEADER_ID "set_of_books_id" FROM
XLA_HISTORIC_MAPPING_GT) GROUP BY XDL.AE_HEADER_ID, XDL.AE_LINE _NUM HAVING
COUNT(DISTINCT XDL.ACCOUNTING_LINE_CODE)=1 ) DAT ON ( DAT.AE_HEADER_ID =
XAL.AE_HEADER_ID AND DAT.AE_LINE_NUM = XAL.AE_LINE_NUM AND XAL.UPG_BATCH_ID
=-5672 AND XAL.APPLICATION_ID = 222 ) WHEN MATCHED THEN UPDATE SET
XAL.ACCOUNTING_CLASS_CODE = DAT.JLT

INSERT INTO XLA_DISTRIBUTION_LINKS (APPLICATION_ID, EVENT_ID,
AE_HEADER_ID, AE_LINE_NUM, ACCOUNTING_LINE_CODE, ACCOUNTING_LINE_TYPE_CODE,
REF_AE_HEADER_ID, SOURCE_DISTRIBUTION_TYPE, SOURCE_DISTRIBUTION_ID_CHAR_1,
SOURCE_DISTRIBUTION_ID_CHAR_2, SOURCE_DISTRIBUTION_ID_CHAR_3,
SOURCE_DISTRIBUTION_ID_CHAR_4, SOURCE_DISTRIBUTION_ID_CHAR_5,
SOURCE_DISTRIBUTION_ID_NUM_1, SOURCE_DISTRIBUTION_ID_NUM_2,
SOURCE_DISTRIBUTION_ID_NUM_3, SOURCE_DISTRIBUTION_ID_NUM_4,
SOURCE_DISTRIBUTION_ID_NUM_5, UNROUNDED_ENTERED_DR, UNROUNDED_ENTERED_CR,
UNROUNDED_ACCOUNTED_DR, UNROUNDED_ACCOUNTED_CR, MERGE_DUPLICATE_CODE,
TAX_LINE_REF_ID, TAX_SUMMARY_LINE_REF_ID, TAX_REC_NREC_DIST_REF_ID,
STATISTICAL_AMOUNT, TEMP_LINE_NUM, EVENT_TYPE_CODE, EVENT_CLASS_CODE,
REF_EVENT_ID, UPG_BATCH_ID) SELECT 222, EVENT_ID, AE_HEADER_ID, LINE_NUM,
ACCOUNT_CLASS, 'C', AE_HEADER_ID, SOURCE_TABLE, '', '', '', '', '', LINE_ID,
'', '', '', '', AMOUNT_DR, AMOUNT_CR, ACCTD_AMOUNT_DR, ACC TD_AMOUNT_CR, 'N',
'', '', '', '', TEMP_LINE_NUM, EVENT_TYPE_CODE, EVENT_CLASS_CODE, '',
BATCH_ID FROM ( SELECT -5672 AS BATCH_ID, AE_HEADER_ID AS AE_HEADER_ID,
LINE_ID AS LINE_ID, EVENT_ID AS EVENT_ID, ACCOUNT_CLASS AS ACCOUNT_CLASS,
GAIN_OR_LOSS_FLAG AS GAIN_OR_LOSS_FLAG, SOURCE_TABLE AS SOURCE_TABLE,
CODE_COMBINATION_ID AS CODE_COMBINATION_ID, AMOUNT_DR AS AMOUNT_DR, AMOUNT_CR
AS AMOUNT_CR, ACCTD_AMOUNT_DR AS ACCTD_AMOUNT_DR, ACCTD_AMOUNT_CR AS
ACCTD_AMOUNT_CR, CURRENCY_CODE AS CURRENCY_CODE, THIRD_PARTY_ID AS
THIRD_PARTY_ID, THIRD_PARTY_SUB_ID AS THIRD_PARTY_SUB_ID, EXCHANGE_DATE AS
EXCHANGE_DATE, EXCHANGE_RATE AS EXCHANGE_RATE, EXCHANGE_TYPE AS
EXCHANGE_TYPE, EVENT_TYPE_CODE AS EVENT_TYPE_CODE, EVENT_CLASS_CODE AS
EVENT_CLASS_CODE, ACCOUNTING_DATE AS ACCOUNTING_DATE, LEDGER_ID AS LEDGER_ID,
(SELECT /*+ no_unnest INDEX(xdlpg xla_distribution_links_n3) */ MAX
(XDLPG.TEMP_LINE_NUM) FROM XLA_DISTRIBUTION_LINKS XDLPG WHERE
XDLPG.APPLICATION_ID = 222 AND XDLPG.AE_HEADER_ID = AE_HEADER_ID ) +
ROW_NUMBER() OVER(PARTITION BY EVENT_ID, AE_HEADER_ID ORDER BY LINE_NUM)
TEMP_LINE_NUM, LINE_NUM FROM (SELECT /*+ ordered use_nl(dat, hdr, xal)
index(hdr XLA_AE_HEADERS_N2) index(xal XLA_AE_LINES_U1)*/ HDR.AE_HEADER_ID AS
AE_HEADER_ID, DAT.LINE_ID AS LINE_ID, DAT.EVENT_ID AS EVENT_ID,
DAT.ACCOUNT_CLASS AS ACCOUNT_CLASS, DAT.GAIN_OR_LOSS_FLAG AS
GAIN_OR_LOSS_FLAG, DAT.SOURCE_TABLE AS SOURCE_TABLE, DAT.CODE_COMBINATION_ID
AS CODE_COMBINATION_ID, DAT.AMOUNT_DR AS AMOUNT_DR, DAT.AMOUNT_CR AS
AMOUNT_CR, DAT.ACCTD_AMOUNT_DR AS ACCTD_AMOUNT_DR, DAT.ACCTD_AMOUNT_CR AS
ACCTD_AMOUNT_CR, DAT.CURRENCY_CODE AS CURRENCY_CODE, DAT.THIRD_PARTY_ID AS
THIRD_PARTY_ID, DAT.THIRD_PARTY_SUB_ID AS THIRD_PARTY_SUB_ID,
DAT.EXCHANGE_DATE AS EXCHANGE_DATE, DAT.EXCHANGE_RATE AS EXCHANGE_RATE,
DAT.EXCHANGE_TYPE AS EXCHANGE_TYPE, DAT.EVENT_TYPE_CODE AS EVENT_TYPE_CODE,
DAT.EVENT_CLASS_CODE AS EVENT_CLASS_CODE, HDR.ACCOUNTING_DATE AS
ACCOUNTING_DATE, HDR.LED GER_ID AS LEDGER_ID, 1 LN_ORDER, XAL.AE_LINE_NUM AS
LINE_NUM FROM (SELECT /*+ ordered use_nl(ent, ev, ae, crh, ard) INDEX(ent
xla_transaction_entities_N1) INDEX(ev xla_events_u2) */
DECODE(ARD.SOURCE_TYPE, 'BANK_CHARGES', 'BANK_CHG', ARD.SOURCE_TYPE)
ACCOUNT_CLASS, 'AR_DISTRIBUTIONS_ALL' SOURCE_TABLE, ARD.SOURCE_ID
ARD_SOURCE_ID, ARD.SOURCE_TABLE ARD_SOURCE_TABLE , ARD.SOURCE_TYPE ,
ARD.CODE_COMBINATION_ID CODE_COMBINATION_ID, ARD.AMOUNT_DR AMOUNT_DR,
ARD.AMOUNT_CR AMOUNT_CR, ARD.ACCTD_AMOUNT_DR ACCTD_AMOUNT_DR,
ARD.ACCTD_AMOUNT_CR ACCTD_AMOUNT_CR, ARD.CURRENCY_CODE CURRENCY_CODE,
ARD.THIRD_PARTY_ID THIRD_PARTY_ID, ARD.THIRD_PARTY_SUB_ID THIRD_PARTY_SUB_ID,
ARD.CURRENCY_CONVERSION_DATE EXCHANGE_DATE, ARD.CURRENCY_CONVERSION_RATE
EXCHANGE_RATE, ARD.CURRENCY_CONVERSION_TYPE EXCHANGE_TYPE, ARD.LINE_ID
LINE_ID, ENT.LEDGER_ID , EV.EVENT_ID EVENT_ID, EV.EVENT_TYPE_CODE
EVENT_TYPE_CODE, AE.EVENT_TYPE AX_EVENT_TYPE , DECODE(CR.TYPE, 'CASH',
'RECEIPT', 'MISC', 'MISC_ RECEIPT', 'RECEIPT') EVENT_CLASS_CODE, 'N'
GAIN_OR_LOSS_FLAG, SUM(ARD.AMOUNT_DR) OVER ( PARTITION BY
CRH.CASH_RECEIPT_HISTORY_ID , ARD.CODE_COMBINATION_ID , CASE WHEN
CRH.FIRST_POSTED_RECORD_FLAG = 'Y' THEN ARD.LINE_ID || ' ' ELSE
DECODE(ARD.SOURCE_TYPE, 'CURR_ROUND', 'ROUNDING', 'EXCH_GAIN', 'GAIN',
'EXCH_LOSS', 'LOSS', 'BANK_CHARGES' , 'CASH', 'MISCCASH' , 'CASH' ,
ARD.SOURCE_TYPE) END ) SUM_AMOUNT_DR , SUM(ARD.AMOUNT_CR) OVER ( PARTITION BY
CRH.CASH_RECEIPT_HISTORY_ID , ARD.CODE_COMBINATION_ID , CASE WHEN
CRH.FIRST_POSTED_RECORD_FLAG = 'Y' THEN ARD.LINE_ID || ' ' ELSE
DECODE(ARD.SOURCE_TYPE, 'CURR_ROUND', 'ROUNDING', 'EXCH_GAIN', 'GAIN',
'EXCH_LOSS', 'LOSS', 'BANK_CHARGES' , 'CASH', 'MISCCASH' , 'CASH',
ARD.SOURCE_TYPE) END ) SUM_AMOUNT_CR , SUM(ARD.ACCTD_AMOUNT_DR) OVER (
PARTITION BY CRH.CASH_RECEIPT_HISTORY_ID , ARD.CODE_COMBINATION_ID , CASE
WHEN CRH.FIRST_POSTED_RECORD_FLAG = 'Y' THEN ARD.LINE_ID || ' ' ELSE
DECODE(ARD.SOURCE_TYPE, 'CURR_ROUND' , 'ROUNDING', 'EXCH_GAIN', 'GAIN',
'EXCH_LOSS', 'LOSS', 'BANK_CHARGES' , 'CASH', 'MISCCASH' , 'CASH',
ARD.SOURCE_TYPE) END ) SUM_ACCTD_AMOUNT_DR , SUM(ARD.ACCTD_AMOUNT_CR) OVER (
PARTITION BY CRH.CASH_RECEIPT_HISTORY_ID , ARD.CODE_COMBINATION_ID , CASE
WHEN CRH.FIRST_POSTED_RECORD_FLAG = 'Y' THEN ARD.LINE_ID ||' ' ELSE
DECODE(ARD.SOURCE_TYPE, 'CURR_ROUND', 'ROUNDING', 'EXCH_GAIN', 'GAIN',
'EXCH_LOSS', 'LOSS', 'BANK_CHARGES' , 'CASH', 'MISCCASH' , 'CASH',
ARD.SOURCE_TYPE) END ) SUM_ACCTD_AMOUNT_CR FROM AR_CASH_RECEIPTS_ALL CR,
XLA_TRANSACTION_ENTITIES_UPG ENT, XLA_EVENTS EV, AX.AX_EVENTS AE ,
AR_CASH_RECEIPT_HISTORY_ALL CRH, AR_DISTRIBUTIONS_ALL ARD WHERE 1 =1 AND
CR.CASH_RECEIPT_ID IN (SELECT PRIMARY_HEADER_ID "cash_receipt

 

 

Changes

 

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
 1. Bug Summary
 2. Fixed Files
 3. Recommended Patches
 4. Solution Steps
References


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