USP_DATALIST_RESERVATION_RESOURCES

CREATE procedure dbo.USP_DATALIST_RESERVATION_RESOURCES
(
@CONTEXTID uniqueidentifier
)
as
set nocount on;

select 
    SALESORDERITEM.ID,
    RESOURCE.NAME as DESCRIPTION,
    dbo.UFN_ITINERARY_STARTDATETIME(ITINERARYRESOURCE.ITINERARYID) as STARTDATE,
    dbo.UFN_ITINERARY_ENDDATETIME(ITINERARYRESOURCE.ITINERARYID) as ENDDATE,
    case RESOURCE.ISPERTICKETITEM
        when 0 then
            ITINERARYRESOURCE.QUANTITYNEEDED
        else
            dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(ITINERARYATTENDEECOUNT.QUANTITY * ITINERARYRESOURCE.PERTICKETQUANTITY, ITINERARYRESOURCE.PERTICKETDIVISOR)
    end as QUANTITY,
    SALESORDERITEM.PRICE,
    SALESORDERITEM.PRICINGSTRUCTURECODE,
    SALESORDERITEM.TOTAL
from 
    dbo.SALESORDERITEM
inner join 
    dbo.SALESORDERITEMITINERARYRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYRESOURCE.SALESORDERITEMID
inner join 
    dbo.ITINERARYRESOURCE on SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID = ITINERARYRESOURCE.ID
inner join 
    dbo.RESOURCE on ITINERARYRESOURCE.RESOURCEID = RESOURCE.ID
outer apply (
    select 
        sum(ITINERARYATTENDEE.QUANTITY) as QUANTITY
    from 
        dbo.ITINERARYATTENDEE
    where 
        ITINERARYATTENDEE.ITINERARYID = ITINERARYRESOURCE.ITINERARYID
) as ITINERARYATTENDEECOUNT
where 
    SALESORDERITEM.SALESORDERID = @CONTEXTID
    and SALESORDERITEM.TYPECODE = 8

union all

select 
    SALESORDERITEM.ID,
    ITINERARYITEM.NAME + ' - ' + RESOURCE.NAME as DESCRIPTION,
    ITINERARYITEM.STARTDATETIME as STARTDATE,
    ITINERARYITEM.ENDDATETIME as ENDDATE,
    case RESOURCE.ISPERTICKETITEM
        when 0 then
            ITINERARYITEMRESOURCE.QUANTITYNEEDED
        else
            dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(ITINERARYATTENDEECOUNT.QUANTITY * ITINERARYITEMRESOURCE.PERTICKETQUANTITY, ITINERARYITEMRESOURCE.PERTICKETDIVISOR)
    end as QUANTITY,
    SALESORDERITEM.PRICE,
    SALESORDERITEM.PRICINGSTRUCTURECODE,
    SALESORDERITEM.TOTAL
from 
    dbo.SALESORDERITEM
inner join 
    dbo.SALESORDERITEMITINERARYITEMRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMRESOURCE.SALESORDERITEMID
inner join 
    dbo.ITINERARYITEMRESOURCE on SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID = ITINERARYITEMRESOURCE.ID
inner join 
    dbo.ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
inner join 
    dbo.RESOURCE on ITINERARYITEMRESOURCE.RESOURCEID = RESOURCE.ID
outer apply (
    select 
        sum(ITINERARYATTENDEE.QUANTITY) as QUANTITY
    from 
        dbo.ITINERARYATTENDEE
    where 
        ITINERARYATTENDEE.ITINERARYID = ITINERARYITEM.ITINERARYID
) as ITINERARYATTENDEECOUNT
where 
    SALESORDERITEM.SALESORDERID = @CONTEXTID
    and SALESORDERITEM.TYPECODE = 9

return 0;