USP_DATAFORMTEMPLATE_EDIT_PAYMENT
The save procedure used by the edit dataform template "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 |
| @POSTSTATUSCODE | tinyint | IN | Post status |
| @POSTDATE | datetime | IN | Post date |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PAYMENT
(
@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),
@POSTSTATUSCODE tinyint,
@POSTDATE datetime,
@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 @CLEARALLGLDISTRIBUTIONS bit;
declare @CLEARREVENUEGLDISTRIBUTION bit;
set @CLEARALLGLDISTRIBUTIONS = 0;
set @CLEARREVENUEGLDISTRIBUTION = 0;
declare @contextCache varbinary(128);
--cache current context information
set @contextCache = CONTEXT_INFO();
--set CONTEXT_INFO to @CHANGEAGENTID
set CONTEXT_INFO @CHANGEAGENTID;
begin try
if @POSTSTATUSCODE = 0
raiserror('You cannot edit a gift to post it.', 13, 1)
declare @DONOTPOST bit;
select @DONOTPOST = case when @POSTSTATUSCODE = 2 then 1 else 0 end;
-- check to see if amount or post status has changed
if (
select count(REVENUE.ID) from dbo.REVENUE
where REVENUE.ID = @ID
and AMOUNT = @AMOUNT
and ((@POSTSTATUSCODE = 2 and DONOTPOST = 1) or (@POSTSTATUSCODE = 1 and DONOTPOST = 0))
) = 0
begin
set @CLEARALLGLDISTRIBUTIONS = 1;
end
/* Check if revenue streams changed */
if @CLEARALLGLDISTRIBUTIONS = 0
if dbo.UFN_CHECKDETAIL_STREAMSCHANGED(@ID, @REVENUESTREAMS) = 1
begin
set @CLEARALLGLDISTRIBUTIONS = 1;
end
-- check to see if post date has changed
if @CLEARALLGLDISTRIBUTIONS = 0
if (
select count(REVENUE.ID) from dbo.REVENUE
where REVENUE.ID = @ID
and POSTDATE = @POSTDATE
) = 0
begin
set @CLEARREVENUEGLDISTRIBUTION = 1;
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
declare @OLDPOSTDATE datetime;
declare @OLDDONOTPOST bit;
select @OLDPOSTDATE = POSTDATE, @OLDDONOTPOST = DONOTPOST from dbo.REVENUE where ID = @ID;
if @OLDPOSTDATE <> @POSTDATE or @OLDDONOTPOST <> @DONOTPOST
begin
if @POSTDATE is null and @DONOTPOST = 0
raiserror ('CK_REVENUE_POSTDATE_REQUIRED', 16, 1);
update dbo.FINANCIALTRANSACTION set
POSTDATE = @POSTDATE,
POSTSTATUSCODE = case FINANCIALTRANSACTION.POSTSTATUSCODE when 2 then 2 else case @DONOTPOST when 1 then 3 else 1 end end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
update dbo.FINANCIALTRANSACTIONLINEITEM set
POSTDATE = @POSTDATE
,POSTSTATUSCODE = case FINANCIALTRANSACTION.POSTSTATUSCODE when 2 then 2 else case @DONOTPOST when 1 then 3 else 1 end end
,CHANGEDBYID = @CHANGEAGENTID
,DATECHANGED = @CURRENTDATE
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.FINANCIALTRANSACTION on @ID = FINANCIALTRANSACTION.ID
left outer join dbo.FINANCIALTRANSACTIONLINEITEM REVERSE on FINANCIALTRANSACTIONLINEITEM.ID = REVERSE.REVERSEDLINEITEMID
where FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE != 2 and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1 AND REVERSE.ID is null;
update dbo.PROPERTYDETAIL
set SALEPOSTSTATUSCODE = @POSTSTATUSCODE,
SALEPOSTDATE = case @POSTSTATUSCODE when 2 then null else coalesce(SALEPOSTDATE, @POSTDATE) end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID in
(
select REVENUEPAYMENTMETHOD.ID from dbo.REVENUEPAYMENTMETHOD where REVENUEID = @ID and PAYMENTMETHODCODE = 5
) and ISSOLD = 1;
end
-- clear the user-defined gl distributions
if @CLEARALLGLDISTRIBUTIONS = 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
if @POSTSTATUSCODE <> 2
begin
-- 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
end
else if @CLEARREVENUEGLDISTRIBUTION = 1 --if just post date has changed, only clear revenue distributions
begin
-- Clear GL
delete from dbo.REVENUEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
-- Add new GL distributions
if @POSTSTATUSCODE <> 2
begin
-- Add new GL distributions
exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
end
end
if (@CLEARALLGLDISTRIBUTIONS = 1 and @POSTSTATUSCODE <> 2) or (@CLEARREVENUEGLDISTRIBUTION = 1 and @POSTSTATUSCODE <> 2)
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
--reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
--reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
return 0;