-- CALL TmSp_CleanupExpiredPersonalDataOfUDO;
-- DROP PROCEDURE TmSp_CleanupExpiredPersonalDataOfUDO;
CREATE PROCEDURE TmSp_CleanupExpiredPersonalDataOfUDO
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
    --*With Encryption
AS
    DatSubType NVARCHAR(4);
    DatSubKey NVARCHAR(15);
    TableName NVARCHAR(10);
    QryString NVARCHAR(400);
    WhereString NVARCHAR(150);
    UpdateDateName NVARCHAR(20);
    KeyName NVARCHAR(20);
    DPPStatus NVARCHAR(2);
    ObjectType NVARCHAR(20);
	TableNameWrapper NVARCHAR(12);
	
	CURSOR C_CURSOR FOR	
----Get Expired Personal Data of OHEM/OUSR/CPN1/OCPR
	SELECT DISTINCT T0."DatSubType", T0."DatSubKey", T0."TableName", T2."ObjectType"
	FROM OBPD T0, ODRP T1, OUTB T2 
	WHERE T1."Active" = 'Y' AND T0."DatSubType" = T1."DatSubType" AND T0."DatSubType" = 'OUDO'
		AND  ADD_YEARS(T0."CreateDate", T1."PeriodYear") < CURDATE()
		AND T0."TableName" like '@%'
		AND T0."TableName" = '@' || T2."TableName"
		AND (T2."ObjectType" = 1 OR T2."ObjectType" = 3)
	ORDER by T0."DatSubType", T0."DatSubKey";
		
BEGIN
    OPEN C_CURSOR;
    FETCH C_CURSOR INTO DatSubType, DatSubKey, TableName, ObjectType;
           
    WHILE  NOT c_cursor::NOTFOUND DO
	   	UpdateDateName := 'UpdateDate';
    	KeyName := 'DocEntry';
        IF :ObjectType = 1 THEN
				KeyName := 'Code';
		END IF;	
    	
    	DPPStatus := 'B';
		TableNameWrapper := '"' || :TableName || '"';
        QryString := 'UPDATE ' || :TableNameWrapper || ' SET "DPPStatus" = ''D'', "' || :UpdateDateName || '" = CURDATE()';
        WhereString := ' WHERE "NaturalPer" = ''Y'' AND "DPPStatus" = ''' || :DPPStatus || ''' AND "' || :KeyName || '" = ''' || :DatSubKey || '''';
        EXEC (:QryString || :WhereString);
        FETCH C_CURSOR INTO DatSubType, DatSubKey, TableName, ObjectType;
    END WHILE;
    
    CLOSE C_CURSOR;	
	    
--Delete Expired Personal Data of OUDO
    QryString := 'DELETE FROM OBPD T0 WHERE T0."DatSubType" = ''OUDO'' AND EXISTS (SELECT T0.* FROM ODRP T1 WHERE T1."Active" = ''Y'' AND T0."DatSubType" = T1."DatSubType" AND ADD_YEARS(T0."CreateDate", T1."PeriodYear") < CURDATE())';
	EXEC (:QryString);   
    
END;