Search This Blog

Sunday, March 11, 2012

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;

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;

3 Comments:

Sree said...

Hi Suresh,

Please let me know the API's to create UOM and UOM Conversion in oracle inventory.

Thanks in advance,
Regards.

Unknown said...

Hi Suresh,

Please let me know the

1. API to create UOM.
2. API to create manufacturers Part Number

Thanks in advance.
Sreehari.

Anonymous said...

running sales order reservation script, getting the following error in 11:5:10:2

INV
INV-RSV-INPUT-MISSING
FIELD_NAME
ship_ready_flag

any idea why?

Thank you,

Ted Cain
262-893-4881

Copyright (c) All rights reserved. Presented by Suresh Vaishya