USP_DATAFORMTEMPLATE_ADD_PAYMENTBYTRANSACTIONPOSTED
The save procedure used by the add dataform template "Payment Add by Transaction Posted 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. |
| @TRANSACTIONID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @AMOUNT | money | IN | Amount |
| @RECEIPTAMOUNT | money | IN | Receipt amount |
| @SPLITS | xml | IN | Designations |
| @REVENUESTREAMS | xml | IN | Revenue streams |
| @SOURCECODE | nvarchar(50) | IN | Source code |
| @APPEALID | uniqueidentifier | IN | Appeal |
| @BENEFITS | xml | IN | Benefits |
| @BENEFITSWAIVED | bit | IN | Benefits waived |
| @MAILINGID | uniqueidentifier | IN | Mailing |
| @CHANNELCODEID | uniqueidentifier | IN | Inbound channel |
| @UNAPPLIEDMATCHINGGIFTSPLITS | xml | IN | Designations |
| @UNAPPLIEDMATCHINGGIFTAMOUNT | money | IN | Applied |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PAYMENTBYTRANSACTIONPOSTED
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier,
@TRANSACTIONID uniqueidentifier,
@AMOUNT money = 0,
@RECEIPTAMOUNT money = 0,
@SPLITS xml = null,
@REVENUESTREAMS xml = null,
@SOURCECODE nvarchar(50) = null,
@APPEALID uniqueidentifier = null,
@BENEFITS xml = null,
@BENEFITSWAIVED bit = 0,
@MAILINGID uniqueidentifier = null,
@CHANNELCODEID uniqueidentifier = null,
@UNAPPLIEDMATCHINGGIFTSPLITS xml = null,
@UNAPPLIEDMATCHINGGIFTAMOUNT money = 0,
@CURRENTAPPUSERID uniqueidentifier = null
)
as
set nocount on;
declare @REVENUEID uniqueidentifier;
declare @DATE datetime;
declare @PAYMENTMETHODCODE tinyint;
declare @OTHERPAYMENTMETHODCODEID uniqueidentifier;
declare @CHECKDATE dbo.UDT_FUZZYDATE;
declare @CHECKNUMBER nvarchar(20);
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(4);
declare @MEDIANPRICE decimal(19,4);
declare @GIFTINKINDSUBTYPECODEID uniqueidentifier;
declare @PROPERTYSUBTYPECODEID uniqueidentifier;
declare @CONSTITUENTACCOUNTID uniqueidentifier;
declare @POSTSTATUSCODE tinyint;
declare @POSTDATE datetime;
declare @FINDERNUMBER bigint;
declare @GIVENANONYMOUSLY bit;
declare @DONOTRECEIPT bit;
declare @DONOTACKNOWLEDGE bit;
declare @ADJUSTMENTDATE datetime;
declare @ADJUSTMENTPOSTDATE datetime;
declare @ADJUSTMENTREASON nvarchar(300);
declare @ADJUSTMENTID uniqueidentifier;
declare @STOCKDETAILADJUSTMENTID uniqueidentifier;
declare @PROPERTYDETAILADJUSTMENTID uniqueidentifier;
declare @ADJUST bit;
declare @CLEARGLDISTRIBUTION bit;
declare @PROPERTYDETAILCOUNT int;
declare @STOCKDETAILCOUNT int;
set @ADJUST = 0
set @PROPERTYDETAILCOUNT = 0;
set @STOCKDETAILCOUNT = 0;
/* TODO REDO
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
--use existing unposted adjustment values if present
select top 1
@ADJUSTMENTDATE = coalesce(ADJUSTMENT.DATE, dbo.UFN_DATE_GETEARLIESTTIME(getdate())),
@ADJUSTMENTPOSTDATE = coalesce(ADJUSTMENT.POSTDATE, dbo.UFN_DATE_GETEARLIESTTIME(getdate())),
@ADJUSTMENTREASON = coalesce(ADJUSTMENT.REASON, 'Adding revenue')
from dbo.REVENUE
left outer join dbo.ADJUSTMENT
on REVENUE.ID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
where REVENUE.TRANSACTIONID = @TRANSACTIONID
declare @ADJUSTED table(REVENUEID uniqueidentifier, ADJUSTMENTID uniqueidentifier, STOCKDETAILADJUSTMENTID uniqueidentifier, PROPERTYDETAILADJUSTMENTID uniqueidentifier);
/* Cursor to use for logging history adjustments */
declare HISTORYCURSOR cursor local fast_forward for
select REVENUEID, ADJUSTMENTID, STOCKDETAILADJUSTMENTID, PROPERTYDETAILADJUSTMENTID from @ADJUSTED;
/* Cursor to use for logging adjustments */
declare REVENUECURSOR cursor local fast_forward for
select REVENUE.ID
from dbo.REVENUE
where REVENUE.TRANSACTIONID = @TRANSACTIONID;
begin try
declare @CONSTITUENTID uniqueidentifier;
--Set default payment values
select top 1
@REVENUEID = REVENUE.ID,
@CONSTITUENTID = REVENUE.CONSTITUENTID,
@DATE = REVENUE.DATE,
@PAYMENTMETHODCODE = REVENUE.PAYMENTMETHODCODE,
@POSTSTATUSCODE = case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end,
@POSTDATE = REVENUE.POSTDATE,
@DONOTRECEIPT = REVENUE.DONOTRECEIPT,
@GIVENANONYMOUSLY = REVENUE.GIVENANONYMOUSLY,
@DONOTACKNOWLEDGE = REVENUE.DONOTACKNOWLEDGE
from dbo.REVENUE
left outer join REVENUEPOSTED
on REVENUE.ID = REVENUEPOSTED.ID
where TRANSACTIONID = @TRANSACTIONID;
if @POSTSTATUSCODE <> 0
raiserror('This action is not valid for unposted transactions.', 13, 1)
select @REFERENCENUMBER = '',
@REFERENCEDATE = '00000000',
@CHECKDATE = '00000000',
@CHECKNUMBER = '',
@CARDHOLDERNAME = '',
@CREDITCARDNUMBER = '',
@CREDITTYPECODEID = null,
@AUTHORIZATIONCODE = '',
@EXPIRESON = '00000000',
@CONSTITUENTACCOUNTID = null,
@ISSUER = '',
@NUMBEROFUNITS = 0,
@SYMBOL = '',
@MEDIANPRICE = 0,
@PROPERTYSUBTYPECODEID = null,
@OTHERPAYMENTMETHODCODEID = null
if @PAYMENTMETHODCODE = 0 --Cash
select
@REFERENCENUMBER = REFERENCENUMBER,
@REFERENCEDATE = REFERENCEDATE
from dbo.CASHPAYMENTMETHODDETAIL where ID = @REVENUEID;
if @PAYMENTMETHODCODE = 1 --Check
select @CHECKDATE = CHECKDATE,
@CHECKNUMBER = CHECKNUMBER
from dbo.CHECKPAYMENTMETHODDETAIL where ID = @REVENUEID;
if @PAYMENTMETHODCODE = 2 --Credit Card
begin
select
@CARDHOLDERNAME = CARDHOLDERNAME,
@CREDITCARDNUMBER = CREDITCARDPARTIALNUMBER,
@CREDITTYPECODEID = CREDITTYPECODEID,
@AUTHORIZATIONCODE = AUTHORIZATIONCODE,
@EXPIRESON = EXPIRESON
from dbo.CREDITCARDPAYMENTMETHODDETAIL where ID = @REVENUEID;
end
if @PAYMENTMETHODCODE = 3 --Direct Debit
select
@REFERENCEDATE = REFERENCEDATE,
@REFERENCENUMBER = REFERENCENUMBER,
@CONSTITUENTACCOUNTID = CONSTITUENTACCOUNTID
from dbo.DIRECTDEBITPAYMENTMETHODDETAIL where ID = @REVENUEID;
if @PAYMENTMETHODCODE = 4 or @PAYMENTMETHODCODE = 7 --Stock or Sold Stock
select
@ISSUER = ISSUER,
@NUMBEROFUNITS = NUMBEROFUNITS,
@SYMBOL = SYMBOL,
@MEDIANPRICE = MEDIANPRICE
from dbo.STOCKDETAIL where ID = @REVENUEID;
if @PAYMENTMETHODCODE = 5 --Property
select
@PROPERTYSUBTYPECODEID = PROPERTYSUBTYPECODEID
from dbo.PROPERTYDETAIL WHERE ID = @REVENUEID;
if @PAYMENTMETHODCODE = 6 --Gift in Kind
select
@GIFTINKINDSUBTYPECODEID = GIFTINKINDSUBTYPECODEID
from dbo.GIFTINKINDPAYMENTMETHODDETAIL where ID = @REVENUEID;
if @PAYMENTMETHODCODE = 10 --Other
select
@REFERENCENUMBER = REFERENCENUMBER,
@REFERENCEDATE = REFERENCEDATE,
@OTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODCODEID
from dbo.OTHERPAYMENTMETHODDETAIL where ID = @REVENUEID;
if @CONSTITUENTID is null
raiserror('Unable to locate associated transaction.', 13, 1);
set @REVENUEID = null;
set @ID = @TRANSACTIONID;
exec dbo.USP_PAYMENT_ADD @REVENUEID output, @CHANGEAGENTID, @CURRENTDATE, @CONSTITUENTID, @ID,
@DATE, @AMOUNT, @PAYMENTMETHODCODE, @CHECKDATE, @CHECKNUMBER,
@REFERENCEDATE, @REFERENCENUMBER, @CARDHOLDERNAME, @CREDITCARDNUMBER,
@CREDITTYPECODEID, @AUTHORIZATIONCODE, @EXPIRESON, @ISSUER,
@NUMBEROFUNITS, @SYMBOL, @MEDIANPRICE, @GIFTINKINDSUBTYPECODEID,
@PROPERTYSUBTYPECODEID, @RECEIPTAMOUNT, @CONSTITUENTACCOUNTID, @SPLITS,
@REVENUESTREAMS, @POSTSTATUSCODE, @POSTDATE, @FINDERNUMBER,
@SOURCECODE, @APPEALID, @BENEFITS, @BENEFITSWAIVED, @GIVENANONYMOUSLY,
@MAILINGID, @CHANNELCODEID, @DONOTACKNOWLEDGE,@DONOTRECEIPT,@UNAPPLIEDMATCHINGGIFTSPLITS,'', @OTHERPAYMENTMETHODCODEID, @CURRENTAPPUSERID
--Flag all new revenue items as posted
insert into dbo.REVENUEPOSTED(ID,ADDEDBYID,CHANGEDBYID)
select REVENUE.ID, @CHANGEAGENTID, @CHANGEAGENTID
from REVENUE
where TRANSACTIONID = @TRANSACTIONID and not exists(select top 1 PST.ID from dbo.REVENUEPOSTED PST where PST.ID = REVENUE.ID);
declare @NEWREVENUEID uniqueidentifier;
set @NEWREVENUEID = @REVENUEID;
-- update the payment information for each revenue record in the transaction
open REVENUECURSOR;
fetch next from REVENUECURSOR into @REVENUEID;
while @@FETCH_STATUS = 0
begin
declare @ISNEWREVENUE bit;
if @NEWREVENUEID = @REVENUEID
set @ISNEWREVENUE = 1;
else
set @ISNEWREVENUE = 0;
set @ADJUSTMENTID = null;
exec dbo.USP_SAVE_ADJUSTMENT @REVENUEID, @ADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @ISNEWREVENUE;
select @STOCKDETAILCOUNT = count(STOCKDETAIL.ID)
from dbo.STOCKSALE
inner join dbo.STOCKDETAIL on STOCKSALE.STOCKDETAILID = STOCKDETAIL.ID
inner join dbo.REVENUEPAYMENTMETHOD on STOCKDETAIL.ID = REVENUEPAYMENTMETHOD.ID
where REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID and SALEPOSTSTATUSCODE = 0;
select @PROPERTYDETAILCOUNT = count(PROPERTYDETAIL.ID)
from dbo.PROPERTYDETAIL
inner join dbo.REVENUEPAYMENTMETHOD on PROPERTYDETAIL.ID = REVENUEPAYMENTMETHOD.ID
where REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID and SALEPOSTSTATUSCODE = 0;
/* If sold stock has been posted, log stock detail adjustment */
if (@PAYMENTMETHODCODE = 4) and (@STOCKDETAILCOUNT > 0) -- If stock detail records exist, some shares must have sold
begin
exec dbo.USP_SAVE_STOCKDETAILADJUSTMENT @REVENUEID, @STOCKDETAILADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON;
end
/* If sold property has been posted, log property detail adjustment */
else if (@PAYMENTMETHODCODE = 5) and (@PROPERTYDETAILCOUNT > 0) -- If a posted property detail record exists, the property must have sold
begin
exec dbo.USP_SAVE_PROPERTYDETAILADJUSTMENT @REVENUEID, @PROPERTYDETAILADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON;
end
insert into @ADJUSTED(REVENUEID, ADJUSTMENTID, STOCKDETAILADJUSTMENTID, PROPERTYDETAILADJUSTMENTID)
values(@REVENUEID, @ADJUSTMENTID, @STOCKDETAILADJUSTMENTID, @PROPERTYDETAILADJUSTMENTID);
fetch next from REVENUECURSOR into @REVENUEID;
end
deallocate REVENUECURSOR;
--log changes to history table
open HISTORYCURSOR;
fetch next from HISTORYCURSOR into @REVENUEID, @ADJUSTMENTID, @STOCKDETAILADJUSTMENTID, @PROPERTYDETAILADJUSTMENTID;
while @@FETCH_STATUS = 0
begin
/*call USP_ADJUSTMENTHISTORY_*_SAVEHISTORY after the revenue tables are updated */
if exists(select top 1 ID from dbo.REVENUE where ID = @REVENUEID)
begin
if @ADJUSTMENTID is not null
exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY @REVENUEID, @CHANGEAGENTID, null, @ADJUSTMENTID;
if @STOCKDETAILADJUSTMENTID is not null
exec dbo.USP_ADJUSTMENTHISTORY_STOCK_SAVEHISTORY @REVENUEID, @CHANGEAGENTID, null, @STOCKDETAILADJUSTMENTID;
if @PROPERTYDETAILADJUSTMENTID is not null
exec dbo.USP_ADJUSTMENTHISTORY_PROPERTY_SAVEHISTORY @REVENUEID, @CHANGEAGENTID, null, @PROPERTYDETAILADJUSTMENTID;
end
fetch next from HISTORYCURSOR into @REVENUEID, @ADJUSTMENTID, @STOCKDETAILADJUSTMENTID, @PROPERTYDETAILADJUSTMENTID;
end
--When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long
close HISTORYCURSOR;
deallocate HISTORYCURSOR;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
*/
return 0;