Fetch data from excel and Move Material Stock from Restricted to Unrestricted in ABAP

REPORT zr_mm_material_so_release.

 

** Global Declarations

    INCLUDE zi_mm_material_so_release_top.

** Selection screen

    INCLUDE zi_mm_material_so_release_sel.

** subroutines

    INCLUDE zi_mm_material_so_release_form.

** Initialization

INITIALIZATION.

  PERFORM init_selscr_icons.

  PERFORM clear_variables.

** AT SELECTION SCREEN

AT SELECTION-SCREEN.

  CASE sscrfields-ucomm.

    WHEN 'FC01'.

      PERFORM prepare_template.

      PERFORM download_template.

  ENDCASE.

** AT SELECTION-SCREEN ON VALUE-REQUEST FOR

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

* Selecting the file

  PERFORM get_file.

START-OF-SELECTION.

  PERFORM read_file .

  PERFORM process_file.

  PERFORM call_bapi_mat_change.

  PERFORM call_reuse_alv_grid_display.

 

*&-----------------------------------------------------------------------------------------------------------------------------*

*& Include   ZI_MM_MATERIAL_SO_RELEASE_TOP

*&-----------------------------------------------------------------------------------------------------------------------------*

TYPE-POOLS:slis, truxs, icon.

TABLES:sscrfields.

SELECTION-SCREEN FUNCTION KEY 1.

FIELD-SYMBOLS : <g_t_data> TYPE STANDARD TABLE .

TYPES: BEGIN OF l_s_file,

              material      TYPE matnr18, "Material number

              stg_loc       TYPE lgort_d, "Storage location

             plant         TYPE werks_d, " Plant

             mov_type      TYPE bwart, " Movement type

             mov_code      TYPE bapi2017_gm_code, " Movement code

             sale_order    TYPE kdauf, " Sale order number

             sale_ord_item TYPE kdpos, " Sale order item

             entry_quan    TYPE erfmg, " Entry quantity

           END OF l_s_file.

TYPES: BEGIN OF l_s_final,

             material     TYPE matnr18, "Material number

             status       TYPE char4,         "Status

             remarks(200) TYPE c,             "Remarks

             uname        TYPE sy-uname,      "Updated By

             udate        TYPE sy-datum,     "Updated On

             utime        TYPE sy-uzeit,      "Updated At

           END OF l_s_final.

DATA: g_t_file  TYPE STANDARD TABLE OF l_s_file,

              g_r_file  TYPE l_s_file,

              g_t_final TYPE TABLE OF l_s_final,

              g_r_final TYPE  l_s_final.

DATA : g_functxt TYPE smp_dyntxt.

DATA:g_t_fcat   TYPE  slis_t_fieldcat_alv,

         g_r_fcat   TYPE slis_fieldcat_alv,

         g_r_layout TYPE slis_layout_alv,

         g_t_listheader TYPE slis_t_listheader,

         g_r_listheader TYPE slis_listheader..

 

DATA:g_t_template  TYPE TABLE OF l_s_file,

         g_t_itab_cols TYPE abap_component_tab.

 

DATA : g_o_excel_structure      TYPE REF TO data,

       g_o_source_table_descr   TYPE REF TO cl_abap_tabledescr,

       g_o_table_row_descriptor TYPE REF TO cl_abap_structdescr,

       g_f_content              TYPE xstring,

       g_t_binary_tab           TYPE TABLE OF sdokcntasc,

       g_f_length               TYPE i,

       g_f_filename1            TYPE string,

       g_f_path                 TYPE string,

       g_f_fullpath             TYPE string.

DATA:g_r_mov_header TYPE bapi2017_gm_head_01,

     g_f_mov_code   TYPE char2,"bapi2017_gm_code,

     g_r_mov_item   TYPE bapi2017_gm_item_create,

     g_t_mov_item   TYPE TABLE OF bapi2017_gm_item_create,

     g_t_return     TYPE TABLE OF bapiret2,

     g_r_return     TYPE  bapiret2.

 

CONSTANTS: g_c_x TYPE c VALUE 'X',

           g_c_e TYPE c VALUE 'E'.

*&---------------------------------------------------------------------------------------------------------------------------------*

*& Include          ZI_MM_MATERIAL_SO_RELEASE_SEL

*&---------------------------------------------------------------------------------------------------------------------------------*

PARAMETERS: p_file TYPE ibipparms-path.

 *&----------------------------------------------------------------------------------------------------------------------------------------------------*

*& Include          ZI_MM_MATERIAL_SO_RELEASE_FORM

*&------------------------------------------------------------------------------------------------------------------------------------------------------*

 

*&-------------------------------------------------------------------------------------------------------------------------------------------*

*& Form init_selscr_icons

*&-----------------------------------------------------------------------------------------------------------------------------------------*

FORM init_selscr_icons .

  g_functxt-icon_id     = icon_xxl.

  g_functxt-icon_text   = TEXT-001.

  sscrfields-functxt_01 = g_functxt.

ENDFORM.

*&--------------------------------------------------------------------------------------------------------------------------------------*

*& Form clear_variables

*&---------------------------------------------------------------------------------------------------------------------------------------*

FORM clear_variables .

  CLEAR:g_r_file,g_r_final,g_r_return,g_r_mov_item,g_r_mov_header,g_f_mov_code,g_r_fcat,g_r_layout.

  REFRESH:g_t_file,g_t_final,g_t_return,g_t_mov_item,g_t_fcat.

ENDFORM.

*&---------------------------------------------------------------------------------------------------------------------------------------------------------------*

*& Form prepare_template

*&----------------------------------------------------------------------------------------------------------------------------------------------------------*

FORM prepare_template .

  GET REFERENCE OF g_t_template INTO g_o_excel_structure.

  DATA(g_o_itab_services)   = cl_salv_itab_services=>create_for_table_ref( g_o_excel_structure ).

  g_o_source_table_descr   ?= cl_abap_tabledescr=>describe_by_data_ref( g_o_excel_structure  ).

  g_o_table_row_descriptor ?= g_o_source_table_descr->get_table_line_type( ).

  DATA(g_o_tool_xls) = cl_salv_export_tool_ats_xls=>create_for_excel(

                            EXPORTING r_data =  g_o_excel_structure  ) .

  DATA : l_f_matnr     TYPE string,

             l_f_lgort     TYPE string,

             l_f_werks     TYPE string,

             l_f_bwart     TYPE string,

             g_f_mov_code  TYPE string,

             l_f_sale_ord  TYPE string,

             l_f_sale_item TYPE string,

             l_f_quantity  TYPE string.

  l_f_MATNR     = TEXT-002. " Material number

  l_f_LGORT     = TEXT-003. " Storage location

  l_f_WERKS     =  TEXT-004. " Plant

  l_f_bwart     = TEXT-005. " Movement Type

  g_f_mov_code  = TEXT-006. " Movement code

  l_f_sale_ord  = TEXT-007.  " Sale order

  l_f_sale_item = TEXT-008.  " Sale item

  l_f_quantity  = TEXT-009.  " Quantity.

  DATA(g_o_config) = g_o_tool_xls->configuration( ).

  g_o_config->add_column(

      EXPORTING

        header_text          =  l_f_MATNR " Material number

        field_name           =  'MATERIAL'

        display_type         =   if_salv_bs_model_column=>uie_text_view ).

 

  g_o_config->add_column(

      EXPORTING

        header_text          =  l_f_LGORT "Storage location

        field_name           =  'STG_LOC'

        display_type         =   if_salv_bs_model_column=>uie_text_view ).

 

  g_o_config->add_column(

      EXPORTING

        header_text          =  l_f_WERKS " Plant

        field_name           =  'PLANT'

        display_type         =   if_salv_bs_model_column=>uie_text_view ).

  g_o_config->add_column(

      EXPORTING

        header_text          =  l_f_bwart" Movement Type

        field_name           =  'MOV_TYPE'

        display_type         =   if_salv_bs_model_column=>uie_text_view ).

  g_o_config->add_column(

      EXPORTING

        header_text          =  g_f_mov_code " MOVEMENT CODE

        field_name           =  'MOV_CODE'

        display_type         =   if_salv_bs_model_column=>uie_text_view ).

  g_o_config->add_column(

      EXPORTING

        header_text          =  l_f_sale_ord " SALE ORDER

        field_name           =  'SALE_ORDER'

        display_type         =   if_salv_bs_model_column=>uie_text_view ).

  g_o_config->add_column(

      EXPORTING

        header_text          =  l_f_sale_ITEM " SALE ITEM

        field_name           =  'SALE_ORD_ITEM'

        display_type         =   if_salv_bs_model_column=>uie_text_view ).

  g_o_config->add_column(

      EXPORTING

        header_text          =  l_f_quantity "QUANTITY

        field_name           =  'ENTRY_QUAN'

        display_type         =   if_salv_bs_model_column=>uie_text_view ).

  TRY.

      g_o_tool_xls->read_result(  IMPORTING content  = g_f_content  ).

    CATCH cx_root.

  ENDTRY.

  CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'

    EXPORTING

      buffer        = g_f_content

    IMPORTING

      output_length = g_f_length

    TABLES

      binary_tab    = g_t_binary_tab.

ENDFORM.

*&---------------------------------------------------------------------------------------------------------------------------------------------*

*& Form download_template

*&---------------------------------------------------------------------------------------------------------------------------------------------*

FORM download_template .

  DATA : lf_window_title TYPE string.

              lf_window_title = TEXT-010.

  CALL METHOD cl_gui_frontend_services=>file_save_dialog

    EXPORTING

      window_title      = lf_window_title "'Select file path'

      default_extension = cl_gui_frontend_services=>filetype_excel

      default_file_name = g_f_filename1

    CHANGING

      filename          = g_f_filename1

      path              = g_f_path

      fullpath          = g_f_fullpath.

  IF g_f_fullpath IS NOT INITIAL.

    CALL FUNCTION 'GUI_DOWNLOAD'

      EXPORTING

        bin_filesize            = g_f_length

        filename                = g_f_fullpath

        filetype                = 'BIN'

      TABLES

        data_tab                = g_t_binary_tab

      EXCEPTIONS

        file_write_error        = 1

        no_batch                = 2

        gui_refuse_filetransfer = 3

        invalid_type            = 4

        no_authority            = 5

        unknown_error           = 6

        header_not_allowed      = 7

        separator_not_allowed   = 8

        filesize_not_allowed    = 9

        header_too_long         = 10

        dp_error_create         = 11

        dp_error_send           = 12

        dp_error_write          = 13

        unknown_dp_error        = 14

        access_denied           = 15

        dp_out_of_memory        = 16

        disk_full               = 17

        dp_timeout              = 18

        file_not_found          = 19

        dataprovider_exception  = 20

        control_flush_error     = 21

        OTHERS                  = 22.

    IF sy-subrc <> 0.

    ELSE.

      CALL METHOD cl_gui_frontend_services=>execute

        EXPORTING

          document               = g_f_fullpath

        EXCEPTIONS

          cntl_error             = 1

          error_no_gui           = 2

          bad_parameter          = 3

          file_not_found         = 4

          path_not_found         = 5

          file_extension_unknown = 6

          error_execute_failed   = 7

          synchronous_failed     = 8

          not_supported_by_gui   = 9

          OTHERS                 = 10.

      IF sy-subrc <> 0.

      ENDIF.

    ENDIF.

  ENDIF.

ENDFORM.

*&--------------------------------------------------------------------------------------------------------------------------------------------*

*& Form get_file

*&-----------------------------------------------------------------------------------------------------------------------------------------------*

FORM get_file .

  DATA: lf_rc           TYPE i,

            lt_file_table   TYPE filetable,

            ls_file_table   TYPE file_table,

            lf_window_title TYPE string.

            lf_window_title = TEXT-011.

 

  CALL METHOD cl_gui_frontend_services=>file_open_dialog

    EXPORTING

      window_title            = lf_window_title

    CHANGING

      file_table              = lt_file_table

      rc                      = lf_rc

    EXCEPTIONS

      file_open_dialog_failed = 1

      cntl_error              = 2

      error_no_gui            = 3

      not_supported_by_gui    = 4

      OTHERS                  = 5.

 

  IF sy-subrc = 0.

    READ TABLE lt_file_table INTO ls_file_table INDEX 1.

    p_file = ls_file_table-filename.

  ENDIF.

ENDFORM.

*&---------------------------------------------------------------------------------------------------------------------------------------*

*& Form read_file

*&-----------------------------------------------------------------------------------------------------------------------------------------*

FORM read_file .

  DATA : l_f_filename      TYPE string,

                 l_t_records       TYPE solix_tab,

                 l_f_headerxstring TYPE xstring,

                 l_f_filelength    TYPE i.

  l_f_filename = p_file.

  CALL FUNCTION 'GUI_UPLOAD'

    EXPORTING

      filename                = l_f_filename

      filetype                = 'BIN'

    IMPORTING

      filelength              = l_f_filelength

      header                  = l_f_headerxstring

    TABLES

      data_tab                = l_t_records

    EXCEPTIONS

      file_open_error         = 1

      file_read_error         = 2

      no_batch                = 3

      gui_refuse_filetransfer = 4

      invalid_type            = 5

      no_authority            = 6

      unknown_error           = 7

      bad_data_format         = 8

      header_not_allowed      = 9

      separator_not_allowed   = 10

      header_too_long         = 11

      unknown_dp_error        = 12

      access_denied           = 13

      dp_out_of_memory        = 14

      disk_full               = 15

      dp_timeout              = 16

      OTHERS                  = 17.

  IF sy-subrc <> 0 .

    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

  ENDIF.

*  convert binary data to xstring

  CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'

    EXPORTING

      input_length = l_f_filelength

    IMPORTING

      buffer       = l_f_headerxstring

    TABLES

      binary_tab   = l_t_records

    EXCEPTIONS

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

  DATA : lo_excel_ref TYPE REF TO cl_fdt_xl_spreadsheet .

  TRY .

      lo_excel_ref = NEW cl_fdt_xl_spreadsheet(

                              document_name = l_f_filename

                              xdocument     = l_f_headerxstring ) .

    CATCH cx_fdt_excel_core.

      "Implement suitable error handling here

  ENDTRY .

 

  "Get List of Worksheets

  lo_excel_ref->if_fdt_doc_spreadsheet~get_worksheet_names(

    IMPORTING

      worksheet_names = DATA(l_t_worksheets) ).

 

  IF NOT l_t_worksheets IS INITIAL.

    READ TABLE l_t_worksheets INTO DATA(lf_woksheetname) INDEX 1.

 

    DATA(lo_data_ref) = lo_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet(

                                             lf_woksheetname ).

    "now you have excel work sheet data in dyanmic internal table

    ASSIGN lo_data_ref->* TO <g_t_data>.

  ENDIF.

ENDFORM.

*&--------------------------------------------------------------------------------------------------------------------------------------------------------*

*& Form process_file

*&-------------------------------------------------------------------------------------------------------------------------------------------------------*

FORM process_file .

  DATA : l_f_numberofcolumns TYPE i.

  FIELD-SYMBOLS : <l_r_data>   TYPE any,

                                      <l_f_field>  TYPE any,

                                      <l_f_field1> TYPE any.

  l_f_numberofcolumns = 8.

  LOOP AT <g_t_data> ASSIGNING <l_r_data> FROM 2 .

 

    "processing columns

    DO l_f_numberofcolumns TIMES.

      ASSIGN COMPONENT sy-index OF STRUCTURE <l_r_data> TO <l_f_field> .

      ASSIGN COMPONENT sy-index OF STRUCTURE g_r_file TO <l_f_field1>.

      IF <l_f_field> IS ASSIGNED AND <l_f_field1> IS ASSIGNED.

        <l_f_field1> = <l_f_field>.

      ENDIF.

      UNASSIGN: <l_f_field1>, <l_f_field>.

    ENDDO .

    APPEND g_r_file TO g_t_file.

    CLEAR g_r_file.

  ENDLOOP .

ENDFORM.

*&----------------------------------------------------------------------------------------------------------------------------------------------*

*& Form call_bapi_mat_change

*&------------------------------------------------------------------------------------------------------------------------------------------------*

FORM call_bapi_mat_change .

  DATA: l_f_m_code(2) TYPE c.

  LOOP AT g_t_file INTO g_r_file.

    CLEAR: g_r_mov_header, g_f_mov_code,g_r_mov_item.

    REFRESH: g_t_mov_item.

    g_r_mov_header-pstng_date = sy-datum.

    g_r_mov_header-doc_date   = sy-datum.

    l_f_m_code                = g_r_file-mov_code.

    g_f_mov_code              = |{ l_f_m_code ALPHA = IN }|.

    g_r_mov_item-material     = g_r_file-material.

    g_r_mov_item-plant        = g_r_file-plant.

    g_r_mov_item-stge_loc     = g_r_file-stg_loc.

    g_r_mov_item-move_type    = g_r_file-mov_type.

    g_r_mov_item-sales_ord    = g_r_file-sale_order.

    g_r_mov_item-s_ord_item   = g_r_file-sale_ord_item.

    g_r_mov_item-entry_qnt    = g_r_file-entry_quan.

    APPEND g_r_mov_item TO g_t_mov_item.

    CALL FUNCTION 'BAPI_GOODSMVT_CREATE'

      EXPORTING

        goodsmvt_header = g_r_mov_header

        goodsmvt_code   = g_f_mov_code

      TABLES

        goodsmvt_item   = g_t_mov_item

        return          = g_t_return.

    READ TABLE g_t_return INTO DATA(l_f_check) WITH KEY type = g_c_e.

    IF sy-subrc EQ 0 AND l_f_check IS NOT INITIAL.

      g_r_final-status = icon_red_light.

      LOOP AT g_t_return INTO g_r_return WHERE type = g_c_e.

        IF g_r_final-remarks IS INITIAL.

          g_r_final-remarks = g_r_return-message.

        ELSE.

          CONCATENATE g_r_final-remarks g_r_return-message

                       INTO g_r_final-remarks SEPARATED BY ','.

        ENDIF.

      ENDLOOP.

    ELSE.

      CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'

        EXPORTING

          wait = 'X'.

      g_r_final-remarks = TEXT-012.

      g_r_final-status = icon_green_light.

    ENDIF.

    g_r_final-material = g_r_file-material.

    g_r_final-uname    = sy-uname.

    g_r_final-udate    = sy-datum.

    g_r_final-utime    = sy-uzeit.

    APPEND g_r_final TO g_t_final.

    clear: g_r_final.

  ENDLOOP.

ENDFORM.

*&--------------------------------------------------------------------------------------------------------------------------------------------------------*

*& Form call_reuse_alv_grid_display

*&-----------------------------------------------------------------------------------------------------------------------------------------------------*

FORM call_reuse_alv_grid_display .

  g_r_layout-zebra             = g_c_x.

  g_r_layout-colwidth_optimize = g_c_x.

 

  REFRESH:g_t_fcat.

  CLEAR:g_r_fcat.

  g_r_fcat-col_pos   = 1.

  g_r_fcat-fieldname = TEXT-014.

  g_r_fcat-seltext_l = TEXT-015.

  g_r_fcat-key       = g_c_x.

  APPEND g_r_fcat TO g_t_fcat.

 

  CLEAR:g_r_fcat.

  g_r_fcat-col_pos   = 2.

  g_r_fcat-fieldname = TEXT-016.

  g_r_fcat-seltext_l = TEXT-017.

*  g_r_fcat-key       = g_c_x.

  APPEND g_r_fcat TO g_t_fcat.

 

  CLEAR:g_r_fcat.

  g_r_fcat-col_pos   = 3.

  g_r_fcat-fieldname = TEXT-018.

  g_r_fcat-seltext_l = TEXT-019.

  APPEND g_r_fcat TO g_t_fcat.

 

  CLEAR:g_r_fcat.

  g_r_fcat-col_pos   = 4.

  g_r_fcat-fieldname = TEXT-020.

  g_r_fcat-seltext_l = TEXT-021.

  g_r_fcat-icon = g_c_x.

  APPEND g_r_fcat TO g_t_fcat.

 

  CLEAR:g_r_fcat.

  g_r_fcat-col_pos   = 5.

  g_r_fcat-fieldname = TEXT-022.

  g_r_fcat-seltext_l = TEXT-023.

  APPEND g_r_fcat TO g_t_fcat.

 

  CLEAR:g_r_fcat.

  g_r_fcat-col_pos   = 6.

  g_r_fcat-fieldname = TEXT-024.

  g_r_fcat-seltext_l = TEXT-025.

  APPEND g_r_fcat TO g_t_fcat.

 

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

    EXPORTING

      i_callback_program     = sy-repid

      i_callback_top_of_page = 'TOP_OF_PAGE'

      is_layout              = g_r_layout

      it_fieldcat            = g_t_fcat

    TABLES

      t_outtab               = g_t_final.

ENDFORM.

*&----------------------------------------------------------------------------------------------------------------------------------------------------------*

*& Form TOP_OF_PAGE

*&----------------------------------------------------------------------------------------------------------------------------------------------------*

FORM top_of_page .

  CLEAR:g_t_listheader.

  g_r_listheader-typ  = 'H'.

  g_r_listheader-info = TEXT-013.

  APPEND g_r_listheader TO g_t_listheader.

  CLEAR g_r_listheader.

 

  CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE'

    EXPORTING

      it_list_commentary = g_t_listheader.

ENDFORM.

File

Input

Output

********************************Thank You*****************************