PRC: Update Project Summary Amounts Very Poor Performance. (Doc ID 2146961.1)

Last updated on MARCH 08, 2017

Applies to:

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

Symptoms

On : 12.2.4 version, Expenditure Processing

Running PRC: Update Project Summary Amounts  and having performance issue.

INSERT INTO PA_COMMITMENT_TXNS_TMP (PROJECT_ID, TASK_ID, TRANSACTION_SOURCE, LINE_TYPE, CMT_NUMBER, CMT_DISTRIBUTION_ID, CMT_HEADER_ID, DESCRIPTION, EXPENDITURE_ITEM_DATE, PA_PERIOD, GL_PERIOD, CMT_LINE_NUMBER, CMT_CREATION_DATE, CMT_APPROVED_DATE, CMT_REQUESTOR_NAME, CMT_BUYER_NAME, CMT_APPROVED_FLAG, CMT_PROMISED_DATE, CMT_NEED_BY_DATE, ORGANIZATION_ID, VENDOR_ID, VENDOR_NAME, EXPENDITURE_TYPE, EXPENDITURE_CATEGORY, REVENUE_CATEGORY, SYSTEM_LINKAGE_FUNCTION, UNIT_OF_MEASURE, UNIT_PRICE, CMT_IND_COMPILED_SET_ID, TOT_CMT_QUANTITY, QUANTITY_ORDERED, AMOUNT_ORDERED, ORIGINAL_QUANTITY_ORDERED, ORIGINAL_AMOUNT_ORDERED, QUANTITY_CANCELLED, AMOUNT_CANCELLED, QUANTITY_DELIVERED, QUANTITY_INVOICED, AMOUNT_INVOICED, QUANTITY_OUTSTANDING_DELIVERY, AMOUNT_OUTSTANDING_DELIVERY, QUANTITY_OUTSTANDING_INVOICE, AMOUNT_OUTSTANDING_INVOICE, QUANTITY_OVERBILLED, AMOUNT_OVERBILLED, ORIGINAL_TXN_REFERENCE1, ORIGINAL_TXN_REFERENCE2, ORIGINAL_TXN_REFERENCE3, LA ST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, ACCT_RAW_COST, ACCT_BURDENED_COST, DENOM_CURRENCY_CODE, DENOM_RAW_COST, DENOM_BURDENED_COST, ACCT_CURRENCY_CODE, ACCT_RATE_DATE, ACCT_RATE_TYPE, ACCT_EXCHANGE_RATE, RECEIPT_CURRENCY_CODE, RECEIPT_CURRENCY_AMOUNT, RECEIPT_EXCHANGE_RATE, INVENTORY_ITEM_ID, UOM_CODE, WIP_RESOURCE_ID, RESOURCE_CLASS, RELEASE_NUMBER ) SELECT /*+ push_pred(PCTV) */ PCTV.PROJECT_ID, PCTV.TASK_ID, PCTV.TRANSACTION_SOURCE, PCTV.LINE_TYPE, PCTV.CMT_NUMBER, PCTV.CMT_DISTRIBUTION_ID, PCTV.CMT_HEADER_ID, PCTV.DESCRIPTION, PCTV.EXPENDITURE_ITEM_DATE, PCTV.PA_PERIOD, PCTV.GL_PERIOD, PCTV.CMT_LINE_NUMBER, PCTV.CMT_CREATION_DATE, PCTV.CMT_APPROVED_DATE, PCTV.CMT_REQUESTOR_NAME, PCTV.CMT_BUYER_NAME, PCTV.CMT_APPROVED_FLAG, PCTV.CMT_PROMISED_DATE, PCTV.CMT_NEED_BY_DATE, PCTV.ORGANIZATION_ID, PCTV.VENDOR_ID, PCTV.VENDOR_NAME, PCTV .EXPENDITURE_TYPE, PCTV.EXPENDITURE_CATEGORY, PCTV.REVENUE_CATEGORY, PCTV.SYSTEM_LINKAGE_FUNCTION, PCTV.UNIT_OF_MEASURE, PCTV.UNIT_PRICE, PCTV.CMT_IND_COMPILED_SET_ID, PCTV.TOT_CMT_QUANTITY, PCTV.QUANTITY_ORDERED, PCTV.AMOUNT_ORDERED, PCTV.ORIGINAL_QUANTITY_ORDERED, PCTV.ORIGINAL_AMOUNT_ORDERED, PCTV.QUANTITY_CANCELLED, PCTV.AMOUNT_CANCELLED, PCTV.QUANTITY_DELIVERED, PCTV.QUANTITY_INVOICED, PCTV.AMOUNT_INVOICED, PCTV.QUANTITY_OUTSTANDING_DELIVERY, PCTV.AMOUNT_OUTSTANDING_DELIVERY, PCTV.QUANTITY_OUTSTANDING_INVOICE, PCTV.AMOUNT_OUTSTANDING_INVOICE, PCTV.QUANTITY_OVERBILLED, PCTV.AMOUNT_OVERBILLED, PCTV.ORIGINAL_TXN_REFERENCE1, PCTV.ORIGINAL_TXN_REFERENCE2, PCTV.ORIGINAL_TXN_REFERENCE3, SYSDATE, :B7 , SYSDATE, :B6 , :B5 , :B4 , :B3 , :B2 , NULL, PCTV.ACCT_RAW_COST, PCTV.ACCT_BURDENED_COST, PCTV.DENOM_CURRENCY_CODE, PCTV.DENOM_RAW_COST, PCTV.DENOM_BURDENED_COST, PCTV.ACCT_CURRENCY_CODE, PCTV.ACCT_RATE_DATE, PCTV.ACCT_RATE_TYPE, PCTV.ACCT_EXCHANGE _RATE, PCTV.RECEIPT_CURRENCY_CODE, PCTV.RECEIPT_CURRENCY_AMOUNT, PCTV.RECEIPT_EXCHANGE_RATE, PCTV.INVENTORY_ITEM_ID, PCTV.UOM_CODE, PCTV.WIP_RESOURCE_ID, PCTV.RESOURCE_CLASS, PCTV.RELEASE_NUMBER FROM PA_COMMITMENTS_V PCTV WHERE NVL(PCTV.SYSTEM_LINKAGE_FUNCTION, 'X') = NVL(:B1 , NVL(PCTV.SYSTEM_LINKAGE_FUNCTION, 'X'))

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