Performance issue on the data validations from vbak and vbap tables

[former_member642890]

Hi,

I have the following below requirement:

SO Sales Item Rejection Reason

100 10 -

100 20 -

100 30 A

100 40 B

100 50 C

100 60 D

-

---

200 10 -

200 20 -

200 30 -

200 40 -

-

---

300 10 A

300 20 B

300 30 C

300 40 D

-

---

400 10 A

400 20 -

Note:

'-' means approved.

without '-' is rejected.

Requirement... if any orders with line items with both approved and rejected exist then we need to validate and delete the rejected line items keeping only the approved once in the final output.

So, in the above list, we need to get only Sales order with 200,300 and ( 100,400 with rejection reason as - ).

Can anyone help me with the logic that is required. I had tried implementing and the results were successful but there was a performance issue as it was hanging in the below mentioned logic and also their is a time out error.

Please review my code and suggest me if anything more is required or any other better logic can be implemented:

"

it_vbap_tmp[] = pc_it_vbap[].

SORT it_vbap_tmp BY vbeln posnr abgru.

LOOP AT pc_it_vbap INTO st_vbap.

READ TABLE it_vbap_tmp INTO st_vbap_tmp WITH KEY vbeln = st_vbap-vbeln

posnr = st_vbap-posnr

abgru = space

BINARY SEARCH.

IF sy-subrc EQ 0.

lv_cnt1 = 1 + lv_cnt1.

ELSE.

lv_cnt2 = 1 + lv_cnt2.

ENDIF.

IF ( lv_cnt1 >= 1 AND lv_cnt2 >= 1 ).

DELETE it_vbap_tmp WHERE vbeln = st_vbap-vbeln AND

abgru NE space.

CLEAR: lv_cnt1, lv_cnt2.

ENDIF.

AT END OF vbeln.

CLEAR: lv_cnt1, lv_cnt2.

ENDAT.

ENDLOOP."

Looking f

[yuri_ziryukin]

Hello Rohith,

here is my proposal, Not tested, please look if it works correcly.

Perfromance should be optimal. Make sure you declare st_vbap_previous identical to st_vbap, and lv_tabix, lv_tabix2 like sy-tabix.

SORT it_vbap_tmp BY vbeln posnr abgru.

read table it_vbap_tmp into st_vbap_previous index 1.

lv_tabix = 1.

LOOP AT it_vbap_tmp INTO st_vbap.

if st_vbap_previous-vbeln <> st_vbap-vbeln.

• document number changed.

• reset index

lv_tabix = sy-tabix. "position of the 1st item

st_vbap_previous = st_vbap.

continue.

endif.

if st_vbap_previous-vbeln = st_vbap-vbeln AND

st_vbap_previous-abgru = space AND

st_vbap_previous-abgru <> st_vbap-abgru.

• there is a change in the rejection reason from space to

• not space

• this item and following items with no space should be deleted

delete it_vbap_tmp.

continue.

endif.

if st_vbap_previous-vbeln = st_vbap-vbeln AND

st_vbap_previous-abgru <> space AND

st_vbap_previous-abgru <> st_vbap-abgru.

• there is a change in the rejection reason from not space to space

• all preceding items for this document should be deleted

lv_tabix2 = sy-tabix - 1. "do not delete the last item

delete it_vbap_tmp from lv_tabix to lv_tabix2.

• now only last, not rejected item left

endif.

st_vbap_previous = st_vbap.

endloop.

Cheers,

Yuri

[yuri_ziryukin]

And now, after thinking one more minute about it, even a simplier solution (works if your rejection reasons start with LETTERS):

SORT it_vbap_tmp BY vbeln abgru.
* after this sort, items w/o rejection reason should
* always come first in a loop, so we only have to take
* care of one case

read table it_vbap_tmp into st_vbap_previous index 1.
LOOP AT it_vbap_tmp INTO st_vbap.
  if st_vbap_previous-vbeln ne st_vbap-vbeln.
* document number changed.
    st_vbap_previous = st_vbap.
    continue.
  endif.
  if st_vbap_previous-vbeln = st_vbap-vbeln AND
     st_vbap_previous-abgru = space AND
     st_vbap_previous-abgru ne st_vbap-abgru.
* there is a change in the rejection reason from space to
* not space
* this item and following items with no space should be deleted
    delete it_vbap_tmp.
    continue.
  endif.
  st_vbap_previous = st_vbap.
endloop.

* final resort
SORT it_vbap_tmp BY vbeln posnr.

Edited by: Yuri Ziryukin on Sep 6, 2011 8:48 AM

[Former Member]

Hi,

you do not even need temporary internal table. Try to do it like this:

SORT pc_it_vbap BY vbeln posnr abgru.

  LOOP AT pc_it_vbap INTO st_vbap.
    gv_tabix = sy-tabix.
    AT NEW vbeln.
      gv_tabix_begin = gv_tabix.
    ENDAT.

    IF st_vbap-abgru EQ space.
      lv_cnt1 = 1 + lv_cnt1.
    ELSE.
      lv_cnt2 = 1 + lv_cnt2.
    ENDIF.

    AT END OF vbeln.
      gv_tabix_end = gv_tabix.
      IF ( lv_cnt1 >= 1 AND lv_cnt2 >= 1 ).
        DELETE it_vbap_tmp FROM gv_tabix_begin TO gv_tabix_end.
      ENDIF.
      CLEAR: lv_cnt1, lv_cnt2.
    ENDAT.
  ENDLOOP.

You can fill your counters without any additional read operations. Also you used delete on standard table by specifying where clause. For deleting from standard tables, using table index is much faster.

Regards

Adrian

[yuri_ziryukin]

Hi,
> 
> you do not even need temporary internal table. Try to do it like this:
> 
>   
SORT pc_it_vbap BY vbeln posnr abgru.
> 
>   LOOP AT pc_it_vbap INTO st_vbap.
>     gv_tabix = sy-tabix.
>     AT NEW vbeln.
>       gv_tabix_begin = gv_tabix.
>     ENDAT.
> 
>     IF st_vbap-abgru EQ space.
>       lv_cnt1 = 1 + lv_cnt1.
>     ELSE.
>       lv_cnt2 = 1 + lv_cnt2.
>     ENDIF.
> 
>     AT END OF vbeln.
>       gv_tabix_end = gv_tabix.
>       IF ( lv_cnt1 >= 1 AND lv_cnt2 >= 1 ).
>         DELETE it_vbap_tmp FROM gv_tabix_begin TO gv_tabix_end.
>       ENDIF.
>       CLEAR: lv_cnt1, lv_cnt2.
>     ENDAT.
>   ENDLOOP.
> 
> You can fill your counters without any additional read operations. Also you used delete on standard table by specifying where clause. For deleting from standard tables, using table index is much faster.
> 
> Regards
> Adrian

This coding is not functionally correct. It will completely delete documents with both rejected and non-rejected items. And this is not what is wanted.

[Former Member]

You are right, I missed that we do not want to delete all lines. So after correction, it could look like this:

SORT pc_it_vbap BY vbeln abgru.
 
LOOP AT pc_it_vbap INTO st_vbap.
    gv_tabix = sy-tabix.
 
    IF st_vbap-abgru EQ space.
      lv_cnt1 = 1 + lv_cnt1.
    ELSE.
      lv_cnt2 = 1 + lv_cnt2.
      IF gv_tabix_begin IS INITIAL. 
       gv_tabix_begin = gv_tabix.
      ENDIF.
    ENDIF.
 
    AT END OF vbeln.
      gv_tabix_end = gv_tabix.
      IF ( lv_cnt1 >= 1 AND lv_cnt2 >= 1 ).
        DELETE it_vbap_tmp FROM gv_tabix_begin TO gv_tabix_end.
      ENDIF.
      CLEAR: lv_cnt1, lv_cnt2, gv_tabix_begin.
    ENDAT.
  ENDLOOP.

The table is sorted also by ABGRU, so it should work.

[former_member585060]

Hi,

This is with few modifications of Jagrik Adrian code.

As he is used control break statements and deleting the entries from Same internal table with control break statements, the output may have some inconsistances.

Declare a FLAG field at last in the internal table PC_IT_VBAP.

DATA : lv_rflg TYPE c LENGTH 1,
       lv_flg  TYPE c LENGTH 1.

FIELD-SYMBOLS : <fl_vbap> TYPE st_vbap.


SORT pc_it_vbap BY vbeln abgru.

LOOP AT pc_it_vbap INTO st_vbap.

  IF st_vbap-abgru EQ space.
    lv_flg = 'X'.             " Sets flag for Non rejected items     
  ELSE.
    lv_rflg = 'X'.            " Sets flag for Rejected items
  ENDIF.

  AT END OF vbeln.
    
    IF lv_flg = 'X' AND lv_rflg = 'X'.   
* This will process only when an Order has both 
* rejected and Non rejected items      

      LOOP AT pc_it_vbap ASSIGNING <fl_vbap> WHERE
                                   vbeln = st_vbap-vbeln.
        <fl_vbap>-flag = 'X'.   " Sets last field in table to 'X'

      ENDLOOP.

    ENDIF.

    CLEAR: lv_rflg, lv_flg.
  ENDAT.
  CLEAR : st_vbap.
ENDLOOP.

* Delete all the records which have flag 'X', so we have only
* rejected or non rejected orders.
DELETE pc_it_vbap WHERE flag = 'X'.

Thanks & Regards

Bala Krishna P

[yuri_ziryukin]

Hi,
> 
>     This is with few modifications of Jagrik Adrian code.
> 
> As he is used control break statements and deleting the entries from Same internal table with control break statements, the output may have some inconsistances.
> 
> Thanks & Regards
> Bala Krishna P

Not exactly, he was deleting entries from ANOTHER table.

But... I just saw that he is deleting entries from another table using the tabix that he gets from the original untouched table. So the output will definitely be inconsistent. After first deletion tabix numbers will not fit.

[Former Member]

Hi Bala Krishna,

why do you think I can not delete entries within the loop? I can not see any problem with it. There could be a problem when using AT statement in loop with where clause. But if there is no where clause specified, it should work properly.

Adrian

[former_member585060]

Hi,

Jagrik Adrian, my apologies. I was thinking that you are deleting from the same internal table on which the control break statements were used.

Thanks & Regards

Bala Krishna.