Wednesday 10 June 2015

Release Hold on Payables Invoices API in oracle apps

DECLARE
  p_invoice_id ap_invoices_all.invoice_id      % TYPE := -1; /* 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 := 'Encumbrance Acctg Fail';

  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 := 'Encumbrance Acctg Ok';

  CURSOR c_inv_on_hold IS
    SELECT hld.hold_lookup_code
         , hld.invoice_id
      FROM ap_holds hld
         , ap_invoices inv
         , ap_batches_all  btch
     WHERE hld.invoice_id = inv.invoice_id
       AND btch.batch_id = inv.batch_id
       AND (p_invoice_id     IS NULL or p_invoice_id = inv.invoice_id      )
       AND (p_inv_batch      IS NULL or p_inv_batch  = btch.batch_name     )
       AND (p_hold_code      IS NULL or p_hold_code  = hld.hold_lookup_code)
    ;
BEGIN

   mo_global.set_policy_context('S',':ORG_ID');
   SELECT description
     INTO v_release_reason
     FROM ap_lookup_codes
    WHERE lookup_type = 'HOLD CODE'
      AND lookup_code = v_release_code
      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;

1 comment:

  1. It should be noted that according to Oracle document ID 2094785.1, the AP_HOLDS_PKG is a private package, and is not a public API. It's use is not supported.

    ReplyDelete