USP_DATALIST_RESERVATION_STAFFRESOURCES

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

select 
    SALESORDERITEM.ID,
    dbo.UFN_VOLUNTEERTYPE_GETDESCRIPTION(ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID) as DESCRIPTION,
    dbo.UFN_ITINERARY_STARTDATETIME(ITINERARYSTAFFRESOURCE.ITINERARYID) as STARTDATE,
    dbo.UFN_ITINERARY_ENDDATETIME(ITINERARYSTAFFRESOURCE.ITINERARYID) as ENDDATE,
    ITINERARYSTAFFRESOURCE.QUANTITYNEEDED as QUANTITY,
    SALESORDERITEM.PRICE,
    SALESORDERITEM.PRICINGSTRUCTURECODE,
    SALESORDERITEM.TOTAL
from 
    dbo.SALESORDERITEM
inner join 
    dbo.SALESORDERITEMITINERARYSTAFFRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYSTAFFRESOURCE.SALESORDERITEMID
inner join 
    dbo.ITINERARYSTAFFRESOURCE on SALESORDERITEMITINERARYSTAFFRESOURCE.ITINERARYSTAFFRESOURCEID = ITINERARYSTAFFRESOURCE.ID
where 
    SALESORDERITEM.SALESORDERID = @CONTEXTID
    and SALESORDERITEM.TYPECODE = 10

union all

select 
    SALESORDERITEM.ID,
    ITINERARYITEM.NAME + ' - ' + dbo.UFN_VOLUNTEERTYPE_GETDESCRIPTION(ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID) as DESCRIPTION,
    ITINERARYITEM.STARTDATETIME as STARTDATE,
    ITINERARYITEM.ENDDATETIME as ENDDATE,
    ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED as QUANTITY,
    SALESORDERITEM.PRICE,
    SALESORDERITEM.PRICINGSTRUCTURECODE,
    SALESORDERITEM.TOTAL
from 
    dbo.SALESORDERITEM
inner join 
    dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMSTAFFRESOURCE.SALESORDERITEMID
inner join 
    dbo.ITINERARYITEMSTAFFRESOURCE on SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID = ITINERARYITEMSTAFFRESOURCE.ID
inner join 
    dbo.ITINERARYITEM on ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
where 
    SALESORDERITEM.SALESORDERID = @CONTEXTID
    and SALESORDERITEM.TYPECODE = 11