08-08-2012 6:35 AM
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.
08-08-2012 7:36 AM
Hi Hema,
Please use the Transaction ST05 for analysis, and find out which query is taking long time and fine tune the same.
The SQL trace tells you:
Please reffer the below link , for the trace procedure.
Regards,
Arun
08-08-2012 9:29 AM