Source – ZS_FIND_UNASSIGNED_ROLES

*&---------------------------------------------------------------------*
*& Report ZDBE_FIND_UNASSIGNED_ROLES
*&---------------------------------------------------------------------*
*& Purpose: Find roles not assigned to any user for X days
*& Author : Daniel Berlin
*& Version: 1.0.3
*& License: CC BY 3.0 (https://creativecommons.org/licenses/by/3.0/)
*&---------------------------------------------------------------------*
 
REPORT zdbe_find_unassigned_roles.
 
TYPE-POOLS: icon, slis.
 
TABLES: agr_define, cdhdr, cdpos.                           "#EC NEEDED
 
DATA: gv_date  LIKE sy-datum,
      gv_never TYPE i,
      BEGIN OF gt_result OCCURS 0,
         agr_name   LIKE agr_define-agr_name,   " Role name
         create_dat LIKE agr_define-create_dat, " Creation date
         change_dat LIKE agr_define-change_dat, " Change   date
         udate      LIKE cdhdr-udate,           " Removal date
         username   LIKE cdhdr-username,        " Removed by
         text       LIKE agr_texts-text,        " Role description
         type       TYPE icon-id,               " Role type
         status(6)  TYPE c,                     " Role status
         used       TYPE icon-id,               " Role usage
         alvbox     TYPE c,                     " ALV checkbox status
      END OF gt_result.
 
SELECTION-SCREEN: BEGIN OF LINE,
                  COMMENT 1(30) n_roles.
SELECT-OPTIONS: pr_roles FOR agr_define-agr_name.
SELECTION-SCREEN: END OF LINE,
                  BEGIN OF LINE,
                  COMMENT 1(30) n_users.
SELECT-OPTIONS: pr_users FOR agr_define-create_usr.
SELECTION-SCREEN: END OF LINE,
                  SKIP,
                  BEGIN OF LINE,
                  COMMENT 1(33) n_days.
PARAMETERS: pv_days TYPE i OBLIGATORY.
SELECTION-SCREEN: END OF LINE.
 
INITIALIZATION.
 
  AUTHORITY-CHECK OBJECT 'S_USER_AGR'
           ID 'ACTVT'     FIELD '03'
           ID 'ACT_GROUP' FIELD '*'.
 
  IF sy-subrc <> 0.
    MESSAGE 'Missing authorisation.' TYPE 'E'.              "#EC NOTEXT
    LEAVE PROGRAM.
  ENDIF.
 
  AUTHORITY-CHECK OBJECT 'S_USER_GRP'
           ID 'ACTVT' FIELD '03'
           ID 'CLASS' FIELD '*'.
 
  IF sy-subrc <> 0.
    MESSAGE 'Missing authorisation.' TYPE 'E'.              "#EC NOTEXT
    LEAVE PROGRAM.
  ENDIF.
 
  " -- Texts
  n_roles = 'Role name'.                                    "#EC NOTEXT
  n_users = 'Role created by'.                              "#EC NOTEXT
  n_days  = 'Days since last user assignment'.              "#EC NOTEXT
 
  " -- Default values
  pr_roles-sign   = 'E'.
  pr_roles-option = 'CP'.
  pr_roles-low    = 'SAP_*'. APPEND pr_roles.
  pr_roles-low    = '/SAP*'. APPEND pr_roles.
 
  pr_users-sign   = 'E'.
  pr_users-option = 'EQ'.
  pr_users-low    = 'SAP'.
  APPEND pr_users.
 
  pv_days = 180.
 
START-OF-SELECTION.
 
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
    EXPORTING
      text = 'Searching...'.                                "#EC NOTEXT
 
  gv_date = sy-datum - pv_days.
 
  " -- Fetch all roles matching the selection
  SELECT * FROM agr_define WHERE agr_name   IN pr_roles
                             AND create_usr IN pr_users.
 
    " -- Fetch the number of assignments to users for matching roles
    SELECT COUNT(*) FROM agr_users WHERE agr_name = agr_define-agr_name.
 
    " -- Skip roles that are currently assigned
    IF sy-dbcnt <> 0.
      CONTINUE.
    ENDIF.
 
    " -- Fetch number of change documents for role-to-user assignments
    SELECT COUNT(*) FROM cdpos
                   WHERE objectclas = 'PFCG'
                     AND objectid   = agr_define-agr_name
                     AND tabname    = 'AGR_USERS'
                     AND chngind    = 'I'.
 
    gt_result-agr_name   = agr_define-agr_name.
    gt_result-create_dat = agr_define-create_dat.
    gt_result-change_dat = agr_define-change_dat.
 
    " -- Role has never been assigned to a user
    IF sy-dbcnt = 0.
      gt_result-udate = 0.
      gv_never = gv_never + 1.
    ELSE.
      " -- Fetch change documents for the current role
      SELECT * FROM cdhdr
              WHERE objectclas = 'PFCG'
                AND objectid   = agr_define-agr_name
                AND udate     <= gv_date
           ORDER BY changenr     DESCENDING.
 
        " -- Check, if the current change was a deletion of an assignment
        SELECT SINGLE * FROM cdpos
                WHERE objectclas = 'PFCG'
                  AND objectid     = agr_define-agr_name
                  AND changenr     = cdhdr-changenr
                  AND tabname      = 'AGR_USERS'
                  AND chngind      = 'D'.
 
        " -- At least one deletion was found
        IF sy-dbcnt = 1.
          gt_result-udate    = cdhdr-udate.
          gt_result-username = cdhdr-username.
          EXIT.
        ENDIF.
 
      ENDSELECT.
    ENDIF.
 
    " -- A matching role was found
    IF NOT gt_result-agr_name IS INITIAL.
      " -- Fetch role description
      PERFORM role_get_description USING agr_define-agr_name
                                CHANGING gt_result-text.
 
      " -- Check, if it is a single or composite role
      PERFORM role_is_composite USING agr_define-agr_name.
 
      IF sy-subrc = 0.
        gt_result-type = icon_composite_activitygroup.
      ELSE.
        gt_result-type = icon_activity_group.
 
        " -- Check, if the role's profile exists and is generated
        CALL FUNCTION 'PRGN_CHECK_PROFILE_STATUS'
          EXPORTING
            activity_group = agr_define-agr_name
          IMPORTING
            led_color      = gt_result-status.
 
        CASE gt_result-status.
          WHEN 'GREEN'.
            gt_result-status = icon_led_green.
          WHEN 'YELLOW'.
            gt_result-status = icon_led_yellow.
          WHEN OTHERS.
            gt_result-status = icon_led_red.
        ENDCASE.
 
        " -- Check, if the single role is used in composite roles
        PERFORM role_is_used_in_composite USING agr_define-agr_name.
 
        IF sy-subrc = 0.
          gt_result-used = icon_presence.
        ELSE.
          gt_result-used = icon_absence.
        ENDIF.
      ENDIF.
 
      APPEND gt_result.
      CLEAR  gt_result.
    ENDIF.
 
  ENDSELECT.
 
  PERFORM alv_show_result.
 
*&---------------------------------------------------------------------*
*&      Form  role_get_description
*&---------------------------------------------------------------------*
FORM role_get_description USING role LIKE agr_define-agr_name
                       CHANGING text LIKE agr_texts-text.
 
  " -- Get role description in current language
  SELECT SINGLE text FROM agr_texts INTO text
          WHERE agr_name = role
            AND spras    = sy-langu.
 
  " -- Fallback: get description in any other language
  IF sy-dbcnt = 0.
    SELECT SINGLE text FROM agr_texts INTO text
            WHERE agr_name = role.
  ENDIF.
 
ENDFORM.                    "role_get_description
 
*&---------------------------------------------------------------------*
*&      Form  role_is_composite
*&---------------------------------------------------------------------*
FORM role_is_composite USING role LIKE agr_define-agr_name.
 
  " -- Determine, if a role is a composite role
  SELECT SINGLE COUNT(*) FROM agr_flags
          WHERE agr_name   = role
            AND flag_type  = 'COLL_AGR'
            AND flag_value = 'X'.
 
  CHECK sy-dbcnt > 0.
 
ENDFORM.                    "role_is_composite
 
*&---------------------------------------------------------------------*
*&      Form  role_is_used_in_composite
*&---------------------------------------------------------------------*
FORM role_is_used_in_composite USING role LIKE agr_define-agr_name.
 
  " -- Determine if the single role is used in composite roles
  SELECT SINGLE COUNT(*) FROM agr_agrs
          WHERE child_agr = role.
 
  CHECK sy-dbcnt > 0.
 
ENDFORM.                    "role_is_used_in_composite
 
*&---------------------------------------------------------------------*
*&      Form  alv_show_result
*&---------------------------------------------------------------------*
FORM alv_show_result.
 
  DATA: lt_fieldcat TYPE slis_t_fieldcat_alv WITH HEADER LINE,
        ls_layout   TYPE slis_layout_alv.
 
  " -- Field catalogue
  lt_fieldcat-fieldname   = 'AGR_NAME'.
  lt_fieldcat-ref_tabname = 'AGR_DEFINE'.
  lt_fieldcat-key         = 'X'.
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname   = 'CREATE_DAT'.
  lt_fieldcat-ref_tabname = 'AGR_DEFINE'.
  lt_fieldcat-seltext_s   = 'Creation'.                     "#EC NOTEXT
  lt_fieldcat-seltext_m   = 'Creation date'.                "#EC NOTEXT
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname   = 'CHANGE_DAT'.
  lt_fieldcat-ref_tabname = 'AGR_DEFINE'.
  lt_fieldcat-seltext_s   = 'Change'.                       "#EC NOTEXT
  lt_fieldcat-seltext_m   = 'Change date'.                  "#EC NOTEXT
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname   = 'UDATE'.
  lt_fieldcat-ref_tabname = 'CDHDR'.
  lt_fieldcat-seltext_s   = 'Removal'.                      "#EC NOTEXT
  lt_fieldcat-seltext_m   = 'Removal date'.                 "#EC NOTEXT
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname   = 'USERNAME'.
  lt_fieldcat-ref_tabname = 'CDHDR'.                        "#EC NOTEXT
  lt_fieldcat-seltext_m   = 'Removed by'.                   "#EC NOTEXT
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname   = 'TEXT'.
  lt_fieldcat-ref_tabname = 'AGR_TEXTS'.
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname = 'TYPE'.
  lt_fieldcat-seltext_s = 'Type'.                           "#EC NOTEXT
  lt_fieldcat-seltext_m = 'Role type'.                      "#EC NOTEXT
  lt_fieldcat-icon      = 'X'.
  lt_fieldcat-outputlen = 8.
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname = 'STATUS'.
  lt_fieldcat-seltext_s = 'Status'.                         "#EC NOTEXT
  lt_fieldcat-seltext_m = 'Profile status'.                 "#EC NOTEXT
  lt_fieldcat-icon      = 'X'.
  lt_fieldcat-outputlen = 8.
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname = 'USED'.
  lt_fieldcat-seltext_s = 'SR in CR'.                       "#EC NOTEXT
  lt_fieldcat-seltext_s = 'SR used in CR'.                  "#EC NOTEXT
  lt_fieldcat-seltext_l = 'Single used in composite role'.  "#EC NOTEXT
  lt_fieldcat-icon      = 'X'.
  lt_fieldcat-outputlen = 8.
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  " --- Layout
  ls_layout-box_fieldname     = 'ALVBOX'.
* ls_layout-colwidth_optimize = 'X'.
  ls_layout-no_input          = 'X'.
  ls_layout-zebra             = 'X'.
 
  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
      i_callback_program      = sy-repid
      i_callback_user_command = 'ALV_CALLBACK_USER_COMMAND'
      i_callback_top_of_page  = 'ALV_CALLBACK_TOP_OF_PAGE'
      is_layout               = ls_layout
      it_fieldcat             = lt_fieldcat[]
      i_save                  = 'X'
    TABLES
      t_outtab                = gt_result
    EXCEPTIONS
      OTHERS                  = 1.
 
ENDFORM.                    "alv_show_result
 
*&---------------------------------------------------------------------*
*&      Form  alv_callback_top_of_page
*&---------------------------------------------------------------------*
FORM alv_callback_top_of_page.                              "#EC CALLED
 
  DATA: ls_header    TYPE slis_listheader,
        lt_header    TYPE slis_t_listheader,
        li_found     TYPE i,
        lc_found(10) TYPE c,
        lc_days(10)  TYPE c,
        lc_never(10) TYPE c.
 
  ls_header-typ = 'S'.
  DESCRIBE TABLE gt_result LINES li_found.
  lc_found = li_found.
 
  lc_days = pv_days.
  lc_never = gv_never.
 
  CONCATENATE 'Found ' lc_found 'roles not assigned'
              'for >=' lc_days 'days.'
         INTO ls_header-info SEPARATED BY space.            "#EC NOTEXT
  APPEND ls_header TO lt_header.
 
  CONCATENATE lc_never 'of them were never assigned (empty "Removal Date").'
         INTO ls_header-info SEPARATED BY space.            "#EC NOTEXT
  APPEND ls_header TO lt_header.
 
  CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE'
    EXPORTING
      it_list_commentary = lt_header.
 
ENDFORM.                    "alv_callback_top_of_page
 
*&---------------------------------------------------------------------*
*&      Form  alv_callback_user_command
*&---------------------------------------------------------------------*
FORM alv_callback_user_command                              "#EC CALLED
                         USING pv_ucomm    LIKE sy-ucomm
                               ps_selfield TYPE slis_selfield.
 
  READ TABLE gt_result INDEX ps_selfield-tabindex.
 
  IF pv_ucomm = '&IC1'.
    CASE ps_selfield-fieldname.
      WHEN 'AGR_NAME'.                            " Click on Role Name
        CALL FUNCTION 'PRGN_SHOW_EDIT_AGR'
          EXPORTING
            agr_name = gt_result-agr_name
            mode     = 'A'
            screen   = space
            sicht    = '1'
          EXCEPTIONS
            OTHERS   = 1.
 
        IF sy-subrc <> 0.
          MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
                  WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
        ENDIF.
 
      WHEN 'UDATE'.                               " Click on Date
        SUBMIT rsscd100_pfcg VIA SELECTION-SCREEN
               WITH fdate = gv_date
               WITH role  = gt_result-agr_name
               WITH users = 'X'
               AND RETURN.
 
      WHEN 'USERNAME'.                            " Click on User
        IF NOT gt_result-username IS INITIAL.
          CALL FUNCTION 'SUSR_USER_MAINT_WITH_DIALOG'
            EXPORTING
              user_to_display = gt_result-username
            EXCEPTIONS
              OTHERS          = 1.
        ENDIF.
 
      WHEN 'TYPE'.                                " Click on Type
        IF gt_result-type = icon_composite_activitygroup.
          CALL FUNCTION 'PRGN_SHOW_EDIT_AGR'
            EXPORTING
              agr_name = gt_result-agr_name
              mode     = 'A'
              screen   = '8'
              sicht    = '1'
            EXCEPTIONS
              OTHERS   = 1.
 
          IF sy-subrc <> 0.
            MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
                    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
          ENDIF.
        ENDIF.
 
      WHEN 'STATUS'.                              " Click on Status
        IF NOT gt_result-status IS INITIAL.
          CALL FUNCTION 'PRGN_SHOW_EDIT_AGR'
            EXPORTING
              agr_name = gt_result-agr_name
              mode     = 'A'
              screen   = '5'
              sicht    = '1'
            EXCEPTIONS
              OTHERS   = 1.
 
          IF sy-subrc <> 0.
            MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
                    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
          ENDIF.
        ENDIF.
 
      WHEN 'USED'.                                " Click on Used
        CALL FUNCTION 'PRGN_START3_CROSSREFERENCE'
          EXPORTING
            role = gt_result-agr_name.
 
    ENDCASE.
  ENDIF.
 
ENDFORM.                    "alv_callback_user_command

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.