Stock Analysis

[Former Member]

Hi Experts,

I am doing a report on stock analysis,

And I am in trouble for one of the field ' Po quantity ' which is calculated

by , sum of ekpo-menge and substracting it from ekbe-menge which is

[ movement type ( 101 ) - (102) is ekbe-menge] .

Both ekpo and ekbe is based on the matnr from mard table.

Finally I got the answer.... but only for few records the report is generated that to taking lots of time

and for huge records its showing dump error.

Please advice me is there any other method to calculate this Po quantity else guide me in correcting this error.

Thanks

R.Karthik

[Former Member]

If you are looking for the "open" PO quantity, you may want to try using table EKET (PO Schedule lines). The open qty then becomes the sum of EKET-MENGE(scheduled qty) minus the sum of EKET-WEMNG (delivered qty).

Going to EKBE is problematic from the standpoint of the records that are not goods moovement related - invoice postings, etc.

Hope this info helps,

Mark

[Former Member]

Thanks a lot Mark....My Problem has been solved

[Former Member]

but, bit curious that, is ur functional folks did not provided the tables info?( i know, its not mandatory to provide, bcoz, these r basics!)

thanq

Edited by: SAP ABAPer on Feb 21, 2009 4:51 AM

[Former Member]

Mark,

Still am getting dump error for huge data's... Please advice.

Thanks

Karthik.

[Former Member]

Still am getting dump for huge data's

[Former Member]

How are you selecting your data? Make sure to add a where clause of

WHERE ...

AND EKET_{MENGE > EKET}WEMNG

...

This will slim down the number of rows that you are evaluating. Otherwise you are evaluating all PO items. If this is not your issue or this does not help, could you post your SELECT statement in your response?

Thanks,

Mark

[Former Member]

Hi Mark,

I have two radio buttons 1 for displaying open orders and the other one for Po balance.

In open order's the value comes * SUM ( MENGE ) WHERE WEMNG = 0.

And for Po balance SUM( MENGE ) - SUM ( WEMNG )

For both the radio buttons I am getting dump error for huge data's please advice if my logic is wrong.

My querry goes like this :

First I take matnr from Mard.

SELECT * FROM MARD AS A INNER JOIN MARA AS B ON A_{MATNR = B}MATNR

INTO CORRESPONDING FIELDS OF TABLE IT_MARD

WHERE A_{MATNR IN MATNR AND A}WERKS IN WERKS

AND B~MTART IN MTART.

Then i connect ekpo to fetch ebeln

SELECT * FROM EKPO INTO CORRESPONDING FIELDS OF TABLE IT_EKPO FOR ALL

ENTRIES IN IT_MARD WHERE MATNR EQ IT_MARD-MATNR

AND WERKS EQ IT_MARD-WERKS.

LOOP AT IT_MARD.

MOVE-CORRESPONDING IT_MARD TO IT_FINAL1.

LOOP AT IT_EKPO WHERE MATNR EQ IT_MARD-MATNR

AND WERKS EQ IT_MARD-WERKS.

MOVE IT_EKPO-EBELN TO IT_FINAL1-EBELN.

MOVE IT_EKPO-EBELP TO IT_FINAL1-EBELP.

APPEND IT_FINAL1.

CLEAR: IT_EKPO,IT_MARD,IT_FINAL1-MINBE,IT_FINAL1-MENGE

,IT_FINAL1-STOCK.

ENDLOOP.

ENDLOOP.

Then I connect eket to fetch menge and wemng

SELECT * FROM EKET INTO CORRESPONDING FIELDS OF TABLE IT_EKET

FOR ALL ENTRIES IN IT_FINAL1 WHERE EBELN EQ IT_FINAL1-EBELN

AND EBELP EQ IT_FINAL1-EBELP.

Thanks.

Karthik

[Former Member]

Wow - that is a lot of data. I hope you have some WHERE clauses in your actual code, as what you have written will bring back A LOT of data.

Here's some ideas:

1) If you aren't using any data from MARD to slim your query (a WHERE statement against a MARD field) I would use MARC. This is the material per plant, whereas MARD goes to the storage location level (LGORT). If you have multiple storage locations per plant, this multiples your data.

2) Don't do a SELECT * - you're bringing back way to much data from the tables. Bring only the fields you need.

3) Move your SQL queries to more INNER JOINS and bypass some of the loops.

4) For your WHERE statement, it's probably worth doing a dynamic WHERE clause based on the user's choice for the radio-button re: open orders vs. PO balance.

For instance here's something I tried, and it works:

TABLES: mara.

TYPES: BEGIN OF typ_item,

matnr TYPE matnr,

werks TYPE werks_d,

menge TYPE etmen,

wemng TYPE weemg,

END OF typ_item.

DATA: gt_item TYPE TABLE OF typ_item,

gs_item TYPE typ_item,

gt_where TYPE TABLE OF string.

SELECT-OPTIONS: s_matnr FOR mara-matnr,

s_mtart FOR mara-mtart.

PARAMETERS: r_open RADIOBUTTON GROUP rad1,

r_bal RADIOBUTTON GROUP rad1.

START-OF-SELECTION.

APPEND 'MARA~MATNR IN S_MATNR' TO gt_where.

APPEND 'AND MARA~MTART IN S_MTART' TO gt_where.

CASE 'X'.

WHEN r_open.

APPEND 'AND EKET~WEMNG = 0' TO gt_where.

WHEN r_bal.

APPEND 'AND EKET_{MENGE GT EKET}WEMNG' TO gt_where.

ENDCASE.

SELECT mara~matnr

marc~werks

SUM( eket_{menge ) SUM( eket}wemng )

INTO TABLE gt_item

FROM ekpo INNER JOIN mara ON ekpo_{matnr = mara}matnr

INNER JOIN marc ON ekpo_{matnr = marc}matnr

AND ekpo_{werks = marc}werks

INNER JOIN eket ON ekpo_{ebeln = eket}ebeln

AND ekpo_{ebelp = eket}ebelp

WHERE (gt_where)

GROUP BY mara_{matnr marc}werks.

LOOP AT gt_item INTO gs_item.

WRITE:/ gs_item-matnr, gs_item-werks, gs_item-menge, gs_item-wemng.

ENDLOOP.

Hope this helps,

Mark

[Former Member]

Hello Mark,

You r genius, its working ...thanks for your kind help.

I have one more question Mark I have read some where that , try to avoid inner joins as it reduces the performance of the program, is it true?

Thanks

Karthik

[Former Member]

Not in my experience - INNER JOINS are my preferred way to query. Some people will argue about defined database views vs. an INNER JOIN, I prefer the INNER JOIN rather than clutter up the data dictionary with lots of views.

I believe the order of performance goes from (best to worst)

1) INNER JOIN / view

2) FOR ALL ENTIRES

3) nested SELECT's e.g. (these are to be avoided when possible)

example:

SELECT * FROM EKPO INTO TABLE x WHERE...

LOOP AT x.

SELECT * FROM EKET INTO TABLE z WHERE EBELN = x-ebeln.

ENDLOOP.

Hope this has helped you out.

-Mark

[Former Member]

Hi Mark,

Thanks a lot !! it was very helpful for me.

Karthik

[Former Member]

Hi Mark,

Could you please help me with this, I am facing problem with title as the character length is only 70

please advice me how to overcome this situation.

My requirement is :

DATA: TITLE TYPE LVC_TITLE.

FORM TITLE.

WRITE BUDAT-LOW DD/MM/YYYY TO DATE1.

WRITE BUDAT-HIGH DD/MM/YYYY TO DATE2.

CONCATENATE IT_FINAL-NAME1 'Reconcilliation Statement From'

DATE1 'TO' DATE2

INTO TITLE SEPARATED BY SPACE .

ENDFORM. "TITLE

My out put is: ABCD Electronincs India Ltd Reconcilliation Statement From 02.02.1998 to 03.

I am not getting the budat-low , it is reducing as per the length of the lfa1-name1.

Please advice.

Karthik

[Former Member]

Try using the "Top of Page" section. In the function module REUSE_ALV_GRID_DISPLAY, you can designate a subroutine name that is performed during the top-of-page event. The import parameter is i_callback_top_of_page. In that subroutine you call function module REUSE_ALV_COMMENTARY_WRITE. This way you can have multiple lines in your header. Check out the structure for the function module do determine how to fill it - you can get fancy with bolding, italicized, etc.

Hope this addresses your need.

Regards,

Mark

[Former Member]

Hi Mark,

Thank you,

Please advice me on the below as I am facing trouble if my vendor name is too lengthy.

my out put is:

Subcontracting Ven

• Reconcilliation Statement From 22.12.2006 T*

it should be:

Subcontracting Vendors

Reconcilliation Statement From 22.12.2006 TO 16.02.2009.

Please advice if there is any thing wrong with my coding.

FORM GETEVENTS  USING  GT_EVENTS TYPE SLIS_T_EVENT.
 
  CALL FUNCTION 'REUSE_ALV_EVENTS_GET'
   EXPORTING
     I_LIST_TYPE           = 0
   IMPORTING
     ET_EVENTS             = GT_EVENTS
* EXCEPTIONS
*   LIST_TYPE_WRONG       = 1
*   OTHERS                = 2
            .
  IF SY-SUBRC  0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.
 
  READ TABLE GT_EVENTS INTO WA_EVENTS WITH KEY NAME = 'TOP_OF_PAGE'.
  WA_EVENTS-FORM = 'TOP_OF_PAGE'.
 
  MODIFY EVENT FROM WA_EVENTS TRANSPORTING FORM WHERE NAME = WA_EVENTS-NAME.
 
ENDFORM.                    "GETEVENTS
 

FORM TOP_OF_PAGE.
 
  DATA : LV_DATE(24) TYPE C.
  WRITE BUDAT-LOW DD/MM/YYYY TO DATE1.
  WRITE BUDAT-HIGH DD/MM/YYYY TO DATE2.
 
  CONCATENATE  DATE1 'TO' DATE2 INTO LV_DATE SEPARATED BY SPACE.
 
  WA_TOP-TYP = 'S'.
  WA_TOP-INFO = IT_FINAL-NAME1.
  APPEND WA_TOP TO IT_TOP.
  CLEAR WA_TOP.
 
  WA_TOP-TYP = 'S'.
  WA_TOP-KEY = 'Recon.Statement From'.
  WA_TOP-INFO = LV_DATE.
  APPEND WA_TOP TO IT_TOP.
  CLEAR WA_TOP.
 
  CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE'
    EXPORTING
      IT_LIST_COMMENTARY       = IT_TOP
*     I_LOGO                   =
*     I_END_OF_LIST_GRID       =
*     I_ALV_FORM               =
            .
 
ENDFORM.                    "TOP_OF_PAGE

Please advice.

Karthik

[Former Member]

I have a couple thoughts on things to try - but I don't know for sure.

Usually, I don't use the 'REUSE_ALV_EVENTS_GET' function module to declare the TOP_OF_PAGE. I usually set this directly in the REUSE_ALV_GRID_DISPLAY on the import parameter I_CALLBACK_TOP_OF_PAGE. I don't know if this makes any difference, though. I just haven't tried it that way before.

Also - have you tried different types of lines in the header (in your code, WA_TOP-TYP)? You may want to try an 'H' - header type. From looking at your code given though, it appears it should work. But you didn't include all of your data declarations - WA_TOP, IT_TOP, IT_FINAL, etc - so I can't be completely sure. Have you tried debugging this? Since performing part of the action, it may be easy to catch if you set a breakpoint in your TOP_OF_PAGE subtroutine.

Hope this gives you some ideas.

Regards,

Mark

[Former Member]

Hi Mark,

Thanks again....for your kind information it was very useful for me.

Please can go thru the below link and let me if you could help me on this

as I am having problem with move-corresponding

[;

Thanks

Karthik

[Former Member]

Hi Mark,

Thanks again !! here is one more !!

This is regarding move-corresponding..... In order to get multiple records from another internal

table is there any other way.

Mark please have a look on the below link where am facing another problem

in moving 0 or ' ' to the values which are repeating.

I have sorted the character values but sorting of numeric values is impossible so I have to move 0 or ' ' to those repeating values. Please have a look and advice me if u have a min.

[;

Thanks

Karthik