USP_DATAFORMTEMPLATE_ADD_RESERVATION

The save procedure used by the add dataform template "Reservation Add Data Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@CONSTITUENTID	uniqueidentifier	IN	Patron
@NAME	nvarchar(100)	IN	Reservation name
@CONTACTRELATIONSHIPID	uniqueidentifier	IN	Contact
@ARRIVALDATE	date	IN	Date of visit
@COMMENTS	nvarchar(1000)	IN	Comments
@ATTENDEES	xml	IN	Visitors
@DEPOSITTYPECODE	tinyint	IN
@DEPOSITREQUIRED	bit	IN	Reservation deposit required
@DEPOSITDUEDATE	date	IN	Reservation deposit due
@FINALDUEDATE	date	IN	Order balance due
@DEPOSITAMOUNT	money	IN	Reservation deposit amount
@EXCEEDCAPACITY	bit	IN	Exceed capacity available
@CONTRACTREQUIRED	bit	IN	Contract required
@CONTRACTDUEDATE	date	IN	Contract due
@FINALCOUNTREQUIRED	bit	IN	Final count required
@FINALCOUNTDUEDATE	date	IN	Final count due
@PRICINGCODE	tinyint	IN	Pricing structure
@RATESCALEID	uniqueidentifier	IN	Flat rate scale
@GROUPSALESGROUPTYPECODEID	uniqueidentifier	IN	Group type
@ARRIVALTIME	UDT_HOURMINUTE	IN	Arrival time
@DEPARTURETIME	UDT_HOURMINUTE	IN	Departure time
@QUICKITINERARYPROGRAMEVENTID	uniqueidentifier	IN	Quick itinerary
@QUICKITINERARYTYPECODE	tinyint	IN
@IGNORECONFLICTS	bit	IN	Ignore group type resource conflicts
@SECURITYDEPOSITTYPECODE	tinyint	IN
@SECURITYDEPOSITREQUIRED	bit	IN	Security deposit required
@SECURITYDEPOSITDUEDATE	date	IN	Security deposit due
@SECURITYDEPOSITAMOUNT	money	IN	Security deposit amount
@SECURITYDEPOSITPERCENT	decimal(5, 2)	IN	Security deposit amount
Definition

Copy

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_RESERVATION
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier,
    @CONSTITUENTID uniqueidentifier = null,
    @NAME nvarchar(100) = null,
    @CONTACTRELATIONSHIPID uniqueidentifier = null,
    @ARRIVALDATE date = null,
    @COMMENTS nvarchar(1000) = '',
    @ATTENDEES xml = null,
    @DEPOSITTYPECODE tinyint = 0,
    @DEPOSITREQUIRED bit = 0,
    @DEPOSITDUEDATE date = null,
    @FINALDUEDATE date = null,
    @DEPOSITAMOUNT money = 0.00,
    @EXCEEDCAPACITY bit = 0,
    @CONTRACTREQUIRED bit = 0,
    @CONTRACTDUEDATE date = null,
    @FINALCOUNTREQUIRED bit = 0,
    @FINALCOUNTDUEDATE date = null,
    @PRICINGCODE tinyint = 0,
    @RATESCALEID uniqueidentifier = null,
    @GROUPSALESGROUPTYPECODEID uniqueidentifier = null,
    @ARRIVALTIME dbo.UDT_HOURMINUTE = '',
    @DEPARTURETIME dbo.UDT_HOURMINUTE = '',
    @QUICKITINERARYPROGRAMEVENTID uniqueidentifier = null,
    @QUICKITINERARYTYPECODE tinyint = 3,
    @IGNORECONFLICTS bit = 0,
    @SECURITYDEPOSITTYPECODE tinyint = 0,
    @SECURITYDEPOSITREQUIRED bit = 0,
    @SECURITYDEPOSITDUEDATE date = null,
    @SECURITYDEPOSITAMOUNT money = 0.00,
    @SECURITYDEPOSITPERCENT decimal(5,2) = null
)
as
    set nocount on;

    if @ID is null
        set @ID = newid();

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    declare @CREATEITINERARY bit = 0;

    begin try
        -- Validate flat rate scale
        if @PRICINGCODE = 0
            set @RATESCALEID = null;
        else if @RATESCALEID is null
            raiserror('ERR_RATESCALEIDREQUIRED', 13, 1);

        declare @ATTENDEESTABLE table (
            ID uniqueidentifier,
            PRICETYPECODEID uniqueidentifier,
            QUANTITY smallint,
            SEQUENCE int
        );

        insert into @ATTENDEESTABLE (
            ID,
            PRICETYPECODEID,
            QUANTITY,
            SEQUENCE
        )
        select
            ID,
            PRICETYPECODEID,
            QUANTITY,
            SEQUENCE
        from
            dbo.UFN_ITINERARY_GETATTENDEES_FROMITEMLISTXML(@ATTENDEES);

        if @@rowcount > 0 begin
            set @CREATEITINERARY = 1;

            update @ATTENDEESTABLE
            set ID = newid()
            where ID is null or ID = '00000000-0000-0000-0000-000000000000';
        end

        -- Validate that the attendee count is not greater than the capacity available
        if (@CREATEITINERARY = 1 and @EXCEEDCAPACITY = 0)
        begin
            declare @MAXIMUMCAPACITY int;
            select @MAXIMUMCAPACITY = MAXIMUMCAPACITY
            from dbo.GROUPSALESDEFAULT

            if (@MAXIMUMCAPACITY > 0)
            begin            
                if ((select sum(QUANTITY) from @ATTENDEESTABLE) > dbo.UFN_GROUPSALESCAPACITY_CAPACITYREMAINING(@ARRIVALDATE, null))
                    raiserror('ERR_EXCEEDSCAPACITY', 13, 1);
            end
        end              

        -- Validation for deposit amount
        if @DEPOSITREQUIRED = 1 and @DEPOSITAMOUNT <= 0.00 and @DEPOSITTYPECODE = 0
        begin
            raiserror('ERR_DEPOSITAMOUNT', 13, 1);
        end

        if @DEPOSITREQUIRED = 0
        begin
            set @DEPOSITDUEDATE = null
            set @DEPOSITAMOUNT = 0.0
        end

        -- Validation for security deposit amount
        if @SECURITYDEPOSITREQUIRED = 1 and @SECURITYDEPOSITAMOUNT <= 0.00 and @SECURITYDEPOSITTYPECODE = 0
        begin
            raiserror('ERR_SECURITYDEPOSITAMOUNT', 13, 1);
        end

        if @SECURITYDEPOSITREQUIRED = 0
        begin
            set @SECURITYDEPOSITDUEDATE = null
            set @SECURITYDEPOSITAMOUNT = 0.0
        end

        declare @CONTACTID uniqueidentifier;
        declare @ADDRESSID uniqueidentifier;
        declare @EMAILADDRESSID uniqueidentifier;
        declare @PHONEID uniqueidentifier;

        exec dbo.USP_RESERVATION_GETCONTACTINFO @CONSTITUENTID, @CONTACTRELATIONSHIPID,
            @CONTACTID output, @ADDRESSID output, @EMAILADDRESSID output, @PHONEID output;

        -- Validate active constituents
        exec dbo.USP_CONSTITUENT_CHECKINACTIVEANDDECEASED @CONSTITUENTID;

        if @CONTACTID <> @CONSTITUENTID
            exec dbo.USP_CONSTITUENT_CHECKINACTIVEANDDECEASED @CONTACTID, 'BBERR_CONTACTISINACTIVE', 'BBERR_CONTACTISDECEASED';

        -- Blank out CONTRACTDUEDATE, FINALCOUNTDUEDATE
        if @CONTRACTREQUIRED = 0
            set @CONTRACTDUEDATE = null;

        if @FINALCOUNTREQUIRED = 0
            set @FINALCOUNTDUEDATE = null;

        insert into dbo.SALESORDER
        (
            ID,
            APPUSERID,
            SALESMETHODTYPECODE,
            CONSTITUENTID,
            CONTACTRELATIONSHIPID,
            COMMENTS,
            RECIPIENTID,
            ADDRESSID,
            PHONEID,
            EMAILADDRESSID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (
            @ID,
            @CURRENTAPPUSERID,
            3, -- Group Sales
            @CONSTITUENTID,
            @CONTACTRELATIONSHIPID,
            @COMMENTS,
            @CONTACTID,
            @ADDRESSID,
            @PHONEID,
            @EMAILADDRESSID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );

        insert into dbo.RESERVATION
        (
            ID,
            NAME,
            ARRIVALDATE,
            DEPOSITREQUIRED,
            DEPOSITAMOUNT,
            DEPOSITDUEDATE,
            SECURITYDEPOSITREQUIRED,
            SECURITYDEPOSITAMOUNT,
            SECURITYDEPOSITDUEDATE,
            FINALDUEDATE,
            CONTRACTREQUIRED,
            CONTRACTDUEDATE,
            FINALCOUNTREQUIRED,
            FINALCOUNTDUEDATE,
            PRICINGCODE,
            ARRIVALTIME,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (
            @ID,
            @NAME,
            @ARRIVALDATE,
            @DEPOSITREQUIRED,
            @DEPOSITAMOUNT,
            @DEPOSITDUEDATE,
            @SECURITYDEPOSITREQUIRED,
            @SECURITYDEPOSITAMOUNT,
            @SECURITYDEPOSITDUEDATE,
            @FINALDUEDATE,
            @CONTRACTREQUIRED,
            @CONTRACTDUEDATE,
            @FINALCOUNTREQUIRED,
            @FINALCOUNTDUEDATE,
            @PRICINGCODE,
            @ARRIVALTIME,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );

        exec dbo.[USP_RESERVATIONSTATUSHISTORY_ADD] @ID, @CHANGEAGENTID, 0

        declare @ITINERARYID uniqueidentifier;

        if @CREATEITINERARY = 1
        begin
            declare @ITINERARYNAME nvarchar(154);
            set @CONTACTID = isnull(@CONTACTID, @CONSTITUENTID)

            select @ITINERARYNAME = NAME from dbo.UFN_CONSTITUENT_DISPLAYNAME(@CONTACTID)    

            set @ITINERARYID = newid();

            insert into dbo.ITINERARY
            (
                ID,
                RESERVATIONID,
                LEADERID,
                NAME,
                GROUPSALESGROUPTYPECODEID,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values
            (
                @ITINERARYID,
                @ID,
                @CONTACTID,
                @ITINERARYNAME,
                @GROUPSALESGROUPTYPECODEID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            );

            insert into dbo.ITINERARYATTENDEE (
                ID,
                ITINERARYID,
                PRICETYPECODEID,
                QUANTITY,
                SEQUENCE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            select
                ID,
                @ITINERARYID,
                PRICETYPECODEID,
                QUANTITY,
                SEQUENCE,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            from
                @ATTENDEESTABLE;

            if (not @GROUPSALESGROUPTYPECODEID is null)
                exec dbo.USP_ITINERARY_LOADREQUIREDRESOURCESBYGROUPTYPE @ITINERARYID, @CHANGEAGENTID;
        end    

        if @PRICINGCODE = 1 and @RATESCALEID is not null
        begin
            exec dbo.USP_RESERVATION_UPDATERATESCALE @ID, @RATESCALEID, @CHANGEAGENTID, @CURRENTDATE;
            exec dbo.USP_RESERVATION_UPDATEFLATRATE @ID, @CHANGEAGENTID, @CURRENTDATE;
        end        

        if @CREATEITINERARY = 1
        begin
            if @QUICKITINERARYTYPECODE = 0
            begin

                -- Add scheduled event
                declare @EITEMID uniqueidentifier = newid()

                declare @EVENTSTARTTIME dbo.UDT_HOURMINUTE = '0000'
                select @EVENTSTARTTIME = EVENT.STARTTIME 
                from dbo.EVENT where ID = @QUICKITINERARYPROGRAMEVENTID

                -- Resetting ARRIVALTIME to be the start of the event time
                -- For purpose of adding event and checking for conflicts
                if @EVENTSTARTTIME > @ARRIVALTIME
                    set @ARRIVALTIME = @EVENTSTARTTIME

                exec dbo.USP_ITINERARYITEMEVENT_ADD @EITEMID, @CHANGEAGENTID, @CURRENTDATE, @ITINERARYID, 
                    @QUICKITINERARYPROGRAMEVENTID, @ARRIVALTIME, @DEPARTURETIME, @ARRIVALDATE, @ARRIVALDATE, 0, '', null, @IGNORECONFLICTS;
            end

            else if @QUICKITINERARYTYPECODE = 1
            begin

                -- Add daily admission program
                declare @DAITEMID uniqueidentifier = newid()
                exec dbo.USP_ITINERARYITEMDAILYADMISSION_ADD @DAITEMID, @CHANGEAGENTID, @CURRENTDATE, @ITINERARYID,
                    @QUICKITINERARYPROGRAMEVENTID, @ARRIVALDATE, @ARRIVALTIME, @DEPARTURETIME, '', null, @IGNORECONFLICTS;

            end

            else if @QUICKITINERARYTYPECODE = 2
            begin

                -- Add custom item
                declare @CUSTOMITEMID uniqueidentifier = newid()
                exec dbo.USP_ITINERARYITEMCUSTOMITEM_ADD @CUSTOMITEMID, @CHANGEAGENTID, @CURRENTDATE, @ITINERARYID, 
                    @NAME, @ARRIVALDATE, @ARRIVALDATE, @ARRIVALTIME, @DEPARTURETIME, '', null, 0, null, @IGNORECONFLICTS;

            end

            if @QUICKITINERARYTYPECODE <> 3 
            begin
                exec dbo.USP_ITINERARY_CHECKPERTICKETRESOURCES @ITINERARYID;

                if @IGNORECONFLICTS = 0
                begin
                    declare @RESOURCES xml
                    declare @STAFFRESOURCES xml

                    set @RESOURCES = dbo.UFN_ITINERARYRESOURCE_GETRESOURCES_TOITEMLISTXML(@ITINERARYID);

                    set @STAFFRESOURCES = dbo.UFN_ITINERARY_GETSTAFFRESOURCES_TOITEMLISTXML(@ITINERARYID);    

                    if dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
                    (
                        dbo.UFN_DATE_ADDHOURMINUTE(@ARRIVALDATE, @ARRIVALTIME), 
                        dbo.UFN_DATE_ADDHOURMINUTE(@ARRIVALDATE, @DEPARTURETIME),
                        null, @RESOURCES, @STAFFRESOURCES, 
                        null, @ITINERARYID,    null,
                        0,
                        1,
                        0,
                        0
                    ) = 1
                    begin
                        raiserror('BBERR_CONFLICTSEXIST', 13, 1);
                        return 1;
                    end
                end
            end
        end
        else if @QUICKITINERARYTYPECODE <> 3
        begin
            raiserror('BBERR_VISITORSREQUIRED', 13, 1);
        end                            

        -- Update deposit amounts
        declare @AMOUNTDUE money = null

        if (@SECURITYDEPOSITREQUIRED = 1 and @SECURITYDEPOSITTYPECODE = 1) or (@DEPOSITREQUIRED = 1 and @DEPOSITTYPECODE = 1) begin
            set @AMOUNTDUE = dbo.UFN_SALESORDER_TOTAL(@ID);

            declare @DEPOSITPERCENT decimal(5,2);

            select top 1 @DEPOSITPERCENT = DEPOSITPERCENT
            from dbo.GROUPSALESDEFAULT;

            update dbo.RESERVATION set
                SECURITYDEPOSITAMOUNT =
                    case
                        when @SECURITYDEPOSITREQUIRED = 1 and @SECURITYDEPOSITTYPECODE = 1 then
                            round(@AMOUNTDUE * @SECURITYDEPOSITPERCENT * .01, 2)
                        else SECURITYDEPOSITAMOUNT
                    end,
                DEPOSITAMOUNT =
                    case
                        when @DEPOSITREQUIRED = 1 and @DEPOSITTYPECODE = 1 then
                            round(@AMOUNTDUE * @DEPOSITPERCENT * .01, 2)
                        else
                            DEPOSITAMOUNT
                    end,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @ID;
        end
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;