#!/bin/csh -fe
setenv NLS_LANG AMERICAN_AMERICA.WE8MSWIN1252
#setenv NLS_LANG AMERICAN_AMERICA.WE8ISO8859P15
set DBLOGIN=ops'$'cendba/uat@gcprmt
set FileName=$1
echo $FileName

sqlplus -s $DBLOGIN <<EOF
host echo "NLS_LANGVALUE :$NLS_LANG"
set serveroutput on size 1000000
SET LINESIZE 32000;
SET WRAP OFF
whenever sqlerror exit sql.SQLCODE

DECLARE
  w_errMsg	VARCHAR2(2000);
  FileName	VARCHAR2(40);
  w_errFlag	NUMBER := 0; -- 0 success; 1 error
  notOkFlag	EXCEPTION;
  o_err_flag	NUMBER := 0;
  o_err_msg	VARCHAR2(2000) := '';
  vB_ErrFlag	BOOLEAN;
  vV_ErrMsg	VARCHAR2(1000);
  
  w_input_dir   VARCHAR2(100);
  w_input_file  VARCHAR2(50);
  w_archv_file  VARCHAR2(50);
  w_output_dir  VARCHAR2(100);
  w_output_file VARCHAR2(50);

  L_exists      BOOLEAN;
  L_size        NUMBER;
  L_block_size  NUMBER;


  TYPE r_error IS RECORD(
    pol_num VARCHAR2(15),
    tran_dt VARCHAR2(10),
    inv_chr VARCHAR2(5),
    lineno  NUMBER,
    colpos  NUMBER);
  TYPE t_error IS TABLE OF r_error INDEX BY PLS_INTEGER;
  err_var  t_error;
  main_err t_error;
  j        NUMBER := 0;

  PROCEDURE check_wcpols_file(i_Infile_name  IN VARCHAR2,
                              i_Outfile_name IN VARCHAR2,
                              i_Infile_dir   IN VARCHAR2,
                              i_Outfile_dir  IN VARCHAR2,
                              o_err_flag     OUT BOOLEAN,
                              o_err_msg      OUT t_error) IS
    
    w_newline     VARCHAR2(2000);    
    w_ProcessLine VARCHAR2(240);
    w_RmngLine    VARCHAR2(200);
    w_lcnt        NUMBER := 0;
    w_strlgth     NUMBER;
    w_chrasci     NUMBER;
    w_NewPolNum   VARCHAR2(20);
    w_NewTranDt   CHAR(5);
    w_errm        VARCHAR2(200);
    w_spc_char    NUMBER := 0;
    w_rplc_char   VARCHAR2(5);
    w_input_dir   VARCHAR2(20);
    w_output_dir  VARCHAR2(20);
    w_archv_dir   VARCHAR2(20);

    L_exists      BOOLEAN;
    L_size        NUMBER;
    L_block_size  NUMBER;
  
    input_file  utl_file.file_type;
    output_file utl_file.file_type;
  
  BEGIN
    dbms_output.enable(null);
    w_input_file  := i_Infile_name;
    w_output_file := i_Outfile_name;
    w_input_dir   := i_Infile_dir;
    w_output_dir  := i_Outfile_dir;
    w_archv_dir   := 'WCPOLS_ARCHIVE_DIR';
    w_archv_file  := SUBSTR(W_input_file, 1, INSTR(W_input_file, '.') - 1) ||'_ARCHV.TXT';
    input_file    := utl_file.fopen(w_input_dir,  w_input_file, 'r', 32000);
    

    IF utl_file.is_open(input_file) THEN
      LOOP
        BEGIN
          utl_file.get_line(input_file, w_newline);          
          w_lcnt        := w_lcnt + 1;
          w_NewPolNum   := SUBSTR(w_newline, 6, 14);
          w_NewTranDt   := SUBSTR(w_newline, 37, 5);
          w_ProcessLine := CONVERT(SUBSTR(w_newline, 1, 220-2),'WE8MSWIN1252','UTF8');
          w_RmngLine    := SUBSTR(w_newline,111);
          
          
          SELECT length(w_ProcessLine) INTO w_strlgth FROM dual;
          w_spc_char := 0; 
          FOR i IN 1 .. w_strlgth LOOP                      
            IF ascii(substr(w_ProcessLine, i, 1)) IN (188,189,190) THEN 
              w_spc_char := 1;  
              IF ascii(substr(w_ProcessLine, i, 1))    = 188 THEN
                w_rplc_char := '1/4';
              ELSIF ascii(substr(w_ProcessLine, i, 1)) = 189 THEN
                w_rplc_char := '1/2';
              ELSIF ascii(substr(w_ProcessLine, i, 1)) = 190 THEN
                w_rplc_char := '3/4';
              END IF;
              
              DBMS_OUTPUT.PUT_LINE('Replaced '||substr(w_ProcessLine, i, 1)||' with '||w_rplc_char||' at line: '||w_lcnt);
            END IF;
          END LOOP;
          
          FOR i IN 1 .. w_strlgth LOOP                      
            IF ascii(substr(w_ProcessLine, i, 1)) BETWEEN 128 AND 255 AND ascii(substr(w_ProcessLine, i, 1)) NOT IN (188,189,190) 
	       AND SUBSTR(w_newline,1,3) <> '?' THEN               
                j := j + 1;
                err_var(j).pol_num := w_NewPolNum;
                err_var(j).tran_dt := w_NewTranDt;
                err_var(j).inv_chr := CHR(ASCII(SUBSTR(w_ProcessLine, i, 1)));
                err_var(j).lineno  := w_lcnt;
                err_var(j).colpos  := i;  
                DBMS_OUTPUT.PUT_LINE(err_var(j).lineno||'-'||chr(ascii(substr(w_ProcessLine, i, 1))));            
            END IF;
          END LOOP;          
          
        EXCEPTION
          WHEN no_data_found THEN
            EXIT;
        END;
      END LOOP; --main loop
     END IF; --InputFile is_open
    utl_file.fclose(input_file);
  END check_wcpols_file;

BEGIN  
  FileName      := '$FileName';
  w_input_dir   := 'WCPOLS_INPUT_DIR';
  w_output_dir  := 'WCPOLS_INPUT_DIR';  
  w_output_file := SUBSTR(W_input_file, 1, INSTR(W_input_file, '.') - 1) ||'_prcsd.TXT';  
  w_archv_file  := SUBSTR(FileName, 1, INSTR(FileName, '.') - 1) ||'_ARCHV.TXT';
  o_err_msg     := 'Invalid Characters in file: '||FileName||CHR(10);

  dbms_output.put_line('==== WCPOLS INVALID CHARACTERS INFORMATION ================================');
  dbms_output.put_line('Processing File: '||FileName); 
  
  
  check_wcpols_file(i_Infile_name  => filename,
                    i_Outfile_name => w_output_file,
                    i_Infile_dir   => w_input_dir,
                    i_Outfile_dir  => w_Output_dir,
                    o_err_flag     => vB_ErrFlag,
                    o_err_msg      => main_err);
  
  j := err_var.FIRST;
  IF err_var.EXISTS(j) THEN
    RAISE notOkFlag;
  END IF;  

  dbms_output.put_line('End Of Processing File: '||FileName);
  dbms_output.put_line('===========================================================================');

EXCEPTION
  WHEN notOkFlag THEN
    ROLLBACK;        
    o_err_flag := 1; -- There is an error    
    DBMS_OUTPUT.PUT_LINE('There are invalid characters.');
    WHILE (j IS NOT NULL) LOOP
      o_err_msg := o_err_msg || ' (' || j || ') ' || 'Policy: ' ||
                   err_var(j).pol_num || ' TranIssueDt: ' || err_var(j)
                  .tran_dt || ' InvalidChar ' || convert(err_var(j).inv_chr,'WE8MSWIN1252') || 
                  ' at LineNo: ' || err_var(j)
                  .lineno || ' ColumnPos: ' || err_var(j).colpos||CHR(10);      
      j := err_var.NEXT(j);
    END LOOP; 
    

    RAISE_APPLICATION_ERROR(-20001, '');  
END;
/
EOF

12742WC934720804        00000016081016251083705   0980          00010000060800000000          00000012116000000073671CITY, TOWNSHIP, Borough or County  all employees                                                      001370000100                                                        000000000000

12742WC934720804        00000016081016251083705   0980          00010000060800000000          00000012116000000073671CITY, TOWNSHIP, Borough or County â€" all employees                                                      001370000100                                                        000000000000