Create Credit Memo AR Invoice in Oracle Apps R12 / Creating Credit Memo Invoices in AR Oracle Apps R12

Creating Credit Memo AR Invoices in Oracle Apps R12:


SET SERVEROUTPUT ON;
DECLARE
  
   /* Local Variables declaration */
   l_customer_trx_id               ra_customer_trx.customer_trx_id%TYPE;
   l_line_credits_flag             ra_cm_requests.line_credits_flag%TYPE;
   l_line_amount                   NUMBER;
   l_freight_amount                NUMBER := 0;
   l_cm_lines_tbl                  arw_cmreq_cover.cm_line_tbl_type_cover;
   l_cm_reason_code                VARCHAR2(150);
   l_comments                      VARCHAR2(150);
   l_msg_count                     NUMBER := 0;
   l_msg_data                      VARCHAR2(20000) := NULL;  
   l_return_status                 VARCHAR2(1);
   l_request_id                    NUMBER;
   l_batch_source_name             VARCHAR2(150) DEFAULT NULL;
   ld_ct_trx_id                    NUMBER;
   lc_trx_id                       ra_customer_trx_all.customer_trx_id%TYPE;
  
   CURSOR c_trx_ct_headers
   IS (
      SELECT *
      FROM xxxx.xxxx_trx_headers_interface xxhdr
      WHERE 1          = 1
      AND   xxhdr.record_status IN ('N','D','T','S')
      AND   xxhdr.error_msg is null );
   CURSOR c_trx_ct_lines (P_record_id NUMBER)
   IS (
      SELECT *
      FROM xxxx.xxxx_trx_lines_interface
      WHERE 1          = 1
      AND   record_status IN ('N','D','T','S')
      AND   error_msg is null
      AND   record_id = P_record_id );
   BEGIN
      /* Profiles Initialization */
   BEGIN
      APPS.FND_GLOBAL.APPS_INITIALIZE (APPS.FND_GLOBAL.USER_ID, FND_GLOBAL.RESP_ID, FND_GLOBAL.RESP_APPL_ID);
      APPS.MO_GLOBAL.SET_POLICY_CONTEXT ('S', FND_GLOBAL.ORG_ID);
      APPS.MO_GLOBAL.INIT('AR');
   EXCEPTION
      WHEN OTHERS THEN
         dbms_output.put_line('Undefined Error In - Profiles Initialization '||'==> '||SQLERRM);
   END;
     
   DELETE  FROM ar_trx_errors_gt;
  
   FOR rec_hdr IN c_trx_ct_headers
   LOOP /* Header Loop Starts Here */
      BEGIN
      SELECT customer_trx_id
   INTO   lc_trx_id
   FROM   ra_customer_trx_all
      WHERE  trx_number = rec_hdr.trx_number;
   EXCEPTION
            WHEN NO_DATA_FOUND THEN
               dbms_output.put_line('No Data Found for - TAX ');
            WHEN OTHERS THEN
               dbms_output.put_line('Undefined Error - in finding TAX '||' '||SQLERRM);
   END;
      FOR rec_line IN c_trx_ct_lines (rec_hdr.record_id) 
   LOOP /* Lines Loop Starts Here */
  
  
            BEGIN
               ar_credit_memo_api_pub.create_request (
                                                   p_api_version                => 1.0,
                                                   p_init_msg_list              => fnd_api.g_true,
                                                   p_commit                     => fnd_api.g_false,
                                                   x_return_status              => l_return_status,
                                                   x_msg_count                  => l_msg_count,
                                                   x_msg_data                   => l_msg_data,
                                                   x_request_id                 => l_request_id,
                                                   p_comments                   => rec_line.comments||':'||rec_hdr.trx_number ,
                                                   p_customer_trx_id            => lc_trx_id,
                                                   p_line_credit_flag           => 'N',
                                                   p_line_amount                => ((rec_line.quantity) * (rec_line.unit_price)),
                                                   p_tax_amount                 => rec_line.tax_amount,
                                                   p_cm_reason_code             => 'RETURN',
                                                   p_skip_workflow_flag         => 'Y',
                                                   p_batch_source_name          => 'MANUAL-OTHER',
                                                   p_credit_method_installments => 'LIFO',
                                                   p_credit_method_rules        => 'LIFO',
                                                   p_org_id                     => FND_PROFILE.VALUE('ORG_ID')
                                                   );
               COMMIT;
            FND_MSG_PUB.count_and_get (
                                             p_encoded => fnd_api.g_false,
                                             p_count   => l_msg_count,
                                             p_data    => l_msg_data
                  );
                     dbms_output.put_line('Return Status ==> '||l_return_status);
                     dbms_output.put_line('Credit Memo request_id ==> '||l_request_id);
                     dbms_output.put_line('l_msg_count ==> '||l_msg_count);
     
         FOR I IN 1..L_MSG_COUNT
   LOOP
            dbms_output.put_line(SUBSTR(FND_MSG_PUB.GET(p_msg_index => I, p_encoded => 'F'), 1, 254));
         END LOOP;
         IF l_return_status <> 'S' THEN
  
   UPDATE XXXX_TRX_HEADERS_INTERFACE
   SET record_status = 'E', error_msg = 'Records Not Inserted into Base Tables'||' Validation Errors '||l_msg_data
   WHERE record_id = rec_hdr.record_id;
   
   UPDATE XXXX_TRX_LINES_INTERFACE
   SET record_status = 'E', error_msg = 'Records Not Inserted into Base Tables'||' Validation Errors '||l_msg_data
   WHERE record_id = rec_hdr.record_id;
         ELSE
   BEGIN
               SELECT cm_customer_trx_id           
               INTO   ld_ct_trx_id
               FROM   ra_cm_requests_all
               WHERE  request_id = l_request_id;
     
      UPDATE XXXX_TRX_HEADERS_INTERFACE
      SET record_status = 'S', error_msg = 'Records Inserted into Base Tables '||' Created Customer TRX ID ==> '||ld_ct_trx_id
      WHERE record_id = rec_hdr.record_id;
      
      UPDATE XXXX_TRX_LINES_INTERFACE
      SET record_status = 'S', error_msg = 'Records Inserted into Base Tables '||' = Created Customer TRX ID ==> '||ld_ct_trx_id
      WHERE record_id = rec_hdr.record_id;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         dbms_output.put_line('No Credit Memo Derived');
      WHEN OTHERS THEN
         dbms_output.put_line('Undefined Error - Credit Memo Derivation '||''||SQLERRM);
   END;
         END IF;
      EXCEPTION         
     WHEN NO_DATA_FOUND THEN   
        dbms_output.put_line(' No In Credit Memo Derivation');   
     WHEN OTHERS THEN   
        dbms_output.put_line('Undefined Error - Credit Memo Derivation '||''||SQLERRM);   
      END;
      END LOOP; /* Lines Loop Ends Here */
   END LOOP; /* Header Loop Ends Here */
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         dbms_output.put_line('No Data Found for - in Main Loop ');
      WHEN OTHERS THEN
         dbms_output.put_line('Undefined Error - in Main Loop '||''||SQLERRM); 
   END ;