boxes

Customer shipping addresses in Dynamics GP


Need to see a list of all the shipping addresses for your Dynamics GP customers? You can use the view below which pulls out the ship to address and the associated tax schedule, shipping method , salesperson, etc.

To see other SQL views for Dynamics GP receivables data, take a look at the Receivables SQL Views page. For other Dynamics GP views and reporting reporting information, check out the GP Reports page.

~~~~~

CREATE VIEW view_Customer_Ship_To_Addresses
AS

/********************************************************************
view_Customer_Ship_To_Addresses
Created on Feb. 18, 2011 by Victoria Yudin - Flexible Solutions, Inc.
For updates visit http://victoriayudin.com/gp-reports/
********************************************************************/

SELECT 	M.CUSTNMBR Customer_ID,
	M.CUSTNAME Customer_Name,
	M.CUSTCLAS Class_ID,
	M.CPRCSTNM Parent_ID,
	M.PRSTADCD Ship_To_Address_ID,
	A.CNTCPRSN Contact,
	A.ADDRESS1 Address_1,
	A.ADDRESS2 Address_2,
	A.ADDRESS3 Address_3,
	A.CITY City,
	A.[STATE] [State],
	A.ZIP Zip,
	A.COUNTRY Country,
	A.PHONE1 Phone_1,
	A.PHONE2 Phone_2,
	A.PHONE3 Phone_3,
	A.FAX Fax,
	A.UPSZONE UPS_Zone,
	A.SHIPMTHD Shipping_Method,
	A.TAXSCHID Tax_Schedule_ID,
	A.LOCNCODE Site_ID,
	A.SLPRSNID Salesperson_ID,
	A.SALSTERR Territory_ID,
	A.USERDEF1 [User-Defined_1],
	A.USERDEF2 [User-Defined_2],
	A.MODIFDT Modified_Date,
	A.CREATDDT Created_Date

FROM RM00101 M  --customer master

INNER JOIN RM00102 A  --addresses
	ON M.CUSTNMBR = A.CUSTNMBR
	AND M.PRSTADCD = A.ADRSCODE

/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/

GO
GRANT SELECT ON view_Customer_Ship_To_Addresses to DYNGRP

 

~~~~~

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone

10 Responses to “Customer shipping addresses in Dynamics GP”

  1. Tried this but I get duplicate results.

    SELECT M.CUSTNMBR AS Customer_ID, M.CUSTNAME AS Customer_Name, M.CUSTCLAS AS Class_ID, M.CPRCSTNM AS Parent_ID, M.PRBTADCD AS Bill_To_Address_Code,
    COALESCE (BillToAddr.CNTCPRSN, ”) AS Bill_To_Contact, COALESCE (BillToAddr.ADDRESS1, M.ADDRESS1) AS Bill_To_Address_1,
    BillToAddr.ADDRESS2 AS Bill_To_Address_2, BillToAddr.ADDRESS3 AS Bill_To_Address_3, BillToAddr.CITY AS Bill_To_City, BillToAddr.STATE AS Bill_To_State,
    BillToAddr.ZIP AS Bill_To_Zip, BillToAddr.COUNTRY AS Bill_To_Country, BillToAddr.PHONE1 AS Bill_To_Phone_1, M.PRSTADCD AS Ship_To_Address_Code,
    COALESCE (ShipToAddr.CNTCPRSN, ”) AS Ship_To_Contact, COALESCE (ShipToAddr.ADDRESS1, M.ADDRESS1) AS Ship_To_Address_1,
    ShipToAddr.ADDRESS2 AS Ship_To_Address_2, ShipToAddr.ADDRESS3 AS Ship_To_Address_3, ShipToAddr.CITY AS Ship_To_City,
    ShipToAddr.STATE AS Ship_To_State, ShipToAddr.ZIP AS Ship_To_Zip, ShipToAddr.COUNTRY AS Ship_To_Country, ShipToAddr.PHONE1 AS Ship_To_Phone_1,
    SOP10200.SOPNUMBE, SOP10100.SOPTYPE, SOP10200.ITEMNMBR, SY01200.INET1
    FROM RM00101 AS M INNER JOIN
    SOP10100 ON M.CUSTNMBR = SOP10100.CUSTNMBR INNER JOIN
    SOP10200 ON SOP10100.SOPNUMBE = SOP10200.SOPNUMBE AND SOP10100.SOPTYPE = SOP10200.SOPTYPE INNER JOIN
    SY01200 ON SOP10100.CUSTNMBR = SY01200.Master_ID LEFT OUTER JOIN
    RM00102 AS ShipToAddr ON M.CUSTNMBR = ShipToAddr.CUSTNMBR AND M.PRSTADCD = ShipToAddr.ADRSCODE LEFT OUTER JOIN
    RM00102 AS BillToAddr ON M.CUSTNMBR = BillToAddr.CUSTNMBR AND M.PRBTADCD = BillToAddr.ADRSCODE
    WHERE (SOP10200.ITEMNMBR LIKE ‘%GIFT%’)

    Like

    • Stewart,

      I don’t think this will work with how you are joining the tables. For example, to join to SY01200 you need at least a Master_Type, Master_ID and ADRSCODE. Also, you’re joining RM00102 to the addresses set up for the customer, not what’s in the SOP tables. Is that on purpose? What if different addresses are used on the SOP transaction? And where is the email address being entered that needs to be used for the emailing of the gift card?

      -Victoria

      Like

      • Thank you so much for your reply.
        I’m very new to GP. I’m not even sure where to look to find what tables are related or how to properly query the data.
        Basically, A person can order a gift card via our website (eCommerce provided by AZOX) I can find the email address in SY01200.INET1. I need to get the shipto and billing address so I can print the to and from address. I also use SOP10200.ITEMNMBR LIKE ‘%GIFT%’ to find the new gift cards. I’m not sure what the best way to do this.

        Like

        • Hi Stewart,

          Sorry, I just realized I never replied to you. It’s very difficult to help with this generically, as it really depends on a lot of specific things in your data. If you still need help, I would recommend working with someone (maybe your GP Partner?) to help you with this kind of stuff until you are more comfortable with the GP tables and how the data is entered and stored. Typically, when I get a report request like this, I sit down with the user and have them show me where all the data is in the GP user interface. That helps me determine where it is stored in the database.

          -Victoria

          Like

  2. I have to send a gift card via email after it’s ordered from our ecommerce package. So I need the ship to, bill to, email address, where the SOP10200.ITEMNBR LIKE ‘GIFT%’
    I don’t have access to the ecommerce code. Currently, online orders are sent to the shipping department. Where it prints invoices and such. They have to manually print the gift cards on our gift card stock. My company now wants the gift cards emailed to the customer automatically. I was going to write something to check the GP database every so often for a gift card purchase then pull the information off to create the email and send it. Anyone have any ideas of the best way to approach this? I’m very new to Dynamics GP

    Like

  3. Great thanks for the above SQL. My company just moved to Dynamics GP. We also purchased an ecommerce package.
    In the above SQL query how would I include an email address?

    Like

  4. Ok, sorry about that, this should be right now.

    SELECT
            M.CUSTNMBR Customer_ID ,
            M.CUSTNAME Customer_Name ,
            M.CUSTCLAS Class_ID ,
            M.CPRCSTNM Parent_ID ,
            M.PRBTADCD Bill_To_Address_Code ,
            
            --COALESCE(BillToAddr.ADRSCODE, '') AS Bill_To_Address_Code ,
            COALESCE(BillToAddr.CNTCPRSN, '') AS Bill_To_Contact ,
            COALESCE(BillToAddr.ADDRESS1, M.ADDRESS1) AS Bill_To_Address_1 ,
            BillToAddr.ADDRESS2 AS Bill_To_Address_2 ,
            BillToAddr.ADDRESS3 AS Bill_To_Address_3 ,
            BillToAddr.CITY AS Bill_To_City ,
            BillToAddr.[STATE] AS Bill_To_State ,
            BillToAddr.ZIP AS Bill_To_Zip ,
            BillToAddr.COUNTRY AS Bill_To_Country ,
            BillToAddr.PHONE1 AS Bill_To_Phone_1 ,
            M.PRSTADCD Ship_To_Address_Code ,
            --COALESCE(ShipToAddr.ADRSCODE, '') AS Ship_To_Address_Code ,
            COALESCE(ShipToAddr.CNTCPRSN, '') AS Ship_To_Contact ,
            COALESCE(ShipToAddr.ADDRESS1, M.ADDRESS1) AS Ship_To_Address_1 ,
            ShipToAddr.ADDRESS2 AS Ship_To_Address_2 ,
            ShipToAddr.ADDRESS3 AS Ship_To_Address_3 ,
            ShipToAddr.CITY AS Ship_To_City ,
            ShipToAddr.[STATE] AS Ship_To_State ,
            ShipToAddr.ZIP AS Ship_To_Zip ,
            ShipToAddr.COUNTRY AS Ship_To_Country ,
            ShipToAddr.PHONE1 AS Ship_To_Phone_1
        FROM
            RM00101 M --customer master
            LEFT OUTER JOIN RM00102 ShipToAddr
            --addresses
            ON M.CUSTNMBR = ShipToAddr.CUSTNMBR
               AND M.PRSTADCD = ShipToAddr.ADRSCODE
            LEFT OUTER JOIN dbo.RM00102 BillToAddr
            ON M.CUSTNMBR = BillToAddr.CUSTNMBR
               AND M.PRBTADCD = BillToAddr.ADRSCODE
    

    Like

  5. Whoops, not quite right – posted a little too fast :0
    That works if all M records have a corresponding entry for BOTH the billing and shipping codes – the INNER JOIN will toss records that don’t have both populated (a rare occurrence in my data, but it does happen) – will post a correction….

    Like

  6. Hi all –
    Thanks, Victoria – I thought I’d take a crack at getting the ShipTo and BillTo broken out in a single row – give this one a shot.

    No warranties express or implied, yada yada – use at your own risk!
    Jon Craigue

    SELECT
            M.CUSTNMBR Customer_ID ,
            M.CUSTNAME Customer_Name ,
            M.CUSTCLAS Class_ID ,
            M.CPRCSTNM Parent_ID ,
            
            M.PRBTADCD Bill_To_Address_Code ,
            
            --COALESCE(BillToAddr.ADRSCODE, '') AS Bill_To_Address_Code ,
            COALESCE(BillToAddr.CNTCPRSN, '') AS Bill_To_Contact ,
            COALESCE(BillToAddr.ADDRESS1, M.ADDRESS1) AS Bill_To_Address_1 ,
            BillToAddr.ADDRESS2 AS Bill_To_Address_2 ,
            BillToAddr.ADDRESS3 AS Bill_To_Address_3 ,
            BillToAddr.CITY AS Bill_To_City ,
            BillToAddr.[STATE] AS Bill_To_State ,
            BillToAddr.ZIP AS Bill_To_Zip ,
            BillToAddr.COUNTRY AS Bill_To_Country ,
            BillToAddr.PHONE1 AS Bill_To_Phone_1 ,
            
            M.PRSTADCD Ship_To_Address_Code ,
            --COALESCE(ShipToAddr.ADRSCODE, '') AS Ship_To_Address_Code ,
            COALESCE(ShipToAddr.CNTCPRSN, '') AS Ship_To_Contact ,
            COALESCE(ShipToAddr.ADDRESS1, M.ADDRESS1) AS Ship_To_Address_1 ,
            ShipToAddr.ADDRESS2 AS Ship_To_Address_2 ,
            ShipToAddr.ADDRESS3 AS Ship_To_Address_3 ,
            ShipToAddr.CITY AS Ship_To_City ,
            ShipToAddr.[STATE] AS Ship_To_State ,
            ShipToAddr.ZIP AS Ship_To_Zip ,
            ShipToAddr.COUNTRY AS Ship_To_Country ,
            ShipToAddr.PHONE1 AS Ship_To_Phone_1
        FROM
            RM00101 M --customer master
            INNER JOIN RM00102 ShipToAddr
            --addresses
            ON M.CUSTNMBR = ShipToAddr.CUSTNMBR
               AND M.PRSTADCD = ShipToAddr.ADRSCODE
            INNER JOIN dbo.RM00102 BillToAddr
            ON M.CUSTNMBR = BillToAddr.CUSTNMBR
               AND M.PRBTADCD = BillToAddr.ADRSCODE
    

    Like

Trackbacks/Pingbacks

  1. Customer shipping addresses in Dynamics GP | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - February 22, 2011

    [...] Comments 0 Victoria Yudin give us SQL code for Customer shipping addresses in Dynamics GP. [...]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,559 other followers

%d bloggers like this: