USP_DATALIST_REVENUEHISTORY
Displays historic audit information for a revenue item.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @USERID | uniqueidentifier | IN | User |
| @ACTIONTYPECODE | tinyint | IN | Action |
| @SHOWCODE | smallint | IN | Show |
| @STARTDATE | datetime | IN | Start date |
| @ENDDATE | datetime | IN | End date |
| @RECORDTYPECODE | tinyint | IN | Field type |
Definition
Copy
CREATE procedure [dbo].[USP_DATALIST_REVENUEHISTORY]
(
@REVENUEID uniqueidentifier,
@USERID uniqueidentifier = null,
@ACTIONTYPECODE tinyint = 0,
@SHOWCODE smallint = 1,
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@RECORDTYPECODE tinyint = 0
)
as
set nocount on;
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
declare @USERNAME nvarchar(128);
declare @REVENUEDATEADDED datetime;
select
@REVENUEDATEADDED = DATEADDED
from dbo.CONSTITUENT
where ID = @REVENUEID;
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)
)
--Transaction information
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 1
begin
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('FINANCIALTRANSACTION', 'ID', @REVENUEID, 0, null)
where (@USERID is null or CHANGEDBYUSER = @USERNAME)
and (@STARTDATE is null or AUDITDATE >= @STARTDATE)
and (@ENDDATE is null or AUDITDATE <= @ENDDATE);
insert into @TEMP(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
select
@REVENUEID as [RECORDID],
[CHANGES].DATECHANGED,
'Update' as [ACTION],
case [DETAIL].FIELD
when 'BASEAMOUNT' then 'AMOUNT'
when 'ORGAMOUNT' then 'ORGANIZATIONAMOUNT'
when 'POSTSTATUSCODE' then case when [DETAIL].NEW = 'Posted'
then 'Posted'
else 'DONOTPOST' end
else [DETAIL].FIELD end [FIELD],
case [DETAIL].FIELD
when 'POSTSTATUSCODE'
then case [DETAIL].NEW
when 'Do not post' then '0'
when 'Not posted' then '1'
else [DETAIL].OLD
end
else [DETAIL].OLD
end [OLD],
case [DETAIL].FIELD
when 'POSTSTATUSCODE'
then case [DETAIL].NEW
when 'Do not post' then '1'
when 'Not posted' then '0'
else [DETAIL].NEW end
else [DETAIL].NEW
end [NEW],
[CHANGES].CHANGEDBYUSER,
'' as [PROCESS],
[CHANGES].CHANGEDBYAPP,
0 as [SEQUENCE]
from dbo.UFN_AUDIT_GETRECENTCHANGES_FORTABLE('FINANCIALTRANSACTION', 500, @STARTDATE, @ENDDATE, 0, 1, 0, null, null, @REVENUEID) as [CHANGES]
cross apply dbo.UFN_AUDIT_GETDETAIL('FINANCIALTRANSACTIONAUDIT', [CHANGES].AUDITKEY, @REVENUEID) 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)
union all
select
@REVENUEID 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('REVENUE_EXT', 500, @STARTDATE, @ENDDATE, 0, 1, 0, null, null, @REVENUEID) as [CHANGES]
cross apply dbo.UFN_AUDIT_GETDETAIL('REVENUE_EXTAUDIT', [CHANGES].AUDITKEY, @REVENUEID) 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)
union all
select
@REVENUEID 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('PLEDGEORIGINALAMOUNT', 500, @STARTDATE, @ENDDATE, 0, 1, 0, null, null, @REVENUEID) as [CHANGES]
cross apply dbo.UFN_AUDIT_GETDETAIL_BYFIELDLIST('PLEDGEORIGINALAMOUNTAUDIT', [CHANGES].AUDITKEY, @REVENUEID, 'ORIGINALAMOUNT' ) as [DETAIL]
where (@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)
union all
select
@REVENUEID 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('PAYMENTORIGINALAMOUNT', 500, @STARTDATE, @ENDDATE, 0, 1, 0, null, null, @REVENUEID) as [CHANGES]
cross apply dbo.UFN_AUDIT_GETDETAIL_BYFIELDLIST('PAYMENTORIGINALAMOUNTAUDIT', [CHANGES].AUDITKEY, @REVENUEID, 'ORIGINALAMOUNT' ) as [DETAIL]
where (@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)
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,
PROCESS,
CHANGEDBYAPP,
0 as [SEQUENCE]
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('RECEIPTDATE','REVENUERECEIPT','REVENUEID',@REVENUEID,null,'','', @STARTDATE, @ENDDATE)
where (@USERID is null or CHANGEDBYUSER = @USERNAME)
and (@STARTDATE is null or AUDITDATE >= @STARTDATE)
and (@ENDDATE is null or AUDITDATE <= @ENDDATE)
;
end
--Payment method information
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 2
begin
insert into @TEMP(RECORDID, AUDITDATE, ACTION, FIELD, OLD, NEW, CHANGEDBYUSER, PROCESS, CHANGEDBYAPP, SEQUENCE)
select
RECORDID,
AUDITDATE,
ACTION,
FIELD,
OLD,
NEW,
CHANGEDBYUSER,
PROCESS,
CHANGEDBYAPP,
SEQUENCE --Since each payment method gets its own sequence, the sequence for payment methods are included in the results from the function
from dbo.UFN_REVENUEHISTORY_PAYMENTMETHOD_AUDIT2(@REVENUEID, @REVENUEDATEADDED, @ACTIONTYPECODE, @STARTDATE, @ENDDATE)
where (@USERID is null or CHANGEDBYUSER = @USERNAME)
and (@STARTDATE is null or AUDITDATE >= @STARTDATE)
and (@ENDDATE is null or AUDITDATE <= @ENDDATE)
end
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_REVENUEHISTORY_REVENUECHILDTABLES_AUDIT2(@REVENUEID, @REVENUEDATEADDED, @ACTIONTYPECODE, @RECORDTYPECODE, @STARTDATE, @ENDDATE)
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,
OLD as ValueBefore,
NEW as ValueAfter,
CHANGEDBYUSER,
PROCESS,
CHANGEDBYAPP,
SEQUENCE,
case when ACTION = 'Insert' then 0 when ACTION = 'Update' then 1 else 2 end as [ACTIONSORT]
from
@TEMP
order by
[ACTIONSORT] asc, AUDITDATE, SEQUENCE asc, RECORDID, FIELD;