MARD and MCHB table join for stock display

[Former Member]

Dear Experts,

I've a requirement to develop a report which generates the output with the freestock for which i've joining th 2 tables of MCHB and MARD  with the key field is matnr. .

If i pass the material into the selection screen the material stock present in MCHB is only flowing and the material which is in MARD is not flowing.

Need help . It's urgent requirement

Piece of coding is as below.

LOOP AT GT_MCHB INTO GS_MCHB.
     GS_FINAL-MATNR = GS_MCHB-MATNR.
     GS_FINAL-WERKS = GS_MCHB-WERKS.
     GS_FINAL-LGORT = GS_MCHB-LGORT.
     GS_FINAL-CHARG = GS_MCHB-CHARG.
     GS_FINAL-J_3ASIZE = GS_MCHB-J_3ASIZE.
     GS_FINAL-CLABS = GS_MCHB-CLABS.

  READ TABLE GT_MAKT INTO GS_MAKT WITH KEY MATNR = GS_MCHB-MATNR.
  GS_FINAL-MAKTX = GS_MAKT-MAKTX.

READ TABLE GT_MARD INTO GS_MARD WITH KEY MATNR = GS_MAKT-MATNR.
GS_FINAL-MATNR = GS_MARD-MATNR.


  APPEND GS_FINAL TO GT_FINAL.

Best rgds/thnks,

Srikanth.

[Former Member]

Hi Srikanth,

As per my understanding from the above query, the data for a particular material is being fetched from MCHB but its corresponding data from MARD is not coming.

If this is the case then please also share the select query where data is being fetched from both tables.

Regards,

Aashika

[Former Member]

Dear Aashika,

Your understanding is correct. Pls look into the below select query.

START-OF-SELECTION.
                     PERFORM GET_MCHB_DATA.
                     PERFORM GET_MAKT_DATA.
                     PERFORM GET_MARD_DATA.
                     PERFORM POPULATE_DATA.
                     PERFORM BUILD_FIELDCATALOG.
                     PERFORM ALV_DISPLAY.
*&---------------------------------------------------------------------*
*&      Form  GET_MCHB_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM GET_MCHB_DATA .
   SELECT
         MATNR
         WERKS
         LGORT
         CHARG
       J_3ASIZE
         CLABS

     FROM MCHB INTO TABLE GT_MCHB WHERE MATNR IN S_MATNR AND
                                        WERKS IN S_WERKS AND
                                        LGORT IN S_LGORT.


ENDFORM.                    " GET_MCHB_DATA
*&---------------------------------------------------------------------*
*&      Form  GET_MAKT_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM GET_MAKT_DATA .
   SELECT
         MATNR
         MAKTX

     FROM MAKT INTO TABLE GT_MAKT FOR ALL ENTRIES IN GT_MCHB WHERE MATNR = GT_MCHB-MATNR.

ENDFORM.                    " GET_MAKT_DATA

*&---------------------------------------------------------------------*
*&      Form  GET_MARD_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM GET_MARD_DATA .
   IF NOT GT_MAKT IS INITIAL.
   SELECT
         MATNR
         WERKS
         LGORT
         LABST
FROM MARD INTO TABLE GT_MARD FOR ALL ENTRIES IN GT_MAKT WHERE MATNR = GT_MAKT-MATNR.

ENDIF.
ENDFORM.                    " GET_MARD_DATA

[Former Member]

Hi,

Is data coming in table GT_MARD from the select query on MARD table. If data is coming there and when final table is being prepared, the data is not being passed on to final table then have you used Binary search in READ STATEMENT. If yes, then please check if the table GT_MARD is sorted or not.

just by looking at these code templates, it looks fine otherwise.

Regards,

Aashika

[Former Member]

Dear Aashika,

When i gone to debug by passing the material which is in MARD , the internal table GT_MARD displays the records which is huge which is not relevant to that of what the material i've passed.

In the read statement i've not used the binary search.

The problem which i'm facing is before appending to the final internal table i was trying to write the condition on if the material is not available in MCHB it has to read the table material value from MARD which i can't able to do it.  Need your advise on the same.

Best rgds/thnks,
Srikanth.

[Former Member]

Hi,

1. In the MARD select query the irrelevant data is coming. To avoid this:

    a. While fetching data from MAKT put spras = sy-langu in its where clause.

    b. Check GT_MAKT is not initial and then fetch data from MARD table. It should only give data pertaining to material entered in selection screen. Also         in select query of MARD, fetch all key fields even though they are not needed.

2. As per your logic, how is it possible that material is not in MCHB.

     Reason: first select query is on MCHB on the basis of material entered on selection screen.

                  second select query is on MAKT, on the basis of material found in MCHB.

                  third select query is on MARD, for the material in MAKT.

So, its not possible that material which is not in MCHB but is present in MARD. as your select queries says that you have fetched details only about those materials which are in MCHB.

If you want the details of material which is not in MCHB, the sequence of SELECT query should be:

1. SELECTION on MARD on the basis of material entered on selection screen.

2. SELECTION on MAKT for materials obtained from MARD. (you can put JOIN on MARD and MAKT     also)

3. SELECTION on MCHB, on the basis of material fetched form above query.

I hope it helps you in resolving your issue.

Regards,

Aashika

[VenkatRamesh_V]

Hi Srikanth,

Declare mchb, mard internal tables as hashed tables.

   SELECT

         MATNR

         WERKS

         LGORT

         CHARG

       J_3ASIZE

         CLABS

     FROM MCHB INTO TABLE GT_MCHB WHERE MATNR IN S_MATNR AND

                                        WERKS IN S_WERKS AND

                                        LGORT IN S_LGORT.

   SELECT

         MATNR

         WERKS

         LGORT

         LABST

FROM MARD INTO TABLE GT_MARD FOR ALL ENTRIES IN GT_MCHB

WHERE WERKS EQ GT_MCHB -WERKS AND

            LGORT  EQ GT_MCHB-LGORT.

"  Matching MCHB _material with mard material.

loop at gt_mchb into gs_mchb.

Read table gt_mard into gs_mard with key matnr = gs_mchb-matnr.

move-corresponding gs_mchb to gs_final.

move-corresponding gs_mard  to gs_final.

append gs_final to gt_final.

clear     gs_final.

endloop.

"Adding missing material from Mard.

loop at gt_mard into gs_mard.

read table gt_final into gs_final with key matnr = gs_mard-matnr.

if sy-subrc <> 0.

move-corresponding gs_mard to gs_final.

append gs_final to gt_final.

endif.

endloop.

Hope it helpful.

Regards,

Venkat.

[Former Member]

Dear Venkat,

The above coding is copied and executed but it shows the same result as it's executing with the output for MCHB and showing blank for the material present in MARD.

Best rgds/thnks,

Srikanth.

[Former Member]

Dear Aashika,

It's working fine.  Thanks a lot.

Best rgds/thnks,

Srikanth.