USP_GENERATEREVENUEUPDATEBATCH
Populates a row of the given batch with data from the given revenue ID.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @BATCHID | uniqueidentifier | IN | |
| @SEQUENCE | int | IN | |
| @DEFAULTADJUSTMENTDATE | datetime | IN | |
| @DEFAULTADJUSTMENTPOSTDATE | datetime | IN | |
| @DEFAULTADJUSTMENTPOSTSTATUSCODE | tinyint | IN | |
| @DEFAULTADJUSTMENTREASONCODEID | uniqueidentifier | IN | |
| @DEFAULTADJUSTMENTREASON | nvarchar(300) | IN | |
| @OVERWRITE | bit | IN | |
| @OVERWRITEFIELD | nvarchar(255) | IN | |
| @OLDGUIDVALUE | uniqueidentifier | IN | |
| @NEWGUIDVALUE | uniqueidentifier | IN | |
| @OLDDATEVALUE | datetime | IN | |
| @NEWDATEVALUE | datetime | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_GENERATEREVENUEUPDATEBATCH
(
@REVENUEID uniqueidentifier,
@BATCHID uniqueidentifier,
@SEQUENCE int,
@DEFAULTADJUSTMENTDATE datetime,
@DEFAULTADJUSTMENTPOSTDATE datetime,
@DEFAULTADJUSTMENTPOSTSTATUSCODE tinyint,
@DEFAULTADJUSTMENTREASONCODEID uniqueidentifier,
@DEFAULTADJUSTMENTREASON nvarchar(300),
@OVERWRITE bit,
@OVERWRITEFIELD nvarchar(255),
@OLDGUIDVALUE uniqueidentifier,
@NEWGUIDVALUE uniqueidentifier,
@OLDDATEVALUE datetime,
@NEWDATEVALUE datetime,
@CURRENTAPPUSERID uniqueidentifier
)
as
begin
set nocount on;
declare @ID uniqueidentifier;
declare @CURRENTDATE datetime;
declare @CHANGEAGENTID uniqueidentifier;
declare @REVENUESPLITID uniqueidentifier;
declare @CONSTITUENTID uniqueidentifier;
declare @AMOUNT money;
declare @DATE datetime;
declare @TYPECODE tinyint;
declare @PAYMENTMETHODCODE tinyint;
declare @APPLICATIONINFO nvarchar(60);
declare @APPEALID uniqueidentifier;
declare @OPPORTUNITYID uniqueidentifier;
declare @POSTSTATUSCODE tinyint;
declare @POSTDATE datetime;
declare @DONOTACKNOWLEDGE bit;
declare @DONOTRECEIPT bit;
declare @MAILINGID uniqueidentifier;
declare @FINDERNUMBER bigint;
declare @SOURCECODE nvarchar(60);
declare @CHANNELCODEID uniqueidentifier;
declare @GIVENANONYMOUSLY bit;
declare @OTHERPAYMENTMETHODCODEID uniqueidentifier;
declare @RECEIPTAMOUNT money;
declare @RECEIPTTYPECODE tinyint;
declare @REFERENCE nvarchar(255);
declare @CATEGORYCODEID uniqueidentifier;
declare @REVENUELOOKUPID nvarchar(100);
declare @SPLITS xml;
declare @INSTALLMENTS xml;
declare @INSTALLMENTFREQUENCYCODE tinyint;
declare @INSTALLMENTSTARTDATE datetime;
declare @INSTALLMENTENDDATE datetime;
declare @NUMBEROFINSTALLMENTS int;
declare @SENDPLEDGEREMINDER bit;
declare @LOCKBOXID uniqueidentifier;
declare @LOCKBOXBATCHNUMBER nvarchar(100);
declare @LOCKBOXBATCHSEQUENCE int;
declare @ADDITIONALAPPLICATIONSSTREAM xml;
declare @OTHERTYPECODEID uniqueidentifier;
declare @PLEDGESUBTYPEID uniqueidentifier;
declare @REVENUESTREAMS xml;
declare @TRIBUTES xml;
declare @MATCHINGGIFTS xml;
declare @APPLICATIONRECOGNITIONS xml;
declare @APPLICATIONSOLICITORS xml;
declare @APPLICATIONBUSINESSUNITS xml;
declare @LETTERS xml;
declare @BASECURRENCYID uniqueidentifier;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @BASEEXCHANGERATEID uniqueidentifier;
declare @EXCHANGERATE decimal(20,8);
declare @PDACCOUNTSYSTEMID uniqueidentifier;
declare @AMOUNTFORVAT money;
declare @VATTAXRATEID uniqueidentifier;
declare @VATAMOUNT money;
declare @CHECKDATE dbo.UDT_FUZZYDATE;
declare @CHECKNUMBER nvarchar(20);
declare @CARDHOLDERNAME nvarchar(255);
declare @CREDITCARDNUMBER nvarchar(20);
declare @CREDITTYPECODEID uniqueidentifier;
declare @AUTHORIZATIONCODE nvarchar(20);
declare @EXPIRESON dbo.UDT_FUZZYDATE;
declare @ISSUER nvarchar(100);
declare @NUMBEROFUNITS decimal(20,3);
declare @SYMBOL nvarchar(25);
declare @MEDIANPRICE decimal(19,4);
declare @LOWPRICE decimal(19,4);
declare @HIGHPRICE decimal(19,4);
declare @CONSTITUENTACCOUNTID uniqueidentifier;
declare @DIRECTDEBITRESULTCODE nvarchar(10);
declare @DIRECTDEBITISREJECTED bit;
declare @PROPERTYSUBTYPECODEID uniqueidentifier;
declare @GIFTINKINDSUBTYPECODEID uniqueidentifier;
declare @GIFTINKINDITEMNAME nvarchar(100);
declare @GIFTINKINDDISPOSITIONCODE tinyint;
declare @GIFTINKINDNUMBEROFUNITS int;
declare @GIFTINKINDFAIRMARKETVALUE money;
declare @REFERENCEDATE dbo.UDT_FUZZYDATE;
declare @REFERENCENUMBER nvarchar(20);
--Variable names are changed to differentiate the benefits, as BENEFITS and PERCENTAGEBENEFITS fields getting overwritten by appeal benefits.
declare @APPEALBENEFITS xml;
declare @REVENUEBENEFITS xml;
declare @REVENUEPERCENTAGEBENEFITS xml;
declare @PERCENTAGEAPPEALBENEFITS xml;
declare @NOTES xml;
declare @STANDINGORDERSETUP bit;
declare @STANDINGORDERSETUPDATE datetime;
declare @USESYSTEMGENERATEDREFERENCENUMBER bit;
declare @DDISOURCECODEID uniqueidentifier;
declare @DDISOURCEDATE date;
declare @ISADJUSTMENT bit;
declare @ADJUSTMENTDATE datetime;
declare @ADJUSTMENTPOSTDATE datetime;
declare @ADJUSTMENTREASON nvarchar(300);
declare @ADJUSTMENTREASONCODEID uniqueidentifier;
declare @ADJUSTMENTPOSTSTATUSCODE tinyint;
declare @ISPOSTED bit;
declare @DECLINESGIFTAID bit;
declare @ISGIFTAIDSPONSORSHIP bit;
declare @INSTALLMENTSCHEDULESEEDDATE date;
declare @SALEAMOUNT money;
declare @SALEDATE datetime;
declare @BROKERFEE money;
declare @SALEPOSTSTATUSCODE tinyint;
declare @SALEPOSTDATE datetime;
declare @CREDITCARDID uniqueidentifier;
declare @INSTALLMENTAMOUNT money;
declare @SPLITSCHEDULEOPTIONCODE tinyint;
--Introduce USERMODIFIEDBENEFITS field, for if the benefits are modified by user.
declare @USERMODIFIEDBENEFITS bit;
--Introduce STANDINGORDERREFERENCENUMBER field, for fetching reference number when payment method is standing order.
declare @STANDINGORDERREFERENCENUMBER nvarchar(20);
if @ID is null
set @ID = NewID();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = GetDate();
if exists
(
select
MEMBERSHIPTRANSACTION.ID
from
dbo.MEMBERSHIPTRANSACTION
inner join dbo.REVENUESPLIT on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT.ID
where
REVENUESPLIT.REVENUEID = @REVENUEID and
REVENUESPLIT.DESIGNATIONID is null
)
begin
raiserror('Membership-related revenue with no associated designation cannot be imported into this batch.',13,1)
return 1;
end
if @OVERWRITE = 1 and @OVERWRITEFIELD = 'Designation'
begin
if exists (select 1
from dbo.REVENUE
left join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
left join dbo.INSTALLMENTSPLITPAYMENT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
where REVENUE.ID = @REVENUEID
and (((REVENUE.TRANSACTIONTYPECODE in (1,2)) and (REVENUESPLIT.DESIGNATIONID = @NEWGUIDVALUE)) or ((REVENUE.TRANSACTIONTYPECODE = 0) and (REVENUESPLIT.APPLICATIONCODE in (0,4,7)) and (REVENUESPLIT.TYPECODE in (0,4,9)) and (INSTALLMENTSPLITPAYMENT.ID is null) and (REVENUESPLIT.DESIGNATIONID = @NEWGUIDVALUE))))
begin
declare @ERRORMESSAGE nvarchar(max);
declare @OLDDESIGNATION nvarchar(512);
declare @NEWDESIGNATION nvarchar(512);
select @OLDDESIGNATION = USERID from dbo.DESIGNATION where ID = @OLDGUIDVALUE;
select @NEWDESIGNATION = USERID from dbo.DESIGNATION where ID = @NEWGUIDVALUE;
set @ERRORMESSAGE = 'Designation ' + @OLDDESIGNATION + ' cannot be overwritten by designation ' + @NEWDESIGNATION + ' because a designation of ' + @NEWDESIGNATION + ' already exists.';
raiserror(@ERRORMESSAGE, 13, 1);
return 1;
end
end
select
@REVENUESPLITID = case when (select count(*) from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = R.ID) <> 1 then null
else (select ID from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = R.ID) end,
@CONSTITUENTID = R.CONSTITUENTID,
@AMOUNT = R.TRANSACTIONAMOUNT,
@DATE = R.DATE,
@TYPECODE = case when R.TRANSACTIONTYPECODE = 2 then 3 else R.TRANSACTIONTYPECODE end,
@PAYMENTMETHODCODE = RP.PAYMENTMETHODCODE,
@APPLICATIONINFO = coalesce(dbo.UFN_REVENUEUPDATEBATCH_GETAPPLICATIONINFO(@REVENUEID), ''),
@APPEALID = R.APPEALID,
@OPPORTUNITYID = case when (select count(*) from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = R.ID) <> 1 then null
else (select OPPORTUNITYID from dbo.REVENUEOPPORTUNITY inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = REVENUEOPPORTUNITY.ID where REVENUESPLIT.REVENUEID = R.ID) end,
@POSTSTATUSCODE = case when R.DONOTPOST = 1 then 2 else 1 end,
@POSTDATE = R.POSTDATE,
@DONOTACKNOWLEDGE = R.DONOTACKNOWLEDGE,
@DONOTRECEIPT = R.DONOTRECEIPT,
@MAILINGID = R.MAILINGID,
@FINDERNUMBER = R.FINDERNUMBER,
@SOURCECODE = R.SOURCECODE,
@CHANNELCODEID = R.CHANNELCODEID,
@GIVENANONYMOUSLY = R.GIVENANONYMOUSLY,
@OTHERPAYMENTMETHODCODEID = OP.OTHERPAYMENTMETHODCODEID,
@RECEIPTAMOUNT = R.RECEIPTAMOUNT,
@RECEIPTTYPECODE = coalesce(R.RECEIPTTYPECODE, 255),
@REFERENCE = coalesce(RR.REFERENCE, ''),
@CATEGORYCODEID = case
when (select count(*) from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = R.ID) <> 1 then
case when (R.TRANSACTIONTYPECODE = 1 or R.TRANSACTIONTYPECODE = 2) -- display revenue category for pledge or recurring gift (it is the same for each split)
then
(select top 1 GLREVENUECATEGORYMAPPINGID from dbo.REVENUECATEGORY inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = REVENUECATEGORY.ID where REVENUESPLIT.REVENUEID = R.ID)
else
null
end
else (select GLREVENUECATEGORYMAPPINGID from dbo.REVENUECATEGORY inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = REVENUECATEGORY.ID where REVENUESPLIT.REVENUEID = R.ID) end,
@REVENUELOOKUPID = R.LOOKUPID,
@SPLITS = case when R.TRANSACTIONTYPECODE in (1,2) then dbo.UFN_REVENUEUPDATEBATCH_GETSPLITS_TOITEMLISTXML(@REVENUEID) else null end,
@INSTALLMENTS = case when R.TRANSACTIONTYPECODE = 1 then dbo.UFN_REVENUEUPDATEBATCH_LOADINSTALLMENTS_TOITEMLISTXML(@REVENUEID) else null end,
@INSTALLMENTFREQUENCYCODE = RS.FREQUENCYCODE,
@INSTALLMENTSTARTDATE = RS.STARTDATE,
@INSTALLMENTENDDATE = RS.ENDDATE,
@SENDPLEDGEREMINDER = coalesce(RS.SENDPLEDGEREMINDER, 1), --cannot insert null, RevenueUpdateBatchDataForm.Add sets null to 1
@LOCKBOXID = RB.LOCKBOXID,
@LOCKBOXBATCHNUMBER = RB.BATCHNUMBER,
@LOCKBOXBATCHSEQUENCE = RB.BATCHSEQUENCE,
@ADDITIONALAPPLICATIONSSTREAM = case when R.TRANSACTIONTYPECODE = 0 then dbo.UFN_REVENUEUPDATEBATCH_GETADDITIONALAPPLICATIONS_TOITEMLISTXML(@REVENUEID) else null end,
@OTHERTYPECODEID = case when (select count(*) from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = R.ID) <> 1 then null
else (select OTHERTYPECODEID from dbo.REVENUESPLITOTHER inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = REVENUESPLITOTHER.ID where REVENUESPLIT.REVENUEID = R.ID) end,
@PLEDGESUBTYPEID = RS.PLEDGESUBTYPEID,
@REVENUESTREAMS = case when R.TRANSACTIONTYPECODE = 0 then dbo.UFN_REVENUEUPDATEBATCH_GETAPPLICATIONSTREAM_TOITEMLISTXML(@REVENUEID, R.CONSTITUENTID) else null end,
@TRIBUTES = case when R.TRANSACTIONTYPECODE in (0,1) then dbo.UFN_REVENUEUPDATEBATCH_GETTRIBUTES_TOITEMLISTXML(@REVENUEID) else null end,
@MATCHINGGIFTS = case when R.TRANSACTIONTYPECODE = 0 then dbo.UFN_REVENUEUPDATEBATCH_GETMATCHINGGIFTS_TOITEMLISTXML(@REVENUEID) else null end,
@APPLICATIONRECOGNITIONS = dbo.UFN_REVENUEUPDATEBATCH_GETRECOGNITIONS_TOITEMLISTXML(@REVENUEID),
@APPLICATIONSOLICITORS = dbo.UFN_REVENUEUPDATEBATCH_GETSOLICITORS_TOITEMLISTXML(@REVENUEID),
@APPLICATIONBUSINESSUNITS = dbo.UFN_REVENUEUPDATEBATCH_GETBUSINESSUNITS(@REVENUEID, R.TRANSACTIONTYPECODE),
@LETTERS = dbo.UFN_REVENUEUPDATEBATCH_GETREVENUELETTERS(@REVENUEID),
@BASECURRENCYID = R.BASECURRENCYID,
@TRANSACTIONCURRENCYID = R.TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID = R.BASEEXCHANGERATEID,
@EXCHANGERATE = coalesce(CURRENCYEXCHANGERATE.RATE, 0),
@PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMFORREVENUE.PDACCOUNTSYSTEMID,
@AMOUNTFORVAT = coalesce(REVENUEVAT.TRANSACTIONAMOUNTTOTAX, 0),
@VATTAXRATEID = REVENUEVAT.VATTAXRATEID,
@VATAMOUNT = coalesce(REVENUEVAT.TRANSACTIONVATAMOUNT, 0),
@CHECKDATE = coalesce(CP.CHECKDATE, '00000000'),
@CHECKNUMBER = coalesce(CP.CHECKNUMBER, ''),
@CARDHOLDERNAME = coalesce(CCP.CARDHOLDERNAME, ''),
@CREDITCARDNUMBER = coalesce(CCP.CREDITCARDPARTIALNUMBER, ''),
@CREDITTYPECODEID = CCP.CREDITTYPECODEID,
@AUTHORIZATIONCODE = coalesce(CCP.AUTHORIZATIONCODE, ''),
@EXPIRESON = coalesce(CCP.EXPIRESON, '00000000'),
@ISSUER = coalesce(SD.ISSUER, ''),
@NUMBEROFUNITS = coalesce(SD.NUMBEROFUNITS, 0),
@SYMBOL = coalesce(SD.SYMBOL, ''),
@MEDIANPRICE = coalesce(SD.TRANSACTIONMEDIANPRICE, 0),
@LOWPRICE = coalesce(SD.TRANSACTIONLOWPRICE, 0),
@HIGHPRICE = coalesce(SD.TRANSACTIONHIGHPRICE, 0),
@CONSTITUENTACCOUNTID = case RP.PAYMENTMETHODCODE when 3 then DDP.CONSTITUENTACCOUNTID when 11 then SOP.CONSTITUENTACCOUNTID else null end,
@DIRECTDEBITRESULTCODE = coalesce(DDP.DIRECTDEBITRESULTCODE, ''),
@DIRECTDEBITISREJECTED = coalesce(DDP.ISREJECTED, 0),
@PROPERTYSUBTYPECODEID = PD.PROPERTYSUBTYPECODEID,
@GIFTINKINDSUBTYPECODEID = GIKP.GIFTINKINDSUBTYPECODEID,
@GIFTINKINDITEMNAME = coalesce(GIKP.ITEMNAME, ''),
@GIFTINKINDDISPOSITIONCODE = coalesce(GIKP.DISPOSITIONCODE, 0),
@GIFTINKINDNUMBEROFUNITS = coalesce(GIKP.NUMBEROFUNITS, 0),
@GIFTINKINDFAIRMARKETVALUE = coalesce(GIKP.TRANSACTIONFAIRMARKETVALUE, 0),
@REFERENCEDATE = case RP.PAYMENTMETHODCODE when 0 then CASH.REFERENCEDATE when 3 then DDP.REFERENCEDATE when 10 then OP.REFERENCEDATE when 11 then SOP.REFERENCEDATE else null end,
@REFERENCENUMBER = case RP.PAYMENTMETHODCODE when 0 then CASH.REFERENCENUMBER when 3 then DDP.REFERENCENUMBER when 10 then OP.REFERENCENUMBER when 11 then N''
when 101 then PAYPAL.REFERENCENUMBER when 102 then VENMO.REFERENCENUMBER else null end,
@ISPOSTED = case when P.ID is null then 0 else 1 end,
@DECLINESGIFTAID = coalesce(case when (select count(*) from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = R.ID) <> 1 then null
else (select DECLINESGIFTAID
from dbo.REVENUESPLIT
inner join dbo.REVENUESPLITGIFTAID on REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID
where REVENUESPLIT.REVENUEID = R.ID) end, 0),
@ISGIFTAIDSPONSORSHIP = coalesce(case when (select count(*) from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = R.ID) <> 1 then null
else (select ISSPONSORSHIP
from dbo.REVENUESPLIT
inner join dbo.REVENUESPLITGIFTAID on REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID
where REVENUESPLIT.REVENUEID = R.ID) end, 0),
@INSTALLMENTSCHEDULESEEDDATE = case when R.TRANSACTIONTYPECODE = 2 then RS.NEXTTRANSACTIONDATE else null end,
@CREDITCARDID = RS.CREDITCARDID
from dbo.REVENUE R
inner join dbo.REVENUEPAYMENTMETHOD RP on R.ID = RP.REVENUEID
left join dbo.PDACCOUNTSYSTEMFORREVENUE on R.ID = PDACCOUNTSYSTEMFORREVENUE.ID
left join dbo.OTHERPAYMENTMETHODDETAIL OP on OP.ID = RP.ID
left join dbo.REVENUEREFERENCE RR on R.ID = RR.ID
left join dbo.REVENUESCHEDULE RS on RS.ID = R.ID
left join dbo.REVENUELOCKBOX RB on RB.ID = R.ID
left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = R.BASEEXCHANGERATEID
left join dbo.REVENUEVAT on R.ID = REVENUEVAT.ID
left join dbo.CHECKPAYMENTMETHODDETAIL CP on RP.ID = CP.ID
left join dbo.CREDITCARDPAYMENTMETHODDETAIL CCP on RP.ID = CCP.ID
left join dbo.STOCKDETAIL SD on RP.ID = SD.ID
left join dbo.DIRECTDEBITPAYMENTMETHODDETAIL DDP on RP.ID = DDP.ID
left join dbo.PROPERTYDETAIL PD on RP.ID = PD.ID
left join dbo.GIFTINKINDPAYMENTMETHODDETAIL GIKP on RP.ID = GIKP.ID
left join dbo.STANDINGORDERPAYMENTMETHODDETAIL SOP on RP.ID = SOP.ID
left join dbo.CASHPAYMENTMETHODDETAIL CASH on RP.ID = CASH.ID
left join dbo.REVENUEPOSTED P on P.ID = R.ID
left join dbo.PAYPALPAYMENTMETHODDETAIL PAYPAL on RP.ID = PAYPAL.ID
left join dbo.VENMOPAYMENTMETHODDETAIL VENMO on RP.ID = VENMO.ID
where R.ID = @REVENUEID
exec dbo.USP_REVENUE_GETPAYMENTDETAILS
@REVENUEID = @REVENUEID,
@SALEAMOUNT = @SALEAMOUNT output,
@SALEDATE = @SALEDATE output,
@BROKERFEE = @BROKERFEE output,
@SALEPOSTSTATUSCODE = @SALEPOSTSTATUSCODE output,
@SALEPOSTDATE = @SALEPOSTDATE output;
if @SALEAMOUNT is null
set @SALEAMOUNT = 0;
if @BROKERFEE is null
set @BROKERFEE = 0;
if @SALEPOSTSTATUSCODE is null
set @SALEPOSTSTATUSCODE = 255;
if @TYPECODE = 1
begin
if @ISPOSTED = 1
set @ISADJUSTMENT = 1;
select @INSTALLMENTAMOUNT = INSTALLMENTAMOUNT, @SPLITSCHEDULEOPTIONCODE = 0 from dbo.PLEDGEINSTALLMENTOPTION where ID = @REVENUEID;
end
if @TYPECODE = 0 and @ISPOSTED = 1 and not exists(select ID from dbo.REVENUESPLIT where REVENUEID = @REVENUEID and APPLICATIONCODE = 11)
begin
declare @ISORDERAPPLICATION bit = 0;
if exists(select 'x' from dbo.SALESORDERPAYMENT
inner join dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDERPAYMENT.SALESORDERID
where SALESORDERPAYMENT.PAYMENTID = @REVENUEID and SALESORDERITEM.TYPECODE <> 12)
begin
select @ISORDERAPPLICATION = 1
from dbo.SALESORDERPAYMENT
where PAYMENTID = @REVENUEID;
end
if @ISORDERAPPLICATION = 0 and dbo.UFN_REVENUE_HASGIFTAIDSPLITONPENDINGR68(@REVENUEID, 1) = 0
set @ISADJUSTMENT = 1;
end
if @PAYMENTMETHODCODE = 3 --Direct Debit
begin
if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
begin
select
@REFERENCEDATE = REFERENCEDATE,
@REFERENCENUMBER = REFERENCENUMBER,
@CONSTITUENTACCOUNTID = CONSTITUENTACCOUNTID,
@DDISOURCECODEID = DDISOURCECODEID,
@DDISOURCEDATE = DDISOURCEDATE
from dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT
where ID = @REVENUEID
end
else
begin
select
@REFERENCEDATE = REFERENCEDATE,
@REFERENCENUMBER = REFERENCENUMBER,
@CONSTITUENTACCOUNTID = CONSTITUENTACCOUNTID
from dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT
where ID = @REVENUEID
end
end
if @PAYMENTMETHODCODE = 11 -- standing order
begin
select
@REFERENCEDATE = REFERENCEDATE,
@STANDINGORDERREFERENCENUMBER = dbo.UFN_STANDINGORDER_REFERENCENUMBER_FORDISPLAY(REVENUESCHEDULESTANDINGORDERPAYMENT.ID),
@CONSTITUENTACCOUNTID = CONSTITUENTACCOUNTID,
@STANDINGORDERSETUP = STANDINGORDERSETUP,
@STANDINGORDERSETUPDATE = STANDINGORDERSETUPDATE,
@USESYSTEMGENERATEDREFERENCENUMBER = USESYSTEMGENERATEDREFERENCENUMBER
from dbo.REVENUESCHEDULESTANDINGORDERPAYMENT
left join dbo.REVENUESTANDINGORDER on REVENUESTANDINGORDER.ID = REVENUESCHEDULESTANDINGORDERPAYMENT.ID
where REVENUESCHEDULESTANDINGORDERPAYMENT.ID = @REVENUEID
end
set @REVENUEBENEFITS =
(
select
'00000000-0000-0000-0000-000000000000' as ID,
BENEFITID,
QUANTITY,
UNITVALUE,
TOTALVALUE,
DETAILS,
SEQUENCE,
ID as REVENUEBENEFITID,
BASECURRENCYID as BENEFITCURRENCYID,
TRANSACTIONCURRENCYID,
TRANSACTIONTOTALVALUE
from UFN_REVENUE_GETBENEFITS_3(@REVENUEID) B
where B.USEPERCENT = 0
for xml raw('ITEM'), type, elements, root('BENEFITS'), binary base64
);
set @REVENUEPERCENTAGEBENEFITS =
(
select
'00000000-0000-0000-0000-000000000000' as ID,
BENEFITID,
PERCENTAPPLICABLEAMOUNT,
VALUEPERCENT,
TOTALVALUE,
DETAILS,
SEQUENCE,
ID as REVENUEBENEFITID,
TRANSACTIONCURRENCYID
from UFN_REVENUE_GETBENEFITS_3(@REVENUEID) B
where B.USEPERCENT = 1
for xml raw('ITEM'), type, elements, root('PERCENTAGEBENEFITS'), binary base64
);
set @NOTES =
(
select
'00000000-0000-0000-0000-000000000000' as ID,
REVENUENOTETYPECODEID as NOTETYPECODEID,
TITLE AS NOTETITLE,
DATEENTERED AS NOTEDATEENTERED,
AUTHORID AS NOTEAUTHORID,
TEXTNOTE AS NOTETEXTNOTE,
ID as REVENUENOTEID,
HTMLNOTE as NOTEHTMLNOTE
from REVENUENOTE RN
where RN.REVENUEID = @REVENUEID
for xml raw('ITEM'), type, elements, root('NOTES'), binary base64
);
if @ISADJUSTMENT = 1
begin
select top 1
@ADJUSTMENTDATE = ADJUSTMENT.DATE,
@ADJUSTMENTPOSTDATE = ADJUSTMENT.POSTDATE,
@ADJUSTMENTPOSTSTATUSCODE = ADJUSTMENT.POSTSTATUSCODE,
@ADJUSTMENTREASON = ADJUSTMENT.REASON,
@ADJUSTMENTREASONCODEID = ADJUSTMENT.REASONCODEID
from dbo.ADJUSTMENT
where ADJUSTMENT.REVENUEID = @REVENUEID
order by dateadded desc
if @ADJUSTMENTREASONCODEID is null
begin
select
@ADJUSTMENTDATE = DATE,
@ADJUSTMENTPOSTDATE = POSTDATE,
@ADJUSTMENTPOSTSTATUSCODE = 1,
@ADJUSTMENTREASON = REASON,
@ADJUSTMENTREASONCODEID = REASONCODEID
from dbo.BENEFITADJUSTMENT
where REVENUEID = @REVENUEID and POSTSTATUSCODE = 1
end
--if the most recent adjustment has been posted then in essence we have no adjustment so clear out the adjustment fields
if @ADJUSTMENTPOSTSTATUSCODE = 0
begin
select @ADJUSTMENTDATE = null,
@ADJUSTMENTPOSTDATE = null,
@ADJUSTMENTPOSTSTATUSCODE = 1,
@ADJUSTMENTREASON = '',
@ADJUSTMENTREASONCODEID = null
end
if @ADJUSTMENTREASONCODEID is null
begin
set @ADJUSTMENTDATE = coalesce(@DEFAULTADJUSTMENTDATE, @CURRENTDATE);
set @ADJUSTMENTPOSTDATE = coalesce(@DEFAULTADJUSTMENTPOSTDATE, @CURRENTDATE);
set @ADJUSTMENTPOSTSTATUSCODE = @DEFAULTADJUSTMENTPOSTSTATUSCODE;
set @ADJUSTMENTREASONCODEID = @DEFAULTADJUSTMENTREASONCODEID;
set @ADJUSTMENTREASON = @DEFAULTADJUSTMENTREASON;
end
end
declare @FIRSTUNPAIDINSTALLMENTSEQUENCE integer;
select
@FIRSTUNPAIDINSTALLMENTSEQUENCE = min(SEQUENCE)
from dbo.INSTALLMENT
where INSTALLMENT.REVENUEID = @REVENUEID and dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) = INSTALLMENT.TRANSACTIONAMOUNT;
if @FIRSTUNPAIDINSTALLMENTSEQUENCE is not null
select @NUMBEROFINSTALLMENTS = count(INSTALLMENT.ID) from dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2(@REVENUEID) INSTALLMENT where INSTALLMENT.BALANCE > 0;
else
set @NUMBEROFINSTALLMENTS = 0;
if @TYPECODE <> 0
set @LOCKBOXID = null;
if @LOCKBOXBATCHNUMBER is null or @LOCKBOXID is null
set @LOCKBOXBATCHNUMBER = '';
if @LOCKBOXBATCHSEQUENCE is null or @LOCKBOXID is null
set @LOCKBOXBATCHSEQUENCE = 0;
if @PDACCOUNTSYSTEMID is null
set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID)
if @BASECURRENCYID is null
begin
declare @CURRENCYSETID uniqueidentifier
select @CURRENCYSETID = CURRENCYSETID
from dbo.PDACCOUNTSYSTEM
where ID = @PDACCOUNTSYSTEMID
select @BASECURRENCYID = CURRENCYSET.BASECURRENCYID
from dbo.CURRENCYSET
where CURRENCYSET.ID = coalesce(@CURRENCYSETID,dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET())
end
if @USESYSTEMGENERATEDREFERENCENUMBER is null
set @USESYSTEMGENERATEDREFERENCENUMBER = 1;
if @PAYMENTMETHODCODE = 11 and @USESYSTEMGENERATEDREFERENCENUMBER is null
begin
set @STANDINGORDERREFERENCENUMBER = '';
set @USESYSTEMGENERATEDREFERENCENUMBER = 0;
end
if @PAYMENTMETHODCODE = 11 and @USESYSTEMGENERATEDREFERENCENUMBER = 1
begin
set @STANDINGORDERREFERENCENUMBER = '';
end
if @TYPECODE = 1 or @TYPECODE = 3 --Pledges and Recurring gifts do not receipt
set @DONOTRECEIPT = 1;
if @INSTALLMENTFREQUENCYCODE is null
begin
-- For Recurring Gifts, default to Monthly. For other types, use Single Installment.
if @TYPECODE = 3
set @INSTALLMENTFREQUENCYCODE = 3;
else
set @INSTALLMENTFREQUENCYCODE = 5;
end
if len(@APPLICATIONINFO) > 3
set @APPLICATIONINFO = ''
--USP to set the USERMODIFIEDBENEFITS field after comparing benefits, as it gets done in Revenue Update Batch.
exec dbo.USP_BENEFIT_USERMODIFIEDBENEFITS
@APPLICATIONINFO, @REVENUESTREAMS,
@APPEALID, @AMOUNT, @DATE,
@TRANSACTIONCURRENCYID,
@BASECURRENCYID,
@REVENUEBENEFITS,
@REVENUEPERCENTAGEBENEFITS,
@EXCHANGERATE,
@BASEEXCHANGERATEID,
@USERMODIFIEDBENEFITS output;
if @USERMODIFIEDBENEFITS is null
set @USERMODIFIEDBENEFITS = 0;
if @OVERWRITE = 1
begin
if @OVERWRITEFIELD = 'Appeal'
begin
if @APPEALID <> @NEWGUIDVALUE
begin
set @APPEALID = @NEWGUIDVALUE;
set @MAILINGID = NULL
-- Default benefits
set @APPEALBENEFITS =
(
select newid() ID, BENEFITID, QUANTITY, VALUE UNITVALUE, SEQUENCE, '' DETAILS
from dbo.UFN_APPEAL_GETBENEFITDETAILS_2(@APPEALID, @AMOUNT, @TRANSACTIONCURRENCYID, @BASECURRENCYID)
for xml raw('ITEM'), type, elements, root('BENEFITS'), binary base64
);
-- Default Percent benefits
set @PERCENTAGEAPPEALBENEFITS =
(
select newid() ID, BENEFITID, 0 PERCENTAPPLICABLEAMOUNT, VALUEPERCENT, SEQUENCE, '' DETAILS
from dbo.UFN_APPEAL_GETBENEFITDETAILS_PCT_2(@APPEALID, @AMOUNT, @TRANSACTIONCURRENCYID)
for xml raw('ITEM'), type, elements, root('PERCENTAGEBENEFITS'), binary base64
);
end
end
else if @OVERWRITEFIELD = 'Campaign'
begin
if @TYPECODE = 0
begin
set @ADDITIONALAPPLICATIONSSTREAM =
(select
'00000000-0000-0000-0000-000000000000' as ID,
case when REVENUESPLIT.APPLICATIONCODE = 0 and REVENUESPLIT.TYPECODE = 0 then 0
when REVENUESPLIT.APPLICATIONCODE = 4 and REVENUESPLIT.TYPECODE = 4 then 1
when REVENUESPLIT.APPLICATIONCODE = 7 and REVENUESPLIT.TYPECODE = 0 then 2
when REVENUESPLIT.APPLICATIONCODE = 0 and REVENUESPLIT.TYPECODE = 9 then 3
end as TYPECODE,
REVENUESPLIT.TRANSACTIONAMOUNT as APPLIED,
DESIGNATIONID,
OTHERTYPECODEID,
0 as DECLINESGIFTAID,
OPPORTUNITYID,
case when REVENUESPLIT.APPLICATIONCODE = 0 and REVENUESPLIT.TYPECODE = 9 then
(case when (select count(*) from dbo.SPONSORSHIP where CONSTITUENTID = REVENUE.CONSTITUENTID and ISMOSTRECENTFORCOMMITMENT = 1) <> 1 then null
else (select ID from dbo.SPONSORSHIP where CONSTITUENTID = REVENUE.CONSTITUENTID and ISMOSTRECENTFORCOMMITMENT = 1) end)
end SPONSORSHIPID,
0 as ISGIFTAIDSPONSORSHIP,
REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID as CATEGORYCODEID,
cast(1 as bit) as DIDCAMPAIGNSDEFAULT,
(select
'00000000-0000-0000-0000-000000000000' as ID,
case when CAMPAIGNID = @OLDGUIDVALUE then @NEWGUIDVALUE else CAMPAIGNID end as CAMPAIGNID,
case when CAMPAIGNID = @OLDGUIDVALUE then null else CAMPAIGNSUBPRIORITYID end as CAMPAIGNSUBPRIORITYID,
REVENUESPLITCAMPAIGN.ID as REVENUESPLITCAMPAIGNID
from dbo.REVENUESPLITCAMPAIGN
where REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
and (CAMPAIGNID <> @OLDGUIDVALUE or not exists (select 1 from dbo.REVENUESPLITCAMPAIGN where REVENUESPLITID = REVENUESPLIT.ID and CAMPAIGNID = @NEWGUIDVALUE))
for xml raw('ITEM'),type,elements,BINARY BASE64) as CAMPAIGNS,
REVENUESPLIT.ID as REVENUESPLITID,
REVENUESPLIT.TRANSACTIONCURRENCYID,
0 as GIFTAIDCOMMITTED
from dbo.REVENUESPLIT
inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
left outer join dbo.REVENUECATEGORY on REVENUESPLIT.ID = REVENUECATEGORY.ID
left outer join dbo.REVENUEOPPORTUNITY on REVENUESPLIT.ID = REVENUEOPPORTUNITY.ID
left outer join dbo.REVENUESPLITOTHER on REVENUESPLITOTHER.ID = REVENUESPLIT.ID
left outer join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
where REVENUE.ID = @REVENUEID
and APPLICATIONCODE in (0,4,7)
and TYPECODE in (0,4,9)
and INSTALLMENTSPLITPAYMENT.ID is null
for xml raw('ITEM'),type,elements,root('ADDITIONALAPPLICATIONSSTREAM'),BINARY BASE64)
end
else if @TYPECODE = 1 or @TYPECODE = 3
begin
set @SPLITS =
(select
'00000000-0000-0000-0000-000000000000' as ID,
REVENUESPLIT.DESIGNATIONID,
TRANSACTIONAMOUNT as AMOUNT,
ROW_NUMBER() OVER(order by REVENUESPLIT.dateadded) as SEQUENCE,
REVENUESPLIT.APPLICATIONCODE,
REVENUESPLIT.TYPECODE,
1 as DIDCAMPAIGNSDEFAULT,
(select
'00000000-0000-0000-0000-000000000000' as ID,
case when CAMPAIGNID = @OLDGUIDVALUE then @NEWGUIDVALUE else CAMPAIGNID end as CAMPAIGNID,
case when CAMPAIGNID = @OLDGUIDVALUE then null else CAMPAIGNSUBPRIORITYID end as CAMPAIGNSUBPRIORITYID,
REVENUESPLITCAMPAIGN.ID as REVENUESPLITCAMPAIGNID
from dbo.REVENUESPLITCAMPAIGN
where REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
and (CAMPAIGNID <> @OLDGUIDVALUE or not exists (select 1 from dbo.REVENUESPLITCAMPAIGN where REVENUESPLITID = REVENUESPLIT.ID and CAMPAIGNID = @NEWGUIDVALUE))
for xml raw('ITEM'),type,elements,BINARY BASE64) as CAMPAIGNS,
coalesce(REVENUESPLITGIFTAID.DECLINESGIFTAID, cast(0 as bit)) as DECLINESGIFTAID,
coalesce(REVENUESPLITGIFTAID.ISSPONSORSHIP, cast(0 as bit)) as ISGIFTAIDSPONSORSHIP,
REVENUESPLIT.ID as REVENUESPLITID,
REVENUESPLIT.TRANSACTIONCURRENCYID
from dbo.REVENUESPLIT
left outer join dbo.REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
where REVENUEID = @REVENUEID
for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64);
end
end
else if @OVERWRITEFIELD = 'Date'
begin
set @DATE = @NEWDATEVALUE;
end
else if @OVERWRITEFIELD = 'Designation'
begin
if @TYPECODE = 0
begin
set @ADDITIONALAPPLICATIONSSTREAM =
(select
'00000000-0000-0000-0000-000000000000' as ID,
case when REVENUESPLIT.APPLICATIONCODE = 0 and REVENUESPLIT.TYPECODE = 0 then 0
when REVENUESPLIT.APPLICATIONCODE = 4 and REVENUESPLIT.TYPECODE = 4 then 1
when REVENUESPLIT.APPLICATIONCODE = 7 and REVENUESPLIT.TYPECODE = 0 then 2
when REVENUESPLIT.APPLICATIONCODE = 0 and REVENUESPLIT.TYPECODE = 9 then 3
end as TYPECODE,
REVENUESPLIT.TRANSACTIONAMOUNT as APPLIED,
case when DESIGNATIONID = @OLDGUIDVALUE then @NEWGUIDVALUE else DESIGNATIONID end as DESIGNATIONID,
OTHERTYPECODEID,
0 as DECLINESGIFTAID,
OPPORTUNITYID,
case when REVENUESPLIT.APPLICATIONCODE = 0 and REVENUESPLIT.TYPECODE = 9 then
(case when (select count(*) from dbo.SPONSORSHIP where CONSTITUENTID = REVENUE.CONSTITUENTID and ISMOSTRECENTFORCOMMITMENT = 1) <> 1 then null
else (select ID from dbo.SPONSORSHIP where CONSTITUENTID = REVENUE.CONSTITUENTID and ISMOSTRECENTFORCOMMITMENT = 1) end)
end SPONSORSHIPID,
0 as ISGIFTAIDSPONSORSHIP,
REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID as CATEGORYCODEID,
cast(1 as bit) as DIDCAMPAIGNSDEFAULT,
case when DESIGNATIONID = @OLDGUIDVALUE then
(select
'00000000-0000-0000-0000-000000000000' as ID,
CAMPAIGNID,
CAMPAIGNSUBPRIORITYID,
'00000000-0000-0000-0000-000000000000' as REVENUESPLITCAMPAIGNID
from dbo.UFN_DESIGNATION_GETCAMPAIGNSTODEFAULT(@NEWGUIDVALUE, @CURRENTDATE)
for xml raw('ITEM'),type,elements,BINARY BASE64)
else
(select
'00000000-0000-0000-0000-000000000000' as ID,
CAMPAIGNID,
CAMPAIGNSUBPRIORITYID,
REVENUESPLITCAMPAIGN.ID as REVENUESPLITCAMPAIGNID
from dbo.REVENUESPLITCAMPAIGN
where REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
for xml raw('ITEM'),type,elements,BINARY BASE64) end as CAMPAIGNS,
REVENUESPLIT.ID as REVENUESPLITID,
REVENUESPLIT.TRANSACTIONCURRENCYID,
0 as GIFTAIDCOMMITTED
from dbo.REVENUESPLIT
inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
left outer join dbo.REVENUECATEGORY on REVENUESPLIT.ID = REVENUECATEGORY.ID
left outer join dbo.REVENUEOPPORTUNITY on REVENUESPLIT.ID = REVENUEOPPORTUNITY.ID
left outer join dbo.REVENUESPLITOTHER on REVENUESPLITOTHER.ID = REVENUESPLIT.ID
left outer join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
where REVENUE.ID = @REVENUEID
and APPLICATIONCODE in (0,4,7)
and TYPECODE in (0,4,9)
and INSTALLMENTSPLITPAYMENT.ID is null
for xml raw('ITEM'),type,elements,root('ADDITIONALAPPLICATIONSSTREAM'),BINARY BASE64)
end
else if @TYPECODE = 1 or @TYPECODE = 3
begin
set @SPLITS =
(select
'00000000-0000-0000-0000-000000000000' as ID,
case when DESIGNATIONID = @OLDGUIDVALUE then @NEWGUIDVALUE else DESIGNATIONID end as DESIGNATIONID,
TRANSACTIONAMOUNT as AMOUNT,
ROW_NUMBER() OVER(order by REVENUESPLIT.dateadded) as SEQUENCE,
REVENUESPLIT.APPLICATIONCODE,
REVENUESPLIT.TYPECODE,
1 as DIDCAMPAIGNSDEFAULT,
case when DESIGNATIONID = @OLDGUIDVALUE then
(select
'00000000-0000-0000-0000-000000000000' as ID,
CAMPAIGNID,
CAMPAIGNSUBPRIORITYID,
'00000000-0000-0000-0000-000000000000' as REVENUESPLITCAMPAIGNID
from dbo.UFN_DESIGNATION_GETCAMPAIGNSTODEFAULT(@NEWGUIDVALUE, @CURRENTDATE)
for xml raw('ITEM'),type,elements,BINARY BASE64)
else
(select
'00000000-0000-0000-0000-000000000000' as ID,
CAMPAIGNID,
CAMPAIGNSUBPRIORITYID,
REVENUESPLITCAMPAIGN.ID as REVENUESPLITCAMPAIGNID
from dbo.REVENUESPLITCAMPAIGN
where REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
for xml raw('ITEM'),type,elements,BINARY BASE64) end as CAMPAIGNS,
coalesce(REVENUESPLITGIFTAID.DECLINESGIFTAID, cast(0 as bit)) as DECLINESGIFTAID,
coalesce(REVENUESPLITGIFTAID.ISSPONSORSHIP, cast(0 as bit)) as ISGIFTAIDSPONSORSHIP,
REVENUESPLIT.ID as REVENUESPLITID,
REVENUESPLIT.TRANSACTIONCURRENCYID
from dbo.REVENUESPLIT
left outer join dbo.REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
where REVENUEID = @REVENUEID
for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64);
end
while (select @APPLICATIONSOLICITORS.exist('(/APPLICATIONSOLICITORS/ITEM/ADDITIONALAPPLICATIONDESIGNATIONID[fn:upper-case(text()[1])=fn:upper-case(sql:variable("@OLDGUIDVALUE"))])')) = 1
begin
set @APPLICATIONSOLICITORS.modify('replace value of (/APPLICATIONSOLICITORS/ITEM/ADDITIONALAPPLICATIONDESIGNATIONID[fn:upper-case(text()[1])=fn:upper-case(sql:variable("@OLDGUIDVALUE"))]/text())[1] with sql:variable("@NEWGUIDVALUE")')
end
end
else if @OVERWRITEFIELD = 'Solicitor'
begin
while (select @APPLICATIONSOLICITORS.exist('(/APPLICATIONSOLICITORS/ITEM/SOLICITORS/ITEM/CONSTITUENTID[fn:upper-case(text()[1])=fn:upper-case(sql:variable("@OLDGUIDVALUE"))])')) = 1
begin
set @APPLICATIONSOLICITORS.modify('replace value of (/APPLICATIONSOLICITORS/ITEM/SOLICITORS/ITEM/CONSTITUENTID[fn:upper-case(text()[1])=fn:upper-case(sql:variable("@OLDGUIDVALUE"))]/text())[1] with sql:variable("@NEWGUIDVALUE")')
end
end
end
if cast(@SPLITS as nvarchar(max)) = ''
begin
set @SPLITS = null;
end
else
begin
if not exists (select 1 from @SPLITS.nodes('/SPLITS/ITEM') T(c))
set @SPLITS = null;
end
begin try
if @CREDITCARDID is null
begin
exec dbo.USP_CREDITCARD_SAVE
@ID = @CREDITCARDID output,
@CREDITCARDTOKEN = null,
@CARDHOLDERNAME = @CARDHOLDERNAME,
@CREDITCARDPARTIALNUMBER = @CREDITCARDNUMBER,
@CREDITTYPECODEID = @CREDITTYPECODEID,
@EXPIRESON = @EXPIRESON,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTDATE = @CURRENTDATE;
end
--Insert the value in USERMODIFIEDBENEFITS field, based on which it decides whether to show or not benefits after appeal change, as it gets done in Revenue Update Batch.
insert into dbo.BATCHREVENUE
(
ID, BATCHID, REVENUEID, REVENUESPLITID, CONSTITUENTID, DATE, PAYMENTMETHODCODE, DONOTACKNOWLEDGE, CHECKDATE, CHECKNUMBER, REFERENCEDATE, REFERENCENUMBER,
CREDITCARDID, AUTHORIZATIONCODE, CONSTITUENTACCOUNTID, AMOUNT, TYPECODE, RECEIPTAMOUNT,
DONOTRECEIPT, SEQUENCE, INSTALLMENTFREQUENCYCODE, INSTALLMENTSTARTDATE,
INSTALLMENTENDDATE, NUMBEROFINSTALLMENTS, FINDERNUMBER, SOURCECODE, APPEALID, FINDERNUMBERISVALID,
POSTDATE, POSTSTATUSCODE, PROPERTYSUBTYPECODEID, GIFTINKINDSUBTYPECODEID,
SENDPLEDGEREMINDER, SALEDATE, SALEAMOUNT, BROKERFEE, SALEPOSTSTATUSCODE, SALEPOSTDATE, ISSUER, NUMBEROFUNITS,
SYMBOL, MEDIANPRICE,
GIVENANONYMOUSLY, PLEDGESUBTYPEID,
MAILINGID, CHANNELCODEID, RECEIPTTYPECODE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, OTHERPAYMENTMETHODCODEID, REFERENCE,
GLREVENUECATEGORYMAPPINGID, OTHERTYPECODEID, OPPORTUNITYID, DIRECTDEBITRESULTCODE, LOWPRICE, HIGHPRICE,
STANDINGORDERSETUP, STANDINGORDERSETUPDATE, DECLINESGIFTAID, DDISOURCECODEID, DDISOURCEDATE,
AMOUNTFORVAT, VATTAXRATEID, VATAMOUNT, APPLICATIONINFO, REVENUELOOKUPID, MGGENERATED, MGALTERED,
GIFTINKINDITEMNAME, GIFTINKINDDISPOSITIONCODE, GIFTINKINDNUMBEROFUNITS, GIFTINKINDFAIRMARKETVALUE, DIRECTDEBITISREJECTED, ISGIFTAIDSPONSORSHIP,
LOCKBOXID, LOCKBOXBATCHNUMBER, LOCKBOXBATCHSEQUENCE, PDACCOUNTSYSTEMID, ISADJUSTMENT, ADJUSTMENTDATE, ADJUSTMENTPOSTDATE, ADJUSTMENTREASON, ADJUSTMENTREASONCODEID, ADJUSTMENTPOSTSTATUSCODE,
ADJPAYMENT_DATE, ADJPAYMENT_POSTDATE, ADJPAYMENT_DETAILS, ADJPAYMENT_REASONCODEID,
ORIGINALAPPLICATIONINFO, BASECURRENCYID, TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID, EXCHANGERATE, USESYSTEMGENERATEDREFERENCENUMBER, INSTALLMENTSCHEDULESEEDDATE,INSTALLMENTAMOUNT,SPLITSCHEDULEOPTIONCODE, USERMODIFIEDBENEFITS, STANDINGORDERREFERENCENUMBER
)
values
(
@ID, @BATCHID, @REVENUEID, @REVENUESPLITID, @CONSTITUENTID, @DATE, @PAYMENTMETHODCODE, @DONOTACKNOWLEDGE, @CHECKDATE, @CHECKNUMBER, coalesce(@REFERENCEDATE, '00000000'), coalesce(@REFERENCENUMBER, ''),
@CREDITCARDID, @AUTHORIZATIONCODE, @CONSTITUENTACCOUNTID, @AMOUNT, @TYPECODE, @RECEIPTAMOUNT,
@DONOTRECEIPT, @SEQUENCE, @INSTALLMENTFREQUENCYCODE, @INSTALLMENTSTARTDATE,
@INSTALLMENTENDDATE, @NUMBEROFINSTALLMENTS, @FINDERNUMBER, @SOURCECODE, @APPEALID, case @FINDERNUMBER when 0 then 0 else 1 end,
@POSTDATE, @POSTSTATUSCODE, @PROPERTYSUBTYPECODEID, @GIFTINKINDSUBTYPECODEID,
@SENDPLEDGEREMINDER, @SALEDATE, @SALEAMOUNT, @BROKERFEE, @SALEPOSTSTATUSCODE, @SALEPOSTDATE, @ISSUER, @NUMBEROFUNITS,
@SYMBOL, @MEDIANPRICE,
@GIVENANONYMOUSLY, @PLEDGESUBTYPEID,
@MAILINGID, @CHANNELCODEID, @RECEIPTTYPECODE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @OTHERPAYMENTMETHODCODEID, @REFERENCE,
@CATEGORYCODEID, @OTHERTYPECODEID, @OPPORTUNITYID, @DIRECTDEBITRESULTCODE, @LOWPRICE, @HIGHPRICE,
coalesce(@STANDINGORDERSETUP, 0), @STANDINGORDERSETUPDATE, @DECLINESGIFTAID, @DDISOURCECODEID, @DDISOURCEDATE,
@AMOUNTFORVAT, @VATTAXRATEID, @VATAMOUNT, @APPLICATIONINFO, coalesce(rtrim(ltrim(@REVENUELOOKUPID)),''), 0, 0,
@GIFTINKINDITEMNAME, @GIFTINKINDDISPOSITIONCODE, @GIFTINKINDNUMBEROFUNITS, @GIFTINKINDFAIRMARKETVALUE, @DIRECTDEBITISREJECTED, @ISGIFTAIDSPONSORSHIP,
@LOCKBOXID, @LOCKBOXBATCHNUMBER, @LOCKBOXBATCHSEQUENCE, @PDACCOUNTSYSTEMID, coalesce(@ISADJUSTMENT, 0), @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, coalesce(@ADJUSTMENTREASON, ''), @ADJUSTMENTREASONCODEID, coalesce(@ADJUSTMENTPOSTSTATUSCODE, 1),
@ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, coalesce(@ADJUSTMENTREASON, ''), @ADJUSTMENTREASONCODEID,
@APPLICATIONINFO, @BASECURRENCYID, @TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID, @EXCHANGERATE, @USESYSTEMGENERATEDREFERENCENUMBER, @INSTALLMENTSCHEDULESEEDDATE,@INSTALLMENTAMOUNT, coalesce(@SPLITSCHEDULEOPTIONCODE, 0), @USERMODIFIEDBENEFITS, coalesce(@STANDINGORDERREFERENCENUMBER, '')
);
if @SPLITS is not null
exec dbo.USP_REVENUEBATCH_SPLITSWITHCHILDREN_UPDATEFROMXML @ID, @SPLITS, @DATE, @CHANGEAGENTID, @CURRENTDATE;
if not @ADDITIONALAPPLICATIONSSTREAM is null
exec dbo.USP_REVENUEBATCH_ADDITIONALAPPLICATIONWITHCHILDREN_UPDATEFROMXML @ID, @ADDITIONALAPPLICATIONSSTREAM, @DATE, @CHANGEAGENTID, @CURRENTDATE;
declare @APPLICATIONCODE tinyint;
declare @SINGLEAPPLICATIONID uniqueidentifier;
declare @ADDITIONALAPPLICATIONCOUNT int;
declare @SPLITCOUNT int;
if @APPLICATIONINFO is not null
if len(@APPLICATIONINFO) > 0
if len(@APPLICATIONINFO) = 3
set @APPLICATIONCODE = cast(substring(@APPLICATIONINFO, 3, 1) as tinyint);
select @ADDITIONALAPPLICATIONCOUNT = count(*) from @ADDITIONALAPPLICATIONSSTREAM.nodes('/ADDITIONALAPPLICATIONSSTREAM/ITEM') T(c)
select @SPLITCOUNT = count(*) from @SPLITS.nodes('/SPLITS/ITEM') T(c)
declare @REVENUESTREAMSCOUNT int;
select @REVENUESTREAMSCOUNT = count(*) from @REVENUESTREAMS.nodes('/REVENUESTREAMS/ITEM') T(c)
-- deal with payment payment application recognitions/solicitors
if @REVENUESTREAMSCOUNT > 0
exec dbo.USP_REVENUEUPDATEBATCH_ADDPAYMENTRECOGNITIONSANDSOLICITORS @ID, @APPLICATIONRECOGNITIONS, @APPLICATIONSOLICITORS, @REVENUESTREAMS, @CHANGEAGENTID, @CURRENTDATE
if @APPLICATIONCODE is not null and @SPLITCOUNT > 1 and @TYPECODE = 0
exec dbo.USP_REVENUEBATCH_ADDRECOGNITIONSANDSOLICITORS @ID, @APPLICATIONRECOGNITIONS, @APPLICATIONSOLICITORS, @SPLITS, @APPLICATIONCODE, NULL, @CHANGEAGENTID, @CURRENTDATE
else if @APPLICATIONCODE is not null and @ADDITIONALAPPLICATIONCOUNT >= 1 and @TYPECODE = 0
exec dbo.USP_REVENUEUPDATEBATCH_ADDRECOGNITIONSANDSOLICITORS @ID, @APPLICATIONRECOGNITIONS, @APPLICATIONSOLICITORS, @ADDITIONALAPPLICATIONSSTREAM, @APPLICATIONCODE, @CHANGEAGENTID, @CURRENTDATE;
else if @APPLICATIONCODE is null and @SINGLEAPPLICATIONID is null and @ADDITIONALAPPLICATIONCOUNT >= 1 and @TYPECODE = 0
exec dbo.USP_REVENUEUPDATEBATCH_ADDRECOGNITIONSANDSOLICITORS @ID, @APPLICATIONRECOGNITIONS, @APPLICATIONSOLICITORS, @ADDITIONALAPPLICATIONSSTREAM, NULL, @CHANGEAGENTID, @CURRENTDATE;
else
exec dbo.USP_REVENUEUPDATEBATCH_ADDRECOGNITIONSANDSOLICITORS @ID, @APPLICATIONRECOGNITIONS, @APPLICATIONSOLICITORS, @SPLITS, 0, @CHANGEAGENTID, @CURRENTDATE;
-- Add Revenue Benefits if they are exist, else appeal benefits, as it gets done in RUB.
if @USERMODIFIEDBENEFITS = 1 or @OVERWRITE = 0
begin
if not @REVENUEBENEFITS is null
exec dbo.USP_REVENUEBATCH_GETBENEFITS_ADDFROMXML @ID, @REVENUEBENEFITS, @CHANGEAGENTID;
if not @REVENUEPERCENTAGEBENEFITS is null
exec dbo.USP_REVENUEBATCH_GETBENEFITSPCT_ADDFROMXML @ID, @REVENUEPERCENTAGEBENEFITS, @CHANGEAGENTID;
end
else
begin
if not @APPEALBENEFITS is null
exec dbo.USP_REVENUEBATCH_GETBENEFITS_ADDFROMXML @ID, @APPEALBENEFITS, @CHANGEAGENTID;
if not @PERCENTAGEAPPEALBENEFITS is null
exec dbo.USP_REVENUEBATCH_GETBENEFITSPCT_ADDFROMXML @ID, @PERCENTAGEAPPEALBENEFITS, @CHANGEAGENTID;
end
if not @LETTERS is null
exec dbo.USP_REVENUEUPDATEBATCH_GETLETTERS_ADDFROMXML @ID, @LETTERS, @CHANGEAGENTID;
if not @NOTES is null
exec dbo.USP_REVENUEUPDATEBATCH_GETNOTES_ADDFROMXML @ID, @NOTES, @CHANGEAGENTID;
if not @INSTALLMENTS is null
begin
set @INSTALLMENTS = (select
case when T.c.value('(ID)[1]','uniqueidentifier') is null or T.c.value('(ID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000' then newid() else T.c.value('(ID)[1]','uniqueidentifier') end as ID,
T.c.value('(INSTALLMENTID)[1]','uniqueidentifier') AS 'INSTALLMENTID',
T.c.value('(DATE)[1]','datetime') AS 'DATE',
T.c.value('(AMOUNT)[1]','money') AS 'AMOUNT',
T.c.value('(BALANCE)[1]','money') AS 'BALANCE',
T.c.value('(APPLIED)[1]','money') AS 'APPLIED',
T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE',
T.c.value('(RECEIPTAMOUNT)[1]','money') AS 'RECEIPTAMOUNT',
(select
ID,
INSTALLMENTSPLITID,
AMOUNT,
APPLIED,
case when @OVERWRITE = 1 and @OVERWRITEFIELD = 'Designation' and DESIGNATIONID = @OLDGUIDVALUE then @NEWGUIDVALUE else DESIGNATIONID end as DESIGNATIONID
from dbo.UFN_REVENUEUPDATEBATCH_GETINSTALLMENTSPLITS_FROMITEMLISTXML(T.c.query('(INSTALLMENTSPLITS)[1]'))
for xml raw('ITEM'),type,elements,BINARY BASE64) as 'INSTALLMENTSPLITS'
from @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM') T(c)
for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),binary base64);
exec dbo.USP_REVENUEUPDATEBATCH_GETINSTALLMENTS_ADDFROMXML @ID, @INSTALLMENTS, @CHANGEAGENTID;
declare @BATCHINSTALLMENTID uniqueidentifier;
declare @INSTALLMENTSPLITS xml;
declare INSTALLMENTSPLITS cursor local fast_forward for
select
T.c.value('(ID)[1]','uniqueidentifier') as BATCHINSTALLMENTID,
cast(T.c.query('INSTALLMENTSPLITS') as xml) as INSTALLMENTSPLITS
from @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM') T(c)
open INSTALLMENTSPLITS
fetch next from INSTALLMENTSPLITS into @BATCHINSTALLMENTID, @INSTALLMENTSPLITS
while @@FETCH_STATUS = 0
begin
exec dbo.USP_REVENUEUPDATEBATCH_GETINSTALLMENTSPLITS_ADDFROMXML @BATCHINSTALLMENTID, @INSTALLMENTSPLITS, @CHANGEAGENTID;
fetch next from INSTALLMENTSPLITS into @BATCHINSTALLMENTID, @INSTALLMENTSPLITS
end
close INSTALLMENTSPLITS
deallocate INSTALLMENTSPLITS
end
if not @TRIBUTES is null
exec dbo.USP_REVENUEBATCH_GETTRIBUTES_ADDFROMXML @ID, @TRIBUTES, @CHANGEAGENTID;
if not @REVENUESTREAMS is null
exec dbo.USP_REVENUEBATCH_REVENUEAPPLICATIONS_ADDFROMXML_2 @ID, @REVENUESTREAMS, @CHANGEAGENTID, @CURRENTDATE, @CURRENTAPPUSERID;
exec dbo.USP_REVENUEBATCH_ENHANCEDMATCHINGGIFTSWITHCHILDREN_UPDATEFROMXML @ID, @MATCHINGGIFTS, @CHANGEAGENTID, @CURRENTDATE;
if @APPLICATIONBUSINESSUNITS is not null
exec dbo.USP_REVENUEBATCH_ADDBUSINESSUNITS @ID, @APPLICATIONBUSINESSUNITS, @TYPECODE
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
end