-- B1 DEPENDS: AFTER:PT:PROCESS_END

CREATE PROCEDURE BEM_CLEAR_OLD_DATA ()
LANGUAGE SQLSCRIPT 
SQL SECURITY INVOKER
AS
BEGIN

UPDATE OBEM SET "Status" = 'E', "RepMessage" = 'Replication time out.'
WHERE "Status" IN ('I', 'R', 'P') AND DAYS_BETWEEN ("UpdateDate", CURRENT_DATE) > 1;

UPDATE BEM1 SET "MDStatus" = 'E', "RepMessage" = 'Master Data replication time out.'
WHERE "MDStatus" IN ('I', 'R', 'P') AND DAYS_BETWEEN ("UpdMDDate", CURRENT_DATE) > 1;

UPDATE BEM1 SET "ScpStatus" = 'E', "RepMessage" = 'Scope replication time out.'
WHERE "ScpStatus" IN ('I', 'R', 'P') AND DAYS_BETWEEN ("UpdScpDate", CURRENT_DATE) > 1;

DELETE FROM BEM2 WHERE DAYS_BETWEEN ("CreateDate", CURRENT_DATE) > 10;

END;