DESCRIPTION: BDC program for upload demand (BATCH SESSION - MD11) Using excel sheet for data upload

YOURCODE: *&---------------------------------------------------------------------* *& Report ZTBPP102 * *& * *&---------------------------------------------------------------------* *& Title: BDC program for upload demand (BATCH SESSION - MD11) *& * *&---------------------------------------------------------------------* * Essential Steps : * 1. Read the data from excel file into ABAP internal table using * function module ALSM_EXCEL_TO_INTERNAL_TABLE * 2. Store the data in internal table and do the validation * 3. For valid data create a Batch session (MD11) * Insert data from the internal table into the batch * 4. Once the batch is created the user needs to process the batch * by going through SM35 *----------------------------------------------------------------------* REPORT ztbpp102. * ------------------- Start of Data Declaration -----------------------* * Constants CONSTANTS: c_begcol TYPE i VALUE 1, c_begrow TYPE i VALUE 1, c_endcol TYPE i VALUE 100, c_endrow TYPE i VALUE 32000, c_x(1) TYPE c VALUE 'X'. DATA : w_field(30), * Flag for data check status w_invalid_data(1) TYPE c. FIELD-SYMBOLS : <fs> TYPE ANY. * Internal Tables * The prefix 000n is becuase the colmn in alsmex_tabline isof data * type n withg length 4 TYPES: BEGIN OF ty_tab, cell_0001 TYPE alsmex_tabline-value, "Plant cell_0002 TYPE alsmex_tabline-value, "Material No

cell_0003 TYPE alsmex_tabline-value, cell_0004 TYPE alsmex_tabline-value, cell_0005 TYPE alsmex_tabline-value, cell_0006 TYPE alsmex_tabline-value, END OF ty_tab. TYPES: BEGIN OF ty_error, line TYPE string, END OF ty_error. TYPES: BEGIN OF ty_mara, matnr TYPE mara-matnr, END OF ty_mara. TYPES: BEGIN OF ty_t001w, werks TYPE t001w-werks, END OF ty_t001w.

"Order Start Date "Order finish Date "Quantity "Quantity unit DV1K905068

DATA: it_upload_data LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE, it_tab TYPE STANDARD TABLE OF ty_tab, wa_tab TYPE ty_tab, * Internal table for BDC it_bdcdata TYPE STANDARD TABLE OF bdcdata, *work area wa_bdcdata TYPE bdcdata, * Internal table to store error it_error TYPE STANDARD TABLE OF ty_error, wa_error TYPE ty_error, it_mara TYPE STANDARD TABLE OF ty_mara, wa_mara TYPE ty_mara, it_t001w TYPE STANDARD TABLE OF ty_t001w, wa_t001w TYPE ty_t001w. * ------------------- End of Data Declaration -----------------------* *-----------Start of Selection Screen -----------------------* SELECTION-SCREEN: BEGIN OF BLOCK b1 WITH FRAME TITLE text-001. PARAMETERS: p_file LIKE rlgrap-filename MEMORY ID m02, p_sesson TYPE apqi-groupid DEFAULT 'DMND_UPLOAD'. SELECTION-SCREEN: END OF BLOCK b1. *-----------End of Selection Screen -------------------------*

* Event INITIALIZATION INITIALIZATION. CLEAR: w_invalid_data, w_field. REFRESH: it_upload_data, it_tab, it_error, it_bdcdata, it_mara, it_t001w. *-------------- Start of at selection-screen -------------------------* AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file. * Show the file selection dialog box CALL FUNCTION 'KD_GET_FILENAME_ON_F4' EXPORTING mask = '(*.xls)|*.xls|(*.*)|*.*' "For excel static = 'X' CHANGING file_name = p_file EXCEPTIONS mask_too_long = 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. *--------------- End of at Selection-screen ---------------------------* * START-OF-SELECTION START-OF-SELECTION. * Upload the excel data into the program PERFORM upload_file. * Populate data in internal table it_tab from excel upload data PERFORM populate_it_tab_from_excel. * Check the mandatory fields PERFORM check_data. * When the dataset is valid proceed IF w_invalid_data IS INITIAL. * Open the BDC session PERFORM open_group. * Populate BDC table PERFORM populate_bdc_session.

* Close the BDC session PERFORM close_group. WRITE:/ 'Batch input session '(m01), p_sesson, ' created successfully'(m02). WRITE:/ 'Please process the session using transaction SM35'(m03). ELSE. WRITE:/ 'Error in Input Data :'(m04). SKIP 1. LOOP AT it_error INTO wa_error. WRITE:/ wa_error-line. ENDLOOP. SKIP 1. WRITE: 'Please correct error and try to upload again'(m05). ENDIF. *&---------------------------------------------------------------------* *& Form UPLOAD_FILE *&---------------------------------------------------------------------* * Upload the data from the excel file into ABAP internal table *----------------------------------------------------------------------* FORM upload_file . * Upload the excel file in ABAP internal table CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE' EXPORTING filename = p_file i_begin_col = c_begcol i_begin_row = c_begrow i_end_col = c_endcol i_end_row = c_endrow TABLES intern = it_upload_data EXCEPTIONS inconsistent_parameters = 1 upload_ole =2 OTHERS = 3. IF sy-subrc <> 0. MESSAGE i999(zttl) WITH 'Error in uploading the file!'(m06). LEAVE LIST-PROCESSING. ENDIF. ENDFORM. " UPLOAD_FILE *&---------------------------------------------------------------------*

*& Form populate_it_tab_from_excel *&---------------------------------------------------------------------* * Populate data in internal table it_tab from excel upload data *----------------------------------------------------------------------* FORM populate_it_tab_from_excel . DATA: l_col_no TYPE alsmex_tabline-col. * Internal table it_upload_data store data in cellwise * with row, colmn and value * Change this data to a row-wise data and populate the table it_tab SORT it_upload_data BY row col. * Delete the entries with row 0001 as those row has the colmn headings DELETE it_upload_data WHERE row = '0001'. * Convert the excel data to row-wise data LOOP AT it_upload_data. l_col_no = it_upload_data-col. CONCATENATE 'WA_TAB-cell_' l_col_no INTO w_field. ASSIGN (w_field) TO <fs>. IF sy-subrc = 0. <fs> = it_upload_data-value. ENDIF. AT END OF row. APPEND wa_tab TO it_tab. CLEAR wa_tab. ENDAT. ENDLOOP. IF it_tab[] IS INITIAL. MESSAGE i999(zttl) WITH 'No record to upload!'(m07). LEAVE LIST-PROCESSING. ENDIF. ENDFORM. " populate_it_tab_from_excel *&---------------------------------------------------------------------* *& Form check_data *&---------------------------------------------------------------------* * Do data validation *----------------------------------------------------------------------* FORM check_data . DATA: l_error TYPE string, l_line_no(4) TYPE c, l_it_tab_temp TYPE STANDARD TABLE OF ty_tab,

l_it_mara TYPE STANDARD TABLE OF ty_mara, l_it_t001w TYPE STANDARD TABLE OF ty_t001w, l_gsmng TYPE plaf-gsmng, l_matnr TYPE plaf-matnr. * Select plant data from T001w * Cell_0001 is for plant IF NOT it_tab[] IS INITIAL. REFRESH l_it_tab_temp. l_it_tab_temp[] = it_tab[]. SORT l_it_tab_temp BY cell_0001. DELETE ADJACENT DUPLICATES FROM l_it_tab_temp COMPARING cell_0001. LOOP AT l_it_tab_temp INTO wa_tab. wa_t001w-werks = wa_tab-cell_0001. TRANSLATE wa_t001w-werks TO UPPER CASE. "#EC TRANSLANG APPEND wa_t001w TO l_it_t001w. ENDLOOP. IF NOT l_it_t001w[] IS INITIAL. SELECT werks FROM t001w INTO TABLE it_t001w FOR ALL ENTRIES IN l_it_t001w WHERE werks = l_it_t001w-werks. IF sy-subrc = 0. SORT it_t001w BY werks. ENDIF. ENDIF. ENDIF. *Select data from Material master IF NOT it_tab[] IS INITIAL. REFRESH l_it_tab_temp. l_it_tab_temp[] = it_tab[]. * Cell 0002 is for material no SORT l_it_tab_temp BY cell_0002. DELETE ADJACENT DUPLICATES FROM l_it_tab_temp COMPARING cell_0002. LOOP AT l_it_tab_temp INTO wa_tab. * call a conversion exit

CLEAR l_matnr. CALL FUNCTION 'CONVERSION_EXIT_MATN1_OUTPUT' EXPORTING input = wa_tab-cell_0002 IMPORTING output = l_matnr. TRANSLATE l_matnr TO UPPER CASE. wa_mara-matnr = l_matnr. APPEND wa_mara TO l_it_mara. ENDLOOP. IF NOT l_it_mara[] IS INITIAL. SELECT matnr FROM mara INTO TABLE it_mara FOR ALL ENTRIES IN l_it_mara WHERE matnr = l_it_mara-matnr. IF sy-subrc = 0. SORT it_mara BY matnr. ENDIF. ENDIF. ENDIF. "#EC TRANSLANG

*Check on mandatory field LOOP AT it_tab INTO wa_tab. l_line_no = sy-tabix. * Increase the line no by 1 as the first row (have the heading) * is not considered l_line_no = l_line_no + 1.

* Check for plant (cell_0001) IF wa_tab-cell_0001 IS INITIAL. CLEAR: l_error, wa_error. CONCATENATE text-m08 l_line_no ': Plant is mandatory'(m13) INTO l_error SEPARATED BY space. wa_error-line = l_error. APPEND wa_error TO it_error. w_invalid_data = c_x. ELSE.

* Check for valid plant CLEAR wa_t001w. TRANSLATE wa_tab-cell_0001 TO UPPER CASE. "#EC TRANSLANG READ TABLE it_t001w INTO wa_t001w WITH KEY werks = wa_tab-cell_0001 BINARY SEARCH. IF sy-subrc <> 0. CLEAR: l_error, wa_error. CONCATENATE text-m08 l_line_no ': Invalid plant '(m14) wa_tab-cell_0001 INTO l_error SEPARATED BY space. wa_error-line = l_error. APPEND wa_error TO it_error. w_invalid_data = c_x. ENDIF. ENDIF. * Check for material number (Cell_0002) IF wa_tab-cell_0002 IS INITIAL. CLEAR: l_error, wa_error. CONCATENATE 'Line'(m08) l_line_no ': Material no is mandatory'(m11) INTO l_error SEPARATED BY space. wa_error-line = l_error. APPEND wa_error TO it_error. w_invalid_data = c_x. ELSE. * Check for valid material CLEAR wa_mara. TRANSLATE wa_tab-cell_0002 TO UPPER CASE. "#EC TRANSLANG READ TABLE it_mara INTO wa_mara WITH KEY matnr = wa_tab-cell_0002 BINARY SEARCH. IF sy-subrc <> 0. CLEAR: l_error, wa_error. CONCATENATE text-m08 l_line_no ': Invalid Material no'(m12) wa_tab-cell_0002 INTO l_error SEPARATED BY space. wa_error-line = l_error. APPEND wa_error TO it_error. w_invalid_data = c_x. ENDIF. ENDIF.

* Check for quantity (cell_0005) IF wa_tab-cell_0005 IS INITIAL. CLEAR: l_error, wa_error. CONCATENATE text-m08 l_line_no ': Order quantity is mandatory'(m21) INTO l_error SEPARATED BY space. wa_error-line = l_error. APPEND wa_error TO it_error. w_invalid_data = c_x. ELSE. * Check that quantity is a number CLEAR: l_gsmng. CATCH SYSTEM-EXCEPTIONS conversion_errors = 1. l_gsmng = wa_tab-cell_0005. IF l_gsmng = 0. CLEAR: l_error, wa_error. CONCATENATE 'Line'(m08) l_line_no ': Quantity cannot be zero'(m22) INTO l_error SEPARATED BY space. wa_error-line = l_error. APPEND wa_error TO it_error. w_invalid_data = c_x. ENDIF. ENDCATCH. * If there is a conversion error IF sy-subrc = 1. CLEAR: l_error, wa_error. CONCATENATE 'Line'(m08) l_line_no ': Quantity'(m23) wa_tab-cell_0005 'is not a number'(m24) INTO l_error SEPARATED BY space. wa_error-line = l_error. APPEND wa_error TO it_error. w_invalid_data = c_x. ENDIF. ENDIF. * Start of insert DV1K905068 IF wa_tab-cell_0006 IS INITIAL. CLEAR: l_error, wa_error. CONCATENATE text-m08 l_line_no ': Quantity unit is mandatory'(m25) INTO l_error SEPARATED BY space. wa_error-line = l_error. APPEND wa_error TO it_error. w_invalid_data = c_x. ENDIF.

* End of insert DV1K905068 * Order finish date is mandatory (Cell_0004) IF wa_tab-cell_0004 IS INITIAL. CLEAR: l_error, wa_error. CONCATENATE text-m08 l_line_no ': Order finish date is mandatory'(m15) INTO l_error SEPARATED BY space. wa_error-line = l_error. APPEND wa_error TO it_error. w_invalid_data = c_x. ENDIF. ENDLOOP. ENDFORM. " check_data *&---------------------------------------------------------------------* *& Form open_group *&---------------------------------------------------------------------* * Open the BDC session *----------------------------------------------------------------------* FORM open_group . CALL FUNCTION 'BDC_OPEN_GROUP' EXPORTING client = sy-mandt group = p_sesson keep = c_x user = sy-uname EXCEPTIONS client_invalid =1 destination_invalid = 2 group_invalid =3 group_is_locked = 4 holddate_invalid = 5 internal_error =6 queue_error =7 running =8 system_lock_error = 9 user_invalid = 10 OTHERS = 11. IF sy-subrc <> 0. MESSAGE i999(zttl) WITH 'Error in creating session'(m16). LEAVE LIST-PROCESSING. ENDIF. ENDFORM. " open_group

*&---------------------------------------------------------------------* *& Form close_group *&---------------------------------------------------------------------* * Close the BDC session *----------------------------------------------------------------------* FORM close_group . CALL FUNCTION 'BDC_CLOSE_GROUP' EXCEPTIONS not_open = 1 queue_error = 2 OTHERS = 3. IF sy-subrc <> 0. MESSAGE i999(zttl) WITH 'Error in closing session'(m17). LEAVE LIST-PROCESSING. ENDIF. ENDFORM. " close_group *&---------------------------------------------------------------------* *& Form populate_bdc_session *&---------------------------------------------------------------------* * Populate the BDC table *----------------------------------------------------------------------* FORM populate_bdc_session . DATA: l_gsmng TYPE plaf-gsmng, l_meins_in TYPE mara-meins, l_matnr TYPE mara-matnr, l_string_gsmng TYPE string. "DV1K905068 "DV1K905068 "DV1K905068 "DV1K905068

LOOP AT it_tab INTO wa_tab. REFRESH it_bdcdata. CLEAR: wa_bdcdata. * First screen of transaction MD111 PERFORM bdc_dynpro USING 'SAPMM61P' '0100'. * Data fiels in screen 0100 PERFORM bdc_field USING 'BDC_CURSOR' 'RM61P-PASCH'. PERFORM bdc_field USING 'BDC_OKCODE' '/00'. * Planned order profile is always LA PERFORM bdc_field USING 'RM61P-PASCH' 'LA'. * Screen 2 PERFORM bdc_dynpro USING 'SAPLM61O' '0110'.

* Data fiels in screen 0110 * Start of change DV1K904978 * PERFORM bdc_field USING 'BDC_OKCODE' '=TERM'. PERFORM bdc_field USING 'BDC_OKCODE' '/00'. * End of change DV1K904978 * Material no PERFORM bdc_field USING 'PLAF-MATNR' wa_tab-cell_0002. * Plant PERFORM bdc_field USING 'PLAF-PLWRK' wa_tab-cell_0001. PERFORM bdc_field USING 'BDC_SUBSCR' 'SAPLM61O 0711INCLUDE1XX'. PERFORM bdc_field USING 'BDC_SUBSCR' 'SAPLM61O 0802INCLUDE711_1'. PERFORM bdc_field USING 'BDC_CURSOR' 'PLAF-PEDTR'. * Strat of change DV1K905068 * Do a material unit conversion from given unit to kg * Use local variable as the fn module needs variable of * type meins CLEAR: l_meins_in, l_matnr, l_gsmng, l_string_gsmng . l_meins_in = wa_tab-cell_0006. l_matnr = wa_tab-cell_0002. * Convert unit text to upper case TRANSLATE l_matnr TO UPPER CASE. TRANSLATE l_meins_in TO UPPER CASE. "#EC TRANSLANG "#EC TRANSLANG

* Conversion exit on unit CALL FUNCTION 'CONVERSION_EXIT_CUNIT_INPUT' EXPORTING input = l_meins_in language = sy-langu IMPORTING output = l_meins_in EXCEPTIONS unit_not_found = 1 OTHERS = 2. IF sy-subrc <> 0. WRITE:/ 'Unit not maintained ', l_meins_in. CONTINUE. ENDIF. * Converting the amount

CATCH SYSTEM-EXCEPTIONS conversion_errors = 1. l_gsmng = wa_tab-cell_0005. ENDCATCH. IF sy-subrc <> 0. WRITE:/ 'Quantity is not in proper format ', wa_tab-cell_0005. CONTINUE. ENDIF. CALL FUNCTION 'MD_CONVERT_MATERIAL_UNIT' EXPORTING i_matnr = l_matnr i_in_me = l_meins_in i_out_me = 'KG' i_menge = l_gsmng IMPORTING e_menge = l_gsmng EXCEPTIONS error_in_application = 1 error =2 OTHERS = 3. IF sy-subrc <> 0. WRITE:/ 'Material Unit conversion not maintained for ', wa_tab-cell_0002. CONTINUE. ELSE. * Assign to straig type variable as gsmng is packed and cannot be appended * directly to the BDC session l_string_gsmng = l_gsmng. ENDIF. * Quantity * PERFORM bdc_field USING 'PLAF-GSMNG' wa_tab-cell_0005. PERFORM bdc_field USING 'PLAF-GSMNG' l_string_gsmng. * End of change DV1K905068 * Order finish date PERFORM bdc_field USING 'PLAF-PEDTR' wa_tab-cell_0004. * Order start date PERFORM bdc_field USING 'PLAF-PSTTR' wa_tab-cell_0003. PERFORM bdc_field USING 'BDC_SUBSCR' 'SAPLM61O 0810INCLUDE711_2'.

* Screen 3 PERFORM bdc_dynpro USING 'SAPLM61O' '0110'. * Data fiels in screen 0110 PERFORM bdc_field USING 'BDC_CURSOR' 'PLAF-MATNR'. PERFORM bdc_field USING 'BDC_OKCODE' 'HZPL'. PERFORM bdc_field USING 'BDC_SUBSCR' 'SAPLM61O 0715INCLUDE1XX'. *Transfer data to batch input by transaction CALL FUNCTION 'BDC_INSERT' EXPORTING tcode = 'MD11' TABLES dynprotab = it_bdcdata EXCEPTIONS internal_error = 1 not_open =2 queue_error = 3 tcode_invalid = 4 OTHERS = 5. IF sy-subrc <> 0. MESSAGE i999(zttl) WITH 'Error in creating session'(m20). LEAVE LIST-PROCESSING. ENDIF. ENDLOOP. ENDFORM. " populate_bdc_session *&---------------------------------------------------------------------* *& Form bdc_dynpro *&---------------------------------------------------------------------* * Populate the dynpros *----------------------------------------------------------------------* * -->P_pogram Progran name * -->P_dynpro Screen no *----------------------------------------------------------------------* FORM bdc_dynpro USING p_program TYPE bdc_prog p_dynpro TYPE bdc_dynr. CLEAR wa_bdcdata. wa_bdcdata-program = p_program. wa_bdcdata-dynpro = p_dynpro. wa_bdcdata-dynbegin = c_x.

APPEND wa_bdcdata TO it_bdcdata. ENDFORM. " bdc_dynpro *&---------------------------------------------------------------------* *& Form bdc_field *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * -->P_fnam Field name * -->P_fval Field value *----------------------------------------------------------------------* FORM bdc_field USING p_fnam TYPE fnam_____4 p_fval. CLEAR wa_bdcdata. wa_bdcdata-fnam = p_fnam. wa_bdcdata-fval = p_fval. APPEND wa_bdcdata TO it_bdcdata. ENDFORM. " bdc_field