CREATE OR REPLACE PROCEDURE internal.ptransferreadingeggsnewlearnerlist(IN par_dataimportid BIGINT, IN par_validateonly NUMERIC DEFAULT 0, 
                                                                        IN par_debugmode INTEGER DEFAULT 0, IN par_logresult NUMERIC DEFAULT 0, 
                                                                        INOUT return_code int DEFAULT 0)
AS 
$BODY$
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 DATE;
    var_SourceDataDateS VARCHAR(20);
    var_dbname VARCHAR(128) DEFAULT current_database();
    var_LogDescription VARCHAR(255);
    var_LogData VARCHAR(2048);
    var_RowCount INTEGER;
    var_LogLevel CHAR(1);
    var_Sql TEXT;
    pdataimportlogtext$par_LogText VARCHAR;
    ptemporalstatus$ReturnCode INTEGER;
    pdataimportlogtext$refcur_1 refcursor;
    pdataimportlogtext$refcur_2 refcursor;
    pdataimportlogtext$refcur_3 refcursor;
    pdataimportlogtext$refcur_4 refcursor;
    pdataimportlogtext$refcur_5 refcursor;
    pdataimportlogtext$refcur_6 refcursor;
    pdataimportlogtext$refcur_7 refcursor;
    pdataimportlogtext$refcur_8 refcursor;
BEGIN
    /*
    [7810 - Severity CRITICAL - PostgreSQL doesn't support the SET NOCOUNT. If need try another way to send message back to the client application.]
    SET NOCOUNT ON;
    */
    IF par_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;
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
    SELECT
    		@SourceDataDate		= DataContractDataDate,
    		@SourceDataDateS	= FORMAT(@SourceDataDate, 'yyyy-MM-dd')
    	FROM staging.DataImportDataContract
    	WHERE DataImportID = @SourceDataImportID
    */
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('SourceDataImportID=%I64d, @SourceDataDate=%s', @SourceDataImportID, @SourceDataDateS);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    CREATE TEMPORARY TABLE syncusernametable$ptransferreadingeggsnewlearnerlist
    (programmeid SMALLINT NOT NULL,
        code VARCHAR(10) NOT NULL);
    INSERT INTO syncusernametable$ptransferreadingeggsnewlearnerlist (programmeid, code)
    SELECT DISTINCT
        p.programmeid, p.code
        FROM dbo.systemparameter AS sp
        JOIN dbo.programme AS p
            ON sp.value1 = p.programmeid
        WHERE LOWER(sp.category) = LOWER('Processing') AND LOWER(sp.subcategory) = LOWER('Learners') AND LOWER(sp.name) = LOWER('Sync Username') AND sp.value = 4;
    /* Copy data from STAGING */
    CREATE TEMPORARY TABLE t$staging
    (rownumber BIGINT NOT NULL,
        studentid INTEGER NULL,
        studentlogin VARCHAR(50) NOT NULL,
        externalstudentid VARCHAR(50) NOT NULL,
        /* */
        personid INTEGER NULL,
        seq INTEGER NULL);
    var_RowCount := (SELECT
        COUNT(*)
        FROM clickstaging_dbo.readingeggsnewlearnerlist
        WHERE dataimportid = var_SourceDataImportID);
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('Total Rows: %d', @RowCount);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    INSERT INTO t$staging (rownumber, studentid, studentlogin, externalstudentid, personid, seq)
    SELECT
        d.rownumber, d.studentid, d.studentlogin, d.externalstudentid,
        CASE
            WHEN LOWER(d.externalstudentid) = LOWER('') THEN NULL
            WHEN d.externalstudentid IS NULL THEN NULL
            ELSE
            CASE
                WHEN aws_sqlserver_ext.ISNUMERIC(d.externalstudentid) THEN CAST (d.externalstudentid AS INTEGER)
                ELSE NULL
            END
        END, ROW_NUMBER() OVER (PARTITION BY d.externalstudentid ORDER BY d.rownumber)
        FROM clickstaging_dbo.readingeggsnewlearnerlist AS d
        WHERE d.dataimportid = var_SourceDataImportID AND NOT (LOWER(d.studentlogin) LIKE LOWER('demo!_student!_%') ESCAPE '!' OR LOWER(d.studentlogin) LIKE LOWER('demostudent%'))
        ORDER BY d.rownumber NULLS FIRST;
    GET DIAGNOSTICS var_RowCount = ROW_COUNT;
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('Rows (ignoring demo): %d', @RowCount);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    var_RowCount := (SELECT
        COUNT(*)
        FROM t$staging
        WHERE (LOWER(externalstudentid) = LOWER('') OR externalstudentid IS NULL) AND personid IS NULL);
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('Ignoring rows with no ExternalStudentID: %d', @RowCount);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    var_RowCount := (SELECT
        COUNT(*)
        FROM t$staging
        WHERE LOWER(externalstudentid) <> LOWER('') AND personid IS NULL);
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('Ignoring rows with invalid ExternalStudentID: %d', @RowCount);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    var_RowCount := (SELECT
        COUNT(*)
        FROM t$staging
        WHERE LOWER(externalstudentid) <> LOWER('') AND personid IS NOT NULL AND seq > 1);
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('Ignoring duplicate rows with valid ExternalStudentID: %d', @RowCount);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    DELETE FROM t$staging
        WHERE personid IS NULL;
    DELETE FROM t$staging
        WHERE seq > 1;

    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;
        CREATE TEMPORARY TABLE updatepersonhasprogramme$ptransferreadingeggsnewlearnerlist
        (personid INTEGER,
            programmeid SMALLINT,
            username VARCHAR(250));
        CALL internal.ptemporalstatus('dbo.PersonHasProgramme', 0, par_Silent := 1, return_code => ptemporalstatus$ReturnCode);
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        UPDATE PP SET
        			UserName		= D.StudentLogin,
        			UserNameDate	= @SourceDataDate
        		OUTPUT inserted.PersonID, inserted.ProgrammeID, inserted.UserName INTO @UpdatePersonHasProgramme(PersonID, ProgrammeID, UserName)
        		FROM #Staging D
        		JOIN PersonHasProgramme PP ON D.PersonID = PP.PersonID AND PP.ProgrammeID = 4
        		WHERE PP.UserName IS NULL
        */
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('RE PersonHasProgramme Updated: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF EXISTS (SELECT
            *
            FROM syncusernametable$ptransferreadingeggsnewlearnerlist) THEN
            /*
            [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
            UPDATE PP SET
            				UserName		= D.StudentLogin,
            				UserNameDate	= @SourceDataDate
            			OUTPUT inserted.PersonID, inserted.ProgrammeID, inserted.UserName INTO @UpdatePersonHasProgramme(PersonID, ProgrammeID, UserName)
            			FROM #Staging D
            			CROSS JOIN @SyncUserNameTable SUN
            			JOIN PersonHasProgramme PP ON D.PersonID = PP.PersonID AND SUN.ProgrammeID = PP.ProgrammeID
            			WHERE PP.UserName IS NULL
            */
            GET DIAGNOSTICS var_RowCount = ROW_COUNT;
            /*
            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
            SET @LogDescription = FORMATMESSAGE('Other PersonHasProgramme Updated : %d', @RowCount);
            */
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        END IF;
        CALL internal.ptemporalstatus('dbo.PersonHasProgramme', 1, par_Silent := 1, return_code => ptemporalstatus$ReturnCode);
        INSERT INTO internal.dataimportaudit (dataimportid, action, item, itemid, detail)
        SELECT
            par_DataImportID, 'U', 'Person', u.personid, p.code || ' PersonHasProgramme.UserName updated to: ' || u.username
            FROM updatepersonhasprogramme$ptransferreadingeggsnewlearnerlist AS u
            JOIN dbo.programme AS p
                ON u.programmeid = p.programmeid;
        /* EXEC internal.pTemporalStatus 'dbo.PersonExternalID', 0, @Silent=1 */
        CREATE TEMPORARY TABLE updatepersonexternalid$ptransferreadingeggsnewlearnerlist
        (personid INTEGER);
        /*
        [7916 - Severity CRITICAL - Current MERGE statement can not be emulated by INSERT ON CONFLICT usage. To achieve the effect of a MERGE statement, use separate INSERT, DELETE, and UPDATE statements or by cursor usage.]
        MERGE PersonExternalID AS target
        		USING
        		(
        			SELECT D.PersonID, 200 PersonExternalIDType, CAST(D.StudentID AS varchar(50)) ExternalID
        			FROM #Staging D
        			WHERE D.StudentID IS NOT NULL
        		) AS source
        		ON target.PersonID = source.PersonID AND target.PersonExternalIDType = source.PersonExternalIDType
        		WHEN MATCHED AND source.ExternalID <> target.ExternalID THEN
        			UPDATE SET
        				ExternalID = source.ExternalID
        		WHEN NOT MATCHED THEN
        			INSERT (PersonID, PersonExternalIDType, ExternalID)
        			VALUES (PersonID, PersonExternalIDType, ExternalID)
        		OUTPUT inserted.PersonID INTO @UpdatePersonExternalID(PersonID);
        */
        /*
        [7833 - Severity CRITICAL - Migration @@rowcount function in current context is not supported. Perform a manual conversion.]
        SET @RowCount = @@ROWCOUNT;
        */
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('RE PersonExternalID merged: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        /* EXEC internal.pTemporalStatus 'dbo.PersonExternalID', 1, @Silent=1 */

        IF var_TranCountOnEntry = 0 THEN
            RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 0, 0, 'COMMIT TRAN' USING ERRCODE = '50000';
            COMMIT;
        END IF;
        CALL internal.pdataimportlogtext(par_DataImportID := par_DataImportID, par_LogResult := 0, par_Select := 1, p_refcur => pdataimportlogtext$refcur_1, p_refcur_2 => pdataimportlogtext$refcur_2, p_refcur_3 => pdataimportlogtext$refcur_3, p_refcur_4 => pdataimportlogtext$refcur_4, p_refcur_5 => pdataimportlogtext$refcur_5, p_refcur_6 => pdataimportlogtext$refcur_6, p_refcur_7 => pdataimportlogtext$refcur_7, p_refcur_8 => pdataimportlogtext$refcur_8);
        CLOSE pdataimportlogtext$refcur_1;
        CLOSE pdataimportlogtext$refcur_2;
        CLOSE pdataimportlogtext$refcur_3;
        CLOSE pdataimportlogtext$refcur_4;
        CLOSE pdataimportlogtext$refcur_5;
        CLOSE pdataimportlogtext$refcur_6;
        CLOSE pdataimportlogtext$refcur_7;
        CLOSE pdataimportlogtext$refcur_8;
        EXCEPTION
            WHEN OTHERS THEN
                CALL internal.pdataimportlogtext(par_DataImportID := par_DataImportID, par_LogResult := 0, par_Select := 1, par_LogText => pdataimportlogtext$par_LogText, p_refcur => pdataimportlogtext$refcur_1, p_refcur_2 => pdataimportlogtext$refcur_2, p_refcur_3 => pdataimportlogtext$refcur_3, p_refcur_4 => pdataimportlogtext$refcur_4, p_refcur_5 => pdataimportlogtext$refcur_5, p_refcur_6 => pdataimportlogtext$refcur_6, p_refcur_7 => pdataimportlogtext$refcur_7, p_refcur_8 => pdataimportlogtext$refcur_8);
                CLOSE pdataimportlogtext$refcur_1;
                CLOSE pdataimportlogtext$refcur_2;
                CLOSE pdataimportlogtext$refcur_3;
                CLOSE pdataimportlogtext$refcur_4;
                CLOSE pdataimportlogtext$refcur_5;
                CLOSE pdataimportlogtext$refcur_6;
                CLOSE pdataimportlogtext$refcur_7;
                CLOSE pdataimportlogtext$refcur_8;

                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;
                    */
                END IF;
                RAISE;
    END;
    return_code := 0;
    DROP TABLE IF EXISTS syncusernametable$ptransferreadingeggsnewlearnerlist;
    DROP TABLE IF EXISTS updatepersonhasprogramme$ptransferreadingeggsnewlearnerlist;
    DROP TABLE IF EXISTS updatepersonexternalid$ptransferreadingeggsnewlearnerlist;
    RETURN;
    /*
    
    DROP TABLE IF EXISTS t$staging;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
END;
$BODY$
LANGUAGE plpgsql;