Introduction
This Post illustrates the steps required to get Normalized Exp Amount from Property Manager
Script to get Normalized Exp Amount from Property Manager
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;
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
