USP_DATALIST_RECEIVABLEPAYMENTHISTORY
Returns history information about payments.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @RECEIVABLEPAYMENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @USERID | uniqueidentifier | IN | User |
| @ACTIONTYPECODE | tinyint | IN | Action |
| @SHOWCODE | tinyint | IN | Show |
| @STARTDATE | datetime | IN | Start date |
| @ENDDATE | datetime | IN | End date |
| @RECORDTYPECODE | tinyint | IN | Field type |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_RECEIVABLEPAYMENTHISTORY
(
@RECEIVABLEPAYMENTID 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 @RECEIVABLEPAYMENTDATEADDED datetime;
select
@RECEIVABLEPAYMENTDATEADDED = DATEADDED
from dbo.FINANCIALTRANSACTION
where ID = @RECEIVABLEPAYMENTID;
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 receivable payment fields
if (@RECORDTYPECODE = 0) or (@RECORDTYPECODE = 1)
begin
-- DO NOT Get the insert into the receivable payment table, the insert of the financial transaction will handle this
-- Get updates to the receivable payment table
if (@ACTIONTYPECODE = 0) or (@ACTIONTYPECODE = 2)
begin
-- First the base receivable payment table
insert into @HISTORY(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
select
@RECEIVABLEPAYMENTID 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('RECEIVABLEPAYMENT', 500, null, null, 0, 1, 0, null, null, @RECEIVABLEPAYMENTID) as [CHANGES]
cross apply dbo.UFN_AUDIT_GETDETAIL('RECEIVABLEPAYMENTAUDIT', [CHANGES].AUDITKEY, @RECEIVABLEPAYMENTID ) 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);
-- Second receivable payment check
insert into @HISTORY(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
select
@RECEIVABLEPAYMENTID 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('RECEIVABLEPAYMENTCHECK', 500, null, null, 0, 1, 0, null, null, @RECEIVABLEPAYMENTID) as [CHANGES]
cross apply dbo.UFN_AUDIT_GETDETAIL('RECEIVABLEPAYMENTCHECKAUDIT', [CHANGES].AUDITKEY, @RECEIVABLEPAYMENTID ) 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);
-- Third receivable payment credit card
insert into @HISTORY(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
select
@RECEIVABLEPAYMENTID 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('RECEIVABLEPAYMENTCREDITCARD', 500, null, null, 0, 1, 0, null, null, @RECEIVABLEPAYMENTID) as [CHANGES]
cross apply dbo.UFN_AUDIT_GETDETAIL('RECEIVABLEPAYMENTCREDITCARDAUDIT', [CHANGES].AUDITKEY, @RECEIVABLEPAYMENTID ) as [DETAIL]
where ( [DETAIL].FIELD <> 'DATECHANGED' and [DETAIL].FIELD <> 'CHANGEDBYID' and [DETAIL].FIELD <> 'ADDEDBYID' and [DETAIL].FIELD <> 'DATEADDED' and [DETAIL].FIELD <> 'TRANSACTIONID')
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);
-- Fourth receivable payment direct debit
insert into @HISTORY(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
select
@RECEIVABLEPAYMENTID as [RECORDID],
[CHANGES].DATECHANGED,
'Update' as [ACTION],
[DETAIL].FIELD,
case [DETAIL].FIELD when 'CONSTITUENTACCOUNTID' then dbo.UFN_CONSTITUENTACCOUNT_GETDESCRIPTION([DETAIL].OLD) else [DETAIL].OLD end as OLD,
case [DETAIL].FIELD when 'CONSTITUENTACCOUNTID' then dbo.UFN_CONSTITUENTACCOUNT_GETDESCRIPTION([DETAIL].NEW) else [DETAIL].NEW end as NEW,
[CHANGES].CHANGEDBYUSER,
'' as [PROCESS],
[CHANGES].CHANGEDBYAPP,
0 as [SEQUENCE]
from dbo.UFN_AUDIT_GETRECENTCHANGES_FORTABLE('RECEIVABLEPAYMENTDIRECTDEBIT', 500, null, null, 0, 1, 0, null, null, @RECEIVABLEPAYMENTID) as [CHANGES]
cross apply dbo.UFN_AUDIT_GETDETAIL('RECEIVABLEPAYMENTDIRECTDEBITAUDIT', [CHANGES].AUDITKEY, @RECEIVABLEPAYMENTID ) as [DETAIL]
where ( [DETAIL].FIELD <> 'DATECHANGED' and [DETAIL].FIELD <> 'CHANGEDBYID' and [DETAIL].FIELD <> 'ADDEDBYID' and [DETAIL].FIELD <> 'DATEADDED' and [DETAIL].FIELD <> 'TRANSACTIONID')
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(@RECEIVABLEPAYMENTID, @USERID, @ACTIONTYPECODE, @STARTDATE, @ENDDATE, @RECORDTYPECODE, 1); -- Receivable payments do not have line items (yet)
-- 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