USP_DATAFORMTEMPLATE_EDIT_RECURRINGGIFTPAYMENTDETAILS_7
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @PAYMENTMETHODCODE | tinyint | IN | |
| @AUTOPAY | bit | IN | |
| @CARDHOLDERNAME | nvarchar(255) | IN | |
| @CREDITCARDNUMBER | nvarchar(20) | IN | |
| @CREDITTYPECODEID | uniqueidentifier | IN | |
| @EXPIRESON | UDT_FUZZYDATE | IN | |
| @REFERENCEDATE | UDT_FUZZYDATE | IN | |
| @REFERENCENUMBER | nvarchar(20) | IN | |
| @ACCOUNTID | uniqueidentifier | IN | |
| @CREDITCARDTOKEN | uniqueidentifier | IN | |
| @STANDINGORDERSETUP | bit | IN | |
| @STANDINGORDERSETUPDATE | datetime | IN | |
| @DDISOURCECODEID | uniqueidentifier | IN | |
| @DDISOURCEDATE | date | IN | |
| @SENDPMINSTRUCTION | bit | IN | |
| @PMINSTRUCTIONTOSENDCODE | tinyint | IN | |
| @PMINSTRUCTIONDATE_NEW | date | IN | |
| @PMINSTRUCTIONDATE_CANCEL | date | IN | |
| @PMINSTRUCTIONDATE_SETUP | date | IN | |
| @PMADVANCENOTICESENTDATE | date | IN | |
| @UPDATEOTHERTRANSACTIONSTOUSENEWCREDITCARD | bit | IN | |
| @USESYSTEMGENERATEDREFERENCENUMBER | bit | IN | |
| @STANDINGORDERREFERENCENUMBER | nvarchar(18) | IN | |
| @SEPAMANDATEID | uniqueidentifier | IN | |
| @OTHERPAYMENTMETHODCODEID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RECURRINGGIFTPAYMENTDETAILS_7
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@PAYMENTMETHODCODE tinyint,
@AUTOPAY bit,
@CARDHOLDERNAME nvarchar(255),
@CREDITCARDNUMBER nvarchar(20),
@CREDITTYPECODEID uniqueidentifier,
@EXPIRESON dbo.UDT_FUZZYDATE,
@REFERENCEDATE dbo.UDT_FUZZYDATE,
@REFERENCENUMBER nvarchar(20),
@ACCOUNTID uniqueidentifier,
@CREDITCARDTOKEN uniqueidentifier,
@STANDINGORDERSETUP bit,
@STANDINGORDERSETUPDATE datetime,
@DDISOURCECODEID uniqueidentifier,
@DDISOURCEDATE date,
@SENDPMINSTRUCTION bit,
@PMINSTRUCTIONTOSENDCODE tinyint,
@PMINSTRUCTIONDATE_NEW date,
@PMINSTRUCTIONDATE_CANCEL date,
@PMINSTRUCTIONDATE_SETUP date,
@PMADVANCENOTICESENTDATE date,
@UPDATEOTHERTRANSACTIONSTOUSENEWCREDITCARD bit,
@USESYSTEMGENERATEDREFERENCENUMBER bit,
@STANDINGORDERREFERENCENUMBER nvarchar(18),
@SEPAMANDATEID uniqueidentifier,
@OTHERPAYMENTMETHODCODEID uniqueidentifier
)
as
set nocount on;
declare @CURRENTDATE datetime;
declare @PREVIOUSPAYMENTMETHODCODE tinyint;
declare @HASEDITS bit = 0;
begin try
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
-- store the previous payment method to be used in the RG activity timeline
select @PREVIOUSPAYMENTMETHODCODE = case when (PAYMENTMETHODCODE = 2 and REVENUESCHEDULE.CREDITCARDID is null) then 98 else PAYMENTMETHODCODE end
from dbo.REVENUEPAYMENTMETHOD
left join dbo.REVENUESCHEDULE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUESCHEDULE.ID
where REVENUEPAYMENTMETHOD.REVENUEID = @ID
-- capture previous payment method information to be recorded in the RG amendment table.
--previous CC fields
declare @PREVIOUSCREDITCARDPARTIALNUMBER nvarchar(20);
declare @PREVIOUSCREDITTYPECODEID uniqueidentifier;
declare @PREVIOUSEXPIRESON dbo.UDT_FUZZYDATE;
declare @PREVIOUSCARDHOLDERNAME nvarchar(255);
-- previous DD and other fields
declare @PREVIOUSCONSTITUENTACCOUNTID uniqueidentifier;
declare @PREVIOUSREFERENCEDATE dbo.UDT_FUZZYDATE;
declare @PREVIOUSREFERENCENUMBER nvarchar(20);
declare @PREVIOUSOTHERPAYMENTMETHODCODEID uniqueidentifier;
-- previous UK/SEPA fields
declare @PREVIOUSDDISOURCECODEID uniqueidentifier;
declare @PREVIOUSDDISOURCEDATE date;
declare @PREVIOUSSENDPMINSTRUCTION bit;
declare @PREVIOUSPMINSTRUCTIONTOSENDCODE tinyint;
declare @PREVIOUSPMINSTRUCTIONDATE_NEW date;
declare @PREVIOUSPMINSTRUCTIONDATE_CANCEL date;
declare @PREVIOUSPMINSTRUCTIONDATE_SETUP date;
declare @PREVIOUSPMADVANCENOTICESENTDATE date;
declare @PREVIOUSSEPAMANDATEID uniqueidentifier;
-- previous Standing Order fields
declare @PREVIOUSSTANDINGORDERSETUP bit;
declare @PREVIOUSSTANDINGORDERSETUPDATE date;
declare @PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER bit;
if @PAYMENTMETHODCODE <> 2 --Credit card
set @CREDITCARDTOKEN = null
-- Have to set @PREVIOUSCREDITCARDID before setting REVENUESCHEDULE.CREDITCARDID to null
-- so that updating other card numbers works when changing to a partial number.
-- populate the previous fields for CC when the paymentmethod code is 2 or the previous code was 2
declare @PREVIOUSCREDITCARDID uniqueidentifier
if (@UPDATEOTHERTRANSACTIONSTOUSENEWCREDITCARD = 1 and @PAYMENTMETHODCODE = 2) or @PREVIOUSPAYMENTMETHODCODE = 2-- Credit card
begin
select
@PREVIOUSCREDITCARDID = REVENUESCHEDULE.CREDITCARDID,
@PREVIOUSCREDITCARDPARTIALNUMBER = [CARD].CREDITCARDPARTIALNUMBER,
@PREVIOUSCREDITTYPECODEID = [CARD].CREDITTYPECODEID,
@PREVIOUSEXPIRESON = [CARD].EXPIRESON,
@PREVIOUSCARDHOLDERNAME = [CARD].CARDHOLDERNAME
from dbo.REVENUESCHEDULE
left outer join dbo.CREDITCARD as [CARD] on REVENUESCHEDULE.CREDITCARDID = CARD.ID
where REVENUESCHEDULE.ID = @ID
end
-- populate the previous fields for DD when the paymentmethod code is 3 or the previous code was 3
if @PREVIOUSPAYMENTMETHODCODE = 3
select
@PREVIOUSCONSTITUENTACCOUNTID = REVENUESCHEDULEDIRECTDEBITPAYMENT.CONSTITUENTACCOUNTID,
@PREVIOUSREFERENCEDATE = REVENUESCHEDULEDIRECTDEBITPAYMENT.REFERENCEDATE,
@PREVIOUSREFERENCENUMBER = REVENUESCHEDULEDIRECTDEBITPAYMENT.REFERENCENUMBER,
@PREVIOUSDDISOURCECODEID = REVENUESCHEDULEDIRECTDEBITPAYMENT.DDISOURCECODEID,
@PREVIOUSDDISOURCEDATE = REVENUESCHEDULEDIRECTDEBITPAYMENT.DDISOURCEDATE,
@PREVIOUSSENDPMINSTRUCTION = REVENUESCHEDULEDIRECTDEBITPAYMENT.SENDPMINSTRUCTION,
@PREVIOUSPMINSTRUCTIONTOSENDCODE = REVENUESCHEDULEDIRECTDEBITPAYMENT.PMINSTRUCTIONTOSENDCODE,
@PREVIOUSPMINSTRUCTIONDATE_NEW = REVENUESCHEDULEDIRECTDEBITPAYMENT.PMINSTRUCTIONDATE_NEW,
@PREVIOUSPMINSTRUCTIONDATE_CANCEL = REVENUESCHEDULEDIRECTDEBITPAYMENT.PMINSTRUCTIONDATE_CANCEL,
@PREVIOUSPMINSTRUCTIONDATE_SETUP = REVENUESCHEDULEDIRECTDEBITPAYMENT.PMINSTRUCTIONDATE_SETUP,
@PREVIOUSPMADVANCENOTICESENTDATE = REVENUESCHEDULEDIRECTDEBITPAYMENT.PMADVANCENOTICESENTDATE,
@PREVIOUSSEPAMANDATEID = REVENUESCHEDULEDIRECTDEBITPAYMENT.SEPAMANDATEID
from dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT
where REVENUESCHEDULEDIRECTDEBITPAYMENT.ID = @ID
-- populate the previous fields for other method when the previous code was 10
else if @PREVIOUSPAYMENTMETHODCODE = 10
select
@PREVIOUSOTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID,
@PREVIOUSREFERENCEDATE = OTHERPAYMENTMETHODDETAIL.REFERENCEDATE,
@PREVIOUSREFERENCENUMBER = OTHERPAYMENTMETHODDETAIL.REFERENCENUMBER
from dbo.OTHERPAYMENTMETHODDETAIL
inner join dbo.REVENUEPAYMENTMETHOD on OTHERPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
where REVENUEPAYMENTMETHOD.REVENUEID = @ID
-- populate the previous fields for the credit card reference only method when the previous code was 98
else if @PREVIOUSPAYMENTMETHODCODE = 98
select
@PREVIOUSCREDITCARDPARTIALNUMBER = D.CREDITCARDPARTIALNUMBER,
@PREVIOUSCREDITTYPECODEID = D.CREDITTYPECODEID,
@PREVIOUSEXPIRESON = D.EXPIRESON,
@PREVIOUSCARDHOLDERNAME = D.CARDHOLDERNAME
from dbo.REVENUEPAYMENTMETHOD M
left join dbo.CREDITCARDPAYMENTMETHODDETAIL D on M.ID = D.ID
where M.REVENUEID = @ID;
-- populate the previous fields for Standing Order when the paymentmethod code is 11 or the previous code was 11
else if @PREVIOUSPAYMENTMETHODCODE = 11
select
@PREVIOUSREFERENCEDATE = REFERENCEDATE,
@PREVIOUSREFERENCENUMBER = case when USESYSTEMGENERATEDREFERENCENUMBER = 1 then dbo.UFN_STANDINGORDER_REFERENCENUMBER_FORDISPLAY(@ID) else REVENUESCHEDULESTANDINGORDERPAYMENT.REFERENCENUMBER end ,
@PREVIOUSCONSTITUENTACCOUNTID = CONSTITUENTACCOUNTID,
@PREVIOUSSTANDINGORDERSETUP = STANDINGORDERSETUP,
@PREVIOUSSTANDINGORDERSETUPDATE = STANDINGORDERSETUPDATE,
@PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER = case when USESYSTEMGENERATEDREFERENCENUMBER is null then 0 else USESYSTEMGENERATEDREFERENCENUMBER end
from dbo.REVENUESCHEDULESTANDINGORDERPAYMENT
left join dbo.REVENUESTANDINGORDER on REVENUESTANDINGORDER.ID = REVENUESCHEDULESTANDINGORDERPAYMENT.ID
where REVENUESCHEDULESTANDINGORDERPAYMENT.ID = @ID
if @CREDITCARDTOKEN is null
update dbo.REVENUESCHEDULE set
CREDITCARDID = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID
if @PREVIOUSPAYMENTMETHODCODE <> @PAYMENTMETHODCODE
set @HASEDITS = 1;
update dbo.REVENUEPAYMENTMETHOD
set PAYMENTMETHODCODE = case when @PAYMENTMETHODCODE = 98 then 2 else @PAYMENTMETHODCODE end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where REVENUEID = @ID;
if @PAYMENTMETHODCODE = 2 --Credit debit
begin
declare @CREDITCARDID uniqueidentifier
-- record changes in the RG amendment table
if (@HASEDITS = 1 and @PREVIOUSPAYMENTMETHODCODE <> 98) or
(@PREVIOUSCREDITCARDPARTIALNUMBER <> @CREDITCARDNUMBER and (isnull(@PREVIOUSCREDITCARDPARTIALNUMBER,'') <> '' or isnull(@CREDITCARDNUMBER,'') <> '')) or
(coalesce(@PREVIOUSCREDITTYPECODEID,'00000000-0000-0000-0000-000000000000') <> coalesce(@CREDITTYPECODEID,'00000000-0000-0000-0000-000000000000') and
(@PREVIOUSCREDITTYPECODEID is not null or @CREDITTYPECODEID is not null)) or
(@PREVIOUSEXPIRESON <> @EXPIRESON and (isnull(@PREVIOUSEXPIRESON,'00000000') <> '00000000' or isnull(@EXPIRESON,'00000000') <> '00000000')) or
(@PREVIOUSCARDHOLDERNAME <> @CARDHOLDERNAME and (isnull(@PREVIOUSCARDHOLDERNAME,'') <> '' or isnull(@CARDHOLDERNAME,'') <> ''))
begin
insert into dbo.RECURRINGGIFTAMENDMENT(ID,FINANCIALTRANSACTIONID,AMENDMENTTYPECODE,DATE,PAYMENTMETHODCODE,PREVIOUSPAYMENTMETHODCODE,
CREDITCARDPARTIALNUMBER,PREVIOUSCREDITCARDPARTIALNUMBER,CREDITTYPECODEID,PREVIOUSCREDITTYPECODEID,
EXPIRESON,PREVIOUSEXPIRESON,CARDHOLDERNAME,PREVIOUSCARDHOLDERNAME,PREVIOUSCONSTITUENTACCOUNTID,PREVIOUSREFERENCEDATE,
PREVIOUSREFERENCENUMBER,PREVIOUSOTHERPAYMENTMETHODCODEID, ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,
PREVIOUSSTANDINGORDERSETUP,PREVIOUSSTANDINGORDERSETUPDATE,PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER,
PREVIOUSDDISOURCECODEID,PREVIOUSDDISOURCEDATE,PREVIOUSSENDPMINSTRUCTION,PREVIOUSPMINSTRUCTIONTOSENDCODE,PREVIOUSPMINSTRUCTIONDATE_NEW,
PREVIOUSPMINSTRUCTIONDATE_CANCEL,PREVIOUSPMINSTRUCTIONDATE_SETUP,PREVIOUSPMADVANCENOTICESENTDATE,PREVIOUSSEPAMANDATEID)
values(newid(), @ID,3,@CURRENTDATE,@PAYMENTMETHODCODE,@PREVIOUSPAYMENTMETHODCODE,
@CREDITCARDNUMBER,isnull(@PREVIOUSCREDITCARDPARTIALNUMBER,''), @CREDITTYPECODEID,@PREVIOUSCREDITTYPECODEID,@EXPIRESON,
isnull(@PREVIOUSEXPIRESON,'00000000'),@CARDHOLDERNAME,isnull(@PREVIOUSCARDHOLDERNAME,''),@PREVIOUSCONSTITUENTACCOUNTID,isnull(@PREVIOUSREFERENCEDATE,'00000000'),
isnull(@PREVIOUSREFERENCENUMBER,''),@PREVIOUSOTHERPAYMENTMETHODCODEID, @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,
isnull(@PREVIOUSSTANDINGORDERSETUP,0),@PREVIOUSSTANDINGORDERSETUPDATE,isnull(@PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER,0),
@PREVIOUSDDISOURCECODEID,@PREVIOUSDDISOURCEDATE,isnull(@PREVIOUSSENDPMINSTRUCTION,0),isnull(@PREVIOUSPMINSTRUCTIONTOSENDCODE,0),@PREVIOUSPMINSTRUCTIONDATE_NEW,
@PREVIOUSPMINSTRUCTIONDATE_CANCEL,@PREVIOUSPMINSTRUCTIONDATE_SETUP,@PREVIOUSPMADVANCENOTICESENTDATE,@PREVIOUSSEPAMANDATEID)
end
exec dbo.USP_CREDITCARD_SAVE
@ID = @CREDITCARDID output,
@CREDITCARDTOKEN = @CREDITCARDTOKEN,
@CARDHOLDERNAME = @CARDHOLDERNAME,
@CREDITCARDPARTIALNUMBER = @CREDITCARDNUMBER,
@CREDITTYPECODEID = @CREDITTYPECODEID,
@EXPIRESON = @EXPIRESON,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTDATE = @CURRENTDATE
update dbo.REVENUESCHEDULE set
CREDITCARDID = @CREDITCARDID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID
-- Update other pledges and recurring gifts that previously used the old card
-- to now use the new card.
-- @PREVIOUSCREDITCARDID can only be set if @UPDATEOTHERTRANSACTIONSTOUSENEWCREDITCARD is true
if @PREVIOUSCREDITCARDID is not null and @UPDATEOTHERTRANSACTIONSTOUSENEWCREDITCARD = 1
begin
update dbo.REVENUESCHEDULE set
CREDITCARDID = @CREDITCARDID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where CREDITCARDID = @PREVIOUSCREDITCARDID
update dbo.BATCHREVENUE set
CREDITCARDID = @CREDITCARDID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
CREDITCARDID = @PREVIOUSCREDITCARDID and
BATCHREVENUE.AUTHORIZATIONCODE = N'' and --Bug 234607 Don't change authorized cards because they have already been charged
(select top 1 STATUSCODE
from dbo.BATCH
where
ID = BATCHREVENUE.BATCHID) = 0 -- Uncommitted
end
end
else
update dbo.REVENUESCHEDULE set
CREDITCARDID = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID
if @PAYMENTMETHODCODE = 3 --Direct debit
begin
if @HASEDITS = 1 or coalesce(@PREVIOUSCONSTITUENTACCOUNTID,'00000000-0000-0000-0000-000000000000') <> coalesce(@ACCOUNTID,'00000000-0000-0000-0000-000000000000') or
isnull(@PREVIOUSREFERENCEDATE,'00000000') <> isnull(@REFERENCEDATE,'00000000') or
isnull(@PREVIOUSREFERENCENUMBER,'') <> isnull(@REFERENCENUMBER,'') or
coalesce(@PREVIOUSDDISOURCECODEID,'00000000-0000-0000-0000-000000000000') <> coalesce(@DDISOURCECODEID,'00000000-0000-0000-0000-000000000000') or
isnull(@PREVIOUSDDISOURCEDATE,'') <> isnull(@PREVIOUSDDISOURCEDATE,'') or
@PREVIOUSSENDPMINSTRUCTION <> @SENDPMINSTRUCTION or
@PREVIOUSPMINSTRUCTIONTOSENDCODE <> @PMINSTRUCTIONTOSENDCODE or
isnull(@PREVIOUSPMINSTRUCTIONDATE_NEW,'') <> isnull(@PMINSTRUCTIONDATE_NEW,'') or
isnull(@PREVIOUSPMINSTRUCTIONDATE_CANCEL,'') <> isnull(@PMINSTRUCTIONDATE_CANCEL,'') or
isnull(@PREVIOUSPMINSTRUCTIONDATE_SETUP,'') <> isnull(@PMINSTRUCTIONDATE_SETUP,'') or
isnull(@PREVIOUSPMADVANCENOTICESENTDATE,'') <> isnull(@PMADVANCENOTICESENTDATE,'') or
coalesce(@PREVIOUSSEPAMANDATEID,'00000000-0000-0000-0000-000000000000') <> coalesce(@SEPAMANDATEID,'00000000-0000-0000-0000-000000000000')
begin
insert into dbo.RECURRINGGIFTAMENDMENT(ID,FINANCIALTRANSACTIONID,AMENDMENTTYPECODE,DATE,PAYMENTMETHODCODE,PREVIOUSPAYMENTMETHODCODE,
CONSTITUENTACCOUNTID, PREVIOUSCONSTITUENTACCOUNTID,REFERENCEDATE,PREVIOUSREFERENCEDATE,
REFERENCENUMBER,PREVIOUSREFERENCENUMBER,PREVIOUSCREDITCARDPARTIALNUMBER,PREVIOUSCREDITTYPECODEID,PREVIOUSEXPIRESON,
PREVIOUSCARDHOLDERNAME,PREVIOUSOTHERPAYMENTMETHODCODEID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,
PREVIOUSSTANDINGORDERSETUP,PREVIOUSSTANDINGORDERSETUPDATE,PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER,
DDISOURCECODEID,PREVIOUSDDISOURCECODEID,DDISOURCEDATE,PREVIOUSDDISOURCEDATE,SENDPMINSTRUCTION,PREVIOUSSENDPMINSTRUCTION,
PMINSTRUCTIONTOSENDCODE,PREVIOUSPMINSTRUCTIONTOSENDCODE,PMINSTRUCTIONDATE_NEW,PREVIOUSPMINSTRUCTIONDATE_NEW,
PMINSTRUCTIONDATE_CANCEL,PREVIOUSPMINSTRUCTIONDATE_CANCEL,PMINSTRUCTIONDATE_SETUP,PREVIOUSPMINSTRUCTIONDATE_SETUP,
PMADVANCENOTICESENTDATE,PREVIOUSPMADVANCENOTICESENTDATE,SEPAMANDATEID, PREVIOUSSEPAMANDATEID)
values(newid(), @ID,3,@CURRENTDATE,@PAYMENTMETHODCODE,@PREVIOUSPAYMENTMETHODCODE,@ACCOUNTID,@PREVIOUSCONSTITUENTACCOUNTID,
isnull(@REFERENCEDATE,'00000000'),isnull(@PREVIOUSREFERENCEDATE,'00000000'),isnull(@REFERENCENUMBER,''),isnull(@PREVIOUSREFERENCENUMBER,''),
isnull(@PREVIOUSCREDITCARDPARTIALNUMBER,''),@PREVIOUSCREDITTYPECODEID,isnull(@PREVIOUSEXPIRESON,'00000000'),isnull(@PREVIOUSCARDHOLDERNAME,''),@PREVIOUSOTHERPAYMENTMETHODCODEID,
@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,isnull(@PREVIOUSSTANDINGORDERSETUP,0),@PREVIOUSSTANDINGORDERSETUPDATE,isnull(@PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER,0),
@DDISOURCECODEID,@PREVIOUSDDISOURCECODEID,@DDISOURCEDATE,@PREVIOUSDDISOURCEDATE,isnull(@SENDPMINSTRUCTION,0),isnull(@PREVIOUSSENDPMINSTRUCTION,0),isnull(@PMINSTRUCTIONTOSENDCODE,0),
isnull(@PREVIOUSPMINSTRUCTIONTOSENDCODE,0),@PMINSTRUCTIONDATE_NEW,@PREVIOUSPMINSTRUCTIONDATE_NEW,@PMINSTRUCTIONDATE_CANCEL,@PREVIOUSPMINSTRUCTIONDATE_CANCEL,
@PMINSTRUCTIONDATE_SETUP,@PREVIOUSPMINSTRUCTIONDATE_SETUP,@PMADVANCENOTICESENTDATE,@PREVIOUSPMADVANCENOTICESENTDATE,@SEPAMANDATEID, @PREVIOUSSEPAMANDATEID)
end
--Direct Debit w/ Paperless mandate fields is UK only
if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
begin
if @PMINSTRUCTIONTOSENDCODE is null
set @PMINSTRUCTIONTOSENDCODE = 0;
if @SENDPMINSTRUCTION is null
set @SENDPMINSTRUCTION = 0;
update dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT
set
REFERENCEDATE = @REFERENCEDATE,
REFERENCENUMBER = @REFERENCENUMBER,
CONSTITUENTACCOUNTID = @ACCOUNTID,
DDISOURCECODEID = @DDISOURCECODEID,
DDISOURCEDATE = @DDISOURCEDATE,
SENDPMINSTRUCTION = @SENDPMINSTRUCTION,
PMINSTRUCTIONTOSENDCODE = case
when @SENDPMINSTRUCTION = 0 then 0
else @PMINSTRUCTIONTOSENDCODE
end,
PMINSTRUCTIONDATE_NEW = @PMINSTRUCTIONDATE_NEW,
PMINSTRUCTIONDATE_CANCEL = @PMINSTRUCTIONDATE_CANCEL,
PMINSTRUCTIONDATE_SETUP = @PMINSTRUCTIONDATE_SETUP,
PMADVANCENOTICESENTDATE = @PMADVANCENOTICESENTDATE,
SEPAMANDATEID = @SEPAMANDATEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
if @@ROWCOUNT = 0
insert into dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT
(
ID,
REFERENCEDATE,
REFERENCENUMBER,
CONSTITUENTACCOUNTID,
DDISOURCECODEID,
DDISOURCEDATE,
SENDPMINSTRUCTION,
PMINSTRUCTIONTOSENDCODE,
PMINSTRUCTIONDATE_NEW,
PMINSTRUCTIONDATE_CANCEL,
PMINSTRUCTIONDATE_SETUP,
PMADVANCENOTICESENTDATE,
SEPAMANDATEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)values(
@ID,
@REFERENCEDATE,
@REFERENCENUMBER,
@ACCOUNTID,
@DDISOURCECODEID,
@DDISOURCEDATE,
@SENDPMINSTRUCTION,
case
when @SENDPMINSTRUCTION = 0 then 0
else @PMINSTRUCTIONTOSENDCODE
end,
@PMINSTRUCTIONDATE_NEW,
@PMINSTRUCTIONDATE_CANCEL,
@PMINSTRUCTIONDATE_SETUP,
@PMADVANCENOTICESENTDATE,
@SEPAMANDATEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
if dbo.UFN_PAPERLESSMANDATES_DAYSADVANCEDNOTICE_FORREVENUE(@ID) = -1
begin
--Bug 138736 - AdamBu 2/2/11 - Error on invalid paperless mandate setup.
raiserror('BBERR_MULTIPLE_PAPERLESSMANDATECONFIGS', 13, 1);
return 1;
end
end
else
begin
update dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT
set
REFERENCEDATE = @REFERENCEDATE,
REFERENCENUMBER = @REFERENCENUMBER,
CONSTITUENTACCOUNTID = @ACCOUNTID,
SEPAMANDATEID = @SEPAMANDATEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
if @@ROWCOUNT = 0
insert into dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT
(ID, REFERENCEDATE, REFERENCENUMBER, CONSTITUENTACCOUNTID, SEPAMANDATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (@ID, @REFERENCEDATE, @REFERENCENUMBER, @ACCOUNTID, @SEPAMANDATEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
end
else
exec dbo.USP_REVENUESCHEDULEDIRECTDEBITPAYMENT_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
if @PAYMENTMETHODCODE = 11 --Standing order
begin
if @USESYSTEMGENERATEDREFERENCENUMBER is null
begin
set @STANDINGORDERREFERENCENUMBER = '';
set @USESYSTEMGENERATEDREFERENCENUMBER = 1;
end
if @STANDINGORDERSETUP = 0
set @STANDINGORDERSETUPDATE = null;
update dbo.REVENUESCHEDULESTANDINGORDERPAYMENT
set
REFERENCEDATE = @REFERENCEDATE,
CONSTITUENTACCOUNTID = @ACCOUNTID,
STANDINGORDERSETUP = @STANDINGORDERSETUP,
STANDINGORDERSETUPDATE = @STANDINGORDERSETUPDATE,
REFERENCENUMBER = @STANDINGORDERREFERENCENUMBER,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
if @@ROWCOUNT = 0
insert into dbo.REVENUESCHEDULESTANDINGORDERPAYMENT
(ID, REFERENCEDATE, REFERENCENUMBER, CONSTITUENTACCOUNTID, STANDINGORDERSETUP, STANDINGORDERSETUPDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (@ID, @REFERENCEDATE, @STANDINGORDERREFERENCENUMBER, @ACCOUNTID, @STANDINGORDERSETUP, @STANDINGORDERSETUPDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
update dbo.REVENUESTANDINGORDER
set
CUSTOMREFERENCENUMBER = @STANDINGORDERREFERENCENUMBER,
USESYSTEMGENERATEDREFERENCENUMBER = @USESYSTEMGENERATEDREFERENCENUMBER,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
if @@ROWCOUNT = 0
insert into dbo.REVENUESTANDINGORDER(ID, CUSTOMREFERENCENUMBER, USESYSTEMGENERATEDREFERENCENUMBER, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(@ID, @STANDINGORDERREFERENCENUMBER, @USESYSTEMGENERATEDREFERENCENUMBER, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
-- determine the system-generated reference number
if @USESYSTEMGENERATEDREFERENCENUMBER = 1
select @STANDINGORDERREFERENCENUMBER = right('000000000000000000' + cast(cast(REFERENCENUMBER as nvarchar(18)) + 1 as nvarchar(18)), 18)
from dbo.REVENUESTANDINGORDER where ID = @ID;
if @HASEDITS = 1 or coalesce(@PREVIOUSCONSTITUENTACCOUNTID,'00000000-0000-0000-0000-000000000000') <> coalesce(@ACCOUNTID,'00000000-0000-0000-0000-000000000000') or
isnull(@PREVIOUSREFERENCEDATE,'00000000') <> isnull(@REFERENCEDATE,'00000000') or
isnull(@PREVIOUSREFERENCENUMBER,'') <> isnull(@STANDINGORDERREFERENCENUMBER,'') or
@PREVIOUSSTANDINGORDERSETUP <> @STANDINGORDERSETUP or
isnull(@PREVIOUSSTANDINGORDERSETUPDATE, '') <> isnull(@STANDINGORDERSETUPDATE, '') or
@PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER <> @USESYSTEMGENERATEDREFERENCENUMBER
begin
insert into dbo.RECURRINGGIFTAMENDMENT(ID,FINANCIALTRANSACTIONID,AMENDMENTTYPECODE,DATE,PAYMENTMETHODCODE,PREVIOUSPAYMENTMETHODCODE,
CONSTITUENTACCOUNTID, PREVIOUSCONSTITUENTACCOUNTID,REFERENCEDATE,PREVIOUSREFERENCEDATE,REFERENCENUMBER,
PREVIOUSREFERENCENUMBER,PREVIOUSCREDITCARDPARTIALNUMBER,PREVIOUSCREDITTYPECODEID,PREVIOUSEXPIRESON,PREVIOUSCARDHOLDERNAME,
PREVIOUSOTHERPAYMENTMETHODCODEID,STANDINGORDERSETUP,PREVIOUSSTANDINGORDERSETUP,STANDINGORDERSETUPDATE,
PREVIOUSSTANDINGORDERSETUPDATE,USESYSTEMGENERATEDREFERENCENUMBER,PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER,
PREVIOUSDDISOURCECODEID,PREVIOUSDDISOURCEDATE,PREVIOUSSENDPMINSTRUCTION,PREVIOUSPMINSTRUCTIONTOSENDCODE,
PREVIOUSPMINSTRUCTIONDATE_NEW,PREVIOUSPMINSTRUCTIONDATE_CANCEL,PREVIOUSPMINSTRUCTIONDATE_SETUP,
PREVIOUSPMADVANCENOTICESENTDATE,PREVIOUSSEPAMANDATEID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
values(newid(), @ID,3,@CURRENTDATE,@PAYMENTMETHODCODE,@PREVIOUSPAYMENTMETHODCODE,@ACCOUNTID,@PREVIOUSCONSTITUENTACCOUNTID,
isnull(@REFERENCEDATE,'00000000'),isnull(@PREVIOUSREFERENCEDATE,'00000000'),isnull(@STANDINGORDERREFERENCENUMBER,''),isnull(@PREVIOUSREFERENCENUMBER,''),
isnull(@PREVIOUSCREDITCARDPARTIALNUMBER,''),@PREVIOUSCREDITTYPECODEID,isnull(@PREVIOUSEXPIRESON,'00000000'),isnull(@PREVIOUSCARDHOLDERNAME,''),@PREVIOUSOTHERPAYMENTMETHODCODEID,
@STANDINGORDERSETUP,isnull(@PREVIOUSSTANDINGORDERSETUP,0),@STANDINGORDERSETUPDATE,@PREVIOUSSTANDINGORDERSETUPDATE,@USESYSTEMGENERATEDREFERENCENUMBER,isnull(@PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER,0),
@PREVIOUSDDISOURCECODEID,@PREVIOUSDDISOURCEDATE,isnull(@PREVIOUSSENDPMINSTRUCTION,0),isnull(@PREVIOUSPMINSTRUCTIONTOSENDCODE,0),@PREVIOUSPMINSTRUCTIONDATE_NEW,@PREVIOUSPMINSTRUCTIONDATE_CANCEL,
@PREVIOUSPMINSTRUCTIONDATE_SETUP,@PREVIOUSPMADVANCENOTICESENTDATE,@PREVIOUSSEPAMANDATEID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE)
end
end
else
begin
exec dbo.USP_REVENUESTANDINGORDER_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
exec dbo.USP_REVENUESCHEDULESTANDINGORDERPAYMENT_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
end
if @PAYMENTMETHODCODE = 10 --Other method
begin
if @HASEDITS = 1 or coalesce(@PREVIOUSOTHERPAYMENTMETHODCODEID,'00000000-0000-0000-0000-000000000000') <> coalesce(@OTHERPAYMENTMETHODCODEID,'00000000-0000-0000-0000-000000000000') or
isnull(@PREVIOUSREFERENCEDATE,'00000000') <> isnull(@REFERENCEDATE,'00000000') or
isnull(@PREVIOUSREFERENCENUMBER,'') <> isnull(@REFERENCENUMBER,'')
begin
insert into dbo.RECURRINGGIFTAMENDMENT(ID,FINANCIALTRANSACTIONID,AMENDMENTTYPECODE,DATE,PAYMENTMETHODCODE,PREVIOUSPAYMENTMETHODCODE,
OTHERPAYMENTMETHODCODEID,PREVIOUSOTHERPAYMENTMETHODCODEID,REFERENCEDATE,PREVIOUSREFERENCEDATE,
REFERENCENUMBER,PREVIOUSREFERENCENUMBER, PREVIOUSCREDITCARDPARTIALNUMBER,PREVIOUSCREDITTYPECODEID,
PREVIOUSEXPIRESON,PREVIOUSCARDHOLDERNAME,PREVIOUSCONSTITUENTACCOUNTID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,
PREVIOUSSTANDINGORDERSETUP,PREVIOUSSTANDINGORDERSETUPDATE,PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER,
PREVIOUSDDISOURCECODEID,PREVIOUSDDISOURCEDATE,PREVIOUSSENDPMINSTRUCTION,PREVIOUSPMINSTRUCTIONTOSENDCODE,PREVIOUSPMINSTRUCTIONDATE_NEW,
PREVIOUSPMINSTRUCTIONDATE_CANCEL,PREVIOUSPMINSTRUCTIONDATE_SETUP,PREVIOUSPMADVANCENOTICESENTDATE,PREVIOUSSEPAMANDATEID)
values(newid(), @ID,3,@CURRENTDATE,@PAYMENTMETHODCODE,@PREVIOUSPAYMENTMETHODCODE,@OTHERPAYMENTMETHODCODEID,@PREVIOUSOTHERPAYMENTMETHODCODEID,
isnull(@REFERENCEDATE,'00000000'),isnull(@PREVIOUSREFERENCEDATE,'00000000'),isnull(@REFERENCENUMBER,''),isnull(@PREVIOUSREFERENCENUMBER,''),
isnull(@PREVIOUSCREDITCARDPARTIALNUMBER,''),@PREVIOUSCREDITTYPECODEID,isnull(@PREVIOUSEXPIRESON,'00000000'),isnull(@PREVIOUSCARDHOLDERNAME,''),@PREVIOUSCONSTITUENTACCOUNTID,
@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,isnull(@PREVIOUSSTANDINGORDERSETUP,0),@PREVIOUSSTANDINGORDERSETUPDATE,isnull(@PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER,0),
@PREVIOUSDDISOURCECODEID,@PREVIOUSDDISOURCEDATE,isnull(@PREVIOUSSENDPMINSTRUCTION,0),isnull(@PREVIOUSPMINSTRUCTIONTOSENDCODE,0),@PREVIOUSPMINSTRUCTIONDATE_NEW,
@PREVIOUSPMINSTRUCTIONDATE_CANCEL,@PREVIOUSPMINSTRUCTIONDATE_SETUP,@PREVIOUSPMADVANCENOTICESENTDATE,@PREVIOUSSEPAMANDATEID)
end
update dbo.OTHERPAYMENTMETHODDETAIL
set
REFERENCEDATE = @REFERENCEDATE,
REFERENCENUMBER = @REFERENCENUMBER,
OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from dbo.OTHERPAYMENTMETHODDETAIL O
inner join dbo.REVENUEPAYMENTMETHOD R on O.ID = R.ID
where R.REVENUEID = @ID;
if @@ROWCOUNT = 0
insert into dbo.OTHERPAYMENTMETHODDETAIL
(ID, REFERENCEDATE, REFERENCENUMBER, OTHERPAYMENTMETHODCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select ID, @REFERENCEDATE, @REFERENCENUMBER, @OTHERPAYMENTMETHODCODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.REVENUEPAYMENTMETHOD where REVENUEID = @ID;
end
else
delete from dbo.OTHERPAYMENTMETHODDETAIL where ID in (select ID from dbo.REVENUEPAYMENTMETHOD where REVENUEID = @ID);
if @PAYMENTMETHODCODE = 98 --Credit 4 digits
begin
-- record changes in the RG amendment table
if @HASEDITS = 1 or
(@PREVIOUSCREDITCARDPARTIALNUMBER <> @CREDITCARDNUMBER and (isnull(@PREVIOUSCREDITCARDPARTIALNUMBER,'') <> '' or isnull(@CREDITCARDNUMBER,'') <> '')) or
(coalesce(@PREVIOUSCREDITTYPECODEID,'00000000-0000-0000-0000-000000000000') <> coalesce(@CREDITTYPECODEID,'00000000-0000-0000-0000-000000000000') and
(@PREVIOUSCREDITTYPECODEID is not null or @CREDITTYPECODEID is not null)) or
(@PREVIOUSEXPIRESON <> @EXPIRESON and (isnull(@PREVIOUSEXPIRESON,'00000000') <> '00000000' or isnull(@EXPIRESON,'00000000') <> '00000000')) or
(@PREVIOUSCARDHOLDERNAME <> @CARDHOLDERNAME and (isnull(@PREVIOUSCARDHOLDERNAME,'') <> '' or isnull(@CARDHOLDERNAME,'') <> ''))
begin
insert into dbo.RECURRINGGIFTAMENDMENT(ID,FINANCIALTRANSACTIONID,AMENDMENTTYPECODE,DATE,PAYMENTMETHODCODE,PREVIOUSPAYMENTMETHODCODE,
CREDITCARDPARTIALNUMBER,PREVIOUSCREDITCARDPARTIALNUMBER,CREDITTYPECODEID,PREVIOUSCREDITTYPECODEID,
EXPIRESON,PREVIOUSEXPIRESON,CARDHOLDERNAME,PREVIOUSCARDHOLDERNAME,PREVIOUSCONSTITUENTACCOUNTID,PREVIOUSREFERENCEDATE,
PREVIOUSREFERENCENUMBER,PREVIOUSOTHERPAYMENTMETHODCODEID, ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,
PREVIOUSSTANDINGORDERSETUP,PREVIOUSSTANDINGORDERSETUPDATE,PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER,
PREVIOUSDDISOURCECODEID,PREVIOUSDDISOURCEDATE,PREVIOUSSENDPMINSTRUCTION,PREVIOUSPMINSTRUCTIONTOSENDCODE,PREVIOUSPMINSTRUCTIONDATE_NEW,
PREVIOUSPMINSTRUCTIONDATE_CANCEL,PREVIOUSPMINSTRUCTIONDATE_SETUP,PREVIOUSPMADVANCENOTICESENTDATE,PREVIOUSSEPAMANDATEID)
values(newid(), @ID,3,@CURRENTDATE,@PAYMENTMETHODCODE,@PREVIOUSPAYMENTMETHODCODE,
@CREDITCARDNUMBER,isnull(@PREVIOUSCREDITCARDPARTIALNUMBER,''), @CREDITTYPECODEID,@PREVIOUSCREDITTYPECODEID,@EXPIRESON,
isnull (@PREVIOUSEXPIRESON,'00000000'),isnull(@CARDHOLDERNAME,''),isnull(@PREVIOUSCARDHOLDERNAME,''),@PREVIOUSCONSTITUENTACCOUNTID,isnull(@PREVIOUSREFERENCEDATE,'00000000'),
isnull(@PREVIOUSREFERENCENUMBER,''),@PREVIOUSOTHERPAYMENTMETHODCODEID, @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,
isnull(@PREVIOUSSTANDINGORDERSETUP,0),@PREVIOUSSTANDINGORDERSETUPDATE,isnull(@PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER,0),@PREVIOUSDDISOURCECODEID,
@PREVIOUSDDISOURCEDATE,isnull(@PREVIOUSSENDPMINSTRUCTION,0),isnull(@PREVIOUSPMINSTRUCTIONTOSENDCODE,0),@PREVIOUSPMINSTRUCTIONDATE_NEW,@PREVIOUSPMINSTRUCTIONDATE_CANCEL,
@PREVIOUSPMINSTRUCTIONDATE_SETUP,@PREVIOUSPMADVANCENOTICESENTDATE,@PREVIOUSSEPAMANDATEID)
end
update dbo.CREDITCARDPAYMENTMETHODDETAIL
set
CARDHOLDERNAME = @CARDHOLDERNAME,
CREDITCARDPARTIALNUMBER = isnull(@CREDITCARDNUMBER,''),
CREDITTYPECODEID = @CREDITTYPECODEID,
EXPIRESON = @EXPIRESON,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from dbo.CREDITCARDPAYMENTMETHODDETAIL O
inner join dbo.REVENUEPAYMENTMETHOD R on O.ID = R.ID
where R.REVENUEID = @ID;
if @@ROWCOUNT = 0
insert into dbo.CREDITCARDPAYMENTMETHODDETAIL
(ID, CARDHOLDERNAME, CREDITCARDPARTIALNUMBER, CREDITTYPECODEID, EXPIRESON, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select ID, @CARDHOLDERNAME, isnull(@CREDITCARDNUMBER,''), @CREDITTYPECODEID, @EXPIRESON, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.REVENUEPAYMENTMETHOD where REVENUEID = @ID;
end
else
delete from dbo.CREDITCARDPAYMENTMETHODDETAIL where ID in (select ID from dbo.REVENUEPAYMENTMETHOD where REVENUEID = @ID);
if @HASEDITS = 1 and @PAYMENTMETHODCODE in(0,1,9, 101, 102) --cash, check, none, paypal, and venmo
begin
insert into dbo.RECURRINGGIFTAMENDMENT(ID,FINANCIALTRANSACTIONID,AMENDMENTTYPECODE,DATE,PAYMENTMETHODCODE,PREVIOUSPAYMENTMETHODCODE,
PREVIOUSCONSTITUENTACCOUNTID,PREVIOUSREFERENCEDATE,PREVIOUSREFERENCENUMBER,
PREVIOUSCREDITCARDPARTIALNUMBER,PREVIOUSCREDITTYPECODEID,PREVIOUSEXPIRESON,PREVIOUSCARDHOLDERNAME,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED, PREVIOUSOTHERPAYMENTMETHODCODEID,
PREVIOUSSTANDINGORDERSETUP,PREVIOUSSTANDINGORDERSETUPDATE,PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER,
PREVIOUSDDISOURCECODEID,PREVIOUSDDISOURCEDATE,PREVIOUSSENDPMINSTRUCTION,PREVIOUSPMINSTRUCTIONTOSENDCODE,PREVIOUSPMINSTRUCTIONDATE_NEW,
PREVIOUSPMINSTRUCTIONDATE_CANCEL,PREVIOUSPMINSTRUCTIONDATE_SETUP,PREVIOUSPMADVANCENOTICESENTDATE,PREVIOUSSEPAMANDATEID)
values(newid(), @ID,3,@CURRENTDATE,@PAYMENTMETHODCODE,@PREVIOUSPAYMENTMETHODCODE,@PREVIOUSCONSTITUENTACCOUNTID,
isnull(@PREVIOUSREFERENCEDATE,'00000000'),isnull(@PREVIOUSREFERENCENUMBER,''),
isnull(@PREVIOUSCREDITCARDPARTIALNUMBER,''),@PREVIOUSCREDITTYPECODEID,isnull(@PREVIOUSEXPIRESON,'00000000'),isnull(@PREVIOUSCARDHOLDERNAME,''),
@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,@PREVIOUSOTHERPAYMENTMETHODCODEID,isnull(@PREVIOUSSTANDINGORDERSETUP,0),@PREVIOUSSTANDINGORDERSETUPDATE,
isnull(@PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER,0),@PREVIOUSDDISOURCECODEID,@PREVIOUSDDISOURCEDATE,isnull(@PREVIOUSSENDPMINSTRUCTION,0),isnull(@PREVIOUSPMINSTRUCTIONTOSENDCODE,0),
@PREVIOUSPMINSTRUCTIONDATE_NEW,@PREVIOUSPMINSTRUCTIONDATE_CANCEL,@PREVIOUSPMINSTRUCTIONDATE_SETUP,@PREVIOUSPMADVANCENOTICESENTDATE,@PREVIOUSSEPAMANDATEID)
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;