-- CALL TmSp_CleanupExpiredPersonalDataOfSubjects;
-- DROP PROCEDURE TmSp_CleanupExpiredPersonalDataOfSubjects;
CREATE PROCEDURE TmSp_CleanupExpiredPersonalDataOfSubjects
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
    --*With Encryption
AS
    DatSubType NVARCHAR(4);
    DatSubKey NVARCHAR(15);
    DatSubKey2 NVARCHAR(50);
    TableName NVARCHAR(10);
    QryString NVARCHAR(400);
    WhereString NVARCHAR(150);
    UpdateDateName NVARCHAR(20);
    KeyName NVARCHAR(20);
    DPPStatus NVARCHAR(2);
  
		
	CURSOR C_CURSOR FOR	
----Get Expired Personal Data of OHEM/OUSR/CPN1/OCPR
	    SELECT DISTINCT T0."DatSubType", T0."DatSubKey", T0."DatSubKey2", T0."TableName" 
		FROM OBPD T0, ODRP T1 
		WHERE T1."Active" = 'Y' AND T0."DatSubType" = T1."DatSubType" AND T0."DatSubType" != 'OCRD' AND T0."DatSubType" != 'OUDO'
			AND ADD_YEARS(T0."CreateDate", T1."PeriodYear") < CURDATE()
			AND T0."TableName" IN ('OCPR','OUSR','OHEM','CPN1','OPRQ','OTSH','ODRF')
		ORDER by T0."DatSubType", T0."DatSubKey", T0."DatSubKey2";	
		
BEGIN
    OPEN C_CURSOR;
    FETCH C_CURSOR INTO DatSubType, DatSubKey, DatSubKey2, TableName;
           
    WHILE NOT c_cursor::NOTFOUND DO
    	UpdateDateName := 'UpdateDate';
    	IF :TableName = 'OCPR' OR :TableName = 'OUSR' THEN
    		UpdateDateName := 'updateDate';
    	END IF;
        
    	KeyName := 'CardCode';
        IF :TableName = 'OCPR' THEN
    		KeyName := 'CntctCode';
    	ELSEIF :TableName = 'OHEM' THEN
    		KeyName := 'empID';
    	ELSEIF :TableName = 'OUSR' THEN
    		KeyName := 'USERID';
    	ELSEIF :TableName = 'CPN1' THEN
    		KeyName := 'BpCode';
    	ELSEIF :TableName = 'OPRQ' THEN
    		KeyName := 'ReqID';
    	ELSEIF :TableName = 'OTSH' THEN
    		KeyName := 'UserID';
    	ELSEIF :TableName = 'ODRF' THEN
    		KeyName := 'ReqID';
    	END IF;
    	
    	DPPStatus := 'N';
    	IF :TableName = 'OCPR' OR :TableName = 'OUSR' OR :TableName = 'OHEM' OR :TableName = 'CPN1' OR :TableName = 'OPRQ' OR :TableName = 'ODRF' THEN
    		DPPStatus := 'B';
    	END IF;
    
        QryString := 'UPDATE ' || :TableName || ' SET "DPPStatus" = ''D'', "' || :UpdateDateName || '" = CURDATE()';
        IF :TableName = 'CPN1' THEN
        	QryString := 'UPDATE ' || :TableName || ' SET "DPPStatus" = ''D''';
        END IF;
        
        WhereString :=  ' WHERE "DPPStatus" = '''|| :DPPStatus || ''' AND "' || :KeyName || '" = ''' || :DatSubKey || '''';
        IF :DatSubType = 'CPN1' AND :TableName = 'CPN1' THEN
            WhereString := ' WHERE "DPPStatus" = ''B'' AND "CpnNo" = ' || :DatSubKey || ' AND "CpnLineNum" = ' || :DatSubKey2;
        END IF;
                
        EXEC (:QryString || :WhereString);
			
		--For sales employee which linked to employee, need set DPPStatus to erased manually.
		IF :TableName = 'OHEM' THEN
			QryString := 'UPDATE T0 SET T0."DPPStatus"=''D'' FROM OSLP T0, OHEM T1 WHERE T0."SlpCode" = T1."salesPrson" AND T1."empID" = ' || :DatSubKey;
			EXEC (:QryString);
		END IF;
        
        FETCH C_CURSOR INTO DatSubType, DatSubKey, DatSubKey2, TableName;
    END WHILE;
    
    CLOSE C_CURSOR;
	
	    
--Delete Expired Personal Data of OHEM/OUSR/CPN1/OCPR
    QryString := 'DELETE FROM OBPD T0 WHERE T0."DatSubType" != ''OCRD'' AND 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;