隨手筆記

declare
v_invoice_no varchar2(25):='invoice';
v_term       varchar2(30):='payment term';
v_trx_id     number;
v_term_id    number;
v_date       date;
v_billing_cycle_id number;
begin

select term_id,billing_cycle_id
  into v_term_id,v_billing_cycle_id
  from RA_TERMS_VL 
 where name=v_term;
 
 if v_billing_cycle_id is null then

    select  AR_BFB_UTILS_PVT.GET_DUE_DATE(rct.trx_date,v_term_id),rct.customer_trx_id
      into v_date,v_trx_id
      from ra_customer_trx_all rct
     where rct.trx_number=v_invoice_no;
     
    update ar_payment_schedules_all apc
      set term_id=v_term_id,
          due_date=v_date
    where apc.customer_trx_id=v_trx_id
      and rownum=1;
      
     update ra_customer_trx_all rct
      set term_id =v_term_id
     where rct.trx_number=v_invoice_no
       and rownum=1;
     
 else
     select  AR_BFB_UTILS_PVT.GET_DUE_DATE(AR_BFB_UTILS_PVT.get_billing_date(v_billing_cycle_id,rct.trx_date),v_term_id),rct.customer_trx_id
      into v_date,v_trx_id
      from ra_customer_trx_all rct
     where rct.trx_number=v_invoice_no;
     
    update ar_payment_schedules_all apc
      set term_id=v_term_id,
          due_date=v_date
    where apc.customer_trx_id=v_trx_id
     and rownum=1;
     
     update ra_customer_trx_all rct
      set term_id =v_term_id,
          billing_date =AR_BFB_UTILS_PVT.get_billing_date(v_billing_cycle_id,rct.trx_date)
     where rct.trx_number=v_invoice_no
       and rownum=1;
    
    
 end if;
 
   
 

dbms_output.put_line(v_date);
dbms_output.put_line(v_term_id);
end;