Skip to Content

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!

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Veselina Peykova

    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?

    (1) 
    1. Michelle Crapo Post author

      Hi Veselina,

      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!

      Michelle

       

      (1) 
      1. Veselina Peykova

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

         

        (1) 
        1. Michelle Crapo Post author

          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.

          (1) 
  2. Chaouki AKIR

    In your code, I saw

         APPEND INITIAL LINE TO gt_data.
         READ TABLE gt_data ASSIGNING <fs_data> INDEX sy-tabix.

     

    If your SAP system allows it, then it can be replaced with

    APPEND INITIAL LINE TO gt_data ASSIGNING <fs_data>.

     

    What is your SAP system version ?

     

    (2) 
    1. Michelle Crapo Post author

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

      Michelle

      (0) 
    1. Michelle Crapo Post author

      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,

      Michelle

      (0) 

Leave a Reply