API code to Create Credit Memo and apply it to invoice

I received a request to post code for creating a credit Memo and apply it to invoice to nullify the balance. Below is the sample code using ar_credit_memo_api_pub.create_request API


SET SERVEROUTPUT ON;

/* Formatted on 7/30/2014 7:04:39 PM (QP5 v5.115.810.9015) */
DECLARE
   -- This script was tested in 11i instance --
   v_return_status          VARCHAR2 (1);
   p_count                  NUMBER;
   v_msg_count              NUMBER;
   v_msg_data               VARCHAR2 (2000);
   v_request_id             NUMBER;
   v_context                VARCHAR2 (2);
   l_cm_lines_tbl           arw_cmreq_cover.cm_line_tbl_type_cover;
   l_customer_trx_id        NUMBER;
   cm_trx_id                NUMBER;
   v_interface_header_rec   arw_cmreq_cover.pq_interface_rec_type;
   ind                      NUMBER;
   l_trx_number             VARCHAR2 (30);

   CURSOR c_inv (p_trx_number varchar2
   )
   IS
      SELECT rct.trx_number,
             rct.customer_trx_id,
             rctl.customer_trx_line_id,
             rctl.quantity_invoiced,
             unit_selling_price
      FROM ra_customer_trx_all rct, ra_customer_trx_lines_all rctl
      WHERE     rct.customer_trx_id = rctl.customer_trx_id
            AND trx_number = p_trx_number
            AND line_type = 'LINE';

   PROCEDURE set_context
   IS
   BEGIN
      DBMS_APPLICATION_INFO.set_client_info (0);
      mo_global.set_policy_context ('S', 0);
   END set_context;
BEGIN
   -- Setting the context ----
   set_context;

   DBMS_OUTPUT.put_line ('Invoking Credit Memo Creation process');

   l_trx_number                 := '20116773';

   FOR lc_inv IN c_inv (l_trx_number)
   LOOP
      ind                 := 1;
      l_customer_trx_id   := lc_inv.customer_trx_id;

      l_cm_lines_tbl (ind).customer_trx_line_id   :=
         lc_inv.customer_trx_line_id;
      l_cm_lines_tbl (ind).quantity_credited   :=
         lc_inv.quantity_invoiced * -1;
      l_cm_lines_tbl (ind).price   :=
         lc_inv.unit_selling_price;
      l_cm_lines_tbl (ind).extended_amount   :=
         lc_inv.quantity_invoiced * lc_inv.unit_selling_price * -1;
   END LOOP;

   ar_credit_memo_api_pub.create_request (          -- standard api parameters
                                          p_api_version => 1.0,
                                          p_init_msg_list => fnd_api.g_true,
                                          p_commit => fnd_api.g_false-- credit memo request parameters
                                          ,
                                          p_customer_trx_id => l_customer_trx_id,
                                          p_line_credit_flag => 'Y',
                                          p_cm_line_tbl => l_cm_lines_tbl,
                                          p_cm_reason_code => 'RETURN',
                                          p_skip_workflow_flag => 'Y',
                                          p_batch_source_name => 'XX_ORDER_ENTRY',
                                          p_interface_attribute_rec => v_interface_header_rec,
                                          p_credit_method_installments => NULL,
                                          p_credit_method_rules => NULL,
                                          x_return_status => v_return_status,
                                          x_msg_count => v_msg_count,
                                          x_msg_data => v_msg_data,
                                          x_request_id => v_request_id
   );
   DBMS_OUTPUT.put_line ('Message count ' || v_msg_count);

   IF v_msg_count = 1
   THEN
      DBMS_OUTPUT.put_line ('l_msg_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' || p_count || ' ---' || v_msg_data);
      END LOOP;
   END IF;

   IF v_return_status <> 'S'
   THEN
      DBMS_OUTPUT.put_line ('Failed');
   ELSE
      SELECT cm_customer_trx_id
      INTO cm_trx_id
      FROM ra_cm_requests_all
      WHERE request_id = v_request_id;

      DBMS_OUTPUT.put_line (' CM trx_id = ' || cm_trx_id);
   -- You can issue a COMMIT; at this point if you want to save the created credit memo to the database
   -- COMMIT;
   END IF;
END;