February 18, 2011 
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 https://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
Victoria Yudin 
Hello,
Here is a theoretical scenario 🙂
If I wanted to blanket update the TAXSCHID on all addresses, would RM00101 and RM00102 be the only two tables that I would have to update?
Stephen
LikeLike
Hi Stephen,
Yes, that should do it. Remember this will only apply to new transactions after you make the change, not any existing posted/unposted transactions. And when I make changes like this directly in SQL, I always validate my data by making sure that the new tax schedules actually exist in table TX00101 (or directly in GP).
-Victoria
LikeLike
Is there any way to modify this to show the LAST TIME each address code had been used?
LikeLike
Pam,
This could be a little tricky, because I can think of many different definitions for ‘LAST TIME used’. However, if you wanted to look at the SOP posted transaction headers, you could add the following at the end of the end of the select clause:
,L.Last_Used_Dateand add the following at the very end before the permissions:
left outer join(select CUSTNMBR, PRSTADCD,
max(DOCDATE) Last_Used_Date
from SOP30200
group by CUSTNMBR, PRSTADCD) L
on M.CUSTNMBR = L.CUSTNMBR
and A.ADRSCODE = L.PRSTADCD
Hopefully you can use that example to write whatever code you need to.
-Victoria
LikeLike
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%’)
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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?
LikeLike
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.ADRSCODELikeLike
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….
LikeLike
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.ADRSCODELikeLike