Question:
Hi,
looks like I have a similar problem to this post - /forums/viewtopic.php?t=47224&highlight=return+table
Can anyone clarify any limitations when loading into an ODS object using a return table? I can get the key fields and key figures populated, but currently all of the characteristics not in the key are blank. I have tried loading into an InfoCube and all characteristics do get populated, but now there is a problem with double counting. My preference for the time being is for an ODS if I can get it working.
If I have a return table for each characteristic, how can I be sure that my characteristic value is appropriate for the key figure - ie BW is going to keep stuff on the right row?
My code is here in case anyone can spot a problem:
DATA: BEGIN OF T_MOVEMENTS OCCURS 0,
MAT_DOC LIKE /BIC/AZSB14O0200-MAT_DOC,
MAT_ITEM LIKE /BIC/AZSB14O0200-MAT_ITEM,
DOC_YEAR LIKE /BIC/AZSB14O0200-DOC_YEAR,
MAT_PLANT LIKE /BIC/AZSB14O0200-MAT_PLANT,
MOVETYPE LIKE /BIC/AZSB14O0200-MOVETYPE,
QUANT_B LIKE /BIC/AZSB14O0200-QUANT_B,
END OF T_MOVEMENTS.
* MOVE-CORRESPONDING ICUBE_VALUES TO RESULT_TABLE.
* SELECT RECEIPTS
CLEAR T_MOVEMENTS.
CLEAR RESULT_TABLE.
SELECT MAT_DOC MAT_ITEM DOC_YEAR MAT_PLANT MOVETYPE QUANT_B
INTO CORRESPONDING FIELDS OF T_MOVEMENTS
FROM /BIC/AZSB14O0200
WHERE COORDER = COMM_STRUCTURE-PRODORDER
AND ( MOVETYPE = '101'
AND GL_ACCOUNT = '0004600000'
OR MOVETYPE = '102'
AND GL_ACCOUNT = '0004600000'
OR MOVETYPE = '261'
AND GL_ACCOUNT = '0004530000'
OR MOVETYPE = '261'
AND GL_ACCOUNT = '0004510000' ).
APPEND T_MOVEMENTS.
ENDSELECT.
LOOP AT T_MOVEMENTS.
MOVE COMM_STRUCTURE-PRODORDER TO RESULT_TABLE-PRODORDER.
MOVE COMM_STRUCTURE-FISCPER TO RESULT_TABLE-FISCPER.
MOVE COMM_STRUCTURE-FISCVARNT TO RESULT_TABLE-FISCVARNT.
MOVE T_MOVEMENTS-MAT_DOC TO RESULT_TABLE-MAT_DOC.
MOVE T_MOVEMENTS-MAT_ITEM TO RESULT_TABLE-MAT_ITEM.
MOVE T_MOVEMENTS-DOC_YEAR TO RESULT_TABLE-DOC_YEAR.
MOVE T_MOVEMENTS-MAT_PLANT TO RESULT_TABLE-MAT_PLANT.
MOVE T_MOVEMENTS-MOVETYPE TO RESULT_TABLE-MOVETYPE.
MOVE T_MOVEMENTS-QUANT_B TO RESULT_TABLE-/BIC/ZSB14QTY.
APPEND RESULT_TABLE.
ENDLOOP.
ABORT = 0.
Many thanks,
Mike
Answer:
I find it strange that it would work for your Cube but not for your ODS.
Check the data when you load into the Cube. are there a record in there where the other characteristics(that is not part of the ODS key) are blank?
What i am thinking that with the ODS overwrite, if you had a entry in the return table where the other characteristics ended up being empty, it could overwrite all those characteristics that are not keys giving you the results you are seeing.
Just somethign to check.
Answer:
Hye.
I hope that the first part of your Abap code is situated in the start routine ! If not, your table T_MOVEMENTS
will be appended every time you'll go to your update routine.
So, please put that code (modified to get better time response) into that start routine :
types : begin of st_movements,
MAT_DOC LIKE /BIC/AZSB14O0200-MAT_DOC,
MAT_ITEM LIKE /BIC/AZSB14O0200-MAT_ITEM,
DOC_YEAR LIKE /BIC/AZSB14O0200-DOC_YEAR,
MAT_PLANT LIKE /BIC/AZSB14O0200-MAT_PLANT,
MOVETYPE LIKE /BIC/AZSB14O0200-MOVETYPE,
QUANT_B LIKE /BIC/AZSB14O0200-QUANT_B,
END OF st_MOVEMENTS.
data : T_MOVEMENTS like table of w_movements,
w_movements like line of table t_movements.
SELECT MAT_DOC MAT_ITEM DOC_YEAR MAT_PLANT MOVETYPE QUANT_B
into table T_MOVEMENTS
FROM /BIC/AZSB14O0200
WHERE COORDER = COMM_STRUCTURE-PRODORDER
AND ( MOVETYPE = '101'
AND GL_ACCOUNT = '0004600000'
OR MOVETYPE = '102'
AND GL_ACCOUNT = '0004600000'
OR MOVETYPE = '261'
AND GL_ACCOUNT = '0004530000'
OR MOVETYPE = '261'
AND GL_ACCOUNT = '0004510000' ).
See the difference ???
Then, try again, and you should avoid some double counting problems.
Keep us informed.
Answer:
Thanks for your help guys. we did get this working in the end.
I'll put the code here in case any one else is interested.
----- start routine----------
PROGRAM UPDATE_ROUTINE.
*$*$ begin of global - insert your declaration only below this line *-*
* TABLES: ...
DATA: BEGIN OF T_MOVEMENTS OCCURS 0,
PRODORDER LIKE /BIC/AZSB14O0200-COORDER,
FISCPER LIKE /BIC/AZSB14O0200-FISCPER,
FISCVARNT LIKE /BIC/AZSB14O0200-FISCVARNT,
MAT_DOC LIKE /BIC/AZSB14O0200-MAT_DOC,
MAT_ITEM LIKE /BIC/AZSB14O0200-MAT_ITEM,
DOC_YEAR LIKE /BIC/AZSB14O0200-DOC_YEAR,
DOC_DATE LIKE /BIC/AZSB14O0800-DOC_DATE,
CO_AREA LIKE /BIC/AZSB14O0200-CO_AREA,
MAT_PLANT LIKE /BIC/AZSB14O0200-MAT_PLANT,
PLANT LIKE /BIC/AZSB14O0200-PLANT,
/BIC/ZRIMAT LIKE /BIC/AZSB14O0800-/BIC/ZRIMAT,
MOVETYPE LIKE /BIC/AZSB14O0200-MOVETYPE,
CHRT_ACCTS LIKE /BIC/AZSB14O0200-CHRT_ACCTS,
GL_ACCOUNT LIKE /BIC/AZSB14O0200-GL_ACCOUNT,
QUANT_B LIKE /BIC/AZSB14O0200-QUANT_B,
FACT LIKE /BIC/AZSB14O0100-/BIC/ZSB14FACT,
TOT LIKE /BIC/AZSB14O0800-/BIC/ZSB14TOT,
END OF T_MOVEMENTS.
*$*$ end of global - insert your declaration only before this line *-*
* The follow definition is new in the BW3.x
TYPES:
BEGIN OF DATA_PACKAGE_STRUCTURE.
INCLUDE STRUCTURE /BIC/CS8ZSB14C02.
TYPES:
RECNO LIKE sy-tabix,
END OF DATA_PACKAGE_STRUCTURE.
DATA:
DATA_PACKAGE TYPE STANDARD TABLE OF DATA_PACKAGE_STRUCTURE
WITH HEADER LINE
WITH NON-UNIQUE DEFAULT KEY INITIAL SIZE 0.
FORM startup
TABLES MONITOR STRUCTURE RSMONITOR "user defined monitoring
MONITOR_RECNO STRUCTURE RSMONITORS " monitoring with record n
DATA_PACKAGE STRUCTURE DATA_PACKAGE
USING RECORD_ALL LIKE SY-TABIX
SOURCE_SYSTEM LIKE RSUPDSIMULH-LOGSYS
CHANGING ABORT LIKE SY-SUBRC. "set ABORT <> 0 to cancel update
*
*$*$ begin of routine - insert your code only below this line *-*
* fill the internal tables "MONITOR" and/or "MONITOR_RECNO",
* to make monitor entries
* Check SB14 Material
DATA: I_TABIX LIKE SY-TABIX.
DATA: T_MAT_PLANT LIKE /BIC/AZSB14O0100-MAT_PLANT.
LOOP AT DATA_PACKAGE.
I_TABIX = SY-TABIX.
CLEAR T_MAT_PLANT.
* Check SB14 Material - does material exist in factors table?
SELECT SINGLE MAT_PLANT INTO T_MAT_PLANT
FROM /BIC/AZSB14O0100
WHERE MAT_PLANT = DATA_PACKAGE-MAT_PLANT
AND PLANT = DATA_PACKAGE-PLANT.
IF SY-SUBRC NE 0.
DELETE DATA_PACKAGE INDEX I_TABIX.
ENDIF.
ENDLOOP.
ABORT = 0.
*$*$ end of routine - insert your code only before this line *-*
*
ENDFORM.
----------first update rule --------------------
PROGRAM UPDATE_ROUTINE.
*$*$ begin of global - insert your declaration only below this line *-*
* TABLES: ...
DATA: BEGIN OF T_MOVEMENTS OCCURS 0,
PRODORDER LIKE /BIC/AZSB14O0200-COORDER,
FISCPER LIKE /BIC/AZSB14O0200-FISCPER,
FISCVARNT LIKE /BIC/AZSB14O0200-FISCVARNT,
MAT_DOC LIKE /BIC/AZSB14O0200-MAT_DOC,
MAT_ITEM LIKE /BIC/AZSB14O0200-MAT_ITEM,
DOC_YEAR LIKE /BIC/AZSB14O0200-DOC_YEAR,
DOC_DATE LIKE /BIC/AZSB14O0800-DOC_DATE,
CO_AREA LIKE /BIC/AZSB14O0200-CO_AREA,
MAT_PLANT LIKE /BIC/AZSB14O0200-MAT_PLANT,
PLANT LIKE /BIC/AZSB14O0200-PLANT,
/BIC/ZRIMAT LIKE /BIC/AZSB14O0800-/BIC/ZRIMAT,
MOVETYPE LIKE /BIC/AZSB14O0200-MOVETYPE,
CHRT_ACCTS LIKE /BIC/AZSB14O0200-CHRT_ACCTS,
GL_ACCOUNT LIKE /BIC/AZSB14O0200-GL_ACCOUNT,
QUANT_B LIKE /BIC/AZSB14O0200-QUANT_B,
FACT LIKE /BIC/AZSB14O0100-/BIC/ZSB14FACT,
TOT LIKE /BIC/AZSB14O0800-/BIC/ZSB14TOT,
END OF T_MOVEMENTS.
*$*$ end of global - insert your declaration only before this line *-*
FORM compute_data_field
TABLES MONITOR STRUCTURE RSMONITOR "user defined monitoring
RESULT_TABLE STRUCTURE /BIC/AZSB14O0800
USING COMM_STRUCTURE LIKE /BIC/CS8ZSB14C02
RECORD_NO LIKE SY-TABIX
RECORD_ALL LIKE SY-TABIX
SOURCE_SYSTEM LIKE RSUPDSIMULH-LOGSYS
ICUBE_VALUES LIKE /BIC/AZSB14O0800
CHANGING RETURNCODE LIKE SY-SUBRC "Do not use!
ABORT LIKE SY-SUBRC. "set ABORT <> 0 to cancel update
*
*$*$ begin of routine - insert your code only below this line *-*
* fill the internal table "MONITOR", to make monitor entries
*data: counter type i.
*while counter <> 1.
*
*endwhile.
DATA: T_MATERIAL LIKE /BI0/PPRODORDER-MATERIAL.
CLEAR T_MATERIAL.
CLEAR T_MOVEMENTS.
* SELECT RELEVANT MATERIAL MOVEMENTS FOR THE PROCESSORDER
SELECT MAT_DOC MAT_ITEM DOC_YEAR MAT_PLANT PLANT DOC_DATE
MOVETYPE CHRT_ACCTS GL_ACCOUNT QUANT_B
INTO CORRESPONDING FIELDS OF T_MOVEMENTS
FROM /BIC/AZSB14O0200
WHERE COORDER = COMM_STRUCTURE-PRODORDER
AND ( MOVETYPE = '101'
AND GL_ACCOUNT = '0004600000'
OR MOVETYPE = '102'
AND GL_ACCOUNT = '0004600000'
OR MOVETYPE = '261'
AND GL_ACCOUNT = '0004530000'
OR MOVETYPE = '261'
AND GL_ACCOUNT = '0004510000' ).
* LOOKUP USAGE FACTOR
SELECT single /BIC/ZSB14FACT INTO T_MOVEMENTS-FACT
FROM /BIC/AZSB14O0100
WHERE MAT_PLANT = T_MOVEMENTS-MAT_PLANT
AND PLANT = T_MOVEMENTS-PLANT.
T_MOVEMENTS-TOT = T_MOVEMENTS-FACT * T_MOVEMENTS-QUANT_B.
MOVE COMM_STRUCTURE-PRODORDER TO T_MOVEMENTS-PRODORDER.
MOVE COMM_STRUCTURE-FISCPER TO T_MOVEMENTS-FISCPER.
MOVE COMM_STRUCTURE-FISCVARNT TO T_MOVEMENTS-FISCVARNT.
MOVE COMM_STRUCTURE-CO_AREA TO T_MOVEMENTS-CO_AREA.
* MOVE MAT_PLANT TO REC / ISS MAT
MOVE T_MOVEMENTS-MAT_PLANT TO T_MOVEMENTS-/BIC/ZRIMAT.
* NOW LOOKUP MATERIAL FROM PROCESS ORDER
SELECT SINGLE MATERIAL INTO T_MATERIAL
FROM /BI0/PPRODORDER
WHERE PRODORDER = COMM_STRUCTURE-PRODORDER
AND OBJVERS = 'A'.
MOVE T_MATERIAL TO T_MOVEMENTS-MAT_PLANT.
APPEND T_MOVEMENTS.
ENDSELECT.
LOOP AT T_MOVEMENTS.
MOVE T_MOVEMENTS-PRODORDER TO RESULT_TABLE-PRODORDER.
MOVE T_MOVEMENTS-FISCPER TO RESULT_TABLE-FISCPER.
MOVE T_MOVEMENTS-FISCVARNT TO RESULT_TABLE-FISCVARNT.
MOVE T_MOVEMENTS-MAT_DOC TO RESULT_TABLE-MAT_DOC.
MOVE T_MOVEMENTS-MAT_ITEM TO RESULT_TABLE-MAT_ITEM.
MOVE T_MOVEMENTS-DOC_YEAR TO RESULT_TABLE-DOC_YEAR.
MOVE T_MOVEMENTS-CO_AREA TO RESULT_TABLE-CO_AREA.
APPEND RESULT_TABLE.
ENDLOOP.
ABORT = 0.
*$*$ end of routine - insert your code only before this line *-*
*
ENDFORM.
---------- subsequent update rules ------------------------
PROGRAM UPDATE_ROUTINE.
*$*$ begin of global - insert your declaration only below this line *-*
* TABLES: ...
DATA: BEGIN OF T_MOVEMENTS OCCURS 0,
PRODORDER LIKE /BIC/AZSB14O0200-COORDER,
FISCPER LIKE /BIC/AZSB14O0200-FISCPER,
FISCVARNT LIKE /BIC/AZSB14O0200-FISCVARNT,
MAT_DOC LIKE /BIC/AZSB14O0200-MAT_DOC,
MAT_ITEM LIKE /BIC/AZSB14O0200-MAT_ITEM,
DOC_YEAR LIKE /BIC/AZSB14O0200-DOC_YEAR,
DOC_DATE LIKE /BIC/AZSB14O0800-DOC_DATE,
CO_AREA LIKE /BIC/AZSB14O0200-CO_AREA,
MAT_PLANT LIKE /BIC/AZSB14O0200-MAT_PLANT,
PLANT LIKE /BIC/AZSB14O0200-PLANT,
/BIC/ZRIMAT LIKE /BIC/AZSB14O0800-/BIC/ZRIMAT,
MOVETYPE LIKE /BIC/AZSB14O0200-MOVETYPE,
CHRT_ACCTS LIKE /BIC/AZSB14O0200-CHRT_ACCTS,
GL_ACCOUNT LIKE /BIC/AZSB14O0200-GL_ACCOUNT,
QUANT_B LIKE /BIC/AZSB14O0200-QUANT_B,
FACT LIKE /BIC/AZSB14O0100-/BIC/ZSB14FACT,
TOT LIKE /BIC/AZSB14O0800-/BIC/ZSB14TOT,
END OF T_MOVEMENTS.
*$*$ end of global - insert your declaration only before this line *-*
FORM compute_data_field
TABLES MONITOR STRUCTURE RSMONITOR "user defined monitoring
RESULT_TABLE STRUCTURE /BIC/AZSB14O0800
USING COMM_STRUCTURE LIKE /BIC/CS8ZSB14C02
RECORD_NO LIKE SY-TABIX
RECORD_ALL LIKE SY-TABIX
SOURCE_SYSTEM LIKE RSUPDSIMULH-LOGSYS
ICUBE_VALUES LIKE /BIC/AZSB14O0800
CHANGING RETURNCODE LIKE SY-SUBRC "Do not use!
ABORT LIKE SY-SUBRC. "set ABORT <> 0 to cancel update
*
*$*$ begin of routine - insert your code only below this line *-*
* fill the internal table "MONITOR", to make monitor entries
loop at t_movements.
MOVE T_MOVEMENTS-PRODORDER TO RESULT_TABLE-PRODORDER.
MOVE T_MOVEMENTS-FISCPER TO RESULT_TABLE-FISCPER.
MOVE T_MOVEMENTS-FISCVARNT TO RESULT_TABLE-FISCVARNT.
MOVE T_MOVEMENTS-MAT_DOC TO RESULT_TABLE-MAT_DOC.
MOVE T_MOVEMENTS-MAT_ITEM TO RESULT_TABLE-MAT_ITEM.
MOVE T_MOVEMENTS-DOC_YEAR TO RESULT_TABLE-DOC_YEAR.
MOVE T_MOVEMENTS-TOT TO RESULT_TABLE-/BIC/ZSB14TOT.
APPEND RESULT_TABLE.
endloop.
ABORT = 0.
*$*$ end of routine - insert your code only before this line *-*
*
ENDFORM.