UFN_CONSTITUENTHISTORY_CONSTITUENTCHILDTABLES_AUDIT
Returns the audit information for the child tables for a given constituent record.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @CONSTITUENTDATEADDED | datetime | IN | |
| @ACTIONTYPECODE | tinyint | IN |
Definition
Copy
CREATE function dbo.UFN_CONSTITUENTHISTORY_CONSTITUENTCHILDTABLES_AUDIT
(
@CONSTITUENTID uniqueidentifier,
@CONSTITUENTDATEADDED datetime,
@ACTIONTYPECODE 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
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('PHONETYPECODEID,NUMBER,ISPRIMARY', 'PHONE', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Phone', '')
union all
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('EMAILADDRESSTYPECODEID,EMAILADDRESS,ISPRIMARY', 'EMAILADDRESS', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Email address', '')
union all
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('ADDRESSTYPECODEID,COUNTRYID,STATEID,ADDRESSBLOCK,CITY,POSTCODE,CART,DPC,LOT,ISPRIMARY,DONOTMAIL,STARTDATE,ENDDATE', 'ADDRESS', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Address', '')
union all
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('CONSTITUENTINTERESTCODEID,NOTE', 'CONSTITUENTINTEREST', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Interest', '')
union all
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('MATCHINGGIFTCONDITIONTYPECODEID,MATCHINGFACTOR,MAXMATCHANNUAL,MAXMATCHPERGIFT,MAXMATCHTOTAL,MINMATCHPERGIFT,NOTES', 'MATCHINGGIFTCONDITION', 'ORGANIZATIONID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Matching gift condition', '')
union all
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('CONSTITUENCYCODEID,DATEFROM,DATETO', 'CONSTITUENCY', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'User defined constituency', '')
union all
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('DATEFROM,DATETO', 'BOARDMEMBERDATERANGE', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Board member constituency', '')
union all
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('DATEFROM,DATETO', 'FUNDRAISERDATERANGE', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Fundraiser constituency', '')
union all
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('DATEFROM,DATETO', 'PROSPECTDATERANGE', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Prospect constituency', '')
union all
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('DATEFROM,DATETO', 'STAFFDATERANGE', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Staff constituency', '')
union all
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('DATEFROM,DATETO', 'VOLUNTEERDATERANGE', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Volunteer constituency', '')
union all
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
100 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('EDUCATIONALINSTITUTIONID,ISPRIMARYRECORD,KNOWNNAME,EDUCATIONALHISTORYTYPECODEID,CONSTITUENCYSTATUSCODE,EDUCATIONALDEGREECODEID,STARTDATE,ENDDATE,GPA,CLASSOF,FRATERNITY', 'EDUCATIONALHISTORY', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Educational history', '')
union all
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
110 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('DATEENTERED,TITLE,AUTHORID,TEXTNOTE,CONSTITUENTNOTETYPECODEID', 'CONSTITUENTNOTE', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Note', '')
union all
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
120 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('DATEENTERED,TITLE,AUTHORID,CONSTITUENTMEDIALINKTYPECODEID,MEDIAURL', 'CONSTITUENTMEDIALINK', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Media link', '')
union all
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
130 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('DATEENTERED,TITLE,AUTHORID,CONSTITUENTATTACHMENTTYPECODEID,FILENAME', 'CONSTITUENTATTACHMENT', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Attachment', '')
union all
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
140 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('ALIASTYPECODEID,KEYNAME,FIRSTNAME', 'ALIAS', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Alias', '')
union all
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
150 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('NAMEFORMATTYPECODEID,NAMEFORMATFUNCTIONID,CUSTOMNAME', 'NAMEFORMAT', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Name format', '')
union all
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
160 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('RECIPROCALCONSTITUENTID,RELATIONSHIPTYPECODEID,RECIPROCALTYPECODEID,STARTDATE,ENDDATE,ISSPOUSE,ISPRIMARYBUSINESS,ISCONTACT,CONTACTTYPECODEID,POSITION,ISMATCHINGGIFTRELATIONSHIP', 'RELATIONSHIP', 'RELATIONSHIPCONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Relationship', '')
union all
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
case when FIELD = 'Financial account:ACCOUNTNUMBER' then '**********' else OLD end,
case when FIELD = 'Financial account:ACCOUNTNUMBER' then '**********' else NEW end,
170 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('FINANCIALINSTITUTIONID,ACCOUNTNUMBER,ACCOUNTTYPECODE,EFTSTATUSCODE', 'CONSTITUENTACCOUNT', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Financial account', '')
union all
select
RECORDID,
case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
180 as [SEQUENCE],
ACTION,
CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('CONSTIT_SECURITY_ATTRIBUTEID', 'CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT', 'CONSTITUENTID', @CONSTITUENTID, @CONSTITUENTDATEADDED, 'Constituent group', '')
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