Gl Interface Package

CREATE OR REPLACE PACKAGE BODY APPS.INSIS_GL_INTERFACE
                      IS
   gn_user_id              NUMBER := Fnd_Profile.VALUE ('USER_ID');
   gn_interface_batch_id   NUMBER;
   gn_request_id           NUMBER := fnd_global.conc_request_id;
   gn_group_id             NUMBER;
   gn_records_inserted     NUMBER := 0;
   gn_records_validated    NUMBER := 0;
   gn_total_records        NUMBER := 0;
   gn_error_records        NUMBER := 0;
   gv_exception            VARCHAR2 (240);
   gn_org_id               NUMBER := 82;

 /* Moving  Data From Warehouse Table To GL Staging Table  */ 
   PROCEDURE load_into_gl_stage_table
   IS
      CURSOR c1
      IS
         SELECT   GL.ROWID, GL.*
           FROM   GL_TRANSACTIONS_TEMP GL;
   --where GL.transfer_status = 'N'

   BEGIN
      FOR rec IN c1
      LOOP
         INSERT INTO GL_STAGE_TABLE (
                                        STATUS,
                                        LEDGER_ID,
                                        ACCOUNTING_DATE,
                                        CURRENCY_CODE,
                                        CURRENCY_CONVERSION_RATE ,
                                        USER_CURRENCY_CONVERSION_TYPE ,
                                        DATE_CREATED,
                                        CREATED_BY,
                                        ACTUAL_FLAG,
                                        USER_JE_CATEGORY_NAME,
                                        USER_JE_SOURCE_NAME,
                                        SEGMENT1,
                                        SEGMENT2,
                                        SEGMENT3,
                                        SEGMENT4,
                                        SEGMENT5,
                                        SEGMENT6,
                                        SEGMENT7,
                                        SEGMENT8,
                                        ENTERED_DR,
                                        ENTERED_CR,
                                        REFERENCE4,
                                        REFERENCE5,
                                        PERIOD_NAME,
                                        PROCESS_FLAG,
                                        ERROR_DESC,
                                        REQUEST_ID,
                                        PROCESS_DATE,
                                        VALIDATION_STATUS
                    )
           VALUES   (
                        rec.STATUS,
                        rec.LEDGER_ID,
                        rec.ACCOUNTING_DATE,
                        rec.CURRENCY_CODE,
                        REC.CURRENCY_CONVERSION_RATE ,
                        REC.USER_CURRENCY_CONVERSION_TYPE ,
                        sysdate,
                        gn_user_id,
                        'A',
                        rec.USER_JE_CATEGORY_NAME,
                        rec.USER_JE_SOURCE_NAME,
                        NVL (rec.SEGMENT1, '00'),
                        NVL (rec.SEGMENT2, '000'),
                        NVL (rec.SEGMENT3, '000'),
                        NVL (rec.SEGMENT4, '000000'),
                        NVL (rec.SEGMENT5, '000'),
                        NVL (rec.SEGMENT6, '00'),
                        NVL (rec.SEGMENT7, '000'),
                        NVL (rec.SEGMENT8, '000'),
                        (CASE
                            WHEN rec.ENTERED_DR < 0 THEN NULL
                            ELSE ABS (rec.ENTERED_DR)
                         END),
                        (CASE
                            WHEN rec.ENTERED_DR > 0 THEN NULL
                            ELSE ABS (rec.ENTERED_DR)
                         END),
                        rec.REFERENCE4,
                        rec.REFERENCE5,
                        rec.PERIOD_NAME,
                        'I',
                        rec.ERROR_DESC,
                        gn_request_id,
                        SYSDATE,
                        'N'
                    );
      END LOOP;

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END load_into_gl_stage_table;

 /* Validating GL Staging Table  */ 
     PROCEDURE validate_gl_records
   IS
      lc_validation_status         VARCHAR2 (10) := NULL;
      lv_error_message             VARCHAR2 (2000);


      CURSOR lcu
      IS
         SELECT   DISTINCT REFERENCE4
           FROM   GL_STAGE_TABLE spst
          WHERE   NVL (process_flag, 'I') = 'I';

      CURSOR lcu_sub (ledger_name VARCHAR2)
      IS
         SELECT   ROWID, spst.*
           FROM   GL_STAGE_TABLE spst
          WHERE   NVL (process_flag, 'I') = 'I' AND REFERENCE4 = ledger_name;

      ln_org_id                    NUMBER;
      ln_exist                     NUMBER;
      code_combination_count       NUMBER;
      CONCATENATED_SEGMENTS_TEMP   VARCHAR2 (100);
      code_invalied_count          NUMBER;
      temp_error_message           VARCHAR2 (500);
      OPEN_PERIOD_COUNT            number;
   BEGIN
      FOR rec IN lcu
      LOOP
         /*   fnd_file.put_line(fnd_file.log, 'Validation Status' || lc_validation_status);

            If rec.patient_number is null then
              lv_error_message := lv_error_message ||'Patient Number not found,';
              lc_validation_status := 'E';
            End If;

            If lc_validation_status is null then

            End If;

         */
         code_invalied_count := 0;

        

         FOR rec_sub IN lcu_sub (rec.REFERENCE4)
         LOOP
            code_combination_count := 0;
            OPEN_PERIOD_COUNT := 0;

            temp_error_message := NULL;
           
            SELECT      rec_sub.SEGMENT1
                     || '.'
                     || rec_sub.SEGMENT2
                     || '.'
                     || rec_sub.SEGMENT3
                     || '.'
                     || rec_sub.SEGMENT4
                     || '.'
                     || rec_sub.SEGMENT5
                     || '.'
                     || rec_sub.SEGMENT6
                     || '.'
                     || rec_sub.SEGMENT7
                     || '.'
                     || rec_sub.SEGMENT8
              INTO   CONCATENATED_SEGMENTS_TEMP
              FROM   DUAL;


            SELECT   COUNT ( * )
              INTO   code_combination_count
              FROM   gl_code_combinations_kfv
             WHERE   CONCATENATED_SEGMENTS = CONCATENATED_SEGMENTS_TEMP;
            
            
            
            SELECT COUNT(*) INTO OPEN_PERIOD_COUNT FROM GL_PERIOD_STATUSES
                  WHERE   CLOSING_STATUS LIKE 'O'
                           AND APPLICATION_ID = 101
                           AND PERIOD_NAME = to_char(rec_sub.ACCOUNTING_DATE,'MON-YY');


            If rec_sub.CURRENCY_CODE != 'AED'
            then
            
                If rec_sub.USER_CURRENCY_CONVERSION_TYPE = 'User' THEN
               
                    IF  rec_sub.CURRENCY_CONVERSION_RATE IS NULL THEN
                   
                      code_invalied_count := code_invalied_count + 1;
                     
                    temp_error_message :=
                     'CURRENCY CONVERSION RATE is not provided'
                     || ' for '
                     || rec_sub.REFERENCE4;
                    
                     UPDATE   GL_STAGE_TABLE spst
               SET  -- spst.validation_status = NVL (lc_validation_status, 'E'),
                     spst.error_desc = spst.error_desc|| '...'||temp_error_message
             WHERE   spst.rowid = rec_sub.rowid;
                    
                     end if;
                    
                     end if;
                    
                     end if;
                 
                 
                   
               IF (OPEN_PERIOD_COUNT = 0)
            THEN
            code_invalied_count := code_invalied_count + 1;
           
             temp_error_message :=
                     'Period Is Closed'
                  || ' for '
                  || rec_sub.REFERENCE4;
                    
                       UPDATE   GL_STAGE_TABLE spst
               SET  -- spst.validation_status = NVL (lc_validation_status, 'E'),
                     spst.error_desc = spst.error_desc|| '...'||temp_error_message
             WHERE   spst.rowid = rec_sub.rowid;
            
                     end if;
                      

            IF (code_combination_count = 0)
            THEN
               code_invalied_count := code_invalied_count + 1;
               temp_error_message :=
                     'Account Code is invalied '
                  || rec_sub.SEGMENT1
                  || '.'
                  || rec_sub.SEGMENT2
                  || '.'
                  || rec_sub.SEGMENT3
                  || '.'
                  || rec_sub.SEGMENT4
                  || '.'
                  || rec_sub.SEGMENT5
                  || '.'
                  || rec_sub.SEGMENT6
                  || '.'
                  || rec_sub.SEGMENT7
                  || '.'
                  || rec_sub.SEGMENT8
                  || ' for '
                  || rec_sub.REFERENCE4;
                 
                   UPDATE   GL_STAGE_TABLE spst
               SET  -- spst.validation_status = NVL (lc_validation_status, 'E'),
                     spst.error_desc = spst.error_desc|| '...'||temp_error_message
             WHERE   spst.rowid = rec_sub.rowid;
            
            END IF;
         END LOOP;

         IF (code_invalied_count >= 1)
         THEN
            UPDATE   GL_STAGE_TABLE spst
               SET   spst.validation_status = NVL (lc_validation_status, 'E')
                 --    spst.error_desc = temp_error_message
             WHERE   SPST.REFERENCE4 = rec.reference4;
         ELSE
            UPDATE   GL_STAGE_TABLE spst
               SET   spst.validation_status = NVL (lc_validation_status, 'S')
                --     spst.error_desc = lv_error_message
             WHERE   SPST.REFERENCE4 = rec.reference4;
         END IF;

         gn_records_validated := gn_records_validated + 1;
      END LOOP;

--      fnd_file.put_line (fnd_file.LOG,
--                         'Total records validated :' || gn_records_validated);
--      fnd_file.put_line (fnd_file.output,
--                         'Total records validated :' || gn_records_validated);
   END validate_gl_records;


 /* Moving data from  GL Staging Table  to GL Interface Table */
   PROCEDURE load_into_gl_interface
   IS
      ln_result              NUMBER;
      lv_error_message       VARCHAR2 (2000);
      lc_return_status       VARCHAR2 (30);
      l_msg_count            INTEGER;
      l_msg_data             VARCHAR2 (200);
      l_msg_data_out         VARCHAR2 (200);
      l_count                INTEGER;
      l_return_status        VARCHAR2 (20);
      coun                   NUMBER := 0;
      l_mesg                 VARCHAR2 (2000);
      CONCATENATED_ID_TEMP   NUMBER;

      CURSOR lcu1
      IS
         SELECT   *
           FROM   GL_STAGE_TABLE
          WHERE   validation_status = 'S';
   BEGIN
      lv_error_message := NULL;



      FOR rec IN lcu1
      LOOP
         SELECT   CODE_COMBINATION_ID
           INTO   CONCATENATED_ID_TEMP
           FROM   GL_CODE_COMBINATIONS_KFV
          WHERE   CONCATENATED_SEGMENTS =
                        rec.SEGMENT1
                     || '.'
                     || rec.SEGMENT2
                     || '.'
                     || rec.SEGMENT3
                     || '.'
                     || rec.SEGMENT4
                     || '.'
                     || rec.SEGMENT5
                     || '.'
                     || rec.SEGMENT6
                     || '.'
                     || rec.SEGMENT7
                     || '.'
                     || rec.SEGMENT8;

         INSERT INTO gl_interface (
                                      status,
                                      ledger_id,
                                      accounting_date,
                                      currency_code,
                                      date_created,
                                      created_by,
                                      actual_flag,
                                      user_je_category_name,
                                      user_je_source_name,
                                      code_combination_id,
                                      entered_dr,
                                      entered_cr,
                                      reference4,
                                      reference5
                    )
           VALUES   (
                        rec.status,
                        rec.ledger_id,
                        rec.accounting_date,
                        rec.currency_code,
                        rec.date_created,
                        rec.created_by,
                        rec.actual_flag,
                        rec.user_je_category_name,
                        rec.user_je_source_name,
                        concatenated_id_temp,
                        rec.entered_dr,
                        rec.entered_cr,
                           rec.reference4
                        || ' '
                        || TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI:SS'),
                        rec.reference5
                    );

         gn_records_inserted := gn_records_inserted + 1;
      END LOOP;

      COMMIT;

      fnd_file.put_line (
         fnd_file.LOG,
         'GL Records Successfully Inserted:  ' || gn_records_inserted
      );
      fnd_file.put_line (fnd_file.LOG,
                         'GL Interface Error' || l_msg_data_out);
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'API has Failed');
   END load_into_gl_interface;


 /* Displaying The Message  */
   PROCEDURE display_log
   IS
      ln_error_records     NUMBER;
      ln_success_records   NUMBER;

      CURSOR c1
      IS
         SELECT   *
           FROM   GL_STAGE_TABLE
          WHERE  request_id = gn_request_id AND VALIDATION_STATUS = 'E';

      CURSOR c2
      IS
         SELECT   *
           FROM   GL_STAGE_TABLE
          WHERE   request_id = gn_request_id AND VALIDATION_STATUS = 'S';
   BEGIN
      SELECT   COUNT (1)
        INTO   ln_error_records
        FROM   GL_STAGE_TABLE
       WHERE  request_id = gn_request_id AND  VALIDATION_STATUS = 'E';

      SELECT   COUNT (1)
        INTO   ln_success_records
        FROM   GL_STAGE_TABLE
       WHERE  request_id = gn_request_id AND  VALIDATION_STATUS = 'S';


      fnd_file.put_line (fnd_file.LOG,
                         'Total Process records :  ' || ln_success_records);
      fnd_file.put_line (fnd_file.output,
                         'Total Process records :  ' || ln_success_records);
      fnd_file.put_line (fnd_file.LOG,
                         'Total Error records :  ' || ln_error_records);
      fnd_file.put_line (fnd_file.output,
                         'Total Error records :  ' || ln_error_records);

      IF ln_error_records > 0
      THEN
         fnd_file.put_line (fnd_file.LOG, 'Error record details.');
         fnd_file.put_line (fnd_file.output, 'Error record details.');
         fnd_file.put_line (
            fnd_file.LOG,
            ' ------------- -------------- ------------ ---------------------------------------'
         );
         fnd_file.put_line (
            fnd_file.output,
            ' ------------- -------------- ------------ ---------------------------------------'
         );

         FOR err IN c1
         LOOP
           
              fnd_file.put_line(fnd_file.log, ' '|| err.ERROR_DESC||'..........');
              fnd_file.put_line(fnd_file.output, ' '|| err.ERROR_DESC||'..........');
             
            NULL;
         END LOOP;
      ELSE
         fnd_file.put_line (fnd_file.LOG,
                            'Successfully created following Gernal Entries');
         fnd_file.put_line (fnd_file.output,
                            'Successfully created following Gernal Entries');
         fnd_file.put_line (fnd_file.LOG,
                            ' ------------- -------------- ------------');
         fnd_file.put_line (fnd_file.output,
                            ' ------------- -------------- ------------');

         FOR poc IN c2
         LOOP
             fnd_file.put_line(fnd_file.log, ' '|| poc.USER_JE_SOURCE_NAME||'..........');
              fnd_file.put_line(fnd_file.output, ' '|| poc.USER_JE_SOURCE_NAME||'..........');
            NULL;
         END LOOP;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END display_log;


 /* Final Procedure Calling All Procedure */
   PROCEDURE load_gl (errbuf OUT VARCHAR2, retcode OUT NUMBER)
   IS
   BEGIN
      gn_org_id := 82;

      load_into_gl_stage_table;

      validate_gl_records;


      COMMIT;
      load_into_gl_interface;
      COMMIT;

      display_log; 

      COMMIT;
      --Removing one month old records from staging table
      --
      DELETE FROM   GL_STAGE_TABLE
            WHERE   TRUNC (process_date) <= TRUNC (ADD_MONTHS (SYSDATE, -1))
                    AND process_flag = 'P';
   --
   --Deleting the errored out record
   --
   /*****
   delete
   from src_po_staging_table
   where process_flag = 'E';
   *******/

   END load_gl;
END INSIS_GL_INTERFACE;

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;

ap invoice payment interface

CREATE OR REPLACE PACKAGE BODY xxte_apinvoice_pay_intf
AS
   PROCEDURE xxte_apinvoice_main (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
   IS
      lv_errbuff   VARCHAR2 (1000);
      lv_retcode   NUMBER;
   BEGIN
      xxte_apinvoice_ins (lv_errbuff, lv_retcode);
      xxte_apinvoice_comm (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_apinvoice_comm (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
   IS
      CURSOR stg_cur
      IS
         SELECT a.*, a.ROWID
           FROM xxte_apinv_comm_st a
          WHERE process_flag = 'N';

      -- declare local variables.
      lv_user_id          NUMBER;
      lv_resp_id          NUMBER;
      lv_resp_appl_id     NUMBER;
      lv_vendor_id        NUMBER;
      lv_vendor_no        NUMBER;
      lv_error_message    VARCHAR2 (2400);
      lv_error_code       NUMBER          := 0;
      lv_vendor_site_id   NUMBER;
      lv_invoice_date     VARCHAR2 (1);
      lv_gl_date          VARCHAR2 (1);
      lv_exist_rec        NUMBER;
      lv_invoice_amt      NUMBER;
      lv_acc              NUMBER;
      lv_source           VARCHAR2 (30);
      lv_invoice_type     VARCHAR2 (50);
      lv_dist_ccid        NUMBER;
   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 ('AP');
      END;

      -- Purge the previous processed data in the stage/interface table
      BEGIN
         DELETE FROM ap_invoice_lines_interface apil
               WHERE apil.invoice_id IN (SELECT invoice_id
                                           FROM ap_invoices_interface
                                          WHERE status = 'PROCESSED');

         DELETE FROM ap_invoices_interface
               WHERE status = 'PROCESSED';
      EXCEPTION
         WHEN OTHERS
         THEN
            lv_error_code := 1;
            lv_error_message :=
                  'Error: Unable To Delete The Invoice Stage/Interface Table.'
               || SQLERRM;
            fnd_file.put_line (fnd_file.LOG, lv_error_message);
      END;

      fnd_file.put_line
         (2,
          '****************************Agent Commission Invoice***************************'
         );
      fnd_file.put_line
         (2,
          '------------------------------------------------------------------------------- '
         );

      FOR i IN stg_cur
      LOOP
         lv_error_message := NULL;
         lv_error_code := 0;
         lv_source := 'TE INSIS';
         lv_dist_ccid := 2106;

         -- Validation for Invoice Type
         BEGIN
            SELECT invoice_type
              INTO lv_invoice_type
              FROM xxte_apinv_comm_st
             WHERE ROWID = i.ROWID;

            IF lv_invoice_type IS NULL
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'Invoice Type Is Null';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'Invalid Invoice Type';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

         -- Validation for vendor name
         BEGIN
            SELECT vendor_id
              INTO lv_vendor_id
              FROM po_vendors
             WHERE vendor_name = i.vendor_name;

            IF lv_vendor_id IS NULL
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Vendor Name '
                  || ' '
                  || i.vendor_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: '
                  || 'Vendor Name '
                  || ' '
                  || i.vendor_name
                  || ' '
                  || 'is Not Found';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

-- Validation for vendor no
         BEGIN
            SELECT segment1
              INTO lv_vendor_no
              FROM po_vendors
             WHERE segment1 = i.vendor_no AND vendor_name = i.vendor_name;

            IF lv_vendor_no IS NULL
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Vendor No '
                  || ' '
                  || i.vendor_no
                  || ' '
                  || ' is Not Found for the vendor'
                  || ' '
                  || i.vendor_name;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Vendor No '
                  || ' '
                  || i.vendor_no
                  || ' '
                  || ' is Not Found for the vendor'
                  || ' '
                  || i.vendor_name;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

         -- Validation for vendor site id
         BEGIN
            SELECT vendor_site_id
              INTO lv_vendor_site_id
              FROM po_vendor_sites_all
             WHERE UPPER (vendor_site_code) = UPPER (i.vendor_site)
               AND vendor_id IN (SELECT vendor_id
                                   FROM po_vendors
                                  WHERE vendor_name = i.vendor_name);

            IF lv_vendor_site_id IS NULL
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Vendor Site Name '
                  || ' '
                  || i.vendor_site
                  || ' '
                  || 'is Not found for the Vendor'
                  || ' '
                  || i.vendor_name;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Vendor Site Name '
                  || ' '
                  || i.vendor_site
                  || ' '
                  || 'is Not found for the Vendor'
                  || ' '
                  || i.vendor_name;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

         -- Validation for invoice date
         BEGIN
            SELECT 'x'
              INTO lv_invoice_date
              FROM gl_period_statuses
             WHERE application_id = 200                ---For Oracle Payables.
               AND set_of_books_id = 2022
               AND (i.invoice_date >= start_date
                    AND i.invoice_date <= end_date
                   )
               AND closing_status = 'O';

            IF lv_invoice_date IS NULL
            THEN
               RAISE NO_DATA_FOUND;
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'Invoice Date Is NULL ';
               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 Invoice Date '
                  || ' '
                  || i.invoice_date;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

-- Validation for GL date
         BEGIN
            SELECT 'x'
              INTO lv_gl_date
              FROM gl_period_statuses
             WHERE application_id = 200                ---For Oracle Payables.
               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 ';
               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 '
                  || ' '
                  || i.gl_date;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

         -- Validation for currency code
         BEGIN
            IF i.invoice_currency != 'AED'
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Invalid Currency Code '
                  || ' '
                  || i.invoice_currency;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;
         END;

         -- Validation for Invoice amount
         BEGIN
            SELECT invoice_amount
              INTO lv_invoice_amt
              FROM xxte_apinv_comm_st
             WHERE ROWID = i.ROWID;

            IF lv_invoice_amt IS NULL
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'Invoice Amount Is Null';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'Invoice Amount Is Null';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

         -- Validation for Account
         BEGIN
            SELECT dist_account
              INTO lv_acc
              FROM xxte_apinv_comm_st
             WHERE ROWID = i.ROWID;

            IF lv_acc IS NULL
            THEN
               lv_error_code := 1;
               lv_error_message :=
                                  'Error: ' || 'Distribution Account Is Null';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message :=
                                  'Error: ' || 'Distribution Account Is Null';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

         --Validation for excisting record in base table
         BEGIN
            SELECT COUNT (*)
              INTO lv_exist_rec
              FROM ap_invoices_all
             WHERE invoice_amount = i.invoice_amount
               AND invoice_date = i.invoice_date
               AND vendor_id IN (SELECT vendor_id
                                   FROM po_vendors
                                  WHERE vendor_name = i.vendor_name);

            IF lv_exist_rec > 0
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'Record Already Exists';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'Record Already Exists';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

         IF     lv_invoice_type IN
                   ('PAYABLE', 'COMMISION ADV', 'CLAIM', 'SURRENDER',
                    'MATURITY', 'RETAKAFUL EXP', 'RETAKAFUL PAYABLE',
                    'CLAIM PAYABLE', 'DEATH CLAIM EXP')
            AND lv_invoice_amt > 0
         THEN
            lv_invoice_type := 'STANDARD';
         ELSIF     lv_invoice_type IN
                      ('PAYABLE', 'COMMISION ADV', 'REFUND', 'RETAKAFUL EXP',
                       'RETAKAFUL PAYABLE', 'RETAFUL  COMMISION',
                       'RETAKAFUL SHARE IN EXP', 'RECEIVABLE FROM RETAKAFUL')
               AND lv_invoice_amt < 0
         THEN
            lv_invoice_type := 'DEBIT';
         ELSE
            lv_error_code := 1;
            lv_error_message := 'Error: ' || 'Invalid Invoice Type';
            fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END IF;

         IF lv_error_code = 0
         THEN
            INSERT INTO ap_invoices_interface
                        (invoice_id,
                         invoice_num,
                         gl_date, invoice_type_lookup_code, invoice_date,
                         vendor_id, vendor_site_id, invoice_amount,
                         invoice_currency_code, description, SOURCE, org_id
                        )
                 VALUES (ap_invoices_interface_s.NEXTVAL,
                            'INSIS_'
                         || i.vendor_site
                         || '_'
                         || ap_invoices_interface_s.CURRVAL,
                         i.gl_date, lv_invoice_type,            -- will change
                                                    i.invoice_date,
                         lv_vendor_id, lv_vendor_site_id, i.invoice_amount,
                         i.invoice_currency, i.description,     -- will change
                                                           lv_source, 81
                        );

            INSERT INTO ap_invoice_lines_interface
                        (invoice_id, line_number, line_type_lookup_code,
                         amount, dist_code_combination_id,
-- if value dist id is entered here no need to enter value in dist_code_concatenated
                                                          org_id
                        )
                 VALUES (ap_invoices_interface_s.CURRVAL, i.line_no, 'ITEM',
                         i.amount, lv_dist_ccid,                -- will update
                                                81
                        );

            fnd_file.put_line (2, 'Payable Invoice Is Created');

            UPDATE xxte_apinv_comm_st
               SET process_flag = 'S'
             WHERE ROWID = i.ROWID;
         ELSE
            fnd_file.put_line (2, 'Please Check The Log File For Error(s)');

            UPDATE xxte_apinv_comm_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;
         fnd_file.put_line (fnd_file.LOG, 'Error :' || SQLERRM);
         COMMIT;
   END;

   PROCEDURE xxte_apinvoice_ins (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
   IS
      CURSOR ins_c
      IS
         SELECT   'PAYABLE' invoice_type, a.vendor_name, a.vendor_no,
                  a.vendor_site,
                  (SELECT invoice_date
                     FROM xxte_apinv_pay_temp
                    WHERE vendor_name = a.vendor_name
                      AND vendor_site = a.vendor_site
                      AND ROWNUM = 1) invoice_date,
                  (SELECT invoice_currency
                     FROM xxte_apinv_pay_temp
                    WHERE vendor_name = a.vendor_name
                      AND vendor_site = a.vendor_site
                      AND ROWNUM = 1) invoice_currency,
                  SUM (a.invoice_amount) invoice_amount,
                  (SELECT gl_date
                     FROM xxte_apinv_pay_temp
                    WHERE vendor_name = a.vendor_name
                      AND vendor_site = a.vendor_site
                      AND ROWNUM = 1) gl_date,
                  (SELECT description
                     FROM xxte_apinv_pay_temp
                    WHERE vendor_name = a.vendor_name
                      AND vendor_site = a.vendor_site
                      AND ROWNUM = 1) description,
                  SUM (a.amount) amount,
                  (SELECT dist_account
                     FROM xxte_apinv_pay_temp
                    WHERE vendor_name = a.vendor_name
                      AND vendor_site = a.vendor_site
                      AND ROWNUM = 1) dist_account
             FROM xxte_apinv_pay_temp a
            WHERE a.invoice_type IN ('COMMISION ADV', 'PAYABLE')
         GROUP BY a.vendor_name, a.vendor_site, a.vendor_no
           HAVING SUM (a.invoice_amount) != 0
         UNION
         SELECT *
           FROM xxte_apinv_pay_temp
          WHERE invoice_type NOT IN ('COMMISION ADV', 'PAYABLE');

      lv_error_message   VARCHAR2 (2400);
      lv_error_code      NUMBER          := 0;
   BEGIN
      BEGIN
         DELETE FROM xxte_apinv_comm_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;

      FOR i IN ins_c
      LOOP
         INSERT INTO xxte_apinv_comm_st
                     (invoice_type, vendor_name, vendor_no,
                      vendor_site, invoice_date, invoice_currency,
                      invoice_amount, gl_date, description, amount,
                      dist_account
                     )
              VALUES (i.invoice_type, i.vendor_name, i.vendor_no,
                      i.vendor_site, i.invoice_date, i.invoice_currency,
                      i.invoice_amount, i.gl_date, i.description, i.amount,
                      i.dist_account
                     );
      END LOOP;

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         lv_error_code := 1;
         lv_error_message :=
                  'Error: Unable To Insert Data In Staging Table.' || SQLERRM;
         fnd_file.put_line (fnd_file.LOG, lv_error_message);
   END;
END xxte_apinvoice_pay_intf;
/