USP_DATALIST_PLANNEDGIFTADDITIONHISTORY

                CREATE procedure dbo.USP_DATALIST_PLANNEDGIFTADDITIONHISTORY
                (
                    @PLANNEDGIFTADDITIONID uniqueidentifier,
                    @USERID uniqueidentifier = null,
                    @ACTIONTYPECODE tinyint = 0,
                    @SHOWCODE tinyint = 1,
                    @STARTDATE datetime = null,
                    @ENDDATE datetime = null,
                    @GROUPCODE bit = 0
                )
                as
                set nocount on;

                declare @USERNAME nvarchar(128);
                declare @PLANNEDGIFTADDITIONDATEADDED datetime;

                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

                if not @USERID is null
                    select 
                        @USERNAME = USERNAME 
                    from dbo.APPUSER 
                    where APPUSER.ID = @USERID;
                else
                    set @USERNAME = '';

                select @PLANNEDGIFTADDITIONDATEADDED = DATEADDED from dbo.PLANNEDGIFTADDITION where ID = @PLANNEDGIFTADDITIONID;

                declare @TEMP table
                (
                    RECORDID uniqueidentifier,
                    AUDITDATE datetime,
                    CHANGEDBYUSER nvarchar(128),
                    PROCESS nvarchar(255),
                    FIELD nvarchar(128),
                    OLD nvarchar(4000),
                    NEW nvarchar(4000),
                    SEQUENCE int,
                    ACTION nvarchar(8),
                    CHANGEDBYAPP nvarchar(200)
                )

                if @ACTIONTYPECODE = 0 or @ACTIONTYPECODE = 1
                    insert into @TEMP(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
                        select
                            RECORDID,
                            DATEADDED,
                            'Insert' as [ACTION],
                            FIELD,
                            OLD,
                            NEW,
                            CHANGEDBYUSER,
                            PROCESS,
                            CHANGEDBYAPP,
                            -1 as [SEQUENCE]
                        from dbo.UFN_AUDIT_GETINSERTS_FORTABLE('PLANNEDGIFTADDITION', 'ID', @PLANNEDGIFTADDITIONID, 0, null)
                        where @USERID is null or CHANGEDBYUSER = @USERNAME

                insert into @TEMP(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
                    select
                        @PLANNEDGIFTADDITIONID as [RECORDID],
                        [CHANGES].DATECHANGED,
                        'Update' as [ACTION],
                        [DETAIL].FIELD,
                        [DETAIL].OLD,
                        [DETAIL].NEW,
                        [CHANGES].CHANGEDBYUSER,
                        '' as [PROCESS],
                        [CHANGES].CHANGEDBYAPP,
                        0 as [SEQUENCE]
                    from dbo.UFN_AUDIT_GETRECENTCHANGES_FORTABLE('PLANNEDGIFTADDITION', 500, null, null, 0, 1, 0, null, null, @PLANNEDGIFTADDITIONID) as [CHANGES]
                    cross apply dbo.UFN_AUDIT_GETDETAIL('PLANNEDGIFTADDITIONAUDIT', [CHANGES].AUDITKEY, @PLANNEDGIFTADDITIONID) as [DETAIL]
                    where( [DETAIL].FIELD <> 'DATECHANGED' and [DETAIL].FIELD <> 'CHANGEDBYID' and [DETAIL].FIELD <> 'ADDEDBYID' and [DETAIL].FIELD <> 'DATEADDED')
                        and (@USERID is null or CHANGEDBYUSER = @USERNAME)
                        and (@ACTIONTYPECODE = 0 or @ACTIONTYPECODE = 2)

                    union all

                    select
                        RECORDID,
                        AUDITDATE,
                        ACTION,
                        FIELD,
                        OLD,
                        NEW,
                        CHANGEDBYUSER,
                        PROCESS,
                        CHANGEDBYAPP,
                        SEQUENCE
                    from dbo.UFN_PLANNEDGIFTADDITIONHISTORY_PLANNEDGIFTADDITIONCHILDTABLES_AUDIT(@PLANNEDGIFTADDITIONID, @PLANNEDGIFTADDITIONDATEADDED, @ACTIONTYPECODE)
                    where @USERID is null or CHANGEDBYUSER = @USERNAME

                    if @STARTDATE is not null
                        begin
                            delete from @TEMP where AUDITDATE < @STARTDATE;
                        end
                    if @ENDDATE is not null
                        begin
                            delete from @TEMP where AUDITDATE > @ENDDATE;
                        end

                select
                    RECORDID,
                    AUDITDATE,
                    ACTION,
                    FIELD,
                    OLD,
                    NEW,
                    CHANGEDBYUSER,
                    PROCESS,
                    CHANGEDBYAPP,
                    SEQUENCE,
                    case when ACTION = 'Delete' then 0 when ACTION = 'Update' then 1 else 2 end as [ACTIONSORT]
                from @TEMP
                order by AUDITDATE desc, [ACTIONSORT] asc, SEQUENCE asc, RECORDID, FIELD