-- CALL TmSp_CleanupExpiredPersonalDataOfOCRD;
-- DROP PROCEDURE TmSp_CleanupExpiredPersonalDataOfOCRD;
CREATE PROCEDURE TmSp_CleanupExpiredPersonalDataOfOCRD
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
    --*With Encryption
AS
    DatSubType NVARCHAR(4);
    DatSubKey NVARCHAR(15);
    TableName NVARCHAR(10);
    QryString NVARCHAR(500);
    WhereString NVARCHAR(150);
    UpdateDateName NVARCHAR(20);
    KeyName NVARCHAR(20);
    DPPStatus NVARCHAR(2);
  
		
	CURSOR C_CURSOR FOR	
--Get Expired Personal Data of OCRD
	    SELECT DISTINCT T0."DatSubType", T0."DatSubKey", T0."TableName"
		FROM OBPD T0, ODRP T1 
		WHERE T1."Active" = 'Y' AND T0."DatSubType" = T1."DatSubType" AND T0."DatSubType" = 'OCRD' AND T0."ObjType" = T1."ObjType" AND T1."ObjType" != '-1'
			AND ADD_YEARS(T0."CreateDate", T1."PeriodYear") < CURDATE()
			AND T0."TableName" IN ('OCRD','OCPR','OUSR','OHEM','CPN1','OCIN','OCPI','OCPV','OCSI','OCSV','ODLN',
									'ODPI','ODPO','ODRF','OIEI','OIGE','OIGN','OINV','OOEI','OPCH','OPDN','OPOR',
									'OPQT','OPRR','OQUT','ORDN','ORDR','ORIN','ORPC','ORPD','ORRR','OSFC','OSFI',
									'OWTQ','OWTR','ORCT','OVPM','OBNK','OOPR','OINS','OCHO','OCTR','OSCL','OCLG',
									'OPMG','OIPF','PWZ1','PWZ3','PWZ5','OCHH','OPEX','DWZ1','OBOE','OOAT')
		
		union
		
--Get Expired Personal Data of OCRD and Object Type = ALL
		SELECT DISTINCT T0."DatSubType", T0."DatSubKey", T0."TableName" 
		FROM OBPD T0, ODRP T1 
		WHERE T1."Active" = 'Y' AND T0."DatSubType" = T1."DatSubType" AND T0."DatSubType" = 'OCRD' AND T1."ObjType" = '-1'
			AND ADD_YEARS(T0."CreateDate", T1."PeriodYear") < CURDATE()
			AND T0."TableName" IN ('OCRD','OCPR','OUSR','OHEM','CPN1','OCIN','OCPI','OCPV','OCSI','OCSV','ODLN',
									'ODPI','ODPO','ODRF','OIEI','OIGE','OIGN','OINV','OOEI','OPCH','OPDN','OPOR',
									'OPQT','OPRR','OQUT','ORDN','ORDR','ORIN','ORPC','ORPD','ORRR','OSFC','OSFI',
									'OWTQ','OWTR','ORCT','OVPM','OBNK','OOPR','OINS','OCHO','OCTR','OSCL','OCLG',
									'OPMG','OIPF','PWZ1','PWZ3','PWZ5','OCHH','OPEX','DWZ1','OBOE','OOAT')
			AND NOT EXISTS (SELECT T0.* FROM ODRP T2 WHERE T2."ObjType" != '-1' AND T2."Active" = 'Y' AND T0."DatSubType" = T2."DatSubType" AND T0."ObjType" = T2."ObjType")	
		ORDER by T0."DatSubType", T0."DatSubKey";	
		
BEGIN
    OPEN C_CURSOR;
    FETCH C_CURSOR INTO DatSubType, DatSubKey, TableName;
           
    WHILE NOT c_cursor::NOTFOUND DO
    	UpdateDateName := 'UpdateDate';
    	IF :TableName = 'OINS' OR :TableName = 'OSCL' THEN
    		UpdateDateName := 'updateDate';
    	ELSEIF :TableName = 'OOAT' THEN
    		UpdateDateName := 'UpdtDate';
    	END IF;
        
    	KeyName := 'CardCode';
        IF :TableName = 'OINS' OR :TableName = 'OSCL' THEN
    		KeyName := 'customer';
    	ELSEIF :TableName = 'OCTR' THEN
    		KeyName := 'CstmrCode';
    	ELSEIF :TableName = 'OPMG' THEN
    		KeyName := 'CARDCODE';
    	ELSEIF :TableName = 'OOAT' OR :TableName = 'CPN1' THEN
    		KeyName := 'BpCode';
    	END IF;
    	
    	DPPStatus := 'N';
    	IF :TableName = 'OCRD' OR :TableName = 'OCIN' OR :TableName = 'OCPI' OR :TableName = 'OCPV' OR :TableName = 'OCSI' OR :TableName = 'OCSV'
			OR :TableName = 'ODLN' OR :TableName = 'ODPI' OR :TableName = 'ODPO' OR :TableName = 'OIEI' OR :TableName = 'OIGE' OR :TableName = 'OIGN'
			OR :TableName = 'OINV' OR :TableName = 'OOEI' OR :TableName = 'OPCH' OR :TableName = 'OPDN' OR :TableName = 'OPOR' OR :TableName = 'OPQT'
			OR :TableName = 'OPRR' OR :TableName = 'OQUT' OR :TableName = 'ORDN' OR :TableName = 'ORDR' OR :TableName = 'ORIN' OR :TableName = 'ORPC'
			OR :TableName = 'ORPD' OR :TableName = 'ORRR' OR :TableName = 'OSFC' OR :TableName = 'OSFI' OR :TableName = 'OWTQ' OR :TableName = 'OWTR'
			OR :TableName = 'ODRF' THEN
    		DPPStatus := 'B';
    	END IF;
    
        QryString := 'UPDATE ' || :TableName || ' SET "DPPStatus" = ''D'', "' || :UpdateDateName || '" = CURDATE()';
        IF :TableName = 'OCHO' OR :TableName = 'OPEX' OR :TableName = 'OBNK' OR :TableName = 'PWZ1' OR :TableName = 'PWZ3' OR :TableName = 'PWZ5' OR :TableName = 'OCHH' OR :TableName = 'DWZ1' THEN
        	QryString := 'UPDATE ' || :TableName || ' SET "DPPStatus" = ''D''';
        END IF;
        
        WhereString :=  ' WHERE "DPPStatus" = '''|| :DPPStatus || ''' AND "' || :KeyName || '" = ''' || :DatSubKey || '''';
        IF :TableName = 'OCHO' THEN
            WhereString := ' WHERE "DPPStatus" = ''N'' AND  "VendorCode" = ''' || :DatSubKey || ''' AND "CardOrAcct" = ''C''';
        ELSEIF :TableName = 'OPEX' THEN
            WhereString := ' WHERE "DPPStatus" = ''N'' AND ("VendorNum" = ''' || :DatSubKey || ''' OR "CustNum" = ''' || :DatSubKey || ''')';
        END IF;
                
        EXEC (:QryString || :WhereString);
        
        FETCH C_CURSOR INTO DatSubType, DatSubKey, TableName;
    END WHILE;
    
    CLOSE C_CURSOR;
	
--Delete Expired Personal Data of OCRD
    QryString := 'DELETE FROM OBPD T0 WHERE T0."DatSubType" = ''OCRD'' AND EXISTS (SELECT T0.* FROM ODRP T1 WHERE T1."ObjType" != ''-1'' AND T1."Active" = ''Y'' AND T0."DatSubType" = T1."DatSubType" AND T0."ObjType" = T1."ObjType" AND ADD_YEARS(T0."CreateDate", T1."PeriodYear") < CURDATE())';
	EXEC (:QryString);
--Delete Expired Personal Data of OCRD and Object Type = ALL
    QryString := 'DELETE FROM OBPD T0 WHERE T0."DatSubType" = ''OCRD'' AND EXISTS (SELECT T0.* FROM ODRP T1 WHERE T1."Active" = ''Y'' AND T0."DatSubType" = T1."DatSubType" AND T1."ObjType" = ''-1'' AND ADD_YEARS(T0."CreateDate", T1."PeriodYear") < CURDATE())';
	QryString := :QryString || ' AND NOT EXISTS (SELECT T0.* FROM ODRP T2 WHERE T2."ObjType" != ''-1'' AND T2."Active" = ''Y'' AND T0."DatSubType" = T2."DatSubType" AND T0."ObjType" = T2."ObjType")';
    EXEC (:QryString);
    
    
END;