CREATE OR REPLACE PROCEDURE WSO2_CONFIRMATION_CODE_CLEANUP_RESTORE IS
    -- ------------------------------------------
    -- DECLARE VARIABLES
    -- ------------------------------------------
    rowCount INT := 0;
    current_schema VARCHAR(128);

    -- ------------------------------------------
    -- CONFIGURABLE ATTRIBUTES
    -- ------------------------------------------
    enableLog BOOLEAN := TRUE; -- ENABLE LOGGING [DEFAULT : FALSE]

BEGIN

    SELECT sys_context('USERENV', 'CURRENT_SCHEMA') INTO current_schema FROM DUAL;

    IF (enableLog)
    THEN
        EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_CONFIRMATION_CODE_CLEANUP (TIMESTAMP,LOG) VALUES (TO_CHAR(SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION STARTED...!'')';
    END IF;

    SELECT COUNT(*) INTO rowCount FROM ALL_TABLES WHERE OWNER = current_schema AND table_name = 'IDN_RECOVERY_FLOW_DATA';
    IF (rowCount = 1)
    THEN
        EXECUTE IMMEDIATE 'INSERT INTO IDN_RECOVERY_FLOW_DATA SELECT A.* FROM BAK_IDN_RECOVERY_FLOW_DATA A LEFT JOIN IDN_RECOVERY_FLOW_DATA B ON A.RECOVERY_FLOW_ID = B.RECOVERY_FLOW_ID WHERE B.RECOVERY_FLOW_ID IS NULL';
        rowCount:= sql%rowcount;
        IF (enableLog)
        THEN
            EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_CONFIRMATION_CODE_CLEANUP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION COMPLETED ON IDN_RECOVERY_FLOW_DATA WITH '||rowCount||''')';
        END IF;
        COMMIT;
    END IF;

    SELECT COUNT(*) INTO rowCount FROM ALL_TABLES WHERE OWNER = current_schema AND table_name = 'IDN_RECOVERY_DATA';
    IF (rowCount = 1)
    THEN
        EXECUTE IMMEDIATE 'INSERT INTO IDN_RECOVERY_DATA SELECT A.* FROM BAK_IDN_RECOVERY_DATA A LEFT JOIN IDN_RECOVERY_DATA B ON A.CODE = B.CODE WHERE B.CODE IS NULL';
        rowCount:= sql%rowcount;
        IF (enableLog)
        THEN
            EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_CONFIRMATION_CODE_CLEANUP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION COMPLETED ON IDN_RECOVERY_DATA WITH '||rowCount||''')';
        END IF;
        COMMIT;
    END IF;

    IF (enableLog)
    THEN
        EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_CONFIRMATION_CODE_CLEANUP (TIMESTAMP,LOG) VALUES (TO_CHAR(SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION COMPLETED...!'')';
        COMMIT;
    END IF;
END;
