/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:52:33
 * @ Usage: CALL MWCONFIG.MERGE_RELOADTS(BROKER, EXECUTIONGROUP, TYPE)
 */

CREATE OR REPLACE PROCEDURE MERGE_RELOADTS (
	IN BROKER VARCHAR(256),
	IN EXECUTIONGROUP VARCHAR(256),
	IN TYPE VARCHAR(16),
	IN MODIFIED_BY VARCHAR(64) DEFAULT CURRENT_USER,
	IN TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
LANGUAGE SQL
BEGIN
	DECLARE SQL_STMT VARCHAR(10000);

	SET SQL_STMT = 'MERGE INTO MWCONFIG.RELOADTS AS T ' ||
		'USING (VALUES (''' || BROKER || ''', ''' || EXECUTIONGROUP || ''', ''' || MODIFIED_BY || ''', ''' || TIMESTAMP || ''', ''' || TYPE || ''')) AS S (BROKER, EXECUTIONGROUP, MODIFIED_BY, TIMESTAMP, TYPE) ' ||
		'ON (T.BROKER = S.BROKER AND T.EXECUTIONGROUP = S.EXECUTIONGROUP AND T.TYPE = S.TYPE) ' ||
		'WHEN MATCHED THEN ' ||
		'  UPDATE SET ' ||
		'    T.MODIFIED_BY = S.MODIFIED_BY, ' ||
		'    T.TIMESTAMP = S.TIMESTAMP ' ||
		'WHEN NOT MATCHED THEN ' ||
		'  INSERT (BROKER, EXECUTIONGROUP, MODIFIED_BY, TIMESTAMP, TYPE) ' ||
		'  VALUES (S.BROKER, S.EXECUTIONGROUP, S.MODIFIED_BY, S.TIMESTAMP, S.TYPE)';

	PREPARE STMT FROM SQL_STMT;
	EXECUTE STMT;
END
@