PAXEXCPD: Performance Issue When Running "EXC: Transaction Exception Details By PA Period" (Doc ID 2861514.1)

Last updated on AUGUST 03, 2023

Applies to:

Symptoms

Performance issue when running the EXC: TRANSACTION EXCEPTION DETAILS BY PA PERIOD.
This report takes hours to run for one period.

Steps to reproduce:

1. Using a Projects responsibility, navigate to Other-->Requests-->Run
2. Select Name = EXC: Transaction Exception Details by PA Period

The most expensive query is:

SELECT
rcv.org_id Receipt_org_id
,'Receipt Exceptions' Receipt_Exceptions
,rcv.period_name Receipt_period
,rcv.exception_reason Receipt_Exception_Reason
,rcv.CORRECTIVE_ACTION Receipt_Corrcetive_reason
,rcv.RECEIPT_NUMBER Receipt_Number
,rcv.GL_DATE Receipt_Date
,rcv.vendor_number Receipt_Supplier_Number
,substr(rcv.vendor_name,1,80) Receipt_Supplier_Name -- Bug 2487415
adding substr for UTF8
,rcv.PO_NUM Receipt_Po_num
,rcv.LINE_NUM Receipt_po_line_num
,rcv.SHIPMENT_NUM Receipt_ship_num
,rcv.DISTRIBUTION_NUM Receipt_dist_num
,rcv.project_number Receipt_project
,rcv.task_number Receipt_task
,rcv.expenditure_type Receipt_exp_type
,rcv.amount Receipt_amount
,substr(rcv.OU_name,1,60) Receipt_OU_name --bug 2487147 adding substr for UTF8
,rcv.set_of_books_id Receipt_books
FROM
pa_rcv_exceptions_det_v rcv
WHERE rcv.pa_date BETWEEN : start_date AND : end_date AND nvl ( :
exception_type , 'RECEIPT_EXCP' ) = 'RECEIPT_EXCP' AND rcv.exception_code =
nvl ( : p_exception_reason , rcv.exception_code ) AND : calling_mode = 'PA'
AND ( : pa_new_gl_date = 'N' OR ( : pa_new_gl_date = 'Y' AND nvl (
rcv.same_pa_gl_period , 'N' ) in ( 'Y' , 'N' ) ) ) AND nvl ( rcv.org_id , -
99 ) = : org_id1 UNION SELECT
rcv.org_id Receipt_org_id
,'Receipt Exceptions' Receipt_Exceptions
,rcv.period_name Receipt_period
,rcv.exception_reason Receipt_Exception_Reason
,rcv.CORRECTIVE_ACTION Receipt_Corrcetive_reason
,rcv.RECEIPT_NUMBER Receipt_Number
,rcv.GL_DATE Receipt_Date
,rcv.vendor_number Receipt_Supplier_Number
,substr(rcv.vendor_name,1,80) Receipt_Supplier_Name -- Bug 2487415 adding substr for UTF8
,rcv.PO_NUM Receipt_Po_num
,rcv.LINE_NUM Receipt_po_line_num
,rcv.SHIPMENT_NUM Receipt_ship_num
,rcv.DISTRIBUTION_NUM Receipt_dist_num
,rcv.project_number Receipt_project
,rcv.task_number Receipt_task
,rcv.expenditure_type Receipt_exp_type
,rcv.amount Receipt_amount
,substr(rcv.OU_name,1,60) Receipt_OU_name--bug 2487147 adding substr for UTF8
,rcv.set_of_books_id Receipt_books
FROM
pa_rcv_exceptions_det_v rcv
WHERE rcv.pa_date BETWEEN : start_date AND : end_date AND nvl ( :
exception_type , 'RECEIPT_EXCP' ) = 'RECEIPT_EXCP' AND rcv.exception_code =
nvl ( : p_exception_reason , rcv.exception_code ) AND : calling_mode = 'GL'
AND : pa_new_gl_date = 'Y' AND nvl ( rcv.same_pa_gl_period , 'N' ) = 'Y' AND
( ( : across_ous = 'Y' and set_of_books_id = ( select set_of_books_id from
pa_implementations where org_id = nvl ( org_id , - 99 ) ) ) OR ( : across_ous
= 'N' and nvl ( rcv.org_id , - 99 ) = ( select nvl ( paimp.org_id , - 99 )
from pa_implementations paimp ) ) ) ORDER BY 2 ASC,18 ASC,1 ASC,19 ASC,3
ASC,4 ASC,5 ASC , 1 , 3 , 4

Patch 28413277:R12.PJC.C has been applied, but the performance issue still exists.

Cause

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