/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:52:33
 * @ Usage: CALL MWCONFIG.MERGE_ROUTING_TABLE(ID, CODE_START, CODE_END, CHANNEL, START_DATE, END_DATE, QUEUE, STATUS, SUBCODEX, ADDITIONAL, REPLY_TO, REPLY_TO_QMGR)
 */

CREATE OR REPLACE PROCEDURE MERGE_ROUTING_TABLE (
	IN CODE_START VARCHAR(256),
	IN CODE_END VARCHAR(256),
	IN CHANNEL VARCHAR(256),
	IN START_DATE TIMESTAMP,
	IN END_DATE TIMESTAMP,
	IN QUEUE VARCHAR(256),
	IN STATUS VARCHAR(20),
	IN SUBCODEX VARCHAR(256),
	IN ADDITIONAL INTEGER,
	IN REPLY_TO VARCHAR(256),
	IN REPLY_TO_QMGR VARCHAR(256),
	IN CREATE_BY VARCHAR(256) DEFAULT CURRENT USER,
	IN CREATE_DATE TIMESTAMP DEFAULT CURRENT TIMESTAMP,
	IN MODIFIED_BY VARCHAR(256) DEFAULT CURRENT USER,
	IN MODIFIED_DATE TIMESTAMP DEFAULT CURRENT TIMESTAMP
)
LANGUAGE SQL
BEGIN
	DECLARE SQL_STMT VARCHAR(10000);

	SET SQL_STMT = 	'MERGE INTO MWCONFIG.ROUTING_TABLE AS T ' ||
		'USING (VALUES (' || 
		CASE WHEN CODE_START IS NOT NULL THEN '''' || CODE_START || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN CODE_END IS NOT NULL THEN '''' || CODE_END || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN CHANNEL IS NOT NULL THEN '''' || CHANNEL || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN START_DATE IS NOT NULL THEN '''' || START_DATE || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN END_DATE IS NOT NULL THEN '''' || END_DATE || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN QUEUE IS NOT NULL THEN '''' || QUEUE || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN STATUS IS NOT NULL THEN '''' || STATUS || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN SUBCODEX IS NOT NULL THEN '''' || SUBCODEX || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN ADDITIONAL IS NOT NULL THEN '''' || ADDITIONAL || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN REPLY_TO IS NOT NULL THEN '''' || REPLY_TO || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN REPLY_TO_QMGR IS NOT NULL THEN '''' || REPLY_TO_QMGR || '''' ELSE 'NULL' END || ', ' ||
		'CURRENT_USER, CURRENT_TIMESTAMP)) AS S ' || '(CODE_START, CODE_END, CHANNEL, START_DATE, END_DATE, QUEUE, STATUS, SUBCODEX, ADDITIONAL, REPLY_TO, REPLY_TO_QMGR, CREATE_BY, CREATE_DATE) ' ||
		'ON (T.CHANNEL = S.CHANNEL AND T.CODE_START = S.CODE_START AND T.CODE_END = S.CODE_END) ' ||
		'WHEN MATCHED THEN ' ||
		'    UPDATE SET ' ||
		'        T.CODE_START = ' || 
		CASE WHEN CODE_START IS NOT NULL THEN '''' || CODE_START || '''' ELSE 'NULL' END || ', ' ||
		'        T.CODE_END = ' || 
		CASE WHEN CODE_END IS NOT NULL THEN '''' || CODE_END || '''' ELSE 'NULL' END || ', ' ||
		'        T.CHANNEL = ' || 
		CASE WHEN CHANNEL IS NOT NULL THEN '''' || CHANNEL || '''' ELSE 'NULL' END || ', ' ||
		'        T.START_DATE = ' || 
		CASE WHEN START_DATE IS NOT NULL THEN '''' || START_DATE || '''' ELSE 'NULL' END || ', ' ||
		'        T.END_DATE = ' || 
		CASE WHEN END_DATE IS NOT NULL THEN '''' || END_DATE || '''' ELSE 'NULL' END || ', ' ||
		'        T.QUEUE = ' || 
		CASE WHEN QUEUE IS NOT NULL THEN '''' || QUEUE || '''' ELSE 'NULL' END || ', ' ||
		'        T.STATUS = ' || 
		CASE WHEN STATUS IS NOT NULL THEN '''' || STATUS || '''' ELSE 'NULL' END || ', ' ||
		'        T.SUBCODEX = ' || 
		CASE WHEN SUBCODEX IS NOT NULL THEN '''' || SUBCODEX || '''' ELSE 'NULL' END || ', ' ||
		'        T.ADDITIONAL = ' || 
		CASE WHEN ADDITIONAL IS NOT NULL THEN '''' || ADDITIONAL || '''' ELSE 'NULL' END || ', ' ||
		'        T.REPLY_TO = ' || 
		CASE WHEN REPLY_TO IS NOT NULL THEN '''' || REPLY_TO || '''' ELSE 'NULL' END || ', ' ||
		'        T.REPLY_TO_QMGR = ' || 
		CASE WHEN REPLY_TO_QMGR IS NOT NULL THEN '''' || REPLY_TO_QMGR || '''' ELSE 'NULL' END || ', ' ||
		'        T.MODIFIED_BY = CURRENT_USER, T.MODIFIED_DATE = CURRENT_TIMESTAMP ' ||
		'WHEN NOT MATCHED THEN ' ||
		'    INSERT (CODE_START, CODE_END, CHANNEL, START_DATE, END_DATE, QUEUE, STATUS, SUBCODEX, ADDITIONAL, REPLY_TO, REPLY_TO_QMGR, CREATE_BY, CREATE_DATE) ' ||
		'    VALUES (' || 
		CASE WHEN CODE_START IS NOT NULL THEN '''' || CODE_START || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN CODE_END IS NOT NULL THEN '''' || CODE_END || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN CHANNEL IS NOT NULL THEN '''' || CHANNEL || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN START_DATE IS NOT NULL THEN '''' || START_DATE || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN END_DATE IS NOT NULL THEN '''' || END_DATE || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN QUEUE IS NOT NULL THEN '''' || QUEUE || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN STATUS IS NOT NULL THEN '''' || STATUS || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN SUBCODEX IS NOT NULL THEN '''' || SUBCODEX || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN ADDITIONAL IS NOT NULL THEN '''' || ADDITIONAL || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN REPLY_TO IS NOT NULL THEN '''' || REPLY_TO || '''' ELSE 'NULL' END || ', ' ||
		CASE WHEN REPLY_TO_QMGR IS NOT NULL THEN '''' || REPLY_TO_QMGR || '''' ELSE 'NULL' END || ', ' ||
		'CURRENT_USER, CURRENT_TIMESTAMP)';

	PREPARE STMT FROM SQL_STMT;
	EXECUTE STMT;
END
@