Function Module Extraction!! Please HELP!!

[Former Member]

Hi Experts,

This is my first project in BW. Please help!! The existing function module using 3 tables to extract (Full update) data. Now, I have to enable the delta for this generic extractor. I have seen RSA3_GET_DATA_SIMPLE (nothing specified for Delta) and RSVD_BW_GET_DELTA_DATA function module to make use of it using OPEN CURSOR but in that fm I can see only one table being used.

Should I use 3 cursors to extract if so how should I join them? Can anyone please help me how to use cursors joining 3 tables and extract right delta data?

This would be a greatest help!! Thanks in advance.

Rishi

[Former Member · Accepted solution]

The cursor may not have much to do with 'delta' functionality per se. It is used so the package size is manageable (or of the size specified).

Check your existing logic. First thing is to identify which field can be a delta identifier. Since you have three tables, it may not be obvious which field can work correctly as delta-identifier.

Once you have such field, you can enhance the existing selection logic to read the value of this delta field from the FM interface internal table getting the selections, and use it to filter the data the code is currently reading.

[Former Member · Accepted solution]

Hi Ajay,

Thanks for the reply. I have selected ERDAT as a calendar day delta field and given safety interval given as 1.

Please let me know what has to be done in the following code. I know it's not fair to give the code and ask you but I'm really stuck to proceed further. Thanks again for your help!!

FUNCTION ZODS_BW_EXTRACT_FM.

*"----

---

""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

*" VALUE(I_UPDMODE) TYPE SBIWA_S_INTERFACE-UPDMODE OPTIONAL

*" 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 ZODS_BW_EXTRACT_STRUCT OPTIONAL

*" EXCEPTIONS

*" NO_MORE_DATA

*" ERROR_PASSED_TO_MESS_HANDLER

*"----

---

• Example: DataSource for table PRPS

TABLES: PRPS.

• 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,

V_CURSOR TYPE CURSOR,

C_CURSOR TYPE CURSOR.

DATA: BEGIN OF t_prps OCCURS 0,

posid TYPE ps_posid,

psphi TYPE ps_psphi,

ERDAT TYPE ERDAT,

AEDAT TYPE UPDAT,

END OF t_prps.

DATA: BEGIN OF t_factor OCCURS 0,

naindex TYPE zd_index,

nayear TYPE zd_year,

cofactor TYPE zd_cofac,

vocfactor TYPE zd_vofac,

noxfactor TYPE zd_nofac,

pmfactor TYPE zd_pmfac,

END OF t_factor.

DATA: BEGIN OF t_deer OCCURS 0,

slindex TYPE zd_index,

slyear TYPE zd_year,

sltldfact TYPE zd_sltldfact,

END OF t_deer.

• Local data declarations

DATA: l_output TYPE proj-pspid.

DATA : t_zods_extract TYPE zods_bw_extract_struct OCCURS 0

WITH HEADER LINE.

• Select ranges

RANGES: L_R_ERDAT for PRPS-ERDAT,

L_R_AEDAT for PRPS-AEDAT.

• 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 'ZODS_DATA_TRANS'.

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)

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'.

• Fill range tables.

loop at i_t_select into l_s_select where fieldnm = 'ERDAT'.

• I_UPDMODE = 'D'.

move-corresponding l_s_select to l_r_ERDAT.

append l_r_ERDAT.

endloop.

loop at i_t_select into l_s_select where fieldnm = 'AEDAT'.

• I_UPDMODE = 'D'.

move-corresponding l_s_select to l_r_AEDAT.

append l_r_AEDAT.

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 posid psphi erdat aedat

FROM PRPS

WHERE prart = '15'

OR prart = '17'

and erdat in L_R_ERDAT

and aedat in L_R_AEDAT.

OPEN CURSOR WITH HOLD V_CURSOR FOR

SELECT naindex

nayear

cofactor

vocfactor

noxfactor

pmfactor

FROM ztable1.

OPEN CURSOR WITH HOLD C_CURSOR FOR

SELECT slindex

syear

stdfact

FROM ztable2.

LOOP AT t_prps.

CLEAR l_output.

CALL FUNCTION 'CONVERSION_EXIT_KONPD_OUTPUT'

EXPORTING

input = t_prps-psphi

IMPORTING

output = l_output.

t_zods_extract-psphi = l_output.

CLEAR t_factor.

READ TABLE t_factor WITH KEY NAINDEX = t_prps-zztpemsnar1+0(3)

nayear = t_prps-zzrprtngyr1

BINARY SEARCH.

IF sy-subrc EQ 0.

t_zods_extract-cofactor = t_factor-cofactor.

t_zods_extract-vocfactor = t_factor-vocfactor.

t_zods_extract-noxfactor = t_factor-noxfactor.

t_zods_extract-pmfactor = t_factor-pmfactor.

ENDIF.

CLEAR t_deer.

READ TABLE t_deer WITH KEY slindex = t_prps-ZZSLITEMAR1+0(3)

slyear = t_prps-zzrprtngyr1

BINARY SEARCH.

IF sy-subrc EQ 0.

t_zods_extract-sltldfact = t_deer-sltldfact.

ELSE.

t_zods_extract-sltldfact = '0'.

ENDIF.

ENDLOOP.

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 E_T_DATA

PACKAGE SIZE S_S_IF-MAXSIZE.

FETCH NEXT CURSOR V_CURSOR

APPENDING CORRESPONDING FIELDS

OF TABLE E_T_DATA

PACKAGE SIZE S_S_IF-MAXSIZE.

FETCH NEXT CURSOR C_CURSOR

APPENDING CORRESPONDING FIELDS

OF TABLE E_T_DATA

PACKAGE SIZE S_S_IF-MAXSIZE.

IF SY-SUBRC <> 0.

CLOSE CURSOR S_CURSOR.

CLOSE CURSOR V_CURSOR.

CLOSE CURSOR C_CURSOR.

RAISE NO_MORE_DATA.

ENDIF.

S_COUNTER_DATAPAKID = S_COUNTER_DATAPAKID + 1.

ENDIF. "Initialization mode or data extraction ?

ENDFUNCTION.

[Former Member · Accepted solution]

Rishi,

Your cursor usage is all wrong. Could you post the original code, it might be easier to check that.

The selection you have added for the delta field is alright. Only other thing I note is in the code below

SELECT posid psphi erdat aedat

FROM PRPS

WHERE prart = '15'

OR prart = '17'

and erdat in L_R_ERDAT

and aedat in L_R_AEDAT.

You should instead be saying

SELECT posid psphi erdat aedat

FROM PRPS

WHERE ( prart = '15'

OR prart = '17' )

and erdat in L_R_ERDAT

and aedat in L_R_AEDAT.

[Former Member · Accepted solution]

Ok please find the original code below before I changed it... please do not mind as I was little scared to give exact tech names sorry... please find the code below.

FUNCTION zz_ods_bw_extract_fm.

*"----

---

""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

*" 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 ZZODS_BW_EXTRACT_STRUCT OPTIONAL

*" EXCEPTIONS

*" NO_MORE_DATA

*" ERROR_PASSED_TO_MESS_HANDLER

*"----

---

DATA : t_zzods_extract TYPE zzods_bw_extract_struct OCCURS 0

WITH HEADER LINE.

• Local data declarations

DATA: l_output TYPE proj-pspid.

• Maximum number of lines for DB table

STATICS: s_s_if TYPE srsc_s_if_simple,

s_counter_datapakid LIKE sy-tabix.

DATA: BEGIN OF t_prps OCCURS 0,

posid TYPE ps_posid,

psphi TYPE ps_psphi,

zzdflt1 TYPE zd_variable,

zzdflt2 TYPE zd_variable,

zzdflt3 TYPE zd_variable,

zzdflt4 TYPE zd_variable,

zzdflt5 TYPE zd_variable,

zzdflt6 TYPE zd_variable,

zzdflt7 TYPE zd_variable,

zzdflt8 TYPE zd_variable,

zzdflt9 TYPE zd_variable,

zzdflt10 TYPE zd_variable,

zzdflt11 TYPE zd_variable,

zzvar1 TYPE zd_var01,

zzvar2 TYPE zd_var02,

zzvar3 TYPE zd_var03,

zzvar4 TYPE zd_var04,

zzvar5 TYPE zd_var05,

zzvar6 TYPE zd_var06,

zzvar7 TYPE zd_var07,

zzvar8 TYPE zd_var08,

zzvar9 TYPE zd_var09,

zzvar10 TYPE zd_var10,

zzvar11 TYPE zd_var09,

zzunit1 TYPE zd_unit01,

zzunit2 TYPE zd_unit02,

zzunit3 TYPE zd_unit03,

zzunit4 TYPE zd_unit04,

zzunit5 TYPE zd_unit05,

zzunit6 TYPE zd_unit06,

zzunit7 TYPE zd_unit07,

zzunit8 TYPE zd_unit08,

zzunit9 TYPE zd_unit09,

zzunit10 TYPE zd_unit10,

zzunit11 TYPE zd_unit11,

zztpemsnar1 TYPE zd_tpemsnar,

zzrprtngyr1 TYPE zd_rprtngyr,

zznonatar1 TYPE zd_nonatr,

zzzcat1 TYPE zd_zcat,

zzslitemar1 TYPE zd_slitemar,

zzbnftyrs1 TYPE zd_bnftyrs,

zzdescfrml TYPE zd_descfrml,

• ZZFORMULA type ZD_FRMLUSD,

END OF t_prps.

DATA: BEGIN OF t_emfactor OCCURS 0,

naindex TYPE zd_index,

nayear TYPE zd_year,

cofactor TYPE zd_cofac,

vocfactor TYPE zd_vofac,

noxfactor TYPE zd_nofac,

pmfactor TYPE zd_pmfac,

END OF t_emfactor.

DATA: BEGIN OF t_slef OCCURS 0,

slindex TYPE zd_index,

slyear TYPE zd_year,

sltldfact TYPE zd_sltldfact,

END OF t_slef.

• Initialization mode (first call by SAPI) or data transfer mode

• (following calls) ?

IF i_initflag = sbiwa_c_flag_on.

• Check DataSource validity

CASE i_dsource.

WHEN 'ZZODS_DATA_TRANS'.

WHEN OTHERS.

RAISE error_passed_to_mess_handler.

ENDCASE.

• 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.

ELSE. "Initialization mode or data extraction ?

************************************************************************

• Data transfer: First Call to pupulate data

************************************************************************

• First data package

IF s_counter_datapakid = 0.

• Extract data from prps for Project Types 15 and 17

SELECT posid psphi zzdflt1 zzdflt2 zzdflt3

zzdflt4 zzdflt5 zzdflt6 zzdflt7

zzdflt8 zzdflt9 zzdflt10 zzdflt11

zzvar1 zzvar2 zzvar3 zzvar4 zzvar5

zzvar6 zzvar7 zzvar8 zzvar9 zzvar10

zzvar11 zzunit1 zzunit2 zzunit3 zzunit4

zzunit5 zzunit6 zzunit7 zzunit8

zzunit9 zzunit10 zzunit11

zztpemsnar1 zzrprtngyr1

zznonatar1 zzzcat1 zzslitemar1

zzbnftyrs1 zzdescfrml

FROM prps

INTO TABLE t_prps

WHERE prart = '15' OR prart = '17'.

• Check for fetched records

IF sy-subrc EQ 0.

SORT t_prps BY posid.

ENDIF.

• If the above condition is true

IF NOT t_prps[] IS INITIAL.

• Get the data from z_fact

Data : t_prps1 like t_prps occurs 0 with header line.

SELECT naindex

nayear

cofactor

vocfactor

noxfactor

pmfactor

INTO TABLE t_emfactor

FROM z_fact.

*RR WHERE naindex = t_prps-zztpemsnar1+0(3)

*RR WHERE naindex = l_prps_index

*GP WHERE naindex = '01' or NAINDEX = '03'.

• AND nayear = t_prps-zzrprtngyr1.

• Sort the data by index and construction year

IF sy-subrc EQ 0.

SORT t_emfactor BY naindex nayear.

ENDIF.

• Get the data from ztp_slef

SELECT slindex

slyear

sltldfact

INTO TABLE t_slef

FROM ztp_slef.

*RR WHERE slindex = t_prps-zztpemsnar1+0(3)

*GP WHERE slindex = '01' or slindex = '03'.

*RR AND slyear = t_prps-zzrprtngyr1.

IF sy-subrc EQ 0.

SORT t_slef BY slindex slyear.

ENDIF.

ENDIF.

• Between this LOOP and ENDLOOP get the internal value of each project.

• Get the required data stored in above internal tables and populate

• the extract internal table

LOOP AT t_prps.

CLEAR l_output.

CALL FUNCTION 'CONVERSION_EXIT_KONPD_OUTPUT'

EXPORTING

input = t_prps-psphi

IMPORTING

output = l_output.

t_zzods_extract-psphi = l_output.

CLEAR t_emfactor.

READ TABLE t_emfactor WITH KEY NAINDEX = t_prps-zztpemsnar1+0(3)

nayear = t_prps-zzrprtngyr1

BINARY SEARCH.

IF sy-subrc EQ 0.

t_zzods_extract-cofactor = t_emfactor-cofactor.

t_zzods_extract-vocfactor = t_emfactor-vocfactor.

t_zzods_extract-noxfactor = t_emfactor-noxfactor.

t_zzods_extract-pmfactor = t_emfactor-pmfactor.

ENDIF.

CLEAR t_slef.

READ TABLE t_slef WITH KEY slindex = t_prps-ZZSLITEMAR1+0(3)

slyear = t_prps-zzrprtngyr1

BINARY SEARCH.

IF sy-subrc EQ 0.

t_zzods_extract-sltldfact = t_slef-sltldfact.

ELSE.

t_zzods_extract-sltldfact = '0'.

ENDIF.

t_zzods_extract-posid = t_prps-posid.

t_zzods_extract-zzdflt1 = t_prps-zzdflt1.

t_zzods_extract-zzdflt2 = t_prps-zzdflt2.

t_zzods_extract-zzdflt3 = t_prps-zzdflt3.

t_zzods_extract-zzdflt4 = t_prps-zzdflt4.

t_zzods_extract-zzdflt5 = t_prps-zzdflt5.

t_zzods_extract-zzdflt6 = t_prps-zzdflt6.

t_zzods_extract-zzdflt7 = t_prps-zzdflt7.

t_zzods_extract-zzdflt8 = t_prps-zzdflt8.

t_zzods_extract-zzdflt9 = t_prps-zzdflt9.

t_zzods_extract-zzdflt10 = t_prps-zzdflt10.

t_zzods_extract-zzdflt11 = t_prps-zzdflt11.

t_zzods_extract-zzvar1 = t_prps-zzvar1.

t_zzods_extract-zzvar2 = t_prps-zzvar2.

t_zzods_extract-zzvar3 = t_prps-zzvar3.

t_zzods_extract-zzvar4 = t_prps-zzvar4.

t_zzods_extract-zzvar5 = t_prps-zzvar5.

t_zzods_extract-zzvar6 = t_prps-zzvar6.

t_zzods_extract-zzvar7 = t_prps-zzvar7.

t_zzods_extract-zzvar8 = t_prps-zzvar8.

t_zzods_extract-zzvar9 = t_prps-zzvar9.

t_zzods_extract-zzvar10 = t_prps-zzvar10.

t_zzods_extract-zzvar11 = t_prps-zzvar11.

t_zzods_extract-zzunit1 = t_prps-zzunit1.

t_zzods_extract-zzunit2 = t_prps-zzunit2.

t_zzods_extract-zzunit3 = t_prps-zzunit3.

t_zzods_extract-zzunit4 = t_prps-zzunit4.

t_zzods_extract-zzunit5 = t_prps-zzunit5.

t_zzods_extract-zzunit6 = t_prps-zzunit6.

t_zzods_extract-zzunit7 = t_prps-zzunit7.

t_zzods_extract-zzunit8 = t_prps-zzunit8.

t_zzods_extract-zzunit9 = t_prps-zzunit9.

t_zzods_extract-zzunit10 = t_prps-zzunit10.

t_zzods_extract-zzunit11 = t_prps-zzunit11.

t_zzods_extract-zznonatar1 = t_prps-zznonatar1.

t_zzods_extract-zzzcat1 = t_prps-zzzcat1.

t_zzods_extract-zztpemsnar1 = t_prps-zztpemsnar1.

t_zzods_extract-zzslitemar1 = t_prps-zzslitemar1.

t_zzods_extract-zzrprtngyr1 = t_prps-zzrprtngyr1.

t_zzods_extract-zzbnftyrs1 = t_prps-zzbnftyrs1.

t_zzods_extract-zzdescfrml = t_prps-zzdescfrml.

• t_ZzODS_EXTRACT-ZZFORMULA = t_prps-ZZFORMULA.

APPEND t_zzods_extract.

CLEAR t_zzods_extract.

ENDLOOP.

• Delete the record before filling the custom Records

CLEAR e_t_data.

REFRESH e_t_data.

• Submit all the extracted records to extract table

e_t_data[] = t_zzods_extract[].

s_counter_datapakid = s_counter_datapakid + 1.

else.

Raise NO_MORE_DATA.

ENDIF.

• s_counter_datapakid = s_counter_datapakid + 1.

ENDIF. "Initialization mode or data extractio

ENDFUNCTION.

[Former Member · Accepted solution]

Rishi,

You do not need to use cursors for this. All you need to do is ensure that the delta selection is done - ny adding the where clause for AEDAT.

Do exactly what you did to read this field from selection table, and modify the SELECT on PRPS to include this as an AND

[where (part = ' ' or part = ' ' ) AND AEDAT in ....].

Leave the rest of the code as it is, and do delta init, and delta tests. It should work fine.

Since your main selection is on one table, you can choose to use cursor and split data into packages, but that is unrelated to your delta requirement.

And, for all this to work, you ofcourse have to first change the datasource and replicate (for delta definition).

Let us know if it doesn't work.

[Former Member · Accepted solution]

Hi Ajay Das, Great!!

I changed the code per your advice. And added in the internal table, select statement, ranges etc since I've copied the old full code. Now I tested the extraction in RSA3 and it is now working fine. But the delta is also working since it might have changed in the tables. I have to load it in the ODS and then only I'll see the clear picture as the data result in the RSA3 is not formatted. Ok I'll delete the Init now and reload the init and tomorrow I'm going to test the new and changed data. Thanks a ton for your help!! Whole points assigned. I really appreciate your timely help!!

I would like to know in what circumstances you would use cursors? that too for custom fm extraction? Please let me know...

Just in case please let me know your email id or I'll post it again if I'm stuck or threw error.

Thanks again!!

Rishi

[Former Member · Accepted solution]

Hi Ajay,

I tested yesterday and it threw total 8 records for Full and Init and 0 for Delta but this morning I again tested it is throwing 8 records for full and init and 5 for delta. We haven't changed or added any records so far but how can that bring 5 records. please help!! I again turned this thread as not answered. Once I get the solution turn it back to answered and assign points. Thanks!!

Rishi

[Former Member]

Delta is coming with 5 records without any change.

[Former Member]

When you put AEDAT as delta, and put a safety limit (1), it will bring data where

AEDAT >= system-date - 1.

So, it is possible there were 5 records with AEDAT = 20071030 which were not picked yesterday, but were picked today.

However it should bring that only one time, not every time. Do check the AEDAT value in those 5 records. Are you running the delta through infopackage or testing in RSA3?

[Former Member]

Hi Ajay,

Thanks for your quick reply. It is taking those 5 records from the existing 8 records only and that too those were updated on 05072007 only. The tables that we are using will be updated only once in a month... If it is updating from somewhere else then what is the best place to check?

[Former Member]

I checked both in RSA3 and also loading data thru InfoPack. BTW, I'll create a new thread as I can not assign points it seems.

Rishi