topic report performance in Application Development and Automation Discussions

report performance

Former Member — Thu, 21 Jun 2007 08:48:07 GMT

Hi experts,

For this following shown report in Test client it is performing good.

But in production client when executing this report it is getting timed out.

I have tried this in different ways to find out the problem. But iam not getting the solution. For this now my requierement is to improve the performance of this report.

Please help me in this issue.

Report

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

----

Program Name : ZSD_QUOTE *
Functional Analyst : TOBY *
Programmer : Vijay Joseph *
Start date : 03/14/2007 (MM/DD/YYYY) *
Initial CTS : DEVK913353 *
Description : This program will generate the Quote detls *

----

Includes : None *
Function Modules : None *
Logical database : None *
Transaction Code : ZQUOTE *
External references : None *

----

Modification Log *

----

Date | Modified by | CTS number | Comments *

*----

03/14/2007|Vijay Joseph | DEVK913353 |Initial Development *

*----

REPORT ZSD_QUOTE

line-size 252

line-count 40(0)

no standard page heading . .

*Tables

TABLES : VBAK,

EQUI,

EKKO.

*TYPES

TYPES : BEGIN OF T_VBAP,

VBELN LIKE VBAK-VBELN,

ERDAT LIKE VBAK-ERDAT,

BNDDT LIKE VBAK-BNDDT,

NETWR LIKE VBAK-NETWR,

VKBUR LIKE VBAK-VKBUR,

BSTNK LIKE VBAK-BSTNK,

KUNNR LIKE VBAK-KUNNR,

POSNR LIKE VBAP-POSNR,

MATNR LIKE VBAP-MATNR,

PSTYV LIKE VBAP-PSTYV,

KWMENG LIKE VBAP-KWMENG,

VGBEL LIKE VBAP-VGBEL,

VGPOS LIKE VBAP-VGPOS,

WERKS LIKE VBAP-WERKS,

END OF T_VBAP.

*Types for the likp and lips

TYPES : BEGIN OF T_LIPS,

VBELN LIKE LIKP-VBELN,

LFDAT LIKE LIKP-LFDAT,

POSNR LIKE LIPS-POSNR,

PSTYV LIKE LIPS-PSTYV,

MATNR LIKE LIPS-MATNR,

WERKS LIKE LIPS-WERKS,

VGBEL LIKE LIPS-VGBEL,

VGPOS LIKE LIPS-VGPOS,

END OF T_LIPS.

*Types for the EQUI

TYPES : BEGIN OF T_EQUI,

EQUNR LIKE EQUI-EQUNR,

SERNR LIKE EQUI-SERNR,

KDAUF LIKE EQBS-KDAUF,

KDPOS LIKE EQBS-KDPOS,

END OF T_EQUI.

*Types for the KNA1

TYPES : BEGIN OF T_KNA1,

KUNNR LIKE KNA1-KUNNR,

NAME1 LIKE KNA1-NAME1,

END OF T_KNA1.

*Types for the MAKT

TYPES : BEGIN OF T_MAKT,

MATNR LIKE MAKT-MATNR,

MAKTX LIKE MAKT-MAKTX,

SPRAS LIKE MAKT-SPRAS,

END OF T_MAKT.

*types for VBFA

TYPES : BEGIN OF T_VBFA,

VBELV LIKE VBFA-VBELV,

POSNV LIKE VBFA-POSNV,

VBELN LIKE VBFA-VBELN,

POSNN LIKE VBFA-POSNN,

VBTYP_N LIKE VBFA-VBTYP_N,

END OF T_VBFA.

*types for the output

TYPES : BEGIN OF T_OUTPUT,

VBELV LIKE VBFA-VBELV,

ERDAT LIKE VBAK-ERDAT,

BNDDT LIKE VBAK-BNDDT,

NETWR(15) type C, " LIKE VBAK-NETWR,

VBELN LIKE VBAK-VBELN,

BSTNK LIKE VBAK-BSTNK,

KUNNR LIKE VBAK-KUNNR,

KWMENG(15) TYPE C, " LIKE VBAP-KWMENG,

NAME1 LIKE KNA1-NAME1,

VKBUR LIKE VBAK-VKBUR,

MATNR LIKE MAKT-MATNR,

MAKTX LIKE MAKT-MAKTX,

LFDAT LIKE LIKP-LFDAT,

SERNR LIKE EQUI-SERNR,

END OF T_OUTPUT.

*Types for the VBUP

TYPES : BEGIN OF T_VBUP,

vbeln LIKE VBUP-VBELN,

posnr LIKE VBUP-POSNR,

lfsta LIKE VBUP-LFSTA,

END OF T_VBUP.

*Internal Table

DATA : GIT_VBAP TYPE STANDARD TABLE OF T_VBAP,

GIT_LIPS TYPE STANDARD TABLE OF T_LIPS,

GIT_EQUI TYPE STANDARD TABLE OF T_EQUI,

GIT_KNA1 TYPE STANDARD TABLE OF T_KNA1,

GIT_MAKT TYPE STANDARD TABLE OF T_MAKT,

GIT_OUTPUT TYPE STANDARD TABLE OF T_OUTPUT,

GIT_VBUP TYPE STANDARD TABLE OF T_VBUP,

GIT_VBFA TYPE STANDARD TABLE OF T_VBFA.

*work Area

DATA : GWA_VBAP TYPE T_VBAP,

GWA_LIPS TYPE T_LIPS,

GWA_EQUI TYPE T_EQUI,

GWA_KNA1 TYPE T_KNA1,

GWA_MAKT TYPE T_MAKT,

GWA_OUTPUT TYPE T_OUTPUT,

GWA_VBUP TYPE T_VBUP,

GWA_VBFA TYPE T_VBFA.

*selection screen.

SELECTION-SCREEN : BEGIN OF BLOCK ZBLOCK WITH FRAME TITLE TEXT-015.

Select-options : S_VBELN FOR VBAK-VBELN,

S_ERDAT FOR VBAK-ERDAT, " OBLIGATORY,

S_EBELN FOR EKKO-EBELN MATCHCODE OBJECT MEKK,

S_SERNR FOR EQUI-SERNR MATCHCODE OBJECT EQSN.

PARAMETERS : P_WERKS LIKE VBAP-WERKS OBLIGATORY.

SELECTION-SCREEN : END OF BLOCK ZBLOCK.

**************top of page*********************************************

TOP-OF-PAGE.

PERFORM SAPSD_TOP_OF_PAGE.

**************At selection screen*************************************

at selection-screen.

*for validating the Sales Order

PERFORM SAPSD_SCREEN_VALIDATION_VBELN.

*for validating the plant

PERFORM SAPSD_SCREEN_VALIDATION_WERKS.

*for the validating the PO number

PERFORM SAPSD_SCREEN_VALIDATION_PO.

*for the validating the serial number

PERFORM SAPSD_SCREEN_VALIDATION_SERIAL.

***************strart of selection************************************

START-OF-SELECTION.

*Get the data

PERFORM SAPSD_FETCH_DATA.

*For the final output table

PERFORM SAPSD_OUTPUT.

&----

*& Form SAPSD_FETCH_DATA

&----

text

----

--> p1 text
<-- p2 text

----

FORM SAPSD_FETCH_DATA .

*FETCH FROM THE VBAK AND VBAP.

SELECT VBAK~VBELN

VBAK~ERDAT

VBAK~BNDDT

VBAK~NETWR

VBAK~VKBUR

VBAK~BSTNK

VBAK~KUNNR

VBAP~POSNR

VBAP~MATNR

VBAP~PSTYV

VBAP~KWMENG

VBAP~VGBEL

VBAP~VGPOS

VBAP~WERKS

FROM VBAK INNER JOIN VBAP

ON VBAK_{VBELN EQ VBAP}VBELN

INTO TABLE GIT_VBAP

WHERE VBAK~VBELN IN S_VBELN

AND VBAK~ERDAT IN S_ERDAT

AND VBAK~BSTNK IN S_EBELN

AND VBAP~PSTYV EQ 'IRRA'

AND VBAP~WERKS EQ P_WERKS.

IF SY-SUBRC EQ 0.

SORT GIT_VBAP BY VBELN.

else.

message e022(z1).

ENDIF.

*from vbfa

select VBELV

POSNV

VBELN

POSNN

VBTYP_N

into table git_vbfa

from vbfa

for all entries in git_vbap

where vbelv eq git_vbap-vbeln

and posnv eq git_vbap-posnr.

*FETCH DATA FROM THE LIKP AND LIPS

IF NOT GIT_VBAP IS INITIAL.

SELECT LIKP~VBELN

LIKP~LFDAT

LIPS~POSNR

LIPS~PSTYV

LIPS~MATNR

LIPS~WERKS

LIPS~VGBEL

LIPS~VGPOS

FROM LIKP INNER JOIN LIPS

ON LIKP_{VBELN EQ LIPS}VBELN

INTO TABLE GIT_LIPS

FOR ALL ENTRIES IN GIT_VBFA

WHERE LIPS~VBELN EQ GIT_VBFA-VBELN

and LIPS~POSNR EQ GIT_VBFA-POSNN.

AND LIPS~WERKS EQ GIT_VBAP-WERKS.
AND LIPS~MATNR EQ GIT_VBAP-MATNR.
AND LIPS~POSNR EQ GIT_VBAP-POSNR.
AND LIPS~PSTYV EQ 'IRRA'.

AND LIPS~VGPOS EQ GIT_VBAP-POSNR.

IF SY-SUBRC EQ 0.

SORT GIT_LIPS BY VBELN.

ENDIF.

*for getting the delivery status(dont take the delivered document number

*take only 'open'.

if not git_lips is initial.

select VBELN

posnr

lfsta

from vbup

into table git_vbup

for all entries in git_lips

where vbeln eq git_lips-vbeln

and posnr eq git_lips-posnr.

and ( lfsta EQ 'A' ) OR
( lfsta EQ 'B' ) .

if sy-subrc eq 0.

sort git_vbup by vbeln.

endif.

*To get the equipment number

IF NOT GIT_VBAP IS INITIAL.

SELECT EQUI~EQUNR

EQUI~SERNR

EQBS~KDAUF

EQBS~KDPOS

FROM EQUI INNER JOIN EQBS

ON EQUI_{EQUNR EQ EQBS}EQUNR

INTO TABLE GIT_EQUI

FOR ALL ENTRIES IN GIT_VBAP

WHERE EQUI~SERNR IN S_SERNR

AND EQBS~KDAUF EQ GIT_VBAP-VBELN.

IF SY-SUBRC EQ 0.

SORT GIT_EQUI BY EQUNR.

ENDIF.

*To get the customer name

IF NOT GIT_VBAP IS INITIAL.

SELECT KUNNR

NAME1

INTO TABLE GIT_KNA1

FROM KNA1

FOR ALL ENTRIES IN GIT_VBAP

WHERE KUNNR EQ GIT_VBAP-KUNNR.

IF SY-SUBRC EQ 0.

SORT GIT_KNA1 BY KUNNR.

ENDIF.

*to get the material number

if not git_vbap is initial.

SELECT MATNR

MAKTX

SPRAS

INTO TABLE GIT_MAKT

FROM MAKT

FOR ALL ENTRIES IN GIT_VBAP

WHERE MATNR EQ GIT_VBAP-MATNR

AND SPRAS EQ SY-LANGU.

IF SY-SUBRC EQ 0.

SORT GIT_MAKT BY MATNR.

ENDIF.

endif.

ENDFORM. " SAPSD_FETCH_DATA

&----

*& Form SAPSD_OUTPUT

&----

text

----

--> p1 text
<-- p2 text

----

FORM SAPSD_OUTPUT .

data : l_vbelv like vbfa-vbelv.

LOOP AT GIT_VBAP INTO GWA_VBAP.

*for getting the delivey date

clear : gwa_lips.

read table git_vbfa into gwa_vbfa with key vbelv = gwa_vbap-vbeln

posnv = gwa_vbap-posnr.

if sy-subrc eq 0.

read table git_lips into gwa_lips

with key VBELN = GWA_vbfa-Vbeln

POSNR = GWA_vbfa-posnn

PSTYV = 'IRRA'.

IF SY-SUBRC EQ 0.

GWA_OUTPUT-LFDAT = GWA_LIPS-LFDAT.

READ TABLE GIT_VBUP INTO GWA_VBUP

WITH KEY VBELN = GWA_LIPS-VBELN

POSNR = GWA_LIPS-POSNR.

IF SY-SUBRC EQ 0.

IF GWA_VBUP-LFSTA EQ 'A' OR GWA_VBUP-LFSTA EQ 'B'.

clear : l_vbelv.

select single vbelv
into l_vbelv
from vbfa
where VBELN EQ gwa_vbap-vbeln.

*Quote Number

if sy-subrc eq 0.
GWA_OUTPUT-VBELV = L_VBELV.
endif.

*Move the details to the final table

GWA_OUTPUT-VBELN = GWA_VBAP-VBELN.

GWA_OUTPUT-ERDAT = GWA_VBAP-ERDAT.

GWA_OUTPUT-BNDDT = GWA_VBAP-BNDDT.

GWA_OUTPUT-NETWR = GWA_VBAP-NETWR.

GWA_OUTPUT-KUNNR = GWA_VBAP-KUNNR.

GWA_OUTPUT-KWMENG = GWA_VBAP-KWMENG.

GWA_OUTPUT-BSTNK = GWA_VBAP-BSTNK.

for getting the name from kna1

CLEAR : GWA_KNA1.

READ TABLE GIT_KNA1 INTO GWA_KNA1

WITH KEY KUNNR = GWA_VBAP-KUNNR.

IF SY-SUBRC EQ 0.

GWA_OUTPUT-NAME1 = GWA_KNA1-NAME1.

ENDIF.

GWA_OUTPUT-VKBUR = GWA_VBAP-VKBUR.

*for getting mateial number and description

CLEAR : GWA_MAKT.

READ TABLE GIT_MAKT INTO GWA_MAKT

WITH KEY MATNR = GWA_VBAP-MATNR

SPRAS = SY-LANGU.

IF SY-SUBRC EQ 0.

GWA_OUTPUT-MATNR = GWA_MAKT-MATNR.

GWA_OUTPUT-MAKTX = GWA_MAKT-MAKTX.

ENDIF.

for getting the serial number

clear : gwa_equi.

read table git_equi into gwa_equi

with key kdauf = gwa_vbap-vbeln

kdpos = gwa_vbap-posnr.

IF SY-SUBRC EQ 0.

GWA_OUTPUT-SERNR = gwa_equi-sernr.

ENDIF.

append gwa_output to git_output.

ENDIF.

CLEAR : GWA_VBAP,

GWA_OUTPUT.

ENDLOOP.

*free and refres the internal table

clear : git_vbap,

git_lips,

git_makt,

git_equi.

refresh : git_vbap,

git_lips,

git_makt,

git_equi.

free: git_vbap,

git_lips,

git_makt,

git_equi.

loop at git_output into gwa_output.

FORMAT COLOR COL_NORMAL INTENSIFIED OFF INVERSE OFF.

WRITE : /1 sy-vline,

2 gwa_output-VBELV, "qte no

13 sy-vline,

14 gwa_output-ERDAT, "cr date

25 sy-vline,

26 gwa_output-BNDDT, "exp date

36 sy-vline,

37 gwa_output-NETWR, "qte value

53 sy-vline,

54 gwa_output-VBELN, "so

65 SY-VLINE,

66 gwa_output-BSTNK, "po

87 SY-VLINE,

88 gwa_output-KUNNR, "customer

99 SY-VLINE,

100 gwa_output-NAME1, "Name

136 sy-vline,

137 gwa_output-VKBUR, "S off

142 sy-vline,

143 gwa_output-MATNR, "Material

162 sy-vline,

163 gwa_output-MAKTX , "Description

204 sy-vline,

205 gwa_output-KWMENG, "Or Qty

221 sy-vline,

222 gwa_output-LFDAT, "Del Date

233 sy-vline,

234 gwa_output-SERNR, "Serial No

252 SY-VLINE.

uline.

clear : gwa_output.

endloop.

*free and refresh the internal table

refresh : git_output.

free : git_output.

ENDFORM. " SAPSD_OUTPUT

&----

*& Form SAPSD_TOP_OF_PAGE

&----

text

----

--> p1 text
<-- p2 text

----

FORM SAPSD_TOP_OF_PAGE .

write: /15 text-016, 30 sy-repid.

FORMAT COLOR COL_HEADING INTENSIFIED ON INVERSE OFF.

ULINE.

WRITE : /1 sy-vline,

2 text-001, "QTE No

13 sy-vline,

14 text-002, "CR Date

25 sy-vline,

26 text-003, "EX Date

36 sy-vline,

37 text-004, "QT Value

53 sy-vline,

54 text-005, "SO

65 SY-VLINE,

66 text-006, "PO

87 SY-VLINE,

88 text-007, "Customer

99 sy-vline,

100 text-008, "Name

136 sy-vline,

137 text-009, "S off

142 sy-vline,

143 text-010, "Material

162 sy-vline,

163 text-011 , "Description

204 sy-vline,

205 text-012, "Or Qty

221 sy-vline,

222 text-013, "Del Date

233 sy-vline,

234 text-014, "Serial No

252 SY-VLINE.

ULINE.

ENDFORM. " SAPSD_TOP_OF_PAG,

&----

*& Form SAPSD_SCREEN_VALIDATION_VBELN

&----

text

----

--> p1 text
<-- p2 text

----

FORM SAPSD_SCREEN_VALIDATION_VBELN .

IF NOT S_VBELN IS INITIAL.

*To check the plant.If entry is wrong the an error message displayed.

DATA : l_VBELN LIKE VBAK-VBELN. "SO

Validating SO in selection screen

SELECT SINGLE VBELN INTO l_VBELN FROM VBAK

WHERE VBELN IN S_VBELN.

IF sy-subrc NE 0.

MESSAGE e023(Z1). " Invalid SO

ENDIF.

endif.

ENDFORM. " SAPSD_SCREEN_VALIDATION_VBELN

&----

*& Form SAPSD_SCREEN_VALIDATION_WERKS

&----

text

----

--> p1 text
<-- p2 text

----

FORM SAPSD_SCREEN_VALIDATION_WERKS .

IF NOT P_WERKS IS INITIAL.

*To check the plant.

*If entry is wrong the an error message displayed.

DATA : l_WERKS LIKE T001W-WERKS. "Plant

Validating Plant in selection screen

SELECT SINGLE WERKS INTO l_WERKS FROM T001W

WHERE WERKS EQ P_WERKS.

IF sy-subrc NE 0.

MESSAGE e024(Z1). " Invalid Plant

ENDIF.

ENDFORM. " SAPSD_SCREEN_VALIDATION_WERKS

&----

*& Form SAPSD_SCREEN_VALIDATION_PO

&----

text

----

--> p1 text
<-- p2 text

----

FORM SAPSD_SCREEN_VALIDATION_PO .

IF NOT S_EBELN IS INITIAL.

*To check the plant.

*If entry is wrong the an error message displayed.

DATA : l_EBELN LIKE EKKO-EBELN. "PO

Validating PO in selection screen

SELECT SINGLE EBELN INTO l_EBELN FROM EKKO

WHERE EBELN IN S_EBELN.

IF sy-subrc NE 0.

MESSAGE e025(Z1). " Invalid PO

ENDIF.

ENDFORM. " SAPSD_SCREEN_VALIDATION_PO

&----

*& Form SAPSD_SCREEN_VALIDATION_SERIAL

&----

text

----

--> p1 text
<-- p2 text

----

FORM SAPSD_SCREEN_VALIDATION_SERIAL .

IF NOT S_SERNR IS INITIAL.

*To check the SERIAL NO.

*If entry is wrong the an error message displayed.

DATA : l_SERNR LIKE EQUI-SERNR. "Serial No

Validating Serial NO in selection screen

SELECT SINGLE SERNR INTO l_SERNR FROM EQUI

WHERE SERNR IN S_SERNR.

IF sy-subrc NE 0.

MESSAGE e026(Z1). " Invalid Serial No

ENDIF.

ENDFORM. " SAPSD_SCREEN_VALIDATION_SERIAL

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

Thanks & Regards

Ahammad

Re: report performance

Former Member — Thu, 21 Jun 2007 09:42:33 GMT

Hi Ahamd,

Normally in development system the data volume will be less but in production it will be huge. So find out what is data volume in the key tables for example...VBFA, equipmen tables, etc will have huge volume of data in production system.

Also run the report using se30 tcode and find out which query is taking more time and then you can tune it.

Reward if useful...

Thanks..

Preetham S

Re: report performance

Former Member — Thu, 21 Jun 2007 12:04:50 GMT

Hi ,

Please help me i this. In se30 how can we know which querey is taking more time.

Please tell me the process. Up to now i had not used se30 please help me in this.

Thanks

Re: report performance

Former Member — Thu, 21 Jun 2007 12:12:12 GMT

Hi,

Go to ST05 and analyze the trace list. There u can see the individual select statement time.

Regards

Kannaiah

Re: report performance

kiran_k8 — Thu, 21 Jun 2007 12:22:01 GMT

Ahmad,

Post the code but not with the history.

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

----

Program Name : ZSD_QUOTE *
Functional Analyst : TOBY *
Programmer : Vijay Joseph *
Start date : 03/14/2007 (MM/DD/YYYY) *
Initial CTS : DEVK913353 *
Description : This program will generate the Quote detls *

----

Includes : None *
Function Modules : None *
Logical database : None *
Transaction Code : ZQUOTE *
External references : None *

----

Modification Log *

----

Date | Modified by | CTS number | Comments *

*----

03/14/2007|Vijay Joseph | DEVK913353 |Initial Development *

*----

REPORT ZSD_QUOTE

line-size 252

line-count 40(0)

no standard page heading . .

*Tables

TABLES : VBAK,

EQUI,

EKKO.

*TYPES

TYPES : BEGIN OF T_VBAP,

VBELN LIKE VBAK-VBELN,

ERDAT LIKE VBAK-ERDAT,

BNDDT LIKE VBAK-BNDDT,

NETWR LIKE VBAK-NETWR,

VKBUR LIKE VBAK-VKBUR,

BSTNK LIKE VBAK-BSTNK,

KUNNR LIKE VBAK-KUNNR,

POSNR LIKE VBAP-POSNR,

MATNR LIKE VBAP-MATNR,

PSTYV LIKE VBAP-PSTYV,

KWMENG LIKE VBAP-KWMENG,

VGBEL LIKE VBAP-VGBEL,

VGPOS LIKE VBAP-VGPOS,

WERKS LIKE VBAP-WERKS,

END OF T_VBAP.

*Types for the likp and lips

TYPES : BEGIN OF T_LIPS,

VBELN LIKE LIKP-VBELN,

LFDAT LIKE LIKP-LFDAT,

POSNR LIKE LIPS-POSNR,

PSTYV LIKE LIPS-PSTYV,

MATNR LIKE LIPS-MATNR,

WERKS LIKE LIPS-WERKS,

VGBEL LIKE LIPS-VGBEL,

VGPOS LIKE LIPS-VGPOS,

END OF T_LIPS.

*Types for the EQUI

TYPES : BEGIN OF T_EQUI,

EQUNR LIKE EQUI-EQUNR,

SERNR LIKE EQUI-SERNR,

KDAUF LIKE EQBS-KDAUF,

KDPOS LIKE EQBS-KDPOS,

END OF T_EQUI.

*Types for the KNA1

TYPES : BEGIN OF T_KNA1,

KUNNR LIKE KNA1-KUNNR,

NAME1 LIKE KNA1-NAME1,

END OF T_KNA1.

*Types for the MAKT

TYPES : BEGIN OF T_MAKT,

MATNR LIKE MAKT-MATNR,

MAKTX LIKE MAKT-MAKTX,

SPRAS LIKE MAKT-SPRAS,

END OF T_MAKT.

*types for VBFA

TYPES : BEGIN OF T_VBFA,

VBELV LIKE VBFA-VBELV,

POSNV LIKE VBFA-POSNV,

VBELN LIKE VBFA-VBELN,

POSNN LIKE VBFA-POSNN,

VBTYP_N LIKE VBFA-VBTYP_N,

END OF T_VBFA.

*types for the output

TYPES : BEGIN OF T_OUTPUT,

VBELV LIKE VBFA-VBELV,

ERDAT LIKE VBAK-ERDAT,

BNDDT LIKE VBAK-BNDDT,

NETWR(15) type C, " LIKE VBAK-NETWR,

VBELN LIKE VBAK-VBELN,

BSTNK LIKE VBAK-BSTNK,

KUNNR LIKE VBAK-KUNNR,

KWMENG(15) TYPE C, " LIKE VBAP-KWMENG,

NAME1 LIKE KNA1-NAME1,

VKBUR LIKE VBAK-VKBUR,

MATNR LIKE MAKT-MATNR,

MAKTX LIKE MAKT-MAKTX,

LFDAT LIKE LIKP-LFDAT,

SERNR LIKE EQUI-SERNR,

END OF T_OUTPUT.

*Types for the VBUP

TYPES : BEGIN OF T_VBUP,

vbeln LIKE VBUP-VBELN,

posnr LIKE VBUP-POSNR,

lfsta LIKE VBUP-LFSTA,

END OF T_VBUP.

*Internal Table

DATA : GIT_VBAP TYPE STANDARD TABLE OF T_VBAP,

GIT_LIPS TYPE STANDARD TABLE OF T_LIPS,

GIT_EQUI TYPE STANDARD TABLE OF T_EQUI,

GIT_KNA1 TYPE STANDARD TABLE OF T_KNA1,

GIT_MAKT TYPE STANDARD TABLE OF T_MAKT,

GIT_OUTPUT TYPE STANDARD TABLE OF T_OUTPUT,

GIT_VBUP TYPE STANDARD TABLE OF T_VBUP,

GIT_VBFA TYPE STANDARD TABLE OF T_VBFA.

*work Area

DATA : GWA_VBAP TYPE T_VBAP,

GWA_LIPS TYPE T_LIPS,

GWA_EQUI TYPE T_EQUI,

GWA_KNA1 TYPE T_KNA1,

GWA_MAKT TYPE T_MAKT,

GWA_OUTPUT TYPE T_OUTPUT,

GWA_VBUP TYPE T_VBUP,

GWA_VBFA TYPE T_VBFA.

*selection screen.

SELECTION-SCREEN : BEGIN OF BLOCK ZBLOCK WITH FRAME TITLE TEXT-015.

Select-options : S_VBELN FOR VBAK-VBELN,

S_ERDAT FOR VBAK-ERDAT, " OBLIGATORY,

S_EBELN FOR EKKO-EBELN MATCHCODE OBJECT MEKK,

S_SERNR FOR EQUI-SERNR MATCHCODE OBJECT EQSN.

PARAMETERS : P_WERKS LIKE VBAP-WERKS OBLIGATORY.

SELECTION-SCREEN : END OF BLOCK ZBLOCK.

**************top of page*********************************************

TOP-OF-PAGE.

PERFORM SAPSD_TOP_OF_PAGE.

**************At selection screen*************************************

at selection-screen.

*for validating the Sales Order

PERFORM SAPSD_SCREEN_VALIDATION_VBELN.

*for validating the plant

PERFORM SAPSD_SCREEN_VALIDATION_WERKS.

*for the validating the PO number

PERFORM SAPSD_SCREEN_VALIDATION_PO.

*for the validating the serial number

PERFORM SAPSD_SCREEN_VALIDATION_SERIAL.

***************strart of selection************************************

START-OF-SELECTION.

*Get the data

PERFORM SAPSD_FETCH_DATA.

*For the final output table

PERFORM SAPSD_OUTPUT.

&----

*& Form SAPSD_FETCH_DATA

&----

text

----

--> p1 text
<-- p2 text

----

FORM SAPSD_FETCH_DATA .

*FETCH FROM THE VBAK AND VBAP.

SELECT VBAK~VBELN

VBAK~ERDAT

VBAK~BNDDT

VBAK~NETWR

VBAK~VKBUR

VBAK~BSTNK

VBAK~KUNNR

VBAP~POSNR

VBAP~MATNR

VBAP~PSTYV

VBAP~KWMENG

VBAP~VGBEL

VBAP~VGPOS

VBAP~WERKS

FROM VBAK INNER JOIN VBAP

ON VBAK_{VBELN EQ VBAP}VBELN

INTO TABLE GIT_VBAP

WHERE VBAK~VBELN IN S_VBELN

AND VBAK~ERDAT IN S_ERDAT

AND VBAK~BSTNK IN S_EBELN

AND VBAP~PSTYV EQ 'IRRA'

AND VBAP~WERKS EQ P_WERKS.

IF SY-SUBRC EQ 0.

SORT GIT_VBAP BY VBELN ASCENDING.

else.

message e022(z1).

ENDIF.

*from vbfa

select VBELV

POSNV

VBELN

POSNN

VBTYP_N

into table git_vbfa

from vbfa

for all entries in git_vbap

where vbelv eq git_vbap-vbeln

and posnv eq git_vbap-posnr.

*FETCH DATA FROM THE LIKP AND LIPS

IF NOT GIT_VBAP IS INITIAL.

SELECT LIKP~VBELN

LIKP~LFDAT

LIPS~POSNR

LIPS~PSTYV

LIPS~MATNR

LIPS~WERKS

LIPS~VGBEL

LIPS~VGPOS

FROM LIKP INNER JOIN LIPS

ON LIKP_{VBELN EQ LIPS}VBELN

INTO TABLE GIT_LIPS

FOR ALL ENTRIES IN GIT_VBFA

WHERE LIPS~VBELN EQ GIT_VBFA-VBELN

and LIPS~POSNR EQ GIT_VBFA-POSNN.

AND LIPS~WERKS EQ GIT_VBAP-WERKS.
AND LIPS~MATNR EQ GIT_VBAP-MATNR.
AND LIPS~POSNR EQ GIT_VBAP-POSNR.
AND LIPS~PSTYV EQ 'IRRA'.

AND LIPS~VGPOS EQ GIT_VBAP-POSNR.

IF SY-SUBRC EQ 0.

SORT GIT_LIPS BY VBELN ASCENDING.

ENDIF.

*for getting the delivery status(dont take the delivered document number

*take only 'open'.

if not git_lips is initial.

select VBELN

posnr

lfsta

from vbup

into table git_vbup

for all entries in git_lips

where vbeln eq git_lips-vbeln

and posnr eq git_lips-posnr.

and ( lfsta EQ 'A' ) OR
( lfsta EQ 'B' ) .

if sy-subrc eq 0.

sort git_vbup by vbeln ASCENDING.

endif.

*To get the equipment number

IF NOT GIT_VBAP IS INITIAL.

SELECT EQUI~EQUNR

EQUI~SERNR

EQBS~KDAUF

EQBS~KDPOS

FROM EQUI INNER JOIN EQBS

ON EQUI_{EQUNR EQ EQBS}EQUNR

INTO TABLE GIT_EQUI

FOR ALL ENTRIES IN GIT_VBAP

WHERE EQUI~SERNR IN S_SERNR

AND EQBS~KDAUF EQ GIT_VBAP-VBELN.

IF SY-SUBRC EQ 0.

SORT GIT_EQUI BY EQUNR ASCENDING.ENDIF.

ENDIF.

*To get the customer name

IF NOT GIT_VBAP IS INITIAL.

SELECT KUNNR

NAME1

INTO TABLE GIT_KNA1

FROM KNA1

FOR ALL ENTRIES IN GIT_VBAP

WHERE KUNNR EQ GIT_VBAP-KUNNR.

IF SY-SUBRC EQ 0.

SORT GIT_KNA1 BY KUNNR ASCENDING.

ENDIF.

*to get the material number

if not git_vbap is initial.

SELECT MATNR

MAKTX

SPRAS

INTO TABLE GIT_MAKT

FROM MAKT

FOR ALL ENTRIES IN GIT_VBAP

WHERE MATNR EQ GIT_VBAP-MATNR

AND SPRAS EQ SY-LANGU.

IF SY-SUBRC EQ 0.

SORT GIT_MAKT BY MATNR ASCENDING.

ENDIF.

endif.

ENDFORM. " SAPSD_FETCH_DATA

&----

*& Form SAPSD_OUTPUT

&----

text

----

--> p1 text
<-- p2 text

----

FORM SAPSD_OUTPUT .

data : l_vbelv like vbfa-vbelv.

LOOP AT GIT_VBAP INTO GWA_VBAP.

*for getting the delivey date

clear : gwa_lips.

SORT GIT_VBFA BY VBELV POSNV ASCENDING.

read table git_vbfa into gwa_vbfa with key vbelv = gwa_vbap-vbeln

posnv = gwa_vbap-posnr BINARY SEARCH.

if sy-subrc eq 0.

SORT GIT_LIPS BY VBELN POSNR PSTYV ASCENDING.

read table git_lips into gwa_lips

with key VBELN = GWA_vbfa-Vbeln

POSNR = GWA_vbfa-posnn

PSTYV = 'IRRA' BINARYSEARCH.

IF SY-SUBRC EQ 0.

GWA_OUTPUT-LFDAT = GWA_LIPS-LFDAT.

SORT GIT_VBUP BY VBELN POSNR ASCENDING.

READ TABLE GIT_VBUP INTO GWA_VBUP

WITH KEY VBELN = GWA_LIPS-VBELN

POSNR = GWA_LIPS-POSNR BINARY SEARCH.

IF SY-SUBRC EQ 0.

IF GWA_VBUP-LFSTA EQ 'A' OR GWA_VBUP-LFSTA EQ 'B'.

clear : l_vbelv.

select single vbelv
into l_vbelv
from vbfa
where VBELN EQ gwa_vbap-vbeln.

*Quote Number

if sy-subrc eq 0.
GWA_OUTPUT-VBELV = L_VBELV.
endif.

*Move the details to the final table

GWA_OUTPUT-VBELN = GWA_VBAP-VBELN.

GWA_OUTPUT-ERDAT = GWA_VBAP-ERDAT.

GWA_OUTPUT-BNDDT = GWA_VBAP-BNDDT.

GWA_OUTPUT-NETWR = GWA_VBAP-NETWR.

GWA_OUTPUT-KUNNR = GWA_VBAP-KUNNR.

GWA_OUTPUT-KWMENG = GWA_VBAP-KWMENG.

GWA_OUTPUT-BSTNK = GWA_VBAP-BSTNK.

for getting the name from kna1

CLEAR : GWA_KNA1.

SORT GIT_KNA1 BY KUNNR ASCENDING.

READ TABLE GIT_KNA1 INTO GWA_KNA1

WITH KEY KUNNR = GWA_VBAP-KUNNR BINARY SEARCH.

IF SY-SUBRC EQ 0.

GWA_OUTPUT-NAME1 = GWA_KNA1-NAME1.

ENDIF.

GWA_OUTPUT-VKBUR = GWA_VBAP-VKBUR.

*for getting mateial number and description

CLEAR : GWA_MAKT.

SORT GIT_MAKT BY MATNR SPRAS ASCENDING.

READ TABLE GIT_MAKT INTO GWA_MAKT

WITH KEY MATNR = GWA_VBAP-MATNR

SPRAS = SY-LANGU BINARY SEARCH.

IF SY-SUBRC EQ 0.

GWA_OUTPUT-MATNR = GWA_MAKT-MATNR.

GWA_OUTPUT-MAKTX = GWA_MAKT-MAKTX.

ENDIF.

for getting the serial number

clear : gwa_equi.

SORT GIT_EQUI BY KDAUF KDPOS ASCENDING.

read table git_equi into gwa_equi

with key kdauf = gwa_vbap-vbeln

kdpos = gwa_vbap-posnr BINARY SEARCH.

IF SY-SUBRC EQ 0.

GWA_OUTPUT-SERNR = gwa_equi-sernr.

ENDIF.

append gwa_output to git_output.

ENDIF.

CLEAR : GWA_VBAP,

GWA_OUTPUT.

ENDLOOP.

*free and refres the internal table

clear : git_vbap,

git_lips,

git_makt,

git_equi.

refresh : git_vbap,

git_lips,

git_makt,

git_equi.

free: git_vbap,

git_lips,

git_makt,

git_equi.

loop at git_output into gwa_output.

FORMAT COLOR COL_NORMAL INTENSIFIED OFF INVERSE OFF.

WRITE : /1 sy-vline,

2 gwa_output-VBELV, "qte no

13 sy-vline,

14 gwa_output-ERDAT, "cr date

25 sy-vline,

26 gwa_output-BNDDT, "exp date

36 sy-vline,

37 gwa_output-NETWR, "qte value

53 sy-vline,

54 gwa_output-VBELN, "so

65 SY-VLINE,

66 gwa_output-BSTNK, "po

87 SY-VLINE,

88 gwa_output-KUNNR, "customer

99 SY-VLINE,

100 gwa_output-NAME1, "Name

136 sy-vline,

137 gwa_output-VKBUR, "S off

142 sy-vline,

143 gwa_output-MATNR, "Material

162 sy-vline,

163 gwa_output-MAKTX , "Description

204 sy-vline,

205 gwa_output-KWMENG, "Or Qty

221 sy-vline,

222 gwa_output-LFDAT, "Del Date

233 sy-vline,

234 gwa_output-SERNR, "Serial No

252 SY-VLINE.

uline.

clear : gwa_output.

endloop.

*free and refresh the internal table

refresh : git_output.

free : git_output.

ENDFORM. " SAPSD_OUTPUT

&----

*& Form SAPSD_TOP_OF_PAGE

&----

text

----

--> p1 text
<-- p2 text

----

FORM SAPSD_TOP_OF_PAGE .

write: /15 text-016, 30 sy-repid.

FORMAT COLOR COL_HEADING INTENSIFIED ON INVERSE OFF.

ULINE.

WRITE : /1 sy-vline,

2 text-001, "QTE No

13 sy-vline,

14 text-002, "CR Date

25 sy-vline,

26 text-003, "EX Date

36 sy-vline,

37 text-004, "QT Value

53 sy-vline,

54 text-005, "SO

65 SY-VLINE,

66 text-006, "PO

87 SY-VLINE,

88 text-007, "Customer

99 sy-vline,

100 text-008, "Name

136 sy-vline,

137 text-009, "S off

142 sy-vline,

143 text-010, "Material

162 sy-vline,

163 text-011 , "Description

204 sy-vline,

205 text-012, "Or Qty

221 sy-vline,

222 text-013, "Del Date

233 sy-vline,

234 text-014, "Serial No

252 SY-VLINE.

ULINE.

ENDFORM. " SAPSD_TOP_OF_PAG,

&----

*& Form SAPSD_SCREEN_VALIDATION_VBELN

&----

text

----

--> p1 text
<-- p2 text

----

FORM SAPSD_SCREEN_VALIDATION_VBELN .

IF NOT S_VBELN IS INITIAL.

*To check the plant.If entry is wrong the an error message displayed.

DATA : l_VBELN LIKE VBAK-VBELN. "SO

Validating SO in selection screen

SELECT SINGLE VBELN INTO l_VBELN FROM VBAK

WHERE VBELN IN S_VBELN.

IF sy-subrc NE 0.

MESSAGE e023(Z1). " Invalid SO

ENDIF.

endif.

ENDFORM. " SAPSD_SCREEN_VALIDATION_VBELN

&----

*& Form SAPSD_SCREEN_VALIDATION_WERKS

&----

text

----

--> p1 text
<-- p2 text

----

FORM SAPSD_SCREEN_VALIDATION_WERKS .

IF NOT P_WERKS IS INITIAL.

*To check the plant.

*If entry is wrong the an error message displayed.

DATA : l_WERKS LIKE T001W-WERKS. "Plant

Validating Plant in selection screen

SELECT SINGLE WERKS INTO l_WERKS FROM T001W

WHERE WERKS EQ P_WERKS.

IF sy-subrc NE 0.

MESSAGE e024(Z1). " Invalid Plant

ENDIF.

ENDFORM. " SAPSD_SCREEN_VALIDATION_WERKS

&----

*& Form SAPSD_SCREEN_VALIDATION_PO

&----

text

----

--> p1 text
<-- p2 text

----

FORM SAPSD_SCREEN_VALIDATION_PO .

IF NOT S_EBELN IS INITIAL.

*To check the plant.

*If entry is wrong the an error message displayed.

DATA : l_EBELN LIKE EKKO-EBELN. "PO

Validating PO in selection screen

SELECT SINGLE EBELN INTO l_EBELN FROM EKKO

WHERE EBELN IN S_EBELN.

IF sy-subrc NE 0.

MESSAGE e025(Z1). " Invalid PO

ENDIF.

ENDFORM. " SAPSD_SCREEN_VALIDATION_PO

&----

*& Form SAPSD_SCREEN_VALIDATION_SERIAL

&----

text

----

--> p1 text
<-- p2 text

----

FORM SAPSD_SCREEN_VALIDATION_SERIAL .

IF NOT S_SERNR IS INITIAL.

*To check the SERIAL NO.

*If entry is wrong the an error message displayed.

DATA : l_SERNR LIKE EQUI-SERNR. "Serial No

Validating Serial NO in selection screen

SELECT SINGLE SERNR INTO l_SERNR FROM EQUI

WHERE SERNR IN S_SERNR.

IF sy-subrc NE 0.

MESSAGE e026(Z1). " Invalid Serial No

ENDIF.

ENDFORM. " SAPSD_SCREEN_VALIDATION_SERIAL

Always sort a table in ascening order if you are reading it using the same key.

Read a table always using binary search because it searching the internal table using index.By default it is linear search which reads the internal table record by record thus taking more time.But a binary search read the internal table w.r.t the index.

I had modified upto some extent,just check whether it works or not.

K.Kiran.

Re: report performance

kiran_k8 — Thu, 21 Jun 2007 12:26:22 GMT

Ahmad,

1.If possible limit the join only for two tables.

2.Try using indexes to fetch the data in the select statement.See to that the where clause has this index field in the same sequence as they are in the index.

3.read the table with key vbeln = itab-vbeln transporting no fields.

this will not fetch the records into the header when not needed thus increasing the performance.

For more search sdn on keyword optimisation.

K.Kiran.