UFN_DELIVERYMETHOD_INFO_BYSALESMETHODID

create function dbo.UFN_DELIVERYMETHOD_INFO_BYSALESMETHODID(
    @SALESMETHODID uniqueidentifier
)
returns @result table (
    [HASDELIVERYMETHOD] bit, 
    [HASUNRESTRICTEDDELIVERY] bit, 
    [EARLIESTVALIDEVENTDATETIMEWITHOFFSET] datetimeoffset
)
with execute as caller
as begin
    --Filtering out events that cannot be sold with any delivery methods available in this sales method

    declare @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(sysutcdatetime(), 1);
    declare @DELIVERYMETHODS table (
        ID uniqueidentifier,
        EARLIESTEVENTTIMEWITHOFFSET datetimeoffset
    );

    insert into @DELIVERYMETHODS
    select 
        [DELIVERYMETHOD].[ID],
        case 
            when [BLOCKDELIVERY] = 0 then null 
            when [BLOCKTIMEUNITCODE] = 0 then dateadd(hour,BLOCKTIMEVALUE,@CURRENTDATETIMEOFFSET)
            when [BLOCKTIMEUNITCODE] = 1 then dateadd(day,BLOCKTIMEVALUE,@CURRENTDATETIMEOFFSET)
        end as [OFFTIME]
    from dbo.[DELIVERYMETHOD]
    inner join dbo.[SALESMETHODDELIVERYMETHOD]
        on [DELIVERYMETHOD].[ID] = [SALESMETHODDELIVERYMETHOD].[DELIVERYMETHODID]
    where 
        [DELIVERYMETHOD].[ISACTIVE] = 1 and
        [SALESMETHODDELIVERYMETHOD].[SALESMETHODID] = @SALESMETHODID

    declare @HASDELIVERYMETHOD bit = 0
    declare     @HASUNRESTRICTEDDELIVERY bit = 0
    declare @EARLIESTVALIDEVENTDATETIMEWITHOFFSET datetimeoffset = null 

    if exists(select top 1 1 from @DELIVERYMETHODS)
        set @HASDELIVERYMETHOD = 1
    else
        set @HASDELIVERYMETHOD = 0

    if exists(select top 1 1 from @DELIVERYMETHODS where [EARLIESTEVENTTIMEWITHOFFSET] is null)
        set @HASUNRESTRICTEDDELIVERY = 1
    else
    begin
        set @HASUNRESTRICTEDDELIVERY = 0
        select top 1 @EARLIESTVALIDEVENTDATETIMEWITHOFFSET = [EARLIESTEVENTTIMEWITHOFFSET]
        from @DELIVERYMETHODS
        where [EARLIESTEVENTTIMEWITHOFFSET] is not null
        order by [EARLIESTEVENTTIMEWITHOFFSET] asc
    end

    insert into @result        
    select @HASDELIVERYMETHOD, @HASUNRESTRICTEDDELIVERY, @EARLIESTVALIDEVENTDATETIMEWITHOFFSET

    return
end