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!

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.
   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.

Happy coding!