SAP BW Generic Function Module Extractor

[Former Member]

Hi SAP Professionals,

I have created generic FM extractor, which contains total of 39 records. and i replicated and load the data into BW.

Total 39 records came into BW but

the status is in yellow state. every thing is green but only one step is in Yellow and showing

"Missing Message : Selection completed"

if make all the steps to green i think i can overcome this error

because of this i think i am unable to display the output in BW

then we went to job overview in source system their i have notice that the job is in cancel state.

Can any one please help me how to change the status to green automatically, i am unable to fix the bug kindly do the needful.

[raymond_giuseppi]

Does your FM raise the NO_MORE_DATA exception when it returns no more data?

[Former Member]

Hi Raymond,

yes i have maintain No_more_data exception.

[Sandra_Rossi]

The question was: does it raise the exception NO_MORE_DATA?

[raymond_giuseppi]

Sandra is right, as soon as the FM returns no data (and not when,it reach end of file if some data found) don't forget to raise the exception NO_MORE_DATA else BW will call it again and again...

[Former Member]

Hi Sandra,

No it is not raising the exception NO_MORE_DATA.

[raymond_giuseppi]

So infinite loop, BW will call the extractor til the no more data exception is raised...

(If the size of data will always been small, and you don't want to actually handle a cursor or similar tool, raise the exception at second call.)

[Loed]

Hi,

You may use the FM RSAX_BIW_GET_DATA_SIMPLE as your sample then just remove some of the codes not needed in your customized FM.

Regards,

Loed

[Former Member]

Hi,

Pl check below statements in your code by comparing it with FM RSAX_BIW_GET_DATA_SIMPLE

OPEN CURSOR WITH HOLD s_cursor FOR ...

FETCH NEXT CURSOR s_cursor
APPENDING CORRESPONDING FIELDS
OF TABLE e_t_data
PACKAGE SIZE s_s_if-maxsize.

IF sy-subrc <> 0.
CLOSE CURSOR s_cursor.
RAISE no_more_data.
ENDIF.

As suggested by Shanthi Bhaskar Pl revisit FM RSAX_BIW_GET_DATA_SIMPLE

Regards,

Sucheta

[Former Member]

Hi Sucheta Babar,

But i used only select queries in the program.

[shanthi_bhaskar]

Does your RSA3 brings data w/o any issue?

[Former Member]

Yes, it is working perfect in RSA3 transaction

[raymond_giuseppi]

RSA3 executes the extraction a defined number of times (Calls) so no infinite loop occurs... (during a test, check how many times the extractor returned data)

[Former Member]

can you please share the code?

[Former Member]

I am sorry Mr. Raymond. i did not understand your post, could you please explain what u exactly mean ?

[shanthi_bhaskar]

Did you write your FM code by using the RSAX_BIW_GET_DATA_SIMPLE, If not you might had some code.

Please revisit.

[Former Member]

hi Shanthi,

Yes i wrote my code by copying the standard function module " RSAX_BIW_GET_DATA_SIMPLE"

and changed the code according to my requirement. if you have any idea please share with us regarding the above query.

[shanthi_bhaskar]

Please share ur code.

[Former Member]

SPAN { font-family: "Courier New"; font-size: 10pt; color: #000000; background: #FFFFFF; } .L0S31 { font-weight: bold; font-style: italic; color: #68838B; } .L0S32 { color: #3399FF; } .L0S33 { color: #4DA619; } .L0S52 { color: #0000FF; } .L0S55 { color: #800080; } .L0S70 { color: #808080; }

FUNCTION ZBW_ZKPI_MRP_USAGE_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
*" 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_ZKPI_MRP_USAGE_ST OPTIONAL
*" EXCEPTIONS
*" NO_MORE_DATA
*" ERROR_PASSED_TO_MESS_HANDLER
*"----------------------------------------------------------------------
* Example: DataSource for table SFLIGHT
TABLES: ZBW_ZKPI_MRP_USAGE_ST.

* 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_WERKS FOR EBAN-WERKS.

TYPES: BEGIN OF T_EBAN,
WERKS TYPE EBAN-WERKS,
BANFN TYPE EBAN-BANFN,
BNFPO TYPE EBAN-BNFPO,
BSART TYPE EBAN-BSART,
ESTKZ TYPE EBAN-ESTKZ,
MATNR TYPE EBAN-MATNR,
MTART TYPE MARA-MTART,
MAKTX TYPE MAKT-MAKTX,
END OF T_EBAN,

BEGIN OF T_EBAN1,
BANFN TYPE EBAN-BANFN,
BNFPO TYPE EBAN-BNFPO,
BSART TYPE EBAN-BSART,
ESTKZ TYPE EBAN-ESTKZ,
MATNR TYPE EBAN-MATNR,
WERKS TYPE EBAN-WERKS,
MTART TYPE MARA-MTART,
MAKTX TYPE MAKT-MAKTX,
END OF T_EBAN1,


BEGIN OF T_MARA,
MATNR TYPE MARA-MATNR,
MTART TYPE MARA-MTART,
MAKTX TYPE MAKT-MAKTX,
END OF T_MARA,

BEGIN OF T_EKKO,
EBELN TYPE EKKO-EBELN,
BUKRS TYPE EKKO-BUKRS,
BSTYP TYPE EKKO-BSTYP,
BSART TYPE EKKO-BSART,
END OF T_EKKO,

BEGIN OF T_OUTPUT,
WERKS TYPE EBAN-WERKS,
TOTAL_SA TYPE P,
MRP_SA TYPE P,
SA_PER TYPE P DECIMALS 2,
TOTAL_PR TYPE P,
MRP_PR TYPE P,
PR_PER TYPE P DECIMALS 2,
END OF T_OUTPUT.

DATA: TOTAL_SA TYPE P,
MRP_SA TYPE P,
SA_PER TYPE P DECIMALS 2.

TYPES : BEGIN OF T_EKET,
BUKRS TYPE BUKRS,
EBELN TYPE EBELN,
EBELP TYPE EBELP,
ETENR TYPE ETENR,
ESTKZ TYPE ESTKZ,
END OF T_EKET.


*TYPES: BEGIN OF T_EKET.
*TYPES: BUKRS TYPE EKKO-BUKRS.
* INCLUDE STRUCTURE EKET.
*TYPES: END OF T_EKET.

DATA: WA_EBAN TYPE T_EBAN,
IT_EBAN TYPE STANDARD TABLE OF T_EBAN,
WA_EBAN1 TYPE T_EBAN1,
IT_EBAN1 TYPE STANDARD TABLE OF T_EBAN1,
WA_MARA TYPE T_MARA,
IT_MARA TYPE STANDARD TABLE OF T_MARA,
WA_EKKO TYPE T_EKKO,
IT_EKKO TYPE STANDARD TABLE OF T_EKKO,
WA_EKET TYPE T_EKET,
IT_EKET TYPE STANDARD TABLE OF T_EKET,
WA_OUT TYPE T_OUTPUT,
IT_OUT TYPE STANDARD TABLE OF T_OUTPUT.

DATA : WA_E_T_DATA TYPE ZBW_ZKPI_MRP_USAGE_ST.

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

IF S_S_IF-T_SELECT IS INITIAL.

DATA: LV_LOW TYPE EBAN-WERKS,
LV_HIGH TYPE EBAN-WERKS.
CLEAR:LV_LOW,LV_HIGH.

SELECT MIN( WERKS ) FROM EBAN INTO LV_LOW.
L_R_WERKS-SIGN = 'I'.
L_R_WERKS-OPTION = 'BT'.
L_R_WERKS-LOW = LV_LOW.

SELECT MAX( WERKS ) FROM EBAN INTO LV_HIGH.

L_R_WERKS-HIGH = LV_HIGH.
APPEND L_R_WERKS.


ELSE.
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.
ENDIF.
* 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.
"First data package ?

* Get all the PRs with materials as per the plant selected
SELECT

BANFN
BNFPO
BSART
ESTKZ
MATNR
WERKS

FROM EBAN
INTO TABLE IT_EBAN1
WHERE MATNR NE ' ' AND
WERKS IN L_R_WERKS.

MOVE-CORRESPONDING IT_EBAN1 TO IT_EBAN.


IF IT_EBAN IS NOT INITIAL.

*Get material type/description for all the PRs
SELECT A~MATNR
A~MTART
B~MAKTX
FROM MARA AS A
INNER JOIN MAKT AS B
ON A~MATNR EQ B~MATNR
INTO TABLE IT_MARA
FOR ALL ENTRIES IN IT_EBAN
WHERE A~MATNR EQ IT_EBAN-MATNR
AND ( A~MTART EQ 'ZROH' OR A~MTART EQ 'HALB')
AND B~SPRAS EQ 'EN'.

ENDIF.

*Filter PRs by mat. type "RAW" and "Semi-Finished"
IF IT_EBAN IS NOT INITIAL.
LOOP AT IT_EBAN INTO WA_EBAN.
READ TABLE IT_MARA INTO WA_MARA WITH KEY MATNR = WA_EBAN-MATNR.
IF SY-SUBRC EQ 0.
WA_EBAN-MTART = WA_MARA-MTART.
WA_EBAN-MAKTX = WA_MARA-MAKTX.
MODIFY IT_EBAN FROM WA_EBAN TRANSPORTING MTART MAKTX.
ELSE.
* Delete PRs if material is not found
DELETE IT_EBAN.
ENDIF.
ENDLOOP.
ENDIF.

SORT IT_EBAN BY WERKS.

* Percentage calculation
LOOP AT IT_EBAN INTO WA_EBAN.
WA_OUT-WERKS = WA_EBAN-WERKS.
* Count total PRs
WA_OUT-TOTAL_PR = WA_OUT-TOTAL_PR + 1.
* Count PRs created by MRP run
IF WA_EBAN-ESTKZ = 'B'.
WA_OUT-MRP_PR = WA_OUT-MRP_PR + 1.
ENDIF.
* By each plant calculcate percentage MRP Usage
AT END OF WERKS.
WA_OUT-PR_PER = ( WA_OUT-MRP_PR / WA_OUT-TOTAL_PR ) * 100.
APPEND WA_OUT TO IT_OUT.
CLEAR: WA_OUT.
ENDAT.
ENDLOOP.

***Scheduling agreement MRP Usage
*Get all SAs by plant selected
* SELECT * FROM EKKO INTO CORRESPONDING FIELDS OF TABLE IT_EKKO

SELECT
EBELN
BUKRS
BSTYP
BSART
FROM EKKO
INTO TABLE IT_EKKO
WHERE BUKRS IN L_R_WERKS AND
BSTYP EQ 'L'.

*Get SA lines for all the SAs selected above
IF IT_EKKO IS NOT INITIAL.
* SELECT * FROM EKET INTO CORRESPONDING FIELDS OF TABLE IT_EKET
SELECT

EBELN
EBELP
ETENR
ESTKZ
FROM EKET
INTO CORRESPONDING FIELDS OF TABLE IT_EKET
FOR ALL ENTRIES IN IT_EKKO
WHERE EBELN EQ IT_EKKO-EBELN.
ENDIF.

LOOP AT IT_EKET INTO WA_EKET.
READ TABLE IT_EKKO INTO WA_EKKO WITH KEY EBELN = WA_EKET-EBELN.
WA_EKET-BUKRS = WA_EKKO-BUKRS.
MODIFY IT_EKET FROM WA_EKET TRANSPORTING BUKRS .
ENDLOOP.



SORT IT_EKET BY BUKRS.

* Percentage calculation
LOOP AT IT_EKET INTO WA_EKET.
* Count total SAs
TOTAL_SA = TOTAL_SA + 1.
* Count SAs created by MRP run
IF WA_EKET-ESTKZ = 'B'.
MRP_SA = MRP_SA + 1.
ENDIF.
AT END OF BUKRS.
* By each plant calculcate percentage MRP Usage
SA_PER = ( MRP_SA / TOTAL_SA ) * 100.
READ TABLE IT_OUT INTO WA_OUT WITH KEY WERKS = WA_EKET-BUKRS.
IF SY-SUBRC = 0.
WA_OUT-TOTAL_SA = TOTAL_SA.
WA_OUT-MRP_SA = MRP_SA.
WA_OUT-SA_PER = SA_PER.
MODIFY IT_OUT FROM WA_OUT INDEX SY-TABIX TRANSPORTING TOTAL_SA MRP_SA SA_PER.
ELSE.
WA_OUT-TOTAL_SA = TOTAL_SA.
WA_OUT-MRP_SA = MRP_SA.
WA_OUT-SA_PER = SA_PER.
WA_OUT-WERKS = WA_EKET-BUKRS.
APPEND WA_OUT TO IT_OUT.
ENDIF.
CLEAR: WA_OUT,
TOTAL_SA,
MRP_SA,
SA_PER.
ENDAT.
ENDLOOP.

SORT IT_OUT BY WERKS.

LOOP AT IT_OUT INTO WA_OUT.
WA_E_T_DATA-WERKS = WA_OUT-WERKS.
WA_E_T_DATA-TOTAL_SA = WA_OUT-TOTAL_SA.
WA_E_T_DATA-MRP_SA = WA_OUT-MRP_SA.
WA_E_T_DATA-SA_PER = WA_OUT-SA_PER.
WA_E_T_DATA-TOTAL_PR = WA_OUT-TOTAL_PR.
WA_E_T_DATA-MRP_PR = WA_OUT-MRP_PR.
WA_E_T_DATA-PR_PER = WA_OUT-PR_PER.

WA_E_T_DATA-EXT_DATE = SY-DATUM.

APPEND WA_E_T_DATA TO E_T_DATA.
CLEAR : WA_E_T_DATA,WA_OUT.

ENDLOOP.

IF s_counter_datapakid GE 1.
RAISE no_more_data.
ENDIF.

s_counter_datapakid = s_counter_datapakid + 1.
ENDIF.
ENDIF.
ENDFUNCTION.