Script to get Cash from Property Manager

CREATE OR REPLACE FUNCTION APPS.PN_CASH(p_payment_item_id number, p_ccid number)
RETURN NUMBER
IS
l_cash_amount       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 account_id = p_ccid;
— 
IF p_payment_item_id != l_max_item_id
THEN
l_cash_amount := 0;
ELSE

select NVL(SUM(i.actual_amount),0)
  into l_cash_amount
  from pn_payment_items_all i, pn_payment_terms_all t, pn_distributions_all d
 where i.payment_item_type_lookup_code = ‘CASH’
   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_schedule_id = l_ps_id
   and account_id = p_ccid;

END IF;
 
  RETURN l_cash_amount;

EXCEPTION
WHEN NO_DATA_FOUND THEN
  RETURN 0;
WHEN OTHERS THEN
  RETURN 0;
END PN_CASH;
/

  • September 24, 2018 | 17 views
  • Comments