Please feel free to comment on a better / different way to do this. I am working with an “older” system. But it makes everything a bit more of a challenge. And I do love a challenge!
Requirement:
Upload data from a spreadsheet into sales order conditions and a custom table. The sales order conditions that needed updating were ones that are manually entered. Shipping cost depends on a lot of factors that can’t be automated. The actual price is normally determined automatically. However at times that price can be manually changed based upon a lot of different business factors. Actual net weight is determined when an order is shipped – as there can be changes.
Data is validated based on the information loaded via cut and paste. Some of the validation includes the load, the customer number, and if the data is numeric. If there is an issue the line will be highlighted in red. There will be a message displayed on the issue. They will have to copy that line again or fix the issue in the SAP system.
There is an Excel template used. It’s one that has been used by the business users for years to manually change things. However on the select screen there is a button to download it.
When the entire spreadsheet has an issue – it’s usually because it isn’t in the right format. They will have to back up to the previous screen, and copy the spreadsheet again, then execute. When there is a problem with a line – they have to fix the issue in system or in the Excel line. Then they have to start from the execute screen. (A future enhancement could allow them to change the entire line. Great idea from Veselina)
The solution:
Use copy / paste from a clipboard into a program. Create an ALV The ALV will allow for some of the fields to be changed.
So here is the selection screen:
Before they execute they must have the excel copied in the Clipboard:
Here’s the result
The program validates the data and only allows updates to the valid data – Load can be updated. Because I had trouble just filling in the load. I allowed a double click and a box to change it.
Then save from the ALV will save the information.
It’s a simple application. The the end user loves because of the ease of use.
So here is some of the code:
Get the clipboard information:
CALL METHOD cl_gui_frontend_services=>clipboard_import( IMPORTING data = gt_clipdata length = gv_clip_len EXCEPTIONS cntl_error = 1 error_no_gui = 2 OTHERS = 4 ). IF sy-subrc NE 0. MESSAGE i000(z3) WITH 'Error while importing data from clipboard'. EXIT. ENDIF.Update table: (Please read the comments append initial line could be changed)
LOOP AT gt_clipdata INTO gs_clipdata. zlog-cnt = zlog-cnt + 1. CLEAR zlog-data. zlog-data = gs_clipdata. zlog-leng = 500. INSERT zcs1_log FROM zlog. SPLIT gs_clipdata AT gc_hex_tab INTO TABLE gt_record. APPEND INITIAL LINE TO gt_data. READ TABLE gt_data ASSIGNING <fs_data> INDEX sy-tabix. LOOP AT gt_record INTO gs_record. ASSIGN COMPONENT sy-tabix OF STRUCTURE <fs_data> TO <fs_field>. IF sy-subrc EQ 0. <fs_field> = gs_record-zdata. ENDIF. ENDLOOP. ENDLOOP. COMMIT WORK.Of course, some more processing validate the data, put into an output table. Finally display the ALV:
IF g_custom_container IS INITIAL. CREATE OBJECT g_custom_container EXPORTING container_name = g_container. CREATE OBJECT g_grid EXPORTING i_parent = g_custom_container. CREATE OBJECT event_rec. SET HANDLER event_rec->handle_double_click FOR g_grid. SET HANDLER event_rec->handle_top_of_page FOR g_grid. gs_layout-info_fname = 'ROWCOLOR'. gs_layout-sel_mode = 'A'. gs_variant-report = sy-repid. gs_variant-username = sy-uname. PERFORM build_fieldcat. CALL METHOD g_grid->set_table_for_first_display EXPORTING i_structure_name = 'ZSTR_TRUCK_UPLOAD' is_layout = gs_layout is_print = gs_print is_variant = gs_variant i_save = 'A' CHANGING it_outtab = gt_out it_fieldcatalog = gt_field_cat. * Register the EDIT Event CALL METHOD g_grid->register_edit_event EXPORTING i_event_id = cl_gui_alv_grid=>mc_evt_modified EXCEPTIONS error = 1 OTHERS = 2. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. * Sets up a handler for any data changed to detail CREATE OBJECT g_event_receiver. SET HANDLER g_event_receiver->handle_data_changed FOR g_grid. ENDIF.And then the update
METHOD handle_data_changed. DATA: ls_good TYPE lvc_s_modi, ls_diff TYPE lvc_s_modi. LOOP AT er_data_changed->mt_mod_cells INTO ls_good. CASE ls_good-fieldname. WHEN 'TOTAL' OR 'LOAD'. CALL METHOD update_change EXPORTING ps_total = ls_good pr_data_change = er_data_changed. ENDCASE. ENDLOOP. ENDMETHOD. *---------------------------------------------------------------------* * Updates the ALV difference field in the master data *---------------------------------------------------------------------* METHOD update_change. DATA: lv_vbeln(10), lv_posnr(10). * DATA: lv_total TYPE zeco_master-total. READ TABLE gt_out INTO gs_out INDEX ps_total-row_id. IF p_disp <> 'X'. CALL METHOD pr_data_change->modify_cell EXPORTING i_row_id = ps_total-row_id i_fieldname = 'PRICE' i_value = gs_out-price. CALL METHOD pr_data_change->modify_cell EXPORTING i_row_id = ps_total-row_id i_fieldname = 'BILL_CUST' i_value = gs_out-bill_cust. ELSE. CALL METHOD pr_data_change->modify_cell EXPORTING i_row_id = ps_total-row_id i_fieldname = 'LOAD' i_value = ps_total-value. SPLIT ps_total-value AT '-' INTO lv_vbeln lv_posnr. PERFORM alpha_conver USING lv_vbeln gs_out-vbeln. PERFORM alpha_conver USING lv_posnr gs_out-posnr. CALL METHOD pr_data_change->modify_cell EXPORTING i_row_id = ps_total-row_id i_fieldname = 'VBELN' i_value = gs_out-vbeln. CALL METHOD pr_data_change->modify_cell EXPORTING i_row_id = ps_total-row_id i_fieldname = 'POSNR' i_value = gs_out-posnr. ENDIF. ENDMETHOYes, there is a lot more code. Perhaps too much. But there you have it – most of it. my quick and easy way to create an ALV with update capabilities and pull the data from the clipboard. And yes, you can find – probably all of this floating around somewhere. However, here is the complete project.
Happy coding!
R/3 Take a step back into time – ALV Interactive Cut / Paste