Performance in report

Former Member — Fri, 22 Jun 2007 09:22:10 GMT

Hi Experts,

In this following shown report Data base performance is consuming , can you please help me in this issue where can i increase my performance. Please help me urgent.

Report need performance

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

----

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

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

Please help me in this .

Thanks & Regards

Ahammad

topic Re: Performance in report in Application Development and Automation Discussions

Performance in report

Re: Performance in report

Re: Performance in report

Re: Performance in report

Re: Performance in report