USP_DATALIST_FUNDINGREQUESTHISTORY

                CREATE procedure dbo.USP_DATALIST_FUNDINGREQUESTHISTORY
                (
                    @FUNDINGREQUESTID uniqueidentifier,
                    @USERID uniqueidentifier = null,
                    @ACTIONTYPECODE tinyint = 0,
                    @SHOWCODE smallint = 1,
                    @STARTDATE datetime = null,
                    @ENDDATE datetime = null,
                    @RECORDTYPECODE tinyint = 0
                )
                as
                    set nocount on;

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = getdate();

                    if @SHOWCODE in (1,2,3,4,5,6)
                        begin
                            set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);

                            if @SHOWCODE = 1 --Last 30 days

                                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-29,@CURRENTDATE));
                            else if @SHOWCODE = 2 --Last 60 days

                                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-59,@CURRENTDATE));
                            else if @SHOWCODE = 3 --Last 90 days

                                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-89,@CURRENTDATE));
                            else if @SHOWCODE = 4 --Last 6 months

                                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(month,-6,@CURRENTDATE)));
                            else if @SHOWCODE = 5 --Last year

                                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(year,-1,@CURRENTDATE)));
                            else if @SHOWCODE = 6 --Last 5 years

                                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(year,-5,@CURRENTDATE)));
                        end
                    else if @SHOWCODE = 0
                        begin
                            set @STARTDATE = null;
                            set @ENDDATE = null;
                        end
                    else if @SHOWCODE = 99
                        begin
                            set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
                            set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);
                        end

                    declare @USERNAME nvarchar(128);
                    if not @USERID is null
                        select 
                            @USERNAME = USERNAME 
                        from dbo.APPUSER 
                        where APPUSER.ID = @USERID;
                    else
                        set @USERNAME = '';

                    select 
                        RECORDID,
                        DATEADDED as [AUDITDATE],
                        'Add' as [ACTION],
                        FIELD,
                        OLD,
                        NEW,
                        CHANGEDBYUSER,
                        PROCESS,
                        CHANGEDBYAPP,
                        -1 as [SEQUENCE],
                        2 [ACTIONSORT]
                    from
                        dbo.UFN_AUDIT_GETINSERTS_FORTABLE('FUNDINGREQUEST', 'ID', @FUNDINGREQUESTID, 0, null)
                    where
                        (@USERID is null or CHANGEDBYUSER = @USERNAME)
                        and (@STARTDATE is null or DATEADDED >= @STARTDATE)
                        and (@ENDDATE is null or DATEADDED <= @ENDDATE)
                        and (@ACTIONTYPECODE = 0 or @ACTIONTYPECODE = 1)

                    union all

                    select
                        @FUNDINGREQUESTID as [RECORDID],
                        [CHANGES].DATECHANGED,
                        'Edit' as [ACTION],
                        [DETAIL].FIELD,
                        [DETAIL].OLD,
                        [DETAIL].NEW,
                        [CHANGES].CHANGEDBYUSER,
                        '' as [PROCESS],
                        [CHANGES].CHANGEDBYAPP,
                        0 as [SEQUENCE],
                        1 as [ACTIONSORT]
                    from dbo.UFN_AUDIT_GETRECENTCHANGES_FORTABLE('FUNDINGREQUEST', 500, null, null, 0, 1, 0, null, null, @FUNDINGREQUESTID) as [CHANGES]
                    cross apply dbo.UFN_AUDIT_GETDETAIL('FUNDINGREQUESTAUDIT', [CHANGES].AUDITKEY, @FUNDINGREQUESTID ) as [DETAIL]                    
                    where
                        ([DETAIL].FIELD <> 'DATECHANGED' and [DETAIL].FIELD <> 'CHANGEDBYID' and [DETAIL].FIELD <> 'ADDEDBYID' and [DETAIL].FIELD <> 'DATEADDED')
                        and
                            (
                                @RECORDTYPECODE = 0
                                or (@RECORDTYPECODE = 1 and [DETAIL].FIELD = 'AMOUNTREQUESTED')
                                or (@RECORDTYPECODE = 2 and [DETAIL].FIELD = 'COMMENT')
                                or (@RECORDTYPECODE = 3 and [DETAIL].FIELD = 'DATESUBMITTED')
                                or (@RECORDTYPECODE = 4 and [DETAIL].FIELD = 'GRANTSID')
                                or (@RECORDTYPECODE = 5 and [DETAIL].FIELD = 'PRIMARYMANAGERID')
                                or (@RECORDTYPECODE = 6 and [DETAIL].FIELD = 'SECONDARYMANAGERID')
                                or (@RECORDTYPECODE = 7 and [DETAIL].FIELD = 'FUNDINGREQUESTSTATUSCODEID')
                            )
                        and (@USERID is null or CHANGEDBYUSER = @USERNAME)
                        and (@ACTIONTYPECODE = 0 or @ACTIONTYPECODE = 2)
                        and (@STARTDATE is null or [CHANGES].DATECHANGED >= @STARTDATE)
                        and (@ENDDATE is null or [CHANGES].DATECHANGED <= @ENDDATE)
                    order by
                        [ACTIONSORT] asc, SEQUENCE asc, AUDITDATE, RECORDID, FIELD;