*&---------------------------------------------------------------------* *& 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 |