CREATE OR REPLACE FUNCTION APPS.PN_NORM_EXP(p_payment_item_id number, p_ccid number)
RETURN NUMBER
IS
l_accrued_amount number;
l_cash_amount number;
l_norm_exp number;
l_max_item_id number;
l_ps_id number;
BEGIN
--
select payment_schedule_id
into l_ps_id
from pn_payment_items_all where payment_item_id = p_payment_item_id;
--
select max(i.payment_item_id)
into l_max_item_id
from pn_payment_items_all i, pn_payment_terms_all t, pn_distributions_all d
where i.payment_schedule_id = l_ps_id
and i.payment_term_id = t.payment_term_id
and d.payment_term_id = t.payment_term_id
and t.normalize = 'Y'
and account_class = 'EXP'
and payment_item_type_lookup_code = 'NORMALIZED'
and account_id = p_ccid;
--
IF p_payment_item_id != l_max_item_id
THEN
l_norm_exp := 0;
ELSE
--
select NVL(SUM(i.actual_amount),0)
into l_accrued_amount
from pn_payment_items_all i, pn_payment_terms_all t, pn_distributions_all d
where i.payment_schedule_id = l_ps_id
and i.payment_term_id = t.payment_term_id
and d.payment_term_id = t.payment_term_id
and t.normalize = 'Y'
and account_class = 'EXP'
and payment_item_type_lookup_code = 'NORMALIZED'
and account_id = p_ccid;
--
select NVL(SUM(actual_amount),0)
into l_cash_amount
from pn_payment_items_all ci
where payment_schedule_id = l_ps_id
and payment_item_type_lookup_code = 'CASH'
and exists (select 1
from pn_payment_items_all i, pn_payment_terms_all t, pn_distributions_all d
where i.payment_schedule_id = l_ps_id
and i.payment_term_id = t.payment_term_id
and d.payment_term_id = t.payment_term_id
and t.normalize = 'Y'
and account_class = 'EXP'
and payment_item_type_lookup_code = 'NORMALIZED'
and account_id = p_ccid
and ci.payment_term_id = i.payment_term_id);
--
l_norm_exp := l_accrued_amount - l_cash_amount;
--
END IF;
RETURN l_norm_exp;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
WHEN OTHERS THEN
RETURN 0;
END PN_NORM_EXP;
RETURN NUMBER
IS
l_accrued_amount number;
l_cash_amount number;
l_norm_exp number;
l_max_item_id number;
l_ps_id number;
BEGIN
--
select payment_schedule_id
into l_ps_id
from pn_payment_items_all where payment_item_id = p_payment_item_id;
--
select max(i.payment_item_id)
into l_max_item_id
from pn_payment_items_all i, pn_payment_terms_all t, pn_distributions_all d
where i.payment_schedule_id = l_ps_id
and i.payment_term_id = t.payment_term_id
and d.payment_term_id = t.payment_term_id
and t.normalize = 'Y'
and account_class = 'EXP'
and payment_item_type_lookup_code = 'NORMALIZED'
and account_id = p_ccid;
--
IF p_payment_item_id != l_max_item_id
THEN
l_norm_exp := 0;
ELSE
--
select NVL(SUM(i.actual_amount),0)
into l_accrued_amount
from pn_payment_items_all i, pn_payment_terms_all t, pn_distributions_all d
where i.payment_schedule_id = l_ps_id
and i.payment_term_id = t.payment_term_id
and d.payment_term_id = t.payment_term_id
and t.normalize = 'Y'
and account_class = 'EXP'
and payment_item_type_lookup_code = 'NORMALIZED'
and account_id = p_ccid;
--
select NVL(SUM(actual_amount),0)
into l_cash_amount
from pn_payment_items_all ci
where payment_schedule_id = l_ps_id
and payment_item_type_lookup_code = 'CASH'
and exists (select 1
from pn_payment_items_all i, pn_payment_terms_all t, pn_distributions_all d
where i.payment_schedule_id = l_ps_id
and i.payment_term_id = t.payment_term_id
and d.payment_term_id = t.payment_term_id
and t.normalize = 'Y'
and account_class = 'EXP'
and payment_item_type_lookup_code = 'NORMALIZED'
and account_id = p_ccid
and ci.payment_term_id = i.payment_term_id);
--
l_norm_exp := l_accrued_amount - l_cash_amount;
--
END IF;
RETURN l_norm_exp;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
WHEN OTHERS THEN
RETURN 0;
END PN_NORM_EXP;
