Search This Blog

Friday, May 10, 2013

Release AP Hold API: ap_holds_pkg.quick_release


Release AP Hold API: ap_holds_pkg.quick_release

DECLARE
  p_invoice_id ap_invoices_all.invoice_id      % TYPE := 63010; /* Change invoice id to correct value */
  p_inv_batch  ap_batches_all.batch_name       % TYPE := NULL;
  p_hold_code  ap_holds_all.hold_lookup_code   % TYPE ;

  v_hold_cnt NUMBER;
  v_apprvl_sts     ap_invoices.wfapproval_status   % TYPE;
  v_release_reason ap_lookup_codes.description     % TYPE ;
  v_release_code   ap_lookup_codes.lookup_code     % TYPE := 'VALIDATED';

  CURSOR c_inv_on_hold IS
    SELECT hld.hold_lookup_code
         , hld.invoice_id
      FROM ap_holds_all hld
         , ap_invoices_all inv
     WHERE hld.invoice_id = inv.invoice_id
       AND INV.INVOICE_ID = 63010
    ;
BEGIN

   mo_global.set_policy_context('S',9003);
   SELECT description
     INTO v_release_reason
     FROM ap_lookup_codes
    WHERE lookup_type = 'HOLD CODE'
      AND lookup_code =  'VALIDATED'
      AND enabled_flag = 'Y'
      AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active, SYSDATE - 1))
                              AND TRUNC (NVL (inactive_date    , SYSDATE + 1))
    ;


   FOR l_rec in c_inv_on_hold
   LOOP
       dbms_output.put_line('Inside Loop : ' || l_rec.invoice_id);
       ap_holds_pkg.quick_release
       ( x_invoice_id                  => l_rec.invoice_id
       , x_hold_lookup_code            => l_rec.hold_lookup_code
       , x_release_lookup_code         => v_release_code
       , x_release_reason              => v_release_reason
       , x_responsibility_id           => fnd_global.resp_id
       , x_last_updated_by             => fnd_global.user_id
       , x_last_update_date            => SYSDATE
       , x_holds_count                 => v_hold_cnt
       , x_approval_status_lookup_code => v_apprvl_sts
       , x_calling_sequence            => 'xxap_invoice_util_pkg.release_holds'
       ) ;
     
       dbms_output.put_line('Hold count = ' || v_hold_cnt ||
                            ', Approval Status:' || v_apprvl_sts);
   END LOOP;

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Exception:' || sqlerrm);
END;

No comments:

Post a Comment