pending order selection

[Former Member]

Hi friends,

I've created an ALV report to print, Order No, Delivery no and invoice no for a customer. but in output i'm getting results if all three numbers are created in va01, vl01n and vf01 respectively. how can i print an order no if it doesn't have a delivery and invoice created? or only have a delivery no?

Edited by: venura12 on Jun 20, 2011 9:49 AM

[Former Member]

Please help me someone

[Former Member]

hi,

can you send me the code?

regards,

rekha.

[Former Member]

hi,

can you send me the code?

regards,

rekha.

[Former Member]

REPORT  ZORDERDETAILS.

TYPE-POOLS : SLIS.
TABLES : VBAK, VBAP, VBRP, VBKD, LIPS, LIKP, KNA1, T001W.

CONSTANTS: c_yes       TYPE flag   VALUE 'X',   "Check Character for Mark
           c_formname_at_user_command TYPE slis_formname VALUE 'USER_COMMAND'.

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

  SELECT-OPTIONS : S_WERKS for VBAP-WERKS. "PLANT
  SELECT-OPTIONS : S_KUNNR for VBAK-KUNNR. "CUSTOMER
  SELECT-OPTIONS : S_ERDAT for VBAK-ERDAT. "DATE

SELECTION-SCREEN END OF BLOCK B1.

DATA : FIELD_CATALOG TYPE SLIS_T_FIELDCAT_ALV WITH HEADER LINE,
       gd_layout type slis_layout_alv,
       GD_REPID LIKE SY-REPID.

TYPES : BEGIN OF TY_FINAL,

  ERDAT TYPE VBAK-ERDAT, "ORDER DATE
  VBELN TYPE VBAK-VBELN, "ORDER NO
  BLDAT TYPE LIKP-BLDAT, "DELIVERY DATE
  VBELN2 TYPE LIKP-VBELN, "DELIVERY NO
  FKDAT TYPE VBRK-FKDAT, "INVOICE DATE
  VBELN3 TYPE VBRK-VBELN, "INVOICE NO
  BSTKD TYPE VBKD-BSTKD, "PO NO
  KUNNR1 TYPE VBAK-KUNNR, "CUSTOMER
  KUNNR2 TYPE KNA1-KUNNR,
  NAME1 TYPE KNA1-NAME1, "CUSTOMER NAME
  WERKS TYPE VBAP-WERKS, "PLANT
*  PNAME TYPE T001W-NAME1, "PLANT NAME

END OF TY_FINAL.

DATA : IT_FINAL TYPE TABLE OF TY_FINAL WITH HEADER LINE,
       WA_FINAL TYPE TY_FINAL.

START-OF-SELECTION.

SELECT VBAK~ERDAT
       VBAK~VBELN
       LIKP~BLDAT
       LIKP~VBELN AS VBELN2
       VBRK~FKDAT
       VBRK~VBELN AS VBELN3
       VBKD~BSTKD
       VBAK~KUNNR AS KUNNR1
       KNA1~KUNNR AS KUNNR2
       KNA1~NAME1
       VBAP~WERKS
INTO CORRESPONDING FIELDS OF TABLE IT_FINAL FROM VBAK
INNER JOIN VBAP ON VBAP~VBELN = VBAK~VBELN
INNER JOIN LIPS ON LIPS~VGBEL = VBAP~VBELN AND LIPS~VGPOS = VBAP~POSNR
INNER JOIN LIKP ON LIKP~VBELN = LIPS~VBELN
INNER JOIN VBRP ON VBRP~AUBEL = VBAK~VBELN
INNER JOIN VBRK ON VBRK~VBELN = VBRP~VBELN
INNER JOIN VBKD ON VBKD~VBELN = VBAK~VBELN
INNER JOIN KNA1 ON KNA1~KUNNR = VBAK~KUNNR
WHERE VBAK~KUNNR IN S_KUNNR AND VBAK~ERDAT IN S_ERDAT AND VBAP~WERKS IN S_WERKS.

*break-point.

SORT IT_FINAL BY ERDAT VBELN BLDAT VBELN2 FKDAT.

DELETE ADJACENT DUPLICATES FROM IT_FINAL COMPARING ERDAT VBELN BLDAT VBELN2 FKDAT.

IF SY-SUBRC EQ 0.
    PERFORM PRINT_DATA.
  else.
    message 'No data exists for chosen selection' type 'I'.
ENDIF.

FORM PRINT_DATA.

FIELD_CATALOG-FIELDNAME   =  'KUNNR1'.
FIELD_CATALOG-SELTEXT_M   = 'Customer Code'.
FIELD_CATALOG-COL_POS     = 0.
FIELD_CATALOG-OUTPUTLEN   = 20.
  FIELD_CATALOG-EMPHASIZE   = 'X'.
APPEND FIELD_CATALOG TO FIELD_CATALOG.
CLEAR  FIELD_CATALOG.

FIELD_CATALOG-FIELDNAME   =  'NAME1'.
FIELD_CATALOG-SELTEXT_M   = 'Customer Name'.
FIELD_CATALOG-COL_POS     = 1.
FIELD_CATALOG-OUTPUTLEN   = 20.
  FIELD_CATALOG-EMPHASIZE   = 'X'.
APPEND FIELD_CATALOG TO FIELD_CATALOG.
CLEAR  FIELD_CATALOG.

FIELD_CATALOG-FIELDNAME   =  'ERDAT'.
FIELD_CATALOG-SELTEXT_M   = 'Order Date'.
FIELD_CATALOG-COL_POS     = 2.
FIELD_CATALOG-OUTPUTLEN   = 20.
  FIELD_CATALOG-EMPHASIZE   = 'X'.
APPEND FIELD_CATALOG TO FIELD_CATALOG.
CLEAR  FIELD_CATALOG.

FIELD_CATALOG-FIELDNAME   =  'VBELN'.
FIELD_CATALOG-SELTEXT_M   = 'Order No'.
FIELD_CATALOG-COL_POS     = 3.
*wa_sort-subtot = 'X'.
*FIELD_CATALOG-DO_SUM = 'X'.
FIELD_CATALOG-HOTSPOT    = c_yes.
FIELD_CATALOG-OUTPUTLEN   = 20.
  FIELD_CATALOG-EMPHASIZE   = 'X'.
APPEND FIELD_CATALOG TO FIELD_CATALOG.
CLEAR  FIELD_CATALOG.

FIELD_CATALOG-FIELDNAME   = 'BLDAT'.
FIELD_CATALOG-SELTEXT_M   = 'Delivery Date'.
FIELD_CATALOG-COL_POS     = 4.
FIELD_CATALOG-OUTPUTLEN   = 20.
*FIELD_CATALOG-DO_SUM = 'X'.
FIELD_CATALOG-EMPHASIZE   = 'X'.
APPEND FIELD_CATALOG TO FIELD_CATALOG.
CLEAR  FIELD_CATALOG.
*

FIELD_CATALOG-FIELDNAME   = 'VBELN2'.
FIELD_CATALOG-SELTEXT_M   = 'Delivery No'.
FIELD_CATALOG-COL_POS     = 5.
FIELD_CATALOG-HOTSPOT    = c_yes.
FIELD_CATALOG-OUTPUTLEN   = 20.
*FIELD_CATALOG-DO_SUM = 'X'.
FIELD_CATALOG-EMPHASIZE   = 'X'.
APPEND FIELD_CATALOG TO FIELD_CATALOG.
CLEAR  FIELD_CATALOG.

FIELD_CATALOG-FIELDNAME   = 'FKDAT'.
FIELD_CATALOG-SELTEXT_M   = 'Invoice Date'.
FIELD_CATALOG-COL_POS     = 6.
FIELD_CATALOG-OUTPUTLEN   = 20.
*FIELD_CATALOG-DO_SUM = 'X'.
FIELD_CATALOG-EMPHASIZE   = 'X'.
APPEND FIELD_CATALOG TO FIELD_CATALOG.
CLEAR  FIELD_CATALOG.
*

FIELD_CATALOG-FIELDNAME   = 'VBELN3'.
FIELD_CATALOG-SELTEXT_M   = 'Invoice No'.
FIELD_CATALOG-REF_TABNAME   = 'VBRK'.
FIELD_CATALOG-COL_POS     = 7.
FIELD_CATALOG-HOTSPOT    = c_yes.
FIELD_CATALOG-OUTPUTLEN   = 20.
*FIELD_CATALOG-DO_SUM = 'X'.
FIELD_CATALOG-EMPHASIZE   = 'X'.
APPEND FIELD_CATALOG TO FIELD_CATALOG.
CLEAR  FIELD_CATALOG.
*
FIELD_CATALOG-FIELDNAME   = 'BSTKD'.
FIELD_CATALOG-SELTEXT_M   = 'PO No'.
FIELD_CATALOG-COL_POS     = 8.
FIELD_CATALOG-OUTPUTLEN   = 20.
*FIELD_CATALOG-DO_SUM = 'X'.
FIELD_CATALOG-EMPHASIZE   = 'X'.
APPEND FIELD_CATALOG TO FIELD_CATALOG.
CLEAR  FIELD_CATALOG.




GD_REPID = SY-REPID.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
  EXPORTING
    I_CALLBACK_PROGRAM      = GD_REPID


      IS_LAYOUT = GD_LAYOUT

    IT_FIELDCAT             = FIELD_CATALOG[]
*      I_GRID_TITLE            = 'Sample report'
*     I_SAVE                            = 'A'

  TABLES
    T_OUTTAB                = IT_FINAL[]

  EXCEPTIONS
    PROGRAM_ERROR           = 1
    OTHERS                  = 2.

ENDFORM.

Edited by: venura12 on Jun 20, 2011 12:48 PM

[Former Member]

hi

please check table VBFA Sales Document Flow u get all details.

abhilash

[Former Member]

so is this select query wrong?

SELECT VBAK~ERDAT

VBAK~VBELN

LIKP~BLDAT

LIKP~VBELN AS VBELN2

VBRK~FKDAT

VBRK~VBELN AS VBELN3

VBKD~BSTKD

VBAK~KUNNR AS KUNNR1

KNA1~KUNNR AS KUNNR2

KNA1~NAME1

VBAP~WERKS

INTO CORRESPONDING FIELDS OF TABLE IT_FINAL FROM VBAK

INNER JOIN VBAP ON VBAP_{VBELN = VBAK}VBELN

INNER JOIN LIPS ON LIPS_{VGBEL = VBAP}VBELN AND LIPS_{VGPOS = VBAP}POSNR

INNER JOIN LIKP ON LIKP_{VBELN = LIPS}VBELN

INNER JOIN VBRP ON VBRP_{AUBEL = VBAK}VBELN

INNER JOIN VBRK ON VBRK_{VBELN = VBRP}VBELN

INNER JOIN VBKD ON VBKD_{VBELN = VBAK}VBELN

INNER JOIN KNA1 ON KNA1_{KUNNR = VBAK}KUNNR

WHERE VBAK_{KUNNR IN S_KUNNR AND VBAK}ERDAT IN S_ERDAT AND VBAP~WERKS IN S_WERKS.

[Former Member]

Hi,

To get the Open sales order list.. check for the field LFSTA from table VBUP which gives you the open sales order.

the below sample query check for each line item. you can modify the below code as per your requirement

sample code:

SELECT A~VBELN

A~ERDAT

A~BSTNK

A~BSTDK

A~KUNNR

A~KNUMV

B~POSNR

B~MATNR

B~ABGRU

B~ARKTX

B~KWMENG

C~LFSTA

D~NAME1

INTO CORRESPONDING FIELDS OF TABLE IT_ORDER

FROM ( ( ( VBAK AS A INNER JOIN VBAP AS B ON A_{VBELN = B}VBELN )

INNER JOIN VBUP AS C ON C_{VBELN = A}VBELN AND

C_{POSNR = B}POSNR )

INNER JOIN KNA1 AS D ON D_{KUNNR = A}KUNNR )

WHERE A~ERDAT IN L_ERDAT AND

A~AUART IN L_AUART AND

A~VKORG IN L_VKORG AND

A~VTWEG IN L_VTWEG AND

A~SPART IN L_SPART and

c~lfsta <> 'C'.

Thanks and regards

shankar