• Kevin

TIP: Coding effective SELECT statements in SAP SD

There are many ways to get to the needed data in SAP but only few give the right result in an efficient way... Below I run through some do's and do not's for access data within the SAP SD module.


Accesses to sales orders (tables VBAK, VBAP, VBKD, VBUK)


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.

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

  • 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


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

  • 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.


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


Other accesses in SD:

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 = ...

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


240 views

© 2020 by ERPGenie. All Rights Reserved