Check Extract Outbound Interface

Package Spec: 

CREATE OR REPLACE PACKAGE APPS.XX_CHECKS_EXTRACT_PKG

AS

  PROCEDURE LOG(

      p_msg IN VARCHAR2);

  PROCEDURE export_checks_details(

      p_errbuf OUT VARCHAR2,

      p_retcode OUT NUMBER );

END ;

/

 

Package Body:

 

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

      p_errbuf OUT VARCHAR2,

      p_retcode OUT NUMBER)

  AS

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

    CURSOR c_check_details

    IS

      SELECT ai.attribute10 voucher_number,

        ac.check_number check_number,

        TO_CHAR (ac.check_date, 'YYYYMMDD') check_date,

        aip.invoice_id client_voucher_number,

        ac.currency_code currency_code,

        --AT                TO_CHAR (ai.terms_date, 'YYYYDDMM') pymnt_schd_date,

        TO_CHAR (ai.terms_date, 'YYYYMMDD') pymnt_schd_date,

        --ac.amount CHECK_AMOUNT,

        ai.invoice_amount check_amount,

        ac.check_id,

        ac.status_lookup_code

      FROM ap_invoices_all ai,

        ap_checks_all ac,

        ap_invoice_payments_all aip

      WHERE aip.invoice_id      = ai.invoice_id

      AND ai.attribute10       IS NOT NULL

      AND ac.check_id           = aip.check_id

      AND ac.status_lookup_code = 'NEGOTIABLE';

    -- and rownum <5;

    CURSOR c_check_details1

    IS

      SELECT ai.attribute10 voucher_number,

        ac.check_number check_number,

        TO_CHAR (ac.check_date, 'YYYYMMDD') check_date,

        aip.invoice_id client_voucher_number,

        ac.currency_code currency_code,

        TO_CHAR (ai.terms_date, 'YYYYDDMM') pymnt_schd_date,

        --ac.amount CHECK_AMOUNT,

        ai.invoice_amount check_amount,

        ac.check_id,

        ac.status_lookup_code

      FROM ap_invoices_all ai,

        ap_checks_all ac,

        ap_invoice_payments_all aip

      WHERE aip.invoice_id = ai.invoice_id

      AND ai.attribute10  IS NOT NULL

      AND ac.check_id      >

        (SELECT MAX (check_id) FROM apps.xx_cor360_checks_ext

        )

    AND ac.status_lookup_code = 'NEGOTIABLE';

    CURSOR c_check_details2

    IS

      SELECT ai.attribute10 voucher_number,

        NULL check_number,

        TO_CHAR (ac.check_date, 'YYYYMMDD') check_date,

        aip.invoice_id client_voucher_number,

        ac.currency_code currency_code,

        TO_CHAR (ai.terms_date, 'YYYYDDMM') pymnt_schd_date,

        ac.amount check_amount,

        ac.check_id,

        ac.status_lookup_code

      FROM ap_invoices_all ai,

        ap_checks_all ac,

        ap_invoice_payments_all aip,

        xxx_cor360_checks_ext scc

      WHERE aip.invoice_id       = ai.invoice_id

      AND ai.attribute10        IS NOT NULL

      AND ac.check_id            = scc.check_id

      AND ac.status_lookup_code  = 'VOIDED'

      AND scc.status_lookup_code = 'NEGOTIABLE'

      AND ac.last_update_date    > scc.extract_date;

    /* -- 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');

    v_count           NUMBER;

    v_count_id        NUMBER;

    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 || '_checkreference.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, 'VOUCHER_NUMBER');

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

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

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

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

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

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

      UTL_FILE.new_line (l_utl_file);

      -- for c_check_details_check in c_check_details (l_run_date) loop

      SELECT COUNT (*)

      INTO v_count

      FROM xx_cor360_checks_ext;

      --where check_id=c_check_details_check.check_id;

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

      --IF condition need here

      IF v_count = 0 THEN

        FOR c_check_rec IN c_check_details

        LOOP

          UTL_FILE.put (l_utl_file, c_check_rec.voucher_number);

          UTL_FILE.put (l_utl_file, '|' || c_check_rec.check_number);

          UTL_FILE.put (l_utl_file, '|' || c_check_rec.check_date);

          UTL_FILE.put (l_utl_file, '|' || c_check_rec.client_voucher_number );

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

          UTL_FILE.put (l_utl_file, '|' || c_check_rec.pymnt_schd_date);

          UTL_FILE.put (l_utl_file, '|' || c_check_rec.check_amount);

          INSERT

          INTO xx_cor360_checks_ext

            (

              check_id,

              status_lookup_code,

              extract_date

            )

            VALUES

            (

              c_check_rec.check_id,

              c_check_rec.status_lookup_code,

              SYSDATE

            );

          COMMIT;

          UTL_FILE.new_line (l_utl_file);

          l_rec_count := l_rec_count + 1;

        END LOOP;

      ELSE

        FOR c_check_rec1 IN c_check_details1

        LOOP

          UTL_FILE.put

          (

            l_utl_file, c_check_rec1.voucher_number

          )

          ;

          UTL_FILE.put (l_utl_file, '|' || c_check_rec1.check_number);

          UTL_FILE.put (l_utl_file, '|' || c_check_rec1.check_date);

          UTL_FILE.put (l_utl_file, '|' || c_check_rec1.client_voucher_number );

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

          UTL_FILE.put (l_utl_file, '|' || c_check_rec1.pymnt_schd_date);

          UTL_FILE.put (l_utl_file, '|' || c_check_rec1.check_amount);

          SELECT COUNT (*)

          INTO v_count_id

          FROM xx_cor360_checks_ext

          WHERE check_id = c_check_rec1.check_id;

          IF v_count_id  = 0 THEN

            INSERT

            INTO xx_cor360_checks_ext

              (

                check_id,

                status_lookup_code,

                extract_date

              )

              VALUES

              (

                c_check_rec1.check_id,

                c_check_rec1.status_lookup_code,

                SYSDATE

              );

          END IF;

          COMMIT;

          UTL_FILE.new_line (l_utl_file);

          l_rec_count := l_rec_count + 1;

        END LOOP;

        FOR c_check_rec2 IN c_check_details2

        LOOP

          UTL_FILE.put

          (

            l_utl_file, c_check_rec2.voucher_number

          )

          ;

          UTL_FILE.put (l_utl_file, '|' || c_check_rec2.check_number);

          UTL_FILE.put (l_utl_file, '|' || c_check_rec2.check_date);

          UTL_FILE.put (l_utl_file, '|' || c_check_rec2.client_voucher_number );

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

          UTL_FILE.put (l_utl_file, '|' || c_check_rec2.pymnt_schd_date);

          UTL_FILE.put (l_utl_file, '|' || c_check_rec2.check_amount);

          UPDATE xx_cor360_checks_ext

          SET status_lookup_code = 'VOIDED',

            extract_date         = SYSDATE

          WHERE check_id         = c_check_rec2.check_id;

          COMMIT;

          UTL_FILE.new_line (l_utl_file);

          l_rec_count := l_rec_count + 1;

        END LOOP;

      END IF;

      --  end loop;

      UTL_FILE.fclose (l_utl_file);

      l_file_name2 := 'AP_' || l_time_comp || '_checkreference.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 );

    --         IF l_file_name IS NOT NULL

    --         THEN

    -- END IF;

  END;

END xx_checks_extract_pkg;

/