Skip to Content
0
Former Member
Aug 08, 2012 at 05:35 AM

Report taking long time to run ...

475 Views

Dear Experts,

I have the below report that pulls all Purchase orders for a single Company code. The report is taking more than 4 hours in production. Need to optimise the running time. Please help.

START-OF-SELECTION.

PERFORM get_po_details.

END-OF-SELECTION.

PERFORM process_data.

*&---------------------------------------------------------------------*

*& Form process_data

*&---------------------------------------------------------------------*

FORM process_data.

data:l_date(10) type c,

l_menge(16) type c,

l_len type i,

l_len1 type i.

*Looping the Internal Tables

IF NOT it_ekpo[] IS INITIAL.

LOOP AT it_ekpo INTO wa_ekpo.

CLEAR : f_flag,l_date.

v_erekz = wa_ekpo-erekz.

wa_output-ebelp = wa_ekpo-ebelp.

if wa_ekpo-txz01 ca '#'.

replace '#' with ' ' into wa_ekpo-txz01.

endif.

wa_output-txz01 = wa_ekpo-txz01.

wa_output-menge = wa_ekpo-menge.

wa_output-meins = wa_ekpo-meins.

wa_output-netpr = wa_ekpo-netpr.

wa_output-netwr = wa_ekpo-netwr.

wa_output-afnam = wa_ekpo-afnam.

READ TABLE it_ekko INTO wa_ekko WITH KEY ebeln = wa_ekpo-ebeln.

IF sy-subrc = 0.

wa_output-bukrs = wa_ekko-bukrs.

wa_output-lifnr = wa_ekko-lifnr.

wa_output-ebeln = wa_ekko-ebeln.

wa_output-bedat = wa_ekko-bedat.

* Format date

l_date+0(2) = wa_output-bedat+6(2).

l_date+2(2) = wa_output-bedat+4(2).

l_date+4(4) = wa_output-bedat+0(4).

concatenate l_date+0(2) l_date+2(2) l_date+4(4) into l_date separated by c_dot.

wa_output-bedat = l_date.

ENDIF.

READ TABLE it_lfa1 INTO wa_lfa1 WITH KEY lifnr = wa_ekko-lifnr sperr = 'X'.

IF sy-subrc = 0.

continue.

else.

wa_output-stceg = wa_lfa1-stceg .

ENDIF.

IF NOT wa_output-ebelp IS INITIAL.

*Fetch the Invoice Quantity

PERFORM invoice_quantity.

*Check for Open PO

IF f_flag NE '1'.

CONTINUE.

ELSE.

*PO Quantity

v_po = qty_invd - wa_output-menge.

*PO Quantity substracted IR Qunatity.

v_qo = wa_output-menge - qty_invd.

l_menge = v_qo.

CALL FUNCTION 'FTR_CORR_SWIFT_DELETE_ENDZERO'

CHANGING

c_value = l_menge.

l_len = strlen( l_menge ) .

l_len1 = l_len - 1.

if l_menge+l_len1(1) = '.'.

replace '.' with ' ' into l_menge.

endif.

wa_output-menge = l_menge . "Quantity

*Difference of PO amount and Invoice amount.

wa_output-netwr = wa_output-netwr - amt_invd.

IF ( v_po LT c_0 AND v_erekz NE c_x AND

wa_output-netwr GE c_0 ).

READ TABLE it_ekkn INTO wa_ekkn WITH KEY ebeln = wa_ekpo-ebeln

ebelp = wa_ekpo-ebelp.

IF sy-subrc = 0.

wa_output-wempf = wa_ekkn-wempf.

ENDIF.

APPEND wa_output TO it_output.

CLEAR :wa_output, wa_ekko, wa_ekpo, wa_ekbe,qty_invd,

amt_invd,v_po,v_qo.

else.

clear: qty_invd,amt_invd,v_po,v_qo.

ENDIF.

ENDIF.

ENDIF.

ENDLOOP.

ENDIF.

ENDFORM. " process_data

*&---------------------------------------------------------------------*

*& Form get_po_details

*&---------------------------------------------------------------------*

FORM get_po_details.

REFRESH:it_ekko,it_ekpo,it_ekkn.

* Fetch the Po number, Vendor Details, Po Data for given COmpany Code

SELECT ebeln "PO.Number

bukrs "Company Code

lifnr "Vendor

bedat "PO.Date

INTO TABLE it_ekko

FROM ekko

WHERE bukrs IN s_bukrs

AND lifnr IN s_lifnr

and bedat >= c_date.

IF NOT it_ekko[] IS INITIAL.

SELECT lifnr "Vendor

sperr

stceg "VAT Registration Number

INTO TABLE it_lfa1

FROM lfa1

FOR ALL ENTRIES IN it_ekko

WHERE lifnr = it_ekko-lifnr.

* Fetch the Po number,PO.Item

SORT it_ekko BY ebeln.

SELECT ebeln "PO.Number

ebelp "PO.ITEm

loekz "Indicator

txz01 "Text

menge "Quantity

meins "Unit.Measure

netpr "price per Unit

netwr "Extended price ordered

erekz

afnam

INTO TABLE it_ekpo

FROM ekpo

FOR ALL ENTRIES IN it_ekko

WHERE ebeln EQ it_ekko-ebeln

AND loekz EQ c_space.

ENDIF.

IF NOT it_ekpo[] IS INITIAL.

SORT it_ekpo by ebeln.

SELECT ebeln

ebelp

wempf

INTO TABLE it_ekkn

FROM ekkn

FOR ALL ENTRIES IN it_ekpo

WHERE ebeln = it_ekpo-ebeln

AND ebelp = it_ekpo-ebelp.

*Fetch history of PO details

SELECT ebeln "PO number

ebelp "Item no.of PO

zekkn "Sequential Number of Account Assignment

vgabe "Transaction/event type

gjahr "Material Document Year

belnr "Number of Material Document

buzei "Item in Material Document

menge

shkzg

INTO TABLE it_ekbe

FROM ekbe

FOR ALL ENTRIES IN it_ekpo

WHERE ebeln = it_ekpo-ebeln

AND ebelp = it_ekpo-ebelp

AND zekkn = '01'

AND ( vgabe = '2' OR vgabe = '3' ).

ENDIF.

IF NOT it_ekbe[] IS INITIAL.

SORT it_ekbe by ebeln.

* Fetch Invoice details for PO

SELECT belnr "Accounting Document Number

gjahr "Fiscal Year

buzei "Document Item in Invoice Document

ebeln "Purchasing Document Number

ebelp "Item Number of Purchasing Document

wrbtr

shkzg

menge

INTO TABLE it_ekbe_invoiced

FROM rseg

FOR ALL ENTRIES IN it_ekbe

WHERE belnr = it_ekbe-belnr

and gjahr = it_ekbe-gjahr

and buzei = it_ekbe-buzei

and ebeln = it_ekbe-ebeln

and ebelp = it_ekbe-ebelp.

endif.

IF NOT it_ekbe_invoiced[] IS INITIAL.

SORT it_ekbe_invoiced by belnr gjahr buzei.

ENDIF.

ENDFORM. " get_po_details

*&---------------------------------------------------------------------*

*& Form invoice_quantity

*&---------------------------------------------------------------------*

FORM invoice_quantity.

CLEAR wa_ekbe.

READ TABLE it_ekbe INTO wa_ekbe with key ebeln = wa_ekpo-ebeln

ebelp = wa_ekpo-ebelp.

IF sy-subrc = 0.

CLEAR wa_ekbe.

LOOP AT it_ekbe INTO wa_ekbe WHERE ebeln EQ wa_ekpo-ebeln

AND ebelp EQ wa_ekpo-ebelp.

* Quantity invoiced.

* If the first character of document number is 2, ignore them

IF wa_ekbe-belnr+0(1) <> '2'.

READ TABLE it_ekbe_invoiced into wa_ekbe_invoiced with key belnr = wa_ekbe-belnr

gjahr = wa_ekbe-gjahr

buzei = wa_ekbe-buzei

ebeln = wa_ekpo-ebeln

ebelp = wa_ekpo-ebelp BINARY SEARCH.

* Summation of Invoiced quantity based on Debit/Credit Indicator

IF wa_ekbe_invoiced-shkzg = 'S'.

qty_invd = qty_invd + wa_ekbe_invoiced-menge.

amt_invd = amt_invd + wa_ekbe_invoiced-wrbtr.

ELSE.

qty_invd = qty_invd - wa_ekbe_invoiced-menge.

amt_invd = amt_invd - wa_ekbe_invoiced-wrbtr.

ENDIF.

f_flag = '1'.

ENDIF.

clear wa_ekbe_invoiced.

ENDLOOP.

ELSE.

CLEAR amt_invd.

qty_invd = '0'.

f_flag = '1'.

ENDIF.

ENDFORM. " invoice_quantity

I tried using joins in Form "get_po_details" for the select statements but still it takes the same time and even more in fact.

Inner join for tables EKKO and LFA1 worked, join for EKPO and EKKN worked but join for EKBE and RSEG fails. Still running time not reduced:-(

Please help. Thanks.

Kind Regards,

Hema Sundar.