Performance Issue when Querying the Bill Group in the Consolidated Invoice Review Page
(Doc ID 2734432.1)
Last updated on OCTOBER 24, 2021
Applies to:
Oracle Project Billing - Version 12.2.9 and laterInformation in this document applies to any platform.
Symptoms
Performance issue with the Consolidate Invoice review page when querying by bill group. The problem query is:
Select BILLING_GROUP,
BILL_GROUP_ID,
DRAFT_INVOICE_NUM,
TRANSFER_STATUS_CODE,
INVOICE_STATUS_CODE,
INVOICE_STATUS,
CHK_STATUS,
PA_DATE,
REQUEST_ID,
APPROVED_DATE,
APPROVED_BY_PERSON_ID,
APPROVED_BY_PERSON_NAME,
RELEASED_DATE,
RELEASED_BY_PERSON_ID,
RELEASED_BY_PERSON,
INVOICE_DATE,
RA_INVOICE_NUMBER,
TRANSFERRED_DATE,
TRANSFER_REJECTION_REASON,
GL_DATE,
SYSTEM_REFERENCE,
(pa_output_tax.GET_DRAFT_INVOICE_TAX_AMT(SYSTEM_REFERENCE)) TAX_AMOUNT,
CANCELED_FLAG,
CANCEL_CREDIT_MEMO_FLAG,
WRITE_OFF_FLAG,
ORG_ID,
INV_CURRENCY_CODE,
INV_RATE_TYPE,
INV_RATE_DATE,
INV_EXCHANGE_RATE,
BILL_TO_ADDRESS_ID,
BILL_TO_ADDRESS,
SHIP_TO_ADDRESS_ID,
SHIP_TO_ADDRESS,
CUSTOMER_ID,
CUSTOMER_NAME,
BILL_TO_CONTACT_ID,
BILL_TO_CONTACT,
SHIP_TO_CONTACT_ID,
SHIP_TO_CONTACT,
REMIT_TO_ADDRESS_ID,
REMIT_TO_ADDRESS,
CREDIT_MEMO_REASON_CODE,
INVOICE_AMOUNT,
DRAFT_INVOICE_NUM_CREDITED,
CONCESSION_FLAG,
ADD_INVOICE_GROUP FROM
PA_DRAFT_CONSOLIDATED_INV_V DCI
WHERE (DCI.draft_invoice_num,DCI.bill_group_id) IN
(SELECT di.consolidated_inv_num,
di.bill_group_id
FROM pa_draft_invoices_all di
WHERE project_id = NVL(
(SELECT project_id FROM pa_projects_all WHERE name =:1
),project_id)
AND project_id = NVL(
(SELECT project_id FROM pa_projects_all WHERE segment1 =:2
),project_id)
AND EXISTS
(SELECT 1
FROM pa_projects_all
WHERE project_status_code = NVL(:3,project_status_code)
AND project_id =di.project_id
)
AND EXISTS
(SELECT 1
FROM pa_project_customers
WHERE customer_id =NVL(
(SELECT customer_id FROM pa_customers_v WHERE customer_number =:4
),customer_id)
AND project_id =di.project_id
)
AND EXISTS
(SELECT 1
FROM pa_project_players
WHERE project_id=di.project_id
AND person_id =NVL(
(SELECT person_id FROM pa_project_players_all_v WHERE full_name=:5
),person_id)
AND person_id =NVL(
(SELECT person_id FROM pa_project_players_all_v WHERE employee_number=:6
),person_id)
)
AND EXISTS
(SELECT 1
FROM pa_projects_all
WHERE CARRYING_OUT_ORGANIZATION_ID = NVL(
(SELECT organization_id FROM hr_all_organization_units WHERE name =:7
),CARRYING_OUT_ORGANIZATION_ID)
AND project_id =di.project_id
)
AND EXISTS
(SELECT 1
FROM pa_project_players
WHERE project_role_type =NVL(
(SELECT project_role_type FROM PA_PROJECT_ROLE_TYPES WHERE meaning=:8
),project_role_type)
AND project_id =di.project_id
)
)
AND invoice_amount BETWEEN NVL(:9,invoice_amount) AND NVL(:10,invoice_amount)
order by BILL_GROUP_ID,
DRAFT_INVOICE_NUM
Steps to Reproduce:
1. Project Billing Super User
2. Billing | Consolidated Invoice Review
3. Enter 'Consolidated Bill Group' and select 'Go'.
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 |