Skip to Content

There is a requirement in our project.

The retail store have the ability to give the customer store credit. So that when create the sales order, the customer can pay through their store credit amount.

The store credit amount can be manually authorized by the user when creating the sales order.

In order to avoid the user authorizing more than the customer’s net store credit amount which will cause risk to the store, we need to get the customer’s and compare with current authorized amount and give error message if necessary.

In order to get the net store credit for the customer, we need to get the accounting document number with the customer number(KUNNR) first. The sql like:

Select * into it_bsegc from BSEG where kunnr = vbak-kunnr.

This SQL has very bad performance. BSEG is a huge tabe and not index on KUNNR. Beacuse BSEG is a clauster table, no index can be created.

To sove the problem, we should get the key fields of the BSEG  before selecting.

We checked the dataelement of the BELNR(this is the account document number), in the where used list, we found another table BSEGC which is a transparent table.

This table is used to specially store the payment card accounting information. it have the same key as the BSEG and a secondary index can be created for it is a transparent table.

So we created a index for the fields KUNNR CCINS and modify the SQL into:

Select * from bsegc where kunnr = vbak-kunnr and ccins = ‘LLCS’.

With the result, we select from bseg with for all entries as:

select * into it_bseg from bseg for all entries in it_bsegc where burks = it_bsegc-burks and belnr = it_bsegc-belnr and gjahr = it_bsegc-gjahr and …….(other conditions)

 With those steps, the performance problem is solved.

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Christoffer Berg
    Hi,

    There is a collection of secondary index tables for BSEG. Have a look at BSID, BSAD, BSIK, BSAK, BSIS, BSAS.

    The third letter in the table names indicates open items (I) or cleared items (A).

    The fourth letter in the table names indicates account type: D = customer, K = vendor, S = G/L account.

    These tables have several indexes themselves.

    Best regards,
    Christoffer

    (0) 
    1. Christoffer Berg
      I should also add that the tables I mentioned contain a lot of the information from BSEG and even some information from BKPF. In most cases you won’t even have to read BKPF and BSEG after reading e.g. BSID.

      /Christoffer

      (0) 
    2. Kangbo Wang Post author
      hi Christoffer,

      I’ve checked the table BSID and BSAD but the records I need is not in there.
      I needs the records whoes SAKNR is 214610.
      Do you have any idea about why the records not go to BSID AND BSAD, but only in BSEG?

      (0) 
  2. Paul Hardy
    select * into it_bseg from bseg for all entries in it_bsegc where burks = it_bsegc-burks and belnr = it_bsegc-belnr and gjahr = it_bsegc-gjahr and …….(other conditions)

    I would not add any other conditions. You need the selections from the underlying cluster table (RFBLG) BUKRS / BELNR / GJAHR. If you add any other selections then the cluster containing all the other BSEG fields needs to be “unpacked” to see if the field you want is there, which has a performance implication – at least that is what we observed.

    As other people have said 99% of the time there is no need to go into BSEG at all. I also feel you need more than just the customer number, otherwise the performance will still not be great, even from BSID. A date restriction of some sort perhaps.

    Cheersy Cheers

    Paul

    (0) 
    1. Kangbo Wang Post author
      Thank you, paul!
      Did you mean that I should only select the BSEG into a interanl table IT_BSEG with the key and then process the internal table to filter the records with furhter conditions?
      I have to goto BSEG. Other condition is SAKNR = ‘0000214610’ and AUGBL = ”. I’ve checked the table BSID and BSAD, records with SAKNR = ‘0000214610’ are not there.
      (0) 
      1. Paul Hardy
        Tests we have done show that when reading from BSEG, using just the key in the selection statement, and then filtering the internal table seems to work a lot faster then using another field from BSEG in the SQL selection statement. Results way vary by database, we are using Oracle V10. The logic, as I have said, is that all the fields in BSEG apart from the key are “clustered” into one big field, and for each record the database has to “unpack” the record to check the value, something it does not have to do if just the key is specified.
        Have a look at tables BSID (customer open items) which contains the SAKNR field to see if your records might be there. As this table contains open items only (which seems to be a confition you require i.e. AUGBL = ”) then it is of limited size and thus performance will be a lot better.
        (0) 
  3. Geoffrey Warriss
    Hi Kangbo ,

    I am not sure if SAP BW is part of your project scope, but it would be far easier to use the standard extractors that SAP provide which cover the aforementioned tables. These can be enhanced with any z fields you have (dependent on delta capability). BSEG is indeed a large table and the extractor logic has already been written to be efficient to extract from this table (and the others mentioned). The extractor which could cover this requirement is 0FI_GL_4. Not sure if this is of help, but there are some OSS notes around performance of this extractor also, which may give you some options if this is not applicable – 1322418. Many thanks Geoff

    (0) 

Leave a Reply