topic Re: ALV select statement not working in Application Development and Automation Discussions

ALV select statement not working

Former Member — Mon, 28 May 2007 11:26:30 GMT

Hi all.... i have done this ALV report.. The first select statement is working and retrieving values i.e. the material details.

The second select statement to retrieve values from EKET,EKKO and EKPO is not retrieving the values based on the MATNR value of the first statement.

Can any1 help me? please give the code for the second select along with the looping statements.

*&---------------------------------------------------------------------*
*& Report  Z_MRP_DETAILS                                               *
*&                                                                     *
*&---------------------------------------------------------------------*
*&                                                                     *
*&                                                                     *
*&---------------------------------------------------------------------*

REPORT  Z_MRP_DETAILS NO STANDARD PAGE HEADING LINE-SIZE 255.

TABLES:MARA,                    "General Material Data
       MDKP,                    "Header Data for MRP Document
       MARD,                    "Storage Location Data for Material
       MSSL,                    "Total Special Stocks with Vendor
       MARC,                    "Plant Data for Material
       MDTB,                    "MRP TABLE
       EKET,                    "Scheduling Agreement Schedule Lines
       EKPO,                    "Purchasing Document Item
       EKKO.                    "Purchasing Document Header

TYPE-POOLS: SLIS.

TYPES: BEGIN OF TYP_MARD,

       MATNR TYPE MARD-MATNR,    "MATERIAL NUMBER
       LABST TYPE MARD-LABST,    "Valuated stock with unrestricted use
       UMLME TYPE MARD-UMLME,    "Stock in transfer
       INSME TYPE MARD-INSME,    "Stock in quality inspection
       EINME TYPE MARD-EINME,    "Total Stock of All Restricted Batches
       SPEME TYPE MARD-SPEME,    "Blocked stock
       RETME TYPE MARD-RETME,    "Blocked Stock Returns
       KLABS TYPE MARD-KLABS,    "Unrestricted-use consignment stock
       KINSM TYPE MARD-KINSM,    "Consignment stock in QI
       KEINM TYPE MARD-KEINM,    "Restricted-use consignment stock
       KSPEM TYPE MARD-KSPEM,    "Blocked consignment stock

       END OF TYP_MARD.

TYPES: BEGIN OF TYP_MDKP_MSSL,

       PLWRK TYPE MDKP-PLWRK,    "Plant
       DISMM TYPE MDKP-DISMM,    "MRP Type
       MTART TYPE MDKP-MTART,    "Material Type
       SUM01 TYPE MDKP-SUM01,    "Plant stock / only plant segment
       UMLMC TYPE MARC-UMLMC,    "Stock in transfer (plant to plant)
       SLLAB TYPE MSSL-SLLAB,    "Valuated stock with unrestricted use
       SLINS TYPE MSSL-SLINS,    "Stock in quality inspection
       DAT00 TYPE MDTB-DAT00,    "RECEIPT DATE

       END OF TYP_MDKP_MSSL.

TYPES: BEGIN OF TYP_EKET_EKPO,

       EBELN TYPE EKET-EBELN,    "Purchasing Document Number
       EBELP TYPE EKET-EBELP,    "Item Number of Purchasing Document
       EINDT TYPE EKET-EINDT,    "Item delivery date
       MENGE TYPE EKET-MENGE,    "Scheduled quantity
       WEMNG TYPE EKET-WEMNG,    "Quantity of goods received

       LIFNR TYPE EKKO-LIFNR,    "Account Number of the Vendor

       MATNR TYPE EKPO-MATNR,    "Material Number
       KTMNG TYPE EKPO-KTMNG,    "Target quantity
       MEINS TYPE EKPO-MEINS,    "Order Unit
       NETPR TYPE EKPO-NETPR,    "Net price in purchasing document
       PEINH TYPE EKPO-PEINH,    "Price unit

       END OF TYP_EKET_EKPO.

TYPES: BEGIN OF TYP_MARA,

       MATNR TYPE MARA-MATNR,    "MATERIAL NUMBER

       END OF TYP_MARA.

TYPES: BEGIN OF TYP_FINAL,

       MATNR TYPE MARD-MATNR,    "MATERIAL NUMBER
       LABST TYPE MARD-LABST,    "Valuated stock with unrestricted use
       UMLME TYPE MARD-UMLME,    "Stock in transfer
       INSME TYPE MARD-INSME,    "Stock in quality inspection
       EINME TYPE MARD-EINME,    "Total Stock of All Restricted Batches
       SPEME TYPE MARD-SPEME,    "Blocked stock
       RETME TYPE MARD-RETME,    "Blocked Stock Returns
       KLABS TYPE MARD-KLABS,    "Unrestricted-use consignment stock
       KINSM TYPE MARD-KINSM,    "Consignment stock QI
       KEINM TYPE MARD-KEINM,    "Restricted-use consignment stock
       KSPEM TYPE MARD-KSPEM,    "Blocked consignment stock

       PLWRK TYPE MDKP-PLWRK,    "Plant
       DISMM TYPE MDKP-DISMM,    "MRP Type
       MTART TYPE MDKP-MTART,    "Material Type
       SUM01 TYPE MDKP-SUM01,    "Plant stock / only plant segment
       UMLMC TYPE MARC-UMLMC,    "Stock in transfer (plant to plant)
       SLLAB TYPE MSSL-SLLAB,    "Valuated stock with unrestricted use
       SLINS TYPE MSSL-SLINS,    "Stock in quality inspection
       DAT00 TYPE MDTB-DAT00,    "RECEIPT DATE

       EBELN TYPE EKET-EBELN,    "Purchasing Document Number
       EBELP TYPE EKET-EBELP,    "Item Number of Purchasing Document
       EINDT TYPE EKET-EINDT,    "Item delivery date
       MENGE TYPE EKET-MENGE,    "Scheduled quantity
       WEMNG TYPE EKET-WEMNG,    "Quantity of goods received

       LIFNR TYPE EKKO-LIFNR,    "Account Number of the Vendor

       KTMNG TYPE EKPO-KTMNG,    "Target quantity
       MEINS TYPE EKPO-MEINS,    "Order Unit
       NETPR TYPE EKPO-NETPR,    "Net price in purchasing document
       PEINH TYPE EKPO-PEINH,    "Price unit

      END OF TYP_FINAL.


DATA: IT_MARD TYPE STANDARD TABLE OF TYP_MARD,
      WA_MARD TYPE TYP_MARD.

DATA: ITAB1 TYPE STANDARD TABLE OF TYP_MDKP_MSSL,
      WA_ITAB1 TYPE TYP_MDKP_MSSL.

DATA: IT_MRP TYPE STANDARD TABLE OF TYP_EKET_EKPO,
      WA_MRP TYPE TYP_EKET_EKPO.

DATA: IT_MARA TYPE STANDARD TABLE OF TYP_MARA,
      WA_MARA TYPE TYP_MARA.

DATA: IT_FINAL TYPE STANDARD TABLE OF TYP_FINAL,
      WA_FINAL TYPE TYP_FINAL.


DATA:VAR  LIKE MARA-MATNR.

DATA: FIELDCATALOG TYPE SLIS_T_FIELDCAT_ALV WITH HEADER LINE,
      GD_TAB_GROUP TYPE SLIS_T_SP_GROUP_ALV,
      GD_REPID     LIKE SY-REPID.


SELECTION-SCREEN : BEGIN OF BLOCK A.


SELECT-OPTIONS :  S_MATNR FOR MARA-MATNR OBLIGATORY.


SELECTION-SCREEN :END OF BLOCK A.

****** INPUT VALIDATION *****
AT SELECTION-SCREEN ON S_MATNR.
  SELECT SINGLE MATNR FROM MARA INTO VAR
           WHERE MATNR IN S_MATNR.
  IF SY-SUBRC <> 0.
*    MESSAGE E000.
  ENDIF.


START-OF-SELECTION.
  PERFORM DATA_RETRIEVAL.
  PERFORM BUILD_FIELDCATALOG.


END-OF-SELECTION.
  PERFORM DISPLAY_ALV_REPORT.



*&---------------------------------------------------------------------*
*&      Form  BUILD_FIELDCATALOG
*&---------------------------------------------------------------------*
*       Build Fieldcatalog for ALV Report
*----------------------------------------------------------------------*
FORM BUILD_FIELDCATALOG.


  FIELDCATALOG-FIELDNAME   = 'MATNR'.
  FIELDCATALOG-SELTEXT_M   = 'MATERIAL NUMBER'.
  FIELDCATALOG-COL_POS     = 1.
  FIELDCATALOG-KEY         = 'X'.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'LABST'.
  FIELDCATALOG-SELTEXT_M   = 'VALUATED STOCK'.
  FIELDCATALOG-COL_POS     = 2.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'UMLME'.
  FIELDCATALOG-SELTEXT_M   = 'STOCK IN TRANSFER'.
  FIELDCATALOG-COL_POS     = 3.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'INSME'.
  FIELDCATALOG-SELTEXT_M   = 'STOCK IN QI'.
  FIELDCATALOG-COL_POS     = 4.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'EINME'.
  FIELDCATALOG-SELTEXT_M   = 'TOTAL STOCK'.
  FIELDCATALOG-COL_POS     = 5.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'SPEME'.
  FIELDCATALOG-SELTEXT_M   = 'BLOCKED STOCK'.
  FIELDCATALOG-COL_POS     = 6.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'RETME'.
  FIELDCATALOG-SELTEXT_M   = 'BLOCKED STOCK RETURNS'.
  FIELDCATALOG-COL_POS     = 7.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'KLABS'.
  FIELDCATALOG-SELTEXT_M   = 'UNRESTRICTED USE'.
  FIELDCATALOG-COL_POS     = 8.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'KINSM'.
  FIELDCATALOG-SELTEXT_M   = 'CONSIGNMENT STOCK'.
  FIELDCATALOG-COL_POS     = 9.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'KEINM'.
  FIELDCATALOG-SELTEXT_M   = 'RESTRICTED USE'.
  FIELDCATALOG-COL_POS     = 10.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.


  FIELDCATALOG-FIELDNAME   = 'KSPEM'.
  FIELDCATALOG-SELTEXT_M   = 'BLOCKED C STOCK'.
  FIELDCATALOG-COL_POS     = 11.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'PLWRK'.
  FIELDCATALOG-SELTEXT_M   = 'PLANT'.
  FIELDCATALOG-COL_POS     = 12.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'DISMM'.
  FIELDCATALOG-SELTEXT_M   = 'MRP TYPE'.
  FIELDCATALOG-COL_POS     = 13.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'MTART'.
  FIELDCATALOG-SELTEXT_M   = 'MATERIAL TYPE'.
  FIELDCATALOG-COL_POS     = 14.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'SUM01'.
  FIELDCATALOG-SELTEXT_M   = 'PLANT STOCK'.
  FIELDCATALOG-COL_POS     = 15.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'UMLMC'.
  FIELDCATALOG-SELTEXT_M   = 'STOCK IN TRANSFER'.
  FIELDCATALOG-COL_POS     = 16.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'SLLAB'.
  FIELDCATALOG-SELTEXT_M   = 'VALUATED STOCK'.
  FIELDCATALOG-COL_POS     = 17.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'SLINS'.
  FIELDCATALOG-SELTEXT_M   = 'STOCK IN QI'.
  FIELDCATALOG-COL_POS     = 18.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'DAT00'.
  FIELDCATALOG-SELTEXT_M   = 'RECEIPT DATE'.
  FIELDCATALOG-COL_POS     = 19.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'EBELN'.
  FIELDCATALOG-SELTEXT_M   = 'P DOC NUM'.
  FIELDCATALOG-COL_POS     = 20.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'EBELP'.
  FIELDCATALOG-SELTEXT_M   = 'ITEM NUM'.
  FIELDCATALOG-COL_POS     = 21.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'EINDT'.
  FIELDCATALOG-SELTEXT_M   = 'ITEM DEL DATE'.
  FIELDCATALOG-COL_POS     = 22.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'MENGE'.
  FIELDCATALOG-SELTEXT_M   = 'SCHEDULED QTY'.
  FIELDCATALOG-COL_POS     = 23.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'WEMNG'.
  FIELDCATALOG-SELTEXT_M   = 'QTY OF GR'.
  FIELDCATALOG-COL_POS     = 24.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'LIFNR'.
  FIELDCATALOG-SELTEXT_M   = 'VENDOR A/c NUM'.
  FIELDCATALOG-COL_POS     = 25.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'KTMNG'.
  FIELDCATALOG-SELTEXT_M   = 'TARGET QTY'.
  FIELDCATALOG-COL_POS     = 26.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'MEINS'.
  FIELDCATALOG-SELTEXT_M   = 'ORDER UNIT'.
  FIELDCATALOG-COL_POS     = 27.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'NETPR'.
  FIELDCATALOG-SELTEXT_M   = 'NET PRICE'.
  FIELDCATALOG-COL_POS     = 28.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.

  FIELDCATALOG-FIELDNAME   = 'PEINH'.
  FIELDCATALOG-SELTEXT_M   = 'PRICE UNIT'.
  FIELDCATALOG-COL_POS     = 29.
  APPEND FIELDCATALOG TO FIELDCATALOG.
  CLEAR  FIELDCATALOG.


ENDFORM.                    " BUILD_FIELDCATALOG


*&---------------------------------------------------------------------*
*&      Form  DISPLAY_ALV_REPORT
*&---------------------------------------------------------------------*
*       Display report using ALV grid
*----------------------------------------------------------------------*
FORM DISPLAY_ALV_REPORT.
  GD_REPID = SY-REPID.
  CALL FUNCTION 'REUSE_ALV_LIST_DISPLAY'
    EXPORTING
*      I_GRID_TITLE = 'M R P DETAILS - ALV REPORT'
      IT_FIELDCAT  = FIELDCATALOG[]
    TABLES
      T_OUTTAB     = IT_FINAL.
  IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.

ENDFORM.                    " DISPLAY_ALV_REPORT.


*&--------------------------------------------------------------------*
*&      Form  DATA_RETRIEVAL
*&--------------------------------------------------------------------*
*       text
*---------------------------------------------------------------------*
FORM DATA_RETRIEVAL.

  SELECT MATNR FROM MARA INTO TABLE IT_MARA
  WHERE MATNR IN S_MATNR.
  SORT IT_MARA BY MATNR.
  DELETE ADJACENT DUPLICATES FROM IT_MARA.


  IF IT_MARA[] IS NOT INITIAL .

    SELECT MARD~MATNR
           MARD~LABST
           MARD~UMLME
           MARD~INSME
           MARD~EINME
           MARD~SPEME
           MARD~RETME
           MARD~KLABS
           MARD~KINSM
           MARD~KEINM
           MARD~KSPEM

           MDKP~PLWRK
           MDKP~DISMM
           MDKP~MTART
           MDKP~SUM01

           MARC~UMLMC

*           MSSL~SLLAB
*           MSSL~SLINS

      INTO TABLE IT_FINAL

      FROM MARD INNER JOIN MDKP ON ( MARD~MATNR = MDKP~MATNR )
                               AND ( MARD~WERKS = MDKP~PLWRK )

                INNER JOIN MARC ON ( MARD~MATNR = MARC~MATNR )
                               AND ( MARD~WERKS = MARC~WERKS )

*                INNER JOIN MSSL ON ( MARD~MATNR = MSSL~MATNR )
*                               AND ( MARD~WERKS = MSSL~WERKS )

      FOR ALL ENTRIES IN IT_MARA
      WHERE MARD~MATNR = IT_MARA-MATNR.

  ENDIF.

  SORT IT_FINAL BY MATNR.

  LOOP AT IT_FINAL INTO WA_FINAL.
    READ TABLE IT_MARA
    INTO WA_MARA WITH KEY MATNR = WA_MARA-MATNR BINARY SEARCH.
    IF SY-SUBRC = 0.
      WA_MARA-MATNR = WA_FINAL-MATNR.
      MODIFY IT_FINAL FROM WA_FINAL.
    ENDIF.
  ENDLOOP.

  IF IT_MARA[] IS NOT INITIAL .
    SELECT EKET~EBELN
           EKET~EBELP
           EKET~EINDT
           EKET~MENGE
           EKET~WEMNG

           EKKO~LIFNR

           EKPO~MATNR
           EKPO~KTMNG
           EKPO~MEINS
           EKPO~NETPR
           EKPO~PEINH

          INTO TABLE IT_MRP

          FROM EKET INNER JOIN EKKO ON ( EKET~EBELN = EKKO~EBELN )

                    INNER JOIN EKPO ON ( EKET~EBELN = EKPO~EBELN )
                                   AND ( EKET~EBELP = EKPO~EBELP )

                    FOR ALL ENTRIES IN IT_MARA
                    WHERE EKPO~MATNR = IT_MARA-MATNR.

  endif.
  LOOP AT IT_FINAL INTO WA_FINAL.
    READ TABLE IT_MRP
    INTO WA_MRP WITH KEY matnr = WA_final-matnr BINARY SEARCH.
    IF SY-SUBRC = 0.
      WA_MRP-matnr = WA_FINAL-matnr.
      APPEND WA_FINAL TO IT_FINAL.
    ENDIF.
  ENDLOOP.

ENDFORM.                    "DATA_RETRIEVAL

Re: ALV select statement not working

Former Member — Mon, 28 May 2007 11:38:07 GMT

Hi,

Did you make sure that using the material number as condition, there are really exist records in the three table join?

By the way, I don't think that using the for all entries is the most efficient way, why didn't you join the four tables together?

Hope this helps

Bob

Re: ALV select statement not working

Former Member — Mon, 28 May 2007 11:44:27 GMT

There are some material numbers that exist in all the four tables. while some are not.

Four table join in the second select? Can you please show me how?

Re: ALV select statement not working

dev_parbutteea — Mon, 28 May 2007 11:49:03 GMT

IF IT_MARA[] IS NOT INITIAL .

SELECT EKET~EBELN

EKET~EBELP

EKET~EINDT

EKET~MENGE

EKET~WEMNG

EKKO~LIFNR

EKPO~MATNR

EKPO~KTMNG

EKPO~MEINS

EKPO~NETPR

EKPO~PEINH

INTO TABLE IT_MRP

FROM EKET INNER JOIN EKKO ON ( EKET_{EBELN = EKKO}EBELN )

INNER JOIN EKPO ON ( EKET_{EBELN = EKPO}EBELN )

AND ( EKET_{EBELP = EKPO}EBELP )

FOR ALL ENTRIES IN IT_MARA

WHERE EKPO~MATNR = IT_MARA-MATNR.

endif.

put:

FROM EKPO INNER JOIN EKET on ( EKPO_{EBELN = EKET}EBELN )

AND ( EKPO_{EBELP = EKET}EBELP )

INNER JOIN EKKO ON ( EKKO_{EBELN = EKET}EBELN )

FOR ALL ENTRIES IN IT_MARA

WHERE EKPO~MATNR = IT_MARA-MATNR.

Re: ALV select statement not working

dev_parbutteea — Mon, 28 May 2007 11:51:38 GMT

EKET INNER JOIN EKKO ON ( EKET_{EBELN = EKKO}EBELN )

Re: ALV select statement not working

dev_parbutteea — Mon, 28 May 2007 11:51:54 GMT

EKET INNER JOIN EKKO ON ( EKET_{EBELN = EKKO}EBELN )

Re: ALV select statement not working

Former Member — Mon, 28 May 2007 11:58:52 GMT

hi sooness,

FROM EKPO INNER JOIN EKET on ( EKPO_{EBELN = EKET}EBELN )

AND ( EKPO_{EBELP = EKET}EBELP )

INNER JOIN EKKO ON ( EKKO_{EBELN = EKET}EBELN )

FOR ALL ENTRIES IN IT_MARA

WHERE EKPO~MATNR = IT_MARA-MATNR.

This is the code that i got to put instead of mine right ?

The loop at itab statement is correct?

Re: ALV select statement not working

Former Member — Mon, 28 May 2007 12:10:24 GMT

SELECT EKET~EBELN

EKET~EBELP

EKET~EINDT

EKET~MENGE

EKET~WEMNG

EKKO~LIFNR

EKPO~MATNR

EKPO~KTMNG

EKPO~MEINS

EKPO~NETPR

EKPO~PEINH

INTO TABLE IT_MRP

FROM EKET INNER JOIN EKKO ON ( EKET_{EBELN = EKKO}EBELN )

INNER JOIN EKPO ON ( EKET_{EBELN = EKPO}EBELN )

AND ( EKET_{EBELP = EKPO}EBELP )

INNER JOIN MARA ON ( EKPO_{MATNR = MARA}MATNR )

FOR ALL ENTRIES IN IT_MARA

WHERE MARA~MATNR IN S_MATNR.

Hope this helps.

Bob

Message was edited by:

Bob

Re: ALV select statement not working

dev_parbutteea — Mon, 28 May 2007 12:10:44 GMT

LOOP AT IT_FINAL INTO WA_FINAL.

READ TABLE IT_MARA

INTO WA_MARA WITH KEY MATNR = WA_MARA-MATNR BINARY SEARCH.

IF SY-SUBRC = 0.
WA_MARA-MATNR = WA_FINAL-MATNR.
MODIFY IT_FINAL FROM WA_FINAL.
ENDIF.

ENDLOOP.

Actually i dn't understand why u have put this code!
-1) you are modifying from WA_FINAL which wasn't modifed at all!

-2) you are doing a read statement WITH KEY MATNR , so obviously

if sy-subrc = 0, both are same, so what r u trying to do afterall???

Re: ALV select statement not working

Former Member — Mon, 28 May 2007 12:11:39 GMT

Hi dinesh,

The inner joins used by you all little bit wrong.Take inner joins based on the keys only.One mor thng is,don't take more than two inner joins,that degrades your progarm performance.

In your case take innerjoin for MARD and MARC,get all the required filed from those tables (use for all entires on previous itab IT_MARA).

Then select fields separately from tables MSSL, MDKP for all entries in the interanal table got from the above.

reward points if useful.

regards

rami

Re: ALV select statement not working

dev_parbutteea — Mon, 28 May 2007 12:14:18 GMT

READ TABLE IT_MARA

INTO WA_MARA WITH KEY MATNR = WA_MARA-MATNR BINARY SEARCH.

Also, matnr in bold above is already pointing to WA_MARA-MATNR !!

So,the code is wrong!

Please explain what you want to do!

Re: ALV select statement not working

Former Member — Mon, 28 May 2007 12:20:02 GMT

hi Sooness,

I want an ALV report that shows the fields as given above.

The fields EBELN, EBELP, EINDT, NETPR PEINH, LIFNR, MENGE, MEINS, WEMNG, KTMNG also needs to be displayed.

however, the common field btwn the material tables and the purchase tables is MATNR that is present in MARA and EKPO.

so thats what am trying to do.... to retreive values using the common field.

Re: ALV select statement not working

dev_parbutteea — Mon, 28 May 2007 12:29:18 GMT

hi,

what i meant is that: what is the use of the loop!

Re: ALV select statement not working

Former Member — Mon, 28 May 2007 12:45:14 GMT

i am modifying the itab IT_FINAL with the values of IT_MRP.

IT_MRP contains the values of the second select statement.

IT_FINAL goes to the ALV Output.

Re: ALV select statement not working

dev_parbutteea — Mon, 28 May 2007 12:54:18 GMT

LOOP AT IT_FINAL INTO WA_FINAL.

READ TABLE IT_MARA

INTO WA_MARA WITH KEY MATNR = WA_MARA-MATNR BINARY SEARCH.

IF SY-SUBRC = 0.

SUBRC = 0. --> already implies WA_MARA-MATNR = WA_FINAL-MATNR.

WA_MARA-MATNR = WA_FINAL-MATNR.

MODIFY IT_FINAL FROM WA_FINAL.

ENDIF.

ENDLOOP.

" not doing anything

. so remove or change logic

FROM EKET INNER JOIN EKKO ON ( EKET_{EBELN = EKKO}EBELN )

INNER JOIN EKPO ON ( EKET_{EBELN = EKPO}EBELN )

AND ( EKET_{EBELP = EKPO}EBELP )

FOR ALL ENTRIES IN IT_MARA

WHERE EKPO~MATNR = IT_MARA-MATNR.

endif.

LOOP AT IT_FINAL INTO WA_FINAL.

READ TABLE IT_MRP

INTO WA_MRP WITH KEY matnr = WA_final-matnr BINARY SEARCH.

IF SY-SUBRC = 0. " if subrc = 0 then value already present in it_final

so, no need to append to it_final

WA_MRP-matnr = WA_FINAL-matnr.

APPEND WA_FINAL TO IT_FINAL.

Else. WA_FINAL-matnr = WA_MRP-matnr .

APPEND WA_FINAL TO IT_FINAL.

" However it will be missing other fields , so you better output table it_mrp itself seperatly or only output it_mrp if that's what you need

ENDIF.

ENDLOOP.

Re: ALV select statement not working

Former Member — Mon, 28 May 2007 13:03:25 GMT

hi,

Thanks buddy.

I will try it out. Thanks a lot for your patience.

Regards

Dinesh

Re: ALV select statement not working

Former Member — Mon, 18 Jun 2007 07:30:32 GMT

thanks guys