need info on data retrieval

Former Member · ‎2007 Nov 05

1.I have an internal table it_vbak with fields vbeln & auart

2. Select-options: s_auart for vbak-auart

3. I need to retrieve data into it_vbak with the following condition :

VBAK-VBELN = VBFA-VBELV and

VBFA-VBELN = LIKP-VBELN and

( VBTYP_N = 'J' or VBTYP_N ) and

(VBTYP_V ='C' or VBTYP_V ='I') and VBAK-AUART in s_auart.

If I use Inner Join I can't use in s_auart.

...Please let me know

Former Member · ‎2007 Nov 05

You can use s_auart in INNER JOIN

Regards,

Atish

Former Member · ‎2007 Nov 05

Hi

you can use inner joins or FOR ALL ENTRIES

to retrive the data

Former Member · ‎2007 Nov 05

hi,

make use of for all entries

first fetch the data from vbak where auart in s_auart(it_vbak_

and the pass it to vbfa(it_vbfa)using for all entries

the pass vbfa to likp using for all entries(it_likp)

then take one final internal table (it_final) and populate final internal table.

check this example for reference: my requirement si little bit different

&----

*& Report ZRSDREPORT

*&

&----

*&

&----

REPORT ZRSDREPORT LINE-SIZE 230

NO STANDARD PAGE HEADING

MESSAGE-ID ZZ.

************************************************************************

Author : Sudha Rani Pathuri *
Date : 07/04/2007 *
Title : DELIVERY STATUS REPORT *
Program Type : Executable Program *
Tables Used: : VBAK,VBAP,LIKP,LIPS,VBFA,VBUP *
Purpose : This report gives the status od the delivery *
based on sales order data *

----

&----

*& TABLES DECLARATION *

&----

TABLES : VBAK,VBPA,LIKP.

&----

declaraing TYPEPOLL SLIS *

&----

TYPE-POOLS : SLIS.

&----

*& SELECTION-SCREEN *

&----

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

SELECTION-SCREEN :SKIP 2.

SELECT-OPTIONS: S_VKORG FOR VBAK-VKORG NO-EXTENSION,

S_VTWEG FOR VBAK-VTWEG NO-EXTENSION,

S_SPART FOR VBAK-SPART NO-EXTENSION,

S_KUNNR FOR VBPA-KUNNR,

SO_KUNNR FOR VBPA-KUNNR,

S_VBELN FOR LIKP-VBELN NO-EXTENSION,

S_ERDAT FOR LIKP-ERDAT OBLIGATORY NO-EXTENSION,

SO_ERDAT FOR VBAK-ERDAT NO-EXTENSION.

SELECTION-SCREEN :SKIP 2.

SELECTION-SCREEN END OF BLOCK B1.

&----

*& TYPES DECARATION *

&----

*--types declaration for capturing vbeln from vbak

TYPES : BEGIN OF TY_VBELN,

VBELN TYPE VBELN, "Sales Doc No

END OF TY_VBELN,

*--types declaration for vbap

BEGIN OF TY_VBAP,

VBELN LIKE VBAP-VBELN, "Sales Document

POSNR LIKE VBAP-POSNR, "Sales Document Item

MATNR LIKE VBAP-MATNR, "Material Number

KWMENG LIKE VBAP-KWMENG, "Cumulative order quantity in

"sales units

KDMAT LIKE VBAP-KDMAT, "Material Number Used by Customer

END OF TY_VBAP,

*--types declaration for vbfa

BEGIN OF TY_VBFA,

VBELV LIKE VBFA-VBELV, "Preceding sales and distribution

"document

POSNV LIKE VBFA-POSNV, "Preceding item of an SD document

VBELN LIKE VBFA-VBELN, "Subsequent s and d document

POSNN LIKE VBFA-POSNN, "Subsequent item of an SDdocument

END OF TY_VBFA,

*--types declaration for likp

BEGIN OF TY_LIKP,

VBELN LIKE LIKP-VBELN, "Delivery no

END OF TY_LIKP,

*--types declaration for lips

BEGIN OF TY_LIPS,

VBELN LIKE LIPS-VBELN, "Delivery

POSNR LIKE LIPS-POSNR, "Delivery item

MATNR LIKE LIPS-MATNR, "Material Number

LFIMG LIKE LIPS-LFIMG, "Actual quantity delivered (in

sales units)
vgbel LIKE lips-vgbel, "Document number of the reference
"document

END OF TY_LIPS,

*--types declaration for vbup

BEGIN OF TY_VBUP,

VBELN LIKE VBUP-VBELN, "Sales and Distribution Document

"Number

POSNR LIKE VBUP-POSNR, "Item number of the SD document

LFSTA LIKE VBUP-LFSTA, "Delivery status

WBSTA LIKE VBUP-WBSTA, "Goods movement status

FKSTA LIKE VBUP-FKSTA, "Billing status of delivery

"related billing document

END OF TY_VBUP,

*--types declaration for final internal table for diplaying purpose

BEGIN OF TY_FINAL,

VBELN(10) TYPE C,

POSNR LIKE VBAP-POSNR,

MATNR LIKE VBAP-MATNR,

KWMENG LIKE VBAP-KWMENG,

KDMAT LIKE VBAP-KDMAT,

VBELN1 LIKE LIKP-VBELN,

POSNR1 LIKE LIPS-POSNR,

LFIMG LIKE LIPS-LFIMG,

MATNR1 LIKE LIPS-MATNR,

DS(25) TYPE C,

END OF TY_FINAL.

&----

*& CONSTANTS DECARATION *

&----

CONSTANTS: C_DTYPE TYPE C VALUE 'J'.

&----

*& INTERNAL TABLE DECARATION *

&----

DATA : I_VBELN TYPE STANDARD TABLE OF TY_VBELN WITH HEADER LINE,

I_VBAP TYPE STANDARD TABLE OF TY_VBAP WITH HEADER LINE,

I_VBFA TYPE STANDARD TABLE OF TY_VBFA WITH HEADER LINE,

I_LIKP TYPE STANDARD TABLE OF TY_LIKP WITH HEADER LINE,

I_LIPS TYPE STANDARD TABLE OF TY_LIPS WITH HEADER LINE,

I_VBUP TYPE STANDARD TABLE OF TY_VBUP WITH HEADER LINE,

I_FINAL TYPE STANDARD TABLE OF TY_FINAL WITH HEADER LINE.

&----

*& FIELDCATALOG DECARATION *

&----

DATA : I_FIELDCAT TYPE SLIS_T_FIELDCAT_ALV,

W_FIELDCAT TYPE SLIS_FIELDCAT_ALV,

LT_SORT TYPE SLIS_T_SORTINFO_ALV,

I_LAYOUT TYPE SLIS_LAYOUT_ALV.

&----

*& START-OF-SELECTION *

&----

START-OF-SELECTION.

*--for fetching slaes and delivery data

PERFORM GET_DATA.

*--populating the data into one internal table for output

PERFORM POPULATE_FINAL.

*--for displaying the data using alv

PERFORM CALL_ALV.

&----

*& Form get_data

&----

FORM GET_DATA .

*--for retrieving vbeln from VBAK or VbAP

PERFORM GET_VBELN.

*--retrieving data from VBAP

PERFORM GET_VBAP.

*--fetching delivery document details using VBFA.

PERFORM GET_VBFA.

*--fetching delivery data from LIKP

PERFORM GET_LIKP.

*--fetching delivery data from LIPS

PERFORM GET_LIPS.

*--fetching data from VBUP

PERFORM GET_VBUP.

ENDFORM. " get_data

&----

*& Form get_vbeln

&----

FORM GET_VBELN .

*--retrieving vbeln from either VBAK or VBPA

*--checking whether payer or sold to values in the selection-screen or

give or not

IF S_KUNNR IS INITIAL OR SO_KUNNR IS INITIAL.

*--select statement for retrieving vbeln from vbak

SELECT VBELN

FROM VBAK

INTO TABLE I_VBELN

WHERE VKORG IN S_VKORG AND

VTWEG IN S_VTWEG AND

SPART IN S_SPART AND

ERDAT IN S_ERDAT.

IF SY-SUBRC = 0.

SORT I_VBELN BY VBELN.

ELSE.

MESSAGE I000 WITH TEXT-002.

STOP.

LEAVE LIST-PROCESSING.

ENDIF.

ELSE.

*--select statement for retrieving data from vbpa

SELECT VBELN

FROM VBPA

INTO TABLE I_VBELN

WHERE KUNNR IN S_KUNNR OR

KUNNR IN SO_KUNNR.

IF SY-SUBRC = 0.

SORT I_VBELN BY VBELN.

ELSE.

MESSAGE I000 WITH TEXT-002.

LEAVE LIST-PROCESSING.

ENDIF.

ENDFORM. " get_vbeln

&----

*& Form get_vbap

&----

FORM GET_VBAP .

IF I_VBELN[] IS NOT INITIAL.

*--select statement for retrieving data from vbap

SELECT VBELN

POSNR

MATNR

KWMENG

KDMAT

FROM VBAP

INTO TABLE I_VBAP

FOR ALL ENTRIES IN I_VBELN

WHERE VBELN = I_VBELN-VBELN.

IF SY-SUBRC = 0.

SORT I_VBAP BY VBELN POSNR.

ELSE.

MESSAGE I000 WITH TEXT-002.

STOP.

LEAVE LIST-PROCESSING.

ENDIF.

ENDFORM. " get_vbap

&----

*& Form get_vbfa

&----

text

----

--> p1 text
<-- p2 text

----

FORM GET_VBFA .

*--select statement to retrieve data from VBFA

IF I_VBAP[] IS NOT INITIAL.

SELECT VBELV

POSNV

VBELN

POSNN

FROM VBFA

INTO TABLE I_VBFA

FOR ALL ENTRIES IN I_VBAP

WHERE VBELV = I_VBAP-VBELN AND

POSNV = I_VBAP-POSNR AND

VBTYP_N = C_DTYPE.

IF SY-SUBRC = 0.

SORT I_VBFA ."BY vbelv posnv vbeln posnn.

ELSE.

MESSAGE I000 WITH TEXT-002.

STOP.

ENDIF.

ENDFORM. " get_vbfa

&----

*& Form get_likp

&----

FORM GET_LIKP .

IF I_VBFA[] IS NOT INITIAL.

*--Fecthing data fron LIKP

SELECT VBELN

FROM LIKP

INTO TABLE I_LIKP

FOR ALL ENTRIES IN I_VBFA

WHERE VBELN = I_VBFA-VBELN.

IF SY-SUBRC = 0.

SORT I_LIKP BY VBELN.

ELSE.

MESSAGE I000 WITH TEXT-002.

STOP.

ENDIF.

ENDFORM. " get_likp

&----

*& Form get_lips

&----

FORM GET_LIPS .

IF NOT I_LIKP[] IS INITIAL.

*--select statement for retrieving data from LIPS

SELECT VBELN

POSNR

MATNR

vgbel

LFIMG

FROM LIPS

INTO TABLE I_LIPS

FOR ALL ENTRIES IN I_LIKP

WHERE VBELN = I_LIKP-VBELN.

IF SY-SUBRC = 0.

SORT I_LIPS BY VBELN POSNR MATNR.

ELSE.

MESSAGE I000 WITH TEXT-002.

STOP.

ENDIF.

ENDFORM. " get_lips

&----

*& Form get_vbup

&----

FORM GET_VBUP .

IF I_LIPS[] IS NOT INITIAL.

*--select statement for retrieving delivery status from VBUP.

SELECT VBELN

POSNR

LFSTA

WBSTA

FKSTA

FROM VBUP

INTO TABLE I_VBUP

FOR ALL ENTRIES IN I_LIPS

WHERE VBELN = I_LIPS-VBELN AND

POSNR = I_LIPS-POSNR.

IF SY-SUBRC = 0.

SORT I_VBUP BY VBELN POSNR.

ELSE.

MESSAGE I000 WITH TEXT-002.

STOP.

ENDIF.

ENDFORM. " get_vbup

&----

*& Form populate_final

&----

FORM POPULATE_FINAL.

*--populating final internal table with sales and delivery data for

displaying purpose

LOOP AT I_LIPS.

I_FINAL-VBELN1 = I_LIPS-VBELN .

I_FINAL-POSNR1 = I_LIPS-POSNR .

I_FINAL-MATNR1 = I_LIPS-MATNR .

I_FINAL-LFIMG = I_LIPS-LFIMG.

READ TABLE I_VBFA WITH KEY VBELN = I_LIPS-VBELN

POSNN = I_LIPS-POSNR BINARY SEARCH.

IF SY-SUBRC = 0.

READ TABLE I_VBAP WITH KEY VBELN = I_VBFA-VBELV

POSNR = I_VBFA-POSNV BINARY SEARCH.

IF SY-SUBRC = 0.

I_FINAL-VBELN = I_VBAP-VBELN .

I_FINAL-POSNR = I_VBAP-POSNR .

I_FINAL-MATNR = I_VBAP-MATNR .

I_FINAL-KWMENG = I_VBAP-KWMENG.

I_FINAL-KDMAT = I_VBAP-KDMAT .

READ TABLE I_VBUP WITH KEY VBELN = I_LIPS-VBELN

POSNR = I_LIPS-POSNR BINARY SEARCH.

IF SY-SUBRC = 0.

IF I_VBUP-WBSTA = 'C'

AND I_VBUP-FKSTA <> ' '.

CONTINUE.

ENDIF.

IF I_VBUP-WBSTA = 'C' AND I_VBUP-FKSTA = ' '.

I_FINAL-DS = 'Shipped but not invoiced'.

ELSEIF I_VBUP-WBSTA <> 'C'.

I_FINAL-DS = 'open'.

ENDIF.

APPEND I_FINAL.

CLEAR I_FINAL.

ENDIF.

ENDLOOP.

ENDFORM. " populate_final

&----

*& Form call_alv

&----

FORM CALL_ALV .

DATA: CPROG TYPE SY-CPROG.

CPROG = SY-CPROG.

*---populating fieldcatalog

PERFORM POPULATE_FIELDCATALOG.

*-----layout

PERFORM Z_LAYOUT.

*----for sub totals.

PERFORM Z_SORT USING LT_SORT.

*-----calling REUSE_ALV_LIST_DISPLAY

CALL FUNCTION 'REUSE_ALV_LIST_DISPLAY'

EXPORTING

I_CALLBACK_PROGRAM = CPROG

IS_LAYOUT = I_LAYOUT

IT_FIELDCAT = I_FIELDCAT

IT_SORT = LT_SORT

TABLES

T_OUTTAB = I_FINAL

EXCEPTIONS

PROGRAM_ERROR = 1

OTHERS = 2.

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. " call_alv

&----

*& Form populate_fieldcatalog

&----

FORM POPULATE_FIELDCATALOG .

CLEAR W_FIELDCAT.

W_FIELDCAT-FIELDNAME = 'VBELN'.

W_FIELDCAT-SELTEXT_L = 'Sales Order Number'.

W_FIELDCAT-TABNAME = 'I_FINAL'.

W_FIELDCAT-COL_POS = 1.

APPEND W_FIELDCAT TO I_FIELDCAT.

CLEAR W_FIELDCAT.

W_FIELDCAT-FIELDNAME = 'POSNR'.

W_FIELDCAT-SELTEXT_L = 'Order Line Number'.

W_FIELDCAT-TABNAME = 'I_FINAL'.

W_FIELDCAT-COL_POS = 2.

APPEND W_FIELDCAT TO I_FIELDCAT.

CLEAR W_FIELDCAT.

W_FIELDCAT-FIELDNAME = 'MATNR'.

W_FIELDCAT-SELTEXT_L = 'MAterial Number'.

W_FIELDCAT-TABNAME = 'I_FINAL'.

W_FIELDCAT-COL_POS = 3.

APPEND W_FIELDCAT TO I_FIELDCAT.

CLEAR W_FIELDCAT.

W_FIELDCAT-FIELDNAME = 'KWMENG'.

W_FIELDCAT-SELTEXT_L = 'Qty Ordered'.

W_FIELDCAT-TABNAME = 'I_FINAL'.

W_FIELDCAT-COL_POS = 4.

APPEND W_FIELDCAT TO I_FIELDCAT.

CLEAR W_FIELDCAT.

W_FIELDCAT-FIELDNAME = 'KDMAT'.

W_FIELDCAT-SELTEXT_L = 'Customer PO number'.

W_FIELDCAT-TABNAME = 'I_FINAL'.

W_FIELDCAT-COL_POS = 5.

W_FIELDCAT-OUTPUTLEN = 35.

APPEND W_FIELDCAT TO I_FIELDCAT.

CLEAR W_FIELDCAT.

W_FIELDCAT-FIELDNAME = 'VBELN1'.

W_FIELDCAT-SELTEXT_L = 'Delivery Number'.

W_FIELDCAT-TABNAME = 'I_FINAL'.

W_FIELDCAT-COL_POS = 6.

APPEND W_FIELDCAT TO I_FIELDCAT.

CLEAR W_FIELDCAT.

W_FIELDCAT-FIELDNAME = 'POSNR1'.

W_FIELDCAT-SELTEXT_L = 'Delivery Line Number'.

W_FIELDCAT-TABNAME = 'I_FINAL'.

W_FIELDCAT-COL_POS = 7.

APPEND W_FIELDCAT TO I_FIELDCAT.

CLEAR W_FIELDCAT.

W_FIELDCAT-FIELDNAME = 'MATNR1'.

W_FIELDCAT-SELTEXT_L = 'Material Number'.

W_FIELDCAT-TABNAME = 'I_FINAL'.

W_FIELDCAT-COL_POS = 8.

APPEND W_FIELDCAT TO I_FIELDCAT.

CLEAR W_FIELDCAT.

W_FIELDCAT-FIELDNAME = 'LFIMG'.

W_FIELDCAT-SELTEXT_L = 'Delivery Qty'.

W_FIELDCAT-TABNAME = 'I_FINAL'.

W_FIELDCAT-COL_POS = 9.

W_FIELDCAT-DO_SUM = 'X'.

APPEND W_FIELDCAT TO I_FIELDCAT.

CLEAR W_FIELDCAT.

W_FIELDCAT-FIELDNAME = 'DS'.

W_FIELDCAT-SELTEXT_L = 'Delivery Status'.

W_FIELDCAT-TABNAME = 'I_FINAL'.

W_FIELDCAT-COL_POS = 10.

APPEND W_FIELDCAT TO I_FIELDCAT.

ENDFORM. " populate_fieldcatalog

&----

*& Form Z_LAYOUT

&----

FORM Z_LAYOUT .

I_LAYOUT-ZEBRA = 'X'.

I_LAYOUT-COLWIDTH_OPTIMIZE = 'X'.

I_LAYOUT-GET_SELINFOS = 'X'.

ENDFORM. " Z_LAYOUT

&----

*& Form Z_SORT

&----

FORM Z_SORT USING P_LT_SORT.

DATA :LS_SORT TYPE SLIS_SORTINFO_ALV.

CLEAR LS_SORT.

LS_SORT-SPOS = '9'.

LS_SORT-FIELDNAME = 'VBELN'.

LS_SORT-TABNAME = 'I_FINAL'.

LS_SORT-UP = 'X'.

LS_SORT-GROUP = 'UL'.

LS_SORT-SUBTOT = 'X'.

APPEND LS_SORT TO LT_SORT.

ENDFORM. " Z_SORT

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

need info on data retrieval