PAXEXGLD: After Upgrade to 19C EXC: Transaction Exception Details by GL Period Report has Performance Issues (Doc ID 2988889.1)

SELECT  
                   imp.org_id  org_id_pmg,
'Inventory/Project Manufacturing Exceptions' Pmg_Exceptions,
pap.period_name Period_Pmg,
pm.user_reason Exception_Reason_Pmg,
substr(org.name, 1, 60) Organization,  /* Bug No. 2487147,  UTF8 change,
used substr */
pm.orig_transaction_table Orig_Table,
pm.transaction_id,
prj.segment1 Project,
tsk.task_number Task
FROMcst_pm_pending_txn_v pm,
pa_projects_all prj,
pa_taskstsk,
hr_organization_units org,
pa_periods                   pap,
                   pa_implementations   imp
 WHERE     pm.project_id =  prj.project_id
ANDpm.task_id      =  tsk.task_id(+)  -- fix for bug 4626821 
ANDpm.organization_id = org.organization_id
ANDtrunc(pm.transaction_date) BETWEEN trunc(:start_date)
AND          trunc(:end_date) /*Bug#3840484*/
ANDtrunc(pm.transaction_date) BETWEEN trunc(pap.start_date)
ANDtrunc(pap.end_date) /*Bug#3840484*/ /* trunc for bug 8802938 
*/
AND   nvl(:exception_type,  'MFG_EXCP') = 'MFG_EXCP'
AND   pm.exception_code = nvl(:p_exception_reason,  pm.exception_code)
AND   :calling_mode  = 'PA'
AND    nvl(imp.org_id , -99)  = nvl(pap.org_id , -99)
AND    nvl(imp.org_id, -99) = nvl(prj.org_id, -99)  /* bug#2830303  

 */
AND   (  :pa_new_gl_date = 'N'
               OR
             ( :pa_new_gl_date = 'Y'
                AND  nvl(imp.same_pa_gl_period , 'N')  in ('N',  'Y')
             )
           )
AND  NOT EXISTS
              ( SELECT 1 FROM gms_implementations_all WHERE org_id=imp.org_id
AND enabled ='Y')
               /*Bug 17530628 : Added above not exist condition because grants does not support inventory Transaction*/
 
/** Added for pa gl period enhancement project **/
UNION
SELECT /*+ leading ( imp pap pm)  push_pred ( pm ) */
    imp.org_id org_id_pmg,   
'Inventory/Project Manufacturing Exceptions' Pmg_Exceptions,
pap.period_name Period_Pmg,
pm.user_reason Exception_Reason_Pmg,
substr(org.name, 1, 60) Organization,  /*Bug No. 2487147,  UTF8 change,
used substr */
pm.orig_transaction_table Orig_Table,
pm.transaction_id,
prj.segment1 Project,
tsk.task_number Task
FROMcst_pm_pending_txn_v pm,
pa_projects_all prj,
pa_taskstsk,
hr_organization_units org,
pa_periods_all              pap,
                   pa_implementations_all  imp
 WHERE    (select pm.project_id from dual) =  prj.project_id  /* Modified for Bug 28100115   */
ANDpm.task_id      =  tsk.task_id(+) -- fix for bug 4626821 
AND    (select pm.organization_id from dual) = org.organization_id /*Modified
for Bug28100115*/
AND pm.transaction_date  BETWEEN trunc(:start_date) AND trunc(:end_date) +0.9999
 /* Modified for Bug 28100115  

 */   /*Bug# 3840484*/ /* trunc for bug 8802938  */
AND pm.transaction_date BETWEEN trunc(pap.start_date) AND trunc(pap.end_date)+ 0.9999
 /* Modified for Bug 28100115   */
AND   nvl(:exception_type,  'MFG_EXCP') = 'MFG_EXCP'
AND   pm.exception_code = nvl(:p_exception_reason,  pm.exception_code)
AND   :calling_mode   =  'GL'
AND   nvl(imp.org_id , -99) = nvl(pap.org_id , -99)
AND   nvl(imp.org_id, -99) = nvl(prj.org_id, -99)  /* bug#2830303  */
AND   :pa_new_gl_date = 'Y'
AND   nvl(imp.same_pa_gl_period, 'N')  = 'Y'
AND   ( ( :across_ous  =  'Y'
  and Exists (Select 1 From HR_ORG_UNITS_NO_JOIN hr
               Where hr.ORGANIZATION_ID = imp.org_id
     )
         ) OR
              ( :across_ous  =  'N'
             and  nvl(imp.org_id , -99)  =  :org_id1
               )
           )
AND  NOT EXISTS
              ( SELECT 1 FROM gms_implementations_all WHERE org_id=imp.org_id
AND enabled ='Y')
                /* Bug 17530628  : Added above not exist condition because grants does not support inventory Transaction*/
/* Added for 4165751*/
UNION
 select
  pti.org_id org_id_pmg,
  'Inventory/Project Manufacturing Exceptions' Pmg_Exceptions,
  pa_utils2.get_pa_period_name( pti.expenditure_item_date,
                                pti.org_id) Period_Pmg,
  pa_exception_reasons_pub.get_exception_text('MFG_EXCP',
  'CCRTIN',
  NULL,
  'R')  Exception_Reason_Pmg,
  substr(Organization_name, 1, 60) Organization,
  1,
  to_number(pti.orig_transaction_reference),
  pti.project_number,
  pti.task_number
  from
  pa_transaction_interface pti
  where
  pti.transaction_source = 'Inventory Misc' and
  nvl(:exception_type,  'MFG_EXCP') = 'MFG_EXCP' AND
  nvl(:p_exception_reason,  'CCRTIN') = 'CCRTIN' AND
  pti.transaction_status_code = 'P' and
  pti.system_linkage = 'INV'
  andpti.expenditure_item_date BETWEEN :start_date  and  :end_date
  and   nvl(:exception_type,  'MFG_EXCP') = 'MFG_EXCP'
  AND   ( ( :across_ous  =  'Y'
  and Exists (Select 1 From HR_ORG_UNITS_NO_JOIN hr
               Where hr.ORGANIZATION_ID = pti.org_id
     )
            )
              OR
              ( :across_ous  =  'N'
             and  nvl(pti.org_id , -99)  =  :org_id1
               )
           )
ORDER BY
    org_id_pmg, period_pmg,  Exception_Reason_Pmg