Uploading Appraisal documents from Excel to SAP

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