CREATE view  usd_v_nr_of_active_applics
as
select A.objectid, A.activity, A.target
from usd_applic A
where
not
(A.status in (0,1,2)
    or(A.task in (0x00000000,0x00000010,0x00000020)
        and
        A.status = 11)
    or(A.task in (0x00000000,0x00000001,0x00000002,0x00000004,0x00000008)
        and
        A.status = 4))
and not
(A.status in (5, 6, 9, 10, 13, 14, 15, 16, 20, 21, 25, 26)
    or(A.task in (0x00000000,0x00000010,0x00000020)
        and
        A.status not in (11, 12))
    or(A.task in (0x00000000,0x00000001,0x00000002,0x00000004,0x00000008)
        and
        A.status not in (4, 7, 8, 27) ))
GO
GRANT SELECT ON  [dbo].[usd_v_nr_of_active_applics] TO [ams_group]
GRANT SELECT ON  [dbo].[usd_v_nr_of_active_applics] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[usd_v_nr_of_active_applics] TO [ca_itrm_group_ams]
GO