PA Transaction Exception Summary by GL Period

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Transaction Exceptions Summary by GL Period
Application: Projects
Source: EXC: Transaction Exception Summary by GL Period (XML)
Short Name: PAXEXGLS_XML
DB package: PA_PAXEXCPS_XMLP_PKG
Run PA Transaction Exception Summary by GL Period and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT
     nvl(uncst.org_id, -99)  uncst_org_id,
     uncst.set_of_books_id  uncst_sob,
     'UnCosted Exceptions'  unCst_warnings,
     uncst.period_name Period_unCst,
     uncst.exception_reason Exception_Reason_unCst,
	 uncst.corrective_action Corrective_Action_unCst,
     uncst.denom_currency_code  uncost_denom_currency_code,
     COUNT(*)  unCst_ei_Lines,
     SUM(uncst.acct_burdened_amount)  Total_acct_amount_unCst,
     SUM(uncst.denom_burdened_amount) Total_denom_amount_uncst,
	PA_PAXEXCPS_XMLP_PKG.cf_uncst_ou_nameformula(nvl ( uncst.org_id , - 99 )) CF_uncst_ou_name, 
	PA_PAXEXCPS_XMLP_PKG.cf_uncst_sob_nameformula(uncst.set_of_books_id) CF_uncst_sob_name
FROM
     pa_gl_uncost_except_sum_v  uncst,
     pa_implementations  imp
     WHERE  :calling_mode  = 'PA'  AND 
     nvl(imp.org_id,-99)  =  nvl(uncst.org_id,-99)  
     and  uncst.set_of_books_id  = imp.set_of_books_id  
     and ( (uncst.system_linkage_function  <> 'PJ' 
                and uncst.expenditure_item_date BETWEEN :start_date AND :end_date)
              OR
              ( uncst.system_linkage_function  = 'PJ'
                and  NVL(uncst.prvdr_accrual_date,uncst.expenditure_item_date )  
                         BETWEEN :start_date AND :end_date ) 
             )   
     and   ( nvl(fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION'),'N')  =  'N'
                 OR
                (nvl(fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION'),'N')  =  'Y'
                  and  (nvl(imp.same_pa_gl_period,'N')  in  ('N', 'Y'))
                )
              )
GROUP BY
    uncst.org_id,uncst.set_of_books_id 
    ,3, uncst.period_name, uncst.exception_reason, uncst.denom_currency_code, uncst.corrective_action
UNION
SELECT
     nvl(uncst.org_id, -99)  uncst_org_id,
     uncst.set_of_books_id  uncst_sob,
     'UnCosted Exceptions'  unCst_warnings,
     uncst.period_name Period_unCst,
     uncst.exception_reason Exception_Reason_unCst,
	 uncst.corrective_action Corrective_Action_unCst,
     uncst.denom_currency_code  uncost_denom_currency_code,
     COUNT(*)  unCst_ei_Lines,
     SUM(uncst.acct_burdened_amount)  Total_acct_amount_unCst,
     SUM(uncst.denom_burdened_amount) Total_denom_amount_uncst,
	 --ADDED
	 PA_PAXEXCPS_XMLP_PKG.cf_uncst_ou_nameformula(nvl ( uncst.org_id , - 99 )) CF_uncst_ou_name, 
	PA_PAXEXCPS_XMLP_PKG.cf_uncst_sob_nameformula(uncst.set_of_books_id) CF_uncst_sob_name
FROM
     pa_gl_uncost_except_sum_v  uncst
WHERE  :calling_mode  = 'GL'     
  and ( (uncst.system_linkage_function  <> 'PJ' 
                and uncst.expenditure_item_date BETWEEN :start_date AND :end_date)
              OR
              ( uncst.system_linkage_function  = 'PJ'
                and  NVL(uncst.prvdr_accrual_date,uncst.expenditure_item_date )  
                         BETWEEN :start_date AND :end_date ) 
             )   
     and  nvl(fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION'),'N') = 'Y'
     and  nvl(uncst.same_pa_gl_period ,'N')  in ('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(uncst.org_id ,-99)  = (
select nvl(paimp.org_id,-99)
from pa_implementations  paimp)
               )
             )
GROUP BY
    uncst.org_id,uncst.set_of_books_id 
    ,3, uncst.period_name, uncst.exception_reason, uncst.denom_currency_code, uncst.corrective_action
Parameter Name SQL text Validation
Operating Unit
 
LOV
From GL Period
 
LOV Oracle
To GL Period
 
LOV Oracle
Show Transaction Exceptions Across Operating Units
 
LOV Oracle
Blitz Report™