USP_DATAFORMTEMPLATE_ADD_RECONCILEMATCINGGIFT
The save procedure used by the add dataform template "Reconcile Matching Gift Edit Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @REVENUESPLITID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @CONSTITUENTID | uniqueidentifier | IN | Constituent ID |
| @AMOUNT | money | IN | Amount to apply |
| @COMMITMENTS | tinyint | IN | Commitments |
| @REVENUESTREAMS | xml | IN | Matching Gift Claims |
| @RELATIONREVENUESTREAMS | xml | IN | Payment by related constituent |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_RECONCILEMATCINGGIFT
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@REVENUESPLITID uniqueidentifier,
@CONSTITUENTID uniqueidentifier,
@AMOUNT money = 0,
@COMMITMENTS tinyint = 0,
@REVENUESTREAMS xml = null,
@RELATIONREVENUESTREAMS xml = null
)
as
set nocount on;
declare @CURRENTDATE datetime;
declare @APPLIEDTOCLAIMS money;
declare @BENEFITS xml;
declare @PAYMENTMETHODCODE tinyint;
declare @TYPECODE tinyint;
declare @RECEIPTAMOUNT money;
declare @FINDERNUMBER bigint;
declare @SOURCECODE nvarchar(50);
declare @APPEALID uniqueidentifier;
declare @BENEFITSWAIVED bit;
declare @GIVENANONYMOUSLY bit;
declare @MAILINGID uniqueidentifier;
declare @CHANNELCODEID uniqueidentifier;
declare @DONOTACKNOWLEDGE bit;
declare @DONOTRECEIPT bit;
declare @POSTDATE datetime;
declare @BATCHNUMBER nvarchar(100);
declare @POSTSTATUSCODE tinyint;
declare @CHECKDATE dbo.UDT_FUZZYDATE;
declare @CHECKNUMBER nvarchar(20);
declare @RETURNREVENUEID uniqueidentifier;
declare @CONSTITUENTACCOUNTID uniqueidentifier;
declare @REFERENCEDATE dbo.UDT_FUZZYDATE;
declare @REFERENCENUMBER nvarchar(20);
declare @CARDHOLDERNAME nvarchar(255);
declare @CREDITCARDNUMBER nvarchar(4);
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 @SALEDATE datetime;
declare @SALEAMOUNT money;
declare @BROKERFEE money;
declare @SALEPOSTSTATUSCODE tinyint;
declare @SALEPOSTDATE datetime;
declare @PROPERTYSUBTYPECODEID uniqueidentifier;
declare @GIFTINKINDSUBTYPECODEID uniqueidentifier;
declare @REVENUEAMOUNT money;
declare @REVENUEID uniqueidentifier;
declare @REVENUEPAYMENTMETHODID uniqueidentifier;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CURRENTDATE is null
set @CURRENTDATE = GetDate();
begin try
select @REVENUEID = R.ID,
@PAYMENTMETHODCODE = PAYMENTMETHODCODE,
@BENEFITS = dbo.UFN_REVENUE_GETBENEFITS_TOITEMLISTXML(R.ID),
@FINDERNUMBER = FINDERNUMBER,
@SOURCECODE = SOURCECODE,
@APPEALID = APPEALID,
@BENEFITSWAIVED = BENEFITSWAIVED,
@GIVENANONYMOUSLY = GIVENANONYMOUSLY,
@MAILINGID = MAILINGID,
@CHANNELCODEID = CHANNELCODEID,
@DONOTACKNOWLEDGE = DONOTACKNOWLEDGE,
@DONOTRECEIPT = DONOTRECEIPT,
@POSTDATE = POSTDATE,
--Bug 13617 AdamBu 9/17/2008
--For the purpose of reconciling, we are only concerned with the amount of money in the unapplied
-- split, not the transaction as a whole.
--@REVENUEAMOUNT = R.AMOUNT,
@REVENUEAMOUNT = RS.TRANSACTIONAMOUNT,
@BATCHNUMBER = BATCHNUMBER,
@REVENUEPAYMENTMETHODID = RP.ID
from
dbo.REVENUESPLIT RS
inner join
dbo.REVENUE R on RS.REVENUEID = R.ID
inner join
dbo.REVENUEPAYMENTMETHOD RP
on R.ID = RP.REVENUEID
where RS.ID = @REVENUESPLITID
if @PAYMENTMETHODCODE = 0 -- Cash
select
@REFERENCEDATE = REFERENCEDATE,
@REFERENCENUMBER = REFERENCENUMBER
from
dbo.CASHPAYMENTMETHODDETAIL
where
ID = @REVENUEPAYMENTMETHODID
if @PAYMENTMETHODCODE = 1 -- Check
select
@CHECKDATE = CHECKDATE,
@CHECKNUMBER = CHECKNUMBER,
@CONSTITUENTACCOUNTID = CONSTITUENTACCOUNTID
from
dbo.CHECKPAYMENTMETHODDETAIL
where
ID = @REVENUEPAYMENTMETHODID
if @PAYMENTMETHODCODE = 2 -- Credit Card
select
@CARDHOLDERNAME = CARDHOLDERNAME,
@CREDITTYPECODEID = CREDITTYPECODEID,
@CREDITCARDNUMBER = CREDITCARDPARTIALNUMBER,
@EXPIRESON = EXPIRESON,
@AUTHORIZATIONCODE = AUTHORIZATIONCODE
from
dbo.CREDITCARDPAYMENTMETHODDETAIL
where
ID = @REVENUEPAYMENTMETHODID
if @PAYMENTMETHODCODE = 3 -- Direct Debit
select
@REFERENCEDATE = REFERENCEDATE,
@REFERENCENUMBER = REFERENCENUMBER,
@CONSTITUENTACCOUNTID = CONSTITUENTACCOUNTID
from
dbo.DIRECTDEBITPAYMENTMETHODDETAIL
where
ID = @REVENUEPAYMENTMETHODID
if @PAYMENTMETHODCODE = 4 --Stock
select
@ISSUER = ISSUER,
@NUMBEROFUNITS = NUMBEROFUNITS,
@SYMBOL = SYMBOL,
@MEDIANPRICE = MEDIANPRICE
from
dbo.STOCKDETAIL
where
ID = @REVENUEPAYMENTMETHODID
if @PAYMENTMETHODCODE = 5 --Property
select
@PROPERTYSUBTYPECODEID = PROPERTYSUBTYPECODEID,
@SALEDATE = SALEDATE,
@SALEAMOUNT = SALEAMOUNT,
@BROKERFEE = BROKERFEE,
@SALEPOSTDATE = SALEPOSTDATE,
@SALEPOSTSTATUSCODE = SALEPOSTSTATUSCODE
from
dbo.PROPERTYDETAIL
where
ID = @REVENUEPAYMENTMETHODID
if @PAYMENTMETHODCODE = 6 -- Gift in Kind
select
@GIFTINKINDSUBTYPECODEID = GIFTINKINDSUBTYPECODEID
from
dbo.GIFTINKINDPAYMENTMETHODDETAIL
where
ID = @REVENUEPAYMENTMETHODID
exec dbo.USP_RECONCILEMATCHINGGIFT_REVENUESTREAMS @REVENUESPLITID, @REVENUEAMOUNT, @REVENUESTREAMS, @RELATIONREVENUESTREAMS, @CONSTITUENTID, @CURRENTDATE, @PAYMENTMETHODCODE, @BATCHNUMBER, @POSTDATE, @POSTSTATUSCODE, @DONOTRECEIPT, @DONOTACKNOWLEDGE, @FINDERNUMBER, @SOURCECODE, @APPEALID, @MAILINGID, @CHANNELCODEID, @CHECKDATE, @CHECKNUMBER, @CONSTITUENTACCOUNTID, @REFERENCEDATE, @REFERENCENUMBER, @CARDHOLDERNAME, @CREDITCARDNUMBER, @CREDITTYPECODEID, @AUTHORIZATIONCODE, @EXPIRESON, @ISSUER, @NUMBEROFUNITS, @SYMBOL, @MEDIANPRICE, @SALEDATE, @SALEAMOUNT, @BROKERFEE, @SALEPOSTSTATUSCODE, @SALEPOSTDATE, @PROPERTYSUBTYPECODEID, @GIFTINKINDSUBTYPECODEID, @CHANGEAGENTID, @CURRENTDATE, @APPLIEDTOCLAIMS output;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;