Skip to Content
-1

VBAK, VBAP, LIKP, LIPS, VBRP, VBRK and KNA1 Join infoset.

Mar 09 at 03:13 PM

121

avatar image
Former Member

Hi. I have succesfully joined the above mentioned tables.

LIKP_KUNNR -> KNA1_KUNNR

LIKP_VBELN -> LIPS_VBELN to LIPS_VBELN & POSNR -> VBRP_VGBEL & POSNR to VBRP_VBELN -> VBRK_VBELN

And finally

LIPS_VGBEL & POSNR -> VBAP_VBELN & POSNR to VBAP_VBELN -> VBAK_VBELN

Now.. This is all working well, but my end output that I hope to achieve is to be able to enter either sales order, delivery number or billing number in either:

Header data field, showing header connection
or
Item data field, showing the full order, delivery and billing doc (all items in these)

Is this possible anyway? I am still quite new to creating queries, and at the moment, it seems to be showing header data, when I enter delivery number or billing number, but item data (several lines) when entering sales order.

Is the thing I want to achieve possible and is it something that can fairy easily be done in either changing the infoset or/and the layout of the query?

Thank you :)

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Jelena Perfiljeva
Mar 09 at 08:58 PM
1

There is no "header connection" in SAP. Just look at the VBFA table keys, the whole document flow is at the item level.

With these JOIN conditions (and if there is no other selection criteria or filters) you should get the same exact results as you would in SE16. Go to VBRP, find VGBEL/VGPOS, then take that to LIPS and find sales order from there. You should also be able to cross-check the results with VBFA table.

In case if the sales order items have generated multiple deliveries and multiple billing documents, naturally, you'd see all the sales order items (and their subsequent documents) if you enter VBAK-VBELN. But if you enter VBRK-VBELN you'd see only the sales order item(s) that refer to it. The relationship between the sales document items and all the subsequent documents is "one to many". With hat in mind, I'm not sure what you're expecting to see as "header connection". At the item level the query seems to be working correctly.

As a side note, this JOIN covers only the delivery-related billing scenario. In the order-related billing the document flow is different.

P.S. Make sure to include all the item numbers (POSNR) in the infoset and query, then you'll see for sure what data is presented.

Show 11 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Jelena and thank you for fast answer! :)

I am suspecting that exactly the POSNR might be what needs to be "updated". I have done the JOINs as described in first thread, but I am not sure if I should do additional POSNR joins in the infoset?

Your sentence: "P.S. Make sure to include all the item numbers (POSNR) in the infoset and query, then you'll see for sure what data is presented." Do you mean by joining these some how or just to make sure I am showing these fields for all tables involved?

I noticed that when I enter Sales Document, I get the item data (all lines from the sales order), but when I enter the delivery document or the billing document, it only gives me one single line (which appears to be a single line item from the document that is selected).

I have tried adding all POSNR fields to the Fields Groups and Displaying all these in the Query afterward, but with no change. So for some reason, only the Sales Document gives me the item data for all lines.

Am I doing something wrong?

Thank you.

0

Sorry, I can't tell more from just your description. Post a screenshot of JOIN in the infoset, query fields and also VBFA entries for the sales order.

Assuming there are no other filters or selection criteria, I can only guess that you might have different order items flowing into different subsequent documents. But it's impossible to tell for sure without looking at all the parts involved.

Edit: it looks like POSNR is already included in your JOIN condition, so it should also be included in the field groups in the infoset and displayed in query. Then you'll see what exactly the query presents. As I mentioned, there is no header-level relationship. You're saying that by entering VBRK-VBELN you see only one line item. But which item number (POSNR) does it belong to? General error here is to assume a relationship solely by the document number.

0
Former Member

Hi again.
So these are my Joins:

And these are my field groups:

I am not currently using VBFA in the Query. Should I be doing so?

Thank you again so much for taking time to help me out on this! :)

joins.jpg (390.6 kB)
0
Former Member

Couldnt add all the pictures in one answer apparently. Here comes the rest.


0
Former Member

More Field groups

0
Former Member

And finally: Query View and selection fields

0
Former Member

This should be the final one.

0

OK, your JOIN is incorrect. Sorry I didn't realize it from the question text. It should be like this:

LIPS-VBELN -> VBRP-VGBEL
LIPS-POSNR -> VBRP-VGPOS

VBAP-VBELN -> LIPS-VGBEL
VBAP-POSNR -> LIPS-VGPOS

I'm guessing your JOIN worked before because POSNR happened to be the same in all the documents. This is frequently the case but not always.

You don't need to include VBFA table in the query but you can use it in SE16 to verify if your query is showing the document flow correctly. It's the table where the whole document flow is stored (same as what you see in the transactions in the document flow screen).

1
Former Member

It is working!!! :) This is amazing. Thank you so much Jelena. I still have a lot to learn I see, but of to a good start now.

May I ask you how you have learned all this? Is there a place I can read more on the join elements, the importance of "main table" in the query and so on? I am eager to learn more so I can create to all of my needs to save time in the future.

Have a great weekend. :)

0

Well, if you simply open a table in SE11 you'll see that VGPOS field is right there under VGBEL. :) Also simply by looking at the data in SE16 it's not difficult to connect the dots usually.

There are very many SCN blogs about the queries. Google -> "sap query site:blogs.sap.com" and start reading.

"Main table" in JOIN is just what you start with. Think of your JOIN as of a simple SELECT statement (that's what the infoset is translated into at the end). If you had to write a custom ABAP report you'd just write SELECT ... FROM LIPS JOIN LIKP JOIN VBRK, etc. The "main table" is just the first one here. It has more importance if you use LEFT JOIN. Then it's really the main table and the left-joined is "may or may not have data".

If you are not familiar with SQL SELECT options then I highly recommend reading some general information on this (just google some simple SQL reference). You can use it in MS Access or other databases too. It's not just specific to SAP, it's general knowledge.

Edit: this question still shows as unanswered. If this has been resolved the kindly close the question (see this blog).

0
Former Member

Thank you Jelena.

I will start reading more about SQL as well as the SAP blogs. I think I loose the overview from time to time on this, as it is all still very new and many things to keep in mind, but practice makes perfect. :)

Have a great day! :)

0