In this blog post I describe a flexible program to upload historic appraisal documents from Excel to SAP.
For example, lets upload 3 records for this template
Create XLS file with data.
First row - names of columns
Second row - for matching data. You need to write type and element's code in each column with data.
Uploading data are from third row. It is possible to upload 65000 documents. Number of document elements - 100.
First 5 columns of data are reserved for document name, document period, appraiser and appraisee personnel number.
Choose file path and run.
Results in transaction PHAP_ADMIN.
Algorithm:
1. Read data from XLS.
2. Preparing/matching data.
3. Creating template
4. Saving document
The source code:
REPORT ZYS_APR_UPLOAD.
PARAMETER fpath TYPE text200 ." OBLIGATORY
DATA: g_file TYPE filetable,
gs_file(1024),
g_filename LIKE rlgrap-filename ,"TYPE string,
g_rc TYPE i,
rec_n type i,
it_xls TYPE TABLE OF ALSMEX_TABLINE,
wa_xls TYPE zbhr_st_alsmex_tabline,
it_prepare TYPE STANDARD TABLE OF zys_upload,
wa_prepare TYPE zys_upload,
ROW_IID type HAP_ROW_IID,
COLUMN_IID type HAP_COLUMN_IID.
"DATA for HAP documents
DATA:
g_template_id TYPE hap_template_id,
g_header_defaulting TYPE flag,
gs_return TYPE bal_s_msg,
gs_menu TYPE hap_s_menu, "for status
gs_appraisal_id TYPE hap_s_appraisal_id,
gs_doc_processing TYPE hap_s_doc_processing,
gs_header_texts TYPE hap_s_header_texts,
gs_header_status TYPE hap_s_header_status,
gs_header_dates TYPE hap_s_header_dates,
gt_header_add_data TYPE hap_t_header_add_data,
gs_header_display TYPE hap_s_header_display,
gt_header_appraiser TYPE hap_t_header_appraiser WITH HEADER LINE,
gt_header_appraisee TYPE hap_t_header_appraisee WITH HEADER LINE,
gt_header_p_appraiser TYPE hap_t_header_part_appraisers WITH HEADER LINE,
gt_header_others TYPE hap_t_header_others,
gt_buttons TYPE hap_t_buttons,
gt_body_columns TYPE hap_t_body_columns,
gs_body_columns LIKE LINE of gt_body_columns,
gt_body_elements TYPE hap_t_body_elements,
gs_body_elements LIKE LINE of gt_body_elements,
gt_body_element_descr TYPE hap_t_body_element_descr,
gt_body_element_buttons TYPE hap_t_body_element_buttons,
gt_body_cells TYPE hap_t_body_cells,
gs_body_cells like line of gt_body_cells,
gt_body_cell_val_values TYPE hap_t_body_cell_val_values,
gt_body_cell_val_ranges TYPE hap_t_body_cell_val_ranges,
gt_body_cell_val_c_like TYPE hap_t_body_cell_val_c_like,
gt_body_cell_val_descr TYPE hap_t_body_cell_val_descr,
gt_body_cell_notes TYPE hap_t_body_cell_notes,
ls_header_appraiser TYPE hap_s_header_appraiser,
ls_header_appraisee TYPE hap_s_header_appraisee.
FIELD-SYMBOLS: <body_cells> like gs_body_cells.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR fpath.
REFRESH g_file.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
file_filter = cl_gui_frontend_services=>filetype_excel
window_title = 'File'
CHANGING
file_table = g_file
rc = g_rc.
READ TABLE g_file INTO gs_file INDEX 1.
g_filename = fpath = gs_file.
START-OF-SELECTION.
PERFORM excel_read.
PERFORM prepare.
PERFORM upload.
FORM excel_read.
DATA: x1 TYPE i VALUE 1,
y1 TYPE i VALUE 1,
x2 TYPE i VALUE 100,
y2 TYPE i VALUE 65000.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = g_filename
I_BEGIN_COL = x1
I_BEGIN_ROW = y1
I_END_COL = x2
I_END_ROW = y2
TABLES
INTERN = it_xls.
CHECK: sy-subrc EQ 0.
DELETE it_xls WHERE row EQ '0001'.
ENDFORM.
FORM PREPARE.
LOOP AT it_xls into wa_xls.
wa_prepare-row = wa_xls-row .
wa_prepare-column = wa_xls-col .
wa_prepare-value = wa_xls-VALUE.
APPEND wa_prepare TO it_prepare.
ENDLOOP.
rec_n = wa_prepare-row.
LOOP AT it_prepare into wa_prepare
WHERE ROW = '00002'
AND column > 6.
wa_prepare-ELEMENT_TYPE = wa_prepare-value+0(2).
wa_prepare-ELEMENT_ID = wa_prepare-value+2(8).
MODIFY it_prepare FROM wa_prepare TRANSPORTING ELEMENT_TYPE ELEMENT_ID
WHERE column = wa_prepare-column.
ENDLOOP.
ENDFORM.
FORM UPLOAD.
DATA: COUNTER(5) type n VALUE '00003',
lv_template_id TYPE objektid,
date type sy-datum,
lv_pernr TYPE pernr_d,
s_return TYPE bal_s_msg,
lt_status_notes TYPE hap_t_status_note.
WHILE counter <= rec_n.
"Making apraisal document header
"Get TEMPLATE ID
READ TABLE it_prepare INTO wa_prepare
WITH KEY row = counter column = '0001'.
lv_template_id = wa_prepare-value.
"Get template.
PERFORM get_template USING lv_template_id.
"APPRAISAL name
READ TABLE it_prepare INTO wa_prepare
WITH KEY row = counter column = '0002'.
gs_header_texts-appraisal_name = wa_prepare-value.
"begda
READ TABLE it_prepare INTO wa_prepare
WITH KEY row = counter column = '0003'.
CONCATENATE wa_prepare-value+6(4) wa_prepare-value+3(2) wa_prepare-value+0(2) INTO date.
gs_header_dates-ap_start_date = date.
"endda
READ TABLE it_prepare INTO wa_prepare
WITH KEY row = counter column = '0004'.
CONCATENATE wa_prepare-value+6(4) wa_prepare-value+3(2) wa_prepare-value+0(2) INTO date.
gs_header_dates-ap_end_date = date.
gs_header_dates-ap_date_set = date.
"APPRAISER
FREE gt_header_appraiser.
READ TABLE it_prepare INTO wa_prepare
WITH KEY row = counter column = '0005'.
ls_header_appraiser-plan_version = '01'.
ls_header_appraiser-type = 'P'.
lv_pernr = wa_prepare-value.
ls_header_appraiser-id = lv_pernr.
APPEND ls_header_appraiser TO gt_header_appraiser .
"APPRAISEE
FREE gt_header_appraisee.
READ TABLE it_prepare INTO wa_prepare
WITH KEY row = counter column = '0006'.
ls_header_appraisee-plan_version = '01'.
ls_header_appraisee-type = 'P'.
lv_pernr = wa_prepare-value.
ls_header_appraisee-id = lv_pernr.
APPEND ls_header_appraisee TO gt_header_appraisee .
"Making apraisal document body
LOOP AT it_prepare INTO wa_prepare
WHERE ELEMENT_ID ne ''
AND row = counter.
"Get row
READ TABLE gt_body_elements into gs_body_elements
WITH KEY element_type = wa_prepare-element_type
element_id = wa_prepare-element_id.
row_iid = gs_body_elements-row_iid.
"Get FAPP Column.
READ TABLE gt_body_columns into gs_body_columns
WITH KEY column_id = 'FAPP'.
column_iid = gs_body_columns-column_iid.
"Write element
READ TABLE gt_body_cells ASSIGNING <body_cells>
WITH KEY row_iid = row_iid
column_iid = column_iid.
<body_cells>-no_value = ''.
<body_cells>-value_num = wa_prepare-value.
<body_cells>-value_txt = wa_prepare-value.
ENDLOOP.
"Saving document
CALL FUNCTION 'HRHAP_DOC_UPDATE_BODY_AND_SAVE'
EXPORTING
plan_version = '01'
IMPORTING
s_return = s_return
CHANGING
s_appraisal_id = gs_appraisal_id
s_doc_processing = gs_doc_processing
t_header_appraiser = gt_header_appraiser[]
t_header_appraisee = gt_header_appraisee[]
t_header_part_appraisers = gt_header_p_appraiser[]
t_header_others = gt_header_others
s_header_texts = gs_header_texts
s_header_dates = gs_header_dates
s_header_status = gs_header_status
s_header_display = gs_header_display
t_body_columns = gt_body_columns
t_body_elements = gt_body_elements
t_body_cells = gt_body_cells[]
t_body_cell_notes = gt_body_cell_notes
t_status_notes = lt_status_notes.
IF s_return IS NOT INITIAL .
WRITE: / s_return-msgid , s_return-msgty ,s_return-msgno ,s_return-msgv1, s_return-msgv2, s_return-msgv3 .
ENDIF.
counter = counter + 1.
ENDWHILE.
ENDFORM.
FORM get_template USING template.
"Make document from template
FREE : gs_return.
CALL FUNCTION 'HRHAP_TEMPLATE_GET_DETAIL'
EXPORTING " add_on_application = 'LSO'
" UI_MODE = 'X'
* ADMINISTRATOR = ' '
plan_version = '01'
template_id = template
* S_VALIDITY_PERIOD =
* S_DISPLAY_UI =
IMPORTING
s_appraisal_id = gs_appraisal_id
s_header_texts = gs_header_texts
s_header_status = gs_header_status
t_header_add_data = gt_header_add_data
s_header_display = gs_header_display
t_buttons = gt_buttons
t_body_columns = gt_body_columns
t_body_elements = gt_body_elements
t_body_element_descr = gt_body_element_descr
t_body_element_buttons = gt_body_element_buttons
t_body_cells = gt_body_cells
t_body_cell_val_values = gt_body_cell_val_values
t_body_cell_val_ranges = gt_body_cell_val_ranges
t_body_cell_val_c_like = gt_body_cell_val_c_like
t_body_cell_val_descr = gt_body_cell_val_descr
t_body_cell_notes = gt_body_cell_notes
s_return = gs_return
CHANGING
s_doc_processing = gs_doc_processing
t_header_appraiser = gt_header_appraiser[]
t_header_appraisee = gt_header_appraisee[]
t_header_part_appraisers = gt_header_p_appraiser[]
t_header_others = gt_header_others
s_header_dates = gs_header_dates.
gs_header_status-ap_status = '5'.
ENDFORM. " GET_TEMPLATES
- SAP Managed Tags:
5 Comments
