CREATE OR REPLACE PROCEDURE internal.pgenerateusernames(IN par_schools VARCHAR DEFAULT 'ALL', 
	IN par_programmes VARCHAR DEFAULT 'ALL')
AS 
$BODY$
DECLARE
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_RowCount INTEGER DEFAULT 0;
    pgenerateusernamesmatific$refcur_1 refcursor;
    pgenerateusernamesreflectivelearning$refcur_1 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;
    */
    CREATE TEMPORARY TABLE t$schoolslist
    (emiscode VARCHAR(9),
        schoolid INTEGER);

    IF (LOWER(par_Schools) = LOWER('ALL')) THEN
        INSERT INTO t$schoolslist
        SELECT
            emiscode, schoolid
            FROM dbo.school;
    END IF;

    IF (LOWER(par_Schools) IN (LOWER('WC'), LOWER('CEMIS'))) THEN
        INSERT INTO t$schoolslist
        SELECT
            emiscode, schoolid
            FROM dbo.school
            WHERE LOWER(LEFT(emiscode, 1)) = LOWER('1');
    ELSE
        IF (LOWER(par_Schools) IN (LOWER('NotWC'), LOWER('DDD'))) THEN
            INSERT INTO t$schoolslist
            SELECT
                emiscode, schoolid
                FROM dbo.school
                WHERE LOWER(LEFT(emiscode, 1)) <> LOWER('1');
        ELSE
            IF (aws_sqlserver_ext.isnumeric(LEFT(par_Schools, 1)) = 1) THEN
                INSERT INTO t$schoolslist
                SELECT
                    emiscode, schoolid
                    FROM dbo.school
                    WHERE emiscode IN (SELECT
                        value
                        FROM string_split(par_Schools, ','));
            END IF;
        END IF;
    END IF;
    CREATE INDEX ix_t$schoolslist_ix1 ON t$schoolslist
        (emiscode);
    CREATE INDEX ix_t$schoolslist_ix2 ON t$schoolslist
        (schoolid);
    CREATE TEMPORARY TABLE t$programmeslist
    (programmeid SMALLINT,
        code VARCHAR(10),
        name VARCHAR(200));

    IF LOWER(par_Programmes) = LOWER('ALL') THEN
        INSERT INTO t$programmeslist
        SELECT
            programmeid, code, name
            FROM dbo.programme
            WHERE programmeid IN (4, 8, 9);
    ELSE
        INSERT INTO t$programmeslist
        SELECT
            p.programmeid, code, name
            FROM dbo.programme AS p
            JOIN (SELECT
                value
                FROM string_split(par_Programmes, ',')
                    AS f) AS f
                ON LOWER(f.value) = LOWER(p.code) OR LOWER(f.value) = LOWER(p.name) OR
                CASE
                    WHEN aws_sqlserver_ext.ISNUMERIC(value) THEN CAST (value AS INTEGER)
                    ELSE NULL
                END = p.programmeid;
    END IF;

    BEGIN
        IF var_TranCountOnEntry = 0 THEN
            /*
            [7807 - Severity CRITICAL - PostgreSQL does not support explicit transaction management in functions. Perform a manual conversion.]
            BEGIN TRAN
            */
            BEGIN
            END;
        END IF;

        IF EXISTS (SELECT
            *
            FROM t$programmeslist
            WHERE programmeid = 8) THEN
            -- CALL internal.pgenerateusernamesmatific(p_refcur => pgenerateusernamesmatific$refcur_1);
            CALL internal.pgenerateusernamesmatific(par_schools);
            --CLOSE pgenerateusernamesmatific$refcur_1;
        END IF;

        IF EXISTS (SELECT
            *
            FROM t$programmeslist
            WHERE programmeid = 9) THEN
            -- CALL internal.pgenerateusernamesreflectivelearning(p_refcur => pgenerateusernamesreflectivelearning$refcur_1);
            CALL internal.pgenerateusernamesreflectivelearning();
            -- CLOSE pgenerateusernamesreflectivelearning$refcur_1;
        END IF;

        IF var_TranCountOnEntry = 0 THEN
            COMMIT;
        END IF;
        EXCEPTION
            WHEN OTHERS THEN
                IF var_TranCountOnEntry = 0 THEN
                    ROLLBACK;
                END IF;
                RAISE;
    END;
    /*
    
    DROP TABLE IF EXISTS t$schoolslist;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$programmeslist;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
END;
$BODY$
LANGUAGE plpgsql;