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 |