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

CREATE OR REPLACE PROCEDURE INSERT_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 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
@