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;

BI/XML Publisher: Leading and Trailing zeroes truncated for excel reports

Microsoft Excel is too smart and it identifies whether the value in the cell is a Text or number and applies formatting accordingly. This sometimes becomes an issue for us when we are trying to generate an excel report. For example item number 0003463262360 has all the numbers and starts with zero, this when printed in excel report displays it as 3463262360. Hence all the leading zeroes are truncated. Same issue happens when we have decimal and trailing zeroes.

FO formatting options can be used to get away with this problem. Below is the syntax for same.

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override">
<?ITEM_NUMBER?>
</fo:bidi-override>