R12 PAIGEN PRC: Generate Draft Invoices for a Single Project Performance Issue

(Doc ID 1507401.1)

Last updated on AUGUST 01, 2016

Applies to:

Oracle Project Billing - Version 12.1.3 to 12.2 [Release 12.1 to 12.2]
Information in this document applies to any platform.
*** Checked for relevance 04-Sep-2014 ***
PAIGEN

Symptoms

On : 12.1.3 version, the concurrent program PRC: Generate Draft Invoices for a Single Project takes long time to complete.  

The costiest query is :

SELECT PF.AGREEMENT_ID, PF.PROJECT_ID, PF.TASK_ID , 
    PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(SUM(DII.AMOUNT * (1 - (NVL(DI.RETENTION_PERCENTAGE,0)/100 )) ), DII.INVPROC_CURRENCY_CODE) 
    DII_AMOUNT,DII.INVPROC_CURRENCY_CODE,    
    PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(SUM(DII.PROJFUNC_BILL_AMOUNT * (1 - ( NVL(DI.RETENTION_PERCENTAGE,0)/100 )) ), DII.PROJFUNC_CURRENCY_CODE) 
  DII_PROJFUNC_AMOUNT,DII.PROJFUNC_CURRENCY_CODE,    
  PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(SUM(DII.PROJECT_BILL_AMOUNT * (1 - ( NVL(DI.RETENTION_PERCENTAGE,0)/100 )) ),  DII.PROJECT_CURRENCY_CODE) DII_PROJECT_AMOUNT,DII.PROJECT_CURRENCY_CODE,   
  PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(SUM(DII.FUNDING_BILL_AMOUNT * (1 - ( NVL(DI.RETENTION_PERCENTAGE,0)/100 )) ),  DII.FUNDING_CURRENCY_CODE) DII_FUNDING_AMOUNT,DII.FUNDING_CURRENCY_CODE 
  FROM 
     PA_DRAFT_INVOICE_ITEMS DII, PA_DRAFT_INVOICES DI, 
     PA_SUMMARY_PROJECT_FUNDINGS PF 
  WHERE DII.PROJECT_ID = DI.PROJECT_ID AND 
        DII.DRAFT_INVOICE_NUM+0 = DI.DRAFT_INVOICE_NUM AND PF.TASK_ID = DII.TASK_ID 
        AND DII.INVOICE_LINE_TYPE <> 'RETENTION' AND DI.PROJECT_ID = PF.PROJECT_ID    
        AND DI.AGREEMENT_ID = PF.AGREEMENT_ID AND PF.PROJECT_ID = :B2 AND 
        PF.PROJECT_ID = DII.PROJECT_ID AND PF.INVPROC_CURRENCY_CODE = DII.INVPROC_CURRENCY_CODE AND
        :B1 = 'N' AND
        EXISTS (SELECT 1 FROM PA_AGREEMENTS_ALL PAA 
                WHERE PAA.AGREEMENT_ID = PF.AGREEMENT_ID AND 
               DII.FUNDING_CURRENCY_CODE = PAA.AGREEMENT_CURRENCY_CODE) 
        GROUP BY PF.AGREEMENT_ID, PF.PROJECT_ID, PF.TASK_ID ,DII.INVPROC_CURRENCY_CODE, DII.PROJFUNC_CURRENCY_CODE, DII.PROJECT_CURRENCY_CODE, DII.FUNDING_CURRENCY_CODE
 



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