topic Re: Picking Information from tables in Application Development and Automation Discussions

Picking Information from tables

Former Member — Fri, 27 Jul 2012 07:49:28 GMT

Hi experts ,

I am new here so excuse me for any mistakes. I am facing a problem while coding a program,

the requirement is to print the list of sales orders. The inputs for the program will be

1. Customer number (KUNNR)

2. Material number (MATNR)

3. Organization number (VKORG) Mandatory field.

4. Doc date (AUDAT) Mandatory field.

The required item details for the report will be

EDATU Schedule line date

VBELN SD document number

POSNR Item

MATNR Material Number

ARKTX Material Description

KWMENG Order Quantity

VRKME Sales unit

NETPR Net Price

AUDAT Document Date

KUNNR Sold-to-party / Customer number

NAME1 name1 description of sold to party

VKORG Sales Organization

VKBUR Sales Office.

the above datas are obtained from VABK,VBAP,VBEP and KNA1 tables .

I am able to achieve the out put for the sales documents with delivery delivery date and schedule line date from EDATU,vdatu in VBEP,VBAK table. But the documents which does not have delivery date scheduled like credit memo are not listed in the output they are available in VBAK table but not in VBEP .. VBAK table does not hold EDATU field and VBEP table does not hold sd douments for credit memos.. How can I proceed from here to bring all of the info in to one single table.U can try running VA05 T-Code with the test data

date : 01-02-1994 till current date

sales org : 1000...

if u leave other fields empty u get credit memo document added to the o/p...

Please help me with a solution

Thanks

Dev

Re: Picking Information from tables

ThomasZloch — Fri, 27 Jul 2012 08:02:33 GMT

Sounds like there will always be entries in VBAK, VBAP and KNA1, but not in all cases in VBEP.

I would try to use a JOIN select, linking VBAK, VBAP and KNA1 via INNER JOIN, and VBEP (to VBAP) via LEFT OUTER JOIN. This means that you would also find the cases without VBEP entries, and the VBEP related columns would remain initial.

Advantage of joins are that you can read all required columns from several (logically linked) DB tables into your final internal table at once, and the system is flexible in choosing the most efficient access path to obtain the data. It might be a challenge for a beginner to do it properly the first time, but once you understand the concept, you can benefit a lot.

Thomas

http://help.sap.com/abapdocu_702/en/abapselect_join.htm

Re: Picking Information from tables

Former Member — Mon, 30 Jul 2012 10:26:21 GMT

Hi Thomas,

what if I wanted to try using tables rather using JOIN select

Re: Picking Information from tables

ThomasZloch — Mon, 30 Jul 2012 10:45:13 GMT

I don't understand, please describe your question in more words.

Thomas

Re: Picking Information from tables

Former Member — Mon, 30 Jul 2012 13:11:11 GMT

Hi Sathya,

Just check with all the fields that will have the link between tables.

I hope you will have the functional specification.

In the data fetching from different tables you can use FOR ALL ENTRIES statement .

Thanks,

Pradeep.

Re: Picking Information from tables

ThomasZloch — Mon, 30 Jul 2012 13:48:06 GMT

Yes, if you want your code more complex and more difficult to maintain, then go for FAE

Thomas

Re: Picking Information from tables

Former Member — Mon, 30 Jul 2012 13:58:45 GMT

Hi Thomas,

May i know what will be complexity in writing the code in FAE??

Re: Picking Information from tables

Former Member — Mon, 30 Jul 2012 14:09:01 GMT

VBAK Table has the header details including the sales document category where K and L is used for credit memos and debit memos.

VBAP Table has the Item details for the above table

Kna1 Table has the sodl to party name

VBEP tables has the schedule line date for the sales document created and for credit memos and debit memos u wont find any records here. and I tried to achieve the o/p to a certain extent, but the output comes with an additional entry. I ll help u with my code .. and I am trying to find a logic to this without using the join select statements .. So please tell me, whats bringing that extra record

test data :

sold to party : 1171

material : m-17

org number : 1000

date : 01-02-1994 to current

1. Declaration

SELECT: VBELN "SD document number

AUDAT "Document Date

VBTYP "SD document category

AUART "Sales Document Type

VKORG "Sales Organization

VKBUR "Sales office

VDATU "Delivery Date

KUNNR "Sold-to-party / Customer number

FROM VBAK

INTO TABLE IT_VBAK

WHERE VBTYP IN ('K','L','C')

AND AUART IN ('TA','G2','ZPBO','LV')

AND KUNNR IN S_STP

AND AUDAT IN S_DAT

AND VKORG IN S_ORG.

IF IT_VBAK IS NOT INITIAL.

SELECT VBELN "SD document number

POSNR "Item

MATNR "Material Number

ARKTX "Material Description

KWMENG "Order Quantity

VRKME "Sales unit

NETPR "Net Price

FROM VBAP

INTO TABLE IT_VBAP

FOR ALL ENTRIES IN IT_VBAK

WHERE VBELN EQ IT_VBAK-VBELN

AND MATNR IN S_MAT.

ENDIF.

IF IT_VBAP IS NOT INITIAL.

SELECT VBELN "SD document number

POSNR "Item

EDATU "Schedule line date

FROM VBEP

INTO TABLE IT_VBEP

FOR ALL ENTRIES IN IT_VBAP

WHERE VBELN EQ IT_VBAP-VBELN AND POSNR EQ IT_VBAP-POSNR.

ENDIF.

SELECT KUNNR "Sold-to-party / Customer number

NAME1 "Name1

FROM KNA1

INTO TABLE IT_KNA1.

2. Loop1

LOOP AT IT_VBEP INTO WA_VBEP.

IF SY-SUBRC EQ 0.

WA_FINAL-EDATU = WA_VBEP-EDATU. "Schedule Line Date

WA_FINAL-POSNR = WA_VBEP-POSNR. "Item

WA_FINAL-VBELN = WA_VBEP-VBELN. "Sales Document Number

ENDIF.

READ TABLE IT_VBAK INTO WA_VBAK

WITH KEY VBELN = WA_VBEP-VBELN BINARY SEARCH.

IF SY-SUBRC EQ 0.

WA_FINAL-AUDAT = WA_VBAK-AUDAT. "Document Date

WA_FINAL-KUNNR = WA_VBAK-KUNNR. "Sold-to-party / Customer number

WA_FINAL-VKORG = WA_VBAK-VKORG. "Sales Organization

WA_FINAL-VKBUR = WA_VBAK-VKBUR. "Sales Office.

ENDIF.

READ TABLE IT_VBAP INTO WA_VBAP

WITH KEY VBELN = WA_VBEP-VBELN

POSNR = WA_VBEP-POSNR BINARY SEARCH.

IF SY-SUBRC EQ 0.

WA_FINAL-MATNR = WA_VBAP-MATNR. "Material Number

WA_FINAL-ARKTX = WA_VBAP-ARKTX. "Material Description

WA_FINAL-KWMENG = WA_VBAP-KWMENG. "Order Quantity

WA_FINAL-VRKME = WA_VBAP-VRKME. "Sales unit

WA_FINAL-NETPR = WA_VBAP-NETPR. "Net Price

ENDIF.

READ TABLE IT_KNA1 INTO WA_KNA1

WITH KEY KUNNR = WA_VBAK-KUNNR BINARY SEARCH.

IF SY-SUBRC EQ 0.

WA_FINAL-NAME1 = WA_KNA1-NAME1. "Name1 description of sold to party

ENDIF.

LW_VBELN = WA_VBEP-VBELN. "Local Variable

APPEND WA_FINAL TO IT_FINAL.

CLEAR: WA_FINAL,

WA_VBEP,

WA_VBAK,

WA_VBAP,

WA_KNA1.

AT NEW VBELN.

DELETE IT_TVBAK WHERE VBELN EQ LW_VBELN.

ENDAT.

ENDLOOP.

IF SY-SUBRC EQ 0.

SORT IT_FINAL.

ENDIF.

3. LOOP2

LOOP AT IT_VBAP INTO WA_VBAP.( this loop is bringing the extra )

IF SY-SUBRC EQ 0.

WA_FINAL-POSNR = WA_VBAP-POSNR. "Item

WA_FINAL-MATNR = WA_VBAP-MATNR. "Material Number

WA_FINAL-ARKTX = WA_VBAP-ARKTX. "Material Description

WA_FINAL-KWMENG = WA_VBAP-KWMENG. "Order Quantity

WA_FINAL-VRKME = WA_VBAP-VRKME. "Sales unit

WA_FINAL-NETPR = WA_VBAP-NETPR. "Net Price

READ TABLE IT_TVBAK INTO WA_TVBAK

WITH KEY VBELN = WA_VBAP-VBELN.

IF SY-SUBRC EQ 0.

WA_FINAL-VBELN = WA_TVBAK-VBELN. "Sales Document Number

WA_FINAL-AUDAT = WA_TVBAK-AUDAT. "Document Date

WA_FINAL-KUNNR = WA_TVBAK-KUNNR. "Sold-to-party / Customer number

WA_FINAL-VKORG = WA_TVBAK-VKORG. "Sales Organization

WA_FINAL-VKBUR = WA_TVBAK-VKBUR. "Sales Office.

ENDIF.

READ TABLE IT_KNA1 INTO WA_KNA1

WITH KEY KUNNR = WA_TVBAK-KUNNR.

IF SY-SUBRC EQ 0.

WA_FINAL-NAME1 = WA_KNA1-NAME1. "Name1 description of sold to party

ENDIF.

APPEND WA_FINAL TO IT_FINAL.

CLEAR: WA_FINAL,

WA_VBAP,

WA_TVBAK,

WA_KNA1.

ENDLOOP.

Re: Picking Information from tables

ThomasZloch — Mon, 30 Jul 2012 14:11:16 GMT

In this example here, information from four DB tables is required in one internal table for list output.

You need one properly constructed SELECT statement when using a JOIN.

With FAE, you need four SELECT statements and additional internal table processing with nested loops to combine the data from four internal tables into the final one.

In addition, these separated SELECTs cannot be optimized by the CBO based on user input in the selection criteria at runtime.

Thomas

Re: Picking Information from tables

Former Member — Mon, 30 Jul 2012 14:12:32 GMT

Hi Pradeep ,

I did used FAE in my program , but I dont think it will help in this issue .. I have posted my code as well if it requires any addon please let me know

Thanks

Re: Picking Information from tables

Former Member — Mon, 30 Jul 2012 14:13:54 GMT

Yes i agree

Re: Picking Information from tables

ThomasZloch — Mon, 30 Jul 2012 14:28:28 GMT

Here is my version of your code.

SELECT

vbak~vbeln
vbak~audat
vbak~vbtyp
vbak~auart
vbak~vkorg
vbak~vkbur
vbak~vdatu
vbak~kunnr
vbap~posnr
vbap~matnr
vbap~arktx
vbap~kwmeng
vbap~vrkme
vbap~netpr
vbep~edatu
kna1~name1
FROM vbak
JOIN kna1 ON kna1~kunnr = vbak~kunnr
JOIN vbap ON vbap~vbeln = vbak~vbeln
LEFT JOIN vbep ON vbep~vbeln = vbap~vbeln
AND vbep~posnr = vbap~posnr
INTO CORRESPONDING FIELDS OF TABLE it_final
WHERE vbak~vbtyp IN ('K','L','C')
AND vbak~auart IN ('TA','G2','ZPBO','LV')
AND vbak~kunnr IN s_stp
AND vbak~audat IN s_dat
AND vbak~vkorg IN s_org
AND vbap~matnr IN s_mat.

Thomas