Cancel Accounts Payable Invoice Example

At my client site, we were testing some customization to invoices and in that process we ended up creating too many invoices. Paying all those invoices would have taken too much time. So i wrote this simple script to cancel unpaid invoices. I used a concurrent program to execute this script so that i do not have to call mo_global.set_policy_context, fnd_global.apps_initialize etc.

DECLARE
  v_errbuf  VARCHAR2(2000);
  v_retcode VARCHAR2(80);
  v_debug_flag VARCHAR2(3) := 'Y';
--
--
PROCEDURE write_fnd_log
( p_message  IN VARCHAR2
, p_msg_type IN VARCHAR2 DEFAULT 'D'
)
IS
BEGIN
   IF p_msg_type = 'E'
   THEN
      fnd_file.put_line (fnd_file.LOG, TO_CHAR (SYSDATE, 'HH24:MI:SS') || ' ERROR: ' || p_message);
   ELSIF p_msg_type = 'W'
   THEN
      fnd_file.put_line (fnd_file.LOG, TO_CHAR (SYSDATE, 'HH24:MI:SS') || ' WARNING: ' || p_message);
   ELSE
      IF p_msg_type = 'I' OR (p_msg_type = 'D' AND v_debug_flag = 'Y')
      THEN
         fnd_file.put_line (fnd_file.LOG, TO_CHAR (SYSDATE, 'HH24:MI:SS') || ' ' || p_message);
      END IF;
   END IF;
END write_fnd_log;
--
PROCEDURE top_cm_cancel
( errbuf             OUT VARCHAR2
, retcode            OUT VARCHAR2
) AS

   --
   -- Cursor to return all unpaid invoices
   --
   CURSOR top_unpaid_memo_cur IS
     SELECT inv.invoice_id
          , inv.invoice_num
          , inv.set_of_books_id
          , inv.gl_date
          , TO_CHAR(inv.gl_date, 'MON-YY') period_name
          , inv.cancelled_date
       FROM ap_invoices_all inv
      WHERE inv.payment_status_flag  = 'N'
        and inv.cancelled_date       IS NULL
        --
        -- Add some filter to cancel desired invoices. rownum filter value should be adjusted
        -- appropriately. in final script i removed rownum filter to cancel all invoices
        --
        and batch_id = 231146
        and rownum < 26
     ;

   -- Variable definition for Cancel API
   v_message_name                 fnd_new_messages.message_name%TYPE;
   v_invoice_amount               ap_invoices.invoice_amount%TYPE;
   v_base_amount                  ap_invoices.base_amount%TYPE;
   v_temp_cancelled_amount        ap_invoices.temp_cancelled_amount%TYPE;
   v_cancelled_by                 ap_invoices.cancelled_by%TYPE;
   v_cancelled_amount             ap_invoices.cancelled_amount%TYPE;
   v_cancelled_date               ap_invoices.cancelled_date%TYPE;
   v_last_update_date             ap_invoices.last_update_date%TYPE;
   v_original_prepayment_amount   NUMBER;
   v_pay_curr_invoice_amount      ap_invoices.pay_curr_invoice_amount%TYPE;
   v_api_return                   BOOLEAN;
   v_error_code                   VARCHAR2(250);
   v_error_msg                    VARCHAR2(2000);
   v_token                        VARCHAR2(2000); -- new parameter in Rel12

   c_procedure_name               VARCHAR2(30);
   v_cancel_fail_count             NUMBER;

BEGIN
   c_procedure_name := 'top_cm_cancel';
   errbuf := NULL;
   retcode := NULL;
   v_cancel_fail_count := 0;
   --
   --
   write_fnd_log( 'Begin TOP_CM_CANCEL ', 'I');

   
   -- Loop through all unpaid credit memo and call the cancel API
   FOR top_unpaid_memo_rec IN top_unpaid_memo_cur
   LOOP

      insert into biv_debug(report_id, message) values ('1', top_unpaid_memo_rec.invoice_id);
      commit;
      --
      -- Cancel the invoice if not already cancelled.
      --
      write_fnd_log('Invoice being Cancelled: ' || top_unpaid_memo_rec.invoice_num);
      v_api_return :=
      AP_CANCEL_PKG.ap_cancel_single_invoice
      ( p_invoice_id                 => top_unpaid_memo_rec.invoice_id
      , p_last_updated_by            => fnd_global.user_id
      , p_last_update_login          => fnd_global.login_id
      , p_accounting_date            => top_unpaid_memo_rec.gl_date
      , p_message_name               => v_message_name
      , p_invoice_amount             => v_invoice_amount
      , p_base_amount                => v_base_amount
      , p_temp_cancelled_amount      => v_temp_cancelled_amount
      , p_cancelled_by               => v_cancelled_by
      , p_cancelled_amount           => v_cancelled_amount
      , p_cancelled_date             => v_cancelled_date
      , p_last_update_date           => v_last_update_date
      , p_original_prepayment_amount => v_original_prepayment_amount
      , p_pay_curr_invoice_amount    => v_pay_curr_invoice_amount
      , p_token                      => v_token
      , p_calling_sequence           => 'XXAP_TOP_UTIL_PKG->xxap_top_cm_cancel'
      ) ;
  
      IF v_api_return = FALSE THEN
         v_cancel_fail_count := v_cancel_fail_count + 1;
         write_fnd_log('Failed to cancel Invoice # - '||top_unpaid_memo_rec.invoice_num, 'I' );
         write_fnd_log(v_message_name, 'E');
         retcode := '-1';
         errbuf := 'TOP CM Cancel procedure could not cancel some of the Credit memos';
      ELSE
         write_fnd_log('Invoice :' || top_unpaid_memo_rec.invoice_num || ' cancelled sucessfully', 'I');
      END IF;

   END LOOP;

   write_fnd_log( 'End of Procedure TOP_CM_CANCEL ', 'I');
   --
   --
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      retcode := '-2';
      v_error_code := SQLCODE;
      v_error_msg := SUBSTR (SQLERRM, 1, 250);
      errbuf := v_error_msg;

      write_fnd_log (   TO_CHAR (v_error_code) || ' - ' || v_error_msg, 'E');
      write_fnd_log (   c_procedure_name || ' script failed on ' || SYSDATE, 'E');

END top_cm_cancel;
BEGIN
      insert into biv_debug(report_id, message) values ('1', 'Start');
  top_cm_cancel(v_errbuf, v_retcode);
END;
/