UFN_REVENUEHISTORY_PAYMENTMETHOD_AUDIT2
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @REVENUEDATEADDED | datetime | IN | |
| @ACTIONTYPECODE | tinyint | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN |
Definition
Copy
create function [dbo].[UFN_REVENUEHISTORY_PAYMENTMETHOD_AUDIT2]
(
@REVENUEID uniqueidentifier,
@REVENUEDATEADDED datetime,
@ACTIONTYPECODE tinyint,
@STARTDATE datetime,
@ENDDATE datetime
)
returns @RESULT 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 @RESULT(
RECORDID,
AUDITDATE,
CHANGEDBYUSER,
PROCESS,
FIELD,
OLD,
NEW,
SEQUENCE,
ACTION,
CHANGEDBYAPP)
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,
10 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('PAYMENTMETHODCODE', 'REVENUEPAYMENTMETHOD', 'REVENUEID', @REVENUEID, @REVENUEDATEADDED, 'Payment Method', '', @STARTDATE, @ENDDATE) 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,
10 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from dbo.REVENUEPAYMENTMETHOD
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('REFERENCEDATE, REFERENCENUMBER', 'CASHPAYMENTMETHODDETAIL', 'ID', REVENUEPAYMENTMETHOD.ID, @REVENUEDATEADDED, 'Cash details', '', @STARTDATE, @ENDDATE) DETAILS
where REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID
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,
10 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from dbo.REVENUEPAYMENTMETHOD
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('CHECKDATE, CHECKNUMBER, CONSTITUENTACCOUNTID', 'CHECKPAYMENTMETHODDETAIL', 'ID', REVENUEPAYMENTMETHOD.ID, @REVENUEDATEADDED, 'Check details', '', @STARTDATE, @ENDDATE) DETAILS
where REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID and (FIELD <> 'CHECKDATE' or NEW <> '00000000')
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,
10 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from dbo.REVENUEPAYMENTMETHOD
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('CARDHOLDERNAME, CREDITTYPECODEID, CREDITCARDPARTIALNUMBER, EXPIRESON, AUTHORIZATIONCODE', 'CREDITCARDPAYMENTMETHODDETAIL', 'ID', REVENUEPAYMENTMETHOD.ID, @REVENUEDATEADDED, 'Credit card details', '', @STARTDATE, @ENDDATE) DETAILS
where REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID and (FIELD <> 'EXPIRESON' or NEW <> '00000000')
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,
10 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from dbo.REVENUEPAYMENTMETHOD
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('REFERENCEDATE, REFERENCENUMBER, CONSTITUENTACCOUNTID, DIRECTDEBITRESULTCODE', 'DIRECTDEBITPAYMENTMETHODDETAIL', 'ID', REVENUEPAYMENTMETHOD.ID, @REVENUEDATEADDED, 'Direct debit details', '', @STARTDATE, @ENDDATE) DETAILS
where REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID and (FIELD <> 'REFERENCEDATE' or NEW <> '00000000')
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,
10 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from dbo.REVENUEPAYMENTMETHOD
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('ISSUER, MEDIANPRICE, NUMBEROFUNITS, SYMBOL', 'STOCKDETAIL', 'ID', REVENUEPAYMENTMETHOD.ID, @REVENUEDATEADDED, 'Stock details', '', @STARTDATE, @ENDDATE) DETAILS
where REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID and ((FIELD <> 'SALEDATE' and FIELD <> 'SALEPOSTDATE') or NEW <> '00000000')
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,
10 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from dbo.REVENUEPAYMENTMETHOD
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('PROPERTYSUBTYPECODEID, SALEDATE, SALEAMOUNT, BROKERFEE, SALEPOSTDATE, SALEPOSTSTATUSCODE', 'PROPERTYDETAIL', 'ID', REVENUEPAYMENTMETHOD.ID, @REVENUEDATEADDED, 'Property details', '', @STARTDATE, @ENDDATE) DETAILS
where REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID and ((FIELD <> 'SALEDATE' and FIELD <> 'SALEPOSTDATE') or NEW <> '00000000')
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,
10 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from dbo.REVENUEPAYMENTMETHOD
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('GIFTINKINDSUBTYPECODEID, ITEMNAME, DISPOSITIONCODE, NUMBEROFUNITS, FAIRMARKETVALUE', 'GIFTINKINDPAYMENTMETHODDETAIL', 'ID', REVENUEPAYMENTMETHOD.ID, @REVENUEDATEADDED, 'Gift-in-kind details', '', @STARTDATE, @ENDDATE) DETAILS
where REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID
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,
10 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from dbo.REVENUEPAYMENTMETHOD
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('REFERENCEDATE, REFERENCENUMBER, CONSTITUENTACCOUNTID', 'STANDINGORDERPAYMENTMETHODDETAIL', 'ID', REVENUEPAYMENTMETHOD.ID, @REVENUEDATEADDED, 'Standing order details', '', @STARTDATE, @ENDDATE) DETAILS
where REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID and (FIELD <> 'REFERENCEDATE' or NEW <> '00000000')
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,
10 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from dbo.REVENUEPAYMENTMETHOD
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('SALEDATE, SALEAMOUNT, FEE, NUMBEROFUNITS, LOWPRICE, MEDIANPRICE, HIGHPRICE, SALEPOSTDATE, SALEPOSTSTATUSCODE', 'STOCKSALE', 'STOCKDETAILID', REVENUEPAYMENTMETHOD.ID, @REVENUEDATEADDED, 'Stock sale details', '', @STARTDATE, @ENDDATE) DETAILS
where REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID and ((FIELD <> 'SALEDATE' and FIELD <> 'SALEPOSTDATE') or NEW <> '00000000')
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,
10 as [SEQUENCE],
DETAILS.ACTION,
DETAILS.CHANGEDBYAPP
from dbo.REVENUEPAYMENTMETHOD
cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('DATE, AMOUNT, APPRAISERID, DESCRIPTION', 'GIFTINKINDAPPRAISAL', 'GIFTINKINDPAYMENTMETHODDETAILID', REVENUEPAYMENTMETHOD.ID, @REVENUEDATEADDED, 'Appraisal', '', @STARTDATE, @ENDDATE) DETAILS
where REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID and (FIELD <> 'DATE' or NEW <> '00000000')
if @ACTIONTYPECODE = 1
delete from @RESULT where ACTION = 'Delete' or ACTION = 'Update';
if @ACTIONTYPECODE = 2
delete from @RESULT where ACTION = 'Insert' or ACTION = 'Delete';
if @ACTIONTYPECODE = 3
delete from @RESULT where ACTION = 'Insert' or ACTION = 'Update';
return;
end