-- FUNCTION: internal.pupdatelearnerfromsourcef(bigint, date, date, smallint, smallint, character varying, integer, refcursor)

-- DROP FUNCTION IF EXISTS internal.pupdatelearnerfromsourcef(bigint, date, date, smallint, smallint, character varying, integer, refcursor);

CREATE OR REPLACE FUNCTION internal.pupdatelearnerfromsourcef(
	par_dataimportid bigint,
	par_startdate date,
	par_date date,
	par_externalidtype smallint,
	par_programmeid smallint,
	par_programmecode character varying DEFAULT NULL::character varying,
	par_debugmode integer DEFAULT 0,
	INOUT p_refcur refcursor DEFAULT NULL::refcursor)
    RETURNS refcursor
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

DECLARE
    var_CreateClassesIfNotExist NUMERIC(1, 0) DEFAULT 1;
    var_EntireSchoolSelfManaged NUMERIC(1, 0) DEFAULT 0;
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_Year SMALLINT DEFAULT date_part('year', par_Date);
    var_SourceRowCount INTEGER;
    var_RowCount INTEGER;
    var_RowCountS VARCHAR(30);
    var_LogData VARCHAR(2000);
    var_Sql TEXT;
    var_WeekEndDate DATE DEFAULT (SELECT
        weekfriday
        FROM dbo.dimdate
        WHERE date = par_Date);
    var_WeekEndDateTime TIMESTAMP(0) WITHOUT TIME ZONE;
    var_SysEndTimeHack TIMESTAMP(0) WITHOUT TIME ZONE;
    ptemporalstatus$par_StatusChanged NUMERIC;
    ptemporalstatus$ReturnCode INTEGER;
    sql$rowcount BIGINT;
BEGIN
    /*
    [7810 - Severity CRITICAL - PostgreSQL doesn't support the SET NOCOUNT. If need try another way to send message back to the client application.]
    SET NOCOUNT ON
    */
    IF par_Date IS NULL THEN
        par_Date := clock_timestamp();
    END IF;
    var_WeekEndDateTime := CAST (var_WeekEndDate AS TIMESTAMP(0) WITHOUT TIME ZONE) + (1::NUMERIC || ' DAY')::INTERVAL + (- 1::NUMERIC || ' SECOND')::INTERVAL;
   -- CALL ClickCOMMON.dbo.pResetSequence('ClickDW', 'dbo', 'Person', 'SchoolLearner', 'SchoolLearnerYear', 'SchoolClass');

	IF NOT EXISTS (
	   SELECT 1
	   FROM   information_schema.tables 
	   WHERE table_type = 'LOCAL TEMPORARY'
	   AND table_name=LOWER('t$updatelearnerfromsource')
	) THEN
        CREATE TEMPORARY TABLE IF NOT EXISTS t$updatelearnerfromsource
        (externalid VARCHAR(30),
            firstname VARCHAR(50),
            lastname VARCHAR(50),
            username VARCHAR(50),
            gradeid SMALLINT,
            programmecode VARCHAR(30),
            sourceclassname VARCHAR(70),
            classname VARCHAR(70),
            personid INTEGER,
            programmeenvironmentid INTEGER);
    END IF;
	
    CREATE TEMPORARY TABLE t$programmeenvironmentselfmanaged
    (programmeenvironmentid INTEGER,
        schoolid INTEGER,
        entireschool NUMERIC(1, 0));
    CREATE TEMPORARY TABLE t$data
    (externalid VARCHAR(30),
        firstname VARCHAR(50),
        lastname VARCHAR(50),
        username VARCHAR(50),
        gradeid SMALLINT,
        programmecode VARCHAR(30),
        sourceclassname VARCHAR(70),
        classname VARCHAR(70),
        personid INTEGER,
        programmeenvironmentid INTEGER,
        /* */
        personidnew NUMERIC(1, 0) DEFAULT (0),
        schoolid INTEGER,
        schoollearnerid BIGINT,
        schoollearneridnew NUMERIC(1, 0) DEFAULT (0),
        schoollearneryearid BIGINT,
        schoollearneryearidnew NUMERIC(1, 0) DEFAULT (0),
        schoollearneryear_gradeid SMALLINT,
        schoollearneryear_schoolclassid INTEGER,
        schoolclassid INTEGER,
        programmeenddate TIMESTAMP(0) WITHOUT TIME ZONE,
        schoollearneryear_grade_selfmanaged NUMERIC(1, 0) DEFAULT (1));
    CREATE TEMPORARY TABLE t$dataclass
    (schoolid INTEGER,
        gradeid SMALLINT,
        classname VARCHAR(70),
        schoolclassid INTEGER,
        schoolclassidnew NUMERIC(1, 0) DEFAULT (0));

    BEGIN
        IF var_TranCountOnEntry = 0 THEN
            /*
            [7807 - Severity CRITICAL - PostgreSQL does not support explicit transaction management in functions. Perform a manual conversion.]
            BEGIN TRAN
            */
            BEGIN
            END;
        END IF;
        INSERT INTO t$programmeenvironmentselfmanaged (programmeenvironmentid, schoolid, entireschool)
        SELECT
            tg.programmeenvironmentid, MIN(pe.schoolid),
            CASE
                WHEN COUNT(*) = SUM(CASE
                    WHEN tg.learnermanagementlevelid = 2 THEN 1
                    ELSE 0
                END) THEN 1
                ELSE 0
            END
            FROM dbo.programmeenvironmenthastargetgrade AS tg
            JOIN dbo.vwschoolenvironmentprogramme AS pe
                ON tg.programmeenvironmentid = pe.programmeenvironmentid
            WHERE pe.programmeid = par_ProgrammeID AND ((par_ProgrammeCode IS NULL) OR (par_ProgrammeCode IS NOT NULL AND LOWER(pe.programmecode) = LOWER(par_ProgrammeCode)))
            GROUP BY tg.programmeenvironmentid
            HAVING SUM(CASE
                WHEN tg.learnermanagementlevelid = 2 THEN 1
                ELSE 0
            END) > 0;
        /* SELECT '#ProgrammeEnvironmentSelfManaged' '#ProgrammeEnvironmentSelfManaged', * FROM #ProgrammeEnvironmentSelfManaged; */

        IF par_DebugMode >= 2 THEN
            OPEN p_refcur FOR
            SELECT
                '#ProgrammeEnvironmentSelfManaged' AS "#ProgrammeEnvironmentSelfManaged", *
                FROM t$programmeenvironmentselfmanaged;
        END IF;
        INSERT INTO internal.processingschool (schoolid, item, timestamp)
        SELECT
            x.schoolid, 'Year First Usable Data', CURRENT_TIMESTAMP(3)
            FROM (SELECT DISTINCT
                x.schoolid
                FROM t$programmeenvironmentselfmanaged AS x
                WHERE x.entireschool = 1) AS x
            LEFT OUTER JOIN internal.processingschool AS p
                ON x.schoolid = p.schoolid AND LOWER(p.item) = LOWER('Year First Usable Data') AND date_part('year', p.timestamp) = var_Year
            WHERE p.schoolid IS NULL
        UNION
        SELECT
            x.schoolid, 'Year First Usable Data - Update', CURRENT_TIMESTAMP(3)
            FROM (SELECT DISTINCT
                x.schoolid
                FROM t$programmeenvironmentselfmanaged AS x
                WHERE x.entireschool = 1) AS x
            LEFT OUTER JOIN internal.processingschool AS p
                ON x.schoolid = p.schoolid AND LOWER(p.item) = LOWER('Year First Usable Data - Update') AND date_part('year', p.timestamp) = var_Year
            WHERE p.schoolid IS NULL;
        SELECT
            COUNT(*)
            INTO var_SourceRowCount;
       
        INSERT INTO t$Data
        		(ExternalID, FirstName, LastName, UserName, GradeID, ProgrammeCode, SourceClassName, ClassName, PersonID, ProgrammeEnvironmentID,
        		SchoolID, ProgrammeEndDate)
        		SELECT
        			S.ExternalID,
        			S.FirstName,
        			S.LastName,
        			S.UserName,
        			S.GradeID,
        			S.ProgrammeCode,
        			S.SourceClassName,
        			S.ClassName,
        			S.PersonID,
        			S.ProgrammeEnvironmentID,
        			SE.SchoolID,
        			PP.SysEndTime
        		FROM t$updatelearnerfromsource S
        		JOIN dbo.ProgrammeEnvironment PE ON PE.ProgrammeEnvironmentID = S.ProgrammeEnvironmentID
        		JOIN dbo.SchoolEnvironment  SE ON PE.EnvironmentID = SE.EnvironmentID
        		JOIN t$programmeenvironmentselfmanaged PESM ON S.ProgrammeEnvironmentID = PESM.ProgrammeEnvironmentID
        		LEFT JOIN LATERAL(SELECT MAX(SysEndTime) SysEndTime FROM dbo.PersonHasProgramme PP WHERE PP.PersonID = S.PersonID AND PP.ProgrammeID = par_programmeid) PP ON TRUE
        		WHERE ((var_EntireSchoolSelfManaged = 1 AND PESM.EntireSchool = 1) OR (var_EntireSchoolSelfManaged = 0));
      
     
        UPDATE t$data SET
        			SchoolLearnerID					= SL.SchoolLearnerID,
        			SchoolLearnerIDNew				= 0,
        			SchoolLearnerYearID				= SLY.SchoolLearnerYearID,
        			SchoolLearnerYearIDNew			= 0, --CASE WHEN SLY.SchoolLearnerYearID IS NULL THEN 1 ELSE 0 END,
        			SchoolLearnerYear_SchoolClassID = SLY.SchoolClassID,
        			SchoolLearnerYear_GradeID       = SLY.GradeID,
        			SchoolLearnerYear_Grade_SelfManaged = CASE WHEN TG.LearnerManagementLevelID = 2 OR TG.LearnerManagementLevelID IS NULL THEN 1 ELSE 0 END
        		FROM t$data D
        		JOIN dbo.SchoolLearner SL ON D.SchoolID = SL.SchoolID AND D.PersonID = SL.PersonID AND par_StartDate BETWEEN SL.StartDate AND SL.EndDate
        		LEFT JOIN dbo.SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID AND SLY.Year = var_Year
        		LEFT JOIN dbo.ProgrammeEnvironmentHasTargetGrade TG ON D.ProgrammeEnvironmentID = TG.ProgrammeEnvironmentID AND SLY.GradeID = TG.GradeID AND par_date >= TG.ActiveFrom;
     
        IF var_EntireSchoolSelfManaged = 0 THEN
            IF par_DebugMode >= 2 THEN
                OPEN p_refcur FOR
                SELECT
                    'Learner DW Grade not self-managed (ignored)' AS "Learner DW Grade not self-managed (ignored)", *
                    FROM t$data AS d
                    WHERE d.schoollearneryear_grade_selfmanaged = 0 AND d.schoollearneryearidnew = 0;
            END IF;
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid, dataid, code, description, data)
            SELECT
                par_DataImportID, 0,
                CASE
                    WHEN aws_sqlserver_ext.ISNUMERIC(d.externalid) THEN CAST (d.externalid AS INTEGER)
                    ELSE NULL
                END, 20014, 'Learner DW Grade not self-managed (ignored)', d.externalid
                FROM t$data AS d
                WHERE d.schoollearneryear_grade_selfmanaged = 0 AND d.schoollearneryearidnew = 0;
            GET DIAGNOSTICS var_LogData = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, 'Learner DW Grade not self-managed (ignored)', 'I', var_LogData, 0, 20014);
            DELETE FROM t$data AS D
                WHERE d.schoollearneryear_grade_selfmanaged = 0 AND d.schoollearneryearidnew = 0;
        END IF;
        /* Match / create classes */
        INSERT INTO t$dataclass (schoolid, gradeid, classname)
        SELECT DISTINCT
            d.schoolid, d.gradeid, d.classname
            FROM t$data AS d
            WHERE d.classname IS NOT NULL;
        UPDATE t$dataclass AS d
        SET schoolclassid = sc.schoolclassid
        FROM dbo.schoolclass AS sc
            WHERE d.schoolid = sc.schoolid AND d.gradeid = sc.gradeid AND sc.year = var_Year AND LOWER(d.classname) = LOWER(sc.classname);
        UPDATE t$dataclass AS d
        SET schoolclassid = nextval('dbo.schoolclasssequence'), schoolclassidnew = 1
            WHERE d.schoolclassid IS NULL;

        IF EXISTS (SELECT
            *
            FROM t$dataclass
            WHERE schoolclassidnew = 1) THEN
            INSERT INTO dbo.schoolclass (schoolclassid, schoolid, gradeid, classname, year)
            SELECT
                d.schoolclassid, d.schoolid, d.gradeid, d.classname, var_Year
                FROM t$dataclass AS d
                WHERE d.schoolclassidnew = 1;
            GET DIAGNOSTICS var_LogData = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, 'SchoolClass created', 'I', var_LogData, 0, 20008);
            INSERT INTO internal.dataimportaudit (dataimportid, action, item, itemid, detail)
            SELECT
                par_DataImportID, 'C', 'SchoolClass', d.schoolclassid, d.classname
                FROM t$dataclass AS d
                WHERE d.schoolclassidnew = 1;

            IF par_DebugMode >= 2 THEN
                OPEN p_refcur FOR
                SELECT
                    'SchoolClass created' AS "SchoolClass created", s.emiscode, s.name, d.*
                    FROM t$dataclass AS d
                    JOIN dbo.school AS s
                        ON d.schoolid = s.schoolid
                    WHERE d.schoolclassidnew = 1
                    ORDER BY d.schoolid NULLS FIRST, d.gradeid NULLS FIRST, d.classname NULLS FIRST;
            END IF;
        ELSE
            var_LogData := CAST (0 AS VARCHAR(30));
            CALL internal.pdataimportloginsert(par_DataImportID, 'SchoolClass created', 'I', var_LogData, 0, 20008);
        END IF;
        UPDATE t$data AS d
        SET schoolclassid = c.schoolclassid
        FROM t$dataclass AS c
            WHERE d.schoolid = c.schoolid AND d.gradeid = c.gradeid AND LOWER(d.classname) = LOWER(c.classname);
        /* Class matched */
        UPDATE t$data AS d
        SET personid = nextval('dbo.personsequence'), personidnew = 1
            WHERE d.classname IS NOT NULL AND personid IS NULL AND schoolclassid IS NOT NULL;
        UPDATE t$data AS d
        SET schoollearnerid = nextval('dbo.schoollearnersequence'), schoollearneridnew = 1
            WHERE personid IS NOT NULL AND schoollearnerid IS NULL AND schoolclassid IS NOT NULL;
        UPDATE t$data AS d
        SET schoollearneryearid = nextval('dbo.schoollearneryearsequence'), schoollearneryearidnew = 1
            WHERE personid IS NOT NULL AND schoollearnerid IS NOT NULL AND schoollearneryearid IS NULL AND schoolclassid IS NOT NULL;
        /* Report Pending Learners (Learners that are new but not in a class) */

        IF EXISTS (SELECT
            *
            FROM t$data
            WHERE personid IS NULL AND classname IS NULL) THEN
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid, dataid, code, description, data)
            SELECT
                par_DataImportID, 0,
                CASE
                    WHEN aws_sqlserver_ext.ISNUMERIC(externalid) THEN CAST (externalid AS INTEGER)
                    ELSE NULL
                END, 20009, d.firstname || ' ' || d.lastname, d.externalid
                FROM t$data AS d
                WHERE personid IS NULL AND classname IS NULL;

            IF par_DebugMode >= 2 THEN
                OPEN p_refcur FOR
                SELECT
                    'Pending Learners' AS "Pending Learners", *
                    FROM t$data AS d
                    WHERE personid IS NULL AND classname IS NULL;
            END IF;
        END IF;
        INSERT INTO dbo.person (personid, firstname, lastname)
        SELECT
            d.personid, d.firstname, d.lastname
            FROM t$data AS d
            WHERE d.personidnew = 1;
        GET DIAGNOSTICS var_LogData = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, 'Person created', 'I', var_LogData, 0, 20008);

        IF par_DebugMode >= 2 THEN
            OPEN p_refcur FOR
            SELECT
                'Person Created' AS "Person Created", *
                FROM t$data AS d
                WHERE d.personidnew = 1;
        END IF;

        IF EXISTS (SELECT
            *
            FROM t$data
            WHERE personidnew = 1) THEN
            INSERT INTO internal.dataimportaudit (dataimportid, action, item, itemid, detail)
            SELECT
                par_DataImportID, 'C', 'Person', d.personid, p.firstname || ' ' || p.lastname
                FROM t$data AS d
                JOIN dbo.person AS p
                    ON d.personid = p.personid
                WHERE d.personidnew = 1;
            INSERT INTO dbo.personrolehasperson (personid, personroleid)
            SELECT
                d.personid, 1
                FROM t$data AS d
                WHERE d.personidnew = 1;
            INSERT INTO dbo.personexternalid (personid, personexternalidtype, externalid)
            SELECT
                d.personid, par_ExternalIDType, d.externalid
                FROM t$data AS d
                WHERE d.personidnew = 1;
            CALL internal.ptemporalstatus('dbo.PersonHasProgramme', 0, NULL, 1,par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
         
			INSERT INTO dbo.PersonHasProgramme(PersonID,ProgrammeID,UserName,RegisterDate,SysStartTime)
			SELECT D.PersonID, par_programmeid, D.UserName, par_StartDate, par_StartDate
			FROM t$data D
			WHERE D.PersonIDNew = 1;

		
            CALL internal.ptemporalstatus('dbo.PersonHasProgramme', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            UPDATE t$updatelearnerfromsource AS s
            SET personid = d.personid
            FROM  t$data AS d
                WHERE d.personidnew = 1 AND LOWER(s.externalid) = LOWER(d.externalid);
        END IF;
        /* Insert SchoolLearner */

        IF EXISTS (SELECT
            *
            FROM t$data
            WHERE schoollearneridnew = 1) THEN
            IF par_DebugMode >= 2 THEN
                OPEN p_refcur FOR
                SELECT
                    'SchoolLearner created' AS "SchoolLearner created", d.schoollearnerid, d.schoolid, d.personid, par_StartDate AS startdate, '9999-12-31' AS enddate, *
                    FROM t$data AS d
                    WHERE d.schoollearneridnew = 1;
            END IF;
            INSERT INTO dbo.schoollearner (schoollearnerid, schoolid, personid, startdate, enddate)
            SELECT
                d.schoollearnerid, d.schoolid, d.personid, par_StartDate, '9999-12-31'
                FROM t$data AS d
                WHERE d.schoollearneridnew = 1;
            GET DIAGNOSTICS var_LogData = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, 'SchoolLearner created', 'I', var_LogData, 0, 20008);
       
            INSERT INTO internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
            			SELECT var_DataImportID, 'C', 'SchoolLearner', D.SchoolLearnerID, 'Started ' + FORMAT(SL.StartDate, 'yyyy-MM-dd')
            			FROM t$data D JOIN dbo.SchoolLearner SL ON D.SchoolLearnerID = SL.SchoolLearnerID
            			WHERE D.SchoolLearnerIDNew = 1;
           
        END IF;
        /* Insert SchoolLearnerYear */

        IF EXISTS (SELECT
            *
            FROM t$data
            WHERE schoollearneryearidnew = 1) THEN
            IF par_DebugMode >= 2 THEN
                OPEN p_refcur FOR
                SELECT
                    'SchoolLearnerYear created' AS "SchoolLearnerYear created", d.schoollearneryearid, d.schoollearnerid, d.gradeid, var_Year AS year, d.schoolclassid, par_StartDate AS startdate, *
                    FROM t$data AS d
                    WHERE d.schoollearneryearidnew = 1;
            END IF;
        
            			INSERT INTO SchoolLearnerYear(SchoolLearnerYearID,SchoolLearnerID,GradeID,Year,SchoolClassID, SysStartTime)
            			SELECT D.SchoolLearnerYearID, D.SchoolLearnerID, D.GradeID, var_Year, D.SchoolClassID, par_StartDate
            			FROM t$data D
            			WHERE D.SchoolLearnerYearIDNew = 1;
            
            
            --CALL internal.ptemporalstatus('dbo.SchoolLearnerYear', 0, NULL, 1, return_code => ptemporalstatus$ReturnCode);
         
            CALL internal.ptemporalstatus('dbo.SchoolLearnerYear', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            var_LogData := CAST (var_RowCount AS VARCHAR(30));
            CALL internal.pdataimportloginsert(par_DataImportID, 'SchoolLearnerYear created', 'I', var_LogData, 0, 20008);
        END IF;
        /* Student is moved into No Class */

        IF EXISTS (SELECT
            *
            FROM t$data
            WHERE personidnew = 0 AND schoollearneryear_schoolclassid IS NOT NULL AND schoolclassid IS NULL AND programmeenddate = '9999-12-31 23:59:59') THEN
            IF par_DebugMode >= 2 THEN
                OPEN p_refcur FOR
                SELECT
                    'Moved into No Class', 'Moved into No Class', *
                    FROM t$data AS d
                    WHERE personidnew = 0 AND schoollearneryear_schoolclassid IS NOT NULL AND schoolclassid IS NULL AND programmeenddate = '9999-12-31 23:59:59';
            END IF;
            var_SysEndTimeHack := timezone('UTC', CURRENT_TIMESTAMP(6)) + (- 1::NUMERIC || ' MINUTE')::INTERVAL;
            DELETE FROM dbo.personhasprogramme AS pp
            USING t$data AS d --, dbo.personhasprogramme AS pp
            WHERE personidnew = 0 AND schoollearneryear_schoolclassid IS NOT NULL AND schoolclassid IS NULL AND programmeenddate = '9999-12-31 23:59:59' AND (d.personid = pp.personid AND pp.programmeid = par_programmeid);
            
         
            			UPDATE PersonHasProgrammeHistory PP SET SysEndTime = CASE
            					WHEN A.LastMovementDate IS NULL THEN    PP.SysStartTime +('1 day')::INTERVAL 
            					WHEN A.LastMovementDate <= PP.SysStartTime THEN  PP.SysStartTime + ('1 day')::INTERVAL 
            					ELSE A.LastMovementDate
            				END
--             			FROM PersonHasProgrammeHistory PP
            			FROM t$data D --  ON D.PersonID = PP.PersonID AND PP.ProgrammeID = par_programmeid
            			LEFT JOIN PersonProgrammeActivity A ON PP.PersonID = A.PersonID AND A.ProgrammeID = PP.ProgrammeID
            			WHERE D.PersonID = PP.PersonID AND PP.ProgrammeID = par_programmeid 
								AND D.PersonIDNew = 0
            					AND D.SchoolLearnerYear_SchoolClassID IS NOT NULL
            					AND D.SchoolClassID IS NULL
            					AND D.ProgrammeEndDate = '9999-12-31 23:59:59'
            					AND PP.SysEndTime >= var_SysEndTimeHack;
            
           
            CALL internal.ptemporalstatus('dbo.PersonHasProgramme', 0, NULL, 1,par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            /*
            [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
            EXEC sp_executesql @Stmt=@sql, @params=N'@ProgrammeID smallint, var_SysEndTimeHack datetime2(0)', @ProgrammeID=@ProgrammeID, var_SysEndTimeHack=var_SysEndTimeHack
            */
            CALL internal.ptemporalstatus('dbo.PersonHasProgramme', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
			
            UPDATE dbo.schoollearneryear AS sly
            SET schoolclassid = NULL
            FROM t$data AS d -- , dbo.schoollearneryear AS sly
                WHERE d.personidnew = 0 AND d.schoollearneryear_schoolclassid IS NOT NULL AND d.schoolclassid IS NULL AND d.programmeenddate = '9999-12-31 23:59:59' AND d.schoollearneridnew = 0 AND d.schoollearneryearid = sly.schoollearneryearid;
          
			--RAISERROR(''SchoolLearnerYearHistory Adjust(4)'',0,0) WITH NOWAIT;

			UPDATE dbo.SchoolLearnerYearHistory SLY SET SysEndTime=var_WeekEndDateTime
			FROM t$data D
-- 			JOIN dbo.SchoolLearnerYearHistory SLY ON D.SchoolLearnerYearID = SLY.SchoolLearnerYearID
			WHERE D.SchoolLearnerYearID = SLY.SchoolLearnerYearID 
				AND D.PersonIDNew = 0
				AND D.SchoolLearnerYear_SchoolClassID IS NOT NULL
				AND D.SchoolClassID IS NULL
				AND D.ProgrammeEndDate = '9999-12-31 23:59:59'
				AND D.SchoolLearnerIDNew = 0
				AND SLY.SysEndTime >= var_SysEndTimeHack;

			
            CALL internal.ptemporalstatus('dbo.SchoolLearnerYear', 0, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
         
            CALL internal.ptemporalstatus('dbo.SchoolLearnerYear', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
           
		   INSERT INTO internal.dataimportlog (dataimportid, loglevelid, dataid, code, description, data)
            SELECT
                par_DataImportID, 0,
                CASE
                    WHEN aws_sqlserver_ext.ISNUMERIC(d.externalid) THEN CAST (d.externalid AS INTEGER)
                    ELSE NULL
                END, 20010, d.firstname || ' ' || d.lastname, d.externalid
                FROM t$data AS d
                WHERE d.personidnew = 0 AND d.schoollearneryear_schoolclassid IS NOT NULL AND d.schoolclassid IS NULL AND d.programmeenddate = '9999-12-31 23:59:59';
				
            GET DIAGNOSTICS var_LogData = ROW_COUNT;
			
            CALL internal.pdataimportloginsert(par_DataImportID, 'Learners moved into No Class', 'I', var_LogData, 0, 20010);
        ELSE
            CALL internal.pdataimportloginsert(par_DataImportID, 'Learners moved into No Class', 'I', '0', 0, 20010);
        END IF;
        /* Existing Student is moved into a Class (after leaving a class 4 or more weeks ago) */

        IF EXISTS (SELECT
            *
            FROM t$data AS d
            WHERE d.personidnew = 0 AND d.schoollearneryear_schoolclassid IS NULL AND d.schoolclassid IS NOT NULL AND d.programmeenddate <= var_WeekEndDateTime + (- 4::NUMERIC || ' WEEK')::INTERVAL) THEN
            IF par_DebugMode >= 2 THEN
                OPEN p_refcur FOR
                SELECT
                    'Moved into Class (> 4 weeks)', 'Moved into Class (> 4 weeks)', *
                    FROM t$data AS d
                    WHERE personidnew = 0 AND d.schoollearneryear_schoolclassid IS NULL AND d.schoolclassid IS NOT NULL AND d.programmeenddate <= var_WeekEndDateTime + (- 4::NUMERIC || ' WEEK')::INTERVAL;
            END IF;
        
			INSERT INTO dbo.PersonHasProgramme(PersonID,ProgrammeID,UserName,Password,RegisterDate,SysStartTime,UserNameDate)
			SELECT PP.PersonID, PP.ProgrammeID, PP.UserName, PP.Password, PP.RegisterDate, par_StartDate, PP.UserNameDate
			FROM t$data D
			JOIN dbo.PersonHasProgrammeHistory PP ON D.PersonID = PP.PersonID AND PP.ProgrammeID = par_programmeid AND D.ProgrammeEndDate = PP.SysEndTime
			WHERE D.PersonIDNew = 0
				AND D.SchoolLearnerYear_SchoolClassID IS NULL
				AND D.SchoolClassID IS NOT NULL
				AND D.ProgrammeEndDate <= var_WeekEndDateTime  - ('4 week')::INTERVAL ;

            CALL internal.ptemporalstatus('dbo.PersonHasProgramme', 0, NULL, 1,par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
          
            CALL internal.ptemporalstatus('dbo.PersonHasProgramme', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
			
            UPDATE dbo.schoollearneryear AS sly
            SET schoolclassid = d.schoolclassid
            FROM t$data AS d -- , dbo.schoollearneryear AS sly
                WHERE d.personidnew = 0 AND d.schoollearneryear_schoolclassid IS NULL AND d.schoolclassid IS NOT NULL AND d.programmeenddate <= var_WeekEndDateTime + (- 4::NUMERIC || ' WEEK')::INTERVAL AND d.schoollearneryearidnew = 0 AND d.schoollearneryearid = sly.schoollearneryearid;
          
			DELETE 			
			FROM dbo.SchoolLearnerYearHistory SLY
			Where SLY.SchoolLearnerYearID IN
			(
				SELECT SLY.SchoolLearnerYearID
				FROM t$data D
				JOIN dbo.SchoolLearnerYearHistory SLY ON D.SchoolLearnerYearID = SLY.SchoolLearnerYearID
				WHERE D.PersonIDNew = 0
					AND D.SchoolLearnerYear_SchoolClassID IS NULL
					AND D.SchoolClassID IS NOT NULL
					AND D.ProgrammeEndDate <= var_WeekEndDateTime - ('4 week')::INTERVAL
					AND D.SchoolLearnerYearIDNew = 0
					AND SLY.SysStartTime >= par_StartDate
				GROUP BY SLY.SchoolLearnerYearID
			);
		

			--RAISERROR(''Moved into Class (> 4 weeks): SchoolLearnerYearHistory delete: %d'', 0, 0, @@ROWCOUNT) WITH NOWAIT;

			UPDATE dbo.SchoolLearnerYearHistory SLY SET SysEndTime=par_StartDate
-- 			FROM SchoolLearnerYearHistory SLY
			FROM
			(
				SELECT SLY.SchoolLearnerYearID, MAX(SLY.SysEndTime) SysEndTime
				FROM t$data D
				JOIN dbo.SchoolLearnerYearHistory SLY ON D.SchoolLearnerYearID = SLY.SchoolLearnerYearID
				WHERE D.PersonIDNew = 0
					AND D.SchoolLearnerYear_SchoolClassID IS NULL
					AND D.SchoolClassID IS NOT NULL
					AND D.ProgrammeEndDate <= var_WeekEndDateTime  - ('4 week')::INTERVAL
					AND D.SchoolLearnerYearIDNew = 0
				GROUP BY SLY.SchoolLearnerYearID
			) U 
			where SLY.SchoolLearnerYearID = U.SchoolLearnerYearID AND SLY.SysEndTime = U.SysEndTime;

			--RAISERROR(''Moved into Class (> 4 weeks): SchoolLearnerYearHistory.SysEndTime set: %d'', 0, 0, @@ROWCOUNT) WITH NOWAIT;

			UPDATE dbo.SchoolLearnerYear SLY SET SysStartTime=par_StartDate
			FROM t$data D
-- 			JOIN SchoolLearnerYear SLY ON D.SchoolLearnerYearID = SLY.SchoolLearnerYearID
			WHERE D.SchoolLearnerYearID = SLY.SchoolLearnerYearID AND D.PersonIDNew = 0
				AND D.SchoolLearnerYear_SchoolClassID IS NULL
				AND D.SchoolClassID IS NOT NULL
				AND D.ProgrammeEndDate <= var_WeekEndDateTime -('4 week')::INTERVAL 
				AND D.SchoolLearnerYearIDNew = 0;

		
            CALL internal.ptemporalstatus('dbo.SchoolLearnerYear', 0, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
           
            CALL internal.ptemporalstatus('dbo.SchoolLearnerYear', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
			
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid, dataid, code, description, data)
            SELECT
                par_DataImportID, 0,
                CASE
                    WHEN aws_sqlserver_ext.ISNUMERIC(d.externalid) THEN CAST (d.externalid AS INTEGER)
                    ELSE NULL
                END, 20011, d.firstname || ' ' || d.lastname, d.externalid
                FROM t$data AS d
                WHERE d.personidnew = 0 AND d.schoollearneryear_schoolclassid IS NULL AND d.schoolclassid IS NOT NULL AND d.programmeenddate <= var_WeekEndDateTime + (- 4::NUMERIC || ' WEEK')::INTERVAL;
           
		   GET DIAGNOSTICS var_LogData = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, 'Learners moved into Class (older than 4 weeks)', 'I', var_LogData, 0, 20011);
        ELSE
            CALL internal.pdataimportloginsert(par_DataImportID, 'Learners moved into Class (older than 4 weeks)', 'I', '0', 0, 20011);
        END IF;
        /* Existing Student is moved into a Class (left class less than 4 weeks ago) */

        IF EXISTS (SELECT
            *
            FROM t$data AS d
            WHERE d.personidnew = 0 AND d.schoollearneryear_schoolclassid IS NULL AND d.schoolclassid IS NOT NULL AND d.programmeenddate > var_WeekEndDateTime + (- 4::NUMERIC || ' WEEK')::INTERVAL AND d.programmeenddate < '9999-12-31 23:59:59') THEN
            CREATE TEMPORARY TABLE t$schoolweek4date
            AS
            WITH schools (schoolid)
            AS (SELECT DISTINCT
                d.schoolid
                FROM t$data AS d
                WHERE d.personidnew = 0 AND d.schoollearneryear_schoolclassid IS NULL AND d.schoolclassid IS NOT NULL AND d.programmeenddate > var_WeekEndDateTime + (- 4::NUMERIC || ' WEEK')::INTERVAL AND d.programmeenddate < '9999-12-31 23:59:59'), schoolprovince (schoolid, entityid2)
            AS (SELECT
                s.schoolid, h.entityid2
                FROM schools AS s
                JOIN dbo.vwentityhierarchy AS h
                    ON s.schoolid = h.schoolid AND h.entityid1 = 1), provinceweeks (entityid2, weekfriday)
            AS (SELECT DISTINCT
                p.entityid2, dd.weekfriday
                FROM (SELECT DISTINCT
                    entityid2
                    FROM schoolprovince) AS p
                JOIN dbo.entityyearterm AS eyt
                    ON p.entityid2 = eyt.entityid AND eyt.year = var_Year
                JOIN dbo.dimdate AS dd
                    ON dd.date BETWEEN eyt.startdate AND eyt.enddate
                WHERE dd.date <= par_Date), provinceweeks4 (entityid2, week4date)
            AS (SELECT
                w.entityid2, MIN(w.weekfriday)
                FROM (SELECT
                    w.entityid2, w.weekfriday, ROW_NUMBER() OVER (PARTITION BY entityid2 ORDER BY weekfriday DESC) AS seq
                    FROM provinceweeks AS w) AS w
                WHERE w.Seq <= 4
                GROUP BY w.entityid2)
            SELECT
                p.schoolid, w.week4date
                FROM provinceweeks4 AS w
                JOIN schoolprovince AS p
                    ON w.entityid2 = p.entityid2;

            IF EXISTS (SELECT
                *
                FROM t$data AS d
                JOIN t$schoolweek4date AS w
                    ON d.schoolid = w.schoolid
                WHERE d.personidnew = 0 AND d.schoollearneryear_schoolclassid IS NULL AND d.schoolclassid IS NOT NULL AND d.programmeenddate > var_WeekEndDateTime + (- 4::NUMERIC || ' WEEK')::INTERVAL AND d.programmeenddate < '9999-12-31 23:59:59' AND d.programmeenddate > w.week4date) THEN
                IF par_DebugMode >= 2 THEN
                    OPEN p_refcur FOR
                    SELECT
                        'Moved into Class (< 4 weeks)', 'Moved into Class (< 4 weeks)', d.*
                        FROM t$data AS d
                        JOIN t$schoolweek4date AS w
                            ON d.schoolid = w.schoolid
                        WHERE d.personidnew = 0 AND d.schoollearneryear_schoolclassid IS NULL AND d.schoolclassid IS NOT NULL AND d.programmeenddate > var_WeekEndDateTime + (- 4::NUMERIC || ' WEEK')::INTERVAL AND d.programmeenddate < '9999-12-31 23:59:59' AND d.programmeenddate > w.week4date;
                END IF;
             
				INSERT INTO dbo.PersonHasProgramme(PersonID,ProgrammeID,UserName,Password,RegisterDate,SysStartTime,UserNameDate)
				SELECT PP.PersonID, PP.ProgrammeID, PP.UserName, PP.Password,PP.RegisterDate,PP.SysStartTime,PP.UserNameDate
				FROM t$data D
				JOIN t$schoolweek4date W ON D.SchoolID = W.SchoolID
				JOIN dbo.PersonHasProgrammeHistory PP ON D.PersonID = PP.PersonID AND PP.ProgrammeID = par_programmeid AND PP.SysEndTime = D.ProgrammeEndDate
				WHERE D.PersonIDNew = 0 
					AND D.SchoolLearnerYear_SchoolClassID IS NULL
					AND D.SchoolClassID IS NOT NULL
					AND D.ProgrammeEndDate > var_WeekEndDateTime - ('4 week')::INTERVAL AND D.ProgrammeEndDate < '9999-12-31 23:59:59'
					AND D.ProgrammeEndDate > W.Week4Date;

				--RAISERROR(''Moved into Class (< 4 weeks): PersonHasProgramme inserted: %d'', 0, 0, @@ROWCOUNT) WITH NOWAIT;

				DELETE 
				FROM t$data D
				WHERE D.SchoolID IN (
					SELECT W.schoolid FROM t$SchoolWeek4Date W 
					JOIN dbo.PersonHasProgrammeHistory PP ON D.PersonID = PP.PersonID AND PP.ProgrammeID = par_programmeid AND PP.SysEndTime = D.ProgrammeEndDate
					WHERE D.PersonIDNew = 0 
					AND D.SchoolLearnerYear_SchoolClassID IS NULL
					AND D.SchoolClassID IS NOT NULL
					AND D.ProgrammeEndDate > var_WeekEndDateTime - ('4 week')::INTERVAL AND D.ProgrammeEndDate < '9999-12-31 23:59:59'
					AND D.ProgrammeEndDate > W.Week4Date);

			
                CALL internal.ptemporalstatus('dbo.PersonHasProgramme', 0, NULL, 1,par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
             
                CALL internal.ptemporalstatus('dbo.PersonHasProgramme', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
                var_SysEndTimeHack := timezone('UTC', CURRENT_TIMESTAMP(6)) + (- 1::NUMERIC || ' MINUTE')::INTERVAL;
              
				UPDATE dbo.SchoolLearnerYear C SET SchoolClassID=H.SchoolClassID, SysStartTime=H.SysStartTime
				FROM dbo.SchoolLearnerYearHistory H
				JOIN
				(
					SELECT D.ExternalID, SLY.SchoolLearnerYearID, MAX(SLY.SysEndTime) SysEndTime
					FROM t$data D
					JOIN t$SchoolWeek4Date W ON D.SchoolID = W.SchoolID
					JOIN dbo.SchoolLearnerYearHistory SLY ON D.SchoolLearnerYearID = SLY.SchoolLearnerYearID
					WHERE D.PersonIDNew = 0
						AND D.SchoolLearnerYear_SchoolClassID IS NULL
						AND D.SchoolClassID IS NOT NULL
						AND D.ProgrammeEndDate > var_WeekEndDateTime - ('4 week')::INTERVAL AND D.ProgrammeEndDate < '9999-12-31 23:59:59'
						AND D.ProgrammeEndDate > W.Week4Date
						AND D.SchoolLearnerYearIDNew = 0
					GROUP BY D.ExternalID, SLY.SchoolLearnerYearID
				) U ON H.SchoolLearnerYearID = U.SchoolLearnerYearID AND H.SysEndTime = U.SysEndTime
				JOIN t$data D ON U.ExternalID = D.ExternalID
-- 				JOIN dbo.SchoolLearnerYear C ON H.SchoolLearnerYearID = C.SchoolLearnerYearID
				WHERE H.SchoolLearnerYearID = C.SchoolLearnerYearID AND H.SchoolClassID = D.SchoolClassID;

				--RAISERROR(''Moved into Class (< 4 weeks): SchoolLearnerYear.SchoolClassID SysStartTime set: %d'', 0, 0, @@ROWCOUNT) WITH NOWAIT;

				DELETE 
				FROM dbo.SchoolLearnerYearHistory H
				where H.SchoolLearnerYearID IN
				(
					SELECT 
					--D.ExternalID, 
					SLY.SchoolLearnerYearID
					--, MAX(SLY.SysEndTime) SysEndTime
					FROM t$data D
					JOIN t$SchoolWeek4Date W ON D.SchoolID = W.SchoolID
					JOIN dbo.SchoolLearnerYearHistory SLY ON D.SchoolLearnerYearID = SLY.SchoolLearnerYearID
					WHERE D.PersonIDNew = 0
						AND D.SchoolLearnerYear_SchoolClassID IS NULL
						AND D.SchoolClassID IS NOT NULL
						AND D.ProgrammeEndDate > var_WeekEndDateTime - ('4 week')::INTERVAL AND D.ProgrammeEndDate < '9999-12-31 23:59:59'
						AND D.ProgrammeEndDate > W.Week4Date
						AND D.SchoolLearnerYearIDNew = 0
						AND H.SchoolClassID = D.SchoolClassID
-- 						AND H.ExternalID = D.ExternalID
						AND H.SchoolLearnerYearID = D.SchoolLearnerYearID
					GROUP BY D.ExternalID, SLY.SchoolLearnerYearID
				
				
				) ;
				

				--RAISERROR(''Moved into Class (< 4 weeks): SchoolLearnerYearHistory deleted: %d'', 0, 0, @@ROWCOUNT) WITH NOWAIT;

				INSERT INTO dbo.SchoolLearnerYear(SchoolLearnerYearID,SchoolLearnerID,Year,GradeID,SchoolClassID,SysStartTime,InstructionLanguageID)
				SELECT H.SchoolLearnerYearID, H.SchoolLearnerID, H.Year, H.GradeID, D.SchoolClassID, par_StartDate, H.InstructionLanguageID
				FROM dbo.SchoolLearnerYearHistory H
				JOIN
				(
					SELECT D.ExternalID, SLY.SchoolLearnerYearID, MAX(SLY.SysEndTime) SysEndTime
					FROM t$data D
					JOIN t$SchoolWeek4Date W ON D.SchoolID = W.SchoolID
					JOIN dbo.SchoolLearnerYearHistory SLY ON D.SchoolLearnerYearID = SLY.SchoolLearnerYearID
					WHERE D.PersonIDNew = 0
						AND D.SchoolLearnerYear_SchoolClassID IS NULL
						AND D.SchoolClassID IS NOT NULL
						AND D.ProgrammeEndDate > var_WeekEndDateTime - ('4 week')::INTERVAL AND D.ProgrammeEndDate < '9999-12-31 23:59:59'
						AND D.ProgrammeEndDate > W.Week4Date
						AND D.SchoolLearnerYearIDNew = 0
					GROUP BY D.ExternalID, SLY.SchoolLearnerYearID
				) U ON H.SchoolLearnerYearID = U.SchoolLearnerYearID AND H.SysEndTime = U.SysEndTime
				JOIN t$data D ON U.ExternalID = D.ExternalID
				JOIN dbo.SchoolLearnerYear C ON H.SchoolLearnerYearID = C.SchoolLearnerYearID
				WHERE H.SchoolClassID <> D.SchoolClassID
				ON CONFLICT DO NOTHING ;

                CALL internal.ptemporalstatus('dbo.SchoolLearnerYear', 0, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
             
                CALL internal.ptemporalstatus('dbo.SchoolLearnerYear', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
               
			   INSERT INTO internal.dataimportlog (dataimportid, loglevelid, dataid, code, description, data)
                SELECT
                    par_DataImportID, 0,
                    CASE
                        WHEN aws_sqlserver_ext.ISNUMERIC(d.externalid) THEN CAST (d.externalid AS INTEGER)
                        ELSE NULL
                    END, 20011, d.firstname || ' ' || d.lastname, d.externalid
                    FROM t$data AS d
                    JOIN t$schoolweek4date AS w
                        ON d.schoolid = w.schoolid
                    WHERE d.personidnew = 0 AND d.schoollearneryear_schoolclassid IS NULL AND d.schoolclassid IS NOT NULL AND d.programmeenddate > var_WeekEndDateTime + (- 4::NUMERIC || ' WEEK')::INTERVAL AND d.programmeenddate < '9999-12-31 23:59:59' AND d.programmeenddate > w.week4date;
                GET DIAGNOSTICS var_LogData = ROW_COUNT;
                CALL internal.pdataimportloginsert(par_DataImportID, 'Learners moved into Class (less than 4 weeks)', 'I', var_LogData, 0, 20011);
            ELSE
                CALL internal.pdataimportloginsert(par_DataImportID, 'Learners moved into Class (less than 4 weeks)', 'I', '0', 0, 20011);
            END IF;
            DROP TABLE t$schoolweek4date;
        END IF;
		
        CREATE TEMPORARY TABLE t$learnerdisappeared
        AS
        SELECT DISTINCT
            pp.personid, pp.programmeid, pp.username, id.externalid
            FROM t$programmeenvironmentselfmanaged AS x
            JOIN dbo.programmeenvironment AS pe
                ON x.programmeenvironmentid = pe.programmeenvironmentid
            JOIN dbo.schoolenvironment AS se
                ON pe.environmentid = se.environmentid
            JOIN dbo.schoollearner AS sl
                ON se.schoolid = sl.schoolid AND var_WeekEndDate BETWEEN sl.startdate AND sl.enddate
            JOIN
            /* JOIN SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID AND SLY.[Year] IN ( @Year */
            dbo.personhasprogramme AS pp
                ON sl.personid = pp.personid AND pp.programmeid = par_programmeid
            LEFT OUTER JOIN dbo.personexternalid AS id
                ON id.personexternalidtype = par_ExternalIDType AND sl.personid = id.personid
            LEFT OUTER JOIN t$data AS d
                ON sl.personid = d.personid AND sl.schoolid = d.schoolid
            WHERE x.entireschool = 1 AND d.personid IS NULL;
        GET DIAGNOSTICS sql$rowcount = ROW_COUNT;

        IF sql$rowcount > 0 THEN
            IF par_DebugMode >= 2 THEN
                OPEN p_refcur FOR
                SELECT
                    'Learners removed from Source', 'Learners removed from Source', d.*
                    FROM t$learnerdisappeared AS d;
            END IF;
            var_SysEndTimeHack := timezone('UTC', CURRENT_TIMESTAMP(6)) + (- 1::NUMERIC || ' MINUTE')::INTERVAL;
            DELETE FROM dbo.personhasprogramme AS pp
            USING t$learnerdisappeared AS x --, dbo.personhasprogramme AS pp
                WHERE x.personid = pp.personid AND x.programmeid = pp.programmeid;
          
            			UPDATE dbo.PersonHasProgrammeHistory PP SET SysEndTime = CASE
            					WHEN A.LastMovementDate IS NULL THEN   PP.SysStartTime +('1 day')::INTERVAL
            					WHEN A.LastMovementDate <= PP.SysStartTime THEN PP.SysStartTime + ('1 day')::INTERVAL 
            					ELSE A.LastMovementDate
            				END
            			FROM t$LearnerDisappeared X
--             			JOIN dbo.PersonHasProgrammeHistory PP ON X.PersonID = PP.PersonID AND X.ProgrammeID = PP.ProgrammeID
            			LEFT JOIN dbo.PersonProgrammeActivity A ON X.PersonID = A.PersonID AND A.ProgrammeID = X.ProgrammeID
            			WHERE X.PersonID = PP.PersonID AND X.ProgrammeID = PP.ProgrammeID AND PP.SysEndTime >= var_SysEndTimeHack;
            
            
            CALL internal.ptemporalstatus('dbo.PersonHasProgramme', 0, NULL, 1,par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
          
            CALL internal.ptemporalstatus('dbo.PersonHasProgramme', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid, dataid, code, description, data)
            SELECT
                par_DataImportID, 0, p.personid, 20012, p.firstname || ' ' || p.lastname, d.externalid
                FROM t$learnerdisappeared AS d
                JOIN dbo.person AS p
                    ON d.personid = p.personid;
            GET DIAGNOSTICS var_LogData = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, 'Learners removed from Source', 'I', var_LogData, 0, 20012);
        ELSE
            CALL internal.pdataimportloginsert(par_DataImportID, 'Learners removed from Source', 'I', '0', 0, 20012);
        END IF;
        DROP TABLE t$learnerdisappeared;
        /* Class / grade changes */

        IF EXISTS (SELECT
            *
            FROM t$data AS d
            WHERE d.schoollearneryear_schoolclassid IS NOT NULL AND d.schoolclassid IS NOT NULL AND (d.schoollearneryear_schoolclassid <> d.schoolclassid OR d.schoollearneryear_gradeid <> d.gradeid) AND d.schoollearneryearidnew = 0) THEN
            IF par_DebugMode >= 2 THEN
                OPEN p_refcur FOR
                SELECT
                    'Class / Grade Change', 'Class / Grade Change', d.schoollearneryear_schoolclassid, d.schoolclassid, d.schoollearneryear_gradeid, d.gradeid, *
                    FROM t$data AS d
                    WHERE d.schoollearneryear_schoolclassid IS NOT NULL AND d.schoolclassid IS NOT NULL AND (d.schoollearneryear_schoolclassid <> d.schoolclassid OR d.schoollearneryear_gradeid <> d.gradeid) AND d.schoollearneryearidnew = 0;
            END IF;
            var_SysEndTimeHack := timezone('UTC', CURRENT_TIMESTAMP(6)) + (- 1::NUMERIC || ' MINUTE')::INTERVAL;
			
            UPDATE dbo.schoollearneryear AS sly
            SET schoolclassid = d.schoolclassid, gradeid = d.gradeid
            FROM t$data AS d
                WHERE d.schoollearneryear_schoolclassid IS NOT NULL AND d.schoolclassid IS NOT NULL AND (d.schoollearneryear_schoolclassid <> d.schoolclassid OR d.schoollearneryear_gradeid <> d.gradeid) AND d.schoollearneryearidnew = 0 AND sly.schoollearneryearid = d.schoollearneryearid;
          
			UPDATE dbo.SchoolLearnerYearHistory SLY SET SysEndTime=par_StartDate
			FROM t$data D
-- 			JOIN SchoolLearnerYearHistory SLY ON D.SchoolLearnerYearID = SLY.SchoolLearnerYearID
			WHERE D.SchoolLearnerYearID = SLY.SchoolLearnerYearID AND D.SchoolLearnerYear_SchoolClassID IS NOT NULL AND D.SchoolClassID IS NOT NULL 
				AND (D.SchoolLearnerYear_SchoolClassID <> D.SchoolClassID OR D.SchoolLearnerYear_GradeID <> D.GradeID)
				AND D.SchoolLearnerYearIDNew = 0
				AND SLY.SysEndTime >= var_SysEndTimeHack;

			--RAISERROR(''Class / Grade Change: SchoolLearnerYear.SchoolClassID,GradeID set (2): %d'', 0, 0, @@ROWCOUNT) WITH NOWAIT;

			UPDATE dbo.SchoolLearnerYear SLY SET SysStartTime=par_StartDate
			FROM t$data D
-- 			JOIN SchoolLearnerYear SLY ON D.SchoolLearnerYearID = SLY.SchoolLearnerYearID
			WHERE D.SchoolLearnerYearID = SLY.SchoolLearnerYearID AND D.SchoolLearnerYear_SchoolClassID IS NOT NULL AND D.SchoolClassID IS NOT NULL 
				AND (D.SchoolLearnerYear_SchoolClassID <> D.SchoolClassID OR D.SchoolLearnerYear_GradeID <> D.GradeID)
				AND D.SchoolLearnerYearIDNew = 0;

            CALL internal.ptemporalstatus('dbo.SchoolLearnerYear', 0, NULL, 1,par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
         
            CALL internal.ptemporalstatus('dbo.SchoolLearnerYear', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            
			INSERT INTO internal.dataimportlog (dataimportid, loglevelid, dataid, code, description, data)
            SELECT
                par_DataImportID, 0,
                CASE
                    WHEN aws_sqlserver_ext.ISNUMERIC(d.externalid) THEN CAST (d.externalid AS INTEGER)
                    ELSE NULL
                END, 20013, 'SchoolClass/Grade change', d.externalid
                FROM t$data AS d
                WHERE d.schoollearneryear_schoolclassid IS NOT NULL AND d.schoolclassid IS NOT NULL AND (d.schoollearneryear_schoolclassid <> d.schoolclassid OR d.schoollearneryear_gradeid <> d.gradeid) AND d.schoollearneryearidnew = 0;
            
			GET DIAGNOSTICS var_LogData = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, 'SchoolClass/Grade change', 'I', var_LogData, 0, 20013);
        ELSE
            CALL internal.pdataimportloginsert(par_DataImportID, 'SchoolClass/Grade change', 'I', '0', 0, 20013);
        END IF;

        IF EXISTS (SELECT
            *
            FROM t$data
            WHERE personidnew = 1) THEN
--             RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 0, 0, 'internal.pUpdateTargetPersonID' USING ERRCODE = '50000';
            CALL internal.pupdatetargetpersonid();
        END IF;

        IF var_TranCountOnEntry = 0 THEN
            COMMIT;
        END IF;
        EXCEPTION
            WHEN OTHERS THEN
                IF var_TranCountOnEntry = 0 THEN
                    ROLLBACK;
                END IF;
                RAISE NOTICE 'Error';
                RAISE;
    END;
   
    
    DROP TABLE IF EXISTS t$updatelearnerfromsource;
  
    DROP TABLE IF EXISTS t$programmeenvironmentselfmanaged;
 
    DROP TABLE IF EXISTS t$data;
 
    DROP TABLE IF EXISTS t$dataclass;
  
    DROP TABLE IF EXISTS t$schoolweek4date;
   
    DROP TABLE IF EXISTS t$learnerdisappeared;
 
    
END;
$BODY$;

ALTER FUNCTION internal.pupdatelearnerfromsourcef(bigint, date, date, smallint, smallint, character varying, integer, refcursor)
    OWNER TO postgres;
