btn-mdw-ace-server-local/mwconfig/storeprocedure/merge_charges.db2

/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:47:05
 * @ Usage: CALL MWCONFIG.MERGE_CHARGES(ID, C1_VALUE, C1_NAME, C1_SCRIPT, C1_ACCOUNT, C2_VALUE, C2_NAME, C2_SCRIPT, C2_ACCOUNT, C3_VALUE, C3_NAME, C3_SCRIPT, C3_ACCOUNT, C4_VALUE, C4_NAME, C4_SCRIPT, C4_ACCOUNT, C5_VALUE, C5_NAME, C5_SCRIPT, C5_ACCOUNT)
 */

CREATE OR REPLACE PROCEDURE MERGE_CHARGES (
	IN ID VARCHAR(1024),
	IN C1_VALUE DECIMAL(12,2),
	IN C1_NAME VARCHAR(256),
	IN C1_SCRIPT VARCHAR(1024),
	IN C1_ACCOUNT VARCHAR(64),
	IN C2_VALUE DECIMAL(12,2),
	IN C2_NAME VARCHAR(256),
	IN C2_SCRIPT VARCHAR(1024),
	IN C2_ACCOUNT VARCHAR(64),
	IN C3_VALUE DECIMAL(12,2),
	IN C3_NAME VARCHAR(256),
	IN C3_SCRIPT VARCHAR(1024),
	IN C3_ACCOUNT VARCHAR(64),
	IN C4_VALUE DECIMAL(12,2),
	IN C4_NAME VARCHAR(256),
	IN C4_SCRIPT VARCHAR(1024),
	IN C4_ACCOUNT VARCHAR(64),
	IN C5_VALUE DECIMAL(12,2),
	IN C5_NAME VARCHAR(256),
	IN C5_SCRIPT VARCHAR(1024),
	IN C5_ACCOUNT VARCHAR(64),
	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.CHARGES AS T ' ||
        'USING (VALUES (''' || ID || ''', ' || 
        CASE WHEN C1_VALUE IS NOT NULL THEN '''' || C1_VALUE || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C1_NAME IS NOT NULL THEN '''' || C1_NAME || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C1_SCRIPT IS NOT NULL THEN '''' || C1_SCRIPT || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C1_ACCOUNT IS NOT NULL THEN '''' || C1_ACCOUNT || '''' ELSE 'NULL' END || ', ' ||
        CASE WHEN C2_VALUE IS NOT NULL THEN '''' || C2_VALUE || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C2_NAME IS NOT NULL THEN '''' || C2_NAME || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C2_SCRIPT IS NOT NULL THEN '''' || C2_SCRIPT || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C2_ACCOUNT IS NOT NULL THEN '''' || C2_ACCOUNT || '''' ELSE 'NULL' END || ', ' ||
        CASE WHEN C3_VALUE IS NOT NULL THEN '''' || C3_VALUE || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C3_NAME IS NOT NULL THEN '''' || C3_NAME || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C3_SCRIPT IS NOT NULL THEN '''' || C3_SCRIPT || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C3_ACCOUNT IS NOT NULL THEN '''' || C3_ACCOUNT || '''' ELSE 'NULL' END || ', ' ||
        CASE WHEN C4_VALUE IS NOT NULL THEN '''' || C4_VALUE || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C4_NAME IS NOT NULL THEN '''' || C4_NAME || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C4_SCRIPT IS NOT NULL THEN '''' || C4_SCRIPT || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C4_ACCOUNT IS NOT NULL THEN '''' || C4_ACCOUNT || '''' ELSE 'NULL' END || ', ' ||
        CASE WHEN C5_VALUE IS NOT NULL THEN '''' || C5_VALUE || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C5_NAME IS NOT NULL THEN '''' || C5_NAME || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C5_SCRIPT IS NOT NULL THEN '''' || C5_SCRIPT || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C5_ACCOUNT IS NOT NULL THEN '''' || C5_ACCOUNT || '''' ELSE 'NULL' END || ')) ' ||
        'AS S (ID, C1_VALUE, C1_NAME, C1_SCRIPT, C1_ACCOUNT, C2_VALUE, C2_NAME, C2_SCRIPT, C2_ACCOUNT, ' ||
        'C3_VALUE, C3_NAME, C3_SCRIPT, C3_ACCOUNT, C4_VALUE, C4_NAME, C4_SCRIPT, C4_ACCOUNT, ' ||
        'C5_VALUE, C5_NAME, C5_SCRIPT, C5_ACCOUNT) ' ||
        'ON (T.ID = S.ID) ' ||
        'WHEN MATCHED THEN ' ||
        '  UPDATE SET T.C1_VALUE = S.C1_VALUE, T.C1_NAME = S.C1_NAME, T.C1_SCRIPT = S.C1_SCRIPT, T.C1_ACCOUNT = S.C1_ACCOUNT, ' ||
        '    T.C2_VALUE = S.C2_VALUE, T.C2_NAME = S.C2_NAME, T.C2_SCRIPT = S.C2_SCRIPT, T.C2_ACCOUNT = S.C2_ACCOUNT, ' ||
        '    T.C3_VALUE = S.C3_VALUE, T.C3_NAME = S.C3_NAME, T.C3_SCRIPT = S.C3_SCRIPT, T.C3_ACCOUNT = S.C3_ACCOUNT, ' ||
        '    T.C4_VALUE = S.C4_VALUE, T.C4_NAME = S.C4_NAME, T.C4_SCRIPT = S.C4_SCRIPT, T.C4_ACCOUNT = S.C4_ACCOUNT, ' ||
        '    T.C5_VALUE = S.C5_VALUE, T.C5_NAME = S.C5_NAME, T.C5_SCRIPT = S.C5_SCRIPT, T.C5_ACCOUNT = S.C5_ACCOUNT, ' ||
        '    T.MODIFIED_BY = ''' || MODIFIED_BY || ''', T.TIMESTAMP = CURRENT_TIMESTAMP' || 
        ' WHEN NOT MATCHED THEN ' ||
        '  INSERT (ID, C1_VALUE, C1_NAME, C1_SCRIPT, C1_ACCOUNT, C2_VALUE, C2_NAME, C2_SCRIPT, C2_ACCOUNT, ' ||
        '  C3_VALUE, C3_NAME, C3_SCRIPT, C3_ACCOUNT, C4_VALUE, C4_NAME, C4_SCRIPT, C4_ACCOUNT, ' ||
        '  C5_VALUE, C5_NAME, C5_SCRIPT, C5_ACCOUNT, MODIFIED_BY, TIMESTAMP) ' ||
        '  VALUES (S.ID, ' || 
        CASE WHEN C1_VALUE IS NOT NULL THEN '''' || C1_VALUE || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C1_NAME IS NOT NULL THEN '''' || C1_NAME || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C1_SCRIPT IS NOT NULL THEN '''' || C1_SCRIPT || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C1_ACCOUNT IS NOT NULL THEN '''' || C1_ACCOUNT || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C2_VALUE IS NOT NULL THEN '''' || C2_VALUE || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C2_NAME IS NOT NULL THEN '''' || C2_NAME || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C2_SCRIPT IS NOT NULL THEN '''' || C2_SCRIPT || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C2_ACCOUNT IS NOT NULL THEN '''' || C2_ACCOUNT || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C3_VALUE IS NOT NULL THEN '''' || C3_VALUE || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C3_NAME IS NOT NULL THEN '''' || C3_NAME || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C3_SCRIPT IS NOT NULL THEN '''' || C3_SCRIPT || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C3_ACCOUNT IS NOT NULL THEN '''' || C3_ACCOUNT || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C4_VALUE IS NOT NULL THEN '''' || C4_VALUE || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C4_NAME IS NOT NULL THEN '''' || C4_NAME || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C4_SCRIPT IS NOT NULL THEN '''' || C4_SCRIPT || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C4_ACCOUNT IS NOT NULL THEN '''' || C4_ACCOUNT || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C5_VALUE IS NOT NULL THEN '''' || C5_VALUE || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C5_NAME IS NOT NULL THEN '''' || C5_NAME || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C5_SCRIPT IS NOT NULL THEN '''' || C5_SCRIPT || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN C5_ACCOUNT IS NOT NULL THEN '''' || C5_ACCOUNT || '''' ELSE 'NULL' END || ', ' || 
        'CURRENT USER, CURRENT TIMESTAMP)';


	PREPARE STMT FROM SQL_STMT;
	EXECUTE STMT;
END
@