CREATE OR REPLACE function internal.pupdatedddids(IN par_sourcelearnerdataimportid bigint)
RETURNS TABLE (return_code integer)  
AS 
$BODY$
DECLARE
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_ChangeDataImportID BIGINT;
    var_MergeDataImportID BIGINT;
    var_DataDate TIMESTAMP WITHOUT TIME ZONE;
    var_DataImportID BIGINT;
    var_LogDescription VARCHAR(1000);
    var_RowCount INTEGER;
    pnewdataimportid$ReturnCode INTEGER;
    pupdatedataimport$ReturnCode INTEGER;
    pnewdataimportid$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
    */
    SELECT
        di.datacontractdatadate
        INTO var_DataDate
        FROM dbo.dataimportdatacontract_staging AS di
        WHERE di.dataimportid = par_SourceLearnerDataImportID;
    SELECT
        di.dataimportid
        INTO var_ChangeDataImportID
        FROM dbo.dataimportdatacontract_staging AS di
        WHERE di.datacontractdatadate = var_DataDate AND di.datasourcetypeid = 29 AND LOWER(di.dataimportstatus) = LOWER('Success');
    SELECT
        di.dataimportid
        INTO var_MergeDataImportID
        FROM dbo.dataimportdatacontract_staging AS di
        WHERE di.datacontractdatadate = var_DataDate AND di.datasourcetypeid = 30 AND LOWER(di.dataimportstatus) = LOWER('Success');

    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;
        -- CALL internal.pnewdataimportid(par_SourceDataImportID := var_ChangeDataImportID, par_Description := 'Update DDD IDs', par_NoResults := 1, return_code => pnewdataimportid$ReturnCode, p_refcur => pnewdataimportid$refcur_1);
        -- select * from internal.pnewdataimportid(var_ChangeDataImportID, 'Update DDD IDs', null, 1);
        perform internal.pnewdataimportid(var_ChangeDataImportID, 'Update DDD IDs');
        -- CLOSE pnewdataimportid$refcur_1;
        var_DataImportID := (SELECT
            MAX(dataimportid)
            FROM internal.dataimport);
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('@SourceLearnerDataImportID=%I64d, @SourceDataImportIDChanged=%I64d, @SourceDataImportIDMerged=%I64d', @SourceLearnerDataImportID, @ChangeDataImportID, @MergeDataImportID);
        */
        CALL internal.pdataimportloginsert(var_DataImportID, var_LogDescription);
        CREATE TEMPORARY TABLE t$changes
        AS
        SELECT
            *
            FROM (SELECT
                c.*, ROW_NUMBER() OVER (PARTITION BY c.olddddlearnerid ORDER BY c.dataimportid, c.rownumber) AS seq
                FROM dbo.learnerdddchangedid_staging AS c
                WHERE c.dataimportid = var_ChangeDataImportID) AS c
            WHERE c.Seq = 1;
        /*
        [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('Change count: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(var_DataImportID, var_LogDescription);
       CREATE TEMPORARY TABLE t$updates
                AS
                SELECT
                    c.olddddlearnerid, c.newdddlearnerid, c.dataimportid, c.rownumber, 
                        make_date(CAST(SUBSTR(c.submissiondate, 5, 4) AS INTEGER), CAST(SUBSTR(c.submissiondate, 3, 2) AS INTEGER), CAST(SUBSTR(c.submissiondate, 1, 2) AS INTEGER)) AS submissiondate
                    FROM t$changes AS c
                    LEFT OUTER JOIN dbo.sourcelearnerdddchangedid AS x
                        ON c.olddddlearnerid = x.olddddlearnerid
                    CROSS JOIN LATERAL (SELECT
                        COUNT(*) AS mergecount
                        FROM dbo.learnerdddmergedid_staging AS m
                        WHERE m.olddddlearnerid = c.olddddlearnerid) AS m
                    WHERE x.olddddlearnerid IS NULL AND m.MergeCount = 0
                    ORDER BY c.dataimportid NULLS FIRST, c.rownumber NULLS FIRST;
        /*
        [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('New update count: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(var_DataImportID, var_LogDescription);
        INSERT INTO dbo.sourcelearnerdddchangedid (olddddlearnerid, newdddlearnerid, dataimportid, sourcerownumber, submissiondate)
        SELECT
            u.olddddlearnerid, u.newdddlearnerid, u.dataimportid, u.rownumber, u.submissiondate
            FROM t$updates AS u;
        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('SourceLearnerDDDChangedID updated: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(var_DataImportID, var_LogDescription);
        CREATE TEMPORARY TABLE t$canapply
        AS
        SELECT
            *
            FROM t$updates AS u
            CROSS JOIN LATERAL (SELECT
                COUNT(*) AS oldcount
                FROM dbo.sourcelearner AS o
                WHERE u.olddddlearnerid = o.dddlearnerid) AS o
            CROSS JOIN LATERAL (SELECT
                COUNT(*) AS newcount
                FROM dbo.sourcelearner AS n
                WHERE u.newdddlearnerid = n.dddlearnerid) AS n
            WHERE o.OldCount > 0 AND n.newcount = 0;
        /*
        [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('Changes to apply: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(var_DataImportID, var_LogDescription);
        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$canapply AS c
            WHERE sl.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_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('SourceLearner updated: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(var_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.DDDLearnerID', RowNumber, FORMATMESSAGE('%d -> %d', OldDDDLearnerID, NewDDDLearnerID)
                FROM #SourceLearnerUpdate
        */
        CREATE TEMPORARY TABLE t$personexternalidupdate
        (personid INTEGER,
            olddddlearnerid INTEGER,
            newdddlearnerid INTEGER);
        WITH upd_stat (personid, olddddlearnerid, newdddlearnerid)
        AS (UPDATE dbo.personexternalid AS id
        SET externalid = c.newdddlearnerid
        FROM t$canapply AS c
            WHERE id.personexternalidtype = 9 AND id.externalid = CAST (c.olddddlearnerid AS VARCHAR(50))
            RETURNING personid, olddddlearnerid, newdddlearnerid)
        INSERT INTO t$personexternalidupdate (personid, olddddlearnerid, newdddlearnerid)
        SELECT
            *
            FROM upd_stat;
        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('PersonExternalID[9] updated: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(var_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
        */
        UPDATE dbo.sourcelearnerdddchangedid AS u
        SET appliedtimestamp = clock_timestamp()
        FROM t$canapply AS o
            WHERE u.olddddlearnerid = o.olddddlearnerid;
        -- CALL internal.pupdatedataimport(par_DataImportID := var_DataImportID, par_DataImportStatus := 'Success', par_Message := '', return_code => pupdatedataimport$ReturnCode);
        select * from internal.pupdatedataimport(var_DataImportID, 'Success', '');

        IF var_TranCountOnEntry = 0 THEN
            COMMIT;
        END IF;
        EXCEPTION
            WHEN OTHERS THEN
                IF var_TranCountOnEntry = 0 THEN
                    ROLLBACK;
                END IF;
                RAISE;
    END;
    return_code := 0;
    RETURN;
    /*
    
    DROP TABLE IF EXISTS t$changes;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$updates;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$canapply;
    */
    /*
    
    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;