To continue from previous blog about Generic Data Sources , in this we will see how a Datasource can be built based on Function Module. A Function Module is needed when a Table or View can be used to built Datasource due to couple of reasons like Data has to be retrieved from more than one table or Business rules to be applied to filter data or add some conditions etc or Delta pointer could be based on more than one filed.
Let us see how we can achieve this.
- Create DataSource using Tcode RSO2. Give desired name click create.
- Fill all the necessary info for Description, Application and Function Module and Extract Structure name.
- The Extract Structure should have all the info which is needed at BI level. This extract structure can be built using Tcode SE11.
- The Extract Structure --
- Now create Function Module using Tcode SE38 and built the logic to fill the Extract Structure .
- Delta Pointer.
- Selection Controls.Data can be limited by Vendor, Article, Delivery num etc.
- Add the following under Tables part of your Function Module definition.
- And finally Import Parameters
- We can add the following source code and see the source code .
FUNCTION zp2p_likplips.
*"----------------------------------------------------------------------
*"*"Local Interface:
*" IMPORTING
*" REFERENCE(I_REQUNR) TYPE SRSC_S_IF_SIMPLE-REQUNR
*" REFERENCE(I_DSOURCE) TYPE SRSC_S_IF_SIMPLE-DSOURCE OPTIONAL
*" REFERENCE(I_MAXSIZE) TYPE SRSC_S_IF_SIMPLE-MAXSIZE DEFAULT 2000
*" REFERENCE(I_INITFLAG) TYPE SRSC_S_IF_SIMPLE-INITFLAG OPTIONAL
*" REFERENCE(I_READ_ONLY) TYPE SRSC_S_IF_SIMPLE-READONLY OPTIONAL
*" REFERENCE(I_REMOTE_CALL) TYPE SBIWA_FLAG DEFAULT
*" SBIWA_C_FLAG_OFF
*" TABLES
*" I_T_SELECT TYPE SBIWA_T_SELECT OPTIONAL
*" I_T_FIELDS TYPE SBIWA_T_FIELDS OPTIONAL
*" E_T_DATA STRUCTURE ZP2P_LIKPLIPS OPTIONAL
*" EXCEPTIONS
*" NO_MORE_DATA
*" ERROR_PASSED_TO_MESS_HANDLER
*"----------------------------------------------------------------------
*DataSource for table LIKP LIPS
TABLES: likp, lips.
* Auxiliary Selection criteria structure
DATA: l_s_select TYPE srsc_s_select.
* Maximum number of lines for DB table
STATICS: s_s_if TYPE srsc_s_if_simple,
* counter
s_counter_datapakid LIKE sy-tabix,
* cursor
s_cursor TYPE cursor.
*********these are the selection criteria.*********************
* Select ranges
RANGES:
l_r_erdat FOR lips-erdat,
l_r_lifnr FOR likp-lifnr,
l_r_matnr FOR lips-matnr,
l_r_vbeln FOR likp-vbeln,
l_r_vgbel FOR lips-vgbel,
l_r_bldat FOR likp-bldat,
l_r_werks FOR lips-werks,
l_r_aedat FOR likp-aedat.
DATA: lwa_data LIKE LINE OF e_t_data.
* Initialization mode (first call by SAPI) or delta transfer mode
* (following calls) ?
IF i_initflag = sbiwa_c_flag_on.
************************************************************************
* Initialization: check input parameters
* buffer input parameters
* prepare data selection
************************************************************************
* Check DataSource validity
CASE i_dsource.
WHEN 'ZP2P_LIKP_LIPS'. " Data Source ZP2P_LIKP_LIPS
WHEN OTHERS.
IF 1 = 2. MESSAGE e009(r3). ENDIF.
* this is a typical log call. Please write every error message like this
log_write 'E' "message type
'R3' "message class
'009' "message number
i_dsource "message variable 1
' '. "message variable 2
RAISE error_passed_to_mess_handler.
ENDCASE.
APPEND LINES OF i_t_select TO s_s_if-t_select.
* Fill parameter buffer for data extraction calls
s_s_if-requnr = i_requnr.
s_s_if-dsource = i_dsource.
s_s_if-maxsize = i_maxsize.
APPEND LINES OF i_t_fields TO s_s_if-t_fields.
ELSE. "Initialization mode or delta extraction ?
************************************************************************
* Data transfer: First Call OPEN CURSOR + FETCH
* Following Calls FETCH only
************************************************************************
* First data package -> OPEN CURSOR
IF s_counter_datapakid = 0.
* Fill range tables BW will only pass down simple selection criteria
* of the type SIGN = 'I' and OPTION = 'EQ' or OPTION = 'BT'.
* CHANGE DATE
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'AEDAT'.
MOVE-CORRESPONDING l_s_select TO l_r_aedat.
l_r_aedat-sign = 'I'.
l_r_aedat-option = 'GE'.
CLEAR l_r_aedat-high.
APPEND l_r_aedat.
ENDLOOP.
* Del DOC DATE
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'BLDAT'.
MOVE-CORRESPONDING l_s_select TO l_r_bldat.
APPEND l_r_bldat.
ENDLOOP.
* VENDOR
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'LIFNR'.
MOVE-CORRESPONDING l_s_select TO l_r_lifnr.
APPEND l_r_lifnr.
ENDLOOP.
* MATNR
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'MATNR'.
MOVE-CORRESPONDING l_s_select TO l_r_matnr.
APPEND l_r_matnr.
ENDLOOP.
*werks
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'WERKS'.
MOVE-CORRESPONDING l_s_select TO l_r_werks.
APPEND l_r_werks.
ENDLOOP.
*PO NUMBER
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'VGBEL'.
MOVE-CORRESPONDING l_s_select TO l_r_vgbel.
APPEND l_r_vgbel.
ENDLOOP.
*Del NUMBER
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'VBELN'.
MOVE-CORRESPONDING l_s_select TO l_r_vbeln.
APPEND l_r_vbeln.
ENDLOOP.
** DOC CREATE DATE
* LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'ERDAT'.
* MOVE-CORRESPONDING l_s_select TO l_r_erdat.
* APPEND l_r_erdat.
* ENDLOOP.
* Determine number of database records to be read per FETCH statement
* from input parameter I_MAXSIZE. If there is a one to one relation
* between DataSource table lines and database entries, this is trivial.
* In other cases, it may be impossible and some estimated value has to
* be determined.
*-- Fetch Purchase Order data from Database Tables LIKP and LIPS
OPEN CURSOR WITH HOLD s_cursor FOR
SELECT
b~vbeln
b~posnr
a~vstel
a~vkorg
a~lfart
a~wadat
a~lddat
a~lfdat
a~kodat
a~route
a~fkdat
a~aedat " LIKP
a~lgnum
a~fkdiv
a~lifnr
a~xabln
a~bldat
a~wadat_ist
a~erdat " LIKP
b~matnr
b~werks
b~lgort
b~kdmat
b~lfimg
b~meins
b~vrkme
b~lgmng
b~vgbel
b~vgpos
a~vbtyp
a~lifex
a~zz_carrier
a~zz_container
a~zz_shipmentid
a~zz_loadid
a~inco1
a~zz_chgdt "LIKP CHANGE DATE CUSTOM
FROM likp AS a
INNER JOIN lips AS b ON
a~vbeln = b~vbeln
WHERE
( ( a~aedat IN l_r_aedat OR
b~aedat IN l_r_aedat OR
a~erdat IN l_r_aedat OR
b~erdat IN l_r_aedat )
AND a~vbeln IN l_r_vbeln
AND a~bldat IN l_r_bldat
AND a~lifnr IN l_r_lifnr
AND b~matnr IN l_r_matnr
AND b~vgbel IN l_r_vgbel
AND b~werks IN l_r_werks
AND a~lfart = 'EL'
AND a~vbtyp = '7' ) .
ENDIF. "First data package ?
* Fetch records into table i_likp_lips
FETCH NEXT CURSOR s_cursor
INTO TABLE i_likp_lips
PACKAGE SIZE s_s_if-maxsize.
IF sy-subrc <> 0.
CLOSE CURSOR s_cursor.
RAISE no_more_data.
ELSE.
IF i_likp_lips[] IS NOT INITIAL.
SORT i_likp_lips.
LOOP AT i_likp_lips ASSIGNING
CLEAR lwa_data.
* move all the fields to work area.
MOVE-CORRESPONDING
*changed date from custom field of LIKP
IF
MOVE
ELSE.
MOVE
ENDIF.
* CREATE DATE FROM LIPS
* IF
* MOVE
* ELSE.
* MOVE
* ENDIF.
APPEND lwa_data TO e_t_data.
ENDLOOP.
ENDIF.
ENDIF.
s_counter_datapakid = s_counter_datapakid + 1.
ENDIF. "Initialization mode or delta extraction ?
ENDFUNCTION.
***************************************************************************
- Once you compile the code and the extractor can be tested using Tcode RSA3.
