Delta Using Multiple Fields

Overview: This blog describes way to create an extractor based on multiple delta fields. We created generic extractor for Function Location , Equipment and many other generic extractors by using multiple delta fields .

Prerequisite: One should know how to create Function Module Based extractor.

Approach : The delta field used on Generic Delta tab is only for setting the pointer in table “ROOSGENDLM” for the last successful delta so that system knows from where to start extracting the next run of delta.

Let’s say delta is based on two fields AEDAT and ERDAT from a table. Then one of the fields, say AEDAT will be set on generic tab screen and second field will be handled in Function module for delta extraction.

For handling multiple fields’ for delta we need a function module based extractor. The approach to create Function module based extractor remains same, you can find many articles on SDN for creating function module based extractor.

The only change is in the where clause of select statement. We need to use “OR” statement between the dates. We can have n number of dates in this where clause.

Writing this “OR” clause is little tricky and is given in the sample code below.

Sample Code :

Below is sample code to write Function Module for a Function Module Based extractor. Have only given the code which is focused around data extraction.

Declaration:

Tables: “I_T_SELECT” AS TYPE “SRSC_S_IF_SIMPLE-T_SELECT” under Tables tab of Function Module.

It_dynamic_select TYPE rsaot_t_dynamic_select to hold data for where clause of select statement.

I_T_SELECT will have data from selections if any and date for delta field ( in our case value for AEDAT as that is the delta field set in Generic Delta screen above).

System automatically extracts this information from the database on the very first call to function module.

I_T_SELECT has the data in a internal table format which needs to go into where clause for data extraction. However needs to be converted into the where clause format. This will be done using “RSAN_FILL_DYNAMICAL_SELECT” Function Module.

* Call the FM to build dynamic where clause for the selected fields
    CALL FUNCTION ‘RSAN_FILL_DYNAMICAL_SELECT’
      EXPORTING
        i_t_select                 = i_t_select
      IMPORTING
        e_t_dynamic_select         = it_dynamic_select
      EXCEPTIONS
        invalid_selection_criteria = 1
        OTHERS                     = 2.

    IF sy-subrc <> 0.
      REFRESH : it_dynamic_select[].
    ENDIF.

Input: I_T_SELECT had data in below format, where selections are on DATEFROM and DATETO. Delta Field AEDAT also has value for delta run.

Output:  IT_DYNAMIC_SELECT has data in the where clause format as below:

Now select statement is ready with its where clause however we need to append the second delta field as well to the above converted IT_DYNAMIC_SELECT table.

   LOOP AT i_t_select INTO wa_range_date WHERE fieldnm = c_aedat.
    wa_r_erdat-sign   = wa_range_date-sign.
    wa_r_erdat-option = wa_range_date-option.
    wa_r_erdat-low    = wa_range_date-low.
    wa_r_erdat-high   = wa_range_date-high.
    APPEND wa_r_erdat TO r_erdat.
    CLEAR: wa_r_erdat,
           wa_range_date.
  ENDLOOP.

* Call the Function Module to build dynamic where clause with
* additional delta fields
  CALL FUNCTION ‘Z_DYNAMIC_WHERE_CLAUSE’
    EXPORTING
*     Additional delta clause that is to be added
      i_addl_delta_clause   = c_erdat
      i_actual_delta_field  = c_aedat “Actual delta field
    CHANGING
*     Dynamic where clause
      e_t_dynamic_select    = it_dynamic_select
    EXCEPTIONS
      delta_field_not_found = 1
      OTHERS                = 2.
*       Write the error message to the log.

FUNCTION Z_DYNAMIC_WHERE_CLAUSE .
*”——————————————————————–
*”*”Local Interface:
*”  IMPORTING
*”     REFERENCE(I_ADDL_DELTA_CLAUSE) TYPE  CHAR100
*”     REFERENCE(I_ACTUAL_DELTA_FIELD) TYPE  CHAR20
*”  CHANGING
*”     REFERENCE(E_T_DYNAMIC_SELECT) TYPE  Z_DYNAMIC_SELECT
*”  EXCEPTIONS
*”      DELTA_FIELD_NOT_FOUND
*”——————————————————————–

  DATA: v_lineno          TYPE i,
        v_modifyline      TYPE char200,
        wa_dynamic_select TYPE LINE OF z_dynamic_select.

  CLEAR: v_lineno, v_modifyline, wa_dynamic_select.

* Check if the actual delta field passed is present in the dynamic
* WHERE clause table
  FIND FIRST OCCURRENCE OF i_actual_delta_field
                        IN TABLE e_t_dynamic_select
                        IN CHARACTER MODE IGNORING CASE
                        MATCH LINE v_lineno.

  IF sy-subrc EQ 0.

*   Update the line in which the delta field is found: Add the
*   new clause passed in I_ADDL_DELTA_CLAUSE with an ‘OR’ condition
    READ TABLE e_t_dynamic_select INDEX v_lineno INTO v_modifyline.
    IF sy-subrc EQ 0.
      CONCATENATE i_addl_delta_clause ‘OR’ v_modifyline
                                      INTO v_modifyline
                                      SEPARATED BY space.

*     Moving into a work area to update the table.
      MOVE v_modifyline TO wa_dynamic_select.
      MODIFY e_t_dynamic_select INDEX v_lineno FROM wa_dynamic_select.
    ENDIF.

* If the actual delta field passed to the FM is not present in the
* dynamic WHERE clause table, raise an exception
  ELSE.
    RAISE delta_field_not_found.
  ENDIF.

ENDFUNCTION.

Output of above code, the new IT_DYNAMIC_SELECT where we have OR statement between two dates:

Select * from view/table where (it_dynamic_select).

This where clause will take care of delta based on two date fields.