PA Transaction Exception Details by PA Period

SELECT
imp.org_id  cst_org_id,
'Costing Exceptions' Cst_Exceptions,
cst.period_name Period_Cst,
cst.exception_reason Exception_Reason_Cst,
DECODE(cst.vendor_number, NULL, cst.person_name,
cst.vendor_number) Vendor_Employee,
cst.project_number Project_Cst,
cst.task_number Task_Cst,
cst.expenditure_type Expenditure_Type_Cst,
cst.expenditure_item_date EI_Date,
cst.quantity  quantity_cst,
substr(cst.expenditure_group,1,20)  Group_Name,
cst.amount Amount_Cst,
cst.denom_currency_code denom_curr_code_cst,
cst.denom_amount denom_amount_cst,
lk.meaning unit_cst,
	PA_PAXEXCPD_XMLP_PKG.cf_cst_ou_nameformula(imp.org_id) CF_cst_ou_name,
	cst.corrective_action Corrective_Action_Cst
FROM
pa_cost_exceptions_det_v cst,
pa_expenditure_types et,
pa_lookups lk,
pa_exception_reasons    pl,
pa_implementations  imp,
pa_projects_all  pp
 WHERE
cst.expenditure_type = et.expenditure_type
AND  NVL(cst.unit_of_measure,et.unit_of_measure) = lk.lookup_code AND   lk.lookup_type = 'UNIT'
AND   cst.pa_date BETWEEN nvl(:start_date, cst.pa_date)
	AND           nvl(:end_date, cst.pa_date)
 AND cst.exception_reason = pl.exception_reason
  AND  :exception_type  = 'COST_EXCP'
   AND  pl.exception_category = 'COST_EXCP'
   AND   pl.exception_code = :p_exception_reason
  AND :p_exception_reason is not null
  AND  :calling_mode  =  'PA'
 --AND  :calling_mode_1  =  'PA'
  AND  nvl(imp.org_id, -99)  =  nvl(pp.org_id ,-99)
  AND  pp.project_id  = cst.project_id
 AND  (  :pa_new_gl_date =  'N'
                OR
              ( :pa_new_gl_date  =  'Y'
                and  nvl(imp.same_pa_gl_period , 'N')  =  'Y'
              )
           )
UNION
SELECT
     imp.org_id   cst_org_id,
     'Costing Exceptions' Cst_Exceptions,
     cst.period_name Period_Cst,
     cst.exception_reason Exception_Reason_Cst,
     DECODE(cst.vendor_number, NULL, cst.person_name,
                     cst.vendor_number) Vendor_Employee,
     cst.project_number Project_Cst,
     cst.task_number Task_Cst,
     cst.expenditure_type Expenditure_Type_Cst,
     cst.expenditure_item_date EI_Date,
     cst.quantity  quantity_cst,
     substr(cst.expenditure_group,1,20)  Group_Name,
     cst.amount Amount_Cst,
     cst.denom_currency_code denom_curr_code_cst,
     cst.denom_amount denom_amount_cst,
     lk.meaning unit_cst,
	PA_PAXEXCPD_XMLP_PKG.cf_cst_ou_nameformula(imp.org_id) CF_cst_ou_name,
	cst.corrective_action Corrective_Action_Cst
FROM
            pa_cost_exceptions_det_v cst,
            pa_expenditure_types et,
            pa_lookups lk,
            pa_implementations  imp,
            pa_projects_all  pp
  WHERE
      cst.expenditure_type = et.expenditure_type
AND  NVL(cst.unit_of_measure,et.unit_of_measure) = lk.lookup_code AND   lk.lookup_type = 'UNIT'
AND   cst.pa_date BETWEEN nvl(:start_date, cst.pa_date)
                    AND           nvl(:end_date, cst.pa_date)
AND   nvl(:exception_type, 'COST_EXCP') = 'COST_EXCP'
AND :p_exception_reason is null
AND  :calling_mode  =  'PA'
--AND  :calling_mode_1  =  'PA'
AND  nvl(imp.org_id, -99)  =  nvl(pp.org_id ,-99)
AND  pp.project_id  = cst.project_id
AND  (  :pa_new_gl_date  =  'N'
                OR
              ( :pa_new_gl_date  =  'Y'
                and  nvl(imp.same_pa_gl_period , 'N')  =  'Y'
              )
           )
UNION
SELECT
     cst.org_id   cst_org_id,
     'Costing Exceptions' Cst_Exceptions,
     cst.period_name Period_Cst,
     cst.exception_reason Exception_Reason_Cst,
     DECODE(cst.vendor_number, NULL, cst.person_name,
                     cst.vendor_number) Vendor_Employee,
     cst.project_number Project_Cst,
     cst.task_number Task_Cst,
     cst.expenditure_type Expenditure_Type_Cst,
     cst.expenditure_item_date EI_Date,
     cst.quantity  quantity_cst,
     substr(cst.expenditure_group,1,20)  Group_Name,
     cst.amount Amount_Cst,
     cst.denom_currency_code denom_curr_code_cst,
     cst.denom_amount denom_amount_cst,
     lk.meaning unit_cst,
	PA_PAXEXCPD_XMLP_PKG.cf_cst_ou_nameformula(cst.org_id) CF_cst_ou_name,
	 cst.corrective_action Corrective_Action_Cst
FROM
            pa_gl_cost_except_det_v cst,
            pa_expenditure_types et,
            pa_lookups lk
 WHERE
      cst.expenditure_type = et.expenditure_type
AND   NVL(cst.unit_of_measure,et.unit_of_measure) = lk.lookup_code AND   lk.lookup_type = 'UNIT'
AND   cst.gl_date BETWEEN nvl(:start_date, cst.gl_date)
                    AND           nvl(:end_date, cst.gl_date)
AND  (   ( nvl(:exception_type, 'COST_EXCP') = 'COST_EXCP'
                AND :p_exception_reason is null
              )
            OR
            ( cst.exception_reason in (  select  pl.exception_reason
                                                           from    pa_exception_reasons pl
                                                           where pl.exception_category = 'COST_EXCP'
                                                            and    pl.exception_code = :p_exception_reason
                                                       )
            AND   :exception_type  = 'COST_EXCP'
            AND :p_exception_reason is not null
           )
        )
AND  :calling_mode  =  'GL'
--AND  :calling_mode_1  =  'GL'
AND  :pa_new_gl_date  =  'Y'
AND nvl(cst.same_pa_gl_period , 'N')  in  ('N', 'Y')
 AND  (   ( :across_ous  =  'Y'
	  and Exists (Select 1 From HR_ORG_UNITS_NO_JOIN hr
	               Where hr.ORGANIZATION_ID = cst.org_id
		     )
            )
            OR
           ( :across_ous  = 'N'
              and nvl(cst.org_id,-99)  =  :org_id1
           )
        )
ORDER BY  cst_org_id,period_cst, exception_reason_cst, denom_curr_code_cst