Passing the inputs of Vbrp in standard table ( VBFA : Sales Document Flow ) giving dump

[former_member220286]

vbfa-1.jpgvbfa-3.jpgvbfa-4.jpg

Dear All ,

Actually I have a ZReport related to the Sales .

In that particular report I am doing some optimizations so that the report can be executed for a period of 1 Year .

Before optimization the report is giving dump if executed for a period of 1 year ( 01.04.2014 to 31.03.2015) for all plants , all distribution channels and all divisions .

so with above inputs the report is giving dump .

Now I have done some optimizations in this zreport so that the run time can be improved .

What I have done is that I have replaced the select single queries within the loop with the for all entries select outside the loop.

So all queries are working fine except the select query on VBFA .It is talking a lot of time in execution then finally resulting in a dump .

Below are my select queries :-

SELECT VBELN
FKART
WAERK
VKORG
VTWEG
KNUMV
FKDAT
NETWR
KUNAG
VTWEG
XBLNR FROM VBRK INTO TABLE IT_VBRK WHERE

VBELN IN S_VBELN “ invoice no.

AND FKART IN S_FKART " Invoice type

AND FKART NOT IN ('ZG2','ZL2','ZF5','ZRE','F8')

AND VKORG = P_VKORG " sales organisation

AND VTWEG IN P_VTWEG " distribution channel

AND FKDAT IN S_DATE " date of invoice

AND KUNAG IN S_KUNAG AND

KUNRG IN S_KUNRG "Ship To Party

AND SFAKN EQ SPACE " cancelled invoices have refrence doc no. here
AND FKSTO NE 'X' " to exclude cancelled invoices

AND REGIO IN S_BLAND AND MRNKZ NE 'X'.

IF SY-SUBRC NE 0.

MESSAGE 'No Data Found' TYPE 'I'.LEAVE LIST-PROCESSING.

ENDIF.

SELECT VBELN
POSNR
FKIMG
VRKME
VGBEL
AUBEL
MATNR
ARKTX
CHARG
WERKS
KONDM
VKAUS FROM VBRP INTO TABLE IT_VBRP FOR ALL ENTRIES IN IT_VBRK WHERE VBELN = IT_VBRK-VBELN AND MATNR IN S_MAT AND SPART IN P_SPART AND CHARG IN S_CHARG AND WERKS IN P_WERKS AND KONDM IN S_MATNR .

IF SY-SUBRC NE 0.

MESSAGE 'No Data Found' TYPE 'I'.LEAVE LIST-PROCESSING.

ENDIF.

SELECT

VBELN

PARVW

KUNNR

ADRNR

FROM VBPA INTO TABLE IT_VBPA FOR ALL ENTRIES IN IT_VBRK WHERE

VBELN = IT_VBRK-VBELN AND PARVW IN ('WE','RE','RG').


SELECT

KUNNR

NAME1

NAME2

STCD3

REGIO

FROM KNA1 INTO TABLE IT_KNA1_TEMP FOR ALL ENTRIES IN IT_VBPA WHERE

KUNNR = IT_VBPA-KUNNR.



SELECT

ADDRNUMBER

STR_SUPPL1

STR_SUPPL2

STREET

CITY1

CITY2 FROM ADRC INTO TABLE IT_ADRC FOR ALL ENTRIES IN IT_VBPA WHERE ADDRNUMBER = IT_VBPA-ADRNR.

IF IT_VBRP[] IS NOT INITIAL .

Select

MATNR

WERKS

STEUC

from MARC into table IT_MARC for all entries in IT_VBRP WHERE MATNR = IT_VBRP-MATNR AND WERKS = IT_VBRP-WERKS .

select

RDOC

EXPIND

STATUS

from J_1IEXCHDR into table IT_J_1IEXCHDR FOR ALL ENTRIES IN IT_VBRP WHERE RDOC = IT_VBRP-VBELN and STATUS = 'C'.

SELECT

VBELV

VBELN

POSNN

VBTYP_N

VBTYP_V

FROM VBFA INTO TABLE IT_VBFA FOR ALL ENTRIES IN IT_VBRP WHERE VBELN = IT_VBRP-AUBEL AND
POSNN = IT_VBRP-POSNR AND

VBTYP_N = 'C' .

delete IT_VBFA where VBTYP_V <> 'G'.

ENDIF.

So I have replaced all the select single queries with for all entries in .

My issue is that only this query which is highlighted in bold is taking a lot of time and hence resulting in a dump . Time limit exceeded .

Please guide me how to fix this issue .

Secondly , If I manually check why the select query to VBFA is giving dump by entering the values which is obtained in the internal table IT_VBRP TO Standard table VBFA in the SE11 VBFA table .

Then also the standard table is also giving dump .

So I am unable to find what exactly the problem is .

vbfa-1.jpgvbfa-3.jpg

The standard table is giving the dump . I have attached the dump logs . I am unable to understand that why the standard table : VBFA is giving the dump .

Regardszsales-report-1.jpgzsales-report-2.jpg

Deep

[former_member539238]

Don't change the types of the variables compared to the tables. Here some variable length should be lesser than the value that is provided it is given at the end of the image. Also you could join both vbrk and vbrp tables to get your data. The sample code is given below.

You could also put a breakpoint to your query and check what's the error message.

  TYPES:BEGIN OF ty_vbrp ,
          "Add VBRK needed here
          vbeln TYPE vbrp-vbeln,
          posnr TYPE vbrp-posnr,
          fkimg TYPE vbrp-fkimg,
          vrkme TYPE vbrp-vrkme,
          vgbel TYPE vbrp-vgbel,
          aubel TYPE vbrp-aubel,
          matnr TYPE vbrp-matnr,
          arktx TYPE vbrp-arktx,
          charg TYPE vbrp-charg,
          werks TYPE vbrp-werks,
          kondm TYPE vbrp-kondm,
          vkaus TYPE vbrp-vkaus,
        END OF ty_vbrp.

  DATA: lt_vbrp TYPE TABLE OF ty_vbrp.

  SELECT
  "Add vbrk needed here
  vbrp~vbeln
  vbrp~posnr
  vbrp~fkimg
  vbrp~vrkme
  vbrp~vgbel
  vbrp~aubel
  vbrp~matnr
  vbrp~arktx
  vbrp~charg
  vbrp~werks
  vbrp~kondm
  vbrp~vkaus
  FROM vbrp
  JOIN vbrk
  ON vbrp~vbeln = vbrk~vbeln
  INTO TABLE lt_vbrp
  WHERE vbrp~matnr IN s_mat
  AND vbrp~spart IN p_spart
  AND vbrp~charg IN s_charg
  AND vbrp~werks IN p_werks
  AND vbrp~kondm IN s_matnr .

[former_member220286]

zsdb-gst-4.jpgzsdb-gst-5.jpgDear Sooraj D ,

I am asking that my select query on VBFA is giving dump ( taking a lot of time for execution ) .Also if I manually passed the values from it_vbrp table (downloading the excel from the debugger ) then passing the values in the standard table VBFA then the standard table is also giving dump which I have attached in the previous post .

I have attached the screenshots of the error .

SELECT VBELN POSNR FKIMG VRKME VGBEL AUBEL MATNR ARKTX CHARG WERKS KONDM VKAUS FROM VBRP INTO TABLE IT_VBRP FOR ALL ENTRIES IN IT_VBRK WHERE VBELN = IT_VBRK-VBELN AND MATNR IN S_MAT AND SPART IN P_SPART AND CHARG IN S_CHARG AND WERKS IN P_WERKS AND KONDM IN S_MATNR .

IF SY-SUBRC NE 0.

MESSAGE 'No Data Found' TYPE 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

SELECT VBELV VBELN POSNN VBTYP_N VBTYP_V FROM VBFA INTO TABLE IT_VBFA FOR ALL ENTRIES IN IT_VBRP WHERE VBELN = IT_VBRP-AUBEL AND POSNN = IT_VBRP-POSNR AND VBTYP_N = 'C' .

delete IT_VBFA where VBTYP_V <> 'G'.

Please guide how to resolve this problem .Any ideas or work around .

Regards

Deep

[former_member539238]

You can try to use 'package size' to handle manageable size of data at a time.

You might find related info here How to find out the optimum Package size for the Select query ?

An example below,

SELECT 
VBELN 
POSNR 
FKIMG 
VRKME 
VGBEL 
AUBEL 
MATNR 
ARKTX 
CHARG 
WERKS 
KONDM 
VKAUS 
FROM VBRP 
INTO TABLE IT_VBRP PACKAGE SIZE 100
FOR ALL ENTRIES IN IT_VBRK
WHERE VBELN = IT_VBRK-VBELN  
AND MATNR IN S_MAT 
AND SPART IN P_SPART 
AND CHARG IN S_CHARG
AND WERKS IN P_WERKS 
AND KONDM IN S_MATNR .
"<< Write the data manipulation here
ENDSELECT.

[former_member220286]

Dear All,

Actually I have 2 requirements :-

1) One is that Query to VBFA is taking a lot of time in execution hence going for dump .

2) I want to avoid the loop of It_konv table because it is having a large number of entries .Also There are some calculations being done inside that loop .

First issue I am having the below query : -

SELECT VBELV VBELN POSNN VBTYP_N VBTYP_V FROM VBFA INTO TABLE IT_VBFA FOR ALL ENTRIES IN IT_VBRP WHERE VBELN = IT_VBRP-AUBEL AND POSNN = IT_VBRP-POSNR AND

VBTYP_N = 'C' AND VBTYP_V = 'G'.

The above query is taking a lot of time in execution and hence going for dump .

Secondly to avoid that loop of IT_KONV table I need to combine three queries into one .

As some one suggested : - " Try changing the data selection to use a JOIN select involving these three tables, so that you have all relevant data in one single internal table " .

Please guide me how to make the below three queries in to one .

-----------------------------

First Query

-------------------------------

SELECT VBELN
FKART
WAERK
VKORG
VTWEG
KNUMV
FKDAT
NETWR
KUNAG
VTWEG
XBLNR FROM VBRK INTO TABLE IT_VBRK

WHERE VBELN IN S_VBELN “ invoice no.

AND FKART IN S_FKART " Invoice type

AND FKART NOT IN ('ZG2','ZL2','ZF5','ZRE','F8')

AND VKORG = P_VKORG " sales organisation

AND VTWEG IN P_VTWEG " distribution channel

AND FKDAT IN S_DATE " date of invoice

AND KUNAG IN S_KUNAG AND

KUNRG IN S_KUNRG "Ship To Party

AND SFAKN EQ SPACE " cancelled invoices have refrence doc no. here

AND FKSTO NE 'X' " to exclude cancelled invoices

AND REGIO IN S_BLAND AND MRNKZ NE 'X'.

IF SY-SUBRC NE 0.

MESSAGE 'No Data Found' TYPE 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

------------------------------------

second query

-------------------------------------

SELECT VBELN
POSNR
FKIMG
VRKME
VGBEL
AUBEL
MATNR
ARKTX
CHARG
WERKS
KONDM
VKAUSFROM VBRPINTO TABLE IT_VBRPFOR ALL ENTRIES IN IT_VBRKWHERE VBELN = IT_VBRK-VBELN AND MATNR IN S_MATAND SPART IN P_SPART AND CHARG IN S_CHARG AND WERKS IN P_WERKS AND KONDM IN S_MATNR .

IF SY-SUBRC NE 0.

MESSAGE 'No Data Found' TYPE 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

-----------------------------------

Third Query

-----------------------------------

SELECT KNUMV
KPOSN
STUNR
ZAEHK
KSCHL
KBETR
KWERT
KINAK FROM KONV INTO CORRESPONDING FIELDS OF TABLE IT_KONV FOR ALL ENTRIES IN IT_VBRK WHERE KNUMV = IT_VBRK-KNUMV.

DELETE IT_KONV WHERE KINAK = 'X'.

delete it_konv where kinak = 'Y'.
SORT IT_KONV BY KNUMV KPOSN.

How can the data be selected through a join for all these three tables : VBRK , VBRP and KONV table .

JOIN select as you are suggesting me . How the above three queries can be combined .

Also I am doing some calculations inside the below loop of IT_KONV table . How can they be done if all three tables are combined and loops are avoided : -

LOOPAT it_vbrp INTO wa_vbrp .READTABLE it_vbrk INTO wa_vbrk WITHKEY vbeln = wa_vbrp-vbeln 

BINARYSEARCH.

LOOP AT it_konv INTO wa_konv WHERE knumv = wa_vbrk-knumv

AND kposn = wa_vbrp-posnr.

IF wa_vbrk-waerk = 'USD'.

                   wa_konv-kwert = wa_konv-kwert /100.ENDIF.IF( kschl ='ZF00'OR kschl ='ZF02'OR kschl ='ZF03'OR 
         kschl ='ZF04'OR kschl ='ZF06'OR kschl ='ZF07'OR 
         kschl ='ZFCD'OR kschl ='ZFI2'OR kschl ='ZFIM'OR 
         kschl ='ZIFI').
      tafkwert        = tafkwert + wa_konv-kwert.
      wa_final-fkwert = wa_final-fkwert + wa_konv-kwert.
      wa_final-netwr  = wa_final-netwr + wa_konv-kwert.ELSEIF( kschl ='ZF01'OR kschl ='ZF05').
      tlfkwert         = tlfkwert + wa_konv-kwert."LESS FREIGHT
      tafkwert         = tafkwert + wa_konv-kwert.
      wa_final-fkwert  = wa_final-fkwert + wa_konv-kwert.
      wa_final-lfkwert = wa_konv-kwert."LESS FREIGHT
      wa_final-netwr   = wa_final-netwr + wa_konv-kwert.ENDIF.ENDLOOP.ENDLOOP.

Please suggest some ideas or workaround to resolve the above two problems .

Regards

Deep

[former_member539238]

HI,

I think you misunderstood me. I said you can join the vbrk and vbrp table to get data in single table. and for getting lot of data, split it into manageable size of data as mentioned above by me. So that even if there is delay it wont give dump.

From what I understood, you are calculating the invoice amount from the tables. For that you can join the vbrk and vbrp to get the data as I mentioned above(with package size mentioned) and then inside there you are allowed to do konv selection.

For KONV table you need to add based on pricing condition KSCHL otherwise there will be random data if you add directly if pricing condition is changed(Price, Tax, Discount, Surcharge, Total and Amount with tax etc everything is in KONV). So you can use read for pricing conditions.

Or you can try to loop at konv and read the relevant data for it if you want to avoid nested loop that much. I would do it this way.

select data from vbrk join vbrp.
if data found {
            <Or any other data needed>
             select data from konv for all entries from vbrk-vbrp
}
loop at konv{
       read relevant vbrk-vbrp data in main table and assign to field symbol.
      if not found{
       Append new row and assign to field symbol.   
      }
      <Do other jobs needed for main data>
}

And is this the same question How to avoid the loop inside loop

As Thomas Zloch said in the above question please join the tables. I already gave an example please do some research.

[Jelena]

Sorry, I'm honestly not able to understand anything from the question or how the short dump screenshots even relate to the code mentioned.

The first part doesn't make any sense as there can't be VBRP without VBRK, so just use JOIN instead of FAE. Most likely VBFA can be JOINed as well, if only you could explain in plain language what you're trying to achieve.

In SE11 we don't "pass" anything and don't "manually enter values", you lost me here completely, sorry!
Please update the question so that it's more clear.

[former_member220286]

Dear Jelena Perfiljeva ,

I have updated the question.Also attached the dump logs . Please check and guide .

Regards

Deep

[matt]

I have removed your new question. You can update this question instead. Please do not open further questions - I've even opened up the other question I closed earlier.