After Upgrade to 12.2, EXC: Transaction Exception Details by GL Period Report has Performance Issues
(Doc ID 2875373.1)
Last updated on JUNE 28, 2023
Applies to:
Oracle Project Costing - Version 12.2.4 and laterInformation in this document applies to any platform.
Symptoms
After upgrade to 12.2, the EXC: Transaction Exception Details by GL Period has slow performance. The process used to take a few minutes and now it takes 2+ hours and still does not complete.
The problem statement is:
SELECT REC FROM ( SELECT PA_INV_EXCEPTION_RPT_OBJ_TYP( INVDIST.PROJECT_ID,
INVDIST.TASK_ID, INV.INVOICE_NUM, INVDIST.INVOICE_LINE_NUMBER ,
INVDIST.DISTRIBUTION_LINE_NUMBER, INVDIST.INVOICE_DISTRIBUTION_ID,
INV.INVOICE_DATE, NVL(INVDIST.BASE_AMOUNT,INVDIST.AMOUNT),
INVDIST.PA_ADDITION_FLAG, INVDIST.EXPENDITURE_TYPE, INVDIST.PERIOD_NAME,
DECODE(INVDIST.POSTED_FLAG,'Y','*',NULL), NULL, NULL, INVDIST.ORG_ID ,
VEND.SEGMENT1, SUBSTR(VEND.VENDOR_NAME,1,80), NULL, NULL,
INV.INVOICE_TYPE_LOOKUP_CODE) REC FROM GL_LEDGERS GLSOB,
PA_IMPLEMENTATIONS_ALL IMP, HR_ORG_UNITS_NO_JOIN OU,
AP_INVOICE_DISTRIBUTIONS_ALL INVDIST, AP_INVOICES_ALL INV, PO_VENDORS VEND
WHERE GLSOB.LEDGER_ID = IMP.SET_OF_BOOKS_ID --++++
AND GLSOB.LEDGER_ID = :B7 --++++
AND (:B6 = 'Y' OR IMP.ORG_ID = :B5 ) --++++
AND NVL(GLSOB.SLA_LEDGER_CASH_BASIS_FLAG,'N') = 'N' --++++
AND IMP.ORG_ID = OU.ORGANIZATION_ID --++++
AND IMP.SAME_PA_GL_PERIOD = 'Y' --++++
...
WHERE INVDIST.CHARGE_APPLICABLE_TO_DIST_ID = APDIST.INVOICE_DISTRIBUTION_ID
AND APDIST.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID AND POD.PO_LINE_ID =
POL.PO_LINE_ID AND SI.INVENTORY_ITEM_ID = POL.ITEM_ID AND
SI.COMMS_NL_TRACKABLE_FLAG = 'Y' AND (SI.ORGANIZATION_ID = POD.ORG_ID OR
SI.ORGANIZATION_ID IN( SELECT NVL(SHIP_TO_ORGANIZATION_ID, -9999) FROM
PO_LINE_LOCATIONS_ALL PLL WHERE PLL.PO_HEADER_ID = POD.PO_HEADER_ID AND
PLL.PO_LINE_ID = POD.PO_LINE_ID) )) )))
INVDIST.TASK_ID, INV.INVOICE_NUM, INVDIST.INVOICE_LINE_NUMBER ,
INVDIST.DISTRIBUTION_LINE_NUMBER, INVDIST.INVOICE_DISTRIBUTION_ID,
INV.INVOICE_DATE, NVL(INVDIST.BASE_AMOUNT,INVDIST.AMOUNT),
INVDIST.PA_ADDITION_FLAG, INVDIST.EXPENDITURE_TYPE, INVDIST.PERIOD_NAME,
DECODE(INVDIST.POSTED_FLAG,'Y','*',NULL), NULL, NULL, INVDIST.ORG_ID ,
VEND.SEGMENT1, SUBSTR(VEND.VENDOR_NAME,1,80), NULL, NULL,
INV.INVOICE_TYPE_LOOKUP_CODE) REC FROM GL_LEDGERS GLSOB,
PA_IMPLEMENTATIONS_ALL IMP, HR_ORG_UNITS_NO_JOIN OU,
AP_INVOICE_DISTRIBUTIONS_ALL INVDIST, AP_INVOICES_ALL INV, PO_VENDORS VEND
WHERE GLSOB.LEDGER_ID = IMP.SET_OF_BOOKS_ID --++++
AND GLSOB.LEDGER_ID = :B7 --++++
AND (:B6 = 'Y' OR IMP.ORG_ID = :B5 ) --++++
AND NVL(GLSOB.SLA_LEDGER_CASH_BASIS_FLAG,'N') = 'N' --++++
AND IMP.ORG_ID = OU.ORGANIZATION_ID --++++
AND IMP.SAME_PA_GL_PERIOD = 'Y' --++++
...
WHERE INVDIST.CHARGE_APPLICABLE_TO_DIST_ID = APDIST.INVOICE_DISTRIBUTION_ID
AND APDIST.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID AND POD.PO_LINE_ID =
POL.PO_LINE_ID AND SI.INVENTORY_ITEM_ID = POL.ITEM_ID AND
SI.COMMS_NL_TRACKABLE_FLAG = 'Y' AND (SI.ORGANIZATION_ID = POD.ORG_ID OR
SI.ORGANIZATION_ID IN( SELECT NVL(SHIP_TO_ORGANIZATION_ID, -9999) FROM
PO_LINE_LOCATIONS_ALL PLL WHERE PLL.PO_HEADER_ID = POD.PO_HEADER_ID AND
PLL.PO_LINE_ID = POD.PO_LINE_ID) )) )))
Steps to Reproduce:
1. Projects responsibility
2. Other | Requests | Run | Run the process 'EXC: Transaction Exception Details by GL Period'
Changes
Upgraded to 12.2
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 |
| References |