topic Re: problem while Fetching BSAD in Application Development and Automation Discussions

problem while Fetching BSAD

Former Member — Tue, 25 Jul 2006 08:04:22 GMT

Hi to all ,

I' ve problem while fetching bsad for a report , in t-code se30 i've seen that it takes %89,1 performance of overall.

SELECT belnr buzei dmbtr blart budat augdt augbl sgtxt

into table odemelerg

FROM bsad

WHERE bukrs EQ bukrs

AND kunnr EQ kunnr

AND ( umsks EQ space OR umsks IS NULL )

AND ( umskz EQ space OR umskz IS NULL )

AND augbl EQ i_augbl

AND augdt GE i_budat

AND gjahr EQ gjahr

AND belnr NE i_belnr

AND bsad_{belnr NE bsad}augbl

AND ( blart EQ blart_bt OR blart EQ blart_hf

OR blart EQ blart_mi ).

here : blart_bt is declared as a constant type and its value is 'BT'. such as blart_hf, blart_mi

How can I make this Select query working in a better performance

Kind regards,

Caglar

Re: problem while Fetching BSAD

Former Member — Tue, 25 Jul 2006 08:07:42 GMT

SELECT belnr buzei dmbtr blart budat augdt augbl sgtxt

into table odemelerg

FROM bsad

WHERE bukrs EQ bukrs

AND kunnr EQ kunnr

AND ( umsks EQ space OR umsks IS NULL )

AND ( umskz EQ space OR umskz IS NULL )

AND augbl EQ i_augbl

AND augdt GE i_budat

AND gjahr EQ gjahr

AND belnr NE i_belnr

AND bsad_{belnr NE bsad}augbl

AND ( blart EQ blart_bt OR blart EQ blart_hf

OR blart EQ blart_mi ).

1)WHERE bukrs EQ bukrs -

>what is this..

2)bsad_{belnr NE bsad}augbl )it is not a join query....

Re: problem while Fetching BSAD

Demirbilek — Tue, 25 Jul 2006 08:12:29 GMT

Hi Caglar;

You have a condition (belnr NE i_belnr) in your SELECT statement which forces query optimizer to scan the whole table sequentially. You should change your logic in your program. If you can give more information, I'd be more helpful.

Regards;

Re: problem while Fetching BSAD

Former Member — Tue, 25 Jul 2006 08:22:22 GMT

Yes you are right Kishan, i forgot to explain them.

this is a class-based report and the biggest was written in se24 .

bukrs is coming from selection screen parameter and i am passing it to the method as 'Bukrs' contains only one value. same as field Kunnr ,

But from 2nd one i couldn' t understand what you meant

thanks

Caglar

Re: problem while Fetching BSAD

Former Member — Tue, 25 Jul 2006 08:32:46 GMT

Hi Hakan ,

I had to wrote this report with classes and had to search " (belnr NE i_belnr) " because the flow scenario of this report could only be solved as this , i mean while you are searching for a billing document if it is parked or not, and it is BSAD doc. number must not equal to its clearing number .

here there is a loop and it includes this method searching for BSAD as the criterions.

CALL METHOD odeme_oku

EXPORTING

i_augbl = lv_augbl

i_belnr = augbl

i_budat = lv_budat.

and in method odeme_oku there is the select statement

kind regards,

Caglar

Re: problem while Fetching BSAD

Former Member — Tue, 25 Jul 2006 08:38:27 GMT

Have you the billing document number?

Max

Re: problem while Fetching BSAD

Demirbilek — Tue, 25 Jul 2006 08:40:28 GMT

If you want to check whether the document has been cleared or not, you can check table BSID with same criteria and using "belnr EQ i_belnr". BSAD table is for cleared customer accounting documents while BSID is for open customer accounting documents. If you still want to use BSAD table, you already state that you don't want to get clearing document by stating "bsad_{belnr NE bsad}augbl". So using "belnr EQ i_belnr" will boost performance as query optimizer will use the primary key of BSAD.

Hope this helps;

Re: problem while Fetching BSAD

Former Member — Tue, 25 Jul 2006 09:06:58 GMT

Thanks to all,

Because large amount of data , i think this problem can not be solved easily

Thanks again

Kind regards,

Caglar

Re: problem while Fetching BSAD

Former Member — Tue, 25 Jul 2006 09:19:23 GMT

It depends on what you really need to do?

Which documents you need to search?

The bills? Why don't you start from them?

If you don't need the payments, you only need to exclude the type document of payments, so you shouldn't check IF BELNR <> AUGBL.

Max

Re: problem while Fetching BSAD

Former Member — Tue, 25 Jul 2006 11:21:03 GMT

Hi Max,

I need the search the bills and the payments both and there is partial clearing of bills too. I need to check all of the bills if it is cleared or not, if cleared ; are there any items still remained and uncleared. this is a little part of scenario(this is where i stuck)

Kind regards,

Caglar

Re: problem while Fetching BSAD

Former Member — Tue, 25 Jul 2006 11:38:06 GMT

If you know the bill number:

-1) Search FI document:

Get header data

select * from bkpf where AWTYP = 'VBRK'

and AWKEY = BILL NUMBER.

EXIT.

ENDSELECT.

Get items data

SELECT * FROM BSEG INTO TABLE T_BSEG

WHERE BUKRS = BKPF-BUKRS

AND BELNR = BKPF-BELNR

AND GJAHR = BKPF-GJAHR

AND KOART = 'D'.

Payment:

LOOP AT T_BSEG WHERE AUGDT <> '00000000'.

IF T_BSEG-AUGBL <> _BKPF-BELNR

T_BSEG-AUGDT <> _BKPF-BUDAT.

SELECT * FROM BKPF INTO _BKPF

WHERE BUKRS = T_BSEG-BUKRS

AND BELNR = T_BSEG-AUGBL

AND BUDAT = T_BSEG-AUGDT.

EXIT.

ENDSELECT.

SELECT * FROM BSEG APPENDING TABLE T_PAYMENT

WHERE BUKRS = _BKPF-BUKRS

AND BELNR = _BKPF-BELNR

AND GJAHR = _BKPF-GJAHR

AND KOART = 'D'.

ENDIF.

ENDLOOP.

Partial payment

SELECT * FROM BSAD INTO TABLE WHERE BUKRS = BKPF-BUKRS

AND KUNNR = T_BSEG-KUNNR

AND REBZG = BKPF-BELNR

AND REBZJ = BKPF-GJAHR.

Max

Re: problem while Fetching BSAD

Former Member — Tue, 25 Jul 2006 11:54:53 GMT

Hi Max,

Sorry for misunderstood and thanks a lot for your attention , i tried to mean FI invoices as bills sorry,

This code is ok but fetching bseg is too risky for this case.

Thanks a lot again