SET PATH *LIBL ; 

CREATE OR REPLACE PROCEDURE CISTOOLS.REVIEW_JOURNAL ( 
	IN FILE_NAME VARCHAR(128) , 
	IN JOURNAL_NAME VARCHAR(128) , 
	IN FILE_LIBRARY VARCHAR(128) DEFAULT  NULL  , 
	IN JOURNAL_LIBRARY VARCHAR(128) DEFAULT  'MPXJRN'  , 
	IN FROM_DATE DATE DEFAULT  NULL  , 
	IN COLUMN_LIST VARCHAR(1024) DEFAULT  'ALL'  , 
	IN WHERE_CLAUSE VARCHAR(2056) DEFAULT  NULL  ) 
	DYNAMIC RESULT SETS 1 
	LANGUAGE SQL 
	SPECIFIC CISTOOLS.REVIEW_JOURNAL 
	NOT DETERMINISTIC 
	MODIFIES SQL DATA 
	CALLED ON NULL INPUT 
	PROGRAM TYPE SUB 
	SET OPTION  ALWBLK = *ALLREAD , 
	ALWCPYDTA = *OPTIMIZE , 
	COMMIT = *NONE , 
	DBGVIEW = *SOURCE , 
	DECRESULT = (31, 31, 00) , 
	DYNDFTCOL = *NO , 
	DYNUSRPRF = *USER , 
	SRTSEQ = *HEX   
	BEGIN 
DECLARE START_POSITION , COLUMN_BUFFER INTEGER ; 
DECLARE PAD VARCHAR ( 3 ) DEFAULT ', ' ; 
DECLARE JOURNAL_CUR CURSOR WITH RETURN TO CALLER FOR SQL_STMT ; 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
BEGIN 
END ; 
IF FROM_DATE IS NULL THEN 
SET FROM_DATE = CURRENT_DATE - 7 DAYS ; 
END IF ; 
SET SQL_STATEMENT = STRIP_SPACES ( 'Select Entry_TimeStamp,
Journal_Code ,
Case Journal_Entry_Type When ''PT''
Then ''Insert''
When ''PX''
Then ''Insert By RRN''
                         When ''UB''
Then ''Update Before''
                         When ''UP''
Then ''Update After''
                         When ''DL''
Then ''Delete''
                         Else Journal_Entry_Type
                    End As JrnType,
                    Job_Name,
Job_User,
                    Job_Number ,
Program_Name,
Program_Library ' ) ; 
SET START_POSITION = 1 ; 
IF FILE_LIBRARY IS NULL 
OR TRIM ( FILE_LIBRARY ) = '' THEN 
SET FILE_LIBRARY = ( SELECT SYSTEM_SCHEMA_NAME 
FROM QSYS2 . LIBLIST B 
JOIN SYSTABLES 
ON SYSTEM_SCHEMA_NAME = TABLE_SCHEMA 
AND FILE_NAME = TABLE_NAME 
ORDER BY B . ORDINAL_POSITION 
LIMIT 1 ) ; 
END IF ; 
FOR SELECT TABLE_SCHEMA AS SYSTEM_TABLE_SCHEMA , COLUMN_NAME , COLUMN_HEADING ,A . ORDINAL_POSITION , DATA_TYPE , 
LENGTH AS FIELD_LENGTH , COALESCE ( NUMERIC_SCALE , 0 ) AS NUMERIC_SCALE , STORAGE , COALESCE ( CCSID , 0 ) AS FIELD_CCSID 
FROM QSYS2 . SYSCOLUMNS A 
WHERE TABLE_SCHEMA = FILE_LIBRARY 
AND FILE_NAME = TABLE_NAME 
ORDER BY A . ORDINAL_POSITION 
DO 
SET COLUMN_BUFFER = 
CASE DATA_TYPE 
WHEN 'DATE' THEN 10 
WHEN 'TIME' THEN 8 
WHEN 'TIMESTMP' THEN 26 
ELSE STORAGE 
END ; 
IF COLUMN_LIST = 'ALL' 
OR LOCATE ( TRIM ( UPPER ( COLUMN_NAME ) ) , UPPER ( COLUMN_LIST ) ) > 0 THEN 
SET SQL_STATEMENT = TRIM ( SQL_STATEMENT ) || PAD || 'Interpret(SubStr(Entry_Data' || PAD || VARCHAR ( START_POSITION ) || PAD || 
VARCHAR ( COLUMN_BUFFER ) || ') As ' || TRIM ( 
CASE DATA_TYPE 
WHEN 'DATE' THEN 'Varchar(10)' 
WHEN 'TIME' THEN 'Varchar(8)' 
WHEN 'TIMESTMP' THEN 'Varchar(26)' 
WHEN 'FLOAT' THEN 
CASE FIELD_LENGTH 
WHEN 16 THEN 'Float' 
WHEN 8 THEN 'Double' 
WHEN 4 THEN 'Real' 
END 
ELSE DATA_TYPE 
END ) ; 
SET SQL_STATEMENT = TRIM ( SQL_STATEMENT ) || 
CASE 
WHEN DATA_TYPE IN ( 'CHAR' , 'VARCHAR' , 'BINARY' ) THEN ' (' || VARCHAR ( FIELD_LENGTH ) || ')' 
WHEN DATA_TYPE IN ( 'DECIMAL' , 'NUMERIC' ) THEN ' (' || VARCHAR ( FIELD_LENGTH) || PAD || VARCHAR ( NUMERIC_SCALE ) || ')' 
END || ')' ; 
SET SQL_STATEMENT = TRIM ( SQL_STATEMENT ) || ' As ' || TRIM ( COLUMN_NAME ) ;END IF ; 
SET START_POSITION = START_POSITION + COLUMN_BUFFER ; 
END FOR ; 
SET SQL_STATEMENT = TRIM ( SQL_STATEMENT ) || ' From Table (
                  Qsys2.Display_Journal(Journal_Library => ''' || TRIM ( JOURNAL_LIBRARY ) || 
''', Journal_Name => ''' || TRIM ( JOURNAL_NAME ) || ''',' || 
'Starting_Receiver_Name => ''*CURAVLCHN'', Journal_Codes => ''R'',' || 'Object_Library => ''' || TRIM ( FILE_LIBRARY ) || 
''', Object_Name => ''' || TRIM ( FILE_NAME ) || ''', Object_Member => ''*ALL'',' || 
'Object_Objtype => ''*FILE'')) Where Entry_Timestamp >  Date(''' || FROM_DATE ||''')' ; 
IF WHERE_CLAUSE IS NOT NULL THEN 
SET WHERE_CLAUSE = 
CASE 
WHEN LOWER ( SUBSTR ( TRIM ( WHERE_CLAUSE ) , 1 , 5 ) ) = 'where' THEN TRIM ( WHERE_CLAUSE ) 
ELSE ' Where ' || WHERE_CLAUSE 
END ; 
SET SQL_STATEMENT = 'Select * From Table(' || SQL_STATEMENT || ')' || WHERE_CLAUSE ; 
END IF ; 
SET SQL_STATEMENT = SQL_STATEMENT || ' For Read Only' ; 
PREPARE SQL_STMT FROM SQL_STATEMENT ; 
OPEN JOURNAL_CUR ; 
END  ; 

COMMENT ON PARAMETER SPECIFIC PROCEDURE CISTOOLS.REVIEW_JOURNAL 
( FILE_NAME IS 'File Name' , 
	JOURNAL_NAME IS 'Journal Name' , 
	FILE_LIBRARY IS 'File Library' , 
	JOURNAL_LIBRARY IS 'Journal Library' , 
	FROM_DATE IS 'From Date' , 
	COLUMN_LIST IS 'Column List' , 
	WHERE_CLAUSE IS 'Where Clause' ) ; 

GRANT ALTER , EXECUTE   
ON SPECIFIC PROCEDURE CISTOOLS.REVIEW_JOURNAL 
TO AMAPICS WITH GRANT OPTION ; 

GRANT EXECUTE   
ON SPECIFIC PROCEDURE CISTOOLS.REVIEW_JOURNAL 
TO PUBLIC ;