Solved: Order Aging Report (AUFK-AUFNR , USER1)

Former Member · ‎2013 Dec 05

Hello Experts,

I'm working on Order Aging Report: A Report of Orders that have been inactive for greater than 90 days.

I need to check the Posting Date of the Order is greater than the Start Date.

How can I do this?

If I read BSEG / BKPF Tables, it is taking a lot of time. Performance is getting affected.

Here's my code:


* Validation for Posting Date
* Display the orders that do not have had any activity during the past 90 days from wdate
  LOOP AT aaufk.
    SELECT SINGLE * FROM aufk WHERE aufnr = aaufk-aufnr.
    IF sy-subrc EQ 0.
      SELECT SINGLE belnr FROM bseg INTO w_belnr
        WHERE bukrs EQ aufk-bukrs
        AND   gjahr EQ p_gjahr
        AND   aufnr EQ aaufk-aufnr
        AND   prctr EQ aufk-prctr.
      IF sy-subrc EQ 0.
        SELECT SINGLE budat INTO w_budat
          FROM bkpf
          WHERE bukrs EQ aufk-bukrs
          AND   belnr EQ w_belnr
          AND   gjahr EQ p_gjahr.
        IF sy-subrc EQ 0.
          IF w_budat GT strt-date.
* If Posting Date is GT Start Date, then delete the entry
* as the order had an activity in the last 90 days
            DELETE aaufk WHERE user1 EQ aufk-user1
                         AND   aufnr EQ aufk-aufnr.
          ENDIF.
        ENDIF.
      ENDIF.
    ENDIF.
  ENDLOOP.

Since this is inside the LOOP, performance is really bad.

I need to delete the entries from an Internal Table where the Posting Date > Start Date (if it is within 90 days).

Which Table can I use to get the Posting Date of a Particular Order (AUFK-AUFNR)?

Please advise.

Thanks & Regards,
Sowmya

Former Member · ‎2013 Dec 13

Dear All,

Thank you all for your suggesitons / advise.

I used COEP / COBK Tables and it worked fine without any performance issues.

Here's my code:


   REFRESH:
      t_coep,
      t_cobk.
 
    SELECT kokrs
           belnr
           perio
           wtgbtr
           objnr
           gjahr
           kstar
     FROM  coep
     INTO  TABLE t_coep
     WHERE kokrs = aufk-kokrs
     AND   objnr = aufk-objnr.
    IF sy-subrc EQ 0.
      SORT t_coep BY belnr objnr.
      SELECT kokrs
             belnr
             gjahr
             budat
       FROM  cobk
       INTO  TABLE t_cobk
       FOR ALL ENTRIES IN t_coep
       WHERE kokrs = t_coep-kokrs
       AND   belnr = t_coep-belnr
       AND   gjahr = t_coep-gjahr.
      IF sy-subrc EQ 0.
        SORT t_cobk BY kokrs belnr.
      ENDIF.
    ENDIF.
* Validation for Posting Date
* Display the orders that do not have had any activity during the past 90 days from wdate
    CLEAR:
      wa_coep,
      wa_cobk.
    LOOP AT t_coep INTO wa_coep WHERE kokrs EQ aufk-kokrs
                                AND ( perio GE w_f_period
                                AND   perio LE w_t_period )
                                AND   objnr EQ aufk-objnr
                                AND   gjahr EQ p_gjahr.
      CLEAR wa_cobk.
      READ TABLE t_cobk INTO wa_cobk WITH KEY
                         kokrs = wa_coep-kokrs
                         belnr = wa_coep-belnr
                         gjahr = wa_coep-gjahr.
      IF sy-subrc EQ 0.
        IF wa_cobk-budat BETWEEN strt-date AND wdate.
          total = total + wa_coep-wtgbtr.
          CLEAR t_90days_aufk.
          t_90days_aufk-user1 = aufk-user1.
          APPEND t_90days_aufk.
        ENDIF.
      ENDIF.
    ENDLOOP.

Thanks & Regards,
Sowmya

Former Member · ‎2013 Dec 05

It is taking time even in SE16 of BSEG as we providing only 4 input parameters.

-Sowmya

Former Member · ‎2013 Dec 05

Hi Sowmya,

from where you are getting the entries for table aaufk.(
LOOP AT aaufk.) Idea is that need to avoid the select statement with in the loop.

Thanks,

Kiran

Former Member · ‎2013 Dec 05

One Option would be to take the Select statement completely out of the loop by creating leaner internal tables with key fields of the aufk / Bkpf / Bseg and do a read statement.

For the Existing code,I believe the BKPF and BSEG can be combined in an inner join Or Still

the Select single * from BKPF , we can change to Select Count(*) from BKPF and add the filter to the select 'and budat > strt_date' since we can just check and avoid fetching the data

Hope it Helps.

Former Member · ‎2013 Dec 05

I even tried to write the SELECT statement outside, and just used READ.

Performance is still bad.

Is there any other FM's or Tables that can be used to compare the Posting Date of an Order or whether it is Inactive?

Please advise.

Thanks & Regards,
Sowmya

nabheetscn · ‎2013 Dec 05

Hi Sowmay

Can you please execute your report via SAT tcode and check where the performance issue lies

Nabheet

Former Member · ‎2013 Dec 05

The report ends in TIME_OUT Error because of BSEG SELECT statement.

It gives the same TIME_OUT Dump even in SE16 of BSEG.

-Sowmya

nabheetscn · ‎2013 Dec 05

First of all please move all select out of loop. Then you can use for all enteries/Join etc to fetch the data

former_member339717 · ‎2013 Dec 05

Dear Swmya sreeram,

try this,

DATA: BEGIN OF it_bkpf OCCURS 0,

bukrs type bkpf-bukrs,

belnr type bkpf-belnr,

gjahr type bkpf-gjahr,

budat type bkpf-budat,

END OF it_bkpf.

DATA: BEGIN OF it_aufk OCCURS 0,

bukrs type aufk-bukrs,

aufnr type aufk-aufnr,

prctr type aufk-prctr,

END OF it_aufk.

DATA: BEGIN OF IT_BSEG,

bukrs type bSEG-bukrs,

belnr type bSEG-belnr,

gjahr type bSEG-gjahr,

PRCTR TYPE BSEG-PRCTR,

AUFNR TYPE BSEG-AUFNR,

END OF IT_BSEG.

IF AAUFK[] IS NOT INITIAL.

SELECT bukrs aufnr prctr INTO TABLE it_aufk FROM aufk

FOR ALL ENTRIES IN aaufk

WHERE aufnr = aaufk-aufnr.

SELECT BUKRS BELNR GJAHR BUDAT FROM BKPF

INTO TABLE IT_BKPF

FOR ALL ENTRIES IN AAUFK

WHERE GJAHR = P_GJAHR

AND BUKRS = AAUFK-BUKRS.

IF IT_BKPF[] IS NOT INITIAL.

SELECT BUKRS BELNR GJAHR PRCTR AUFNR INTO TABLE IT_BSEG

FROM BSEG

FOR ALL ENTRIES IN IT_BKPF

WHERE BELNR = IT_BKPF-BELNR

AND GJAHR = IT_BKPF-GJAHR

AND BUKRS = IT_BKPF-BUKRS.

ENDIF.

LOOP AT aaufk.

SELECT SINGLE * FROM aufk WHERE aufnr = aaufk-aufnr.

IF sy-subrc EQ 0.

READ TABLE it_bseg with key bukrs = aufk-bukrs gjahr = p_gjahr aufnr = aaufk-aufnr prctr = aufk-prctr.

IF sy-subrc EQ 0.

READ TABLE IT_BKPF WITH KEY BKRS = IT_BSEG-BUKRS GJAHR = IT_BSEG-GJAHR BELNR = IT_BSEG-BELNR.

IF sy-subrc EQ 0.

IF IT_BSEG-BUDAT GT strt-date.

* If Posting Date is GT Start Date, then delete the entry

* as the order had an activity in the last 90 days

DELETE aaufk WHERE user1 EQ aufk-user1

AND aufnr EQ aufk-aufnr.

ENDIF.

ENDLOOP.

Former Member · ‎2013 Dec 05

I already tried this.

Performance is not good. Slow.

Any other suggestion to avoid using BSEG Table?

-Sowmya

former_member339717 · ‎2013 Dec 05

sowmya,

please check this link this will help you.

https://scn.sap.com/thread/187065

Regards,

Former Member · ‎2013 Dec 05

Zahid,

Was not of any help

AUFM is not used in my report.

former_member339717 · ‎2013 Dec 05

Try this please,

data: it_aufm type STANDARD TABLE OF aufm.

DATA: I_AWTYP LIKE ACCHD-AWTYP,

I_AWREF LIKE ACCHD-AWREF,

I_AWORG LIKE ACCHD-AWORG,

I_AWSYS LIKE ACCHD-AWSYS,

I_AWTYP_INCL LIKE ACCHD-AWTYP,

I_AWTYP_EXCL LIKE ACCHD-AWTYP,

I_BUKRS LIKE ACCIT-BUKRS,

I_VALUTYP LIKE ACCCR-VALUTYP,

X_DIALOG LIKE BOOLE.

DATA: T_DOCUMENTS LIKE ACC_DOC OCCURS 0 WITH HEADER LINE.

if aaufk[] is NOT INITIAL.

select * from aufm into table it_aufm FOR ALL ENTRIES IN aaukf

where aufnr = aaufk-aufnr.

endif.

LOOP AT aaufk.

SELECT SINGLE * FROM aufk WHERE aufnr = aaufk-aufnr.

IF sy-subrc EQ 0.

READ TABLE IT_AUFM WITH KEY AUFNR = AAUFK-AUFNR.

IF sy-subrc EQ 0.

MOVE: IT_AUFM-MBLNR TO I_AWORG,

IT_AUFM-MJAHR TO I_AWREF,

AUFK-BUKRS TO I_BUKRS .

CALL FUNCTION 'AC_DOCUMENT_RECORD'

EXPORTING

I_AWTYP = 'MKPF'

I_AWREF = I_AWREF

I_AWORG = I_AWORG

* I_AWSYS = ' '

* I_AWTYP_INCL = ' '

* I_AWTYP_EXCL = ' '

I_BUKRS = I_BUKRS

I_VALUTYP = '0'

X_DIALOG = ' '

TABLES

T_DOCUMENTS = t_documents

EXCEPTIONS

NO_REFERENCE = 1

NO_DOCUMENT = 2

OTHERS = 3

.

IF SY-SUBRC <> 0.

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

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

else.

DELETE t_documents where awtyp ne 'BKPF'.

ENDIF.

SELECT BUDAT INTO W_BUDAT FROM BKPF WHERE BELNR = T_DOCUMENT-BELNR AND BUKRS = AUFK-BUKRS AND GJAHR = I_AWORG.

IF sy-subrc EQ 0.

IF W_BUDAT GT strt-date.

* If Posting Date is GT Start Date, then delete the entry

* as the order had an activity in the last 90 days

DELETE aaufk WHERE user1 EQ aufk-user1

AND aufnr EQ aufk-aufnr.

ENDIF.

ENDLOOP.

regards,

former_member192023 · ‎2013 Dec 05

Hi Sowmya,

Try to check those tables.

BSAS+ BSIS+ BSAK+ BSIK+ BSAD+ BSID = BSEG

BSAS + BSIS ==>all GL documents Account

BSAK + BSIK ==>all AP documents Vendor

BSAD + BSID ==>all AR documents Customer

Thanks.

former_member192023 · ‎2013 Dec 05

Table：BSEG

Company Code BUKRS

Document Number BELNR

Fiscal Year GJAHR

Line item BUZEI

Order AURNR

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

Table:BSAS

Company Code BUKRS

Fiscal Year GJAHR

Document Number BELNR

Line item BUZEI

Order AURNR

G/L HKONT

Clearing AUGDT

Clrng doc. AUGBL

Assignment ZUONR

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

Table:BSIS

Company Code BUKRS

G/L HKONT

Fiscal Year GJAHR

Document Number BELNR

Line item BUZEI

Order AURNR

Clearing AUGDT

Clrng doc. AUGBL

Assignment ZUONR

atul_mohanty · ‎2013 Dec 05

Hi - Check for the index table with the AUFNR if any . like BSID , BSIK etc.

Regards,

Atul Mohanty

Former Member · ‎2013 Dec 05

Thanks Feiyun Wu.

But BSIS Table is also taking longer time and ending up in TIME_OUT Error.

Please suggest an alternate solution.

-Sowmya

Former Member · ‎2013 Dec 05

hi,

Use inner join so it will improve performance.

Former Member · ‎2013 Dec 05

Any suggestions? Please help.

I tried to search CO Tables also, but there's no AUFNR in it.

Kindly advise on this.

-Sowmya

venkateswaran_k · ‎2013 Dec 05

Hi Sowmya

No need to go to BSEG tables

Read data from BSIK and BSAK tables..

Regards,

Venkat

Former Member · ‎2013 Dec 05

Can someone please let me know how is it done in KOB1?

I want to implement the same logic in my code.

KOB1 accepts Order number and posting date range, then displays whether it had any postings during that period.

COBK-BUDAT is the field used here. But this doesn't have any AUFNR.

Please advise.

Thanks & Regards,
Sowmya

nabheetscn · ‎2013 Dec 05

If you debug this report it is using function module K_LINE_ITEMS_SELECT. Put abbreak point inside it and check how it is calling

Nabheet

Former Member · ‎2013 Dec 05

Dear Experts,

Has anyone worked on this?

Please provide your suggestions.

Thanks & Regards,

Sowmya

former_member192023 · ‎2013 Dec 06

Hi Sowmya,

Some advice.

1. Better not to fetch data from table in the loop,

2. Always free the memory

3. Before read table sort it then use binary search

4. Only select columns from table what you actually need

5. Use range table in some where condition.

Try below source code.

Thanks.

Feiyun

TYPES:
BEGIN OF ty_aufk,
user1 TYPE aufk-user1,
aufnr TYPE aufk-aufnr,
bukrs TYPE aufk-bukrs,
prctr TYPE aufk-prctr,
END OF ty_aufk,
BEGIN OF ty_bkpf,
budat TYPE bkpf-budat,
bukrs TYPE bkpf-bukrs,
belnr TYPE bkpf-belnr,
gjahr TYPE bkpf-gjahr,
END OF ty_bkpf,

BEGIN OF ty_bsis,
belnr TYPE bsis-belnr,
bukrs TYPE bsis-bukrs,
gjahr TYPE bsis-gjahr,
aufnr TYPE bsis-aufnr,
prctr TYPE bsis-prctr,
END OF ty_bsis.

DATA:
gt_aaufk TYPE STANDARD TABLE OF aaufk.
PARAMETERS p_gjahr TYPE bseg-gjahr.
PARAMETERS p_date_s TYPE sy-datum.

START-OF-SELECTION.

DATA:
wa_aaufk TYPE aaufk,
lt_bsis TYPE STANDARD TABLE OF ty_bsis,
lt_bsis_tmp TYPE STANDARD TABLE OF ty_bsis,
wa_aufk TYPE ty_aufk,
wa_bsis TYPE ty_bsis,
lt_bkpf TYPE STANDARD TABLE OF ty_bkpf,
wa_bkpf TYPE ty_bkpf,
lt_aufk TYPE STANDARD TABLE OF ty_aufk,
lt_aufk_tmp TYPE STANDARD TABLE OF ty_aufk,
lr_bukrs TYPE RANGE OF bukrs,
ls_bukrs LIKE LINE OF lr_bukrs,
lr_aufnr TYPE RANGE OF aufnr,
ls_aufnr LIKE LINE OF lr_aufnr,
w_belnr TYPE bseg-belnr,
w_budat TYPE bkpf-budat,
lt_aaufk TYPE STANDARD TABLE OF aaufk.

lt_aaufk = gt_aaufk .
SORT lt_aaufk BY aufnr ASCENDING.
DELETE ADJACENT DUPLICATES FROM lt_aaufk COMPARING aufnr.
IF NOT lt_aaufk IS INITIAL.
SELECT user1
aufnr
bukrs
prctr
FROM aufk
INTO TABLE lt_aufk
FOR ALL ENTRIES IN lt_aaufk
WHERE aufnr = lt_aaufk-aufnr.
IF sy-subrc = 0.
FREE: lt_aaufk.
SORT lt_aufk BY user1
aufnr
bukrs
prctr.
DELETE ADJACENT DUPLICATES FROM lt_aufk
COMPARING user1
aufnr
bukrs
prctr.
lt_aufk_tmp = lt_aufk.
SORT lt_aufk_tmp BY bukrs.
DELETE ADJACENT DUPLICATES FROM lt_aufk_tmp COMPARING bukrs.
ls_bukrs-sign = 'I'.
ls_bukrs-option = 'EQ'.
LOOP AT lt_aufk_tmp INTO wa_aufk.

ls_bukrs-low = wa_aufk-bukrs.
APPEND ls_bukrs TO lr_bukrs.
ENDLOOP.
lt_aufk_tmp = lt_aufk.
SORT lt_aufk_tmp BY aufnr.
DELETE ADJACENT DUPLICATES FROM lt_aufk_tmp COMPARING aufnr.
LOOP AT lt_aufk_tmp INTO wa_aufk.

ls_aufnr-low = wa_aufk-aufnr.
APPEND ls_aufnr TO lr_aufnr.
ENDLOOP.
FREE:lt_aufk_tmp.
ENDIF.
ENDIF.

IF NOT lt_aufk IS INITIAL.
SELECT belnr
bukrs
gjahr
aufnr
prctr
FROM bsis
INTO TABLE lt_bsis
FOR ALL ENTRIES IN lt_aufk
WHERE bukrs EQ lt_aufk-bukrs
AND   gjahr EQ p_gjahr
AND   aufnr IN lr_aufnr
AND   prctr EQ lt_aufk-prctr.
IF sy-subrc = 0.
SORT lt_bsis BY belnr
bukrs
gjahr
aufnr
prctr.
DELETE ADJACENT DUPLICATES FROM lt_bsis
COMPARING belnr
bukrs
gjahr
aufnr
prctr.
ENDIF.
ENDIF.
FREE:lr_aufnr.

IF NOT lt_bsis IS INITIAL.
lt_bsis_tmp = lt_bsis.
SORT lt_bsis_tmp BY belnr.
DELETE ADJACENT DUPLICATES FROM lt_bsis_tmp COMPARING belnr.
SELECT budat
bukrs
belnr
gjahr
FROM bkpf
INTO TABLE lt_bkpf
FOR ALL ENTRIES IN lt_bsis_tmp
WHERE bukrs IN lr_bukrs
AND belnr EQ lt_bsis_tmp-belnr
AND gjahr EQ p_gjahr.
IF sy-subrc = 0.
SORT lt_bkpf BY budat
bukrs
belnr
gjahr.
DELETE ADJACENT DUPLICATES FROM lt_bkpf COMPARING budat
bukrs
belnr
gjahr.
ENDIF.
FREE:lt_bsis_tmp.

ENDIF.
FREE:lr_bukrs.

LOOP AT gt_aaufk INTO wa_aaufk.
READ TABLE lt_aufk INTO wa_aufk
WITH KEY aufnr = wa_aaufk-aufnr
BINARY SEARCH.
IF sy-subrc <> 0.
CONTINUE.
ENDIF.
READ TABLE lt_bsis INTO wa_bsis
WITH KEY bukrs = wa_aufk-bukrs
gjahr = p_gjahr
aufnr = wa_aaufk-aufnr
prctr = wa_aufk-prctr
BINARY SEARCH.
IF sy-subrc <> 0.
CONTINUE.
ENDIF.
READ TABLE lt_bkpf INTO wa_bkpf
WITH KEY bukrs = wa_aufk-bukrs
belnr = wa_bsis-belnr
gjahr = p_gjahr
BINARY SEARCH.
IF sy-subrc <> 0.
CONTINUE.
ENDIF.

IF wa_bkpf-budat GT p_date_s.
*   If Posting Date is GT Start Date, then delete the entry
*   as the order had an activity in the last 90 days
DELETE gt_aaufk WHERE user1 EQ wa_aufk-user1
AND aufnr EQ wa_aufk-aufnr.

ENDIF.
ENDLOOP.
FREE:
lt_aufk,
lt_bsis,
lt_bkpf.

former_member192023 · ‎2013 Dec 06

Above code format is too ugly.

TYPES:

BEGIN OF ty_aufk,

user1 TYPE aufk-user1,

aufnr TYPE aufk-aufnr,

bukrs TYPE aufk-bukrs,

prctr TYPE aufk-prctr,

END OF ty_aufk,

BEGIN OF ty_bkpf,

budat TYPE bkpf-budat,

bukrs TYPE bkpf-bukrs,

belnr TYPE bkpf-belnr,

gjahr TYPE bkpf-gjahr,

END OF ty_bkpf,

BEGIN OF ty_bsis,

belnr TYPE bsis-belnr,

bukrs TYPE bsis-bukrs,

gjahr TYPE bsis-gjahr,

aufnr TYPE bsis-aufnr,

prctr TYPE bsis-prctr,

END OF ty_bsis.

DATA:

gt_aaufk TYPE STANDARD TABLE OF aaufk,

gt_aaufk_delete type standard table of aaufk.

PARAMETERS p_gjahr TYPE bseg-gjahr.

PARAMETERS p_date_s TYPE sy-datum.

START-OF-SELECTION.

DATA:

wa_aaufk TYPE aaufk,

lt_bsis TYPE STANDARD TABLE OF ty_bsis,

lt_bsis_tmp TYPE STANDARD TABLE OF ty_bsis,

wa_aufk TYPE ty_aufk,

wa_bsis TYPE ty_bsis,

lt_bkpf TYPE STANDARD TABLE OF ty_bkpf,

wa_bkpf TYPE ty_bkpf,

lt_aufk TYPE STANDARD TABLE OF ty_aufk,

lt_aufk_tmp TYPE STANDARD TABLE OF ty_aufk,

lr_bukrs TYPE RANGE OF bukrs,

ls_bukrs LIKE LINE OF lr_bukrs,

lr_aufnr TYPE RANGE OF aufnr,

ls_aufnr LIKE LINE OF lr_aufnr,

w_belnr TYPE bseg-belnr,

w_budat TYPE bkpf-budat,

lt_aaufk TYPE STANDARD TABLE OF aaufk.

lt_aaufk = gt_aaufk .

SORT lt_aaufk BY aufnr ASCENDING.

DELETE ADJACENT DUPLICATES FROM lt_aaufk COMPARING aufnr.

IF NOT lt_aaufk IS INITIAL.

SELECT user1

aufnr

bukrs

prctr

FROM aufk

INTO TABLE lt_aufk

FOR ALL ENTRIES IN lt_aaufk

WHERE aufnr = lt_aaufk-aufnr.

IF sy-subrc = 0.

FREE: lt_aaufk.

SORT lt_aufk BY user1

aufnr

bukrs

prctr.

DELETE ADJACENT DUPLICATES FROM lt_aufk

COMPARING user1

aufnr

bukrs

prctr.

lt_aufk_tmp = lt_aufk.

SORT lt_aufk_tmp BY bukrs.

DELETE ADJACENT DUPLICATES FROM lt_aufk_tmp COMPARING bukrs.

ls_bukrs-sign = 'I'.

ls_bukrs-option = 'EQ'.

LOOP AT lt_aufk_tmp INTO wa_aufk.

ls_bukrs-low = wa_aufk-bukrs.

APPEND ls_bukrs TO lr_bukrs.

ENDLOOP.

lt_aufk_tmp = lt_aufk.

SORT lt_aufk_tmp BY aufnr.

DELETE ADJACENT DUPLICATES FROM lt_aufk_tmp COMPARING aufnr.

LOOP AT lt_aufk_tmp INTO wa_aufk.

ls_aufnr-low = wa_aufk-aufnr.

APPEND ls_aufnr TO lr_aufnr.

ENDLOOP.

FREE:lt_aufk_tmp.

ENDIF.

IF NOT lt_aufk IS INITIAL.

SELECT belnr

bukrs

gjahr

aufnr

prctr

FROM bsis

INTO TABLE lt_bsis

FOR ALL ENTRIES IN lt_aufk

WHERE bukrs EQ lt_aufk-bukrs

AND gjahr EQ p_gjahr

AND aufnr IN lr_aufnr

AND prctr EQ lt_aufk-prctr.

IF sy-subrc = 0.

SORT lt_bsis BY belnr

bukrs

gjahr

aufnr

prctr.

DELETE ADJACENT DUPLICATES FROM lt_bsis

COMPARING belnr

bukrs

gjahr

aufnr

prctr.

ENDIF.

FREE:lr_aufnr.

IF NOT lt_bsis IS INITIAL.

lt_bsis_tmp = lt_bsis.

SORT lt_bsis_tmp BY belnr.

DELETE ADJACENT DUPLICATES FROM lt_bsis_tmp COMPARING belnr.

SELECT budat

bukrs

belnr

gjahr

FROM bkpf

INTO TABLE lt_bkpf

FOR ALL ENTRIES IN lt_bsis_tmp

WHERE bukrs IN lr_bukrs

AND belnr EQ lt_bsis_tmp-belnr

AND gjahr EQ p_gjahr.

IF sy-subrc = 0.

SORT lt_bkpf BY budat

bukrs

belnr

gjahr.

DELETE ADJACENT DUPLICATES FROM lt_bkpf COMPARING budat

bukrs

belnr

gjahr.

ENDIF.

FREE:lt_bsis_tmp.

ENDIF.

FREE:lr_bukrs.

LOOP AT gt_aaufk INTO wa_aaufk.

READ TABLE lt_aufk INTO wa_aufk

WITH KEY aufnr = wa_aaufk-aufnr

BINARY SEARCH.

IF sy-subrc <> 0.

CONTINUE.

ENDIF.

READ TABLE lt_bsis INTO wa_bsis

WITH KEY bukrs = wa_aufk-bukrs

gjahr = p_gjahr

aufnr = wa_aaufk-aufnr

prctr = wa_aufk-prctr

BINARY SEARCH.

IF sy-subrc <> 0.

CONTINUE.

ENDIF.

READ TABLE lt_bkpf INTO wa_bkpf

WITH KEY bukrs = wa_aufk-bukrs

belnr = wa_bsis-belnr

gjahr = p_gjahr

BINARY SEARCH.

IF sy-subrc <> 0.

CONTINUE.

ENDIF.

IF wa_bkpf-budat GT p_date_s.

* If Posting Date is GT Start Date, then delete the entry

* as the order had an activity in the last 90 days

* DELETE gt_aaufk WHERE user1 EQ wa_aufk-user1

* AND aufnr EQ wa_aufk-aufnr.

*bug here Dead loop maybe.

wa_aaufk-user1 = wa_aufk-user1.

wa_aaufk-aufnr = wa_aufk-aufnr.

append wa_aaufk to gt_aaufk_delete.

ENDIF.

ENDLOOP.

SORT gt_aaufk_delete by user1 aufnr ASCENDING.

delete ADJACENT DUPLICATES FROM gt_aaufk_delete COMPARING user1 aufnr.

LOOP AT gt_aaufk_delete into WA_AAUFK.

DELETE gt_aaufk WHERE user1 EQ WA_AAUFK-user1

AND aufnr EQ WA_AAUFK-aufnr.

ENDLOOP.

FREE:

gt_aaufk_delete,

lt_aufk,

lt_bsis,

lt_bkpf.

former_member184155 · ‎2013 Dec 06

Hi Sowmya Sreeram.

Donot use SELECT SINGLE or DELETE Statements inside a LOOP.

Former Member · ‎2013 Dec 13

Dear All,

Thank you all for your suggesitons / advise.

I used COEP / COBK Tables and it worked fine without any performance issues.

Here's my code:


   REFRESH:
      t_coep,
      t_cobk.
 
    SELECT kokrs
           belnr
           perio
           wtgbtr
           objnr
           gjahr
           kstar
     FROM  coep
     INTO  TABLE t_coep
     WHERE kokrs = aufk-kokrs
     AND   objnr = aufk-objnr.
    IF sy-subrc EQ 0.
      SORT t_coep BY belnr objnr.
      SELECT kokrs
             belnr
             gjahr
             budat
       FROM  cobk
       INTO  TABLE t_cobk
       FOR ALL ENTRIES IN t_coep
       WHERE kokrs = t_coep-kokrs
       AND   belnr = t_coep-belnr
       AND   gjahr = t_coep-gjahr.
      IF sy-subrc EQ 0.
        SORT t_cobk BY kokrs belnr.
      ENDIF.
    ENDIF.
* Validation for Posting Date
* Display the orders that do not have had any activity during the past 90 days from wdate
    CLEAR:
      wa_coep,
      wa_cobk.
    LOOP AT t_coep INTO wa_coep WHERE kokrs EQ aufk-kokrs
                                AND ( perio GE w_f_period
                                AND   perio LE w_t_period )
                                AND   objnr EQ aufk-objnr
                                AND   gjahr EQ p_gjahr.
      CLEAR wa_cobk.
      READ TABLE t_cobk INTO wa_cobk WITH KEY
                         kokrs = wa_coep-kokrs
                         belnr = wa_coep-belnr
                         gjahr = wa_coep-gjahr.
      IF sy-subrc EQ 0.
        IF wa_cobk-budat BETWEEN strt-date AND wdate.
          total = total + wa_coep-wtgbtr.
          CLEAR t_90days_aufk.
          t_90days_aufk-user1 = aufk-user1.
          APPEND t_90days_aufk.
        ENDIF.
      ENDIF.
    ENDLOOP.

Thanks & Regards,
Sowmya