05-10-2011 2:40 PM
Hi,
i need to find out the materials that has no movement for a long time, time determined by user,
and i went to the mkpf-mseg tables.
However i have to select the data based on the bwart field's values.
For example if mseg~bwart is not eq to '261' 262,601,602,201,202,543,544 etc then it is an inactive material for us.
But my query is not working:
select mseg~matnr mseg~werks mseg~bwart from mseg
inner join mkpf on mseg~mblnr eq mkpf~mblnr
and mseg~mjahr eq mkpf~mjahr
into corresponding fields of table itab
for all entries in mat
where
mkpf~budat le so_budat and
mseg~matnr eq mat-matnr
and mseg~bwart ne '261'
and mseg~bwart ne '262'
and mseg~bwart ne '601'
and mseg~bwart ne '602'
and mseg~bwart ne '201'
and mseg~bwart ne '202'
and mseg~bwart ne '543'
and mseg~bwart ne '544' .
05-10-2011 3:04 PM
Use a range with EQ for BWART and in select query write 'BWART NOT IN r_bwart[].
05-10-2011 3:08 PM
Try this..
Data: lr_bwart type range of mseg-bwart,
ls_bwart like line of lr_bwart.
ls_bwart-sign = 'E'.
ls_bwart-option = 'EQ'.
ls_bwart-low = '261'.
append ls_bwart to lr_bwart.
ls_bwart-low = '262'.
append ls_bwart to lr_bwart.
ls_bwart-low = '601'.
append ls_bwart to lr_bwart.
....
select mseg~matnr mseg~werks mseg~bwart from mseg
inner join mkpf on mseg~mblnr eq mkpf~mblnr
and mseg~mjahr eq mkpf~mjahr
into corresponding fields of table itab
for all entries in mat
where mkpf~budat le so_budat and
mseg~matnr eq mat-matnr
and mseg~bwart in lr_bwart.
05-10-2011 9:15 PM
Hello.
You need to do something like that:
REPORT zdave_inactive_materials.
TYPE-POOLS: slis.
DATA: BEGIN OF tg_mkpf_mseg OCCURS 0,
bwart LIKE mseg-bwart,
matnr LIKE mseg-matnr,
werks LIKE mseg-werks,
budat LIKE mkpf-budat,
END OF tg_mkpf_mseg,
rg_bwart TYPE RANGE OF bwart,
sg_bwart LIKE LINE OF rg_bwart,
tg_fcat TYPE slis_t_fieldcat_alv,
vg_repid TYPE sy-repid.
START-OF-SELECTION.
***Here I create a list with all movements will be excluded
sg_bwart-sign = 'E'. "Means exclude
sg_bwart-option = 'EQ'.
sg_bwart-low = '601'.
APPEND sg_bwart TO rg_bwart.
CLEAR sg_bwart.
sg_bwart-sign = 'E'.
sg_bwart-option = 'EQ'.
sg_bwart-low = '602'.
APPEND sg_bwart TO rg_bwart.
***I created a view zmkpf_mseg but is the same if you use a INNER JOIN
SELECT * INTO TABLE tg_mkpf_mseg
FROM zmkpf_mseg
WHERE bwart IN rg_bwart
AND budat LE sy-datum.
***From this line on it is only for ilustrative purposes
IF sy-subrc EQ 0.
DELETE tg_mkpf_mseg WHERE matnr IS INITIAL.
vg_repid = sy-repid.
CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'
EXPORTING
i_program_name = vg_repid
i_internal_tabname = 'TG_MKPF_MSEG'
i_inclname = vg_repid
CHANGING
ct_fieldcat = tg_fcat
EXCEPTIONS
inconsistent_interface = 1
program_error = 2
OTHERS = 3.
IF sy-subrc <> 0.
WRITE: / 'Error:',sy-subrc,
'When Create Field Catalog'.
EXIT.
ENDIF.
Call ALV List Display
CALL FUNCTION 'REUSE_ALV_LIST_DISPLAY'
EXPORTING
i_callback_program = vg_repid
it_fieldcat = tg_fcat
TABLES
t_outtab = tg_mkpf_mseg
EXCEPTIONS
program_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
WRITE: / 'Error:',sy-subrc,
'When Call ALV List Display'.
EXIT.
ENDIF.
ENDIF.
END-OF-SELECTION.
By the other hand, I highly recommend don't use "FOR ALL ENTRIES" because it cause poor performance. Is better if you have a internal table with the data structure that you want store