*********************************************************************************************************** * Program: Z_TABLE_DATA_DOWNLOAD * Restrictions: -Should be run in the background, for large tables * -If run in production, should be run after business hours, as it could cause system * performance issues * Description: Given the name of a database table, a program is generated that allows the user to enter * values for each field in the table. When the generated report is run, the contents of the * table are downloaded to a unix file. * * The user is prompt to choose the fields to appear on the selection screen of the program * (up to 40 fields) and the fields to write to the downloaded file. * * The generated program can be run in the background, via the Program menu on the selection * screen of the generated program. *********************************************************************************************************** REPORT z_table_data_download MESSAGE-ID zbc_o. *********************************************************************************************************** * VARIABLES *********************************************************************************************************** *** Data DATA: v_report_name(30) TYPE c VALUE 'ZBC_O_TABLE_DATA_DOWNLOAD_GEN', v_back_to_start(1) TYPE c, v_ok_code TYPE sy-ucomm. "ok code from the screens *** Screen CONTROLS: i_popup_table TYPE TABLEVIEW USING SCREEN 2000. *** Internal Tables TYPES: BEGIN OF t_fields, fieldname TYPE dd03l-fieldname, position TYPE dd03l-position, screen(1) TYPE c, " show on the selection screen download(1) TYPE c, " download to the file END OF t_fields. DATA: i_fields TYPE STANDARD TABLE OF t_fields, v_fields TYPE t_fields. DATA: i_source TYPE STANDARD TABLE OF char72. * CHECK FOR AUTHORIZATION INCLUDE: zbc_o_modify_sap_table_check. *********************************************************************************************************** * SELECTION SCREEN *********************************************************************************************************** PARAMETERS: p_table TYPE dd02l-tabname OBLIGATORY MEMORY ID tbl, "#EC EXISTS p_file TYPE rlgrap-filename OBLIGATORY DEFAULT '/usr/sap/OSD/local/downloads/table.txt'. *********************************************************************************************************** * START-OF-SELECTION *********************************************************************************************************** START-OF-SELECTION. CLEAR: v_back_to_start. PERFORM get_table_fields. IF i_fields IS INITIAL. MESSAGE e037. ELSE. PERFORM select_table_fields. IF v_back_to_start = 'X'. CLEAR: v_back_to_start. ELSE. PERFORM generate_selection_program. ENDIF. ENDIF. *********************************************************************************************************** * FORM GET_TABLE_FIELDS *********************************************************************************************************** * [+] Checks that the table exists, and is the correct type of table (transparent, cluster, or pool) * [+] Gets the fields of the table *********************************************************************************************************** FORM get_table_fields. TABLES: dd02l. "#EC NEEDED CLEAR: i_fields. REFRESH: i_fields. * check that the table type is legit. SELECT * FROM dd02l WHERE tabname = p_table AND ( tabclass = 'TRANSP' OR tabclass = 'CLUSTER' OR tabclass = 'POOL' ) . EXIT. ENDSELECT. IF sy-subrc <> 0. EXIT. ENDIF. * now get the fields SELECT fieldname position FROM dd03l INTO CORRESPONDING FIELDS OF TABLE i_fields WHERE tabname = p_table. SORT i_fields BY position. * Deletes any .INCLUDE and similar statements DELETE i_fields WHERE fieldname+0(1) = '.'. ENDFORM. "get_table_fields *********************************************************************************************************** * FORMM SELECT_TABLE_FIELDS *********************************************************************************************************** * [+] Asks the user to select the fields for the selection screen and the download file *********************************************************************************************************** FORM select_table_fields. CALL SCREEN 2000 STARTING AT 1 1. ENDFORM. "select_table_fields *********************************************************************************************************** * FORM GENERATE_SELECTION_PROGRAM *********************************************************************************************************** * [+] Generates the program that performs the downloading and selection from the database *********************************************************************************************************** FORM generate_selection_program. DATA: lv_line(72) TYPE c, lv_index(3) TYPE c, lv_table(31) TYPE c, lv_selscr_key(8) TYPE c, lv_more_than_one(1) TYPE c. DATA: lv_textpool TYPE textpool, li_textpool TYPE STANDARD TABLE OF textpool. CLEAR: lv_table, li_textpool. WRITE p_table TO lv_table+1. * Setting up the variables PERFORM build_selection_program USING: 'REPORT temp.'. PERFORM build_selection_program USING: 'DATA: li_select_table LIKE STANDARD TABLE OF'. CLEAR: lv_line. CONCATENATE lv_table '.' INTO lv_line. PERFORM build_selection_program USING: lv_line. PERFORM build_selection_program USING: 'DATA: lv_select_table LIKE'. CLEAR: lv_line. CONCATENATE lv_table '.' INTO lv_line. PERFORM build_selection_program USING: lv_line. PERFORM build_selection_program USING: 'DATA: lv_field_char(1000) TYPE c.'. PERFORM build_selection_program USING: 'DATA: lv_offset TYPE i.'. PERFORM build_selection_program USING: 'CONSTANTS: c_linebreak(1) TYPE x VALUE ''0D''.'. PERFORM build_selection_program USING: 'CONSTANTS: c_tab(1) TYPE x VALUE ''09''.'. CLEAR: lv_line. CONCATENATE 'TABLES: ' lv_table '.' INTO lv_line. PERFORM build_selection_program USING: lv_line. * Creating the select-options LOOP AT i_fields INTO v_fields WHERE screen = 'X'. CLEAR: lv_line, lv_index, lv_selscr_key. WRITE sy-tabix TO lv_index LEFT-JUSTIFIED. CONCATENATE 'SELECT-OPTIONS: s_fld' lv_index ' FOR ' lv_table '-' v_fields-fieldname '.' INTO lv_line. PERFORM build_selection_program USING: lv_line. CONCATENATE 's_fld' lv_index INTO lv_selscr_key. * Adds the name for the select-option into the text elements lv_textpool-id = 'S'. lv_textpool-key = lv_selscr_key. lv_textpool-entry = 'D'. "means to get the name from the dictionary TRANSLATE lv_textpool-key TO UPPER CASE. "#EC SYNTCHAR APPEND lv_textpool TO li_textpool. ENDLOOP. DATA: lv_file(130) TYPE c. CONCATENATE '''' p_file '''' INTO lv_file. * Open the file PERFORM build_selection_program USING: 'OPEN DATASET ', lv_file, ' FOR OUTPUT IN BINARY MODE.'. * insert the row headers CLEAR: lv_more_than_one. LOOP AT i_fields INTO v_fields WHERE download = 'X'. CLEAR: lv_line, lv_index. WRITE sy-tabix TO lv_index LEFT-JUSTIFIED. IF lv_more_than_one = 'X'. PERFORM build_selection_program USING: 'TRANSFER c_tab TO ', lv_file, '.'. ENDIF. CONCATENATE 'TRANSFER ''' v_fields-fieldname ''' TO ' INTO lv_line. PERFORM build_selection_program USING: lv_line, lv_file, '.'. lv_more_than_one = 'X'. ENDLOOP. PERFORM build_selection_program USING: 'TRANSFER c_linebreak TO ', lv_file, '.'. * Create the SELECT statement CLEAR: lv_line. CONCATENATE 'SELECT * FROM ' lv_table INTO lv_line. PERFORM build_selection_program USING: lv_line. READ TABLE i_fields INTO v_fields WITH KEY fieldname = 'MANDT' screen = 'X'. IF sy-subrc = 0. * mandt is specified - the user will specify the system mandt to use PERFORM build_selection_program USING: 'CLIENT SPECIFIED INTO TABLE li_select_table PACKAGE SIZE 1000 WHERE'. ELSE. * mandt is not specified - so use the system mandt PERFORM build_selection_program USING: 'INTO TABLE li_select_table PACKAGE SIZE 1000 WHERE'. ENDIF. * insert the select-options into the SELECT statement CLEAR: lv_more_than_one. LOOP AT i_fields INTO v_fields WHERE screen = 'X'. CLEAR: lv_line, lv_index. WRITE sy-tabix TO lv_index LEFT-JUSTIFIED. IF lv_more_than_one = 'X'. PERFORM build_selection_program USING: 'AND'. ENDIF. PERFORM build_selection_program USING: v_fields-fieldname. CONCATENATE 'IN s_fld' lv_index INTO lv_line. PERFORM build_selection_program USING: lv_line. lv_more_than_one = 'X'. ENDLOOP. PERFORM build_selection_program USING: '.'. "dot at the end of the SELECT * loop over the data, appending to the file. PERFORM build_selection_program USING: 'LOOP AT li_select_table INTO lv_select_table.'. * insert the select-options into the output loop CLEAR: lv_more_than_one. LOOP AT i_fields INTO v_fields WHERE download = 'X'. CLEAR: lv_line, lv_index. WRITE sy-tabix TO lv_index LEFT-JUSTIFIED. IF lv_more_than_one = 'X'. PERFORM build_selection_program USING: 'TRANSFER c_tab TO ', lv_file, '.'. ENDIF. CONCATENATE 'WRITE: lv_select_table-' v_fields-fieldname INTO lv_line. PERFORM build_selection_program USING: lv_line, 'TO lv_field_char LEFT-JUSTIFIED.'. PERFORM build_selection_program USING: 'lv_offset = STRLEN( lv_field_char ).'. PERFORM build_selection_program USING: 'TRANSFER lv_field_char TO ', lv_file, ' LENGTH lv_offset.'. lv_more_than_one = 'X'. ENDLOOP. PERFORM build_selection_program USING: 'TRANSFER c_linebreak TO ', lv_file, '.'. PERFORM build_selection_program USING: 'ENDLOOP.'. * ENDSELECT because PACKAGE SIZZE was specified, creating a loop. PERFORM build_selection_program USING: 'ENDSELECT.'. * close the file PERFORM build_selection_program USING: 'CLOSE DATASET ', lv_file, '.'. PERFORM build_selection_program USING: 'WRITE: / ''Download has completed.''.'. * Save the report DELETE REPORT v_report_name. INSERT REPORT v_report_name FROM i_source. GENERATE REPORT v_report_name. * Save the text elements INSERT textpool v_report_name FROM li_textpool LANGUAGE sy-langu. * Run the report SUBMIT (v_report_name) VIA SELECTION-SCREEN AND RETURN. ENDFORM. "generate_selection_program *********************************************************************************************************** * FORM BUILD_SELECTION_PROGRAM *********************************************************************************************************** * [+] Adds the p_code text to the source code of the generated program *********************************************************************************************************** FORM build_selection_program USING p_code TYPE c. APPEND p_code TO i_source. ENDFORM. "build_selection_program *********************************************************************************************************** * FORM SELECT_ALL_SCREEN *********************************************************************************************************** * [+] Selects all the 'screen' checkboxes on the popup *********************************************************************************************************** FORM select_all_screen. LOOP AT i_fields INTO v_fields. v_fields-screen = 'X'. MODIFY i_fields FROM v_fields. ENDLOOP. ENDFORM. "select_all_screen *********************************************************************************************************** * FORM SELECT_ALL_DOWNLOAD *********************************************************************************************************** * [+] Selects all the 'download' checkboxes on the popup *********************************************************************************************************** FORM select_all_download. LOOP AT i_fields INTO v_fields. v_fields-download = 'X'. MODIFY i_fields FROM v_fields. ENDLOOP. ENDFORM. "select_all_download *********************************************************************************************************** * FORM SELECT_NONE_SCREEN *********************************************************************************************************** * [+] Deselects all the 'screen' checkboxes on the popup *********************************************************************************************************** FORM select_none_screen. LOOP AT i_fields INTO v_fields. v_fields-screen = ' '. MODIFY i_fields FROM v_fields. ENDLOOP. ENDFORM. "select_none_screen *********************************************************************************************************** * FORM SELECT_NONE_DOWNLOAD *********************************************************************************************************** * [+] Deselects all the 'download' checkboxes on the popup *********************************************************************************************************** FORM select_none_download. LOOP AT i_fields INTO v_fields. v_fields-download = ' '. MODIFY i_fields FROM v_fields. ENDLOOP. ENDFORM. "select_none_download *********************************************************************************************************** * MODULE SET_FIELDS_2000 *********************************************************************************************************** * [+] Sets the field values on the popup *********************************************************************************************************** MODULE set_fields_2000 OUTPUT. "#EC NEEDED ENDMODULE. " set_fields_2000 OUTPUT *********************************************************************************************************** * MODULE SET_STATUS_2000 *********************************************************************************************************** * [+] Sets the status of the popup, to alllow for function codes *********************************************************************************************************** MODULE set_status_2000 OUTPUT. SET PF-STATUS 'POPUP'. ENDMODULE. " set_status_2000 OUTPUT *********************************************************************************************************** * MODULE UPDATE_CHECKBOXES_2000 *********************************************************************************************************** * [+] Saves the selected checkboxes from the popup back to the source table *********************************************************************************************************** MODULE update_checkboxes_2000 INPUT. DATA: lv_temp_fields LIKE v_fields. "#EC NEEDED IF v_ok_code = 'OK' OR v_ok_code = space. READ TABLE i_fields INTO lv_temp_fields WITH KEY fieldname = v_fields-fieldname. IF sy-subrc = 0. MODIFY i_fields FROM v_fields INDEX sy-tabix. ENDIF. ENDIF. ENDMODULE. " update_checkboxes_2000 INPUT *********************************************************************************************************** * MODULE PROCESS_BUTTONS_2000 *********************************************************************************************************** * [+] Processes the function codes from the popup *********************************************************************************************************** MODULE process_buttons_2000 INPUT. DATA: lv_num_screen TYPE i, lv_ok_code LIKE v_ok_code. CLEAR: lv_num_screen. lv_ok_code = v_ok_code. IF NOT lv_ok_code IS INITIAL. CLEAR: v_ok_code. IF lv_ok_code = 'ALL_S'. PERFORM select_all_screen. ELSEIF lv_ok_code = 'ALL_D'. PERFORM select_all_download. ELSEIF lv_ok_code = 'NONE_S'. PERFORM select_none_screen. ELSEIF lv_ok_code = 'NONE_D'. PERFORM select_none_download. ELSEIF lv_ok_code = 'CLOSE'. v_back_to_start = 'X'. LEAVE TO SCREEN 0. ELSE. * check not more than 40 SCREEN items selected LOOP AT i_fields INTO v_fields WHERE screen = 'X'. ADD 1 TO lv_num_screen. ENDLOOP. IF lv_num_screen > 40. * popup - only allowed to have < 40. MESSAGE e034. EXIT. ELSEIF lv_num_screen = 0. * popup - no screen items selected MESSAGE e036. EXIT. ENDIF. READ TABLE i_fields INTO v_fields WITH KEY download = 'X'. IF sy-subrc <> 0. * popup - no download items selected MESSAGE e035. EXIT. ENDIF. LEAVE TO SCREEN 0. ENDIF. ENDIF. ENDMODULE. " process_buttons_2000 INPUT