My Oracle Support Banner

R12: PAIGEN_SINGLE, Generate Draft Invoices for a Single Project Performance Issue (Doc ID 2522409.1)

Last updated on JULY 08, 2020

Applies to:

Oracle Project Billing - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Invoices

Running PRC: Generate Draft Invoices for a Single Project and having  performance issue.

This is sql causing performance
SELECT SUBSTRB( p.segment1
, 1
, 18 )
, i.draft_invoice_num
, NVL( i.draft_invoice_num_credited, 0 )
, NVL( SUBSTRB( hz_c.account_number
, 1
, 15 )
, 'N/A' )
, NVL( SUBSTRB( hz_p.party_name
, 1
, 17 )
, 'N/A' )
, NVL( SUBSTRB( hz_c1.account_number
, 1
, 15 )
, 'N/A' )
, NVL( SUBSTRB( hz_p1.party_name
, 1
, 25 )
, 'N/A' )
, NVL( SUBSTRB( a.agreement_num
, 1
, 10 )
, 'N/A' )
, SUBSTRB( NVL( i.transfer_rejection_reason, 'NONE' )
, 1
, 46 )
FROM hz_cust_accounts hz_c
, hz_parties hz_p
, pa_agreements_all a
, pa_projects p
, pa_draft_invoices i
, pa_project_customers pc
, hz_cust_accounts hz_c1
, hz_parties hz_p1
WHERE ((((((((((i.released_by_person_id IS NULL
AND i.request_id = :b0)
AND i.generation_error_flag = 'Y')
AND p.project_id = i.project_id)
AND a.agreement_id = i.agreement_id)
AND hz_c.cust_account_id = a.customer_id)
AND hz_c.party_id = hz_p.party_id)
AND pc.project_id = i.project_id)
AND pc.customer_id = a.customer_id)
AND hz_c1.cust_account_id = pc.bill_to_customer_id)
AND hz_c1.party_id = hz_p1.party_id)
UNION
SELECT SUBSTRB( p.segment1
, 1
, 18 )
, 0
, 0
, NVL( SUBSTRB( hz_c.account_number
, 1
, 15 )
, 'N/A' )
, NVL( SUBSTRB( hz_p.party_name
, 1
, 17 )
, 'N/A' )
, NVL( SUBSTRB( hz_c1.account_number
, 1
, 15 )
, 'N/A' )
, NVL( SUBSTRB( hz_p1.party_name
, 1
, 25 )
, 'N/A' )
, NVL( SUBSTRB( a.agreement_num
, 1
, 10 )
, 'N/A' )
, SUBSTRB( NVL( lk.meaning, 'NONE' )
, 1
, 46 )
FROM hz_cust_accounts hz_c
, hz_parties hz_p
, pa_agreements_all a
, pa_projects p
, pa_project_customers pc
, pa_distribution_warnings w
, pa_lookups lk
, hz_cust_accounts hz_c1
, hz_parties hz_p1
, pa_summary_project_fundings spf
WHERE ((((((((((((w.request_id = :b0
AND w.warning_message_code = 'PA_CUST_MERGE')
AND p.project_id = w.project_id)
AND lk.lookup_type = 'INVOICE DISTRIBUTION WARNING')
AND lk.lookup_code = w.warning_message_code)
AND pc.project_id = p.project_id)
AND spf.project_id = p.project_id)
AND a.agreement_id = spf.agreement_id)
AND hz_c.cust_account_id = a.customer_id)
AND hz_c.party_id = hz_p.party_id)
AND pc.customer_id = a.customer_id)
AND hz_c1.cust_account_id = pc.bill_to_customer_id)
AND hz_c1.party_id = hz_p1.party_id)
ORDER BY 1, 2

 

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


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