Monday 24 September 2018

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;

No comments:

Post a Comment