Sales Order With Serial Number

[dipaknakum]

I want to show Sales Order with the Material's Serial Number.

i am preparing Query in the dbacockpit as below:

SELECT VBAP.WERKS, VBAP.VBELN, KNA1.KUNNR, KNA1.NAME1, VBAP.POSNR, VBAP.MATNR,VBAP.ARKTX, OBJK.SERNR, VBAK.AUART, VBAK.ERDAT, VBAK.BSTNK, VBAK.BSTDK, VBAK.ERNAM
FROM VBAP
LEFT JOIN VBAK ON VBAK.VBELN = VBAP.VBELN
LEFT JOIN OBJK ON OBJK.OBKNR = VBAP.PAOBJNR AND OBJK.OBJVW = 'S' AND OBJK.MATNR = VBAP.MATNR
LEFT JOIN KNA1 ON KNA1.KUNNR = VBAK.KUNNR
ORDER BY VBAP.VBELN, VBAP.POSNR

above query is working ok and gives the result as per requirement.

after i am preparing it's ABAP code using SE38 as below:

REPORT ZSERIALNO.
TYPES : SLIS.
TABLES: VBAP, VBAK, KNA1, OBJK.

TYPES: BEGIN OF TY_FINAL,
         WERKS      TYPE VBAP-WERKS,
         VBELN      TYPE VBAP-VBELN,
         KUNNR      TYPE KNA1-KUNNR,
         NAME1      TYPE KNA1-NAME1,
         POSNR      TYPE VBAP-POSNR,
         MATNR       TYPE VBAP-MATNR,
         ARKTX      TYPE VBAP-ARKTX,
         SERNR      TYPE OBJK-SERNR,
         AUART      TYPE VBAK-AUART,
         ERDAT      TYPE VBAK-ERDAT,
         BSTNK      TYPE VBAK-BSTNK,
         BSTDK      TYPE VBAK-BSTDK,
         ERNAM      TYPE VBAK-ERNAM,
END OF TY_FINAL.

DATA : LV_LINES  TYPE TLINE,
       LV_LINES1 TYPE TLINE,
       T1_VBELN  TYPE THEAD-TDNAME,
       LINES     TYPE TABLE OF TLINE,
       WA_LINES  TYPE TLINE.
DATA : LS_LINES TYPE TLINE,
       LT_LINES TYPE STANDARD TABLE OF TLINE.
DATA : IT_FINAL TYPE TABLE OF TY_FINAL WITH HEADER LINE,
       WA_FINAL TYPE TY_FINAL.

DATA : LT_FIELDCAT TYPE SLIS_T_FIELDCAT_ALV.
DATA : GS_LAYOUT TYPE SLIS_LAYOUT_ALV.
DATA : IT_LISTHEADER TYPE SLIS_T_LISTHEADER.

SELECTION-SCREEN : BEGIN OF BLOCK B1 WITH FRAME TITLE TEST.
SELECT-OPTIONS : P_WERKS FOR VBAP-WERKS NO INTERVALS NO-EXTENSION OBLIGATORY.
SELECTION-SCREEN : END OF BLOCK B1.

INITIALIZATION.

  TEST = 'Report of Sales Order with Serial Number'.

START-OF-SELECTION.
  PERFORM GET_DATA.
  PERFORM VALIDATE_DATA.
  PERFORM FIELD_CATALOG.
  PERFORM DISPLAY_ALV_REPORT.

FORM GET_DATA .
SELECT VBAP~WERKS, VBAP~VBELN, KNA1~KUNNR, KNA1~NAME1, VBAP~POSNR, VBAP~MATNR,VBAP~ARKTX, OBJK~SERNR, VBAK~AUART, VBAK~ERDAT, VBAK~BSTNK, VBAK~BSTDK, VBAK~ERNAM
FROM VBAP
LEFT JOIN VBAK ON VBAK~VBELN = VBAP~VBELN
LEFT JOIN OBJK ON OBJK~OBKNR = SER03~OBKNR AND OBJK~OBJVW = 'S' AND OBJK~MATNR = VBAP~MATNR
LEFT JOIN KNA1 ON KNA1~KUNNR = VBAK~KUNNR
INTO TABLE @IT_FINAL
WHERE VBAP~VBELN = '0050003286'
AND VBAP~WERKS IN P_WERKS
ORDER BY VBAP~VBELN, VBAP~POSNR.
ENDFORM.

FORM VALIDATE_DATA .
  IF SY-SUBRC <> 0.
    MESSAGE 'Invalid Selection' TYPE 'E'.
  ENDIF.
ENDFORM.

GIVES ERROR AS BELOW:

Program ZSERIALNO
The type of "SER03~OBKNR" or the result type of the function
"SER03~OBKNR" and the type of "VBAP~PAOBJNR" are not compatible for the
operation in question.

[ClausB]

Please describe your problem in more detail.

Is the data type your problem?

LEFT JOIN SER03 ON SER03~OBKNR = VBAP~PAOBJNR

[dipaknakum]

give the error as below:

The type of "SER03~OBKNR" or the result type of the function
"SER03~OBKNR" and the type of "VBAP~PAOBJNR" are not compatible for the
operation in question.

[MateuszAdamus]

Please use "Code" button in message body editor to format the ABAP coding. It will be easier to read this way.

Kind regards,
Mateusz

[MateuszAdamus]

Hello dipaknakum

You cannot use SER03 in your JOIN condition because this table is not used in any other place of the SQL query.

Also, you need to at the at sign (@) before the P_WERKS.

Kind regards,
Mateusz

Edit: if you want to get serial numbers for VBAP record then you should search the SER02 table, joined with VBAP using the SDAUFNR and POSNR fields. Then connect OBJK with records from SER02 by OBKNR field.

[dipaknakum]

hi mateuszadamus

instead of table SER03 which table i should used for Serial No?

[dipaknakum]

UPDATED QUERY:

SELECT VBAP~WERKS, VBAP~VBELN, KNA1~KUNNR, KNA1~NAME1, VBAP~POSNR, VBAP~MATNR,VBAP~ARKTX, OBJK~SERNR, VBAK~AUART, VBAK~ERDAT, VBAK~BSTNK, VBAK~BSTDK, VBAK~ERNAM
FROM VBAP
LEFT JOIN VBAK ON VBAK~VBELN = VBAP~VBELN
LEFT JOIN VBRP ON VBRP~VBELN = VBAP~VBELN
LEFT JOIN OBJK ON OBJK~OBKNR = VBRP~PAOBJNR AND OBJK~OBJVW = 'S' AND OBJK~MATNR = VBAP~MATNR
LEFT JOIN KNA1 ON KNA1~KUNNR = VBAK~KUNNR
INTO TABLE @IT_FINAL
WHERE VBAP~VBELN = '0050003286'
AND VBAP~WERKS IN @P_WERKS
ORDER BY VBAP~VBELN, VBAP~POSNR.

ERROR:
The type of "OBJK~OBKNR" or the result type of the function
"OBJK~OBKNR" and the type of "VBRP~PAOBJNR" are not compatible for the
operation in question.

[MateuszAdamus]

Please use "Comment" feature to add additional details to the question.

You cannot connect OBJK directly with VBRP. You need to use SER02 table, get record from there using the SDAUFNR and POSNR fields and then use SER02 OBKNR field to join with OBJK.

I've udpated my initial answer.

Kind regards,
Mateusz