Oracle API - How to Create Credit Memo & Apply it against Standard AR Invoice

CREATE OR REPLACE PROCEDURE xx_create_cm_inv (p_cust_trx_id      IN NUMBER)

                                

AS                                 

   -- 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:=0;

   l_trx_number             VARCHAR2(30);

   CURSOR c_inv(p_cust_trx_id NUMBER)

   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     rct.customer_trx_id=p_cust_trx_id

      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;

   

--   fnd_global.apps_initialize (user_id           => 0,

--                               resp_id           => v_resp_id,

--                               resp_appl_id      => 222

--                              );

--                              

   fnd_global.apps_initialize (0,51967, 222);                           

   mo_global.init ('AR');

   

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

--   l_trx_number := '19445';

  

   FOR lc_inv IN c_inv(p_cust_trx_id)

   LOOP

      ind := 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;

      

      DBMS_OUTPUT.put_line('process'||ind);

      

   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

--                                       , p_validation_level    => FND_API.G_VALID_LEVEL_FULL

                                       -- 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            =>  'MANUAL-OTHER'

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

How to replace multiple special character from oracle BI etext template

Requirement: Bank disbursement etext report output file having multiple special characters like (i.e special character ‘&’, ’*’, ‘(‘, ‘)’ ) which is not accepted by banking team.

Example :

Report output having following text,

ABC & XYZ cooperation (LTD)  - Bank will not accept this format

Actual output required. Which is accepted at the banking system interface.

ABC  XYZ cooperation LTD  - Bank accepted format

Issue Facing during using SQL REPLACE function:

We can use REPLACE function to replace special character from a specific field, but on specific the field we can use max 1 or 2 nested REPLACE function at etext template level which is a limitation.

But when we have multiple special characters to replace REPLACE function will not work properly, We required to find other solution

i.e  REPLACE(REPLACE(FiledXMLtag))

Solution:

We can define the set of replacing a character in etext template definition part. You can see highlighted screenshot.

In between 

start tag <REPLACE CHARACTERS> and 

end tag <END REPLACE CHARACTERS> 

we have to use all special character which we have to replace, in the 1st column we have to add special character which we have to replace and in 2^nd the column we have to add what we have to use in replacement of special character,

I have given space only as I want to remove the special character from my report output.

You can update your template and check your output your issue will be resolved.

Oracle Fusion Etext Report template output file numeric filed decimal value format issue

Problem Summary:

I have generated Etext file template where decimal numeric field value in Amount not showing properly. I have tried with format masking also but still facing the same issue.

We have to provide this output text payment detail file to Bank system so they can make payment against the customer.

Bank the requirement is to use decimal separator ‘,’ instead of ‘.’ (Bank accepted format – 10,50 QAR)

i.e If payment the amount is 10.50 than output showing 10.5 only. Which is not correct as per the bank requirement, bank system wants 10.50 as an output.

i.e If payment the amount is 1.88 than output showing 1.88 properly.

Report Path: Shared Folders/Custom/Financial/Payment

Report Name: Disbursement payment file formats. (Seeded Report we made custom template)

Etext RTF template Amount field datatype where we facing an issue:

I have tried with the format, masking option suggested by oracle but we not able to use this solution because oracle uses Decimal separator as ‘.’ When as per bank the requirement we have to use decimal separator as ‘,’

Oracle SR suggested solution: (Which is not working in our case, as we have required decimal separator ‘,’)

Solution:

Step1: Add Format setup command in RTF template.

Step 2: Add extra ‘0’ in decimal part based on conditional format. You can add condition as per your business needs.

Syntax: 

IF Length(DECIMAL_PART (PaymentAmount/Value)

) = 1 THEN

‘0’

END IF

Now issue will be resolve.

Report output: