USP_RESERVATION_UPDATEITEMQUANTITIES

        CREATE procedure dbo.USP_RESERVATION_UPDATEITEMQUANTITIES
        (
            @ID uniqueidentifier = null,
            @CHANGEAGENTID uniqueidentifier = null,
            @CURRENTDATE datetime = null,
            @UPDATEPERTICKETBALANCE bit = 1
        )
        as
        begin

            set nocount on;

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

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

            declare @SOI table
            (
                ID uniqueidentifier,
                PRICETYPECODEID uniqueidentifier,
                EVENTID uniqueidentifier,
                PROGRAMID uniqueidentifier,
                QUANTITY int
            );    

            with CTE_DISTINCT as
            (
                select distinct
                    ITINERARYATTENDEE.QUANTITY as QUANTITY,
                    ITINERARYATTENDEE.PRICETYPECODEID as PRICETYPECODEID,
                    PROGRAMID,
                    STARTDATE,
                    ITINERARY.ID
                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.RESERVATIONID = @ID and
                    ITEMTYPECODE = 1
            )    
            insert into @SOI
            (ID, PRICETYPECODEID, PROGRAMID, QUANTITY)
            select
                SALESORDERITEM.ID,
                SALESORDERITEMTICKET.PRICETYPECODEID,
                SALESORDERITEMTICKET.PROGRAMID,
                sum(T.QUANTITY)
            from dbo.SALESORDERITEM with (nolock)
            inner join dbo.SALESORDERITEMTICKET with (nolock) on
                SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
            inner join CTE_DISTINCT T on
                T.PROGRAMID = SALESORDERITEMTICKET.PROGRAMID
            where
                SALESORDERITEMTICKET.PRICETYPECODEID = T.PRICETYPECODEID and
                SALESORDERID = @ID and
                TYPECODE = 0
            group by SALESORDERITEM.ID, SALESORDERITEMTICKET.PRICETYPECODEID, SALESORDERITEMTICKET.PROGRAMID, SALESORDERITEM.TOTAL;

            with CTE_DISTINCT_EVENTS as
            (
                select distinct
                    ITINERARYATTENDEE.QUANTITY as QUANTITY,
                    ITINERARYATTENDEE.PRICETYPECODEID as PRICETYPECODEID,
                    EVENTID,
                    ITINERARY.ID
                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.RESERVATIONID = @ID and
                    ITEMTYPECODE = 0
            )    
            insert into @SOI
            (ID, PRICETYPECODEID, EVENTID, QUANTITY)
            select
                SALESORDERITEM.ID,
                SALESORDERITEMTICKET.PRICETYPECODEID,
                SALESORDERITEMTICKET.EVENTID,
                sum(E.QUANTITY)
            from dbo.SALESORDERITEM with (nolock)
            inner join dbo.SALESORDERITEMTICKET with (nolock) on
                SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
            inner join CTE_DISTINCT_EVENTS E on
                E.EVENTID = SALESORDERITEMTICKET.EVENTID
            where
                SALESORDERITEMTICKET.PRICETYPECODEID = E.PRICETYPECODEID and
                SALESORDERID = @ID and
                TYPECODE = 0
            group by SALESORDERITEM.ID, SALESORDERITEMTICKET.PRICETYPECODEID, SALESORDERITEMTICKET.EVENTID, SALESORDERITEM.TOTAL;

            declare @contextCache varbinary(128);
            declare @e int;

            if @UPDATEPERTICKETBALANCE = 1
            begin
                update dbo.[SALESORDERITEM] set
                    QUANTITY = SOI.QUANTITY,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                from @SOI SOI
                where
                    SALESORDERITEM.ID = SOI.ID and
                    SALESORDERITEM.QUANTITY <> SOI.QUANTITY;

                -- cache current context information 

                set @contextCache = CONTEXT_INFO();

                -- set CONTEXT_INFO to @CHANGEAGENTID 

                if not @CHANGEAGENTID is null
                    set CONTEXT_INFO @CHANGEAGENTID;

                -- delete fees from non-existent price types

                with DELETEFEES_CTE as
                (
                    select SALESORDERITEMFEE.ID 
                    from dbo.SALESORDERITEM with (nolock)
                    inner join dbo.SALESORDERITEMFEE with (nolock) on
                        SALESORDERITEM.ID = SALESORDERITEMFEE.SALESORDERITEMID
                    where 
                        SALESORDERITEM.SALESORDERID = @ID and 
                        (
                            SALESORDERITEM.QUANTITY = 0 or
                            SALESORDERITEM.ID not in (select ID from @SOI)
                        )
                )
                delete from dbo.[SALESORDERITEM] with (rowlock)
                from DELETEFEES_CTE
                where
                    DELETEFEES_CTE.ID = SALESORDERITEM.ID

                -- Order level discounts will need to be recalculated so go ahead and delete

                -- them to avoid FK_SALESORDERITEMORDERDISCOUNTDETAIL_SALESORDERITEMID

                delete from dbo.SALESORDERITEMORDERDISCOUNTDETAIL with (rowlock)
                where (select SALESORDERID from dbo.SALESORDERITEM where ID = SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMORDERDISCOUNTID) = @ID;

                if exists (select 1 from dbo.SALESORDER where STATUSCODE = 1 and ID = @ID) begin
                    delete from dbo.TICKET with (rowlock)
                    where
                        (
                            TICKET.SALESORDERITEMTICKETID in 
                            (
                                select 
                                    SALESORDERITEMTICKET.ID 
                                from 
                                    dbo.SALESORDERITEMTICKET 
                                inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID 
                                where 
                                    SALESORDERITEMTICKET.ID = TICKET.SALESORDERITEMTICKETID and SALESORDERITEM.SALESORDERID = @ID
                                    and TICKET.ID not in
                                    (
                                        select 
                                            SALESORDERITEMTICKET.ID 
                                        from 
                                            dbo.SALESORDERITEMTICKET 
                                        inner join  
                                            dbo.TICKET on TICKET.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID
                                        inner join 
                                            @SOI SOI on SOI.ID = SALESORDERITEMTICKET.ID                                
                                    )
                            )
                        )
                        or
                        TICKET.ID in
                        (
                            select
                                SALESORDERITEMTICKET.ID
                            from 
                                dbo.SALESORDERITEMTICKET 
                            inner join
                                dbo.TICKET on TICKET.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID
                            inner join
                                dbo.SALESORDERITEM on SALESORDERITEM.ID = TICKET.SALESORDERITEMTICKETID
                            where
                                SALESORDERITEM.SALESORDERID = @ID
                                and SALESORDERITEM.QUANTITY = 0
                        )

                end

                -- delete non-existent price types

                delete dbo.SALESORDERITEM with (rowlock)
                where 
                    TYPECODE = 0 and
                    SALESORDERITEM.SALESORDERID = @ID and
                    (
                        SALESORDERITEM.QUANTITY = 0 or
                        SALESORDERITEM.ID not in (select ID from @SOI)
                    )

                select @e=@@error;

                -- reset CONTEXT_INFO to previous value 

                if not @contextCache is null
                    set CONTEXT_INFO @contextCache;

                if @e <> 0
                    return 2;
            end

            else
            begin
                -- Update flat rate tickets

                update dbo.SALESORDERITEM set
                    SALESORDERITEM.QUANTITY = SOI.QUANTITY,
                    SALESORDERITEM.DATECHANGED = @CURRENTDATE,
                    SALESORDERITEM.CHANGEDBYID = @CHANGEAGENTID
                from
                    @SOI SOI
                where
                    SALESORDERITEM.ID = SOI.ID
                    and SALESORDERITEM.PRICINGSTRUCTURECODE = 1  -- Flat rate

                    and SALESORDERITEM.QUANTITY <> SOI.QUANTITY;

                -- cache current context information 

                set @contextCache = CONTEXT_INFO();

                -- set CONTEXT_INFO to @CHANGEAGENTID 

                if not @CHANGEAGENTID is null
                    set CONTEXT_INFO @CHANGEAGENTID;

                -- delete fees from non-existent flat rate price types

                with DELETEFEES_CTE as
                (
                    select SALESORDERITEMFEE.ID 
                    from dbo.SALESORDERITEM with (nolock)
                    inner join dbo.SALESORDERITEMFEE with (nolock) on
                        SALESORDERITEM.ID = SALESORDERITEMFEE.SALESORDERITEMID
                    where 
                        SALESORDERITEM.SALESORDERID = @ID and
                        SALESORDERITEM.PRICINGSTRUCTURECODE = 1 and  -- Flat rate

                        (
                            SALESORDERITEM.QUANTITY = 0 or
                            SALESORDERITEM.ID not in (select ID from @SOI)
                        )
                )
                delete from dbo.[SALESORDERITEM]
                from DELETEFEES_CTE
                where
                    DELETEFEES_CTE.ID = SALESORDERITEM.ID

                -- delete non-existent flat rate price types

                delete dbo.SALESORDERITEM with (rowlock)
                where 
                    TYPECODE = 0 and
                    SALESORDERITEM.SALESORDERID = @ID and
                    SALESORDERITEM.PRICINGSTRUCTURECODE = 1 and  -- Flat rate

                    (
                        SALESORDERITEM.QUANTITY = 0 or
                        SALESORDERITEM.ID not in (select ID from @SOI)
                    )

                select @e=@@error;

                -- reset CONTEXT_INFO to previous value 

                if not @contextCache is null
                    set CONTEXT_INFO @contextCache;

                if @e <> 0
                    return 2;

                -- Update non-flat rate tickets

                update dbo.[SALESORDERITEM] set
                    QUANTITY = SOI.QUANTITY,
                    PRICINGSTRUCTURECODE = 2,  -- Override rate

                    FLATRATEPRICE = TOTAL,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                from
                    @SOI SOI
                where
                    SALESORDERITEM.ID = SOI.ID
                    and SALESORDERITEM.PRICINGSTRUCTURECODE <> 1  -- Not flat rate

                    and SALESORDERITEM.QUANTITY <> SOI.QUANTITY;

                -- This handles the scenario of reducing the quantity of a price type to zero

                update dbo.SALESORDERITEM set
                    QUANTITY = 0,
                    PRICINGSTRUCTURECODE = 2,  -- Override rate

                    FLATRATEPRICE = TOTAL,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                where
                    SALESORDERITEM.SALESORDERID = @ID
                    and SALESORDERITEM.TYPECODE = 0  -- Ticket

                    and SALESORDERITEM.ID not in (select ID from @SOI);
            end

            return 0;
        end