Wednesday 30 July 2014

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;

6 comments:

  1. Your articles are inventive. I am looking forward to reading the plethora of articles that you have linked here. Thumbs up! Microsoft Dynamics 365 Sales Credit Card Processing

    ReplyDelete
  2. Thanks for the blog filled with so many information. Stopping by your blog helped me to get what I was looking for. Now my task has become as easy as ABC. 상품권 현금화

    ReplyDelete
  3. Thank you because you have been willing to share information with us. we will always appreciate all you have done here because I know you are very concerned with our. Topcc Shop

    ReplyDelete
  4. I’m going to read this. I’ll be sure to come back. thanks for sharing. and also This article gives the light in which we can observe the reality. this is very nice one and gives indepth information. thanks for this nice article... Microsoft Dynamics 365 Business Central Credit Card Processing

    ReplyDelete
  5. Forex Search Engine Optimization Company You Can Use Link Building Services To Increase The Visibility Of Your Website. The Service Is An Affordable And Long-term Solution To Help Your Site Rank Higher In Search Engine Results. Link Building Services Use A Variety Of Strategies To Help Your Site Rank Higher. One Such Strategy Is To Create Backlinks To Your Site. The Link Building Service Will Create And Place Links On Websites That Are Relevant To Your Business And Industry. This Helps Your Site Rank Higher In Search Engine Results And Increases The Likelihood Of Potential Customers Finding Your Site.

    ReplyDelete
  6. The most comprehensive guide to Forex Brokers and Forex Trading Companies 1k , showing you what you can save or earn by choosing the best broker for your needs – from Highest to Lowest Charges. Useful for both beginner and seasoned trader.

    ReplyDelete