In the previous blog we have covered how to create Create a custom Shared Memory Area and its class. In this Blog post we will cover the Part 2 section of Excel Mass Upload in Background Job Using SHMA – Part 1: Create a Custom Shared Memory Area.
Section 2
In this section we will cover detailed steps to create ALV program, which will use the SHMA created in part 1.
Step 1: Create Program and have the following includes as shown below.
Step 2: Create Selection Screen using below code lines in the include ‘YMASS_UPLOAD_S01’
** Selection Screen
SELECTION-SCREEN:BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-t01.
* File selection
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN POSITION 2.
SELECTION-SCREEN COMMENT 2(10) TEXT-t02.
PARAMETERS: p_file TYPE char200.
SELECTION-SCREEN END OF LINE.
* Execute Option
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN POSITION 2.
PARAMETERS: cb_bg AS CHECKBOX .
SELECTION-SCREEN COMMENT 4(30) TEXT-t03.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN:END OF BLOCK b1.
Code Block – Selection Screen
Step 3: Create Local Class with following types Data, Constants & methods as below code lines in the include ‘YMASS_UPLOAD_C01’
CLASS lcl_upload DEFINITION FINAL.
PUBLIC SECTION.
* Types
TYPES:BEGIN OF gty_alv, "Alv table
status TYPE char10,
id TYPE ymass_upload-id,
description TYPE ymass_upload-description,
quantity TYPE ymass_upload-quantity,
unit TYPE ymass_upload-unit,
msg TYPE bapi_msg,
END OF gty_alv.
* Data declaration
DATA: gt_mat_upload TYPE ycl_mass_upload_shma=>gtt_mat_upload,
gs_mat_upload TYPE ycl_mass_upload_shma=>gty_mat_upload,
gt_alv TYPE STANDARD TABLE OF gty_alv.
* ALV data declaration
DATA :go_salv TYPE REF TO cl_salv_table,
go_columns TYPE REF TO cl_salv_columns_table,
go_functions TYPE REF TO cl_salv_functions.
##NO_TEXT
CONSTANTS: gc_mat TYPE string VALUE 'Material',
gc_s TYPE c LENGTH 1 VALUE 'S',
gc_e TYPE c LENGTH 1 VALUE 'E'.
METHODS:
get_browse_file "F4 help
CHANGING
cv_file TYPE char200,
get_excel_data "Excel upload
IMPORTING
iv_file TYPE char200,
get_material "fill table from Material sheet
IMPORTING
io_excel_ref TYPE REF TO cl_fdt_xl_spreadsheet
iv_sheetname TYPE string
EXPORTING
et_mat_upload TYPE ycl_mass_upload_shma=>gtt_mat_upload,
validate_update_mat,
display_alv, "Display alv
update_alv_fc, "Alv field catalog changes
update_alv_header "Alv header
CHANGING
co_salv TYPE REF TO cl_salv_table,
schedule_job, "schedule BG job
insert_shma "insert data in SHMA
IMPORTING
iv_jobname TYPE tbtcjob-jobname
iv_jobcount TYPE tbtcjob-jobcount,
read_shma. "read data from SHMA
ENDCLASS.
Code Block – Local Class Definition
Step 4: Implement above declared methods using following code lines.
CLASS lcl_upload IMPLEMENTATION.
METHOD get_browse_file.
DATA: lt_filetab TYPE filetable,
lv_rc TYPE i,
lv_string TYPE string.
FREE lt_filetab.
lv_string = TEXT-001.
* dialog box for F4 help
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = lv_string
default_extension = '*.*'
default_filename = 'c:*.xls' ##NO_TEXT
CHANGING
file_table = lt_filetab
rc = lv_rc
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
OTHERS = 4.
IF sy-subrc = 0.
READ TABLE lt_filetab INTO cv_file INDEX 1.
ENDIF.
ENDMETHOD.
METHOD get_excel_data.
DATA : lv_filename TYPE string,
lt_records TYPE solix_tab,
lv_headerxstring TYPE xstring,
lv_filelength TYPE i,
lo_excel_ref TYPE REF TO cl_fdt_xl_spreadsheet.
lv_filename = iv_file.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = lv_filename
filetype = 'BIN'
has_field_separator = ' '
IMPORTING
filelength = lv_filelength
header = lv_headerxstring
TABLES
data_tab = lt_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.
**Raise exceptions
MESSAGE TEXT-e01 TYPE gc_s DISPLAY LIKE gc_e.
LEAVE LIST-PROCESSING.
RETURN.
ENDIF.
** convert binary data to xstring
** if you are using cl_fdt_xl_spreadsheet in odata then skips this step
** as excel file will already be in xstring
CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
EXPORTING
input_length = lv_filelength
IMPORTING
buffer = lv_headerxstring
TABLES
binary_tab = lt_records
EXCEPTIONS
failed = 1
OTHERS = 2.
##NEEDED
IF sy-subrc <> 0.
**Raise Exception
ENDIF.
TRY .
lo_excel_ref = NEW cl_fdt_xl_spreadsheet(
document_name = lv_filename
xdocument = lv_headerxstring ) .
CATCH cx_fdt_excel_core.
CLEAR lo_excel_ref.
ENDTRY .
IF lo_excel_ref IS BOUND.
** Get List of Worksheets
lo_excel_ref->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING
worksheet_names = DATA(lt_worksheets) ).
IF NOT lt_worksheets IS INITIAL.
* Fill Material
me->get_material(
EXPORTING
io_excel_ref = lo_excel_ref
iv_sheetname = gc_mat
IMPORTING
et_mat_upload = gt_mat_upload
).
ENDIF.
ENDIF.
ENDMETHOD.
METHOD get_material.
DATA: lv_date TYPE char10,
lv_istru TYPE char18.
FIELD-SYMBOLS: <lfs_t_data> TYPE STANDARD TABLE.
DATA(lo_data_ref) = io_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet(
iv_sheetname ).
* now you have excel work sheet data in dyanmic internal table
ASSIGN lo_data_ref->* TO <lfs_t_data>.
IF sy-subrc EQ 0.
LOOP AT <lfs_t_data> ASSIGNING FIELD-SYMBOL(<lfs_data>) FROM 2.
##NUMBER_OK DO 4 TIMES.
ASSIGN COMPONENT sy-index OF STRUCTURE <lfs_data> TO FIELD-SYMBOL(<lfs_v_field>).
CASE sy-index.
WHEN 1.
gs_mat_upload-id = <lfs_v_field>.
WHEN 2.
gs_mat_upload-description = <lfs_v_field>.
WHEN 3.
gs_mat_upload-quantity = <lfs_v_field>.
WHEN 4.
gs_mat_upload-unit = <lfs_v_field>.
ENDCASE.
ENDDO.
APPEND gs_mat_upload TO et_mat_upload.
CLEAR gs_mat_upload.
ENDLOOP.
ENDIF.
ENDMETHOD.
METHOD validate_update_mat.
DATA: lt_ymass_upload TYPE STANDARD TABLE OF ymass_upload,
lt_ymass_upload_db TYPE STANDARD TABLE OF ymass_upload.
SELECT * FROM
ymass_upload
INTO TABLE lt_ymass_upload_db.
* validation
LOOP AT gt_mat_upload ASSIGNING FIELD-SYMBOL(<lfs_mat>).
IF <lfs_mat>-id IS INITIAL.
gt_alv = VALUE #( BASE gt_alv
( status = icon_led_red
id = <lfs_mat>-id
description = <lfs_mat>-description
quantity = <lfs_mat>-quantity
unit = <lfs_mat>-unit
msg = TEXT-e02 ) ).
ELSEIF line_exists( lt_ymass_upload_db[ id = <lfs_mat>-id ] ).
gt_alv = VALUE #( BASE gt_alv
( status = icon_led_red
id = <lfs_mat>-id
description = <lfs_mat>-description
quantity = <lfs_mat>-quantity
unit = <lfs_mat>-unit
msg = TEXT-e03 ) ).
ELSEIF line_exists( lt_ymass_upload[ id = <lfs_mat>-id ] ).
gt_alv = VALUE #( BASE gt_alv
( status = icon_led_red
id = <lfs_mat>-id
description = <lfs_mat>-description
quantity = <lfs_mat>-quantity
unit = <lfs_mat>-unit
msg = TEXT-e04 ) ).
ELSE.
* Fill table ymass upload.
lt_ymass_upload = VALUE #( BASE lt_ymass_upload
(
mandt = sy-mandt
id = <lfs_mat>-id
description = <lfs_mat>-description
quantity = <lfs_mat>-quantity
unit = <lfs_mat>-unit
) ).
gt_alv = VALUE #( BASE gt_alv
( status = icon_led_green
id = <lfs_mat>-id
description = <lfs_mat>-description
quantity = <lfs_mat>-quantity
unit = <lfs_mat>-unit
msg = TEXT-s01 ) ).
ENDIF.
IF lt_ymass_upload IS NOT INITIAL.
UPDATE ymass_upload FROM TABLE lt_ymass_upload.
COMMIT WORK.
ENDIF.
ENDLOOP.
ENDMETHOD.
METHOD display_alv.
* clear alv info to diplay alv data
cl_salv_bs_runtime_info=>clear_all( ).
* Factory method to create SALV object for ALV
TRY.
CALL METHOD cl_salv_table=>factory
IMPORTING
r_salv_table = go_salv
CHANGING
t_table = gt_alv.
CATCH cx_salv_msg.
CLEAR go_salv.
ENDTRY.
* fetch columns reference
go_columns = go_salv->get_columns( ).
* Column Width optimization
go_columns->set_optimize( abap_true ).
* Method call to update Field catalog
me->update_alv_fc( ).
* fetch toolbar funtions reference
go_functions = go_salv->get_functions( ).
* seeting all toolbar functions for ALV
go_functions->set_all( abap_true ).
* Alv header
me->update_alv_header( CHANGING co_salv = go_salv ).
* Display ALV.
go_salv->display( ).
ENDMETHOD.
METHOD update_alv_fc.
DATA lo_column_tab TYPE salv_t_column_ref.
DATA lo_column TYPE REF TO cl_salv_column_table.
* column table with attributes (Field catalog)
lo_column_tab = go_columns->get( ).
* updating Field Catalog
LOOP AT lo_column_tab ASSIGNING FIELD-SYMBOL(<lfs_col>).
lo_column ?= <lfs_col>-r_column.
CASE <lfs_col>-columnname.
WHEN 'STATUS'.
lo_column->set_fixed_header_text( 'L' ).
lo_column->set_icon( if_salv_c_bool_sap=>true ).
lo_column->set_alignment( if_salv_c_alignment=>centered ).
lo_column->set_long_text( TEXT-002 ).
WHEN 'ID'.
lo_column->set_fixed_header_text( 'L' ).
lo_column->set_long_text( TEXT-003 ).
WHEN 'DESCRIPTION'.
lo_column->set_fixed_header_text( 'L' ).
lo_column->set_long_text( TEXT-004 ).
WHEN 'QUANTITY'.
lo_column->set_fixed_header_text( 'L' ).
lo_column->set_long_text( TEXT-005 ).
WHEN 'UNIT'.
lo_column->set_fixed_header_text( 'L' ).
lo_column->set_long_text( TEXT-006 ).
WHEN 'MSG'.
lo_column->set_fixed_header_text( 'L' ).
lo_column->set_long_text( TEXT-007 ).
ENDCASE.
ENDLOOP.
ENDMETHOD.
METHOD update_alv_header.
DATA: lo_header TYPE REF TO cl_salv_form_layout_grid,
lo_h_label TYPE REF TO cl_salv_form_label,
lv_str_head TYPE string,
lv_str TYPE string,
lv_dttm TYPE char10,
lv_total TYPE string,
lv_success TYPE string,
lv_error TYPE string.
* header object
CREATE OBJECT lo_header.
lv_total = lines( gt_mat_upload ).
* Success count
DATA(lt_alv) = gt_alv.
DELETE lt_alv WHERE status = icon_led_red.
SORT lt_alv BY id.
lv_success = lines( lt_alv ).
* Error count
CLEAR lt_alv.
lt_alv = gt_alv.
DELETE lt_alv WHERE status = icon_led_green.
SORT lt_alv BY id.
lv_error = lines( lt_alv ).
lo_h_label = lo_header->create_label( row = 1 column = 1 ).
lo_h_label->set_text( TEXT-008 ).
lo_h_label = lo_header->create_label( row = 2 column = 1 colspan = 6 ).
lo_h_label->set_text( TEXT-009 ).
lo_h_label = lo_header->create_label( row = 3 column = 1 ).
* Total
CLEAR lv_str.
CONCATENATE TEXT-010 lv_total INTO lv_str SEPARATED BY space.
lo_h_label = lo_header->create_label( row = 4 column = 1 ).
lo_h_label->set_text( lv_str ).
lo_h_label = lo_header->create_label( row = 4 column = 3 ).
lo_h_label = lo_header->create_label( row = 4 column = 4 ).
lo_h_label = lo_header->create_label( row = 4 column = 5 ).
* User
CLEAR lv_str.
CONCATENATE TEXT-011 sy-uname INTO lv_str SEPARATED BY space.
lo_h_label = lo_header->create_label( row = 4 column = 6 ).
lo_h_label->set_text( lv_str ).
* Success
CLEAR lv_str.
CONCATENATE TEXT-012 lv_success INTO lv_str SEPARATED BY space.
lo_h_label = lo_header->create_label( row = 5 column = 1 ).
lo_h_label->set_text( lv_str ).
lo_h_label = lo_header->create_label( row = 5 column = 3 ).
lo_h_label = lo_header->create_label( row = 5 column = 4 ).
lo_h_label = lo_header->create_label( row = 5 column = 5 ).
* Date
CLEAR: lv_str, lv_dttm.
WRITE sy-datum TO lv_dttm.
CONCATENATE TEXT-013 lv_dttm INTO lv_str SEPARATED BY space.
lo_h_label = lo_header->create_label( row = 5 column = 6 ).
lo_h_label->set_text( lv_str ).
* Error
CLEAR lv_str.
CONCATENATE TEXT-014 lv_error INTO lv_str SEPARATED BY space.
lo_h_label = lo_header->create_label( row = 6 column = 1 ).
lo_h_label->set_text( lv_str ).
lo_h_label = lo_header->create_label( row = 6 column = 3 ).
lo_h_label = lo_header->create_label( row = 6 column = 4 ).
lo_h_label = lo_header->create_label( row = 6 column = 5 ).
* Time
CLEAR: lv_str, lv_dttm.
WRITE sy-uzeit TO lv_dttm.
CONCATENATE TEXT-015 lv_dttm INTO lv_str SEPARATED BY space.
lo_h_label = lo_header->create_label( row = 6 column = 6 ).
lo_h_label->set_text( lv_str ).
* set the top of list using the header for Online.
co_salv->set_top_of_list( lo_header ).
* set the top of list using the header for Print.
co_salv->set_top_of_list_print( lo_header ).
ENDMETHOD.
METHOD schedule_job.
DATA: lv_jobname TYPE tbtcjob-jobname VALUE sy-repid,
lv_jobcount TYPE tbtcjob-jobcount,
lv_starttimeimmediate TYPE btch0000-char1 VALUE 'X',
lv_server TYPE btctgtsrvr-srvname.
CONSTANTS: lc_underscore TYPE c VALUE '_'.
* Determine job name
lv_jobname = sy-repid && lc_underscore && sy-datum && lc_underscore && sy-uzeit.
* Job open
CALL FUNCTION 'JOB_OPEN'
EXPORTING
delanfrep = ' '
jobgroup = ' '
jobname = lv_jobname
sdlstrtdt = sy-datum
sdlstrttm = sy-uzeit
IMPORTING
jobcount = lv_jobcount
EXCEPTIONS
cant_create_job = 01
invalid_job_data = 02
jobname_missing = 03.
IF sy-subrc = 0.
* Put data into SHMA
me->insert_shma( EXPORTING iv_jobname = lv_jobname iv_jobcount = lv_jobcount ).
* Submit Program
SUBMIT (sy-repid) AND RETURN
USER sy-uname
VIA JOB lv_jobname
NUMBER lv_jobcount. "#EC CI_SUBMIT
* to Execute BG job in same application server
DATA(lv_inst) = cl_abap_syst=>get_instance_name( ).
lv_server = lv_inst.
* Close job
CALL FUNCTION 'JOB_CLOSE'
EXPORTING
jobcount = lv_jobcount
jobname = lv_jobname
strtimmed = lv_starttimeimmediate
targetserver = lv_server "++ALEXNA 02/03/2023 DS4K909819
EXCEPTIONS
cant_start_immediate = 01
invalid_startdate = 02
jobname_missing = 03
job_close_failed = 04
job_nosteps = 05
job_notex = 06
lock_failed = 07
OTHERS = 99.
IF sy-subrc = 0.
MESSAGE TEXT-s02 TYPE gc_s DISPLAY LIKE gc_s.
ELSE.
MESSAGE TEXT-e05 TYPE gc_s DISPLAY LIKE gc_e.
ENDIF.
ELSE.
MESSAGE TEXT-e05 TYPE gc_s DISPLAY LIKE gc_e.
ENDIF.
ENDMETHOD.
METHOD insert_shma.
* shma
DATA:lo_area TYPE REF TO ycl_mass_upload_shma_area,
lo_root TYPE REF TO ycl_mass_upload_shma.
* BUild SHAMA area
TRY.
ycl_mass_upload_shma_area=>build( ).
##NO_HANDLER
CATCH cx_shma_not_configured.
##NO_HANDLER
CATCH cx_shm_inconsistent.
##NO_HANDLER
CATCH cx_shm_build_failed.
ENDTRY.
* Block SHMA area for update
TRY.
lo_area = ycl_mass_upload_shma_area=>attach_for_update( ).
##NO_HANDLER
CATCH cx_shm_pending_lock_removed.
##NO_HANDLER
CATCH cx_shm_change_lock_active.
##NO_HANDLER
CATCH cx_shm_version_limit_exceeded.
##NO_HANDLER
CATCH cx_shm_exclusive_lock_active.
##NO_HANDLER
CATCH cx_shm_inconsistent.
##NO_HANDLER
CATCH cx_shm_no_active_version.
ENDTRY.
* get SHMA area root class
lo_root ?= lo_area->get_root( ).
IF lo_root IS INITIAL.
CREATE OBJECT lo_root AREA HANDLE lo_area.
ENDIF.
* store data in SHMA
CALL METHOD lo_root->set_data
EXPORTING
iv_jobname = iv_jobname
iv_jobcount = iv_jobcount
iv_file = p_file
it_mat_upload = gt_mat_upload.
* Set SHMA class data to area
lo_area->set_root( lo_root ).
* Close Area
lo_area->detach_commit( ).
ENDMETHOD.
METHOD read_shma.
##NEEDED
DATA : lv_jobcount TYPE tbtcm-jobcount,
lv_jobcount1 TYPE tbtcm-jobcount,
lv_jobname TYPE tbtcm-jobname,
lv_jobname1 TYPE tbtcm-jobname.
* shma
DATA:lo_area TYPE REF TO ycl_mass_upload_shma_area.
* BG job info
CALL FUNCTION 'GET_JOB_RUNTIME_INFO'
IMPORTING
jobcount = lv_jobcount
jobname = lv_jobname
EXCEPTIONS
no_runtime_info = 1
OTHERS = 2.
IF sy-subrc = 0.
* Bloack SHMA for read
TRY.
lo_area = ycl_mass_upload_shma_area=>attach_for_read( ).
##NO_HANDLER
CATCH cx_shm_pending_lock_removed.
##NO_HANDLER
CATCH cx_shm_change_lock_active.
##NO_HANDLER
CATCH cx_shm_read_lock_active.
##NO_HANDLER
CATCH cx_shm_version_limit_exceeded.
##NO_HANDLER
CATCH cx_shm_exclusive_lock_active.
##NO_HANDLER
CATCH cx_shm_inconsistent.
##NO_HANDLER
CATCH cx_shm_no_active_version.
ENDTRY.
* get SHMA root class data
lo_area->root->get_data(
IMPORTING
ev_jobname = lv_jobname1
ev_jobcount = lv_jobcount1
ev_file = p_file
et_mat_upload = gt_mat_upload ).
* CLose SHAM area
lo_area->detach( ).
ENDIF.
ENDMETHOD.
ENDCLASS.
Code Block – Local Class Methods Implementation
Methods:
- GET_BROWSE_FILE – F4 Help for Selection for Field File path
- GET_EXCEL_DATA – Get Data from Excel file in to internal Object using standard class CL_FDT_XL_SPREADSHEET.
- GET_MATERIAL – Fill Internal Table from Internal Object
- VALIDATE_UPDATE_MAT – Validate Internal table and store success/error msg in ALV and update records in custom table ‘YMASS_UPLOAD’
- DISPLAY_ALV – Display ALV using SALV.
- UPDATE_ALV_FC – Update columns which needs to be displayed in ALV
- UPDATE_ALV_HEADER – Updates ALV header with Upload details like total records. Success & Error records count.
- SCHEDULE_JOB – Schedule a Background job when background mode is selected.
- INSERT_SHMA – Inserts internal table data from Excel to SHMA.
- READ_SHMA – Fills internal table data from SHMA which was stored using above method.
Note: While Scheduling a job we will fetch the current server name to run that job in current server, as SHMA data can be read only in current server. Hence Scheduling Job in same Server.
Text Elements used in the Program:
- 001- Select upload file
- 002- Status
- 003- ID
- 004- Description
- 005- Quantity
- 006- Unit
- 007- Message
- 008- Program Result
- 009- ___________________________________________________________________________________________________________________________________
- 010- Total:
- 011- User:
- 012- Success:
- 013- Date:
- 014- Error:
- 015- Time:
- E01- File path specified is not valid. Please input valid file and execute again
- E02- Missing Material ID
- E03- Entry already present for ID in table
- E04- Duplicate ID
- E05- Error while scheduling Background Job
- S01- Record updated Successfully
- S02- Background Job Scheduled Successfully
- T01- Input Criteria
- T02- File Path:
- T03- Execute in Background
Now we are ready with both ALV upload program and Custom Shared Memory Area.