select
'CommandProcessor' as PKey,
'READ' as permission from
(
   select
   x.USER_RESOURCE_ACCESS
   FROM (CALL chorus_resource.resource_permission('CHORUS.ROLE.DB2DBA',1)) as x ,
   chorus_metadata.chorus_role r
   where r.ROLE_NAME='CHORUS.ROLE.DB2DBA'
   and r.INSTALL_STATUS=true
   and r.LICENSE_STATUS=true
)
as y
where y.USER_RESOURCE_ACCESS='Y'
union
SELECT
'GettingStarted' as PKey,'READ' as permission
union
SELECT
'WebApp' as PKey,'READ' as permission
union
SELECT
'TextBox' as PKey,'READ' as permission
union
SELECT
'QuickLinks' as PKey,'READ' as permission
union
SELECT
distinct 'Investigator' as PKey, 'READ' as permission
from
(
   SELECT
   distinct treedef.SCHEMA_NAME
   FROM chorus_metadata.tree_definition treedef
   join chorus_metadata_schema.schema sc on sc.SCHEMA_NAME=treedef.SCHEMA_NAME
   and treedef.UI_AREA='Investigator'
   and sc.SCHEMA_RESOURCE_NAME in
   (
      select
      SCHEMA_RESOURCE_NAME
      from
      (
         select
         distinct s.SCHEMA_RESOURCE_NAME,
         (
            select
            x.USER_RESOURCE_ACCESS
            FROM
            (
               CALL chorus_resource.resource_permission(s.SCHEMA_RESOURCE_NAME,1)
            )
            as x
         )
         as access_mode
         from chorus_metadata_schema.schema s
      )
      as scr
      where access_mode='Y'
      and SCHEMA_RESOURCE_NAME != 'CHORUS.ROLE.NOLICENSE'
      and SCHEMA_RESOURCE_NAME != 'CHORUS.ROLE.DEMODATA'
   )
)
as z
union
SELECT
distinct 'Metrics' as PKey, 'READ' as permission
from
(
   SELECT
   distinct treedef.SCHEMA_NAME
   FROM chorus_metadata.tree_definition treedef
   join chorus_metadata_schema.schema sc on sc.SCHEMA_NAME=treedef.SCHEMA_NAME
   and treedef.UI_AREA='Metric Panel'
   and sc.SCHEMA_RESOURCE_NAME in
   (
      select
      SCHEMA_RESOURCE_NAME
      from
      (
         select
         distinct s.SCHEMA_RESOURCE_NAME,
         (
            select
            x.USER_RESOURCE_ACCESS
            FROM
            (
               CALL chorus_resource.resource_permission(s.SCHEMA_RESOURCE_NAME,1)
            )
            as x
         )
         as access_mode
         from chorus_metadata_schema.schema s
      )
      as scr
      where access_mode='Y'
      and SCHEMA_RESOURCE_NAME != 'CHORUS.ROLE.NOLICENSE'
      and SCHEMA_RESOURCE_NAME != 'CHORUS.ROLE.DEMODATA'
   )
)
as z
union
SELECT
distinct 'trafficLight' as PKey,
'READ' as permission from
(
   select
   USER_RESOURCE_ACCESS from
   (
      (
         select
         x.USER_RESOURCE_ACCESS
         FROM
         (
            CALL chorus_resource.resource_permission('CHORUS.ROLE.DB2DBA',1)
         )
         as x
      )
      UNION
      (
         select
         x.USER_RESOURCE_ACCESS
         FROM
         (
            CALL chorus_resource.resource_permission
            (
               'CHORUS.ROLE.INFRASTRUCTURE',1
            )
         )
         as x
      )
      UNION
      (
         select
         x.USER_RESOURCE_ACCESS
         FROM
         (
            CALL chorus_resource.resource_permission('CHORUS.ROLE.SECURITY',1)
         )
         as x
      )
      UNION
      (
         select
         x.USER_RESOURCE_ACCESS
         FROM
         (
            CALL chorus_resource.resource_permission('CHORUS.ROLE.STORAGE',1)
         )
         as x
      )
   )
   as acc
   where acc.USER_RESOURCE_ACCESS='Y'
)
as z
union
SELECT
distinct 'Alerts' as PKey,
'READ' as permission from
(
   select
   USER_RESOURCE_ACCESS from
   (
      (
         select
         x.USER_RESOURCE_ACCESS
         FROM
         (
            CALL chorus_resource.resource_permission('CHORUS.ROLE.DB2DBA',1)
         )
         as x
      )
      UNION
      (
         select
         x.USER_RESOURCE_ACCESS
         FROM
         (
            CALL chorus_resource.resource_permission
            (
               'CHORUS.ROLE.INFRASTRUCTURE',1
            )
         )
         as x
      )
      UNION
      (
         select
         x.USER_RESOURCE_ACCESS
         FROM
         (
            CALL chorus_resource.resource_permission('CHORUS.ROLE.SECURITY',1)
         )
         as x
      )
      UNION
      (
         select
         x.USER_RESOURCE_ACCESS
         FROM
         (
            CALL chorus_resource.resource_permission('CHORUS.ROLE.STORAGE',1)
         )
         as x
      )
   )
   as acc
   where acc.USER_RESOURCE_ACCESS='Y'
)
as z
union
select
'SecurityCommandManager' as PKey,
'READ' as permission from
(
   select
   x.USER_RESOURCE_ACCESS
   FROM (CALL chorus_resource.resource_permission('CHORUS.ROLE.SECURITY',1)) as x ,
   chorus_metadata.chorus_role r
   where r.ROLE_NAME='CHORUS.ROLE.SECURITY'
   and r.INSTALL_STATUS=true
   and r.LICENSE_STATUS=true
)
as y
where y.USER_RESOURCE_ACCESS='Y'