USP_ITINERARY_UPDATEITEMQUANTITIES

        CREATE procedure dbo.USP_ITINERARY_UPDATEITEMQUANTITIES
        (
            @ID uniqueidentifier = null,
            @CHANGEAGENTID uniqueidentifier = null,
            @CURRENTDATE datetime = null
        )
        as
        begin

            if @CURRENTDATE is null
                set @CURRENTDATE = getdate()

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

            declare @RESERVATIONID uniqueidentifier

            select @RESERVATIONID = RESERVATIONID
            from dbo.ITINERARY where ID = @ID

            exec dbo.USP_RESERVATION_UPDATEITEMQUANTITIES @RESERVATIONID, @CHANGEAGENTID;

            declare @ORDERITEMS table
            (
                ID uniqueidentifier,
                QUANTITY decimal(20,4),
                PRICETYPECODEID uniqueidentifier,
                DESCRIPTION nvarchar(255),
                EVENTID uniqueidentifier,
                PROGRAMID uniqueidentifier,
                PRICE money,
                STARTDATE datetime,
                PRICINGSTRUCTURECODE tinyint,
                ITINERARYITEMTYPECODE tinyint
            );

            -- Scheduled Events, distinct per event

            with CTE_DISTINCT_EVENTS as
            (
                select distinct
                    ITINERARYATTENDEE.QUANTITY as QUANTITY,
                    ITINERARYATTENDEE.PRICETYPECODEID as PRICETYPECODEID,
                    EVENTID
                from dbo.ITINERARY
                inner join dbo.ITINERARYATTENDEE on
                    ITINERARY.ID = ITINERARYATTENDEE.ITINERARYID
                inner join dbo.ITINERARYITEM on
                    ITINERARY.ID = ITINERARYITEM.ITINERARYID
                inner join dbo.EVENT on
                    ITINERARYITEM.EVENTID = EVENT.ID
                where
                    ITINERARY.ID = @ID and 
                    ITINERARYITEM.ITEMTYPECODE = 0 and
                    not exists 
                    (
                        select PRICETYPECODEID 
                        from dbo.SALESORDERITEMTICKET
                        inner join dbo.SALESORDERITEM on
                            SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                        where 
                            SALESORDERITEM.SALESORDERID = @RESERVATIONID and
                            SALESORDERITEMTICKET.EVENTID = ITINERARYITEM.EVENTID and
                            SALESORDERITEMTICKET.PRICETYPECODEID = ITINERARYATTENDEE.PRICETYPECODEID
                    )
            )            
            insert into @ORDERITEMS
            ( QUANTITY, PRICETYPECODEID, EVENTID, PROGRAMID, ITINERARYITEMTYPECODE)
            select
                sum(QUANTITY),
                PRICETYPECODEID,
                EVENTID,
                (select PROGRAMID from dbo.EVENT where ID = EVENTID),
                0
            from CTE_DISTINCT_EVENTS
            group by PRICETYPECODEID, EVENTID;            



            -- daily admission programs, distinct per program per day    

            with CTE_DISTINCT as
            (
                select distinct
                    ITINERARYATTENDEE.QUANTITY as QUANTITY,
                    ITINERARYATTENDEE.PRICETYPECODEID as PRICETYPECODEID,
                    PROGRAMID,
                    STARTDATE
                from dbo.ITINERARY
                inner join dbo.ITINERARYATTENDEE on
                    ITINERARY.ID = ITINERARYATTENDEE.ITINERARYID
                inner join dbo.ITINERARYITEM on
                    ITINERARY.ID = ITINERARYITEM.ITINERARYID
                inner join dbo.PROGRAM on
                    ITINERARYITEM.PROGRAMID = PROGRAM.ID
                where
                    ITINERARY.ID = @ID and 
                    ITINERARYITEM.ITEMTYPECODE = 1 and
                    ITINERARYATTENDEE.PRICETYPECODEID not in 
                    (
                        select PRICETYPECODEID 
                        from dbo.SALESORDERITEMTICKET
                        inner join dbo.SALESORDERITEM on
                            SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                        where 
                            SALESORDERITEM.SALESORDERID = @RESERVATIONID and
                            SALESORDERITEMTICKET.PROGRAMID = ITINERARYITEM.PROGRAMID
                    )
            )            
            insert into @ORDERITEMS
            ( QUANTITY, PRICETYPECODEID, PROGRAMID, ITINERARYITEMTYPECODE)
            select
                sum(QUANTITY),
                PRICETYPECODEID,
                PROGRAMID,
                1
            from CTE_DISTINCT
            group by PRICETYPECODEID, PROGRAMID

            -- additional information that couldn't be done with distinct

            update @ORDERITEMS set
                DESCRIPTION = EVENT.NAME + ' - '
            from dbo.EVENT
            where
                EVENT.ID = [@ORDERITEMS].EVENTID and
                [@ORDERITEMS].ID is null

            update @ORDERITEMS set
                DESCRIPTION = PROGRAM.NAME + ' - '
            from dbo.PROGRAM
            where
                PROGRAM.ID = [@ORDERITEMS].PROGRAMID and
                [@ORDERITEMS].ID is null

            -- final information for daily admission programs and events

            update @ORDERITEMS set
                [@ORDERITEMS].ID = newid(),
                DESCRIPTION = [@ORDERITEMS].DESCRIPTION + PRICETYPECODE.DESCRIPTION
            from dbo.PRICETYPECODE
            where
                PRICETYPECODE.ID = [@ORDERITEMS].PRICETYPECODEID and
                [@ORDERITEMS].ID is null

            -- Events with specific prices

            update @ORDERITEMS set
                PRICE = PROGRAMEVENTPRICE.FACEPRICE
            from dbo.PROGRAMEVENTPRICE
            where 
                PROGRAMEVENTPRICE.EVENTID = [@ORDERITEMS].EVENTID and
                [@ORDERITEMS].PRICETYPECODEID = PROGRAMEVENTPRICE.PRICETYPECODEID

            -- Events without specific prices

            update @ORDERITEMS set
                PRICE = PROGRAMPRICE.FACEPRICE
            from dbo.PROGRAMPRICE 
            inner join dbo.EVENT on 
                EVENT.PROGRAMID = PROGRAMPRICE.PROGRAMID
            where 
                EVENT.ID = [@ORDERITEMS].EVENTID and
                [@ORDERITEMS].PRICE is null and
                [@ORDERITEMS].PRICETYPECODEID = PROGRAMPRICE.PRICETYPECODEID and
                not exists 
                (
                    select 1 from dbo.PROGRAMEVENTPRICE
                    where PROGRAMEVENTPRICE.EVENTID = EVENT.ID
                )


            --Programs with specific prices

            update @ORDERITEMS set
                PRICE = PROGRAMPRICE.FACEPRICE
            from dbo.PROGRAMPRICE
            where 
                PROGRAMPRICE.PROGRAMID = [@ORDERITEMS].PROGRAMID and
                [@ORDERITEMS].PRICETYPECODEID = PROGRAMPRICE.PRICETYPECODEID

            update @ORDERITEMS set PRICINGSTRUCTURECODE = 0

            -- Set items to be flat rate if the reservation is flat rate and the program is included

            declare @ISFLATRATE bit = 0
            select @ISFLATRATE = 1 from dbo.RESERVATION
            where ID = @RESERVATIONID and PRICINGCODE = 1
            if @ISFLATRATE = 1
            begin
                update @ORDERITEMS set PRICINGSTRUCTURECODE = 1
                where 
                    dbo.UFN_RESERVATIONRATESCALE_ISEVENTINCLUDED(@RESERVATIONID,[@ORDERITEMS].EVENTID) = 1 or
                    dbo.UFN_RESERVATIONRATESCALE_ISPROGRAMINCLUDED(@RESERVATIONID,[@ORDERITEMS].PROGRAMID) = 1
            end

            if exists (select 1 from @ORDERITEMS where PRICE is null)
                raiserror('BBERR_INVALIDPRICETYPE', 13, 1);

            -- insert new items

            insert into dbo.SALESORDERITEM
                (ID, SALESORDERID, TYPECODE, DESCRIPTION, QUANTITY, PRICE, PRICINGSTRUCTURECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select
                ITEMS.ID, 
                @RESERVATIONID, 
                0, 
                ITEMS.DESCRIPTION, 
                ITEMS.QUANTITY, 
                ITEMS.PRICE,
                ITEMS.PRICINGSTRUCTURECODE,
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE
            from @ORDERITEMS ITEMS

            insert into dbo.SALESORDERITEMTICKET
            (
                ID,
                EVENTID,
                PROGRAMID,
                PROGRAMNAME,
                PROGRAMCATEGORYNAME,
                PRICETYPECODEID,
                PRICE, 
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED, 
                DATECHANGED
            )
            select
                ITEMS.ID, 
                ITEMS.EVENTID,
                ITEMS.PROGRAMID,
                case ITEMS.ITINERARYITEMTYPECODE
                    when 1 then coalesce((select [NAME] from dbo.[PROGRAM] where [ID] = [ITEMS].[PROGRAMID]),'')
                    when 0 then coalesce((
                        select [PROGRAM].[NAME] 
                        from dbo.[EVENT]
                        inner join dbo.[PROGRAM]
                            on [EVENT].[PROGRAMID] = [PROGRAM].[ID]
                        where [EVENT].[ID] = [ITEMS].[EVENTID]
                    ),'')
                end,
                case ITEMS.ITINERARYITEMTYPECODE
                    when 1 then coalesce((
                        select [PROGRAMCATEGORYCODE].[DESCRIPTION] 
                        from dbo.[PROGRAM] 
                        inner join dbo.[PROGRAMCATEGORYCODE]
                            on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
                        where [PROGRAM].[ID] = [ITEMS].[PROGRAMID]
                    ),'')
                    when 0 then coalesce((
                        select [PROGRAMCATEGORYCODE].[DESCRIPTION]
                        from dbo.[EVENT]
                        inner join dbo.[PROGRAM]
                            on [EVENT].[PROGRAMID] = [PROGRAM].[ID]
                        inner join dbo.[PROGRAMCATEGORYCODE]
                            on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
                        where [EVENT].[ID] = [ITEMS].[EVENTID]
                    ),'')
                end,
                ITEMS.PRICETYPECODEID,
                ITEMS.PRICE,
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE
            from @ORDERITEMS ITEMS

            -- Be sure the Event capacity isn't over maximum

            declare @ERROREVENTID uniqueidentifier
            select top(1) @ERROREVENTID = EVENTID from dbo.SALESORDERITEMTICKET SOITOUTER
                inner join dbo.SALESORDERITEM SOIOUTER on 
                    SOITOUTER.ID = SOIOUTER.ID
                where
                    SOIOUTER.SALESORDERID = @RESERVATIONID and
                    dbo.UFN_SALESORDERTICKET_VALIDQUANTITY(
                        SOITOUTER.EVENTID, 
                        @RESERVATIONID,
                        isnull((
                            select sum(SOIINNER.QUANTITY) 
                            from dbo.SALESORDERITEM SOIINNER
                            inner join dbo.SALESORDERITEMTICKET SOITINNER on
                                SOIINNER.ID = SOITINNER.ID
                            where
                                SOIINNER.SALESORDERID = @RESERVATIONID and
                                SOITINNER.EVENTID = SOITOUTER.EVENTID
                            ),0)                                
                    ) = 0

            if @ERROREVENTID is not null
            begin
                declare @ERRORMESSAGE nvarchar(500)
                declare @DIFFERENCE integer = 0;
                declare @QUANTITY integer = 0;

                set @ERRORMESSAGE = dbo.UFN_TRANSLATIONFUNCTION_EVENT_GETNAME(@ERROREVENTID)
                set @DIFFERENCE = 0 - dbo.UFN_EVENT_GETAVAILABILITY(@ERROREVENTID)

                set @ERRORMESSAGE = @ERRORMESSAGE + ' does not have enough capacity for this group. You can either reduce the group size by ' + 
                            convert(nvarchar(10), @DIFFERENCE) + ' or choose a different event.' 

                raiserror(@ERRORMESSAGE, 13, 1)            
            end

            if @ISFLATRATE = 1
            begin
                exec dbo.USP_RESERVATION_DISTRIBUTEAPPLICATIONS @RESERVATIONID, @CHANGEAGENTID, @CURRENTDATE;    
                exec dbo.USP_SALESORDER_CALCULATEFEES @RESERVATIONID, @CHANGEAGENTID;
                exec dbo.USP_RESERVATION_CALCULATEFLATRATEFEES @RESERVATIONID, @CHANGEAGENTID;
                --exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @ID, @CHANGEAGENTID;

                --exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @ID, @CHANGEAGENTID;

                exec dbo.USP_SALESORDER_CALCULATETAXES @RESERVATIONID, @CHANGEAGENTID;
                exec dbo.USP_RESERVATIONRATESCALE_UPDATETAXES @RESERVATIONID, @CHANGEAGENTID, @CURRENTDATE;
            end
            else
            begin
                exec dbo.USP_SALESORDER_CALCULATEFEES @RESERVATIONID, @CHANGEAGENTID;
                exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @RESERVATIONID, @CHANGEAGENTID;
                exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @RESERVATIONID, @CHANGEAGENTID;
                exec dbo.USP_SALESORDER_CALCULATETAXES @RESERVATIONID, @CHANGEAGENTID;
            end
        end