Description: A mechanism needed to automatically inform the basis team if any user has not logged for 180 days at a stretch.
Solution:
Need a program which finds out if a user has not logged for 180 days and send a mail to the basis team. This program would be scheduled in background to run on daily basis.
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
* 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
No comments:
Post a Comment