USP_DATAFORMTEMPLATE_EDIT_REVENUEPAYMENT_3
The save procedure used by the edit dataform template "Revenue Payment Edit Form 3".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @DATE | datetime | IN | Date |
| @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 | Cardholder name |
| @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 |
| @POSTSTATUSCODE | tinyint | IN | Post status |
| @POSTDATE | datetime | IN | Post date |
| @GIVENANONYMOUSLY | bit | IN | Payment is anonymous |
| @DONOTRECEIPT | bit | IN | Do not receipt |
| @REFERENCE | nvarchar(255) | IN | Reference |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUEPAYMENT_3
(
@ID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@DATE datetime,
@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,
@POSTSTATUSCODE tinyint,
@POSTDATE datetime,
@GIVENANONYMOUSLY bit,
@DONOTRECEIPT bit,
@REFERENCE nvarchar(255)
)
as
set nocount on;
declare @CHANGEDATE datetime;
declare @TYPECODE tinyint;
declare @CLEARALLGLDISTRIBUTIONS bit;
declare @CLEARREVENUEGLDISTRIBUTION bit;
set @CLEARALLGLDISTRIBUTIONS = 0;
set @CLEARREVENUEGLDISTRIBUTION = 0;
begin try
set @CHANGEDATE = getdate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
/* Validate payment information */
--IS THE REVENUE POSTED?
if exists ( select 1 from dbo.REVENUE
inner join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
where REVENUE.ID = @ID)
raiserror('You cannot edit a posted gift.', 13, 1)
if @POSTSTATUSCODE = 0
raiserror('You cannot edit a gift to post it.', 13, 1)
select @TYPECODE = TRANSACTIONTYPECODE
from dbo.REVENUE
where ID = @ID;
-- if the payment method or post status has changed, clear any user-defined gl distributions for this revenue record
if (select count(REVENUE.ID) from dbo.REVENUE
inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
where REVENUE.ID = @ID
and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = @PAYMENTMETHODCODE
and ((@POSTSTATUSCODE = 2 and REVENUE.DONOTPOST = 1) or (@POSTSTATUSCODE = 1 and REVENUE.DONOTPOST = 0))
) = 0
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_REVENUEPAYMENT_EDIT_2 @ID, @CURRENTAPPUSERID, @CHANGEAGENTID, @DATE,
@PAYMENTMETHODCODE, @CHECKDATE, @CHECKNUMBER, @REFERENCEDATE, @REFERENCENUMBER,
@CARDHOLDERNAME, @CREDITCARDNUMBER, @CREDITTYPECODEID, @AUTHORIZATIONCODE, @EXPIRESON,
@ISSUER, @NUMBEROFUNITS, @SYMBOL, @MEDIANPRICE, @GIFTINKINDSUBTYPECODEID,
@PROPERTYSUBTYPECODEID, @CONSTITUENTACCOUNTID, @GIVENANONYMOUSLY, @DONOTRECEIPT, @CHANGEDATE,
@OTHERPAYMENTMETHODCODEID, @REFERENCE;
/*JamesWill 2007/03/23 Only run this update if POSTDATE or DONOTPOST actually changed to avoid unnecessary rows in the audit table.
Also, add CHANGEDBYID and DATECHANGED to the update statement */
declare @OLDPOSTDATE datetime;
declare @OLDDONOTPOST bit;
declare @DONOTPOST bit;
select @DONOTPOST = case when @POSTSTATUSCODE = 2 then 1 else 0 end;
select top 1 @OLDPOSTDATE = POSTDATE, @OLDDONOTPOST = DONOTPOST from dbo.REVENUE where ID = @ID;
if @OLDPOSTDATE <> @POSTDATE or @OLDDONOTPOST <> @DONOTPOST
begin
update dbo.REVENUE
set POSTDATE = @POSTDATE,
DONOTPOST = case when @POSTSTATUSCODE = 2 then 1 else 0 end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = @ID;
update dbo.PROPERTYDETAIL
set SALEPOSTSTATUSCODE = @POSTSTATUSCODE,
SALEPOSTDATE = case @POSTSTATUSCODE when 2 then null else coalesce(SALEPOSTDATE, @POSTDATE) end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where
ID in
(
select REVENUEPAYMENTMETHOD.ID from dbo.REVENUEPAYMENTMETHOD where REVENUEID = @ID and PAYMENTMETHODCODE = 5
) and
PROPERTYDETAIL.ISSOLD = 1; -- Property sold
end
if @CLEARALLGLDISTRIBUTIONS = 1
begin
--Clear GL
--Cache CONTEXT INFO
declare @contextCache varbinary(128);
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
-- 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;
--Restore CONTEXT_INFO
if not @contextCache is null
set CONTEXT_INFO @contextCache;
-- Add new GL distributions
if @POSTSTATUSCODE <> 2
begin
-- Add new GL distributions
exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;
-- Add new stock detail GL distributions
exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;
-- Add new property detail GL distributions
exec dbo.USP_SAVE_PROPERTYDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;
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, @CHANGEDATE;
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
return 0;