How to remotely access ECC table in BW Info Package Selection

[sukdev_koner2]

How to remotely access ECC table in BW Info Package Selection

Background :

Let me give you some insight  on the background of this solution. This solution  was prepared for 'Travel Module'.

 Travel Module in ECC :

In Travel module in ECC , the user(or employee)  is creating the Trip Expense document or Trip Document (in short). They create this document  to claim / reimburse  different  types of expenses that they had incurred during their official trip.

 This trip document is then approved by the respective Manager and the financial settlement is started. Then the finance document is processed and claim is settled down.

Data Source:

We use the SAP provided data source 0FI_TV_01 to get the Trip document details. This  data is then loaded to a DSO (ZEXP_DSO) in BW.

Data Source Constraint :

This Data source does not support Delta. If we  go for Full load, then it would take  almost 4 to 6  hours in a Production environment.  So somehow we need only changed / incremental records instead of all records every time.

When existing solution has flaws:

Previously, for the uploading of the trip expense data (0FI_TV_01), we had a piece of code in the selection of Info Package that gets the last trip number, already updated in the DSO (on previous day)  and that Trip document number was used as the start(low value) of the Trip Document range for next data load.

But this is not working, as the following business scenario exists:

Business Scenario:

 What is happening is that a user will create a trip document / report and just save it as a Draft. He would not submit it, so that document is not yet released for further processing. just 'Saved'.

  Another user/ employee comes later and creates  a trip report and submits it. So as per the above BW design, the submitted trip document would be extracted, but the draft document is not (as not 'Submitted').

The maximum value  in the DSO will be the submitted document. So when the draft would be submitted at a later point of time, as the load has already moved to the next higher trip document value, this Trip document number is missed out from extraction.

Work to be done to resolve this issue:

As in BW system, we are unable to determine the lowest Trip document number (to be extracted), so we need to modify the logic to determine this trip number from an ECC table directly.

We have found that the table PTRV_HEAD is holding all the Trip Document details as well as last changed date for the Trip number. So during data selection in Info Package, we need a RFC to ECC to get the Trip Document number based on  changed date from table PTRV_HEAD.

Technical Resolution:

1. Create an Info Package on the DataSource 0FI_TV_01. This Info Package would be loaded through a Process Chain.

2. In the 'Data Selection' tab of this info package, against the field REINR ( Trip Document), use Type as '6' - 'ABAP Routine'.

3. There is a  table in BW (RSPCPROCESSLOG)  which gives you the Batch Date (Last execution date) of  a particular Info Package/ DTP through a Process Chain.

 Select Batch Date from RSPCPROCESSLOG table by providing the following parameters:

VARIANTE =  Info Package Technical Id

TYPE = ‘LOADING’ and

STATE = ‘G’.[G stands for 'GREEN', means Load was successful]

Get the latest batch date. Calculate Date range Low value as 'Batch Date - 5' .

 Take the High value as SY-DATUM. (Refer the code below).

If Batch Date is Blank then take '01.01.1000' as Low Date.

4. Double click on the 'ABAP Routine' on Info Package 'Data Selection' tab.. It would open the ABAP Editor. Write the code in Info Package  as follows:

Form compute_REINR
  tables   l_t_range      structure rssdlrange
  using    p_infopackage  type rslogdpid
           p_fieldname    type rsfnm
  changing p_subrc        like sy-subrc.

*       Insert source code to current selection field
*$*$ begin of routine - insert your code only below this line        *-*

****** Code to Get the Trip Number range from
*****   ECC  table PTRV_HEAD     depending
******  on the last Changed Date (field name 'DATES')


  DATA:   l_idx LIKE sy-tabix,
          lc_reinr TYPE /bi0/oitv_trip,
          ls_date TYPE sy-datum,
          ls_logsys TYPE rsslogsys,
          lv_batchdate TYPE btcreldt,
          lr_date TYPE RANGE OF rspcprocesslog-batchdate,
          ls_r_date LIKE LINE OF lr_date,
          lv_date_low TYPE sy-datum,
          lv_lin TYPE p.

  CONSTANTS : lc_loading(7) TYPE c VALUE 'LOADING',
              lc_variante(32) TYPE c VALUE
              'ZPAK_D8YRA3A7ZO97CU59M3SHNZHIW',
              lc_state(1)     TYPE c VALUE 'G'.

*Get the max batch date (IP execution date)
  SELECT SINGLE MAX( batchdate )
  FROM rspcprocesslog
  INTO lv_batchdate
  WHERE type     = lc_loading
  AND  variante = lc_variante
  AND  state    = lc_state.

  lv_date_low = lv_batchdate - 5. " 5 days safety interval

*get the logical system name (used as RFC destination below)
  SELECT SINGLE logsys INTO ls_logsys FROM rsldpio
    WHERE logdpid = p_infopackage.

*check if batchdate is initial
  IF lv_batchdate IS INITIAL.
    lv_date_low = '10000101'.
  ENDIF.

*call the RFC Function module (Defined in ECC)
  CALL FUNCTION 'ZFM_GET_REINR_BY_CHG_DATE' DESTINATION ls_logsys
    EXPORTING
      highdate  = sy-datum
      lowdate   = lv_date_low
    IMPORTING
      reinr_low = lc_reinr.

  REFRESH l_t_range[].

  l_t_range-low = lc_reinr.
  l_t_range-high = '9999999999'.
  l_t_range-sign = 'I'.
  l_t_range-option = 'BT'.
  APPEND l_t_range.

  p_subrc = 0. 

5.  Build a Function Module in ECC which would be called from the BW Info Package.  (Refer the Source code below).

Make this FM as RFC enabled:

Import Parameters:

Export Parameters:

 

Source Code snapshot:

 

Source Code of the Remote Function Module :

FUNCTION zfm_get_reinr_by_chg_date.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     VALUE(HIGHDATE) TYPE  EDI_UPDDAT OPTIONAL
*"     VALUE(LOWDATE) TYPE  EDI_UPDDAT OPTIONAL
*"  EXPORTING
*"     VALUE(REINR_LOW) TYPE  REINR
*"----------------------------------------------------------------------
  TYPES : BEGIN OF  st_data,
           zreinr TYPE reinr,
          END OF st_data.

  DATA : lt_data TYPE STANDARD TABLE OF st_data,
         ls_data TYPE st_data.

  DATA : l_s_data TYPE ptrv_reinr_so,
         lc_reinr TYPE reinr.

***  Select the Trip Doc from table PTRV_HEAD

***  where the document was Changed in last 5 days

***  as High Date is 'SY-DATUM' and Low Date as

***  Batch Date - 5 (as parameters are passed from

***  BI Info Package )           *****************

  SELECT reinr
    FROM ptrv_head
    INTO TABLE lt_data
    WHERE dates >= lowdate
      AND dates <= highdate.

  SORT lt_data ASCENDING.

  CLEAR : lc_reinr,l_s_data.

*Get the lowest Trip Document Number
  READ TABLE lt_data INTO lc_reinr INDEX 1.

* Pass the Trip Doc to BI InfoPackage
  reinr_low = lc_reinr.


ENDFUNCTION.