1 query found for "User Group Security Type"

Retrieve all User Groups Information including Application Services [Can be used in CCB, MDM, ODM]

alter session set current_schema=CISADM;
SELECT
   TRIM(T1.USR_GRP_ID),
   TRIM(T1.USG_GRP_DESCR),
   TRIM(T2.APP_SVC_ID),
   TRIM(T3.APP_SVC_DESCR),
   T3.APP_SVC_EXP_DT,
   TRIM(T2.ACCESS_MODE),
   TRIM(T2.ACC_MODE_DESCR),
   T2.OWNER_FLG,
   T3.SC_TYPE_CD,
   TRIM(T3.SC_TYPE_DESCR),
   T3.AUTH_LEVEL_NBR,
   TRIM(T3.AUTH_LVL_DESCR)
FROM
   (
      SELECT
         USG.USR_GRP_ID,
         USGL.DESCR AS USG_GRP_DESCR 
      FROM
         CISADM.SC_USER_GROUP USG,
         CISADM.SC_USER_GROUP_L USGL 
      WHERE
         USG.USR_GRP_ID = USGL.USR_GRP_ID 
   )
   T1,
   (
      SELECT
         B.USR_GRP_ID,
         B.APP_SVC_ID,
         B.ACCESS_MODE,
         A.DESCR AS ACC_MODE_DESCR,
         B.OWNER_FLG 
      FROM
         CISADM.CI_LOOKUP_VAL_L A,
         CISADM.SC_ACCESS_CNTL B 
      WHERE
         B.ACCESS_MODE = A.FIELD_VALUE 
         AND A.FIELD_NAME = 'ACCESS_MODE' 
      ORDER BY
         B.USR_GRP_ID,
         B.APP_SVC_ID 
   )
   T2,
   (
      SELECT
         J1.USR_GRP_ID,
         J1.APP_SVC_ID,
         J1.APP_SVC_DESCR,
         J1.APP_SVC_EXP_DT,
         J2.SC_TYPE_CD,
         J2.SC_TYPE_DESCR,
         J2.AUTH_LEVEL_NBR,
         J2.AUTH_LVL_DESCR 
      FROM
         (
            SELECT
               USGP.USR_GRP_ID,
               USGP.APP_SVC_ID,
               ASL.DESCR AS APP_SVC_DESCR,
               USGP.EXPIRATION_DT AS APP_SVC_EXP_DT 
            FROM
               CISADM.SC_USR_GRP_PROF USGP,
               CISADM.SC_APP_SERVICE_L ASL 
            WHERE
               USGP.APP_SVC_ID = ASL.APP_SVC_ID 
         )
         J1 
         LEFT JOIN
            (
               SELECT
                  UGSC.USR_GRP_ID,
                  UGSC.APP_SVC_ID,
                  SCL.SC_TYPE_CD,
                  SCL.DESCR AS SC_TYPE_DESCR,
                  AL.AUTH_LEVEL_NBR,
                  AL.DESCR AS AUTH_LVL_DESCR 
               FROM
                  CISADM.CI_USR_GRP_SC UGSC,
                  CISADM.CI_SC_TYPE_L SCL,
                  CISADM.CI_SC_AUTH_LVL_L AL 
               WHERE
                  UGSC.SC_TYPE_CD = SCL.SC_TYPE_CD 
                  AND UGSC.SC_TYPE_CD = AL.SC_TYPE_CD 
                  AND SCL.SC_TYPE_CD = AL.SC_TYPE_CD 
                  AND UGSC.AUTH_LEVEL_NBR = AL.AUTH_LEVEL_NBR 
            )
            J2 
            ON J1.USR_GRP_ID = J2.USR_GRP_ID 
            AND J1.APP_SVC_ID = J2.APP_SVC_ID 
   )
   T3
WHERE
   T1.USR_GRP_ID = T2.USR_GRP_ID 
   AND T1.USR_GRP_ID = T3.USR_GRP_ID
   AND T2.USR_GRP_ID = T3.USR_GRP_ID
   AND T2.APP_SVC_ID = T3.APP_SVC_ID 
   AND T1.USR_GRP_ID IN 
   (
      'ALL_SERVICES'
   )
ORDER BY
   T1.USR_GRP_ID,
   T2.APP_SVC_ID,
   T2.ACCESS_MODE,
   T3.SC_TYPE_CD,
   T3.AUTH_LEVEL_NBR;

uploaded by Jessie Dean U. Tomas