R11 Performance Issue with PRC: Update Project Summary Amounts

(Doc ID 1304966.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Project Planning and Control - Version 11.5.10.2 and later
Information in this document applies to any platform.
Report:PAXACMPT.RDF - PRC: Update Project Summary Amounts


Symptoms

Performance issue with the PRC: Update Project Summary for a Range of Projects.  Statements with the performance issue are:

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, LAST_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 ) SELECT pctv.project_id, pctv.task_id,
pctv.transaction_source, decode(pctv.line_type,'P','P','R','R','I','I','O'),
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, 5001,
SYSDATE, 5001, -1, 23601049, 275, 33703, 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 FROM pa_commitment_txns_v pctv WHERE pctv.project_id
IN (4, 82, 142, 150, 152, 161, 162, 167, 168, 170, 171, 177, 178, 179, 180,

and

UPDATE PA_TXN_ACCUM PTA SET PTA.TOT_CMT_RAW_COST = NULL,
PTA.TOT_CMT_BURDENED_COST = NULL, PTA.CMT_ROLLUP_FLAG = 'N',
PTA.LAST_UPDATED_BY = :B6 , PTA.LAST_UPDATE_DATE = SYSDATE, PTA.REQUEST_ID =
:B5 , PTA.PROGRAM_APPLICATION_ID = :B4 , PTA.PROGRAM_ID = :B3 ,
PTA.PROGRAM_UPDATE_DATE = SYSDATE
WHERE
PTA.PROJECT_ID = :B2 AND NVL(PTA.SYSTEM_LINKAGE_FUNCTION,'X') = NVL(NVL(:B1 ,
PTA.SYSTEM_LINKAGE_FUNCTION),'X') AND EXISTS ( SELECT 'Yes' FROM
PA_TXN_ACCUM_DETAILS PTAD WHERE PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID AND
PTAD.LINE_TYPE = 'M' ) AND EXISTS ( SELECT 'Yes' FROM PA_PERIODS WHERE
PERIOD_NAME = PTA.PA_PERIOD AND START_DATE >= :B8 AND END_DATE <= :B7 )

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