AR 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;



*/