WHENEVER SQLERROR EXIT
set pages 0
set feed off
set verify off
set trims on
set term off
set linesize 4000
set recsep off
REM ********************************************************************************
REM DML START
REM ********************************************************************************
REM Part1 creates files where there is one file for several batches
UNDEFINE fnamevar
UNDEFINE fnamevar2
UNDEFINE filpath
COLUMN filname NEW_VALUE fnamevar NOPRINT
COLUMN filname2 NEW_VALUE fnamevar2 NOPRINT
COLUMN file_path NEW_VALUE filpath NOPRINT
SELECT DISTINCT eba_filename filname
,‘CTL.’||eba_filename filname2
,eta_file_path file_path
FROM o_external_batches, o_extracts
WHERE eba_status = ‘READY’
AND eta_multiple_files = ‘N’
AND eba_bat_class = eta_bat_class
ORDER BY eba_filename;
spool &&filpath&&fnamevar…
COLUMN etr_id NOPRINT
COLUMN eba_id NOPRINT
SELECT TO_NUMBER(eba_id) eba_id
,‘0’ etr_id
,eba_text etr_text
FROM o_external_batches
WHERE eba_filename = ‘&&fnamevar’
AND eba_text <> ‘DUMMY’
UNION
SELECT TO_NUMBER(eba_id)
,etr_id
,etr_text
FROM o_external_transfer, o_external_batches
WHERE etr_eba_id = eba_id
AND eba_filename = ‘&&fnamevar’
AND etr_type <> ‘CTL’
ORDER BY 1,2;
spool &&filpath&&fnamevar2
SELECT etr_text
FROM o_external_transfer
,o_external_batches
WHERE etr_eba_id = eba_id
AND eba_filename = ‘&&fnamevar’
AND etr_type = ‘CTL’;
spool off
UPDATE o_external_batches
SET eba_status = ‘PROCESS’
WHERE eba_filename = ‘&&fnamevar’;
INSERT INTO O_LOGS(log_id,log_type,log_message,log_date)
SELECT o_log_seq.nextval
,eba_bat_class
,‘OBFX001S v1.01: Created extract data files &&fnamevar, &&fnamevar2’
,sysdate
FROM o_external_batches
WHERE eba_filename = ‘&&fnamevar’
AND rownum = 1;
COMMIT;
REM Part2 creates files where there is one file for each external batch
UNDEFINE idvar
UNDEFINE fnamevar
UNDEFINE filpath
COLUMN eba_id NEW_VALUE idvar NOPRINT
COLUMN filname NEW_VALUE fnamevar NOPRINT
COLUMN file_path NEW_VALUE filpath NOPRINT
SELECT eba_id
,eba_filename filname
,eta_file_path file_path
FROM o_external_batches, o_extracts
WHERE eba_status = ‘READY’
AND eta_multiple_files = ‘Y’
AND eba_bat_class = eta_bat_class
ORDER BY TO_NUMBER(eba_id) DESC;
spool &&filpath&&fnamevar…
COLUMN etr_id NOPRINT
SELECT eba_id
,‘0’ etr_id
,eba_text etr_text
FROM o_external_batches
WHERE eba_id = ‘&&idvar’
AND eba_text <> ‘DUMMY’
UNION
SELECT etr_eba_id
,etr_id
,etr_text
FROM o_external_transfer
WHERE etr_eba_id = ‘&&idvar’
AND etr_type <> ‘CTL’
ORDER BY 1,2;
spool off
UNDEFINE fnamevar2
COLUMN filname2 NEW_VALUE fnamevar2 NOPRINT
REM only print out control file on last batch file created
SELECT ‘CTL.’||eba_filename filname2
FROM o_external_batches eba, o_external_transfer etr
WHERE eba_status = ‘READY’
AND etr_eba_id = eba_id
AND etr_type = ‘CTL’
AND eba_id = ‘&&idvar’;
spool &&filpath&&fnamevar2
SELECT etr_text
FROM o_external_transfer
WHERE etr_eba_id = ‘&&idvar’
AND etr_type = ‘CTL’;
spool off
UPDATE o_external_batches
SET eba_status = ‘PROCESS’
WHERE eba_id = ‘&&idvar’;
INSERT INTO O_LOGS(log_id,log_type,log_message,log_date)
SELECT o_log_seq.nextval
,eba_bat_class
,‘OBFX001S v1.01: Created extract data files &&fnamevar &&fnamevar2’
,sysdate
FROM o_external_batches
WHERE eba_id = ‘&&idvar’;
COMMIT;
UNDEFINE pnamevar
COLUMN pname NEW_VALUE pnamevar NOPRINT
REM reruns where there are more files to be created
SELECT ‘OBFX001S’ pname
FROM o_external_batches, o_extracts
WHERE eba_status = ‘READY’
AND eba_bat_class = eta_bat_class;
@&&pnamevar
exit
REM ********************************************************************************
REM DML END
REM ********************************************************************************