My Oracle Support Banner

R12: PA: Performance With Payables Mass Additions Create Program (APMACR) (Doc ID 2552519.1)

Last updated on DECEMBER 04, 2019

Applies to:

Oracle Project Costing - Version 12.2.7 and later
Information in this document applies to any platform.

Symptoms

The Payables Mass Additions Create Program (APMACR) usually finishes in few
seconds. Now it is taking more than 15 minutes causing delays in the nightly processing.

Trace file shows problematic query:

SELECT /*+ leading(cdl) */EI.EXPENDITURE_ITEM_ID, EI.EXPENDITURE_ITEM_DATE ,
CDL.LINE_NUM, EI.DOCUMENT_HEADER_ID, EI.DOCUMENT_DISTRIBUTION_ID,
EI.DOCUMENT_PAYMENT_ID, EI.DOCUMENT_LINE_NUMBER, EI.DOCUMENT_TYPE,
EI.DOCUMENT_DISTRIBUTION_TYPE, EI.TRANSACTION_SOURCE,
RTRIM(SUBSTRB(EIC.EXPENDITURE_COMMENT,1,80)) DESCRIPTION,
(NVL(XDL.UNROUNDED_ACCOUNTED_DR,0) - NVL(XDL.UNROUNDED_ACCOUNTED_CR,0))
ACCT_RAW_COST, CDL.GL_DATE, CDL.ACCT_EVENT_ID, CDL.QUANTITY,
EI.NET_ZERO_ADJUSTMENT_FLAG, EI.ADJUSTED_EXPENDITURE_ITEM_ID,
EI.TRANSFERRED_FROM_EXP_ITEM_ID, EI.VENDOR_ID, RTRIM(POV.SEGMENT1)
VENDOR_NUMBER, APB.BATCH_NAME, API.INVOICE_DATE, API.CREATED_BY
INVOICE_CREATED_BY, API.LAST_UPDATED_BY INVOICE_UPDATED_BY, API.INVOICE_ID
INVOICE_ID, API.SOURCE, RTRIM(API.INVOICE_NUM) INVOICE_NUM,
APIL.WARRANTY_NUMBER, APIL.MANUFACTURER, APIL.SERIAL_NUMBER,
APIL.MODEL_NUMBER, DECODE(APD.LINE_TYPE_LOOKUP_CODE, 'ITEM', 'PA-ADJ',
APD.LINE_TYPE_LOOKUP_CODE) LINE_TYPE_LOOKUP_CODE, APD.PO_DISTRIBUTION_ID,
APD.RELATED_ID, APD.DISTRIBUTION_LINE_NUMBER, APD.INVOICE_DISTRIBUTION_ID,
APD.DIST_CODE_COMBINATION_ID, APD.INVOICE_LINE_NUMBER ,
DECODE(CDL.REVERSED_FLAG, NULL, NULL, ( SELECT CDL2.SI_ASSETS_ADDITION_FLAG
FROM
PA_COST_DISTRIBUTION_LINES_ALL CDL2 WHERE CDL2.EXPENDITURE_ITEM_ID =
CDL.EXPENDITURE_ITEM_ID AND CDL2.LINE_NUM_REVERSED = CDL.LINE_NUM ) )
REVERSED_ASSETS_FLAG, DECODE(CDL.LINE_NUM_REVERSED, NULL, NULL, ( SELECT
CDL2.SI_ASSETS_ADDITION_FLAG FROM PA_COST_DISTRIBUTION_LINES_ALL CDL2 WHERE
CDL2.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID AND CDL2.LINE_NUM =
CDL.LINE_NUM_REVERSED ) ) SOURCE_ASSETS_FLAG,
DECODE(APD.LINE_TYPE_LOOKUP_CODE, 'ITEM', DECODE(
APD.CORRECTED_INVOICE_DIST_ID, NULL,APD.INVOICE_DISTRIBUTION_ID,
APD.CORRECTED_INVOICE_DIST_ID), 'ACCRUAL',
DECODE(APD.CORRECTED_INVOICE_DIST_ID, NULL, APD.INVOICE_DISTRIBUTION_ID,
APD.CORRECTED_INVOICE_DIST_ID), 'IPV', APD.RELATED_ID, 'ERV',
APD.RELATED_ID, APD.CHARGE_APPLICABLE_TO_DIST_ID) PARENT_INVOICE_DIST_ID,
APD.ASSETS_ADDITION_FLAG AP_ASSETS_ADDITION_FLAG, APD.DIST_MATCH_TYPE,
GLCC.ACCOUNT_TYPE , XAL.CODE_COMBINATION_ID, ALGT.LEDGER_CATEGORY_CODE,
ALGT.LEDGER_ID, FSP.INVENTORY_ORGANIZATION_ID, APD.ASSETS_TRACKING_FLAG
FROM PA_EXPENDITURE_ITEMS EI, PA_EXPENDITURE_COMMENTS EIC,
PA_COST_DISTRIBUTION_LINES CDL, AP_INVOICES API, AP_BATCHES_ALL APB,
AP_INVOICE_LINES APIL, AP_INVOICE_DISTRIBUTIONS APD,
FINANCIALS_SYSTEM_PARAMS_ALL FSP, PO_VENDORS POV, XLA_DISTRIBUTION_LINKS
XDL, XLA_AE_HEADERS XAH, AP_ALC_LEDGER_GT ALGT, XLA_AE_LINES XAL,
AP_ACCT_CLASS_CODE_GT AAGT, GL_CODE_COMBINATIONS GLCC, PA_PROJECTS_ALL P,
PA_PROJECT_TYPES_ALL PT WHERE EI.EXPENDITURE_ITEM_ID =
CDL.EXPENDITURE_ITEM_ID AND CDL.EXPENDITURE_ITEM_ID =
EIC.EXPENDITURE_ITEM_ID (+) AND CDL.LINE_NUM = EIC.LINE_NUMBER (+) AND
EI.TRANSACTION_SOURCE IN ('AP INVOICE' , 'AP EXPENSE', 'AP NRTAX', 'AP
VARIANCE', 'AP ERV', 'INTERPROJECT_AP_INVOICES','INTERCOMPANY_AP_INVOICES')
AND CDL.GL_DATE <= :B2 AND CDL.LINE_TYPE = 'R' AND CDL.TRANSFER_STATUS_CODE
= 'A' AND CDL.SI_ASSETS_ADDITION_FLAG = 'T' AND CDL.PROJECT_ID =
P.PROJECT_ID AND P.PROJECT_TYPE = PT.PROJECT_TYPE AND P.ORG_ID = PT.ORG_ID
AND PT.PROJECT_TYPE_CLASS_CODE <> 'CAPITAL' AND EI.DOCUMENT_HEADER_ID =
API.INVOICE_ID AND EI.DOCUMENT_DISTRIBUTION_ID =
APD.INVOICE_DISTRIBUTION_ID AND EI.DOCUMENT_LINE_NUMBER =
APD.INVOICE_LINE_NUMBER AND APIL.INVOICE_ID = API.INVOICE_ID AND API.ORG_ID
= FSP.ORG_ID AND APIL.LINE_NUMBER = APD.INVOICE_LINE_NUMBER AND
API.BATCH_ID = APB.BATCH_ID(+) AND APD.POSTED_FLAG = 'Y' AND API.VENDOR_ID =
POV.VENDOR_ID AND APD.SET_OF_BOOKS_ID = :B1 AND XAH.APPLICATION_ID = 275
AND XAH.EVENT_ID = CDL.ACCT_EVENT_ID AND XAH.BALANCE_TYPE_CODE = 'A' AND
XAH.ACCOUNTING_ENTRY_STATUS_CODE = 'F' AND XAL.APPLICATION_ID =
XAH.APPLICATION_ID AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND
XAL.ACCOUNTING_CLASS_CODE = AAGT.ACCOUNTING_CLASS_CODE AND XDL.EVENT_ID =
XAH.EVENT_ID AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID AND XDL.AE_LINE_NUM =
XAL.AE_LINE_NUM AND XDL.APPLICATION_ID = XAL.APPLICATION_ID AND
XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = EI.EXPENDITURE_ITEM_ID AND
XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = CDL.LINE_NUM AND XAH.LEDGER_ID =
ALGT.LEDGER_ID AND DECODE(ALGT.ORG_ID, -99, ALGT.ORG_ID, CDL.ORG_ID) =
DECODE(ALGT.ORG_ID, -99, -99, ALGT.ORG_ID) AND GLCC.CODE_COMBINATION_ID =
XAL.CODE_COMBINATION_ID ORDER BY EI.DOCUMENT_DISTRIBUTION_ID,
EI.EXPENDITURE_ITEM_ID, CDL.LINE_NUM

 

This is occurring on release 12.2.7 and has the following file version:
$Header: PAMASSAB.pls 120.18.12020000.4 2013/05/15 09:31:04 bdixit ship $

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
Cause
Solution
References


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