Thursday, October 8, 2015

Transferring data between systems in SAP

Sometimes you find the need to transfer the data from a table in one client to another client or another system.  E.g. when you've built a Z table and you've copied production down to the test environment or you might want to keep a copy of the table's contents before you make some change in case you mess things up and want to restore back to the old copy.

The program below will copy the data down to a text file and will then read that text file to reload the table again. Obviously there are a few caveats;

  • Table structures between the two systems must be completely identical.
  • Default parameters for things like date formats need to be identical between the two systems.
The program has been left with a deliberate compile bug in it at the point where you should put some kind of appropriate authorization check in place to prevent people from uploading junk into your system.

When you run it in download mode, the program will construct a second selection screen with all the key fields from the table available to narrow the selection down.

When you run it in upload mode it will ask you to overwrite existing records or skip them.

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

*& Report  ZDATA_TRANSFER
*&
*&---------------------------------------------------------------------*
*& Transfer data between different systems. The table structure obviously
*& has to be identical between the two systems.
*& Mark Langenhoven
*& 2014/10/22
*&---------------------------------------------------------------------*

report zdata_transfer.

*--- Data declarations ----------------------------------------------
data: gt_fcat      type lvc_t_fcat.
data: begin of coderec,
        code(200) type c,
      end of coderec.


*--- Selection screen -----------------------------------------------
"Direction
selection-screen begin of block b1 with frame title text-dir.
parameters: p_down radiobutton group grp1 default 'X',
            p_up   radiobutton group grp1.
selection-screen end of block b1.

"General selections
selection-screen begin of block b2 with frame title text-gen.
parameters: p_table like tadir-obj_name obligatory.
parameters: p_file like rlgrap-filename obligatory.
selection-screen end of block b2.

"Just here for the upload part of the program
"as it makes coding easier
parameters: p_over no-display.



at selection-screen on value-request for p_file.
  "Pop up a file selector for the user to choose a file
  data: objfile type ref to cl_gui_frontend_services.
  data: lv_rc    type i,
        ls_files type line of filetable,
        lt_files type filetable.

  check sy-batch is initial.

  create object objfile.
  call method cl_gui_frontend_services=>file_open_dialog
    exporting
      window_title            = 'Select file'
      default_extension       = 'TXT'
    changing
      file_table              = lt_files
      rc                      = lv_rc
    exceptions
      file_open_dialog_failed = 1
      cntl_error              = 2
      error_no_gui            = 3
      not_supported_by_gui    = 4
      others                  = 5.
  check sy-subrc = 0.

  read table lt_files into ls_files index 1.
  check sy-subrc = 0.

  p_file = ls_files-filename.


*--- Macros ---------------------------------------------------------
  define add_code.
    lt_code = &1.
    append lt_code.
  end-of-definition.



*=== Main program ===================================================
start-of-selection.

  if p_down = abap_true.
    perform download.
  else.
    perform upload.
  endif.


*  write: / 'Done'.

*&---------------------------------------------------------------------*
*&      Form  DOWNLOAD
*&---------------------------------------------------------------------*
* Download a full table into the specified filename
*----------------------------------------------------------------------*
form download .

  perform make_prog using 'DOWNLOAD_MAKER'.

  submit zzload via selection-screen and return.

endform.                    " DOWNLOAD



*&---------------------------------------------------------------------*
*&      Form  UPLOAD
*&---------------------------------------------------------------------*
* Upload data from a file
*----------------------------------------------------------------------*
form upload .
  data lv_parva like usr05-parva.

  "Check the authorization
  Build authorization check in here
  if sy-subrc <> 0 .
    write: / sy-uname, ': NOT authorized to upload data'.
  else.

    perform make_prog using 'UPLOAD_MAKER'.

    submit zzload via selection-screen and return.

*    "Display the newly loaded data
*    set parameter id 'DTB' field p_table.
*    call transaction 'SE16' and skip first screen.

  endif.

endform.                    " UPLOAD


*&---------------------------------------------------------------------*
*&      Form  DOWNLOAD_MAKER
*&---------------------------------------------------------------------*
*& A template that will be copied into the ZZLOAD program with the
*& correct table name hardcoded into it.
*&---------------------------------------------------------------------*
form download_maker.

  data: gt_data      like sflight occurs 0 with header line,
        ls_data      like sflight,
        lt_down(200) occurs 0 with header line,
        lt_dd03l     like dd03l occurs 0 with header line.

  field-symbols: <wa> type any.
  "SFLIGHT will be replaced with the real table name
  select * from sflight into table gt_data. "add keys here

  select * from dd03l into table lt_dd03l where tabname = p_table.
  sort lt_dd03l by position.

  refresh lt_down.

  "Build in the table name as a safety check
  lt_down = p_table.
  append lt_down.

  loop at gt_data into ls_data.
    loop at lt_dd03l.
      assign component lt_dd03l-fieldname of structure ls_data to <wa>.
      lt_down = <wa>.
      append lt_down.
    endloop.
  endloop.

  describe table gt_data.
  write: / 'Number of records downloaded', sy-tfill.

  check lt_down[] is not initial.
  call function 'WS_DOWNLOAD'
    exporting
      filename                = p_file
    tables
      data_tab                = lt_down
    exceptions
      file_open_error         = 1
      file_write_error        = 2
      invalid_filesize        = 3
      invalid_type            = 4
      no_batch                = 5
      unknown_error           = 6
      invalid_table_width     = 7
      gui_refuse_filetransfer = 8
      customer_error          = 9
      no_authority            = 10
      others                  = 11.
  if sy-subrc <> 0.
* Implement suitable error handling here
  endif.


endform.                    " DOWNLOAD_MAKER

*&---------------------------------------------------------------------*
*&      Form  UPLOAD_MAKER
*&---------------------------------------------------------------------*
*& A template to be copied into the ZZLOAD program
*&---------------------------------------------------------------------*
form upload_maker.

  data: lt_data(200) occurs 0 with header line,
        ls_data(200),
        lv_count     type i,
        ls_up        like sflight,
        ls_tmp       like sflight,
        lv_pos       like dd03l-position,
        lt_dd03l     like dd03l occurs 0 with header line.

  field-symbols <wa> type any.

  call function 'WS_UPLOAD'
    exporting
      filename                = p_file
    tables
      data_tab                = lt_data
    exceptions
      conversion_error        = 1
      file_open_error         = 2
      file_read_error         = 3
      invalid_type            = 4
      no_batch                = 5
      unknown_error           = 6
      invalid_table_width     = 7
      gui_refuse_filetransfer = 8
      customer_error          = 9
      no_authority            = 10
      others                  = 11.
  if sy-subrc <> 0.
    exit.
  endif.

  check lt_data[] is not initial.

  select * from dd03l into table lt_dd03l where tabname = p_table.
  sort lt_dd03l by position.

  clear lv_count.

  "Read the first line and make sure it's for this table
  read table lt_data into ls_data index 1.
  if ls_data <> p_table.
    message e000(38) with 'File is for table:' ls_data.
    exit.
  endif.

  delete lt_data index 1.

  "Build up the data to be inserted into the table
  lv_pos = 1.
  clear ls_up.
  loop at lt_data into ls_data.
    read table lt_dd03l with key position = lv_pos.
    if sy-subrc <> 0.
      if ls_up is not initial.
        "Update the table
        if p_over = abap_true.
          modify sflight from ls_up.
          lv_count = lv_count + 1.
        else.
          "Make sure this record doesn't already exist
          select single * into ls_tmp from sflight. "add unique
          if sy-subrc <> 0.
            modify sflight from ls_up.
            lv_count = lv_count + 1.
          endif.
        endif.

        commit work.
      endif.
      clear ls_up.
      lv_pos = 1.
      read table lt_dd03l with key position = lv_pos.
    endif.

    "Make sure to override the client so that it's correct for the
    "system we're loading into
    if lt_dd03l-fieldname = 'MANDT'.  "client-specific
      ls_up-mandt = sy-mandt.         "client-specific
    else.                             "client-specific
      assign component lt_dd03l-fieldname of structure ls_up to <wa>.
      <wa> = ls_data.
    endif.                            "client-specific

    lv_pos = lv_pos + 1.
  endloop.

  "Update the last record
  if ls_up is not initial.
    "Update the table
    if p_over = abap_true.
      modify sflight from ls_up.
      lv_count = lv_count + 1.
    else.
      "Make sure this record doesn't already exist
      select single * into ls_tmp from sflight. "add unique
      if sy-subrc <> 0.
        modify sflight from ls_up.
        lv_count = lv_count + 1.
      endif.
    endif.

    commit work.
  endif.


  write: / 'Records updated ', lv_count.


endform.                    " UPLOAD_MAKER

*&---------------------------------------------------------------------*
*&      Form  MAKE_PROG
*&---------------------------------------------------------------------*
* Make a program using on of my performs as a template
*----------------------------------------------------------------------*
form make_prog  using    p_name.

  data: lt_code      like coderec occurs 0 with header line,
        lt_curr      like coderec occurs 0 with header line,
        lt_dd03l     like dd03l occurs 0 with header line,
        ls_code      like coderec,
        lv_table     like coderec,
        lv_file(300),
        lv_name(60),
        ls_data      like zca_data,
        lv_mandt,
        lv_copy.

  add_code 'report zzload.'.

  "For downloads we want to have a selection screen
  if p_name cs 'DOWNLOAD'.
    perform add_selection tables lt_code.
  endif.
  "For uploads we want an overwrite option
  if p_name cs 'UPLOAD'.
    add_code 'selection-screen begin of line.'.
    add_code 'selection-screen comment 1(15) pv_over.'.
    add_code 'parameters: p_over radiobutton group grp1 default ''X''.'.
    add_code 'selection-screen end of line.'.

    add_code 'selection-screen begin of line.'.
    add_code 'selection-screen comment 1(15) pv_skip.'.
    add_code 'parameters: p_skip radiobutton group grp1.'.
    add_code 'selection-screen end of line.'.

    add_code 'initialization.'.
    add_code 'pv_over = ''Overwrite''.'.
    add_code 'pv_skip = ''Skip existing''.'.

  endif.

  add_code 'start-of-selection.'.


  "See if this table is client-specific or not
  select * from dd03l into table lt_dd03l
       where tabname = p_table.
  sort lt_dd03l by position.

  read table lt_dd03l with key fieldname = 'MANDT'.
  if sy-subrc = 0.
    lv_mandt = abap_true.
  else.
    lv_mandt = abap_false.
  endif.

  concatenate 'form ' p_name into lv_name separated by space.
  concatenate lv_name '.' into lv_name.
  translate lv_name to lower case.

  concatenate '''' p_file '''' into lv_file.
  concatenate '''' p_table '''' into lv_table.

  read report sy-repid into lt_curr.

  lv_copy = abap_false.

  loop at lt_curr.
    if lt_curr cs 'endform.'.
      lv_copy = abap_false.
    endif.

    if lv_copy = abap_true.
      replace all occurrences of 'sflight' in lt_curr with p_table.
      replace all occurrences of 'p_table' in lt_curr with lv_table.
      replace all occurrences of 'p_file' in lt_curr with lv_file.


      "Leave out lines for client-specific instances
      if lv_mandt = abap_false and
         lt_curr cs 'client-specific'.
        continue.
      endif.

      "See if we need to build up the selection criteria
      if lt_curr cs p_table and lt_curr cs 'add keys'.
        read table lt_dd03l with key keyflag = abap_true.
        if sy-subrc = 0.
          "Remove the period so we can add the key selections
          translate lt_curr using '. '.
          append lt_curr to lt_code.

          perform key_select tables lt_code.

          continue.
        endif.
      endif.

      if lt_curr cs p_table and lt_curr cs 'add unique'.
        translate lt_curr using '. '.
        append lt_curr to lt_code.

        perform check_exist tables lt_code.
        continue.
      endif.

      append lt_curr to lt_code.
    endif.


    "Decide if we're in the right form or not.
    if lt_curr = lv_name.
      lv_copy = abap_true.
    endif.
  endloop.


  insert report 'ZZLOAD' from lt_code.

*  write: / 'ZZLOAD has been built'.

endform.                    " MAKE_PROG


*&---------------------------------------------------------------------*
*&      Form  ADD_SELECTION
*&---------------------------------------------------------------------*
* Add a selection screen containing the key fields
*----------------------------------------------------------------------*
form add_selection  tables   lt_code structure coderec.

  data: lt_dd03l    like dd03l occurs 0 with header line,
        ls_code     like coderec,
        lv_num(3)   type n,
        lv_text     like dd04t-ddtext,
        lv_tmp(200).

  select * from dd03l into table lt_dd03l
       where tabname = p_table
       and   keyflag = abap_true.

  concatenate 'tables:' p_table '.' into ls_code separated by space.
  add_code ls_code.

  sort lt_dd03l by position.
  delete lt_dd03l where fieldname = 'MANDT'.


  loop at lt_dd03l.
    lv_num = sy-tabix.
    add_code 'selection-screen begin of line.'.
    "Add the description code
    concatenate 'selection-screen comment 1(25) lv_' lv_num '$for field s_' lv_num '.' into lv_tmp.
    translate lv_tmp using '$ '.
    add_code  lv_tmp.

    "Add the actual select-option
    concatenate 's_' lv_num into ls_code.
    concatenate lt_dd03l-tabname '-' lt_dd03l-fieldname into lv_tmp.
    concatenate 'select-options:' ls_code 'for' lv_tmp '.' into ls_code separated by space.
    add_code ls_code.

    add_code 'selection-screen end of line.'.
  endloop.

  add_code ''.

  "Initialize the descriptions
  add_code 'initialization.'.

  loop at lt_dd03l.

    lv_num = sy-tabix.

    select single ddtext from dd04t into lv_text
          where rollname = lt_dd03l-rollname
          and   ddlanguage = sy-langu.
    if sy-subrc <> 0.
      lv_text = lt_dd03l-fieldname.
    endif.

    concatenate 'lv_' lv_num into lv_tmp.

    concatenate lv_tmp '=' '''' lv_text '''' '.' into ls_code separated by space.

    add_code ls_code.
  endloop.
endform.                    " ADD_SELECTION


*&---------------------------------------------------------------------*
*&      Form  KEY_SELECT
*&---------------------------------------------------------------------*
* Add the key fields as select statements
*----------------------------------------------------------------------*
form key_select  tables   lt_code structure coderec.

  data: lt_dd03l  like dd03l occurs 0 with header line,
        ls_code   like coderec,
        lv_tmp    like coderec,
        lv_num(3) type n.

  select * from dd03l into table lt_dd03l
      where tabname = p_table
      and   keyflag = abap_true.

  delete lt_dd03l where fieldname = 'MANDT'.
  sort lt_dd03l by position.

  loop at lt_dd03l.
    lv_num = sy-tabix.
    concatenate 's_' lv_num into lv_tmp.

    if sy-tabix = 1.
      concatenate 'where' lt_dd03l-fieldname 'in' lv_tmp into ls_code separated by space.
    else.
      concatenate 'and' lt_dd03l-fieldname 'in' lv_tmp into ls_code separated by space.
    endif.

    add_code ls_code.
  endloop.

  add_code '.'.
  add_code ' '.

endform.                    " KEY_SELECT


*&---------------------------------------------------------------------*
*&      Form  CHECK_EXIST
*&---------------------------------------------------------------------*
* Select a record with a full key to see if it already exists
*----------------------------------------------------------------------*
form check_exist  tables   lt_code structure coderec.

  data: lt_dd03l like dd03l occurs 0 with header line,
        ls_code  like coderec,
        lv_tmp   like coderec.


  select * from dd03l into table lt_dd03l
       where tabname = p_table
       and   keyflag = abap_true.

  delete lt_dd03l where fieldname = 'MANDT'.
  sort lt_dd03l by position.

  loop at lt_dd03l.
    concatenate 'ls_up-' lt_dd03l-fieldname into lv_tmp.
    if sy-tabix = 1.
      concatenate 'where' lt_dd03l-fieldname '=' lv_tmp into ls_code separated by space.
    else.
      concatenate 'and' lt_dd03l-fieldname '=' lv_tmp into ls_code separated by space.
    endif.

    add_code ls_code.
  endloop.

  add_code '.'.
  add_code ' '.
endform.                    " CHECK_EXIST


Tuesday, October 6, 2015

Killing other sessions in SAP

Often times when I am busy testing something I will open up numerous SAP sessions to check and view various pieces of data as I'm debugging. Once I am done, I want to kill all the sessions except for the one I am currently in.
SAP allows you to use /i to kill the current session and /in to kill session number n. However, those t-codes are really built into the GUI and cannot be coded.
So I put together the following program and attached it to a simple t-code like ZZ. That way you can at any time use /nZZ to kill all the other sessions and clean up your desktop a bit.

*&---------------------------------------------------------------------*
*& Report  ZZKILLSESSION
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

report zzkillsession
       no standard page heading line-size 255.


start-of-selection.

    datalv_mode like sy-tabix,
        lv_keep like sy-tabix,
        lv_time like sy-uzeit.

  datalt_info like uinfo2 occurs with header line.


  call function 'TH_LONG_USR_INFO'
    tables
      user_info lt_info.


  delete lt_info where client <> sy-mandt.

  "SAP counts from 0 <- Important otherwise you accidentally delete the current mode
  loop at lt_info.
    lt_info-mode lt_info-mode 1.
    modify lt_info.
  endloop.

  "Find the current mode by looking for the one with the latest time
  lv_time '00:00:00'.
  loop at lt_info.
    if lt_info-time > lv_time.
      lv_keep lt_info-mode.
      lv_time lt_info-time.
    endif.
  endloop.

  "delete all the other modes
  lv_mode 7.
  do times.


    lv_mode lv_mode 1.

    "Don't delete the current mode
    if lv_mode lv_keep.
      continue.
    endif.

    "Only delete modes that actually exist otherwise you might crash the system
    read table lt_info with key mode lv_mode.
    check sy-subrc 0.

    call function 'TH_DELETE_MODE'
      exporting
        mode lv_mode.

  enddo.