Last updated on MAY 26, 2017

Applies to:

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