Connect Sales order Nr to VBAK and LIKP

[Former Member]

Im working with a CE1**** table where i have sales order nr KAUFN. im trying to:

1- join KAUFN to table VBRP.AUBEL - OK, it's done, i will then have to distinct to obtain only one record. (VBRK doesn't have sales order nr)

2- join VBRP.VBELN to VBAK.VBELN, in order to obtain ERDAT (Creation date of Sale) NOK

3- join VBRP.VBELN to LIKP.VBELN, in order to obtain WADAT_IST (delivery date) NOK

These last two steps are failing, i've filtered for some VBRP VBELN's in VBAK and LIKP and i can't seem to find.

What im i missing?

In the end i pretend to obtain creation date and delivery date of each sales order nr.

Kind regards

[Lakshmipathi]

If you want to retrieve

• Sale Order number
• Sale Order Creation Date and
• Actual Goods Issue Date

you can very well table join VBAK with LIKP in SQVI so that you can get the required information

G. Lakshmipathi

[Former Member]

Im sorry, but as a SAP newbie i don't know what you mean by SQVI.

As a BI developer, im extracting tables with Theobald software to SQL Server, and then i work there.

Can you explain in more detail your suggestion?

As i've written, i can't find some specific VBRP VBELN values in VBAK and LIKP tables.

Regards

[VeselinaPeykova]

Hello Ricardo,

I am not really sure what you have in this CE1* table and what business processes the company has, but I will try to point out a couple of things.

1- join KAUFN to table VBRP.AUBEL - OK, it's done, i will then have to distinct to obtain only one record. (VBRK doesn't have sales order nr)

It is possible in SAP to have a single billing document for sales order items from different orders - e.g I might bill the customer for several orders at once, because the customer wants to get one invoice per day/week.

It is possible to have the same sales order item as reference in multiple billing documents:

I can bill first 2 EA from item 10 of order 123, then bill the remaining 1 EA in a second billing document, then I can decide to cancel the first and second invoice (again 2 billing documents) and bill order 123 item 10 again - billing document 5. All these will refer to the same sales order. So it is not that simple to get one VBRK-VBELN per sales order. You will probably end up with several billing documents.

It could happen that a billing document item is not created with reference to a sales order - e.g. delivery without reference. What are you expected to do in such cases? What is the client requirement and the purpose of the whole report?

2- join VBRP.VBELN to VBAK.VBELN, in order to obtain ERDAT (Creation date of Sale) NOK

VBRP-VBELN is the billing document number. If your billing item refers to a sales order, then VBRP-AUBEL will have the order number and VBRP-AUTYP will be C, H, K,L. You need to use this AUBEL in VBAK-VBELN to get ERDAT instead of VBRK-VBELN.

3- join VBRP.VBELN to LIKP.VBELN, in order to obtain WADAT_IST (delivery date) NOK

It will also not work for a similar reason as in 2. If you have a delivery, you might find the number in VBRP-VGBEL, but you have to check VBRP-VGTYP to ensure that this is a delivery. In some special processes like advanced returns, it can get even more complicated and you will have to get the delivery number from VBFA starting from the sales order. After you find the delivery number, you can check in likp to get the actual goods movement date.

I strongly suggest that you speak with some of your SD/LE/MM colleagues to help you with building whatever logic you need.

As to SQVI, this is a very simple transaction in SAP, which consultants use to build small queries by joining transparent tables - looks a bit like MS Access, but has limited functionality and is not very good for big amounts of data. You can try it out to verify if you are using correct determination between some tables.  I usually don't use tables to get an overview of the document flow for a single order in SAP - I use FM SD_DOCUMENT_FLOW_GET or TCODE ALO1. If your intention is to understand the document flow and to find some useful tables in the process, you can try them out as well.

[Former Member]

Hi Veselina, first of all thank you for your detailed explanation, which i've read carefully.

I'm a BI developer and SAP newbie, so i might fail understanding/explaining some details; the CE1* table i'm working with returns details about production/sales at sales level,
for example, for the production of product A (100 items) it returns sale X of 30 items and Y of 70 items.

This CE1* table returns KAUFN (sales order nr), it's my objective to get for each KAUFN the sales order date creation and the delivery date.

Admiting that KAUFN is a sales product order nr , at least it should be easy to identify
to identify the sales order creation date, no?

Is there any other way to obtain these values?

Regards

Regards

[VeselinaPeykova]

Hello Ricardo,

I think you skipped the part, where I suggested that you contact your colleagues, who are SAP consultants in your company to help you build the logic.

You should have received some functional specification or at least some solution proposal explaining the process and from where to get the data, right? So you know the author and you can ask him for assistance if you don't even access ERP.

If you have no functional specification, then make sure to get one. You can't experiment by joining tables/fields and just ask random people in an internet forum until you get some meaningful results in your report.

It is also possible that your client has some developments and stores data in z-fields or z-tables, which SCN members will not know about, because they are specific to your company. They might be important for building your report and provide more effective (faster) way to get the information.

If you are absolutely sure that you are always getting the correct sales order number, then SD order creation date will be in VBAK-ERDAT (VBAK-VBELN is the sales order number).

[Former Member]

Hi Veselina,

the problem is that the organization has suffered many changes... but i'll speak with the existing advanced users.

Thanks for remembering about specific developments.

Kind regards