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

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

CREATE OR REPLACE PROCEDURE internal.ptransferenvironment(
	par_dataimportid bigint,
	par_validateonly numeric DEFAULT 0,
	par_ignoretemporal numeric DEFAULT 0,
	par_onlyrows character varying DEFAULT NULL::character varying,
	par_debugmode integer DEFAULT 0,
	INOUT return_code integer DEFAULT 0,
	INOUT p_refcur refcursor DEFAULT NULL::refcursor,
	INOUT p_refcur_2 refcursor DEFAULT NULL::refcursor,
	INOUT p_refcur_3 refcursor DEFAULT NULL::refcursor,
	INOUT p_refcur_4 refcursor DEFAULT NULL::refcursor,
	INOUT p_refcur_5 refcursor DEFAULT NULL::refcursor,
	INOUT p_refcur_6 refcursor DEFAULT NULL::refcursor,
	INOUT p_refcur_7 refcursor DEFAULT NULL::refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_LogDescription VARCHAR(255);
    var_SourceDataImportID BIGINT;
    var_DataDate TIMESTAMP WITHOUT TIME ZONE;
    var_RowCount INTEGER;
    var_dbname VARCHAR(128) DEFAULT current_database();
    ptemporalstatus$par_StatusChanged NUMERIC;
    presetsequence$ReturnCode INTEGER;
    ptemporalstatus$ReturnCode INTEGER;
BEGIN

	DROP TABLE IF EXISTS t$environment;
    /* 2022-03-27	DP	Register grades for school programme moving from deactivated to planned */
    /*
    [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;
    */
	RAISE NOTICE 'par_DataImportID: %',par_DataImportID;
    SELECT
        di.sourcedataimportid, sdi.datacontractdatadate
        INTO var_SourceDataImportID, var_DataDate
        FROM internal.dataimport AS di
        JOIN dbo.dataimportdatacontract_staging AS sdi
            ON di.sourcedataimportid = sdi.dataimportid
        WHERE di.dataimportid = par_DataImportID;

    IF par_DataImportID IS NULL THEN
--         RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 16, 0, '@DataImportID must be supplied.' USING ERRCODE = '50000';
--         return_code := 1;
        RETURN;
    END IF;

    CALL aws_commons.presetsequence(var_dbname, NULL, 'Environment', 'SchoolEnvironment', 'ComputerLab', 'ProgrammeEnvironment');

    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('@SourceDataImportID = %I64d', @SourceDataImportID);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
	
    CREATE TEMPORARY TABLE t$environment
    (dataimportid BIGINT NOT NULL,
        rownumber BIGINT NOT NULL,
        emiscode VARCHAR(9) NULL,
        /* Environment.ExternalID */
        organization VARCHAR(255) NULL,
        /* ProgrammeEnvironment.Organisation */
        extcode VARCHAR(50) NULL,
        /* ProgrammeEnvironment.ProgrammeCode */
        extusername VARCHAR(255) NULL,
        /* ProgrammeEnvironment.ProgrammeUserName */
        extpassword VARCHAR(255) NULL,
        /* ProgrammeEnvironment.ProgrammePassword */
        exttype VARCHAR(50) NULL,
        /* ProgrammeEnvironment.ProgrammeID */
        environmenttype VARCHAR(50) NULL,
        /* Environment.EnvironmentTypeID */
        implementationtype VARCHAR(50) NULL,
        /* ProgrammeEnvironment.ImplementationTypeID */
        instructionlanguage VARCHAR(100) NULL,
        /* School.SchoolHasInstructionLanguage, Environment.EnvironmentHasInstructionLanguage */
        englishsubject VARCHAR(50) NULL,
        /* Environment.EnglishLanguageLevelID */
        managementlevel VARCHAR(50) NULL,
        /* ProgrammeEnvironment.ProgrammeManagementLevelID */
        implementationpartner VARCHAR(255) NULL,
        /* ProgrammeEnvironment.ImplementationPartnerID */
        fundingpartner VARCHAR(255) NULL,
        /* ProgrammeEnvironment.ProgrammeEnvironmentHasFundingPartner */
        connectivity VARCHAR(255) NULL,
        /* Environment.ComputerLabHasEnvironment.ComputerLab.ComputerLabHasConnectivity.ConnectivitySupplierID */
        connectivitytype VARCHAR(50) NULL,
        /* Environment.ComputerLabHasEnvironment.ComputerLab.ComputerLabHasConnectivity.ConnectivityTypeID */
        connectivitystartdate DATE NULL,
        /* Environment.ComputerLabHasEnvironment.ComputerLab.ComputerLabHasConnectivity.StartDate */
        connectionspeed INTEGER NULL,
        /* Environment.ComputerLabHasEnvironment.ComputerLab.ComputerLabHasConnectivity.DownloadSpeed / UploadSpeed */
        technician VARCHAR(255) NULL,
        /* Environment.Technician */
        maincontact VARCHAR(255) NULL,
        /* Environment.MainContact */
        numoperationcomputers INTEGER NULL,
        /* Environment.ComputerLabHasEnvironment.ComputerLab.NoOfComputers */
        numfacilitators INTEGER NULL,
        /* Environment.FacilitatorCount */
        internetmonthlycost INTEGER NULL,
        /* Environment.ComputerLabHasEnvironment.ComputerLab.InternetMonthlyCost */
        internetdonatedby VARCHAR(255) NULL,
        /* Environment.EnvironmentHasDonor */
        connectivityproblems VARCHAR(255) NULL,
        /* Environment.ComputerLabHasEnvironment.ComputerLabHasConnectivity.ConnectivityComments */
        implementationstatus VARCHAR(50) NULL,
        /* ProgrammeEnvironment.ImplementationStatusID */
        launchdate DATE NULL,
        /* ProgrammeEnvironment.LaunchDate */
        activegradefrom SMALLINT NULL,
        /* ProgrammeEnvironmentHasTargetGrade */
        activegradeto SMALLINT NULL,
        /* ProgrammeEnvironmentHasTargetGrade */
        /* [GeographicArea] varchar(255) NULL,			-- Environment.GeographicAreaID */
        seatcapacity INTEGER NULL,
        /* Environment.ComputerLabHasEnvironment.ComputerLab.SeatCapacity */
        learnerpassword VARCHAR(255) NULL,
        schoolid INTEGER,
        schoolentityid INTEGER,
        name VARCHAR(250) NULL,
        organizationid INTEGER,
        schoolenvironmentid INTEGER,
        schoolenvironmentidnew NUMERIC(1, 0) DEFAULT (0),
        environmentid INTEGER,
        environmentidnew NUMERIC(1, 0) DEFAULT (0),
        computerlabid INTEGER,
        computerlabidnew NUMERIC(1, 0) DEFAULT (0),
        connectivitysupplierid INTEGER,
        connectivitytypeid SMALLINT,
        programmeenvironmentid INTEGER,
        programmeenvironmentidnew NUMERIC(1, 0) DEFAULT (0),
        programmeid SMALLINT,
        environmenttypeid SMALLINT,
        implementationtypeid SMALLINT,
        instructionlanguageid1 SMALLINT,
        instructionlanguageid2 SMALLINT,
        instructionlanguageid3 SMALLINT,
        instructionlanguageid4 SMALLINT,
        englishlanguagelevelid SMALLINT,
        programmemanagementlevelid SMALLINT,
        implementationpartnerid INTEGER,
        fundingpartnerid INTEGER,
        implementationstatusid SMALLINT,
        currentimplementationstatusid SMALLINT,
        internetdonororganisationid INTEGER
    /* Province varchar(255) */
    /* GeographicAreaID int, */
    /* GeographicAreaIDCurrent int */
    );

--     PERFORM dbo.fgetschoolhierarchy(e.schoolid);
--     PERFORM dbo.fgetschoolhierarchy(e.schoolid);
--     PERFORM dbo.fgetschoolhierarchy(e.schoolid);
    /*
    [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., 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 INTO t$environment(DataImportID, RowNumber, EMISCode, Organization, ExtCode, ExtUserName, ExtPassword, ExtType, EnvironmentType, ImplementationType, InstructionLanguage, EnglishSubject
						, ManagementLevel, ImplementationPartner, FundingPartner, Connectivity, ConnectivityType, ConnectivityStartDate, ConnectionSpeed, Technician, MainContact
						, NumOperationComputers, NumFacilitators, InternetMonthlyCost, InternetDonatedBy, ConnectivityProblems, ImplementationStatus, ActiveGradeFrom, ActiveGradeTo
						, SeatCapacity, LaunchDate)
    	SELECT
    		S.DataImportID,
    		S.RowNumber,
    		NULLIF(TRIM(S.EMISCode),''),
    		NULLIF(TRIM(S.Organization),''),
    		NULLIF(TRIM(S.ExtCode),''),
    		NULLIF(TRIM(S.ExtUserName),''),
    		NULLIF(TRIM(S.ExtPassword),''),
    		NULLIF(TRIM(S.ExtType),''),
    		NULLIF(TRIM(S.EnvironmentType),''),
    		NULLIF(TRIM(S.ImplementationType),''),
    		NULLIF(TRIM(S.InstructionLanguage),''),
    		NULLIF(TRIM(S.EnglishSubject),''),
    		NULLIF(TRIM(S.ManagementLevel),''),
    		NULLIF(TRIM(S.ImplementationPartner),''),
    		NULLIF(TRIM(S.FundingPartner),''),
    		NULLIF(TRIM(S.Connectivity),''),
    		NULLIF(TRIM(S.ConnectivityType),''),
    		COALESCE(S.ConnectivityStartDate,'0001-01-01'),
    		S.ConnectionSpeed,
    		NULLIF(TRIM(S.Technician),''),
    		NULLIF(TRIM(S.MainContact),''),
    		S.NumOperationComputers,
    		S.NumFacilitators,
    		S.InternetMonthlyCost,
    		NULLIF(TRIM(S.InternetDonatedBy),''),
    		NULLIF(TRIM(S.ConnectivityProblems),''),
    		NULLIF(TRIM(S.ImplementationStatus),''),
    		S.ActiveGradeFrom,
    		S.ActiveGradeTo,
    		--[GeographicArea],
    		S.SeatCapacity,
    		S.LaunchDate
    	FROM dbo.environment_staging S
    	WHERE S.DataImportID = var_SourceDataImportID
    		AND ((par_onlyrows IS NULL OR TRIM(par_onlyrows) = '')
    			OR (S.RowNumber IN (SELECT CAST(value AS bigint) FROM regexp_split_to_table(par_onlyrows,',') as value)));
		
		GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
		RAISE NOTICE 'ROWS: %',var_LogDescription;
		
    	RAISE NOTICE 'var_SourceDataImportID: %',var_SourceDataImportID;
		RAISE NOTICE 'par_onlyrows: %',par_onlyrows;
    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;

        IF EXISTS (SELECT
            emiscode, exttype
            FROM t$environment
            GROUP BY emiscode, exttype
            HAVING COUNT(*) > 1) THEN
            CALL internal.pdataimportloginsert(par_DataImportID, 'Duplicate EMISCode/ExtType values', 'X');
			GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
			RAISE NOTICE 'Duplicate ROWS: %',var_LogDescription;
        END IF;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function., 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 t$environment E SET
        			SchoolID = S.SchoolID,
        			SchoolEntityID = S.EntityID,
        			Name = S.Name,
        			--Province = HSA.EntityName2,
        			LearnerPassword = concat(PRE.Code,TO_CHAR(S.SchoolID,'fm0000'))
--         		FROM #Environment E
        		FROM dbo.School S  -- ON E.EMISCode = S.EMISCode
--         		OUTER APPLY (SELECT TOP 1 * FROM EntityEMISCodePrefix WHERE EMISCodePrefix = LEFT(E.EMISCode, LEN(EMISCodePrefix))) PRE
				LEFT JOIN (SELECT * FROM dbo.EntityEMISCodePrefix) PRE ON  PRE.EMISCodePrefix = LEFT(S.EMISCode, LENGTH(PRE.EMISCodePrefix))
				WHERE E.EMISCode = S.EMISCode;
       
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		RAISE NOTICE 'var_LogDescription: %',var_LogDescription;
		
        IF EXISTS (SELECT
            *
            FROM t$environment AS e
            WHERE schoolid IS NULL) THEN
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid, dataid, code, description, data)
            SELECT DISTINCT
                par_DataImportID, 2, e.rownumber, 10001, 'Unknown EMISCode', 'Unknown EMISCode: ' || e.emiscode
                FROM t$environment AS e
                WHERE e.schoolid IS NULL;
			GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
			
           	CALL internal.pdataimportloginsert(par_DataImportID, 'Unknown EMISCodes.', 'X');
			 
			 RAISE NOTICE 'Unknown EMISCodes Rows: %',var_LogDescription;
        END IF;
        /* Check Click Name */

        IF EXISTS (SELECT
            *
            FROM dbo.school AS s
            WHERE s.schoolid IN (SELECT
                schoolid
                FROM t$environment) AND s.name = s.masterlistname AND UPPER(s.name) = s.masterlistname AND LOWER(LEFT(s.emiscode, 1)) <> LOWER('1')) THEN
            OPEN p_refcur FOR
            SELECT
                'No Click Name for School' AS issue, s.schoolid, s.emiscode, s.name, s.masterlistname
                FROM dbo.school AS s
                WHERE s.schoolid IN (SELECT
                    schoolid
                    FROM t$environment) AND s.name = s.masterlistname
                ORDER BY s.name NULLS FIRST;
            /*
            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
			*/
            INSERT INTO dbo.DataImportLog(DataImportID, LogLevelID, DataID, Code, Description, Data)
            			SELECT DISTINCT par_DataImportID, 2, E.RowNumber, 10001, 'No Click Name for School', concat(S.Name,' : ',S.MasterListName) 
            			FROM t$environment E
            			JOIN dbo.School S ON E.SchoolID = S.SchoolID;
						
            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, 'No Click Name for Schools.', 'X');			
			raise notice 'No click name for school: %',var_LogDescription;
        END IF;
        /* Check assigned to Region */
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
		*/
        IF EXISTS(SELECT *
					FROM t$environment E
					JOIN LATERAL dbo.fGetSchoolHierarchy(E.SchoolID) S ON TRUE
					WHERE S.ReportEntityName2 IS NULL
        		)
        		THEN
        			SELECT 'School District not assigned to Report Region', 'Issue', S.SchoolID, S.EMISCode, S.SchoolName, S.EntityName2 'Province', S.EntityName4 'District'
        			FROM t$environment E
        			JOIN LATERAL  dbo.fGetSchoolHierarchy(E.SchoolID) S ON TRUE
        			WHERE S.ReportEntityName2 IS NULL;
        
        			INSERT INTO dbo.DataImportLog(DataImportID, LogLevelID, DataID, Code, Description, Data)
        			SELECT DISTINCT par_DataImportID, 2, E.RowNumber, 10001, 'School District not assigned to Report Region', CONCAT(S.SchoolName,' : ',S.EntityName2,'->',S.EntityName4)
        			FROM t$environment E
        			JOIN LATERAL dbo.fGetSchoolHierarchy(E.SchoolID) S ON TRUE;
					
					GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        			CALL internal.pdataimportloginsert(par_DataImportID , 'School District not assigned to Report Region.', 'X');
					raise notice 'School District not assigned to Report Region: %',var_LogDescription;
        		END IF;
        
        SELECT
            COUNT(*)
            INTO var_RowCount
            FROM t$environment AS e
            CROSS JOIN LATERAL (SELECT
                COUNT(*) AS cnt
                FROM dbo.vwschoolenvironmentprogramme AS sep
                WHERE LOWER(sep.name) = LOWER(e.name) AND LOWER(sep.emiscode) <> LOWER(e.emiscode)) AS o
            WHERE o.cnt > 0;
			
		RAISE NOTICE 'Duplicate SchoolName Rows: %',var_RowCount;

        IF var_RowCount > 0 THEN
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid, dataid, code, description, data)
            SELECT DISTINCT
                par_DataImportID, 2, e.rownumber, 10014, 'Duplicate SchoolName', 'Duplicate SchoolName: ' || e.name
                FROM t$environment AS e
                CROSS JOIN LATERAL (SELECT
                    COUNT(*) AS cnt
                    FROM dbo.vwschoolenvironmentprogramme AS sep
                    WHERE LOWER(sep.name) = LOWER(e.name) AND LOWER(sep.emiscode) <> LOWER(e.emiscode)) AS o
                WHERE o.cnt > 0;
            CALL internal.pdataimportloginsert(par_DataImportID, 'Duplicate SchoolNames.', 'X');
        END IF;
		
        UPDATE t$environment AS e
        SET programmeid = p.programmeid
        FROM dbo.programme AS p
            WHERE LOWER(e.exttype) = LOWER(p.name);
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		RAISE NOTICE 'Update form programme Rows: %',var_RowCount;
		
        IF EXISTS (SELECT
            *
            FROM t$environment AS e
            WHERE programmeid IS NULL) THEN
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid, code, description, data)
            SELECT DISTINCT
                par_DataImportID, 2, 10002, 'Unknown ExtType', 'Unknown ExtType: ' || e.fundingpartner
                FROM t$environment AS e
                WHERE e.programmeid IS NULL;
            CALL internal.pdataimportloginsert(par_DataImportID, 'Unknown ExtType', 'X');
        END IF;
		
        UPDATE t$environment AS e
        SET organizationid = o.organisationid
        FROM dbo.organisation AS o
            WHERE LOWER(e.organization) = LOWER(o.name);
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF EXISTS (SELECT
            *
            FROM t$environment
            WHERE organizationid IS NULL) THEN
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid, code, description, data)
            SELECT DISTINCT
                par_DataImportID, 2,  10003, 'Unknown Organization', 'Unknown Organization: ' || e.organization
                FROM t$environment AS e
                WHERE e.organizationid IS NULL;
            CALL internal.pdataimportloginsert(par_DataImportID, 'Unknown Organization', 'X');
        END IF;
		
        UPDATE t$environment AS e
        SET schoolenvironmentid = se.schoolenvironmentid, environmentid = se.environmentid
        FROM dbo.schoolenvironment AS se
            WHERE e.schoolid = se.schoolid;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
		
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        UPDATE t$environment AS e
        SET computerlabid = c.computerlabid
        FROM dbo.computerlabhasenvironment AS c
            WHERE e.environmentid = c.environmentid;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
		
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        /* SELECT * FROM #Environment; */
        UPDATE t$environment AS e
        SET programmeenvironmentid = p.programmeenvironmentid, currentimplementationstatusid = p.implementationstatusid
        FROM dbo.programmeenvironment AS p
            WHERE e.environmentid = p.environmentid AND /* AND P.ProgrammeCode = E.ExtCode */ p.programmeid = e.programmeid;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
		
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        UPDATE t$environment AS e
        SET environmenttypeid = t.environmenttypeid
        FROM dbo.environmenttype AS t
            WHERE LOWER(e.environmenttype) = LOWER(t.description);
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
		
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF EXISTS (SELECT
            *
            FROM t$environment
            WHERE environmenttypeid IS NULL) THEN
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid,  code, description, data)
            SELECT DISTINCT
                par_DataImportID, 2,  10004, 'Unknown EnvironmentType', 'Unknown EnvironmentType: ' || e.environmenttype
                FROM t$environment AS e
                WHERE e.environmenttypeid IS NULL;
            CALL internal.pdataimportloginsert(par_DataImportID, 'Unknown EnvironmentType', 'X');
        END IF;
		
        UPDATE t$environment AS e
        SET implementationtypeid = i.implementationtypeid
        FROM dbo.implementationtype AS i
            WHERE LOWER(e.implementationtype) = LOWER(i.description);
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF EXISTS (SELECT
            *
            FROM t$environment
            WHERE implementationtypeid IS NULL) THEN
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid,  code, description, data)
            SELECT DISTINCT
                par_DataImportID, 2, 10005, 'Unknown ImplementationType', 'Unknown ImplementationType: ' || e.implementationtype
                FROM t$environment AS e
                WHERE e.implementationtypeid IS NULL;
            CALL internal.pdataimportloginsert(par_DataImportID, 'Unknown ImplementationType', 'X');
        END IF;
		
        UPDATE t$environment AS e
        SET programmemanagementlevelid = p.programmemanagementlevelid
        FROM dbo.programmemanagementlevel AS p
            WHERE LOWER(e.managementlevel) = LOWER(p.description);
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF EXISTS (SELECT
            *
            FROM t$environment
            WHERE programmemanagementlevelid IS NULL AND managementlevel IS NOT NULL) THEN
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid,  code, description, data)
            SELECT DISTINCT
                par_DataImportID, 2,  10006, 'Unknown ManagementLevel', 'Unknown ManagementLevel: ' || e.managementlevel
                FROM t$environment AS e
                WHERE e.programmemanagementlevelid IS NULL AND managementlevel IS NOT NULL;
				
            CALL internal.pdataimportloginsert(par_DataImportID, 'Unknown ManagementLevel', 'X');
        END IF;
		
        UPDATE t$environment AS e
        SET instructionlanguageid1 = u.languageid
        FROM (SELECT
            e.rownumber, l.languageid
            FROM t$environment AS e
            JOIN dbo.language AS l
                ON LOWER(l.description) = LOWER(aws_commons.fgetstringparts(CASE
                    WHEN LOWER(e.instructionlanguage) LIKE LOWER('Dual%') THEN SUBSTR(e.instructionlanguage, 6, 200)
                    WHEN LOWER(e.instructionlanguage) LIKE LOWER('Multi%') THEN SUBSTR(e.instructionlanguage, 7, 200)
                    ELSE e.instructionlanguage
                END, '/', 1)) OR LOWER(l.descriptionendonym) = LOWER(aws_commons.fgetstringparts(CASE
                    WHEN LOWER(e.instructionlanguage) LIKE LOWER('Dual%') THEN SUBSTR(e.instructionlanguage, 6, 200)
                    WHEN LOWER(e.instructionlanguage) LIKE LOWER('Multi%') THEN SUBSTR(e.instructionlanguage, 7, 200)
                    ELSE e.instructionlanguage
                END, '/', 1))) AS u
            WHERE e.rownumber = u.rownumber;
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        UPDATE t$environment AS e
        SET instructionlanguageid2 = u.languageid
        FROM (SELECT
            e.rownumber, l.languageid
            FROM t$environment AS e
            JOIN dbo.language AS l
                ON LOWER(l.description) = LOWER(aws_commons.fgetstringparts(CASE
                    WHEN LOWER(e.instructionlanguage) LIKE LOWER('Dual%') THEN SUBSTR(e.instructionlanguage, 6, 200)
                    WHEN LOWER(e.instructionlanguage) LIKE LOWER('Multi%') THEN SUBSTR(e.instructionlanguage, 7, 200)
                    ELSE e.instructionlanguage
                END, '/', 2)) OR LOWER(l.descriptionendonym) = LOWER(aws_commons.fgetstringparts(CASE
                    WHEN LOWER(e.instructionlanguage) LIKE LOWER('Dual%') THEN SUBSTR(e.instructionlanguage, 6, 200)
                    WHEN LOWER(e.instructionlanguage) LIKE LOWER('Multi%') THEN SUBSTR(e.instructionlanguage, 7, 200)
                    ELSE e.instructionlanguage
                END, '/', 2))) AS u
            WHERE e.rownumber = u.rownumber;
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        UPDATE t$environment AS e
        SET instructionlanguageid3 = u.languageid
        FROM (SELECT
            e.rownumber, l.languageid
            FROM t$environment AS e
            JOIN dbo.language AS l
                ON LOWER(l.description) = LOWER(aws_commons.fgetstringparts(CASE
                    WHEN LOWER(e.instructionlanguage) LIKE LOWER('Dual%') THEN SUBSTR(e.instructionlanguage, 6, 200)
                    WHEN LOWER(e.instructionlanguage) LIKE LOWER('Multi%') THEN SUBSTR(e.instructionlanguage, 7, 200)
                    ELSE e.instructionlanguage
                END, '/', 3)) OR LOWER(l.descriptionendonym) = LOWER(aws_commons.fgetstringparts(CASE
                    WHEN LOWER(e.instructionlanguage) LIKE LOWER('Dual%') THEN SUBSTR(e.instructionlanguage, 6, 200)
                    WHEN LOWER(e.instructionlanguage) LIKE LOWER('Multi%') THEN SUBSTR(e.instructionlanguage, 7, 200)
                    ELSE e.instructionlanguage
                END, '/', 3))) AS u
            WHERE e.rownumber = u.rownumber;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
		
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        UPDATE t$environment AS e
        SET instructionlanguageid4 = u.languageid
        FROM (SELECT
            e.rownumber, l.languageid
            FROM t$environment AS e
            JOIN dbo.language AS l
                ON LOWER(l.description) = LOWER(aws_commons.fgetstringparts(CASE
                    WHEN LOWER(e.instructionlanguage) LIKE LOWER('Dual%') THEN SUBSTR(e.instructionlanguage, 6, 200)
                    WHEN LOWER(e.instructionlanguage) LIKE LOWER('Multi%') THEN SUBSTR(e.instructionlanguage, 7, 200)
                    ELSE e.instructionlanguage
                END, '/', 4)) OR LOWER(l.descriptionendonym) = LOWER(aws_commons.fgetstringparts(CASE
                    WHEN LOWER(e.instructionlanguage) LIKE LOWER('Dual%') THEN SUBSTR(e.instructionlanguage, 6, 200)
                    WHEN LOWER(e.instructionlanguage) LIKE LOWER('Multi%') THEN SUBSTR(e.instructionlanguage, 7, 200)
                    ELSE e.instructionlanguage
                END, '/', 4))) AS u
            WHERE e.rownumber = u.rownumber;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
		
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        UPDATE t$environment AS e
        SET englishlanguagelevelid = l.languagelevelid
        FROM dbo.languagelevel AS l
            WHERE (LOWER(e.englishsubject) = LOWER(l.description) OR LOWER(e.englishsubject) = LOWER(l.description || ' Language'));
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF EXISTS (SELECT
            *
            FROM t$environment
            WHERE englishlanguagelevelid IS NULL AND englishsubject IS NOT NULL) THEN
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid,  code, description, data)
            SELECT DISTINCT
                par_DataImportID, 2,  10007, 'Unknown EnglishSubject', 'Unknown EnglishSubject: ' || e.englishsubject
                FROM t$environment AS e
                WHERE e.englishlanguagelevelid IS NULL AND englishsubject IS NOT NULL;
            CALL internal.pdataimportloginsert(par_DataImportID, 'Unknown EnglishSubject', 'X');
        END IF;
        UPDATE t$environment AS e
        SET implementationpartnerid = p.partnerid
        FROM dbo.partner AS p
            WHERE LOWER(e.implementationpartner) = LOWER(p.name) AND p.implementation::int = 1;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF EXISTS (SELECT
            *
            FROM t$environment
            WHERE implementationpartnerid IS NULL AND implementationpartner IS NOT NULL) THEN
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid,  code, description, data)
            SELECT DISTINCT
                par_DataImportID, 2,  10008, 'Unknown ImplementationPartner', 'Unknown ImplementationPartner: ' || e.implementationpartner
                FROM t$environment AS e
                WHERE e.implementationpartnerid IS NULL AND e.implementationpartner IS NOT NULL;
            CALL internal.pdataimportloginsert(par_DataImportID, 'Unknown ImplementationPartner', 'X');
        END IF;
        UPDATE t$environment AS e
        SET fundingpartnerid = p.partnerid
        FROM dbo.partner AS p
            WHERE LOWER(e.fundingpartner) = LOWER(p.name) AND p.funding::int = 1;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF EXISTS (SELECT
            *
            FROM t$environment
            WHERE fundingpartnerid IS NULL AND fundingpartner IS NOT NULL) THEN
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid,  code, description, data)
            SELECT DISTINCT
                par_DataImportID, 2, 10009, 'Unknown FundingPartner', 'Unknown FundingPartner: ' || e.fundingpartner
                FROM t$environment AS e
                WHERE e.fundingpartnerid IS NULL AND e.fundingpartner IS NOT NULL;
            CALL internal.pdataimportloginsert(par_DataImportID, 'Unknown FundingPartner', 'X');
        END IF;
        UPDATE t$environment AS e
        SET internetdonororganisationid = o.organisationid
        FROM dbo.organisation AS o
            WHERE LOWER(e.internetdonatedby) = LOWER(o.name);
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        UPDATE t$environment AS e
        SET implementationstatusid = i.implementationstatusid
        FROM dbo.implementationstatus AS i
            WHERE LOWER(e.implementationstatus) = LOWER(i.description);
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF EXISTS (SELECT
            *
            FROM t$environment
            WHERE implementationstatusid IS NULL AND implementationstatus IS NOT NULL) THEN
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid, code, description, data)
            SELECT DISTINCT
                par_DataImportID, 2,  10010, 'Unknown ImplementationStatus', 'Unknown ImplementationStatus: ' || e.implementationstatus
                FROM t$environment AS e
                WHERE e.implementationstatusid IS NULL AND implementationstatus IS NOT NULL;
            CALL internal.pdataimportloginsert(par_DataImportID, 'Unknown ImplementationStatus', 'X');
        END IF;
		
        UPDATE t$environment AS e
        SET activegradefrom = COALESCE(e.activegradefrom, x.activegradefromid), activegradeto = COALESCE(e.activegradeto, x.activegradetoid)
        FROM (SELECT
            ppt.programmeid, MIN(pt.gradeid) AS activegradefromid, MAX(pt.gradeid) AS activegradetoid
            FROM t$environment AS e
            JOIN dbo.programmehasprogrammetarget AS ppt
                ON e.programmeid = ppt.programmeid
            JOIN dbo.programmetarget AS pt
                ON ppt.programmetargetid = pt.programmetargetid AND LOWER(pt.targettype) = LOWER('GRADE')
            WHERE e.activegradefrom IS NULL AND e.activegradeto IS NULL
            GROUP BY ppt.programmeid) AS x
            WHERE e.programmeid = x.programmeid AND e.activegradefrom IS NULL AND e.activegradeto IS NULL;
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
		/* COMMENTED OUT IN MSSQL */
		
        /* DECLARE @GeographicAreaRootEntityID int = (SELECT EntityID FROM Entity WHERE [Name] = 'Click Foundation Regions' AND EntityID=RootEntityID); */
        /* UPDATE E SET GeographicAreaID = G.EntityID FROM #Environment E JOIN Entity G ON G.RootEntityID=@GeographicAreaRootEntityID AND E.GeographicArea = G.[Name] */
        /* SET @LogDescription = FORMATMESSAGE('GeographicArea matched: %d', @@ROWCOUNT); EXEC internal.pDataImportLogInsert @DataImportID, @LogDescription; */
        /* IF EXISTS(SELECT * FROM #Environment WHERE GeographicAreaID IS NULL AND GeographicArea IS NOT NULL) */
        /* BEGIN */
        /* SELECT DISTINCT @DataImportID, 2, NULL, 10011, 'Unknown GeographicArea', 'Unknown GeographicArea: ' + E.GeographicArea */
        /* FROM #Environment E */
        /* WHERE E.GeographicAreaID IS NULL AND GeographicArea IS NOT NULL; */
        /* INSERT DataImportLog(DataImportID, LogLevelID, DataID, Code, [Description], [Data]) */
        /* SELECT DISTINCT @DataImportID, 2, NULL, 10011, 'Unknown GeographicArea', 'Unknown GeographicArea: ' + E.GeographicArea */
        /* FROM #Environment E */
        /* WHERE E.GeographicAreaID IS NULL AND GeographicArea IS NOT NULL; */
        /* EXEC internal.pDataImportLogInsert @DataImportID, 'Unknown GeographicArea', 'X' */
        /* END */
        /* UPDATE E SET GeographicAreaIDCurrent = H.EntityId2 FROM #Environment E JOIN vwEntityHierarchy H ON E.SchoolEntityID = H.SchoolEntityId AND H.EntityId1 = @GeographicAreaRootEntityID; */
        
		
		UPDATE t$environment AS e
        SET connectivitysupplierid = cs.connectivitysupplierid
        FROM dbo.connectivitysupplier AS cs
            WHERE LOWER(COALESCE(e.connectivity, 'Unknown')) = LOWER(cs.name);
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF EXISTS (SELECT
            *
            FROM t$environment
            WHERE connectivitysupplierid IS NULL AND connectivity IS NOT NULL) THEN
            OPEN p_refcur_3 FOR
            SELECT DISTINCT
                par_DataImportID, 2, NULL, 10012, 'Unknown Connectivity', 'Unknown Connectivity: ' || e.connectivity
                FROM t$environment AS e
                WHERE e.connectivitysupplierid IS NULL AND connectivity IS NOT NULL;
				
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid,  code, description, data)
            SELECT DISTINCT
                par_DataImportID, 2,  10012, 'Unknown Connectivity', 'Unknown Connectivity: ' || e.connectivity
                FROM t$environment AS e
                WHERE e.connectivitysupplierid IS NULL AND connectivity IS NOT NULL;
				
            CALL internal.pdataimportloginsert(par_DataImportID, 'Unknown Connectivity', 'X');
        END IF;
		
        UPDATE t$environment AS e
        SET connectivitytypeid = ct.connectivitytypeid
        FROM dbo.connectivitytype AS ct
            WHERE LOWER(e.connectivitytype) = LOWER(ct.description);
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF EXISTS (SELECT
            *
            FROM t$environment
            WHERE connectivitysupplierid IS NULL AND connectivity IS NOT NULL) THEN
            OPEN p_refcur_4 FOR
            SELECT DISTINCT
                par_DataImportID, 2, NULL, 10013, 'Unknown ConnectivityType', 'Unknown ConnectivityType: ' || e.connectivitytype
                FROM t$environment AS e
                WHERE e.connectivitytypeid IS NULL AND connectivitytype IS NOT NULL;
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid,  code, description, data)
            SELECT DISTINCT
                par_DataImportID, 2,  10013, 'Unknown ConnectivityType', 'Unknown ConnectivityType: ' || e.connectivitytype
                FROM t$environment AS e
                WHERE e.connectivitytypeid IS NULL AND connectivitytype IS NOT NULL;
            CALL internal.pdataimportloginsert(par_DataImportID, 'Unknown ConnectivityType', 'X');
        END IF;

        IF par_DebugMode >= 2 THEN
            OPEN p_refcur_5 FOR
            SELECT
                *
                FROM t$environment;
        END IF;
        /* Environment, SchoolEnvironment */

        IF par_IgnoreTemporal = 1 THEN
            CALL internal.ptemporalstatus('dbo.Environment', 0, return_code => ptemporalstatus$ReturnCode);
        END IF;

		/*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
		*/
        UPDATE dbo.environment T SET
			Name = E.Name,
			ExternalID = E.EMISCode,
			EnvironmentTypeID= E.EnvironmentTypeID,
			FacilitatorCount = COALESCE(E.NumFacilitators,T.FacilitatorCount),
			Technician = COALESCE(E.Technician, T.Technician),
			MainContact = COALESCE(E.MainContact, T.MainContact),
			EnglishLanguageLevelID = COALESCE(E.EnglishLanguageLevelID, T.EnglishLanguageLevelID)
		FROM t$environment E 
		WHERE  T.EnvironmentID = E.EnvironmentID AND
				(((T.ExternalID IS NULL AND E.EMISCode IS NOT NULL) 
					OR (T.ExternalID IS NOT NULL AND E.EMISCode IS NULL) 
					OR (T.ExternalID <> E.EMISCode))
			OR ((T.Name IS NULL AND E.Name IS NOT NULL) 
					OR (T.Name IS NOT NULL AND E.Name IS NULL) 
					OR (T.Name <> E.Name COLLATE pg_catalog."default"))
			OR ((T.EnvironmentTypeID IS NULL AND E.EnvironmentTypeID IS NOT NULL) 
					OR (T.EnvironmentTypeID IS NOT NULL AND E.EnvironmentTypeID IS NULL) 
					OR (T.EnvironmentTypeID <> E.EnvironmentTypeID))
			OR ((T.FacilitatorCount IS NULL AND E.NumFacilitators IS NOT NULL) 
				OR (T.FacilitatorCount <> E.NumFacilitators))
			OR ((T.Technician IS NULL AND E.Technician IS NOT NULL) 
				OR (T.Technician <> E.Technician COLLATE pg_catalog."default"))
			OR ((T.MainContact IS NULL AND E.MainContact IS NOT NULL) 
				OR (T.MainContact <> e.MainContact COLLATE pg_catalog."default"))
			OR ((T.EnglishLanguageLevelID IS NULL AND E.EnglishLanguageLevelID IS NOT NULL) 
				OR (T.EnglishLanguageLevelID <> E.EnglishLanguageLevelID)));
        
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		RAISE NOTICE 'Update dbo.enviroment rows: %',var_LogDescription;
		
        UPDATE t$environment AS e
        SET environmentid = nextval('dbo.environmentsequence'), environmentidnew = 1, schoolenvironmentid = nextval('dbo.schoolenvironmentsequence'), schoolenvironmentidnew = 1
            WHERE environmentid IS NULL;
			
        INSERT INTO dbo.environment (environmentid, environmenttypeid, externalid, name, giscoordinates, leadershiprating, facilitatorcount, addressid, englishlanguagelevelid, technician, maincontact)
        SELECT
            e.environmentid, e.environmenttypeid, e.emiscode, s.name, NULL, NULL, e.numfacilitators, NULL, e.englishlanguagelevelid, e.technician, e.maincontact
            FROM t$environment AS e
            JOIN dbo.school AS s
                ON e.schoolid = s.schoolid
            WHERE e.environmentidnew = 1;
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF par_IgnoreTemporal = 1 THEN
            CALL internal.ptemporalstatus('dbo.Environment', 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
        END IF;

        IF par_IgnoreTemporal = 1 THEN
            CALL internal.ptemporalstatus('dbo.SchoolEnvironment', 0, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
        END IF;

		INSERT INTO dbo.schoolenvironment (schoolenvironmentid, schoolid, environmentid)
        SELECT
            e.schoolenvironmentid, e.schoolid, e.environmentid
            FROM t$environment AS e
            WHERE e.schoolenvironmentidnew = 1;
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF par_IgnoreTemporal = 1 THEN
            CALL internal.ptemporalstatus('dbo.SchoolEnvironment', 1, return_code => ptemporalstatus$ReturnCode);
        END IF;
        /*
        IF @IgnoreTemporal = 1 EXEC internal.pTemporalStatus 'dbo.School', 0;
        
        -- School
        	UPDATE T SET
        		T.EnglishLanguageLevelID	= ISNULL(E.EnglishLanguageLevelID, T.EnglishLanguageLevelID)
        	FROM School T
        	JOIN #Environment E ON T.SchoolID = E.SchoolID
        	WHERE ((T.EnglishLanguageLevelID IS NULL AND E.EnglishLanguageLevelID IS NOT NULL) OR (T.EnglishLanguageLevelID <> E.EnglishLanguageLevelID))
        
        	SET @LogDescription = FORMATMESSAGE('School updated: %d', @@ROWCOUNT); EXEC internal.pDataImportLogInsert @DataImportID, @LogDescription;
        
        	IF @IgnoreTemporal = 1 EXEC internal.pTemporalStatus 'dbo.School', 1;
        */
        /* School GeographicArea */
        /* IF @IgnoreTemporal = 1 EXEC internal.pTemporalStatus 'dbo.EntityHasEntity', 0; */
        /* DELETE EE */
        /* FROM EntityHasEntity EE */
        /* JOIN #Environment E ON EE.ChildEntityID = E.SchoolEntityID AND EE.ParentEntityID = E.GeographicAreaIDCurrent */
        /* WHERE (E.GeographicAreaID IS NULL AND E.GeographicAreaIDCurrent IS NOT NULL) OR (E.GeographicAreaID IS NOT NULL AND E.GeographicAreaIDCurrent IS NULL) OR (E.GeographicAreaID <> E.GeographicAreaIDCurrent); */
        /* SET @LogDescription = FORMATMESSAGE('GeographicArea Link deleted: %d', @@ROWCOUNT); EXEC internal.pDataImportLogInsert @DataImportID, @LogDescription; */
        /* INSERT EntityHasEntity(ParentEntityID, ChildEntityID) */
        /* SELECT E.GeographicAreaID, E.SchoolEntityID */
        /* FROM #Environment E */
        /* WHERE (E.GeographicAreaID IS NOT NULL AND E.GeographicAreaIDCurrent IS NULL) OR (E.GeographicAreaID <> E.GeographicAreaIDCurrent); */
        /* SET @LogDescription = FORMATMESSAGE('GeographicArea Link inserted: %d', @@ROWCOUNT); EXEC internal.pDataImportLogInsert @DataImportID, @LogDescription; */
        /* IF @IgnoreTemporal = 1 EXEC internal.pTemporalStatus 'dbo.EntityHasEntity', 1; */
        /* Environment.EnvironmentHasInstructionLanguage */
		
        IF par_IgnoreTemporal = 1 THEN
            CALL internal.ptemporalstatus('dbo.EnvironmentHasInstructionLanguage', 0, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
        END IF;
		
        DELETE FROM dbo.environmenthasinstructionlanguage l
        USING (SELECT
            e.environmentid, e.instructionlanguageid1 AS languageid, 1 AS "order"
            FROM t$environment AS e
            WHERE e.instructionlanguageid1 IS NOT NULL
        UNION
        SELECT
            e.environmentid, e.instructionlanguageid2, 2 AS "order"
            FROM t$environment AS e
            WHERE e.instructionlanguageid2 IS NOT NULL
        UNION
        SELECT
            e.environmentid, e.instructionlanguageid3, 3 AS "order"
            FROM t$environment AS e
            WHERE e.instructionlanguageid3 IS NOT NULL
        UNION
        SELECT
            e.environmentid, e.instructionlanguageid4, 4 AS "order"
            FROM t$environment AS e
            WHERE e.instructionlanguageid4 IS NOT NULL) AS x, 
		t$environment AS e
        WHERE x.environmentid IS NULL 
			AND e.instructionlanguage IS NOT NULL 
			AND (l.environmentid = x.environmentid AND l.languageid = x.languageid AND l.environmentid = e.environmentid);
		
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
		 		WHERE L.[Order] <> X.[Order];
        */
        UPDATE dbo.EnvironmentHasInstructionLanguage L 
		SET "order" = X.order
		FROM
		(
			SELECT E.EnvironmentID, E.InstructionLanguageID1 as LanguageID, 1 as order FROM t$environment E WHERE E.InstructionLanguageID1 IS NOT NULL
			UNION SELECT E.EnvironmentID, E.InstructionLanguageID2, 2 as order FROM t$environment E WHERE E.InstructionLanguageID2 IS NOT NULL
			UNION SELECT E.EnvironmentID, E.InstructionLanguageID3, 3 as order FROM t$environment E WHERE E.InstructionLanguageID3 IS NOT NULL
			UNION SELECT E.EnvironmentID, E.InstructionLanguageID4, 4 as order FROM t$environment E WHERE E.InstructionLanguageID4 IS NOT NULL
		) X 
		WHERE L.EnvironmentID = X.EnvironmentID AND L.LanguageID = X.LanguageID and L.order <> X.order;
       
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        INSERT INTO dbo.environmenthasinstructionlanguage (environmentid, languageid, "order")
        SELECT
            x.environmentid, x.languageid, x."order"
            FROM (SELECT
                e.environmentid, e.instructionlanguageid1 AS languageid, 1 AS "order"
                FROM t$environment AS e
                WHERE e.instructionlanguageid1 IS NOT NULL
            UNION
            SELECT
                e.environmentid, e.instructionlanguageid2, 2 AS "order"
                FROM t$environment AS e
                WHERE e.instructionlanguageid2 IS NOT NULL
            UNION
            SELECT
                e.environmentid, e.instructionlanguageid3, 3 AS "order"
                FROM t$environment AS e
                WHERE e.instructionlanguageid3 IS NOT NULL
            UNION
            SELECT
                e.environmentid, e.instructionlanguageid4, 4 AS "order"
                FROM t$environment AS e
                WHERE e.instructionlanguageid4 IS NOT NULL) AS x
            LEFT OUTER JOIN dbo.environmenthasinstructionlanguage AS l
                ON l.environmentid = x.environmentid AND l.languageid = x.languageid
            WHERE l.environmentid IS NULL;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF par_IgnoreTemporal = 1 THEN
            CALL internal.ptemporalstatus('dbo.EnvironmentHasInstructionLanguage', 1, return_code => ptemporalstatus$ReturnCode);
        END IF;
        /* School.SchoolHasInstructionLanguage */
	RAISE NOTICE 'School.SchoolHasInstructionLanguage';
        IF par_IgnoreTemporal = 1 THEN
            CALL internal.ptemporalstatus('dbo.SchoolHasInstructionLanguage', 0, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
        END IF;
		
        DELETE FROM dbo.schoolhasinstructionlanguage l
        USING (SELECT DISTINCT
            e.schoolid, e.instructionlanguageid1 AS languageid, 1 AS "order"
            FROM t$environment AS e
            WHERE e.instructionlanguageid1 IS NOT NULL
        UNION
        SELECT
            e.schoolid, e.instructionlanguageid2, 2 AS "order"
            FROM t$environment AS e
            WHERE e.instructionlanguageid2 IS NOT NULL
        UNION
        SELECT
            e.schoolid, e.instructionlanguageid3, 3 AS "order"
            FROM t$environment AS e
            WHERE e.instructionlanguageid3 IS NOT NULL
        UNION
        SELECT
            e.schoolid, e.instructionlanguageid4, 4 AS "order"
            FROM t$environment AS e
            WHERE e.instructionlanguageid4 IS NOT NULL) AS x, t$environment AS e
            WHERE x.schoolid IS NULL AND e.instructionlanguage IS NOT NULL AND (l.schoolid = x.schoolid AND l.languageid = x.languageid AND l.schoolid = e.schoolid);
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
		  */
        UPDATE dbo.SchoolHasInstructionLanguage L SET "order" = X.order
		FROM
		(
			SELECT DISTINCT E.SchoolID, E.InstructionLanguageID1 LanguageID, 1 as order FROM t$environment E WHERE E.InstructionLanguageID1 IS NOT NULL
			UNION SELECT E.SchoolID, E.InstructionLanguageID2, 2 as order FROM t$environment E WHERE E.InstructionLanguageID2 IS NOT NULL
			UNION SELECT E.SchoolID, E.InstructionLanguageID3, 3 as order FROM t$environment E WHERE E.InstructionLanguageID3 IS NOT NULL
			UNION SELECT E.SchoolID, E.InstructionLanguageID4, 4 as order FROM t$environment E WHERE E.InstructionLanguageID4 IS NOT NULL
		) X WHERE L.SchoolID = X.SchoolID AND L.LanguageID = X.LanguageID
		AND L.order <> X.order;

		GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        INSERT INTO dbo.schoolhasinstructionlanguage (schoolid, languageid, "order")
        SELECT
            x.schoolid, x.languageid, x."order"
            FROM (SELECT DISTINCT
                e.schoolid, e.instructionlanguageid1 AS languageid, 1 AS "order"
                FROM t$environment AS e
                WHERE e.instructionlanguageid1 IS NOT NULL
            UNION
            SELECT
                e.schoolid, e.instructionlanguageid2, 2 AS "order"
                FROM t$environment AS e
                WHERE e.instructionlanguageid2 IS NOT NULL
            UNION
            SELECT
                e.schoolid, e.instructionlanguageid3, 3 AS "order"
                FROM t$environment AS e
                WHERE e.instructionlanguageid3 IS NOT NULL
            UNION
            SELECT
                e.schoolid, e.instructionlanguageid4, 4 AS "order"
                FROM t$environment AS e
                WHERE e.instructionlanguageid4 IS NOT NULL) AS x
            LEFT OUTER JOIN dbo.schoolhasinstructionlanguage AS l
                ON l.schoolid = x.schoolid AND l.languageid = x.languageid
            WHERE l.schoolid IS NULL;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF par_IgnoreTemporal = 1 THEN
            CALL internal.ptemporalstatus('dbo.SchoolHasInstructionLanguage', 1, return_code => ptemporalstatus$ReturnCode);
        END IF;
        /* ComputerLab, ComputerLabHasEnvironment */
        RAISE NOTICE 'ComputerLab, ComputerLabHasEnvironment';
        /*
        UPDATE C SET
        	C.SeatCapacity			= ISNULL(E.NumOperationComputers, C.SeatCapacity),
        	C.DownloadSpeed			= ISNULL(E.ConnectionSpeed, C.DownloadSpeed),
        	C.UploadSpeed			= ISNULL(E.ConnectionSpeed, C.UploadSpeed),
        	C.ConnectivityTypeID	= ISNULL(E.ConnectivityTypeID, C.ConnectivityTypeID),
        	C.ConnectivityComments	= ISNULL(E.ConnectivityProblems, C.ConnectivityComments),
        	C.InternetMonthlyCost	= ISNULL(E.InternetMonthlyCost, C.InternetMonthlyCost),
        	C.ConnectivitySupplier  = ISNULL(E.Connectivity, C.ConnectivitySupplier)
        FROM ComputerLab C
        JOIN #Environment E ON C.ComputerLabID = E.ComputerLabID
        WHERE ((C.SeatCapacity IS NULL AND E.NumOperationComputers IS NOT NULL) OR (C.SeatCapacity <> E.NumOperationComputers))
        	OR ((C.DownloadSpeed IS NULL AND E.ConnectionSpeed IS NOT NULL) OR (C.DownloadSpeed <> E.ConnectionSpeed))
        	OR ((C.UploadSpeed IS NULL AND E.ConnectionSpeed IS NOT NULL) OR (C.UploadSpeed <> E.ConnectionSpeed))
        	OR ((C.ConnectivityTypeID IS NULL AND E.ConnectionSpeed IS NOT NULL) OR (C.ConnectivityTypeID <> E.ConnectionSpeed))
        	OR ((C.ConnectivityComments IS NULL AND E.ConnectionSpeed IS NOT NULL) OR (C.ConnectivityComments <> E.ConnectivityProblems COLLATE Latin1_General_CS_AS))
        	OR ((C.InternetMonthlyCost IS NULL AND E.ConnectionSpeed IS NOT NULL) OR (C.InternetMonthlyCost <> E.ConnectionSpeed))
        	OR ((C.ConnectivitySupplier IS NULL AND E.Connectivity IS NOT NULL) OR (C.ConnectivitySupplier <> E.Connectivity COLLATE Latin1_General_CS_AS));
        */
        IF par_IgnoreTemporal = 1 THEN
            CALL internal.ptemporalstatus('dbo.ComputerLab', 0, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
        END IF;
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
		 */
        UPDATE  dbo.ComputerLab C SET
        			SeatCapacity = E.SeatCapacity,
        			InternetMonthlyCost	= E.InternetMonthlyCost,
        			NoOfComputers = E.NumOperationComputers
        		FROM t$environment E 
        		WHERE   C.ComputerLabID = E.ComputerLabID 
					AND ((C.SeatCapacity IS NULL AND E.SeatCapacity IS NOT NULL) OR (C.SeatCapacity <> E.SeatCapacity))
        			OR ((C.InternetMonthlyCost IS NULL AND E.InternetMonthlyCost IS NOT NULL) OR (C.InternetMonthlyCost <> E.InternetMonthlyCost))
        			OR ((C.NoOfComputers IS NULL AND E.NumOperationComputers IS NOT NULL) OR (C.NoOfComputers <> E.NumOperationComputers));
       
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        UPDATE t$environment AS e
        SET computerlabid = nextval('dbo.computerlabsequence'), computerlabidnew = 1
            WHERE computerlabid IS NULL;
			
        INSERT INTO dbo.computerlab (computerlabid, seatcapacity, internetmonthlycost, noofcomputers)
        SELECT
            e.computerlabid, e.seatcapacity, e.internetmonthlycost, e.numoperationcomputers
            FROM t$environment AS e
            WHERE e.computerlabidnew = 1;
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF par_IgnoreTemporal = 1 THEN
            CALL internal.ptemporalstatus('dbo.ComputerLab', 1, return_code => ptemporalstatus$ReturnCode);
        END IF;
        /* ComputerLabHasConnectivity */
		RAISE NOTICE 'ComputerLabHasConnectivity';
		
        CALL internal.ptemporalstatus('dbo.ComputerLabHasConnectivity', 0, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);

        IF par_DebugMode >= 2 THEN
            OPEN p_refcur_6 FOR
            SELECT
                'ComputerLabHasConnectivity update' AS "ComputerLabHasConnectivity update", e.emiscode, e.name, c.connectivitycomments, e.connectivityproblems, c.downloadspeed, e.connectionspeed, c.uploadspeed, e.connectionspeed, c.connectivitytypeid, e.connectivitytypeid, c.connectivitysupplierid, e.connectivitysupplierid, CAST (c.sysstarttime AS DATE) AS sysstarttime, e.connectivitystartdate, e.*
                FROM dbo.computerlabhasconnectivity AS c
                JOIN t$environment AS e
                    ON c.computerlabid = e.computerlabid AND (((c.connectivitycomments IS NULL AND e.connectivityproblems IS NOT NULL) OR (c.connectivitycomments IS NOT NULL AND e.connectivityproblems IS NULL) OR (c.connectivitycomments <> e.connectivityproblems)) OR ((c.downloadspeed IS NULL AND e.connectionspeed IS NOT NULL) OR (c.downloadspeed IS NOT NULL AND e.connectionspeed IS NULL) OR (c.downloadspeed <> e.connectionspeed)) OR ((c.uploadspeed IS NULL AND e.connectionspeed IS NOT NULL) OR (c.uploadspeed IS NOT NULL AND e.connectionspeed IS NULL) OR (c.uploadspeed <> e.connectionspeed)) OR ((c.connectivitytypeid IS NULL AND e.connectivitytypeid IS NOT NULL) OR (c.connectivitytypeid IS NOT NULL AND e.connectivitytypeid IS NULL) OR (c.connectivitytypeid <> e.connectivitytypeid)) OR ((c.connectivitysupplierid <> e.connectivitysupplierid)) OR ((CAST (c.sysstarttime AS DATE) <> COALESCE(e.connectivitystartdate, '0001-01-01'))));
        END IF;
		
        UPDATE dbo.computerlabhasconnectivity AS c
        SET connectivitysupplierid = COALESCE(e.connectivitysupplierid, c.connectivitysupplierid), 
			connectivitytypeid = COALESCE(e.connectivitytypeid, c.connectivitytypeid), 
			connectivitycomments = COALESCE(e.connectivityproblems, c.connectivitycomments), 
			downloadspeed = COALESCE(e.connectionspeed, c.downloadspeed), 
			uploadspeed = COALESCE(e.connectionspeed, c.uploadspeed), 
			sysstarttime = COALESCE(e.connectivitystartdate, '0001-01-01')
        FROM t$environment AS e
            WHERE c.computerlabid = e.computerlabid 
			AND (((c.connectivitycomments IS NULL AND e.connectivityproblems IS NOT NULL) 
				  	OR (c.connectivitycomments <> e.connectivityproblems)) 
				 OR ((c.downloadspeed IS NULL AND e.connectionspeed IS NOT NULL) 
					OR (c.downloadspeed <> e.connectionspeed)) 
				 OR ((c.uploadspeed IS NULL AND e.connectionspeed IS NOT NULL) 
					 OR (c.uploadspeed <> e.connectionspeed)) 
				 OR ((c.connectivitytypeid IS NULL AND e.connectivitytypeid IS NOT NULL) 
					 OR (c.connectivitytypeid <> e.connectivitytypeid)) 
				 OR ((c.connectivitysupplierid <> e.connectivitysupplierid)) 
				 OR ((c.sysstarttime <> COALESCE(e.connectivitystartdate, '0001-01-01'))));
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        INSERT INTO dbo.computerlabhasconnectivity (computerlabid, connectivitysupplierid, connectivitytypeid, connectivitycapacity, connectivitycomments, uploadspeed, downloadspeed, sysstarttime, sysendtime)
        SELECT
            e.computerlabid, e.connectivitysupplierid, e.connectivitytypeid, NULL, e.connectivityproblems, e.connectionspeed, e.connectionspeed, COALESCE(e.connectivitystartdate, '0001-01-01'), '9999-12-31 23:59:59'
            FROM t$environment AS e
            LEFT OUTER JOIN dbo.computerlabhasconnectivity AS c
                ON e.computerlabid = c.computerlabid
            WHERE c.computerlabid IS NULL;
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        CALL internal.ptemporalstatus('dbo.ComputerLabHasConnectivity', 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
		
        /* ComputerLabHasEnvironment */
		RAISE NOTICE ' ComputerLabHasEnvironment';
		
        INSERT INTO dbo.computerlabhasenvironment (computerlabid, environmentid)
        SELECT
            e.computerlabid, e.environmentid
            FROM t$environment AS e
            WHERE e.computerlabidnew = 1;
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        /* EnvironmentHasDonor */
		RAISE NOTICE 'EnvironmentHasDonor';
		
        IF par_IgnoreTemporal = 1 THEN
            CALL internal.ptemporalstatus('dbo.EnvironmentHasDonor', 0, return_code => ptemporalstatus$ReturnCode);
        END IF;
		
        DELETE FROM dbo.environmenthasdonor d
        USING (SELECT
            *
            FROM t$environment
            WHERE internetdonatedby IS NOT NULL) AS x, t$environment AS e
            WHERE LOWER(d.donorof) = LOWER('Internet') AND x.environmentid IS NULL AND e.internetdonatedby IS NOT NULL AND (d.environmentid = x.environmentid AND d.donororganisationid = x.internetdonororganisationid AND d.environmentid = e.environmentid);
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
		
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        INSERT INTO dbo.environmenthasdonor (environmentid, donororganisationid, donorof)
        SELECT
            e.environmentid, e.internetdonororganisationid, 'Internet'
            FROM t$environment AS e
            LEFT OUTER JOIN dbo.environmenthasdonor AS d
                ON d.environmentid = e.environmentid AND e.internetdonororganisationid = d.donororganisationid AND LOWER(d.donorof) = LOWER('Internet')
            WHERE e.internetdonororganisationid IS NOT NULL AND d.environmentid IS NULL;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF par_IgnoreTemporal = 1 THEN
            CALL internal.ptemporalstatus('dbo.EnvironmentHasDonor', 1, return_code => ptemporalstatus$ReturnCode);
        END IF;
		
        /* ProgrammeEnvironment */
		RAISE NOTICE 'ProgrammeEnvironment';

        IF par_IgnoreTemporal = 1 THEN
            CALL internal.ptemporalstatus('dbo.ProgrammeEnvironment', 0, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
        END IF;

        IF par_DebugMode >= 2 THEN
            OPEN p_refcur_7 FOR
            SELECT
                'ProgrammeEnvironment Update' AS "ProgrammeEnvironment Update", pe.organisationid, e.organizationid, pe.programmeusername, e.extusername, pe.programmepassword, e.extpassword, pe.implementationtypeid, e.implementationtypeid, pe.programmemanagementlevelid, e.programmemanagementlevelid, pe.implementationpartnerid, e.implementationpartnerid, pe.implementationstatusid, e.implementationstatusid, pe.programmecode, e.extcode, pe.launchdate, e.launchdate, e.*, pe.*
                FROM dbo.programmeenvironment AS pe
                JOIN t$environment AS e
                    ON pe.programmeenvironmentid = e.programmeenvironmentid
                WHERE ((pe.organisationid IS NULL AND e.organizationid IS NOT NULL) OR (pe.organisationid <> e.organizationid)) OR ((pe.programmeusername IS NULL AND e.extusername IS NOT NULL) OR (pe.programmeusername <> e.extusername)) OR ((pe.programmepassword IS NULL AND e.extpassword IS NOT NULL) OR (pe.programmepassword <> e.extpassword)) OR ((pe.implementationtypeid IS NULL AND e.implementationtypeid IS NOT NULL) OR (pe.implementationtypeid <> e.implementationtypeid)) OR ((pe.programmemanagementlevelid IS NULL AND e.programmemanagementlevelid IS NOT NULL) OR (pe.programmemanagementlevelid <> e.programmemanagementlevelid)) OR ((pe.implementationpartnerid IS NULL AND e.implementationpartnerid IS NOT NULL) OR (pe.implementationpartnerid IS NOT NULL AND e.implementationpartnerid IS NULL) OR (pe.implementationpartnerid <> e.implementationpartnerid)) OR ((pe.implementationstatusid IS NULL AND e.implementationstatusid IS NOT NULL) OR (pe.implementationstatusid <> e.implementationstatusid)) OR ((pe.programmecode IS NULL AND e.extcode IS NOT NULL) OR (pe.programmecode IS NOT NULL AND e.extcode IS NULL) OR (pe.programmecode <> e.extcode)) OR ((pe.launchdate IS NULL AND e.launchdate IS NOT NULL) OR (pe.launchdate IS NOT NULL AND e.launchdate IS NULL) OR (pe.launchdate <> e.launchdate));
        END IF;
        /*
        	[9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
		 */
        UPDATE dbo.ProgrammeEnvironment PE SET
        			OrganisationID			= COALESCE(E.OrganizationID, PE.OrganisationID),
        			ProgrammeCode			= E.ExtCode,
        			ProgrammeUserName		= COALESCE(E.ExtUserName, PE.ProgrammeUserName),
        			ProgrammePassword		= COALESCE(E.ExtPassword, PE.ProgrammePassword),
        			ImplementationTypeID	= COALESCE(E.ImplementationTypeID, PE.ImplementationTypeID),
        			ProgrammeManagementLevelID	= COALESCE(E.ProgrammeManagementLevelID, PE.ProgrammeManagementLevelID),
        			ImplementationPartnerID	= E.ImplementationPartnerID,
        			ImplementationStatusID	= COALESCE(E.ImplementationStatusID, PE.ImplementationStatusID),
        			LaunchDate				= E.LaunchDate
        		FROM t$environment E  
        		WHERE PE.ProgrammeEnvironmentID = E.ProgrammeEnvironmentID 
					AND( ((PE.OrganisationID IS NULL AND E.OrganizationID IS NOT NULL) OR (PE.OrganisationID <> E.OrganizationID))
        			OR ((PE.ProgrammeUserName IS NULL AND E.ExtUserName IS NOT NULL) OR (PE.ProgrammeUserName <> E.ExtUserName COLLATE pg_catalog."default"))
        			OR ((PE.ProgrammePassword IS NULL AND E.ExtPassword IS NOT NULL) OR (PE.ProgrammePassword <> E.ExtPassword COLLATE pg_catalog."default"))
        			OR ((PE.ImplementationTypeID IS NULL AND E.ImplementationTypeID IS NOT NULL) OR (PE.ImplementationTypeID <> E.ImplementationTypeID))
        			OR ((PE.ProgrammeManagementLevelID IS NULL AND E.ProgrammeManagementLevelID IS NOT NULL) OR (PE.ProgrammeManagementLevelID <> E.ProgrammeManagementLevelID))
        			OR ((PE.ImplementationPartnerID IS NULL AND E.ImplementationPartnerID IS NOT NULL) OR (PE.ImplementationPartnerID IS NOT NULL AND E.ImplementationPartnerID IS NULL) OR (PE.ImplementationPartnerID <> E.ImplementationPartnerID))
        			OR ((PE.ImplementationStatusID IS NULL AND E.ImplementationStatusID IS NOT NULL) OR (PE.ImplementationStatusID <> E.ImplementationStatusID))
        			OR ((PE.ProgrammeCode IS NULL AND E.ExtCode IS NOT NULL) OR (PE.ProgrammeCode IS NOT NULL AND E.ExtCode IS NULL)  OR (PE.ProgrammeCode <> E.ExtCode COLLATE pg_catalog."default"))
        			OR ((PE.LaunchDate IS NULL AND E.LaunchDate IS NOT NULL) OR (PE.LaunchDate IS NOT NULL AND E.LaunchDate IS NULL) OR (PE.LaunchDate <> E.LaunchDate)));
       
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        UPDATE t$environment AS e
        SET programmeenvironmentid = nextval('dbo.programmeenvironmentsequence'), programmeenvironmentidnew = 1
            WHERE e.programmeenvironmentid IS NULL;

        IF par_IgnoreTemporal = 1 THEN
            CALL internal.ptemporalstatus('dbo.ProgrammeEnvironment', 1, return_code => ptemporalstatus$ReturnCode);
        END IF;
		
        INSERT INTO dbo.programmeenvironment (programmeenvironmentid, programmeid, environmentid, implementationpartnerid, implementationstatusid, programmecode, implementationtypeid, programmemanagementlevelid, programmeusername, programmepassword, organisationid, launchdate, learnerpassword)
        SELECT
            e.programmeenvironmentid, e.programmeid, e.environmentid, e.implementationpartnerid, e.implementationstatusid, e.extcode, e.implementationtypeid, e.programmemanagementlevelid, e.extusername, e.extpassword, e.organizationid, e.launchdate, e.learnerpassword
            FROM t$environment AS e
            WHERE e.programmeenvironmentidnew = 1;
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        /* ProgrammeEnvironmentHasFundingPartner */
		RAISE NOTICE 'ProgrammeEnvironmentHasFundingPartner';
		
        IF par_IgnoreTemporal = 1 THEN
            CALL internal.ptemporalstatus('dbo.ProgrammeEnvironmentHasFundingPartner', 0, return_code => ptemporalstatus$ReturnCode);
        END IF;
		
        DELETE FROM dbo.programmeenvironmenthasfundingpartner f
        USING (SELECT
            e.programmeenvironmentid, e.fundingpartnerid
            FROM t$environment AS e
            WHERE e.fundingpartnerid IS NOT NULL) AS x,
			t$environment AS e
            WHERE x.programmeenvironmentid IS NULL AND (f.programmeenvironmentid = x.programmeenvironmentid AND f.partnerid = x.fundingpartnerid AND e.programmeenvironmentid = f.programmeenvironmentid);
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
		
        INSERT INTO dbo.programmeenvironmenthasfundingpartner (programmeenvironmentid, partnerid, fundedlearnercount)
        SELECT
            e.programmeenvironmentid, e.fundingpartnerid, NULL
            FROM t$environment AS e
            LEFT OUTER JOIN dbo.programmeenvironmenthasfundingpartner AS f
                ON e.programmeenvironmentid = f.programmeenvironmentid AND e.fundingpartnerid = f.partnerid
            WHERE f.programmeenvironmentid IS NULL AND e.fundingpartnerid IS NOT NULL;
			
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF par_IgnoreTemporal = 1 THEN
            CALL internal.ptemporalstatus('dbo.ProgrammeEnvironmentHasFundingPartner', 1, return_code => ptemporalstatus$ReturnCode);
        END IF;

		/* ProgrammeEnvironmentHasTargetGrade (only for first time import or going from deactivated to planned/active) */
		RAISE NOTICE 'ProgrammeEnvironmentHasTargetGrade';

        IF EXISTS (SELECT
            *
            FROM t$environment AS e
            WHERE (e.programmeenvironmentidnew = 1) OR (e.currentimplementationstatusid IN (2) AND e.implementationstatusid IN (4, 13))) THEN
            INSERT INTO dbo.programmeenvironmenthastargetgrade (programmeenvironmentid, gradeid, activefrom, learnermanagementlevelid, usetarget, timetarget)
            SELECT
                e.programmeenvironmentid, g.gradeid, CAST (clock_timestamp() AS DATE), e.programmemanagementlevelid, 0.8, 40
                FROM t$environment AS e
                JOIN dbo.grade AS g
                    ON g.gradeid BETWEEN e.activegradefrom AND e.activegradeto
                WHERE ((e.programmeenvironmentidnew = 1) OR (e.currentimplementationstatusid IN (2) AND e.implementationstatusid IN (4, 13))) AND e.activegradefrom <> - 10 AND e.activegradeto <> - 10;
            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        END IF;
        /* EXEC internal.pTemporalStatus 'dbo.ProgrammeEnvironmentHasTargetGrade', 0; */
        /* DELETE PETG */
        /* FROM ProgrammeEnvironmentHasTargetGrade PETG */
        /* JOIN #Environment E ON PETG.ProgrammeEnvironmentID = E.ProgrammeEnvironmentID */
        /* WHERE E.ActiveGradeFrom = -10 AND E.ActiveGradeTo = -10 */
        /* SET @LogDescription = FORMATMESSAGE('ProgrammeEnvironmentHasTargetGrade cleared: %d', @@ROWCOUNT); EXEC internal.pDataImportLogInsert @DataImportID, @LogDescription; */
        /* DELETE PETG */
        /* FROM ProgrammeEnvironmentHasTargetGrade PETG */
        /* JOIN #Environment E ON PETG.ProgrammeEnvironmentID = E.ProgrammeEnvironmentID */
        /* WHERE E.ActiveGradeFrom <> -10 AND E.ActiveGradeTo <> -10 AND (PETG.GradeID < E.ActiveGradeFrom OR PETG.GradeID > E.ActiveGradeTo) */
        /* SET @LogDescription = FORMATMESSAGE('ProgrammeEnvironmentHasTargetGrade deleted: %d', @@ROWCOUNT); EXEC internal.pDataImportLogInsert @DataImportID, @LogDescription; */
        /* IF OBJECT_ID('tempdb..#TargetGrade') IS NOT NULL DROP TABLE #TargetGrade; */
        /* INSERT ProgrammeEnvironmentHasTargetGrade(ProgrammeEnvironmentID, GradeID, ActiveFrom) */
        /* SELECT E.ProgrammeEnvironmentID, G.GradeID, '0001-01-01' */
        /* FROM #Environment E */
        /* JOIN Grade G ON G.GradeID BETWEEN E.ActiveGradeFrom AND E.ActiveGradeTo */
        /* LEFT JOIN ProgrammeEnvironmentHasTargetGrade P ON E.ProgrammeEnvironmentID = P.ProgrammeEnvironmentID AND G.GradeID = P.GradeID */
        /* WHERE E.ActiveGradeFrom <> -10 AND E.ActiveGradeTo <> -10 AND P.ProgrammeEnvironmentID IS NULL */
        /* SET @LogDescription = FORMATMESSAGE('ProgrammeEnvironmentHasTargetGrade inserted (active): %d', @@ROWCOUNT); EXEC internal.pDataImportLogInsert @DataImportID, @LogDescription; */
        /* EXEC internal.pTemporalStatus 'dbo.ProgrammeEnvironmentHasTargetGrade', 1; */

--         IF var_TranCountOnEntry = 0 THEN
--             RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 0, 0, 'COMMIT TRAN' USING ERRCODE = '50000';
--             COMMIT;
--         END IF;
--         EXCEPTION
--             WHEN OTHERS THEN
--                 IF var_TranCountOnEntry = 0 THEN
--                     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;
--                 RAISE;
    END;
    return_code := 0;
    RETURN;
    
    DROP TABLE IF EXISTS t$environment;
    /*
    
    Temporary table must be removed before end of the function.
    */
END;
$BODY$;
