This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.
This script selects all Saes transactions at a status of work and returns the ship to address of the transaction and the default from the customer card.
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
['Sales Transaction Work'].CUSTNMBR AS 'Customer Number'
,['Sales Transaction Work'].CUSTNAME AS 'Customer Name'
,['Sales Transaction Work'].PRSTADCD AS 'Trx Address Code'
,['Customer Address Master'].ADRSCODE AS 'Cust Address Code'
,['Sales Transaction Work'].ShipToName AS 'Trx Ship To Name'
,['Customer Address Master'].ShipToName AS 'Cust Ship To Name'
,['Sales Transaction Work'].ADDRESS1 AS 'Trx Address 1'
,['Customer Address Master'].ADDRESS1 AS 'Cust Address 1'
,['Sales Transaction Work'].ADDRESS2 AS 'Trx Address 2'
,['Customer Address Master'].ADDRESS2 AS 'Cust Address 2'
,['Sales Transaction Work'].CITY AS 'Trx City'
,['Customer Address Master'].CITY AS 'Cust City'
,['Sales Transaction Work'].STATE AS 'Trx State'
,['Customer Address Master'].STATE AS 'Cust State'
FROM
SOP10100 AS ['Sales Transaction Work'] --Sales Transaction Work (SOP10100)
INNER JOIN
RM00101 AS ['Customer Master'] --RM Customer MSTR (RM00101)
ON
['Customer Master'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR
INNER JOIN
RM00102 AS ['Customer Address Master'] --Customer Master Address File (RM00102)
ON
['Customer Address Master'].CUSTNMBR = ['Customer Master'].CUSTNMBR
AND
['Customer Address Master'].ADRSCODE = ['Customer Master'].PRSTADCD
