Join VBPA,VBAK,VBAP,VBKD & KNA1

[former_member300568]

Hi All,

I created a SQVI with the following tables and i was able to achieve what i wanted. But i am trying to write a select query with this and it does not seem to work. Please help.

 select vbak~vkorg
        vbpa~kunnr
        vbap~zzlifnr
        vbpa~vbeln
        vbap~posnr
        vbap~matnr
        vbap~meins
        vbap~zzoqty
        vbap~zzouom
        vbak~netwr
        vbkd~fkdat
        vbpa~parvw
        kna1~land1
        kna1~name1
        kna1~ort01
        vbak~bsark
        vbak~ernam
    into table lt_sales_gallo
  from ( ( ( ( vbpa join vbak on vbpa~vbeln = vbak~vbeln )
                    join kna1 on vbpa~kunnr = kna1~kunnr )
                    join vbap on vbpa~vbeln = vbap~vbeln )
                    join vbkd on vbpa~vbeln = vbkd~vbeln )
      where vbak~vkorg = p_vkorg and
            vbkd~fkdat in s_date and
            vbak~ernam = 'INTERFC_USER' and
            vbpa~kunnr = '700116168' and
            vbap~zzlifnr in s_vendor.

[former_member300568]

Hi All,

I created a SQVI with the following tables and i was able to achieve what i wanted. But i am trying to write a select query with this and it does not seem to work. Please help.

 select vbak~vkorg
        vbpa~kunnr
        vbap~zzlifnr
        vbpa~vbeln
        vbap~posnr
        vbap~matnr
        vbap~meins
        vbap~zzoqty
        vbap~zzouom
        vbak~netwr
        vbkd~fkdat
        vbpa~parvw
        kna1~land1
        kna1~name1
        kna1~ort01
        vbak~bsark
        vbak~ernam
    into table lt_sales_gallo
  from ( ( ( ( vbpa join vbak on vbpa~vbeln = vbak~vbeln )
                    join kna1 on vbpa~kunnr = kna1~kunnr )
                    join vbap on vbpa~vbeln = vbap~vbeln )
                    join vbkd on vbpa~vbeln = vbkd~vbeln )
      where vbak~vkorg = p_vkorg and
            vbkd~fkdat in s_date and
            vbak~ernam = 'INTERFC_USER' and
            vbpa~kunnr = '700116168' and
            vbap~zzlifnr in s_vendor.

[Jelena_Perfiljeva]

What "does not work"? What are you trying to achieve?

[VeselinaPeykova]

I do not know how exactly your SQVI query looks, but form the list of tables used (especially VBKD) I am almost sure that this query also does not produce correct results.

If you have not done so already - speak with your functional SD colleague to understand better the relationships between these tables. Before that it is not efficient to spend time on developing the report.

[former_member300568]

Actually this is the query i am using and i thought i gave me the necessary output.

[VeselinaPeykova]

Whether you will have a VBKD entry on item level depends on the existence of different data between header and item.

With your query you can only capture business data if it differs from the header - e.g. you have items 000010; 000020; 000030 in a sales order and you change incoterms for item 000020 you can only get the data from VBKD for 000020; you won't see the information for 000010 and 000030, because it is stored as item 000000 and you do not have such item in VBAP. If this report is aimed at business users it will be a challenge to explain the logic to them.

[JL23]

I don't believe that your SQVI works correctly, the left outer join is on the wrong place. Only the the most right table can have a left outer join for a meaningful result.

Further is a generated Quickview nothing else than a program, you can get the program name via the menu and can look into the code to see how SAP does the selection

[former_member300568]

The inner join is a wrong i agree , thanks @ Jürgen L .

Veselina Peykova : I am trying to capture the sales order details of customers who have orders coming in through EDI , hence i decided to join VBKD as well. I have included in my select query where vbak~ernam = 'INTERFC_USER' and VBAK-BSARK = 'EDI'.

So VBAK,VBAP and VBPA is enough to generate the necessary result?

I am not sure, if anyone has any clue it would be great!