USP_DATAFORMTEMPLATE_EDITLOAD_RESERVATION

The load procedure used by the edit dataform template "Reservation Edit Data Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@NAME	nvarchar(100)	INOUT	Reservation name
@COMMENTS	nvarchar(1000)	INOUT	Comments
@FINALCOUNTDUEDATE	date	INOUT	Final count date
@FINALCOUNTREQUIRED	bit	INOUT	Final count required
@DEPOSITREQUIRED	bit	INOUT	Deposit required
@DEPOSITAMOUNT	money	INOUT	Amount due
@DEPOSITDUEDATE	date	INOUT	Deposit due date
@TOTALAMOUNTDUE	money	INOUT
@DEFAULTAMOUNT	money	INOUT
@DEFAULTPERCENT	decimal(5, 2)	INOUT
@DEFAULTTYPECODE	tinyint	INOUT
@DEPOSITTYPECODE	tinyint	INOUT
@DEFAULTDEPOSITREQUIRED	bit	INOUT
@FINALDUEDATE	date	INOUT	Order balance due
@CONTRACTREQUIRED	bit	INOUT	Contract required
@CONTRACTDUEDATE	date	INOUT	Contract due date
@PRICINGCODE	tinyint	INOUT	Pricing structure
@INITIALRATESCALEID	uniqueidentifier	INOUT
@RATESCALEID	uniqueidentifier	INOUT	Flat rate scale
@RATESCALEPRICES	xml	INOUT
@TOTALVISITORS	smallint	INOUT	Total visitors
@SECURITYDEPOSITREQUIRED	bit	INOUT	Security deposit required
@SECURITYDEPOSITAMOUNT	money	INOUT	Amount due
@SECURITYDEPOSITDUEDATE	date	INOUT	Security deposit due date
@DEFAULTSECURITYDEPOSITAMOUNT	money	INOUT
@DEFAULTSECURITYDEPOSITPERCENT	decimal(5, 2)	INOUT
@DEFAULTSECURITYDEPOSITTYPECODE	tinyint	INOUT
@SECURITYDEPOSITTYPECODE	tinyint	INOUT
@DEFAULTSECURITYDEPOSITREQUIRED	bit	INOUT

Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RESERVATION
                    (
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @TSLONG bigint = 0 output,
                        @NAME nvarchar(100) = null output,
                        @COMMENTS nvarchar(1000) = null output,
                        @FINALCOUNTDUEDATE date = null output,
                        @FINALCOUNTREQUIRED bit = null output,
                        @DEPOSITREQUIRED bit = null output,
                        @DEPOSITAMOUNT money = null output,
                        @DEPOSITDUEDATE date = null output,
                        @TOTALAMOUNTDUE money = null output,
                        @DEFAULTAMOUNT money = null output,
                        @DEFAULTPERCENT decimal(5,2) = null output,
                        @DEFAULTTYPECODE tinyint = null output,
                        @DEPOSITTYPECODE tinyint = null output,
                        @DEFAULTDEPOSITREQUIRED bit = null output,
                        @FINALDUEDATE date = null output,
                        @CONTRACTREQUIRED bit = null output,
                        @CONTRACTDUEDATE date = null output,
                        @PRICINGCODE tinyint = null output,
                        @INITIALRATESCALEID uniqueidentifier = null output,
                        @RATESCALEID uniqueidentifier = null output,
                        @RATESCALEPRICES xml = null output,
                        @TOTALVISITORS smallint = null output,
                        @SECURITYDEPOSITREQUIRED bit = null output,
                        @SECURITYDEPOSITAMOUNT money = null output,
                        @SECURITYDEPOSITDUEDATE date = null output,
                        @DEFAULTSECURITYDEPOSITAMOUNT money = null output,
                        @DEFAULTSECURITYDEPOSITPERCENT decimal(5,2) = null output,
                        @DEFAULTSECURITYDEPOSITTYPECODE tinyint = null output,
                        @SECURITYDEPOSITTYPECODE tinyint = null output,
                        @DEFAULTSECURITYDEPOSITREQUIRED bit = null output
                    )
                    as
                        set nocount on;

                        declare @SALESORDERTSLONG bigint;

                        set @DATALOADED = 0;
                        set @TSLONG = 0;

                        select
                            @DATALOADED = 1,
                            @TSLONG = RESERVATION.TSLONG,
                            @SALESORDERTSLONG = SALESORDER.TSLONG,
                            @NAME = RESERVATION.NAME,
                            @COMMENTS = SALESORDER.COMMENTS,
                            @DEPOSITREQUIRED = DEPOSITREQUIRED,
                            @DEPOSITAMOUNT = DEPOSITAMOUNT,
                            @DEPOSITDUEDATE = DEPOSITDUEDATE,
                            @TOTALAMOUNTDUE = dbo.UFN_SALESORDER_GETAMOUNTDUE(@ID),
                            @FINALDUEDATE = FINALDUEDATE,
                            @FINALCOUNTDUEDATE = FINALCOUNTDUEDATE,
                            @FINALCOUNTREQUIRED = FINALCOUNTREQUIRED,
                            @CONTRACTREQUIRED = CONTRACTREQUIRED,
                            @CONTRACTDUEDATE = CONTRACTDUEDATE,
                            @PRICINGCODE = PRICINGCODE,
                            @SECURITYDEPOSITREQUIRED = SECURITYDEPOSITREQUIRED,
                            @SECURITYDEPOSITAMOUNT = SECURITYDEPOSITAMOUNT,
                            @SECURITYDEPOSITDUEDATE = SECURITYDEPOSITDUEDATE
                        from
                            dbo.RESERVATION
                        inner join
                            dbo.SALESORDER on RESERVATION.ID = SALESORDER.ID
                        where
                            RESERVATION.ID = @ID;

                        if @DATALOADED = 1
                        begin
                            if @DEPOSITREQUIRED = 1 and @DEPOSITAMOUNT = 0.0
                            begin
                                set @DEPOSITTYPECODE = 1 -- Percent, has not been set before
                                set @DEPOSITAMOUNT = null
                            end
                            else
                                set @DEPOSITTYPECODE = 0 -- Amount

                            if @SECURITYDEPOSITREQUIRED = 1 and @SECURITYDEPOSITAMOUNT = 0.0
                            begin
                                set @SECURITYDEPOSITTYPECODE = 1 -- Percent, has not been set before
                                set @SECURITYDEPOSITAMOUNT = null
                            end
                            else
                                set @SECURITYDEPOSITTYPECODE = 0 -- Amount

                            select
                                @DEFAULTTYPECODE = DEPOSITTYPECODE,
                                @DEFAULTAMOUNT = DEPOSITAMOUNT,
                                @DEFAULTPERCENT = DEPOSITPERCENT,
                                @DEFAULTDEPOSITREQUIRED = DEPOSITREQUIRED,
                                @DEFAULTSECURITYDEPOSITTYPECODE = SECURITYDEPOSITTYPECODE,
                                @DEFAULTSECURITYDEPOSITAMOUNT = SECURITYDEPOSITAMOUNT,
                                @DEFAULTSECURITYDEPOSITPERCENT = SECURITYDEPOSITPERCENT,
                                @DEFAULTSECURITYDEPOSITREQUIRED = SECURITYDEPOSITREQUIRED
                            from dbo.GROUPSALESDEFAULT

                            if @PRICINGCODE = 1
                            begin
                                select 
                                    @RATESCALEID = RATESCALEID
                                from dbo.RESERVATIONRATESCALE 
                                where 
                                    ID = @ID;
                                set @INITIALRATESCALEID = @RATESCALEID
                            end

                            set @TOTALVISITORS = dbo.UFN_RESERVATION_TOTALVISITORCOUNT(@ID);

                            set @RATESCALEPRICES = (
                                select
                                    RATESCALEID,
                                    AMOUNT,
                                    GROUPMINIMUM,
                                    GROUPMAXIMUM,
                                    USEPERTICKETAFTERMAX
                                from
                                    dbo.RATESCALEPRICE
                                    inner join dbo.RATESCALE on RATESCALE.ID = RATESCALEPRICE.RATESCALEID
                                for xml raw('ITEM'), type, elements, root('RATESCALEPRICES'), binary base64
                            );                            
                        end

                        if @SALESORDERTSLONG > @TSLONG
                            set @TSLONG = @SALESORDERTSLONG;

                        return 0;