Performance Issue with PATTAR: PRC: Interface Invoices To Receivables
(Doc ID 3043376.1)
Last updated on AUGUST 26, 2024
Applies to:
Oracle Project Billing - Version 12.2.10 and laterInformation in this document applies to any platform.
Symptoms
PRC: Interface Invoices to Receivables shows a performance issue.
Per the AWR, the problem statement is:
select ITEM.UNEARNED_REVENUE_CR , ITEM.UNBILLED_RECEIVABLE_DR , INV.Draft_Invoice_Num , nvl(ITEM.bill_group_id, 0) , nvl(ITEM.consolidated_inv_num, 0) , ITEM.Line_Num , nvl(ITEM.CONSOLIDATED_INV_LINE_NUM, 0) , nvl(ITEM.DRAFT_INV_LINE_NUM_CREDITED, 0) , pa_invoice_xfer.GetConsInvLIneNumCredited(INV2.project_id, INV2.draft_invoice_num, ITEM.DRAFT_INV_LINE_NUM_CREDITED) , min(TRUNC(INV.Invoice_Date)) , min(SUBSTRB(hz_p.party_Name, 1, 27)) , min(hz_c.cust_account_id) , min(INV.Bill_To_Address_ID) , min(INV.BILL_TO_CONTACT_ID) , min(INV.SHIP_TO_CONTACT_ID) , min(AGREE.Agreement_Num) , min(NVL(INV.RA_Invoice_Number, '-1')) , min(AGREE.Term_ID) , min(INV.PA_Date) , min(NVL(ITEM.Taxable_Flag, 'N')) , min(DECODE(INV.Draft_Invoice_Num_Credited, null , 'P', DECODE(NVL(INV.Write_Off_Flag, 'N'), 'N', 'CM', 'WO'))) , min(DECODE(PAC.Bill_Another_Project_Flag, 'Y', DECODE(INV.Draft_Invoice_Num_Credited, null , :b0, :b1), DECODE(INV.Draft_Invoice_Num_Credited, null , :b2, :b3))) , min(DECODE(INV.Draft_Invoice_Num_Credited, null , '', DECODE(INV.credit_memo_reason_code, null , DECODE(NVL(INV.Write_Off_Flag, 'N'), 'N', 'PA_CREDIT_MEMO', 'PA_WRITE_OFF'), INV.credit_memo_reason_code))) , min(TO_CHAR(INV.Draft_Invoice_Num_Credited)) , min(TO_CHAR(ITEM.Projfunc_Bill_Amount)) , min(NVL(SOURCE.Name, decode(PAC.Bill_Another_Project_Flag, 'Y', :b4, :b5))) , min(NVL(INV.PROJFUNC_INVTRANS_RATE_TYPE, 'User')) , min(NVL(INV.PROJFUNC_INVTRANS_RATE_DATE, TRUNC(INV.Invoice_Date))) , min(decode(INV.PROJFUNC_INVTRANS_RATE_TYPE, 'User', NVL(TO_CHAR((1/INV.PROJFUNC_INVTRANS_EX_RATE)), '1'), null , '1', null )) , min(NVL(RTRIM(INV.inv_currency_code), :b6)) , min(NVL(TRX.Primary_Salesrep_ID, :b7)) , min(INV.GL_DATE) , min( case when :b8 is not null then :b8 when (SITE.cust_acct_site_id is null or NVL(SITE.Status, 'A')<>'A') then 'NO_ACTIVE_SHIP_ADDR' when (NVL(PAC.bill_another_project_flag, 'N')='Y' and RECPRJ.task_id is null ) then 'PA_IP_INVALID_PROJ_TASK' else null end ) rejcode , min(TO_CHAR(ITEM.Inv_Amount)) , min(NVL(PAC.bill_another_project_flag, 'N')) , min(PAC.Receiver_Task_ID) , min(ITEM.invoice_line_type) , min(NVL(INV.payment_set_id, 0)) , min(NVL(INV.Legal_entity_id, 0)) , min(NVL(INV.Receiver_legal_entity_id, 0)) , INV2.system_reference , SUBSTR(PROJ.Segment1, 1, 25) , SUBSTR(INV2.ADD_INV_GROUP, 1, 40) , NVL(inv2.bill_group_id, 0) , to_char(ITEM.draft_inv_line_num_credited) from RA_Customer_Trx TRX , RA_Batch_Sources SOURCE , PA_Draft_Invoices INV2 , PA_Draft_Invoices INV , PA_Draft_Invoice_Items ITEM , PA_Agreements_all AGREE , PA_Project_Customers PAC , hz_cust_accounts hz_c , hz_parties hz_p , hz_cust_site_uses site , pa_projects_all proj , pa_customer_receiver_prjects_v recprj where ((((((((((((((((((((SITE.cust_acct_site_id(+)=INV.SHIP_TO_ADDRESS_ID and SITE.Site_Use_Code(+)='SHIP_TO') and
Steps to Reproduce:
1. Projects responsibility
2. Other | Request | Run
3. Run the process PRC: Interface Invoices to Receivables and note an performance issue
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 |
Cause |
Solution |
References |