Vendor Extract Outbound Interface

Package Spec: 

CREATE OR REPLACE PACKAGE APPS.xx_vendor_extract_pkg

AS

   PROCEDURE LOG (p_msg IN VARCHAR2); 

   PROCEDURE export_vendor_details (

      p_errbuf         OUT      VARCHAR2,

      p_retcode        OUT      NUMBER   

   );

END xx_vendor_extract_pkg;

/

Package Body: 

CREATE OR REPLACE PACKAGE BODY APPS.xx_vendor_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_vendor_details(

      p_errbuf OUT VARCHAR2,

      p_retcode OUT NUMBER)

  AS

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

    CURSOR c_vendor_details (p_run_date DATE)

    IS

      SELECT pv.segment1 vendor_number,

        SUBSTR (pv.vendor_name, 1, 50) vendor_name,

        pvs.vendor_site_code vendor_site_code,

        DECODE (pvs.address_line1, NULL, 'No Address Line1 in Oracle', pvs.address_line1 ) address_line1,

        pvs.address_line2,

        pvs.address_line3,

        pvs.city,

        DECODE (pvs.country, 'US', pvs.state, 'CA', pvs.province, NULL ) state,

        pvs.zip,

        pvs.country,

        pv.vendor_type_lookup_code vendor_type,

        flv.description org_id,

        att.NAME term,

        DECODE (pvs.payment_currency_code, NULL, gsob.currency_code, pvs.payment_currency_code ) currency_code,

        NULL poc_user_name,

        NULL company,

        NULL plant,

        NULL department,

        NULL natural_account,

        NULL sub_account,

        NULL future_use

      FROM po_vendors pv,

        po_vendor_sites_all pvs,

        ap_terms_vl att,

        fnd_lookup_values_vl flv,

        gl_sets_of_books gsob

      WHERE 1                                 = 1

      AND pv.enabled_flag                     = 'Y'

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

      AND pvs.vendor_id                       = pv.vendor_id

      AND NVL (pvs.inactive_date, SYSDATE)   >= SYSDATE

      AND pvs.terms_id                        = att.term_id(+)

      AND flv.lookup_type                     = 'COR360_ORGS'

      AND flv.lookup_code                     = pvs.org_id

      AND flv.enabled_flag                    = 'Y'

      AND flv.start_date_active               < SYSDATE

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

      AND gsob.set_of_books_id                = pv.set_of_books_id(+);

    /* -- 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_sftpuser        VARCHAR (50);

    l_sftppasswrd     VARCHAR (50);

    l_remote_dir      VARCHAR (50);

    l_sftp_request_id NUMBER;

  BEGIN

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

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

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

    l_file_name := 'AP_' || l_time_comp || '_vendor.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, 'VENDOR_NUMBER');

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      UTL_FILE.new_line (l_utl_file);

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

      FOR c_vendor_rec IN c_vendor_details (l_run_date)

      LOOP

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

        UTL_FILE.put (l_utl_file, c_vendor_rec.vendor_number);

        UTL_FILE.put (l_utl_file, '|' || c_vendor_rec.vendor_name);

        UTL_FILE.put (l_utl_file, '|' || c_vendor_rec.vendor_site_code);

        UTL_FILE.put (l_utl_file, '|' || c_vendor_rec.address_line1);

        UTL_FILE.put (l_utl_file, '|' || c_vendor_rec.address_line2);

        UTL_FILE.put (l_utl_file, '|' || c_vendor_rec.address_line3);

        UTL_FILE.put (l_utl_file, '|' || c_vendor_rec.city);

        UTL_FILE.put (l_utl_file, '|' || c_vendor_rec.state);

        UTL_FILE.put (l_utl_file, '|' || c_vendor_rec.zip);

        UTL_FILE.put (l_utl_file, '|' || c_vendor_rec.country);

        UTL_FILE.put (l_utl_file, '|' || c_vendor_rec.vendor_type);

        UTL_FILE.put (l_utl_file, '|' || c_vendor_rec.org_id);

        UTL_FILE.put (l_utl_file, '|' || c_vendor_rec.term);

        UTL_FILE.put (l_utl_file, '|' || c_vendor_rec.currency_code);

        UTL_FILE.put (l_utl_file, '|' || c_vendor_rec.poc_user_name);

        UTL_FILE.put (l_utl_file, '|' || c_vendor_rec.company);

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

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

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

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

        UTL_FILE.put (l_utl_file, '|' || c_vendor_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 || '_vendor.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');

      BEGIN

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

/