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:

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

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