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

/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:52:33
 * @ Usage: CALL MWCONFIG.MERGE_DEV_TELLER_MAP(GROUP, DEVICE_ID, DEVICE_NAME, TERMINAL_ID, TERMINAL_IP, TELLER_ID, CTRL_UNIT_ID)
 */

CREATE OR REPLACE PROCEDURE MERGE_DEV_TELLER_MAP (
    IN GROUP VARCHAR(64),
    IN DEVICE_ID VARCHAR(256),
    IN DEVICE_NAME VARCHAR(256),
    IN TERMINAL_ID VARCHAR(256),
    IN TERMINAL_IP VARCHAR(64),
    IN TELLER_ID VARCHAR(256),
    IN CTRL_UNIT_ID VARCHAR(256),
    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.DEV_TELLER_MAP AS T ' ||
        'USING (VALUES (''' || GROUP || ''', ''' || DEVICE_ID || ''', ''' ||
        DEVICE_NAME || ''', ''' || TERMINAL_ID || ''', ''' || TELLER_ID || ''', ''' ||
        CTRL_UNIT_ID || ''', ''' || TERMINAL_IP || ''', ''' || MODIFIED_BY || ''', ''' ||
        TIMESTAMP || ''')) ' ||
        'AS S ("GROUP", DEVICE_ID, DEVICE_NAME, TERMINAL_ID, TELLER_ID, CTRL_UNIT_ID, ' ||
        'TERMINAL_IP, MODIFIED_BY, TIMESTAMP) ' ||
        'ON (T."GROUP" = S."GROUP" AND T.DEVICE_ID = S.DEVICE_ID) ' ||
        'WHEN MATCHED THEN ' ||
        'UPDATE SET T."GROUP" = S."GROUP", ' ||
        'T.DEVICE_NAME = S.DEVICE_NAME, ' ||
        'T.TERMINAL_ID = S.TERMINAL_ID, ' ||
        'T.TELLER_ID = S.TELLER_ID, ' ||
        'T.CTRL_UNIT_ID = S.CTRL_UNIT_ID, ' ||
        'T.MODIFIED_BY = S.MODIFIED_BY, ' ||
        'T.TIMESTAMP = S.TIMESTAMP, ' ||
        'T.TERMINAL_IP = S.TERMINAL_IP ' ||
        'WHEN NOT MATCHED THEN ' ||
        'INSERT ("GROUP", DEVICE_ID, DEVICE_NAME, TERMINAL_ID, TELLER_ID, CTRL_UNIT_ID, ' ||
        'TERMINAL_IP, MODIFIED_BY, TIMESTAMP) ' ||
        'VALUES (S."GROUP", ''' || DEVICE_ID || ''', ''' || DEVICE_NAME || ''', ''' || TERMINAL_ID || ''', ''' ||
        TELLER_ID || ''', ''' || CTRL_UNIT_ID || ''', ''' || TERMINAL_IP || ''', CURRENT USER, CURRENT TIMESTAMP)';



    PREPARE STMT FROM SQL_STMT;
    EXECUTE STMT;
END
@