UFN_SALESORDER_RESERVATIONSTAFFASSIGNMENT

        CREATE function [dbo].[UFN_SALESORDER_RESERVATIONSTAFFASSIGNMENT] (@SALESORDERID uniqueidentifier)
        returns @RESERVATIONSTAFFASSIGNMENT table (ID uniqueidentifier, VOLUNTEERTYPE nvarchar(100), ASSIGNEDTO nvarchar(154), STARTTIME dbo.UDT_HOURMINUTE, ENDTIME dbo.UDT_HOURMINUTE, INITERARYNAME nvarchar(154), ITINERARYITEMNAME nvarchar(100), QUANTITYOPEN int, ISASSIGNED nvarchar(20), FILLEDBYCODE tinyint, ISITINERARYLEVEL bit, CONSTITUENTID uniqueidentifier)
        as
        begin
        declare @ITINERARYSTAFFRESOURCES table
        (
            ID uniqueidentifier,
            QUANTITYNEEDED int,
            ITINERARYNAME nvarchar(154),
            VOLUNTEERTYPE nvarchar(100),
            STARTTIME dbo.UDT_HOURMINUTE,
            ENDTIME dbo.UDT_HOURMINUTE,
            JOBOCCURRENCEID uniqueidentifier,
            FILLEDBYCODE tinyint
        )
        insert into @ITINERARYSTAFFRESOURCES
        select
            ITINERARYSTAFFRESOURCE.ID as ID,
            ITINERARYSTAFFRESOURCE.QUANTITYNEEDED as QUANTITYNEEDED,
            ITINERARY.NAME,
            VOLUNTEERTYPE.NAME,
            dbo.[UFN_HOURMINUTE_GETFROMDATE](ITINERARY.STARTDATETIME) as STARTTIME,
            dbo.[UFN_HOURMINUTE_GETFROMDATE](ITINERARY.ENDDATETIME) as ENDTIME,
            ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID,
            ITINERARYSTAFFRESOURCE.FILLEDBYCODE
        from dbo.ITINERARYSTAFFRESOURCE
        inner join dbo.ITINERARY on
            ITINERARYSTAFFRESOURCE.ITINERARYID = ITINERARY.ID
        inner join dbo.VOLUNTEERTYPE on
            ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
        where
            ITINERARY.RESERVATIONID = @SALESORDERID and
            ITINERARY.STARTDATETIME is not null and
            ITINERARY.ENDDATETIME is not null


        declare @ITINERARYITEMSTAFFRESOURCES table
        (
            ID uniqueidentifier,
            QUANTITYNEEDED int,
            ITINERARYNAME nvarchar(154),
            ITINERARYITEMNAME nvarchar(100),
            VOLUNTEERTYPE nvarchar(100),
            STARTTIME dbo.UDT_HOURMINUTE,
            ENDTIME dbo.UDT_HOURMINUTE,
            JOBOCCURRENCEID uniqueidentifier,
            FILLEDBYCODE tinyint
        )
        insert into @ITINERARYITEMSTAFFRESOURCES
        select
            ITINERARYITEMSTAFFRESOURCE.ID as ID,
            ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED as QUANTITYNEEDED,
            ITINERARY.NAME,
            ITINERARYITEM.NAME,
            VOLUNTEERTYPE.NAME,
            ITINERARYITEM.STARTTIME, 
            ITINERARYITEM.ENDTIME,
            ITINERARYITEMSTAFFRESOURCE.JOBOCCURRENCEID,
            ITINERARYITEMSTAFFRESOURCE.FILLEDBYCODE
        from dbo.ITINERARYITEMSTAFFRESOURCE
        inner join dbo.ITINERARYITEM on
            ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
        inner join dbo.ITINERARY on
            ITINERARYITEM.ITINERARYID = ITINERARY.ID
        inner join dbo.VOLUNTEERTYPE on
            ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
        where
            ITINERARY.RESERVATIONID = @SALESORDERID and 
            ITINERARYITEM.ITEMTYPECODE <> 3

        declare @LIST table
        (
            ID uniqueidentifier,
            ITINERARYNAME nvarchar(154),
            ITINERARYITEMNAME nvarchar(100),
            STARTTIME dbo.UDT_HOURMINUTE,
            ENDTIME dbo.UDT_HOURMINUTE,
            VOLUNTEERTYPE nvarchar(100),
            ASSIGNEDTO nvarchar(154),
            QUANTITYOPEN int,
            ISSCHEDULED bit,
            FILLEDBYCODE tinyint,
            ISITINERARYLEVEL bit,
            CONSTITUENTID uniqueidentifier
        );

        -- Select all staff and board type itinerary staff resources without assignments

        with COUNT_CTE as 
        (
            select
                ITINERARYSTAFFRESOURCEASSIGNMENT.ITINERARYSTAFFRESOURCEID as ID,
                count(1) as ASSIGNMENTCOUNT
            from dbo.ITINERARYSTAFFRESOURCEASSIGNMENT
            inner join @ITINERARYSTAFFRESOURCES as STAFFRESOURCES on
                STAFFRESOURCES.ID = ITINERARYSTAFFRESOURCEASSIGNMENT.ITINERARYSTAFFRESOURCEID
            group by ITINERARYSTAFFRESOURCEASSIGNMENT.ITINERARYSTAFFRESOURCEID
        )
        insert into @LIST
        select
            STAFFRESOURCES.ID as ID,
            STAFFRESOURCES.ITINERARYNAME as ITINERARYNAME,
            '' as ITINERARYITEMNAME,
            STAFFRESOURCES.STARTTIME as STARTTIME,
            STAFFRESOURCES.ENDTIME as ENDTIME,
            STAFFRESOURCES.VOLUNTEERTYPE as VOLUNTEERTYPE,
            '(Unassigned)' as ASSIGNEDTO,
            STAFFRESOURCES.QUANTITYNEEDED - coalesce(COUNT.ASSIGNMENTCOUNT,0) as QUANTITYOPEN,
            0 as ISSCHEDULED,
            STAFFRESOURCES.FILLEDBYCODE,
            1 as ISITINERARYLEVEL,
            null as CONSTITUENTID
        from @ITINERARYSTAFFRESOURCES as STAFFRESOURCES
        left join COUNT_CTE as COUNT on
            STAFFRESOURCES.ID = COUNT.ID
        where
            STAFFRESOURCES.FILLEDBYCODE <> 0 and
            (
                COUNT.ID is null or
                STAFFRESOURCES.QUANTITYNEEDED <> COUNT.ASSIGNMENTCOUNT
            );

        -- Select all staff and board type itinerary item staff resources without assignments

        with COUNT_CTE as 
        (
            select
                ITINERARYITEMSTAFFRESOURCEASSIGNMENT.ITINERARYITEMSTAFFRESOURCEID as ID,
                count(1) as ASSIGNMENTCOUNT
            from dbo.ITINERARYITEMSTAFFRESOURCEASSIGNMENT
            inner join @ITINERARYITEMSTAFFRESOURCES as STAFFRESOURCES on
                STAFFRESOURCES.ID = ITINERARYITEMSTAFFRESOURCEASSIGNMENT.ITINERARYITEMSTAFFRESOURCEID
            group by ITINERARYITEMSTAFFRESOURCEASSIGNMENT.ITINERARYITEMSTAFFRESOURCEID
        )
        insert into @LIST
        select
            STAFFRESOURCES.ID as ID,
            STAFFRESOURCES.ITINERARYNAME as ITINERARYNAME,
            STAFFRESOURCES.ITINERARYITEMNAME as ITINERARYITEMNAME,
            STAFFRESOURCES.STARTTIME as STARTTIME,
            STAFFRESOURCES.ENDTIME as ENDTIME,
            STAFFRESOURCES.VOLUNTEERTYPE as VOLUNTEERTYPE,
            '(Unassigned)' as ASSIGNEDTO,
            STAFFRESOURCES.QUANTITYNEEDED - coalesce(COUNT.ASSIGNMENTCOUNT,0) as QUANTITYOPEN,
            0 as ISSCHEDULED,
            STAFFRESOURCES.FILLEDBYCODE,
            0 as ISITINERARYLEVEL,
            null as CONSTITUENTID
        from @ITINERARYITEMSTAFFRESOURCES as STAFFRESOURCES
        left join COUNT_CTE as COUNT on
            STAFFRESOURCES.ID = COUNT.ID
        where
            STAFFRESOURCES.FILLEDBYCODE <> 0 and
            (
                COUNT.ID is null or
                STAFFRESOURCES.QUANTITYNEEDED <> COUNT.ASSIGNMENTCOUNT
            )

        -- Select all staff and board type itinerary staff resources with assignments

        insert into @LIST
        select
            STAFFRESOURCES.ID as ID,
            STAFFRESOURCES.ITINERARYNAME as ITINERARYNAME,
            '' as ITINERARYITEMNAME,
            STAFFRESOURCES.STARTTIME as STARTTIME,
            STAFFRESOURCES.ENDTIME as ENDTIME,
            STAFFRESOURCES.VOLUNTEERTYPE as VOLUNTEERTYPE,
            dbo.UFN_CONSTITUENT_BUILDNAME(ITINERARYSTAFFRESOURCEASSIGNMENT.CONSTITUENTID) as ASSIGNEDTO,
            null as QUANTITYOPEN,
            1 as ISSCHEDULED,
            STAFFRESOURCES.FILLEDBYCODE,
            1 as ISITINERARYLEVEL,
            ITINERARYSTAFFRESOURCEASSIGNMENT.CONSTITUENTID as CONSTITUENTID
        from dbo.ITINERARYSTAFFRESOURCEASSIGNMENT
        inner join @ITINERARYSTAFFRESOURCES as STAFFRESOURCES on
            STAFFRESOURCES.ID = ITINERARYSTAFFRESOURCEASSIGNMENT.ITINERARYSTAFFRESOURCEID
        where
            STAFFRESOURCES.FILLEDBYCODE <> 0

        -- Select all staff and board type itinerary item staff resources with assignments

        insert into @LIST
        select
            STAFFRESOURCES.ID as ID,
            STAFFRESOURCES.ITINERARYNAME as ITINERARYNAME,
            STAFFRESOURCES.ITINERARYITEMNAME as ITINERARYITEMNAME,
            STAFFRESOURCES.STARTTIME as STARTTIME,
            STAFFRESOURCES.ENDTIME as ENDTIME,
            STAFFRESOURCES.VOLUNTEERTYPE as VOLUNTEERTYPE,
            dbo.UFN_CONSTITUENT_BUILDNAME(ITINERARYITEMSTAFFRESOURCEASSIGNMENT.CONSTITUENTID) as ASSIGNEDTO,
            null as QUANTITYOPEN,
            1 as ISSCHEDULED,
            STAFFRESOURCES.FILLEDBYCODE,
            0 as ISITINERARYLEVEL,
            ITINERARYITEMSTAFFRESOURCEASSIGNMENT.CONSTITUENTID as CONSTITUENTID
        from dbo.ITINERARYITEMSTAFFRESOURCEASSIGNMENT
        inner join @ITINERARYITEMSTAFFRESOURCES as STAFFRESOURCES on
            STAFFRESOURCES.ID = ITINERARYITEMSTAFFRESOURCEASSIGNMENT.ITINERARYITEMSTAFFRESOURCEID
        where
            STAFFRESOURCES.FILLEDBYCODE <> 0;


        -- Select all volunteer type itinerary staff resources without assignments

        with COUNT_CTE as 
        (
            select
                VOLUNTEERASSIGNMENT.JOBOCCURRENCEID as ID,
                count(1) as ASSIGNMENTCOUNT
            from dbo.VOLUNTEERASSIGNMENT
            inner join @ITINERARYSTAFFRESOURCES as STAFFRESOURCES on
                STAFFRESOURCES.JOBOCCURRENCEID = VOLUNTEERASSIGNMENT.JOBOCCURRENCEID
            group by VOLUNTEERASSIGNMENT.JOBOCCURRENCEID
        )
        insert into @LIST
        select
            STAFFRESOURCES.ID as ID,
            STAFFRESOURCES.ITINERARYNAME as ITINERARYNAME,
            '' as ITINERARYITEMNAME,
            STAFFRESOURCES.STARTTIME as STARTTIME,
            STAFFRESOURCES.ENDTIME as ENDTIME,
            STAFFRESOURCES.VOLUNTEERTYPE as VOLUNTEERTYPE,
            '(Unassigned)' as ASSIGNEDTO,
            STAFFRESOURCES.QUANTITYNEEDED - coalesce(COUNT.ASSIGNMENTCOUNT,0) as QUANTITYOPEN,
            0 as ISSCHEDULED,
            STAFFRESOURCES.FILLEDBYCODE,
            1 as ISITINERARYLEVEL,
            null as CONSTITUENTID
        from @ITINERARYSTAFFRESOURCES as STAFFRESOURCES
        left join COUNT_CTE as COUNT on
            STAFFRESOURCES.JOBOCCURRENCEID = COUNT.ID
        where
            STAFFRESOURCES.FILLEDBYCODE = 0 and
            (
                COUNT.ID is null or
                STAFFRESOURCES.QUANTITYNEEDED <> COUNT.ASSIGNMENTCOUNT
            );

        -- Select all volunteer type itinerary item staff resources without assignments

        with COUNT_CTE as 
        (
            select
                VOLUNTEERASSIGNMENT.JOBOCCURRENCEID as ID,
                count(1) as ASSIGNMENTCOUNT
            from dbo.VOLUNTEERASSIGNMENT
            inner join @ITINERARYITEMSTAFFRESOURCES as STAFFRESOURCES on
                STAFFRESOURCES.JOBOCCURRENCEID = VOLUNTEERASSIGNMENT.JOBOCCURRENCEID
            group by VOLUNTEERASSIGNMENT.JOBOCCURRENCEID
        )
        insert into @LIST
        select
            STAFFRESOURCES.ID as ID,
            STAFFRESOURCES.ITINERARYNAME as ITINERARYNAME,
            STAFFRESOURCES.ITINERARYITEMNAME as ITINERARYITEMNAME,
            STAFFRESOURCES.STARTTIME as STARTTIME,
            STAFFRESOURCES.ENDTIME as ENDTIME,
            STAFFRESOURCES.VOLUNTEERTYPE as VOLUNTEERTYPE,
            '(Unassigned)' as ASSIGNEDTO,
            STAFFRESOURCES.QUANTITYNEEDED - coalesce(COUNT.ASSIGNMENTCOUNT,0) as QUANTITYOPEN,
            0 as ISSCHEDULED,
            STAFFRESOURCES.FILLEDBYCODE,
            0 as ISITINERARYLEVEL,
            null as CONSTITUENTID
        from @ITINERARYITEMSTAFFRESOURCES as STAFFRESOURCES
        left join COUNT_CTE as COUNT on
            STAFFRESOURCES.JOBOCCURRENCEID = COUNT.ID
        where
            STAFFRESOURCES.FILLEDBYCODE = 0 and
            (
                COUNT.ID is null or
                STAFFRESOURCES.QUANTITYNEEDED <> COUNT.ASSIGNMENTCOUNT
            )

        -- Select all volunteer type itinerary staff resources with assignments

        insert into @LIST
        select
            STAFFRESOURCES.ID as ID,
            STAFFRESOURCES.ITINERARYNAME as ITINERARYNAME,
            '' as ITINERARYITEMNAME,
            STAFFRESOURCES.STARTTIME as STARTTIME,
            STAFFRESOURCES.ENDTIME as ENDTIME,
            STAFFRESOURCES.VOLUNTEERTYPE as VOLUNTEERTYPE,
            dbo.UFN_CONSTITUENT_BUILDNAME(VOLUNTEERASSIGNMENT.VOLUNTEERID) as ASSIGNEDTO,
            null as QUANTITYOPEN,
            1 as ISSCHEDULED,
            STAFFRESOURCES.FILLEDBYCODE,
            1 as ISITINERARYLEVEL,
            VOLUNTEERASSIGNMENT.VOLUNTEERID as CONSTITUENTID
        from dbo.VOLUNTEERASSIGNMENT
        inner join @ITINERARYSTAFFRESOURCES as STAFFRESOURCES on
            VOLUNTEERASSIGNMENT.JOBOCCURRENCEID = STAFFRESOURCES.JOBOCCURRENCEID
        where
            STAFFRESOURCES.FILLEDBYCODE = 0

        -- Select all volunteer type itinerary item staff resources with assignments

        insert into @LIST
        select
            STAFFRESOURCES.ID as ID,
            STAFFRESOURCES.ITINERARYNAME as ITINERARYNAME,
            STAFFRESOURCES.ITINERARYITEMNAME as ITINERARYITEMNAME,
            STAFFRESOURCES.STARTTIME as STARTTIME,
            STAFFRESOURCES.ENDTIME as ENDTIME,
            STAFFRESOURCES.VOLUNTEERTYPE as VOLUNTEERTYPE,
            dbo.UFN_CONSTITUENT_BUILDNAME(VOLUNTEERASSIGNMENT.VOLUNTEERID) as ASSIGNEDTO,
            null as QUANTITYOPEN,
            1 as ISSCHEDULED,
            STAFFRESOURCES.FILLEDBYCODE,
            0 as ISITINERARYLEVEL,
            VOLUNTEERASSIGNMENT.VOLUNTEERID as CONSTITUENTID
        from dbo.VOLUNTEERASSIGNMENT
        inner join @ITINERARYITEMSTAFFRESOURCES as STAFFRESOURCES on
            VOLUNTEERASSIGNMENT.JOBOCCURRENCEID = STAFFRESOURCES.JOBOCCURRENCEID
        where
            STAFFRESOURCES.FILLEDBYCODE = 0

        insert into @RESERVATIONSTAFFASSIGNMENT (ID, VOLUNTEERTYPE, ASSIGNEDTO, STARTTIME, ENDTIME, INITERARYNAME, ITINERARYITEMNAME, QUANTITYOPEN, ISASSIGNED, FILLEDBYCODE, ISITINERARYLEVEL, CONSTITUENTID)
        select
            ID,
            VOLUNTEERTYPE,
            case 
                when coalesce(QUANTITYOPEN,0) < 0 then '(Overassigned)'
                else ASSIGNEDTO
            end as ASSIGNEDTO,
            STARTTIME,
            ENDTIME,
            ITINERARYNAME,
            ITINERARYITEMNAME,
            QUANTITYOPEN,
            case 
                when ISSCHEDULED = 1 then 'Assigned' 
                else
                    case 
                        when coalesce(QUANTITYOPEN,0) < 0 then 'Overassigned'
                        else 'Unassigned' 
                    end 
            end as ISASSIGNED,
            FILLEDBYCODE,
            ISITINERARYLEVEL,
            CONSTITUENTID
        from @LIST

        return

        end