Script to release AP Invoice holds using the standard API 

In certain situations we have to complete the Invoice Validation and Accounting process for testing.
During validation an Invoice can be placed on Hold due to reasons like "Encumbrance Accounting Failed" or some other reasons which are difficult to track and resolve.  The following script be executed from APPS schema to release the hold placed on the invoice.

DECLARE
  p_invoice_id ap_invoices_all.invoice_id      % TYPE := 25010; /* Change invoice id to correct value */
  p_inv_batch  ap_batches_all.batch_name       % TYPE := 'TEST17SEP1.1';
  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 lcu_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',82);
 
 
   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 lr_rec in lcu_inv_on_hold
   LOOP
       dbms_output.put_line('Inside Loop : ' || lr_rec.invoice_id);
       ap_holds_pkg.quick_release
       ( x_invoice_id                  => lr_rec.invoice_id
       , x_hold_lookup_code            => lr_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;

COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Oracle Exception:' || SUBSTR(sqlerrm,1,500));
END;