UFN_FINANCIALTRANSACTION_GETHISTORY

            CREATE function dbo.UFN_FINANCIALTRANSACTION_GETHISTORY
            (
                @FINANCIALTRANSACTIONID uniqueidentifier,
                @USERID uniqueidentifier,
                @ACTIONTYPECODE tinyint,
                @STARTDATE datetime,
                @ENDDATE datetime,
                @RECORDTYPECODE tinyint = 0,
                @INCLUDELINEITEMS bit = 1
            )
            returns @HISTORY 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)
                )
            as begin

                declare @USERNAME nvarchar(128);

                -- If we are including a child object then we need to get the date added

                if (@INCLUDELINEITEMS = 1) and ((@RECORDTYPECODE = 0) or (@RECORDTYPECODE = 2)) or (@RECORDTYPECODE > 2)
                begin
                    declare @FINANCIALTRANSACTIONDATEADDED datetime;
                    select
                        @FINANCIALTRANSACTIONDATEADDED = DATEADDED
                    from dbo.FINANCIALTRANSACTION
                    where ID = @FINANCIALTRANSACTIONID;
                end

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

                --Handle generic transaction fields

                if (@RECORDTYPECODE = 0) or (@RECORDTYPECODE = 1)
                begin
                    -- Get the insert into the financial transaction table

                    if (@ACTIONTYPECODE = 0) or (@ACTIONTYPECODE = 1)
                    begin
                        insert into @HISTORY(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('FINANCIALTRANSACTION', 'ID', @FINANCIALTRANSACTIONID, 0, null)
                            where (FIELD <> 'TYPECODE')
                                and (@USERID is null or CHANGEDBYUSER = @USERNAME)
                                and (@STARTDATE is null or DATEADDED >= @STARTDATE)
                                and (@ENDDATE is null or DATEADDED <= @ENDDATE);
                    end

                    if (@ACTIONTYPECODE = 0) or (@ACTIONTYPECODE = 2)
                    begin
                        -- Get updates to the financial transaction table

                        insert into @HISTORY(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
                            select
                                @FINANCIALTRANSACTIONID 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('FINANCIALTRANSACTION', 500, null, null, 0, 1, 0, null, null, @FINANCIALTRANSACTIONID) as [CHANGES]
                            cross apply dbo.UFN_AUDIT_GETDETAIL('FINANCIALTRANSACTIONAUDIT', [CHANGES].AUDITKEY, @FINANCIALTRANSACTIONID ) as [DETAIL]                    
                            where ( [DETAIL].FIELD <> 'DATECHANGED' and [DETAIL].FIELD <> 'CHANGEDBYID' and [DETAIL].FIELD <> 'ADDEDBYID' and [DETAIL].FIELD <> 'DATEADDED' and [DETAIL].FIELD <> 'TYPECODE')
                                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
                end

 -- Handle generic line item fields

                --  Including a way for individual history lists to not expose the line item fields if they do not support a line item.

                if (@INCLUDELINEITEMS = 1) and ((@RECORDTYPECODE = 0) or (@RECORDTYPECODE = 2))
                begin
                    insert into @HISTORY(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
                    select 
                        RECORDID,
                        case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
                        ACTION,
                        case (select NEW from dbo.UFN_AUDIT_GETINSERTS_FORTABLE_BYFIELD('TYPECODE', 'FINANCIALTRANSACTIONLINEITEM', 'ID', RECORDID, 1, null))
                            when 'Standard' then
                                'Line item:' + FIELD
                            when 'Reversal' then
                                'Reversal:' + FIELD
                            when 'Application' then
                                'Application:' + FIELD
                        end,
                        OLD,
                        NEW,
                        CHANGEDBYUSER,
                        '' as [PROCESS],
                        CHANGEDBYAPP,
                        50 as [SEQUENCE]
                    from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('AMOUNT,SEQUENCE,DESCRIPTION', 'FINANCIALTRANSACTIONLINEITEM', 'FINANCIALTRANSACTIONID', @FINANCIALTRANSACTIONID, @FINANCIALTRANSACTIONDATEADDED, '', '')
                    where (@ACTIONTYPECODE = 0)
                            or ((@ACTIONTYPECODE = 1) and (ACTION = 'Insert'))
                            or ((@ACTIONTYPECODE = 2) and (ACTION = 'Update'))
                            or ((@ACTIONTYPECODE = 3) and (ACTION = 'Delete'));

                end

                -- Handle child record types

                insert into @HISTORY(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
                select 
                    RECORDID,
                    case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
                    ACTION,
                    FIELD,
                    OLD,
                    NEW,
                    CHANGEDBYUSER,
                    '' as [PROCESS],
                    CHANGEDBYAPP,
                    130 as [SEQUENCE]
                from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('DATEENTERED,TITLE,AUTHORID,TEXTNOTE,FINANCIALTRANSACTIONNOTETYPECODEID', 'FINANCIALTRANSACTIONNOTE', 'FINANCIALTRANSACTIONID', @FINANCIALTRANSACTIONID, @FINANCIALTRANSACTIONDATEADDED, 'Note', '')
                where @RECORDTYPECODE = 0 or @RECORDTYPECODE = 3
                union all
                select 
                    RECORDID,
                    case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
                    ACTION,
                    FIELD,
                    OLD,
                    NEW,
                    CHANGEDBYUSER,
                    '' as [PROCESS],
                    CHANGEDBYAPP,
                    131 as [SEQUENCE]
                from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('DATEENTERED,TITLE,AUTHORID,FINANCIALTRANSACTIONMEDIALINKTYPECODEID,MEDIAURL', 'FINANCIALTRANSACTIONMEDIALINK', 'FINANCIALTRANSACTIONID', @FINANCIALTRANSACTIONID, @FINANCIALTRANSACTIONDATEADDED, 'Media link', '')
                where @RECORDTYPECODE = 0 or @RECORDTYPECODE = 3
                union all
                select 
                    RECORDID,
                    case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
                    ACTION,
                    FIELD,
                    OLD,
                    NEW,
                    CHANGEDBYUSER,
                    '' as [PROCESS],
                    CHANGEDBYAPP,
                    132 as [SEQUENCE]
                from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('DATEENTERED,TITLE,AUTHORID,FINANCIALTRANSACTIONATTACHMENTTYPECODEID,FILENAME', 'FINANCIALTRANSACTIONATTACHMENT', 'FINANCIALTRANSACTIONID', @FINANCIALTRANSACTIONID, @FINANCIALTRANSACTIONDATEADDED, 'Attachment', '')
                where @RECORDTYPECODE = 0 or @RECORDTYPECODE = 3

                return;
            end