UFN_REVENUEHISTORY_REVENUECHILDTABLES_AUDIT
Returns the audit information for revenue detail child tables for a given revenue record.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @REVENUEDATEADDED | datetime | IN | |
| @ACTIONTYPECODE | tinyint | IN | |
| @RECORDTYPECODE | tinyint | IN |
Definition
Copy
CREATE function dbo.UFN_REVENUEHISTORY_REVENUECHILDTABLES_AUDIT
(
@REVENUEID uniqueidentifier,
@REVENUEDATEADDED datetime,
@ACTIONTYPECODE tinyint,
@RECORDTYPECODE tinyint
)
returns @RESULTS 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 @DETAILID uniqueidentifier;
declare @REVENUETYPECODE tinyint;
--Letter
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 3
insert into @RESULTS
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
200 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('LETTERCODEID, PROCESSDATE, ACKNOWLEDGEDATE, ACKNOWLEDGEEID , MKTPACKAGEID','REVENUELETTER','REVENUEID',@REVENUEID,null,'Letter','')
--Benefits
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 4
insert into @RESULTS
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
40 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('BENEFITID,QUANTITY,UNITVALUE,DETAILS,PERCENTAPPLICABLEAMOUNT,VALUEPERCENT','REVENUEBENEFIT','REVENUEID',@REVENUEID,@REVENUEDATEADDED,'Benefit for','')
--GL
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 5
insert into @RESULTS
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
50 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('GLPAYMENTMETHODREVENUETYPEMAPPINGID,ACCOUNT,TRANSACTIONTYPECODE,PROJECT,REFERENCE,AMOUNT','REVENUEGLDISTRIBUTION','REVENUEID',@REVENUEID,@REVENUEDATEADDED,'GL Distribution for','')
--Schedule
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 6
insert into @RESULTS
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
60 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('STARTDATE,ENDDATE,NUMBEROFINSTALLMENTS,FREQUENCYCODE,NEXTTRANSACTIONDATE,STATUSCODE,SENDPLEDGEREMINDER,ISPENDING,CREDITCARDID','REVENUESCHEDULE','ID',@REVENUEID,@REVENUEDATEADDED,'Schedule','')
--Installments
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 7
insert into @RESULTS
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
70 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('DATE,AMOUNT','INSTALLMENT','REVENUEID',@REVENUEID,@REVENUEDATEADDED,'Installment','')
--RGA
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 8
insert into @RESULTS
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
80 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('TYPECODE, AMOUNT, SCHEDULEDATE','RECURRINGGIFTACTIVITY','SOURCEREVENUEID',@REVENUEID,null,'Recurring gift activity','')
--MG Claim
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 9
insert into @RESULTS
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
90 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('MGSOURCEREVENUEID, ISACTIVE','REVENUEMATCHINGGIFT','ID',@REVENUEID,null,'Matching gift claim for','')
--Donor Challenge
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 10
insert into @RESULTS
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
170 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('REVENUESPLITID','DONORCHALLENGEENCUMBERED','MATCHEDREVENUEID',@REVENUEID,null,'Donor Challenge claim for','')
declare @REVENUESPLITSTIDS table (ID uniqueidentifier)
if(@RECORDTYPECODE in (0,11,12,13,14,15,16,18))
begin
insert into @REVENUESPLITSTIDS
select ID from dbo.REVENUESPLIT where REVENUEID = @REVENUEID
union
select AUDITRECORDID as ID from dbo.REVENUESPLITAUDIT where REVENUEID = @REVENUEID
end
--Application details
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 11
insert into @RESULTS
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
30 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('DESIGNATIONID,AMOUNT','REVENUESPLIT','REVENUEID',@REVENUEID,@REVENUEDATEADDED,'Application details','')
union all
select
DETAILS.RECORDID,
case when DETAILS.ACTION = 'Insert' then DETAILS.DATEADDED when DETAILS.ACTION = 'Update' then DETAILS.DATECHANGED else DETAILS.AUDITDATE end as [AUDITDATE],
DETAILS.CHANGEDBYUSER,
DETAILS.PROCESS,
DETAILS.FIELD,
DETAILS.OLD,
DETAILS.NEW,
100 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from @REVENUESPLITSTIDS as REVENUESPLIT
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('OTHERTYPECODEID', 'REVENUESPLITOTHER', 'ID', REVENUESPLIT.ID, @REVENUEDATEADDED, 'Other application', '') DETAILS
union all
select
DETAILS.RECORDID,
case when DETAILS.ACTION = 'Insert' then DETAILS.DATEADDED when DETAILS.ACTION = 'Update' then DETAILS.DATECHANGED else DETAILS.AUDITDATE end as [AUDITDATE],
DETAILS.CHANGEDBYUSER,
DETAILS.PROCESS,
DETAILS.FIELD,
DETAILS.OLD,
DETAILS.NEW,
110 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from @REVENUESPLITSTIDS as REVENUESPLIT
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('GLREVENUECATEGORYMAPPINGID', 'REVENUECATEGORY', 'ID', REVENUESPLIT.ID, @REVENUEDATEADDED, 'Revenue Category', '') DETAILS
--Solicitors
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 12
insert into @RESULTS
select
DETAILS.RECORDID,
case when DETAILS.ACTION = 'Insert' then DETAILS.DATEADDED when DETAILS.ACTION = 'Update' then DETAILS.DATECHANGED else DETAILS.AUDITDATE end as [AUDITDATE],
DETAILS.CHANGEDBYUSER,
DETAILS.PROCESS,
DETAILS.FIELD,
DETAILS.OLD,
DETAILS.NEW,
120 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from @REVENUESPLITSTIDS as REVENUESPLIT
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('CONSTITUENTID, AMOUNT', 'REVENUESOLICITOR', 'REVENUESPLITID', REVENUESPLIT.ID, @REVENUEDATEADDED, 'Solicitors', '') DETAILS
--Recognition
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 13
insert into @RESULTS
select
DETAILS.RECORDID,
case when DETAILS.ACTION = 'Insert' then DETAILS.DATEADDED when DETAILS.ACTION = 'Update' then DETAILS.DATECHANGED else DETAILS.AUDITDATE end as [AUDITDATE],
DETAILS.CHANGEDBYUSER,
DETAILS.PROCESS,
DETAILS.FIELD,
DETAILS.OLD,
DETAILS.NEW,
130 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from @REVENUESPLITSTIDS as REVENUESPLIT
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('CONSTITUENTID, AMOUNT, EFFECTIVEDATE, REVENUERECOGNITIONTYPECODEID', 'REVENUERECOGNITION', 'REVENUESPLITID', REVENUESPLIT.ID, @REVENUEDATEADDED, 'Recognition', '') DETAILS
--Named Recognition
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 14
insert into @RESULTS
select
DETAILS.RECORDID,
case when DETAILS.ACTION = 'Insert' then DETAILS.DATEADDED when DETAILS.ACTION = 'Update' then DETAILS.DATECHANGED else DETAILS.AUDITDATE end as [AUDITDATE],
DETAILS.CHANGEDBYUSER,
DETAILS.PROCESS,
DETAILS.FIELD,
DETAILS.OLD,
DETAILS.NEW,
160 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from @REVENUESPLITSTIDS as REVENUESPLIT
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('NAMINGOPPORTUNITYRECOGNITIONID', 'NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT', 'REVENUESPLITID', REVENUESPLIT.ID, '01/01/1900', 'Named recognition', '') DETAILS
--Campaigns
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 15
insert into @RESULTS
select
DETAILS.RECORDID,
case when DETAILS.ACTION = 'Insert' then DETAILS.DATEADDED when DETAILS.ACTION = 'Update' then DETAILS.DATECHANGED else DETAILS.AUDITDATE end as [AUDITDATE],
DETAILS.CHANGEDBYUSER,
DETAILS.PROCESS,
DETAILS.FIELD,
DETAILS.OLD,
DETAILS.NEW,
140 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from @REVENUESPLITSTIDS as REVENUESPLIT
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('CAMPAIGNID, CAMPAIGNSUBPRIORITYID', 'REVENUESPLITCAMPAIGN', 'REVENUESPLITID', REVENUESPLIT.ID, @REVENUEDATEADDED, 'Campaigns', '') DETAILS
--Business Units
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 16
insert into @RESULTS
select
DETAILS.RECORDID,
case when DETAILS.ACTION = 'Insert' then DETAILS.DATEADDED when DETAILS.ACTION = 'Update' then DETAILS.DATECHANGED else DETAILS.AUDITDATE end as [AUDITDATE],
DETAILS.CHANGEDBYUSER,
DETAILS.PROCESS,
DETAILS.FIELD,
DETAILS.OLD,
DETAILS.NEW,
150 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from @REVENUESPLITSTIDS as REVENUESPLIT
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('BUSINESSUNITCODEID, AMOUNT', 'REVENUESPLITBUSINESSUNIT', 'REVENUESPLITID', REVENUESPLIT.ID, @REVENUEDATEADDED, 'Business Unit', '') DETAILS
--Tributes
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 17
insert into @RESULTS
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
210 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('TRIBUTEID, AMOUNT','REVENUETRIBUTE','REVENUEID',@REVENUEID,null,'Tribute','')
--Gift Aid/VAT
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 18
begin
insert into @RESULTS
select
DETAILS.RECORDID,
case when DETAILS.ACTION = 'Insert' then DETAILS.DATEADDED when DETAILS.ACTION = 'Update' then DETAILS.DATECHANGED else DETAILS.AUDITDATE end as [AUDITDATE],
DETAILS.CHANGEDBYUSER,
DETAILS.PROCESS,
DETAILS.FIELD,
DETAILS.OLD,
DETAILS.NEW,
220 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from @REVENUESPLITSTIDS as REVENUESPLIT
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('DECLINESGIFTAID, TAXCLAIMNUMBER, BASETAXCLAIMAMOUNT, TRANSITIONALTAXCLAIMAMOUNT, ISCOVENANT, ISSPONSORSHIP, CHARITYCLAIMREFERENCENUMBER', 'REVENUESPLITGIFTAID', 'ID', REVENUESPLIT.ID, @REVENUEDATEADDED, 'Gift Aid', '') DETAILS
insert into @RESULTS
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
230 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('AMOUNTTOTAX, VATTAXRATEID, VATAMOUNT','REVENUEVAT', 'ID', @REVENUEID, @REVENUEDATEADDED, 'VAT', '')
end
--Revenue schedule credit card
if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 6
begin
with REVENUESCHEDULECREDITCARDS_CTE as
(
select distinct CREDITCARDID
from
(select CREDITCARDID from dbo.REVENUESCHEDULE where ID = @REVENUEID
union all
select CREDITCARDID from dbo.REVENUESCHEDULEAUDIT where AUDITRECORDID = @REVENUEID
) a
where CREDITCARDID is not null
)
insert into @RESULTS
select
DETAILS.RECORDID,
case when DETAILS.ACTION = 'Insert' then DETAILS.DATEADDED when DETAILS.ACTION = 'Update' then DETAILS.DATECHANGED else DETAILS.AUDITDATE end as [AUDITDATE],
DETAILS.CHANGEDBYUSER,
DETAILS.PROCESS,
DETAILS.FIELD,
DETAILS.OLD,
DETAILS.NEW,
60 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from REVENUESCHEDULECREDITCARDS_CTE as CREDITCARD
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('CARDHOLDERNAME, CREDITTYPECODEID, CREDITCARDPARTIALNUMBER, EXPIRESON', 'CREDITCARD', 'ID', CREDITCARD.CREDITCARDID, @REVENUEDATEADDED, 'Credit card', '') DETAILS
end
if @ACTIONTYPECODE = 1
delete from @RESULTS where ACTION = 'Delete' or ACTION = 'Update';
if @ACTIONTYPECODE = 2
delete from @RESULTS where ACTION = 'Insert' or ACTION = 'Delete';
if @ACTIONTYPECODE = 3
delete from @RESULTS where ACTION = 'Insert' or ACTION = 'Update';
return;
end