Doubts in using INNER JOIN

[Former Member]

Hi Experts,

I am working on customizing a stock summary report.

I have to output the details of Materials which are deleted in a given time period.

I have 10 select-options from 4 different tables i.e MARA, MARC, MBEW & EKPO.

I am using innner join for the same.

My doubts are:

1. Is it the best way?????

2. How do i find out the Quantity of material which was deleted???

3. Are there any tables where i can search for BUKRS(Compnay code) & WERKS(Plant) based on MATNR (Material Number)????

[Former Member]

Hi Experts,

I am working on customizing a stock summary report.

I have to output the details of Materials which are deleted in a given time period.

I have 10 select-options from 4 different tables i.e MARA, MARC, MBEW & EKPO.

I am using innner join for the same.

My doubts are:

1. Is it the best way?????

2. How do i find out the Quantity of material which was deleted???

3. Are there any tables where i can search for BUKRS(Compnay code) & WERKS(Plant) based on MATNR (Material Number)????

[former_member386202]

Hi,

Dont use joins it will affect on performance better to use FOR ALL ENTRIES

Refer this code

&----

---

*& Form sub_read_mara *

&----

---

• This form will Select the material number material type material *

• group & batch management indicator from table mara using given *

• material number *

----

---

FORM sub_read_mara .

*--Select query to pick the material number material group material type

• and batch management from table MARA

SELECT matnr "Material Number

mtart "Material Type

matkl "Material Group

xchpf "Batch Management

FROM mara "Material Master

INTO TABLE i_mara

WHERE matnr IN s_matnr

AND mtart IN s_mtart

AND matkl IN s_matkl .

*--Check Subrc

IF sy-subrc <> 0.

*--No data found for the given selection criteria.

MESSAGE i001.

LEAVE TO LIST-PROCESSING.

ELSE.

*--Sort table by Material Number

SORT i_mara BY matnr.

ENDIF.

ENDFORM. "sub_read_mara

&----

---

*& Form sub_read_marc *

&----

---

• This form will Select the material number plant purchasing group & *

• MRP controller from table MARC using given material number plant & *

• purchasing group. *

----

---

FORM sub_read_marc .

IF NOT i_mara[] IS INITIAL.

*--Select query to pick the material number plant purchasing group &

• MRP controller from table MARC

SELECT matnr "Material Number

werks "Plant

ekgrp "Purchasing Group

dispo "MRP Controller

FROM marc "Material Master

INTO TABLE i_marc

FOR ALL ENTRIES IN i_mara

WHERE matnr = i_mara-matnr

AND werks IN s_werks

AND ekgrp IN s_ekgrp

AND dispo IN s_dispo.

*--Check Subrc

IF sy-subrc <> 0.

MESSAGE i001.

LEAVE TO LIST-PROCESSING.

ELSE.

*--Sort table by Material Number Plant

SORT i_marc BY matnr werks.

ENDIF.

ENDIF.

ENDFORM. "sub_read_marc

&----

---

*& Form sub_read_t320 *

&----

---

• This form retrieve warehouse number from table t320 *

----

---

FORM sub_read_t320 .

IF NOT i_marc[] IS INITIAL.

*--Select query to pick the plant storage location & warehouse number

SELECT werks "Plant

lgort "Storage Location

lgnum "Warehouse Number

FROM t320

INTO TABLE i_t320

FOR ALL ENTRIES IN i_marc

WHERE werks = i_marc-werks

AND lgort IN s_lgort.

*--Check Subrc

IF sy-subrc = 0.

*--Sort table by Plant Storage Location

SORT i_t320 BY werks lgort.

ENDIF.

ENDIF.

ENDFORM. "sub_read_t320

Regards,

Prashant

[Former Member]

Thanks Prashant.

this solves first part of my question.

but i want to know the quantity & value of material which was deleted.

how do i go about it???

Thanks.

[Former Member]

hi,

u can fine werks from MARC

[Former Member]

Hi Gaurav,

as we alll know tht using inner join is a performance issue i suggest not to use this instead use for all entries this might avoid the performance issue.

if u want to check the tables u need go to tcode se15 or se84 and give the field names and u can get the tables .

i am sending the links between the tables since u can get the exact relationships between thm....

http://www.erpgenie.com/abap/tables.htm

Hope this might be helpful to u

Regards,

Sana M.

reward if helpful.....

[Former Member]

Hi sana,

Thanks for the reply.

So now part1 and part3 of my question is answered.

Still i would like to know if which field tells me the Quanitity of Material deleted,

as i have to display the Quantity of material deleted and its value using ALV.

Can i use the Fields MENGE & VKWRT from table ISEG for the same???