Automate

 View Only

  • 1.  Name for both sold to and ship to

    Posted 09-22-2021 13:18
    Hello,
    I'm trying to build a simple sales order report in Query using VBAK, VBAP and KNA1.  In my output, i'm hoping to include both Sold to and ship to name.  So I have sold to number from VBAK and ship to number from VBAP.  I try to get the Name 1 from KNA1 but i think that's only for Sold to... how can i get ship to name then?
    Thank you in advance for your help.

    ------------------------------
    Carmen Lim | Global Process Advisor
    ------------------------------


  • 2.  RE: Name for both sold to and ship to

    Posted 09-23-2021 02:44
    Hi Carmen,

    these are both in the KNA1 table. 
    But they have different account groups. Fieldname KNA1-KTOKD.
    In standard: 0001 = Sold-To party, 0002 = Goods Recepient.
    But most companies have Z... or Y... variants for this.
    So you have to check that.
    succes

    ------------------------------
    Jan van Asseldonk | Consultant
    CTAC | +31629078169
    ------------------------------

    Original Message


  • 3.  RE: Name for both sold to and ship to

    Employee
    Posted 09-23-2021 09:16
    Hi Carmen

    Jan is correct.  You can use VBAK and VBPA.  Then you can add a table twice to a query, so add KNA1 and join to VBAK and add it again and join to VBPA.

    Another tip, partner type is stored differently than what you see in SAP.  Use the conversion exit on the workspace tab and choose the partner type.

    Otherwise 
    • WE is Ship to
    • AG is Sold To
    • RG is Payer
    • RE is Bill To

    Quick example attached.  

    Hope that helps,
    Sigrid

    ------------------------------
    Sigrid Kok
    PSE | Winshuttle NA
    ------------------------------

    Attachment(s)

    Qsq
    QuerySalesOrderPartners.Qsq   288 KB 1 version
    xlsx
    QuerySalesOrderPartners.xlsx   5 KB 1 version
    Original Message


  • 4.  RE: Name for both sold to and ship to

    Posted 09-23-2021 12:28
    Thank you Jan and Sigrid for your quick response!  The attached excel helps to visualize the out put but is it possible to get all in one row otherwise it looks like the records are duplicated?
    Sales Doc Number / Sold To Number / Sold to Name / Ship To Number / Ship to Name / Material number /

    Else, will have to pivot the results I guess which is still ok.

    carmen

    ------------------------------
    Carmen Lim | Global Process Advisor
    ------------------------------

    Original Message


  • 5.  RE: Name for both sold to and ship to

    Employee
    Posted 09-23-2021 12:44
    Hi Carmen

    Two options with query output you want to "flatten":
    • split into 2 queries into 2 sheets, and use a vlookup from 1 sheet for the additional partner data
    • run the query for everything but the additional partner(s), then use a transaction script with the query data as input to open the sales order, and then  find and download the partner(s) into the same row

    Best Regards,
    Sigrid


    ------------------------------
    Sigrid Kok
    PSE | Winshuttle NA
    ------------------------------

    Original Message


  • 6.  RE: Name for both sold to and ship to

    Posted 09-23-2021 12:57
    Thank you Sigrid!  Let me try out your suggestion.  It is a common use case for us to pull data that contains sales document data by sold to and ship to so I hope to get it to work. :)

    carmen

    ------------------------------
    Carmen Lim | Global Process Advisor
    ------------------------------

    Original Message