How to remotely access ECC table in BW Info Package Selection
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.
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:
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.
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:
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
lc_state(1) TYPE c VALUE ‘G’.
*Get the max batch date (IP execution date)
SELECT SINGLE MAX( 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’.
*call the RFC Function module (Defined in ECC)
CALL FUNCTION ‘ZFM_GET_REINR_BY_CHG_DATE’ DESTINATION ls_logsys
highdate = sy-datum
lowdate = lv_date_low
reinr_low = lc_reinr.
l_t_range-low = lc_reinr.
l_t_range-high = ‘9999999999’.
l_t_range-sign = ‘I’.
l_t_range-option = ‘BT’.
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:
Source Code snapshot:
Source Code of the Remote Function Module :
*” VALUE(HIGHDATE) TYPE EDI_UPDDAT OPTIONAL
*” VALUE(LOWDATE) TYPE EDI_UPDDAT OPTIONAL
*” 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 ) *****************
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.