/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:52:33
 * @ Usage: CALL MWCONFIG.MERGE_CONFIG_METADATA(ACTIVE, XML)
 */

CREATE OR REPLACE PROCEDURE MERGE_CONFIG_METADATA (
	IN ACTIVE SMALLINT,
	IN XML CLOB,
	IN MODIFIED_BY VARCHAR(64) DEFAULT CURRENT USER,
	IN TIMESTAMP TIMESTAMP DEFAULT CURRENT TIMESTAMP
)
BEGIN
	DECLARE SQL_STMT VARCHAR(4000);

	SET SQL_STMT = 'MERGE INTO MWCONFIG.CONFIG_METADATA AS T ' ||
		'USING (VALUES (' || ACTIVE || ')) ' ||
		'AS S (ACTIVE) ' ||
		'ON (T.ACTIVE = S.ACTIVE) ' ||
		'WHEN MATCHED THEN ' ||
		'UPDATE SET 
			T.XML = ''' || XML || ''',
			T.MODIFIED_BY = ''' || MODIFIED_BY || ''',
			T.TIMESTAMP = ''' || TIMESTAMP || ''' ' ||
		'WHEN NOT MATCHED THEN ' ||
		'INSERT (ACTIVE, XML, MODIFIED_BY, TIMESTAMP) ' ||
		'VALUES (' || ACTIVE || ', ''' || XML || ''', ''' || MODIFIED_BY || ''', ''' || TIMESTAMP || ''')';

	PREPARE STMT FROM SQL_STMT;
	EXECUTE STMT;
END
@