GL Account Extract Outbound Interface

Package Spec:

 

CREATE OR REPLACE PACKAGE APPS.XX_GL_ACCOUNT_EXTRACT_PKG

AS

   PROCEDURE LOG (p_msg IN VARCHAR2);

 

   PROCEDURE export_gl_account_details (

      p_errbuf    OUT   VARCHAR2,

      p_retcode   OUT   NUMBER

   );

END XX_GL_ACCOUNT_EXTRACT_PKG;

/

 

Package Body:

 

CREATE OR REPLACE PACKAGE BODY APPS.xx_gl_account_extract_pkg

AS

   g_conc_success   NUMBER := 0;

   g_conc_warning   NUMBER := 1;

   g_conc_error     NUMBER := 2;

 

   PROCEDURE LOG (p_msg IN VARCHAR2)

   IS

      l_msg   VARCHAR2 (32000)

                      := TO_CHAR (SYSTIMESTAMP, 'HH.MI.SS.FF') || '-'

                         || p_msg;

   BEGIN

      fnd_file.put_line (fnd_file.LOG, l_msg);

   END LOG;

 

   PROCEDURE export_gl_account_details (

      p_errbuf    OUT   VARCHAR2,

      p_retcode   OUT   NUMBER

   )

   AS

/*-- Cursor to get the Vendor Details --*/

      CURSOR c_gl_account_details (p_run_date DATE)

      IS

         SELECT gcc.segment1 company,

                (SELECT NVL (ffvv1.attribute1,

                             ffvv1.description

                            )

                   FROM fnd_flex_value_sets ffvs1, fnd_flex_values_vl ffvv1

                  WHERE ffvs1.flex_value_set_name = 'XX Company'

                    AND ffvv1.flex_value_set_id = ffvs1.flex_value_set_id

                    AND ffvv1.flex_value = gcc.segment1) company_description,

                gcc.segment2 plant,

                (SELECT NVL (ffvv2.attribute1, ffvv2.description)

                   FROM fnd_flex_value_sets ffvs2,

                        fnd_flex_values_vl ffvv2

                  WHERE ffvs2.flex_value_set_name = 'XX Plant'

                    AND ffvv2.flex_value_set_id = ffvs2.flex_value_set_id

                    AND ffvv2.flex_value = gcc.segment2) plant_name,

                gcc.segment3 department,

                (SELECT ffvv3.description

                   FROM fnd_flex_value_sets ffvs3,

                        fnd_flex_values_vl ffvv3

                  WHERE ffvs3.flex_value_set_name = 'XX Department'

                    AND ffvv3.flex_value_set_id = ffvs3.flex_value_set_id

                    AND ffvv3.flex_value = gcc.segment3) department_name,

                gcc.segment4 natural_account,

                (SELECT ffvv4.description

                   FROM fnd_flex_value_sets ffvs4,

                        fnd_flex_values_vl ffvv4

                  WHERE ffvs4.flex_value_set_name = 'XX Account'

                    AND ffvv4.flex_value_set_id = ffvs4.flex_value_set_id

                    AND ffvv4.flex_value = gcc.segment4)

                                                 natural_account_description,

                gcc.segment5 sub_account,

                (SELECT ffvv5.description

                   FROM fnd_flex_value_sets ffvs5,

                        fnd_flex_values_vl ffvv5

                  WHERE ffvs5.flex_value_set_name = 'XX Sub-Account'

                    AND ffvv5.flex_value_set_id = ffvs5.flex_value_set_id

                    AND ffvv5.flex_value = gcc.segment5)

                                                     sub_account_description,

                gcc.segment6 future_use

           FROM gl_code_combinations gcc, fnd_id_flex_structures ffs

          WHERE ffs.id_flex_code = 'GL#'

            AND ffs.id_flex_structure_code = 'XX_ACCT_FLEXFIELD'

            AND gcc.chart_of_accounts_id = ffs.id_flex_num

            AND NVL (gcc.end_date_active, SYSDATE) >= SYSDATE

            AND gcc.detail_posting_allowed_flag = 'Y'

            AND gcc.enabled_flag = 'Y';

 

/* -- Variable declarations --*/

      l_utl_file          UTL_FILE.file_type;

      l_current_time      fnd_lookup_values.meaning%TYPE   := '';

      l_run_date          DATE;

/* -- Output File Directory -- */

      l_outfile_path      VARCHAR2 (4000)             := 'COR360_OUT_DATA_DIR';

      l_file_name         VARCHAR2 (240);

      l_create_dir        VARCHAR2 (500);

      l_rec_count         NUMBER                           := 0;

      l_file_name2        VARCHAR2 (240);

      l_time_comp         VARCHAR2 (100)

                                      := TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS');

      l_sftp_request_id   NUMBER;

      l_sftpuser          VARCHAR (50); 

      l_sftppasswrd       VARCHAR (50); 

      l_remote_dir        VARCHAR (50);    

   BEGIN

      /*-------------In the main program ------------------*/

      LOG ('Directory File Path: ' || l_outfile_path);

      /*-------Set the Output File Name -----------*/

      l_file_name := 'AP_' || l_time_comp || '_account.txt';

      BEGIN

         l_utl_file :=

                     UTL_FILE.fopen (l_outfile_path, l_file_name, 'W', 32767);

         /*--Writing Column Headers---*/

         UTL_FILE.put (l_utl_file, 'COMPANY');

         UTL_FILE.put (l_utl_file, '|' || 'COMPANY_DESCRIPTION');

         UTL_FILE.put (l_utl_file, '|' || 'PLANT');

         UTL_FILE.put (l_utl_file, '|' || 'PLANT_NAME');

         UTL_FILE.put (l_utl_file, '|' || 'DEPARTMENT');

         UTL_FILE.put (l_utl_file, '|' || 'DEPARTMENT_NAME');

         UTL_FILE.put (l_utl_file, '|' || 'NATURAL_ACCOUNT');

         UTL_FILE.put (l_utl_file, '|' || 'NATURAL_ACCOUNT_DESCRIPTION');

         UTL_FILE.put (l_utl_file, '|' || 'SUB_ACCOUNT');

         UTL_FILE.put (l_utl_file, '|' || 'SUB_ACCOUNT_DESCRIPTION');

         UTL_FILE.put (l_utl_file, '|' || 'FUTURE_USE');

         UTL_FILE.new_line (l_utl_file);

 

         /*-------- Write records ---------*/

         FOR c_gl_account_rec IN c_gl_account_details (l_run_date)

         LOOP

            --UTL_FILE.put (l_utl_file, 'VENDOR');

            UTL_FILE.put (l_utl_file, c_gl_account_rec.company);

            UTL_FILE.put (l_utl_file,

                          '|' || c_gl_account_rec.company_description

                         );

            UTL_FILE.put (l_utl_file, '|' || c_gl_account_rec.plant);

            UTL_FILE.put (l_utl_file, '|' || c_gl_account_rec.plant_name);

            UTL_FILE.put (l_utl_file, '|' || c_gl_account_rec.department);

            UTL_FILE.put (l_utl_file, '|' || c_gl_account_rec.department_name);

            UTL_FILE.put (l_utl_file, '|' || c_gl_account_rec.natural_account);

            UTL_FILE.put (l_utl_file,

                          '|' || c_gl_account_rec.natural_account_description

                         );

            UTL_FILE.put (l_utl_file, '|' || c_gl_account_rec.sub_account);

            UTL_FILE.put (l_utl_file,

                          '|' || c_gl_account_rec.sub_account_description

                         );

            UTL_FILE.put (l_utl_file, '|' || c_gl_account_rec.future_use);

            UTL_FILE.new_line (l_utl_file);

            l_rec_count := l_rec_count + 1;

         END LOOP;

 

         UTL_FILE.fclose (l_utl_file);

         l_file_name2 := 'AP_' || l_time_comp || '_account.ctl';

         l_utl_file :=

                     UTL_FILE.fopen (l_outfile_path, l_file_name2, 'W', 32767);

         UTL_FILE.put (l_utl_file, 'ROWCOUNT');

         UTL_FILE.new_line (l_utl_file);

         UTL_FILE.put (l_utl_file, l_rec_count);

         UTL_FILE.fclose (l_utl_file);

         fnd_file.put_line (fnd_file.LOG, 'Submitting SFTP Program');

      EXCEPTION

         WHEN UTL_FILE.invalid_operation

         THEN

            p_retcode := g_conc_error;

            LOG

               (   'Error occured when attempting as trying to do invalid operation'

                || SQLERRM

                || ' '

                || SQLCODE

               );

            UTL_FILE.fclose_all;

         WHEN UTL_FILE.invalid_path

         THEN

            p_retcode := g_conc_error;

            LOG

               (   'Error occured when attempting to write a file into server: '

                || SQLERRM

                || ' '

                || SQLCODE

               );

            UTL_FILE.fclose_all;

         WHEN UTL_FILE.invalid_mode

         THEN

            p_retcode := g_conc_error;

            LOG ('Invalid mode: ' || SQLERRM || ' ' || SQLCODE);

            UTL_FILE.fclose_all;

         WHEN UTL_FILE.invalid_filehandle

         THEN

            p_retcode := g_conc_error;

            LOG ('invalid filehandle' || SQLERRM || ' ' || SQLCODE);

            UTL_FILE.fclose_all;

         WHEN UTL_FILE.read_error

         THEN

            p_retcode := g_conc_error;

            LOG ('read error' || SQLERRM || ' ' || SQLCODE);

            UTL_FILE.fclose_all;

         WHEN UTL_FILE.internal_error

         THEN

            p_retcode := g_conc_error;

            LOG ('internal error' || SQLERRM || ' ' || SQLCODE);

            UTL_FILE.fclose_all;

         WHEN OTHERS

         THEN

            p_retcode := g_conc_error;

            LOG ('other error' || SQLERRM || ' ' || SQLCODE);

            UTL_FILE.fclose_all;

      END;

   EXCEPTION

      WHEN OTHERS

      THEN

         p_retcode := g_conc_error;

         LOG ('Error occured in the main program: ' || SQLERRM || ' '

              || SQLCODE

             );

   END;

END xx_gl_account_extract_pkg;

/