UFN_SALESORDER_EXISTSINVALIDMEMBERSHIP_CODE

CREATE function dbo.UFN_SALESORDER_EXISTSINVALIDMEMBERSHIP_CODE (
    @SALESORDERID uniqueidentifier
)
returns tinyint
with execute as caller
as begin
    declare @MEMBERSHIPERRORCODE tinyint = 0

    select top 1
        @MEMBERSHIPERRORCODE = [ERROR].[CODE]
    from
        dbo.[SALESORDERITEM]
    inner join
        dbo.[SALESORDERITEMMEMBERSHIP] on [SALESORDERITEM].ID = [SALESORDERITEMMEMBERSHIP].ID
    inner join
        dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELID
    inner join
        dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.ID = SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELTERMID
    outer apply (
        select case 
            --Check for no membership primary member

            when not exists(
                    select 1
                    from dbo.[SALESORDERITEMMEMBER]
                    where 
                        [SALESORDERITEMMEMBER].[SALESORDERITEMMEMBERSHIPID] = [SALESORDERITEMMEMBERSHIP].[ID] and
                        [SALESORDERITEMMEMBER].[ISPRIMARY] = 1
                ) then 1
        end as NOTEXISTS
    ) as PRIMARYMEMBER
    outer apply (
        select 
            case 
                --Ordering in terms of which errors could affect pricing of membership then in terms of severity.

                when MEMBERSHIPLEVELTERM.ISACTIVE = 0 and MEMBERSHIPLEVEL.ISACTIVE = 0 then 4
                when MEMBERSHIPLEVEL.ISACTIVE = 0 then 2
                when MEMBERSHIPLEVELTERM.ISACTIVE = 0 then 3
                when PRIMARYMEMBER.NOTEXISTS = 1 then 1
            end as CODE
    ) as [ERROR]
    where 
        [SALESORDERITEM].[SALESORDERID] = @SALESORDERID
        and [SALESORDERITEM].[TYPECODE] = 1  -- Membership

    order by
        [ERROR].CODE desc --Returning those which affect price first


    return @MEMBERSHIPERRORCODE;
end