Thursday, 16 July 2015

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

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete