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.