PA Transaction Exception Summary by GL Period

Description
Categories: BI Publisher
Columns: Uncst Org Id, Uncst Sob, Uncst Warnings, Period Uncst, Exception Reason Uncst, Corrective Action Uncst, Uncost Denom Currency Code, Uncst Ei Lines, Total Acct Amount Uncst, Total Denom Amount Uncst ...
Application: Projects
Source: EXC: Transaction Exception Summary by GL Period (XML)
Short Name: PAXEXGLS_XML
DB package: PA_PAXEXCPS_XMLP_PKG
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
From GL Period
 
LOV Oracle
To GL Period
 
LOV Oracle
Show Transaction Exceptions Across Operating Units
 
LOV Oracle
Calling Mode