-- PROCEDURE: internal.pupdatelearnerfromsourcelearner(bigint, integer, numeric, numeric, numeric, numeric, character varying, character varying, integer, integer, refcursor, refcursor, refcursor)

-- DROP PROCEDURE IF EXISTS internal.pupdatelearnerfromsourcelearner(bigint, integer, numeric, numeric, numeric, numeric, character varying, character varying, integer, integer, refcursor, refcursor, refcursor);

CREATE OR REPLACE PROCEDURE internal.pupdatelearnerfromsourcelearner(
	par_dataimportid bigint,
	par_year integer DEFAULT NULL::integer,
	par_validateonly numeric DEFAULT 0,
	par_ignoreclasses numeric DEFAULT 0,
	par_firstusabledatamode numeric DEFAULT 0,
	par_updatepersoninfoonly numeric DEFAULT 0,
	par_schools character varying DEFAULT 'ALL'::character varying,
	par_programmes character varying DEFAULT 'ALL'::character varying,
	par_debugmode integer DEFAULT 0,
	return_code integer DEFAULT 0,
	p_refcur refcursor DEFAULT NULL::refcursor,
	p_refcur_2 refcursor DEFAULT NULL::refcursor,
	p_refcur_3 refcursor DEFAULT NULL::refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
/* @Rollover                        bit = 0, */
DECLARE
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_dbname VARCHAR(128) DEFAULT current_database();
    var_LogDescription VARCHAR(4000);
    var_LogData VARCHAR(2048);
    var_RowCount INTEGER;
    var_Sql TEXT;
    var_Message VARCHAR(2000);
    var_TimeHack TIMESTAMP WITHOUT TIME ZONE;
    var_LastYearEndDate TIMESTAMP WITHOUT TIME ZONE;
    var_MaxLastSubmissionDate TIMESTAMP WITHOUT TIME ZONE;
    var_CutoffDate DATE;
    var_PreviousYearCutoffDate DATE;
    var_ProgrammeEndTime TIMESTAMP WITHOUT TIME ZONE;
    var_YearTerm4EndDate DATE;
    var_PreviousYearTerm4EndDate DATE;
    /* @DateLeftCutoffDate      date, */
    var_PreviousYear SMALLINT;
    var_StartDate TIMESTAMP WITHOUT TIME ZONE DEFAULT (SELECT
        MAX(date)
        FROM dbo.dimdate
        WHERE date <= var_MaxLastSubmissionDate AND LOWER(weekdayname) = LOWER('Monday'));
    var_crsr_clearid_PersonExternalIDType SMALLINT;
    var_crsr_clearid_ProgrammeID SMALLINT;
    var_crsr_clearid_Programme VARCHAR(50);
    var_crsr_clearid_LearnerDataTable VARCHAR(100);
    crsr_clearid CURSOR FOR
    SELECT DISTINCT
        t.personexternalidtype, x.programmeid, x.programme, p.learnerdatatable
        FROM t$phpstart AS x
        JOIN dbo.programme AS p
            ON x.programmeid = p.programmeid
        JOIN dbo.personexternalidtype AS t
            ON x.programmeid = t.programmeid
        WHERE p.learnerdatatable IS NOT NULL;
    ptemporalstatus$par_StatusChanged NUMERIC;
    ptemporalstatus$ReturnCode INTEGER;
    sql$rowcount BIGINT;
    datarowcount1 int;
BEGIN

	drop table if exists t$schoolslist;
    drop table if exists t$programmeslist;
    drop table if exists t$schoolgradeprogrammeauto;
    drop table if exists t$activeschoolgrade;
    drop table if exists t$selfmanagedschool;
    drop table if exists t$firstusabledataschools;
    drop table if exists t$data;
    drop table if exists t$previousdata;
    drop table if exists t$schoolclass;
    DROP TABLE IF EXISTS t$phpstart;
    DROP TABLE IF EXISTS t$phpend;
    DROP TABLE IF EXISTS t$phpenda;
    drop table if Exists t$data;
	DROP TABLE IF EXISTS t$ignoreclass;
	DROP TABLE IF EXISTS t$newclass;
    /*
    [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_DataImportID IS NULL THEN
        -- RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 16, 0, 'par_DataImportID must be supplied.' USING ERRCODE = '50000';
        return_code := 1;
--         RETURN;
    END IF;

    IF par_Year IS NULL THEN
        par_Year = date_part('year', clock_timestamp());
    END IF;
    var_PreviousYear = (par_Year - 1)::INT;

    -- Needs to still look at the below
    -- CALL ClickCOMMON.dbo.pResetSequence(var_dbname, 'dbo', 'Person', 'SchoolClass', 'SchoolLearner', 'SchoolLearnerYear', "@silent" := 1);

    var_LastYearEndDate := make_date(var_PreviousYear, 12, 31);
    /* (SELECT MAX(EndDate) FROM EntityYearTerm WHERE [Year]=@Year-1); */
    var_YearTerm4EndDate = (SELECT
        MAX(enddate)
        FROM dbo.entityyearterm
        WHERE year = par_Year);
    var_PreviousYearTerm4EndDate = (SELECT
        MAX(enddate)
        FROM dbo.entityyearterm
        WHERE year = var_PreviousYear);
    CREATE TEMPORARY TABLE t$schoolslist
    (emiscode VARCHAR(9),
        schoolid INTEGER);
    CREATE INDEX IF NOT EXISTS sourcelearner_schoolid_idx
        ON t$schoolslist USING btree
        (schoolid ASC NULLS LAST)
        TABLESPACE pg_default; 
		
RAISE NOTICE 't$schoolslist';
RAISE NOTICE 'par_schools: %',par_Schools;

    IF (LOWER(par_Schools) = LOWER('ALL')) THEN
        INSERT INTO t$schoolslist
        SELECT
            emiscode, schoolid
            FROM dbo.school;
            GET DIAGNOSTICS datarowcount1 = ROW_COUNT;
            RAISE NOTICE 'datarowcount1a: %', datarowcount1;            
    END IF;

    IF (LOWER(par_Schools) IN (LOWER('WC'), LOWER('CEMIS'))) THEN
        INSERT INTO t$schoolslist
        SELECT
            emiscode, schoolid
            FROM dbo.school
            WHERE LOWER(LEFT(emiscode, 1)) = LOWER('1');
            GET DIAGNOSTICS datarowcount1 = ROW_COUNT;
            RAISE NOTICE 'datarowcount1b: %', datarowcount1;                        
    ELSE
	
        IF (LOWER(par_Schools) IN (LOWER('NotWC'), LOWER('DDD'))) THEN
		RAISE NOTICE 'INSERT INTO t$schoolslist DDD ';
            INSERT INTO t$schoolslist
            SELECT
                emiscode, schoolid
                FROM dbo.school
                WHERE LOWER(LEFT(emiscode, 1)) <> LOWER('1');
            GET DIAGNOSTICS datarowcount1 = ROW_COUNT;
            RAISE NOTICE 'datarowcount1c: %', datarowcount1;
        ELSE
            IF (aws_sqlserver_ext.isnumeric(LEFT(par_Schools, 1))::int = 1::int) THEN
RAISE NOTICE 'schools1';    

                INSERT INTO t$schoolslist
                SELECT
                    emiscode, schoolid
                    FROM dbo.school
                    WHERE emiscode IN (SELECT
                        value
                        FROM string_split(par_Schools, ','));
            GET DIAGNOSTICS datarowcount1 = ROW_COUNT;
            RAISE NOTICE 'datarowcount1d: %', datarowcount1;                                    
            END IF;
        END IF;
    END IF;
RAISE NOTICE 'schools2';    
    CREATE INDEX ix_t$schoolslist_ix1 ON t$schoolslist
        (emiscode);
    CREATE INDEX ix_t$schoolslist_ix2 ON t$schoolslist
        (schoolid);
    CREATE TEMPORARY TABLE t$programmeslist
    (programmeid SMALLINT,
        code VARCHAR(10),
        name VARCHAR(200));

    IF LOWER(par_Programmes) = LOWER('ALL') THEN
RAISE NOTICE 'par_Programmes1: %', par_Programmes;  
        INSERT INTO t$programmeslist
        SELECT
            programmeid, code, name
            FROM dbo.programme
            WHERE programmeid IN (4, 8, 9);
    ELSE
RAISE NOTICE 'par_Programmes2: %', par_Programmes;      
        INSERT INTO t$programmeslist
        SELECT
            p.programmeid, code, name
            FROM dbo.programme AS p
            JOIN (SELECT
                value
                FROM string_split(par_Programmes, ',')
                    AS f) AS f
                ON LOWER(f.value) = LOWER(p.code) OR LOWER(f.value) = LOWER(p.name) OR
                CASE
                    WHEN aws_sqlserver_ext.ISNUMERIC(value) THEN CAST (value AS INTEGER)
                    ELSE NULL
                END = p.programmeid;
    END IF;
RAISE NOTICE 'par_Programmes3: %', par_Programmes;  
    BEGIN
        <<committran>>
        BEGIN
          
            var_LogDescription = FORMAT('Updating for year: %s', par_Year);
       
RAISE NOTICE 'CALL internal.pdataimportloginsert';                                      
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
RAISE NOTICE 'par_Year: %', par_Year;                       
            SELECT
                CAST ((CAST (par_Year AS VARCHAR(30)) || '-' || p.value) AS DATE)
                INTO var_CutoffDate
                FROM dbo.systemparameter AS p
                WHERE LOWER(p.category) = LOWER('Processing') AND LOWER(p.subcategory) = LOWER('Deregistration') AND LOWER(p.name) = LOWER('Cutoff Date');
            var_PreviousYearCutoffDate := make_date(date_part('year', var_CutoffDate)::int - 1, date_part('month', var_CutoffDate)::int, date_part('day', var_CutoffDate)::int);
RAISE NOTICE 'var_PreviousYearCutoffDate: %', var_PreviousYearCutoffDate;  
RAISE NOTICE 'var_CutoffDate: %', var_CutoffDate;  
            /*
            [7773 - Severity CRITICAL - Unable to perform an automated migration of arithmetic operations with dates. Make cast operands to the expected type.]
            SELECT @ProgrammeEndTime = CAST(MAX([Date]) AS datetime) + CAST('23:59:59' AS datetime) FROM DimDate WHERE [Date] <= GETDATE() AND WeekDayName = 'Sunday' AND Year = @Year
            */
            /* SET @DateLeftCutoffDate = @CutoffDate */
            /* SET @PreviousYearDateLeftCutoffDate = DATEFROMPARTS(YEAR(@CutoffDate)-1,MONTH(@CutoffDate),DAY(@CutoffDate)); */
            /* SELECT @DateLeftCutoffDate '@DateLeftCutoffDate' */
            CREATE TEMPORARY TABLE t$schoolgradeprogrammeauto
            AS
            SELECT DISTINCT
                sep.schoolid, tg.gradeid, sep.programmeid,
                CASE
                    WHEN tg.learnermanagementlevelid = 1 THEN 1
                    ELSE 0
                END AS automatic
                FROM dbo.vwschoolenvironmentprogramme AS sep
                JOIN t$schoolslist AS sf
                    ON sep.schoolid = sf.schoolid
                JOIN t$programmeslist AS pf
                    ON sep.programmeid = pf.programmeid
                JOIN dbo.programmeenvironmenthastargetgrade AS tg
                    ON sep.programmeenvironmentid = tg.programmeenvironmentid
                WHERE sep.implementationstatusid IN (4, 13);
           
           Raise Notice 'Created t$schoolgradeprogrammeauto';
            CREATE TEMPORARY TABLE t$activeschoolgrade
            AS
            SELECT DISTINCT
                schoolid, gradeid
                FROM t$schoolgradeprogrammeauto
                WHERE automatic = 1;
             Raise Notice 'Created t$activeschoolgrade';
            CREATE TEMPORARY TABLE t$selfmanagedschool
            AS
            SELECT
                sep.schoolid
                FROM dbo.vwschoolenvironmentprogramme AS sep
                JOIN t$schoolslist AS sf
                    ON sep.schoolid = sf.schoolid
                JOIN t$programmeslist AS pf
                    ON sep.programmeid = pf.programmeid
                JOIN dbo.programmeenvironmenthastargetgrade AS tg
                    ON sep.programmeenvironmentid = tg.programmeenvironmentid
                GROUP BY sep.schoolid
                HAVING COUNT(*) = SUM(CASE
                    WHEN tg.learnermanagementlevelid = 2 THEN 1
                    ELSE 0
                END);
                Raise Notice 'Created t$activeschoolgrade';
            CREATE INDEX IF NOT EXISTS selfmanagedschool_schoolid_idx
                ON t$selfmanagedschool USING btree
                (schoolid ASC NULLS LAST)
                TABLESPACE pg_default;
            CREATE TEMPORARY TABLE t$firstusabledataschools
            AS
            SELECT
                x.schoolid
                FROM (SELECT DISTINCT
                    sl.schoolid
                    FROM dbo.sourcelearner AS sl
                    JOIN t$schoolslist AS sf
                        ON sl.schoolid = sf.schoolid) AS x
                JOIN internal.processingschool AS yfud
                    ON x.schoolid = yfud.schoolid AND LOWER(yfud.item) = LOWER('Year First Usable Data') AND date_part('year', yfud.timestamp) = par_Year
                LEFT OUTER JOIN internal.processingschool AS yfudu
                    ON x.schoolid = yfudu.schoolid AND LOWER(yfudu.item) = LOWER('Year First Usable Data - Update') AND date_part('year', yfudu.timestamp) = par_Year
                WHERE yfudu.schoolid IS NULL;
                
                Raise Notice 'Created t$firstusabledataschools';

        CREATE TEMPORARY TABLE t$schoolclass
                    AS
                    SELECT
                        *
                        FROM dbo.SchoolClass;
                        
            CREATE INDEX IF NOT EXISTS SchoolClass_schoolid_idx
                ON t$schoolclass USING btree
                (schoolid ASC NULLS LAST)
                TABLESPACE pg_default; 
        Raise Notice 'Creating t$data';
-- return; -- Testing
-- CREATE TEMPORARY TABLE t$data
--         AS
--             SELECT
--                         ROW_NUMBER() OVER (ORDER BY SourceDataImportID, SourceRowNumber) AS ID,
--                         X.SourceSystem,
--                         X.SourceRowNumber,
--                         X.SourceLearnerID,
--                         X.EMISCode,
--                         X.DateJoined,
--                         CAST(COALESCE(CASE
--                             WHEN /*@Rollover = 0 AND*/ X.DateLeft IS NOT NULL AND X.DateLeft >= var_CutoffDate THEN '9999-12-31'
--                             --WHEN @Rollover = 1 AND X.DateLeft IS NOT NULL AND X.DateLeft >= @CutoffDate THEN DATEFROMPARTS(@Year,12,31)
--                             ELSE X.DateLeft
--                         END, '9999-12-31') AS date) DateLeft,
--                         X.LastSubmissionDate,
--                         X.Grade GradeID,
--                         X.ClassName ClassName,
--                         CT.CitizenshipTypeID,
--                         G.GenderID,
--                         E.EthnicityID,
--                         X.DateOfBirth,
--                         -- TRIM(ClickCOMMON.dbo.fGetStringPart(FirstName, ' ', 1))             FirstName,
--                         -- NULLIF(TRIM(ClickCOMMON.dbo.fGetStringPart(FirstName, ' ', 2)),'')  SecondName,
--                         -- NULLIF(TRIM(ClickCOMMON.dbo.fGetStringPart(FirstName, ' ', 3)),'')  ThirdName,
--                         X.FirstName AS FirstName,
--                         X.FirstName AS SecondName,
--                         X.FirstName AS ThirdName,
--                         X.LastName,
--                         X.IDNumber,
--                         HL.LanguageID AS           HomeLanguageID,
--                         X.MappedPersonID AS        PersonID,
--                         X.SchoolID,
--                         IL.LanguageID       AS     InstructionLanguageID,
--                         SL.SchoolLearnerID   AS    SchoolLearnerID,
--                         CAST(0 AS bit)      AS     SchoolLearnerIDNew,
--                         SLY.SchoolLearnerYearID AS SchoolLearnerYearID,
--                         --CAST(0 AS bit)            AS SchoolLearnerYearIDNew,
--                         SLY.GradeID             AS SchoolLearnerYear_GradeID,
--                         SLY.SchoolClassID       AS SchoolLearnerYear_SchoolClassID,
--                         SC.SchoolClassID,
--                         EYT.Term,
--                         EYT.StartDate           AS TermStartDate,
--                         EYT.EndDate             AS TermEndDate,
--                         X.InTerm,
--                         X.DateLeftSetReason,
--                         CAST(0 AS bit)          AS DateLeftSetNow,
--                         H.EntityID2             AS ProvinceEntityID

--                     FROM dbo.SourceLearner X
-- 					LEFT JOIN internal.ProcessingSchool YFUD  ON X.SchoolID = YFUD.SchoolID AND YFUD.Item = 'Year First Usable Data' AND EXTRACT(YEAR FROM YFUD.Timestamp) = par_year
-- 					LEFT JOIN internal.ProcessingSchool YFUDU ON X.SchoolID = YFUDU.SchoolID AND YFUDU.Item = 'Year First Usable Data - Update' AND EXTRACT(YEAR FROM YFUDU.Timestamp) = par_year
--                     JOIN dbo.EntityHierarchy H ON X.SchoolID = H.SchoolID AND H.EntityID1 = 1
--                     LEFT JOIN dbo.EntityYearTerm EYT ON H.EntityID2 = EYT.EntityID AND X.LastSubmissionDate BETWEEN EYT.StartDate AND EYT.EndWeekFriday
-- 					JOIN t$schoolslist SF ON X.SchoolID = SF.SchoolID
--                     JOIN (SELECT 6 PersonExternalIDType, 'CEMIS' SourceSystem UNION SELECT 9, 'DDD') XSS ON X.SourceSystem = XSS.SourceSystem
--                     JOIN dbo.PersonExternalID EXTID ON X.MappedPersonID = EXTID.PersonID AND XSS.PersonExternalIDType = EXTID.PersonExternalIDType AND EXTID.ExternalID = X.SourceLearnerID
--                     LEFT JOIN t$selfmanagedschool SELFMS ON X.SchoolID = SELFMS.SchoolID
--                     LEFT JOIN dbo.SchoolLearner SL ON X.MappedSchoolLearnerID = SL.SchoolLearnerID
--                     LEFT JOIN dbo.SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID AND SLY.Year = X.Year
              
-- 				   CROSS JOIN LATERAL
--                     (
--                         SELECT *
--                         FROM t$schoolclass SC
--                         WHERE SC.Year = X.Year AND SC.SchoolID = X.SchoolID AND SC.GradeID = X.Grade AND (SC.ClassName = COALESCE(X.ClassName,'Unknown') OR SC.SASAMSClassName = COALESCE(X.ClassName,'Unknown') OR SC.InternalClassName = COALESCE(X.ClassName,'Unknown') OR SC.MatchClassName2 = COALESCE(X.ClassName,'Unknown'))
--                         ORDER BY SchoolClassID
--                         LIMIT 1
--                     ) SC
					 
-- 				/*
-- 					LEFT JOIN
--                     (
--                         SELECT DISTINCT (Year,SchoolID,GradeID,ClassName,SASAMSClassName,InternalClassName,MatchClassName2), *
--                         FROM t$schoolclass SC						
--                         ORDER BY SchoolClassID
--                     ) SC  ON SC.Year = X.Year AND SC.SchoolID = X.SchoolID AND SC.GradeID = X.Grade AND (SC.ClassName = COALESCE(X.ClassName,'Unknown') OR SC.SASAMSClassName = COALESCE(X.ClassName,'Unknown') OR SC.InternalClassName = COALESCE(X.ClassName,'Unknown') OR SC.MatchClassName2 = COALESCE(X.ClassName,'Unknown'))
--                   */
-- 					LEFT JOIN dbo.Language IL ON X.InstructionLanguage = IL.Description OR X.InstructionLanguage = IL.DescriptionEndonym
--                     LEFT JOIN dbo.Language HL ON X.HomeLanguage = HL.Description OR X.HomeLanguage = HL.DescriptionEndonym
--                     LEFT JOIN dbo.Gender G ON X.Gender = G.Code OR X.Gender = G.Description
--                     LEFT JOIN dbo.Ethnicity E ON X.Race = E.Description OR X.Race = E.DescriptionDDD
--                     LEFT JOIN dbo.CitizenshipType CT ON X.Citizenship = CT.Description OR X.Citizenship = CT.DescriptionDDD
-- --                     LEFT JOIN internal.ProcessingSchool YFUD  ON X.SchoolID = YFUD.SchoolID AND YFUD.Item = 'Year First Usable Data' AND EXTRACT(YEAR FROM YFUD.Timestamp) = par_year
-- --                     LEFT JOIN internal.ProcessingSchool YFUDU ON X.SchoolID = YFUDU.SchoolID AND YFUDU.Item = 'Year First Usable Data - Update' AND EXTRACT(YEAR FROM YFUDU.Timestamp) = par_year
-- --                     JOIN dbo.EntityHierarchy H ON X.SchoolID = H.SchoolID AND H.EntityID1 = 1
-- --                     LEFT JOIN dbo.EntityYearTerm EYT ON H.EntityID2 = EYT.EntityID AND X.LastSubmissionDate BETWEEN EYT.StartDate AND EYT.EndWeekFriday
--                     WHERE X.MappedPersonID IS NOT NULL
--                         AND EXTRACT(YEAR FROM X.LastSubmissionDate) >= par_year
--                         AND SELFMS.SchoolID IS NULL
--                         AND
--                         (
--                             (par_firstusabledatamode = 0)
--                             OR
--                             (par_firstusabledatamode = 1 AND YFUD.SchoolID IS NOT NULL AND YFUDU.SchoolID IS NULL)
--                         );  
CREATE TEMPORARY TABLE t$data1
        AS
            SELECT
                        ROW_NUMBER() OVER (ORDER BY SourceDataImportID, SourceRowNumber) AS ID,
                        X.SourceSystem,
                        X.SourceRowNumber,
                        X.SourceLearnerID,
                        X.EMISCode,
                        X.DateJoined,
                        CAST(COALESCE(CASE
                            WHEN /*@Rollover = 0 AND*/ X.DateLeft IS NOT NULL  AND X.DateLeft >= var_CutoffDate  THEN '9999-12-31'
                            --WHEN @Rollover = 1 AND X.DateLeft IS NOT NULL AND X.DateLeft >= @CutoffDate THEN DATEFROMPARTS(@Year,12,31)
                            ELSE X.DateLeft
                        END, '9999-12-31') AS date) DateLeft,
                        X.LastSubmissionDate,
                        X.Grade GradeID,
                        X.ClassName ClassName,
                        CT.CitizenshipTypeID,
                        G.GenderID,
                        E.EthnicityID,
                        X.DateOfBirth,
                        -- TRIM(ClickCOMMON.dbo.fGetStringPart(FirstName, ' ', 1))             FirstName,
                        -- NULLIF(TRIM(ClickCOMMON.dbo.fGetStringPart(FirstName, ' ', 2)),'')  SecondName,
                        -- NULLIF(TRIM(ClickCOMMON.dbo.fGetStringPart(FirstName, ' ', 3)),'')  ThirdName,
                        X.FirstName AS FirstName,
                        X.FirstName AS SecondName,
                        X.FirstName AS ThirdName,
                        X.LastName,
                        X.IDNumber,
                        HL.LanguageID AS           HomeLanguageID,
                        X.MappedPersonID AS        PersonID,
                        X.SchoolID,
                        IL.LanguageID       AS     InstructionLanguageID,
                        SL.SchoolLearnerID   AS    SchoolLearnerID,
                        CAST(0 AS bit)      AS     SchoolLearnerIDNew,
                        SLY.SchoolLearnerYearID AS SchoolLearnerYearID,
                        --CAST(0 AS bit)            AS SchoolLearnerYearIDNew,
                        SLY.GradeID             AS SchoolLearnerYear_GradeID,
                        SLY.SchoolClassID       AS SchoolLearnerYear_SchoolClassID,
                        SC.SchoolClassID,
--                         EYT.Term,
--                         EYT.StartDate           AS TermStartDate,
--                         EYT.EndDate             AS TermEndDate,
                        X.InTerm,
                        X.DateLeftSetReason,
                        CAST(0 AS bit)          AS DateLeftSetNow
--                         H.EntityID2             AS ProvinceEntityID

                    FROM dbo.SourceLearner X
					JOIN t$schoolslist SF ON X.SchoolID = SF.SchoolID
                    JOIN (SELECT 6 PersonExternalIDType, 'CEMIS' SourceSystem UNION SELECT 9, 'DDD') XSS ON X.SourceSystem = XSS.SourceSystem
                    JOIN dbo.PersonExternalID EXTID ON X.MappedPersonID = EXTID.PersonID AND XSS.PersonExternalIDType = EXTID.PersonExternalIDType AND EXTID.ExternalID = X.SourceLearnerID
                    LEFT JOIN t$selfmanagedschool SELFMS ON X.SchoolID = SELFMS.SchoolID
                    LEFT JOIN dbo.SchoolLearner SL ON X.MappedSchoolLearnerID = SL.SchoolLearnerID
                    LEFT JOIN dbo.SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID AND SLY.Year = X.Year
              
				   CROSS JOIN LATERAL
                    (
                        SELECT *
                        FROM t$schoolclass SC
                        WHERE SC.Year = X.Year AND SC.SchoolID = X.SchoolID AND SC.GradeID = X.Grade AND (SC.ClassName = COALESCE(X.ClassName,'Unknown') OR SC.SASAMSClassName = COALESCE(X.ClassName,'Unknown') OR SC.InternalClassName = COALESCE(X.ClassName,'Unknown') OR SC.MatchClassName2 = COALESCE(X.ClassName,'Unknown'))
                        ORDER BY SchoolClassID
                        LIMIT 1
                    ) SC
					 
				/*
					LEFT JOIN
                    (
                        SELECT DISTINCT (Year,SchoolID,GradeID,ClassName,SASAMSClassName,InternalClassName,MatchClassName2), *
                        FROM t$schoolclass SC						
                        ORDER BY SchoolClassID
                    ) SC  ON SC.Year = X.Year AND SC.SchoolID = X.SchoolID AND SC.GradeID = X.Grade AND (SC.ClassName = COALESCE(X.ClassName,'Unknown') OR SC.SASAMSClassName = COALESCE(X.ClassName,'Unknown') OR SC.InternalClassName = COALESCE(X.ClassName,'Unknown') OR SC.MatchClassName2 = COALESCE(X.ClassName,'Unknown'))
                  */
					LEFT JOIN dbo.Language IL ON X.InstructionLanguage = IL.Description OR X.InstructionLanguage = IL.DescriptionEndonym
                    LEFT JOIN dbo.Language HL ON X.HomeLanguage = HL.Description OR X.HomeLanguage = HL.DescriptionEndonym
                    LEFT JOIN dbo.Gender G ON X.Gender = G.Code OR X.Gender = G.Description
                    LEFT JOIN dbo.Ethnicity E ON X.Race = E.Description OR X.Race = E.DescriptionDDD
                    LEFT JOIN dbo.CitizenshipType CT ON X.Citizenship = CT.Description OR X.Citizenship = CT.DescriptionDDD
					

                    WHERE X.MappedPersonID IS NOT NULL
                        AND EXTRACT(YEAR FROM X.LastSubmissionDate) >= par_year
                        AND SELFMS.SchoolID IS NULL;  
						
						
				CREATE TEMPORARY TABLE t$data
				AS
					SELECT X.*,		
					    EYT.Term,
                        EYT.StartDate           AS TermStartDate,
                        EYT.EndDate             AS TermEndDate,
						H.EntityID2             AS ProvinceEntityID
					FROM t$data1 AS X
					LEFT JOIN internal.ProcessingSchool YFUD  ON X.SchoolID = YFUD.SchoolID AND YFUD.Item = 'Year First Usable Data' AND EXTRACT(YEAR FROM YFUD.Timestamp) = par_year
					LEFT JOIN internal.ProcessingSchool YFUDU ON X.SchoolID = YFUDU.SchoolID AND YFUDU.Item = 'Year First Usable Data - Update' AND EXTRACT(YEAR FROM YFUDU.Timestamp) = par_year
                    JOIN dbo.EntityHierarchy H ON X.SchoolID = H.SchoolID AND H.EntityID1 = 1
                    LEFT JOIN dbo.EntityYearTerm EYT ON H.EntityID2 = EYT.EntityID AND X.LastSubmissionDate BETWEEN EYT.StartDate AND EYT.EndWeekFriday
					WHERE (
                            (par_firstusabledatamode = 0)
                            OR
                            (par_firstusabledatamode = 1 AND YFUD.SchoolID IS NOT NULL AND YFUDU.SchoolID IS NULL)
                        );
            GET DIAGNOSTICS datarowcount1 = ROW_COUNT;
			DROP TABLE t$data1;
RAISE NOTICE 't$data'; 

RAISE NOTICE 'datarowcount1: %', datarowcount1;
            /*
            [7833 - Severity CRITICAL - Migration @@rowcount function in current context is not supported. Perform a manual conversion.]
            SET @RowCount = @@ROWCOUNT;
            */
            /*
            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
            SET @LogDescription = FORMATMESSAGE('#Data rows: %d', @RowCount);
            */
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

            IF par_DebugMode >= 2 THEN
                OPEN p_refcur FOR
                SELECT
                    *
                    FROM t$data AS d
                    ORDER BY d.emiscode NULLS FIRST, d.gradeid NULLS FIRST, d.lastname NULLS FIRST, d.firstname NULLS FIRST;
            END IF;

   RAISE NOTICE 'Delete from t$data';

				DELETE FROM  t$data 
                    USING
                    (
                        SELECT SX.SourceSystem, SX.SourceLearnerID, SX.EMISCode, SX.DateJoined
                        FROM
                        (
                            SELECT X.SourceLearnerID, X.EMISCode
                            FROM t$data X
                            WHERE X.SourceSystem = 'DDD' AND X.PersonID IS NOT NULL AND X.DateLeft = '9999-12-31'
                            GROUP BY X.SourceLearnerID, X.EMISCode
                            HAVING COUNT(*) > 1 AND COUNT(X.SchoolLearnerID) >= 1 AND COUNT(X.SchoolLearnerID) < COUNT(*)
                        ) X
                        JOIN t$data SX ON X.SourceLearnerID = SX.SourceLearnerID AND X.EMISCode = SX.EMISCode AND SX.SourceSystem = 'DDD' AND SX.DateLeft = '9999-12-31'
                    ) X
                    JOIN t$data SX ON X.SourceSystem = SX.SourceSystem AND X.EMISCode = SX.EMISCode AND X.SourceLearnerID = SX.SourceLearnerID AND X.DateJoined = SX.DateJoined
                    WHERE SX.SchoolLearnerID IS NULL;
           
            GET DIAGNOSTICS var_RowCount = ROW_COUNT;
RAISE NOTICE 'var_RowCount: %', var_RowCount;           
            /*
            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
            SET @LogDescription = FORMATMESSAGE('Remove "duplicates" from #Data rows: %d', @RowCount);
            */
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
			
            CREATE TEMPORARY TABLE t$previousdata
            AS
            SELECT
                ROW_NUMBER() OVER (ORDER BY sourcedataimportid, sourcerownumber) AS id, x.sourcesystem, x.sourcerownumber, x.sourcelearnerid, x.emiscode, x.datejoined,
                CASE
                    WHEN x.dateleft >= var_PreviousYearCutoffDate THEN make_date(var_PreviousYear, 12, 31)
                    ELSE x.dateleft
                END AS dateleft, sl.enddate, x.lastsubmissiondate, x.mappedpersonid AS personid, x.mappedschoollearnerid AS schoollearnerid
                FROM dbo.sourcelearner AS x
                JOIN t$schoolslist AS sf
                    ON x.schoolid = sf.schoolid
                JOIN dbo.schoollearner AS sl
                    ON x.mappedschoollearnerid = sl.schoollearnerid
                LEFT OUTER JOIN LATERAL (SELECT
                    xc.mappedschoollearnerid
                    FROM dbo.sourcelearner AS xc
                    WHERE x.mappedschoollearnerid = xc.mappedschoollearnerid AND xc.year = par_Year
                    LIMIT 1) AS xc
                    ON true
                LEFT OUTER JOIN t$selfmanagedschool AS selfms
                    ON x.schoolid = selfms.schoolid
                WHERE /*
                @Rollover = 0
                AND
                */
                x.year = var_PreviousYear AND x.emiscode NOT IN (SELECT DISTINCT
                    emiscode
                    FROM t$data) AND date_part('year', x.lastsubmissiondate) >= var_PreviousYear 
                    AND date_part('year', x.sysstarttime) = par_Year AND selfms.schoolid IS NULL 
                    AND x.dateleft IS NOT NULL AND sl.enddate = '9999-12-31' 
                    AND date_part('year', x.dateleft) = var_PreviousYear 
                    -- AND xc.mappedschoollearnerid IS NULL;
                    AND xc.mappedschoollearnerid IS NOT NULL;
					
RAISE NOTICE 't$previousdata';                          
           GET DIAGNOSTICS var_RowCount = ROW_COUNT;
           
            var_LogDescription = FORMAT('PreviousData rows: %s', var_RowCount);
          
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
            
            IF NOT EXISTS (               
            SELECT
                *
                FROM t$data) AND NOT EXISTS (SELECT
                *
                FROM t$previousdata) THEN
RAISE NOTICE 'not exists';
                EXIT committran;
            END IF;
            
            IF EXISTS (
            SELECT
                *
                FROM t$previousdata) THEN
RAISE NOTICE 'exists';                           
                CREATE TEMPORARY TABLE t$previousyearlearnerleft
                AS
                SELECT
                    d.*
                    FROM t$previousdata AS d
                    WHERE d.dateleft <> '9999-12-31' AND d.enddate = '9999-12-31';
                GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
                var_RowCount := sql$rowcount;
RAISE NOTICE 't$previousyearlearnerleft';               
RAISE NOTICE 'var_RowCount: %', var_RowCount;               
                CALL internal.pdataimportloginsertcount(par_DataImportID, 'Learner has left school (previous year): %d', var_RowCount);
                IF var_RowCount > 0 THEN
/*                
                    var_Sql := '
                INSERT internal.DataImportAudit([DataImportID], [Action], [Item], [ItemID], [Detail])
                SELECT par_DataImportID, ''U'', ''SchoolLearner'', C.SchoolLearnerID, FORMATMESSAGE(''Learner has left school (previous year). SchoolLearner.EndDate set: %s'',FORMAT(C.DateLeft,''yyyy-MM-dd''))
                FROM #PreviousYearLearnerLeft C
                JOIN SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID;

                UPDATE SL SET EndDate = C.DateLeft, EndDateSetReason = ''Source Data''
                FROM #PreviousYearLearnerLeft C
                JOIN SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID;

                EXEC internal.pDataImportLogInsertCount par_DataImportID, ''Learner has left school (previous year): SchoolLearner.EndDate set: %d'', @@ROWCOUNT;

                INSERT internal.DataImportAudit([DataImportID], [Action], [Item], [ItemID], [Detail])
                SELECT par_DataImportID, ''D'', ''SchoolLearnerYear'', SLY.SchoolLearnerYearID, FORMATMESSAGE(''Learner has left school  (previous year). SchoolLearnerYear in future deleted: %d'',SLY.[Year])
                FROM #PreviousYearLearnerLeft C
                JOIN SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID
                JOIN SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID
                WHERE SLY.Year > YEAR(C.DateLeft);

                DELETE SLY
                FROM #PreviousYearLearnerLeft C
                JOIN SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID
                JOIN SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID
                WHERE SLY.Year > YEAR(C.DateLeft);

                EXEC internal.pDataImportLogInsertCount par_DataImportID, ''Learner has left school (previous year): SchoolLearnerYear in future deleted: %d'', @@ROWCOUNT;
                ';
*/
/*                    var_Sql := '
                INSERT INTO internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
                SELECT par_DataImportID, ''U'', ''SchoolLearner'', C.SchoolLearnerID, CONCAT(''Learner has left school (previous year). SchoolLearner.EndDate set: '',TO_CHAR(C.DateLeft::DATE,''yyyy-MM-dd''))
                FROM t$PreviousYearLearnerLeft C
                JOIN dbo.SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID;
RAISE NOTICE ''EXECUTE1a'';
                UPDATE dbo.SchoolLearner SET EndDate = C.DateLeft, EndDateSetReason = ''Source Data''
                FROM t$PreviousYearLearnerLeft C
                JOIN dbo.SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID;
                GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
RAISE NOTICE ''EXECUTE1b'';
                CALL internal.pDataImportLogInsertCount var_DataImportID, ''Learner has left school (previous year): SchoolLearner.EndDate set: '', sql$rowcount;
RAISE NOTICE ''EXECUTE1c'';
                INSERT into internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
                SELECT var_DataImportID, ''D'', ''SchoolLearnerYear'', SLY.SchoolLearnerYearID, CONCAT(''Learner has left school  (previous year). SchoolLearnerYear in future deleted: '',SLY.Year)
                FROM t$PreviousYearLearnerLeft C
                JOIN SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID
                JOIN SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID
                WHERE SLY.Year > date_part(''year'',C.DateLeft);
RAISE NOTICE ''EXECUTE1d'';                
/*
                DELETE 
                FROM 
                    dbo.SchoolLearnerYear SLY
                USING
                    t$PreviousYearLearnerLeft C,
                    dbo.SchoolLearner SL
                WHERE
                    C.SchoolLearnerID = SL.SchoolLearnerID
                    AND SL.SchoolLearnerID = SLY.SchoolLearnerID
                    AND SLY.Year > date_part(''year'',C.DateLeft);
RAISE NOTICE ''EXECUTE1e'';
                GET DIAGNOSTICS sql$rowcount = ROW_COUNT;

                CALL internal.pDataImportLogInsertCount var_DataImportID, ''Learner has left school (previous year): SchoolLearnerYear in future deleted: '', sql$rowcount;
RAISE NOTICE ''EXECUTE1f'';                
*/                
                ';
*/
/*
         var_Sql := '
                INSERT INTO internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
                SELECT ' || $1 || ', ''U'', ''SchoolLearner'', C.SchoolLearnerID, CONCAT(''Learner has left school (previous year). SchoolLearner.EndDate set: '',TO_CHAR(C.DateLeft::DATE,''yyyy-MM-dd''))
                FROM t$PreviousYearLearnerLeft C
                JOIN dbo.SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID';
EXECUTE var_Sql
USING par_DataImportID;
*/

EXECUTE 'INSERT INTO internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
                SELECT $1, ''U'', ''SchoolLearner'', C.SchoolLearnerID, CONCAT(''Learner has left school (previous year). SchoolLearner.EndDate set: '',TO_CHAR(C.DateLeft::DATE,''yyyy-MM-dd''))
                FROM t$PreviousYearLearnerLeft C
                JOIN dbo.SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID'
USING par_DataImportID;
RAISE NOTICE 'EXECUTE1a';

/*
EXECUTE 'UPDATE dbo.SchoolLearner SET EndDate = C.DateLeft, EndDateSetReason = ''Source Data''
                FROM t$PreviousYearLearnerLeft C
                JOIN dbo.SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID;
                GET DIAGNOSTICS sql$rowcount = ROW_COUNT'
USING par_DataImportID;             
*/
RAISE NOTICE 'EXECUTE1b';

-- CALL internal.pDataImportLogInsertCount(par_DataImportID, 'Learner has left school (previous year): SchoolLearner.EndDate set: ', sql$rowcount);
CALL internal.pdataimportloginsertcount(par_DataImportID, 'Learner has left school (previous year): SchoolLearner.EndDate set: ', var_RowCount);
RAISE NOTICE 'EXECUTE1c';

EXECUTE 'INSERT into internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
                SELECT $1, ''D'', ''SchoolLearnerYear'', SLY.SchoolLearnerYearID, CONCAT(''Learner has left school  (previous year). SchoolLearnerYear in future deleted: '',SLY.Year)
                FROM t$PreviousYearLearnerLeft C
                JOIN dbo.SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID
                JOIN dbo.SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID
                WHERE SLY.Year > date_part(''year'',C.DateLeft)'
USING par_DataImportID;                             
RAISE NOTICE 'EXECUTE1d';   
/*
EXECUTE 'DELETE 
                FROM 
                    dbo.SchoolLearnerYear SLY
                USING
                    t$PreviousYearLearnerLeft C,
                    dbo.SchoolLearner SL
                WHERE
                    C.SchoolLearnerID = SL.SchoolLearnerID
                    AND SL.SchoolLearnerID = SLY.SchoolLearnerID
                    AND SLY.Year > date_part(''year'',C.DateLeft);
                GET DIAGNOSTICS sql$rowcount = ROW_COUNT'
USING par_DataImportID;                                             
*/
RAISE NOTICE 'EXECUTE1e';

-- CALL internal.pDataImportLogInsertCount(par_DataImportID, 'Learner has left school (previous year): SchoolLearnerYear in future deleted: ', sql$rowcount);
CALL internal.pdataimportloginsertcount(par_DataImportID, 'Learner has left school (previous year): SchoolLearnerYear in future deleted: ', var_RowCount);
RAISE NOTICE 'EXECUTE1f';                

-- RAISE NOTICE 'var_Sql: %', var_Sql;                             
                    -- CALL clickdw_internal.ptemporalstatus('dbo.SchoolLearner', 0, NULL, 1, return_code => ptemporalstatus$ReturnCode);
                    -- CALL clickdw_internal.ptemporalstatus('dbo.SchoolLearnerYear', 0, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
                    /*
                    [7672 - Severity CRITICAL - Automatic conversion of this command is not supported. Perform a manual conversion.]
                    */
--                    EXECUTE sp_executesql @stmt=@Sql, @Params=N'par_DataImportID bigint', par_DataImportID=par_DataImportID;
RAISE NOTICE 'EXECUTE1';
                    
                    -- CALL clickdw_internal.ptemporalstatus('dbo.SchoolLearner', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
                    -- CALL clickdw_internal.ptemporalstatus('dbo.SchoolLearnerYear', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
                    var_Sql := NULL;
                END IF;
                DROP TABLE t$previousyearlearnerleft;
            END IF;
	 /*
            CREATE TABLE #YearLearnerLeft
            (
                SchoolID int,
                SchoolLearnerID bigint,
                SourceLearnerID varchar(30),
                SchoolLearnerYearID bigint,
                DateLeft date,
                PrevYear smallint
            )
            */
            Raise Notice '621 error query start';
            IF EXISTS (SELECT
                *
                FROM t$data) THEN
                /* Update Person details */
                PERFORM dbo.tinttable$aws$f('"var_PersonUpdate$aws$tmp"'); --Check with HD
                --CALL internal.ptemporalstatus('dbo.Person', 0, par_Silent := 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
                WITH upd_stat (value)
                AS (UPDATE dbo.person p
                SET firstname = d.firstname, secondname = d.secondname, thirdname = d.thirdname, lastname = d.lastname, birthdate =
                CASE
                    WHEN d.dateofbirth IS NOT NULL THEN d.dateofbirth
                    ELSE p.birthdate
                END, genderid =
                CASE
                    WHEN d.genderid IS NOT NULL THEN d.genderid
                    ELSE p.genderid
                END, ethnicityid =
                CASE
                    WHEN d.ethnicityid IS NOT NULL THEN d.ethnicityid
                    ELSE p.ethnicityid
                END, citizenshiptypeid =
                CASE
                    WHEN d.citizenshiptypeid IS NOT NULL THEN d.citizenshiptypeid
                    ELSE p.citizenshiptypeid
                END, homelanguageid =
                CASE
                    WHEN d.homelanguageid IS NOT NULL THEN d.homelanguageid
                    ELSE p.homelanguageid
                END
--                FROM t$data AS d, dbo.person AS p
--                    WHERE d.schoollearnerid IS NOT NULL AND d.schoollearneryearid IS NOT NULL AND d.dateleft = '9999-12-31' AND (((d.firstname <> p.firstname::VARCHAR) OR (d.firstname IS NOT NULL AND p.firstname IS NULL)) OR ((d.secondname <> p.secondname::VARCHAR) OR (d.secondname IS NOT NULL AND p.secondname IS NULL) OR (d.secondname IS NULL AND p.secondname IS NOT NULL)) OR ((d.thirdname <> p.thirdname::VARCHAR) OR (d.thirdname IS NOT NULL AND p.thirdname IS NULL) OR (d.thirdname IS NULL AND p.thirdname IS NOT NULL)) OR ((LOWER(d.lastname) <> LOWER(p.lastname)) OR (d.lastname IS NOT NULL AND p.lastname IS NULL)) OR ((d.dateofbirth <> p.birthdate) OR (d.dateofbirth IS NOT NULL AND p.birthdate IS NULL)) OR ((d.genderid <> p.genderid) OR (d.genderid IS NOT NULL AND p.genderid IS NULL)) OR ((d.ethnicityid <> p.ethnicityid) OR (d.ethnicityid IS NOT NULL AND p.ethnicityid IS NULL)) OR ((d.citizenshiptypeid <> p.citizenshiptypeid) OR (d.citizenshiptypeid IS NOT NULL AND p.citizenshiptypeid IS NULL)) OR ((d.homelanguageid <> p.homelanguageid) OR (d.homelanguageid IS NOT NULL AND p.homelanguageid IS NULL))) AND d.personid = p.personid
-- FROM dbo.person AS p
-- JOIN t$data AS d ON d.personid = p.personid

FROM t$data AS d -- ON d.personid = p.personid
    WHERE d.schoollearnerid IS NOT NULL 
        AND d.schoollearneryearid IS NOT NULL 
        AND d.dateleft = '9999-12-31' 
        AND (((d.firstname <> p.firstname::VARCHAR) 
                OR (d.firstname IS NOT NULL AND p.firstname IS NULL)) 
                OR ((d.secondname <> p.secondname::VARCHAR) 
                OR (d.secondname IS NOT NULL AND p.secondname IS NULL) 
                OR (d.secondname IS NULL AND p.secondname IS NOT NULL)) 
                OR ((d.thirdname <> p.thirdname::VARCHAR) 
                OR (d.thirdname IS NOT NULL AND p.thirdname IS NULL) 
                OR (d.thirdname IS NULL AND p.thirdname IS NOT NULL)) 
                OR ((LOWER(d.lastname) <> LOWER(p.lastname)) 
                OR (d.lastname IS NOT NULL AND p.lastname IS NULL)) 
                OR ((d.dateofbirth <> p.birthdate) 
                OR (d.dateofbirth IS NOT NULL AND p.birthdate IS NULL)) 
                OR ((d.genderid <> p.genderid) 
                OR (d.genderid IS NOT NULL AND p.genderid IS NULL)) 
                OR ((d.ethnicityid <> p.ethnicityid) 
                OR (d.ethnicityid IS NOT NULL AND p.ethnicityid IS NULL)) 
                OR ((d.citizenshiptypeid <> p.citizenshiptypeid) 
                OR (d.citizenshiptypeid IS NOT NULL AND p.citizenshiptypeid IS NULL)) 
                OR ((d.homelanguageid <> p.homelanguageid) 
                OR (d.homelanguageid IS NOT NULL AND p.homelanguageid IS NULL))) 
        AND d.personid = p.personid                 
                    RETURNING d.personid)
                INSERT INTO "var_PersonUpdate$aws$tmp" (value)
                SELECT
                    *
                    FROM upd_stat;
                Raise notice 'Update done 1';
                GET DIAGNOSTICS var_RowCount = ROW_COUNT;
                CALL internal.pdataimportloginsertcount(par_DataImportID, 'Person updates: %d', var_RowCount);
                --CALL clickdw_internal.ptemporalstatus('dbo.Person', 1, par_Silent := 1, return_code => ptemporalstatus$ReturnCode);
                INSERT INTO internal.dataimportaudit (dataimportid, action, item, itemid, detail)
                SELECT
                    par_DataImportID, 'U', 'Person', d.value, 'Person updates'
                    FROM "var_PersonUpdate$aws$tmp" AS d;

                IF par_UpdatePersonInfoOnly <> 0 THEN
                    EXIT committran;
                END IF;
                /* create any missing classes */

                IF aws_sqlserver_ext.object_id('tempdb..#IgnoreClass') IS NULL THEN
                    CREATE TEMPORARY TABLE t$ignoreclass
                    (schoolid INTEGER,
                        gradeid INTEGER,
                        classname VARCHAR(250));

                    IF par_IgnoreClasses = 1 THEN
                        INSERT INTO t$ignoreclass (schoolid, gradeid, classname)
                        SELECT
                            schoolid, gradeid, classname
                            FROM clickdw_internal.updatelearnerfromsourcelearnerignoreclasses;
                    END IF;
                END IF;
                CREATE TEMPORARY TABLE t$newclass
                AS
                SELECT DISTINCT
                    CAST (schoolclassid AS INTEGER) AS schoolclassid, d.schoolid, d.gradeid, par_Year AS year, COALESCE(d.classname, 'Unknown') AS classname
                    FROM t$data AS d
                    LEFT OUTER JOIN t$ignoreclass AS i
                        ON i.schoolid = d.schoolid AND i.gradeid = d.gradeid AND LOWER(i.classname) = LOWER(COALESCE(d.classname, 'Unknown'))
                    WHERE schoolclassid IS NULL AND d.schoolid IS NOT NULL AND d.gradeid IS NOT NULL AND d.classname IS NOT NULL AND ((par_IgnoreClasses = 0) OR (par_IgnoreClasses = 1 AND i.schoolid IS NOT NULL));
                GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
                var_RowCount := sql$rowcount;
               
                var_LogDescription = CONCAT('New Classes: %s', var_RowCount);
               
                CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
                /* SELECT * FROM #NewClass */

                IF var_RowCount > 0 THEN
                    UPDATE t$newclass
                    SET schoolclassid = NEXTVAL('dbo.SchoolLearnerYearSequence');
                    INSERT INTO dbo.schoolclass (schoolclassid, schoolid, gradeid, year, classname, sasamsclassname)
                    SELECT
                        schoolclassid, schoolid, gradeid, Year, classname, classname
                        FROM t$newclass;
                    
                    UPDATE t$data D SET SchoolClassID = N.SchoolClassID
                                    FROM t$data D
                                   JOIN t$newclass N ON D.SchoolID = N.SchoolID AND D.GradeID = N.GradeID AND COALESCE(D.ClassName,'Unknown') = N.ClassName
                                    WHERE D.SchoolClassID IS NULL
                                        AND D.SchoolID IS NOT NULL
                                        AND D.GradeID IS NOT NULL
                                        AND D.ClassName IS NOT NULL;

                   
                END IF;
                var_MaxLastSubmissionDate = CAST ((SELECT
                    MAX(lastsubmissiondate)
                    FROM t$data) AS DATE);
                /* Learner has left school (new year, not in file) */
                
                /*
                INSERT #YearLearnerLeft
                SELECT SX.SchoolID, SLP.MappedSchoolLearnerID SchoolLearnerID, SLP.SourceLearnerID, SLY.SchoolLearnerYearID, DATEFROMPARTS(@Year-1,12,31) DateLeft, @Year-1 PrevYear
                --INTO #YearLearnerLeft
                FROM (SELECT DISTINCT SchoolID FROM #Data WHERE @FirstUsableDataMode = 1) SX
                JOIN SourceLearner SLP ON SX.SchoolID = SLP.SchoolID AND SLP.Year = @Year - 1 AND SLP.MappedSchoolLearnerID IS NOT NULL
                LEFT JOIN #Data D ON SLP.SchoolID = D.SchoolID AND SLP.SourceLearnerID = D.SourceLearnerID
                LEFT JOIN SchoolLearnerYear SLY ON SLP.MappedSchoolLearnerID = SLY.SchoolLearnerID AND SLY.[Year] = @Year
                WHERE D.SchoolID IS NULL AND SLP.DateLeft IS NULL AND SLP.MappedSchoolLearnerID IS NOT NULL
                    AND @FirstUsableDataMode = 1
                
                SET @RowCount = @@ROWCOUNT;
                IF @FirstUsableDataMode = 1 EXEC internal.pDataImportLogInsertCount par_DataImportID, 'Learner has left school (not in file): %d', @RowCount
                
                IF @RowCount > 0
                BEGIN
                    SET @Sql = N'
                    INSERT internal.DataImportAudit([DataImportID], [Action], [Item], [ItemID], [Detail])
                    SELECT par_DataImportID, ''U'', ''SchoolLearner'', C.SchoolLearnerID, FORMATMESSAGE(''Learner has left school (not in file). SchoolLearner.EndDate set: %s'',FORMAT(C.DateLeft,''yyyy-MM-dd''))
                    FROM #YearLearnerLeft C
                    JOIN SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID;
                
                    UPDATE SL SET EndDate = C.DateLeft
                    FROM #YearLearnerLeft C
                    JOIN SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID;
                
                    EXEC internal.pDataImportLogInsertCount par_DataImportID, ''Learner has left school (not in file): SchoolLearner.EndDate set: %d'', @@ROWCOUNT;
                
                    INSERT internal.DataImportAudit([DataImportID], [Action], [Item], [ItemID], [Detail])
                    SELECT par_DataImportID, ''D'', ''SchoolLearnerYear'', SLY.SchoolLearnerYearID, FORMATMESSAGE(''Learner has left school (not in file). SchoolLearnerYear in future deleted: %d'',SLY.[Year])
                    FROM #YearLearnerLeft C
                    JOIN SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID
                    JOIN SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID
                    WHERE SLY.Year > YEAR(C.DateLeft);
                
                    DELETE SLY
                    FROM #YearLearnerLeft C
                    JOIN SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID
                    JOIN SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID
                    WHERE SLY.Year > YEAR(C.DateLeft);
                
                    EXEC internal.pDataImportLogInsertCount par_DataImportID, ''Learner has left school (not in file): SchoolLearnerYear in future deleted: %d'', @@ROWCOUNT;
                
                    UPDATE SL SET DateLeft = C.DateLeft
                    FROM #YearLearnerLeft C
                    JOIN SourceLearner SL ON SL.Year = C.PrevYear AND C.SchoolLearnerID = SL.MappedSchoolLearnerID
                    '
                    EXEC internal.pTemporalStatus 'dbo.SchoolLearner', 0, null, 1
                    EXEC internal.pTemporalStatus 'dbo.SchoolLearnerYear', 0, null, 1
                    EXEC internal.pTemporalStatus 'dbo.SourceLearner', 0, null, 1
                    EXEC sp_executesql @stmt=@Sql, @Params=N'par_DataImportID bigint', par_DataImportID=par_DataImportID;
                    EXEC internal.pTemporalStatus 'dbo.SchoolLearner', 1, null, 1
                    EXEC internal.pTemporalStatus 'dbo.SchoolLearnerYear', 1, null, 1
                    EXEC internal.pTemporalStatus 'dbo.SourceLearner', 1, null, 1
                    SET @Sql=null;
                END
                
                --DROP TABLE #YearLearnerLeft
                */
                /* Learner has left school */
				RAISE NOTICE 'CREATE TEMPORARY TABLE t$learnerleft';
                CREATE TEMPORARY TABLE t$learnerleft
                AS
                SELECT
                    d.*
                    FROM t$data AS d
                    JOIN dbo.schoollearner AS sl
                        ON d.schoollearnerid = sl.schoollearnerid
                    WHERE d.dateleft <> '9999-12-31' AND sl.enddate = '9999-12-31';
                GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
                var_RowCount := sql$rowcount;
                CALL internal.pdataimportloginsertcount(par_DataImportID, 'Learner has left school: %d', var_RowCount);

                IF var_RowCount > 0 THEN
                
                INSERT INTO internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
                SELECT par_DataImportID, 'U', 'SchoolLearner', C.SchoolLearnerID, concat('Learner has left school. SchoolLearner.EndDate set: %s',concat(C.DateLeft,'yyyy-MM-dd'))
                FROM t$LearnerLeft C
                JOIN dbo.SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID;

                UPDATE dbo.SchoolLearner SL SET EndDate = C.DateLeft, EndDateSetReason = 'Source Data'
                FROM t$LearnerLeft C
                WHERE C.SchoolLearnerID = SL.SchoolLearnerID;

                CALL internal.pDataImportLogInsertCount(par_DataImportID, 'Learner has left school: SchoolLearner.EndDate set: %d', var_RowCount);

                INSERT INTO internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
                SELECT par_DataImportID, 'D', 'SchoolLearnerYear', SLY.SchoolLearnerYearID, concat('Learner has left school. SchoolLearnerYear in future deleted: %d',SLY.Year)
                FROM t$LearnerLeft C
                JOIN dbo.SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID
                JOIN dbo.SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID
                WHERE SLY.Year > EXTRACT(YEAR FROM C.DateLeft);
                
RAISE NOTICE 'Needs to replace 1';
/*                DELETE 
                FROM t$LearnerLeft C
                WHERE SLY.Year > EXTRACT(YEAR FROM C.DateLeft) in (             
                    SELECT * FROM t$LearnerLeft C
                JOIN dbo.SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID
                JOIN dbo.SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID);
                
*/              
                GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
                var_RowCount := sql$rowcount;
                CALL internal.pDataImportLogInsertCount(par_DataImportID, 'Learner has left school: SchoolLearnerYear in future deleted: %s', var_RowCount);
                  
                END IF;
                DROP TABLE t$learnerleft;
                /* Learner's DateLeft disappears */
                CREATE TEMPORARY TABLE t$learnerdateleftgone
                AS
                SELECT
                    d.*
                    FROM t$data AS d
                    JOIN dbo.schoollearner AS sl
                        ON d.schoollearnerid = sl.schoollearnerid
                    WHERE d.dateleft = '9999-12-31' AND sl.enddate <> '9999-12-31';
				
				GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
                var_RowCount := sql$rowcount;	
				
				 CREATE INDEX IF NOT EXISTS learnerdateleftgone_schoolid_idx
					ON t$learnerdateleftgone USING btree
					(schoollearnerid ASC )
					TABLESPACE pg_default; 
				
                
                CALL internal.pdataimportloginsertcount(par_DataImportID, 'Learner''s DateLeft disappeared: %d', var_RowCount);
RAISE NOTICE 'Step 1';
                IF var_RowCount > 0 THEN
RAISE NOTICE 'Step 2';                 
                INSERT INTO internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
                SELECT par_DataImportID, 'U', 'SchoolLearner', C.SchoolLearnerID, 'Learner''s DateLeft disappeared: SchoolLearner.EndDate cleared'
                FROM t$learnerdateleftgone C
                JOIN dbo.SchoolLearner SL ON C.SchoolLearnerID = SL.SchoolLearnerID;
RAISE NOTICE 'Step 3';
              UPDATE dbo.SchoolLearner SL SET enddate = '9999-12-31', enddatesetreason = 'Source Data (cleared)'
                FROM t$learnerdateleftgone C
--                 JOIN dbo.schoollearner SL ON C.schoollearnerid = SL.schoollearnerid;
				WHERE C.schoollearnerid = SL.schoollearnerid;
RAISE NOTICE 'Step 4';
                call internal.pDataImportLogInsertCount(par_DataImportID, 'Learner''s DateLeft disappeared: SchoolLearner.EndDate cleared: %d', var_RowCount);
RAISE NOTICE 'Step 5';
                UPDATE t$learnerdateleftgone  C SET SchoolLearnerYearID =  NEXTVAL('dbo.SchoolLearnerYearSequence')
--                 FROM t$learnerdateleftgone C 
--                 JOIN t$activeschoolgrade A ON C.SchoolID = A.SchoolID AND C.GradeID = A.GradeID
				FROM  t$activeschoolgrade A
--                 WHERE C.SchoolLearnerYearID IS NULL;
				WHERE  C.SchoolID = A.SchoolID AND C.GradeID = A.GradeID AND C.SchoolLearnerYearID IS NULL;
RAISE NOTICE 'Step 6';
                INSERT INTO internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
                SELECT par_DataImportID, 'C', 'SchoolLearnerYear', C.SchoolLearnerYearID, 'Learner''s DateLeft disappeared: SchoolLearnerYear inserted'
                FROM t$learnerdateleftgone C
                LEFT JOIN dbo.SchoolLearnerYear SLY ON C.SchoolLearnerYearID = SLY.SchoolLearnerYearID
                WHERE C.SchoolLearnerYearID IS NOT NULL AND SLY.SchoolLearnerYearID IS NULL;
RAISE NOTICE 'Step 7';

				INSERT into dbo.SchoolLearnerYear(SchoolLearnerID,Year,GradeID,InstructionLanguageID,SysStartTime,sysendtime,SchoolClassID,schoollearneryearid) 
                SELECT C.SchoolLearnerID, par_year, C.GradeID, C.InstructionLanguageID, make_date(par_year,1,1),C.dateleft, C.SchoolClassID,C.schoollearneryearid
                FROM t$learnerdateleftgone C
                LEFT JOIN dbo.SchoolLearnerYear SLY ON C.SchoolLearnerYearID = SLY.SchoolLearnerYearID
                WHERE C.SchoolLearnerYearID IS NOT NULL AND SLY.SchoolLearnerYearID IS NULL;
				
RAISE NOTICE 'Step 8';
				GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
                var_RowCount := sql$rowcount;
                CALL internal.pDataImportLogInsertCount(par_DataImportID, 'Learner''s DateLeft disappeared: SchoolLearnerYear inserted: %s',var_RowCount );
RAISE NOTICE 'Step 9';
                UPDATE t$data D SET 
                    SchoolLearnerYearID             = C.SchoolLearnerYearID,
                    SchoolLearnerYear_SchoolClassID = C.SchoolClassID,
                    SchoolLearnerYear_GradeID       = C.GradeID
                FROM t$learnerdateleftgone C 
                WHERE  C.ID = D.ID;
RAISE NOTICE 'Step 10';                
                  
                END IF;
                DROP TABLE t$learnerdateleftgone;
                /* Existing Learner in a new school */
				
				
RAISE NOTICE 'CREATE TEMPORARY TABLE t$learnernewschool 1';  
-- RETURN; --Testing
                CREATE TEMPORARY TABLE t$learnernewschool
                AS
                SELECT
                    x1.*
                    FROM (SELECT
                        x.*, px.otherschoolcount
                        FROM t$data AS x
                        JOIN t$activeschoolgrade AS a
                            ON x.schoolid = a.schoolid AND x.gradeid = a.gradeid
--                         CROSS JOIN LATERAL (SELECT
--                             COUNT(*) AS otherschoolcount
--                             FROM (SELECT
--                                 sl.sourcelearnerid, sl.emiscode, sl.datejoined, MAX(sl.lastsubmissiondate) AS lastsubmissiondate
--                                 FROM dbo.sourcelearner AS sl
--                                 WHERE LOWER(sl.sourcelearnerid) = LOWER(x.sourcelearnerid) AND LOWER(sl.emiscode) <> LOWER(x.emiscode) AND sl.dateleft IS NULL AND sl.year = par_Year
--                                 GROUP BY sl.sourcelearnerid, sl.emiscode, sl.datejoined) AS slx
--                             JOIN dbo.sourcelearner AS sl
--                                 ON LOWER(slx.sourcelearnerid) = LOWER(sl.sourcelearnerid) AND LOWER(slx.emiscode) = LOWER(sl.emiscode) AND slx.datejoined = sl.datejoined AND slx.lastsubmissiondate = sl.lastsubmissiondate
--                             WHERE sl.dateleft IS NULL) AS px
--                         WHERE x.schoollearnerid IS NULL AND x.dateleft = '9999-12-31') AS x
						  
						 LEFT OUTER JOIN LATERAL (
							SELECT COUNT(*) AS otherschoolcount
								FROM(
									SELECT sl.sourcelearnerid, sl.emiscode, sl.datejoined, MAX(sl.lastsubmissiondate) AS lastsubmissiondate
									FROM dbo.sourcelearner AS sl
									WHERE sl.SourceLearnerID = x.SourceLearnerID 
										AND sl.EMISCode <> x.EMISCode
										AND sl.DateLeft IS NULL
										AND sl.Year = par_Year
-- 									WHERE  sl.dateleft IS NULL AND sl.year = par_Year
									GROUP BY sl.sourcelearnerid, sl.emiscode, sl.datejoined) AS slx 
								JOIN dbo.sourcelearner AS sl
									ON LOWER(slx.sourcelearnerid) = LOWER(sl.sourcelearnerid) 
										AND LOWER(slx.emiscode) = LOWER(sl.emiscode) 
										AND slx.datejoined = sl.datejoined 
										AND slx.lastsubmissiondate = sl.lastsubmissiondate							  	
									WHERE sl.dateleft IS NULL						  			
						  ) AS px ON x.schoollearnerid IS NULL AND x.dateleft = '9999-12-31'
						) AS x1
						  
                    WHERE x1.OtherSchoolCount = 0;
					
                GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
                var_RowCount := sql$rowcount;
				RAISE NOTICE 'ROWS: %',var_RowCount;
				CALL internal.pdataimportloginsertcount(par_DataImportID, 'Existing Learner in a new school: %s', var_RowCount);

                IF par_DebugMode >= 2 THEN
                    OPEN p_refcur_2 FOR
                    SELECT
                        '#LearnerNewSchool' AS "#LearnerNewSchool", x.*
                        FROM (SELECT
                            x.*, px.otherschoolcount
                            FROM t$data AS x
                            JOIN t$activeschoolgrade AS a
                                ON x.schoolid = a.schoolid AND x.gradeid = a.gradeid
--                             CROSS JOIN LATERAL (SELECT
--                                 COUNT(*) AS otherschoolcount
--                                 FROM (SELECT
--                                     sl.sourcelearnerid, sl.emiscode, sl.datejoined, MAX(sl.lastsubmissiondate) AS lastsubmissiondate
--                                     FROM dbo.sourcelearner AS sl
--                                     WHERE LOWER(sl.sourcelearnerid) = LOWER(x.sourcelearnerid) AND LOWER(sl.emiscode) <> LOWER(x.emiscode) AND sl.dateleft IS NULL AND sl.year = par_Year
--                                     GROUP BY sl.sourcelearnerid, sl.emiscode, sl.datejoined) AS slx
--                                 JOIN dbo.sourcelearner AS sl
--                                     ON LOWER(slx.sourcelearnerid) = LOWER(sl.sourcelearnerid) AND LOWER(slx.emiscode) = LOWER(sl.emiscode) AND slx.datejoined = sl.datejoined AND slx.lastsubmissiondate = sl.lastsubmissiondate
--                                 WHERE sl.dateleft IS NULL) AS px
--                             WHERE x.schoollearnerid IS NULL AND x.dateleft = '9999-12-31') AS x
							  
							   JOIN (
								SELECT COUNT(*) AS otherschoolcount
									FROM(
										SELECT DISTINCT (sl.sourcelearnerid, sl.emiscode, sl.datejoined),sl.sourcelearnerid, sl.emiscode, sl.datejoined, MAX(sl.lastsubmissiondate) AS lastsubmissiondate
										FROM dbo.sourcelearner AS sl
										JOIN t$data AS x on LOWER(sl.sourcelearnerid) = LOWER(x.sourcelearnerid) AND LOWER(sl.emiscode) <> LOWER(x.emiscode)
										WHERE  sl.dateleft IS NULL AND sl.year = par_Year
										GROUP BY sl.sourcelearnerid, sl.emiscode, sl.datejoined) AS slx 
									JOIN dbo.sourcelearner AS sl
										ON LOWER(slx.sourcelearnerid) = LOWER(sl.sourcelearnerid) 
											AND LOWER(slx.emiscode) = LOWER(sl.emiscode) 
											AND slx.datejoined = sl.datejoined 
											AND slx.lastsubmissiondate = sl.lastsubmissiondate
									WHERE sl.dateleft IS NULL) AS px ON x.schoollearnerid IS NULL AND x.dateleft = '9999-12-31'
								) AS x
                        ORDER BY x.emiscode NULLS FIRST, x.gradeid NULLS FIRST, x.lastname NULLS FIRST, x.firstname NULLS FIRST;
                END IF;

                IF var_RowCount > 0 THEN
                 RAISE NOTICE 'UPDATE t$learnernewschool';
                UPDATE t$learnernewschool SET 
                    SchoolLearnerID     = NEXTVAL('dbo.SchoolLearnerYearSequence'), 
                    SchoolLearnerYearID = NEXTVAL('dbo.SchoolLearnerYearSequence')
                FROM t$learnernewschool L;
				
				RAISE NOTICE 'INSERT INTO internal.DataImportAuditr';
                INSERT into internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
                SELECT par_DataImportID, 'C', 'SchoolLearner', L.SchoolLearnerID, 'Existing Learner in a new school: SchoolLearner inserted'
                FROM t$learnernewschool L;

				RAISE NOTICE 'INSERT INTO dbo.SchoolLearner';
                INSERT INTO dbo.SchoolLearner(SchoolLearnerID, SchoolID, PersonID, StartDate, EndDate, SysStartTime,sysendtime)
                SELECT L.SchoolLearnerID, L.SchoolID, L.PersonID, L.DateJoined, L.DateLeft, L.DateJoined,L.DateLeft
                FROM t$learnernewschool L;

				GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
                var_RowCount := sql$rowcount;
				
                CALL internal.pDataImportLogInsertCount(par_DataImportID, 'Existing Learner in a new school: SchoolLearner inserted: %s', var_RowCount);
				
				RAISE NOTICE 'INSERT INTO internal.DataImportAudit';
                INSERT INTO internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
                SELECT par_DataImportID, 'C', 'SchoolLearnerYear', L.SchoolLearnerYearID, 'Existing Learner in a new school: SchoolLearnerYear inserted'
                FROM t$learnernewschool L;

				RAISE NOTICE 'INSERT INTO dbo.SchoolLearnerYear';
                INSERT INTO dbo.SchoolLearnerYear(SchoolLearnerYearID, SchoolLearnerID, Year, GradeID, InstructionLanguageID, SchoolClassID,sysstarttime,sysendtime)
                SELECT L.SchoolLearnerYearID, L.SchoolLearnerID, par_year, L.GradeID, L.InstructionLanguageID, L.SchoolClassID,L.DateJoined,L.DateLeft
                FROM t$learnernewschool L;

				GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
                var_RowCount := sql$rowcount;
				
                CALL internal.pDataImportLogInsertCount(par_DataImportID, 'Existing Learner in a new school: SchoolLearnerYear inserted: %s', var_RowCount);
				
				RAISE NOTICE 'UPDATE t$Data D';
				UPDATE t$Data D SET 
                    SchoolLearnerYearID = C.SchoolLearnerYearID,
                    SchoolLearnerID     = C.SchoolLearnerID,
                    SchoolLearnerIDNew  = 1::bit,
                    SchoolLearnerYear_SchoolClassID = C.SchoolClassID,
                    SchoolLearnerYear_GradeID       = C.GradeID
                FROM t$learnernewschool C 
                where C.ID = D.ID;
				
				RAISE NOTICE 'UPDATE dbo.SourceLearner';
--                 UPDATE dbo.SourceLearner SET MappedSchoolLearnerID=D.SchoolLearnerID
--                 FROM t$Data D
--                 JOIN dbo.SourceLearner X ON X.Year = par_year AND X.EMISCode = D.EMISCode AND X.DateJoined = D.DateJoined AND X.SourceLearnerID = D.SourceLearnerID
--                 WHERE D.SchoolLearnerIDNew = 1::bit ;
                UPDATE dbo.SourceLearner X SET MappedSchoolLearnerID=D.SchoolLearnerID
                FROM t$Data D
                WHERE D.SchoolLearnerIDNew = 1::bit  AND X.Year = par_year AND X.EMISCode = D.EMISCode AND X.DateJoined = D.DateJoined AND X.SourceLearnerID = D.SourceLearnerID ;
				
				GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
                var_RowCount := sql$rowcount;
				
                CALL internal.pDataImportLogInsertCount(par_DataImportID, 'Existing Learner in a new school: SourceLearner.MappeedSchoolLearnerID: %d', var_RowCount);
                
                  
                END IF;
                DROP TABLE t$learnernewschool;
                /* Existing Learner in same school but SchoolLearnerYear wasn't rolled */
				RAISE NOTICE 'CREATE TEMPORARY TABLE t$learnersameschoolnoyear';
                CREATE TEMPORARY TABLE t$learnersameschoolnoyear
                AS
                SELECT
                    d.*
                    FROM t$data AS d
                    JOIN t$activeschoolgrade AS a
                        ON d.schoolid = a.schoolid AND d.gradeid = a.gradeid
                    WHERE d.dateleft = '9999-12-31' AND d.schoollearneryearid IS NULL AND d.schoollearnerid IS NOT NULL;
					
                GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
                var_RowCount := sql$rowcount;
                CALL internal.pdataimportloginsertcount(par_DataImportID, 'Learners have no SchoolLearnerYear record: %d', var_RowCount);
				
				RAISE NOTICE 'var_RowCount: %',var_RowCount;
                
				IF var_RowCount > 0 THEN
                
				RAISE NOTICE 'UPDATE t$learnersameschoolnoyear';  
                
				UPDATE t$learnersameschoolnoyear SET SchoolLearnerYearID = NEXTVAL('dbo.SchoolLearnerYearSequence')
                FROM t$learnersameschoolnoyear C; 
				
				RAISE NOTICE 'INSERT into internal.DataImportAudit';
                
				INSERT into internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
                SELECT par_DataImportID, 'C', 'SchoolLearnerYear', C.SchoolLearnerYearID, 'Learners have no SchoolLearnerYear record'
                FROM t$learnersameschoolnoyear C;
				
				RAISE NOTICE 'INSERT into dbo.SchoolLearnerYear';
                
				INSERT into dbo.SchoolLearnerYear(SchoolLearnerID,Year,GradeID,InstructionLanguageID,SysStartTime,sysendtime,SchoolClassID,schoollearneryearid)
                SELECT C.SchoolLearnerID, par_year, C.GradeID, C.InstructionLanguageID, make_date(par_year,1,1),C.dateleft, C.SchoolClassID,C.schoollearneryearid
                FROM t$learnersameschoolnoyear C;
				
				GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
                var_RowCount := sql$rowcount;
                
				call internal.pDataImportLogInsertCount(par_DataImportID, 'Learners have no SchoolLearnerYear record: SchoolLearnerYear inserted: %s', var_rowcount);
				
				RAISE NOTICE 'UPDATE t$data';
                
				UPDATE t$data D SET 
                    SchoolLearnerYearID             = C.SchoolLearnerYearID,
                    SchoolLearnerYear_SchoolClassID = C.SchoolClassID,
                    SchoolLearnerYear_GradeID       = C.GradeID
                FROM t$learnersameschoolnoyear C 
                WHERE C.ID = D.ID;
       
                END IF;
                DROP TABLE t$learnersameschoolnoyear;
                /* Update class / grade changes */
                CREATE TEMPORARY TABLE t$classchange
                AS
                SELECT
                    d.*
                    FROM t$data AS d
                    /* JOIN #ActiveSchoolGrade A ON D.SchoolID = A.SchoolID AND D.GradeID = A.GradeID */
                    WHERE d.schoollearneryearid IS NOT NULL 
							AND (((d.gradeid <> d.schoollearneryear_gradeid) 
								  	OR (d.gradeid IS NOT NULL AND d.schoollearneryear_gradeid IS NULL)) 
								 	OR ((d.schoolclassid <> d.schoollearneryear_schoolclassid) 
									OR (d.schoolclassid IS NOT NULL AND d.schoollearneryear_schoolclassid IS NULL)));
					
					
                GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
                var_RowCount := sql$rowcount;
                CALL internal.pdataimportloginsertcount(par_DataImportID, 'Class/grade changes: %d', var_RowCount);
					
-- 				CREATE INDEX IF NOT EXISTS classchange_SchoolLearnerYearID_idx
-- 					ON t$classchange USING btree
-- 					(SchoolLearnerYearID ASC NULLS LAST)
-- 					TABLESPACE pg_default;
					
				RAISE NOTICE 'Add data import audit';
				
                IF var_RowCount > 0 THEN
                  
                                    INSERT INTO internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
                                    SELECT par_DataImportID, 'U', 'SchoolLearnerYear', D.SchoolLearnerYearID, FORMAT('Class/grade changes: SchoolLearnerYear updated: %s', D.ClassName)
                                    FROM t$classchange D
                                    JOIN dbo.SchoolLearnerYear SLY ON D.SchoolLearnerYearID = SLY.SchoolLearnerYearID;
									
						RAISE NOTICE 'Update dbo.SchoolLearnerYear';

                                    UPDATE dbo.SchoolLearnerYear SLY SET SchoolClassID = D.SchoolClassID, GradeID = D.GradeID
                                    FROM t$classchange D
                                    WHERE D.SchoolLearnerYearID = SLY.SchoolLearnerYearID;
									
   						 RAISE NOTICE 'Done Update dbo.SchoolLearnerYear';
                                    --EXEC internal.pDataImportLogInsertCount par_DataImportID, ''Class/grade changes: SchoolLearnerYear updated: %d'', @@ROWCOUNT
                                    
                   
                    -- CALL clickdw_internal.ptemporalstatus('dbo.SchoolLearnerYear', 0, NULL, 1, return_code => ptemporalstatus$ReturnCode);
                    /*
                    [7672 - Severity CRITICAL - Automatic conversion of this command is not supported. Perform a manual conversion.]
                    EXEC sp_executesql @stmt=@Sql, @Params=N'par_DataImportID bigint', par_DataImportID=par_DataImportID
                    */
                    -- CALL clickdw_internal.ptemporalstatus('dbo.SchoolLearnerYear', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
                    var_Sql := NULL;
                END IF;
                DROP TABLE t$classchange;
            END IF;
            /* PersonHasProgramme ending (left school) */
			RAISE NOTICE 'CREATE TEMPORARY TABLE t$phpend';
            CREATE TEMPORARY TABLE t$phpend
            AS
            SELECT DISTINCT
                pp.personid, pp.programmeid, p.name AS programme, ppa.firstappeardate, ppa.lastmovementdate, sl.enddate, pph.sysendtime, pp.sysstarttime,
                CASE
                    WHEN ppa.lastmovementdate IS NULL THEN pp.sysstarttime + (1::NUMERIC || ' SECOND')::INTERVAL
                    WHEN ppa.lastmovementdate < var_CutoffDate THEN ppa.lastmovementdate
                /* WHEN PPA.LastMovementDate >= @CutoffDate THEN YearEnd  (need to set in annual rollover)  DateLeft for these learners should still be not set. */
                END AS newsysendtime
                FROM (SELECT DISTINCT
                    schoollearnerid
                    FROM (SELECT
                        schoollearnerid
                        FROM t$data
                    /* UNION SELECT SchoolLearnerID FROM #YearLearnerLeft */
                    UNION
                    SELECT
                        schoollearnerid
                        FROM t$previousdata) AS d) AS d
                JOIN dbo.schoollearner AS sl
                    ON d.schoollearnerid = sl.schoollearnerid
                JOIN dbo.personhasprogramme AS pp
                    ON sl.personid = pp.personid
                JOIN t$programmeslist AS p
                    ON pp.programmeid = p.programmeid
                LEFT OUTER JOIN dbo.personprogrammeactivity AS ppa
                    ON pp.personid = ppa.personid AND pp.programmeid = ppa.programmeid
				
				CROSS JOIN LATERAL (SELECT
                    COUNT(*) AS otherschoolcount
                    FROM dbo.schoollearner AS slo
                    JOIN dbo.schoollearneryear AS slyo
                        ON slo.schoollearnerid = slyo.schoollearnerid AND slyo.year = par_Year
                    WHERE slo.personid = sl.personid AND slo.schoollearnerid <> sl.schoollearnerid AND slo.enddate = '9999-12-31') AS slo
				
-- 				 JOIN (
-- 						SELECT DISTINCT (slo.personid,slo.schoollearnerid,slo.enddate),
--                     		slo.personid,slo.schoollearnerid,slo.enddate,COUNT(*) AS otherschoolcount
--                     	FROM dbo.schoollearner AS slo
--                     	JOIN dbo.schoollearneryear AS slyo
--                         	ON slo.schoollearnerid = slyo.schoollearnerid AND slyo.year = par_Year
-- 					   GROUP BY slo.personid,slo.schoollearnerid,slo.enddate
--                     	) AS slo
-- 							ON slo.personid = sl.personid AND slo.schoollearnerid <> sl.schoollearnerid AND slo.enddate = '9999-12-31'
				
                CROSS JOIN LATERAL (SELECT
                    *
                    FROM dbo.personhasprogrammehistory
                    WHERE personid = pp.personid AND programmeid = pp.programmeid
                    ORDER BY sysendtime DESC NULLS FIRST
                    LIMIT 1) AS pph
				
-- 				JOIN (
-- 					SELECT DISTINCT (personid,programmeid,sysendtime),*
-- 					FROM dbo.personhasprogrammehistory
-- 					ORDER BY sysendtime DESC NULLS FIRST
-- 				) AS 	pph ON pph.personid = pp.personid AND pph.programmeid = pp.programmeid
                WHERE sl.enddate <> '9999-12-31' AND slo.OtherSchoolCount = 0 AND (ppa.lastmovementdate IS NULL OR ppa.lastmovementdate < var_CutoffDate);
				GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
                var_RowCount = sql$rowcount;
RAISE NOTICE 't$phpend';                                
          	
			var_LogDescription = 'PersonHasProgramme ending (left school)';
			
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
          
            INSERT INTO internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
                    SELECT par_DataImportID, 'D', 'PersonHasProgramme', PP.PersonID, FORMAT('PersonHasProgramme Ending (left school): %s', X.Programme)
                    FROM dbo.PersonHasProgramme PP
                    JOIN t$phpend X ON PP.PersonID = X.PersonID AND PP.ProgrammeID = X.ProgrammeID;
          
            INSERT INTO internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
            SELECT par_DataImportID, 'D', 'PersonHasProgramme', PP.PersonID, CONCAT('PersonHasProgramme Ending (left school): ', X.Programme)
            FROM dbo.PersonHasProgramme PP
            JOIN t$phpend X ON PP.PersonID = X.PersonID AND PP.ProgrammeID = X.ProgrammeID;
			
RAISE NOTICE 'DataImportAudit1';        
            var_TimeHack := (timezone('UTC', CURRENT_TIMESTAMP(6)))::TIMESTAMP + (- 1::NUMERIC || ' MINUTE')::INTERVAL;
RAISE NOTICE 'var_TimeHack: %', var_TimeHack;   

            DELETE FROM dbo.personhasprogramme AS PP
            USING t$phpend AS x
                WHERE pp.personid = x.personid AND pp.programmeid = x.programmeid;
				
RAISE NOTICE 'personhasprogramme1';             
/*            var_Sql := '
        UPDATE PP SET SysEndTime = CASE WHEN X.NewSysEndTime < X.SysStartTime THEN X.SysStartTime ELSE X.NewSysEndTime END
        FROM PersonHasProgrammeHistory PP
        JOIN #PHPEND X ON PP.PersonID = X.PersonID AND PP.ProgrammeID = X.ProgrammeID
        WHERE X.SysEndTime >= @TimeHack;
        ';
*/      
        EXECUTE 'UPDATE dbo.PersonHasProgrammeHistory 
            SET SysEndTime = CASE WHEN X.NewSysEndTime < X.SysStartTime THEN X.SysStartTime ELSE X.NewSysEndTime END
            FROM dbo.PersonHasProgrammeHistory PP
            JOIN t$phpend X ON PP.PersonID = X.PersonID AND PP.ProgrammeID = X.ProgrammeID
            WHERE X.SysEndTime >= $1'
        USING var_TimeHack;
		
RAISE NOTICE 'EXECUTE PersonHasProgrammeHistory 1a';
        
            --CALL clickdw_internal.ptemporalstatus('dbo.PersonHasProgramme', 0, NULL, 1, return_code => ptemporalstatus$ReturnCode);
            /*
            [7672 - Severity CRITICAL - Automatic conversion of this command is not supported. Perform a manual conversion.]
            EXEC sp_executesql @stmt=@Sql, @params=N'@TimeHack datetime', @TimeHack=@TimeHack;
            */
            --CALL clickdw_internal.ptemporalstatus('dbo.PersonHasProgramme', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            var_Sql := NULL;
            DROP TABLE IF EXISTS t$phpend;
            /* PersonHasProgramme starting */
           
        --INSERT INTO t$phpstart
        CREATE TEMPORARY TABLE t$phpstart
        AS
        SELECT X.PersonID, X.ProgrammeID, P.Name Programme, H.UserName, COALESCE(H.UserID,'1') UserID, H.UserNameDate, H.RegisterDate, X.StartDate, X.SysStartTime SchoolLearnerSysStartTime, H.SysStartTime, H.SysEndTime, PPA.LastAppearDate, PPA.LastMovementDate,
            CASE
                WHEN ID.ExternalID IS NULL OR ID.ExternalID = '' THEN 0
                WHEN PPA.LastAppearDate IS NULL THEN 0
                --WHEN DATEDIFF(week, ISNULL(PPENDED.AuditTimestamp,H.SysEndTime), GETDATE()) >= 4 THEN 0
                WHEN DATE_PART('day',NOW() - PPA.LastAppearDate) <= 6 THEN 1
                ELSE 0
            END InLatestFile,
            CASE
                WHEN (ID.ExternalID IS NULL OR ID.ExternalID = '') AND H.SysEndTime >= var_StartDate THEN 0
                WHEN (ID.ExternalID IS NULL OR ID.ExternalID = '') AND (H.SysEndTime IS NULL OR H.SysEndTime < var_StartDate) THEN 1
                WHEN H.SysEndTime IS NOT NULL AND TRUNC(DATE_PART('day', NOW() - PPENDED.AuditTimestamp)/7) < 4 THEN 0
                WHEN H.SysEndTime IS NOT NULL AND ABS(TRUNC(DATE_PART('day', var_LastYearEndDate - H.sysEndTime)/7)) < 2 AND (DATE_PART('year', NOW()::date) - DATE_PART('year', var_LastYearEndDate::date)) * 12 + (DATE_PART('month', NOW()::date) - DATE_PART('month', var_LastYearEndDate::date)) <= 1 THEN 0
                WHEN par_FirstUsableDataMode = 1 THEN 0
                ELSE 1
            END StartNewPeriod,
            X.SchoolLearnerID,
            ID.ExternalID ProgrammeStudentID        
        FROM
        (
            SELECT DISTINCT SL.PersonID, TG.ProgrammeID, SL.StartDate, SL.SysStartTime, SL.SchoolLearnerID
            FROM dbo.SchoolLearner SL
            JOIN dbo.SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID AND SLY.Year = par_Year
            JOIN t$SchoolGradeProgrammeAuto TG ON SL.SchoolID = TG.SchoolID AND SLY.GradeID = TG.GradeID AND TG.Automatic = 1
            LEFT JOIN dbo.PersonHasProgramme PP ON SL.PersonID = PP.PersonID AND TG.ProgrammeID = PP.ProgrammeID
            JOIN dbo.SourceLearner X ON SL.SchoolLearnerID = X.MappedSchoolLearnerID AND X.Year = par_Year
            WHERE PP.PersonID IS NULL AND SL.EndDate = '9999-12-31'
        ) X
        JOIN t$ProgrammesList P ON X.ProgrammeID = P.ProgrammeID
	
		CROSS JOIN LATERAL
        (
            SELECT
                SysStartTime,
                SysEndTime,
                UserName,
                UserID,
                UserNameDate,
                RegisterDate
            FROM dbo.PersonHasProgrammeHistory H
            WHERE H.PersonID = X.PersonID AND H.ProgrammeID = X.ProgrammeID
            ORDER BY H.SysEndTime DESC
            LIMIT 1
        ) H
	
-- 		JOIN (
-- 			SELECT DISTINCT (PersonID,ProgrammeID),
-- 				PersonID,
-- 				ProgrammeID,
-- 			 	SysStartTime,
--                 SysEndTime,
--                 UserName,
--                 UserID,
--                 UserNameDate,
--                 RegisterDate
--             FROM dbo.PersonHasProgrammeHistory H
-- 			ORDER BY H.SysEndTime DESC
-- 		) H ON  H.PersonID = X.PersonID AND H.ProgrammeID = X.ProgrammeID
        LEFT JOIN dbo.PersonProgrammeActivity PPA ON X.PersonID = PPA.PersonID AND X.ProgrammeID = PPA.ProgrammeID
        LEFT JOIN dbo.PersonExternalIDType IDT ON P.ProgrammeID = IDT.ProgrammeID
        LEFT JOIN dbo.PersonExternalID ID ON X.PersonID = ID.PersonID AND ID.PersonExternalIDType = IDT.PersonExternalIDType
        
		CROSS JOIN LATERAL
        (
            SELECT A.AuditTimestamp
            FROM internal.DataImportAudit A
            WHERE A.Item = 'PersonHasProgramme' AND A.Action = 'D' AND A.ItemID = X.PersonID AND A.Detail LIKE CONCAT('PersonHasProgramme Ending %%:', P.Name)
            ORDER BY A.AuditTimestamp DESC
            LIMIT 1
        ) PPENDED
		
-- 		JOIN (
-- 			SELECT DISTINCT (Item,Action,ItemID,Detail),
-- 				Item,
-- 				Action,
-- 				ItemID,
-- 				Detail,
-- 				A.AuditTimestamp
-- 			 FROM internal.DataImportAudit A
-- 			 ORDER BY A.AuditTimestamp DESC
-- 		) PPENDED ON PPENDED.Item = 'PersonHasProgramme' AND PPENDED.Action = 'D' AND PPENDED.ItemID = X.PersonID AND PPENDED.Detail LIKE CONCAT('PersonHasProgramme Ending %%:', P.Name)
        WHERE par_firstusabledatamode = 0;   
RAISE NOTICE 't$phpstart 1';      
           
 
            IF par_DebugMode >= 2 THEN
                OPEN p_refcur_3 FOR
                SELECT
                    '#PHPSTART' AS "#PHPSTART", *
                    FROM t$phpstart
                    ORDER BY personid NULLS FIRST;
RAISE NOTICE 't$phpstart 2';                     
            END IF;
            INSERT INTO internal.dataimportaudit (dataimportid, action, item, itemid, detail)
            SELECT
                par_DataImportID, 'C', 'PersonHasProgramme', x.personid, 'Restore: ' || x.programme
                FROM t$phpstart AS x
                WHERE x.startnewperiod = 0;
            /* SELECT * FROM #PHPSTART; */
            var_TimeHack := (timezone('UTC', CURRENT_TIMESTAMP(6)))::TIMESTAMP + (- 1::NUMERIC || ' MINUTE')::INTERVAL;
RAISE NOTICE 'var_TimeHack: %', var_TimeHack;              
/*
            var_Sql := '
        DECLARE @LogDescription varchar(4000);

        SELECT @LogDescription=STRING_AGG(FORMATMESSAGE(''%s: %d'', Programme, Count),'', '') FROM (SELECT X.Programme, COUNT(*) Count FROM #PHPSTART X WHERE X.StartNewPeriod = 0 GROUP BY X.Programme) X

        INSERT PersonHasProgramme
        ([PersonID], [ProgrammeID], [UserName], [Password], [RegisterDate], [SysStartTime], [SysEndTime], [UserNameDate], [UserID])
        SELECT DISTINCT
            X.PersonID, 
            X.ProgrammeID, 
            CASE WHEN X.InLatestFile = 1 THEN X.UserName ELSE NULL END, 
            NULL [Password],
            X.RegisterDate, 
            X.SysStartTime,
            ''9999-12-31 23:59:59'',
            CASE WHEN X.InLatestFile = 1 THEN X.UserNameDate ELSE NULL END,
            X.UserID
        FROM #PHPSTART X
        WHERE X.StartNewPeriod = 0;

        SET @LogDescription = FORMATMESSAGE(''PersonHasProgramme Starting: Restore: %d   [%s]'', @@ROWCOUNT, @LogDescription);
        EXEC internal.pDataImportLogInsert par_DataImportID, @LogDescription

        DELETE PP
        FROM PersonHasProgrammeHistory PP
        JOIN #PHPSTART X ON PP.PersonID = X.PersonID AND PP.ProgrammeID = X.ProgrammeID AND PP.SysStartTime = X.SysStartTime
        WHERE X.StartNewPeriod = 0;
        ';
*/    

            EXECUTE 'INSERT INTO dbo.PersonHasProgramme
                    (PersonID, ProgrammeID, UserName, Password, RegisterDate, SysStartTime, SysEndTime, UserNameDate, UserID)
                    SELECT DISTINCT
                        X.PersonID, 
                        X.ProgrammeID, 
                        CASE WHEN X.InLatestFile = 1 THEN X.UserName ELSE NULL END, 
                        NULL AS Password,
                        X.RegisterDate, 
                        X.SysStartTime,
                        to_timestamp(''9999-12-31 23:59:59'', ''DD-MM-YYYY hh24:mi:s'')::timestamp without time zone,
                        CASE WHEN X.InLatestFile = 1 THEN X.UserNameDate ELSE NULL END,
                        X.UserID
                    FROM t$phpstart X
                    WHERE X.StartNewPeriod = 0'
            USING par_DataImportID;                             
            RAISE NOTICE 'EXECUTE PersonHasProgramme 1a'; 

            EXECUTE 'DELETE
                    FROM 
                        dbo.PersonHasProgrammeHistory PP
                    USING   
                        t$phpstart X
                    WHERE PP.PersonID = X.PersonID 
                        AND PP.ProgrammeID = X.ProgrammeID 
                        AND PP.SysStartTime = X.SysStartTime
                        AND X.StartNewPeriod = 0'
            USING par_DataImportID;                             
            RAISE NOTICE 'EXECUTE PersonHasProgramme 1b';         

            --CALL clickdw_internal.ptemporalstatus('dbo.PersonHasProgramme', 0, NULL, 1, return_code => ptemporalstatus$ReturnCode);
            /*
            [7672 - Severity CRITICAL - Automatic conversion of this command is not supported. Perform a manual conversion.]
            --EXEC sp_executesql @Stmt=@Sql, @Params=N'par_DataImportID bigint', par_DataImportID=par_DataImportID
            */
            --CALL clickdw_internal.ptemporalstatus('dbo.PersonHasProgramme', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            var_Sql := NULL;
            INSERT INTO internal.dataimportaudit (dataimportid, action, item, itemid, detail)
            SELECT
                par_DataImportID, 'C', 'PersonHasProgramme', x.personid, 'Create: ' || x.programme
                FROM t$phpstart AS x
                WHERE x.startnewperiod = 1 AND x.sysendtime IS NULL;

/*
            var_Sql := '
        DECLARE @LogDescription varchar(4000);
        SELECT @LogDescription=STRING_AGG(FORMATMESSAGE(''%s: %d'', Programme, Count),'', '') FROM (SELECT X.Programme, COUNT(*) Count FROM #PHPSTART X WHERE X.StartNewPeriod = 1 AND X.SysEndTime IS NULL GROUP BY X.Programme) X

        INSERT PersonHasProgramme
        ([PersonID], [ProgrammeID], [UserName], [Password], [RegisterDate], [SysStartTime], [SysEndTime], [UserNameDate], [UserID])
        SELECT 
            X.PersonID, 
            X.ProgrammeID, 
            X.UserName, 
            NULL [Password],
            ISNULL(X.RegisterDate,GETDATE()), 
            CAST(GETUTCDATE() AS date), /*@StartDate,*/ /*X.SchoolLearnerSysStartTime,*/
            ''9999-12-31 23:59:59'',
            X.UserNameDate,
            X.UserID
        FROM #PHPSTART X
        WHERE X.StartNewPeriod = 1 AND X.SysEndTime IS NULL

        SET @LogDescription = FORMATMESSAGE(''PersonHasProgramme Starting: Create: %d   [%s]'', @@ROWCOUNT, @LogDescription);
        EXEC internal.pDataImportLogInsert par_DataImportID, @LogDescription
        ';
*/

            INSERT into dbo.PersonHasProgramme
            (PersonID, ProgrammeID, UserName, Password, RegisterDate, SysStartTime, SysEndTime, UserNameDate, UserID)
            SELECT 
                X.PersonID, 
                X.ProgrammeID, 
                X.UserName, 
                NULL AS Password,
                COALESCE(X.RegisterDate,NOW()), 
                now() at time zone 'utc' AS date, /*@StartDate,*/ /*X.SchoolLearnerSysStartTime,*/
                to_timestamp('9999-12-31 23:59:59', 'DD-MM-YYYY hh24:mi:s')::timestamp without time zone,
                X.UserNameDate,
                X.UserID
            FROM t$phpstart X
            WHERE X.StartNewPeriod = 1 AND X.SysEndTime IS NULL;

RAISE NOTICE 'EXECUTE PersonHasProgramme 1c';         

            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);                

            --CALL clickdw_internal.ptemporalstatus('dbo.PersonHasProgramme', 0, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            /*
            [7672 - Severity CRITICAL - Automatic conversion of this command is not supported. Perform a manual conversion.]
            --EXEC sp_executesql @Stmt=@Sql, @Params=N'par_DataImportID bigint, @StartDate datetime', par_DataImportID=par_DataImportID, @StartDate=@StartDate
            */
            --CALL clickdw_internal.ptemporalstatus('dbo.PersonHasProgramme', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            var_Sql := NULL;

            INSERT INTO internal.dataimportaudit (dataimportid, action, item, itemid, detail)
            SELECT
                par_DataImportID, 'C', 'PersonHasProgramme', x.personid,
                CASE
                    WHEN x.inlatestfile = 1 THEN 'Restart: ' || x.programme
                    ELSE 'Create: ' || x.programme
                END
                FROM t$phpstart AS x
                WHERE x.startnewperiod = 1 AND x.sysendtime IS NOT NULL;
RAISE NOTICE 'internal.dataimportaudit';  

/*                
            var_Sql := '
        DECLARE @LogDescription1 varchar(4000), @LogDescription2 varchar(4000)
        SELECT @LogDescription1=STRING_AGG(FORMATMESSAGE(''%s: %d'', Programme, Count),'', '') FROM (SELECT X.Programme, COUNT(*) Count FROM #PHPSTART X WHERE X.StartNewPeriod = 1 AND X.SysEndTime IS NOT NULL AND X.InLatestFile=1 GROUP BY X.Programme) X
        SELECT @LogDescription2=STRING_AGG(FORMATMESSAGE(''%s: %d'', Programme, Count),'', '') FROM (SELECT X.Programme, COUNT(*) Count FROM #PHPSTART X WHERE X.StartNewPeriod = 1 AND X.SysEndTime IS NOT NULL AND X.InLatestFile=0 GROUP BY X.Programme) X

        INSERT PersonHasProgramme
        ([PersonID], [ProgrammeID], [UserName], [Password], [RegisterDate], [SysStartTime], [SysEndTime], [UserNameDate], [UserID])
        SELECT DISTINCT
            X.PersonID, 
            X.ProgrammeID, 
            CASE WHEN X.InLatestFile = 1 THEN X.UserName ELSE NULL END, 
            NULL [Password],
            X.RegisterDate, 
            CAST(GETUTCDATE() AS date), /*CASE WHEN @StartDate < X.SysEndTime THEN X.SysEndTime ELSE @StartDate END,*/
            ''9999-12-31 23:59:59'',
            CASE WHEN X.InLatestFile = 1 THEN X.UserNameDate ELSE NULL END,
            X.UserID
        FROM #PHPSTART X
        WHERE X.StartNewPeriod = 1 AND X.SysEndTime IS NOT NULL

        DECLARE @RowCount int;
        SET @RowCount = (SELECT COUNT(*) FROM #PHPSTART X WHERE X.StartNewPeriod = 1 AND X.SysEndTime IS NOT NULL AND X.InLatestFile = 1)
        SET @LogDescription1 = FORMATMESSAGE(''PersonHasProgramme Starting: Restart: %d   [%s]'', @RowCount, @LogDescription1);
        EXEC internal.pDataImportLogInsert par_DataImportID, @LogDescription1

        SET @RowCount = (SELECT COUNT(*) FROM #PHPSTART X WHERE X.StartNewPeriod = 1 AND X.SysEndTime IS NOT NULL AND X.InLatestFile = 0)
        SET @LogDescription2 = FORMATMESSAGE(''PersonHasProgramme Starting: Create: %d   [%s]'', @RowCount, @LogDescription2);
        EXEC internal.pDataImportLogInsert par_DataImportID, @LogDescription2
        ';
*/        

        INSERT INTO dbo.PersonHasProgramme
        (PersonID, ProgrammeID, UserName, Password, RegisterDate, SysStartTime, SysEndTime, UserNameDate, UserID)
        SELECT DISTINCT
            X.PersonID, 
            X.ProgrammeID, 
            CASE WHEN X.InLatestFile = 1 THEN X.UserName ELSE NULL END, 
            NULL AS Password,
            X.RegisterDate, 
            now() at time zone 'utc' AS date, /*CASE WHEN @StartDate < X.SysEndTime THEN X.SysEndTime ELSE @StartDate END,*/
            to_timestamp('9999-12-31 23:59:59', 'DD-MM-YYYY hh24:mi:s')::timestamp without time zone,
            CASE WHEN X.InLatestFile = 1 THEN X.UserNameDate ELSE NULL END,
            X.UserID
        FROM  t$phpstart X
        WHERE X.StartNewPeriod = 1 AND X.SysEndTime IS NOT NULL;

RAISE NOTICE 'EXECUTE PersonHasProgramme 1d';         

            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);     

            --CALL clickdw_internal.ptemporalstatus('dbo.PersonHasProgramme', 0, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            /*
            [7672 - Severity CRITICAL - Automatic conversion of this command is not supported. Perform a manual conversion.]
            --EXEC sp_executesql @Stmt=@Sql, @Params=N'par_DataImportID bigint, @StartDate datetime', par_DataImportID=par_DataImportID, @StartDate=@StartDate;
            */
            --CALL clickdw_internal.ptemporalstatus('dbo.PersonHasProgramme', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            var_Sql := NULL;
            /*
            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]

            SET @Sql = N'
                    DECLARE @LogDescription varchar(4000);
            
                    UPDATE PT SET UserName = PF.UserName, UserNameDate = PF.UserNameDate
                    FROM
                    (
                        SELECT CAST(Value AS smallint) SyncFromProgrammeID, CAST(Value1 AS smallint) SyncToProgrammeID
                        FROM SystemParameter
                        WHERE Category = ''Processing'' AND SubCategory = ''Learners'' AND [Name] = ''Sync Username''
                    ) S
                    JOIN PersonHasProgramme PF ON S.SyncFromProgrammeID = PF.ProgrammeID
                    JOIN PersonHasProgramme PT ON PF.PersonID = PT.PersonID AND S.SyncToProgrammeID = PT.ProgrammeID
                    WHERE PF.UserName IS NOT NULL AND PT.UserName IS NULL
            
                    SET @LogDescription = FORMATMESSAGE(''PersonHasProgramme Starting: Syncing UserName: %d   [%s]'', @@ROWCOUNT, @LogDescription);
                    EXEC internal.pDataImportLogInsert par_DataImportID, @LogDescription
                    '
            */

            UPDATE dbo.PersonHasProgramme 
            SET UserName = PF.UserName, UserNameDate = PF.UserNameDate
            FROM
            (
                SELECT CAST(Value AS smallint) SyncFromProgrammeID, CAST(Value1 AS smallint) SyncToProgrammeID
                FROM dbo.SystemParameter
                WHERE Category = 'Processing' AND SubCategory = 'Learners' AND Name = 'Sync Username'
            ) S
            JOIN dbo.PersonHasProgramme PF ON S.SyncFromProgrammeID = PF.ProgrammeID
            JOIN dbo.PersonHasProgramme PT ON PF.PersonID = PT.PersonID AND S.SyncToProgrammeID = PT.ProgrammeID
            WHERE PF.UserName IS NOT NULL AND PT.UserName IS NULL;

RAISE NOTICE 'EXECUTE PersonHasProgramme 1e';   

            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

            --CALL clickdw_internal.ptemporalstatus('dbo.PersonHasProgramme', 0, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            --EXECUTE CONCAT_WS('', 'DO $bodydynamic$ ', par_stmt := var_Sql, ' $bodydynamic$ ') USING par_DataImportID;
            --CALL clickdw_internal.ptemporalstatus('dbo.PersonHasProgramme', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            var_Sql := NULL;
            /* Clear PersonExternalID for RE if necessary */
RAISE NOTICE 'Check 1a';     
/* Need to check and change       
            OPEN crsr_clearid;
            var_LogDescription := '';
            var_RowCount := 0;
            FETCH NEXT FROM crsr_clearid INTO var_crsr_clearid_PersonExternalIDType, var_crsr_clearid_ProgrammeID, var_crsr_clearid_Programme, var_crsr_clearid_LearnerDataTable;

            WHILE (CASE FOUND::INT
                WHEN 0 THEN - 1
                ELSE 0
            END) = 0 LOOP
                var_Sql := '
            DECLARE @MaxDate date = (SELECT MAX([Date]) FROM {LEARNERDATATABLE});
         

            DELETE ID
            FROM #PHPSTART X
            JOIN PersonExternalID ID ON X.PersonID = ID.PersonID AND ID.PersonExternalIDType = @PersonExternalIDType
            LEFT JOIN {LEARNERDATATABLE} LD ON X.PersonID = LD.PersonID AND LD.[Date] = @MaxDate
            WHERE LD.PersonID IS NULL;

            DECLARE @ThisRowCount int = @@ROWCOUNT;
            SET @LogDescription = @LogDescription + FORMATMESSAGE('', %s: %d'', @Programme, @ThisRowCount);
            SET @RowCount = @RowCount + @ThisRowCount;
            ';
                var_Sql := REPLACE(var_Sql, '{LEARNERDATATABLE}', var_crsr_clearid_LearnerDataTable);
                /*
                [7672 - Severity CRITICAL - Automatic conversion of this command is not supported. Perform a manual conversion.]
                EXEC sp_executesql @Stmt=@Sql, @Params=N'@PersonExternalIDType tinyint, @Programme varchar(50), @LogDescription varchar(4000) OUTPUT, @RowCount int OUTPUT',
                                @PersonExternalIDType=@crsr_clearid_PersonExternalIDType, @Programme=@crsr_clearid_Programme, @LogDescription=@LogDescription OUTPUT, @RowCount=@RowCount OUTPUT;
                */
                var_Sql := NULL;
                FETCH NEXT FROM crsr_clearid INTO var_crsr_clearid_PersonExternalIDType, var_crsr_clearid_ProgrammeID, var_crsr_clearid_Programme, var_crsr_clearid_LearnerDataTable;
            END LOOP;
            CLOSE crsr_clearid;
*/            
RAISE NOTICE 'Check 1b';

            IF LOWER(var_LogDescription) <> LOWER('') THEN
                var_LogDescription := SUBSTR(var_LogDescription, 2, 4000);
            END IF;
            /*
            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
            SET @LogDescription = FORMATMESSAGE('PersonExternalID deleted: %d   [%s]', @RowCount, @LogDescription);
            */
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
            DROP TABLE if exists t$phpstart;
            /* End PersonHasProgramme because grade not auto */
            
    	RAISE NOTICE 'CREATE TEMPORARY TABLE t$phpenda';
		RAISE NOTICE 'var_LastYearEndDate: %',var_LastYearEndDate;
		RAISE NOTICE 'par_Year: %',par_Year;
-- 		RETURN; --Testing
--         CREATE TEMPORARY TABLE t$phpenda
--         AS
--         SELECT X.*, P.Name Programme
--         FROM
--         (
--             SELECT DISTINCT
--                 D.PersonID, PP.ProgrammeID, PPA.LastMovementDate, PPA.LastAppearDate, PP.SysStartTime,
--                 MAKE_DATE(par_Year,1,1) YearStartDate,
--                 --CAST(CAST('2020-12-31' AS date) AS datetime) + CAST('23:59:59' AS datetime) LastYearEndDate,
--                 -- TO_TIMESTAMP(CONCAT(var_LastYearEndDate,' ','23:59:59'), 'YYYY-MM-DD HH:MI:SS') AS LastYearEndDate,
--                 CONCAT(var_LastYearEndDate::timestamp without time zone,' ','23:59:59') AS LastYearEndDate,
--                 H.SysStartTime HistorySysStartTime,
--                 D.SchoolLearnerID,
--                 D.SchoolID,
--                 D.GradeID,
--                 O.Count OtherSchoolCount
--             FROM t$data D
--             JOIN dbo.PersonHasProgramme PP ON D.PersonID = PP.PersonID
--             JOIN dbo.SchoolLearner SL ON D.SchoolLearnerID = SL.SchoolLearnerID
--             JOIN dbo.SourceLearner X ON SL.SchoolLearnerID = X.MappedSchoolLearnerID AND X.Year = par_Year
--             LEFT JOIN dbo.PersonProgrammeActivity PPA ON PP.PersonID = PPA.PersonID AND PP.ProgrammeID = PPA.ProgrammeID
--             LEFT JOIN t$schoolgradeprogrammeauto A ON D.SchoolID = A.SchoolID AND D.GradeID = A.GradeID AND A.ProgrammeID = PP.ProgrammeID
--              CROSS JOIN LATERAL (
--                 SELECT * 
--                 FROM dbo.PersonHasProgrammeHistory H 
--                 WHERE PP.PersonID = H.PersonID 
--                     AND PP.ProgrammeID = H.ProgrammeID 
--                 ORDER BY H.SysEndTime DESC
--                 LIMIT 1
--             ) H 
-- --             JOIN (
-- --                 SELECT DISTINCT (PersonID, ProgrammeID), * 
-- --                 FROM dbo.PersonHasProgrammeHistory H 
-- --                 -- WHERE PP.PersonID = H.PersonID 
-- --                 --    AND PP.ProgrammeID = H.ProgrammeID 
-- --                 ORDER BY H.SysEndTime DESC
-- --                 -- LIMIT 1
-- --             ) H ON PP.PersonID = H.PersonID 
-- --                     AND PP.ProgrammeID = H.ProgrammeID            
--             --LEFT JOIN PersonHasProgrammeHistory H ON PP.PersonID = H.PersonID AND PP.ProgrammeID = H.ProgrammeID AND PP.SysStartTime = H.Sys
--             CROSS JOIN LATERAL
--             (
--                 SELECT COUNT(*) AS Count
--                 FROM t$data AS DO1
--                 JOIN dbo.PersonHasProgramme PPO ON DO1.PersonID = PPO.PersonID
--                 JOIN dbo.SchoolLearner SLO ON DO1.SchoolLearnerID = SLO.SchoolLearnerID
--                 JOIN dbo.SourceLearner XO ON SLO.SchoolLearnerID = XO.MappedSchoolLearnerID AND XO.Year = par_Year
--                 JOIN t$schoolgradeprogrammeauto AO ON DO1.SchoolID = AO.SchoolID AND DO1.GradeID = AO.GradeID AND AO.ProgrammeID = PPO.ProgrammeID
--                 WHERE DO1.PersonID = D.PersonID 
--                     AND SLO.SchoolLearnerID <> SL.SchoolLearnerID 
--                     AND SLO.EndDate = '9999-12-31' 
--                     AND AO.Automatic = 1
--             ) O
-- --             LEFT JOIN
-- --             (
-- --                 SELECT DISTINCT (DO1.PersonID, SLO.SchoolLearnerID), DO1.PersonID, SLO.SchoolLearnerID, COUNT(*) AS Count
-- --                 FROM t$data AS DO1
-- --                 JOIN dbo.PersonHasProgramme PPO ON DO1.PersonID = PPO.PersonID
-- --                 JOIN dbo.SchoolLearner SLO ON DO1.SchoolLearnerID = SLO.SchoolLearnerID
-- --                 JOIN dbo.SourceLearner XO ON SLO.SchoolLearnerID = XO.MappedSchoolLearnerID AND XO.Year = 2022
-- --                 JOIN t$schoolgradeprogrammeauto AO ON DO1.SchoolID = AO.SchoolID AND DO1.GradeID = AO.GradeID AND AO.ProgrammeID = PPO.ProgrammeID              
-- --                 WHERE -- DO1.PersonID = D.PersonID 
-- --                     -- AND SLO.SchoolLearnerID <> SL.SchoolLearnerID 
-- --                     SLO.EndDate = '9999-12-31' 
-- --                     AND AO.Automatic = 1
-- --                 GROUP BY DO1.PersonID, SLO.SchoolLearnerID              
-- --             ) O ON O.PersonID = D.PersonID 
-- --                     AND O.SchoolLearnerID <> SL.SchoolLearnerID            
--             WHERE A.Automatic = 0 OR A.Automatic IS NULL
--         ) X
--         JOIN t$programmeslist P ON X.ProgrammeID = P.ProgrammeID
--         WHERE X.OtherSchoolCount = 0;
		
		CREATE TEMPORARY TABLE t$phpenda
        AS
        SELECT X.*, P.Name Programme
        FROM
        (
            SELECT DISTINCT
                D.PersonID, PP.ProgrammeID, PPA.LastMovementDate, PPA.LastAppearDate, PP.SysStartTime,
                MAKE_DATE(par_Year,1,1) YearStartDate,
                --CAST(CAST('2020-12-31' AS date) AS datetime) + CAST('23:59:59' AS datetime) LastYearEndDate,
                -- TO_TIMESTAMP(CONCAT(var_LastYearEndDate,' ','23:59:59'), 'YYYY-MM-DD HH:MI:SS') AS LastYearEndDate,
                CONCAT(var_LastYearEndDate::timestamp without time zone,' ','23:59:59') AS LastYearEndDate,
                H.SysStartTime HistorySysStartTime,
                D.SchoolLearnerID,
                D.SchoolID,
                D.GradeID,
                COALESCE(O.Count,0) OtherSchoolCount
            FROM t$data D
            JOIN dbo.PersonHasProgramme PP ON D.PersonID = PP.PersonID
            JOIN dbo.SchoolLearner SL ON D.SchoolLearnerID = SL.SchoolLearnerID
            JOIN dbo.SourceLearner X ON SL.SchoolLearnerID = X.MappedSchoolLearnerID AND X.Year = par_Year

            LEFT JOIN dbo.PersonProgrammeActivity PPA ON PP.PersonID = PPA.PersonID AND PP.ProgrammeID = PPA.ProgrammeID
            LEFT JOIN t$schoolgradeprogrammeauto A ON D.SchoolID = A.SchoolID AND D.GradeID = A.GradeID AND A.ProgrammeID = PP.ProgrammeID
            JOIN (
                SELECT DISTINCT (PersonID, ProgrammeID), * 
                FROM dbo.PersonHasProgrammeHistory H 
                ORDER BY H.SysEndTime DESC
            ) H ON PP.PersonID = H.PersonID 
                    AND PP.ProgrammeID = H.ProgrammeID            
			LEFT OUTER JOIN 
            (
                SELECT DISTINCT DO1.PersonID,SLO.SchoolLearnerID, COUNT(*) AS Count
                FROM t$data AS DO1
                JOIN dbo.PersonHasProgramme PPO ON DO1.PersonID = PPO.PersonID
                JOIN dbo.SchoolLearner SLO ON DO1.SchoolLearnerID = SLO.SchoolLearnerID
                JOIN dbo.SourceLearner XO ON SLO.SchoolLearnerID = XO.MappedSchoolLearnerID AND XO.Year = par_Year
                JOIN t$schoolgradeprogrammeauto AO ON DO1.SchoolID = AO.SchoolID AND DO1.GradeID = AO.GradeID AND AO.ProgrammeID = PPO.ProgrammeID
                WHERE -- DO1.PersonID = D.PersonID 
--                    --  AND SLO.SchoolLearnerID <> SL.SchoolLearnerID 
                    SLO.EndDate = '9999-12-31' 
                    AND AO.Automatic = 1
				GROUP BY DO1.PersonID, SLO.SchoolLearnerID 
            ) O ON O.PersonID = D.PersonID  AND O.SchoolLearnerID <> SL.SchoolLearnerID 

           
            WHERE A.Automatic = 0 OR A.Automatic IS NULL
        ) X
        JOIN t$programmeslist P ON X.ProgrammeID = P.ProgrammeID 
        WHERE X.OtherSchoolCount = 0;
		

RAISE NOTICE 'INTO t$phpenda';
/*
            var_Sql := '
        DECLARE @LogDescription varchar(4000);
        SELECT @LogDescription=STRING_AGG(FORMATMESSAGE(''%s: %d'', Programme, Count),'', '') FROM (SELECT X.Programme, COUNT(*) Count FROM #PHPENDA X WHERE X.SysStartTime = X.HistorySysStartTime GROUP BY X.Programme) X

        UPDATE PP SET SysStartTime = DATEADD(second,1,PP.SysStartTime)
        FROM #PHPENDA X
        JOIN PersonHasProgramme PP ON X.PersonID = PP.PersonID AND X.ProgrammeID = PP.ProgrammeID
        WHERE X.SysStartTime = X.HistorySysStartTime

        SET @LogDescription = FORMATMESSAGE(''PersonHasProgramme Ending (in non auto grade): %d   [%s]'', @@ROWCOUNT, ISNULL(@LogDescription,''''));
        EXEC internal.pDataImportLogInsert par_DataImportID, @LogDescription

        UPDATE X SET SysStartTime = DATEADD(second,1,X.SysStartTime)
        FROM #PHPENDA X
        WHERE X.SysStartTime = X.HistorySysStartTime
        ';
*/
        UPDATE dbo.PersonHasProgramme PP
        SET SysStartTime = PP.SysStartTime + interval '1 second'
        FROM t$phpenda X
--         JOIN dbo.PersonHasProgramme PP ON X.PersonID = PP.PersonID AND X.ProgrammeID = PP.ProgrammeID
        WHERE X.SysStartTime = X.HistorySysStartTime 
		AND X.PersonID = PP.PersonID 
		AND X.ProgrammeID = PP.ProgrammeID;
RAISE NOTICE 'UPDATE PersonHasProgramme';
var_LogDescription = 'UPDATE PersonHasProgramme';
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        UPDATE t$phpenda
        SET SysStartTime = X.SysStartTime + interval '1 second'
        FROM t$phpenda X
        WHERE X.SysStartTime = X.HistorySysStartTime;
RAISE NOTICE 'UPDATE t$phpenda';

            --CALL clickdw_internal.ptemporalstatus('dbo.PersonHasProgramme', 0, NULL, 1, return_code => ptemporalstatus$ReturnCode);
            /*
            [7672 - Severity CRITICAL - Automatic conversion of this command is not supported. Perform a manual conversion.]
            --EXEC sp_executesql @Stmt=@Sql, @Params=N'par_DataImportID bigint', par_DataImportID=par_DataImportID
            */
            --CALL clickdw_internal.ptemporalstatus('dbo.PersonHasProgramme', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            var_Sql := NULL;
            DELETE FROM dbo.personhasprogramme pp
            using t$phpenda AS x
--                 dbo.personhasprogramme AS pp
            WHERE x.personid = pp.personid AND x.programmeid = pp.programmeid;
RAISE NOTICE 'DELETE FROM dbo.personhasprogramme';   
/*         
            var_Sql := '
        UPDATE dbo.personhasprogrammehistory AS x SET sysendtime = CASE WHEN x.lastyearenddate < pp.sysstarttime THEN pp.sysstarttime ELSE x.lastyearenddate END FROM t$phpenda AS x, dbo.personhasprogrammehistory AS pp WHERE x.personid = pp.personid AND x.programmeid = pp.programmeid AND x.sysstarttime = pp.sysstarttime
        ';
*/
            UPDATE dbo.personhasprogrammehistory
            SET sysendtime = CASE WHEN x.lastyearenddate::timestamp without time zone < pp.sysstarttime::timestamp without time zone THEN pp.sysstarttime::timestamp without time zone ELSE x.lastyearenddate::timestamp without time zone END 
            FROM t$phpenda AS x, dbo.personhasprogrammehistory AS pp 
            WHERE x.personid = pp.personid 
                AND x.programmeid = pp.programmeid 
                AND x.sysstarttime = pp.sysstarttime;
RAISE NOTICE 'UPDATE dbo.personhasprogrammehistory';            

            --CALL clickdw_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
            */
            --CALL clickdw_internal.ptemporalstatus('dbo.PersonHasProgramme', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            var_Sql := NULL;
            DROP TABLE IF EXISTS t$phpenda;
        END;

        IF par_FirstUsableDataMode = 1 THEN
            INSERT INTO internal.processingschool (schoolid, item, timestamp)
            SELECT
                schoolid, 'Year First Usable Data - Update', CURRENT_TIMESTAMP(3)
                FROM t$firstusabledataschools AS d;
RAISE NOTICE 'INSERT INTO internal.processingschool';                            
          
        END IF;

        IF var_TranCountOnEntry = 0 THEN
            COMMIT;
        END IF;
--         EXCEPTION
--             WHEN OTHERS THEN
--                  ROLLBACK;
--                 IF var_Sql IS NOT NULL THEN
--                     RAISE NOTICE '%', var_Sql;
--                 END IF;
--                 RAISE;
    END;
  
    /* Removing Temporary tables */
    drop table if exists t$schoolslist;
    drop table if exists t$programmeslist;
    drop table if exists t$schoolgradeprogrammeauto;
    drop table if exists t$activeschoolgrade;
    drop table if exists t$selfmanagedschool;
    drop table if exists t$firstusabledataschools;
    drop table if exists t$data;
    drop table if exists t$previousdata;
    drop table if exists t$schoolclass;
    DROP TABLE IF EXISTS t$phpstart;
    DROP TABLE IF EXISTS t$phpend;
    DROP TABLE IF EXISTS t$phpenda;
    drop table if Exists t$data;
	DROP TABLE IF EXISTS t$ignoreclass;
    
RAISE NOTICE 'PROCEDURE internal.pupdatelearnerfromsourcelearner COMPLETED'; 
END;
$BODY$;
