Skip to Content
Technical Articles
Author's profile photo Oğuzhan Subaşı

Mass Routing Upload program

Introduction

LSMW and LTMC are weak on complexity of routing. Component allocation, PRT assignment, etc. is hard to manage with these standart tools.

Therefore i developped a flexible program that you can upload a whole excel with multiple sheets at once.

 

Details

In the selection screen below, you can map your excel details.

Routing%20Upload%20Program%20selection%20screen

Routing Upload Program selection screen

 

Your excel need to have Technical names in a specific row in all the sheets. And the data should start in the same row in all the sheets.

Routing%20Upload%20program%20excel%20example

Routing Upload program excel example

You can find the complete excel in the link: Upload Format

 

 

Additional information:

  1. You can have extra sheet in your excel. If it is not mapped in selection screen, system will not consider that sheet.
  2. You can have extra fields. If the field is name is not in BAPI structure, system will not consider it.
  3. You can send the program to the Background task not to wait the Bapi run. Then you can check you task with tcode SM58.
  4. You can split the bapi runs, so if any task gets error, system can contionue with the rest of the rountings.

 

 

 

Step-by-step program creation

 

Creation of the program needs only 2 ABAP steps:

 

  1. Create the program ZPP_000_ROUTING_UPLOAD in tcode SE38, paste the code below. No screen creation or text edit needed.
*&---------------------------------------------------------------------*
*& Report ZPP_ROUTING_UPLOAD
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zpp_000_routing_upload.

**********************************************************************
**********************************************************************
****    Created by Oğuzhan Subaşı at 13.07.2015                  *****
****     You can copy and distribute as you wish                 *****
****     Just keep this section please                           *****
**********************************************************************
**********************************************************************

TABLES:sscrfields, mapl.

TYPES: BEGIN OF lty_s_itab, "alsmex_tabline but with bigger "row"
         row   TYPE  numc10,
         col   TYPE  kcd_ex_col_n,
         value TYPE  char50,
       END OF lty_s_itab,

       lty_t_itab TYPE TABLE OF lty_s_itab.


DATA: task                   TYPE TABLE OF   bapi1012_tsk_c,
      materialtaskallocation TYPE TABLE OF   bapi1012_mtk_c,
      sequence               TYPE TABLE OF   bapi1012_seq_c,
      operation              TYPE TABLE OF   bapi1012_opr_c,
      suboperation           TYPE TABLE OF   bapi1012_sub_opr_c,
      referenceoperation     TYPE TABLE OF   bapi1012_ref_opr_c,
      workcenterreference    TYPE TABLE OF   bapi1012_wc_ref_opr_c,
      componentallocation    TYPE TABLE OF   bapi1012_com_c,
      productionresource     TYPE TABLE OF   bapi1012_prt_c,
      inspcharacteristic     TYPE TABLE OF   bapi1012_cha_c,
      textallocation         TYPE TABLE OF   bapi1012_txt_hdr_c,
      text                   TYPE TABLE OF   bapi1012_txt_c,
      testrun                TYPE bapiflag.


**********************************************************************
**ALSM_EXCEL_TO_INTERNAL_TABLE****
****LALSMEXTOP*******

*FUNCTION-POOL alsmex.                  "MESSAGE-ID ..

TYPE-POOLS: ole2.

*      value of excel-cell
TYPES: ty_d_itabvalue TYPE alsmex_tabline-value,
*      internal table containing the excel data
       ty_t_itab      TYPE alsmex_tabline   OCCURS 0,

*      line type of sender table
       BEGIN OF ty_s_senderline,
         line(4096) TYPE c,
       END OF ty_s_senderline,
*      sender table
       ty_t_sender TYPE ty_s_senderline  OCCURS 0.

*
CONSTANTS:  gc_esc              VALUE '"'.

INCLUDE lalsmexf01. "For ALSM_EXCEL_TO_INTERNAL_TABLE
**********************************************************************
*--------------------------------------------------------------------*
**********************************************************************



**********************************************************************
******SELECTION SCREEN************************************************
**********************************************************************
SELECTION-SCREEN BEGIN OF BLOCK bl1 WITH FRAME TITLE  tit1.
  PARAMETERS: p_file TYPE localfile OBLIGATORY .

  SELECT-OPTIONS: r_plnnr  FOR mapl-plnnr.
SELECTION-SCREEN  END OF BLOCK bl1.

SELECTION-SCREEN BEGIN OF BLOCK bl2 WITH FRAME TITLE  tit2.
  PARAMETERS: sht_task TYPE i LENGTH 2 OBLIGATORY DEFAULT 1,
              sht_allc TYPE i LENGTH 2 DEFAULT 2,
              sht_seqn TYPE i LENGTH 2 DEFAULT 3,
              sht_oprt TYPE i LENGTH 2 DEFAULT 4,
              sht_subo TYPE i LENGTH 2 DEFAULT 5,
              sht_comp TYPE i LENGTH 2 DEFAULT 6,
              sht_insp TYPE i LENGTH 2 DEFAULT 7,
              sht_prt  TYPE i LENGTH 2 DEFAULT 8.
SELECTION-SCREEN  END OF BLOCK bl2.

SELECTION-SCREEN BEGIN OF BLOCK bl3 WITH FRAME TITLE  tit3.
  PARAMETERS: p_hdrrow TYPE n LENGTH 2 OBLIGATORY DEFAULT 3, "Header Row
              p_dstrow TYPE n LENGTH 2 OBLIGATORY DEFAULT 4. "First Data Row
SELECTION-SCREEN  END OF BLOCK bl3.


SELECTION-SCREEN BEGIN OF BLOCK bl4 WITH FRAME TITLE  tit4.
  PARAMETERS: p_updtsk AS CHECKBOX ,          "Run in Update task
              p_bapitq TYPE numc10 DEFAULT 1. "RUN bapi with that much TASK quantity each time
SELECTION-SCREEN  END OF BLOCK bl4.



AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
    EXPORTING
      static    = 'X'
    CHANGING
      file_name = p_file.


INITIALIZATION.
  PERFORM change_texts.


AT SELECTION-SCREEN.
  DATA(lv_ucomm) = sy-ucomm.
  CLEAR sy-ucomm.

  CASE lv_ucomm.
    WHEN 'ONLI'.
      DATA: lv_answer.
      CALL FUNCTION 'POPUP_TO_CONFIRM'
        EXPORTING
          titlebar      = 'Excel Upload Start'
          text_question = 'Do Not use your computer until the Excel read finishes'
        IMPORTING
          answer        = lv_answer.
      CHECK lv_answer EQ '1'.

      PERFORM zalsm_excel .

      IF p_updtsk IS INITIAL.
        CALL FUNCTION 'POPUP_TO_CONFIRM'
          EXPORTING
            titlebar      = 'Excel Upload Finish'
            text_question = 'Excel read completed. Now on, you can use your computer'
          IMPORTING
            answer        = lv_answer.
        CHECK lv_answer EQ '1'.
      ENDIF.

      PERFORM bapi_routing_create.
  ENDCASE.

END-OF-SELECTION.



**********************************************************************
********FORMS*********************************************************
**********************************************************************
*&---------------------------------------------------------------------*
*&      Form  FILL_BAPI_TABLES
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM fill_bapi_tables  TABLES t_imp TYPE lty_t_itab  "kcde_cells
                       USING p_table TYPE dd03m-tabname.


  DATA: lv_error      TYPE string,
        lv_table_type TYPE fupararef-structure,
        lv_hdrrow     TYPE lty_s_itab-row,
        lv_dstrow     TYPE lty_s_itab-row,


        fields        TYPE string OCCURS 0 WITH HEADER LINE,
        lt_dd03m      TYPE dd03m OCCURS 0 WITH HEADER LINE.


  FIELD-SYMBOLS: <structure> TYPE any,
                 <table>     TYPE STANDARD TABLE,
                 <field>     TYPE any.

  CHECK t_imp[] IS NOT INITIAL .

  ASSIGN (p_table) TO <table>.
  CLEAR <table>.

  DATA: ls_struct TYPE REF TO data.
  CREATE DATA: ls_struct LIKE LINE OF <table> .
  ASSIGN ls_struct->* TO <structure>.

  SELECT  structure FROM fupararef INTO lv_table_type UP TO 1 ROWS
          WHERE funcname = 'BAPI_ROUTING_CREATE'
            AND parameter = p_table
            AND paramtype = 'T' ORDER BY PRIMARY KEY.
    EXIT.
  ENDSELECT.

  SELECT * FROM dd03m
     INTO TABLE lt_dd03m
          WHERE tabname     = lv_table_type.
  SORT lt_dd03m BY fieldname.
  DELETE ADJACENT DUPLICATES FROM lt_dd03m COMPARING fieldname .


  SORT t_imp BY row col .

  lv_hdrrow = p_hdrrow .

  "Sütun adlarını al
  READ TABLE t_imp WITH KEY row = lv_hdrrow BINARY SEARCH.
  CHECK sy-subrc IS INITIAL.
  LOOP AT t_imp INTO DATA(ls_imp) FROM sy-tabix.
    IF ls_imp-row NE lv_hdrrow. EXIT. ENDIF.
    fields = ls_imp-value.
    APPEND fields.
  ENDLOOP.


  "İlk Satır Başlık Satırı
  lv_dstrow = p_dstrow .
  READ TABLE t_imp WITH KEY row = lv_dstrow BINARY SEARCH.
  CHECK sy-subrc IS INITIAL.
  LOOP AT t_imp INTO ls_imp FROM sy-tabix.
    AT NEW row.
      IF <structure> IS NOT INITIAL.
        APPEND <structure> TO <table>.
        CLEAR <structure>.
      ENDIF.
    ENDAT.

    READ TABLE fields INDEX ls_imp-col .
    CHECK sy-subrc IS INITIAL.
    CHECK fields IS NOT INITIAL. "Boş sütunlar.
    CHECK ls_imp-value IS NOT INITIAL. "Boş Alanlar

    READ TABLE lt_dd03m WITH KEY fieldname = fields BINARY SEARCH.
    CHECK sy-subrc IS INITIAL.
*      CONCATENATE p_table 'tablosunun' fields ' Sütunu BAPI ye aktarılamıyor'
*       INTO lv_error.
*      MESSAGE lv_error TYPE 'E'.
*    ENDIF.


    ASSIGN COMPONENT fields OF STRUCTURE <structure> TO <field>.
    CHECK sy-subrc IS INITIAL.

    PERFORM check_and_correct USING lt_dd03m ls_imp-value CHANGING <field>.
  ENDLOOP.


  IF <structure> IS NOT INITIAL.
    APPEND <structure> TO <table>. "Son satırı da ekle
  ENDIF.

ENDFORM.                    " FILL_BAPI_TABLES
*&---------------------------------------------------------------------*
*&      Form  CHECK_AND_CORRECT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_LS_DD03M  text
*      <--P_LS_IMP_VALUE  text
*----------------------------------------------------------------------*
FORM check_and_correct  USING    s_dd03m TYPE dd03m
                                 p_input
                        CHANGING p_output .

  CHECK p_input IS NOT INITIAL.

  IF s_dd03m-convexit IS NOT INITIAL.
    DATA(lv_func_conv) = |CONVERSION_EXIT_{ s_dd03m-convexit }_INPUT|.

    CALL FUNCTION lv_func_conv
      EXPORTING
        input  = p_input
      IMPORTING
        output = p_output.

  ELSE.
    CASE s_dd03m-datatype.
      WHEN 'QUAN' OR 'DEC'.
        REPLACE ',' WITH '.' INTO p_input.

    ENDCASE.

    p_output = p_input.
  ENDIF.

ENDFORM.                    " CHECK_AND_CORRECT
*&---------------------------------------------------------------------*
*&      Form  BAPI_ROUTING_CREATE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM bapi_routing_create .

  DATA: lt_return                 TYPE TABLE OF   bapiret2 WITH HEADER LINE.

  DELETE task WHERE task_list_group NOT IN r_plnnr.

  IF p_updtsk IS INITIAL.
    CALL FUNCTION 'ZPP_BAPI_ROUTING_CREATE'
      EXPORTING
        iv_bapitq = p_bapitq
      TABLES
        tsk       = task
        mtk       = materialtaskallocation
        seq       = sequence
        opr       = operation
        sbo       = suboperation
        rfo       = referenceoperation
        wcr       = workcenterreference
        com       = componentallocation
        prt       = productionresource
        cha       = inspcharacteristic
        txt       = textallocation
        et_return = lt_return.

  ELSE.
    CALL FUNCTION 'ZPP_BAPI_ROUTING_CREATE' IN BACKGROUND TASK AS SEPARATE UNIT
      EXPORTING
        iv_bapitq = p_bapitq
      TABLES
        tsk       = task
        mtk       = materialtaskallocation
        seq       = sequence
        opr       = operation
        sbo       = suboperation
        rfo       = referenceoperation
        wcr       = workcenterreference
        com       = componentallocation
        prt       = productionresource
        cha       = inspcharacteristic
        txt       = textallocation
*       et_return = lt_return
      .

    CALL FUNCTION 'BAPI_TRANSACTION_COMMIT' EXPORTING wait = 'X'.
    MESSAGE 'Process sent to Update Task' TYPE 'I'.
  ENDIF.

  IF lt_return[] IS NOT INITIAL .
    cl_rmsl_message=>display( lt_return[] ).
  ENDIF.


ENDFORM.                    " BAPI_ROUTING_CREATE
*&---------------------------------------------------------------------*
*&      Form  ZALSM_EXCEL_TO_INTERNAL_TABLE
*&---------------------------------------------------------------------*
*       * ALSM_EXCEL_TO_INTERNAL_TABLE code enhanced for multiple sheets
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM zalsm_excel.

  DATA: i_beg_col TYPE i VALUE 1,
        i_end_col TYPE i VALUE 999,
        i_beg_row TYPE i,
        i_end_row TYPE i,
        sheet_no  TYPE i,

        intern    TYPE lty_t_itab.


**********************************************************************
**********************************************************************
*--> "13.07.15 Oğuzhan Subaşı
  "ALSM_EXCEL_TO_INTERNAL_TABLE code enhanced for multiple sheets
*<-- "13.07.15 Oğuzhan Subaşı

  DATA: excel_tab       TYPE  ty_t_sender,
        excel_tab_sheet TYPE  ty_t_sender,
        ld_separator    TYPE  c,
        application     TYPE  ole2_object,
        workbook        TYPE  ole2_object,
        range           TYPE  ole2_object,
        worksheet       TYPE  ole2_object,
        h_cell          TYPE  ole2_object,
        h_cell1         TYPE  ole2_object,
        ld_rc           TYPE i,
        lv_tabname      TYPE dd03m-tabname.

* Makro für Fehlerbehandlung der Methods
  DEFINE m_message.
    CASE sy-subrc.
      WHEN 0.
      WHEN 1.
        MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
      WHEN OTHERS. RAISE upload_ole.
    ENDCASE.
  END-OF-DEFINITION.


* Get TAB-sign for separation of fields
  CLASS cl_abap_char_utilities DEFINITION LOAD.
  ld_separator = cl_abap_char_utilities=>horizontal_tab.

* open file in Excel
  IF application-header = space OR application-handle = -1.
    CREATE OBJECT application 'Excel.Application'.
    m_message.
  ENDIF.
  CALL METHOD  OF application    'Workbooks' = workbook.
  m_message.
  CALL METHOD  OF workbook 'Open'    EXPORTING #1 = p_file.
  m_message.
*  set property of application 'Visible' = 1.
*  m_message.

**********************************************************************
  DO 8 TIMES.
    DATA(lv_index) = sy-index.

    CASE lv_index .
      WHEN 1. sheet_no = sht_task . lv_tabname = 'TASK'.
      WHEN 2. sheet_no = sht_allc . lv_tabname = 'MATERIALTASKALLOCATION'.
      WHEN 3. sheet_no = sht_seqn . lv_tabname = 'SEQUENCE'.
      WHEN 4. sheet_no = sht_oprt . lv_tabname = 'OPERATION'.
      WHEN 5. sheet_no = sht_subo . lv_tabname = 'SUBOPERATION'.
      WHEN 6. sheet_no = sht_comp . lv_tabname = 'COMPONENTALLOCATION'.
      WHEN 7. sheet_no = sht_insp . lv_tabname = 'INSPCHARACTERISTIC'.
      WHEN 8. sheet_no = sht_prt  . lv_tabname = 'PRODUCTIONRESOURCE'.
    ENDCASE.

    CHECK sheet_no GT 0.

    CLEAR: intern[], excel_tab_sheet[].

*ACTIVATE THE SELECTED SHEET
    CALL METHOD OF application 'Worksheets' = worksheet EXPORTING #1 = sheet_no.
    m_message.
    CALL METHOD OF worksheet 'Activate'.
    m_message.

    GET PROPERTY OF  application 'ACTIVESHEET' = worksheet.
    m_message.


    DO. "Read the data in packages of 65.000 rows
      DATA(lv_end_row) = 65000 * sy-index .
      DATA(lv_beg_row) = 65000 * ( sy-index - 1 ) + 1 .

      cl_progress_indicator=>progress_indicate(
                i_text = |Reading: Sheet { sheet_no }, Lines { lv_beg_row }->{ lv_end_row }|
                i_output_immediately = abap_true ).


* mark whole spread sheet
      CALL METHOD OF worksheet 'Cells' = h_cell  EXPORTING #1 = lv_beg_row #2 = i_beg_col.
      m_message.
      CALL METHOD OF worksheet 'Cells' = h_cell1 EXPORTING #1 = lv_end_row #2 = i_end_col.
      m_message.
      CALL METHOD  OF worksheet 'RANGE' = range  EXPORTING #1 = h_cell #2 = h_cell1.
      m_message.
      CALL METHOD OF range 'SELECT'.
      m_message.

* copy marked area (whole spread sheet) into Clippboard
      CALL METHOD OF range 'COPY'.
      m_message.

      DO 5 TIMES.
        CLEAR excel_tab.
        cl_gui_frontend_services=>clipboard_import( IMPORTING data = excel_tab ). "read clipboard

        IF excel_tab IS NOT INITIAL.
          EXIT .
        ENDIF.
        WAIT UP TO 1 SECONDS.
      ENDDO.


      IF excel_tab[] IS INITIAL.
*       quit Excel and free ABAP Object - unfortunately, this does not kill
*       the Excel process
        CALL METHOD OF application 'QUIT'.
        m_message.

*       >>>>> Begin of change note 575877
*       to kill the Excel process it's necessary to free all used objects
        FREE OBJECT h_cell.       m_message.
        FREE OBJECT h_cell1.      m_message.
        FREE OBJECT range.        m_message.
        FREE OBJECT worksheet.    m_message.
        FREE OBJECT workbook.     m_message.
        FREE OBJECT application.  m_message.

        MESSAGE |Issue on reading from clipboard for { sheet_no }. sheet, try again.|
          TYPE 'E'.
      ENDIF.

      DELETE excel_tab WHERE line CO |{ ld_separator } |. "# (seperator) and Space

      IF excel_tab IS INITIAL.  "Sheet completed
        EXIT .
      ENDIF.

      APPEND LINES OF excel_tab TO excel_tab_sheet.
    ENDDO.

    cl_progress_indicator=>progress_indicate(
       i_text = |Converting Sheet { sheet_no } to { lv_tabname } table. ({ lines( excel_tab_sheet ) } rows)|
       i_output_immediately = abap_true ).


    PERFORM z_separated_to_intern_convert TABLES excel_tab_sheet intern[] USING ld_separator.
    PERFORM fill_bapi_tables TABLES intern[] USING lv_tabname .

  ENDDO.
**********************************************************************

  "Clear Clipboard
  DO 10 TIMES.
    CLEAR excel_tab.
    cl_gui_frontend_services=>clipboard_export( IMPORTING data = excel_tab CHANGING rc = ld_rc ) .

    CLEAR excel_tab.
    cl_gui_frontend_services=>clipboard_import( IMPORTING data = excel_tab ). "read clipboard

    IF excel_tab IS INITIAL.
      EXIT .
    ENDIF.
    WAIT UP TO 1 SECONDS.
  ENDDO.

* quit Excel and free ABAP Object - unfortunately, this does not kill
* the Excel process
  CALL METHOD OF application 'QUIT'.
  m_message.

* >>>>> Begin of change note 575877
* to kill the Excel process it's necessary to free all used objects
  FREE OBJECT h_cell.       m_message.
  FREE OBJECT h_cell1.      m_message.
  FREE OBJECT range.        m_message.
  FREE OBJECT worksheet.    m_message.
  FREE OBJECT workbook.     m_message.
  FREE OBJECT application.  m_message.
* <<<<< End of change note 575877
**********************************************************************
**********************************************************************

ENDFORM.                    " ZALSM_EXCEL
**********************************************************************
*--------------------------------------------------------------------*
**********************************************************************
*&---------------------------------------------------------------------*
*&      Form  CHANGE_TEXTS
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM change_texts .
  tit1 = 'Path of the Excel file' .
  tit2 = 'Excel Sheet Mapping. Enter the related sheet number of the excel'.
  tit3 = 'Excel Format Defination. Excel row numbers of the Technical Names and Data Fields'.
  tit4 = 'Other Parameters'.

  %_p_file_%_app_%-text = 'File Name'.

  %_sht_task_%_app_%-text = 'Task List Header'.
  %_sht_allc_%_app_%-text = 'Material Task List Assignment'.
  %_sht_seqn_%_app_%-text = 'Task List Sequences'.
  %_sht_oprt_%_app_%-text = 'Operations'.
  %_sht_subo_%_app_%-text = 'Sub operations'.
  %_sht_comp_%_app_%-text = 'Component Allocation'.
  %_sht_insp_%_app_%-text = 'Inspection Characteristics'.
  %_sht_prt_%_app_%-text  = 'Production Resource Tools'.

  %_p_hdrrow_%_app_%-text = 'Row for BAPI field names'.
  %_p_dstrow_%_app_%-text = 'First Data Row'.
  %_p_updtsk_%_app_%-text = 'Run BAPI in update task'.
  %_p_bapitq_%_app_%-text = 'Task Qty for each bapi Run'.
  %_r_plnnr_%_app_%-text = 'Task filter'.
ENDFORM.                    " CHANGE_TEXTS

FORM z_separated_to_intern_convert TABLES i_tab       TYPE ty_t_sender
                                        i_intern    TYPE lty_t_itab
                                 USING  i_separator TYPE c.
  DATA: l_sic_tabix LIKE sy-tabix,
        l_sic_col   TYPE kcd_ex_col.
  DATA: l_fdpos     LIKE sy-fdpos.

*  REFRESH i_intern.

  LOOP AT i_tab.
    l_sic_tabix = sy-tabix.
    l_sic_col = 0.
    WHILE i_tab CA i_separator.
      l_fdpos = sy-fdpos.
      l_sic_col = l_sic_col + 1.
      PERFORM z_line_to_cell_separat TABLES i_intern
                                   USING  i_tab l_sic_tabix l_sic_col
                                          i_separator l_fdpos.
    ENDWHILE.
    IF i_tab <> space.
      CLEAR i_intern.
      i_intern-row = l_sic_tabix.
      i_intern-col = l_sic_col + 1.
      i_intern-value = i_tab.
      APPEND i_intern.
    ENDIF.
  ENDLOOP.
ENDFORM.                    " SEPARATED_TO_INTERN_CONVERT
FORM z_line_to_cell_separat TABLES i_intern    TYPE lty_t_itab
                          USING  i_line
                                 i_row       LIKE sy-tabix
                                 ch_cell_col TYPE kcd_ex_col
                                 i_separator TYPE c
                                 i_fdpos     LIKE sy-fdpos.
  DATA: l_string   TYPE ty_s_senderline.
  DATA  l_sic_int  TYPE i.

  CLEAR i_intern.
  l_sic_int = i_fdpos.
  i_intern-row = i_row.
  l_string = i_line.
  i_intern-col = ch_cell_col.
* csv Dateien mit separator in Zelle: --> ;"abc;cd";
  IF ( i_separator = ';' OR  i_separator = ',' ) AND
       l_string(1) = gc_esc.
    PERFORM line_to_cell_esc_sep USING l_string
                                       l_sic_int
                                       i_separator
                                       i_intern-value.
  ELSE.
    IF l_sic_int > 0.
      i_intern-value = i_line(l_sic_int).
    ENDIF.
  ENDIF.
  IF l_sic_int > 0.
    APPEND i_intern.
  ENDIF.
  l_sic_int = l_sic_int + 1.
  i_line = i_line+l_sic_int.
ENDFORM.

 

 

 

2. Create the Function Module ZPP_000_BAPI_ROUTING_CREATE in tcode SE37.

FUNCTION ZPP_BAPI_ROUTING_CREATE .
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     REFERENCE(IV_BAPITQ) TYPE  NUMC10
*"  TABLES
*"      TSK STRUCTURE  BAPI1012_TSK_C
*"      MTK STRUCTURE  BAPI1012_MTK_C
*"      SEQ STRUCTURE  BAPI1012_SEQ_C
*"      OPR STRUCTURE  BAPI1012_OPR_C
*"      SBO STRUCTURE  BAPI1012_SUB_OPR_C
*"      RFO STRUCTURE  BAPI1012_REF_OPR_C
*"      WCR STRUCTURE  BAPI1012_WC_REF_OPR_C
*"      COM STRUCTURE  BAPI1012_COM_C
*"      PRT STRUCTURE  BAPI1012_PRT_C
*"      CHA STRUCTURE  BAPI1012_CHA_C
*"      TXT STRUCTURE  BAPI1012_TXT_HDR_C
*"      ET_RETURN STRUCTURE  BAPIRET2
*"----------------------------------------------------------------------

  DATA: lt_tsk          TYPE TABLE OF   bapi1012_tsk_c,
        lt_mtk          TYPE TABLE OF   bapi1012_mtk_c,
        lt_seq          TYPE TABLE OF   bapi1012_seq_c,
        lt_opr          TYPE TABLE OF   bapi1012_opr_c,
        lt_sbo          TYPE TABLE OF   bapi1012_sub_opr_c,
        lt_rfo          TYPE TABLE OF   bapi1012_ref_opr_c,
        lt_wcr          TYPE TABLE OF   bapi1012_wc_ref_opr_c,
        lt_com          TYPE TABLE OF   bapi1012_com_c,
        lt_prt          TYPE TABLE OF   bapi1012_prt_c,
        lt_cha          TYPE TABLE OF   bapi1012_cha_c,
        lt_txt          TYPE TABLE OF   bapi1012_txt_hdr_c,
        lt_return       TYPE TABLE OF   bapiret2 WITH HEADER LINE,

        lv_group        TYPE bapi1012_tsk_c-task_list_group,
        lv_groupcounter TYPE bapi1012_tsk_c-group_counter,
        lv_testrun      TYPE bapiflag,

        lr_plnnr        TYPE RANGE OF plko-plnnr.

  CHECK tsk[] IS NOT INITIAL.

  SORT: tsk   BY task_list_group group_counter,
        mtk   BY task_list_group group_counter,
        seq   BY task_list_group group_counter sequence_no,
        opr   BY task_list_group group_counter sequence_no activity,
        sbo   BY task_list_group group_counter sequence_no activity sub_activity,
        rfo   BY task_list_group group_counter sequence_no activity,
        wcr   BY task_list_group group_counter sequence_no activity,
        com   BY task_list_group group_counter sequence_no activity,
        prt   BY task_list_group group_counter sequence_no activity item_no_of_production_resource,
        cha   BY task_list_group group_counter sequence_no activity,
        txt   BY task_list_group group_counter sequence_no activity sub_activity.


  SELECT DISTINCT plnnr FROM plko INTO TABLE @DATA(lt_plko)
    FOR ALL ENTRIES IN @tsk
    WHERE plnnr EQ @tsk-task_list_group
      AND plnty EQ 'N'.
  SORT lt_plko BY plnnr.
  LOOP AT tsk .
    READ TABLE lt_plko TRANSPORTING NO FIELDS
    WITH KEY plnnr = tsk-task_list_group BINARY SEARCH.
    IF sy-subrc IS INITIAL.
      DELETE tsk.
    ENDIF.
  ENDLOOP.

  CHECK tsk[] IS NOT INITIAL.

  DATA(lt_tlg) = tsk[]. "task_list_group
  SORT lt_tlg BY task_list_group.
  DELETE ADJACENT DUPLICATES FROM lt_tlg COMPARING task_list_group.

  DO.
    DATA(lv_lines_end) = iv_bapitq * sy-index .
    DATA(lv_lines_beg) = iv_bapitq * ( sy-index - 1 ) + 1 .

    IF lv_lines_end GT lines( lt_tlg ).
      lv_lines_end = lines( lt_tlg ).
    ENDIF.

    IF lv_lines_beg GT lines( lt_tlg ).
      EXIT .
    ENDIF.

    CLEAR: lv_group, lv_groupcounter, lt_return, lt_return[].

    lr_plnnr = VALUE #( FOR ls_wa1 IN lt_tlg FROM lv_lines_beg TO lv_lines_end
                            ( sign = 'I' option = 'EQ' low = ls_wa1-task_list_group ) ).

    CHECK lr_plnnr[] IS NOT INITIAL.

    IF sy-batch IS INITIAL.
      cl_progress_indicator=>progress_indicate(
                i_text = |Processed: { lv_lines_beg }/{ lines( lt_tlg ) }|
                i_output_immediately = abap_true ).
    ENDIF.

    SORT lr_plnnr.
    DELETE ADJACENT DUPLICATES FROM lr_plnnr COMPARING ALL FIELDS.

    lt_tsk = VALUE #( FOR ls_wa1  IN tsk WHERE ( task_list_group IN lr_plnnr ) ( ls_wa1  ) ) .
    lt_mtk = VALUE #( FOR ls_wa2  IN mtk WHERE ( task_list_group IN lr_plnnr ) ( ls_wa2  ) ) .
    lt_seq = VALUE #( FOR ls_wa3  IN seq WHERE ( task_list_group IN lr_plnnr ) ( ls_wa3  ) ) .
    lt_opr = VALUE #( FOR ls_wa4  IN opr WHERE ( task_list_group IN lr_plnnr ) ( ls_wa4  ) ) .
    lt_sbo = VALUE #( FOR ls_wa5  IN sbo WHERE ( task_list_group IN lr_plnnr ) ( ls_wa5  ) ) .
    lt_rfo = VALUE #( FOR ls_wa6  IN rfo WHERE ( task_list_group IN lr_plnnr ) ( ls_wa6  ) ) .
    lt_wcr = VALUE #( FOR ls_wa7  IN wcr WHERE ( task_list_group IN lr_plnnr ) ( ls_wa7  ) ) .
    lt_com = VALUE #( FOR ls_wa8  IN com WHERE ( task_list_group IN lr_plnnr ) ( ls_wa8  ) ) .
    lt_prt = VALUE #( FOR ls_wa9  IN prt WHERE ( task_list_group IN lr_plnnr ) ( ls_wa9  ) ) .
    lt_cha = VALUE #( FOR ls_wa10 IN cha WHERE ( task_list_group IN lr_plnnr ) ( ls_wa10 ) ) .
    lt_txt = VALUE #( FOR ls_wa11 IN txt WHERE ( task_list_group IN lr_plnnr ) ( ls_wa11 ) ) .


    CALL FUNCTION 'BAPI_ROUTING_CREATE'
      EXPORTING
        testrun                = lv_testrun
*       PROFILE                = 'PI01'
*       BOMUSAGE               = '1'
*       APPLICATION            = 'PP01'
      IMPORTING
        group                  = lv_group
        groupcounter           = lv_groupcounter
      TABLES
        task                   = lt_tsk
        materialtaskallocation = lt_mtk
        sequence               = lt_seq
        operation              = lt_opr
        suboperation           = lt_sbo
        referenceoperation     = lt_rfo
        workcenterreference    = lt_wcr
        componentallocation    = lt_com
        productionresource     = lt_prt
        inspcharacteristic     = lt_cha
        textallocation         = lt_txt
*       text                   = text[]
        return                 = lt_return[]
      EXCEPTIONS
        error_message          = 1
        OTHERS                 = 2.


    LOOP AT lt_return WHERE type CA 'EAX'. ENDLOOP.

    IF sy-subrc IS INITIAL.
      CALL FUNCTION 'BAPI_TRANSACTION_ROLLBACK'.
    ELSE.
      CALL FUNCTION 'BAPI_TRANSACTION_COMMIT' EXPORTING wait = 'X'.
    ENDIF.

    cl_progress_indicator=>progress_indicate(
              i_text = |Processed: { lv_lines_end }/{ lines( lt_tlg ) }|
              i_output_immediately = abap_true ).


    APPEND LINES OF lt_return TO et_return.

    IF sy-batch IS NOT INITIAL.
      WRITE:/ lr_plnnr[ 1 ]-low, lr_plnnr[ lines( lr_plnnr[] ) ]-low.

      LOOP AT lt_return INTO DATA(ls_return) WHERE type CA 'EAX'.
        WRITE:/ ls_return-type, ls_return-number, ls_return-id, '-', ls_return-message.
      ENDLOOP.
    ENDIF.

  ENDDO.


ENDFUNCTION.

 

Notes

  • Excel upload is not stable. If program gets dump for any reason, your excel can stuck as open. You need to go to task manager and kill the backgourd excel file process.
  • Program does not have a “Change mod”. If a task already exists (has PLKO records) the program will just skip it.
  • If you want to change the routing, you can delete the entire Routing with CA98 easily and upload it again.

 

Conclusion

I hope this program decreases consultany effort for routing upload processes.

 

If you have any question, please ask. I will be checking the blog for  Q&A. I am eager to share and learn.

 

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Anil Kumar Aleti
      Anil Kumar Aleti

      Hi this blog meets a need which many many people have during data migration. Thanks for your effort in publishing this. Question: how to extend the program to input MICs (QM inspection characteristics) in the routings during creation of routing ?

      Author's profile photo Oğuzhan Subaşı
      Oğuzhan Subaşı
      Blog Post Author

      Hi Anil,

      You can check the excel file. There is a sheet for Inspection Characteristics. You can use that sheet.

      Author's profile photo Anil Kumar Aleti
      Anil Kumar Aleti

      Oh great, thank you.

      Author's profile photo Vignesh Veerasamy
      Vignesh Veerasamy

      Need of the hour as many struggle with LTMC.This one is a boon.

      Keep going.Bookmarked.

      Regards

      Vignesh V