R/3 Take a step back into time – ALV Interactive Cut / Paste
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!
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)
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. ENDMETHO
Yes, 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.
Would you please add a bit more on the business background for this requirement?
I think that I understood most of the technical solution, but not why it was needed - usually prices in sales orders are determined based on specific criteria and users rarely need to modify more than 1-2 conditions, even this is done in exceptional cases.
Another thing that I do not fully understand is how you handle the following challenge - the settings in Excel for date and number representation can be different from the format in SAP. Does this solution require the usage of a specific template where you force formatting, or you determine formatting from the system locale or the control for correctness is done in the ALV after pasting?
You mention that the user can modify only valid data, but I am not entirely sure what valid data means exactly - if I see that the pasted information is wrong due to formatting - do I delete the entry and re-upload or I can correct it directly in the grid with a double-click?
I tried to update the blog with the information needed. Thank you!
Sales prices - for us there are a lot of manually entered conditions. Freight is a big one that changes depending on customer, millage, and freight company used.
Now the manual table side of things - it contains the data that will be used when a purchase order is created. It also contains the conditions used.
Yes - there is a template they use. In fact I have a download button on the screen for the template in case they need it. So far they haven't. It's their template that they have used for years.
User modify valid data. There are validation checks on the data. Including customer, load and data format. When there is an issue the line is highlighted in red and they can't update it.
In the actual spread sheet, I believe 5 conditions are moved to the system. These conditions are manually controlled. There just isn't a way to automate them.
Thank you for the great questions!
Thank you Michelle, now it is more clear how you control formatting - to me this is one of the biggest annoyances with Excel, especially when the IT support is not fully centralized.
This business requirement (freight charges) is quite challenging without substantial investment in third-party software and custom development - I assume that users need to populate miles billed based on actual distance minus the agreed FOC mileage. If FOC mileage is set on per case basis I am afraid that you are right and there is no way to eliminate completely manual entry. Usually in such cases I beg the customer to consider streamlining the process if possible and reduce the risk of mistakes.
It is a bizarre case - normally I expect complex freight calculation to originate from a shipment cost document (even if it requires additional development) and be transferred to final billing, I've never seen it done in the way you described.
Thank you for sharing, I learned something different today. 🙂
Yes - it's a very different case. It makes things a bit more interesting. We transport things like grain mixes. It can be by train, plane, automobile or a combination. And then all the many different business factors makes it interesting. Yes they enter miles. This was just something we couldn't automate. As soon as we tried a different business rule would be added / removed.
In your code, I saw
If your SAP system allows it, then it can be replaced with
What is your SAP system version ?
Thank you just what I'm looking for. I'm not on my system now - but I'll have to try it.
My system is 4.6C. 🙂
abap2xlsx - if you haven't checked it out, worth taking a look at.
Love it! And I used it on 6.0. It can't go back this far. But great addition! That way any and all of us can think about it in the future.
Thank you for the reminder,