AR Invoice Interface

CREATE OR REPLACE PACKAGE BODY XXTE_AR_INVOICE_INTF
AS
   PROCEDURE XXTE_ARINVOICE_MAIN (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
   IS
      lv_errbuff   VARCHAR2 (1000);
      lv_retcode   NUMBER;
   BEGIN     
      xxte_arinvoice_ins (lv_errbuff, lv_retcode);
   EXCEPTION
      WHEN OTHERS
      THEN
         lv_retcode := 1;
         lv_errbuff := 'Error: In Main Procudure.' || SQLERRM;
         fnd_file.put_line (fnd_file.LOG, lv_errbuff);
   END;
   PROCEDURE xxte_arinvoice_ins (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
   IS     
      CURSOR stg_cur
      IS
         SELECT a.*, a.ROWID
           FROM xxte_ar_invoice_st a
          WHERE process_flag = 'N';
         
           p_api_version             NUMBER;
           p_init_msg_list           VARCHAR2 (200);
           p_commit                  VARCHAR2 (200);
           p_batch_source_rec        apps.ar_invoice_api_pub.batch_source_rec_type;
           p_trx_header_tbl          apps.ar_invoice_api_pub.trx_header_tbl_type;
           p_trx_lines_tbl           apps.ar_invoice_api_pub.trx_line_tbl_type;
           p_trx_dist_tbl            apps.ar_invoice_api_pub.trx_dist_tbl_type; 
           p_trx_salescredits_tbl    apps.ar_invoice_api_pub.trx_salescredits_tbl_type;
           p_trx_contingencies_tbl   apps.ar_invoice_api_pub.trx_contingencies_tbl_type;
           x_customer_trx_id         NUMBER;
           x_return_status           VARCHAR2 (200);
           x_msg_count               NUMBER;
           x_msg_data                VARCHAR2 (200);
           h_trx_header_id           ra_customer_trx_all.customer_trx_id%TYPE;
           l_trx_ln_hdr_id           ra_customer_trx_all.customer_trx_id%TYPE;
           l_trx_line_id             ra_customer_trx_lines_all.customer_trx_line_id%TYPE;
           lv_errbuff                VARCHAR2 (1000);
           lv_retcode                NUMBER;          
           lv_user_id                NUMBER;
           lv_resp_id                NUMBER;
           lv_resp_appl_id           NUMBER;
           lv_source                 NUMBER;
           lv_gl_date                VARCHAR2(1);
           lv_trx_date               VARCHAR2(1);
           lv_class                  NUMBER;
           lv_customer_id            NUMBER;
           lv_error_message          VARCHAR2 (2400);
           lv_error_code             NUMBER := 0;
          
   BEGIN     
      lv_user_id := fnd_profile.VALUE ('USER_ID');
      lv_resp_id := fnd_profile.VALUE ('RESP_ID');
      lv_resp_appl_id := fnd_profile.VALUE ('RESP_APPL_ID');
      BEGIN
         fnd_global.apps_initialize (lv_user_id, lv_resp_id, lv_resp_appl_id);
         mo_global.init ('AR');
      END;
     
      -- Purge the previous processed data in the stage table
      BEGIN
         DELETE FROM xxte_ar_invoice_st
               WHERE process_flag != 'N';
      EXCEPTION
         WHEN OTHERS
         THEN
            lv_error_code := 1;
            lv_error_message :=
                  'Error: Unable To Delete Record(s) In Staging Table.'
               || SQLERRM;
            fnd_file.put_line (fnd_file.LOG, lv_error_message);
      END;
     
   fnd_file.put_line
         (2,
          '****************************AR Invoice Process***************************'
         );
      fnd_file.put_line
         (2,
          '------------------------------------------------------------------------------- '
         );
  
    FOR i IN stg_cur
    LOOP
        lv_error_message := NULL;
        lv_error_code := 0;
        lv_source := 1002;--'TE INSIS';
        --lv_class := 1080;
   
   
        -- Validation for GL date
         BEGIN
            SELECT 'X'
              INTO lv_gl_date
              FROM gl_period_statuses
             WHERE application_id = 222                ---For Oracle Receivable
               AND set_of_books_id = 2022
               AND (i.gl_date >= start_date AND i.gl_date <= end_date)
               AND closing_status = 'O';     
         
         
            IF lv_gl_date IS NULL
            THEN
               RAISE NO_DATA_FOUND;
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'GL Date Is NULL or Period is Not Open for the GL Date ';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Period is Not Open for the GL Date or GL Date IS Null'
                  || ' '
                  || i.gl_date;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;
       
    -- Validation for Trx date
    BEGIN
            SELECT 'X'
              INTO lv_trx_date
              FROM gl_period_statuses
             WHERE application_id = 222                ---For Oracle Receivable
               AND set_of_books_id = 2022
               AND (i.trx_date >= start_date AND i.trx_date <= end_date)
               AND closing_status = 'O';     
         
         
            IF lv_trx_date IS NULL
            THEN
               RAISE NO_DATA_FOUND;
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'Transaction Date Is NULL or Period is Not Open for the Transaction Date ';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Period is Not Open for the Transaction Date or Transaction Date IS Null'
                  || ' '
                  || i.trx_date;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;    
         -- Validation for customer name
         BEGIN
            SELECT customer_id
              INTO lv_customer_id
              FROM ar_customers
             WHERE customer_name = i.bill_to_name;

            IF lv_customer_id IS NULL
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Customer Name '
                  || ' '
                  || i.bill_to_name
                  || ' '
                  || 'is Not Found';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Customer Name '
                  || ' '
                  || i.bill_to_name
                  || ' '
                  || 'is Not Found';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

         --- Validation for description        
         BEGIN
            IF i.description IS NULL THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Description is null'                  ;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;  
         END;
        
         --- Validation for unit price        
         BEGIN
            IF i.unit_price IS NULL THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Unit Price is null'                  ;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;  
         END;
        
         --- Validation for invoicing rule
        BEGIN
            IF i.class = '1020' THEN
                IF i.rule_end_date IS NULL and i.rule_start_date IS NULL THEN
                       lv_error_code := 1;
                       lv_error_message :=
                             'Error: '
                          || 'Rule Start Date/Rule End Date Is Null'                  ;
                       fnd_file.put_line (fnd_file.LOG, lv_error_message);
                ELSIF i.rule_start_date > i.rule_end_date THEN
                       lv_error_code := 1;
                       lv_error_message :=
                             'Error: '
                          || 'Rule End Date Is Greater Then Rule Start Date'                  ;
                       fnd_file.put_line (fnd_file.LOG, lv_error_message);
                ELSIF i.rule_end_date <> i.gl_date and i.rule_end_date <> i.trx_date THEN
                       lv_error_code := 1;
                       lv_error_message :=
                             'Error: '
                          || 'GL Date, Transaction Date and Rule End Date Must Be Same'                  ;
                       fnd_file.put_line (fnd_file.LOG, lv_error_message);      
                END IF;
            END IF;
        END;
        
        
         IF lv_error_code = 0 THEN
        
         /* API initialize */
           p_api_version := 1.0;
           p_init_msg_list := fnd_api.g_false;
           p_commit := fnd_api.g_true;          
           p_batch_source_rec.batch_source_id := lv_source;
          
           /*Header Part*/
           SELECT ra_customer_trx_s.NEXTVAL
             INTO h_trx_header_id
             FROM DUAL;

           p_trx_header_tbl (1).trx_header_id := h_trx_header_id;
           p_trx_header_tbl (1).trx_date := i.trx_date;
            --  p_trx_header_tbl (1).trx_currency := 'AED';
            --  p_trx_header_tbl (1).trx_class := 'INV';
           p_trx_header_tbl (1).cust_trx_type_id := i.class;
           p_trx_header_tbl (1).gl_date := i.gl_date;
           p_trx_header_tbl (1).bill_to_customer_id := lv_customer_id;
          
           IF i.class = '1020' THEN
            p_trx_header_tbl (1).invoicing_rule_id := -2;
           END IF;     
           --p_trx_header_tbl (1).term_id := 5;
          
           /*Line Part*/
           SELECT ra_customer_trx_s.CURRVAL
             INTO l_trx_ln_hdr_id
             FROM DUAL;

            p_trx_lines_tbl (1).trx_header_id := l_trx_ln_hdr_id;

           SELECT ra_customer_trx_lines_s.NEXTVAL
             INTO l_trx_line_id
             FROM DUAL;

           p_trx_lines_tbl (1).trx_line_id := l_trx_line_id;
           p_trx_lines_tbl (1).line_number := 1;          
           p_trx_lines_tbl (1).description := i.description;                             
           p_trx_lines_tbl (1).quantity_invoiced := 1;
           p_trx_lines_tbl (1).unit_selling_price := i.unit_price;
           p_trx_lines_tbl (1).line_type := 'LINE';
          
           IF i.class = '1020' THEN
            p_trx_lines_tbl (1).accounting_rule_id := 2000;
            p_trx_lines_tbl (1).rule_start_date := i.rule_start_date;
            p_trx_lines_tbl (1).rule_end_date := i.rule_end_date;
           END IF;          
                   
           x_customer_trx_id := NULL;
           x_return_status := NULL;
           x_msg_count := NULL;
           x_msg_data := NULL;
           
          
          
           BEGIN                             
               apps.ar_invoice_api_pub.create_single_invoice (p_api_version,
                                                              p_init_msg_list,
                                                              p_commit,
                                                              p_batch_source_rec,
                                                              p_trx_header_tbl,
                                                              p_trx_lines_tbl,
                                                              p_trx_dist_tbl,
                                                              p_trx_salescredits_tbl,
                                                              p_trx_contingencies_tbl,
                                                              x_customer_trx_id,
                                                              x_return_status,
                                                              x_msg_count,
                                                              x_msg_data
                                                             );
               
            fnd_file.put_line (2, 'Receivable Invoice Is Created. Customer Trx ID:'|| x_customer_trx_id);
            UPDATE xxte_ar_invoice_st
               SET process_flag = 'S'
             WHERE ROWID = i.ROWID;                                                                                                                                                                                            
           EXCEPTION                     
           WHEN OTHERS
           THEN
                 lv_error_code := 1;
                 lv_error_message :=
                          'Error: Unable To Insert Data In Base Table.' || SQLERRM;
                 fnd_file.put_line (fnd_file.LOG, lv_error_message);                                     
           END;
           ELSE
                fnd_file.put_line (2, 'Please Check The Log File For Error(s)');
                UPDATE xxte_ar_invoice_st
                  SET process_flag = 'E',
                      error_message = lv_error_message
                WHERE ROWID = i.ROWID;
           END IF;
    END LOOP;   
           COMMIT;
           fnd_file.put_line
               (2,
                '**************************** END ***************************'
               );
           fnd_file.put_line
                (2,
                    '------------------------------------------------------------------------------- '
                );          
EXCEPTION
WHEN OTHERS
  THEN
     lv_error_code := 1;
     lv_error_message :=
              'Error: Unable To Insert Data In Base Table.' || SQLERRM;
     fnd_file.put_line (fnd_file.LOG, lv_error_message);      
       
END;  
END xxte_ar_invoice_intf;