R12: PAIGEN: Generate Draft Invoices Performance Is Poor (Doc ID 1944609.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Project Billing - Version 12.1.3 to 12.2.5 [Release 12.1 to 12.2]
Information in this document applies to any platform.

Symptoms

We are using Oracle E-Business suite release 12.1.2. I have attached the query that has been running now for 40+ hours.

Concurrent request, PRC: Generate Draft Invoices for a Range of Projects, which calls program PAIGEN.

 The following SQL is performing poor., which is shown in AWR report
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)) , ..
..
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
having decode(:b4, 'Y', 1, sum(RDL.bill_trans_bill_amount))<>0 order by 17,
18, 23


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