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

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

CREATE OR REPLACE PROCEDURE internal.ptransferlearner(
	par_dataimportid bigint,
	par_validateonly numeric DEFAULT 0,
	par_emiscode character varying DEFAULT NULL::character varying,
	par_matchondddorcemislearneridonly numeric DEFAULT 1,
	par_updatepersoninfoonly numeric DEFAULT 0,
	par_allowmismatchidnumbers numeric DEFAULT 0,
	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,
	p_refcur_4 refcursor DEFAULT NULL::refcursor,
	p_refcur_5 refcursor DEFAULT NULL::refcursor,
	p_refcur_6 refcursor DEFAULT NULL::refcursor,
	p_refcur_7 refcursor DEFAULT NULL::refcursor,
	p_refcur_8 refcursor DEFAULT NULL::refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
/*
After the transfer is completed, an email will be generated that will contain a list of schools that had new data loaded with the following information:
-   New learners were added to schools
-   Learners that have left schools
-   Schools that have had learners loaded for the first time (i.e. onboarding of new schools)
-   Schools that have not been registered with RE (i.e. the school is active on the Click system as a RE school, but does not have a ZAC code)

DataImportLog
    10001 -
*/
/* @SchoolSet                   bit = 0, */
DECLARE
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_SourceDataImportID BIGINT DEFAULT (SELECT
        sourcedataimportid
        FROM internal.dataimport
        WHERE dataimportid = par_DataImportID);
    var_SourceDataDate TIMESTAMP WITHOUT TIME ZONE;
    var_SourceDataDateS VARCHAR(20);
    var_SourceDataDateYear INTEGER;
    var_SourceDataTypeID INTEGER;
    var_dbname VARCHAR(128) DEFAULT current_database();
    var_LogDescription VARCHAR(255);
    var_LogData VARCHAR(2048);
    var_Sql TEXT;
    var_RowCount INTEGER;
    var_MaxPersonID INTEGER DEFAULT 0;
    var_PersonNextValue BIGINT DEFAULT var_MaxPersonID + 1;
    var_MaxSchoolLearnerID BIGINT DEFAULT dbo.sequence_current('dbo.SchoolLearner');
    var_MaxSchoolLearnerYearID BIGINT DEFAULT dbo.sequence_current('dbo.SchoolLearnerYear');
    ptemporalstatus$par_StatusChanged NUMERIC;
    ptemporalstatus$ReturnCode INTEGER;
    sql$rowcount BIGINT;
BEGIN
 	DROP TABLE IF EXISTS t$itemid;
    
    DROP TABLE IF EXISTS t$learner;
  
    DROP TABLE IF EXISTS t$schoolclass;

    DROP TABLE IF EXISTS t$personhasprogrammeoutput;
	
	/* Reset the sequence tables to the new avalible max value. */
	CALL internal.presetsequence();
    /*
    [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 warning 'Import ID is not null'; 
		return_code := 1;
        RETURN;
    END IF;

    IF par_UpdatePersonInfoOnly = 1 THEN
        -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %.', '50000', 0, 0, 'Updating Person Info only' USING ERRCODE = '50000';
    END IF;
    -- CALL ClickCOMMON.dbo.pResetSequence(var_dbname, 'dbo', 'Person', 'SchoolClass', 'SchoolLearner', 'SchoolLearnerYear');
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
    SELECT
            @SourceDataDate     = DataContractDataDate,
            @SourceDataDateYear = YEAR(DataContractDataDate),
            @SourceDataDateS    = FORMAT(@SourceDataDate, 'yyyy-MM-dd HH:mm:ss'),
            @SourceDataTypeID   = DataSourceTypeID
        FROM staging.DataImportDataContract
        WHERE DataImportID = @SourceDataImportID
    */
    SELECT DataContractDataDate, EXTRACT(YEAR FROM DataContractDataDate),
        TO_CHAR(DataContractDataDate :: DATE, 'yyyy-MM-dd HH:mm:ss'), DataSourceTypeID
    INTO
        var_SourceDataDate, var_SourceDataDateYear, var_SourceDataDateS, var_SourceDataTypeID
    FROM dbo.dataimportdatacontract_staging
    WHERE DataImportID = var_SourceDataImportID;
	
RAISE NOTICE 'dbo.dataimportdatacontract_staging';
RAISE NOTICE 'var_SourceDataDate: %',var_SourceDataDate;
RAISE NOTICE 'var_SourceDataDateYear: %',var_SourceDataDateYear;
RAISE NOTICE 'var_SourceDataDateS: %',var_SourceDataDateS;
RAISE NOTICE 'var_SourceDataTypeID: %',var_SourceDataTypeID;
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('SourceDataImportID=%I64d, @SourceDataDateYear=%d, @SourceDataDate=%s, @EMISCode=%s', @SourceDataImportID, @SourceDataDateYear, @SourceDataDateS, @EMISCode);
    */
	if(var_SourceDataImportID Is not null) then 
 Raise warning 'Import ID is not null';
 end if;
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    CREATE TEMPORARY TABLE t$itemid
    (itemid BIGINT NOT NULL,
        detail VARCHAR(100) NULL);
RAISE NOTICE 'CREATE TEMPORARY TABLE t$itemid';    
    /* Copy data from STAGING */
    CREATE TEMPORARY TABLE t$learner
    (rownumber BIGINT NOT NULL,
        lastsubmissiondate DATE NOT NULL,
        emiscode VARCHAR(9) NOT NULL,
        dddlearnerid VARCHAR(20) NULL,
        firstnamesource VARCHAR(255) NOT NULL,
        firstname VARCHAR(255) NOT NULL,
        secondname VARCHAR(255) NOT NULL,
        thirdname VARCHAR(255) NOT NULL,
        lastname VARCHAR(255) NOT NULL,
        accessionnumber VARCHAR(30) NULL,
        idnumber VARCHAR(20) NULL,
        dateofbirth DATE NULL,
        gender VARCHAR(10) NULL,
        race VARCHAR(50) NULL,
        luritsnumber VARCHAR(20) NULL,
        citizenship VARCHAR(50) NULL,
        passportnumber VARCHAR(20) NULL,
        datejoined DATE NOT NULL,
        dateleft DATE NOT NULL,
        homelanguage VARCHAR(100) NULL,
        instructionlanguage VARCHAR(100) NULL,
        grade INTEGER NULL,
        class VARCHAR(100) NULL,
        classname VARCHAR(255) NULL,
        existingpersonid INTEGER NULL,
        cemislearnerid VARCHAR(30) NULL,
        idnumberinvalidreason VARCHAR(50) NULL,
        idnumberinvalid VARCHAR(20) NULL,
        /* */
        dwschoolid INTEGER NULL,
        dwpersonid INTEGER NULL,
        newperson NUMERIC(1, 0) DEFAULT (0),
        dwpersonmatchmethod VARCHAR(100) NULL,
        dwgenderid SMALLINT NULL,
        dwethnicityid SMALLINT NULL,
        dwhomelanguageid SMALLINT NULL,
        dwinstructionlanguageid SMALLINT NULL,
        dwcitizenshiptypeid SMALLINT NULL,
        dwcountryid SMALLINT NULL,
        dwschoolclassid INTEGER NULL,
        dwschoollearnerid BIGINT NULL,
        dwschoollearneryearid BIGINT NULL);

CREATE INDEX IF NOT EXISTS t$learner_DWPersonID_idx
    ON t$learner USING btree
    (DWPersonID ASC NULLS LAST)
    TABLESPACE pg_default;

RAISE NOTICE 'CREATE TEMPORARY TABLE t$learner';    
    /*
    [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers., 9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers., 9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers., 9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers., 9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers., 9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers., 9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers., 9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers., 9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers., 9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers., 9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers., 9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
    INSERT #Learner
        (RowNumber, LastSubmissionDate, EMISCode, DDDLearnerID, FirstNameSource, FirstName, SecondName, ThirdName, LastName, AccessionNumber, IDNumber, DateOfBirth, Gender, Race, LuritsNumber, Citizenship, PassportNumber, DateJoined, DateLeft,
         HomeLanguage, InstructionLanguage, Grade, Class, ClassName, ExistingPersonID, CEMISLearnerID, IDNumberInvalid, IDNumberInvalidReason)
        SELECT
            RowNumber,
            LastSubmissionDate,
            EMISCode,
            NULLIF(CAST(DDDLearnerID AS varchar(20)),''),
            FirstName,
            TRIM(ClickCOMMON.dbo.fGetStringPart(REPLACE(TRIM(FirstName),' ',','), ',', 1)),
            TRIM(ISNULL(ClickCOMMON.dbo.fGetStringPart(REPLACE(TRIM(FirstName),' ',','), ',', 2),'')),
            TRIM(ISNULL(ClickCOMMON.dbo.fGetStringPart(REPLACE(TRIM(FirstName),' ',','), ',', 3),'')),
            ClickCOMMON.dbo.fGetStringPart(TRIM(LastName),',',1),
            CASE WHEN AccessionNumber = '' OR AccessionNumber = '0' THEN NULL ELSE AccessionNumber END,
            CASE WHEN IDNumberInvalid IS NULL THEN NULLIF(IDNumber, '') ELSE NULL END,
            DateOfBirth,
            NULLIF(Gender, ''),
            NULLIF(Race, ''),
            CASE WHEN LuritsNumber = '' OR LuritsNumber = '0' THEN NULL ELSE LuritsNumber END,
            NULLIF(Citizenship, ''),
            NULLIF(PassportNumber, ''),
            ISNULL(DateJoined, '0001-01-01'),
            ISNULL(DateLeft,'9999-12-31'),
            TRIM(REPLACE(NULLIF(HomeLanguage,''),'Home Language','')),
            TRIM(REPLACE(REPLACE(NULLIF(InstructionLanguage,''),'Home Language',''),'First Additional Language','')),
            CASE
                WHEN ISNUMERIC(Grade) = 1 THEN CAST(Grade AS int)
                WHEN TRIM(Grade) = 'LSEN' THEN 14
                WHEN TRIM(Grade) = 'R' THEN 0
                WHEN TRIM(Grade) = 'Grade R' THEN 0
                WHEN TRIM(Grade) LIKE 'Grade %' THEN CAST(TRIM(REPLACE(Grade,'Grade ','')) AS int)
                ELSE CAST(Grade AS int)
            END,
            CAST(CASE
                WHEN TRIM(Class) = 'R' THEN 0
                ELSE CAST(Class AS int)
            END AS varchar(100)),
            ClassName,
            PersonID,
            CEMISLearnerID,
            IDNumberInvalid,
            IDNumberInvalidReason
        FROM staging.Learner L
        WHERE L.DataImportID = @SourceDataImportID
            --AND ((@SchoolSet = 0) OR (@SchoolSet = 1 AND L.EMISCode IN (SELECT EMISCode FROM SchoolSet)))
            AND ((@EMISCode IS NULL) OR (@EMISCode IS NOT NULL AND @EMISCode = EMISCode))
            --AND RowNumber = 14311
        ORDER BY RowNumber;
    */

INSERT INTO t$Learner
        (RowNumber, LastSubmissionDate, EMISCode, DDDLearnerID, FirstNameSource, FirstName, SecondName, ThirdName, LastName, AccessionNumber, IDNumber, DateOfBirth, Gender, Race, LuritsNumber, Citizenship, PassportNumber, DateJoined, DateLeft,
         HomeLanguage, InstructionLanguage, Grade, Class, ClassName, ExistingPersonID, CEMISLearnerID, IDNumberInvalid, IDNumberInvalidReason)
        SELECT
            RowNumber,
            LastSubmissionDate,
            EMISCode,
            COALESCE(CAST(DDDLearnerID AS varchar(20)),''),
            FirstName,
            --TRIM(ClickCOMMON.dbo.fGetStringPart(REPLACE(TRIM(FirstName),' ',','), ',', 1)),
            --TRIM(ISNULL(ClickCOMMON.dbo.fGetStringPart(REPLACE(TRIM(FirstName),' ',','), ',', 2),'')),
            --TRIM(ISNULL(ClickCOMMON.dbo.fGetStringPart(REPLACE(TRIM(FirstName),' ',','), ',', 3),'')),
            FirstName,
            FirstName,
            FirstName,
            --ClickCOMMON.dbo.fGetStringPart(TRIM(LastName),',',1),
            LastName,
            CASE WHEN AccessionNumber = '' OR AccessionNumber = '0' THEN NULL ELSE AccessionNumber END,
            CASE WHEN IDNumberInvalid IS NULL THEN NULLIF(IDNumber, '') ELSE NULL END,
            DateOfBirth,
            NULLIF(Gender, ''),
            NULLIF(Race, ''),
            CASE WHEN LuritsNumber = '' OR LuritsNumber = '0' THEN NULL ELSE LuritsNumber END,
            NULLIF(Citizenship, ''),
            NULLIF(PassportNumber, ''),
            COALESCE(DateJoined, '0001-01-01'),
            COALESCE(DateLeft,'9999-12-31'),
            TRIM(REPLACE(COALESCE(HomeLanguage,''),'Home Language','')),
            TRIM(REPLACE(REPLACE(COALESCE(InstructionLanguage,''),'Home Language',''),'First Additional Language','')),
            CASE
                WHEN aws_sqlserver_ext.isnumeric(Grade)::int = 1 THEN CAST(Grade AS int)
                WHEN TRIM(Grade) = 'LSEN' THEN 14
                WHEN TRIM(Grade) = 'R' THEN 0
                WHEN TRIM(Grade) = 'Grade R' THEN 0
                WHEN TRIM(Grade) LIKE 'Grade %' THEN CAST(TRIM(REPLACE(Grade,'Grade ','')) AS int)
                ELSE CAST(Grade AS int)
            END,
            CAST(CASE
                WHEN TRIM(Class) = 'R' THEN 0
                ELSE CAST(Class AS int)
            END AS varchar(100)),
            ClassName,
            PersonID,
            CEMISLearnerID,
            IDNumberInvalid,
            IDNumberInvalidReason
        FROM dbo.learner_staging L
        WHERE L.DataImportID = var_SourceDataImportID
            --AND ((@SchoolSet = 0) OR (@SchoolSet = 1 AND L.EMISCode IN (SELECT EMISCode FROM SchoolSet)))
            AND ((par_emiscode IS NULL) OR (par_emiscode IS NOT NULL AND par_emiscode = EMISCode))
            --AND RowNumber = 14311
        ORDER BY RowNumber;

RAISE NOTICE 'INSERT INTO t$Learner';    

	GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
	RAISE NOTICE 'ROWS: %',var_LogDescription;
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

RAISE NOTICE 'INSERT INTO t$Learner Count';   
IF (SELECT count(*) FROM t$Learner) < 1 THEN
   RAISE WARNING 'table is empty';
ELSE
   RAISE NOTICE 't$Learner not empty'; --populates the table
END IF;
    var_MaxPersonID = (SELECT
        MAX(existingpersonid)
        FROM t$learner);

    var_PersonNextValue = var_MaxPersonID + 1;
RAISE NOTICE 'var_MaxPersonID1: %', var_MaxPersonID;        
RAISE NOTICE 'var_PersonNextValue: %', var_PersonNextValue;        

    IF par_DebugMode >= 2 THEN
        OPEN p_refcur FOR
        SELECT
            *
            FROM t$learner;
    END IF;

    BEGIN
        IF var_TranCountOnEntry = 0 THEN
            -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %.', '50000', 0, 0, 'BEGIN TRAN' USING ERRCODE = '50000';
            /*
            [7807 - Severity CRITICAL - PostgreSQL does not support explicit transaction management in functions. Perform a manual conversion.]
            BEGIN TRAN
            */
        END IF;
        /* Match School */
        UPDATE t$learner AS tl
        SET dwschoolid = s.schoolid
        FROM dbo.school AS s
            WHERE LOWER(tl.emiscode) = LOWER(s.emiscode);
RAISE NOTICE 'UPDATE t$learner1';                
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
		RAISE NOTICE 'ROWS: %',var_LogDescription;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF par_DebugMode >= 2 THEN
            OPEN p_refcur_2 FOR
            SELECT
                'Unmatched School' AS "Unmatched School", *
                FROM t$learner AS tl
                WHERE tl.dwschoolid IS NULL;
        END IF;
        /* Match Gender, Race/Ethnicity, HomeLanguage, InstructionLanguage, Citizenship */
        
        /*
        [7927 - Severity CRITICAL - PostgreSQL doesn't support OUTER joins for self-reference to the label from UPDATE / DELETE in the FROM clause and without PK on table-for-update. Perform a manual conversion.]
        UPDATE TL SET
                    TL.DWGenderID               = G.GenderID,
                    TL.DWEthnicityID            = E.EthnicityID,
                    TL.DWHomeLanguageID         = HL.LanguageID,
                    TL.DWInstructionLanguageID  = HL.LanguageID,
                    TL.DWCitizenshipTypeID      = CT.CitizenshipTypeID,
                    TL.DWCountryID              = C.CountryID
                FROM #Learner TL
                LEFT JOIN Gender G ON (TL.Gender = G.Code OR TL.Gender = G.[Description])
                LEFT JOIN Ethnicity E ON (TL.Race = E.[Description] OR TL.Race = E.DescriptionDDD)
                LEFT JOIN [Language] HL ON TL.HomeLanguage = HL.[Description] OR TL.HomeLanguage = HL.DescriptionEndonym
                LEFT JOIN [Language] IL ON TL.InstructionLanguage = IL.[Description] OR TL.InstructionLanguage = IL.DescriptionEndonym
                LEFT JOIN CitizenshipType CT ON (TL.Citizenship = CT.[Description] OR TL.Citizenship = CT.DescriptionDDD)
                LEFT JOIN Country C ON CT.[Description] = 'South Africa Citizen' AND C.Code = 'ZA'
                WHERE TL.DWSchoolID IS NOT NULL;
        */

		UPDATE t$Learner S SET
            DWGenderID               = G.GenderID,
            DWEthnicityID            = E.EthnicityID,
            DWHomeLanguageID         = HL.LanguageID,
            DWInstructionLanguageID  = HL.LanguageID,
            DWCitizenshipTypeID      = CT.CitizenshipTypeID,
            DWCountryID              = C.CountryID
        FROM t$Learner TL
        LEFT JOIN dbo.Gender G ON (TL.Gender = G.Code OR TL.Gender = G.Description)
        LEFT JOIN dbo.Ethnicity E ON (TL.Race = E.Description OR TL.Race = E.DescriptionDDD)
        LEFT JOIN dbo.Language HL ON TL.HomeLanguage = HL.Description OR TL.HomeLanguage = HL.DescriptionEndonym
        LEFT JOIN dbo.Language IL ON TL.InstructionLanguage = IL.Description OR TL.InstructionLanguage = IL.DescriptionEndonym
        LEFT JOIN dbo.CitizenshipType CT ON (TL.Citizenship = CT.Description OR TL.Citizenship = CT.DescriptionDDD)
        LEFT JOIN dbo.Country C ON CT.Description= 'South Africa Citizen' AND C.Code = 'ZA'
        WHERE TL.DWSchoolID IS NOT NULL AND S.RowNumber = TL.RowNumber;   
		
RAISE NOTICE 'UPDATE t$learner2';        

        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
		Raise Notice 'var_LogDes: %', var_LogDescription;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        /* Match Person (By PersonID) */
       
	   UPDATE t$learner AS tl
        SET dwpersonid = tl.existingpersonid, dwpersonmatchmethod = 'Existing PersonID'
            WHERE tl.existingpersonid IS NOT NULL;
RAISE NOTICE 'UPDATE t$learner3';  

        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
		Raise Notice 'var_LogDes: %', var_LogDescription;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
	
	CREATE INDEX IF NOT EXISTS t$learner_dddlearnerid_idx
    ON t$learner USING btree
    (dddlearnerid ASC NULLS LAST)
    TABLESPACE pg_default;

		/* Match Person (by DDDLearnerID) 
			Added p.dddlearnerid <> ''  because the view table has alot of blanks that links to the learner table.
		*/
        UPDATE t$learner AS tl
        SET dwpersonid = p.personid, dwpersonmatchmethod = 'DDDLearnerID'
        FROM dbo.vwpersonlearner AS p
           WHERE p.dddlearnerid <> '' and tl.dwschoolid IS NOT NULL AND tl.dddlearnerid IS NOT NULL AND tl.dwpersonid IS NULL AND LOWER(tl.dddlearnerid) = LOWER(p.dddlearnerid);
		   
RAISE NOTICE 'UPDATE t$learner4';  

        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
		Raise Notice 'var_LogDes: %', var_LogDescription;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        /* IF @SourceDataTypeID = 11 */
        /* SELECT 'Match DDDLearnerID' 'Match DDDLearnerID', * FROM #Learner WHERE DWPersonID IS NOT NULL AND DWPersonMatchMethod  = 'DDDLearnerID' */
        /* Match Person (BY CEMISLearnerID) */
        UPDATE t$learner AS tl
        SET dwpersonid = p.personid, dwpersonmatchmethod = 'CEMISLearnerID'
        FROM dbo.vwpersonlearner AS p
            WHERE tl.dwschoolid IS NOT NULL AND tl.cemislearnerid IS NOT NULL AND tl.dwpersonid IS NULL AND LOWER(tl.cemislearnerid) = LOWER(p.cemisnumber);
			
RAISE NOTICE 'UPDATE t$learner5';  

        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
		Raise Notice 'var_LogDes: %', var_LogDescription;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        /* IF @SourceDataTypeID = 11 */
        /* SELECT 'Match CEMISLearnerID' 'Match CEMISLearnerID', * FROM #Learner WHERE DWPersonID IS NOT NULL AND DWPersonMatchMethod  = 'CEMISLearnerID' */

        IF EXISTS (SELECT
                *
                FROM t$learner AS tl
                JOIN dbo.vwpersonlearner AS p
                    ON tl.dwpersonid = p.personid
                WHERE LENGTH(tl.idnumber) = 13 AND LOWER(tl.idnumber) <> LOWER(p.saidnumber) AND p.saidnumber IS NOT NULL
            ) THEN
RAISE NOTICE 't$learner EXISTS';
            IF par_DebugMode >= 2 THEN
                OPEN p_refcur_3 FOR
                SELECT
                    'DDDLearnerID mismatch with ID Number' AS "DDDLearnerID mismatch with ID Number", *
                    FROM t$learner AS tl
                    JOIN dbo.vwpersonlearner AS p
                        ON tl.dwpersonid = p.personid
                    WHERE LENGTH(tl.idnumber) = 13 AND LOWER(tl.idnumber) <> LOWER(p.saidnumber) AND p.saidnumber IS NOT NULL;
            END IF;
            SELECT
                string_agg(CAST (tl.rownumber AS VARCHAR(20)), ',')
                INTO var_LogData
                FROM t$learner AS tl
                JOIN dbo.vwpersonlearner AS p
                    ON tl.dwpersonid = p.personid
                WHERE LENGTH(tl.idnumber) = 13 AND LOWER(tl.idnumber) <> LOWER(p.saidnumber) AND p.saidnumber IS NOT NULL;
            var_LogData = 'Row numbers: ' || COALESCE(var_LogData, '');

            IF par_AllowMismatchIDNumbers = 0 THEN
                CALL internal.pdataimportloginsert(par_DataImportID, 'DDDLearnerID mismatch with IDNumber.', 'W', var_LogData);
            END IF;
        END IF;
RAISE NOTICE 'IF EXISTS 1';
RAISE NOTICE 'par_MatchOnDDDorCEMISLearnerIDOnly: %', par_MatchOnDDDorCEMISLearnerIDOnly;
        IF par_MatchOnDDDorCEMISLearnerIDOnly = 0 THEN
		
            /* Match Person (by IDNumber in School) */
            UPDATE t$learner AS tl
            SET dwpersonid = peid.personid, dwpersonmatchmethod = 'IDNumber in school', dwschoollearnerid = sl.schoollearnerid
            FROM dbo.vwpersonlearner AS peid
            JOIN dbo.schoollearner AS sl
                ON peid.personid = sl_dml.personid AND sl_dml.schoolid = tl.dwschoolid
                WHERE tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NULL AND tl.idnumber IS NOT NULL AND LENGTH(tl.idnumber) = 13 AND aws_sqlserver_ext.isnumeric(tl.idnumber) = 1 AND (tl.dddlearnerid IS NULL AND tl.cemislearnerid IS NULL) AND LOWER(tl.idnumber) = LOWER(peid.saidnumber);
            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
			
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
			
            /* Match Person (by IDNumber global) */
            UPDATE t$learner AS tl
            SET dwpersonid = peid.personid, dwpersonmatchmethod = 'IDNumber in global'
            FROM dbo.vwpersonlearner AS peid
                WHERE tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NULL AND tl.idnumber IS NOT NULL AND LENGTH(tl.idnumber) = 13 AND aws_sqlserver_ext.isnumeric(tl.idnumber) = 1 AND (tl.dddlearnerid IS NULL AND tl.cemislearnerid IS NULL) AND LOWER(tl.idnumber) = LOWER(peid.saidnumber);
            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
			
            /* Match Person (by LuritsNumber global) */
            /* UPDATE TL SET DWPersonID = PEID.PersonID, DWPersonMatchMethod = 'LURITS number in global' */
            /* FROM #Learner TL */
            /* JOIN PersonExternalID PEID ON TL.LuritsNumber = PEID.ExternalID AND PEID.PersonExternalIDType = 7 */
            /* WHERE TL.DWSchoolID IS NOT NULL AND TL.DWPersonID IS NULL AND TL.LuritsNumber IS NOT NULL AND LEN(TL.LuritsNumber) > 6; */
            /* SET @LogDescription = FORMATMESSAGE('Learner Match (By LURITS number global): %d', @@ROWCOUNT);  EXEC internal.pDataImportLogInsert @DataImportID, @LogDescription; */
            
			/* Match Person (by AccessionNumber, LastName, FirstNameSource in School) */
            UPDATE t$learner AS tl
            SET dwpersonid = sl.personid, dwpersonmatchmethod = 'AccessionNumber, LastName, FirstName/FirstNameSource in school', dwschoollearnerid = sl.schoollearnerid
            FROM dbo.schoollearner AS sl
            JOIN dbo.vwpersonlearner AS p
                ON sl.personid = p.personid AND LOWER(tl.lastname) = LOWER(p.lastname) AND (LOWER(tl.firstnamesource) = LOWER(p.firstname) OR LOWER(tl.firstname) = LOWER(p.firstname))
                WHERE tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NULL AND (tl.dddlearnerid IS NULL AND tl.cemislearnerid IS NULL) AND LOWER(sl.accessionno) <> LOWER('');
            /* AND (((P.DDDLearnerID IS NULL AND TL.DDDLearnerID IS NULL) OR (P.DDDLearnerID IS NULL AND TL.DDDLearnerID IS NOT NULL)) */
            /* OR ((P.CEMISNumber IS NULL AND TL.CEMISLearnerID IS NULL) OR (P.CEMISNumber IS NULL AND TL.CEMISLearnerID IS NOT NULL))) */
            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
			
            /* Match Person (by LastName, FirstName, Gender, DOB in School) */
            UPDATE t$learner AS tl
            SET dwpersonid = sl.personid, dwpersonmatchmethod = 'LastName, FirstName, Gender, DOB in School', dwschoollearnerid = sl.schoollearnerid
            FROM dbo.schoollearner AS sl
            JOIN dbo.vwpersonlearner AS p
                ON sl.personid = p.personid AND LOWER(tl.lastname) = LOWER(p.lastname) AND (LOWER(tl.firstnamesource) = LOWER(p.firstname) OR LOWER(tl.firstname) = LOWER(p.firstname)) AND (tl.dwgenderid IS NOT NULL AND p.genderid IS NOT NULL AND tl.dwgenderid = p.genderid) AND (tl.dateofbirth IS NOT NULL AND p.birthdate IS NOT NULL AND tl.dateofbirth = p.birthdate)
                WHERE tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NULL AND (tl.dddlearnerid IS NULL AND tl.cemislearnerid IS NULL) AND sl.schoolid = tl.dwschoolid;
            /* AND (((P.DDDLearnerID IS NULL AND TL.DDDLearnerID IS NULL) OR (P.DDDLearnerID IS NULL AND TL.DDDLearnerID IS NOT NULL)) */
            /* OR ((P.CEMISNumber IS NULL AND TL.CEMISLearnerID IS NULL) OR (P.CEMISNumber IS NULL AND TL.CEMISLearnerID IS NOT NULL))) */
            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
			
            /* Match Person (by LastName, FirstName, Gender in School) */
            UPDATE t$learner AS tl
            SET dwpersonid = sl.personid, dwpersonmatchmethod = 'LastName, FirstName, Gender in School', dwschoollearnerid = sl.schoollearnerid
            FROM dbo.schoollearner AS sl
            JOIN dbo.vwpersonlearner AS p
                ON sl.personid = p.personid AND LOWER(tl.lastname) = LOWER(p.lastname) AND (LOWER(tl.firstnamesource) = LOWER(p.firstname) OR LOWER(tl.firstname) = LOWER(p.firstname)) AND (tl.dwgenderid IS NOT NULL AND p.genderid IS NOT NULL AND tl.dwgenderid = p.genderid)
                WHERE tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NULL AND (tl.dddlearnerid IS NULL AND tl.cemislearnerid IS NULL) AND sl.schoolid = tl.dwschoolid;
            /* AND (((P.DDDLearnerID IS NULL AND TL.DDDLearnerID IS NULL) OR (P.DDDLearnerID IS NULL AND TL.DDDLearnerID IS NOT NULL)) */
            /* OR ((P.CEMISNumber IS NULL AND TL.CEMISLearnerID IS NULL) OR (P.CEMISNumber IS NULL AND TL.CEMISLearnerID IS NOT NULL))) */
            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
			
            /* Match Person (by LastName, FirstName, and rest is null) */
            UPDATE t$learner AS tl
            SET dwpersonid = sl.personid, dwpersonmatchmethod = 'LastName, FirstName in School', dwschoollearnerid = sl.schoollearnerid
            FROM dbo.schoollearner AS sl
            JOIN dbo.vwpersonlearner AS p
                ON sl.personid = p.personid AND LOWER(tl.lastname) = LOWER(p.lastname) AND (LOWER(tl.firstnamesource) = LOWER(p.firstname) OR LOWER(tl.firstname) = LOWER(p.firstname))
                WHERE tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NULL AND tl.dwgenderid IS NULL AND tl.dateofbirth IS NULL AND tl.dddlearnerid IS NULL AND tl.accessionnumber IS NULL AND tl.luritsnumber IS NULL AND tl.idnumber IS NULL AND (tl.dddlearnerid IS NULL AND tl.cemislearnerid IS NULL) AND sl.schoolid = tl.dwschoolid;
            /* AND (((P.DDDLearnerID IS NULL AND TL.DDDLearnerID IS NULL) OR (P.DDDLearnerID IS NULL AND TL.DDDLearnerID IS NOT NULL)) */
            /* OR ((P.CEMISNumber IS NULL AND TL.CEMISLearnerID IS NULL) OR (P.CEMISNumber IS NULL AND TL.CEMISLearnerID IS NOT NULL))) */
            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

            IF EXISTS (SELECT
                dwpersonid
                FROM t$learner
                WHERE dwpersonid IS NOT NULL
                GROUP BY dwpersonid
                HAVING COUNT(*) > 1) THEN
                IF par_DebugMode >= 2 THEN
                    OPEN p_refcur_4 FOR
                    SELECT
                        'Duplicate DWPersonID(s)' AS "Duplicate DWPersonID(s)", *
                        FROM t$learner
                        WHERE dwpersonid IN (SELECT
                            dwpersonid
                            FROM t$learner
                            WHERE dwpersonid IS NOT NULL
                            GROUP BY dwpersonid
                            HAVING COUNT(*) > 1)
                        ORDER BY rownumber NULLS FIRST;
                END IF;
                SELECT
                    string_agg(CAST (tl.rownumber AS VARCHAR(20)), ',')
                    INTO var_LogData
                    FROM t$learner AS tl
                    WHERE dwpersonid IN (SELECT
                        dwpersonid
                        FROM t$learner
                        WHERE dwpersonid IS NOT NULL
                        GROUP BY dwpersonid
                        HAVING COUNT(*) > 1);
                /* ORDER BY RowNumber; */
                var_LogData := 'Row numbers: ' || COALESCE(var_LogData, '');
                CALL internal.pdataimportloginsert(par_DataImportID, 'Duplicate DWPersonID(s)', 'X', var_LogData);
            END IF;

            IF par_DebugMode >= 0 THEN
                OPEN p_refcur_5 FOR
                WITH results (rownumber, schoollearnerid)
                AS (SELECT
                    slx.rownumber, sl.schoollearnerid AS dwschoollearnerid
                    FROM (SELECT
                        tl.rownumber, tl.dwpersonid, MAX(sl.enddate) AS maxenddate
                        FROM t$learner AS tl
                        JOIN dbo.schoollearner AS sl
                            ON tl.dwpersonid = sl.personid
                        WHERE tl.dwpersonid IS NOT NULL AND tl.dwschoollearnerid IS NULL
                        GROUP BY tl.rownumber, tl.dwpersonid) AS slx
                    JOIN dbo.schoollearner AS sl
                        ON slx.dwpersonid = sl.personid AND slx.maxenddate = sl.enddate
                UNION
                SELECT
                    tl.rownumber, tl.dwschoollearnerid
                    FROM t$learner AS tl
                    WHERE tl.dwpersonid IS NOT NULL AND tl.dwschoollearnerid IS NOT NULL), learners (rownumber, dwpersonid, firstname, lastname, whichschool, schoolid, name)
                AS (SELECT
                    x.rownumber, tl.dwpersonid, tl.firstname, tl.lastname,
                    CASE
                        WHEN s.schoolid = tl.dwschoolid THEN 'Current School'
                        ELSE 'Previous School'
                    END AS whichschool, s.schoolid, s.name AS schoolname
                    FROM results AS x
                    JOIN t$learner AS tl
                        ON x.rownumber = tl.rownumber
                    LEFT OUTER JOIN dbo.schoollearner AS sl
                        ON x.schoollearnerid = sl.schoollearnerid
                    LEFT OUTER JOIN dbo.school AS s
                        ON sl.schoolid = s.schoolid), re (dwpersonid, studentid, username)
                AS (SELECT
                    x.dwpersonid, peidre.studentid,
                    CASE
                        WHEN pp.personid IS NOT NULL THEN pp.username
                        ELSE pph.username
                    END
                    FROM learners AS x
                    LEFT OUTER JOIN dbo.vwpersonreadingeggsstudentid AS peidre
                        ON x.dwpersonid = peidre.personid
                    LEFT OUTER JOIN dbo.personhasprogramme AS pp
                        ON x.dwpersonid = pp.personid AND pp.programmeid = 4
                    LEFT OUTER JOIN (SELECT
                        pph.*
                        FROM (SELECT
                            x.dwpersonid, MAX(pph.sysstarttime) AS latest
                            FROM learners AS x
                            JOIN dbo.personhasprogrammehistory AS pph
                                ON x.dwpersonid = pph.personid AND pph.programmeid = 4
                            GROUP BY x.dwpersonid) AS x
                        JOIN dbo.personhasprogrammehistory AS pph
                            ON x.dwpersonid = pph.personid AND pph.programmeid = 4 AND x.latest = pph.sysstarttime) AS pph
                        ON x.dwpersonid = pph.personid)
                SELECT
                    'Matched Learners' AS "Matched Learners", REPLACE(CAST (CAST (var_SourceDataDate AS DATE) AS VARCHAR(10)), '/', '') AS filedate, x.rownumber AS "File Line No", x.firstname, x.lastname, x.whichschool, x.name,
                    CASE
                        WHEN re.StudentID IS NULL AND dea.itemid IS NOT NULL THEN 'Processed before: ' || aws_sqlserver_ext.conv_datetime_to_string('VARCHAR(10)', 'DATETIME', dea.audittimestamp, 121)
                        WHEN re.StudentID IS NOT NULL THEN CAST (re.StudentID AS VARCHAR(40))
                        ELSE ''
                    END AS "RE Student ID",
                    CASE
                        WHEN re.UserName IS NULL THEN ''
                        ELSE re.UserName
                    END AS "RE Login", x.dwpersonid AS personid, x.schoolid
                    FROM learners AS x
                    LEFT OUTER JOIN re AS re
                        ON x.dwpersonid = re.DWPersonID
                    LEFT OUTER JOIN internal.dataexportaudit AS dea
                        ON dea.programmeid = 4 AND LOWER(dea.item) = LOWER('Person') AND dea.itemid = x.dwpersonid
                    ORDER BY schoolname NULLS FIRST, lastname NULLS FIRST, firstname NULLS FIRST;
            END IF;
        END IF;
		
        /* Update Person */
        DELETE FROM t$itemid;
RAISE NOTICE 'par_UpdatePersonInfoOnly1: %', par_UpdatePersonInfoOnly;
        IF par_UpdatePersonInfoOnly = 1 THEN
            --CALL internal.ptemporalstatus('dbo.Learner', 0, return_code => ptemporalstatus$ReturnCode);
        END IF;
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        UPDATE P SET
                    P.FirstName             = CASE WHEN TL.FirstName = '' THEN P.FirstName ELSE TL.FirstName END,
                    P.SecondName            = CASE WHEN TL.SecondName = '' OR TL.SecondName IS NULL THEN P.SecondName ELSE TL.SecondName END,
                    P.ThirdName             = CASE WHEN TL.ThirdName = '' OR TL.ThirdName IS NULL THEN P.ThirdName ELSE TL.ThirdName END,
                    P.LastName              = CASE WHEN TL.LastName = '' THEN P.LastName ELSE TL.LastName END,
                    P.BirthDate             = ISNULL(TL.DateOfBirth, P.BirthDate),
                    P.CitizenshipTypeID     = ISNULL(TL.DWCitizenshipTypeID, P.CitizenshipTypeID),
                    P.GenderID              = ISNULL(TL.DWGenderID, P.GenderID),
                    P.EthnicityID           = ISNULL(TL.DWEthnicityID, P.EthnicityID),
                    P.HomeLanguageID        = ISNULL(TL.DWHomeLanguageID, P.HomeLanguageID),
                    P.CountryID             = ISNULL(TL.DWCountryID, P.CountryID)
                OUTPUT inserted.PersonID INTO #ItemID(ItemID)
                FROM Person P
                JOIN #Learner TL ON P.PersonID = TL.DWPersonID
                WHERE ((P.FirstName IS NULL AND TL.FirstName IS NOT NULL) OR (P.FirstName IS NOT NULL AND TL.FirstName IS NULL) OR  (P.FirstName <> TL.FirstName COLLATE Latin1_General_CS_AS))
                    OR ((P.LastName IS NULL AND TL.LastName IS NOT NULL) OR (P.LastName IS NOT NULL AND TL.LastName IS NULL) OR  (P.LastName <> TL.LastName COLLATE Latin1_General_CS_AS))
                    OR ((P.BirthDate IS NULL AND TL.DateOfBirth IS NOT NULL) OR (P.BirthDate IS NOT NULL AND TL.DateOfBirth IS NULL) OR  (P.BirthDate <> TL.DateOfBirth))
                    OR ((P.CitizenshipTypeID IS NULL AND TL.DWCitizenshipTypeID IS NOT NULL) OR (P.CitizenshipTypeID IS NOT NULL AND TL.DWCitizenshipTypeID IS NULL) OR  (P.CitizenshipTypeID <> TL.DWCitizenshipTypeID))
                    OR ((P.GenderID IS NULL AND TL.DWGenderID IS NOT NULL) OR (P.GenderID IS NOT NULL AND TL.DWGenderID IS NULL) OR  (P.GenderID <> TL.DWGenderID))
                    OR ((P.EthnicityID IS NULL AND TL.DWEthnicityID IS NOT NULL) OR (P.EthnicityID IS NOT NULL AND TL.DWEthnicityID IS NULL) OR  (P.EthnicityID <> TL.DWEthnicityID))
                    OR ((P.HomeLanguageID IS NULL AND TL.DWHomeLanguageID IS NOT NULL) OR (P.HomeLanguageID IS NOT NULL AND TL.DWHomeLanguageID IS NULL) OR  (P.HomeLanguageID <> TL.DWHomeLanguageID))
                    OR ((P.CountryID IS NULL AND TL.DWCountryID IS NOT NULL) OR (P.CountryID IS NOT NULL AND TL.DWCountryID IS NULL) OR  (P.CountryID <> TL.DWCountryID))
        */
       
        WITH rows AS(
            UPDATE dbo.Person S SET
                FirstName             = CASE WHEN TL.FirstName = '' THEN P.FirstName ELSE TL.FirstName END,
                SecondName            = CASE WHEN TL.SecondName = '' OR TL.SecondName IS NULL THEN P.SecondName ELSE TL.SecondName END,
                ThirdName             = CASE WHEN TL.ThirdName = '' OR TL.ThirdName IS NULL THEN P.ThirdName ELSE TL.ThirdName END,
                LastName              = CASE WHEN TL.LastName = '' THEN P.LastName ELSE TL.LastName END,
                BirthDate             = COALESCE(TL.DateOfBirth, P.BirthDate),
                CitizenshipTypeID     = COALESCE(TL.DWCitizenshipTypeID, P.CitizenshipTypeID),
                GenderID              = COALESCE(TL.DWGenderID, P.GenderID),
                EthnicityID           = COALESCE(TL.DWEthnicityID, P.EthnicityID),
                HomeLanguageID        = COALESCE(TL.DWHomeLanguageID, P.HomeLanguageID),
                CountryID             = COALESCE(TL.DWCountryID, P.CountryID)
            FROM dbo.Person P
            JOIN t$Learner TL ON P.PersonID = TL.DWPersonID
            WHERE S.PersonID = p.PersonID 
				AND	(((P.FirstName IS NULL AND TL.FirstName IS NOT NULL) OR (P.FirstName IS NOT NULL AND TL.FirstName IS NULL) OR  (P.FirstName <> TL.FirstName))
                OR ((P.LastName IS NULL AND TL.LastName IS NOT NULL) OR (P.LastName IS NOT NULL AND TL.LastName IS NULL) OR  (P.LastName <> TL.LastName))
                OR ((P.BirthDate IS NULL AND TL.DateOfBirth IS NOT NULL) OR (P.BirthDate IS NOT NULL AND TL.DateOfBirth IS NULL) OR  (P.BirthDate <> TL.DateOfBirth))
                OR ((P.CitizenshipTypeID IS NULL AND TL.DWCitizenshipTypeID IS NOT NULL) OR (P.CitizenshipTypeID IS NOT NULL AND TL.DWCitizenshipTypeID IS NULL) OR  (P.CitizenshipTypeID <> TL.DWCitizenshipTypeID))
                OR ((P.GenderID IS NULL AND TL.DWGenderID IS NOT NULL) OR (P.GenderID IS NOT NULL AND TL.DWGenderID IS NULL) OR  (P.GenderID <> TL.DWGenderID))
                OR ((P.EthnicityID IS NULL AND TL.DWEthnicityID IS NOT NULL) OR (P.EthnicityID IS NOT NULL AND TL.DWEthnicityID IS NULL) OR  (P.EthnicityID <> TL.DWEthnicityID))
                OR ((P.HomeLanguageID IS NULL AND TL.DWHomeLanguageID IS NOT NULL) OR (P.HomeLanguageID IS NOT NULL AND TL.DWHomeLanguageID IS NULL) OR  (P.HomeLanguageID <> TL.DWHomeLanguageID))
                OR ((P.CountryID IS NULL AND TL.DWCountryID IS NOT NULL) OR (P.CountryID IS NOT NULL AND TL.DWCountryID IS NULL) OR  (P.CountryID <> TL.DWCountryID)))
                RETURNING P.PersonID 
        )
        INSERT INTO t$itemid(ItemID)
        SELECT PersonID
        FROM rows;        
     
RAISE NOTICE 'UPDATE person';

        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

RAISE NOTICE 'par_UpdatePersonInfoOnly: %', par_UpdatePersonInfoOnly;
        IF par_UpdatePersonInfoOnly = 1 THEN
            --CALL internal.ptemporalstatus('dbo.Learner', 1, return_code => ptemporalstatus$ReturnCode);
        END IF;
		
        INSERT INTO internal.dataimportaudit (dataimportid, action, item, itemid, detail)
        SELECT
            par_DataImportID, 'U', 'Person', itemid, p.firstname || ' ' || p.lastname
            FROM t$itemid AS i
            JOIN dbo.person AS p
                ON i.itemid = p.personid;
        /* Insert Person */

        IF par_UpdatePersonInfoOnly = 0 AND EXISTS (SELECT
            *
            FROM t$learner
            WHERE dwpersonid IS NULL) THEN
		RAISE NOTICE 'var_MaxPersonID: %', var_MaxPersonID;        
            IF var_MaxPersonID IS NOT NULL THEN
                IF EXISTS (SELECT
                    *
                    FROM t$learner AS tl
                    JOIN dbo.person AS p
                        ON tl.existingpersonid = p.personid) THEN
                    
					CALL internal.pdataimportloginsert(par_DataImportID, 'PersonID(s) already allocated.', 'X');
                    return_code := 1;
                    RETURN;
                END IF;
            END IF;
            /* SET @MaxPersonID = dbo.SEQUENCE_CURRENT('Person'); */
          
		  UPDATE t$learner AS l
            SET dwpersonid = existingpersonid, newperson = 0
            FROM dbo.person AS p
                WHERE l.existingpersonid IS NOT NULL AND l.dwpersonid IS NULL AND l.existingpersonid = p.personid;
				
RAISE NOTICE 'UPDATE t$learner6';                
            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
            /*
            [7927 - Severity CRITICAL - PostgreSQL doesn't support OUTER joins for self-reference to the label from UPDATE / DELETE in the FROM clause and without PK on table-for-update. Perform a manual conversion.]
            UPDATE L SET DWPersonID = ExistingPersonID, NewPerson = 1
                        FROM #Learner L
                        LEFT JOIN Person P ON L.ExistingPersonID = P.PersonID
                        WHERE L.ExistingPersonID IS NOT NULL AND L.DWPersonID IS NULL AND P.PersonID IS NULL
            */
--             UPDATE t$Learner S
--             SET DWPersonID = L.ExistingPersonID, 
--                 NewPerson = 1
--             FROM t$Learner L
--             LEFT JOIN dbo.Person P ON L.ExistingPersonID = P.PersonID
--             WHERE L.ExistingPersonID IS NOT NULL AND L.DWPersonID IS NULL AND P.PersonID IS NULL AND S.RowNumber = L.RowNumber; 
			
			UPDATE t$Learner S
            SET DWPersonID = L.ExistingPersonID, 
                NewPerson = 1
            FROM t$Learner L
            LEFT JOIN dbo.Person P ON L.ExistingPersonID = P.PersonID
            WHERE L.ExistingPersonID IS NOT NULL AND L.DWPersonID IS NULL AND P.PersonID IS NULL; -- AND S.RowNumber = L.RowNumber; 
			
RAISE NOTICE 'UPDATE t$learner7'; 

            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

            IF var_MaxPersonID IS NOT NULL AND dbo.sequence_current('Person') < var_MaxPersonID THEN
                --CALL ClickCOMMON.dbo.pSetSequenceNextValue('ClickDW', 'dbo', 'Person', var_PersonNextValue);
            END IF;
			
            UPDATE t$learner AS tl
            SET dwpersonid = nextval('dbo.personsequence'), newperson = 1
                WHERE tl.dwpersonid IS NULL;
				
RAISE NOTICE 'UPDATE t$learner8';                

            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
            DELETE FROM t$itemid;
			
RAISE NOTICE 'DELETE FROM t$itemid';            
/* To be checked. Not working in SP
            WITH ins_stat (ItemID)
            AS (INSERT INTO dbo.person (personid, firstname, secondname, thirdname, lastname, birthdate, citizenshiptypeid, genderid, ethnicityid, homelanguageid, countryid)
            SELECT DISTINCT
                tl.dwpersonid, tl.firstname, tl.secondname, tl.thirdname, tl.lastname, tl.dateofbirth, tl.dwcitizenshiptypeid, tl.dwgenderid, tl.dwethnicityid, tl.dwhomelanguageid, tl.dwcountryid
                FROM t$learner AS tl
                WHERE tl.newperson = 1
            RETURNING personid)
            INSERT INTO t$itemid (ItemID)
            SELECT
                *
                FROM ins_stat;
*/        

WITH ins_stat (ItemID)
            AS (INSERT  INTO dbo.person (personid, firstname, secondname, thirdname, lastname, birthdate, citizenshiptypeid, genderid, ethnicityid, homelanguageid, countryid,sysstarttime,sysendtime  )
            SELECT DISTINCT
                tl.dwpersonid, tl.firstname, tl.secondname, tl.thirdname, tl.lastname, tl.dateofbirth, tl.dwcitizenshiptypeid, tl.dwgenderid, tl.dwethnicityid, tl.dwhomelanguageid, tl.dwcountryid,MAKE_DATE(EXTRACT(YEAR FROM tl.DateJoined)::int,1,1),'9999-12-31 23:59:59'::timestamp without time zone
                FROM t$learner AS tl
                WHERE tl.newperson = 1
            RETURNING personid) 
            INSERT INTO t$itemid (ItemID)
            SELECT
                *
                FROM ins_stat;
				
RAISE NOTICE 'UPDATE t$learner9 - to be checked';                

			GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
			
            INSERT INTO internal.dataimportaudit (dataimportid, action, item, itemid, detail)
            SELECT
                par_DataImportID, 'C', 'Person', itemid, p.firstname || ' ' || p.lastname
                FROM t$itemid AS i
                JOIN dbo.person AS p ON i.itemid = p.personid;
					
RAISE NOTICE 'INSERT INTO internal.dataimportaudit';

            /* Insert PersonRoleHasPerson */
/* To be checked. Not working in SP            
            INSERT INTO dbo.personrolehasperson (personroleid, personid)
            SELECT DISTINCT
                r.personroleid, tl.dwpersonid
                FROM t$learner AS tl
                JOIN dbo.personrole AS r
                    ON LOWER(r.code) = LOWER('LE')
                WHERE tl.newperson = 1;
*/                
            /* TL.DWPersonID > @MaxPersonID; */
RAISE NOTICE 'INSERT INTO dbo.personrolehasperson - to be checked';            
            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        END IF;

        /* Update PersonExternalID */
        UPDATE dbo.personexternalid AS t
        SET externalid = s.externalid
        FROM (SELECT DISTINCT
            tl.dwpersonid, tl.idnumber AS externalid, 1 AS personexternalidtype
            /* IDNumber */
            FROM t$learner AS tl
            WHERE tl.dwpersonid IS NOT NULL 
                AND tl.idnumber IS NOT NULL 
                AND LENGTH(tl.idnumber) = 13 
                AND aws_sqlserver_ext.isnumeric(tl.idnumber)::int = 1
        UNION
        SELECT
            tl.dwpersonid, tl.passportnumber, 3
            /* Passport Number */
            FROM t$learner AS tl
            WHERE tl.dwpersonid IS NOT NULL AND tl.passportnumber IS NOT NULL AND LENGTH(tl.passportnumber) >= 2
        UNION
        SELECT
            tl.dwpersonid, tl.accessionnumber, 8
            /* Accession Number */
            FROM t$learner AS tl
            WHERE tl.dwpersonid IS NOT NULL AND tl.accessionnumber IS NOT NULL AND LENGTH(tl.accessionnumber) >= 1
        UNION
        SELECT
            tl.dwpersonid, tl.luritsnumber, 7
            /* LURITS Number */
            FROM t$learner AS tl
            WHERE tl.dwpersonid IS NOT NULL AND tl.luritsnumber IS NOT NULL AND LENGTH(tl.luritsnumber) >= 1
        UNION
        SELECT
            tl.dwpersonid, tl.cemislearnerid, 6
            FROM t$learner AS tl
            WHERE tl.dwpersonid IS NOT NULL AND tl.cemislearnerid IS NOT NULL AND LENGTH(tl.cemislearnerid) >= 1) AS s
        WHERE s.dwpersonid = t.personid AND s.personexternalidtype = t.personexternalidtype AND t.externalid <> s.externalid;
		
RAISE NOTICE 'UPDATE dbo.personexternalid';             

        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

	/* Insert PersonExternalID */
    /*    INSERT INTO dbo.personexternalid (personid, externalid, personexternalidtype)
        SELECT DISTINCT
            s.dwpersonid, s.externalid, s.personexternalidtype
            FROM (SELECT DISTINCT
                tl.dwpersonid, tl.dddlearnerid AS externalid, 9 AS personexternalidtype
                /* DDD LearnerID */
                FROM t$learner AS tl
                WHERE tl.dwpersonid IS NOT NULL AND tl.dddlearnerid IS NOT NULL
            UNION
            SELECT
                tl.dwpersonid, tl.idnumber, 1
                /* IDNumber */
                FROM t$learner AS tl
                WHERE tl.dwpersonid IS NOT NULL 
                    AND tl.idnumber IS NOT NULL 
                    AND LENGTH(tl.idnumber) = 13 
                    AND aws_sqlserver_ext.isnumeric(tl.idnumber)::int = 1
            UNION
            SELECT
                tl.dwpersonid, tl.passportnumber, 3
                /* Passport Number */
                FROM t$learner AS tl
                WHERE tl.dwpersonid IS NOT NULL AND tl.passportnumber IS NOT NULL AND LENGTH(tl.passportnumber) >= 2
            UNION
            /* SELECT TL.DWPersonID, TL.AccessionNumber, 8      -- Accession Number */
            /* FROM #Learner TL */
            /* WHERE TL.DWPersonID IS NOT NULL AND TL.AccessionNumber IS NOT NULL AND LEN(TL.AccessionNumber) >= 1 */
            /* UNION */
            SELECT
                tl.dwpersonid, tl.luritsnumber, 7
                /* LURITS Number */
                FROM t$learner AS tl
                WHERE tl.dwpersonid IS NOT NULL AND tl.luritsnumber IS NOT NULL AND LENGTH(tl.luritsnumber) >= 1
            UNION
            SELECT
                tl.dwpersonid, tl.cemislearnerid, 6
                FROM t$learner AS tl
                WHERE tl.dwpersonid IS NOT NULL AND tl.cemislearnerid IS NOT NULL AND LENGTH(tl.cemislearnerid) >= 1) AS s
            LEFT OUTER JOIN dbo.personexternalid AS t
                ON s.dwpersonid = t.personid AND s.personexternalidtype = t.personexternalidtype
            WHERE t.personid IS NULL;
			*/

INSERT INTO dbo.personexternalid (personid, externalid, personexternalidtype,sysstarttime  ,sysendtime  )				
SELECT DISTINCT
            s.dwpersonid, s.externalid, s.personexternalidtype  ,MAKE_DATE(EXTRACT(YEAR FROM s.DateJoined)::int,1,1),'9999-12-31 23:59:59'::timestamp without time zone
            FROM (SELECT DISTINCT
                tl.dwpersonid, tl.dddlearnerid AS externalid, 9 AS personexternalidtype,tl.datejoined
                /* DDD LearnerID */
                FROM t$learner AS tl
                WHERE tl.dwpersonid IS NOT NULL AND tl.dddlearnerid IS NOT NULL
            UNION
            SELECT
                tl.dwpersonid, tl.idnumber, 1,tl.datejoined
                /* IDNumber */
                FROM t$learner AS tl
                WHERE tl.dwpersonid IS NOT NULL 
                    AND tl.idnumber IS NOT NULL 
                    AND LENGTH(tl.idnumber) = 13 
                    AND aws_sqlserver_ext.isnumeric(tl.idnumber)::int = 1
            UNION
            SELECT
                tl.dwpersonid, tl.passportnumber, 3,tl.datejoined
                /* Passport Number */
                FROM t$learner AS tl
                WHERE tl.dwpersonid IS NOT NULL AND tl.passportnumber IS NOT NULL AND LENGTH(tl.passportnumber) >= 2
            UNION
            /* SELECT TL.DWPersonID, TL.AccessionNumber, 8      -- Accession Number */
            /* FROM #Learner TL */
            /* WHERE TL.DWPersonID IS NOT NULL AND TL.AccessionNumber IS NOT NULL AND LEN(TL.AccessionNumber) >= 1 */
            /* UNION */
            SELECT
                tl.dwpersonid, tl.luritsnumber, 7,tl.datejoined
                /* LURITS Number */
                FROM t$learner AS tl
                WHERE tl.dwpersonid IS NOT NULL AND tl.luritsnumber IS NOT NULL AND LENGTH(tl.luritsnumber) >= 1
            UNION
            SELECT
                tl.dwpersonid, tl.cemislearnerid, 6 ,tl.datejoined
                FROM t$learner AS tl
                WHERE tl.dwpersonid IS NOT NULL AND tl.cemislearnerid IS NOT NULL AND LENGTH(tl.cemislearnerid) >= 1) AS s
            LEFT OUTER JOIN dbo.personexternalid AS t
                ON s.dwpersonid = t.personid AND s.personexternalidtype = t.personexternalidtype
            WHERE t.personid IS NULL
			ON CONFLICT DO NOTHING; -- This is added to not add duplicates Remove after testing
			

RAISE NOTICE 'INSERT INTO dbo.personexternalid - to be checked';             

        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
		RAISE NOTICE 'var_LogDescription: %',var_LogDescription;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

RAISE NOTICE 'par_UpdatePersonInfoOnly2: %', par_UpdatePersonInfoOnly;
        IF par_UpdatePersonInfoOnly = 0 THEN
            /* SchoolClass */
            CREATE TEMPORARY TABLE t$schoolclass
            (seq BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
                schoolclassid INTEGER NULL,
                schoolid INTEGER NOT NULL,
                gradeid SMALLINT NOT NULL,
                sasamsclassname VARCHAR(100) NOT NULL,
                classname VARCHAR(100) NOT NULL,
                year SMALLINT NOT NULL,
                newschoolclass NUMERIC(1, 0) DEFAULT (0) NOT NULL);
RAISE NOTICE 'CREATE TEMPORARY TABLE t$schoolclass';              
            
            INSERT INTO t$schoolclass (schoolid, gradeid, sasamsclassname, classname, year)
            SELECT DISTINCT
                tl.dwschoolid, tl.grade, COALESCE(tl.classname, 'Unknown') AS sasamsclassname, COALESCE(tl.classname, 'Unknown') AS classname, var_SourceDataDateYear AS year
                FROM t$learner AS tl
                WHERE tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NOT NULL
                /* AND Class IS NOT NULL */
                ORDER BY dwschoolid NULLS FIRST, grade NULLS FIRST, classname NULLS FIRST;
           
RAISE NOTICE 'INSERT INTO t$schoolclass1 - to be checked';                

            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

            UPDATE t$schoolclass AS x
            SET schoolclassid = sc.schoolclassid
            FROM dbo.schoolclass AS sc
                WHERE x.schoolid = sc.schoolid AND x.year = sc.year AND x.gradeid = sc.gradeid 
                    AND (LOWER(x.classname) = LOWER(sc.classname) OR LOWER(x.classname) = LOWER(sc.sasamsclassname) OR LOWER(x.classname) = LOWER(sc.internalclassname) OR LOWER(x.classname) = LOWER(sc.matchclassname2));
RAISE NOTICE 'UPDATE t$schoolclass1';                

            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
	
			/* 
			'dbo.schoolclasssequence' next number was used in dbo.schoolclass 
			SELECT
                max(schoolclassid)
            FROM dbo.schoolclass
			and update the next value.
			SELECT setval('dbo.schoolclasssequence',142573, true)
			
			*/
			UPDATE t$schoolclass
            SET schoolclassid = nextval('dbo.schoolclasssequence'), newschoolclass = 1
                WHERE schoolclassid IS NULL;
				
RAISE NOTICE 'UPDATE t$schoolclass2';                

            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

            INSERT INTO dbo.schoolclass (schoolclassid, schoolid, gradeid, year, classname, sasamsclassname)
            SELECT
                schoolclassid, schoolid, gradeid, year, classname, sasamsclassname
            FROM t$schoolclass
            WHERE newschoolclass = 1;
RAISE NOTICE 'INSERT INTO dbo.schoolclass1';                

            /*
            [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
            UPDATE TL SET TL.DWSchoolClassID = TSC.SchoolClassID
                        FROM #Learner TL
                        JOIN #SchoolClass TSC ON TSC.SchoolID = TL.DWSchoolID AND TSC.GradeID = TL.Grade AND TSC.[Year] = @SourceDataDateYear AND TSC.SASAMSClassName = ISNULL(TL.ClassName,'Unknown')
                        WHERE TL.DWSchoolID IS NOT NULL AND TL.DWPersonID IS NOT NULL
            */ /* AND TL.Class IS NOT NULL */
            UPDATE t$Learner S
            SET DWSchoolClassID = TSC.SchoolClassID
            FROM t$Learner TL
            JOIN t$SchoolClass TSC ON TSC.SchoolID = TL.DWSchoolID 
                AND TSC.GradeID = TL.Grade 
                AND TSC.Year = var_SourceDataDateYear 
                AND TSC.SASAMSClassName = COALESCE(TL.ClassName,'Unknown')
            WHERE TL.DWSchoolID IS NOT NULL AND TL.DWPersonID IS NOT NULL AND S.RowNumber = TL.RowNumber;            
RAISE NOTICE 'UPDATE t$Learner10';                

            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

RAISE NOTICE 'par_DebugMode: %', par_DebugMode;                
            IF par_DebugMode >= 2 THEN
                OPEN p_refcur_6 FOR
                SELECT
                    '#Learner' AS "#Learner", *
                    FROM t$learner;
            END IF;
		
RAISE NOTICE 'update learner with schoollearnerid';

            /* SchoolLearner */
--             UPDATE t$learner S
--             SET dwschoollearnerid = sl.schoollearnerid
--             FROM t$learner AS tl
            
-- 			CROSS JOIN LATERAL (SELECT
--                 *
--                 FROM dbo.schoollearner AS sl
--                 WHERE tl.dwschoolid = sl.schoolid AND tl.dwpersonid = sl.personid AND (sl.startdate = '0001-01-01' OR (ABS(aws_sqlserver_ext.datediff('day', tl.datejoined::TIMESTAMP, sl.startdate::TIMESTAMP)) <= 14) OR (sl.startdate <> '0001-01-01' AND tl.datejoined = '0001-01-01'))
--                 ORDER BY sl.enddate DESC NULLS FIRST
--                 LIMIT 1) AS sl
			
-- 			/*
-- 			LEFT JOIN (
--                 SELECT DISTINCT (schoolid,personid),*
--                 FROM dbo.schoollearner AS sl
-- 				JOIN t$learner AS tl on tl.dwschoolid = sl.schoolid AND tl.dwpersonid = sl.personid 
-- 	 			WHERE (sl.startdate = '0001-01-01' OR (ABS(aws_sqlserver_ext.datediff('day', tl.datejoined::TIMESTAMP, sl.startdate::TIMESTAMP)) <= 14) OR (sl.startdate <> '0001-01-01' AND tl.datejoined = '0001-01-01'))
-- -- 				ORDER BY sl.enddate DESC NULLS FIRST
--             ) AS sl ON tl.dwschoolid = sl.schoolid AND tl.dwpersonid = sl.personid 
-- 		WHERE tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NOT NULL AND tl.dwschoollearnerid IS NULL;
-- 			*/
-- 			WHERE tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NOT NULL AND tl.dwschoollearnerid IS NULL and S.RowNumber = tl.RowNumber;

		UPDATE t$learner S
					SET dwschoollearnerid = sl.schoollearnerid
					FROM t$learner AS tl            
					LEFT JOIN  (SELECT DISTINCT 
						*
						FROM dbo.schoollearner AS sl

						ORDER BY sl.enddate DESC NULLS FIRST
						) AS sl ON tl.dwschoolid = sl.schoolid AND tl.dwpersonid = sl.personid AND (sl.startdate = '0001-01-01' 
											 OR (ABS(aws_sqlserver_ext.datediff('day', tl.datejoined::TIMESTAMP, sl.startdate::TIMESTAMP)) <= 14) 
											 OR (sl.startdate <> '0001-01-01' AND tl.datejoined = '0001-01-01')
											)		

					WHERE tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NOT NULL AND tl.dwschoollearnerid IS NULL   and S.RowNumber = tl.RowNumber;
			
RAISE NOTICE 'UPDATE t$learner11';

            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

            IF par_DebugMode >= 2 THEN
                OPEN p_refcur_7 FOR
                SELECT
                    'SchoolLearner - Fixed EndDate' AS "SchoolLearner - Fixed EndDate", sl.schoolid, tl.dwschoolid, sl.personid, tl.dwpersonid, sl.schoollearnerid, tl.dwschoollearnerid, sl.startdate, tl.datejoined, sl.enddate,
                    CASE
                        WHEN date_part('month', tl.datejoined) = 1 AND make_date(var_SourceDataDateYear - 1, 12, 31) > sl.startdate THEN make_date(var_SourceDataDateYear - 1, 12, 31)
                        ELSE tl.datejoined + (- 1::NUMERIC || ' DAY')::INTERVAL
                    END AS newenddate
                    FROM dbo.schoollearner AS sl
                    JOIN t$learner AS tl
                        ON sl.personid = tl.dwpersonid AND sl.enddate > tl.datejoined AND (tl.dwschoollearnerid IS NULL OR tl.dwschoollearnerid <> sl.schoollearnerid)
                    WHERE tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NOT NULL AND tl.datejoined <> '0001-01-01';
            END IF;

            UPDATE dbo.schoollearner  S --AS sl1
            SET enddate = u.newenddate
            FROM dbo.schoollearner AS sl
            JOIN (SELECT
                sl.schoollearnerid, tl.datejoined,
                CASE
                    WHEN date_part('month', tl.datejoined) = 1 
                        AND make_date(date_part('year', tl.datejoined)::int - 1, 12, 31) > sl.startdate 
                    THEN make_date(date_part('year', tl.datejoined)::int - 1, 12, 31)
                    ELSE tl.datejoined + (- 1::NUMERIC || ' DAY')::INTERVAL
                END AS newenddate
                FROM dbo.schoollearner AS sl
                JOIN t$learner AS tl
                    ON sl.personid = tl.dwpersonid 
                    AND sl.enddate > tl.datejoined 
                    AND (tl.dwschoollearnerid IS NULL OR tl.dwschoollearnerid <> sl.schoollearnerid)
                	WHERE tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NOT NULL AND tl.datejoined <> '0001-01-01') AS u
                ON sl.schoollearnerid = u.schoollearnerid
				WHERE sl.schoollearnerid = S.schoollearnerid;
             
RAISE NOTICE 'UPDATE dbo.schoollearner1';

            UPDATE dbo.schoollearner AS t
            SET startdate =
            CASE
                WHEN s.datejoined = '0001-01-01' THEN t.startdate
                ELSE s.datejoined
            END, enddate = s.dateleft, accessionno = s.accessionnumber, initialgradeid =
            CASE
                WHEN t.initialgradeid IS NULL THEN s.grade
                ELSE t.initialgradeid
            END, instructionlanguageid = s.dwinstructionlanguageid
            FROM t$learner AS s
                WHERE (t.enddate = s.dateleft) 
                    AND ((t.startdate = s.datejoined AND s.datejoined != '0001-01-01') 
                        OR ((LOWER(t.accessionno) <> LOWER(s.accessionnumber)) 
                        OR (t.accessionno IS NULL AND s.accessionnumber IS NOT NULL)) 
                        OR (t.initialgradeid IS NULL AND s.grade IS NOT NULL) 
                        OR ((t.instructionlanguageid <> s.dwinstructionlanguageid) 
                        OR (t.instructionlanguageid IS NULL AND s.dwinstructionlanguageid IS NOT NULL))) 
                    AND t.schoollearnerid = s.dwschoollearnerid;
RAISE NOTICE 'UPDATE dbo.schoollearner2';

            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

            DELETE FROM t$itemid;
RAISE NOTICE 'DELETE FROM t$itemid1';

            WITH upd_stat (ItemID)
            AS (UPDATE dbo.schoollearner AS t
            SET enddate = s.dateleft
            FROM t$learner AS s
                WHERE (t.enddate != s.dateleft) AND t.schoollearnerid = s.dwschoollearnerid
                RETURNING schoollearnerid)
            INSERT INTO t$itemid (ItemID)
            SELECT
                *
                FROM upd_stat;
RAISE NOTICE 'UPDATE dbo.schoollearner2';

            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

            /*
            UPDATE T SET
                T.EndDate = CASE
                        WHEN MONTH(@SourceDataDate) = 1 AND DATEFROMPARTS(@SourceDataDateYear-1,12,31) > T.StartDate THEN DATEFROMPARTS(@SourceDataDateYear-1,12,31)
                        ELSE @SourceDataDate
                    END
            OUTPUT inserted.SchoolLearnerID INTO #ItemID(ItemID)
            FROM SchoolLearner T
            LEFT JOIN #Learner TL ON T.SchoolID = TL.DWSchoolID AND T.PersonID = TL.DWPersonID AND @SourceDataDate BETWEEN T.StartDate AND T.EndDate
            WHERE TL.DWPersonID IS NULL
                AND T.SchoolID IN (SELECT DISTINCT DWSchoolID FROM #Learner)
                AND T.StartDate <> '0001-01-01'
            
            SET @LogDescription = FORMATMESSAGE('SchoolLearner.EndDate - Updated (not in file): %d', @@ROWCOUNT);  EXEC internal.pDataImportLogInsert @DataImportID, @LogDescription;
            */
            /*
            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
            INSERT internal.DataImportAudit([DataImportID], [Action], [Item], [ItemID], [Detail])
                        SELECT @DataImportID, 'U', 'SchoolLearner', ItemID, 'Left ' + FORMAT(SL.EndDate, 'yyyy-MM-dd')
                        FROM #ItemID I JOIN SchoolLearner SL ON I.ItemID = SL.SchoolLearnerID
            */
            INSERT INTO internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
            SELECT par_dataimportid, 'U', 'SchoolLearner', ItemID, concat('Left ', TO_CHAR(SL.EndDate::DATE, 'yyyy-MM-dd'))
            FROM t$itemid I 
            JOIN dbo.SchoolLearner SL ON I.ItemID = SL.SchoolLearnerID;
RAISE NOTICE 'INSERT INTO internal.DataImportAudit1';

			/* 
				select max(schoollearnerid)from dbo.SchoolLearner
				SELECT setval('dbo.schoollearnersequence',1518905, true)
			*/
            UPDATE t$learner AS tl
            SET dwschoollearnerid = nextval('dbo.schoollearnersequence')
                WHERE tl.dwschoollearnerid IS NULL AND tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NOT NULL;
RAISE NOTICE 'UPDATE t$learner12';

            INSERT INTO internal.dataimportaudit (dataimportid, action, item, itemid, detail)
            SELECT
                par_dataimportid, 'N', 'SchoolLearner', tl.dwschoollearnerid, 'Same School'
                FROM t$learner AS tl
                WHERE dwschoollearnerid <= var_MaxSchoolLearnerID AND dwschoollearnerid IS NOT NULL;
RAISE NOTICE 'INSERT INTO internal.dataimportaudit2';

            DELETE FROM t$itemid;

RAISE NOTICE 'DELETE FROM t$itemid2';

            /*
            [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
            SET @Sql = N'
                        INSERT SchoolLearner
                        ([SchoolLearnerID], [SchoolID], [PersonID], [StartDate], [EndDate], [AccessionNo], [InitialGradeID], [LSENStatusID], [InstructionLanguageID], SysStartTime)
                        OUTPUT inserted.SchoolLearnerID INTO #ItemID(ItemID)
                        SELECT DWSchoolLearnerID, DWSchoolID, DWPersonID, DateJoined, DateLeft, AccessionNumber, Grade, NULL, DWInstructionLanguageID, DATEFROMPARTS(YEAR(DateJoined),1,1)
                        FROM #Learner TL
                        WHERE DWSchoolLearnerID > @MaxSchoolLearnerID;
                        SET @RowCount = @@ROWCOUNT;
                        ';
            */
            WITH ins_stat (ItemID)
            AS (INSERT INTO dbo.SchoolLearner
                (SchoolLearnerID, SchoolID, PersonID, StartDate, EndDate, AccessionNo, InitialGradeID, LSENStatusID, InstructionLanguageID,
                 SysStartTime,sysendtime)
            SELECT DWSchoolLearnerID, DWSchoolID, DWPersonID, DateJoined, DateLeft, AccessionNumber, Grade, NULL, DWInstructionLanguageID, 
                MAKE_DATE(EXTRACT(YEAR FROM DateJoined)::int,1,1),'9999-12-31 23:59:59'::timestamp without time zone
            FROM t$Learner TL
            WHERE DWSchoolLearnerID > var_MaxSchoolLearnerID
            RETURNING SchoolLearnerID)
            INSERT INTO t$itemid(ItemID)
                SELECT
                    *
                    FROM ins_stat;
RAISE NOTICE 'INSERT INTO dbo.SchoolLearner1';

            --CALL internal.ptemporalstatus('dbo.SchoolLearner', 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'@MaxSchoolLearnerID bigint, @RowCount int OUTPUT', @MaxSchoolLearnerID=@MaxSchoolLearnerID, @RowCount=@RowCount OUTPUT;
            */
            /*
            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
            --SET @LogDescription = FORMATMESSAGE('SchoolLearner - Inserted: %d', @RowCount);
            */
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
            /*
            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
            INSERT internal.DataImportAudit([DataImportID], [Action], [Item], [ItemID], [Detail])
                        SELECT @DataImportID, 'C', 'SchoolLearner', ItemID, 'Started ' + FORMAT(SL.StartDate, 'yyyy-MM-dd')
                        FROM #ItemID I JOIN SchoolLearner SL ON I.ItemID = SL.SchoolLearnerID
            */
            INSERT INTO internal.DataImportAudit(DataImportID, Action, Item, ItemID, Detail)
            SELECT par_dataimportid, 'C', 'SchoolLearner', ItemID, concat('Started ', TO_CHAR(SL.StartDate::DATE, 'yyyy-MM-dd'))
            FROM t$itemid I 
            JOIN dbo.SchoolLearner SL ON I.ItemID = SL.SchoolLearnerID;
RAISE NOTICE 'INSERT INTO internal.dataimportaudit3';

            /* SchoolLearnerYear */
            
            /*
            [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
            UPDATE TL SET DWSchoolLearnerYearID = SLY.SchoolLearnerYearID
                        FROM #Learner TL
                        JOIN SchoolLearnerYear SLY ON SLY.SchoolLearnerID = TL.DWSchoolLearnerID AND SLY.[Year] = @SourceDataDateYear
                        WHERE TL.DWSchoolID IS NOT NULL AND TL.DWPersonID IS NOT NULL;
            */
            UPDATE t$learner S
            SET DWSchoolLearnerYearID = SLY.SchoolLearnerYearID
            FROM t$learner TL
            JOIN dbo.SchoolLearnerYear SLY ON SLY.SchoolLearnerID = TL.DWSchoolLearnerID AND SLY.Year = var_SourceDataDateYear
            WHERE TL.DWSchoolID IS NOT NULL 
                AND TL.DWPersonID IS NOT NULL AND S.RowNumber = TL.RowNumber;            
RAISE NOTICE 'UPDATE t$learner13 - Slow running';    

            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

            UPDATE dbo.schoollearneryear sly
            SET gradeid = tl.grade, schoolclassid = tl.dwschoolclassid
            FROM t$learner AS tl --, dbo.schoollearneryear AS sly
                WHERE tl.dwschoollearneryearid IS NOT NULL AND 
				(
					(
						(sly.gradeid <> tl.grade) 
                     OR (sly.gradeid IS NULL AND tl.grade IS NOT NULL)
					) 
				OR 
					(
						(sly.schoolclassid <> tl.dwschoolclassid) 
                     OR (sly.schoolclassid IS NULL AND tl.dwschoolclassid IS NOT NULL)
					)
				) 
                AND tl.dwschoollearneryearid = sly.schoollearneryearid;

RAISE NOTICE 'UPDATE dbo.schoollearneryear1 - to be checked';                

            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

            UPDATE t$learner AS tl
            SET dwschoollearneryearid = nextval('dbo.schoollearneryearsequence')
                WHERE tl.dwschoollearneryearid IS NULL AND tl.dwschoollearnerid IS NOT NULL AND tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NOT NULL;
RAISE NOTICE 'UPDATE t$learner14';

            /*
            [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
            SET @Sql = N'
                        INSERT SchoolLearnerYear
                        ([SchoolLearnerYearID], [SchoolLearnerID], [Year], [GradeID], [SchoolClassID], SysStartTime)
                        SELECT TL.DWSchoolLearnerYearID, TL.DWSchoolLearnerID, @SourceDataDateYear, TL.Grade, TL.DWSchoolClassID, DATEFROMPARTS(@SourceDataDateYear,1,1)
                        FROM #Learner TL
                        WHERE TL.DWSchoolLearnerYearID > @MaxSchoolLearnerYearID;
                        SET @RowCount = @@ROWCOUNT;
                        ';
            */
          
            INSERT INTO dbo.SchoolLearnerYear
                (SchoolLearnerYearID, SchoolLearnerID, Year, GradeID, SchoolClassID, SysStartTime,sysendtime)
            SELECT TL.DWSchoolLearnerYearID, TL.DWSchoolLearnerID, var_SourceDataDateYear, TL.Grade, TL.DWSchoolClassID,
                MAKE_DATE(var_SourceDataDateYear::int,1,1),'9999-12-31 23:59:59'::timestamp without time zone
            FROM t$learner TL
            WHERE TL.DWSchoolLearnerYearID > var_MaxSchoolLearnerYearID;
          
RAISE NOTICE 'INSERT INTO dbo.SchoolLearnerYear2 - to be checked';   
         
            --CALL 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'@SourceDataDateYear smallint, @MaxSchoolLearnerYearID bigint, @RowCount int OUTPUT', @SourceDataDateYear=@SourceDataDateYear, @MaxSchoolLearnerYearID=@MaxSchoolLearnerYearID, @RowCount=@RowCount OUTPUT;
            */
            --CALL internal.ptemporalstatus('dbo.SchoolLearnerYear', 1, NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            /*
            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
            SET @LogDescription = FORMATMESSAGE('SchoolLearnerYear - Inserted: %d', @RowCount);
            */
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

		CREATE TEMPORARY TABLE t$personhasprogrammeoutput
            (personid INTEGER,
                programmeid SMALLINT);
				
            WITH ins_stat (personid, programmeid)
            AS (INSERT INTO dbo.personhasprogramme (personid, programmeid, username, password, registerdate,sysstarttime,sysendtime)
            SELECT DISTINCT
                tl.dwpersonid, pe.programmeid,
                CASE
                    WHEN pph.personid IS NOT NULL THEN pph.username
                    ELSE NULL
                END AS username, NULL AS password, CAST (clock_timestamp() AS DATE) AS registerdate,MAKE_DATE(EXTRACT(YEAR FROM tl.DateJoined)::int,1,1),'9999-12-31 23:59:59'::timestamp without time zone
                FROM t$learner AS tl
                JOIN dbo.schoolenvironment AS se
                    ON tl.dwschoolid = se.schoolid
                JOIN dbo.programmeenvironment AS pe
                    ON se.environmentid = pe.environmentid
                JOIN dbo.programmeenvironmenthastargetgrade AS petg
                    ON pe.programmeenvironmentid = petg.programmeenvironmentid AND tl.grade = petg.gradeid AND var_SourceDataDate BETWEEN petg.activefrom AND petg.sysendtime
                LEFT OUTER JOIN dbo.personhasprogramme AS pp
                    ON tl.dwpersonid = pp.personid AND pe.programmeid = pp.programmeid
                LEFT OUTER JOIN (SELECT
                    pph.*
                    FROM (SELECT
                        pph.personid, pph.programmeid, MAX(pph.sysstarttime) AS latest
                        FROM t$learner AS tl
                        JOIN dbo.personhasprogrammehistory AS pph
                            ON tl.dwpersonid = pph.personid
                        GROUP BY pph.personid, pph.programmeid) AS x
                    JOIN dbo.personhasprogrammehistory AS pph
                        ON x.personid = pph.personid AND x.programmeid = pph.programmeid AND x.latest = pph.sysstarttime) AS pph
                    ON tl.dwpersonid = pph.personid AND pe.programmeid = pph.programmeid
                WHERE tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NOT NULL AND pp.personid IS NULL
            RETURNING personid, programmeid)
			
            INSERT INTO t$personhasprogrammeoutput (personid, programmeid)
            SELECT
                *
                FROM ins_stat;
RAISE NOTICE 'CREATE TEMPORARY TABLE t$personhasprogrammeoutput';   

            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
			
            INSERT INTO internal.dataimportaudit (dataimportid, action, item, itemid, detail)
            SELECT
                par_DataImportID, 'C', 'PersonHasProgramme', i.personid, 'Create: ' || p.name
                FROM t$personhasprogrammeoutput AS i
                JOIN dbo.programme AS p
                    ON i.programmeid = p.programmeid;
					
RAISE NOTICE 'Cursor';   
            IF par_DebugMode >= 2 THEN
                OPEN p_refcur_8 FOR
                SELECT
                    tl.*, petg.programmeenvironmentid, petg.MinGradeID, petg.MaxGradeID
                    FROM t$learner AS tl
                    JOIN dbo.schoolenvironment AS se
                        ON tl.dwschoolid = se.schoolid
                    JOIN dbo.programmeenvironment AS pe
                        ON se.environmentid = pe.environmentid
                    LEFT OUTER JOIN (SELECT
                        pe.programmeenvironmentid, MIN(petg.gradeid) AS mingradeid, MAX(petg.gradeid) AS maxgradeid
                        FROM t$learner AS tl
                        JOIN dbo.schoolenvironment AS se
                            ON tl.dwschoolid = se.schoolid
                        JOIN dbo.programmeenvironment AS pe
                            ON se.environmentid = pe.environmentid
                        JOIN dbo.programmeenvironmenthastargetgrade AS petg
                            ON pe.programmeenvironmentid = petg.programmeenvironmentid AND tl.grade = petg.gradeid AND var_SourceDataDate BETWEEN petg.activefrom AND petg.sysendtime
                        WHERE tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NOT NULL
                        GROUP BY pe.programmeenvironmentid) AS petg
                        ON pe.programmeenvironmentid = petg.programmeenvironmentid
                    LEFT OUTER JOIN dbo.personhasprogramme AS pp
                        ON tl.dwpersonid = pp.personid AND pe.programmeid = pp.programmeid
                    WHERE tl.dwschoolid IS NOT NULL AND tl.dwpersonid IS NOT NULL AND pp.personid IS NULL;
                /* AND PETG.ProgrammeEnvironmentID IS NULL; */
            END IF;
			RAISE NOTICE 'Update 1 with year: %', var_SourceDataDateYear;
          
            UPDATE dbo.SourceLearner LC SET MappedPersonID = TL.DWPersonID, MappedSchoolLearnerID = TL.DWSchoolLearnerID
                        FROM t$learner TL 
                       -- JOIN dbo.SourceLearner LC ON LC.Year = var_SourceDataDateYear AND LC.EMISCode = TL.EMISCode AND LC.SourceSystem = 'DDD' AND LC.SourceLearnerID = TL.DDDLearnerID
                        WHERE /*TL.NewPerson = 1 AND*/ TL.DDDLearnerID IS NOT NULL AND LC.MappedPersonID IS NULL AND LC.Year = var_SourceDataDateYear AND LC.EMISCode = TL.EMISCode AND LC.SourceSystem = 'DDD' AND LC.SourceLearnerID = TL.DDDLearnerID;
           RAISE NOTICE 'Update 2';
            
			UPDATE dbo.SourceLearner LC SET MappedPersonID = TL.DWPersonID, MappedSchoolLearnerID = TL.DWSchoolLearnerID
                        FROM t$learner TL
                        --JOIN dbo.SourceLearner LC ON LC.Year = var_SourceDataDateYear AND LC.EMISCode = TL.EMISCode AND LC.SourceSystem = 'CEMIS' AND LC.SourceLearnerID = TL.CEMISLearnerID
                        WHERE /*TL.NewPerson = 1 AND*/ TL.CEMISLearnerID IS NOT NULL AND LC.MappedPersonID IS NULL AND LC.Year = var_SourceDataDateYear AND LC.EMISCode = TL.EMISCode AND LC.SourceSystem = 'CEMIS' AND LC.SourceLearnerID = TL.CEMISLearnerID;
        
        END IF;
		Raise Notice '  CALL internal.pupdatetargetpersonid()';
        CALL internal.pupdatetargetpersonid(); 
		Raise Notice '%', var_TranCountOnEntry;
        IF var_TranCountOnEntry = 0 THEN
		Raise Notice '%', par_ValidateOnly;
            IF par_ValidateOnly = 0 THEN
                -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %.', '50000', 0, 0, 'COMMIT TRAN' USING ERRCODE = '50000';
                COMMIT;
            ELSE
                -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %.', '50000', 0, 0, 'ROLLBACK TRAN' USING ERRCODE = '50000';
                /*
                [7807 - Severity CRITICAL - PostgreSQL does not support explicit transaction management in functions. Perform a manual conversion.]
                ROLLBACK TRAN;
                */
            END IF;
        END IF;
--         EXCEPTION
--             WHEN OTHERS THEN
--                 IF var_TranCountOnEntry = 0 THEN
--                     -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %.', '50000', 0, 0, 'ROLLBACK TRAN (CATCH)' USING ERRCODE = '50000';
--                     /*
--                     [7807 - Severity CRITICAL - PostgreSQL does not support explicit transaction management in functions. Perform a manual conversion.]
--                     ROLLBACK TRAN;
--                     */
-- 					Raise exception 'Error procedure ran with errors';
--                 END IF;
              
    END;
    return_code = 0;
--     RETURN;
   
Raise Notice 'DROP TEMP TABLES'; 
    DROP TABLE IF EXISTS t$itemid;    
    DROP TABLE IF EXISTS t$learner;  
    DROP TABLE IF EXISTS t$schoolclass;
    DROP TABLE IF EXISTS t$personhasprogrammeoutput;
   
    /* EXEC internal.pTransferLearner @DataImportID=364, @DebugMode=1 */
END;
$BODY$;
