05-03-2007 4:42 PM
hi,
I have to retrieve the data from the different tables...
BSEG ( WEBTR, BELNR, BURKS), BKPF( BKTXT, BLNR, BLDAT, BLART, GJAHR), LFA1( LIFNR, NAME1), EKKO( EBELN) and T001W( WERKS)...
can anyone guide me what is the best way to do this...
being BSEG is cluster table.. i think its better to retrieve the data from BSEG first of all into one internal table and then for all entries in that int. table retrieving the records from the other tables bkpf into one int.table, LFA1 into another int.table and T001W into another int.table.... and combining all these records into final int.table
is this is correct way to do...
one more doubt is EBELN and WERKS existing in the BSEG table, is it necessary to take those from the EKKO and T001W tables.... is there any difference if i take from BSEG.
Thanks a lot for your anticipation...
naveen
05-03-2007 4:56 PM
Hi,
What data you have for input??
Also it is better to start with BKPF instead of BSEG...
BKPF has got good indexes...
Thanks,
Naren
05-03-2007 4:45 PM
Hi,
What kind of data you want to display??
Is it a material document related accounting document.
Or
Is it a Invoice document related accounting document..
What will be input for you???
What data you have so that we can start selecting based on that??
Thanks,
Naren
05-03-2007 4:53 PM
Ya
it is a Invoice document related accounting document..related to vendor..
05-03-2007 5:16 PM
As Rob pointed out, use BSIK and BSAK tables to get the documents based on vendor index instead of going to BKPF and BSEG directly. Take a look at your requirement and see if this is all you need. These two tables store only the vendor line of the accounting document, not all the line items. So if you need only the vendor line item of an accounting document, you can stop here but if you need all the line items then you have to go back to BSEG. You are still at an advantage because you now have the document number and the year to go against the BSEG table.
About your other question regarding do I need to go to T001W or EKKO, it depends on if you need any further information regarding the PO or the plant other than just the number. If all you need is the PO number and the plant, then you don't have to go to these tables. But if you want to get some other details of the PO or the plant, then you will have to go to these tables.
05-03-2007 5:21 PM
Srini...
Thats what i am wondering you know...
actually requirement is not restricted to only vendor lines.. actually i have to display the report to monitor invoice accuracy, timeliness of data entry and employee performance .....
and from the remaining tables i need only those two fields.....
so can u gimme perfect decision and solution how to proceed ....
05-03-2007 5:29 PM
You can go to BSIK and BSAK (open and cleared items), get all the document numbers and years, use that to go to BKPF and BSEG to get all the details you need. That is how I would proceed. If you use your selection criteria against BKPF, BSEG directly, you may not get the kind of performance improvement you need compared to going to BSIK and BSAK first using the same criteria. Also these two tables have the most commonly used fields of BKPF and BSEG as key fields such as posting date, vendor number etc.
But again, if you have a specific document type that you are interested in or one posting date, then you can use the indexes available on BKPF and get the documents. You don't have to go to BSIK or BSAK in this case.
The underlying message is that depending on your selection criteria, you need to assess which indexes you can hit of which tables. In general you will have a better chance of getting the relevant documents by directly hitting these two tables.
05-03-2007 4:46 PM
Hi
Don't use BKPF and BSEG tables at all
Use BSIk and BSAK tables instead, where you will get all the data required.
just pass LIFNR and EBELN fields to BSIk and BSAK table and get the BELNR and other fields
first write a join for LFA1, T001W and EKKO tables using common field LIFNR for LFA1 and EKKO and WERKS for EKKO and T001W tables
then pass LIFNR and EBELN fields to BSIk and BSAK table and get the BELNR and other fields
reward points if useful
regards
Anji
05-03-2007 4:47 PM
better way is to select data from BKPF first and then goto BSEG using the document number and then retrieve data from LFA1.
The data in BSEG for WERKS & EBELN is same as data, so u can take this data as well so that u can avoid 2 select statements.
05-03-2007 4:51 PM
Since it looks like you are interested in vendors, you may be better off going against tables BSIK and BSAK instead of BKPF and BSEG. but it depends on what your selection criteria will be.
Rob
05-03-2007 4:56 PM
Hi,
What data you have for input??
Also it is better to start with BKPF instead of BSEG...
BKPF has got good indexes...
Thanks,
Naren
05-03-2007 5:10 PM
In the selection screen I am having...different fields ..
from BSEG----company code( burks),
from BKPF-----fiscal perios ( gjahr), postion period(monat), entered by ( usnam), parked by( ppnam), entry date(cpudt), PO#(ebeln), docu.type ( blart),
vendor (lifnr) , plant(werks)....
Actually in my report i have to display different invoices like parked invoices, open invoices and closed invoices based upon the selected radio button in the selection screen....
05-03-2007 5:16 PM
So long as the vendor is mandatory, use BSIK and BSAK to start.
Rob
05-03-2007 5:20 PM
Hi,
You can start with BKPF...
Ex..
there is an index on the fields BUKRS, BSTAT & BLART.
make sure the BUKRS & BLART is mandatory in your selection screen.
there is also an index on the fields BUKRS, CPUDT & BSTAT.
Go to your QA system...then in the transaction ST05..In the explain SQL give
the sql and check which one is having less cost...
SELECT BUKRS BELNR GJAHR
FROM BKPF
INTO TABLE T_BKPF
WHERE BUKRS = P_BUKRS
AND BSTAT IN ( ' ' , 'A' ) " ' ' - Normal document, A - Parked doc
AND BLART = P_BLART
AND CPUDT IN SO_CPUDT " Selection screen input.
.
IF NOT T_BKPF[] IS INITIAL.
SELECT BUKRS BELNR GJAHR BUZEI EBELN AUGBL AUGBT
INTO TABLE T_BSEG
FOR ALL ENTRIES IN T_BKPF
WHERE BUKRS = T_BKPF-BUKRS
AND BELNR = T_BKPF-BELNR
AND GJAHR = T_BKPF-GJAHR
AND EBELN IN SO_EBELN " selection-screen input
.
ENDIF.
Thanks,
Naren