Dear Experts,
The below query is causing a lot of performance concern. Kindly go through and let me know about the suitable modifications i can make.
select
vbeln
fkart
vkorg
vtweg
fkdat
sum( fkimg ) as fkimg
matnr
aubel
vstel
ktgrm
matkl
prctr
spart
SAKN1
from ZV_BSEG_VBRP_RK
into table it_sales
where
fkdat_i in s_fkdat and
vtweg in s_vtweg and
vkorg in s_vkorg and
fkart in so_fkart and
spart in s_spart and
vstel in s_werks and
matnr in s_matnr and
vbeln in s_vbeln and
fkimg ne '0' and
ktgrm in ('01','02','03','04','05','06','07','08','09','10','11','12') and
fksto ne 'X'
group by vbeln fkart vkorg vtweg fkdat matnr aubel vstel ktgrm matkl prctr spart SAKN1.
.
sort it_sales by vbeln matnr prctr.
if it_sales[] is not initial.
select vbeln fkdat GJAHR VKORG from vbrk into table it_vbeln for all entries in it_sales where vbeln = it_sales-vbeln.
select
belnr
shkzg
dmbtr
hkont
MATNR
prctr
from bsEG into table it_fin1
for all entries in it_vbeln
where belnr = it_vbeln-vbeln AND
BUKRS EQ IT_VBELN-VKORG and
hkont >= '0000400001' and hkont <= '0000400251'.
.
* hkont = it_sales-sakn1.
* group by belnr shkzg hkont prctr.
*
SORT IT_FIN BY hkont.
*delete it_fin where hkont >= '0000400001' and hkont <= '0000400251'.
SORT IT_FIN BY BELNR MATNR PRCTR.
IF it_fin1[] IS NOT INITIAL.
loop at it_fin1.
move-corresponding it_fin1 to it_fin.
collect it_fin.
append it_fin.
endloop.
loop at it_fin.
it_data_1-vbeln = it_fin-belnr.
it_data_1-matnr = it_fin-matnr.
it_data_1-prctr = it_fin-prctr.
*read table it_sales transporting no fields with key vbeln = it_data_1-vbeln matnr = it_Data_1-matnr prctr = it_data_1-prctr.
*if sy-subrc = 0.
*tabix = sy-tabix.
*FOR SALES INVOICE
read table it_sales with key vbeln = it_data_1-vbeln matnr = it_Data_1-matnr prctr = it_data_1-prctr.
it_data_1-aubel = it_sales-aubel.
it_data_1-vstel = it_sales-vstel.
it_data_1-ktgrm = it_sales-ktgrm.
it_data_1-matkl = it_sales-matkl.
it_data_1-fkart = it_sales-fkart.
it_data_1-vkorg = it_sales-vkorg.
it_data_1-vtweg = it_sales-vtweg.
it_data_1-fkdat = it_sales-fkdat.
it_data_1-spart = it_sales-spart.
if it_data_1-fkart = 'F2' or it_data_1-fkart = 'IV' or it_data_1-fkart = 'ZF2' or it_data_1-fkart = 'ZMIS' or it_data_1-fkart = 'ZSF2' or it_data_1-fkart = 'ZMF2'.
it_data_1-fkimg = it_sales-fkimg.
it_data_1-shkzg = it_fin-shkzg.
if it_data_1-shkzg = 'H'.
it_data_1-dmbtr_1 = it_data_1-dmbtr_1 + it_fin-dmbtr.
endif.
if it_data_1-shkzg = 'S'.
it_data_1-dmbtr_2 = it_data_1-dmbtr_2 + it_fin-dmbtr.
endif.
endif.
* H - Credit
* S - Debit
if it_data_1-fkart = 'G2' or it_data_1-fkart = 'IG' or it_data_1-fkart = 'RE' or it_data_1-fkart = 'ZCRE'.
if it_data_1-fkart = 'G2'.
it_data_1-cr_qty = '0'.
else.
it_data_1-cr_qty = it_sales-fkimg.
endif.
it_data_1-shkzg = it_fin-shkzg.
if it_data_1-shkzg = 'H'.
it_data_1-dmbtr_cr_1 = it_data_1-dmbtr_cr_1 + it_fin-dmbtr.
endif.
if it_data_1-shkzg = 'S'.
it_data_1-dmbtr_cr_2 = it_data_1-dmbtr_cr_2 + it_fin-dmbtr.
endif.
endif.
if it_data_1-fkart = 'L2'.
it_data_1-dr_qty = 0.
it_data_1-shkzg = it_fin-shkzg.
if it_data_1-shkzg = 'H'.
it_data_1-dmbtr_dr_1 = it_data_1-dmbtr_dr_1 + it_fin-dmbtr.
endif.
if it_data_1-shkzg = 'S'.
it_data_1-dmbtr_dr_2 = it_data_1-dmbtr_dr_2 + it_fin-dmbtr .
endif.
endif.
select single vtext into it_data_1-vtext from tvkmt where spras = 'EN' and ktgrm = it_data_1-ktgrm.
select single txt20 into it_data_1-gltxt from skat where spras = 'EN' and saknr = it_data_1-hkont and ktopl = '1000'.
select single maktg into it_data_1-maktg from makt where matnr = it_data_1-matnr.
select single vtext into it_data_1-division from tspat where spart = it_data_1-spart and spras = 'EN'.
append it_data_1.
clear it_data_1.
clear it_sales.
endloop.
sort it_data_1 by matnr vbeln.
if p_check ne 'X'.
loop at it_data_1.
concatenate it_data_1-matnr ' ' it_data_1-matkl ' ' it_data_1-ktgrm into it_final_1-count.
move: it_data_1-matnr to it_final_1-matnr,
it_data_1-matkl to it_final_1-matkl,
it_data_1-ktgrm to it_final_1-ktgrm,
it_data_1-vtext to it_final_1-vtext,
it_data_1-sakn1 to it_final_1-sakn1,
it_data_1-spart to it_final_1-spart,
it_data_1-gltxt to it_final_1-gltxt,
it_data_1-division to it_final_1-division,
it_data_1-prctr to it_final_1-prctr,
it_data_1-maktg to it_final_1-maktg,
it_data_1-fkimg to it_final_1-fkimg,
* it_data_1-dmbtr to it_final_1-dmbtr,
* it_data_1-dmbtr_cr to it_final_1-dmbtr_cr,
* it_data_1-dmbtr_dr to it_final_1-dmbtr_dr,
it_data_1-dr_qty to it_final_1-dr_qty,
it_data_1-cr_qty to it_final_1-cr_qty,
it_data_1-dmbtr_1 TO it_final_1-dmbtr_1,
it_data_1-dmbtr_2 TO it_final_1-dmbtr_2,
it_data_1-dmbtr_dr_1 TO it_final_1-dmbtr_dr_1,
it_data_1-dmbtr_dr_2 TO it_final_1-dmbtr_dr_2,
it_data_1-dmbtr_cr_1 TO it_final_1-dmbtr_cr_1,
it_data_1-dmbtr_cr_2 TO it_final_1-dmbtr_cr_2.
append it_final_1.
clear it_data_1.
endloop.
data: wa_matnr_1 like mara-matnr,
wa_matkl_1 like vbrp-matkl,
wa_ktgrm_1 like vbrp-ktgrm,
wa_hkont like bsis-hkont,
wa_gltxt like skat-txt20,
wa_hkont_dr like bsis-hkont,
wa_hkont_cr like bsis-hkont,
wa_maktg_1 like makt-maktg,
wa_vtext_1 like tvkmt-vtext,
wa_vtext_2 like tspat-vtext,
wa_spart like vbrk-spart,
wa_prctr like vbrp-prctr.
sort it_final_1 by matnr matkl ktgrm division prctr hkont.
loop at it_final_1.
wa_matnr_1 = it_final_1-matnr.
wa_matkl_1 = it_final_1-matkl.
wa_ktgrm_1 = it_final_1-ktgrm.
wa_vtext_1 = it_final_1-vtext.
wa_spart = it_final_1-spart.
wa_hkont = it_final_1-sakn1.
wa_gltxt = it_final_1-gltxt.
wa_vtext_2 = it_final_1-division.
wa_prctr = it_final_1-prctr.
wa_maktg_1 = it_final_1-maktg.
* wa_hkont_cr = it_final_1-hkont_cr.
at end of count.
sum.
* it_gl-hkont_dr = it_final_1-hkont_dr.
* it_gl-hkont_cr = it_final_1-hkont_cr.
it_gl-fkimg = it_final_1-fkimg.
it_gl-dr_qty = it_final_1-dr_qty.
it_gl-cr_qty = it_final_1-cr_qty.
it_gl-dmbtr_1 = it_final_1-dmbtr_1.
it_gl-dmbtr_2 = it_final_1-dmbtr_2.
it_gl-dmbtr = it_final_1-dmbtr_1 - it_final_1-dmbtr_2.
*it_gl-dmbtr = it_final_1-dmbtr.
*
it_gl-dmbtr_dr_1 = it_final_1-dmbtr_dr_1.
it_gl-dmbtr_dr_2 = it_final_1-dmbtr_dr_2.
it_gl-dmbtr_dr = it_final_1-dmbtr_dr_1 - it_final_1-dmbtr_dr_2.
*it_gl-dmbtr_dr = it_final_1-dmbtr_dr.
it_gl-dmbtr_cr_1 = it_final_1-dmbtr_cr_1.
it_gl-dmbtr_cr_2 = it_final_1-dmbtr_cr_2.
it_gl-dmbtr_cr = it_final_1-dmbtr_cr_1 - it_final_1-dmbtr_cr_2.
*it_gl-dmbtr_cr = it_final_1-dmbtr_cr.
it_gl-gltxt = wa_gltxt.
it_gl-matnr = wa_matnr_1.
it_gl-matkl = wa_matkl_1.
it_gl-vtext = wa_vtext_1.
it_gl-spart = wa_spart.
it_gl-division = wa_vtext_2.
it_gl-prctr = wa_prctr.
it_gl-hkont = wa_hkont.
it_gl-maktg = wa_maktg_1.
it_gl-netqty = ( it_gl-fkimg + it_gl-dr_qty ) - ( it_gl-cr_qty ).
it_gl-netval = ( it_gl-dmbtr + it_gl-dmbtr_dr ) - ( it_gl-dmbtr_cr ).
append it_gl.
clear wa_matnr_1.
clear wa_vtext_1.
clear wa_matkl_1.
clear wa_ktgrm_1.
clear wa_hkont.
clear wa_hkont_dr.
clear wa_hkont_cr.
endat.
clear it_final_1.
clear it_gl.
endloop.
endif.
ENDIF.
endif.
Do provide your valuable suggestions
Regards,
Jitesh
Use meaningful subject for your Future questions
Edited by: Vijay Babu Dudla on Mar 23, 2009 6:20 AM