GL INTERFACE

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