UFN_RESERVATION_GETPERPERSONCHARGES

CREATE function dbo.UFN_RESERVATION_GETPERPERSONCHARGES
(
    @RESERVATIONID uniqueidentifier,
    @PRICINGSTRUCTURECODE int
)
returns table
as 
    return
    select
        SOIT.PRICETYPECODEID,
        ITINERARYITEM.ITINERARYID,
        sum(SOI.PRICE) as PRICE,
        max(ITINERARYATTENDEE.QUANTITY) as QUANTITY
    from dbo.SALESORDERITEMTICKET as SOIT
        inner join SALESORDERITEM as SOI on SOI.ID = SOIT.ID
        inner join ITINERARYITEM on SOIT.PROGRAMID = ITINERARYITEM.PROGRAMID or SOIT.EVENTID = ITINERARYITEM.EVENTID
        inner join ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
        inner join ITINERARYATTENDEE on ITINERARY.ID = ITINERARYATTENDEE.ITINERARYID and SOIT.PRICETYPECODEID = ITINERARYATTENDEE.PRICETYPECODEID
    where
        SALESORDERID = @RESERVATIONID and
        ITINERARY.RESERVATIONID = @RESERVATIONID and
        TYPECODE = 0 and
        (@PRICINGSTRUCTURECODE is null or @PRICINGSTRUCTURECODE = PRICINGSTRUCTURECODE)
    group by
        SOIT.PRICETYPECODEID,
        ITINERARYITEM.ITINERARYID