USP_DATAFORMTEMPLATE_EDIT_PAYMENTMETHOD
The save procedure used by the edit dataform template "Payment Method 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. |
| @REFERENCE | nvarchar(255) | IN | Description |
| @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 | int | 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 |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PAYMENTMETHOD
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@REFERENCE nvarchar(255),
@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 int,
@SYMBOL nvarchar(25),
@MEDIANPRICE decimal(19,4),
@GIFTINKINDSUBTYPECODEID uniqueidentifier,
@PROPERTYSUBTYPECODEID uniqueidentifier,
@CONSTITUENTACCOUNTID uniqueidentifier,
@DIRECTDEBITRESULTCODE nvarchar(10),
@LOWPRICE decimal(19,4),
@HIGHPRICE decimal(19,4)
)
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 @CLEARSTOCKGLDISTRIBUTION bit;
set @CLEARALLGLDISTRIBUTIONS = 0;
set @CLEARSTOCKGLDISTRIBUTION = 0;
declare @POSTSTATUSCODE tinyint;
declare @AMOUNT money;
select @AMOUNT= REVENUE.AMOUNT,
@POSTSTATUSCODE = case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end
from dbo.REVENUE
left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
where REVENUE.ID = @ID and REVENUE.TRANSACTIONTYPECODE = 0
declare @contextCache varbinary(128);
--cache current context information
set @contextCache = CONTEXT_INFO();
--set CONTEXT_INFO to @CHANGEAGENTID
set CONTEXT_INFO @CHANGEAGENTID;
begin try
declare @ORIGINALPAYMETHODID uniqueidentifier, @ORIGINALPAYMENTMETHODCODE tinyint
select
@ORIGINALPAYMETHODID = ID,
@ORIGINALPAYMENTMETHODCODE = PAYMENTMETHODCODE
from dbo.REVENUEPAYMENTMETHOD
where REVENUEID = @ID
-- check to see if payment method has changed
if @ORIGINALPAYMENTMETHODCODE <> @PAYMENTMETHODCODE
begin
set @CLEARALLGLDISTRIBUTIONS = 1;
end
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
-- Reset stock GL distribution when the number of units or median price changed
if @PAYMENTMETHODCODE = 4
begin
declare @ORIGINALNUMBEROFUNITS int, @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
end
end
if @REFERENCE is null
exec dbo.USP_REVENUEREFERENCE_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
else
begin
if exists(select top 1 ID from dbo.REVENUEREFERENCE where ID = @ID)
update dbo.REVENUEREFERENCE
set REFERENCE = @REFERENCE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
else
insert into dbo.REVENUEREFERENCE (ID, REFERENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (@ID, @REFERENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
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
-- 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
begin
if @CLEARSTOCKGLDISTRIBUTION = 1
begin
delete from dbo.STOCKSALEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
-- Add new stock detail GL distributions
if @POSTSTATUSCODE <> 2
exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
end
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;