Function module based delta data source is not working.

[former_member547444]

I am trying to create a function module-based delta data source.

• 1.Delta will be based on CPUTM (time of entry) and AEDAT (changed on)
• 2.Setting CPUTM as delta pointer and safety interval low as 1
• 3.Using the standard template RSAX_BIW_GET_DATA
• 4.In E_T_DATA, passing the extract structure name
• 5.The delta loop is written in the code is as below

ranges: l_r_CPUTM forMKPF- CPUTM,

l_r_ AEDAT forMKPF- AEDAT.

LOOP AT s_s_if-t_select INTO l_s_select.

CASE l_s_select-fieldnm.

WHEN ‘CPUTM’.

ls_ CPUTM -sign= ‘I’

ls_ CPUTM -option= ‘EQ’.

APPEND l_r_ CPUTM.

WHEN ‘AEDAT’.

ls_ AEDAT -sign= ‘I’

ls_ AEDAT -option= ‘EQ’.

APPEND l_r__ AEDAT.

ENDCASE.

ENDLOOP.

Is there anything wrong as the changed records are not pulled up?

[prem_shanker · Accepted solution]

Hi Sudip,

Please find my answer below

Question 1. How you are populating ZDATE.? it will always have blank data

Answer: The ZDATE will have blank value when you will run a full load and in that case it will select all the data from Table.

When you run from RSA3 (it is still full load)

See the below select code.

      loop at s_s_if-t_select into l_s_select where fieldnm = 'ZDATE'.
        move-corresponding l_s_select to l_r_zdate.
        l_r_zdate-sign = 'I'.
        l_r_zdate-option = 'GE'.
        clear l_r_zdate-high.
        append l_r_zdate.
      endloop.

    open cursor with hold s_cursor for
           select *
           from mkpf
                where ( ( CPUDT in l_r_zdate
                or AEDAT in l_r_zdate )).
endif. 

In case of full load the range table l_r_zdate will be Greater than Equal to Blank(Zero) and the select statement will execute as [Select from MKPF where CPUDT is Greater than Equal to Blank(Zero) OR AEDAT is Greater than Equal to Blank(Zero). i.e. it will bring all the data from the table that means Full load].

Do you agree to that ??

Now when you run a delta Load :-

Either by Replicating the Data source in BW , making a Delta Init and then running Next Delta

OR by running init and extracting in other system (like SAP Data Services etc)

The delta Pointer will be available in Source System RSA7/ODQMON will have the date of last delta. This date field will be feed to the append table l_r_zdate in the FM and then the value of l_r_zdate will be Greater than Equal to that Date.

In the below Screenshot the last run of Delta for this was 17/09/2019. Random Data-source screenshot.

For Example :- You have performed Init today (19/08/2020) and when you are running the Delta tomorrow (20/20/2020),the value passed to the l_r_zdate will be Greater than Equal to 19/08/2020 or simply ">=" "20200819".

Now the above select condition will work like:- Select from MKPF where CPUDT is Greater than Equal to "20200819" OR AEDAT is Greater than Equal to "20200819". That means it select all the records with CPUDT (Date of Entry) greater than equal to the last ran of successful Delta ("20200819") or it will select all the records with AEDAT (changed on) greater than equal to the last ran of successful Delta ("20200819").

This is what Delta Functionality means.

Note : It already have 1 days buffer, as it has "GE" Sign. That means if it is ran on 21/08/2020, then it will bring data of 20th and 21st data both even though 20th Data were already extracted yesterday(as delta pointer is 20th - date of last successful delta). As you have already mention to keep Safety Internal to 1 day in your question I believe this wont be any issue. It is frequently used in BW as BW TRFN has Overwrite capabilities.

2.are you setting ZDATE as delta field in generic delta option in RSO2 along with the code?

Answer: Yes, I am setting ZDATE as a delta field in Generic Delta Option in RSO2.

3. any changes in includes of that function module ?

Answer: Nothing special in the Include , it just be pointing towards this Function Module.

If you want you can do the declaration of structure of your Internal table in the Include "L*****TOP" of that function group (not mandatory).

I have used this code sample for writing all my FM based Datasource selecting from Change Date and Created Date by creating ZDATE field as an extra field. It should work.

Note:E_T_DATA will be like the extract structure of the Datasource

Please let me know if you have any questions.

Thank you!!

[former_member547444]

No Prem, that above code is also not working. Changed records are not being captured .

I got few questions ?

1.How you are popluating ZDATE.? it will always have blank data

2.are you setting ZDATE as delta field in generic delta option in RSO2 along with the code?

3. any changes in includes of that function module ?

[prem_shanker]

Hi,
please check my commets below

[prem_shanker]

Hi Sudip,

This is a very frequent requirement for any FM based data source to create delta based on created Date and Changed Date.I have worked on Several such requirement,

Please find the basic step.

1. Create a additional field (say ZDATE) in extract structure. The Delta will be based on this field.

2. Please use the standard template RSAX_BIW_GET_DATA_SIMPLE. This one is lot simpler

The FM will be designed in such a way that ZDATE will bring any change in CPUDT. or AEDAT.

Below is some Important part of the Function Module I am putting for your reference.

For Range part use the below code for the newly added field ZDATE.

      loop at s_s_if-t_select into l_s_select where fieldnm = 'ZDATE'.
        move-corresponding l_s_select to l_r_zdate.
        l_r_zdate-sign = 'I'.
        l_r_zdate-option = 'GE'.
        clear l_r_zdate-high.
        append l_r_zdate.
      endloop.
 

In the Select part use the below statement, It will make a select on the table based on either CPUDT or AEDAT.

open cursor with hold s_cursor for
           select *
           from mkpf
                where ( ( CPUDT in l_r_zdate
                or AEDAT in l_r_zdate )).
endif. 

Note: Please do not use Select * for Performance reason 🙂

I have done a development on MCHB table where the delta was based on Created Date (ERSDA) and Changed Date(LAEDA). I am putting the whole code in the comment section of this for your reference.

Please let me know if this worked for you.

Thanks

[prem_shanker]

Hi Sudip,

As told I am sharing the entire code for an exactly similar requirement I have done sometime back for one of my CLient.

function zbw_get_mchb.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     VALUE(I_REQUNR) TYPE  SRSC_S_IF_SIMPLE-REQUNR
*"     VALUE(I_DSOURCE) TYPE  SRSC_S_IF_SIMPLE-DSOURCE OPTIONAL
*"     VALUE(I_MAXSIZE) TYPE  SRSC_S_IF_SIMPLE-MAXSIZE OPTIONAL
*"     VALUE(I_INITFLAG) TYPE  SRSC_S_IF_SIMPLE-INITFLAG OPTIONAL
*"     VALUE(I_READ_ONLY) TYPE  SRSC_S_IF_SIMPLE-READONLY OPTIONAL
*"     VALUE(I_REMOTE_CALL) TYPE  SBIWA_FLAG DEFAULT SBIWA_C_FLAG_OFF
*"  TABLES
*"      I_T_SELECT TYPE  SRSC_S_IF_SIMPLE-T_SELECT OPTIONAL
*"      I_T_FIELDS TYPE  SRSC_S_IF_SIMPLE-T_FIELDS OPTIONAL
*"      E_T_DATA STRUCTURE  ZBW_MCHB OPTIONAL
*"  EXCEPTIONS
*"      NO_MORE_DATA
*"      ERROR_PASSED_TO_MESS_HANDLER
*"----------------------------------------------------------------------


* Example: DataSource for table MCHB
  tables: mchb.

types: begin of ls_mchb,
        .............
        .............
        .............
       end of ls_mchb.


types: begin of ls_mara,


         lv_matnr type matnr,
         lv_meins type meins,


       end of ls_mara.


  field-symbols: <fs_mchb> type ls_mchb,
                 <fs_mara> type ls_mara.
  data : it_mchb type standard table of ls_mchb,
         it_mara type standard table of ls_mara.
  data : w_t_data like zbw_mchb.


* 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.
* Select ranges
  ranges: l_r_matnr  for zbw_mchb-matnr,
          l_r_werks  for zbw_mchb-werks,
          l_r_ersda  for zbw_mchb-ersda,
          l_r_lfgja  for zbw_mchb-lfgja,
          l_r_laeda  for zbw_mchb-laeda,
          l_r_zdate  for zbw_mchb-zdate.


* Initialization mode (first call by SAPI) or data 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 'ZBW_MCHB'.
      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.


* Fill field list table for an optimized select statement
* (in case that there is no 1:1 relation between InfoSource fields
* and database table fields this may be far from beeing trivial)
    delete   i_t_fields[] where  fieldnm  =  'MEINS' or fieldnm  = 'ZDATE'.
    append lines of i_t_fields to s_s_if-t_fields.


  else.                 "Initialization mode or data 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'.
      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.


      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.


      loop at s_s_if-t_select into l_s_select where fieldnm = 'ERSDA'.
        move-corresponding l_s_select to l_r_ersda.
        append l_r_ersda.
      endloop.


      loop at s_s_if-t_select into l_s_select where fieldnm = 'LFGJA'.
        move-corresponding l_s_select to l_r_lfgja.
        append l_r_lfgja.
      endloop.


      loop at s_s_if-t_select into l_s_select where fieldnm = 'LAEDA'.
        move-corresponding l_s_select to l_r_laeda.
        append l_r_laeda.
      endloop.


      loop at s_s_if-t_select into l_s_select where fieldnm = 'ZDATE'.
        move-corresponding l_s_select to l_r_zdate.
        l_r_zdate-sign = 'I'.
        l_r_zdate-option = 'GE'.
        clear l_r_zdate-high.
        append l_r_zdate.
      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.
      open cursor with hold s_cursor for
           select (s_s_if-t_fields)
           from mchb
                where ( ( ersda in l_r_zdate
                or laeda in l_r_zdate )
                and matnr in l_r_matnr
               and werks in l_r_werks
               and ersda in l_r_ersda
               and lfgja in l_r_lfgja ).


    endif.                             "First data package ?


* Fetch records into interface table.
*   named E_T_'Name of extract structure'.
    fetch next cursor s_cursor
               appending corresponding fields
               of table it_mchb
               package size s_s_if-maxsize.


    if sy-subrc <> 0.
      close cursor s_cursor.
      raise no_more_data.
    endif.


*Get Unit from MARA table, join condition on MATNR(Material)


    select matnr
           meins
       from mara
       into table it_mara
       for all entries in it_mchb
       where matnr = it_mchb-matnr.


    sort it_mara by lv_matnr.


* When changed date is not present use the created date for Delta(ZDATE).


    if it_mchb[] is not initial.
      unassign <fs_mchb>.
      clear  w_t_data.


      loop at it_mchb assigning <fs_mchb>.
        unassign <fs_mara>.
        read table it_mara assigning <fs_mara>
        with key lv_matnr = <fs_mchb>-matnr
        binary search.
        if sy-subrc is initial.
          w_t_data-meins = <fs_mara>-lv_meins.
        endif.
        if <fs_mchb>-laeda is initial.
          move <fs_mchb>-ersda  to w_t_data-zdate.
        else.
          move <fs_mchb>-laeda  to w_t_data-zdate.
        endif.
        move :
        <fs_mchb>-mandt  to w_t_data-mandt,
        <fs_mchb>-matnr  to w_t_data-matnr,
        <fs_mchb>-werks  to w_t_data-werks,
        <fs_mchb>-lgort  to w_t_data-lgort,
        .................................
        .................................
        .................................
        <fs_mchb>-sgt_scat  to w_t_data-sgt_scat.
        append w_t_data to e_t_data.
      endloop.
    endif.




    s_counter_datapakid = s_counter_datapakid + 1.


  endif.              "Initialization mode or data extraction ?


endfunction.

[former_member547444]

Hello Prem,

My mistake. it should be CPUDT. (Date of entry).in the the above code it should be CPUDT insted of CPUTM and the selection i am using is

select * MKPF where

CPUDT in l_r_CPUDT AND

AEDAT in l_r_AEDAT endif.

[prem_shanker]

Hi Sudip,

From above I understood that you are using this time field CPUTM for delta.

Please provide your Select statement on how you are putting the record from the table

My recommendation is to use TSTMP (Timestamp field) by concatenating Date and Time i.e. AEDAT and CPUTM.

Then use the timestamp field for Delta.

This will make it very simple.

Than k you!!