187906 - Performance: Customer developments in PP and PM

Symptom

 

Customer programs and program enhancements ("user exits") have a bad performance. The bad performance can be seen when accessing the following SAP logistic tables:
AFRU, AFKO, AUFK, CAUFV, RESB
 

Reason and Prerequisites

 

The R/3 System does not contain secondary indexes to the most important PP / PM flow tables.
To access them effectively you need a basic knowledge of the SAP data model. This notes contains information on the most important errors and provides alternatives.

However, note the following WARNINGS:
- Before you use the suggested alternatives check your program for functional correctness.

Pay attention to the following NOTES:
In the following examples, you often find accesses to several
e.g..:
  SELECT aufnr FROM resb WHERE rsnum = <afko-rsnum>
    SELECT ..... FROM afko WHERE aufnr = <resb-aufnr>
To increase the performance you should define a selection view on the corresponding tables (here: resb and afko) to combine the accesses to both table into one.

Solution

1. Access to order confirmation (table AFRU)

a) Search for confirmation of orders (field AFRU-AUFNR):
Incorrect:
  SELECT FROM afru WHERE aufnr = ...
Correct:
  SELECT aufpl FROM afko WHERE aufnr = <afru-aufnr>
    SELECT rueck FROM afvc WHERE aufpl = <afko-aufpl>
      SELECT ..... FROM afru WHERE rueck = <afvc-rueck>
Caution:
From table AFVC, you receive one value per operation for "RUECK".

2. Access to PPS orders (tables AFKO, CAUFV, AUFK)

a) Find orders for reservation (field AFKO-RSNUM; CAUFV-RSNUM):
Incorrect:
  SELECT FROM afko WHERE rsnum = ...
Correct:
  SELECT aufnr FROM resb WHERE rsnum = <afko-rsnum>
    SELECT ..... FROM afko WHERE aufnr = <resb-aufnr>
Note:
The same access path applies to CAUFV and AUFK.
Search for sales orders (tables VBAP-VBELN, VBAP-POSNR; VBFA-VBELN, VBFA-POSNN; and so on.)
Incorrect:
  SELECT FROM AUFK WHERE KDAUF = <VBAP-VBELN>
                    AND KDPOS = <VBAP-POSNR>
Correct:
   SELECT FROM AFPO WHERE KDAUF = <VBAP-VBELN>
                      AND KDPOS = <VBAP-POSNR>
    SELECT FROM AUFK WHERE AUFNR = AFPO-AUFNR.
Note:
The tables AFKO and CAUFV can be selected analogously to the AUFK access using order number AUFNR.

3. Access to reservations and dependent requirement (table: RESB, MDRS ATP _RESB)

a) Search for reservation for PPS orders (field RESB-AUFNR):
Incorrect:
  SELECT FROM resb WHERE AUFNR = ...
Correct:
  SELECT rsnum FROM afko WHERE aufnr = <resb-aufnr>
    SELECT ..... FROM resb WHERE rsnum = <afko-rsnum>
Note:
The same access path applies to MDRS and ATP_RESB.

b) Search for reservation for Planned orders (field RESB-PLNUM):
Incorrect:
  SELECT FROM resb WHERE PLNUM = ...
Correct:
  SELECT rsnum FROM plaf WHERE plnum = <resb-plnum>
    SELECT ..... FROM resb WHERE rsnum = <plaf-rsnum>
Note:
The same access path applies to MDRS and ATP_RESB.

Conversion Routines in SAP

CONVERSION ROUTINES are also called as CONVERSION EXITS.

In some cases SAP displays values differently then they the way they are stored.

For example while creating material the input value for the filed language of description “MAKT-SPRAS” is actually 'EN' that is on the screen.

Values are stored differently internally.

The field “SPRAS” is tied with data element  “SPRAS” and  conversion routine “ISOLA”. This routine is responsible for conversion of EN > E

 

 

For example for the language filed MAKT-SPRAS there are 2 conversion routines associated with it namely:

 

Conversion routines are identified by a five-place name and stored as a group of two function modules. The function modules have fixed naming convention. The following function modules are assigned to conversion routine XXXXX:

• CONVERSION_EXIT_XXXXX_INPUT
• CONVERSION_EXIT_XXXXX_OUTPUT

185530 - Performance: Customer developments in SD

Reason and Prerequisites

 

The R/3 System contains no secondary indexes to the most important SD transaction data tables in the delivery. Instead, the R/3 System has proprietary index tables (sales document indexes, for example the tables VAKPA and VAPMA, matchcode tables for example M_VMVAB, M_VMVAC or matchcode views for example M_VMVAA, M_VMVAE), which allow an efficient access.
In order to use these efficiently a certain basic knowledge of the SAP data model is required. For this this note lists the most important errors which are made and it offers alternatives.
However note the following WARNINGS:

• Before you use the listed alternatives productively, you must check your program closely for functional accuracy.

• The sales document indexes must be maintained correctly in your system. This is usually automatically the case, however, there can be inconsistencies. Carefully read and follow Notes 128947 and 178328.

Consider the following NOTES:

In the following examples, there are often accesses to several tables, for example:
  SELECT FROM vakpa WHERE kunde = ...     SELECT FROM vbak WHERE vbeln = vakpa-vbeln.
It can increase the performance, to define a selection view on the corresponding tables (here: VAKPA and VBAK) in order to combine the accesses to the two tables in one access.

Solution

1. Accesses to sales orders (tables VBAK, VBAP)

a) Search orders for customer number (field VBAK-KUNNR):
Incorrect:   SELECT FROM vbak WHERE kunnr = ...

                       Correct:   SELECT FROM vakpa WHERE kunde = ...
    SELECT FROM vbak WHERE vbeln = vakpa-vbeln.

b) Search order items for material number (field VBAP-MATNR):
Incorrect:   SELECT FROM vbap WHERE matnr = ...

                       Correct:   SELECT FROM vapma WHERE matnr = ...    SELECT FROM vbap WHERE vbeln = vapma-vbeln
                      AND posnr = vapma-posnr

c) Other search helps for sales orders offer the matchcode tables and views M_VMVAx, x. = A, B, C, ... M; for example search for purchase order number of the customer, description and so on

2. Accesses to deliveries (tables LIKP, LIPS)

a) Search for deliveries with customer number (field LIKP-KUNNR):
Incorrect:   SELECT FROM likp WHERE kunnr = ...

                       Correct:   SELECT FROM vlkpa WHERE kunde = ...    SELECT FROM likp WHERE vbeln = vlkpa-vbeln.

b) Search for delivery items with material number (field LIKP-MATNR):
Incorrect:   SELECT FROM lips WHERE matnr = ...

                       Correct:   SELECT FROM vlpma WHERE matnr = ...    SELECT FROM lips WHERE vbeln = vlpma-vbeln
                      AND posnr = vlpma-posnr

c) Search for deliveries with sales order number (preceding document, field LIPS-VGBEL):
Incorrect:   SELECT FROM lips WHERE vgbel = ...

                       Correct:   SELECT FROM vbfa WHERE VBELV  = ... and VBTYP_N = 'J'    SELECT FROM lips WHERE vbeln = vbfa-vbeln
                      AND posnr = vbfa-posnn

d) Other search helps for deliveries offer matchcode tables and views M_VMVLx, x. = A, B, C, ... M; for example search for goods issue date, picking date, transportation planning date and so on.

3. Accesses to invoices (tables VBRK, VBRP)

a) Search for invoices with customer number ("payer") (field VBRK-KUNRG):
Incorrect:   SELECT FROM vbrk WHERE kunrg = ...

                       Correct:   SELECT FROM vrkpa WHERE kunde = ...    SELECT FROM vbrk WHERE vbeln = vrkpa-vbeln

b) Search for invoice items with material number (field VBRP-MATNR):
Incorrect:   SELECT FROM vbrp WHERE matnr = ...

                       Correct:   SELECT FROM vrpma WHERE matnr = ...    SELECT FROM vbrp WHERE vbeln = vrpma-vbeln
                      AND posnr = vrpma-posnr

c) Search for invoices with delivery number (preceding document, field VBRP-VGBEL):
Incorrect:   SELECT FROM vbrp WHERE vgbel = ...

                       Correct:   SELECT FROM vbfa WHERE vbtyp_n = 'M'
                     AND vbelv  = ...    SELECT FROM vbrp WHERE vbeln = vbfa-vbeln
                      AND posnr = vbfa-posnn

d) Search for invoices with order number (preceding document, field VBRP-AUBEL):
Incorrect:   SELECT FROM vbrp WHERE aubel = ...

                       Correct:   SELECT FROM vbfa WHERE vbtyp_n = 'M'                    AND vbelv   = ...
    SELECT FROM vbrp WHERE vbeln = vbfa-vbeln
                      AND posnr = vbfa-posnn

4. Other accesses in SD:

a) Document flow:
Incorrect:   SELECT vbelv FROM vbfa WHERE vbeln ...

                       In table VBFA only the preceeding document is used to search for the subsequent document (for example, delivery for order). Searching the other way makes no sense with this table since the preceding documents (for example, order for delivery) are stored directly in the document tables. Thus reading in table VBFA is a one-way street.
Correct:  SELECT vgbel FROM lips WHERE vbeln = ...; or  SELECT vgbel FROM vbrp WHERE vbeln = ...; or
  SELECT aubel FROM vbrp WHERE vbeln = ...

b) Search for shipping unit item with delivery
Incorrect:   SELECT FROM vepo WHERE vbtyp = 'J'                    AND vbeln = i_lips-vbeln

                       Correct:   SELECT FROM vbfa WHERE vbtyp_n = 'X'                    AND vbelv = i_lips-vbeln
  SELECT FROM vepo WHERE venum = vbfa-vbeln