CREATE OR REPLACE PROCEDURE internal.ptransferlearnerdddid(IN par_dataimportid BIGINT, IN par_validateonly NUMERIC DEFAULT 0, 
    IN par_debugmode INTEGER DEFAULT 0, INOUT return_code int DEFAULT 0, INOUT p_refcur refcursor DEFAULT NULL, 
    INOUT p_refcur_2 refcursor DEFAULT NULL, INOUT p_refcur_3 refcursor DEFAULT NULL, INOUT p_refcur_4 refcursor DEFAULT NULL)
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_SourceDataImportIDChanged BIGINT;
    var_SourceDataImportIDMerged BIGINT;
    var_SourceDataDate TIMESTAMP WITHOUT TIME ZONE;
    var_SourceDataDateS VARCHAR(20);
    var_SourceDataTypeID INTEGER;
    var_SourceDataTypeIDChanged INTEGER DEFAULT 29;
    var_SourceDataTypeIDMerged INTEGER DEFAULT 30;
    var_dbname VARCHAR(128) DEFAULT current_database();
    var_LogDescription VARCHAR(255);
    var_LogData VARCHAR(2048);
    var_Sql TEXT;
    var_RowCount INTEGER;
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 HH:mm:ss'),
    		@SourceDataTypeID   = DataSourceTypeID
    	FROM staging.DataImportDataContract
    	WHERE DataImportID = @SourceDataImportID
    */
    SELECT
        di.dataimportid
        INTO var_SourceDataImportIDChanged
        FROM staging.dataimportdatacontract AS di
        WHERE di.datasourcetypeid = var_SourceDataTypeIDChanged AND di.datacontractdatadate = var_SourceDataDate AND LOWER(di.dataimportstatus) = LOWER('Success')
        ORDER BY di.dataimportstarttime DESC NULLS FIRST
        LIMIT 1;
    SELECT
        di.dataimportid
        INTO var_SourceDataImportIDMerged
        FROM staging.dataimportdatacontract AS di
        WHERE di.datasourcetypeid = var_SourceDataTypeIDMerged AND di.datacontractdatadate = var_SourceDataDate AND LOWER(di.dataimportstatus) = LOWER('Success')
        ORDER BY di.dataimportstarttime DESC NULLS FIRST
        LIMIT 1;
    /*
    [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, @SourceDataImportIDChanged=%I64d, @SourceDataImportIDMerged=%I64d', @SourceDataImportID, @SourceDataDateS, @SourceDataImportIDChanged, @SourceDataImportIDMerged);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    CREATE TEMPORARY TABLE t$itemid
    (itemid BIGINT NOT NULL,
        detail VARCHAR(100) NULL);
    /* Copy data from STAGING */
    CREATE TEMPORARY TABLE t$changeddata
    (rownumber BIGINT NOT NULL,
        submissiondate DATE NOT NULL,
        olddddlearnerid INTEGER NOT NULL,
        newdddlearnerid INTEGER NOT NULL,
        /* */
        olddddlearneridexists INTEGER NULL DEFAULT (0),
        newdddlearneridexists INTEGER NULL DEFAULT (0),
        inmergeddata INTEGER DEFAULT (0),
        status VARCHAR(100) NULL);
    CREATE TEMPORARY TABLE t$mergeddata
    (rownumber BIGINT NOT NULL,
        submissiondate DATE NOT NULL,
        olddddlearnerid INTEGER NOT NULL,
        shareddddlearnerid INTEGER NOT NULL,
        /* */
        olddddlearneridexists INTEGER NULL DEFAULT (0),
        shareddddlearneridexists INTEGER NULL DEFAULT (0),
        status VARCHAR(100) NULL);
    INSERT INTO t$changeddata (rownumber, submissiondate, olddddlearnerid, newdddlearnerid)
    SELECT
        rownumber, make_date(SUBSTR(submissiondate, 5, 4), SUBSTR(submissiondate, 3, 2), SUBSTR(submissiondate, 1, 2)), l.olddddlearnerid, l.newdddlearnerid
        FROM clickstaging_dbo.learnerdddchangedid AS l
        WHERE l.dataimportid = var_SourceDataImportIDChanged
        ORDER BY rownumber NULLS FIRST;
    GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    CREATE INDEX ix_t$changeddata_ix1 ON t$changeddata
        (olddddlearnerid, newdddlearnerid);
    INSERT INTO t$mergeddata (rownumber, submissiondate, olddddlearnerid, shareddddlearnerid)
    SELECT
        rownumber, make_date(SUBSTR(submissiondate, 5, 4), SUBSTR(submissiondate, 3, 2), SUBSTR(submissiondate, 1, 2)), l.olddddlearnerid, l.shareddddlearnerid
        FROM clickstaging_dbo.learnerdddmergedid AS l
        WHERE l.dataimportid = var_SourceDataImportIDMerged
        ORDER BY l.rownumber NULLS FIRST;
    GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

    IF par_DebugMode >= 2 THEN
        OPEN p_refcur FOR
        SELECT
            '#ChangedData' AS "#ChangedData", *
            FROM t$changeddata;
        OPEN p_refcur_2 FOR
        SELECT
            '#MergedData' AS "#MergedData", *
            FROM t$mergeddata;
    END IF;

    BEGIN
        IF var_TranCountOnEntry = 0 THEN
            RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 0, 0, 'BEGIN TRAN' USING ERRCODE = '50000';
            /*
            [7807 - Severity CRITICAL - PostgreSQL does not support explicit transaction management in functions. Perform a manual conversion.]
            BEGIN TRAN
            */
        END IF;
        DELETE FROM t$changeddata AS C
        USING dbo.sourcelearnerdddchangedid AS s
            WHERE c.olddddlearnerid = s.olddddlearnerid AND c.newdddlearnerid = s.newdddlearnerid;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        UPDATE t$changeddata AS c
        SET inmergeddata = 1
        FROM t$mergeddata AS m
            WHERE c.olddddlearnerid = m.olddddlearnerid;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF par_DebugMode >= 2 THEN
            OPEN p_refcur_3 FOR
            SELECT
                'Ignored - Merge' AS "Ignored - Merge", *
                FROM t$changeddata AS c
                JOIN t$mergeddata AS m
                    ON c.olddddlearnerid = m.olddddlearnerid
                ORDER BY c.rownumber NULLS FIRST, m.rownumber NULLS FIRST;
        END IF;
        UPDATE t$changeddata AS c
        SET olddddlearneridexists = 1
        FROM t$changeddata
        CROSS JOIN LATERAL (SELECT
            *
            FROM dbo.sourcelearner AS sl
            WHERE sl.sourcelearnerid = CAST (t$changeddata.olddddlearnerid AS VARCHAR(30))
            ORDER BY sl.year DESC NULLS FIRST
            LIMIT 1) AS sl;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        UPDATE t$changeddata AS c
        SET newdddlearneridexists = 1
        FROM t$changeddata
        CROSS JOIN LATERAL (SELECT
            *
            FROM dbo.sourcelearner AS sl
            WHERE sl.sourcelearnerid = CAST (t$changeddata.newdddlearnerid AS VARCHAR(30))
            ORDER BY sl.year DESC NULLS FIRST
            LIMIT 1) AS sl;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        UPDATE t$mergeddata AS m
        SET olddddlearneridexists = 1
        FROM t$mergeddata
        CROSS JOIN LATERAL (SELECT
            *
            FROM dbo.sourcelearner AS sl
            WHERE sl.sourcelearnerid = CAST (t$mergeddata.olddddlearnerid AS VARCHAR(30))
            ORDER BY sl.year DESC NULLS FIRST
            LIMIT 1) AS sl;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        UPDATE t$mergeddata AS m
        SET shareddddlearneridexists = 1
        FROM t$mergeddata
        CROSS JOIN LATERAL (SELECT
            *
            FROM dbo.sourcelearner AS sl
            WHERE sl.sourcelearnerid = CAST (t$mergeddata.shareddddlearnerid AS VARCHAR(30))
            ORDER BY sl.year DESC NULLS FIRST
            LIMIT 1) AS sl;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        UPDATE t$changeddata AS c
        SET status =
        CASE
            WHEN c.inmergeddata = 1 THEN 'Ignored - Merge'
            WHEN c.olddddlearneridexists = 1 AND c.newdddlearneridexists = 1 THEN 'NewID already exists'
            WHEN c.olddddlearneridexists = 0 THEN 'OldID not found'
            WHEN c.olddddlearneridexists = 1 AND c.newdddlearneridexists = 0 THEN 'Updated'
        END;

        IF EXISTS (SELECT
            *
            FROM t$changeddata
            WHERE status IS NULL) THEN
            RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 16, 0, '#ChangedData.Status has some nulls' USING ERRCODE = '50000';
        END IF;
        INSERT INTO dbo.sourcelearnerdddchangedid (olddddlearnerid, newdddlearnerid, dataimportid, sourcerownumber, submissiondate, status)
        SELECT
            c.olddddlearnerid, c.newdddlearnerid, par_DataImportID, c.rownumber, c.submissiondate, COALESCE(c.status, '')
            FROM t$changeddata AS c;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        /* SELECT * FROM #ChangedData C */

        IF EXISTS (SELECT
            *
            FROM t$changeddata AS c
            WHERE c.olddddlearneridexists = 1 AND c.newdddlearneridexists = 0) THEN
            CREATE TEMPORARY TABLE t$sourcelearnerupdate
            (rownumber BIGINT,
                olddddlearnerid INTEGER,
                newdddlearnerid INTEGER);
            WITH upd_stat (rownumber, olddddlearnerid, newdddlearnerid)
            AS (UPDATE dbo.sourcelearner AS sl
            SET sourcelearnerid = c.newdddlearnerid, dddlearnerid = c.newdddlearnerid
            FROM t$changeddata AS c
                WHERE c.olddddlearneridexists = 1 AND c.newdddlearneridexists = 0 AND sl_dml.sourcelearnerid = CAST (c.olddddlearnerid AS VARCHAR(30))
                RETURNING rownumber, olddddlearnerid, newdddlearnerid)
            INSERT INTO t$sourcelearnerupdate (rownumber, olddddlearnerid, newdddlearnerid)
            SELECT
                *
                FROM upd_stat;
            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
            /*
            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
            INSERT internal.DataImportAudit
            			(DataImportID, [Action], Item, ItemID, Detail)
            			SELECT @DataImportID, 'U', 'SourceLearner', RowNumber, FORMATMESSAGE('%d -> %d', OldDDDLearnerID, NewDDDLearnerID)
            			FROM #SourceLearnerUpdate
            */
            CREATE TEMPORARY TABLE t$personexternalidupdate
            (personid INTEGER,
                olddddlearnerid INTEGER,
                newdddlearnerid INTEGER);
            /*
            [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
            UPDATE ID SET ExternalID = C.NewDDDLearnerID
            			OUTPUT inserted.PersonID, C.OldDDDLearnerID, C.NewDDDLearnerID INTO #PersonExternalIDUpdate(PersonID, OldDDDLearnerID, NewDDDLearnerID)
            			FROM PersonExternalID ID
            			JOIN #ChangedData C ON ID.PersonExternalIDType = 9 AND ID.ExternalID = CAST(C.OldDDDLearnerID AS varchar(50))
            			WHERE C.OldDDDLearnerIDExists = 1 AND C.NewDDDLearnerIDExists = 0
            */
            GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
            CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
            /*
            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
            INSERT internal.DataImportAudit
            			(DataImportID, [Action], Item, ItemID, Detail)
            			SELECT @DataImportID, 'U', 'PersonExternalID[9]', PersonID, FORMATMESSAGE('%d -> %d', OldDDDLearnerID, NewDDDLearnerID)
            			FROM #PersonExternalIDUpdate
            */
        END IF;
        INSERT INTO internal.dataimportlog (dataimportid, loglevelid, dataid, code, description, data)
        SELECT
            par_DataImportID, 0, rownumber, 00001, 'Changed: ' || c.status, NULL
            FROM t$changeddata AS c;
        INSERT INTO internal.dataimportlog (dataimportid, loglevelid, dataid, code, description, data)
        SELECT
            par_DataImportID, 1, MIN(m.rownumber), 10001, 'Merged: Invalid Merge Data', NULL
            FROM t$mergeddata AS m
            GROUP BY m.olddddlearnerid, m.shareddddlearnerid
            HAVING COUNT(*) > 1;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription, 'W');
        DELETE FROM t$mergeddata AS M
        USING (SELECT
            m.olddddlearnerid, m.shareddddlearnerid
            FROM t$mergeddata AS m
            GROUP BY m.olddddlearnerid, m.shareddddlearnerid
            HAVING COUNT(*) > 1) AS x
            WHERE m.olddddlearnerid = x.olddddlearnerid AND m.shareddddlearnerid = x.shareddddlearnerid;
        UPDATE t$mergeddata AS m
        SET status = 'Merge Learners - both in DW'
        FROM (SELECT
            m.shareddddlearnerid
            FROM t$mergeddata AS m
            WHERE m.status IS NULL AND m.shareddddlearneridexists = 1
            GROUP BY m.shareddddlearnerid
            HAVING SUM(m.olddddlearneridexists) = 2) AS x
            WHERE m.shareddddlearnerid = x.shareddddlearnerid;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        UPDATE t$mergeddata AS m
        SET status = 'Merge - SharedID not found'
        FROM (SELECT
            m.shareddddlearnerid
            FROM t$mergeddata AS m
            WHERE m.status IS NULL AND m.shareddddlearneridexists = 0
            GROUP BY m.shareddddlearnerid
            HAVING SUM(m.olddddlearneridexists) = 1 AND COUNT(*) = 2) AS x
            WHERE m.shareddddlearnerid = x.shareddddlearnerid;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        UPDATE t$mergeddata AS m
        SET status = 'Merge - OldID not found'
        FROM (SELECT
            m.shareddddlearnerid
            FROM t$mergeddata AS m
            WHERE m.status IS NULL AND m.shareddddlearneridexists = 1
            GROUP BY m.shareddddlearnerid
            HAVING SUM(m.olddddlearneridexists) = 1 AND COUNT(*) = 2) AS x
            WHERE m.shareddddlearnerid = x.shareddddlearnerid;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        UPDATE t$mergeddata AS m
        SET status = 'Merge - OldID & SharedID not found'
        FROM (SELECT
            m.shareddddlearnerid
            FROM t$mergeddata AS m
            WHERE m.status IS NULL AND m.shareddddlearneridexists = 0
            GROUP BY m.shareddddlearnerid
            HAVING SUM(m.olddddlearneridexists) = 0 AND COUNT(*) = 2) AS x
            WHERE m.shareddddlearnerid = x.shareddddlearnerid;
        GET DIAGNOSTICS var_LogDescription = ROW_COUNT;
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF par_DebugMode >= 2 THEN
            OPEN p_refcur_4 FOR
            SELECT
                *
                FROM t$mergeddata
                WHERE status IS NULL;
        END IF;

        IF EXISTS (SELECT
            *
            FROM t$mergeddata
            WHERE status IS NULL) THEN
            RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 16, 0, '#MergedData.Status has some nulls' USING ERRCODE = '50000';
        END IF;
        INSERT INTO internal.dataimportlog (dataimportid, loglevelid, dataid, code, description, data)
        SELECT
            par_DataImportID, 0, rownumber, 2, 'Merged: ' || c.status, NULL
            FROM t$mergeddata AS c;

        IF var_TranCountOnEntry = 0 THEN
            IF par_ValidateOnly = 0 THEN
                RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 0, 0, 'COMMIT TRAN' USING ERRCODE = '50000';
                COMMIT;
            ELSE
                RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 0, 0, 'ROLLBACK TRAN' USING ERRCODE = '50000';
                /*
                [7807 - Severity CRITICAL - PostgreSQL does not support explicit transaction management in functions. Perform a manual conversion.]
                ROLLBACK TRAN;
                */
            END IF;
        END IF;
        EXCEPTION
            WHEN OTHERS THEN
                IF var_TranCountOnEntry = 0 THEN
                    RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 0, 0, 'ROLLBACK TRAN (CATCH)' USING ERRCODE = '50000';
                    /*
                    [7807 - Severity CRITICAL - PostgreSQL does not support explicit transaction management in functions. Perform a manual conversion.]
                    ROLLBACK TRAN;
                    */
                END IF;
                RAISE;
    END;
    return_code := 0;
    RETURN;
    /*
    
    DROP TABLE IF EXISTS t$itemid;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$changeddata;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$mergeddata;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$sourcelearnerupdate;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$personexternalidupdate;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
END;
$BODY$
LANGUAGE plpgsql;