Outer left join in SQVI

[Former Member]

Need urgent advise on SQVI left join.

I am trying to link Purchasing Document Header with Delivery Item with Shipment Item and Header table.  The purpose is to have all the PO/STO records with the corresponding shipment details.  As there might be less records in the shipment table and all PO records will be need, I include an outer left join between LIPS and VTTP.  I got what I want to have the PO records, DN number and shipment number, shipment number will be blank if there are no record.  The issue arise when I add the VTTK table inner join with the VTTP, which is not returning any records.  Can anyone pls tell me what have I done wrong?

Here is the join condition:

EKKO (EBELN) inner join EKPO (EBELN) 

EKPO (EBELN, MATNR) inner join LIPS (MATNR, VGBEL)

LIPS (VBELN) left outer join VTTP (VBELN)

VTTP (TKNUM) inner join VTTK (TKNUM)

[Former Member]

Please don't take matnr as key field and also take the posnr in to consideration, try to take all the key fields in SQVI.

hope this will solve your query.

Regards,

Abdul Rahim

[Former Member]

Thanks for your prompt reply.

Can you please elaborate more?  I need to join the matnr as it relates the correct material number to the correct delivery item.  And what field does POSNR be linked to when the Item no. in EKPO and VTTP actually do not match?

All I need are the PO by material with qty and net price, plus the Tracking No (TNDR TRKID) in VTTK.  I don't really need anything in the delivery table.  They are just for the linkage.

[Former Member]

hello, there is no link between EKPO and VTTP, the only link is LIPS table. again if you want to relate to shipment cost then use VFKP.
there you will find the EBELN.

Lips-vgbel = EKPO-EBLEN

Vttp-vblen = lips-vbeln

kindly let me know what is your exact report requirement.

[Former Member]

Hi, below is the requirement:

1) PO/STO info: PO no, Plant, Material No, Qty placed, Net price

2) Shipment info: Shipment no, Shipment Tracking ID

As there might be 100 PO/STO in the EKKO table, not all of them has created shipment, so I will need to have all the 100 PO/STO down to materials, with the corresponding shipment/Tracking No.  The Shipment no can be blank if there is no relevant record.

[Former Member]

HI please check the below joining, for me it is working in SQ02,

for me I can view the report also.

Regards,

Abdul Rahim

[Former Member]

Hi Abdul, I tried this joining, it is returning record with PO no, DN no, and shipment no in place.  It is not returning those record with PO, DN but no shipment.  Kindly advise.

I'll need all PO record with or without shipment no please.

[Former Member]

Hello,

Please try in SQ02 and SQ01 then you will find the desired result.

For me I have joined and i can able to get the for the price also you can get from the KONV table.

Regards,

Abdul Rahim

[Former Member]

I do not have access to SQ01 SQ02, therefore I can only do SQVI.

[Former Member]

HI,

Please take it from BASIS, it is more useful than SQVI.

[Former Member]

This message was moderated.