Journals Monthly Extract

CREATE OR REPLACE PACKAGE APPS.journals_mon_ext
IS
/*************************************************************************
*
* Package: journals_mon_ext
*
*
* Purpose: To implement data extraction process for journals monthly Audit requirements

*
* REF : MD.070 -APPLICATION EXTENSION TECHNICAL DESIGN
*
* $Author:   xibmo61
* $Date:
* $Modified: $APR-18-2011
* $Revision: 1.0
*
* Modification History:
*
* Date           Author                Version           Description
* -----------------------------------------------------------------------
*NOV-19-2008    Chandra Sekhar kadali  1.0          Initial Development
************************************************************************/
 
  PROCEDURE extract_lt_gl_je_lines(
   errbuf                     OUT      VARCHAR2
     ,retcode                    OUT      NUMBER,
      p_period_name              IN       VARCHAR2

   );

  PROCEDURE extract_lt_gl_je_headers(
   errbuf                     OUT      VARCHAR2
     ,retcode                    OUT      NUMBER,
      p_period_name              IN       VARCHAR2

   );
   PROCEDURE extract_lt_gl_je_batches( errbuf                     OUT      VARCHAR2
     ,retcode                    OUT      NUMBER,
      p_period_name              IN       VARCHAR2

   );


  
END;
/
CREATE OR REPLACE PACKAGE BODY APPS.lt_journals_mon_ext
AS
   PROCEDURE extract_lt_gl_je_lines(
      errbuf                     OUT      VARCHAR2
     ,retcode                    OUT      NUMBER
     ,p_period_name              IN       VARCHAR2
   )
   IS
      v_file_handler                UTL_FILE.file_type;

      CURSOR lt_gl_je_l_cur
      IS
         SELECT gl.*
           FROM gl_je_lines gl
               ,gl_code_combinations gc
          WHERE gl.period_name = p_period_name AND gl.set_of_books_id = 2 AND gl.code_combination_id = gc.code_combination_id AND gc.segment1 = '21';

      -- To Include LT ALSO
      l_errbuf                      VARCHAR2(3000);
      l_retcode                     NUMBER;
      v_file_name                   VARCHAR2(50);
      v_profile_path                VARCHAR2(100);
      v_rec_txt                     VARCHAR2(32000);
      v_rec_header                  VARCHAR2(32000);
      v_record_count                NUMBER;
      l_line_dr                     NUMBER;
      l_line_cr                     NUMBER;
      l_journal_debit               NUMBER;
      l_journal_credit              NUMBER;
      l_total                       NUMBER;
      l_journal_debit_y             NUMBER;
      l_journal_credit_y            NUMBER;
      l_total_y                     NUMBER;
      l_par                         VARCHAR2(100);
      l_full                        VARCHAR2(100);
   BEGIN
      v_file_name                := 'xxlt_dt_gl_je_lines_' || REPLACE(p_period_name, '-', '') || '.dat';

      SELECT 'P01-' || SUBSTR(p_period_name, 5, 2)
        INTO l_par
        FROM DUAL;

      SELECT SUM(accounted_dr)
            ,SUM(accounted_cr)
        INTO l_line_dr
            ,l_line_cr
        FROM gl_je_lines gl
            ,gl_code_combinations gc
       WHERE gl.period_name = p_period_name AND gl.set_of_books_id = 2 AND gl.code_combination_id = gc.code_combination_id AND gc.segment1 = '21';

      SELECT   SUM(a.accounted_dr) journal_debit
              ,SUM(a.accounted_cr) journal_credit
              ,(SUM(a.accounted_dr) - SUM(a.accounted_cr)) total
          INTO l_journal_debit
              ,l_journal_credit
              ,l_total
          FROM gl.gl_je_lines a
              ,gl.gl_code_combinations b
              ,gl.gl_je_headers d
         WHERE a.period_name = p_period_name                                                              --IN('P09-09', 'P10-09', 'P11-09', 'P12-09')
           AND a.set_of_books_id = 2
           AND a.code_combination_id = b.code_combination_id
           AND b.segment1 = '21'
           AND a.status = 'P'
           AND a.je_header_id = d.je_header_id
           AND NOT(d.NAME LIKE 'MV%')
           AND NOT(d.NAME LIKE 'Move/Merge%')
      GROUP BY a.period_name;

      SELECT SUM(a.accounted_dr) journal_debit
            ,SUM(a.accounted_cr) journal_credit
            ,(SUM(a.accounted_dr) - SUM(a.accounted_cr)) total
        INTO l_journal_debit_y
            ,l_journal_credit_y
            ,l_total_y
        FROM gl.gl_je_lines a
            ,gl.gl_code_combinations b
            ,gl.gl_je_headers d
       WHERE a.period_name BETWEEN l_par AND p_period_name                                                --IN('P09-09', 'P10-09', 'P11-09', 'P12-09')
         AND a.set_of_books_id = 2
         AND a.code_combination_id = b.code_combination_id
         AND b.segment1 = '21'
         AND a.status = 'P'
         AND a.je_header_id = d.je_header_id
         AND NOT(d.NAME LIKE 'MV%')
         AND NOT(d.NAME LIKE 'Move/Merge%');

      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_lt_gl_je_l IN lt_gl_je_l_cur
      LOOP
         v_rec_txt                  :=
            REPLACE(REPLACE(   vcur_lt_gl_je_l.je_header_id
                            || '|'
                            || vcur_lt_gl_je_l.je_line_num
                            || '|'
                            || TO_CHAR(vcur_lt_gl_je_l.last_update_date, 'DD-MON-YY HH:MI:SS PM')
                            || '|'
                            || vcur_lt_gl_je_l.last_updated_by
                            || '|'
                            || vcur_lt_gl_je_l.set_of_books_id
                            || '|'
                            || vcur_lt_gl_je_l.code_combination_id
                            || '|'
                            || vcur_lt_gl_je_l.period_name
                            || '|'
                            || vcur_lt_gl_je_l.effective_date
                            || '|'
                            || vcur_lt_gl_je_l.status
                            || '|'
                            || TO_CHAR(vcur_lt_gl_je_l.creation_date, 'DD-MON-YY HH:MI:SS PM')
                            || '|'
                            || vcur_lt_gl_je_l.created_by
                            || '|'
                            || vcur_lt_gl_je_l.last_update_login
                            || '|'
                            || vcur_lt_gl_je_l.accounted_dr
                            || '|'
                            || vcur_lt_gl_je_l.accounted_cr
                            || '|'
                            || vcur_lt_gl_je_l.description
                            || '|'
                           ,CHR(10)
                           ,' '
                           )
                   ,CHR(13)
                   ,' '
                   );
         UTL_FILE.put_line(v_file_handler, v_rec_txt);
--vcur_lt_gl_je_l.je_header_id||'|'||vcur_lt_gl_je_l.je_line_num||'|'||TO_CHAR(vcur_lt_gl_je_l.last_update_date,'DD-MON-YY HH:MI:SS PM')||'|'||vcur_lt_gl_je_l.last_updated_by||'|'||vcur_lt_gl_je_l.set_of_books_id||'|'||vcur_lt_gl_je_l.code_combination_id||'|'||vcur_lt_gl_je_l.period_name||'|'||vcur_lt_gl_je_l.effective_date||'|'||vcur_lt_gl_je_l.status||'|'||TO_CHAR(vcur_lt_gl_je_l.creation_date, 'DD-MON-YY HH:MI:SS PM')||'|'||vcur_lt_gl_je_l.created_by||'|'||vcur_lt_gl_je_l.last_update_login||'|'||vcur_lt_gl_je_l.accounted_dr||'|'||vcur_lt_gl_je_l.accounted_cr||'|'||vcur_lt_gl_je_l.description||'|');
         v_record_count             := v_record_count + 1;
      END LOOP;

      fnd_file.put_line(fnd_file.output, '------------------------------***********************************----------------------------------------');
      fnd_file.put_line(fnd_file.output, '---------------------------------------------------------------------------------------------------------');
      fnd_file.put_line(fnd_file.output, '                                        Lord and Taylor         ');
      fnd_file.put_line(fnd_file.output
                       , 'Program: LT Journal Extract        Monthly IRS Extract - GL Journals            Rundate :' || TRUNC(SYSDATE));
      fnd_file.put_line(fnd_file.output, '                                       For  Fiscal Month:' || p_period_name);
      fnd_file.put_line(fnd_file.output
                       ,'                                                                                                           ');
      fnd_file.put_line(fnd_file.output
                       ,'                                                                                                           ');
      fnd_file.put_line(fnd_file.output, '                                   TOTAL DEBITS                TOTAL CREDITS ');
      fnd_file.put_line(fnd_file.output
                       ,    'Current Period                     $'
                         || TRIM(TO_CHAR(l_journal_debit, '999,999,999,999,999,999.99'))
                         || '           $'
                         || TRIM(TO_CHAR(l_journal_credit, '999,999,999,999,999,999.99'))
                       );
      fnd_file.put_line(fnd_file.output
                       ,'                                                                                                           ');
      fnd_file.put_line(fnd_file.output
                       ,    'Year To Date                       $'
                         || TRIM(TO_CHAR(l_journal_debit_y, '999,999,999,999,999,999.99'))
                         || '           $'
                         || TRIM(TO_CHAR(l_journal_credit_y, '999,999,999,999,999,999.99'))
                       );
      fnd_file.put_line(fnd_file.output
                       ,'                                                                                                           ');
    fnd_file.put_line(fnd_file.output
                       ,'                                                                                                           ');
     
      fnd_file.put_line(fnd_file.output
                       ,'Approved By:_________________________________________________                    Date:________________     ');
      fnd_file.put_line(fnd_file.output
                       ,'                                                                                                           ');
      fnd_file.put_line(fnd_file.output
                       ,'                                                                                                           ');
      fnd_file.put_line(fnd_file.output
                       ,'                                                                                                           ');
      fnd_file.put_line(fnd_file.output, '---------------------------------------------------------------------------------------------------------');
      fnd_file.put_line(fnd_file.output, '------------------------------***********************************----------------------------------------');
      /* fnd_file.put_line(fnd_file.output, '----------------*************************----------------');
       fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
       fnd_file.put_line(fnd_file.output, '         Summary Report For GL_JE_LINES');
       fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
       fnd_file.put_line(fnd_file.output, 'File Name     : ' || v_file_name);
       fnd_file.put_line(fnd_file.output, 'Total Credits : ' || l_line_cr);
       fnd_file.put_line(fnd_file.output, 'Total Debits  : ' || l_line_dr);
       fnd_file.put_line(fnd_file.output, 'Total Records Extracted: ' || v_record_count);
       fnd_file.put_line(fnd_file.output, 'Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
       fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
       fnd_file.put_line(fnd_file.output, '----------------*************************----------------'); */
      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);
      extract_lt_gl_je_headers(l_errbuf, l_retcode, p_period_name);
   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_lt_gl_je_lines;

   PROCEDURE extract_lt_gl_je_headers(
      errbuf                     OUT      VARCHAR2
     ,retcode                    OUT      NUMBER
     ,p_period_name              IN       VARCHAR2
   )
   IS
      v_file_handler                UTL_FILE.file_type;

      CURSOR gl_je_h_cur
      IS
         SELECT DISTINCT js.user_je_source_name
                        ,js.description source_description
                        ,jh.*
                    FROM gl_je_headers jh
                        ,gl_je_sources js
                        ,gl_je_lines jl
                        ,gl_code_combinations jb
                   WHERE jh.period_name = p_period_name
                     AND jh.je_source = js.je_source_name
                     AND jh.set_of_books_id = 2
                     AND jl.code_combination_id = jb.code_combination_id
                     AND jl.je_header_id = jh.je_header_id
                     AND jl.period_name = jh.period_name
                     AND jb.segment1 = '21';

      -- 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);
      l_jh_dr                       NUMBER;
      l_jh_cr                       NUMBER;
      l_errbuf                      VARCHAR2(3000);
      l_retcode                     NUMBER;
   BEGIN
      v_file_name                := 'xxlt_dt_gl_je_headers_' || REPLACE(p_period_name, '-', '') || '.dat';

      SELECT SUM(jh.running_total_dr)
            ,SUM(jh.running_total_cr)
        INTO l_jh_dr
            ,l_jh_cr
        FROM gl_je_headers jh
            ,gl_je_sources js
            ,gl_je_lines jl
            ,gl_code_combinations jb
       WHERE jh.period_name = p_period_name
         AND jh.je_source = js.je_source_name
         AND jh.set_of_books_id = 2
         AND jl.code_combination_id = jb.code_combination_id
         AND jl.je_header_id = jh.je_header_id
         AND jl.period_name = jh.period_name
         AND jb.segment1 = '21';

      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;

/*      fnd_file.put_line(fnd_file.output, '----------------*************************----------------');
      fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
      fnd_file.put_line(fnd_file.output, '         Summary Report For GL_JE_HEADERS');
      fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
      fnd_file.put_line(fnd_file.output, 'File Name     : ' || v_file_name);
      fnd_file.put_line(fnd_file.output, 'Total Credits : ' || l_jh_dr);
      fnd_file.put_line(fnd_file.output, 'Total Debits  : ' || l_jh_cr);
      fnd_file.put_line(fnd_file.output, 'Total Records Extracted: ' || v_record_count);
      fnd_file.put_line(fnd_file.output, 'Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
      fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
      fnd_file.put_line(fnd_file.output, '----------------*************************----------------');*/
      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);
      extract_lt_gl_je_batches(l_errbuf, l_retcode, p_period_name);
   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_lt_gl_je_headers;

   PROCEDURE extract_lt_gl_je_batches(
      errbuf                     OUT      VARCHAR2
     ,retcode                    OUT      NUMBER
     ,p_period_name              IN       VARCHAR2
   )
   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
               ,gl_je_lines jl
               ,gl_je_headers jh
               ,gl_code_combinations jc
          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 = 2
            AND jl.je_header_id = jh.je_header_id
            AND jh.je_batch_id = jb.je_batch_id
            AND jl.set_of_books_id = jh.set_of_books_id
            AND jc.segment1 = '21'
            AND jl.period_name = jh.period_name
            AND jc.code_combination_id = jl.code_combination_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);
      l_bat_dr                      NUMBER;
      l_bat_cr                      NUMBER;
   BEGIN
      v_file_name                := 'xxlt_dt_gl_je_batches_' || REPLACE(p_period_name, '-', '') || '.dat';

      SELECT SUM(jb.running_total_dr)
            ,SUM(jb.running_total_cr)
        INTO l_bat_dr
            ,l_bat_cr
        FROM gl_je_batches jb
            ,fnd_user fu1
            ,fnd_user fu2
            ,gl_je_lines jl
            ,gl_je_headers jh
            ,gl_code_combinations jc
       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 = 2
         AND jl.je_header_id = jh.je_header_id
         AND jh.je_batch_id = jb.je_batch_id
         AND jl.set_of_books_id = jh.set_of_books_id
         AND jc.segment1 = '21'
         AND jl.period_name = jh.period_name
         AND jc.code_combination_id = jl.code_combination_id;

      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;

      /*fnd_file.put_line(fnd_file.output, '----------------*************************----------------');
      fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
      fnd_file.put_line(fnd_file.output, '         Summary Report For GL_JE_BATCHES');
      fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
      fnd_file.put_line(fnd_file.output, 'File Name     : ' || v_file_name);
      fnd_file.put_line(fnd_file.output, 'Total Credits : ' || l_bat_cr);
      fnd_file.put_line(fnd_file.output, 'Total Debits  : ' || l_bat_dr);
      fnd_file.put_line(fnd_file.output, 'Total Records Extracted: ' || v_record_count);
      fnd_file.put_line(fnd_file.output, 'Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
      fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
      fnd_file.put_line(fnd_file.output, '----------------*************************----------------');*/
      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);
   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_lt_gl_je_batches;
END journals_mon_ext;
--- End of the package body
/
exit
/

Parameter File Syntax - .prm

.prm files will be used to dump the table data into a .dmp file.
We can call it from shell scripts and we can pass parameters to .prm file from Shell Script.

Syntax:

file=$XXHBCDATA_TOP/outbound/xxhbc_spi_ap_batches_all.dmp
tables=(AP.AP_BATCHES_ALL)
query="where org_id=22"
rows=y
indexes=n
grants=n
constraints=n
statistics=none
triggers=n
direct=n
compress=n
log=$APPLCSF/$APPLLOG/exp_ap_batches_all.log

DRV Script Creation

 DRV --> Driver Script

DRV files are used to execute the Shell Scripts from Main Frame / ESP Scheduled Programs.
Instead of executing the concurrent programs in Concurrent Requests Window, we use drv scripts
to execute intended shell scripts.

#!/bin/ksh
#-------------------------------------------------------------------------------
#
# FILE: aol@2804.drv
#
# DESCRIPTION:  script to get ECOVA files from pgp server to inbound
#
#
# Revision History
#
# Date       Revision By       Description
# ---------- ----------------  ----------------------------------------------
# 2011-10-11 Chandra Sekhar K  created
#
#-------------------------------------------------------------------------------
#
# $Revision:   1.0  $
# $Workfile:   aol@advatageiq.drv $
# $Log:   //ccpvcs01/pvcsvm/bstfs/oracle_financials/ESP_scripts/aol@5015.drv  $
#
#
#-------------------------------------------------------------------------------
#
[[ $DEBUGESP != "no" ]] && set -vx
#
. ESPStep0.sh
. ESPprocs.sh


ESPecho "STEP 0 COMPLETED"

#----------------------------------------------------------------------
#
# step 10 - Receive file from pgp server
#
#----------------------------------------------------------------------

STEPexec 10 xxhbc_get_ecova_file.sh ccpgp01

#----------------------------------------------------------------------
#
# end of job
#
#----------------------------------------------------------------------

ESPupdate 999
--- End of Drv File .



Shell Script :

#!/usr/bin/ksh
#################################################################################################
##    Name        : xxhbc_get_ecova_file.sh                                                                      ##        
##    Description : This script will used to get AP008*_42.dat inbound                            ##
##                  files from pgp server                                                                                 ##
##    Parameters  : ccpgp01                                                                                            ##
##    Source Server : ccpgp01                                                                                        ##
##    Source Path : /pgp/csftp6e/adviq/                                                                           ##
##    Destination : $XXHBCDATA_TOP/inbound/ap                                                      ##
##    Created By : Chandra Sekhar Kadali                                                                      ##
##    Created On : 11-OCT-2011                                                                                   ##
##                                                                                                                                  ##
##      Change History                                                                                                     ##
##      --------------                                                                                                        ##
##    Modified BY                       Date            Version      Comments                               ##
##    --------------------              ---------       --------     ----------------------             ##
##    Chandra Sekhar Kadali             11-OCT-2011     1.0          Initial Creation               ##
#################################################################################################
MACHINE=$1

echo "Machine Name"
echo $MACHINE


LUSER=$(grep $MACHINE $OL_HOME/filepw | awk '{print $4}')

echo "User Name"
echo $LUSER

LPWD=$(grep $MACHINE $OL_HOME/filepw | awk '{print $6}')

echo "Password"
echo $LPWD

echo "Moving Files"

##  get files from pgp server
ftp -niv $MACHINE <user $LUSER $LPWD
cd ..
lcd $XXHBCDATA_TOP/inbound/ap
mget /pgp/csftp6e/adviq/financein/AP008*_42.dat 
 quit
FTP

echo "Apply Execution Permissions"

chmod 654 $XXHBCDATA_TOP/inbound/ap/AP008*_42.dat

## Moving files to Inbound Directory
##for file in $XXHBCDATA_TOP/inbound/ap/AP008*_42.dat
##do
##echo "Moving Files"

## mv $file $XXHBCDATA_TOP/inbound/ap/$file
## chmod 644 $XXHBCDATA_TOP/inbound/ap/$file
##done

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
/