PA Transaction Exception Summary by PA Period

Description
Categories: BI Publisher
Columns: Uncst Org Id, Uncst Sob, Uncst Warnings, Period Uncst, Exception Reason Uncst, Uncost Denom Currency Code, Uncst Ei Lines, Total Acct Amount Uncst, Total Denom Amount Uncst, Corrective Action Cst ...
Application: Projects
Source: EXC: Transaction Exception Summary by PA Period (XML)
Short Name: PAXEXCPS_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.pa_period_name Period_unCst,
     uncst.exception_reason Exception_Reason_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,
     uncst.corrective_action Corrective_Action_Cst, 
	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_SPECIFIC('PA_EN_NEW_GLDATE_DERIVATION'),'N')  =  'N'
                 OR
                (nvl(fnd_profile.VALUE_SPECIFIC('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.pa_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.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,
     uncst.corrective_action Corrective_Action_Cst
	 --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_SPECIFIC('PA_EN_NEW_GLDATE_DERIVATION'),'N') = 'Y'
     and  nvl(uncst.same_pa_gl_period ,'N')  in ('N', 'Y')
     and  ( :across_ous  =  'Y'
               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 PA Period
 
LOV Oracle
To PA Period
 
LOV Oracle