USP_DATALIST_RECEIVABLECREDITHISTORY

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

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

                    declare @USERNAME nvarchar(128);

                    declare @CREDITDATEADDED datetime;
                    select
                        @CREDITDATEADDED = DATEADDED
                    from dbo.FINANCIALTRANSACTION
                    where ID = @CREDITID;

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

                    -- Handle the date filters

                    if (@SHOWCODE = 0)        -- all

                    begin
                        set @STARTDATE = null
                        set @ENDDATE = null
                    end

                    -- See if we are filtering, if so then we know the end date is today

                    if (@SHOWCODE >= 1) and (@SHOWCODE < 99)
                    begin
                        set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(getdate());

                        if (@SHOWCODE <= 3)        -- Last 30, 60, 90 days

                        begin
                            set @STARTDATE = dateadd(d, -30 * @SHOWCODE, @ENDDATE)
                        end

                        if (@SHOWCODE = 4)        -- Last 6 months

                        begin
                            set @STARTDATE = dateadd(m, -6, @ENDDATE)
                        end

                        if (@SHOWCODE = 5)        -- Last year

                        begin
                            set @STARTDATE = dateadd(y, -1, @ENDDATE)
                        end

                        if (@SHOWCODE = 6)        -- Last 5 years

                        begin
                            set @STARTDATE = dateadd(y, -5, @ENDDATE)
                        end

                    end

                    --Handle charge fields

                    if (@RECORDTYPECODE = 0) or (@RECORDTYPECODE = 1)
                    begin
                        -- DO NOT Get the insert into the charge table, the insert of the financial transaction will handle this


                        -- Get updates to the charge table

                        if (@ACTIONTYPECODE = 0) or (@ACTIONTYPECODE = 2)
                        begin

                            insert into @HISTORY(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
                                select
                                    @CREDITID as [RECORDID],
                                    [CHANGES].DATECHANGED,
                                    'Update' as [ACTION],
                                    [DETAIL].FIELD,
                                    case [DETAIL].FIELD when 'BILLINGITEMID' then dbo.UFN_BILLINGITEM_GETNAMEFROMPRODUCT([DETAIL].OLD) else [DETAIL].OLD end as OLD,
                                    case [DETAIL].FIELD when 'BILLINGITEMID' then dbo.UFN_BILLINGITEM_GETNAMEFROMPRODUCT([DETAIL].NEW) else [DETAIL].NEW end as NEW,
                                    [CHANGES].CHANGEDBYUSER,
                                    '' as [PROCESS],
                                    [CHANGES].CHANGEDBYAPP,
                                    0 as [SEQUENCE]
                                from dbo.UFN_AUDIT_GETRECENTCHANGES_FORTABLE('RECEIVABLECREDIT', 500, null, null, 0, 1, 0, null, null, @CREDITID) as [CHANGES]
                                cross apply dbo.UFN_AUDIT_GETDETAIL('RECEIVABLECREDITAUDIT', [CHANGES].AUDITKEY, @CREDITID ) as [DETAIL]
                                where ( [DETAIL].FIELD <> 'DATECHANGED' and [DETAIL].FIELD <> 'CHANGEDBYID' and [DETAIL].FIELD <> 'ADDEDBYID' and [DETAIL].FIELD <> 'DATEADDED' and [DETAIL].FIELD <> 'TYPE')
                                    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

                    --Get the generic financial transaction changes

                    insert into @HISTORY(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
                    select RECORDID, 
                            AUDITDATE, 
                            ACTION, 
                            FIELD, 
                            OLD, 
                            NEW, 
                            CHANGEDBYUSER, 
                            PROCESS, 
                            CHANGEDBYAPP, 
                            SEQUENCE
                    from dbo.UFN_FINANCIALTRANSACTION_GETHISTORY(@CREDITID, @USERID, @ACTIONTYPECODE, @STARTDATE, @ENDDATE, @RECORDTYPECODE, 1);

                    /*
                    -- Get the charge line item information
                    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,
                        case FIELD when 'BILLINGITEMID' then dbo.UFN_BILLINGITEM_GETNAMEFROMPRODUCT(OLD) else OLD end as OLD,
                        case FIELD when 'BILLINGITEMID' then dbo.UFN_BILLINGITEM_GETNAMEFROMPRODUCT(NEW) else NEW end as NEW,
                        CHANGEDBYUSER,
                        '' as [PROCESS],
                        CHANGEDBYAPP,
                        50 as [SEQUENCE]
                    from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('BILLINGITEMID', 'RECEIVABLECREDITLINEITEM', 'CREDITID', @CREDITID, @CREDITDATEADDED, 'Line item', '')
                    where (@ACTIONTYPECODE = 0)
                            or ((@ACTIONTYPECODE = 1) and (ACTION = 'Insert'))
                            or ((@ACTIONTYPECODE = 2) and (ACTION = 'Update'))
                            or ((@ACTIONTYPECODE = 3) and (ACTION = 'Delete'));
                    */

                    -- Return the contents of the history table

                    select RECORDID, 
                            AUDITDATE, 
                            case when ACTION = 'Insert' then 'Add' when ACTION = 'Update' then 'Edit' else ACTION end as [ACTION], 
                            FIELD, 
                            OLD as ValueBefore, 
                            NEW as ValueAfter, 
                            CHANGEDBYUSER, 
                            PROCESS, 
                            CHANGEDBYAPP, 
                            SEQUENCE,
                            case when ACTION = 'Delete' then 0 when ACTION = 'Update' then 1 else 2 end as [ACTIONSORT]
                    from @HISTORY;

                end