USP_DATALIST_ITINERARYSTAFFRESOURCE

Gives the user a list of all the itinerary staff resources used on an itinerary.
Parameters

Parameter	Parameter Type	Mode	Description
@ITINERARYID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_ITINERARYSTAFFRESOURCE(@ITINERARYID uniqueidentifier)
                as
                    set nocount on;

                    select ITINERARYSTAFFRESOURCE.ID,
                           VOLUNTEERTYPE.ID,
                           VOLUNTEERTYPE.NAME,
                           ITINERARYSTAFFRESOURCE.QUANTITYNEEDED,
                           ITINERARYSTAFFRESOURCE.FILLEDBYCODE,
                           ITINERARYSTAFFRESOURCE.FILLEDBY,
                           case when (ITINERARYSTAFFRESOURCE.FILLEDBYCODE != 0) or (ITINERARY.STARTDATETIME is null) then 
                               dbo.UFN_ITINERARYSTAFFRESOURCE_GETASSIGNMENTSTRING(ITINERARYSTAFFRESOURCE.ID) 
                           else 
                               dbo.UFN_JOBOCCURRENCE_GETASSIGNMENTSTRING(ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID) 
                           end as ASSIGNEDRESOURCES,
                           case when (not GROUPTYPEREQUIREDSTAFFRESOURCE.ID is null) then 1 else 0 end as ISREQUIREDRESOURCE
                    from dbo.ITINERARYSTAFFRESOURCE
                        inner join dbo.VOLUNTEERTYPE
                            on ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
                        inner join dbo.ITINERARY
                            on ITINERARYSTAFFRESOURCE.ITINERARYID = ITINERARY.ID
                        left outer join dbo.GROUPTYPEREQUIREDSTAFFRESOURCE
                            on (GROUPTYPEREQUIREDSTAFFRESOURCE.FILLEDBYCODE = ITINERARYSTAFFRESOURCE.FILLEDBYCODE) and
                                (GROUPTYPEREQUIREDSTAFFRESOURCE.VOLUNTEERTYPEID = ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID) and
                                (ITINERARY.GROUPSALESGROUPTYPECODEID = GROUPTYPEREQUIREDSTAFFRESOURCE.GROUPSALESGROUPTYPECODEID)
                    where ITINERARYSTAFFRESOURCE.ITINERARYID = @ITINERARYID