report for incoterms in sales order

[Kaitlin]

Hello.

I have SOs with different incoterms for per lines, for instance, an SO with 8 lines:

- for the fist 5 lines I have different incoterms to the header: identical INCO1 but different INCO2

- for next 3 lines have similar incoterms with the header, both INCO1 and INCO2

and I can't pull the correct data in a report or query to see all 8 lines, each with its data.

When interrogating VBKD table in SE16N I can see for the above case 6 lines, the fist with no line number probably signifying the header + the next 5 lines with different INCO2 but none for the remaining 3 lines that are identical to the header for INCO1 & INCO2.

If I try to use a query starting from a join between VBAK+VBAP+VBKD the result is even stranger, sometimes the orders display empty fields for both INCO1 & INCO2 although the respective order has data there and sometimes some data is displayed.

Any thoughts on how could I make this report work as expected in a consistent manner?

Thank you.

[Kaitlin · Accepted solution]

Hello.

After trying several combinations the easiest and straightforward solution was to provide the Business user a query made with VBAK (left) joined with VBKD + interpretation of the 3 types of results displayed: just header(POSNR=null), identical header and lines, different value for header and lines.

Thank you much for your time and patience Veselina and Jelena, both your answers helped me crystallize the solution - I'm sorry I was not able to mark your answers as helpful, I could not find this option.

[Jelena]

To add to Veselina's answer - you can't easily produce such report using query because you need to get the data from different levels, unless someone (an ABAPer) creates a custom view for this or you do quite a bit of extra work in the query.

You can do a LEFT JOIN with VBKD using VBELN/POSNR. That will give you the records (where they exist) at the line item level. But then you also need header records. You can't just do another JOIN by VBELN because that would drag in both header and line item level records. And in JOIN screen in query it's not possible to specify POSNR = 0 condition. However, you can do that if you go to Extras and add VBKD as an additional table (may need to use an alias to avoid confusion with item level JOIN). This will give you two sets of columns - at item and header level. From there combining the data is just a matter or presentation. E.g. you could add a new local column in query and then fill it in using a formula.

This is way easier to do with ABAP though.

[Kaitlin]

Thank you for the time taken to reply, my apologies for my late one.

I'm not able to follow, my join in SQVI is: VBAK-(VBELN)->VBAP-(VBELN/POSNR)->VBKD with whatever the type of join was provided by default by SAP.

Is the order of joining the tables and the key used OK?

For the above in Extras I have no option to add another table, just 1. Field documentation and 2. reference field.

What am I missing here?

Thank you.

[Kaitlin]

Based on Veselina's patient advises I'm currently trying to create the infoset first in SQ02 but...the options in Extras are identical with the ones in SQVI...however I created an alias table and simply added to the join in the same manner with the previous ones.

Now the question: should the aliased VBKD table be joined to the same VBAP or to the first VBKD?

Thank you.

[Jelena]

I don't know what is the default JOIN proposed but whatever it is it is simply not possible to cover all the scenarios in a single JOIN here.

Records in VBKD can exist at both header and item level. There could be a scenario when only the header record exist. Item level records could exist for some items or for no items. Unfortunately, we need to evaluate both levels to get accurate information.

Here is a very simple example (top is VBAP and bottom VBKD):

For the first order (...44) only header record exists in VBKD (POSNR = 0). So if you try to do JOIN using POSNR then you won't find this record. For the second order (...66) JOIN with POSNR would work but, as you see, it's just one possible scenario.

[Jelena]

Depending on how you defined it, it needs to be JOINed to either VBAK or VBAP. Not VBKD.

[VeselinaPeykova]

You have incoterms at header and at item level.

If the business data is the same for all items, you will have VBKD only for item 000000.

If you create items with differing incoterms at item level, this will result in additional items in VBKD with the corresponding item number.

Basically, you need to do the following:

1. check what items you have in VBAP for this sales order - e.g. 000010 000020 000040 000050.

2. check in VBKD whether there is a record for the combination of the order number and item number:

2.1. if there is - get INCO1 and INCO2 from there.

2.2. if there is no result for this combination - get incoterms from item 000000.

[Kaitlin]

Thank you for the time taken to reply, my apologies for my late one.

I seem to have cases where lines' INC01/02 are identical to the header's but still in VBKD I have entries for each...or maybe here I missed some other "Business data" that differ: what should I look for?

Thank you.

[VeselinaPeykova]

When I have a lot of items to compare, I usually export from SE16 to Excel and use Exact function.

There is one minor point, which I did not mention explicitly - if you have sales order with two items and initially you had differing incoterms for item 000020, an entry in VBKD is created. If you later change back the incoterms to be the same as for the header, the business data will be the same, but there will still be an entry for this item in VBKD.

As to the question, which you asked Jelena - I think, there was a small misunderstanding: when you mentioned query, both of us understood the infoset ones - SQ01/SQ02/SQ03, which offer greater flexibility compared to quick views (SQVI). The feature, which Jelena mentions, is available in SQ02,but not in SQVI.