R12: PAIGEN: Performance Issue With PRC: Generate Draft Invoices Process (Doc ID 2191548.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Project Billing - Version 12.1.3 and later
Information in this document applies to any platform.
PRC: Generate Draft Invoices Process

Symptoms

On 12.1.3:

-After applied the following patches:
PRC4 patch (21236633) and 22687240 (Oracle Projects Release Update Pack 7),
PRC: Generate Draft Invoices for a Single Project process is not completing. It keeps running.

-Issue seems to happen with big projects

-The following query causes the performance issue:

SELECT /*+ USE_NL(T, L) +*/
  E.Expenditure_item_id,
  TO_CHAR (SUM (L.BILL_AMOUNT)),
  TO_CHAR (
  PA_CURRENCY.ROUND_CURRENCY_AMT (
  ( SUM (L.BILL_AMOUNT)
  * (1 - (NVL (inv.retention_percentage, 0) / 100))))),
  TO_CHAR (SUM (L.PROJFUNC_BILL_AMOUNT)),
  TO_CHAR (
  PA_CURRENCY.ROUND_CURRENCY_AMT (
  ( SUM (L.PROJFUNC_BILL_AMOUNT)
  * (1 - (NVL (inv.retention_percentage, 0) / 100))))),
  TO_CHAR (SUM (L.PROJECT_BILL_AMOUNT)),
  TO_CHAR (
  PA_CURRENCY.ROUND_CURRENCY_AMT (
  ( SUM (L.PROJECT_BILL_AMOUNT)
  * (1 - (NVL (inv.retention_percentage, 0) / 100))))),
  TO_CHAR (SUM (L.FUNDING_BILL_AMOUNT)),
  TO_CHAR (
  PA_CURRENCY.ROUND_CURRENCY_AMT (
  ( SUM (L.FUNDING_BILL_AMOUNT)
  * (1 - (NVL (inv.retention_percentage, 0) / 100))))),
  ITEM.projfunc_currency_code,
  ITEM.project_currency_code,
  ITEM.funding_currency_code,
  ITEM.invproc_currency_code,
  ITEM.funding_rate_type,
  TO_CHAR (ITEM.funding_rate_date, 'YYYY/MM/DD'),
  TO_CHAR (ITEM.funding_exchange_rate),
  L2.DRAFT_INVOICE_NUM,
  ITEM.LINE_NUM,
  ITEM.INV_RATE_TYPE,
  ITEM.INV_RATE_DATE,
  ITEM.INV_EXCHANGE_RATE,
  NVL (ITEM.TASK_ID, 0),
  NVL (ITEM.EVENT_TASK_ID, 0),
  NVL (ITEM.EVENT_NUM, 0),
  1,
  ITEM.TEXT,
  TO_CHAR (NVL (MAX (E.EXPENDITURE_ITEM_DATE), SYSDATE), 'J'),
  INV.BILL_TO_ADDRESS_ID,
  INV.SHIP_TO_ADDRESS_ID,
  ITEM.output_tax_classification_code,
  ITEM.output_tax_exempt_flag,
  ITEM.output_tax_exempt_reason_code,
  ITEM.output_tax_exempt_number,
  INV.LANGUAGE,
  ITEM.TRANSLATED_TEXT,
  TO_CHAR ( (NVL (INV.RETENTION_PERCENTAGE, 0) / 100)),
  TO_CHAR (
  PA_CURRENCY.ROUND_CURRENCY_AMT (
  ( SUM (L.BILL_AMOUNT)
  * (NVL ( (-inv.retention_percentage), 0) / 100)))),
  INV.BILL_TO_CUSTOMER_ID,
  INV.SHIP_TO_CUSTOMER_ID,
  MAX (L.LINE_NUM) RDL_LINE_NUM,
  INV.ADD_INV_GROUP
  FROM pa_agreements_all A,
  PA_TASKS T2,
  PA_TASKS T,
  PA_CUST_REV_DIST_LINES L,
  PA_DRAFT_REVENUES R,
  PA_CUST_REV_DIST_LINES L2,
  pa_expenditure_items_all E,
  PA_DRAFT_INVOICE_ITEMS ITEM,
  PA_DRAFT_INVOICES INV,
.
.
GROUP BY i.Expenditure_item_id,
  ITEM.projfunc_currency_code,
  ITEM.project_currency_code,
  ITEM.funding_currency_code,
  ITEM.invproc_currency_code,
  ITEM.funding_rate_type,
  TO_CHAR (ITEM.funding_rate_date, 'YYYY/MM/DD'),
  TO_CHAR (ITEM.funding_exchange_rate),
  INV.DRAFT_INVOICE_NUM,
  ITEM.LINE_NUM,
  ITEM.INV_RATE_TYPE,
  ITEM.INV_RATE_DATE,
  ITEM.INV_EXCHANGE_RATE,
  NVL (ITEM.TASK_ID, 0),
  NVL (ITEM.EVENT_TASK_ID, 0),
  NVL (ITEM.EVENT_NUM, 0),
  2,
  ITEM.TEXT,
  INV.BILL_TO_ADDRESS_ID,
  INV.SHIP_TO_ADDRESS_ID,
  ITEM.output_tax_classification_code,
  ITEM.output_tax_exempt_flag,
  ITEM.output_tax_exempt_reason_code,
  ITEM.output_tax_exempt_number,
  INV.LANGUAGE,
  ITEM.TRANSLATED_TEXT,
  INV.RETENTION_PERCENTAGE,
  INV.BILL_TO_CUSTOMER_ID,
  INV.SHIP_TO_CUSTOMER_ID,
  INV.ADD_INV_GROUP
  HAVING DECODE ( :b6, 'Y', 1, SUM (RDL.bill_trans_bill_amount)) <> 0
ORDER BY 17, 18, 23

Changes

Applied the following patches:
PRC4 patch 21236633 and 22687240 (Oracle Projects Release Update Pack 7),

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