USP_DATAFORMTEMPLATE_EDIT_PAYMENTADJUST2
The save procedure used by the edit dataform template "Posted Payment Edit Form 2".
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. |
| @DATE | datetime | IN | Date |
| @AMOUNT | money | IN | Amount |
| @RECEIPTAMOUNT | money | IN | Receipt amount |
| @REVENUESTREAMS | xml | IN | Revenue streams |
| @SOURCECODE | nvarchar(50) | IN | Source code |
| @APPEALID | uniqueidentifier | IN | Appeal |
| @BENEFITS | xml | IN | Benefits |
| @BENEFITSWAIVED | bit | IN | Benefits waived |
| @GIVENANONYMOUSLY | bit | IN | Payment is anonymous |
| @MAILINGID | uniqueidentifier | IN | Mailing |
| @CHANNELCODEID | uniqueidentifier | IN | Channel |
| @DONOTRECEIPT | bit | IN | Do not receipt |
| @DONOTACKNOWLEDGE | bit | IN | Do not acknowledge |
| @REFERENCE | nvarchar(255) | IN | Reference |
| @ADJUSTMENTDATE | datetime | IN | Adjustment date |
| @ADJUSTMENTPOSTDATE | datetime | IN | Adjustment post date |
| @ADJUSTMENTREASON | nvarchar(300) | IN | Adjustment reason |
| @PAYMENTMETHODCODE | tinyint | IN | Payment method |
| @OTHERPAYMENTMETHODCODEID | uniqueidentifier | IN | Other method |
| @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(4) | 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 |
| @GIFTINKINDSUBTYPECODEID | uniqueidentifier | IN | Subtype |
| @PROPERTYSUBTYPECODEID | uniqueidentifier | IN | Subtype |
| @CONSTITUENTACCOUNTID | uniqueidentifier | IN | Account |
| @DIRECTDEBITRESULTCODE | nvarchar(10) | IN | Result code |
| @LOWPRICE | decimal(19, 4) | IN | Low price |
| @HIGHPRICE | decimal(19, 4) | IN | High price |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PAYMENTADJUST2
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@DATE datetime,
@AMOUNT money,
@RECEIPTAMOUNT money ,
@REVENUESTREAMS xml ,
@SOURCECODE nvarchar(50),
@APPEALID uniqueidentifier,
@BENEFITS xml,
@BENEFITSWAIVED bit,
@GIVENANONYMOUSLY bit,
@MAILINGID uniqueidentifier,
@CHANNELCODEID uniqueidentifier,
@DONOTRECEIPT bit,
@DONOTACKNOWLEDGE bit,
@REFERENCE nvarchar(255),
@ADJUSTMENTDATE datetime,
@ADJUSTMENTPOSTDATE datetime,
@ADJUSTMENTREASON nvarchar(300),
@PAYMENTMETHODCODE tinyint,
@OTHERPAYMENTMETHODCODEID uniqueidentifier,
@CHECKDATE dbo.UDT_FUZZYDATE,
@CHECKNUMBER nvarchar(20),
@REFERENCEDATE dbo.UDT_FUZZYDATE,
@REFERENCENUMBER nvarchar(20),
@CARDHOLDERNAME nvarchar(255),
@CREDITCARDNUMBER nvarchar(4),
@CREDITTYPECODEID uniqueidentifier,
@AUTHORIZATIONCODE nvarchar(20),
@EXPIRESON dbo.UDT_FUZZYDATE,
@ISSUER nvarchar(100),
@NUMBEROFUNITS decimal(20,3),
@SYMBOL nvarchar(25),
@MEDIANPRICE decimal(19,4),
@GIFTINKINDSUBTYPECODEID uniqueidentifier,
@PROPERTYSUBTYPECODEID uniqueidentifier,
@CONSTITUENTACCOUNTID uniqueidentifier,
@DIRECTDEBITRESULTCODE nvarchar(10),
@LOWPRICE decimal(19,4),
@HIGHPRICE decimal(19,4),
@CURRENTAPPUSERID uniqueidentifier = null
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = GetDate();
declare @ADJUSTMENTID uniqueidentifier;
declare @STOCKSALEADJUSTMENTIDS xml;
declare @PROPERTYDETAILADJUSTMENTID uniqueidentifier;
declare @ADJUST bit;
declare @ADJUSTSTOCK bit;
declare @PROPERTYDETAILCOUNT int;
declare @STOCKDETAILCOUNT int;
declare @CLEARGLDISTRIBUTION bit;
declare @CLEARSTOCKGLDISTRIBUTION bit;
set @ADJUST = 0;
set @ADJUSTSTOCK = 0;
set @PROPERTYDETAILCOUNT = 0;
set @STOCKDETAILCOUNT = 0;
set @CLEARGLDISTRIBUTION = 0;
set @CLEARSTOCKGLDISTRIBUTION = 0;
begin try
/* Check if amount changed */
if (select COUNT(REVENUE.ID) from dbo.REVENUE where REVENUE.ID = @ID
and AMOUNT = @AMOUNT) = 0
begin
set @ADJUST = 1;
set @CLEARGLDISTRIBUTION = 1;
end
declare @ORIGINALPAYMETHODID uniqueidentifier, @ORIGINALPAYMENTMETHODCODE tinyint
select
@ORIGINALPAYMETHODID = ID,
@ORIGINALPAYMENTMETHODCODE = PAYMENTMETHODCODE
from dbo.REVENUEPAYMENTMETHOD
where REVENUEID = @ID
declare @SALEDATE datetime
declare @SALEAMOUNT money
declare @BROKERFEE money
declare @SALEPOSTDATE datetime
declare @SALEPOSTSTATUSCODE tinyint
if @PAYMENTMETHODCODE = 5
begin
select top 1
@SALEDATE = SALEDATE,
@SALEAMOUNT = SALEAMOUNT,
@BROKERFEE = BROKERFEE,
@SALEPOSTDATE = SALEPOSTDATE,
@SALEPOSTSTATUSCODE = SALEPOSTSTATUSCODE
from dbo.PROPERTYDETAIL
where ID = @ORIGINALPAYMETHODID and ISSOLD = 1
end
/* Check if revenue streams changed */
if @ADJUST = 0
if dbo.UFN_CHECKDETAIL_STREAMSCHANGED(@ID, @REVENUESTREAMS) = 1 or @PAYMENTMETHODCODE <> @ORIGINALPAYMENTMETHODCODE
begin
set @ADJUST = 1;
set @CLEARGLDISTRIBUTION = 1;
end
/* Already adjusted */
if @ADJUST = 0
if (select COUNT(ADJUSTMENT.ID)
from dbo.ADJUSTMENT
where ADJUSTMENT.REVENUEID = @ID and ADJUSTMENT.POSTSTATUSCODE = 1) > 0
set @ADJUST = 1;
-- If NUMBEROFUNITS or MEDIANPRICE changed and stock has already been sold, the GL distributions
-- for stocks need to be reset since the gain/loss value will have changed for that stock sale. Also,
-- if those values changed and sold stock has posted, the stock needs to be adjusted.
if @PAYMENTMETHODCODE = 4
begin
declare @ORIGINALNUMBEROFUNITS decimal(20,3), @ORIGINALMEDIANPRICE decimal(19,4)
select
@ORIGINALNUMBEROFUNITS = NUMBEROFUNITS,
@ORIGINALMEDIANPRICE = MEDIANPRICE
from dbo.STOCKDETAIL where ID = @ORIGINALPAYMETHODID
if (@ORIGINALNUMBEROFUNITS <> @NUMBEROFUNITS or @ORIGINALMEDIANPRICE <> @MEDIANPRICE) and
exists (select 1 from dbo.STOCKSALE where STOCKDETAILID = @ORIGINALPAYMETHODID)
begin
set @CLEARSTOCKGLDISTRIBUTION = 1;
-- Indicate the stock sales need to be adjusted if they were already sold
if exists (select 1 from dbo.STOCKSALE where STOCKDETAILID = @ORIGINALPAYMETHODID and SALEPOSTSTATUSCODE = 0)
set @ADJUSTSTOCK = 1;
end
end
/* If there was a change to GL related data log an adjustment for each revenue in the transaction */
if @ADJUST = 1
begin
set @ADJUSTMENTID = null;
exec dbo.USP_SAVE_ADJUSTMENT @ID, @ADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON;
select @STOCKDETAILCOUNT = count(STOCKSALE.ID)
from dbo.STOCKSALE
where STOCKDETAILID = @ORIGINALPAYMETHODID and SALEPOSTSTATUSCODE = 0;
select @PROPERTYDETAILCOUNT = count(PROPERTYDETAIL.ID)
from dbo.PROPERTYDETAIL
where PROPERTYDETAIL.ID = @ORIGINALPAYMETHODID and SALEPOSTSTATUSCODE = 0;
/* If sold stock has been posted, log stock detail adjustment */
if (@STOCKDETAILCOUNT > 0) and @ORIGINALPAYMENTMETHODCODE = 4
begin
exec dbo.USP_SAVE_STOCKDETAILADJUSTMENT @ORIGINALPAYMETHODID, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @STOCKSALEADJUSTMENTIDS output
end
/* If sold property has been posted, log property detail adjustment */
else if (@ORIGINALPAYMENTMETHODCODE = 5) and (@PROPERTYDETAILCOUNT > 0)
begin
exec dbo.USP_SAVE_PROPERTYDETAILADJUSTMENT @ORIGINALPAYMETHODID, @PROPERTYDETAILADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON;
end
end
else
begin
-- If the entire transaction isn't being adjusted, check if just the stock sales need to be adjusted
if @ADJUSTSTOCK = 1
exec dbo.USP_SAVE_STOCKDETAILADJUSTMENT @ORIGINALPAYMETHODID, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @STOCKSALEADJUSTMENTIDS output
end
declare @OLDGIFTAID xml;
set @OLDGIFTAID = (
select REVENUESPLIT.ID, REVENUESPLIT.DESIGNATIONID, coalesce(REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID, '00000000-0000-0000-0000-000000000000') as GLREVENUECATEGORYMAPPINGID, dbo.UFN_GIFTAIDREVENUESPLIT_GETQUALIFICATIONSTATUS(REVENUESPLIT.ID) as STATUS
from dbo.REVENUESPLIT
inner join dbo.REVENUESPLITGIFTAID on REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID
left join dbo.REVENUECATEGORY on REVENUESPLIT.ID = REVENUECATEGORY.ID
where REVENUEID = @ID
for xml raw('ITEM'),type,elements,root('GIFTAIDQUALIFICATIONSTATUS'),BINARY BASE64
)
-- create reversals for gift aid tied to splits that have been deleted.
exec dbo.USP_REVENUESPLITGIFTAID_CREATEREVERSALSFORDELETEDSPLITS @OLDGIFTAID, @REVENUESTREAMS, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_PAYMENT_EDIT_2
@ID=@ID, @CHANGEAGENTID=@CHANGEAGENTID, @CURRENTDATE=@CURRENTDATE, @DATE=@DATE, @AMOUNT=@AMOUNT, @RECEIPTAMOUNT=@RECEIPTAMOUNT,
@REVENUESTREAMS=@REVENUESTREAMS, @SOURCECODE=@SOURCECODE, @APPEALID=@APPEALID,@BENEFITS=@BENEFITS, @BENEFITSWAIVED=@BENEFITSWAIVED,
@GIVENANONYMOUSLY=@GIVENANONYMOUSLY, @MAILINGID=@MAILINGID, @CHANNELCODEID=@CHANNELCODEID, @DONOTRECEIPT=@DONOTRECEIPT, @REFERENCE=@REFERENCE, @DONOTACKNOWLEDGE=@DONOTACKNOWLEDGE, @CURRENTAPPUSERID=@CURRENTAPPUSERID
update dbo.REVENUEPAYMENTMETHOD set
PAYMENTMETHODCODE = @PAYMENTMETHODCODE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ORIGINALPAYMETHODID
exec dbo.USP_REVENUE_UPDATEPAYMENTDETAILS
@PAYMENTMETHODID = @ORIGINALPAYMETHODID,
@PAYMENTMETHODCODE = @PAYMENTMETHODCODE,
@CHECKDATE = @CHECKDATE,
@CHECKNUMBER = @CHECKNUMBER,
@CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
@REFERENCEDATE = @REFERENCEDATE,
@REFERENCENUMBER = @REFERENCENUMBER,
@CARDHOLDERNAME = @CARDHOLDERNAME,
@CREDITCARDNUMBER = @CREDITCARDNUMBER,
@CREDITTYPECODEID = @CREDITTYPECODEID,
@AUTHORIZATIONCODE = @AUTHORIZATIONCODE,
@EXPIRESON = @EXPIRESON,
@ISSUER = @ISSUER,
@NUMBEROFUNITS = @NUMBEROFUNITS,
@SYMBOL = @SYMBOL,
@MEDIANPRICE = @MEDIANPRICE,
@PROPERTYSUBTYPECODEID = @PROPERTYSUBTYPECODEID,
@GIFTINKINDSUBTYPECODEID = @GIFTINKINDSUBTYPECODEID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CHANGEDATE = @CURRENTDATE,
@KEYALREADYOPEN = 0,
@OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID,
@DIRECTDEBITRESULTCODE = @DIRECTDEBITRESULTCODE,
@LOWPRICE = @LOWPRICE,
@HIGHPRICE = @HIGHPRICE,
@SALEDATE = @SALEDATE,
@SALEAMOUNT = @SALEAMOUNT,
@BROKERFEE = @BROKERFEE,
@SALEPOSTDATE = @SALEPOSTDATE,
@SALEPOSTSTATUSCODE = @SALEPOSTSTATUSCODE,
@REVENUEAMOUNT = @AMOUNT,
@ORIGINALPAYMENTMETHODCODE = @ORIGINALPAYMENTMETHODCODE
--Cache CONTEXT INFO
declare @contextCache varbinary(128);
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
-- if the payment method has changed, clear any user-defined gl distributions for all revenue records
if @CLEARGLDISTRIBUTION = 1
begin
--Clear GL
delete from dbo.REVENUEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
delete from dbo.STOCKSALEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
delete from dbo.PROPERTYDETAILGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
-- Add new GL distributions
exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
-- Add new stock detail GL distributions
exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
-- Add new property detail GL distributions
exec dbo.USP_SAVE_PROPERTYDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
end
else
begin
if @CLEARSTOCKGLDISTRIBUTION = 1
begin
--Clear stock GL
delete from dbo.STOCKSALEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
-- Add new stock detail GL distributions
exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
end
end
--Restore CONTEXT_INFO
if not @contextCache is null
set CONTEXT_INFO @contextCache;
/* add adjustment history information */
if @ADJUST = 1
begin
if @ADJUSTMENTID is not null
exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY @ID, @CHANGEAGENTID, null, @ADJUSTMENTID;
if @STOCKSALEADJUSTMENTIDS is not null
exec dbo.USP_ADJUSTMENTHISTORY_STOCK_SAVEHISTORY @ORIGINALPAYMETHODID, @CHANGEAGENTID, null, @STOCKSALEADJUSTMENTIDS;
if @PROPERTYDETAILADJUSTMENTID is not null
exec dbo.USP_ADJUSTMENTHISTORY_PROPERTY_SAVEHISTORY @ORIGINALPAYMETHODID, @CHANGEAGENTID, null, @PROPERTYDETAILADJUSTMENTID;
end
else
begin
-- If the entire transaction wasn't adjusted, check if just the stocks need to be adjusted
if @ADJUSTSTOCK = 1 and @STOCKSALEADJUSTMENTIDS is not null
exec dbo.USP_ADJUSTMENTHISTORY_STOCK_SAVEHISTORY @ORIGINALPAYMETHODID, @CHANGEAGENTID, null, @STOCKSALEADJUSTMENTIDS;
end
exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, 0, null; --revenue transaction type code of payment is 0
-- update existing gift aid GL distributions
exec dbo.USP_REVENUESPLITGIFTAID_UPDATEGLDISTRIBUTIONS_FORREVENUE @ID, @OLDGIFTAID, 0, 0, @CHANGEAGENTID, @CURRENTDATE;
if @CLEARGLDISTRIBUTION = 1
begin
declare @DEPOSITID uniqueidentifier;
select @DEPOSITID = DEPOSITID
from dbo.BANKACCOUNTDEPOSITPAYMENT
where ID = @ID;
if @DEPOSITID is not null
exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS @ID, @DEPOSITID, @CHANGEAGENTID, @CURRENTDATE;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;