Need to select from BSEG on Non Key fields.

[Former Member]

Hi all,

I am developing a report on Work Order Cost Analysis. The selection screen has Order Type (AFPO-DAUAT), Plant (AFPO-DWERK), Date range (AFKO-GLTRI) and Part Number (AFKO-PLNBEZ) as the selection criteria. <b>All the orders and their corresponding object numbers (OBJNR) are picked in an internal table</b>, for all orders that fulfill the selection criteria and their Actual Finish Date (AFKO-GLTRI) falls between the entered date range.

Now comes the problem, <b>corresponding to these Order Numbers I need to pick records from BSEG.</b> Since this is not a key field in BSEG, its not indexed and the report times out on the Development Server itself.

<b>I also tried using COEP</b> table as the fields that I need are present there as well (Though I am not sure weather it would be give me all the lines of records that I need from BSEG), but that operation also times out.

LDBs also don't seem to help. Is there any way I can achieve the above?

<b>PLEASE HELP. REWARDS GUARANTEED.</b>

Regards,

Nikhil

[Former Member]

Hi Nikhil,

Instead of taking data directly from BSEG table try some other tables which has relation with BSEG table and AFPO or AFKO.

Wait for some time i will come up with table and flowchart.

Regards,

Amey

[Former Member]

Hey Amey,

Thanks for the update, I'm looking fwd for the table relations and flowcharts. Thanks again.

Regards,

Nikhil

[Former Member]

Nikhil try COER - Sales Order Value Revenue table.

Regards,

Amey

[Former Member]

Hey Amey,

What you are refering to is a Sales Order Revenue table but I am looking something on the lines of Production Orders, Internal Orders etc. The Order Type would not be fixed, it would be entered at the selection screen.

I guess I mentioned tables AFKO (PP Orders) and AUFK in my original post. Looking to hear from you back....

Regards,

Nikhil

[FredericGirod]

Hi Nikhil,

don't be affraid by BSEG, and don't look the key. The key is not so important, what is really important is the index. Look into the index if you can find your fields.

And don't forget, there are secondary index like BSIS, BSIK, BSAS, BSAK, ....

Rgd

Frédéric

[Former Member]

Hey Frederic,

I am aware of the fact that theoretically the key does not guarantee a fast data retrieval, but usually all key fields are well indexed so specifying all or even a good part of the key fields does speed up the process considerably.

I am also aware of tables like BSIS/BSAS Secondary Index for G/L Accounts, but the problem is that in G/L master data, (FS00), on Control data tab, under Account Management in Company Code, if the G/L account's Line Item Display is not clicked, then it would hold ONLY one entry and it would not exist in BSIS/BSAS. This scenario can cause inconsistency in my report.

Hope u can suggest another way,

Rgds,

Nikhil

[Former Member]

Try COVP - it's a view of COEP and COBK. You need REFBN and REFBZ. Then you should be able to go to BSEG.

Rob

[Former Member]

Hey Rob,

That sounds too good. I have checked it on my Sandbox System, COVP has all the fields that I need to link it to BSEG so that I can achieve fast data retrieval. Though I don't have access to my Development Server right away. I would check the same and I would surely come back for some points for you!!!!

Thanks pal,

Nikhil

[Former Member]

Hi Nikil,

SAP has provided one table for one module related ( like PS, PM) cost. Using these tables, you can retrieve the cost based on OBJNR and transaction type ( WRTTP, I am not logged on to SAP right now, spelling might be off). For example the cost related to PM work orders can be retrieved from table PMCO. The cost related to PS objects ( WBS elements and networks) can be obtained from RPSCO. I am sure there will be one table for PP also. I hope it helps.

[Former Member]

Glad to help - bear in mind that these are CO tables and the referenece document numbers do not necessarily refer to FI documents. Before going to BSEG, you will also have to do some further checking (ORGVG = 'RFBU' FI Postings).

Rob

[Former Member]

Also, don't use FI tables to get the cost because accounting documents might have other non cost related items. You will need to know the correct accounts to filter out your cost related items. You have to also figure out if you want debit or credit side entries ( which is also affected in case of cancellation or reversal). You have to also take into account the status of accounting document ( for example if FI doc is parked or not). Your best bet will to go against the CO tables directly where you just get the cost against object and sum it up.

[Former Member]

Hey Rob,

I checked it......... Its still very slow.... I am just trying to pick the key fields of BSEG from COVP and its taking ages in that too. Here is a code segment, just in case you want to figure out what and how I am doing it...

SELECT A~AUFNR GLTRI GAMNG IGMNG OBJNR
         FROM AFKO AS A INNER JOIN AUFK AS B ON A~AUFNR = B~AUFNR
         INTO CORRESPONDING FIELDS OF TABLE IT_WORDER
         WHERE GLTRI IN S_GLTRI AND
         AUART IN P_AUART AND
         WERKS IN P_WERKS AND
         PLNBEZ IN P_PLNBEZ.

  IF SY-SUBRC <> 0.
    MESSAGE E314 WITH 'No Orders for this criteria.'.
  ENDIF.


  SELECT OBJNR BUKRS BELNR GJAHR BUZEI
         FROM COVP
         INTO CORRESPONDING FIELDS OF TABLE IT_COVP
         FOR ALL ENTRIES IN IT_WORDER
         WHERE OBJNR = IT_WORDER-OBJNR
         AND ORGVG = 'RFBU'.

Please note that the user is entering Order Type, Plant, End Date range and Part Number on the selection screen. Its the select on COVP thats taking time. Please help.

Regards,

Nikhil

[Former Member]

Nikhil - offhand, it looks ok. I'll have a look at it, but in the meantime, how many entries are there in IT_WORDER?

In particular, is IT_WORDER empty? If it is, you will retrieve all entries in COVP. So, in any event you should check to see if it is empty and not do the second select if it is.

Rob

Message was edited by: Rob Burbank

[Former Member]

OK - a couple of things:

In the select from covp, you are retrieving the CO document data not the FI document data, so you need the reference documents.

Even though OBJNR is the first field of a key it will not be very selective. It would be better if you could specify more fields. Fortunately, some are standard and you can probably figure out what to use. If you can limit this to a single fiscal year, it would be best. Failing that, you should specify any (or all) fiscal years in a range table (using =).

This is the select that I came up with:

SELECT refbk refbn refgj refbz
       FROM  covp
       INTO  CORRESPONDING FIELDS OF TABLE it_covp
       FOR ALL ENTRIES IN it_worder
       WHERE lednr = '00'               "Standard ledger
       AND   objnr = it_worder-objnr
       AND   gjahr IN s_gjahr
       AND   wrttp = '04'               "Actuals
       AND   versn = '000'              "Plan/Actual ver.
       AND orgvg = 'RFBU'.              "FI Postings

I filled s_gjahr with the individual years from 1995 to 2006 and it ran in under 20 minutes. (The first select was wide open and selected all.)

Rob

By the way - do you still need to go to BSEG, or can you get everything you need from COVP?

Message was edited by: Rob Burbank

[former_member186741]

Hi Nikhil,

how many entries are in IT_WORDER.. if it is excessive you could break the Select up and get chunks of, say, 1000 at a time by using a loop and a 'select...appending table' approach. Also I think you should use COEP direct rather than COVP (which is a view derived from it and another table) but try to ensure IT_WORDER is sorted by OBJNR AND has duplicates removed from it. There is an index on COEP on objnr/kstar/gjahr which this will probably use.. can you derive the GJAHR too as this may help the index selection?

Regards

Neil

[Former Member]

Hey Rob/Neil,

Sorry for the delay in replying. I was not working on this weekend.

Well, it_worder, for the test case I am using has ONLY 6 entries. Give me some time to check the new solutions you guys have come up with. Thanks for the post Neil and thank you Rob for the update.

Regards,

Nikhil

[Former Member]

Hey Rob,

Wether I read from COEP or COVP, I would still need to go to BSEG. That would add another overhead to the report. The situation seems grim I am looking for a solution as soon as possible. Please help.

Regards,

Nikhil

[Former Member]

Did you try the code I suggested above. It's faster.

Rob

[Former Member]

So long as you are getting all the key fields, there won't be much more overhead for BSEG.

Rob

[former_member186741]

Hi Nikhil,

I am surprised that the COEP select is slow when there are only 6 entries in it_worder. Are you sure the problem is this select and not the earlier one with joins on AUFK?

If it is the COEP select maybe you should talk to your basis guys as it sounds wrong.

Regards

Neil

[Former Member]

Hi,

Go through INDEXES of BSEG like:

BSIS, BSIK, BSAS, BSAK.

Hope this helps....