USP_DATALIST_SYSTEMROLEWORKFLOWFEATURES

CREATE procedure dbo.USP_DATALIST_SYSTEMROLEWORKFLOWFEATURES
(
    @SYSTEMROLEID uniqueidentifier
)
as
    -- data forms

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    select DFT.ID,
        case DFT.MODE
            when 0 then 19
            when 1 then 3
            when 2 then 0
        end as ITEMTYPE,
        DFT.TEMPLATENAME
    from dbo.DATAFORMINSTANCECATALOG as DFI 
        inner join dbo.V_INSTALLED_DATAFORMTEMPLATECATALOG as DFT on DFI.DATAFORMTEMPLATECATALOGID = DFT.ID
        inner join dbo.SYSTEMROLEPERM_DATAFORMINSTANCE as PERM on DFI.ID = PERM.DATAFORMINSTANCECATALOGID
    where 
        (PERM.SYSTEMROLEID = @SYSTEMROLEID)
        and (PERM.GRANTORDENY = 1)

    -- feature group data forms

    union
    select DFT.ID,
        case DFT.MODE
            when 0 then 19
            when 1 then 3
            when 2 then 0
        end,
        DFT.TEMPLATENAME
    from dbo.DATAFORMINSTANCECATALOG AS DFI
        inner join dbo.V_INSTALLED_DATAFORMTEMPLATECATALOG as DFT on DFI.DATAFORMTEMPLATECATALOGID = DFT.ID
        inner join dbo.FEATUREGROUPFEATURE as FGF on DFI.ID = FGF.FEATUREID and FGF.FEATURETYPECODE = 4
        inner join dbo.SYSTEMROLEPERM_FEATUREGROUP as PERM on FGF.FEATUREGROUPCATALOGID = PERM.FEATUREGROUPCATALOGID
    where
        (PERM.SYSTEMROLEID = @SYSTEMROLEID)
        and (PERM.GRANTORDENY = 1)

    -- record operations

    union all
    select R.ID,
        23, 
        R.DISPLAYNAME
    from dbo.V_INSTALLED_RECORDOPERATIONCATALOG as R
        inner join dbo.SYSTEMROLEPERM_RECORDOPERATION as PERM on R.ID = PERM.RECORDOPERATIONCATALOGID
    where 
        (PERM.SYSTEMROLEID = @SYSTEMROLEID)
        and (PERM.GRANTORDENY = 1)

    -- feature group record operations

    union
    select R.ID,
        23,
        R.DISPLAYNAME
    from dbo.V_INSTALLED_RECORDOPERATIONCATALOG as R
        inner join dbo.FEATUREGROUPFEATURE as FGF on R.ID = FGF.FEATUREID and FGF.FEATURETYPECODE = 9
        inner join dbo.SYSTEMROLEPERM_FEATUREGROUP as PERM on FGF.FEATUREGROUPCATALOGID = PERM.FEATUREGROUPCATALOGID
    where
        (PERM.SYSTEMROLEID = @SYSTEMROLEID)
        and (PERM.GRANTORDENY = 1)

    -- data lists

    union all
    select D.ID,
        2, 
        D.NAME
    from dbo.V_INSTALLED_DATALISTCATALOG as D
        inner join dbo.SYSTEMROLEPERM_DATALIST as PERM on D.ID = PERM.DATALISTCATALOGID
    where 
        (PERM.SYSTEMROLEID = @SYSTEMROLEID)
        and (PERM.GRANTORDENY = 1)

    -- feature group data lists

    union
    select D.ID,
        2, 
        D.NAME
    from dbo.V_INSTALLED_DATALISTCATALOG as D
        inner join dbo.FEATUREGROUPFEATURE as FGF on D.ID = FGF.FEATUREID and FGF.FEATURETYPECODE = 7
        inner join dbo.SYSTEMROLEPERM_FEATUREGROUP as PERM on FGF.FEATUREGROUPCATALOGID = PERM.FEATUREGROUPCATALOGID
    where
        (PERM.SYSTEMROLEID = @SYSTEMROLEID)
        and (PERM.GRANTORDENY = 1)

    --search lists

    union all  
    select S.ID,  
        10,   
        S.NAME  
    from dbo.V_INSTALLED_SEARCHLISTCATALOG as S
        inner join dbo.SYSTEMROLEPERM_SEARCHLIST as PERM on S.ID = PERM.SEARCHLISTCATALOGID  
    where   
        (PERM.SYSTEMROLEID = @SYSTEMROLEID)  
        and (PERM.GRANTORDENY = 1)  

    -- feature group search lists

    union
    select S.ID,  
        10,   
        S.NAME  
    from dbo.V_INSTALLED_SEARCHLISTCATALOG as S
        inner join dbo.FEATUREGROUPFEATURE as FGF on S.ID = FGF.FEATUREID and FGF.FEATURETYPECODE = 11
        inner join dbo.SYSTEMROLEPERM_FEATUREGROUP as PERM on FGF.FEATUREGROUPCATALOGID = PERM.FEATUREGROUPCATALOGID
    where   
        (PERM.SYSTEMROLEID = @SYSTEMROLEID)  
        and (PERM.GRANTORDENY = 1)  

    -- order the features returned by name

    order by 3