Oracle Apps: Script to unapply Credit memo

This is some sample code which we have used in Release 12.1.3 instance to unapply Credit Memo  in Bulk.

This code expects you already have the Customer Account Number, Credit Memo and Debit Memo/Invoice details. You just need to replace the SELECT from DUAL section in the cursor and replace with your values.

DECLARE
   CURSOR c_get_trx IS 
   SELECT t.account_number          account_number,
          TO_NUMBER(t.amt)          amount,
          t.field5                  cm_trx_number,
          t.cm_date                 cm_trx_date,
          psc.customer_trx_id       cm_trx_id,
          psc.payment_schedule_id   cm_ps_id,
          psc.amount_due_remaining  cm_amt_remaining,
          psc.status                cm_status, 
          adj.customer_trx_id       inv_trx_id,
          adj.trx_rec_number        inv_trx_number
   FROM   (SELECT '2412315'                             "ACCOUNT_NUMBER"
                   ,'100.50'                             "AMT"
                   ,'95000'                              "DM_NUMBER"
                   ,TO_DATE('01-Jan-2018','DD-Mon-YYYY') "DM_DATE"
                   ,'130000'                             "CM_NUMBER"
                   ,TO_DATE('05-Jan-2018','DD-Mon-YYYY') "CM_DATE"
            FROM   dual) t ,
          hz_cust_accounts     ca,
         ar_payment_schedules psc,
         ar_app_adj_v         adj
   WHERE ca.account_number  = t.account_number
   AND   psc.customer_id    = ca.cust_account_id
   AND   psc.class          = 'CM'
   AND   psc.trx_number     = t.field5
   AND   TRUNC(psc.trx_date)= t.cm_date
   AND   adj.payment_schedule_id = psc.payment_schedule_id
   AND   psc.status = 'CL';
 
  v_return_status           VARCHAR2(1);
  p_count                   NUMBER;
  v_msg_count               NUMBER;
  v_msg_data                VARCHAR2(2000);
 
  v_cm_unapp_rec            AR_CM_API_PUB.CM_UNAPP_REC_TYPE;
  v_cm_id                   NUMBER(15) DEFAULT 1605;
  v_inv_id                  NUMBER(15) DEFAULT 1604;
  v_gl_date                 DATE       DEFAULT SYSDATE;
 
  v_context                 VARCHAR2(2);
 
BEGIN
   -- Apps Initialize
   MO_GLOBAL.SET_POLICY_CONTEXT('S', 0);
   fnd_global.apps_initialize (1000,50000,222);
 
   FOR r_rec IN c_get_trx LOOP
      DBMS_OUTPUT.put_line('Processing account number ' || r_rec.account_number || ': Trying to un apply credit memo number: ' || r_rec.cm_trx_number ||' from debit memo number: ' || r_rec.dm_trx_number);
  
      BEGIN
 
     v_cm_unapp_rec.cm_customer_trx_id       := r_rec.cm_trx_id;
         v_cm_unapp_rec.inv_customer_trx_id  := r_rec.inv_trx_id;
         v_cm_unapp_rec.reversal_gl_date     := TRUNC(SYSDATE);
 
        /* Invoking credit memo unapplication api */
        ar_cm_api_pub.unapply_on_account(
                  p_api_version                => 1.0
                 ,p_init_msg_list              => FND_API.G_FALSE
                 ,p_commit                     => FND_API.G_FALSE
                 ,p_cm_unapp_rec               => v_cm_unapp_rec
                 ,x_return_status              => v_return_status
                 ,x_msg_count                  => v_msg_count
                 ,x_msg_data                   => v_msg_data);

        DBMS_OUTPUT.put_line('Message count:' || v_msg_count);
 
        IF v_msg_count = 1 THEN
          DBMS_OUTPUT.put_line('Message Data: '||v_msg_data);
        ELSIF v_msg_count > 1 THEN
          LOOP
            p_count := p_count+1;
            v_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
            IF v_msg_data IS NULL THEN
              EXIT;
            END IF;
            DBMS_OUTPUT.put_line('Message Data : ' || p_count ||' ---'||v_msg_data);
          END LOOP;
        END IF; 
 
      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.put_line('*   ERROR: Unable to apply credit memo ' || r_rec.cm_trx_number ||': ' || app_exception.get_type||':'||app_exception.get_code||':'||app_exception.get_text);
      END;
   END LOOP;
   COMMIT;
END;
 

Feel free to point out if anything is wrong/missing in this post.