General Ledger Summary Reports

CREATE OR REPLACE PACKAGE APPS.xxhbc_deloitte_audit_extract
IS
   PROCEDURE extract_gl_je_lines(
      p_period_name              IN       VARCHAR2
     ,p_set_of_books_id          IN       NUMBER
   );

   PROCEDURE extract_gl_je_headers(
      p_period_name              IN       VARCHAR2
     ,p_set_of_books_id          IN       NUMBER
   );

   PROCEDURE extract_gl_code_combinations(
      p_period_name              IN       VARCHAR2
     ,p_set_of_books_id          IN       NUMBER
   );

   PROCEDURE extract_gl_je_batches(
      p_period_name              IN       VARCHAR2
     ,p_set_of_books_id          IN       NUMBER
   );

   PROCEDURE extract_flex_values(
      p_period_name              IN       VARCHAR2
     ,p_set_of_books_id          IN       NUMBER
   );
    PROCEDURE extract_lt_flex_values(
      p_period_name              IN       VARCHAR2
     ,p_set_of_books_id          IN       NUMBER
   );

   PROCEDURE main(
      errbuf                     OUT      VARCHAR2
     ,retcode                    OUT      VARCHAR2
     ,p_period_name              IN       VARCHAR2
     ,p_set_of_books_id          IN       NUMBER
   );
END;
/

CREATE OR REPLACE PACKAGE BODY APPS.xxhbc_deloitte_audit_extract
AS
   PROCEDURE main(
      errbuf                     OUT      VARCHAR2
     ,retcode                    OUT      VARCHAR2
     ,p_period_name              IN       VARCHAR2
     ,p_set_of_books_id          IN       NUMBER
   )
   IS

   BEGIN
      -- calls the Procedures with parameter p_period_name
      -- p_period_name contains the period info for which the record is to be extracted
      fnd_file.put_line(fnd_file.LOG, 'CALLING THE PROCEDURE extract_gl_je_lines');
      fnd_file.put_line(fnd_file.LOG, 'PARAMETER : Period Name : ' || p_period_name);
      fnd_file.put_line(fnd_file.LOG, 'Date :' || SYSDATE);
      fnd_file.put_line(fnd_file.LOG, 'Set Of Book ID :' || p_set_of_books_id);
      extract_gl_je_lines(p_period_name, p_set_of_books_id);
                                                            -- CALLING THE PROCEDURE extract_gl_je_lines :  PARAMETER : Period Name :,Set Of Books ID
      fnd_file.put_line(fnd_file.LOG, ' ');
      fnd_file.put_line(fnd_file.LOG, '           *********************         ');
      fnd_file.put_line(fnd_file.LOG, ' ');
      fnd_file.put_line(fnd_file.LOG, 'CALLING THE PROCEDURE extract_gl_je_headers');
      fnd_file.put_line(fnd_file.LOG, 'PARAMETER : Period Name : ' || p_period_name);
      fnd_file.put_line(fnd_file.LOG, 'Date :' || SYSDATE);
      fnd_file.put_line(fnd_file.LOG, 'Set Of Book ID :' || p_set_of_books_id);
      extract_gl_je_headers(p_period_name, p_set_of_books_id);
                                                           -- CALLING THE PROCEDURE extract_gl_je_headers:  PARAMETER : Period Name :,Set Of Books ID
      fnd_file.put_line(fnd_file.LOG, ' ');
      fnd_file.put_line(fnd_file.LOG, '           *********************         ');
      fnd_file.put_line(fnd_file.LOG, ' ');
      fnd_file.put_line(fnd_file.LOG, 'CALLING THE PROCEDURE extract_gl_code_combinations');
      fnd_file.put_line(fnd_file.LOG, 'PARAMETER : Period Name : ' || p_period_name);
      fnd_file.put_line(fnd_file.LOG, 'Date :' || SYSDATE);
      fnd_file.put_line(fnd_file.LOG, 'Set Of Book ID :' || p_set_of_books_id);
      extract_gl_code_combinations(p_period_name, p_set_of_books_id);
      -- CALLING THE PROCEDURE extract_gl_code_combinations:  PARAMETER : Period Name :,Set Of Books ID
      fnd_file.put_line(fnd_file.LOG, ' ');
      fnd_file.put_line(fnd_file.LOG, '           *********************         ');
      fnd_file.put_line(fnd_file.LOG, ' ');
      fnd_file.put_line(fnd_file.LOG, 'CALLING THE PROCEDURE extract_gl_je_batches');
      fnd_file.put_line(fnd_file.LOG, 'PARAMETER : Period Name : ' || p_period_name);
      fnd_file.put_line(fnd_file.LOG, 'Date :' || SYSDATE);
      fnd_file.put_line(fnd_file.LOG, 'Set Of Book ID :' || p_set_of_books_id);
      extract_gl_je_batches(p_period_name, p_set_of_books_id);
                                                           -- CALLING THE PROCEDURE extract_gl_je_batches:  PARAMETER : Period Name :,Set Of Books ID
      fnd_file.put_line(fnd_file.LOG, ' ');
      fnd_file.put_line(fnd_file.LOG, '           *********************         ');
      fnd_file.put_line(fnd_file.LOG, ' ');
      fnd_file.put_line(fnd_file.LOG, 'CALLING THE PROCEDURE extract_flex_values');
      fnd_file.put_line(fnd_file.LOG, 'PARAMETER : Period Name : ' || p_period_name);
      fnd_file.put_line(fnd_file.LOG, 'Date :' || SYSDATE);
      fnd_file.put_line(fnd_file.LOG, 'Set Of Book ID :' || p_set_of_books_id);

      IF (p_set_of_books_id = 1)
      THEN
         extract_flex_values(p_period_name, p_set_of_books_id);
                                                             -- CALLING THE PROCEDURE extract_flex_values:  PARAMETER : Period Name :,Set Of Books ID
      ELSE
         extract_lt_flex_values(p_period_name, p_set_of_books_id);
      END IF;
   --It will handel the exception if any
   EXCEPTION
      WHEN UTL_FILE.invalid_mode
      THEN
         fnd_file.put_line(fnd_file.LOG, ' Error, 29281 Invalid Mode Parameter, The open_mode parameter in FOPEN is invalid');
      WHEN UTL_FILE.invalid_path
      THEN
         fnd_file.put_line(fnd_file.LOG, 'Error, 29280 Invalid File Location, Specified path does not exist or is not visible to Oracle');
      WHEN UTL_FILE.invalid_filehandle
      THEN
         fnd_file.put_line(fnd_file.LOG, 'Error, 29282 Invalid Filehandle, File handle does not exist');
      WHEN UTL_FILE.invalid_operation
      THEN
         fnd_file.put_line(fnd_file.LOG, 'Error, 29283 Invalid Operation, File could not be opened or operated on as requested');
      WHEN UTL_FILE.internal_error
      THEN
         fnd_file.put_line(fnd_file.LOG, 'Internal Error, 29286 Unhandled internal error in the UTL_FILE package');
      WHEN UTL_FILE.charsetmismatch
      THEN
         fnd_file.put_line
                          (fnd_file.LOG
                          ,'ERROR, 29298 A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE'
                          );
      WHEN UTL_FILE.file_open
      THEN
         fnd_file.put_line(fnd_file.LOG, 'ERROR, File Already Opened');
      WHEN UTL_FILE.invalid_filename
      THEN
         fnd_file.put_line(fnd_file.LOG, 'Error, 29288 Invalid File Name, A file with the specified name does not exist in the path');
      WHEN UTL_FILE.access_denied
      THEN
         fnd_file.put_line(fnd_file.LOG, 'Error, 29289 Access to the file has been denied by the operating system');
      --- Default exception if all the above failed to handel
      WHEN OTHERS
      THEN
         errbuf                     := SUBSTR(SQLERRM, 1, 150);
         retcode                    := 1;
         fnd_file.put_line(fnd_file.LOG, ' LAST EXCEPTION HIT (OTHER EXCEPTION) IN MAIN PROCEDURE, Unknown UTL_FILE Error');
         fnd_file.put_line(fnd_file.LOG, ' UNHANDLED EXCEPTION/p_errbuf = ' || errbuf);
         fnd_file.put_line(fnd_file.LOG, ' UNHANDLED EXCEPTION/p_retcode = ' || retcode);
   END;

   ------------------------************* extract_gl_je_lines *************----------------
/*
                           ---*** Procedure Description ***---
This procedure will extract the data from the table gl_je_lines based on
the p_period_name criteria and will save them as an ascii text file (.dat) format

                           --***Variable Defination: ***---

gl_je_l_cur : Is a cursor defination
vcur_gl_je_h : Is the cursor variable of type gl_je_l_cur which will fetch the data.
*/
   PROCEDURE extract_gl_je_lines(
      p_period_name              IN       VARCHAR2
     ,p_set_of_books_id          IN       NUMBER
   )
   IS
      v_file_handler                UTL_FILE.file_type;

      CURSOR gl_je_l_cur
      IS
         SELECT *
           FROM gl_je_lines
          WHERE period_name = p_period_name AND set_of_books_id = p_set_of_books_id;                                             -- To Include LT ALSO

      v_file_name                   VARCHAR2(50);
      v_profile_path                VARCHAR2(100);
      v_rec_txt                     VARCHAR2(32000);
      v_rec_header                  VARCHAR2(32000);
      v_record_count                NUMBER;
   BEGIN
      IF (p_set_of_books_id = 1)
      THEN
         v_file_name                := 'xxhbc_dt_gl_je_lines_' || p_period_name || '.dat';
      ELSE
         v_file_name                := 'xxlt_dt_gl_je_lines_' || p_period_name || '.dat';
      END IF;

      fnd_profile.get('XXHBCDATA_TOP', v_profile_path);
      v_profile_path             := v_profile_path || '/outbound';
      v_file_handler             := UTL_FILE.fopen(v_profile_path, v_file_name, 'W', 32000);
      v_rec_header               :=
         'JE_HEADER_ID|JE_LINE_NUM|LAST_UPDATE_DATE|LAST_UPDATED_BY|SET_OF_BOOKS_ID|CODE_COMBINATION_ID|PERIOD_NAME|EFFECTIVE_DATE|STATUS|CREATION_DATE|CREATED_BY|LAST_UPDATE_LOGIN|ACCOUNTED_DR|ACCOUNTED_CR|DESCRIPTION|';
      UTL_FILE.put_line(v_file_handler, v_rec_header);
      v_record_count             := 0;
      fnd_file.put_line(fnd_file.LOG, 'START OF EXTRACT           Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
      fnd_file.put_line(fnd_file.LOG, 'Extracting Into: ' || v_file_name);
      fnd_file.put_line(fnd_file.LOG, ' ');

      -- Opens the cursor and fetches the rows
      FOR vcur_gl_je_l IN gl_je_l_cur
      LOOP
         v_rec_txt                  :=
            REPLACE(REPLACE(   vcur_gl_je_l.je_header_id
                            || '|'
                            || vcur_gl_je_l.je_line_num
                            || '|'
                            || TO_CHAR(vcur_gl_je_l.last_update_date, 'DD-MON-YY HH:MI:SS PM')
                            || '|'
                            || vcur_gl_je_l.last_updated_by
                            || '|'
                            || vcur_gl_je_l.set_of_books_id
                            || '|'
                            || vcur_gl_je_l.code_combination_id
                            || '|'
                            || vcur_gl_je_l.period_name
                            || '|'
                            || vcur_gl_je_l.effective_date
                            || '|'
                            || vcur_gl_je_l.status
                            || '|'
                            || TO_CHAR(vcur_gl_je_l.creation_date, 'DD-MON-YY HH:MI:SS PM')
                            || '|'
                            || vcur_gl_je_l.created_by
                            || '|'
                            || vcur_gl_je_l.last_update_login
                            || '|'
                            || vcur_gl_je_l.accounted_dr
                            || '|'
                            || vcur_gl_je_l.accounted_cr
                            || '|'
                            || vcur_gl_je_l.description
                            || '|'
                           ,CHR(10)
                           ,' '
                           )
                   ,CHR(13)
                   ,' '
                   );
         UTL_FILE.put_line(v_file_handler, v_rec_txt);
--vcur_gl_je_l.je_header_id||'|'||vcur_gl_je_l.je_line_num||'|'||TO_CHAR(vcur_gl_je_l.last_update_date,'DD-MON-YY HH:MI:SS PM')||'|'||vcur_gl_je_l.last_updated_by||'|'||vcur_gl_je_l.set_of_books_id||'|'||vcur_gl_je_l.code_combination_id||'|'||vcur_gl_je_l.period_name||'|'||vcur_gl_je_l.effective_date||'|'||vcur_gl_je_l.status||'|'||TO_CHAR(vcur_gl_je_l.creation_date, 'DD-MON-YY HH:MI:SS PM')||'|'||vcur_gl_je_l.created_by||'|'||vcur_gl_je_l.last_update_login||'|'||vcur_gl_je_l.accounted_dr||'|'||vcur_gl_je_l.accounted_cr||'|'||vcur_gl_je_l.description||'|');
         v_record_count             := v_record_count + 1;
      END LOOP;

      UTL_FILE.put_line(v_file_handler, 'Record_Count:' || v_record_count);
      fnd_file.put_line(fnd_file.LOG, 'Total Records Extracted: ' || v_record_count);
      fnd_file.put_line(fnd_file.LOG, 'END OF EXTRACT             Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
      UTL_FILE.fclose(v_file_handler);
   END extract_gl_je_lines;

   ------------------------*************END  extract_gl_je_lines *************----------------

   ------------------------************* extract_gl_je_headers *************----------------

   /*
                              ---*** Procedure Description ***---
   This procedure will extract the data from the table gl_je_headers based on
   the p_period_name criteria and will save them as an ascii text file (.dat) format

                              --***Variable Defination: ***---

   gl_je_h_cur : Is a cursor defination
   vcur_gl_je_h : Is the cursor variable of type gl_je_l_cur which will fetch the data.
   */
   PROCEDURE extract_gl_je_headers(
      p_period_name              IN       VARCHAR2
     ,p_set_of_books_id          IN       NUMBER
   )
   IS
      v_file_handler                UTL_FILE.file_type;

      CURSOR gl_je_h_cur
      IS
         SELECT js.user_je_source_name
               ,js.description source_description
               ,jh.*
           FROM gl_je_headers jh
               ,gl_je_sources js
          WHERE period_name = p_period_name AND jh.je_source = js.je_source_name AND jh.set_of_books_id = p_set_of_books_id;

      -- Modified by Chandra  - for LT Changes
      v_file_name                   VARCHAR2(50);
      v_profile_path                VARCHAR2(100);
      v_record_count                NUMBER;
      v_rec_txt                     VARCHAR2(32000);
      v_rec_header                  VARCHAR2(32000);
   BEGIN
      IF (p_set_of_books_id = 1)
      THEN
         v_file_name                := 'xxhbc_dt_gl_je_headers_' || p_period_name || '.dat';
      ELSE
         v_file_name                := 'xxlt_dt_gl_je_headers_' || p_period_name || '.dat';
      END IF;

      fnd_profile.get('XXHBCDATA_TOP', v_profile_path);
      v_profile_path             := v_profile_path || '/outbound';
      v_file_handler             := UTL_FILE.fopen(v_profile_path, v_file_name, 'W', 32000);
      v_rec_header               :=
         'JE_HEADER_ID|LAST_UPDATE_DATE|LAST_UPDATED_BY|SET_OF_BOOKS_ID|JE_CATEGORY|JE_SOURCE|PERIOD_NAME|NAME|CURRENCY_CODE|STATUS|DATE_CREATED|CREATION_DATE|CREATED_BY|LAST_UPDATE_LOGIN|BALANCED_JE_FLAG|BALANCING_SEGMENT_VALUE|JE_BATCH_ID|FROM_RECURRING_HEADER_ID|EARLIEST_POSTABLE_DATE|POSTED_DATE|DESCRIPTION|CURRENCY_CONVERSION_RATE|CURRENCY_CONVERSION_TYPE|CURRENCY_CONVERSION_DATE|PARENT_JE_HEADER_ID|REVERSED_JE_HEADER_ID|INTERCOMPANY_MODE|USER_JE_SOURCE_NAME|SOURCE_DESCRIPTION|';
      UTL_FILE.put_line(v_file_handler, v_rec_header);
      v_record_count             := 0;
      fnd_file.put_line(fnd_file.LOG, 'START OF EXTRACT           Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
      fnd_file.put_line(fnd_file.LOG, 'Extracting Into: ' || v_file_name);
      fnd_file.put_line(fnd_file.LOG, ' ');

      FOR vcur_gl_je_h IN gl_je_h_cur
      LOOP
         v_rec_txt                  :=
            REPLACE(REPLACE(   vcur_gl_je_h.je_header_id
                            || '|'
                            || TO_CHAR(vcur_gl_je_h.last_update_date, 'DD-MON-YY HH:MI:SS PM')
                            || '|'
                            || vcur_gl_je_h.last_updated_by
                            || '|'
                            || vcur_gl_je_h.set_of_books_id
                            || '|'
                            || vcur_gl_je_h.je_category
                            || '|'
                            || vcur_gl_je_h.je_source
                            || '|'
                            || vcur_gl_je_h.period_name
                            || '|'
                            || vcur_gl_je_h.NAME
                            || '|'
                            || vcur_gl_je_h.currency_code
                            || '|'
                            || vcur_gl_je_h.status
                            || '|'
                            || TO_CHAR(vcur_gl_je_h.date_created, 'DD-MON-YY HH:MI:SS PM')
                            || '|'
                            || TO_CHAR(vcur_gl_je_h.creation_date, 'DD-MON-YY HH:MI:SS PM')
                            || '|'
                            || vcur_gl_je_h.created_by
                            || '|'
                            || vcur_gl_je_h.last_update_login
                            || '|'
                            || vcur_gl_je_h.balanced_je_flag
                            || '|'
                            || vcur_gl_je_h.balancing_segment_value
                            || '|'
                            || vcur_gl_je_h.je_batch_id
                            || '|'
                            || vcur_gl_je_h.from_recurring_header_id
                            || '|'
                            || TO_CHAR(vcur_gl_je_h.earliest_postable_date, 'DD-MON-YY HH:MI:SS PM')
                            || '|'
                            || TO_CHAR(vcur_gl_je_h.posted_date, 'DD-MON-YY HH:MI:SS PM')
                            || '|'
                            || vcur_gl_je_h.description
                            || '|'
                            || vcur_gl_je_h.currency_conversion_rate
                            || '|'
                            || vcur_gl_je_h.currency_conversion_type
                            || '|'
                            || vcur_gl_je_h.currency_conversion_date
                            || '|'
                            || vcur_gl_je_h.parent_je_header_id
                            || '|'
                            || vcur_gl_je_h.reversed_je_header_id
                            || '|'
                            || vcur_gl_je_h.intercompany_mode
                            || '|'
                            || vcur_gl_je_h.user_je_source_name
                            || '|'
                            || vcur_gl_je_h.source_description
                            || '|'
                           ,CHR(10)
                           ,' '
                           )
                   ,CHR(13)
                   ,' '
                   );
         UTL_FILE.put_line(v_file_handler, v_rec_txt);
         v_record_count             := v_record_count + 1;
      END LOOP;

      UTL_FILE.put_line(v_file_handler, 'Record_Count:' || v_record_count);
      fnd_file.put_line(fnd_file.LOG, 'Total Records Extracted: ' || v_record_count);
      fnd_file.put_line(fnd_file.LOG, 'END OF EXTRACT             Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
      UTL_FILE.fclose(v_file_handler);
   END extract_gl_je_headers;

   ------------------------************* END extract_gl_je_headers *************----------------

   ------------------------************* extract_GL_CODE_COMBINATIONS *************----------------

   /*
                              ---*** Procedure Description ***---
   This procedure will extract the data from the table gl_code_combinations
   and will save them as an ascii text file (.dat) format

                              --***Variable Defination: ***---

   gl_code_c_cur : Is a cursor defination
   vcur_gl_code_c : Is the cursor variable of type gl_je_l_cur which will fetch the data.
   */
   PROCEDURE extract_gl_code_combinations(
      p_period_name              IN       VARCHAR2
     ,p_set_of_books_id          IN       NUMBER
   )
   IS
      v_file_handler                UTL_FILE.file_type;

      CURSOR gl_code_c_cur
      IS
         SELECT *
           FROM gl_code_combinations
          WHERE chart_of_accounts_id = DECODE(p_set_of_books_id, 1, 50182, 2, 50254);           -- Modified by Narasimha G Devalaraju - for LT Changes

      v_file_name                   VARCHAR2(50);
      v_profile_path                VARCHAR2(100);
      v_record_count                NUMBER;
      v_rec_txt                     VARCHAR2(32000);
      v_rec_header                  VARCHAR2(32000);
   BEGIN
      IF (p_set_of_books_id = 1)
      THEN
         v_file_name                := 'xxhbc_dt_gl_code_combinations_' || p_period_name || '.dat';
      ELSE
         v_file_name                := 'xxlt_dt_gl_code_combinations_' || p_period_name || '.dat';
      END IF;

      fnd_profile.get('XXHBCDATA_TOP', v_profile_path);
      v_profile_path             := v_profile_path || '/outbound';
      v_file_handler             := UTL_FILE.fopen(v_profile_path, v_file_name, 'W', 32000);
      v_rec_header               :=
         'CODE_COMBINATION_ID|CHART_OF_ACCOUNTS_ID|ACCOUNT_TYPE|ENABLED_FLAG|SEGMENT1|SEGMENT2|SEGMENT3|SEGMENT4|SEGMENT5|SEGMENT6|SEGMENT7|SEGMENT8|';
      UTL_FILE.put_line(v_file_handler, v_rec_header);
      v_record_count             := 0;
      fnd_file.put_line(fnd_file.LOG, 'START OF EXTRACT           Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
      fnd_file.put_line(fnd_file.LOG, 'Extracting Into: ' || v_file_name);
      fnd_file.put_line(fnd_file.LOG, ' ');

      FOR vcur_gl_code_c IN gl_code_c_cur
      LOOP
         v_rec_txt                  :=
            REPLACE(REPLACE(   vcur_gl_code_c.code_combination_id
                            || '|'
                            || vcur_gl_code_c.chart_of_accounts_id
                            || '|'
                            || vcur_gl_code_c.account_type
                            || '|'
                            || vcur_gl_code_c.enabled_flag
                            || '|'
                            || vcur_gl_code_c.segment1
                            || '|'
                            || vcur_gl_code_c.segment2
                            || '|'
                            || vcur_gl_code_c.segment3
                            || '|'
                            || vcur_gl_code_c.segment4
                            || '|'
                            || vcur_gl_code_c.segment5
                            || '|'
                            || vcur_gl_code_c.segment6
                            || '|'
                            || vcur_gl_code_c.segment7
                            || '|'
                            || vcur_gl_code_c.segment8
                            || '|'
                           ,CHR(10)
                           ,' '
                           )
                   ,CHR(13)
                   ,' '
                   );
         UTL_FILE.put_line(v_file_handler, v_rec_txt);
         v_record_count             := v_record_count + 1;
      END LOOP;

      UTL_FILE.put_line(v_file_handler, 'Record_Count:' || v_record_count);
      fnd_file.put_line(fnd_file.LOG, 'Total Records Extracted: ' || v_record_count);
      fnd_file.put_line(fnd_file.LOG, 'END OF EXTRACT             Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
      UTL_FILE.fclose(v_file_handler);
   END extract_gl_code_combinations;

   ------------------------*************END  extract_GL_CODE_COMBINATIONS *************----------------

   ------------------------************* extract_GL_JE_BATCHES *************----------------

   /*
                              ---*** Procedure Description ***---
   This procedure will extract the data from the table gl_je_batches based on
   the p_period_name criteria and will save them as an ascii text file (.dat) format

                              --***Variable Defination: ***---

   gl_je_b_cur : Is a cursor defination
   vcur_gl_je_b : Is the cursor variable of type gl_je_l_cur which will fetch the data.
   */
   PROCEDURE extract_gl_je_batches(
      p_period_name              IN       VARCHAR2
     ,p_set_of_books_id          IN       NUMBER
   )
   IS
      v_file_handler                UTL_FILE.file_type;

      CURSOR gl_je_b_cur
      IS
         SELECT jb.je_batch_id
               ,jb.NAME
               ,jb.status
               ,jb.approval_status_code
               ,jb.created_by
               ,fu1.user_name created_by_user
               ,fu1.description created_by_user_name
               ,jb.last_updated_by
               ,fu2.user_name last_updated_by_user
               ,fu2.description last_updated_by_user_name
           FROM gl_je_batches jb
               ,fnd_user fu1
               ,fnd_user fu2
          WHERE jb.default_period_name = p_period_name
            AND jb.created_by = fu1.user_id
            AND jb.last_updated_by = fu2.user_id
            AND jb.set_of_books_id = p_set_of_books_id;

      -- Modified by Narasimha G Devalaraju - for LT Changes
      v_file_name                   VARCHAR2(50);
      v_profile_path                VARCHAR2(100);
      v_record_count                NUMBER;
      v_rec_txt                     VARCHAR2(32000);
      v_rec_header                  VARCHAR2(32000);
   BEGIN
      IF (p_set_of_books_id = 1)
      THEN
         v_file_name                := 'xxhbc_dt_gl_je_batches_' || p_period_name || '.dat';
      ELSE
         v_file_name                := 'xxlt_dt_gl_je_batches_' || p_period_name || '.dat';
      END IF;

      fnd_profile.get('XXHBCDATA_TOP', v_profile_path);
      v_profile_path             := v_profile_path || '/outbound';
      v_file_handler             := UTL_FILE.fopen(v_profile_path, v_file_name, 'W', 32000);
      v_rec_header               :=
         'JE_BATCH_ID|NAME|STATUS|APPROVAL_STATUS_CODE|CREATED_BY|CREATED_BY_USER|CREATED_BY_USER_NAME|LAST_UPDATED_BY|LAST_UPDATED_BY_USER|LAST_UPDATED_BY_USER_NAME|';
      UTL_FILE.put_line(v_file_handler, v_rec_header);
      v_record_count             := 0;
      fnd_file.put_line(fnd_file.LOG, 'START OF EXTRACT           Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
      fnd_file.put_line(fnd_file.LOG, 'Extracting Into: ' || v_file_name);
      fnd_file.put_line(fnd_file.LOG, ' ');

      FOR vcur_gl_je_b IN gl_je_b_cur
      LOOP
         v_rec_txt                  :=
            REPLACE(REPLACE(   vcur_gl_je_b.je_batch_id
                            || '|'
                            || vcur_gl_je_b.NAME
                            || '|'
                            || vcur_gl_je_b.status
                            || '|'
                            || vcur_gl_je_b.approval_status_code
                            || '|'
                            || vcur_gl_je_b.created_by
                            || '|'
                            || vcur_gl_je_b.created_by_user
                            || '|'
                            || vcur_gl_je_b.created_by_user_name
                            || '|'
                            || vcur_gl_je_b.last_updated_by
                            || '|'
                            || vcur_gl_je_b.last_updated_by_user
                            || '|'
                            || vcur_gl_je_b.last_updated_by_user_name
                            || '|'
                           ,CHR(10)
                           ,' '
                           )
                   ,CHR(13)
                   ,' '
                   );
         UTL_FILE.put_line(v_file_handler, v_rec_txt);
         v_record_count             := v_record_count + 1;
      END LOOP;

      UTL_FILE.put_line(v_file_handler, 'Record_Count:' || v_record_count);
      fnd_file.put_line(fnd_file.LOG, 'Total Records Extracted: ' || v_record_count);
      fnd_file.put_line(fnd_file.LOG, 'END OF EXTRACT             Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
      UTL_FILE.fclose(v_file_handler);
   END extract_gl_je_batches;

   ------------------------*************END extract_GL_JE_BATCHES *************----------------

   ------------------------************* extract_flex_values *************----------------

   /*
                              ---*** Procedure Description ***---
   This procedure will extract the data from the table fnd_flex_values
   and will save them in an ascii text file (.dat) format

                              --***Variable Defination: ***---

   flex_val_cur : Is a cursor defination
   vcur_flex_val : Is the cursor variable of type gl_je_l_cur which will fetch the data.
   */
   PROCEDURE extract_flex_values(
      p_period_name              IN       VARCHAR2
     ,p_set_of_books_id          IN       NUMBER
   )
   IS
      v_file_handler                UTL_FILE.file_type;

      CURSOR flex_val_cur
      IS
         SELECT   s.flex_value_set_name
                 ,v.flex_value
                 ,v.description
                 ,v.enabled_flag
             FROM fnd_flex_values_vl v
                 ,fnd_flex_value_sets s
            WHERE v.flex_value_set_id = s.flex_value_set_id
              AND s.flex_value_set_name IN('HBC COMPANY', 'HBC LOCATION', 'HBC COST CENTRE', 'HBC ACCOUNT')
         ORDER BY v.flex_value;

      v_file_name                   VARCHAR2(50);
      v_profile_path                VARCHAR2(100);
      v_record_count                NUMBER;
      v_rec_txt                     VARCHAR2(32000);
      v_rec_header                  VARCHAR2(32000);
   BEGIN
      v_file_name                := 'xxhbc_dt_fnd_flex_values_vl_' || p_period_name || '.dat';
      fnd_profile.get('XXHBCDATA_TOP', v_profile_path);
      v_profile_path             := v_profile_path || '/outbound';
      v_file_handler             := UTL_FILE.fopen(v_profile_path, v_file_name, 'W', 32000);
      v_rec_header               := 'FLEX_VALUE_SET_NAME|FLEX_VALUE|DESCRIPTION|ENABLED_FLAG|';
      UTL_FILE.put_line(v_file_handler, v_rec_header);
      v_record_count             := 0;
      fnd_file.put_line(fnd_file.LOG, 'START OF EXTRACT           Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
      fnd_file.put_line(fnd_file.LOG, 'Extracting Into: ' || v_file_name);
      fnd_file.put_line(fnd_file.LOG, ' ');

      FOR vcur_flex_val IN flex_val_cur
      LOOP
         v_rec_txt                  :=
            REPLACE(REPLACE(   vcur_flex_val.flex_value_set_name
                            || '|'
                            || vcur_flex_val.flex_value
                            || '|'
                            || vcur_flex_val.description
                            || '|'
                            || vcur_flex_val.enabled_flag
                            || '|'
                           ,CHR(10)
                           ,' '
                           )
                   ,CHR(13)
                   ,' '
                   );
         UTL_FILE.put_line(v_file_handler, v_rec_txt);
         v_record_count             := v_record_count + 1;
      END LOOP;

      UTL_FILE.put_line(v_file_handler, 'Record_Count:' || v_record_count);
      fnd_file.put_line(fnd_file.LOG, 'Total Records Extracted: ' || v_record_count);
      fnd_file.put_line(fnd_file.LOG, 'END OF EXTRACT             Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
      UTL_FILE.fclose(v_file_handler);
   END extract_flex_values;

------------------------*************END extract_flex_values *************----------------
   PROCEDURE extract_lt_flex_values(
      p_period_name              IN       VARCHAR2
     ,p_set_of_books_id          IN       NUMBER
   )
   IS
      v_file_handler                UTL_FILE.file_type;

      CURSOR flex_val_cur
      IS
         SELECT   s.flex_value_set_name
                 ,v.flex_value
                 ,v.description
                 ,v.enabled_flag
             FROM fnd_flex_values_vl v
                 ,fnd_flex_value_sets s
            WHERE v.flex_value_set_id = s.flex_value_set_id AND s.flex_value_set_name IN('LT COMPANY', 'LT LOCATION', 'LT COST CENTRE', 'LT ACCOUNT')
         ORDER BY v.flex_value;

      v_file_name                   VARCHAR2(50);
      v_profile_path                VARCHAR2(100);
      v_record_count                NUMBER;
      v_rec_txt                     VARCHAR2(32000);
      v_rec_header                  VARCHAR2(32000);
   BEGIN
      v_file_name                := 'xxlt_dt_fnd_flex_values_vl_' || p_period_name || '.dat';
      fnd_profile.get('XXHBCDATA_TOP', v_profile_path);
      v_profile_path             := v_profile_path || '/outbound';
      v_file_handler             := UTL_FILE.fopen(v_profile_path, v_file_name, 'W', 32000);
      v_rec_header               := 'FLEX_VALUE_SET_NAME|FLEX_VALUE|DESCRIPTION|ENABLED_FLAG|';
      UTL_FILE.put_line(v_file_handler, v_rec_header);
      v_record_count             := 0;
      fnd_file.put_line(fnd_file.LOG, 'START OF EXTRACT           Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
      fnd_file.put_line(fnd_file.LOG, 'Extracting Into: ' || v_file_name);
      fnd_file.put_line(fnd_file.LOG, ' ');

      FOR vcur_flex_val IN flex_val_cur
      LOOP
         v_rec_txt                  :=
            REPLACE(REPLACE(   vcur_flex_val.flex_value_set_name
                            || '|'
                            || vcur_flex_val.flex_value
                            || '|'
                            || vcur_flex_val.description
                            || '|'
                            || vcur_flex_val.enabled_flag
                            || '|'
                           ,CHR(10)
                           ,' '
                           )
                   ,CHR(13)
                   ,' '
                   );
         UTL_FILE.put_line(v_file_handler, v_rec_txt);
         v_record_count             := v_record_count + 1;
      END LOOP;

      UTL_FILE.put_line(v_file_handler, 'Record_Count:' || v_record_count);
      fnd_file.put_line(fnd_file.LOG, 'Total Records Extracted: ' || v_record_count);
      fnd_file.put_line(fnd_file.LOG, 'END OF EXTRACT             Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
      UTL_FILE.fclose(v_file_handler);
   END extract_lt_flex_values;
------------------------*************END extract_flex_values *************----------------
END xxhbc_deloitte_audit_extract;
--- End of the package body
/