Category: GP (Great Plains)

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 uses BULK INSERT to update the item resource planning fields on Item Quantity Master (IV00102).

/*
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).
*/
/*
	CREATE TEMP ERROR TABLE
*/
CREATE TABLE #Errors
	(
		Error VARCHAR(1000)
		,ROW_ID INT IDENTITY
	)
GO

/*
	CREATE TEMP TABLE FOR DATA
*/
CREATE TABLE #IV00102_IMPORT
	(
		ITEMNMBR VARCHAR(30)
		,SHRINKAGEFACTOR NUMERIC(19,5) -- Shrinkage Factor
		,PRCHSNGLDTM NUMERIC(19,5) -- Purchasing Lead Time
		,MNFCTRNGFXDLDTM NUMERIC(19,5) -- Manufacturing Fixed Lead Time
		,MNFCTRNGVRBLLDTM NUMERIC(19,5) -- Manufacturing Variable Lead Time
	)
GO

/*
	BULK INSERT
*/
BULK INSERT
	#IV00102_IMPORT
FROM
	'c:\temp\ItemResourcePlanning.txt'
WITH
	(FIELDTERMINATOR = '\t'
	,ROWTERMINATOR = '\n'
	,FIRSTROW = 2
)
GO

/*
	VALIDATE DATA
*/
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Item does not exist: ' + CAST(IMPORT.ITEMNMBR AS VARCHAR(100))
		FROM
			#IV00102_IMPORT AS IMPORT
		LEFT JOIN
			IV00101 AS ['Item Master'] --Item Master (IV00101)
				ON
					['Item Master'].ITEMNMBR = IMPORT.ITEMNMBR
		WHERE
			['Item Master'].ITEMNMBR IS NULL
	)
GO

INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Item/Site assignment does not exist: ' + CAST(IMPORT.ITEMNMBR AS VARCHAR(100)) +  ' / MAIN'
		FROM
			#IV00102_IMPORT AS IMPORT
		LEFT JOIN
			IV00102 AS ['Item Quantity Master '] --Item Quantity Master (IV00102)
				ON
					['Item Quantity Master '].ITEMNMBR = IMPORT.ITEMNMBR
				AND
					['Item Quantity Master '].LOCNCODE = 'MAIN'
		WHERE
			['Item Quantity Master '].ITEMNMBR IS NULL
	)
GO

/*
	UPDATE EXISTING DATA IF NO ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) = 0
		-- UPDATE if Email Details present
	UPDATE
		['Item Quantity Master ']
	SET
		SHRINKAGEFACTOR = IMPORT.SHRINKAGEFACTOR
		,PRCHSNGLDTM = IMPORT.PRCHSNGLDTM
		,MNFCTRNGFXDLDTM = IMPORT.MNFCTRNGFXDLDTM
		,MNFCTRNGVRBLLDTM = IMPORT.MNFCTRNGVRBLLDTM
		,INCLDDINPLNNNG = 2
		,REPLENISHMENTMETHOD = 2
		,INCLDMRPMOVEIN = 2
		,INCLDMRPMOVEOUT = 2
		,INCLDMRPCANCEL = 2
	FROM
		IV00102 AS ['Item Quantity Master '] --Item Quantity Master (IV00102)
	INNER JOIN
		#IV00102_IMPORT AS IMPORT
			ON
				IMPORT.ITEMNMBR = ['Item Quantity Master '].ITEMNMBR
	WHERE
		['Item Quantity Master '].LOCNCODE = 'MAIN'
GO

/*
	OUTPUT ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) > 0
	SELECT Error FROM #Errors ORDER BY ROW_ID
GO

/*
	DROP TEMP TABLES
*/
DROP TABLE #IV00102_IMPORT
GO

DROP TABLE #Errors
GO

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 items from Item Master (IV00101) and creates a Dynamics GP macro which can be played back to delete the items. It was created as some items were imported during an implementation and then it was decided to change how items were configured requiring all current items to be removed; there was no transactional data entered so a clean delete was still possible.

/*
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
	'# DEXVERSION=18.00.0028.000 2 2
CheckActiveWin dictionary ''default''  form ''IV_Item_Maintenance'' window ''IV_Item_Maintenance'' 
  TypeTo field ''Item Number'' , ''' + RTRIM(ITEMNMBR) + '''
  MoveTo field Inactive  # ''FALSE''
  MoveTo field ''Delete Button'' 
  ClickHit field ''Delete Button'' 
# Are you sure you want to delete this record?
NewActiveWin dictionary ''default''  form DiaLog window DiaLog 
  ClickHit field OK 
NewActiveWin dictionary ''default''  form ''IV_Item_Maintenance'' window ''IV_Item_Maintenance'' 
'
FROM
	IV00101

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 updates the manufacturing cost accounts on the items using the accounts on the linked classes; this script was created for a client where it was found that classes had been updated and the items still had the wrong codes.

/*
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).
*/
UPDATE
	[IC COST Item Master']
SET 
	ICIVCLIXI = ['IC COST Item Class Setup'].ICIVCLIXI
	,ICIVCLABOHIXI = ['IC COST Item Class Setup'].ICIVCLABOHIXI
	,ICIVCOGSLABOHVARIX_I = ['IC COST Item Class Setup'].ICIVCOGSLABOHVARIX_I
	,ICIVCCIXI = ['IC COST Item Class Setup'].ICIVCCIXI
	,ICIVCMACHOHIXI = ['IC COST Item Class Setup'].ICIVCMACHOHIXI
	,ICIVCOGSMACHOHVARIX_I = ['IC COST Item Class Setup'].ICIVCOGSMACHOHVARIX_I
	,ICIVCMIXI = ['IC COST Item Class Setup'].ICIVCMIXI
	,ICIVCMATOHIXI = ['IC COST Item Class Setup'].ICIVCMATOHIXI
	,ICIVCOGSMATOHVARIX_I = ['IC COST Item Class Setup'].ICIVCOGSMATOHVARIX_I
	,ICIVCOIXI = ['IC COST Item Class Setup'].ICIVCOIXI
	,ICIVMFIXI = ['IC COST Item Class Setup'].ICIVMFIXI
	,ICIVMUIXI = ['IC COST Item Class Setup'].ICIVMUIXI
	,ICIVMATUSAGEVAROHIX_I = ['IC COST Item Class Setup'].ICIVMATUSAGEVAROHIX_I
	,ICIVMATUSAGEVAROHVARIX_I = ['IC COST Item Class Setup'].ICIVMATUSAGEVAROHVARIX_I
	,ICIVMVIXI = ['IC COST Item Class Setup'].ICIVMVIXI
	,ICIVPVIXI = ['IC COST Item Class Setup'].ICIVPVIXI
	,ICIVROIXI = ['IC COST Item Class Setup'].ICIVROIXI
	,ICIVSCIXI = ['IC COST Item Class Setup'].ICIVSCIXI
	,ICIVLVIXI = ['IC COST Item Class Setup'].ICIVLVIXI
	,ICIVUPPV_I = ['IC COST Item Class Setup'].ICIVUPPV_I
	,ICIVLABVAROHIX_I = ['IC COST Item Class Setup'].ICIVLABVAROHIX_I
	,ICIVLABVAROHVARIX_I = ['IC COST Item Class Setup'].ICIVLABVAROHVARIX_I
	,ICIVMEVIXI = ['IC COST Item Class Setup'].ICIVMEVIXI
	,ICIVMACHEFFVAROHIX_I = ['IC COST Item Class Setup'].ICIVMACHEFFVAROHIX_I
	,ICIVMACHEFFVAROHVARIX_I = ['IC COST Item Class Setup'].ICIVMACHEFFVAROHVARIX_I
	,ICIVOVIXI = ['IC COST Item Class Setup'].ICIVOVIXI
	,ICIVWLIXI = ['IC COST Item Class Setup'].ICIVWLIXI
	,ICIVWLOHIXI = ['IC COST Item Class Setup'].ICIVWLOHIXI
	,ICIVWIPLABOHVARIX_I = ['IC COST Item Class Setup'].ICIVWIPLABOHVARIX_I
	,ICIVWCIXI = ['IC COST Item Class Setup'].ICIVWCIXI
	,ICIVWMACHOHIXI = ['IC COST Item Class Setup'].ICIVWMACHOHIXI
	,ICIVWIPMACHOHVARIX_I = ['IC COST Item Class Setup'].ICIVWIPMACHOHVARIX_I
	,ICIVWMIXI = ['IC COST Item Class Setup'].ICIVWMIXI
	,ICIVWMATOHIXI = ['IC COST Item Class Setup'].ICIVWMATOHIXI
	,ICIVWIPMATOHVARIX_I = ['IC COST Item Class Setup'].ICIVWIPMATOHVARIX_I
	,ICIVWOIXI = ['IC COST Item Class Setup'].ICIVWOIXI
	,ICIVILIXI = ['IC COST Item Class Setup'].ICIVILIXI
	,ICIVILABOHIXI = ['IC COST Item Class Setup'].ICIVILABOHIXI
	,ICIVINVLABOHVARIX_I = ['IC COST Item Class Setup'].ICIVINVLABOHVARIX_I
	,ICIVIMXI = ['IC COST Item Class Setup'].ICIVIMXI
	,ICIVIMACHOHIXI = ['IC COST Item Class Setup'].ICIVIMACHOHIXI
	,ICIVINVMACHOHVARIX_I = ['IC COST Item Class Setup'].ICIVINVMACHOHVARIX_I
	,ICIVIMATOHIXI = ['IC COST Item Class Setup'].ICIVIMATOHIXI
	,ICIVINVMATOHVARIX_I = ['IC COST Item Class Setup'].ICIVINVMATOHVARIX_I
FROM
	CT00102 AS [IC COST Item Master'] --CT00102
INNER JOIN
	CT40401 AS ['IC COST Item Class Setup'] --CT40401
		ON
			['IC COST Item Class Setup'].ITMCLSCD = [IC COST Item Master'].ITMCLSCD
GO

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 imports a tab delimited file and updates the Min Order Qty and Average Lead Time on Vendor Item card; there is error handling built in to ensure the vendor item exists. If there are errors they are returned to the user; vendori item cards are only updated if there are no errors.

/*
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).
*/
/*
	CREATE TEMP ERROR TABLE
*/
CREATE TABLE #Errors
	(
		Error VARCHAR(1000)
		,ROW_ID INT IDENTITY
	)
GO

/*
	CREATE TEMP TABLE FOR IV00103 DATA
*/
CREATE TABLE #IV00103_IMPORT
	(
		ITEMNMBR VARCHAR(300)
		,ITEMDESC VARCHAR(300)
		,VENDORID VARCHAR(300)
		,MINORQTY NUMERIC(19,5)
		,AVRGLDTM INTEGER
	)
GO

/*
	BULK INSERT
*/
BULK INSERT
	#IV00103_IMPORT
FROM
	'c:\temp\Vendor Items.txt'
WITH
	(FIELDTERMINATOR = '\t'
	,ROWTERMINATOR = '\n'
	,FIRSTROW = 2
)
GO

/*
	VALIDATE DATA
*/
--validate price sheet header
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Item/Vendor does not exist: ' + CAST(['Import'].ITEMNMBR AS VARCHAR(100)) + '/' + CAST(['Import'].VENDORID AS VARCHAR(100))
		FROM
			#IV00103_IMPORT AS ['Import']
		LEFT JOIN
			IV00103 AS ['Item Vendor Master'] --Item Vendor Master (IV00103)
				ON
					['Item Vendor Master'].ITEMNMBR = ['Import'].ITEMNMBR
				AND
					['Item Vendor Master'].VENDORID = ['Import'].VENDORID
		WHERE
			['Item Vendor Master'].ITEMNMBR IS NULL
	)
GO

/*
	UPDATE DATA IF NO ERRORS
*/
-- update item/vendors
IF (SELECT COUNT(*) FROM #Errors) = 0
	-- update if item/vendor combination exists
	UPDATE
		['Item Vendor Master']
	SET
		MINORQTY = ['Import'].MINORQTY
		,AVRGLDTM = ['Import'].AVRGLDTM
	FROM
		IV00103 AS ['Item Vendor Master'] --Item Vendor Master (IV00103)
	INNER JOIN
		#IV00103_IMPORT AS ['Import']
			ON
				['Item Vendor Master'].ITEMNMBR = ['Import'].ITEMNMBR
			AND
				['Item Vendor Master'].VENDORID = ['Import'].VENDORID
GO

/*
	OUTPUT ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) > 0
	SELECT Error FROM #Errors ORDER BY ROW_ID
GO

/*
	DROP TEMP TABLES
*/
DROP TABLE #IV00103_IMPORT
GO

DROP TABLE #Errors
GO

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 is the customer equivalent of the ,a href=https://www.azurecurve.co.uk/2021/07/validate-and-insert-update-vendor-emails-in-microsoft-dynamics-gp-from-a-text-file/’>vendor script I posted a while ago; it uses BULK INSERT to upload a text file and updates the email addresses on the supplied customer and address combinations.

/*
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).
*/
/*
	CREATE TEMP ERROR TABLE
*/
CREATE TABLE #Errors
	(
		Error VARCHAR(1000)
		,ROW_ID INT IDENTITY
	)
GO

/*
	CREATE TEMP TABLE FOR DATA
*/
CREATE TABLE #CUSTOMER_IMPORT
	(
		CUSTNMBR VARCHAR(30)
		,ADRSCODE VARCHAR(30)
		,EmailToAddress VARCHAR(230)
		,EmailCcAddress VARCHAR(230)
		,EmailBccAddress VARCHAR(230)
	)
GO

/*
	BULK INSERT
*/
BULK INSERT
	#CUSTOMER_IMPORT
FROM
	'C:\Temp\Customer Address Emails.txt'
WITH
	(FIELDTERMINATOR = '\t'
	,ROWTERMINATOR = '\n'
	,FIRSTROW = 2
)
GO

/*
	VALIDATE DATA
*/
--validate parent item
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Customer Address does not exist: ' + CAST(['Import'].CUSTNMBR AS VARCHAR(100)) + ' / ' + CAST(['Import'].ADRSCODE AS VARCHAR(100))
		FROM
			#CUSTOMER_IMPORT AS ['Import']
		LEFT JOIN
			RM00101 AS ['RM Customer Address Master'] --RM Customer MSTR (RM00101)
				ON
					['RM Customer Address Master'].CUSTNMBR = ['Import'].CUSTNMBR
				AND
					['RM Customer Address Master'].ADRSCODE = ['Import'].ADRSCODE
		WHERE
			['RM Customer Address Master'].ADRSCODE IS NULL
		AND
			['Import'].EmailToAddress IS NOT NULL
	)
GO

/*
	INSERT/UPDATE EMAIL DATA IF NO ERRORS
*/
--insert emails
IF (SELECT COUNT(*) FROM #Errors) = 0
	BEGIN
		-- UPDATE if Email Details present
		UPDATE
			SY
		SET
			SY.EmailToAddress = SY_I.EmailToAddress
			,SY.EmailCcAddress = ISNULL(SY_I.EmailCcAddress, '')
			,SY.EmailBccAddress = ISNULL(SY_I.EmailBccAddress, '')
		FROM
			SY01200 SY --Internet Addresses (SY01200)
		INNER JOIN
			#CUSTOMER_IMPORT AS SY_I
				ON
					SY_I.CUSTNMBR = SY.Master_ID
				AND
					SY.Master_Type = 'CUS'
				AND
					SY.ADRSCODE = SY_I.ADRSCODE
		WHERE
			SY_I.EmailToAddress IS NOT NULL

		-- Insert if no Email Details
		INSERT INTO SY01200
			(
				Master_Type
				,Master_ID
				,ADRSCODE
				,INETINFO
				,EmailToAddress
				,EmailCcAddress
				,EmailBccAddress
			)

			(
				SELECT
					'CUS'
					,CUSTNMBR
					,ADRSCODE
					,''
					,EmailToAddress
					,ISNULL(EmailCcAddress, '')
					,ISNULL(EmailBccAddress, '')
				FROM
					#CUSTOMER_IMPORT AS SY_I
				WHERE
					SY_I.EmailToAddress IS NOT NULL
				AND
					(SELECT
						COUNT(Master_ID)
					FROM
						SY01200
					WHERE
						Master_Type = 'CUS'
					AND
						Master_ID = SY_I.CUSTNMBR
					AND
						ADRSCODE = SY_I.ADRSCODE) = 0
					)
	END
GO

/*
	OUTPUT ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) > 0
	SELECT Error FROM #Errors ORDER BY ROW_ID
GO

/*
	DROP TEMP TABLES
*/
DROP TABLE #CUSTOMER_IMPORT
GO

DROP TABLE #Errors
GO

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 imports a tab delimited text file and generates a Dynamics GP macro which can be played back to update the standard costs.

There is built in error handling to validate the supplied item numbers exist.

/*
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).
*/
/*
	CREATE TEMP ERROR TABLE
*/
CREATE TABLE #Errors
	(
		Error VARCHAR(1000)
		,ROW_ID INT IDENTITY
	)
GO

/*
	CREATE TEMP TABLE FOR DATA
*/
CREATE TABLE #COST_IMPORT
	(
		ITEMNMBR VARCHAR(100)
		,STDCOST NUMERIC(19,2)
	)
GO

/*
	BULK INSERT
*/
BULK INSERT
	#COST_IMPORT
FROM
	'C:\Temp\STDCOST.txt'
WITH
	(FIELDTERMINATOR = '\t'
	,ROWTERMINATOR = '\n'
	,FIRSTROW = 2
)
GO

/*
	VALIDATE DATA
*/
--validate ITEM
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Item does not exist: ' + CAST(['Import'].ITEMNMBR AS VARCHAR(100))
		FROM
			#COST_IMPORT AS ['Import']
		LEFT JOIN
			IV00101 AS ['Item Master'] --Item Master (IV00101)
				ON
					['Item Master'].ITEMNMBR = ['Import'].ITEMNMBR
		WHERE
			['Item Master'].ITEMNMBR IS NULL
	)
GO

/*
	CREATE MACRO OUTPUT IF NO ERROR
*/
IF (SELECT COUNT(*) FROM #Errors) = 0
SELECT DISTINCT
	'# DEXVERSION=18.00.0028.000 2 2
CheckActiveWin dictionary ''Manufacturing''  form ''IC_COST_IV_Item_Rollup'' window ''Item_Rollup'' 
  TypeTo field ''(L) Temp Control Number'' , ''' + ['Import'].ITEMNMBR +  '''
  MoveTo field ''NO_CALC_MAT''  # ''FALSE''' + CASE WHEN BM.PPN_I IS NULL THEN + '
# This item doesn''t have a Manufacturing bill of materials and isn''t a component of one.  When you perform a rollup and revalue, costs entered here will overwrite the standard cost displayed in Item Maintenance.  
NewActiveWin dictionary ''Manufacturing''  form DiaLog window DiaLog 
  ClickHit field OK 
NewActiveWin dictionary ''Manufacturing''  form ''IC_COST_IV_Item_Rollup'' window ''Item_Rollup''' ELSE + '' END + '
  ClickHit field ''NO_CALC_MAT''  # ''TRUE''
  TypeTo field ''Mat Cost''[5] , ''' + CAST(['Import'].STDCOST AS VARCHAR(20)) + '''
  MoveTo field ''Mat Cost Eff Date''[5] 
  TypeTo field ''Mat Cost Eff Date''[5] , ''01012021''
  MoveTo field ''NO_CALC_MAT_FIX_OHD''  # ''FALSE''
  CommandExec dictionary ''Manufacturing''  form ''IC_COST_IV_Item_Rollup'' command ''IC_Save_T_w_Item_Rollup_f_IC_COST_IV_Item_Rollup'' '
FROM
	#COST_IMPORT AS ['Import']
LEFT JOIN
	BM010115 AS BM
		ON
			BM.PPN_I = ['Import'].ITEMNMBR
		OR
			BM.CPN_I = ['Import'].ITEMNMBR
GO

/*
	OUTPUT ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) > 0
	SELECT Error FROM #Errors ORDER BY ROW_ID
GO

/*
	DROP TEMP TABLES
*/
DROP TABLE #COST_IMPORT
GO

DROP TABLE #Errors
GO

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 returns a list of work status assemblies and the quantity required for the assembly.

CREATE VIEW uv_AZRCRV_AssembleQuantityRequired AS
/*
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
	TRX_ID
	,ITEMNMBR
	,Assemble_Quantity
FROM
	BM10300 --Assembly Component (BM10300)
WHERE
	Component_ID = 0
AND
	Parent_Component_ID = -1
GO

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 returns work status sales invoices which are linked to a history status sales order and along with data for the work transaction returns the originating transactions order number and document date.

/*
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'].SOPNUMBE
	,['Sales Transaction Work'].SOPTYPE
	,['Company Master'].CMPNYNAM AS COCMPNYNAM
	,['Company Master'].ADDRESS1 AS COADDRESS1
	,['Company Master'].ADDRESS2 AS COADDRESS2
	,['Company Master'].ADDRESS3 AS COADDRESS3
	,['Company Master'].CITY AS COCITY
	,['Company Master'].STATE AS COSTATE
	,['Company Master'].ZIPCODE AS COZIPCODE
	,['Company Master'].COUNTY AS COCOUNTRY
	,['Company Master'].TAXREGTN AS COTAXREGNO
	,['Company Master'].UDCOSTR1 AS COREGNO
	,['Company Master'].PHONE1 AS COPHONE1
	,['Internet Addresses - Company'].INET1 AS COEMAIL
	,['Internet Addresses - Company'].INET2 AS COWEBSITE
	,['Sales Transaction Work'].CUSTNMBR
	,['Sales Transaction Work'].CUSTNAME
	,['Sales Transaction Work'].CSTPONBR
	,['Sales Transaction Work'].SOPNUMBE AS INVNO
	,['Sales Transaction History'].SOPNUMBE AS ORDNO
	,['Sales Transaction History'].DOCDATE
	,['Currency Setup'].CRNCYSYM
	,['RM Customer Master'].TXRGNNUM AS CUSTTAXREGNO
	,['Customer Master Address'].ADDRESS1 AS BTADDRESS1
	,['Customer Master Address'].ADDRESS2 AS BTADDRESS2
	,['Customer Master Address'].ADDRESS3 AS BTADDRESS3
	,['Customer Master Address'].CITY AS BTCITY
	,['Customer Master Address'].STATE AS BTSTATE
	,['Customer Master Address'].ZIP AS BTZIP
	,['Customer Master Address'].COUNTRY AS BTCOUNTRY
	,['Sales Transaction Work'].CUSTNMBR AS CUSTCODE
	,['Sales Transaction Work'].ADDRESS1 AS STADRESS1
	,['Sales Transaction Work'].ADDRESS2 AS STADDRESS2
	,['Sales Transaction Work'].ADDRESS3 AS STADDRESS3
	,['Sales Transaction Work'].CITY AS STCITY
	,['Sales Transaction Work'].STATE AS STSTATE
	,['Sales Transaction Work'].ZIPCODE AS STZIP
	,['Sales Transaction Work'].COUNTRY AS STCOUNTRY
	,['Sales Transaction Work'].ORSUBTOT
	,['Sales Transaction Work'].ORTAXAMT
	,['Sales Transaction Work'].ORSUBTOT + ['Sales Transaction Work'].ORTAXAMT AS ORTOTAL
	,['Sales Transaction Work'].DUEDATE
FROM
	SOP10100 AS ['Sales Transaction Work'] WITH (NOLOCK) --Sales Transaction Work (SOP10100)
INNER JOIN
	RM00101 AS ['RM Customer Master'] WITH (NOLOCK) --RM Customer MSTR (RM00101)
		ON
			['RM Customer Master'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR
INNER JOIN
	RM00102 AS ['Customer Master Address'] WITH (NOLOCK) --Customer Master Address File (RM00102)
		ON
			['Customer Master Address'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR
		AND
			['Customer Master Address'].ADRSCODE = ['Sales Transaction Work'].PRBTADCD
INNER JOIN
	SOP30200 AS ['Sales Transaction History'] WITH (NOLOCK) --Sales Transaction History (SOP30200)
		ON
			['Sales Transaction History'].SOPTYPE = ['Sales Transaction Work'].ORIGTYPE
		AND
			['Sales Transaction History'].SOPNUMBE = ['Sales Transaction Work'].ORIGNUMB
INNER JOIN
	DYNAMICS..SY01500 AS ['Company Master'] WITH (NOLOCK) --Company Master (SY01500)
		ON
			['Company Master'].INTERID = DB_NAME()
LEFT JOIN
	DYNAMICS..MC40200 AS ['Currency Setup'] WITH (NOLOCK) --Currency Setup (MC40200)
		ON
			['Currency Setup'].CURNCYID = ['Sales Transaction Work'].CURNCYID
LEFT JOIN
	SY01200 AS ['Internet Addresses - Company'] WITH (NOLOCK) --Assembly Component (BM10300)
		ON
			['Internet Addresses - Company'].Master_Type = 'CMP'
		AND
			['Internet Addresses - Company'].ADRSCODE = 'PRIMARY'
WHERE
	['Sales Transaction Work'].SOPTYPE = 3

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 was created to allow a client to easily review the workflow step assignments.

/*
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
	['Workflow Tasks Table'].Workflow_Name
	,['Workflow Tasks Table'].WorkflowInstanceID
	,['Workflow Tasks Table'].WorkflowTaskAssignedTo
	,['Workflow Step Instance Table'].Workflow_Step_Status
	,['Workflow Step Instance Table'].Workflow_Step_Assign_To
	,['Workflow Instance Master'].Workflow_Status
	,['Workflow Instance Master'].Workflow_Originator
FROM
	WFI10004 AS ['Workflow Tasks Table'] --Workflow Tasks Table (WFI10004)
INNER JOIN
	WFI10003 AS ['Workflow Step Instance Table'] --Workflow Step Instance Table (WFI10003)
		ON
			['Workflow Tasks Table'].WorkflowInstanceID = ['Workflow Step Instance Table'].WorkflowInstanceID
INNER JOIN
	WFI10002 AS ['Workflow Instance Master'] --Workflow Instance Master (WFI10002)
		ON
			['Workflow Tasks Table'].WorkflowInstanceID = ['Workflow Instance Master'].WorkflowInstanceID