Skip to Content
0
Former Member
Nov 13, 2012 at 01:09 AM

performance isuses in this report

38 Views

Hi Guru,

I need a help form you.for me more performance in my report it take ten mint for single report.







SELECT banfn bnfpo
matnr werks txz01 menge badat

lfdat frgdt ebeln
ebelp

FROM eban

INTO TABLE it_pr

WHERE banfn IN s_banfn

AND werks IN s_werks

AND badat IN s_badat

AND loekz NE 'X'

AND knttp IN ('K' , 'A' , ' ' ).





IF it_pr[] IS NOT INITIAL.



SELECT a~ebeln a~bukrs a~lifnr a~ekgrp a~waers a~bedat

b~ebelp b~txz01 b~matnr b~menge b~meins b~netwr b~effwr b~banfn b~bnfpo

INTO TABLE it_po

FROM ( ekko AS a INNER JOIN ekpo AS b ON a~ebeln = b~ebeln )

FOR ALL ENTRIES IN it_pr

WHERE a~ebeln = it_pr-ebeln

and a~bukrs in s_bukrs

AND a~loekz NE 'X'

AND b~ebelp = it_pr-ebelp

AND b~loekz NE 'L'

AND b~banfn = it_pr-banfn

AND b~bnfpo = it_pr-bnfpo.



ELSE.



MESSAGE 'NO RECORD EXIST' TYPE 'E'.



ENDIF.





IF it_po[] IS NOT INITIAL.



SELECT b~mblnr b~mjahr b~zeile b~matnr b~lgort b~dmbtr b~meins b~erfmg b~ebeln b~ebelp

b~nplnr b~aufpl b~aplzl

a~xblnr a~bldat

INTO CORRESPONDING FIELDS OF TABLE it_mseg

FROM ( mkpf AS a INNER JOIN mseg AS b ON b~mblnr = a~mblnr

AND b~mjahr = a~mjahr )

FOR ALL ENTRIES IN it_po

WHERE b~bwart = '101'

AND b~ebeln = it_po-ebeln

AND b~ebelp = it_po-ebelp.



ENDIF.



IF it_po[] IS NOT INITIAL.



SELECT b~mblnr b~mjahr b~zeile b~matnr b~lgort b~dmbtr b~meins b~erfmg b~ebeln b~ebelp

b~sjahr b~smbln b~smblp b~nplnr b~aufpl b~aplzl

a~bldat a~xblnr

INTO TABLE it_mseg1

FROM ( mkpf AS a INNER JOIN mseg AS b ON b~mblnr = a~mblnr

AND b~mjahr = a~mjahr )

FOR ALL ENTRIES IN it_po

WHERE b~bwart = '102'

AND b~ebeln = it_po-ebeln

AND b~ebelp = it_po-ebelp.



ENDIF.



ENDFORM. "getdata





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

*& Form prepare_final_data

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

* text

*----------------------------------------------------------------------*

FORM prepare_final_data .



*Looping at internal table to Work Area



LOOP AT it_mseg INTO wa_mseg.



READ TABLE it_mseg1 INTO wa_mseg1 WITH KEY smbln = wa_mseg-mblnr

mjahr = wa_mseg-mjahr

zeile = wa_mseg-zeile.

IF sy-subrc = 0.

*

wa_mseg-tblnr = wa_mseg1-mblnr.

wa_mseg-tjahr = wa_mseg1-mjahr.

wa_mseg-teile = wa_mseg1-zeile.

wa_mseg-tdate = wa_mseg1-bldat.

wa_mseg-tatnr = wa_mseg1-matnr.

wa_mseg-tenge = wa_mseg1-erfmg.

wa_mseg-xblnr = wa_mseg1-xblnr.



MODIFY it_mseg FROM wa_mseg. "INDEX SY-TABIX.



ENDIF.



ENDLOOP.

DELETE it_mseg WHERE tblnr NE ' '.



LOOP AT it_pr INTO wa_pr.



it_final1-banfn = wa_pr-banfn.

it_final1-bnfpo = wa_pr-bnfpo.

it_final1-badat = wa_pr-badat.

it_final1-matnr = wa_pr-matnr.

it_final1-txz01 = wa_pr-txz01.

it_final1-menge = wa_pr-menge.

it_final1-lfdat = wa_pr-lfdat.

it_final1-frgdt = wa_pr-frgdt.

APPEND it_final1.

CLEAR wa_pr.

ENDLOOP.

SORT it_final1 BY banfn bnfpo ebeln ebelp mblnr.

DELETE ADJACENT DUPLICATES FROM it_final1 COMPARING ALL FIELDS.



LOOP AT it_final1.



it_final2-banfn = it_final1-banfn.

it_final2-bnfpo = it_final1-bnfpo.

it_final2-badat = it_final1-badat.

it_final2-matnr = it_final1-matnr.

it_final2-txz01 = it_final1-txz01.

it_final2-menge = it_final1-menge.

it_final2-lfdat = it_final1-lfdat.

it_final2-frgdt = it_final1-frgdt.



IF it_final1-banfn IS NOT INITIAL AND

it_final1-bnfpo IS NOT INITIAL.



LOOP AT it_po INTO wa_po WHERE banfn = it_final1-banfn

AND bnfpo = it_final1-bnfpo.



it_final2-ekgrp = wa_po-ekgrp.

it_final2-ebeln = wa_po-ebeln.

it_final2-ebelp = wa_po-ebelp.

it_final2-bedat = wa_po-bedat.

it_final2-matnrp = wa_po-matnr.

it_final2-txz01p = wa_po-txz01.

it_final2-mengep = wa_po-menge.

it_final2-effwr = wa_po-effwr.

it_final2-waers = wa_po-waers.

it_final2-netwr = wa_po-netwr.

it_final2-lifnr = wa_po-lifnr.



DATA : l_ebeln TYPE ekpo-ebeln.

DATA : l_rwbtr TYPE rwbtr.



IF l_ebeln <> it_final2-ebeln.



REFRESH it_payr1.

SELECT a~chect a~vblnr a~gjahr a~zaldt a~waers a~rwbtr

FROM ( payr AS a INNER JOIN bsik AS b ON b~belnr = a~vblnr

AND b~bukrs = a~zbukr

AND b~gjahr = a~gjahr )

INTO TABLE it_payr1

WHERE b~ebeln = it_final2-ebeln

AND b~blart = 'KZ'

AND b~bschl = '29'

AND a~voidr = '00'.



SELECT a~chect a~vblnr a~gjahr a~zaldt a~waers a~rwbtr

FROM ( payr AS a INNER JOIN bsak AS b ON b~belnr = a~vblnr

AND b~bukrs = a~zbukr

AND b~gjahr = a~gjahr )

INTO TABLE it_payr1

WHERE b~ebeln = it_final2-ebeln

AND b~blart = 'KZ'

AND b~bschl = '29'

AND a~voidr = '00'.



SORT it_payr1 BY chect vblnr gjahr.

DELETE ADJACENT DUPLICATES FROM it_payr1 COMPARING chect vblnr gjahr.



IF it_payr1[] IS NOT INITIAL.

APPEND it_final2.

CLEAR l_rwbtr.

CLEAR it_final2-rwbtr.

CLEAR it_final2-chect.

CLEAR it_final2-zaldt.



LOOP AT it_payr1 INTO wa_payr1.

it_final2-chect = wa_payr1-chect.

it_final2-zaldt = wa_payr1-zaldt.

l_rwbtr = l_rwbtr + wa_payr1-rwbtr * ( -1 ).

ENDLOOP.



it_final2-rwbtr = l_rwbtr.

CLEAR it_final2-ebelp.

CLEAR it_final2-bedat.

CLEAR it_final2-matnrp.

CLEAR it_final2-txz01p.

CLEAR it_final2-mengep.

CLEAR it_final2-effwr.

CLEAR it_final2-waers.

CLEAR it_final2-netwr.

CLEAR it_final2-lifnr.

CLEAR it_final2-name1.

APPEND it_final2.



ELSE.

it_final2-ekgrp = wa_po-ekgrp.

it_final2-ebeln = wa_po-ebeln.

it_final2-ebelp = wa_po-ebelp.

it_final2-bedat = wa_po-bedat.

it_final2-matnrp = wa_po-matnr.

it_final2-txz01p = wa_po-txz01.

it_final2-mengep = wa_po-menge.

it_final2-effwr = wa_po-effwr.

it_final2-waers = wa_po-waers.

it_final2-netwr = wa_po-netwr.

it_final2-lifnr = wa_po-lifnr.

APPEND it_final2.



ENDIF.



ELSE.



it_final2-ekgrp = wa_po-ekgrp.

it_final2-ebeln = wa_po-ebeln.

it_final2-ebelp = wa_po-ebelp.

it_final2-bedat = wa_po-bedat.

it_final2-matnrp = wa_po-matnr.

it_final2-txz01p = wa_po-txz01.

it_final2-mengep = wa_po-menge.

it_final2-effwr = wa_po-effwr.

it_final2-waers = wa_po-waers.

it_final2-netwr = wa_po-netwr.

it_final2-lifnr = wa_po-lifnr.

APPEND it_final2.

ENDIF.

l_ebeln = it_final2-ebeln.

ENDLOOP.



ENDIF.



CLEAR it_pr.

APPEND it_final2.

CLEAR it_final2.



ENDLOOP.



SORT it_final2 BY banfn bnfpo ebeln ebelp mblnr.

DELETE ADJACENT DUPLICATES FROM it_final2 COMPARING ALL FIELDS.



LOOP AT it_final2.



it_final3-banfn = it_final2-banfn.

it_final3-bnfpo = it_final2-bnfpo.

it_final3-badat = it_final2-badat.

it_final3-matnr = it_final2-matnr.

it_final3-txz01 = it_final2-txz01.

it_final3-menge = it_final2-menge.

it_final3-lfdat = it_final2-lfdat.

it_final3-frgdt = it_final2-frgdt.

it_final3-effwr = it_final2-effwr.

it_final3-waers = it_final2-waers.

it_final3-netwr = it_final2-netwr.

it_final3-ekgrp = it_final2-ekgrp.

it_final3-ebeln = it_final2-ebeln.

it_final3-ebelp = it_final2-ebelp.

it_final3-bedat = it_final2-bedat.

it_final3-matnrp = it_final2-matnrp.

it_final3-txz01p = it_final2-txz01p.

it_final3-mengep = it_final2-mengep.

it_final3-lifnr = it_final2-lifnr.

it_final3-name1 = it_final2-name1.

it_final3-chect = it_final2-chect.

it_final3-zaldt = it_final2-zaldt.

it_final3-rwbtr = it_final2-rwbtr.



IF it_final2-ebeln IS NOT INITIAL AND

it_final2-ebelp IS NOT INITIAL.



LOOP AT it_mseg INTO wa_mseg WHERE ebeln = it_final2-ebeln

AND ebelp = it_final2-ebelp.



it_final3-mblnr = wa_mseg-mblnr.

it_final3-mjahr = wa_mseg-mjahr.

it_final3-zeile = wa_mseg-zeile.

it_final3-bldat = wa_mseg-bldat.

it_final3-matnrm = wa_mseg-matnr.

it_final3-lgort = wa_mseg-lgort.

it_final3-erfmg = wa_mseg-erfmg.

it_final3-meins = wa_mseg-meins.

it_final3-dmbtr = wa_mseg-dmbtr.

it_final3-tblnr = wa_mseg-tblnr.

it_final3-tjahr = wa_mseg-tjahr.

it_final3-teile = wa_mseg-teile.

it_final3-tdate = wa_mseg-tdate.

it_final3-tatnr = wa_mseg-tatnr.

it_final3-tenge = wa_mseg-tenge.

it_final3-xblnr = wa_mseg-xblnr.

APPEND it_final3.

ENDLOOP.

ENDIF.



APPEND it_final3.

CLEAR it_final3.



ENDLOOP.



SORT it_final3 BY banfn bnfpo ebeln ebelp mblnr.

DELETE ADJACENT DUPLICATES FROM it_final3 COMPARING ALL FIELDS.



LOOP AT it_final3.





it_final-banfn = it_final3-banfn.

it_final-bnfpo = it_final3-bnfpo.

it_final-badat = it_final3-badat.

it_final-matnr = it_final3-matnr.

it_final-txz01 = it_final3-txz01.

it_final-menge = it_final3-menge.

it_final-lfdat = it_final3-lfdat.

it_final-frgdt = it_final3-frgdt.

it_final-effwr = it_final3-effwr.

it_final-waers = it_final3-waers.



IF it_final3-bedat NE '00000000' AND it_final3-badat NE '00000000'.



IF it_final3-bedat >= it_final3-badat.



CALL FUNCTION 'HR_HK_DIFF_BT_2_DATES'

EXPORTING

date1 = it_final3-bedat

date2 = it_final3-badat

output_format = '03'

IMPORTING

* YEARS =

* MONTHS =

days = it_final-days

* EXCEPTIONS

* INVALID_DATES_SPECIFIED = 1

* OTHERS = 2

.

IF sy-subrc <> 0.

* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.



ENDIF.



ENDIF.



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

it_final-netwr = it_final3-netwr.

it_final-ekgrp = it_final3-ekgrp.

it_final-ebeln = it_final3-ebeln.

it_final-ebelp = it_final3-ebelp.

it_final-bedat = it_final3-bedat.

it_final-matnrp = it_final3-matnrp.

it_final-txz01p = it_final3-txz01p.

it_final-mengep = it_final3-mengep.

it_final-lifnr = it_final3-lifnr.

it_final-name1 = it_final3-name1.

it_final-mblnr = it_final3-mblnr.

it_final-mjahr = it_final3-mjahr.

it_final-zeile = it_final3-zeile.

it_final-bldat = it_final3-bldat.

it_final-matnrm = it_final3-matnrm.

it_final-lgort = it_final3-lgort.

it_final-erfmg = it_final3-erfmg.

it_final-meins = it_final3-meins.

it_final-dmbtr = it_final3-dmbtr.

it_final-tblnr = it_final3-tblnr.

it_final-tjahr = it_final3-tjahr.

it_final-teile = it_final3-teile.

it_final-tdate = it_final3-tdate.

it_final-tatnr = it_final3-tatnr.

it_final-tenge = it_final3-tenge.

it_final-xblnr = it_final3-xblnr.

it_final-vinvoice = it_final3-vinvoice. " Vendor Invoice Number

it_final-vidate = it_final3-vidate. " Vendor Invoice Date

it_final-wrbtr = it_final3-wrbtr. "INVOICE Amount

it_final-chect = it_final3-chect.

it_final-zaldt = it_final3-zaldt.

it_final-rwbtr = it_final3-rwbtr.

IF it_final3-mblnr IS NOT INITIAL AND

it_final3-mjahr IS NOT INITIAL AND

it_final3-zeile IS NOT INITIAL.

CLEAR it_payr.

LOOP AT it_payr WHERE mblnr = it_final3-mblnr

AND mjahr = it_final3-mjahr

AND zeile = it_final3-zeile.



it_final-chect = it_payr-chect.

it_final-zaldt = it_payr-zaldt.

it_final-rwbtr = it_payr-rwbtr.



APPEND it_final.



ENDLOOP.

ENDIF.



APPEND it_final.

CLEAR it_final.

CLEAR it_final3.

ENDLOOP.



SORT it_final BY banfn bnfpo ebeln ebelp mblnr.

DELETE ADJACENT DUPLICATES FROM it_final COMPARING ALL FIELDS.



DELETE it_final WHERE banfn IS INITIAL.



IF it_final[] IS NOT INITIAL.

ENDIF.





CLEAR : gv_po_pr,

gv_pi_pr,

gv_po_pa,

gv_pi_pa.



SELECT matnr meins FROM mara INTO TABLE it_mara

FOR ALL ENTRIES IN it_final

WHERE matnr = it_final-matnr.



LOOP AT it_final.



READ TABLE it_mara INTO wa_mara WITH KEY matnr = it_final-matnr.



IF sy-subrc = 0.



it_final-uom = wa_mara-meins. " UNIT OF MEASURE



ENDIF.



IF it_final-zaldt NE '00000000' AND it_final-vidate NE '00000000'.



IF it_final-zaldt >= it_final-vidate.



CALL FUNCTION 'HR_HK_DIFF_BT_2_DATES'

EXPORTING

date1 = it_final-zaldt

date2 = it_final-vidate

output_format = '03'

IMPORTING

* YEARS =

* MONTHS =

days = it_final-lead

* EXCEPTIONS

* INVALID_DATES_SPECIFIED = 1

* OTHERS = 2

.

IF sy-subrc <> 0.

* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.



ENDIF.



ENDIF.

gv_po_pr = it_final-ebeln.

gv_pi_pr = it_final-ebelp.





IF gv_po_pa IS INITIAL AND

gv_pi_pa IS INITIAL.



gv_pend1 = it_final-menge - it_final-erfmg + it_final-tenge.





ELSEIF gv_po_pa = gv_po_pr AND

gv_pi_pa = gv_pi_pr.



gv_pend1 = it_final-menge - it_final-erfmg + it_final-tenge.



CLEAR it_final-effwr.



ELSE.



CLEAR gv_pend1.

gv_pend1 = it_final-menge - it_final-erfmg + it_final-tenge.

ENDIF.



gv_po_pa = gv_po_pr.

gv_pi_pa = gv_pi_pr.



it_final-pend = gv_pend1.



IF it_final-pend NE 0.



it_final-stat = 'PENDING'.



ENDIF.



IF it_final-pend LE 0.

it_final-stat = 'COMPLETED'.

ENDIF.





IF it_final-mengep IS NOT INITIAL AND it_final-effwr IS NOT INITIAL.



it_final-netpr = it_final-effwr / it_final-mengep.



ENDIF.

MODIFY it_final.

CLEAR it_final.

ENDLOOP.

ENDFORM. "prepare_final_data



END-OF-SELECTION.

Please help me give me the solution gurus...

By

Mathumitha.T