CREATE OR REPLACE PROCEDURE internal.ptransfer(IN par_sourcedataimportid bigint, IN par_tranaction character DEFAULT 'C'::bpchar, 
    IN par_validateonly numeric DEFAULT 0, IN par_parameters text DEFAULT NULL::text, IN par_debugmode integer DEFAULT 0, 
    IN par_outputlog integer DEFAULT 0, INOUT p_refcur refcursor DEFAULT NULL::refcursor)
AS 
$BODY$
/* @EMISCode                varchar(10) = null, */
/* @AllowExternalStudentIDMismatch bit = 0, */
/* @AllowStudentIDMismatch bit = 0 */
DECLARE
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_DataSourceTypeID INTEGER;
    var_DataSourceType VARCHAR(255);
    var_DataContractDataSource VARCHAR(255);
    var_DataContractDescription VARCHAR(255);
    var_Description VARCHAR(1024);
    var_Msg VARCHAR(2048);
    var_DataImportID BIGINT DEFAULT (SELECT
        dataimportid
        FROM dataimportidtable$ptransfer);
    var_Proc VARCHAR(1000);
    var_ProcParams VARCHAR(200) DEFAULT '@DataImportID=@DataImportID, @ValidateOnly=@ValidateOnly, @DebugMode=@DebugMode';
    var_Params VARCHAR(200) DEFAULT '@DataImportID bigint, @ValidateOnly bit, @DebugMode int';
    pupdatedataimport$ReturnCode 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;
    */
    SELECT
        datasourcetypeid, datasourcetype, datacontractdatasource, datacontractdescription
        INTO var_DataSourceTypeID, var_DataSourceType, var_DataContractDataSource, var_DataContractDescription
        FROM staging.dataimportdatacontract
        WHERE dataimportid = par_SourceDataImportID;
    var_Description := var_DataSourceType || ': ' || var_DataContractDescription;

    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 dataimportidtable$ptransfer
        (dataimportid BIGINT,
            description VARCHAR(1024));
        CALL internal.pnewdataimportid$TMP(par_SourceDataImportID := par_SourceDataImportID, par_Description := var_Description);
        INSERT INTO dataimportidtable$ptransfer
        SELECT
            *
            FROM pnewdataimportid$TMPTBL;
        RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %. Argument: %. Argument: %', '50000', 0, 0, 'Created DataImportID=%I64d [%s] for SourceDataImportID=%I64d', var_DataImportID, var_Description, par_SourceDataImportID USING ERRCODE = '50000';

        IF par_Parameters IS NOT NULL AND LENGTH(par_Parameters) > 0 THEN
            var_ProcParams := var_ProcParams || ',' || par_Parameters;
        END IF;
        /* SELECT * FROM staging.DataSourceType */

        IF var_DataSourceTypeID IN (1) THEN
            var_Proc := 'internal.pTransferReadingEggs';
        /* ReadingEggs */
        ELSE
            IF var_DataSourceTypeID IN (2) THEN
                var_Proc := 'internal.pTransferReadingEggs2';
            /* ReadingEggs2 */
            ELSE
                IF var_DataSourceTypeID IN (7) THEN
                    var_Proc := 'internal.pTransferMathseeds';
                /* Mathseeds */
                ELSE
                    IF var_DataSourceTypeID IN (8) THEN
                        var_Proc := 'internal.pTransferLearner';
                    /* Learner - DDD */
                    ELSE
                        IF var_DataSourceTypeID IN (9) THEN
                            var_Proc := 'internal.pTransferMasterList';
                        /* MasterList */
                        ELSE
                            IF var_DataSourceTypeID IN (10) THEN
                                var_Proc := 'internal.pTransferEnvironment';
                            /* Environment */
                            ELSE
                                IF var_DataSourceTypeID IN (11) THEN
                                    var_Proc := 'internal.pTransferLearner';
                                /* Learner - Manual */
                                ELSE
                                    IF var_DataSourceTypeID IN (14) THEN
                                        var_Proc := 'internal.pTransferLearner';
                                    /* Learner - CEMIS */
                                    ELSE
                                        IF var_DataSourceTypeID IN (16) THEN
                                            var_Proc := 'internal.pTransferReadingEggsLearnerList';
                                        /* ReadingEggs Learner List */
                                        ELSE
                                            IF var_DataSourceTypeID IN (17) THEN
                                                var_Proc := 'internal.pTransferQuiz';
                                            ELSE
                                                IF var_DataSourceTypeID IN (19) THEN
                                                    var_Proc := 'internal.pTransferParent';
                                                ELSE
                                                    IF var_DataSourceTypeID IN (26) THEN
                                                        var_Proc := 'internal.pTransferMatificLearnerList';
                                                    ELSE
                                                        IF var_DataSourceTypeID IN (27) THEN
                                                            var_Proc := 'internal.pTransferReadingEggsNewLearnerList';
                                                        ELSE
                                                            IF var_DataSourceTypeID IN (28) THEN
                                                                var_Proc := 'internal.pTransferParent2';
                                                            ELSE
                                                                IF var_DataSourceTypeID IN (29) THEN
                                                                    var_Proc := 'internal.pTransferLearnerDDDID';
                                                                END IF;
                                                            END IF;
                                                        END IF;
                                                    END IF;
                                                END IF;
                                            END IF;
                                        END IF;
                                    END IF;
                                END IF;
                            END IF;
                        END IF;
                    END IF;
                END IF;
            END IF;
        END IF;
        /* ELSE IF @DataSourceTypeID IN (30)    SET @Proc = N'internal.pTransferLearnerDDDMergedID'; */

        IF var_Proc IS NULL THEN
            RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 16, 0, 'Unknown DataSourceType: %s', var_DataSourceType USING ERRCODE = '50000';
        END IF;
        RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'Executing: %s', var_Proc USING ERRCODE = '50000';
        var_Proc := 'EXEC ' || var_Proc || ' ' || var_ProcParams;
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        EXEC sp_executesql @stmt = @Proc, @params=@Params, @DataImportID=@DataImportID, @DebugMode=@DebugMode, @ValidateOnly=@ValidateOnly;
        */
        /* PRINT @Proc; */
        CALL internal.pupdatedataimport(par_DataImportID := var_DataImportID, par_DataImportStatus := 'Success', return_code => pupdatedataimport$ReturnCode);

        IF par_OutputLog = 1 THEN
            OPEN p_refcur FOR
            SELECT
                *
                FROM internal.dataimportlog AS l
                WHERE l.dataimportid = var_DataImportID;
        END IF;

        IF var_TranCountOnEntry = 0 THEN
            IF LOWER(par_TranAction) = LOWER('C') THEN
                RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 0, 0, 'COMMIT TRAN' USING ERRCODE = '50000';
                COMMIT;
            ELSE
                IF LOWER(par_TranAction) = LOWER('R') 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;
            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;
                /* SET @Msg = ERROR_MESSAGE() + '[Line ' + CAST(ERROR_LINE() AS varchar(20)) + ']'; */
                /* EXEC internal.pUpdateDataImport @DataImportID=@DataImportID, @DataImportStatus='Failed', @Message=@Msg; */
                RAISE;
                DROP TABLE IF EXISTS dataimportidtable$ptransfer;
    END;
END;
$BODY$
LANGUAGE plpgsql;