PA Transaction Exception Details by GL Period

Description
Categories: BI Publisher
Columns: Cst Org Id, Cst Exceptions, Period Cst, Exception Reason Cst, Vendor Employee, Project Cst, Task Cst, Expenditure Type Cst, Ei Date, Quantity Cst ...
Application: Projects
Source: EXC: Transaction Exception Details by GL Period (XML)
Short Name: PAXEXGLD_XML
DB package: PA_PAXEXCPD_XMLP_PKG
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
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
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
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
		     )
			 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(cst.org_id,-99)  =  :org_id1
           )
        )
ORDER BY  cst_org_id,period_cst, exception_reason_cst, denom_curr_code_cst
Parameter Name SQL text Validation
From GL Period
 
LOV Oracle
To GL Period
 
LOV Oracle
Exception Category
 
LOV Oracle
Exception Reason
 
LOV Oracle
Show Transaction Exceptions Across Operating Units
 
LOV Oracle
Calling Mode