Outbound Interface UTL_FILE Process

CREATE OR REPLACE PROCEDURE XXCUS.XX_DBTIER_STAGING(ERRBUFF    OUT VARCHAR2,
                                              RETCODE    OUT NUMBER,
                                              P_DIR      IN VARCHAR2,
                                              P_FILE     IN VARCHAR2,
                                              P_SEQUENCE IN NUMBER) IS
  --+==============================================================================+--
  --|                                       |--
  --+==============================================================================+--
  /*--------------------------------------------------------------------------------*\
  -- Module   : Cash Management                                                         --
                                                                        --
  --                                                                             --
  -- Purpose  : To migrate data from DB tier. in to a Staging File
  -- Author   : Madhu Dhare                                                        --
  --+==============================================================================+--
  --|                                CHANGE HISTORY                                |--
  --+==============================================================================+--
  -- Version  C/R No.          When      Who     What                       --
  -- -------  -------             -----           ---     ----                      --
  -- 1.0                   28-Jan-2008  184755    Intial version.           --
  --                                                                                --
  \*--------------------------------------------------------------------------------*/

  L_FILEHANDLE UTL_FILE.FILE_TYPE;
  L_LINE       VARCHAR2(32000);
  READ_ERROR EXCEPTION;
  L_DIRECTORY     VARCHAR2(100) := P_DIR;
  L_FILENAME      VARCHAR2(100) := P_FILE;
  L_VAR1_BANK     VARCHAR2(255);
  L_VAR2_BANKBR   VARCHAR2(255);
  L_VAR3_BANKAC   VARCHAR2(255);
  L_VAR_STMT_DATE VARCHAR2(100);
BEGIN
  -- Call the function
  IF UTL_FILE.IS_OPEN(L_FILEHANDLE) THEN
    UTL_FILE.FCLOSE_ALL;
  END IF;
  L_FILEHANDLE := UTL_FILE.FOPEN(L_DIRECTORY, L_FILENAME, 'R');
  -- dbms_output.put_line (1);

  BEGIN
    DELETE FROM XX_BANK_LOADER_STAGING_TB WHERE to_number(attribute14) = P_SEQUENCE;
    COMMIT;
    SELECT CHARACTER2,
           CHARACTER3,
           CHARACTER4,
           TO_CHAR(TO_DATE(CHARACTER5, 'yyyy/mm/dd'), 'DD-MON-YYYY')
      INTO L_VAR1_BANK, L_VAR2_BANKBR, L_VAR3_BANKAC, L_VAR_STMT_DATE
      FROM QA_RESULTS
     WHERE PLAN_ID =
           (SELECT PLAN_ID
              FROM QA_PLANS
             WHERE NAME LIKE 'XX_BANK_STATEMENT_LOADER') AND
           SEQUENCE7 = P_SEQUENCE;
    INSERT INTO XX_BANK_LOADER_STAGING_TB
    VALUES
      ('Bank Name',
       L_VAR1_BANK,
       'Branch Name',
       L_VAR2_BANKBR,
       'Date',
       L_VAR_STMT_DATE,
       'Account Number',
       L_VAR3_BANKAC,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       P_SEQUENCE,
       NULL);
    COMMIT;
    --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'First Line Loaded from Collection Plan');
  END;
  LOOP

    BEGIN
      UTL_FILE.GET_LINE(L_FILEHANDLE, L_LINE);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;

    BEGIN
      INSERT INTO XX_BANK_LOADER_STAGING_TB
      VALUES
        (REPLACE(REPLACE(XX_GET_TOKEN(XX_DELIMITER(L_LINE, ',', '"'), 1, ','),
                         '^',
                         '"'),
                 '|',
                 ','), --get_token(V_LINE,1,','),
         REPLACE(REPLACE(XX_GET_TOKEN(XX_DELIMITER(L_LINE, ',', '"'), 2, ','),
                         '^',
                         '"'),
                 '|',
                 ','), --get_token(V_LINE,2,','),
         REPLACE(REPLACE(XX_GET_TOKEN(XX_DELIMITER(L_LINE, ',', '"'), 3, ','),
                         '^',
                         '"'),
                 '|',
                 ','), --get_token(V_LINE,3,','),
         REPLACE(REPLACE(XX_GET_TOKEN(XX_DELIMITER(L_LINE, ',', '"'), 4, ','),
                         '^',
                         '"'),
                 '|',
                 ','), --get_token(V_LINE,4,','),
         REPLACE(REPLACE(XX_GET_TOKEN(XX_DELIMITER(L_LINE, ',', '"'), 5, ','),
                         '^',
                         '"'),
                 '|',
                 ','), --get_token(V_LINE,5,','),
         REPLACE(REPLACE(XX_GET_TOKEN(XX_DELIMITER(L_LINE, ',', '"'), 6, ','),
                         '^',
                         '"'),
                 '|',
                 ','), --get_token(V_LINE,6,','),
         REPLACE(REPLACE(XX_GET_TOKEN(XX_DELIMITER(L_LINE, ',', '"'), 7, ','),
                         '^',
                         '"'),
                 '|',
                 ','), --get_token(V_LINE,7,','),
         REPLACE(REPLACE(XX_GET_TOKEN(XX_DELIMITER(L_LINE, ',', '"'), 8, ','),
                         '^',
                         '"'),
                 '|',
                 ','), --get_token(V_LINE,8,','),
         REPLACE(REPLACE(XX_GET_TOKEN(XX_DELIMITER(L_LINE, ',', '"'), 9, ','),
                         '^',
                         '"'),
                 '|',
                 ','), --get_token(V_LINE,9,','),
         REPLACE(REPLACE(XX_GET_TOKEN(XX_DELIMITER(L_LINE, ',', '"'), 10, ','),
                         '^',
                         '"'),
                 '|',
                 ','), --get_token(V_LINE,10,','),
         REPLACE(REPLACE(XX_GET_TOKEN(XX_DELIMITER(L_LINE, ',', '"'), 11, ','),
                         '^',
                         '"'),
                 '|',
                 ','), --get_token(V_LINE,11,','),
         REPLACE(REPLACE(XX_GET_TOKEN(XX_DELIMITER(L_LINE, ',', '"'), 12, ','),
                         '^',
                         '"'),
                 '|',
                 ','), --get_token(V_LINE,12,','),
         REPLACE(REPLACE(XX_GET_TOKEN(XX_DELIMITER(L_LINE, ',', '"'), 13, ','),
                         '^',
                         '"'),
                 '|',
                 ','), --get_token(V_LINE,13,','),
         P_SEQUENCE,
         REPLACE(REPLACE(XX_GET_TOKEN(XX_DELIMITER(L_LINE, ',', '"'), 15, ','),
                         '^',
                         '"'),
                 '|',
                 ',')
         -- get_token(V_LINE,14,',')
         );
      --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Lines are Loaded into the Staging Tables');
      ERRBUFF := ' ';
      RETCODE := 0;
    EXCEPTION
      WHEN OTHERS THEN
        FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                          'Line Not Loaded in to Staging Table due to' ||
                          SQLERRM);
        DBMS_OUTPUT.PUT_LINE('123' || SQLERRM);
        ERRBUFF := SQLERRM;
        RETCODE := 2;
    END;

    COMMIT;
  END LOOP;
  UTL_FILE.FREMOVE(LOCATION => P_DIR, FILENAME => P_FILE);
EXCEPTION
  WHEN READ_ERROR THEN
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Unable to Read');
    UTL_FILE.FREMOVE(LOCATION => P_DIR, FILENAME => P_FILE);
    DBMS_OUTPUT.PUT_LINE('Unable to Read');
    ERRBUFF := SQLERRM;
    RETCODE := 2;
  WHEN OTHERS THEN
    --dbms_output.put_line ('1234');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, SQLERRM);
    UTL_FILE.FREMOVE(LOCATION => P_DIR, FILENAME => P_FILE);
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    ERRBUFF := SQLERRM;
    RETCODE := 2;
END;
/