Skip to Content
0
Former Member
Mar 23, 2009 at 08:40 AM

performance concern

37 Views

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