UFN_ACCOUNTINGELEMENT_ELEMENTSFORUSER

CREATE function dbo.UFN_ACCOUNTINGELEMENT_ELEMENTSFORUSER
(
  @APPUSERID uniqueidentifier
)
returns @T table (ELEMENTID uniqueidentifier primary key) as
begin
  -- Return all elements if user is sysadmin.

  -- If user has a security mode = 0, they have access to all elements

  if dbo.UFN_APPUSER_ISSYSADMIN(@APPUSERID) = 1 or exists(select 1 from dbo.SYSTEMROLEAPPUSER APP where APP.APPUSERID = @APPUSERID and APP.ACCOUNTINGELEMENTSECURITYMODECODE = 0)
  begin
    insert @T (ELEMENTID) select ID from dbo.PDACCOUNTSEGMENTVALUE
    return;
  end

  -- Elements explicitly granted via security mode 1

  insert @T (ELEMENTID)
    select distinct AESGE.DATAELEMENTID ELEMENTID
    from 
      dbo.ACCOUNTINGELEMENTSECURITYGROUPELEMENT AESGE
      inner join dbo.ACCOUNTINGELEMENTSECURITYGROUP AESG on AESGE.ACCOUNTINGELEMENTSECURITYGROUPID = AESG.ID
      inner join dbo.SYSTEMROLEAPPUSERACCOUNTINGELEMENTSECURITY SAE on sae.ACCOUNTINGELEMENTSECURITYGROUPID = AESG.ID
      inner join dbo.SYSTEMROLEAPPUSER on SAE.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID
    where 
      SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID
      and SYSTEMROLEAPPUSER.ACCOUNTINGELEMENTSECURITYMODECODE = 1

  -- Elements implicitly granted via security mode 2

  if exists (select 1 from dbo.SYSTEMROLEAPPUSER APP where APP.APPUSERID = @APPUSERID and APP.ACCOUNTINGELEMENTSECURITYMODECODE = 2)
    -- A security mode 2 includes all elements not in the denied group, so include all elements.

    insert @T(ELEMENTID)
      select ID from
        dbo.PDACCOUNTSEGMENTVALUE
      where ID not in (select ELEMENTID from @T)
        and ID not in 
          (select AESGE.DATAELEMENTID from
            dbo.ACCOUNTINGELEMENTSECURITYGROUPELEMENT AESGE
            inner join dbo.ACCOUNTINGELEMENTSECURITYGROUP AESG on AESGE.ACCOUNTINGELEMENTSECURITYGROUPID = AESG.ID
            inner join dbo.SYSTEMROLEAPPUSERACCOUNTINGELEMENTSECURITY SAE on sae.ACCOUNTINGELEMENTSECURITYGROUPID = AESG.ID
            inner join dbo.SYSTEMROLEAPPUSER on SAE.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID
          where 
            SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID
            and SYSTEMROLEAPPUSER.ACCOUNTINGELEMENTSECURITYMODECODE = 2
          )

  -- Elements not of the same type as those in the groups

  declare @STRUCTURES table (ID uniqueidentifier)
  declare @STCOUNT int
  select @STCOUNT = COUNT(ID) from dbo.PDACCOUNTSTRUCTURE --where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID

  insert @STRUCTURES (ID)
    select distinct PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID from
      dbo.ACCOUNTINGELEMENTSECURITYGROUPELEMENT AESGE
      inner join dbo.ACCOUNTINGELEMENTSECURITYGROUP AESG on AESGE.ACCOUNTINGELEMENTSECURITYGROUPID = AESG.ID
      inner join dbo.SYSTEMROLEAPPUSERACCOUNTINGELEMENTSECURITY SAE on sae.ACCOUNTINGELEMENTSECURITYGROUPID = AESG.ID
      inner join dbo.SYSTEMROLEAPPUSER on SAE.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID
      inner join dbo.PDACCOUNTSEGMENTVALUE on AESGE.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
  if @@ROWCOUNT > 0 and @@ROWCOUNT < @STCOUNT -- If number of rows is = number of structures, no additional elements

    insert @T(ELEMENTID)
      select ID from
        dbo.PDACCOUNTSEGMENTVALUE
      where ID not in (select ELEMENTID from @T)
        and PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID not in 
          (select ID from @STRUCTURES)
    return
end