USP_DATAFORMTEMPLATE_EDIT_PAYMENTADJUST
The save procedure used by the edit dataform template "Posted Payment Edit Form".
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 |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PAYMENTADJUST
(
@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),
@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 @REVENUEPAYMENTMETHODID uniqueidentifier;
declare @PAYMENTMETHODCODE tinyint;
declare @ADJUSTMENTID uniqueidentifier;
declare @STOCKSALEADJUSTMENTIDS xml;
declare @PROPERTYDETAILADJUSTMENTID uniqueidentifier;
declare @ADJUST bit
declare @PROPERTYDETAILCOUNT int;
declare @STOCKDETAILCOUNT int;
declare @CLEARGLDISTRIBUTION bit;
set @ADJUST = 0
set @PROPERTYDETAILCOUNT = 0;
set @STOCKDETAILCOUNT = 0;
set @CLEARGLDISTRIBUTION = 0;
begin try
select top 1
@REVENUEPAYMENTMETHODID = ID,
@PAYMENTMETHODCODE = PAYMENTMETHODCODE
from dbo.REVENUEPAYMENTMETHOD
where REVENUEID = @ID
/* 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
/* Check if revenue streams changed */
if @ADJUST = 0
if dbo.UFN_CHECKDETAIL_STREAMSCHANGED(@ID, @REVENUESTREAMS) = 1
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 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 = @REVENUEPAYMENTMETHODID and SALEPOSTSTATUSCODE = 0;
select @PROPERTYDETAILCOUNT = count(PROPERTYDETAIL.ID)
from dbo.PROPERTYDETAIL
where PROPERTYDETAIL.ID = @REVENUEPAYMENTMETHODID and SALEPOSTSTATUSCODE = 0;
/* If sold stock has been posted, log stock detail adjustment */
if (@STOCKDETAILCOUNT > 0) and @PAYMENTMETHODCODE = 4
begin
exec dbo.USP_SAVE_STOCKDETAILADJUSTMENT @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @STOCKSALEADJUSTMENTIDS output
end
/* If sold property has been posted, log property detail adjustment */
else if (@PAYMENTMETHODCODE = 5) and (@PROPERTYDETAILCOUNT > 0)
begin
exec dbo.USP_SAVE_PROPERTYDETAILADJUSTMENT @REVENUEPAYMENTMETHODID, @PROPERTYDETAILADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON;
end
end
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
-- if the payment method has changed, clear any user-defined gl distributions for all revenue records
if @CLEARGLDISTRIBUTION = 1
begin
--Clear GL
--Cache CONTEXT INFO
declare @contextCache varbinary(128);
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
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;
--Restore CONTEXT_INFO
if not @contextCache is null
set CONTEXT_INFO @contextCache;
-- 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
/* 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 @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, null, @STOCKSALEADJUSTMENTIDS;
if @PROPERTYDETAILADJUSTMENTID is not null
exec dbo.USP_ADJUSTMENTHISTORY_PROPERTY_SAVEHISTORY @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, null, @PROPERTYDETAILADJUSTMENTID;
end
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;