topic Re: inactive materials tables in Application Development and Automation Discussions

inactive materials tables

Former Member — Tue, 10 May 2011 13:40:56 GMT

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

Re: inactive materials tables

Former Member — Tue, 10 May 2011 14:04:18 GMT

Use a range with EQ for BWART and in select query write 'BWART NOT IN r_bwart[].

Re: inactive materials tables

Former Member — Tue, 10 May 2011 14:08:33 GMT

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.

Re: inactive materials tables

former_member312349 — Tue, 10 May 2011 20:15:53 GMT

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.

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