USP_RESERVATION_DISTRIBUTEAPPLICATIONS

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

            set nocount on;

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

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

            exec dbo.USP_RESERVATIONRATESCALE_UPDATEAUTOMATICPROGRAMAPPLICATIONS @ID, @CHANGEAGENTID, @CURRENTDATE;
            exec dbo.USP_RESERVATIONRATESCALE_UPDATEAUTOMATICRESOURCEAPPLICATIONS @ID, @CHANGEAGENTID, @CURRENTDATE;
            exec dbo.USP_RESERVATIONRATESCALE_UPDATEAUTOMATICSTAFFRESOURCEAPPLICATIONS @ID, @CHANGEAGENTID, @CURRENTDATE;

            declare @PROGRAMS table
            (
                PROGRAMID uniqueidentifier,
                PROGRAMCOUNT smallint,
                AMOUNT money,
                NEEDSOFFSET bit
            )

            declare @EVENTS table
            (
                PROGRAMID uniqueidentifier,
                EVENTCOUNT smallint,
                AMOUNT money
            )

            declare @RESOURCES table
            (
                RESOURCEID uniqueidentifier,
                RESOURCECOUNT smallint,
                AMOUNT money,
                NEEDSOFFSET bit
            )

            declare @STAFFRESOURCES table
            (
                VOLUNTEERTYPEID uniqueidentifier,
                VOLUNTEERTYPECOUNT smallint,
                AMOUNT money,
                NEEDSOFFSET bit
            )

            insert into @PROGRAMS (PROGRAMID, PROGRAMCOUNT)
            select
                PROGRAMID,
                count(PROGRAMID)
            from dbo.SALESORDERITEM
            inner join dbo.SALESORDERITEMTICKET on
                SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
            where
                SALESORDERID = @ID and
                PROGRAMID is not null and
                EVENTID is null
            group by PROGRAMID

            insert into @EVENTS (PROGRAMID, EVENTCOUNT)
            select
                EVENT.PROGRAMID,
                count(EVENT.PROGRAMID)
            from dbo.SALESORDERITEM
            inner join dbo.SALESORDERITEMTICKET on
                SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
            inner join dbo.EVENT on 
                SALESORDERITEMTICKET.EVENTID = EVENT.ID
            where
                SALESORDERID = @ID and
                SALESORDERITEMTICKET.EVENTID is not null
            group by EVENT.PROGRAMID

            update @PROGRAMS set
                PROGRAMCOUNT = PROGRAMCOUNT + EVENTCOUNT
            from @EVENTS 
            where [@EVENTS].PROGRAMID = [@PROGRAMS].PROGRAMID

            insert into @PROGRAMS (PROGRAMID, PROGRAMCOUNT)
            select
                PROGRAMID,
                EVENTCOUNT
            from @EVENTS 
            where PROGRAMID not in (select PROGRAMID from @PROGRAMS)

            update @PROGRAMS set
                AMOUNT = round(RRSA.[AMOUNT] / [@PROGRAMS].PROGRAMCOUNT , 2),
                NEEDSOFFSET = case
                                when round(RRSA.[AMOUNT] / [@PROGRAMS].PROGRAMCOUNT, 2) * [@PROGRAMS].PROGRAMCOUNT <> RRSA.[AMOUNT]
                                    then 1
                                else 0 end
            from dbo.RESERVATIONRATESCALEAPPLICATION RRSA
            inner join dbo.RESERVATIONRATESCALE RRS on
                RRSA.RESERVATIONRATESCALEID = RRS.ID
            where
                RRS.ID = @ID and
                [@PROGRAMS].PROGRAMID = RRSA.PROGRAMID

            update dbo.SALESORDERITEM set
                FLATRATEPRICE = isnull([@PROGRAMS].AMOUNT,0.0),
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from dbo.SALESORDERITEMTICKET
            inner join @PROGRAMS on
                SALESORDERITEMTICKET.PROGRAMID = [@PROGRAMS].PROGRAMID or
                exists (
                    select 1 from dbo.EVENT 
                    where 
                        EVENT.ID = SALESORDERITEMTICKET.EVENTID and 
                        EVENT.PROGRAMID = [@PROGRAMS].PROGRAMID
                )
            where
                SALESORDERID = @ID and
                SALESORDERITEMTICKET.ID = SALESORDERITEM.ID and
                PRICINGSTRUCTURECODE = 1 and
                FLATRATEPRICE <> isnull([@PROGRAMS].AMOUNT,0.0)

            if exists (select 1 from @PROGRAMS where NEEDSOFFSET = 1)
            begin
                declare @PROGRAMOFFSETITEMS table
                (
                    ID uniqueidentifier,
                    PROGRAMID uniqueidentifier,
                    OFFSET money
                )

                insert into @PROGRAMOFFSETITEMS (PROGRAMID, OFFSET)
                select
                    RRSA.PROGRAMID,
                    RRSA.[AMOUNT] - ( round(RRSA.[AMOUNT] / [@PROGRAMS].PROGRAMCOUNT , 2) * [@PROGRAMS].PROGRAMCOUNT )
                from @PROGRAMS
                inner join dbo.RESERVATIONRATESCALEAPPLICATION RRSA on
                    RRSA.PROGRAMID = [@PROGRAMS].PROGRAMID
                inner join dbo.RESERVATIONRATESCALE RRS on
                    RRSA.RESERVATIONRATESCALEID = RRS.ID
                where
                    RRS.ID = @ID and
                    NEEDSOFFSET = 1

                -- Grab the any sales order item that contains the program to be the offset    

                update @PROGRAMOFFSETITEMS set
                    ID = (
                            select top(1) SALESORDERITEM.ID
                            from dbo.SALESORDERITEM
                            inner join dbo.SALESORDERITEMTICKET on
                                SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                            where
                                SALESORDERID = @ID and
                                (
                                    SALESORDERITEMTICKET.PROGRAMID = [@PROGRAMOFFSETITEMS].PROGRAMID or
                                    [@PROGRAMOFFSETITEMS].PROGRAMID = (select PROGRAMID from dbo.EVENT where EVENT.ID = SALESORDERITEMTICKET.EVENTID)
                                )
                        )

                update dbo.SALESORDERITEM set
                    FLATRATEPRICE = FLATRATEPRICE + isnull(OFFSET,0.0),
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from @PROGRAMOFFSETITEMS
                where 
                    SALESORDERITEM.ID = [@PROGRAMOFFSETITEMS].ID

            end



            insert into @RESOURCES (RESOURCEID, RESOURCECOUNT)
            select
                RESOURCEID,
                count(RESOURCEID)
            from (
                select RESOURCEID
                from dbo.SALESORDERITEM
                    inner join dbo.SALESORDERITEMITINERARYRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYRESOURCE.SALESORDERITEMID
                    inner join dbo.ITINERARYRESOURCE on SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID = ITINERARYRESOURCE.ID
                where SALESORDERITEM.SALESORDERID = @ID

                union all

                select RESOURCEID
                from dbo.SALESORDERITEM
                    inner join dbo.SALESORDERITEMITINERARYITEMRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMRESOURCE.SALESORDERITEMID
                    inner join dbo.ITINERARYITEMRESOURCE on SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID = ITINERARYITEMRESOURCE.ID
                where SALESORDERITEM.SALESORDERID = @ID
            ) T
            group by RESOURCEID

            insert into @STAFFRESOURCES (VOLUNTEERTYPEID, VOLUNTEERTYPECOUNT)
            select
                VOLUNTEERTYPEID,
                count(VOLUNTEERTYPEID)
            from (
                select VOLUNTEERTYPEID
                from dbo.SALESORDERITEM
                    inner join dbo.SALESORDERITEMITINERARYSTAFFRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYSTAFFRESOURCE.SALESORDERITEMID
                    inner join dbo.ITINERARYSTAFFRESOURCE on SALESORDERITEMITINERARYSTAFFRESOURCE.ITINERARYSTAFFRESOURCEID = ITINERARYSTAFFRESOURCE.ID
                where SALESORDERITEM.SALESORDERID = @ID

                union all

                select VOLUNTEERTYPEID
                from dbo.SALESORDERITEM
                    inner join dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMSTAFFRESOURCE.SALESORDERITEMID
                    inner join dbo.ITINERARYITEMSTAFFRESOURCE on SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID = ITINERARYITEMSTAFFRESOURCE.ID
                where SALESORDERITEM.SALESORDERID = @ID
            ) T
            group by VOLUNTEERTYPEID

            update @RESOURCES set
                AMOUNT = round(RRSA.[AMOUNT] / [@RESOURCES].RESOURCECOUNT , 2),
                NEEDSOFFSET = case
                                when round(RRSA.[AMOUNT] / [@RESOURCES].RESOURCECOUNT, 2) * [@RESOURCES].RESOURCECOUNT <> RRSA.[AMOUNT]
                                    then 1
                                else 0 end
            from dbo.RESERVATIONRATESCALEAPPLICATION RRSA
            inner join dbo.RESERVATIONRATESCALE RRS on
                RRSA.RESERVATIONRATESCALEID = RRS.ID
            where
                RRS.ID = @ID and
                [@RESOURCES].RESOURCEID = RRSA.RESOURCEID

            update @STAFFRESOURCES set
                AMOUNT = round(RRSA.[AMOUNT] / [@STAFFRESOURCES].VOLUNTEERTYPECOUNT , 2),
                NEEDSOFFSET = case
                                when round(RRSA.[AMOUNT] / [@STAFFRESOURCES].VOLUNTEERTYPECOUNT, 2) * [@STAFFRESOURCES].VOLUNTEERTYPECOUNT <> RRSA.[AMOUNT]
                                    then 1
                                else 0 end
            from dbo.RESERVATIONRATESCALEAPPLICATION RRSA
            inner join dbo.RESERVATIONRATESCALE RRS on
                RRSA.RESERVATIONRATESCALEID = RRS.ID
            where
                RRS.ID = @ID and
                [@STAFFRESOURCES].VOLUNTEERTYPEID = RRSA.VOLUNTEERTYPEID



            update dbo.SALESORDERITEM set
                FLATRATEPRICE = coalesce([@RESOURCES].AMOUNT,0.0),
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from dbo.SALESORDERITEM
                inner join dbo.SALESORDERITEMITINERARYRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYRESOURCE.SALESORDERITEMID
                inner join dbo.ITINERARYRESOURCE on SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID = ITINERARYRESOURCE.ID
                inner join @RESOURCES on ITINERARYRESOURCE.RESOURCEID = [@RESOURCES].RESOURCEID
            where
                SALESORDERITEM.SALESORDERID = @ID and
                SALESORDERITEM.PRICINGSTRUCTURECODE = 1 and
                FLATRATEPRICE <> coalesce([@RESOURCES].AMOUNT,0.0)

            update dbo.SALESORDERITEM set
                FLATRATEPRICE = coalesce([@RESOURCES].AMOUNT,0.0),
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from dbo.SALESORDERITEM
                inner join dbo.SALESORDERITEMITINERARYITEMRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMRESOURCE.SALESORDERITEMID
                inner join dbo.ITINERARYITEMRESOURCE on SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID = ITINERARYITEMRESOURCE.ID
                inner join @RESOURCES on ITINERARYITEMRESOURCE.RESOURCEID = [@RESOURCES].RESOURCEID
            where
                SALESORDERITEM.SALESORDERID = @ID and
                SALESORDERITEM.PRICINGSTRUCTURECODE = 1 and
                FLATRATEPRICE <> coalesce([@RESOURCES].AMOUNT,0.0)


            update dbo.SALESORDERITEM set
                FLATRATEPRICE = coalesce([@STAFFRESOURCES].AMOUNT,0.0),
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from dbo.SALESORDERITEM
                inner join dbo.SALESORDERITEMITINERARYSTAFFRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYSTAFFRESOURCE.SALESORDERITEMID
                inner join dbo.ITINERARYSTAFFRESOURCE on SALESORDERITEMITINERARYSTAFFRESOURCE.ITINERARYSTAFFRESOURCEID = ITINERARYSTAFFRESOURCE.ID
                inner join @STAFFRESOURCES on ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = [@STAFFRESOURCES].VOLUNTEERTYPEID
            where
                SALESORDERITEM.SALESORDERID = @ID and
                SALESORDERITEM.PRICINGSTRUCTURECODE = 1 and
                FLATRATEPRICE <> coalesce([@STAFFRESOURCES].AMOUNT,0.0)

            update dbo.SALESORDERITEM set
                FLATRATEPRICE = coalesce([@STAFFRESOURCES].AMOUNT,0.0),
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from dbo.SALESORDERITEM
                inner join dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMSTAFFRESOURCE.SALESORDERITEMID
                inner join dbo.ITINERARYITEMSTAFFRESOURCE on SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID = ITINERARYITEMSTAFFRESOURCE.ID
                inner join @STAFFRESOURCES on ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = [@STAFFRESOURCES].VOLUNTEERTYPEID
            where
                SALESORDERITEM.SALESORDERID = @ID and
                SALESORDERITEM.PRICINGSTRUCTURECODE = 1 and
                FLATRATEPRICE <> coalesce([@STAFFRESOURCES].AMOUNT,0.0)


            if exists (select 1 from @RESOURCES where NEEDSOFFSET = 1)
            begin
                declare @RESOURCEOFFSETITEMS table
                (
                    ID uniqueidentifier,
                    RESOURCEID uniqueidentifier,
                    OFFSET money
                )

                insert into @RESOURCEOFFSETITEMS (RESOURCEID, OFFSET)
                select
                    RRSA.RESOURCEID,
                    RRSA.[AMOUNT] - ( round(RRSA.[AMOUNT] / [@RESOURCES].RESOURCECOUNT , 2) * [@RESOURCES].RESOURCECOUNT )
                from @RESOURCES
                    inner join dbo.RESERVATIONRATESCALEAPPLICATION RRSA on
                        RRSA.RESOURCEID = [@RESOURCES].RESOURCEID
                    inner join dbo.RESERVATIONRATESCALE RRS on
                        RRSA.RESERVATIONRATESCALEID = RRS.ID
                where
                    RRS.ID = @ID and
                    NEEDSOFFSET = 1

                -- Grab the any sales order item that contains the resource to be the offset    

                update @RESOURCEOFFSETITEMS set
                    ID = (
                        select top(1) SALESORDERITEMID
                        from (
                            select SALESORDERITEM.ID as SALESORDERITEMID
                            from dbo.SALESORDERITEM
                                inner join dbo.SALESORDERITEMITINERARYRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYRESOURCE.SALESORDERITEMID
                                inner join dbo.ITINERARYRESOURCE on SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID = ITINERARYRESOURCE.ID
                            where SALESORDERITEM.SALESORDERID = @ID
                                and ITINERARYRESOURCE.RESOURCEID = [@RESOURCEOFFSETITEMS].RESOURCEID

                            union all

                            select SALESORDERITEM.ID as SALESORDERITEMID
                            from dbo.SALESORDERITEM
                                inner join dbo.SALESORDERITEMITINERARYITEMRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMRESOURCE.SALESORDERITEMID
                                inner join dbo.ITINERARYITEMRESOURCE on SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID = ITINERARYITEMRESOURCE.ID
                            where SALESORDERITEM.SALESORDERID = @ID
                                and ITINERARYITEMRESOURCE.RESOURCEID = [@RESOURCEOFFSETITEMS].RESOURCEID
                        ) T
                    )

                update dbo.SALESORDERITEM set
                    FLATRATEPRICE = FLATRATEPRICE + isnull(OFFSET,0.0),
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from @RESOURCEOFFSETITEMS
                where 
                    SALESORDERITEM.ID = [@RESOURCEOFFSETITEMS].ID
            end

            if exists (select 1 from @STAFFRESOURCES where NEEDSOFFSET = 1)
            begin
                declare @STAFFRESOURCEOFFSETITEMS table
                (
                    ID uniqueidentifier,
                    VOLUNTEERTYPEID uniqueidentifier,
                    OFFSET money
                )

                insert into @STAFFRESOURCEOFFSETITEMS (VOLUNTEERTYPEID, OFFSET)
                select
                    RRSA.VOLUNTEERTYPEID,
                    RRSA.[AMOUNT] - ( round(RRSA.[AMOUNT] / [@STAFFRESOURCES].VOLUNTEERTYPECOUNT , 2) * [@STAFFRESOURCES].VOLUNTEERTYPECOUNT )
                from @STAFFRESOURCES
                    inner join dbo.RESERVATIONRATESCALEAPPLICATION RRSA on
                        RRSA.VOLUNTEERTYPEID = [@STAFFRESOURCES].VOLUNTEERTYPEID
                inner join dbo.RESERVATIONRATESCALE RRS on
                    RRSA.RESERVATIONRATESCALEID = RRS.ID
                where
                    RRS.ID = @ID and
                    NEEDSOFFSET = 1

                -- Grab the any sales order item that contains the resource to be the offset    

                update @STAFFRESOURCEOFFSETITEMS set
                    ID = (
                        select top(1) SALESORDERITEMID
                        from (
                            select SALESORDERITEM.ID as SALESORDERITEMID
                            from dbo.SALESORDERITEM
                                inner join dbo.SALESORDERITEMITINERARYSTAFFRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYSTAFFRESOURCE.SALESORDERITEMID
                                inner join dbo.ITINERARYSTAFFRESOURCE on SALESORDERITEMITINERARYSTAFFRESOURCE.ITINERARYSTAFFRESOURCEID = ITINERARYSTAFFRESOURCE.ID
                            where SALESORDERITEM.SALESORDERID = @ID
                                and ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = [@STAFFRESOURCEOFFSETITEMS].VOLUNTEERTYPEID

                            union all

                            select SALESORDERITEM.ID as SALESORDERITEMID
                            from dbo.SALESORDERITEM
                                inner join dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMSTAFFRESOURCE.SALESORDERITEMID
                                inner join dbo.ITINERARYITEMSTAFFRESOURCE on SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID = ITINERARYITEMSTAFFRESOURCE.ID
                            where SALESORDERITEM.SALESORDERID = @ID
                                and ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = [@STAFFRESOURCEOFFSETITEMS].VOLUNTEERTYPEID
                        ) T
                    )

                update dbo.SALESORDERITEM set
                    FLATRATEPRICE = FLATRATEPRICE + isnull(OFFSET,0.0),
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from @STAFFRESOURCEOFFSETITEMS
                where 
                    SALESORDERITEM.ID = [@STAFFRESOURCEOFFSETITEMS].ID
            end
        end