REPORT z_user_logon_180_days . ************************************************************************ * TABLES * ************************************************************************ TABLES:usr02, usr21, adrp, adr6. ************************************************************************ * TYPES * ************************************************************************ TYPES: BEGIN OF ty_final, bname TYPE usr02-bname, name_first TYPE adrp-name_first, name_last TYPE adrp-name_last, smtp_addr(60), trdat TYPE usr02-trdat, period TYPE i, email_found TYPE c, END OF ty_final, BEGIN OF ty_final_temp, bname(20), name_first(40), name_last(40), smtp_addr(60), trdat(15), period(10), email_found(1), END OF ty_final_temp, BEGIN OF ty_adrp, persnumber TYPE adrp-persnumber, name_first TYPE adrp-name_first, name_last TYPE adrp-name_last, END OF ty_adrp, BEGIN OF ty_adr6, addrnumber TYPE adr6-addrnumber, persnumber TYPE adr6-persnumber, smtp_addr TYPE adr6-smtp_addr, END OF ty_adr6, BEGIN OF ty_usr02, bname TYPE usr02-bname, trdat TYPE usr02-trdat, END OF ty_usr02. ************************************************************************ * INTERNAL TABLES * ************************************************************************ * internal tables to send data to users. DATA: doc_chng LIKE sodocchgi1, it_objtxt LIKE solisti1 OCCURS 10 WITH HEADER LINE, it_objpack LIKE sopcklsti1 OCCURS 2 WITH HEADER LINE, it_reclist LIKE somlreci1 OCCURS 5 WITH HEADER LINE. * internal tables to send data to basis. DATA:it_upload TYPE STANDARD TABLE OF solisti1 WITH HEADER LINE, it_objpack_basis LIKE sopcklsti1 OCCURS 2 WITH HEADER LINE, it_reclist_basis LIKE somlreci1 OCCURS 5 WITH HEADER LINE, wa_doc_chng LIKE sodocchgi1, it_objbin_basis TYPE STANDARD TABLE OF solisti1, wa_objbin_basis LIKE LINE OF it_objbin_basis, it_objtxt_basis LIKE solisti1 OCCURS 10 WITH HEADER LINE, l_cline TYPE solisti1. *internal table to store fieldnames(header) in excel sheet DATA: BEGIN OF it_fieldnames OCCURS 0, string(30), END OF it_fieldnames. DATA: it_final TYPE STANDARD TABLE OF ty_final, wa_final TYPE ty_final, it_final_temp TYPE STANDARD TABLE OF ty_final_temp, wa_final_temp TYPE ty_final_temp, it_adrp TYPE STANDARD TABLE OF ty_adrp, wa_adrp TYPE ty_adrp, it_adr6 TYPE STANDARD TABLE OF ty_adr6, wa_adr6 TYPE ty_adr6, it_usr02 TYPE STANDARD TABLE OF ty_usr02, wa_usr02 TYPE ty_usr02, it_usr21 TYPE STANDARD TABLE OF usr21, wa_usr21 TYPE usr21. ************************************************************************ * SIMPLE DATA * ************************************************************************ DATA:w_tab_line TYPE i, w_subject(255). ************************************************************************ * START-OF-SELECTION * ************************************************************************ START-OF-SELECTION. *gets user list who have not logged on for the past 180 days or more PERFORM get_user_list. *get mail ids of users to send the mail PERFORM get_mail_ids. *send mail to all the users who have not logged on for 180 days or maore PERFORM send_mail. *send mail to basis with the list of users PERFORM send_mail_to_basis. *&---------------------------------------------------------------------* *& Form get_user_list *&---------------------------------------------------------------------* * gets the list of users with their mail ids who have not logged on * for the past 180 days or more *----------------------------------------------------------------------* FORM get_user_list . DATA:l_date TYPE sy-datum. l_date = sy-datum - 180. *selects username and last logon date who have not logged on for the *past 180 days or more CLEAR it_usr02. SELECT bname trdat FROM usr02 INTO TABLE it_usr02 WHERE trdat LE l_date. CHECK NOT it_usr02[] IS INITIAL. *selects address no and person number CLEAR it_usr21. SELECT * FROM usr21 INTO TABLE it_usr21 FOR ALL ENTRIES IN it_usr02 WHERE bname EQ it_usr02-bname. *selects smtp address for the users IF NOT it_usr21[] IS INITIAL. CLEAR it_adr6. SELECT addrnumber persnumber smtp_addr FROM adr6 INTO TABLE it_adr6 FOR ALL ENTRIES IN it_usr21 WHERE addrnumber EQ it_usr21-addrnumber AND persnumber EQ it_usr21-persnumber. *selects first name and last name for the users CLEAR it_adrp. SELECT persnumber name_first name_last FROM adrp INTO TABLE it_adrp FOR ALL ENTRIES IN it_usr21 WHERE persnumber EQ it_usr21-persnumber. ENDIF. SORT it_usr02 BY bname. SORT it_usr21 BY bname. SORT it_adr6 BY addrnumber persnumber. SORT it_adrp BY persnumber. *populate final table CLEAR :it_final , wa_final , wa_usr02 , wa_usr21, wa_adr6 , wa_adrp. LOOP AT it_usr02 INTO wa_usr02. READ TABLE it_usr21 INTO wa_usr21 WITH KEY bname = wa_usr02-bname BINARY SEARCH. IF sy-subrc EQ 0. READ TABLE it_adr6 INTO wa_adr6 WITH KEY addrnumber = wa_usr21-addrnumber persnumber = wa_usr21-persnumber BINARY SEARCH. IF sy-subrc EQ 0. wa_final-smtp_addr = wa_adr6-smtp_addr. IF NOT wa_adr6-smtp_addr IS INITIAL. wa_final-email_found = 'X'. ELSE. wa_final-email_found = space. ENDIF. ENDIF. READ TABLE it_adrp INTO wa_adrp WITH KEY persnumber = wa_usr21-persnumber BINARY SEARCH. IF sy-subrc EQ 0. wa_final-name_first = wa_adrp-name_first. wa_final-name_last = wa_adrp-name_last. ENDIF. wa_final-bname = wa_usr02-bname. wa_final-trdat = wa_usr02-trdat. wa_final-period = sy-datum - wa_usr02-trdat. ENDIF. APPEND wa_final TO it_final. CLEAR : wa_final,wa_usr02,wa_usr21,wa_adr6,wa_adrp. ENDLOOP. ENDFORM. " get_user_list *&---------------------------------------------------------------------* *& Form get_mail_ids *&---------------------------------------------------------------------* * stores the mail ids of the users who have not logged on for 180 * days or more *----------------------------------------------------------------------* FORM get_mail_ids . CLEAR it_reclist. REFRESH it_reclist. LOOP AT it_final INTO wa_final. IF wa_final-email_found = 'X'. it_reclist-receiver = wa_final-smtp_addr. it_reclist-rec_type = 'U'. APPEND it_reclist. CLEAR:it_reclist,wa_final. ENDIF. ENDLOOP. ENDFORM. " get_mail_ids *&---------------------------------------------------------------------* *& Form send_mail *&---------------------------------------------------------------------* * sends mail to the users *----------------------------------------------------------------------* FORM send_mail . CLEAR :doc_chng,w_subject. CONCATENATE 'You have not logged on for 180 days for client' sy-mandt INTO w_subject SEPARATED BY space. doc_chng-obj_descr = w_subject. it_objtxt-line = w_subject. APPEND it_objtxt. CLEAR it_objtxt. it_objtxt-line = text-001. APPEND it_objtxt. CLEAR it_objtxt. it_objtxt-line = text-002. APPEND it_objtxt. CLEAR it_objtxt. it_objtxt-line = text-003. APPEND it_objtxt. CLEAR it_objtxt. DESCRIBE TABLE it_objtxt LINES w_tab_line. READ TABLE it_objtxt INDEX w_tab_line. doc_chng-doc_size = ( w_tab_line - 1 ) * 255 + STRLEN( it_objtxt ). CLEAR it_objpack-transf_bin. it_objpack-head_start = 1. it_objpack-head_num = 0. it_objpack-body_start = 1. it_objpack-body_num = w_tab_line. it_objpack-doc_type = 'RAW'. APPEND it_objpack. CLEAR it_objpack. CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1' EXPORTING document_data = doc_chng put_in_outbox = 'X' commit_work = 'X' TABLES packing_list = it_objpack contents_txt = it_objtxt receivers = it_reclist EXCEPTIONS too_many_receivers = 1 document_not_sent = 2 document_type_not_exist = 3 operation_no_authorization = 4 parameter_error = 5 x_error = 6 enqueue_error = 7 OTHERS = 8. IF sy-subrc EQ 0. SUBMIT rsconn01 WITH mode = 'INT' AND RETURN. ENDIF. ENDFORM. " send_mail *&---------------------------------------------------------------------* *& Form send_mail_to_basis *&---------------------------------------------------------------------* * downloads the list of users and sends mail to ercitbasis@ge.com * * with the list of users *----------------------------------------------------------------------* FORM send_mail_to_basis . *download all the users to Q drive PERFORM download_list. *send mail to basis. PERFORM send_list_to_basis. ENDFORM. " send_mail_to_basis *&---------------------------------------------------------------------* *& Form download_list *&---------------------------------------------------------------------* * downloads the list of users to excel sheet *----------------------------------------------------------------------* FORM download_list . DATA:l_filename TYPE rlgrap-filename , l_filetype TYPE rlgrap-filetype, l_file(100). CLEAR it_fieldnames. REFRESH it_fieldnames. it_fieldnames-string = 'SSO ID'. APPEND it_fieldnames.CLEAR it_fieldnames. it_fieldnames-string = 'First Name'. APPEND it_fieldnames.CLEAR it_fieldnames. it_fieldnames-string = 'Last Name'. APPEND it_fieldnames.CLEAR it_fieldnames. it_fieldnames-string = 'SMTP Address'. APPEND it_fieldnames.CLEAR it_fieldnames. it_fieldnames-string = 'Date of last Logon'. APPEND it_fieldnames.CLEAR it_fieldnames. it_fieldnames-string = 'Days not logged on'. APPEND it_fieldnames.CLEAR it_fieldnames. it_fieldnames-string = 'Email Sent'. APPEND it_fieldnames.CLEAR it_fieldnames. CLEAR it_final_temp. REFRESH it_final_temp. LOOP AT it_final INTO wa_final. wa_final_temp-bname = wa_final-bname. wa_final_temp-name_first = wa_final-name_first. wa_final_temp-name_last = wa_final-name_last. wa_final_temp-smtp_addr = wa_final-smtp_addr. wa_final_temp-trdat = wa_final-trdat. wa_final_temp-period = wa_final-period. wa_final_temp-email_found = wa_final-email_found. APPEND wa_final_temp TO it_final_temp. CLEAR :wa_final_temp,wa_final. ENDLOOP. CONCATENATE 'C:\' sy-repid '_' sy-datum '.XLS' INTO l_file. l_filename = l_file. *using WS_DOWNLOAD instead of GUI_DOWNLOAD as the columnnames(header) *are not appearing in Excel sheet while using GUI_DOWNLOAD CALL FUNCTION 'WS_DOWNLOAD' EXPORTING filename = l_filename filetype = 'DAT' TABLES data_tab = it_final_temp fieldnames = it_fieldnames EXCEPTIONS file_open_error = 1 file_write_error = 2 invalid_filesize = 3 invalid_type = 4 no_batch = 5 unknown_error = 6 invalid_table_width = 7 gui_refuse_filetransfer = 8 customer_error = 9 no_authority = 10 OTHERS = 11. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. ENDFORM. " download_list *&---------------------------------------------------------------------* *& Form send_list_to_basis *&---------------------------------------------------------------------* * sends mail to ercitbasis.ge.com *----------------------------------------------------------------------* FORM send_list_to_basis . DATA: w_path LIKE rlgrap OCCURS 0 WITH HEADER LINE, lt_index TYPE sy-tabix, doc_type(3) TYPE c, descr LIKE it_objpack_basis-obj_descr, temp_data LIKE w_path, temp1 TYPE string, tab_lines TYPE i, langu(15) TYPE c, expirydate TYPE so_obj_edt, L_FILE1(100). CONCATENATE 'C:\' sy-repid '_' sy-datum '.XLS' INTO L_FILE1. W_PATH-FILENAME = L_FILE1. APPEND w_path. CLEAR w_path. wa_doc_chng-obj_descr = 'User List not logged on for 180 days'. wa_doc_chng-obj_langu = 'E'. wa_doc_chng-obj_expdat = sy-datum. CLEAR w_subject. CONCATENATE 'Please find attached document with list of users' 'not logged on for 180 days for client' sy-mandt INTO w_subject SEPARATED BY space. it_objtxt_basis-line = w_subject. APPEND it_objtxt_basis. CLEAR it_objtxt_basis. it_objtxt_basis-line = text-004. APPEND it_objtxt_basis. CLEAR it_objtxt_basis. CLEAR w_tab_line. DESCRIBE TABLE it_objtxt_basis LINES w_tab_line. READ TABLE it_objtxt_basis INDEX w_tab_line INTO l_cline. wa_doc_chng-doc_size = ( w_tab_line - 1 ) * 255 + STRLEN( l_cline ). CLEAR it_objpack_basis-transf_bin. it_objpack_basis-head_start = 1. it_objpack_basis-head_num = 0. it_objpack_basis-body_start = 1. it_objpack_basis-body_num = w_tab_line. it_objpack_basis-doc_type = 'RAW'. APPEND it_objpack_basis. CLEAR it_objpack_basis. LOOP AT w_path. temp1 = w_path. descr = w_path. CALL FUNCTION 'STRING_REVERSE' EXPORTING string = descr lang = 'E' IMPORTING rstring = descr. CALL FUNCTION 'STRING_SPLIT' EXPORTING delimiter = '\' string = descr IMPORTING head = descr tail = temp_data. CALL FUNCTION 'STRING_REVERSE' EXPORTING string = descr lang = 'E' IMPORTING rstring = descr. CALL FUNCTION 'STRING_SPLIT' EXPORTING delimiter = '.' string = descr IMPORTING head = temp_data tail = doc_type. CALL FUNCTION 'GUI_UPLOAD' EXPORTING filename = temp1 filetype = 'BIN' header_length = 0 read_by_line = 'X' replacement = '#' TABLES data_tab = it_upload. DESCRIBE TABLE it_upload LINES tab_lines. DESCRIBE TABLE it_objbin_basis LINES lt_index. lt_index = lt_index + 1. LOOP AT it_upload. wa_objbin_basis-line = it_upload-line. APPEND wa_objbin_basis TO it_objbin_basis. CLEAR wa_objbin_basis. ENDLOOP. it_objpack_basis-transf_bin = 'X'. it_objpack_basis-head_start = 0. it_objpack_basis-head_num = 0. it_objpack_basis-body_start = lt_index. it_objpack_basis-body_num = tab_lines. it_objpack_basis-doc_type = doc_type. it_objpack_basis-obj_descr = descr. it_objpack_basis-doc_size = tab_lines * 255. APPEND it_objpack_basis. CLEAR it_objpack_basis. ENDLOOP. it_reclist_basis-receiver = ‘xyz@xyz.com’. it_reclist_basis-rec_type = 'U'. APPEND it_reclist_basis. CLEAR it_reclist_basis. CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1' EXPORTING document_data = wa_doc_chng put_in_outbox = 'X' commit_work = 'X' TABLES packing_list = it_objpack_basis contents_txt = it_objtxt_basis contents_bin = it_objbin_basis receivers = it_reclist_basis EXCEPTIONS too_many_receivers = 1 document_not_sent = 2 operation_no_authorization = 4 OTHERS = 99. IF sy-subrc EQ 0. SUBMIT rsconn01 WITH mode = 'INT' AND RETURN. ENDIF. ENDFORM. " send_list_to_basis
