USP_CONSTITUENT_GETHISTORY

            CREATE procedure dbo.USP_CONSTITUENT_GETHISTORY
            (
                @CONSTITUENTID uniqueidentifier,
                @USERID uniqueidentifier,
                @ACTIONTYPECODE tinyint,
                @STARTDATE datetime,
                @ENDDATE datetime,
                @RECORDTYPECODE tinyint = 0
            ) as
                set nocount on;

                declare @USERNAME nvarchar(128);

                declare @CONSTITUENTDATEADDED datetime;
                select
                    @CONSTITUENTDATEADDED = DATEADDED
                from dbo.CONSTITUENT
                where ID = @CONSTITUENTID;

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

                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)
                )


                --Constituent information 

                if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 1
                begin
                    -- Get the insert into the constituent table

                    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('CONSTITUENT', 'ID', @CONSTITUENTID, 0, null)
                            where (@USERID is null or CHANGEDBYUSER = @USERNAME)
                                and (@STARTDATE is null or DATEADDED >= @STARTDATE)
                                and (@ENDDATE is null or DATEADDED <= @ENDDATE)

                    -- Get updates to the constituent table

                    insert into @TEMP(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
                        select
                            @CONSTITUENTID 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('CONSTITUENT', 500, null, null, 0, 1, 0, null, null, @CONSTITUENTID) as [CHANGES]
                        cross apply dbo.UFN_AUDIT_GETDETAIL('CONSTITUENTAUDIT', [CHANGES].AUDITKEY, @CONSTITUENTID ) 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)
                            and (@STARTDATE is null or [CHANGES].DATECHANGED >= @STARTDATE)
                            and (@ENDDATE is null or [CHANGES].DATECHANGED <= @ENDDATE)
                end


                -- Get changes to organization information

                if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 3
                    insert into @TEMP(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
                    select 
                        RECORDID,
                        AUDITDATE,
                        ACTION,
                        FIELD,
                        OLD,
                        NEW,
                        CHANGEDBYUSER,
                        PROCESS,
                        CHANGEDBYAPP,
                        10 as [SEQUENCE]                    
                    from dbo.UFN_CONSTITUENTHISTORY_ORGANIZATIONDATA_AUDIT(@CONSTITUENTID, @CONSTITUENTDATEADDED, @ACTIONTYPECODE) 
                    where (@USERID is null or CHANGEDBYUSER = @USERNAME)
                        and (@STARTDATE is null or AUDITDATE >= @STARTDATE)
                        and (@ENDDATE is null or AUDITDATE <= @ENDDATE)

                -- Get changes to deceased information

                if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 2
                    insert into @TEMP(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
                    select 
                        RECORDID,
                        AUDITDATE,
                        ACTION,
                        FIELD,
                        OLD,
                        NEW,
                        CHANGEDBYUSER,
                        PROCESS,
                        CHANGEDBYAPP,
                        20 as [SEQUENCE]                    
                    from dbo.UFN_CONSTITUENTHISTORY_DECEASEDCONSTITUENT_AUDIT(@CONSTITUENTID, @CONSTITUENTDATEADDED, @ACTIONTYPECODE) 
                    where (@USERID is null or CHANGEDBYUSER = @USERNAME)
                        and (@STARTDATE is null or AUDITDATE >= @STARTDATE)
                        and (@ENDDATE is null or AUDITDATE <= @ENDDATE)

                -- Get changes to application user information

                if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 4
                    insert into @TEMP(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
                    select 
                        RECORDID,
                        AUDITDATE,
                        ACTION,
                        FIELD,
                        OLD,
                        NEW,
                        CHANGEDBYUSER,
                        PROCESS,
                        CHANGEDBYAPP,
                        30 as [SEQUENCE]                    
                    from dbo.UFN_CONSTITUENTHISTORY_APPUSER_AUDIT(@CONSTITUENTID, @CONSTITUENTDATEADDED, @ACTIONTYPECODE) 
                    where (@USERID is null or CHANGEDBYUSER = @USERNAME)
                        and (@STARTDATE is null or AUDITDATE >= @STARTDATE)
                        and (@ENDDATE is null or AUDITDATE <= @ENDDATE)

                -- Get changes to tables related to constituent

                insert into @TEMP(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
                    select
                        RECORDID,
                        AUDITDATE,
                        ACTION,
                        FIELD,
                        OLD,
                        NEW,
                        CHANGEDBYUSER,
                        PROCESS,
                        CHANGEDBYAPP,
                        SEQUENCE                    
                    from dbo.UFN_CONSTITUENTHISTORY_CONSTITUENTCHILDTABLES_RECORDTYPE_AUDIT(@CONSTITUENTID, @CONSTITUENTDATEADDED, @ACTIONTYPECODE, @RECORDTYPECODE)
                    where (@USERID is null or CHANGEDBYUSER = @USERNAME)
                        and (@STARTDATE is null or AUDITDATE >= @STARTDATE)
                        and (@ENDDATE is null or AUDITDATE <= @ENDDATE);

                select 
                    RECORDID, 
                    AUDITDATE, 
                    case when ACTION = 'Insert' then 'Add' when ACTION = 'Update' then 'Edit' else ACTION end as [ACTION], 
                    FIELD, 
                    case when FIELD = 'SSN' then '***-**-****' else OLD end ValueBefore, 
                    case when FIELD = 'SSN' then '***-**-****' else NEW end ValueAfter, 
                    CHANGEDBYUSER, 
                    PROCESS, 
                    CHANGEDBYAPP, 
                    SEQUENCE,
                    case when ACTION = 'Delete' then 0 when ACTION = 'Update' then 1 else 2 end as [ACTIONSORT]
                from
                    @TEMP
                where
                    FIELD <> 'SSNINDEX'
                order by
                    AUDITDATE, [ACTIONSORT] asc, SEQUENCE asc, RECORDID, FIELD;