need to check the code!

Former Member — Wed, 19 Dec 2007 11:02:52 GMT

please help me to check the code as i am able to fetch all the data.

What modifications shall i make in my query part

REPORT YINTERACTIVEPROGRAM01.

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

*CREATED BY:JYOTIRMOY DEB *

*DATE OF CREATION : *

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

*TABLE DECLARATION

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

TABLES:VBAK, "Sales Document: Header Data

VBKD, "Sales Document: Business Data

VBPA, "Sales Document: Partner

VBAP, "Sales Document: Item Data

ADRC, "Addresses (central address admin.)

T178T, "Conditions: Groups for Materials:text

T188T, "Pricing groups for customers

TVM1,

TVM1T, "Material pricing group 1: Description

TVM2,

TVM2T, "Material Pricing Group 2: Description

TVM3,

TVM3T, "Material Pricing Group 3: Description

TVM4,

TVM4T, "Material Pricing Group 4: Description

TVM5,

TVM5T, "Material Pricing Group 5: Description

SZA1_D0100,

TVLV , "Check table for release orders: Usage ID

TVLVT,

TVKGG, "Customer Condition Groups (Customer Master)

TVKGGT,

TPAR, "Business Partner: Functions

TPART,

TVAUT. "Sales Documents: Order Reasons: Texts

*SELECT OPTIONS

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

Select-options S_VBELN for vbak-vbeln.

*CREATING INTERNAL TABLES

TYPES:BEGIN OF TY_TAB,

VBELN LIKE VBAK-VBELN,

VKORG LIKE VBAK-VKORG,

ERDAT LIKE VBAK-ERDAT,

AUGRU LIKE VBAK-AUGRU,

BEZEI LIKE TVAUT-BEZEI,

BSTDK LIKE VBKD-BSTDK,

BSTDK_E LIKE VBKD-BSTDK_E,

BSTKD LIKE VBKD-BSTKD,

KDKG2 LIKE VBKD-KDKG2,

KONDA LIKE VBKD-KONDA,

MATNR LIKE VBAP-MATNR,

SPART LIKE VBAP-SPART,

KONDM LIKE VBAP-KONDM,

WAERK LIKE VBAP-WAERK,

NETWR LIKE VBAP-NETWR,

KWMENG LIKE VBAP-KWMENG,

VKAUS LIKE VBAP-VKAUS,

MVGR1 LIKE VBAP-MVGR1,

MVGR2 LIKE VBAP-MVGR2,

MVGR3 LIKE VBAP-MVGR3,

MVGR4 LIKE VBAP-MVGR4,

MVGR5 LIKE VBAP-MVGR5,

KUNNR LIKE VBPA-KUNNR,

POSTCODE1 LIKE ADRC-POST_CODE1,

POBOX LIKE ADRC-PO_BOX,

NAME1 LIKE ADRC-NAME1,

NAME2 LIKE ADRC-NAME2,

CITY1 LIKE ADRC-CITY1,

CITY2 LIKE ADRC-CITY2,

COUNTRY LIKE ADRC-COUNTRY,

STREET LIKE ADRC-STREET,

STRSUPPL1 LIKE ADRC-STR_SUPPL1,

STRSUPPL2 LIKE ADRC-STR_SUPPL2,

STRSUPPL3 LIKE ADRC-STR_SUPPL3,

NRART LIKE TPAR-NRART,

VTEXT1 LIKE TPART-VTEXT,

PARVW LIKE TPART-PARVW,

BEZE_I LIKE TVLVT-BEZEI,

VTEXT LIKE V_T178-VTEXT,

V_TEXT like V_T188-VTEXT,

BEZEI1 LIKE TVM1T-BEZEI,

BEZEI2 LIKE TVM2T-BEZEI,

BEZEI3 LIKE TVM3T-BEZEI,

BEZEI4 LIKE TVM4T-BEZEI,

BEZEI5 LIKE TVM5T-BEZEI,

VTEXT2 LIKE TVKGGT-VTEXT,

SMTP_ADDR LIKE ADR6-SMTP_ADDR,

END OF TY_TAB.

DATA: ITAB TYPE STANDARD TABLE OF TY_TAB WITH HEADER LINE.

*WRITING THE QUERY

SELECT A1~VBELN

A1~VKORG

A1~ERDAT

A1~AUGRU

A2~BSTDK

A2~BSTDK_E

A2~BSTDK

A2~KONDA

A2~KDKG2

A3~MATNR

A3~SPART

A3~KONDM

A3~WAERK

A3~NETWR

A3~KWMENG

A3~VKAUS

A3~MVGR1

A3~MVGR2

A3~MVGR3

A3~MVGR4

A3~MVGR5

A4~POST_CODE1

A4~PO_BOX

A4~NAME1

A4~NAME2

A4~CITY1

A4~CITY2

A4~COUNTRY

A4~STREET

A4~STR_SUPPL1

A4~STR_SUPPL2

A4~STR_SUPPL3

A5~BEZEI

A6~KUNNR

A7~NRART

A8~VTEXT

A9~PARVW

A10~BEZEI

A11~VTEXT

A12~VTEXT

A13~BEZEI

A14~BEZEI

A15~BEZEI

A16~BEZEI

A17~BEZEI

A18~VTEXT

A20~SMTP_ADDR

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM VBAK AS A1

JOIN VBKD AS A2

ON A1_{VBELN = A2}VBELN

JOIN VBAP AS A3

ON A1_{VBELN = A3}VBELN

LEFT OUTER JOIN VBPA AS A6

ON A2_{VBELN = A6}VBELN

AND A2_{POSNR = A6}POSNR

*JOIN ADR6 AS A20

*ON A6_{ADRNR = A20}ADDRNUMBER

JOIN ADRC AS A4

ON A6_{ADRNR = A4}ADDRNUMBER

LEFT OUTER JOIN TVM1T AS A13

ON A3_{MVGR1 = A13}MVGR1

LEFT OUTER JOIN TVM2T AS A14

ON A3_{MVGR2 = A14}MVGR2

LEFT OUTER JOIN TVM3T AS A15

ON A3_{MVGR3 = A15}MVGR3

LEFT OUTER JOIN TVM4T AS A16

ON A3_{MVGR4 = A16}MVGR4

LEFT OUTER JOIN TVM5T AS A17

ON A3_{MVGR5 = A17}MVGR5

JOIN TPAR AS A7

ON A7_{PARVW = A6}PARVW

JOIN TPART AS A8

ON A8_{PARVW = A6}PARVW

LEFT OUTER JOIN T178T AS A11

ON A3_{KONDM = A11}KONDM

AND A11~SPRAS = SY-LANGU

LEFT OUTER JOIN T188T as A12

ON A2_{KONDA = A12}KONDA

AND A12~SPRAS = SY-LANGU

JOIN TPART AS A9

ON A6_{PARVW = A9}PARVW

LEFT OUTER JOIN TVLVT AS A10

ON A1_{ABRVW = A10}ABRVW

AND A10~SPRAS = SY-LANGU

LEFT OUTER JOIN TVAUT AS A5

ON A1_{AUGRU = A5}AUGRU

AND A5~SPRAS = SY-LANGU

LEFT OUTER JOIN TVKGGT AS A18

ON A8_{SPRAS = A18}SPRAS

UP TO 100 ROWS

WHERE A1~VBELN IN S_VBELN.

*PRINTING THE OUTPUT

loop AT ITAB.

WRITE :

/(30) 'Order No :', itab-VBELN,

/(30) 'Sales Org :', itab-VKORG,

/(30) 'Order Date :', itab-ERDAT,

/(30) 'Purchase Order # :', itab-BSTKD,

/(30) 'Purchase Order Date :', itab-BSTDK,

/(30) 'Order Reason code :', itab-AUGRU,

/(30) 'Order Reason description :', itab-BEZEI,

/(30) 'Partner Type :', itab-NRART,

/(30) 'Partner Type description :', itab-VTEXT,

/(30) 'Partner no :', itab-KUNNR,

/(30) 'Partner Name1 :', itab-NAME1,

/(30) 'Partner Name2 :', itab-NAME2,

/(30) 'Partner Address 1 :', itab-STREET,

/(30) 'Partner Address 2 :', itab-STRSUPPL1,

/(30) 'Partner Address 3 :', itab-STRSUPPL2,

/(30) 'Partner Address 4 :', itab-STRSUPPL3,

/(30) 'City :', itab-CITY1,

/(30) 'DISTRICT :', itab-CITY2,

/(30) 'Country :', itab-COUNTRY,

/(30) 'Post Code :', itab-POSTCODE1,

/(30) 'PO BOX :', itab-POBOX,

/(30) 'Partner email :', itab-SMTP_ADDR,

/(30) 'Material no :', itab-MATNR,

/(30) 'Material Division :', itab-SPART,

/(30) 'Product Line and Edition code:', itab-MVGR1,

/(30) 'Product Line and Edition Desc:', itab-BEZEI1,

/(30) 'Product Name code :', itab-MVGR2,

/(30) 'ProductName Desc :', itab-BEZEI2,

/(30) 'Product Version Code :', itab-MVGR3,

/(30) 'Product Version Desc :', itab-BEZEI3,

/(30) 'Product Platform Code :', itab-MVGR4,

/(30) 'Product Platform Desc :', itab-BEZEI4,

/(30) 'Product App Server Code :', itab-MVGR5,

/(30) 'Product App Server Desc :', itab-BEZEI5,

/(30) 'Compiler Code :', itab-KONDM,

/(30) 'Compiler Desc :', itab-VTEXT,

/(30) 'LanguageCode :', itab-KONDA,

/(30) 'LanguageDesc :', itab-V_TEXT,

/(30) 'Runtime Band Code :', itab-VKAUS,

/(30) 'Runtime Band description :', itab-BEZE_I,

/(30) 'Support Start Date :', itab-BSTDK,

/(30) 'Support End Date :', itab-BSTDK_E,

/(30) 'Support Level Code :', itab-KDKG2,

/(30) 'Support Level Desc :', itab-VTEXT2,

/(30) 'Order Quantity :', itab-KWMENG,

/(30) 'Net Price :', itab-NETWR,

/(30) 'Currency :', itab-WAERK.

uline.

ENDLOOP.

if sy-subrc <> 0.

write : / 'No data found'.

endif.

Re: need to check the code!

Former Member — Wed, 19 Dec 2007 11:08:14 GMT

Hello,

you have to many joins in this code. It is better to use 1 join between vbak vbap

(SD documents) and then several select into table.

Ex:

refresh lt_vbak.

Select

A1_{VBELN A1}VKORG A1_{ERDAT A1}AUGRU

A3~MATNR

A3~SPART

A3~KONDM

A3~WAERK

A3~NETWR

A3~KWMENG

A3~VKAUS

A3~MVGR1

A3~MVGR2

A3~MVGR3

A3~MVGR4

A3~MVGR5

into table lt_vbak_vbap

from vbak as a1 inner join vbap as a3

ON A1_{VBELN = A3}VBELN

where A1~vbeln in s_vbeln.

check sy-subrc eq 0.

refresh lt_itab.

loop at lt_vbak_vbap into lw_vbak_vbap.

clear lw_itab.

move corresponding lw_vbak_vbap to lw_itab.

select BSTDK

BSTDK_E

BSTDK

KONDA

KDKG2

from vbkd

into correspondign of lw_itab.

where

vbeln eq lw_vbak_vbap-vbeln

posnr eq lw_vbak_vbap-posnr.

..... other tables.

append lw_itab to lt_itab.

endloop.

Edited by: alejo arrighi on Dec 19, 2007 12:08 PM

Re: need to check the code!

Former Member — Wed, 19 Dec 2007 11:11:52 GMT

Hi,

You are using join on 18 Tables, it is not at all recommended by SAP to use Join on these many Tables. Use for all entries and try to get the data individually. Finally move the data to Final Internal table which will have all required data by looping VBAK and use read statements on remaining tables and move to final internal table.

Regards,

Satish

Re: need to check the code!

Former Member — Wed, 19 Dec 2007 11:18:01 GMT

I HAVE TO CREATE A BAPI WHICH WILL READ DATA FROM ALL THE TABLES AND FIELDS GIVEN .

topic Re: need to check the code! in Application Development and Automation Discussions

need to check the code!

Re: need to check the code!

Re: need to check the code!

Re: need to check the code!