USP_DATAFORMTEMPLATE_EDIT_PLEDGEPAYMENTDETAILS_6
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 |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PLEDGEPAYMENTDETAILS_6
(
@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
)
as
set nocount on;
declare @CURRENTDATE datetime;
begin try
if @AUTOPAY = 0
set @PAYMENTMETHODCODE = 9;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
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.
declare @PREVIOUSCREDITCARDID uniqueidentifier
if @UPDATEOTHERTRANSACTIONSTOUSENEWCREDITCARD = 1 and @PAYMENTMETHODCODE = 2 -- Credit card
begin
select
@PREVIOUSCREDITCARDID = CREDITCARDID
from dbo.REVENUESCHEDULE
where
ID = @ID
end
if @CREDITCARDTOKEN is null
update dbo.REVENUESCHEDULE set
CREDITCARDID = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID
update dbo.REVENUEPAYMENTMETHOD
set PAYMENTMETHODCODE = @PAYMENTMETHODCODE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where REVENUEID = @ID;
if @PAYMENTMETHODCODE = 2 --Credit debit
begin
declare @CREDITCARDID uniqueidentifier
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
select @PREVIOUSCREDITCARDID
if @PREVIOUSCREDITCARDID is not null
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
(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
--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 or @USESYSTEMGENERATEDREFERENCENUMBER = 1
select
@STANDINGORDERREFERENCENUMBER = '',
@USESYSTEMGENERATEDREFERENCENUMBER = 1
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);
end
else
begin
exec dbo.USP_REVENUESTANDINGORDER_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
exec dbo.USP_REVENUESCHEDULESTANDINGORDERPAYMENT_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
close symmetric key sym_BBInfinity;
return 1;
end catch
return 0;