Query to get Normalized Exp Amount from Property Manager
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
