Delete AR Transaction (Receivables) Using API

Step 1 : Create a table which is having all the transaction which has to be deleted.

CREATE TABLE xx_delete_transaction
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 = 109 AND trx_date = '03-AUG-2013'      


Step 2 : Now run this procedure

/* Formatted on 3/5/2014 11:33:42 PM (QP5 v5.163.1008.3004) */
DECLARE
   CURSOR cur_all_trx
   IS
      SELECT ROWID,
             NULL party_number,
             rct.org_id,
             rct.customer_trx_id,
             rct.trx_number
        FROM xx_delete_transaction rct
       WHERE NVL (V_RET_STATUS, 0) <> 'S';

   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 := 1712;
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 = 50705;

      ---- 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 xx_delete_transaction
         SET v_ret_status = xv_ret_status
       WHERE ROWID = c_rec.ROWID;

      UPDATE xx_delete_transaction
         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 xx_delete_transaction
            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;