如何選擇正確的讀取SD的資料表,提高程式效能

JackWu發表於2019-01-15

SD Table Access – SAP Note 185530

Read SAP Note 185530 before developing new SD report or if your SD report is running slow.

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

Search orders for customer number (field VBAK-KUNNR)
If you want to look for sales order for a customer do not directly select from VBAK, instead first select on VAKPA and then based on what sales order you get execute second select in VBAK to get additional data.

Incorrect SELECT FROM vbak WHERE kunnr = …
Correct SELECT FROM vakpa WHERE kunde = …
SELECT FROM vbak WHERE vbeln = vakpa-vbeln.

Search order items for material number (field VBAP-MATNR)
To search for sales order line for particular material select from table VAPMA first and then go to VBAP table for more information.

Incorrect SELECT FROM vbap WHERE matnr = …
Correct SELECT FROM vapma WHERE matnr = …

SELECT FROM vbap WHERE vbeln = vapma-vbeln
AND posnr = vapma-posnr

Use search help views defined for Sales Order
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.

View Name Search Fields
M_VMVAA Customer PO number
M_VMVAE Partner number (KUNNR, LIFNR, or PERNR)
M_VMVAL Sales Org, Sold-to party
M_VMVAO Sold-to party

2. Accesses to deliveries (tables LIKP, LIPS)

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.

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

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

Use search help views defined for Delivery
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)

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

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

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

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

Document flow
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.

 

Incorrect SELECT vbelv FROM vbfa WHERE vbeln …
Correct SELECT vgbel FROM lips WHERE vbeln = …;
or
SELECT vgbel FROM vbrp WHERE vbeln = …;
or
SELECT aubel FROM vbrp WHERE vbeln = …

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

Link to SAP Note 185530

相關文章