API to Close Credit Memo and Generate Refund through AP invoice in Oracle Receivables

DECLARE
  lc_inv                   NUMBER;
  lb_complete              BOOLEAN;
  lc_phase                 VARCHAR2(100);
  lc_status                VARCHAR2(100);
  lc_dev_phase             VARCHAR2(100);
  lc_dev_status            VARCHAR2(100);
  lc_message               VARCHAR2(100);
  v_cm_payment_schedule    number; -- Payment Schedule ID of Credit Memo from APPS.AR_PAYMENT_SCHEDULES_ALL
  v_inv_payment_schedule   number; -- Payment Schedule ID of Invoice from APPS.AR_PAYMENT_SCHEDULES_ALL
  v_amount_applied         number; -- Amount of credit memo to apply to invoice
  v_apply_date             date := TRUNC(SYSDATE);
  v_gl_date                date := TRUNC(SYSDATE);
  v_ussgl_transaction_code varchar2(1024); -- null, but check AR_RECEIVABLE_APPLICATIONS_ALL
  v_null_flex              varchar2(1024); -- null, unless you have flexfield segments to define
  v_customer_trx_line_id   number; -- null, but check AR_RECEIVABLE_APPLICATIONS_ALL
  v_comments               varchar2(240);
  v_module_name            varchar2(128); -- := 'XXCREDIT_APPLICATION.APPLY_CREDIT_MEMO'; -- If null, validation won't occur
  v_module_version         varchar2(128) := '1'; -- If null, validation won't occur
  lc_msg_txt               varchar2(1000);
  --
  -- Output
  --
  v_out_rec_application_id    number;
  v_acctd_amount_applied_from number;
  v_acctd_amount_applied_to   number;
  v_receivable_trx_id         NUMBER;
  ln_org_id                   NUMBER := 101;
  lc_trx                      number;

  l_return_status VARCHAR2(10);
  l_msg_count     NUMBER;
  l_msg_data      VARCHAR2(1000);
  p_count         NUMBER;

  l_application_ref_id   NUMBER;
  l_application_ref_num  VARCHAR2(30);
  l_application_ref_type VARCHAR2(30);

  p_credit_memo_trx_id     NUMBER;
  p_receipt_application_id NUMBER;

BEGIN

  v_amount_applied     := 2850; /*Amount pending for Credit Memo*/
  p_credit_memo_trx_id := 67727;
  v_receivable_trx_id  := 1158; /*AR_RECEIVABLES_TRX_ALL.RECEIVABLES_TRX_ID*/

  -- if you are on R12, remove the -- in the next 2 lines:
  Mo_global.init('AR');
  Mo_global.set_policy_context('S', 101);

  -- Set User/Resp        

  Fnd_Global.apps_initialize(15548, 20678, 222);

  dbms_output.put_line('Calling Credit Memo Acitvity Application API.. ');

  Ar_cm_application_pub.activity_application(p_api_version                 => 1.0,
                                             p_init_msg_list               => FND_API.G_TRUE,
                                             p_customer_trx_id             => p_credit_memo_trx_id,
                                             p_amount_applied              => v_amount_applied,
                                             p_applied_payment_schedule_id => -8,
                                             p_receivables_trx_id          => v_receivable_trx_id,
                                             p_apply_date                  => v_apply_date,
                                             p_apply_gl_date               => v_gl_date,
                                             x_return_status               => l_return_status,
                                             x_msg_count                   => l_msg_count,
                                             x_msg_data                    => l_msg_data,
                                             p_application_ref_type        => l_application_ref_type,
                                             p_application_ref_id          => l_application_ref_id,
                                             p_application_ref_num         => l_application_ref_num,
                                             p_receivable_application_id   => p_receipt_application_id);

  IF (l_return_status <> 'S') THEN

    dbms_output.put_line(' Error in Creating Employee Type Supplier..');

    IF (l_msg_count = 1) THEN
      dbms_output.put_line('x_msg_data ' || l_msg_data);
 
    ELSIF (l_msg_count > 1) THEN
      LOOP
        p_count    := p_count + 1;
        l_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
   
        IF (l_msg_data IS NULL) THEN
          EXIT;
        END IF;
   
        dbms_output.put_line(' Error Message' || p_count || ' ---' ||
                             l_msg_data);
      END LOOP;
    END IF;

  ELSE

    dbms_output.put_line('Successfully Applied with Receivable Application Id..' ||
                         p_receipt_application_id);

  END IF;

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error while Appyling Credit Memo to Invoice - ' ||
                         SQLERRM);
END;