UFN_ACADEMICCATALOG_GETHISTORY

Gets the history for a given academic catalog level.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode	Description
@LEVELCODE	tinyint	IN
@ID	uniqueidentifier	IN
Definition

Copy


CREATE function dbo.UFN_ACADEMICCATALOG_GETHISTORY
(
    @LEVELCODE tinyint,
    @ID uniqueidentifier
)
returns @TABLE table (ID uniqueidentifier, USERID nvarchar(50), NAME nvarchar(150), DATEFROM dbo.UDT_FUZZYDATE, DATETO dbo.UDT_FUZZYDATE, COMMENT nvarchar(500))
as
begin
    declare @DATEFROM dbo.UDT_FUZZYDATE;

    /****************   LEVEL 2 (Degree)   ****************/
    if @LEVELCODE = 2
    begin
        select
            @DATEFROM = DATEFROM
        from
            dbo.ACADEMICCATALOGDEGREE
        where
            ID = @ID;

        insert into @TABLE
        (ID, USERID, NAME, DATEFROM, DATETO, COMMENT)
        (
            select
                ID,
                USERID,
                NAME,
                DATEFROM,
                DATETO,
                COMMENT
            from
                dbo.ACADEMICCATALOGDEGREE
            where
                CURRENTID = @ID
                --and DATETO <= @DATEFROM

        );
    end

    /****************   LEVEL 3 (College)   ****************/
    if @LEVELCODE = 3
    begin
        select
            @DATEFROM = DATEFROM
        from
            dbo.ACADEMICCATALOGCOLLEGE
        where
            ID = @ID;

        insert into @TABLE
        (ID, USERID, NAME, DATEFROM, DATETO, COMMENT)
        (
            select
                ID,
                USERID,
                NAME,
                DATEFROM,
                DATETO,
                COMMENT
            from
                dbo.ACADEMICCATALOGCOLLEGE
            where
                CURRENTID = @ID
                --and DATETO <= @DATEFROM

        );
    end

    /****************   LEVEL 4 (Division)   ****************/
    if @LEVELCODE = 4
    begin
        select
            @DATEFROM = DATEFROM
        from
            dbo.ACADEMICCATALOGDIVISION
        where
            ID = @ID;

        insert into @TABLE
        (ID, USERID, NAME, DATEFROM, DATETO, COMMENT)
        (
            select
                ID,
                USERID,
                NAME,
                DATEFROM,
                DATETO,
                COMMENT
            from
                dbo.ACADEMICCATALOGDIVISION
            where
                CURRENTID = @ID
                --and DATETO <= @DATEFROM

        );
    end

    /****************   LEVEL 5 (Department)   ****************/
    if @LEVELCODE = 5
    begin
        select
            @DATEFROM = DATEFROM
        from
            dbo.ACADEMICCATALOGDEPARTMENT
        where
            ID = @ID;

        insert into @TABLE
        (ID, USERID, NAME, DATEFROM, DATETO, COMMENT)
        (
            select
                ID,
                USERID,
                NAME,
                DATEFROM,
                DATETO,
                COMMENT
            from
                dbo.ACADEMICCATALOGDEPARTMENT
            where
                CURRENTID = @ID
                --and DATETO <= @DATEFROM

            );
    end

    /****************   LEVEL 6 (Sub Department)   ****************/
    if @LEVELCODE = 6
    begin
        select
            @DATEFROM = DATEFROM
        from
            dbo.ACADEMICCATALOGSUBDEPARTMENT
        where
            ID = @ID;

        insert into @TABLE
        (ID, USERID, NAME, DATEFROM, DATETO, COMMENT)
        (
            select
                ID,
                USERID,
                NAME,
                DATEFROM,
                DATETO,
                COMMENT
            from
                dbo.ACADEMICCATALOGSUBDEPARTMENT
            where
                CURRENTID = @ID
                --and DATETO <= @DATEFROM

            );
    end

    return;
end