My Oracle Support Banner

PAXACMPT PRC: Update Project Summary Amounts Has Slow Performance (Doc ID 2721036.1)

Last updated on JANUARY 03, 2021

Applies to:

Oracle Project Planning and Control - Version 12.2.6 and later
Information in this document applies to any platform.

Symptoms

PRC: Update Project Summary Amounts has a  performance issue with this SQL:

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, 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 ,
  PA_PROJ_SUMM_TMP PRJ WHERE PCTV.PROJECT_ID = PRJ.PROJECT_ID AND
  NVL(PCTV.SYSTEM_LINKAGE_FUNCTION,'X') = NVL(:B1 ,
  NVL(PCTV.SYSTEM_LINKAGE_FUNCTION,'X'))

Changes

 

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


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