USP_DATAFORMTEMPLATE_EDIT_REVENUEBATCHROW_5_1
The save procedure used by the edit dataform template "Revenue Batch Row Edit Form 5.1".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @CONSTITUENTID | uniqueidentifier | IN | Constituent |
| @TYPECODE | tinyint | IN | Revenue type |
| @DATE | datetime | IN | Date |
| @AMOUNT | money | IN | Amount |
| @PAYMENTMETHODCODE | tinyint | IN | Payment method |
| @DONOTACKNOWLEDGE | bit | IN | Do not acknowledge |
| @CHECKDATE | UDT_FUZZYDATE | IN | Check date |
| @CHECKNUMBER | nvarchar(20) | IN | Check number |
| @REFERENCEDATE | UDT_FUZZYDATE | IN | Reference date |
| @REFERENCENUMBER | nvarchar(20) | IN | Reference number |
| @CARDHOLDERNAME | nvarchar(255) | IN | Name on card |
| @CREDITCARDNUMBER | nvarchar(20) | IN | Card number |
| @CREDITTYPECODEID | uniqueidentifier | IN | Card type |
| @AUTHORIZATIONCODE | nvarchar(20) | IN | Authorization code |
| @EXPIRESON | UDT_FUZZYDATE | IN | Expires on |
| @ISSUER | nvarchar(100) | IN | Issuer |
| @NUMBEROFUNITS | decimal(20, 3) | IN | Number of units |
| @SYMBOL | nvarchar(25) | IN | Symbol |
| @MEDIANPRICE | decimal(19, 4) | IN | Median price |
| @PROPERTYSUBTYPECODEID | uniqueidentifier | IN | Property subtype |
| @GIFTINKINDSUBTYPECODEID | uniqueidentifier | IN | Gift-in-kind subtype |
| @RECEIPTAMOUNT | money | IN | Receipt amount |
| @DONOTRECEIPT | bit | IN | Do not receipt |
| @CONSTITUENTACCOUNTID | uniqueidentifier | IN | Account |
| @SPLITS | xml | IN | Designations |
| @SINGLEDESIGNATIONID | uniqueidentifier | IN | Designation |
| @REVENUESTREAMS | xml | IN | Revenue streams |
| @APPLYTOSHOWNFORCONSTITUENTID | uniqueidentifier | IN | Apply to shown for constituent |
| @SEQUENCE | int | IN | Sequence |
| @INSTALLMENTFREQUENCYCODE | tinyint | IN | Installment frequency |
| @INSTALLMENTSTARTDATE | datetime | IN | Installment start date |
| @INSTALLMENTENDDATE | datetime | IN | Installment end date |
| @NUMBEROFINSTALLMENTS | int | IN | No. installments |
| @SOLICITORS | xml | IN | Solicitors |
| @BENEFITS | xml | IN | Benefits |
| @FINDERNUMBER | bigint | IN | Finder number |
| @SOURCECODE | nvarchar(60) | IN | Source code |
| @APPEALID | uniqueidentifier | IN | Appeal |
| @FINDERNUMBERISVALID | bit | IN | Finder number is valid |
| @USERMODIFIEDBENEFITS | bit | IN | User modified benefits |
| @BENEFITSWAIVED | bit | IN | User waived benefits |
| @POSTDATE | datetime | IN | GL post date |
| @POSTSTATUSCODE | tinyint | IN | GL post status |
| @SENDPLEDGEREMINDER | bit | IN | Send reminders |
| @SALEDATE | datetime | IN | Sale date |
| @SALEAMOUNT | money | IN | Sale amount |
| @BROKERFEE | money | IN | Sale broker fee |
| @SALEPOSTSTATUSCODE | tinyint | IN | Sale GL post status |
| @SALEPOSTDATE | datetime | IN | Sale GL post date |
| @NOTETITLE | nvarchar(50) | IN | Note Title |
| @NOTEAUTHORID | uniqueidentifier | IN | Note Author |
| @NOTEDATEENTERED | datetime | IN | Note Date |
| @NOTETYPECODEID | uniqueidentifier | IN | Note Type |
| @NOTETEXTNOTE | nvarchar(max) | IN | Notes |
| @MGMATCHINGCONSTITUENTID | uniqueidentifier | IN | MG Matching constituent |
| @MGDATE | datetime | IN | MG Date |
| @MGAMOUNT | money | IN | MG Amount |
| @MGPOSTDATE | datetime | IN | MG Post date |
| @MGPOSTSTATUSCODE | tinyint | IN | MG Post status |
| @MGCONDITIONID | uniqueidentifier | IN | Matching gift condition ID |
| @MGSPLITS | xml | IN | MG Splits |
| @GIVENANONYMOUSLY | bit | IN | Given anonymously |
| @GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID | uniqueidentifier | IN | Given anonymously defaulted for constituent |
| @USERMODIFIEDRECEIPTAMOUNT | bit | IN | User modified receipt amount |
| @PLEDGESUBTYPEID | uniqueidentifier | IN | Pledge subtype |
| @REJECTIONCODEID | uniqueidentifier | IN | Rejection code |
| @CONSTITUENTLOOKUPID | uniqueidentifier | IN | Lookup ID |
| @MAILINGID | uniqueidentifier | IN | Mailing |
| @CHANNELCODEID | uniqueidentifier | IN | Channel |
| @INSTALLMENTS | xml | IN | Installments |
| @PAYMENTFORPLEDGEAMOUNT | money | IN | Payment for pledge amount |
| @RECOGNITIONS | xml | IN | Recognition credits |
| @DIDRECOGNITIONSDEFAULT | bit | IN | Did default recognition credits |
| @TRIBUTES | xml | IN | Tributes |
| @UNAPPLIEDMATCHINGGIFTSPLITS | xml | IN | Unapplied MG Split |
| @UNAPPLIEDMATCHINGGIFTAMOUNT | money | IN | Applied |
| @RECEIPTTYPECODE | tinyint | IN | Receipt type |
| @NEWCONSTITUENT | xml | IN | New constituent |
| @MGRELATIONSHIPID | uniqueidentifier | IN | Relationship |
| @OTHERPAYMENTMETHODCODEID | uniqueidentifier | IN | Other method |
| @LETTERCODEID | uniqueidentifier | IN | Letter |
| @ACKNOWLEDGEDATE | datetime | IN | Acknowledge date |
| @REFERENCE | nvarchar(255) | IN | Reference |
| @CATEGORYCODEID | uniqueidentifier | IN | Revenue category |
| @ACKNOWLEDGEEID | uniqueidentifier | IN | Acknowledgee |
| @APPLICATIONCODE | tinyint | IN | Application |
| @OTHERTYPECODEID | uniqueidentifier | IN | Other type |
| @OPPORTUNITYID | uniqueidentifier | IN | Opportunity |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUEBATCHROW_5_1
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@CONSTITUENTID uniqueidentifier,
@TYPECODE tinyint,
@DATE datetime,
@AMOUNT money,
@PAYMENTMETHODCODE tinyint,
@DONOTACKNOWLEDGE bit,
@CHECKDATE dbo.UDT_FUZZYDATE,
@CHECKNUMBER nvarchar(20),
@REFERENCEDATE dbo.UDT_FUZZYDATE,
@REFERENCENUMBER nvarchar(20),
@CARDHOLDERNAME nvarchar(255),
@CREDITCARDNUMBER nvarchar(20),
@CREDITTYPECODEID uniqueidentifier,
@AUTHORIZATIONCODE nvarchar(20),
@EXPIRESON dbo.UDT_FUZZYDATE,
@ISSUER nvarchar(100),
@NUMBEROFUNITS decimal(20,3),
@SYMBOL nvarchar(25),
@MEDIANPRICE decimal(19,4),
@PROPERTYSUBTYPECODEID uniqueidentifier,
@GIFTINKINDSUBTYPECODEID uniqueidentifier,
@RECEIPTAMOUNT money,
@DONOTRECEIPT bit,
@CONSTITUENTACCOUNTID uniqueidentifier,
@SPLITS xml,
@SINGLEDESIGNATIONID uniqueidentifier,
@REVENUESTREAMS xml,
@APPLYTOSHOWNFORCONSTITUENTID uniqueidentifier,
@SEQUENCE int,
@INSTALLMENTFREQUENCYCODE tinyint,
@INSTALLMENTSTARTDATE datetime,
@INSTALLMENTENDDATE datetime,
@NUMBEROFINSTALLMENTS int,
@SOLICITORS xml,
@BENEFITS xml,
@FINDERNUMBER bigint,
@SOURCECODE nvarchar(60),
@APPEALID uniqueidentifier,
@FINDERNUMBERISVALID bit,
@USERMODIFIEDBENEFITS bit,
@BENEFITSWAIVED bit,
@POSTDATE datetime,
@POSTSTATUSCODE tinyint,
@SENDPLEDGEREMINDER bit,
@SALEDATE datetime,
@SALEAMOUNT money,
@BROKERFEE money,
@SALEPOSTSTATUSCODE tinyint,
@SALEPOSTDATE datetime,
@NOTETITLE nvarchar(50),
@NOTEAUTHORID uniqueidentifier,
@NOTEDATEENTERED datetime,
@NOTETYPECODEID uniqueidentifier,
@NOTETEXTNOTE nvarchar(max),
@MGMATCHINGCONSTITUENTID uniqueidentifier,
@MGDATE datetime,
@MGAMOUNT money,
@MGPOSTDATE datetime,
@MGPOSTSTATUSCODE tinyint,
@MGCONDITIONID uniqueidentifier,
@MGSPLITS xml,
@GIVENANONYMOUSLY bit,
@GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID uniqueidentifier,
@USERMODIFIEDRECEIPTAMOUNT bit,
@PLEDGESUBTYPEID uniqueidentifier,
@REJECTIONCODEID uniqueidentifier,
@CONSTITUENTLOOKUPID uniqueidentifier,
@MAILINGID uniqueidentifier,
@CHANNELCODEID uniqueidentifier,
@INSTALLMENTS xml,
@PAYMENTFORPLEDGEAMOUNT money,
@RECOGNITIONS xml,
@DIDRECOGNITIONSDEFAULT bit,
@TRIBUTES xml,
@UNAPPLIEDMATCHINGGIFTSPLITS xml,
@UNAPPLIEDMATCHINGGIFTAMOUNT money,
@RECEIPTTYPECODE tinyint,
@NEWCONSTITUENT xml,
@MGRELATIONSHIPID uniqueidentifier,
@OTHERPAYMENTMETHODCODEID uniqueidentifier,
@LETTERCODEID uniqueidentifier,
@ACKNOWLEDGEDATE datetime,
@REFERENCE nvarchar(255),
@CATEGORYCODEID uniqueidentifier,
@ACKNOWLEDGEEID uniqueidentifier,
@APPLICATIONCODE tinyint,
@OTHERTYPECODEID uniqueidentifier,
@OPPORTUNITYID uniqueidentifier
)
as
begin
set nocount on;
declare @CURRENTDATE datetime
if @ID is null
set @ID = NewID()
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
set @CURRENTDATE = GetDate()
declare @contextCache varbinary(128);
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
declare @ENCRYPTEDCC nvarchar(68);
set @ENCRYPTEDCC = '';
begin try
--JamesWill CR269707-031107 2007/03/19 Set default values for any non-nullable fields that might come in as null
if @DONOTACKNOWLEDGE is null
set @DONOTACKNOWLEDGE = 0;
if @CHECKDATE is null
set @CHECKDATE = '00000000';
if @CHECKNUMBER is null
set @CHECKNUMBER = '';
if @REFERENCEDATE is null
set @REFERENCEDATE = '00000000';
if @REFERENCENUMBER is null
set @REFERENCENUMBER = '';
if @CARDHOLDERNAME is null
set @CARDHOLDERNAME = '';
if @CREDITCARDNUMBER is null
set @CREDITCARDNUMBER = '';
if @AUTHORIZATIONCODE is null
set @AUTHORIZATIONCODE = '';
if @EXPIRESON is null
set @EXPIRESON = '00000000';
if @ISSUER is null
set @ISSUER = '';
if @NUMBEROFUNITS is null
set @NUMBEROFUNITS = 0;
if @SYMBOL is null
set @SYMBOL = '';
if @MEDIANPRICE is null
set @MEDIANPRICE = 0;
if @RECEIPTAMOUNT is null
set @RECEIPTAMOUNT = 0;
if @DONOTRECEIPT is null
set @DONOTRECEIPT = 0;
if @NUMBEROFINSTALLMENTS is null
set @NUMBEROFINSTALLMENTS = 0;
if @SOURCECODE is null
set @SOURCECODE = '';
if @FINDERNUMBERISVALID is null
set @FINDERNUMBERISVALID = 0;
if @USERMODIFIEDBENEFITS is null
set @USERMODIFIEDBENEFITS = 0;
if @BENEFITSWAIVED is null
set @BENEFITSWAIVED = 0;
if @SENDPLEDGEREMINDER is null
set @SENDPLEDGEREMINDER = 1;
if @SALEAMOUNT is null
set @SALEAMOUNT = 0;
if @BROKERFEE is null
set @BROKERFEE = 0;
if @NOTETITLE is null
set @NOTETITLE = '';
if @NOTETEXTNOTE is null
set @NOTETEXTNOTE = '';
if @MGAMOUNT is null
set @MGAMOUNT = 0;
if @MGPOSTSTATUSCODE is null
set @MGPOSTSTATUSCODE = 1;
if @GIVENANONYMOUSLY is null
set @GIVENANONYMOUSLY = 0;
if @USERMODIFIEDRECEIPTAMOUNT is null
set @USERMODIFIEDRECEIPTAMOUNT = 0;
if @PAYMENTFORPLEDGEAMOUNT is null
set @PAYMENTFORPLEDGEAMOUNT = 0;
if @DIDRECOGNITIONSDEFAULT is null
set @DIDRECOGNITIONSDEFAULT = 0;
if @FINDERNUMBER is null
set @FINDERNUMBER = 0;
if cast(@SPLITS as nvarchar(max)) = ''
set @SPLITS = null;
if not @SPLITS is null
set @SINGLEDESIGNATIONID = null;
if @PAYMENTMETHODCODE is null
set @PAYMENTMETHODCODE = 255;
/* JamesWill CR267402-021907 INSTALLMENTFREQUENCYCODE cannot be null in the database, but it can be null in the defaults */
if @INSTALLMENTFREQUENCYCODE is null
set @INSTALLMENTFREQUENCYCODE = 255;
/* JamesWill CR265838-020507 2007/03/07 */
if @POSTSTATUSCODE is null
set @POSTSTATUSCODE = 255;
if @SALEPOSTSTATUSCODE is null
set @SALEPOSTSTATUSCODE = 255;
if not @CREDITCARDNUMBER is null and len(@CREDITCARDNUMBER) > 0
begin
--Batch opens and closes the key for us
set @ENCRYPTEDCC = convert(nvarchar(68), EncryptByKey(Key_GUID('sym_BBInfinity'), @CREDITCARDNUMBER))
end
if @RECEIPTTYPECODE is null
set @RECEIPTTYPECODE = 255;
update dbo.BATCHREVENUE
set CONSTITUENTID = @CONSTITUENTID,
TYPECODE = @TYPECODE,
DATE = @DATE,
PAYMENTMETHODCODE = @PAYMENTMETHODCODE,
DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE,
CHECKDATE = @CHECKDATE,
CHECKNUMBER = @CHECKNUMBER,
REFERENCEDATE = @REFERENCEDATE,
REFERENCENUMBER = @REFERENCENUMBER,
AUTHORIZATIONCODE = @AUTHORIZATIONCODE,
CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
AMOUNT = @AMOUNT,
RECEIPTAMOUNT = @RECEIPTAMOUNT,
DONOTRECEIPT = @DONOTRECEIPT,
SEQUENCE = @SEQUENCE,
INSTALLMENTFREQUENCYCODE = @INSTALLMENTFREQUENCYCODE,
INSTALLMENTSTARTDATE = @INSTALLMENTSTARTDATE,
INSTALLMENTENDDATE = @INSTALLMENTENDDATE,
NUMBEROFINSTALLMENTS = @NUMBEROFINSTALLMENTS,
APPLYTOSHOWNFORCONSTITUENTID = @APPLYTOSHOWNFORCONSTITUENTID,
FINDERNUMBER = @FINDERNUMBER,
SOURCECODE = @SOURCECODE,
APPEALID = @APPEALID,
FINDERNUMBERISVALID = @FINDERNUMBERISVALID,
USERMODIFIEDBENEFITS = @USERMODIFIEDBENEFITS,
BENEFITSWAIVED = @BENEFITSWAIVED,
POSTDATE = @POSTDATE,
POSTSTATUSCODE = @POSTSTATUSCODE,
SENDPLEDGEREMINDER = @SENDPLEDGEREMINDER,
SALEDATE = @SALEDATE,
SALEAMOUNT = @SALEAMOUNT,
BROKERFEE = @BROKERFEE,
SALEPOSTSTATUSCODE = @SALEPOSTSTATUSCODE,
SALEPOSTDATE = @SALEPOSTDATE,
ISSUER = @ISSUER,
NUMBEROFUNITS = @NUMBEROFUNITS,
SYMBOL = @SYMBOL,
MEDIANPRICE = @MEDIANPRICE,
GIFTINKINDSUBTYPECODEID = @GIFTINKINDSUBTYPECODEID,
PROPERTYSUBTYPECODEID = @PROPERTYSUBTYPECODEID,
NOTETITLE = @NOTETITLE,
NOTEAUTHORID = @NOTEAUTHORID,
NOTEDATEENTERED = @NOTEDATEENTERED,
NOTETYPECODEID = @NOTETYPECODEID,
NOTETEXTNOTE = @NOTETEXTNOTE,
MGMATCHINGCONSTITUENTID = @MGMATCHINGCONSTITUENTID,
MGDATE = @MGDATE,
MGAMOUNT = @MGAMOUNT,
MGPOSTDATE = null, --JamesWill CR275664-052107 2007/05/24 Matching gift claims are never posted
MGPOSTSTATUSCODE = 2, --JamesWill CR275664-052107 2007/05/24 Matching gift claims are never posted
MGCONDITIONID = @MGCONDITIONID,
GIVENANONYMOUSLY = @GIVENANONYMOUSLY,
GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID = @GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID,
USERMODIFIEDRECEIPTAMOUNT = @USERMODIFIEDRECEIPTAMOUNT,
PLEDGESUBTYPEID = @PLEDGESUBTYPEID,
REJECTIONCODEID = @REJECTIONCODEID,
MAILINGID = @MAILINGID,
CHANNELCODEID = @CHANNELCODEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
PAYMENTFORPLEDGEAMOUNT = @PAYMENTFORPLEDGEAMOUNT,
RECEIPTTYPECODE = @RECEIPTTYPECODE,
MGRELATIONSHIPID =@MGRELATIONSHIPID,
OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID,
LETTERCODEID = @LETTERCODEID,
ACKNOWLEDGEDATE = @ACKNOWLEDGEDATE,
REFERENCE = @REFERENCE,
GLREVENUECATEGORYMAPPINGID = @CATEGORYCODEID,
ACKNOWLEDGEEID = @ACKNOWLEDGEEID,
APPLICATIONCODE = @APPLICATIONCODE,
OTHERTYPECODEID = @OTHERTYPECODEID,
OPPORTUNITYID = @OPPORTUNITYID
where ID = @ID
-- Create the splits collection with the single designation if the splits collection is null and the designation isn't
if @SPLITS is null and @SINGLEDESIGNATIONID is not null
begin
set @SPLITS = ( select
@SINGLEDESIGNATIONID as DESIGNATIONID,
@AMOUNT as AMOUNT,
1 as SEQUENCE
for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64)
end
if @SPLITS is not null
begin
exec dbo.USP_REVENUEBATCH_SPLITSWITHCHILDREN_UPDATEFROMXML @ID, @SPLITS, @DATE, @CHANGEAGENTID, @CURRENTDATE;
end
else
delete from dbo.BATCHREVENUESPLIT where BATCHREVENUEID = @ID;
exec dbo.USP_REVENUEBATCH_GETRECOGNITIONS_UPDATEFROMXML @ID, @RECOGNITIONS, @CHANGEAGENTID;
exec dbo.USP_REVENUEBATCH_GETSOLICITORS_UPDATEFROMXML @ID, @SOLICITORS, @CHANGEAGENTID;
exec dbo.USP_REVENUEBATCH_GETBENEFITS_UPDATEFROMXML @ID, @BENEFITS, @CHANGEAGENTID;
exec dbo.USP_REVENUEBATCH_GETMATCHINGGIFTSPLITS_UPDATEFROMXML @ID, @MGSPLITS, @CHANGEAGENTID;
exec dbo.USP_REVENUEBATCH_GETUNAPPLIEDMATCHINGGIFTSPLITS_UPDATEFROMXML @ID, @UNAPPLIEDMATCHINGGIFTSPLITS, @CHANGEAGENTID;
exec dbo.USP_REVENUEBATCH_GETINSTALLMENTS_UPDATEFROMXML @ID, @INSTALLMENTS, @CHANGEAGENTID;
exec dbo.USP_REVENUEBATCH_GETTRIBUTES_UPDATEFROMXML @ID, @TRIBUTES, @CHANGEAGENTID;
--JamesWill CR268756-030207 2007/03/02
--Unset the ISPENDING flag for all of the gifts we were previously applying to from a generated transaction
update dbo.REVENUESCHEDULE
set REVENUESCHEDULE.ISPENDING = 0,
REVENUESCHEDULE.CHANGEDBYID = @CHANGEAGENTID,
REVENUESCHEDULE.DATECHANGED = @CURRENTDATE
where REVENUESCHEDULE.ID in (select REVENUEID from dbo.BATCHREVENUEAPPLICATION where BATCHREVENUEID = @ID and APPLIED > 0 and WASGENERATED = 1)
delete from dbo.BATCHREVENUEAPPLICATIONPLEDGE where ID in (select BATCHREVENUEAPPLICATIONPLEDGEID from BATCHREVENUEAPPLICATION where BATCHREVENUEID = @ID);
delete from dbo.BATCHREVENUEAPPLICATIONMEMBERSHIP where ID in (select BATCHREVENUEAPPLICATIONMEMBERSHIPID from BATCHREVENUEAPPLICATION where BATCHREVENUEID = @ID);
delete from BATCHREVENUEAPPLICATION where BATCHREVENUEID = @ID
exec dbo.USP_REVENUEBATCH_REVENUEAPPLICATIONS_ADDFROMXML @ID, @REVENUESTREAMS, @CHANGEAGENTID, @CURRENTDATE;
--JamesWill CR268756-030207 2007/03/02
--Set the ISPENDING flag for all gifts that we are currently applying to which were from a generated transaction
update dbo.REVENUESCHEDULE
set REVENUESCHEDULE.ISPENDING = 1,
REVENUESCHEDULE.CHANGEDBYID = @CHANGEAGENTID,
REVENUESCHEDULE.DATECHANGED = @CURRENTDATE
where REVENUESCHEDULE.ID in (select REVENUEID from dbo.BATCHREVENUEAPPLICATION where BATCHREVENUEID = @ID and APPLIED > 0 and WASGENERATED = 1)
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0
end