R12 – How to Delete Oracle AR Transactions

User can able to delete the AR transaction in below cases are met:

• The invoice in the incomplete status.
• The invoice doesn’t posted.
• This is no receipt applied to the invoice.

Note:

• You might miss your invoice document sequence.
• Based on company policy, whether you can keep the document or remove from system.

If the Invoice in ‘complete‘ status, create a Credit Memo against this invoice and nullify the transaction.

AR invoice transaction using this package ‘ar_invoice_api_pub.delete_transaction’ in R12

Step 1: Incomplete the invoice.
— For R12 ——
–AR_TRANSACTION_GRP.INCOMPLETE_TRANSACTION

SET SERVEROUTPUT ON;
DECLARE 
   l_return_status    VARCHAR2 (100);
   l_message_count    NUMBER;
   l_message_data     VARCHAR2 (1000);
   lv_error_message   VARCHAR2 (800);
BEGIN
   mo_global.set_policy_context ('S', 800);
   
   fnd_global.apps_initialize(0,51234,222);
   
   ar_transaction_grp.incomplete_transaction
                           (p_api_version           => '1.0',
                            p_init_msg_list         => fnd_api.g_true,
                            p_commit                => fnd_api.g_false,
                            p_validation_level      => fnd_api.g_valid_level_none,
                            p_customer_trx_id       => 198364532,
                            x_return_status         => l_return_status,
                            x_msg_count             => l_message_count,
                            x_msg_data              => l_message_data
                           );
   COMMIT;

   IF l_return_status IN ('E', 'U')
   THEN
      FOR i IN 1 .. l_message_count
      LOOP
         lv_error_message :=
                         lv_error_message || '--' || fnd_msg_pub.get (i, 'F');
         DBMS_OUTPUT.put_line ('l_return_status- ' || l_return_status);
      END LOOP;

      DBMS_OUTPUT.put_line (   'API Failed. Error:'
                            || SUBSTR (lv_error_message, 1, 800)
                           );
   ELSE
      DBMS_OUTPUT.put_line ('AR Invoice Incompleted successfully');
      DBMS_OUTPUT.put_line ('lv_return_status-' || l_return_status);
   END IF;
END ;

Step 2: Check the following Prerequisites:

1. Check if ‘Allow Transaction Deletion’ flag is Yes, If “No”, check the flag for respective operating unit.
   Navigation : Receivables -> Setup -> System -> System Options
   >> Query for respective operating unit
   >> Click on ‘Trans and Customers’ Tab > Check for ‘Allow Transaction Deletion’ flag.
2. Transaction Should be incomplete.
3. There should be no activity against the transaction like it must neither be applied, printed or posted to GL etc.

Step 3: Sample script for invoice deletion

------------------- Create Custom Table ---- For your Deleting Data Set --------------
CREATE TABLE OEAG.XXERP_AR_DELETE_TRANS
AS
   SELECT CUSTOMER_TRX_ID,
          TRX_DATE,
          TRX_NUMBER,
          BILL_TO_CUSTOMER_ID,
          BILL_TO_SITE_USE_ID,
          ORG_ID,
          CREATION_DATE,
          CREATED_BY,
          'N' V_RET_STATUS,
          'N' V_MSG_COUNT,
          'N' V_MSG_DATA,
          'N' V_MESSAGE_TBL
     FROM RA_CUSTOMER_TRX_ALL     
    WHERE ORG_ID = 800 
      AND CUST_TRX_TYPE_ID = 1265      
      AND TRX_DATE = '20-MAR-2019';   

---------------- START --- AR INVOICE DATA DELETE PROGRAM ---------------------
SET SERVEROUTPUT ON;
DECLARE
   CURSOR cur_all_trx
   IS
     
    SELECT ROWID,
             NULL PARTY_NUMBER,
             RCT.ORG_ID,
             RCT.CUSTOMER_TRX_ID,
             RCT.TRX_NUMBER
        FROM OEAG.XXERP_AR_DELETE_TRANS RCT
       WHERE NVL (V_RET_STATUS, 0) <> 'S'
         AND TRX_NUMBER = '900918263';
       

   xv_msg_data     VARCHAR2 (4000) := NULL;
   xv_msg_count    NUMBER := 0;
   v_msg_index     NUMBER := 0;
   xv_ret_status   VARCHAR2 (1) := NULL;
   v_message_tbl   arp_trx_validate.message_tbl_type;
   v_res           VARCHAR2 (4000) := NULL;
   v_res_name      VARCHAR2 (4000) := NULL;
   v_app           VARCHAR2 (4000) := NULL;
   v_user          NUMBER := 1110;
BEGIN
   DBMS_OUTPUT.put_line ('Detele Transaction...');

   FOR c_rec IN cur_all_trx
   LOOP
      DBMS_OUTPUT.put_line ('   Transaction No.: ' || c_rec.trx_number);
      DBMS_OUTPUT.put_line ('   Transaction ID : ' || c_rec.customer_trx_id);
      DBMS_OUTPUT.put_line ('   Org ID         : ' || c_rec.org_id);
      ----------------------------------------------------------------------------
      ---- Setting the org context for the particular session
      apps.mo_global.set_policy_context ('S', c_rec.org_id);

      -- apps.mo_global.init('AR');

      SELECT application_id, responsibility_id
        INTO v_app, v_res
        FROM fnd_responsibility_tl        
       WHERE responsibility_id = 51234;

      ---- Setting the oracle applications context for the particular session
      apps.fnd_global.apps_initialize (v_user, v_res, v_app);
      ----------------------------------------------------------------------------
      xv_ret_status := NULL;
      xv_msg_count := NULL;
      xv_msg_data := NULL;

      --update the Allow Transaction Deletion to Yes to Delete (As mentioned above, better to do it from application)
      UPDATE ar_system_parameters_all
         SET invoice_deletion_flag = 'Y'
       WHERE org_id = c_rec.org_id;

      ar_invoice_api_pub.delete_transaction (
         p_api_name           => 'Delete_Transaction',
         p_api_version        => 1.0,
         p_init_msg_list      => fnd_api.g_true,
         p_commit             => fnd_api.g_true,
         p_validation_level   => fnd_api.g_valid_level_full,
         p_customer_trx_id    => c_rec.customer_trx_id,
         p_return_status      => xv_ret_status,
         p_msg_count          => xv_msg_count,
         p_msg_data           => xv_msg_data,
         p_errors             => v_message_tbl);

      UPDATE OEAG.XXERP_AR_DELETE_TRANS
         SET v_ret_status = xv_ret_status
       WHERE ROWID = c_rec.ROWID;

      UPDATE OEAG.XXERP_AR_DELETE_TRANS
         SET v_msg_count = xv_msg_count
       WHERE ROWID = c_rec.ROWID;



      IF xv_ret_status <> 'S'
      THEN
         DBMS_OUTPUT.put_line ('   Status: ' || xv_ret_status);

         UPDATE OEAG.XXERP_AR_DELETE_TRANS
            SET v_msg_data = v_ret_status
          WHERE ROWID = c_rec.ROWID;

         FOR i IN 1 .. xv_msg_count
         LOOP
            apps.fnd_msg_pub.get (i,
                                  apps.fnd_api.g_false,
                                  xv_msg_data,
                                  v_msg_index);
            DBMS_OUTPUT.put_line ('   Error : ' || xv_msg_data);
         END LOOP;

         DBMS_OUTPUT.put_line ('   ' || xv_msg_data);
      ELSE
         DBMS_OUTPUT.put_line ('   Deleted.');

         -- Revert back to the original value for the deletion flag
         UPDATE ar_system_parameters_all
            SET invoice_deletion_flag = 'N'
          WHERE org_id = c_rec.org_id;
      END IF;

      DBMS_OUTPUT.put_line ('--------------------');
      COMMIT;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error : ' || SQLERRM);
END;
/

Note: User can able to delete the transaction front end also, after completing Step 2.