UFN_WORKFLOW_GET_USER_INBOX_ASSIGNMENTS

CREATE function dbo.UFN_WORKFLOW_GET_USER_INBOX_ASSIGNMENTS(@CURRENTAPPUSERID uniqueidentifier)
returns table
--Returns BBWORKFLOWINBOXID and ASSIGNMENTTYPECODE (1=user,0=role) for all inboxes assigned to a user directly or via a role.

as return

(

    --user directly assigned

    select BBWORKFLOWINBOXID, ASSIGNMENTTYPECODE  from dbo.BBWORKFLOWINBOXASSIGNMENT as WFIBA_U
    WHERE

    WFIBA_U.ASSIGNMENTTYPECODE=1 
    AND 
    WFIBA_U.APPUSERID=@CURRENTAPPUSERID

UNION --return distinct inbox ids via Union (not UNION ALL)


    --user assigned via a role

    select BBWORKFLOWINBOXID, ASSIGNMENTTYPECODE from dbo.BBWORKFLOWINBOXASSIGNMENT as WFIBA_U
    where
    WFIBA_U.ASSIGNMENTTYPECODE=0 
    AND 
    WFIBA_U.SYSTEMROLEID in 
            (SELECT SYSTEMROLEID FROM dbo.SYSTEMROLEAPPUSER AS SRAU WHERE SRAU.APPUSERID=@CURRENTAPPUSERID)


)