-- CALL TmSp_GetExpiredPersonalData;
-- DROP PROCEDURE TmSp_GetExpiredPersonalData;
CREATE PROCEDURE TmSp_GetExpiredPersonalData
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
	--*With Encryption
AS
BEGIN
--Get Expired Personal Data of OHEM/OUSR/CPN1/OCPR
	SELECT DISTINCT T0."DatSubType", T0."DatSubKey", T0."DatSubKey2", T0."ObjType", T0."KeyValue1", T0."CreateDate", T1."PeriodYear", ADD_YEARS(T0."CreateDate", T1."PeriodYear") AS "ExpiredDate"
	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" LIKE ('O%') OR T0."TableName" = 'CPN1')
		 AND (T0."DatSubType" != 'OCPR' OR (T0."DatSubType" = 'OCPR' AND T0."TableName" != 'OCRD'))
		 
	UNION

--Get Expired Personal Data of OCRD
	SELECT DISTINCT T0."DatSubType", T0."DatSubKey", T0."DatSubKey2", T0."ObjType", T0."KeyValue1", T0."CreateDate", T1."PeriodYear", ADD_YEARS(T0."CreateDate", T1."PeriodYear") AS "ExpiredDate"
	FROM OBPD T0, ODRP T1
	WHERE T1."Active" = 'Y' AND T1."ObjType" != -1
		 AND T0."DatSubType" = T1."DatSubType" 
		 AND T0."DatSubType" ='OCRD' AND T0."ObjType" = T1."ObjType"
		 AND ADD_YEARS(T0."CreateDate", T1."PeriodYear") < CURDATE()
		 AND T0."TableName" LIKE ('O%')
		 
	UNION

--Get Expired Personal Data of OCRD and Object Type = ALL	
	SELECT DISTINCT T0."DatSubType", T0."DatSubKey", T0."DatSubKey2", T0."ObjType", T0."KeyValue1", T0."CreateDate", T1."PeriodYear", ADD_YEARS(T0."CreateDate", T1."PeriodYear") AS "ExpiredDate"
	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" LIKE ('O%') 
		 AND T0."TableName" NOT IN ('OILM', 'OITL') 
		 AND NOT EXISTS (SELECT 1 FROM ODRP T2 WHERE T2."Active" = 'Y' AND T2."ObjType" != -1 AND T0."DatSubType" = T2."DatSubType" AND T0."ObjType" = T2."ObjType")
		 
	UNION
--Get Expired Personal Data of OUDO and Object Type = ALL	
	SELECT DISTINCT T0."DatSubType", T0."DatSubKey", T0."DatSubKey2", T0."ObjType", T0."KeyValue1", T0."CreateDate", T1."PeriodYear", ADD_YEARS(T0."CreateDate", T1."PeriodYear") AS "ExpiredDate"
	FROM OBPD T0, ODRP T1
	WHERE T1."Active" = 'Y' 
		 AND T0."DatSubType" = T1."DatSubType" 
		 AND T0."DatSubType" = 'OUDO' AND T1."ObjType" = -1
		 AND ADD_YEARS(T0."CreateDate", T1."PeriodYear") < CURDATE()
		 AND T0."TableName" LIKE ('@%') 
		 AND NOT EXISTS (SELECT 1 FROM ODRP T2 WHERE T2."Active" = 'Y' AND T2."ObjType" != -1 AND T0."DatSubType" = T2."DatSubType")
		 
	ORDER BY T0."DatSubType", T0."DatSubKey", T0."ObjType", T0."KeyValue1";
END;