USP_DATAFORMTEMPLATE_EDIT_RESERVATION_1
The save procedure used by the edit dataform template "Reservation Edit Data 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. |
| @NAME | nvarchar(100) | IN | Reservation name |
| @COMMENTS | nvarchar(1000) | IN | Comments |
| @FINALCOUNTDUEDATE | date | IN | Final count date |
| @FINALCOUNTREQUIRED | bit | IN | Final count required |
| @DEPOSITREQUIRED | bit | IN | Deposit required |
| @DEPOSITAMOUNT | money | IN | Amount due |
| @DEPOSITDUEDATE | date | IN | Deposit due date |
| @DEPOSITTYPECODE | tinyint | IN | |
| @FINALDUEDATE | date | IN | Order balance due |
| @CONTRACTREQUIRED | bit | IN | Contract required |
| @CONTRACTDUEDATE | date | IN | Contract due date |
| @PRICINGCODE | tinyint | IN | Pricing structure |
| @RATESCALEID | uniqueidentifier | IN | Flat rate scale |
| @SECURITYDEPOSITREQUIRED | bit | IN | Security deposit required |
| @SECURITYDEPOSITAMOUNT | money | IN | Amount due |
| @SECURITYDEPOSITDUEDATE | date | IN | Security deposit due date |
| @SECURITYDEPOSITTYPECODE | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RESERVATION_1
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@NAME nvarchar(100),
@COMMENTS nvarchar(1000),
@FINALCOUNTDUEDATE date,
@FINALCOUNTREQUIRED bit,
@DEPOSITREQUIRED bit,
@DEPOSITAMOUNT money,
@DEPOSITDUEDATE date,
@DEPOSITTYPECODE tinyint,
@FINALDUEDATE date,
@CONTRACTREQUIRED bit,
@CONTRACTDUEDATE date,
@PRICINGCODE tinyint,
@RATESCALEID uniqueidentifier,
@SECURITYDEPOSITREQUIRED bit,
@SECURITYDEPOSITAMOUNT money,
@SECURITYDEPOSITDUEDATE date,
@SECURITYDEPOSITTYPECODE tinyint
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
declare @CONTRACTRECEIVED bit;
declare @CONTRACTSENT bit;
declare @FINALCOUNTRECEIVED bit;
begin try
declare @STATUSCODE tinyint;
select @STATUSCODE = SALESORDER.STATUSCODE
from dbo.SALESORDER
where SALESORDER.ID = @ID
if @STATUSCODE in (1, 5)
raiserror('BBERR_INVALIDSTATUS', 13, 1);
if (@FINALCOUNTREQUIRED = 0)
begin
set @FINALCOUNTDUEDATE = null;
set @FINALCOUNTRECEIVED = 0;
end
if (@CONTRACTREQUIRED = 0)
begin
set @CONTRACTDUEDATE = null;
set @CONTRACTRECEIVED = 0;
set @CONTRACTSENT = 0;
end
if (@DEPOSITREQUIRED = 0)
begin
set @DEPOSITDUEDATE = null;
set @DEPOSITAMOUNT = 0.0;
end
if (@SECURITYDEPOSITREQUIRED = 0)
begin
set @SECURITYDEPOSITDUEDATE = null;
set @SECURITYDEPOSITAMOUNT = 0.0;
end
-- Validation for deposit amount
if @DEPOSITREQUIRED = 1 and @DEPOSITAMOUNT <= 0.00 and @DEPOSITTYPECODE = 0
begin
raiserror('ERR_DEPOSITAMOUNT', 13, 1);
end
-- Validation for security deposit amount
if @SECURITYDEPOSITREQUIRED = 1 and @SECURITYDEPOSITAMOUNT <= 0.00 and @SECURITYDEPOSITTYPECODE = 0
begin
raiserror('ERR_SECURITYDEPOSITAMOUNT', 13, 1);
end
-- Validation for flat rate
if @PRICINGCODE = 1 and @RATESCALEID is null
begin
raiserror('ERR_RATESCALE',13,1);
end
update
dbo.RESERVATION
set
NAME = @NAME,
DEPOSITREQUIRED = @DEPOSITREQUIRED,
DEPOSITAMOUNT = @DEPOSITAMOUNT,
DEPOSITDUEDATE = @DEPOSITDUEDATE,
SECURITYDEPOSITREQUIRED = @SECURITYDEPOSITREQUIRED,
SECURITYDEPOSITAMOUNT = @SECURITYDEPOSITAMOUNT,
SECURITYDEPOSITDUEDATE = @SECURITYDEPOSITDUEDATE,
FINALDUEDATE = @FINALDUEDATE,
CONTRACTREQUIRED = @CONTRACTREQUIRED,
CONTRACTDUEDATE = @CONTRACTDUEDATE,
CONTRACTSENT = isnull(@CONTRACTSENT, CONTRACTSENT),
CONTRACTRECEIVED = isnull(@CONTRACTRECEIVED, CONTRACTRECEIVED),
FINALCOUNTDUEDATE = @FINALCOUNTDUEDATE,
FINALCOUNTREQUIRED = @FINALCOUNTREQUIRED,
FINALCOUNTRECEIVED = isnull(@FINALCOUNTRECEIVED, FINALCOUNTRECEIVED),
PRICINGCODE = @PRICINGCODE,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @ID;
update
dbo.SALESORDER
set
COMMENTS = @COMMENTS,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @ID;
if @PRICINGCODE = 1 and @RATESCALEID is not null
begin
-- remove all discounts until flat rate discounts functionality is ready
delete from [dbo].[SALESORDERITEMITEMDISCOUNT] with (rowlock)
from
[dbo].[SALESORDERITEMITEMDISCOUNT] inner join [dbo].[SALESORDERITEM] on
[SALESORDERITEMITEMDISCOUNT].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
where
[SALESORDERITEM].[SALESORDERID] = @ID;
delete from dbo.SALESORDERITEMSCENARIOSDONE with (rowlock) where SALESORDERID = @ID;
delete from dbo.SALESORDERDISCOUNTSCENARIOCOMBINATION with (rowlock) where SALESORDERID = @ID;
delete from dbo.SALESORDERITEMDISCOUNTOPTION with (rowlock) where SALESORDERID = @ID;
delete from dbo.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION with (rowlock) where SALESORDERID = @ID
delete from dbo.SALESORDERITEM where SALESORDERID = @ID and TYPECODE = 5
delete from dbo.SALESORDERITEMORDERDISCOUNT with (rowlock)
from
[dbo].[SALESORDERITEMORDERDISCOUNT] inner join [dbo].[SALESORDERITEM] on
[SALESORDERITEMORDERDISCOUNT].[ID] = [SALESORDERITEM].[ID]
where
[SALESORDERITEM].[SALESORDERID] = @ID;
delete from dbo.SALESORDERMANUALDISCOUNT where SALESORDERID = @ID
declare @OLDRATESCALEID uniqueidentifier = null
declare @RATESCALEEXISTS bit = 0
select
@RATESCALEEXISTS = 1,
@OLDRATESCALEID = RATESCALEID
from dbo.RESERVATIONRATESCALE
where ID = @ID
if @OLDRATESCALEID is null or @OLDRATESCALEID <> @RATESCALEID
begin
exec dbo.USP_RESERVATION_UPDATERATESCALE @ID, @RATESCALEID, @CHANGEAGENTID, @CURRENTDATE;
end
end
exec dbo.USP_RESERVATION_UPDATEFLATRATE @ID, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_SALESORDER_CALCULATEFEES @ID, @CHANGEAGENTID;
if @PRICINGCODE = 1
begin
exec dbo.USP_RESERVATION_CALCULATEFLATRATEFEES @ID, @CHANGEAGENTID;
end
else
begin
exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @ID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @ID, @CHANGEAGENTID;
end
exec dbo.USP_SALESORDER_CALCULATETAXES @ID, @CHANGEAGENTID;
exec dbo.USP_RESERVATION_UPDATESTATUSANDHISTORY @ID, @CHANGEAGENTID;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;