USP_REVENUEUPDATEBATCH_VALIDATIONREPORT
Provide data for the revenue update batch validation report.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @BATCHID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_REVENUEUPDATEBATCH_VALIDATIONREPORT
(
@BATCHID uniqueidentifier
)
as
set nocount on;
set transaction isolation level read uncommitted;
declare @NULLGUID uniqueidentifier;
set @NULLGUID = newid();
declare @NULLDATE datetime;
set @NULLDATE = '1/1/1753';
declare @DATEFORMAT tinyint;
set @DATEFORMAT = 101;
declare @RESULTS table (BATCHRECORDSEQUENCE int,
FIELDRECORDTYPE nvarchar(200),
FIELDRECORDTYPEDATE datetime,
FIELDRECORDID uniqueidentifier,
--Longest value is generated by Recognition CTE
--DESIGNATION.NAME + ' - ' + CONSTITUENT.NAME
--512 + 3 + 154 = 669
FIELDRECORDKEY nvarchar(670),
FIELDRECORDKEYDATE datetime,
FIELDRECORDCODE tinyint, -- 0=edit, 1=added, 2=deleted
FIELDNAME nvarchar(50),
BEFOREVALUE nvarchar(max),
AFTERVALUE nvarchar(max),
FIELDSEQUENCE smallint,
DATATYPE nvarchar(20),
ISO4217 nvarchar(3),
CURRENCYSYMBOL nvarchar(5),
DECIMALDIGITS tinyint,
SYMBOLDISPLAYSETTINGCODE tinyint
);
with CTE as (
select BR.SEQUENCE,
C.NAME CONSTITUENTNAME,
BR.TYPE,
ARC.CODE ADJUSTMENTREASONCODE,
-- field differences
BR.CONSTITUENTID BCONSTITUENTID,
R.CONSTITUENTID RCONSTITUENTID,
BR.DATE BDATE,
R.DATE RDATE,
BR.REVENUELOOKUPID BLOOKUPID,
R.LOOKUPID RLOOKUPID,
BR.AMOUNT BAMOUNT,
R.TRANSACTIONAMOUNT RAMOUNT,
BR.PAYMENTMETHODCODE BPAYMENTMETHODCODE,
RPM.PAYMENTMETHODCODE RPAYMENTMETHODCODE,
BR.PAYMENTMETHOD BPAYMENTMETHOD,
RPM.PAYMENTMETHOD RPAYMENTMETHOD,
BR.DONOTACKNOWLEDGE BDONOTACKNOWLEDGE,
R.DONOTACKNOWLEDGE RDONOTACKNOWLEDGE,
-- receipt fields
BR.RECEIPTAMOUNT BRECEIPTAMOUNT,
R.RECEIPTAMOUNT RRECEIPTAMOUNT,
case when BR.TYPECODE <> 3 then BR.DONOTRECEIPT end BDONOTRECEIPT,
R.DONOTRECEIPT RDONOTRECEIPT,
BR.RECEIPTTYPE BRECEIPTTYPE,
R.RECEIPTTYPE RRECEIPTTYPE,
-- installment fields
BR.INSTALLMENTSTARTDATE BINSTALLMENTSTARTDATE,
RS.STARTDATE RINSTALLMENTSTARTDATE,
BR.INSTALLMENTENDDATE BINSTALLMENTENDDATE,
RS.ENDDATE RINSTALLMENTENDDATE,
BR.NUMBEROFINSTALLMENTS BNUMBEROFINSTALLMENTS,
RS.NUMBEROFINSTALLMENTS RNUMBEROFINSTALLMENTS,
BR.INSTALLMENTFREQUENCY BINSTALLMENTFREQUENCY,
RS.FREQUENCY RINSTALLMENTFREQUENCY,
-- DM fields
BR.FINDERNUMBER BFINDERNUMBER,
R.FINDERNUMBER RFINDERNUMBER,
BR.SOURCECODE BSOURCECODE,
R.SOURCECODE RSOURCECODE,
BR.APPEALID BAPPEALID,
R.APPEALID RAPPEALID,
BR.MAILINGID BMAILINGID,
R.MAILINGID RMAILINGID,
BR.CHANNELCODEID BCHANNELCODEID,
R.CHANNELCODEID RCHANNELCODEID,
-- misc fields
BR.BENEFITSWAIVED BBENEFITSWAIVED,
R.BENEFITSWAIVED RBENEFITSWAIVED,
case when BR.TYPECODE <> 3 then BR.POSTDATE end BPOSTDATE,
R.POSTDATE RPOSTDATE,
BR.POSTSTATUS BPOSTSTATUS,
case R.DONOTPOST when 0 then 'Not posted' when 1 then 'Do not post' end RPOSTSTATUS,
BR.SENDPLEDGEREMINDER BSENDPLEDGEREMINDER,
RS.SENDPLEDGEREMINDER RSENDPLEDGEREMINDER,
BR.GIVENANONYMOUSLY BGIVENANONYMOUSLY,
R.GIVENANONYMOUSLY RGIVENANONYMOUSLY,
BR.PLEDGESUBTYPEID BPLEDGESUBTYPEID,
RS.PLEDGESUBTYPEID RPLEDGESUBTYPEID,
-- lockbox fields
BR.LOCKBOXID BLOCKBOXID,
RL.LOCKBOXID RLOCKBOXID,
BR.LOCKBOXBATCHNUMBER BLOCKBOXBATCHNUMBER,
RL.BATCHNUMBER RLOCKBOXBATCHNUMBER,
BR.LOCKBOXBATCHSEQUENCE BLOCKBOXBATCHSEQUENCE,
RL.BATCHSEQUENCE RLOCKBOXBATCHSEQUENCE,
-- payment method fields used across multiple payment methods
case when BR.PAYMENTMETHODCODE in(0,3,10,11) then BR.REFERENCEDATE end BREFERENCEDATE,
case RPM.PAYMENTMETHODCODE
when 0 then CAPM.REFERENCEDATE
when 3 then DDPM.REFERENCEDATE
when 10 then OPM.REFERENCEDATE
when 11 then SOPM.REFERENCEDATE
end RREFERENCEDATE,
case when BR.PAYMENTMETHODCODE in(0,3,10,11) then BR.REFERENCENUMBER end BREFERENCENUMBER,
case RPM.PAYMENTMETHODCODE
when 0 then CAPM.REFERENCENUMBER
when 3 then DDPM.REFERENCENUMBER
when 10 then OPM.REFERENCENUMBER
when 11 then SOPM.REFERENCENUMBER
end RREFERENCENUMBER,
case when BR.PAYMENTMETHODCODE in(1,3,11) then BR.CONSTITUENTACCOUNTID end BCONSTITUENTACCOUNTID,
case RPM.PAYMENTMETHODCODE
when 1 then CHPM.CONSTITUENTACCOUNTID
when 3 then DDPM.CONSTITUENTACCOUNTID
when 11 then SOPM.CONSTITUENTACCOUNTID
end RCONSTITUENTACCOUNTID,
-- check payment method fields
case BR.PAYMENTMETHODCODE when 1 then BR.CHECKDATE end BCHECKDATE,
CHPM.CHECKDATE RCHECKDATE,
case BR.PAYMENTMETHODCODE when 1 then BR.CHECKNUMBER end BCHECKNUMBER,
CHPM.CHECKNUMBER RCHECKNUMBER,
-- credit card payment method fields
BCC.CARDHOLDERNAME BCARDHOLDERNAME,
case R.TRANSACTIONTYPECODE when 0 then CRPM.CARDHOLDERNAME else RCC.CARDHOLDERNAME end RCARDHOLDERNAME,
BCC.CREDITTYPECODEID BCREDITTYPECODEID,
case R.TRANSACTIONTYPECODE when 0 then CRPM.CREDITTYPECODEID else RCC.CREDITTYPECODEID end RCREDITTYPECODEID,
BCC.CREDITCARDPARTIALNUMBER BCREDITCARDPARTIALNUMBER,
case R.TRANSACTIONTYPECODE when 0 then CRPM.CREDITCARDPARTIALNUMBER else RCC.CREDITCARDPARTIALNUMBER end RCREDITCARDPARTIALNUMBER,
BCC.EXPIRESON BEXPIRESON,
case R.TRANSACTIONTYPECODE when 0 then CRPM.EXPIRESON else RCC.EXPIRESON end REXPIRESON,
BR.AUTHORIZATIONCODE BAUTHORIZATIONCODE,
CRPM.AUTHORIZATIONCODE RAUTHORIZATIONCODE,
-- direct debit payment method fields
case BR.PAYMENTMETHODCODE when 3 then BR.DIRECTDEBITRESULTCODE end BDIRECTDEBITRESULTCODE,
DDPM.DIRECTDEBITRESULTCODE RDIRECTDEBITRESULTCODE,
case BR.PAYMENTMETHODCODE when 3 then BR.DIRECTDEBITISREJECTED end BDIRECTDEBITISREJECTED,
DDPM.ISREJECTED RDIRECTDEBITISREJECTED,
-- stock payment method fields
case BR.PAYMENTMETHODCODE when 4 then BR.ISSUER end BISSUER,
SD.ISSUER RISSUER,
case BR.PAYMENTMETHODCODE when 4 then BR.MEDIANPRICE end BMEDIANPRICE,
SD.MEDIANPRICE RMEDIANPRICE,
case BR.PAYMENTMETHODCODE when 4 then BR.NUMBEROFUNITS end BNUMBEROFUNITS,
SD.NUMBEROFUNITS RNUMBEROFUNITS,
case BR.PAYMENTMETHODCODE when 4 then BR.SYMBOL end BSYMBOL,
SD.SYMBOL RSYMBOL,
case BR.PAYMENTMETHODCODE when 4 then BR.LOWPRICE end BLOWPRICE,
SD.LOWPRICE RLOWPRICE,
case BR.PAYMENTMETHODCODE when 4 then BR.HIGHPRICE end BHIGHPRICE,
SD.HIGHPRICE RHIGHPRICE,
-- property payment method fields
case BR.PAYMENTMETHODCODE when 5 then BR.PROPERTYSUBTYPECODEID end BPROPERTYSUBTYPECODEID,
PD.PROPERTYSUBTYPECODEID RPROPERTYSUBTYPECODEID,
case BR.PAYMENTMETHODCODE when 5 then BR.SALEDATE end BSALEDATE,
PD.SALEDATE RSALEDATE,
case BR.PAYMENTMETHODCODE when 5 then BR.SALEAMOUNT end BSALEAMOUNT,
PD.TRANSACTIONSALEAMOUNT RSALEAMOUNT,
case BR.PAYMENTMETHODCODE when 5 then BR.BROKERFEE end BBROKERFEE,
PD.BROKERFEE RBROKERFEE,
case BR.PAYMENTMETHODCODE when 5 then BR.SALEPOSTDATE end BSALEPOSTDATE,
PD.SALEPOSTDATE RSALEPOSTDATE,
case BR.PAYMENTMETHODCODE when 5 then BR.SALEPOSTSTATUS end BSALEPOSTSTATUS,
PD.SALEPOSTSTATUS RSALEPOSTSTATUS,
-- gift-in-kind payment method fields
case BR.PAYMENTMETHODCODE when 6 then BR.GIFTINKINDSUBTYPECODEID end BGIFTINKINDSUBTYPECODEID,
GIKPM.GIFTINKINDSUBTYPECODEID RGIFTINKINDSUBTYPECODEID,
case BR.PAYMENTMETHODCODE when 6 then BR.GIFTINKINDITEMNAME end BGIFTINKINDITEMNAME,
GIKPM.ITEMNAME RGIFTINKINDITEMNAME,
case BR.PAYMENTMETHODCODE when 6 then BR.GIFTINKINDDISPOSITION end BGIFTINKINDDISPOSITION,
GIKPM.DISPOSITION RGIFTINKINDDISPOSITION,
case BR.PAYMENTMETHODCODE when 6 then BR.GIFTINKINDNUMBEROFUNITS end BGIFTINKINDNUMBEROFUNITS,
GIKPM.NUMBEROFUNITS RGIFTINKINDNUMBEROFUNITS,
case BR.PAYMENTMETHODCODE when 6 then BR.GIFTINKINDFAIRMARKETVALUE end BGIFTINKINDFAIRMARKETVALUE,
GIKPM.FAIRMARKETVALUE RGIFTINKINDFAIRMARKETVALUE,
-- other payment method fields
case BR.PAYMENTMETHODCODE when 10 then BR.OTHERPAYMENTMETHODCODEID end BOTHERPAYMENTMETHODCODEID,
OPM.OTHERPAYMENTMETHODCODEID ROTHERPAYMENTMETHODCODEID,
BR.EXCHANGERATE BEXCHANGERATE,
REVENUEBASERATE.RATE REXCHANGERATE,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.REVENUE R on R.ID = BR.REVENUEID
inner join dbo.CONSTITUENT C on C.ID = BR.CONSTITUENTID
inner join dbo.CURRENCY on CURRENCY.ID = BR.TRANSACTIONCURRENCYID
left join dbo.ADJUSTMENTREASONCODE ARC on ARC.ID = BR.ADJUSTMENTREASONCODEID
left join dbo.REVENUEPAYMENTMETHOD RPM on RPM.REVENUEID = R.ID
left join dbo.REVENUESCHEDULE RS on RS.ID = R.ID
left join dbo.REVENUELOCKBOX RL on RL.ID = R.ID
left join dbo.CASHPAYMENTMETHODDETAIL CAPM on CAPM.ID = RPM.ID and RPM.PAYMENTMETHODCODE = 0
left join dbo.CHECKPAYMENTMETHODDETAIL CHPM on CHPM.ID = RPM.ID and RPM.PAYMENTMETHODCODE = 1
left join dbo.CREDITCARDPAYMENTMETHODDETAIL CRPM on CRPM.ID = RPM.ID and RPM.PAYMENTMETHODCODE = 2 and R.TRANSACTIONTYPECODE = 0
left join dbo.CREDITCARD BCC on BCC.ID = BR.CREDITCARDID
left join dbo.CREDITCARD RCC on RCC.ID = RS.CREDITCARDID and R.TRANSACTIONTYPECODE <> 0
left join dbo.DIRECTDEBITPAYMENTMETHODDETAIL DDPM on DDPM.ID = RPM.ID and RPM.PAYMENTMETHODCODE = 3
left join dbo.STOCKDETAIL SD on SD.ID = RPM.ID and RPM.PAYMENTMETHODCODE = 4
left join dbo.PROPERTYDETAIL PD on PD.ID = RPM.ID and RPM.PAYMENTMETHODCODE = 5
left join dbo.GIFTINKINDPAYMENTMETHODDETAIL GIKPM on GIKPM.ID = RPM.ID and RPM.PAYMENTMETHODCODE = 6
left join dbo.OTHERPAYMENTMETHODDETAIL OPM on OPM.ID = RPM.ID and RPM.PAYMENTMETHODCODE = 10
left join dbo.STANDINGORDERPAYMENTMETHODDETAIL SOPM on SOPM.ID = RPM.ID and RPM.PAYMENTMETHODCODE = 11
left join dbo.CURRENCYEXCHANGERATE REVENUEBASERATE on REVENUEBASERATE.ID = R.BASEEXCHANGERATEID
where BR.BATCHID = @BATCHID
)
insert into @RESULTS
select SEQUENCE, TYPE + '-' + CONSTITUENTNAME + '-<!DATE!>-' + BLOOKUPID +
case when ADJUSTMENTREASONCODE is not null then '-Adjustment(' + ADJUSTMENTREASONCODE + ')' else '' end, BDATE,
null, null, null, 0, FIELDNAME, BEFOREVALUE, AFTERVALUE, FIELDSEQUENCE, DATATYPE, ISO4217, CURRENCYSYMBOL, DECIMALDIGITS, SYMBOLDISPLAYSETTINGCODE
from (
--
select CTE.SEQUENCE, null FIELDNAME, null BEFOREVALUE, null AFTERVALUE,
0 FIELDSEQUENCE, CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
union all
--
select CTE.SEQUENCE, 'Constituent',
RC.NAME + ' (' + RC.LOOKUPID + ')', BC.NAME + ' (' + BC.LOOKUPID + ')',
0, CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
inner join dbo.CONSTITUENT BC on BC.ID = CTE.BCONSTITUENTID
inner join dbo.CONSTITUENT RC on RC.ID = CTE.RCONSTITUENTID
where CTE.BCONSTITUENTID <> CTE.RCONSTITUENTID
union all
--
select CTE.SEQUENCE, 'Date', convert(nvarchar(100),CTE.RDATE), convert(nvarchar(100),CTE.BDATE), 1,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, 'DATE' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BDATE <> CTE.RDATE
union all
--
select CTE.SEQUENCE, 'Revenue ID', CTE.RLOOKUPID, CTE.BLOOKUPID, 2,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BLOOKUPID <> CTE.RLOOKUPID
union all
---
select CTE.SEQUENCE, 'Amount', convert(nvarchar(100), CTE.RAMOUNT, 126), convert(nvarchar(100), CTE.BAMOUNT, 126), 3,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, 'MONEY' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BAMOUNT <> CTE.RAMOUNT
union all
--
select CTE.SEQUENCE, 'Payment method', CTE.RPAYMENTMETHOD, CTE.BPAYMENTMETHOD, 4,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BPAYMENTMETHOD <> CTE.RPAYMENTMETHOD
union all
--
select CTE.SEQUENCE, 'Do not acknowledge',
case CTE.RDONOTACKNOWLEDGE when 1 then 'True' when 0 then 'False' end,
case CTE.BDONOTACKNOWLEDGE when 1 then 'True' when 0 then 'False' end, 10,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BDONOTACKNOWLEDGE <> CTE.RDONOTACKNOWLEDGE
union all
--
-- receipt fields
--
select CTE.SEQUENCE, 'Receipt amount',
convert(nvarchar(100), CTE.RRECEIPTAMOUNT, 126),convert(nvarchar(100), CTE.BRECEIPTAMOUNT, 126), 20,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, 'MONEY' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BRECEIPTAMOUNT <> CTE.RRECEIPTAMOUNT
union all
--
select CTE.SEQUENCE, 'Do not receipt',
case CTE.RDONOTRECEIPT when 1 then 'True' when 0 then 'False' end,
case CTE.BDONOTRECEIPT when 1 then 'True' when 0 then 'False' end, 21,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BDONOTRECEIPT <> CTE.RDONOTRECEIPT
union all
--
select CTE.SEQUENCE, 'Receipt type', CTE.RRECEIPTTYPE, CTE.BRECEIPTTYPE, 22,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BRECEIPTTYPE <> CTE.RRECEIPTTYPE
union all
--
-- installment fields
--
select CTE.SEQUENCE, 'Installment start date',
convert(nvarchar(100),CTE.RINSTALLMENTSTARTDATE), convert(nvarchar(100),CTE.BINSTALLMENTSTARTDATE), 30,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, 'DATE' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BINSTALLMENTSTARTDATE <> CTE.RINSTALLMENTSTARTDATE
union all
--
select CTE.SEQUENCE, 'Installment end date',
convert(nvarchar(100),CTE.RINSTALLMENTENDDATE), convert(nvarchar(100),CTE.BINSTALLMENTENDDATE), 31,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, 'DATE' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BINSTALLMENTENDDATE,@NULLDATE) <> isnull(CTE.RINSTALLMENTENDDATE,@NULLDATE)
union all
--
select CTE.SEQUENCE, 'No. installments',
cast(CTE.RNUMBEROFINSTALLMENTS as nvarchar(10)),
cast(CTE.BNUMBEROFINSTALLMENTS as nvarchar(10)), 32,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BNUMBEROFINSTALLMENTS <> CTE.RNUMBEROFINSTALLMENTS
union all
--
select CTE.SEQUENCE, 'Installment frequency', CTE.RINSTALLMENTFREQUENCY, CTE.BINSTALLMENTFREQUENCY, 33,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BINSTALLMENTFREQUENCY <> CTE.RINSTALLMENTFREQUENCY
union all
--
-- DM fields
--
select CTE.SEQUENCE, 'Finder number',
cast(CTE.RFINDERNUMBER as nvarchar(19)), cast(CTE.BFINDERNUMBER as nvarchar(19)), 40,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BFINDERNUMBER,-1) <> isnull(CTE.RFINDERNUMBER,-1)
union all
--
select CTE.SEQUENCE, 'Source code', CTE.RSOURCECODE, CTE.BSOURCECODE, 41,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BSOURCECODE <> CTE.RSOURCECODE
union all
--
select CTE.SEQUENCE, 'Appeal', RA.NAME, BA.NAME, 42,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
left join dbo.APPEAL BA on BA.ID = CTE.BAPPEALID
left join dbo.APPEAL RA on RA.ID = CTE.RAPPEALID
where isnull(CTE.BAPPEALID,@NULLGUID) <> isnull(CTE.RAPPEALID,@NULLGUID)
union all
--
select CTE.SEQUENCE, 'Effort', RM.NAME, BM.NAME, 43,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
left join dbo.MKTSEGMENTATION BM on BM.ID = CTE.BMAILINGID
left join dbo.MKTSEGMENTATION RM on RM.ID = CTE.RMAILINGID
where isnull(CTE.BMAILINGID,@NULLGUID) <> isnull(CTE.RMAILINGID,@NULLGUID)
union all
--
select CTE.SEQUENCE, 'Inbound channel', RC.DESCRIPTION, BC.DESCRIPTION, 44,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
left join dbo.CHANNELCODE BC on BC.ID = CTE.BCHANNELCODEID
left join dbo.CHANNELCODE RC on RC.ID = CTE.RCHANNELCODEID
where isnull(CTE.BCHANNELCODEID,@NULLGUID) <> isnull(CTE.RCHANNELCODEID,@NULLGUID)
union all
--
-- misc fields
--
select CTE.SEQUENCE, 'Benefits waived',
case CTE.RBENEFITSWAIVED when 1 then 'Yes' when 0 then 'No' end,
case CTE.BBENEFITSWAIVED when 1 then 'Yes' when 0 then 'No' end, 50,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BBENEFITSWAIVED <> CTE.RBENEFITSWAIVED
union all
--
select CTE.SEQUENCE, 'GL post date',
convert(nvarchar(100),CTE.RPOSTDATE), convert(nvarchar(100),CTE.BPOSTDATE), 50,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, 'DATE' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BPOSTDATE,@NULLDATE) <> isnull(CTE.RPOSTDATE,@NULLDATE)
union all
--
select CTE.SEQUENCE, 'GL post status', CTE.RPOSTSTATUS, CTE.BPOSTSTATUS, 50,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BPOSTSTATUS <> CTE.RPOSTSTATUS
and CTE.BPOSTSTATUS <> 'None'
union all
--
select CTE.SEQUENCE, 'Send reminders',
case CTE.RSENDPLEDGEREMINDER when 1 then 'Yes' when 0 then 'No' end,
case CTE.BSENDPLEDGEREMINDER when 1 then 'Yes' when 0 then 'No' end, 50,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BSENDPLEDGEREMINDER <> CTE.RSENDPLEDGEREMINDER
union all
--
select CTE.SEQUENCE, 'Pledge is anonymous',
case CTE.RGIVENANONYMOUSLY when 1 then 'Yes' when 0 then 'No' end,
case CTE.BGIVENANONYMOUSLY when 1 then 'Yes' when 0 then 'No' end, 50,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BGIVENANONYMOUSLY <> CTE.RGIVENANONYMOUSLY
union all
--
select CTE.SEQUENCE, 'Pledge subtype', RP.NAME, BP.NAME, 50,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
left join dbo.PLEDGESUBTYPE BP on BP.ID = CTE.BPLEDGESUBTYPEID
left join dbo.PLEDGESUBTYPE RP on RP.ID = CTE.RPLEDGESUBTYPEID
where isnull(CTE.BPLEDGESUBTYPEID,@NULLGUID) <> isnull(CTE.RPLEDGESUBTYPEID,@NULLGUID)
union all
--
-- lockbox fields
--
select CTE.SEQUENCE, 'Lockbox', R.NAME, B.NAME, 60,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
left join dbo.LOCKBOX B on B.ID = CTE.BLOCKBOXID
left join dbo.LOCKBOX R on R.ID = CTE.RLOCKBOXID
where isnull(CTE.BLOCKBOXID,@NULLGUID) <> isnull(CTE.RLOCKBOXID,@NULLGUID)
union all
--
select CTE.SEQUENCE, 'Lockbox batch number', CTE.RLOCKBOXBATCHNUMBER, CTE.BLOCKBOXBATCHNUMBER, 61,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BLOCKBOXBATCHNUMBER <> isnull(CTE.RLOCKBOXBATCHNUMBER,'')
union all
--
select CTE.SEQUENCE, 'Lockbox batch sequence',
cast(CTE.RLOCKBOXBATCHSEQUENCE as nvarchar(10)),
cast(CTE.BLOCKBOXBATCHSEQUENCE as nvarchar(10)), 62,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BLOCKBOXBATCHSEQUENCE <> isnull(CTE.RLOCKBOXBATCHSEQUENCE,0)
union all
--
-- payment method fields used across multiple payment methods
--
select CTE.SEQUENCE, 'Reference date',
case when CTE.RPAYMENTMETHODCODE in(0,3,10,11) then convert(nvarchar(100),dbo.UFN_DATE_FROMFUZZYDATE(CTE.RREFERENCEDATE)) end,
case when CTE.BPAYMENTMETHODCODE in(0,3,10,11) then convert(nvarchar(100),dbo.UFN_DATE_FROMFUZZYDATE(CTE.BREFERENCEDATE)) end,
70, CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, 'DATE' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BREFERENCEDATE <> isnull(CTE.RREFERENCEDATE,'00000000')
union all
--
select CTE.SEQUENCE, 'Reference number', CTE.RREFERENCENUMBER, CTE.BREFERENCENUMBER, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BREFERENCENUMBER <> isnull(CTE.RREFERENCENUMBER,'')
union all
--
select CTE.SEQUENCE, 'Account', R.DESCRIPTION, B.DESCRIPTION, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
left join dbo.CONSTITUENTACCOUNT B on B.ID = CTE.BCONSTITUENTACCOUNTID
left join dbo.CONSTITUENTACCOUNT R on R.ID = CTE.RCONSTITUENTACCOUNTID
where isnull(CTE.BCONSTITUENTACCOUNTID,@NULLGUID) <> isnull(CTE.RCONSTITUENTACCOUNTID,@NULLGUID)
union all
--
-- check payment method fields
--
select CTE.SEQUENCE, 'Check date',
case CTE.RPAYMENTMETHODCODE when 1 then convert(nvarchar(100),dbo.UFN_DATE_FROMFUZZYDATE(CTE.RCHECKDATE)) end,
case CTE.BPAYMENTMETHODCODE when 1 then convert(nvarchar(100),dbo.UFN_DATE_FROMFUZZYDATE(CTE.BCHECKDATE)) end, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, 'DATE' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BCHECKDATE <> isnull(CTE.RCHECKDATE,'00000000')
union all
--
select CTE.SEQUENCE, 'Check number', CTE.RCHECKNUMBER, CTE.BCHECKNUMBER, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BCHECKNUMBER <> isnull(CTE.RCHECKNUMBER,'')
union all
--
-- credit card payment method fields
--
select CTE.SEQUENCE, 'Name on credit card', CTE.RCARDHOLDERNAME, CTE.BCARDHOLDERNAME, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BCARDHOLDERNAME,'') <> isnull(CTE.RCARDHOLDERNAME,'')
union all
--
select CTE.SEQUENCE, 'Credit card type', R.DESCRIPTION, B.DESCRIPTION, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
left join dbo.CREDITTYPECODE B on B.ID = CTE.BCREDITTYPECODEID
left join dbo.CREDITTYPECODE R on R.ID = CTE.RCREDITTYPECODEID
where isnull(CTE.BCREDITTYPECODEID,@NULLGUID) <> isnull(CTE.RCREDITTYPECODEID,@NULLGUID)
union all
--
select CTE.SEQUENCE, 'Credit card number', CTE.RCREDITCARDPARTIALNUMBER, CTE.BCREDITCARDPARTIALNUMBER, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BCREDITCARDPARTIALNUMBER,'') <> isnull(CTE.RCREDITCARDPARTIALNUMBER,'')
union all
--
select CTE.SEQUENCE, 'Credit card expires on',
case when CTE.RPAYMENTMETHODCODE = 2 then convert(nvarchar(100),dbo.UFN_DATE_FROMFUZZYDATE(CTE.REXPIRESON)) end,
case when CTE.BPAYMENTMETHODCODE = 2 then convert(nvarchar(100),dbo.UFN_DATE_FROMFUZZYDATE(CTE.BEXPIRESON)) end, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, 'DATE' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BEXPIRESON,'00000000') <> isnull(CTE.REXPIRESON,'00000000')
union all
--
select CTE.SEQUENCE, 'Credit card authorization code', CTE.RAUTHORIZATIONCODE, CTE.BAUTHORIZATIONCODE, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BAUTHORIZATIONCODE,'') <> isnull(CTE.RAUTHORIZATIONCODE,'')
union all
--
-- direct debit payment method fields
--
select CTE.SEQUENCE, 'Direct debit result', CTE.RDIRECTDEBITRESULTCODE, CTE.BDIRECTDEBITRESULTCODE, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BDIRECTDEBITRESULTCODE <> isnull(CTE.RDIRECTDEBITRESULTCODE,'')
union all
--
select CTE.SEQUENCE, 'Direct debit is rejected',
case CTE.RDIRECTDEBITISREJECTED when 1 then 'Yes' when 0 then 'No' end,
case CTE.BDIRECTDEBITISREJECTED when 1 then 'Yes' when 0 then 'No' end, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BDIRECTDEBITISREJECTED,-1) <> isnull(CTE.RDIRECTDEBITISREJECTED,-1)
union all
--
-- stock payment method fields
--
select CTE.SEQUENCE, 'Issuer', CTE.RISSUER, CTE.BISSUER, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BISSUER <> isnull(CTE.RISSUER,'')
union all
--
select CTE.SEQUENCE, 'Median price',
cast(CTE.RMEDIANPRICE as nvarchar(15)), cast(CTE.BMEDIANPRICE as nvarchar(15)), 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BMEDIANPRICE <> isnull(CTE.RMEDIANPRICE,0)
union all
--
select CTE.SEQUENCE, 'Stock number of units',
cast(CTE.RNUMBEROFUNITS as nvarchar(24)), cast(CTE.BNUMBEROFUNITS as nvarchar(24)), 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BNUMBEROFUNITS <> isnull(CTE.RNUMBEROFUNITS,0)
union all
--
select CTE.SEQUENCE, 'Symbol', CTE.RSYMBOL, CTE.BSYMBOL, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BSYMBOL <> isnull(CTE.RSYMBOL,'')
union all
--
select CTE.SEQUENCE, 'Low price',
cast(CTE.RLOWPRICE as nvarchar(24)), cast(CTE.BLOWPRICE as nvarchar(24)), 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BLOWPRICE <> isnull(CTE.RLOWPRICE,0)
union all
--
select CTE.SEQUENCE, 'High price',
cast(CTE.RHIGHPRICE as nvarchar(24)), cast(CTE.BHIGHPRICE as nvarchar(24)), 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BHIGHPRICE <> isnull(CTE.RHIGHPRICE,0)
union all
--
-- property payment method fields
--
select CTE.SEQUENCE, 'Property subtype', R.DESCRIPTION, B.DESCRIPTION, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
left join dbo.PROPERTYSUBTYPECODE B on B.ID = CTE.BPROPERTYSUBTYPECODEID
left join dbo.PROPERTYSUBTYPECODE R on R.ID = CTE.RPROPERTYSUBTYPECODEID
where isnull(CTE.BPROPERTYSUBTYPECODEID,@NULLGUID) <> isnull(CTE.RPROPERTYSUBTYPECODEID,@NULLGUID)
union all
--
select CTE.SEQUENCE, 'Sale date',
convert(nvarchar(100),CTE.RSALEDATE), convert(nvarchar(100),CTE.BSALEDATE), 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, 'DATE' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BSALEDATE,@NULLDATE) <> isnull(CTE.RSALEDATE,@NULLDATE)
union all
--
select CTE.SEQUENCE, 'Sale amount',
convert(nvarchar(100), CTE.RSALEAMOUNT, 126),convert(nvarchar(100), CTE.BSALEAMOUNT, 126), 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, 'MONEY' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BSALEAMOUNT <> isnull(CTE.RSALEAMOUNT,0)
union all
--
select CTE.SEQUENCE, 'Sale fees',
cast(CTE.RBROKERFEE as nvarchar(19)), cast(CTE.BBROKERFEE as nvarchar(19)), 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BBROKERFEE <> isnull(CTE.RBROKERFEE,0)
union all
--
select CTE.SEQUENCE, 'Sale GL post date',
convert(nvarchar(100),CTE.RSALEPOSTDATE), convert(nvarchar(100),CTE.BSALEPOSTDATE), 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, 'DATE' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BSALEPOSTDATE,@NULLDATE) <> isnull(CTE.RSALEPOSTDATE,@NULLDATE)
union all
--
select CTE.SEQUENCE, 'Sale GL post status', CTE.RSALEPOSTSTATUS, CTE.BSALEPOSTSTATUS, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BSALEPOSTSTATUS,'') <> isnull(CTE.RSALEPOSTSTATUS,'')
union all
--
-- gift-in-kind payment method fields
--
select CTE.SEQUENCE, 'Gift-in-kind subtype', R.DESCRIPTION, B.DESCRIPTION, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
left join dbo.GIFTINKINDSUBTYPECODE B on B.ID = CTE.BGIFTINKINDSUBTYPECODEID
left join dbo.GIFTINKINDSUBTYPECODE R on R.ID = CTE.RGIFTINKINDSUBTYPECODEID
where isnull(CTE.BGIFTINKINDSUBTYPECODEID,@NULLGUID) <> isnull(CTE.RGIFTINKINDSUBTYPECODEID,@NULLGUID)
union all
--
select CTE.SEQUENCE, 'Item name', CTE.RGIFTINKINDITEMNAME, CTE.BGIFTINKINDITEMNAME, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BGIFTINKINDITEMNAME <> isnull(CTE.RGIFTINKINDITEMNAME,'')
union all
--
select CTE.SEQUENCE, 'Disposition', CTE.RGIFTINKINDDISPOSITION, CTE.BGIFTINKINDDISPOSITION, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BGIFTINKINDDISPOSITION,'') <> isnull(CTE.RGIFTINKINDDISPOSITION,'')
union all
--
select CTE.SEQUENCE, 'Gift-in-kind number of units',
cast(CTE.RGIFTINKINDNUMBEROFUNITS as nvarchar(10)),
cast(CTE.BGIFTINKINDNUMBEROFUNITS as nvarchar(10)), 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BGIFTINKINDNUMBEROFUNITS <> isnull(CTE.RGIFTINKINDNUMBEROFUNITS,0)
union all
--
select CTE.SEQUENCE, 'Fair market value per unit',
cast(CTE.RGIFTINKINDFAIRMARKETVALUE as nvarchar(19)),
cast(CTE.BGIFTINKINDFAIRMARKETVALUE as nvarchar(19)), 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BGIFTINKINDFAIRMARKETVALUE <> isnull(CTE.RGIFTINKINDFAIRMARKETVALUE,0)
union all
--
-- other payment method fields
--
select CTE.SEQUENCE, 'Other method', R.DESCRIPTION, B.DESCRIPTION, 70,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
left join dbo.OTHERPAYMENTMETHODCODE B on B.ID = CTE.BOTHERPAYMENTMETHODCODEID
left join dbo.OTHERPAYMENTMETHODCODE R on R.ID = CTE.ROTHERPAYMENTMETHODCODEID
where isnull(CTE.BOTHERPAYMENTMETHODCODEID,@NULLGUID) <> isnull(CTE.ROTHERPAYMENTMETHODCODEID,@NULLGUID)
union all
--
select CTE.SEQUENCE, 'Exchange rate',
convert(nvarchar(100),CTE.REXCHANGERATE,126),
convert(nvarchar(100),CTE.BEXCHANGERATE,126), 4,
CTE.TYPE, CTE.CONSTITUENTNAME, CTE.BDATE, CTE.BLOOKUPID, CTE.ADJUSTMENTREASONCODE, 'DECIMAL' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BEXCHANGERATE <> isnull(CTE.REXCHANGERATE,0)
) X;
----------------------------------------------------------------------
-- Splits
with CTE as (
-- single non-application split
select BR.SEQUENCE,
BR.REVENUESPLITID FIELDRECORDID,
case BR.TYPECODE when 0 then BR.APPLICATION + ' - ' else '' end + dbo.UFN_DESIGNATION_GETNAME(BR.SINGLEDESIGNATIONID) FIELDRECORDKEY,
null FIELDRECORDKEYDATE,
case when RS.ID is not null then 0 else 1 end FIELDRECORDCODE,
0 BAMOUNT, -- ignore changes in amount to single split scenario, this will just mirror change to revenue amount
0 RAMOUNT,
null BCAMPAIGNS,
(select dbo.UDA_BUILDLIST(C.USERID+case when len(X.SUBPRIORITIES) > 0 then '('+X.SUBPRIORITIES+')' else '' end)
from (select RSC.CAMPAIGNID, dbo.UDA_BUILDLIST(CS.NAME) SUBPRIORITIES
from dbo.REVENUESPLITCAMPAIGN RSC
left join dbo.CAMPAIGNSUBPRIORITY CS on CS.ID = RSC.CAMPAIGNSUBPRIORITYID
where RSC.REVENUESPLITID = RS.ID
group by RSC.CAMPAIGNID) X
inner join dbo.CAMPAIGN C on C.ID = X.CAMPAIGNID) RCAMPAIGNS,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.CURRENCY on CURRENCY.ID = BR.TRANSACTIONCURRENCYID
left join dbo.REVENUESPLIT RS on RS.ID = BR.REVENUESPLITID and RS.DESIGNATIONID = BR.SINGLEDESIGNATIONID and RS.APPLICATIONCODE = case BR.APPLICATIONCODE when 1 then 4 when 2 then 7 when 3 then 0 else BR.APPLICATIONCODE end
where BR.BATCHID = @BATCHID
and not exists(select 'x'
from dbo.BATCHREVENUESPLIT BRS
where BRS.BATCHREVENUEID = BR.ID)
and not exists(select 'x'
from dbo.BATCHREVENUEAPPLICATION BRA
where BRA.BATCHREVENUEID = BR.ID)
and not exists(select 'x'
from dbo.BATCHREVENUEADDITIONALAPPLICATIONS BRA
where BRA.BATCHREVENUEID = BR.ID)
union all
-- added/updated multiple non-application splits
select BR.SEQUENCE,
BRS.REVENUESPLITID,
case BR.TYPECODE when 0 then BR.APPLICATION + ' - ' else '' end + dbo.UFN_DESIGNATION_GETNAME(BRS.DESIGNATIONID),
null FIELDRECORDKEYDATE,
case when RS.ID is not null then 0 else 1 end,
BRS.AMOUNT,
RS.TRANSACTIONAMOUNT,
(select dbo.UDA_BUILDLIST(C.USERID+case when len(X.SUBPRIORITIES) > 0 then '('+X.SUBPRIORITIES+')' else '' end)
from (select BRSC.CAMPAIGNID, dbo.UDA_BUILDLIST(CS.NAME) SUBPRIORITIES
from dbo.BATCHREVENUESPLITCAMPAIGN BRSC
left join dbo.CAMPAIGNSUBPRIORITY CS on CS.ID = BRSC.CAMPAIGNSUBPRIORITYID
where BRSC.BATCHREVENUESPLITID = BRS.ID
group by BRSC.CAMPAIGNID) X
inner join dbo.CAMPAIGN C on C.ID = X.CAMPAIGNID),
(select dbo.UDA_BUILDLIST(C.USERID+case when len(X.SUBPRIORITIES) > 0 then '('+X.SUBPRIORITIES+')' else '' end)
from (select RSC.CAMPAIGNID, dbo.UDA_BUILDLIST(CS.NAME) SUBPRIORITIES
from dbo.REVENUESPLITCAMPAIGN RSC
left join dbo.CAMPAIGNSUBPRIORITY CS on CS.ID = RSC.CAMPAIGNSUBPRIORITYID
where RSC.REVENUESPLITID = RS.ID
group by RSC.CAMPAIGNID) X
inner join dbo.CAMPAIGN C on C.ID = X.CAMPAIGNID),
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.CURRENCY on CURRENCY.ID = BR.TRANSACTIONCURRENCYID
inner join dbo.BATCHREVENUESPLIT BRS on BRS.BATCHREVENUEID = BR.ID
left join dbo.REVENUESPLIT RS on RS.ID = BRS.REVENUESPLITID and RS.DESIGNATIONID = BRS.DESIGNATIONID and RS.APPLICATIONCODE = BRS.APPLICATIONCODE
where BR.BATCHID = @BATCHID
and not exists(select 'x'
from dbo.BATCHREVENUEAPPLICATION BRA
where BRA.BATCHREVENUEID = BR.ID)
and not exists(select 'x'
from dbo.BATCHREVENUEADDITIONALAPPLICATIONS BRA
where BRA.BATCHREVENUEID = BR.ID)
union all
-- added/updated application splits
select BR.SEQUENCE,
BRA.REVENUESPLITID,
case BRA.APPLICATIONTYPECODE
-- existing revenue item
when 0 then (select AR.TRANSACTIONTYPE + '(' + cast(AR.AMOUNT as nvarchar(19)) + ',<!DATE!>,' + D.NAME + ')' +
case when AR.CONSTITUENTID <> BR.CONSTITUENTID then ' from ' + AC.NAME else '' end
from dbo.REVENUE AR
left join dbo.CONSTITUENT AC on AC.ID = AR.CONSTITUENTID
left join dbo.DESIGNATION D on D.ID = RS.DESIGNATIONID
where AR.ID = BRA.REVENUEID)
-- event registrant
when 1 then (select 'Event registration(' + AE.NAME + ')' +
case when AR.CONSTITUENTID <> BR.CONSTITUENTID then ' for ' + AC.NAME else '' end
from dbo.REGISTRANT AR
inner join dbo.EVENT AE on AE.ID = AR.EVENTID
left join dbo.CONSTITUENT AC on AC.ID = AR.CONSTITUENTID
where AR.ID = BRA.REGISTRANTID
union all
select 'New event registration(' + AE.NAME + ')' +
case when AR.CONSTITUENTID <> BR.CONSTITUENTID then ' for ' + isnull(AC.NAME,ANC.NAME) else '' end
from dbo.BATCHREVENUEREGISTRANT AR
inner join dbo.EVENT AE on AE.ID = AR.EVENTID
left join dbo.CONSTITUENT AC on AC.ID = AR.CONSTITUENTID
left join dbo.BATCHREVENUECONSTITUENT ANC on ANC.ID = AR.CONSTITUENTID
where AR.ID = BRA.BATCHREVENUEREGISTRANTID)
-- new pledge
when 2 then (select 'New pledge(' + cast(AR.AMOUNT as nvarchar(19)) + ',' + cast(AR.DATE as nvarchar(20)) + ')' +
case when AR.CONSTITUENTID <> BR.CONSTITUENTID then ' from ' + isnull(AC.NAME,ANC.NAME) else '' end
from dbo.BATCHREVENUEAPPLICATIONPLEDGE AR
left join dbo.CONSTITUENT AC on AC.ID = AR.CONSTITUENTID
left join dbo.BATCHREVENUECONSTITUENT ANC on ANC.ID = AR.CONSTITUENTID
where AR.ID = BRA.BATCHREVENUEAPPLICATIONPLEDGEID)
when 5 then (select 'Membership(' + AMP.NAME + ')'
from dbo.BATCHREVENUEAPPLICATIONMEMBERSHIP AR
inner join dbo.MEMBERSHIPPROGRAM AMP on AMP.ID = AR.MEMBERSHIPPROGRAMID
where AR.ID = BRA.BATCHREVENUEAPPLICATIONMEMBERSHIPID)
end,
REVENUE.DATE FIELDRECORDKEYDATE,
case when RS.ID is not null then 0 else 1 end,
BRA.APPLIED,
RS.TRANSACTIONAMOUNT,
null,
null,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.CURRENCY on CURRENCY.ID = BR.TRANSACTIONCURRENCYID
inner join dbo.BATCHREVENUEAPPLICATION BRA on BRA.BATCHREVENUEID = BR.ID
left join dbo.REVENUESPLIT RS on RS.ID = BRA.REVENUESPLITID
left join dbo.REVENUE on REVENUE.ID = BRA.REVENUEID
where BR.BATCHID = @BATCHID
-- bug 128781
and BRA.APPLIED > 0
union all
-- added/updated additional non-application splits
select BR.SEQUENCE,
BRA.REVENUESPLITID,
case BR.TYPECODE when 0 then BRA.TYPE + ' - ' else '' end + dbo.UFN_DESIGNATION_GETNAME(BRA.DESIGNATIONID) FIELDRECORDKEY,
null FIELDRECORDKEYDATE,
case when RS.ID is not null then 0 else 1 end,
BRA.APPLIED,
RS.TRANSACTIONAMOUNT,
(select dbo.UDA_BUILDLIST(C.USERID+case when len(X.SUBPRIORITIES) > 0 then '('+X.SUBPRIORITIES+')' else '' end)
from (select BRAC.CAMPAIGNID, dbo.UDA_BUILDLIST(CS.NAME) SUBPRIORITIES
from dbo.BATCHREVENUEADDITIONALAPPLICATIONCAMPAIGN BRAC
left join dbo.CAMPAIGNSUBPRIORITY CS on CS.ID = BRAC.CAMPAIGNSUBPRIORITYID
where BRAC.BATCHREVENUEADDITIONALAPPLICATIONSID = BRA.ID
group by BRAC.CAMPAIGNID) X
inner join dbo.CAMPAIGN C on C.ID = X.CAMPAIGNID),
(select dbo.UDA_BUILDLIST(C.USERID+case when len(X.SUBPRIORITIES) > 0 then '('+X.SUBPRIORITIES+')' else '' end)
from (select RSC.CAMPAIGNID, dbo.UDA_BUILDLIST(CS.NAME) SUBPRIORITIES
from dbo.REVENUESPLITCAMPAIGN RSC
left join dbo.CAMPAIGNSUBPRIORITY CS on CS.ID = RSC.CAMPAIGNSUBPRIORITYID
where RSC.REVENUESPLITID = RS.ID
group by RSC.CAMPAIGNID) X
inner join dbo.CAMPAIGN C on C.ID = X.CAMPAIGNID),
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.CURRENCY on CURRENCY.ID = BR.TRANSACTIONCURRENCYID
inner join dbo.BATCHREVENUEADDITIONALAPPLICATIONS BRA on BRA.BATCHREVENUEID = BR.ID
left join dbo.REVENUESPLIT RS on RS.ID = BRA.REVENUESPLITID and RS.DESIGNATIONID = BRA.DESIGNATIONID and RS.APPLICATIONCODE = case BRA.TYPECODE when 1 then 4 when 2 then 7 when 3 then 0 else BRA.TYPECODE end
where BR.BATCHID = @BATCHID
union all
-- deleted splits
select BR.SEQUENCE,
RS.ID,
case R.TRANSACTIONTYPECODE when 0 then RS.APPLICATION + ' - ' else '' end + dbo.UFN_DESIGNATION_GETNAME(RS.DESIGNATIONID),
null FIELDRECORDKEYDATE,
2,
null,
RS.TRANSACTIONAMOUNT,
null,
(select dbo.UDA_BUILDLIST(C.USERID+case when len(X.SUBPRIORITIES) > 0 then '('+X.SUBPRIORITIES+')' else '' end)
from (select RSC.CAMPAIGNID, dbo.UDA_BUILDLIST(CS.NAME) SUBPRIORITIES
from dbo.REVENUESPLITCAMPAIGN RSC
left join dbo.CAMPAIGNSUBPRIORITY CS on CS.ID = RSC.CAMPAIGNSUBPRIORITYID
where RSC.REVENUESPLITID = RS.ID
group by RSC.CAMPAIGNID) X
inner join dbo.CAMPAIGN C on C.ID = X.CAMPAIGNID),
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.CURRENCY on CURRENCY.ID = BR.TRANSACTIONCURRENCYID
inner join dbo.REVENUE R on R.ID = BR.REVENUEID
inner join dbo.REVENUESPLIT RS on RS.REVENUEID = R.ID
where BR.BATCHID = @BATCHID
and (((BR.REVENUESPLITID is null or
RS.ID <> BR.REVENUESPLITID or
RS.DESIGNATIONID <> BR.SINGLEDESIGNATIONID or
RS.APPLICATIONCODE <> case BR.APPLICATIONCODE when 1 then 4 when 2 then 7 when 3 then 0 else BR.APPLICATIONCODE end) and
not exists(select 'x'
from dbo.BATCHREVENUESPLIT BRS
where BRS.BATCHREVENUEID = BR.ID) and
not exists(select 'x'
from dbo.BATCHREVENUEAPPLICATION BRA
where BRA.BATCHREVENUEID = BR.ID) and
not exists(select 'x'
from dbo.BATCHREVENUEADDITIONALAPPLICATIONS BRA
where BRA.BATCHREVENUEID = BR.ID)) or
(not exists(select 'x'
from dbo.BATCHREVENUESPLIT BRS
where BRS.BATCHREVENUEID = BR.ID
and BRS.REVENUESPLITID = RS.ID
and BRS.DESIGNATIONID = RS.DESIGNATIONID
and BRS.APPLICATIONCODE = RS.APPLICATIONCODE) and
not exists(select 'x'
from dbo.BATCHREVENUEAPPLICATION BRA
where BRA.BATCHREVENUEID = BR.ID
and BRA.REVENUESPLITID = RS.ID) and
not exists(select 'x'
from dbo.BATCHREVENUEADDITIONALAPPLICATIONS BRA
where BRA.BATCHREVENUEID = BR.ID
and BRA.REVENUESPLITID = RS.ID
and BRA.DESIGNATIONID = RS.DESIGNATIONID
and case BRA.TYPECODE when 1 then 4 when 2 then 7 when 3 then 0 else BRA.TYPECODE end = RS.APPLICATIONCODE)))
)
insert into @RESULTS
select CTE.SEQUENCE, 'Splits', null, CTE.FIELDRECORDID, CTE.FIELDRECORDKEY, CTE.FIELDRECORDKEYDATE, CTE.FIELDRECORDCODE,
'Amount', convert(nvarchar(100), CTE.RAMOUNT, 126),convert(nvarchar(100), CTE.BAMOUNT, 126), 200, 'MONEY' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BAMOUNT <> CTE.RAMOUNT
or CTE.BAMOUNT is null
or CTE.RAMOUNT is null
union all
--
select CTE.SEQUENCE, 'Splits', null, CTE.FIELDRECORDID, CTE.FIELDRECORDKEY, CTE.FIELDRECORDKEYDATE, CTE.FIELDRECORDCODE,
'Campaigns', CTE.RCAMPAIGNS, CTE.BCAMPAIGNS, 201, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BCAMPAIGNS,'') <> isnull(CTE.RCAMPAIGNS,'');
----------------------------------------------------------------------
-- Recognition
with CTE as (
-- added/updated recognition credits
select BR.SEQUENCE,
BRR.REVENUERECOGNITIONID FIELDRECORDID,
case when BRR.DONORCHALLENGERECOGNITIONTYPECODE in (1, 2) then case when RCD.NAME is not null then RCD.NAME + ' - ' else '' end else case when D.NAME is not null then D.NAME + ' - ' else '' end end + isnull(C.NAME,BC.NAME) FIELDRECORDKEY,
case when RR.ID is not null and BRR.CONSTITUENTID = RR.CONSTITUENTID then 0 when RC.ID is not null and BRR.CONSTITUENTID = RC.CONSTITUENTID then 0 else 1 end FIELDRECORDCODE,
BRR.AMOUNT BAMOUNT,
case when RC.ID is not null then RC.AMOUNT else RR.AMOUNT end RAMOUNT,
BRR.EFFECTIVEDATE BEFFECTIVEDATE,
case when RC.ID is not null then RC.EFFECTIVEDATE else RR.EFFECTIVEDATE end REFFECTIVEDATE,
BRR.REVENUERECOGNITIONTYPECODEID BREVENUERECOGNITIONTYPECODEID,
case when RC.ID is not null then RC.USERRECOGNITIONTYPECODEID else RR.REVENUERECOGNITIONTYPECODEID end RREVENUERECOGNITIONTYPECODEID,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.CURRENCY on CURRENCY.ID = BR.BASECURRENCYID
inner join dbo.BATCHREVENUERECOGNITION BRR on BRR.BATCHREVENUEID = BR.ID
left join dbo.CONSTITUENT C on C.ID = BRR.CONSTITUENTID
left join dbo.BATCHREVENUECONSTITUENT BC on BC.ID = BRR.CONSTITUENTID
left join dbo.DESIGNATION D on D.ID = BRR.ADDITIONALAPPLICATIONDESIGNATIONID
left join dbo.REVENUERECOGNITION RR on RR.ID = BRR.REVENUERECOGNITIONID
left join dbo.RECOGNITIONCREDIT RC on RC.ID = BRR.REVENUERECOGNITIONID
left join dbo.DESIGNATION RCD on RCD.ID = BRR.RECOGNITIONCREDITDESIGNATIONID
where BR.BATCHID = @BATCHID
union all
-- deleted recognition credits
select BR.SEQUENCE,
RR.ID,
D.NAME + ' - ' + C.NAME,
2,
null,
RR.AMOUNT,
null,
RR.EFFECTIVEDATE,
null,
RR.REVENUERECOGNITIONTYPECODEID,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.CURRENCY on CURRENCY.ID = BR.BASECURRENCYID
inner join dbo.REVENUESPLIT RS on RS.REVENUEID = BR.REVENUEID
inner join dbo.REVENUERECOGNITION RR on RR.REVENUESPLITID = RS.ID
inner join dbo.DESIGNATION D on D.ID = RS.DESIGNATIONID
inner join dbo.CONSTITUENT C on C.ID = RR.CONSTITUENTID
where BR.BATCHID = @BATCHID
and not exists(select 'x'
from dbo.BATCHREVENUERECOGNITION BRR
where BRR.BATCHREVENUEID = BR.ID
and BRR.REVENUERECOGNITIONID = RR.ID
and BRR.CONSTITUENTID = RR.CONSTITUENTID)
union all
-- deleted recognition credits matched by donor challenge
select BR.SEQUENCE,
RC.ID,
D.NAME + ' - ' + C.NAME,
2,
null,
RC.AMOUNT,
null,
RC.EFFECTIVEDATE,
null,
RC.REVENUERECOGNITIONTYPECODEID,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.CURRENCY on CURRENCY.ID = BR.BASECURRENCYID
inner join dbo.REVENUESPLIT RS on RS.REVENUEID = BR.REVENUEID
cross apply dbo.UFN_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS(RS.ID) RC
inner join dbo.DESIGNATION D on D.ID = RC.DESIGNATIONID
inner join dbo.CONSTITUENT C on C.ID = RC.CONSTITUENTID
where BR.BATCHID = @BATCHID and RC.DONORCHALLENGERECOGNITIONTYPECODE in (1, 2)
and not exists(select 'x'
from dbo.BATCHREVENUERECOGNITION BRR
where BRR.BATCHREVENUEID = BR.ID
and BRR.REVENUERECOGNITIONID = RC.ID
and BRR.CONSTITUENTID = RC.CONSTITUENTID)
)
insert into @RESULTS
select CTE.SEQUENCE, 'Recognition credits', null, CTE.FIELDRECORDID, CTE.FIELDRECORDKEY, null, CTE.FIELDRECORDCODE,
'Amount', convert(nvarchar(100), CTE.RAMOUNT, 126),convert(nvarchar(100), CTE.BAMOUNT, 126), 210, 'MONEY' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BAMOUNT,0) <> isnull(CTE.RAMOUNT,0)
union all
--
select CTE.SEQUENCE, 'Recognition credits', null, CTE.FIELDRECORDID, CTE.FIELDRECORDKEY, null, CTE.FIELDRECORDCODE,
'Effective date', convert(nvarchar(100),CTE.REFFECTIVEDATE),
convert(nvarchar(100),CTE.BEFFECTIVEDATE), 211, 'DATE' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BEFFECTIVEDATE,@NULLDATE) <> isnull(CTE.REFFECTIVEDATE,@NULLDATE)
union all
--
select CTE.SEQUENCE, 'Recognition credits', null, CTE.FIELDRECORDID, CTE.FIELDRECORDKEY, null, CTE.FIELDRECORDCODE,
'Type', R.DESCRIPTION, B.DESCRIPTION, 212, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
left join dbo.REVENUERECOGNITIONTYPECODE B on B.ID = CTE.BREVENUERECOGNITIONTYPECODEID
left join dbo.REVENUERECOGNITIONTYPECODE R on R.ID = CTE.RREVENUERECOGNITIONTYPECODEID
where isnull(CTE.BREVENUERECOGNITIONTYPECODEID,@NULLGUID) <> isnull(CTE.RREVENUERECOGNITIONTYPECODEID,@NULLGUID);
----------------------------------------------------------------------
-- Solicitors
with CTE as (
-- added/updated solicitors
select BR.SEQUENCE,
BRS.REVENUESOLICITORID FIELDRECORDID,
case when D.NAME is not null then D.NAME + ' - ' else '' end + isnull(C.NAME,BC.NAME) FIELDRECORDKEY,
case when RS.ID is not null and BRS.CONSTITUENTID = RS.CONSTITUENTID then 0 else 1 end FIELDRECORDCODE,
BRS.AMOUNT BAMOUNT,
RS.AMOUNT RAMOUNT,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.CURRENCY on CURRENCY.ID = BR.BASECURRENCYID
inner join dbo.BATCHREVENUESOLICITOR BRS on BRS.BATCHREVENUEID = BR.ID
left join dbo.CONSTITUENT C on C.ID = BRS.CONSTITUENTID
left join dbo.BATCHREVENUECONSTITUENT BC on BC.ID = BRS.CONSTITUENTID
left join dbo.DESIGNATION D on D.ID = BRS.ADDITIONALAPPLICATIONDESIGNATIONID
left join dbo.REVENUESOLICITOR RS on RS.ID = BRS.REVENUESOLICITORID
where BR.BATCHID = @BATCHID
union all
-- deleted solicitors
select BR.SEQUENCE,
RSO.ID,
D.NAME + ' - ' + C.NAME,
2,
null,
RSO.AMOUNT,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.CURRENCY on CURRENCY.ID = BR.BASECURRENCYID
inner join dbo.REVENUESPLIT RS on RS.REVENUEID = BR.REVENUEID
inner join dbo.REVENUESOLICITOR RSO on RSO.REVENUESPLITID = RS.ID
inner join dbo.DESIGNATION D on D.ID = RS.DESIGNATIONID
inner join dbo.CONSTITUENT C on C.ID = RSO.CONSTITUENTID
where BR.BATCHID = @BATCHID
and not exists(select 'x'
from dbo.BATCHREVENUESOLICITOR BRSO
where BRSO.BATCHREVENUEID = BR.ID
and BRSO.REVENUESOLICITORID = RSO.ID
and BRSO.CONSTITUENTID = RSO.CONSTITUENTID)
)
insert into @RESULTS
select CTE.SEQUENCE, 'Solicitors', null, CTE.FIELDRECORDID, CTE.FIELDRECORDKEY, null, CTE.FIELDRECORDCODE,
'Amount', convert(nvarchar(100), CTE.RAMOUNT, 126),convert(nvarchar(100), CTE.BAMOUNT, 126), 220, 'MONEY' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BAMOUNT,0) <> isnull(CTE.RAMOUNT,0);
------------------------------------------------------------------------
-- Benefits
with CTE as (
-- added/updated amount benefits
select BR.SEQUENCE,
BRB.REVENUEBENEFITID FIELDRECORDID,
B.NAME FIELDRECORDKEY,
case when RB.ID is not null and RB.BENEFITID = BRB.BENEFITID then 0 else 1 end FIELDRECORDCODE,
0 BENEFITTYPE,
BRB.QUANTITY BQUANTITY,
RB.QUANTITY RQUANTITY,
BRB.UNITVALUE BUNITVALUE,
RB.UNITVALUE RUNITVALUE,
BRB.DETAILS BDETAILS,
RB.DETAILS RDETAILS,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.BATCHREVENUEBENEFIT BRB on BRB.BATCHREVENUEID = BR.ID
inner join dbo.BENEFIT B on B.ID = BRB.BENEFITID
inner join dbo.CURRENCY on CURRENCY.ID = B.BASECURRENCYID
left join dbo.REVENUEBENEFIT RB on RB.ID = BRB.REVENUEBENEFITID
where BR.BATCHID = @BATCHID
union all
-- added/updated percent benefits
select BR.SEQUENCE,
BRB.REVENUEBENEFITID,
B.NAME,
case when RB.ID is not null and RB.BENEFITID = BRB.BENEFITID then 0 else 1 end,
1,
BRB.VALUEPERCENT,
RB.VALUEPERCENT,
BRB.PERCENTAPPLICABLEAMOUNT,
RB.PERCENTAPPLICABLEAMOUNT,
BRB.DETAILS,
RB.DETAILS,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.BATCHREVENUEBENEFITPCT BRB on BRB.BATCHREVENUEID = BR.ID
inner join dbo.BENEFIT B on B.ID = BRB.BENEFITID
inner join dbo.CURRENCY on CURRENCY.ID = B.BASECURRENCYID
left join dbo.REVENUEBENEFIT RB on RB.ID = BRB.REVENUEBENEFITID
where BR.BATCHID = @BATCHID
union all
-- deleted benefits
select BR.SEQUENCE,
RB.ID,
B.NAME,
2,
case RB.UNITVALUE when 0 then 1 else 0 end,
null,
case RB.UNITVALUE when 0 then RB.VALUEPERCENT else RB.QUANTITY end,
null,
case RB.UNITVALUE when 0 then RB.PERCENTAPPLICABLEAMOUNT else RB.UNITVALUE end,
null,
RB.DETAILS RDETAILS,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.REVENUEBENEFIT RB on RB.REVENUEID = BR.REVENUEID
inner join dbo.BENEFIT B on B.ID = RB.BENEFITID
inner join dbo.CURRENCY on CURRENCY.ID = B.BASECURRENCYID
where BR.BATCHID = @BATCHID
and not exists(select 'x'
from dbo.BATCHREVENUEBENEFIT BRB
where BRB.BATCHREVENUEID = BR.ID
and BRB.BENEFITID = RB.BENEFITID)
)
insert into @RESULTS
select CTE.SEQUENCE, 'Benefits', null, CTE.FIELDRECORDID, CTE.FIELDRECORDKEY, null, CTE.FIELDRECORDCODE,
case CTE.BENEFITTYPE when 0 then 'Quantity' else 'Percent value' end,
cast(CTE.RQUANTITY as nvarchar(10)), cast(CTE.BQUANTITY as nvarchar(10)), 230, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BQUANTITY <> isnull(CTE.RQUANTITY,0)
union all
--
select CTE.SEQUENCE, 'Benefits', null, CTE.FIELDRECORDID, CTE.FIELDRECORDKEY, null, CTE.FIELDRECORDCODE,
case CTE.BENEFITTYPE when 0 then 'Unit value' else 'Amount' end,
convert(nvarchar(100), CTE.RUNITVALUE, 126),convert(nvarchar(100), CTE.BUNITVALUE, 126), 231, 'MONEY' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BUNITVALUE <> isnull(CTE.RUNITVALUE,0)
union all
--
select CTE.SEQUENCE, 'Benefits', null, CTE.FIELDRECORDID, CTE.FIELDRECORDKEY, null, CTE.FIELDRECORDCODE,
'Details', CTE.RDETAILS, CTE.BDETAILS, 232, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where CTE.BDETAILS <> isnull(CTE.RDETAILS,'');
------------------------------------------------------------------------
-- Tributes
with CTE as (
-- added/updated tributes
select BR.SEQUENCE,
BRT.REVENUETRIBUTEID FIELDRECORDID,
C.NAME FIELDRECORDKEY,
case when RT.ID is not null and RT.TRIBUTEID = BRT.TRIBUTEID then 0 else 1 end FIELDRECORDCODE,
BRT.AMOUNT BAMOUNT,
RT.AMOUNT RAMOUNT,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.BATCHREVENUETRIBUTE BRT on BRT.BATCHREVENUEID = BR.ID
inner join dbo.TRIBUTE T on T.ID = BRT.TRIBUTEID
inner join dbo.CONSTITUENT C on C.ID = T.TRIBUTEEID
left join dbo.REVENUETRIBUTE RT on RT.ID = BRT.REVENUETRIBUTEID
left join dbo.CURRENCY on CURRENCY.ID = RT.BASECURRENCYID
where BR.BATCHID = @BATCHID
union all
-- deleted tributes
select BR.SEQUENCE,
RT.ID,
C.NAME,
2,
null,
RT.AMOUNT,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.REVENUETRIBUTE RT on RT.REVENUEID = BR.REVENUEID
inner join dbo.CURRENCY on CURRENCY.ID = RT.BASECURRENCYID
inner join dbo.TRIBUTE T on T.ID = RT.TRIBUTEID
inner join dbo.CONSTITUENT C on C.ID = T.TRIBUTEEID
where BR.BATCHID = @BATCHID
and not exists(select 'x'
from dbo.BATCHREVENUETRIBUTE BRT
where BRT.BATCHREVENUEID = BR.ID
and BRT.TRIBUTEID = RT.TRIBUTEID)
)
insert into @RESULTS
select CTE.SEQUENCE, 'Tributes', null, CTE.FIELDRECORDID, CTE.FIELDRECORDKEY, null, CTE.FIELDRECORDCODE,
'Amount', convert(nvarchar(100), CTE.RAMOUNT, 126),convert(nvarchar(100), CTE.BAMOUNT, 126), 240, 'MONEY' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BAMOUNT,0) <> isnull(CTE.RAMOUNT,0);
------------------------------------------------------------------------
-- Matching gift claims
with CTE as (
-- added/updated matching gift claims
select BR.SEQUENCE,
BRM.REVENUEMATCHINGGIFTID FIELDRECORDID,
C.NAME FIELDRECORDKEY,
case when R.ID is not null and R.CONSTITUENTID = BRM.ORGANIZATIONID then 0 else 1 end FIELDRECORDCODE,
BRM.RELATIONSHIPID BRELATIONSHIPID,
RMG.RELATIONSHIPID RRELATIONSHIPID,
BRM.MATCHINGGIFTCONDITIONID BMATCHINGGIFTCONDITIONID,
RMG.MATCHINGGIFTCONDITIONID RMATCHINGGIFTCONDITIONID,
BRM.AMOUNT BAMOUNT,
R.TRANSACTIONAMOUNT RAMOUNT,
BRM.DATE BDATE,
R.DATE RDATE,
(select dbo.UDA_BUILDLIST(D.NAME+'('+cast(BRMS.AMOUNT as nvarchar(19))+')')
from dbo.BATCHREVENUEENHANCEDMATCHINGGIFTSPLIT BRMS
inner join dbo.DESIGNATION D on D.ID = BRMS.DESIGNATIONID
where BRMS.BATCHREVENUEENHANCEDMATCHINGGIFTID = BRM.ID) BSPLITS,
(select dbo.UDA_BUILDLIST(D.NAME+'('+cast(RS.AMOUNT as nvarchar(19))+')')
from dbo.REVENUESPLIT RS
inner join dbo.DESIGNATION D on D.ID = RS.DESIGNATIONID
where RS.REVENUEID = R.ID) RSPLITS,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.CURRENCY on CURRENCY.ID = BR.BASECURRENCYID
inner join dbo.BATCHREVENUEENHANCEDMATCHINGGIFTS BRM on BRM.BATCHREVENUEID = BR.ID
inner join dbo.CONSTITUENT C on C.ID = BRM.ORGANIZATIONID
left join dbo.REVENUE R on R.ID = BRM.REVENUEMATCHINGGIFTID
left join dbo.REVENUEMATCHINGGIFT RMG on RMG.ID = R.ID and RMG.MGSOURCEREVENUEID = BR.REVENUEID
where BR.BATCHID = @BATCHID
union all
-- deleted benefits
select BR.SEQUENCE,
RMG.ID,
C.NAME,
2,
null,
RMG.RELATIONSHIPID,
null,
RMG.MATCHINGGIFTCONDITIONID,
null,
R.TRANSACTIONAMOUNT,
null,
R.DATE,
null,
(select dbo.UDA_BUILDLIST(D.NAME+'('+cast(RS.AMOUNT as nvarchar(19))+')')
from dbo.REVENUESPLIT RS
inner join dbo.DESIGNATION D on D.ID = RS.DESIGNATIONID
where RS.REVENUEID = R.ID) RSPLITS,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.BATCHREVENUE BR
inner join dbo.CURRENCY on CURRENCY.ID = BR.BASECURRENCYID
inner join dbo.REVENUEMATCHINGGIFT RMG on RMG.MGSOURCEREVENUEID = BR.REVENUEID
inner join dbo.REVENUE R on R.ID = RMG.ID
inner join dbo.CONSTITUENT C on C.ID = R.CONSTITUENTID
where BR.BATCHID = @BATCHID
and not exists(select 'x'
from dbo.BATCHREVENUEENHANCEDMATCHINGGIFTS BRM
where BRM.BATCHREVENUEID = BR.ID
and BRM.ORGANIZATIONID = R.CONSTITUENTID)
)
insert into @RESULTS
select CTE.SEQUENCE, 'Matching gift claims', null, CTE.FIELDRECORDID, CTE.FIELDRECORDKEY, null, CTE.FIELDRECORDCODE,
'Relationship', RT.DESCRIPTION, BT.DESCRIPTION, 250, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
left join dbo.RELATIONSHIP B on B.ID = CTE.BRELATIONSHIPID
left join dbo.RELATIONSHIPTYPECODE BT on BT.ID = B.RELATIONSHIPTYPECODEID
left join dbo.RELATIONSHIP R on R.ID = CTE.RRELATIONSHIPID
left join dbo.RELATIONSHIPTYPECODE RT on RT.ID = R.RELATIONSHIPTYPECODEID
where isnull(CTE.BRELATIONSHIPID,@NULLGUID) <> isnull(CTE.RRELATIONSHIPID,@NULLGUID)
union all
--
select CTE.SEQUENCE, 'Matching gift claims', null, CTE.FIELDRECORDID, CTE.FIELDRECORDKEY, null, CTE.FIELDRECORDCODE,
'MG condition type', RT.DESCRIPTION, BT.DESCRIPTION, 251, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
left join dbo.MATCHINGGIFTCONDITION B on B.ID = CTE.BMATCHINGGIFTCONDITIONID
left join dbo.MATCHINGGIFTCONDITIONTYPECODE BT on BT.ID = B.MATCHINGGIFTCONDITIONTYPECODEID
left join dbo.MATCHINGGIFTCONDITION R on R.ID = CTE.RMATCHINGGIFTCONDITIONID
left join dbo.MATCHINGGIFTCONDITIONTYPECODE RT on RT.ID = R.MATCHINGGIFTCONDITIONTYPECODEID
where isnull(CTE.BMATCHINGGIFTCONDITIONID,@NULLGUID) <> isnull(CTE.RMATCHINGGIFTCONDITIONID,@NULLGUID)
union all
--
select CTE.SEQUENCE, 'Matching gift claims', null, CTE.FIELDRECORDID, CTE.FIELDRECORDKEY, null, CTE.FIELDRECORDCODE,
'Amount', convert(nvarchar(100), CTE.RAMOUNT, 126),convert(nvarchar(100), CTE.BAMOUNT, 126), 252, 'MONEY' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BAMOUNT,0) <> isnull(CTE.RAMOUNT,0)
union all
--
select CTE.SEQUENCE, 'Matching gift claims', null, CTE.FIELDRECORDID, CTE.FIELDRECORDKEY, null, CTE.FIELDRECORDCODE,
'Date', convert(nvarchar(100),CTE.RDATE), convert(nvarchar(100),CTE.BDATE), 253, 'DATE' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BDATE,@NULLDATE) <> isnull(CTE.RDATE,@NULLDATE)
union all
--
select CTE.SEQUENCE, 'Matching gift claims', null, CTE.FIELDRECORDID, CTE.FIELDRECORDKEY, null, CTE.FIELDRECORDCODE,
'Splits', CTE.RSPLITS, CTE.BSPLITS, 254, '' DATATYPE,
CTE.ISO4217, CTE.CURRENCYSYMBOL, CTE.DECIMALDIGITS, CTE.SYMBOLDISPLAYSETTINGCODE
from CTE
where isnull(CTE.BSPLITS,'') <> isnull(CTE.RSPLITS,'');
----------------------------------------------------------------------
-- Return results!!!
select *
from @RESULTS
order by BATCHRECORDSEQUENCE, FIELDSEQUENCE, FIELDRECORDID;