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 will update the Accrued Purchases distribution on history receipts from Posting Account Master (SY01100) table. I think this script was created as a client created a new accrual account for purchase orders to split the value out of manual accruals and needed the distributions updated so invoices would post to the new account for the existing receipts.

/*
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
	['Purchasing Distribution Work']
SET
	ACTINDX = ['Posting Account Master'].ACTINDX
FROM
	POP30390 AS ['Purchasing Distribution Work'] --Purchasing Distribution History (POP30390)
INNER JOIN
	POP30300 AS ['Purchasing Receipt History'] --Purchasing Receipt History (POP30300)
		ON
			['Purchasing Receipt History'].POPRCTNM = ['Purchasing Distribution Work'].POPRCTNM
INNER JOIN
	SY01100 AS ['Posting Account Master'] --Posting Account Master (SY01100)
		ON
			['Posting Account Master'].SERIES = 4
		AND
			['Posting Account Master'].PTGACDSC = 'Accrued Purchases'
WHERE
	['Purchasing Receipt History'].POPTYPE = 1
AND
	['Purchasing Distribution Work'].DISTTYPE = 9

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 update the Inventory distribution on purchase orders as a work status to the account on the Item Master (IV00101) table.

/*
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
	['Purchase Order Line']
SET
	INVINDX = ['Inventory Item Master'].IVIVINDX
FROM
	POP10110 AS ['Purchase Order Line'] --PM10110
INNER JOIN
	IV00101 AS ['Inventory Item Master'] --Item Master (IV00101)
		ON
			['Inventory Item Master'].ITEMNMBR = ['Purchase Order Line'].ITEMNMBR

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 will update the accounts payable distribution on all open purchasing transactions from the Posting Account Master (SY01100) table.

/*
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
	['PM Distribution Work/Open']
SET
	DSTINDX = ACTINDX
FROM
	PM10100 AS ['PM Distribution Work/Open'] --PM Distribution WORK OPEN (PM10100)
INNER JOIN
	SY01100 AS ['Posting Account Master'] --Posting Account Master (SY01100)
		ON
			['Posting Account Master'].SERIES = 4
		AND
			['Posting Account Master'].PTGACDSC = 'Accounts Payable'
WHERE
	DISTTYPE = 2 -- ACCOUNTS PAYABLE

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 accounts on an inventory item from the linked Item Class. The script was written after some accounts were updated and the user opted not to roll-down the changes to the items. The roll-down should have been performed and was needed, so this script was created to updsate the items.

/*
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
	['Inventory Item Master']
SET
	IVIVINDX = ['Item Class Setup'].IVIVINDX
	,IVIVOFIX = ['Item Class Setup'].IVIVOFIX
	,IVCOGSIX = ['Item Class Setup'].IVCOGSIX
	,IVSLSIDX = ['Item Class Setup'].IVSLSIDX
	,IVSLDSIX = ['Item Class Setup'].IVSLDSIX
	,IVSLRNIX = ['Item Class Setup'].IVSLRNIX
	,IVINUSIX = ['Item Class Setup'].IVINUSIX
	,IVINSVIX = ['Item Class Setup'].IVINSVIX
	,IVDMGIDX = ['Item Class Setup'].IVDMGIDX
	,IVVARIDX = ['Item Class Setup'].IVVARIDX
	,DPSHPIDX = ['Item Class Setup'].DPSHPIDX
	,PURPVIDX = ['Item Class Setup'].PURPVIDX
	,UPPVIDX = ['Item Class Setup'].UPPVIDX
	,IVRETIDX = ['Item Class Setup'].IVRETIDX
	,ASMVRIDX = ['Item Class Setup'].ASMVRIDX
	,IVSCRVIX = ['Item Class Setup'].IVSCRVIX
FROM
	IV00101 AS ['Inventory Item Master']
INNER JOIN
	IV40400 AS ['Item Class Setup']
		ON
			['Item Class Setup'].ITMCLSCD = ['Inventory 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 will copy vendor emails from the Active Documents table to the standard Dynamics GP email fields where they weren’t already present. This script was written for a client who was replacing the sending of remittances using Active Docs to using the standard functionality.

The script returns mutiple emai address from the Active Docs tables as a semi-colon delimited ist to insert into one field in the standard tables.

/*
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).
*/
INSERT INTO SY01200 --Internet Addresses (SY01200)
	(
		Master_Type,Master_ID,ADRSCODE,INET1,INET2,INET3,INET4,INET5,INET6,INET7,INET8,Messenger_Address,INETINFO,EmailToAddress,EmailCcAddress,EmailBccAddress
	)

SELECT DISTINCT
	'VEN' as Master_Type
	,['Active Docs'].VENDORID AS Master_ID
	,['PM Creditor Master'].VADCDTRO AS ADRSCODE
	,'' AS INET1
	,'' AS INET2
	,'' AS INET3
	,'' AS INET4
	,'' AS INET5
	,'' AS INET6
	,'' AS INET7
	,'' AS INET8
	,'' AS Messenger_Address
	,'' AS INETINFO
	,ISNULL((STUFF((
        SELECT
			'; ' + RTRIM(EMail)
        FROM
			EMA00101 AS ['INNER']
        WHERE
			['INNER'].Email_Type = 1
		AND
			['INNER'].VENDORID = ['Active Docs'].VENDORID
        ORDER BY
			VENDORID
			,IntegerValue
        FOR XML PATH('')
        ), 1, 2, '')
    ),'') AS EmailToAddress
	,ISNULL((STUFF((
        SELECT
			'; ' + RTRIM(EMail)
        FROM
			EMA00101 AS ['INNER']
        WHERE
			['INNER'].Email_Type = 2
		AND
			['INNER'].VENDORID = ['Active Docs'].VENDORID
        ORDER BY
			VENDORID
			,IntegerValue
        FOR XML PATH('')
        ), 1, 2, '')
    ), '') AS EmailCcAddress
	,'' AS EmailBccAddress
FROM
	EMA00101 AS ['Active Docs']
INNER JOIN
	PM00200 AS ['PM Creditor Master'] --PM Vendor Master File (PM00200)
		ON
			['PM Creditor Master'].VENDORID = ['Active Docs'].VENDORID
WHERE
	(
		SELECT
			COUNT(*)
		FROM
			SY01200 AS ['Internet Addresses'] --Internet Addresses (SY01200)
		WHERE
			['Internet Addresses'].Master_Type = 'VEN'
		AND
			['Internet Addresses'].Master_ID = ['PM Creditor Master'].VENDORID
		AND
			['Internet Addresses'].ADRSCODE = ['PM Creditor Master'].VADCDTRO
	) = 0

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 email addresses for vendors from both the standard Dynamics GP fields as well as Active Docs. The email addresses from Active Docs are returned as a semi colon delimited list.

They were returned this way to allow users to check the returned email addresses and correct any incorrect ones, through the Active Docs window, before another script (which I’ll post soon) was run to migrate the emails from the Active Docs fields to the standard ones.

/*
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
	['PM Creditor Master'].VENDORID AS 'Creditor ID'
	,['PM Creditor Master'].VENDNAME AS 'Creditor Name'
	,['PM Creditor Master'].VADCDTRO AS 'Address Code'
	,['Internet Addresses'].EmailToAddress AS 'GP Email To Address'
	,['Internet Addresses'].EmailCcAddress AS 'GP Email Cc Address'
	,['Internet Addresses'].EmailBccAddress AS 'GP Email Bcc Address'
	,ISNULL((STUFF((
        SELECT
			'; ' + RTRIM(EMail)
        FROM
			EMA00101 AS ['INNER']
        WHERE
			['INNER'].Email_Type = 1
		AND
			['INNER'].VENDORID = ['PM Creditor Master'].VENDORID
        ORDER BY
			VENDORID
			,IntegerValue
        FOR XML PATH('')
        ), 1, 2, '')
    ),'') AS 'Active Docs Email To Address'
	,ISNULL((STUFF((
        SELECT
			'; ' + RTRIM(EMail)
        FROM
			EMA00101 AS ['INNER']
        WHERE
			['INNER'].Email_Type = 2
		AND
			['INNER'].VENDORID = ['PM Creditor Master'].VENDORID
        ORDER BY
			VENDORID
			,IntegerValue
        FOR XML PATH('')
        ), 1, 2, '')
    ), '') AS 'Active Docs Email Cc Address'
FROM
	PM00200 AS ['PM Creditor Master']
LEFT JOIN
	SY01200 AS ['Internet Addresses']
		ON
			['Internet Addresses'].Master_Type = 'VEN'
		AND
			['Internet Addresses'].Master_ID = ['PM Creditor Master'].VENDORID
		AND
			['Internet Addresses'].ADRSCODE = ['PM Creditor Master'].VADCDTRO

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 will return all unposted batches and the workflow approval comments which have been entered; it was used within a customisation of workflow in Dynamics GP which allowed the returned comment to be added to the workflow approval notification emails.

/*
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).
*/
WITH COMMENTS AS
	(
		SELECT
			['Posting Definitions Master'].BACHNUMB
			,['Posting Definitions Master'].BCHSOURC
			,CASE WHEN LEFT(CAST(['Workflow History'].Workflow_Comments AS VARCHAR(2000)), 54) = 'The workflow was automatically rejected by the system.' THEN
					'SYSTEM'
				ELSE
					LTRIM(RTRIM(SUBSTRING(REPLACE(['Workflow History'].Workflow_History_User, '\', REPLICATE(' ', LEN(['Workflow History'].Workflow_History_User))), (1) * LEN(['Workflow History'].Workflow_History_User)+1, LEN(['Workflow History'].Workflow_History_User))))
				END + 
				', ' +
				FORMAT(['Workflow History'].Workflow_Completion_Date, 'dd/MM/yyyy') + 
				' ' + 
				FORMAT(['Workflow History'].Workflow_Completion_Time, 'T', 'en-US') + 
				': ' + 
				CASE ['Workflow History'].Workflow_Action
				WHEN 1 THEN
					'SUBMITTED'
				WHEN 2 THEN
					'RESUBMITTED'
				WHEN 3  THEN
					'APPROVED'
				WHEN 4 THEN
					'TASK COMPLETED'
				WHEN 5 THEN
					'REJECTED'
				WHEN 6 THEN
					'DELEGATED'
				WHEN 7 THEN
					'RECALLED'
				WHEN 8 THEN
					'ESCALATED'
				WHEN 9 THEN
					'EDITED'
				WHEN 10 THEN
					'FINAL APPROVED'
			END + ' ' + RTRIM(['Workflow Instance Master'].WfBusObjKey)  + '. ' +
				RTRIM(CAST(ISNULL(['Workflow History'].Workflow_Comments,'') AS VARCHAR(8000))) AS Comment

			,ROW_NUMBER() OVER (PARTITION BY ['Posting Definitions Master'].BACHNUMB, ['Posting Definitions Master'].BCHSOURC ORDER BY Workflow_Completion_Date DESC,Workflow_Completion_Time DESC) AS RowNumber

		FROM
			SY00500 AS ['Posting Definitions Master']
		INNER JOIN
			WFI10002 AS ['Workflow Instance Master']
				ON
					['Workflow Instance Master'].WfBusObjKey = RTRIM(['Posting Definitions Master'].BACHNUMB) + '~' + RTRIM(['Posting Definitions Master'].BCHSOURC) + '~' + FORMAT(['Posting Definitions Master'].CREATDDT, 'yyyy/M/dd') + '~' + FORMAT(['Posting Definitions Master'].TIME1, 'HH:mm:ss')
		LEFT JOIN
			WF30100 AS ['Workflow History']
				ON
					['Workflow History'].WorkflowInstanceID = ['Workflow Instance Master'].WorkflowInstanceID
		WHERE
			['Workflow Instance Master'].WfBusObjKey = RTRIM(BACHNUMB) + '~' + RTRIM(BCHSOURC) + '~' + FORMAT(CREATDDT, 'yyyy/M/dd') + '~' + FORMAT(TIME1, 'HH:mm:ss')
		AND
			CAST(['Workflow History'].Workflow_Comments AS VARCHAR(2000)) <> 'No approval is required for this step.'
		AND
			CAST(['Workflow History'].Workflow_Comments AS VARCHAR(2000)) <> 'No action is required for this step.'
		AND
			CAST(['Workflow History'].Workflow_Comments AS VARCHAR(2000)) <> ''
	)

SELECT
    ['Posting Definitions Master'].BCHSOURC
	,['Posting Definitions Master'].BACHNUMB
    ,(STUFF((
        SELECT
			CHAR(10) + 
				COMMENTS.Comment
        FROM
			COMMENTS
		WHERE
			COMMENTS.BACHNUMB = ['Posting Definitions Master'].BACHNUMB
		AND
			COMMENTS.BCHSOURC = ['Posting Definitions Master'].BCHSOURC
		ORDER BY
			COMMENTS.RowNumber DESC
        FOR XML PATH('')
        ), 1, 1, '')
	) AS Comments
    ,'' AS CMMTTEXT
FROM
	SY00500 AS ['Posting Definitions Master']

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 for a client who wanted to be able to see how much of the annual PO commitment budget was remaining. It takes into account postings to the GL, PO commitments and entered purchase requisitions. This particular client wasn;t interested in seeing having a period check, just a check against the overall annual budget amount.

/*
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
	ACTINDX
	,SUM(VALUE) AS VALUE
	,YEAR1
FROM
	(SELECT
		GLB.ACTINDX AS ACTINDX
		,SUM(GLB.BUDGETAMT) AS VALUE
		,GLB.YEAR1 AS YEAR1
	FROM
		GL00201 AS GLB --[gpt=GL00201]
	INNER JOIN
		CPO40002 AS CPOS --[gpt=CPO40002]
			ON
				CPOS.BUDGETID = GLB.BUDGETID WHERE PERIODID NOT IN (0,13)
	GROUP BY
		GLB.ACTINDX
		,GLB.YEAR1
	UNION ALL
		SELECT
			GLPB.ACTINDX AS ACTINDX
			,SUM(ISNULL(GLPB.PERDBLNC,0)) * -1 AS VALUE
			,GLPB.YEAR1 AS YEAR1
		FROM
			GL10110 AS GLPB --[gpt=GL10110]
		WHERE
			GLPB.PERIODID NOT IN (0,13)
		GROUP BY
			GLPB.ACTINDX
			,GLPB.YEAR1
		UNION ALL
			SELECT
				CPO.ACTINDX AS ACTINDX
				,SUM(ISNULL(CPO.Committed_Amount,0)) * -1 AS VALUE
				,SYY.YEAR1 AS YEAR1
			FROM
				CPO10110 AS CPO --[gpt=CPO10110]
			INNER JOIN
				SY40100 AS SYY --[gpt=SY40100]
					ON
						SYY.ODESCTN = 'Budget Transaction Entry'
					AND
						PERIODDT <= CPO.REQDATE
					AND
						PERDENDT >= CPO.REQDATE
						AND
							PERIODID NOT IN (0,13)
			GROUP BY
				CPO.ACTINDX
				,SYY.YEAR1
			UNION ALL
				SELECT
					POP.INVINDX AS ACTINDX
					,SUM(ISNULL(POP.EXTDCOST,0)) * -1 AS VALUE
					,SYY.YEAR1 AS YEAR1
				FROM
					POP10210 AS POP --[gpt=POP10210]
				INNER JOIN
					SY40100 AS SYY --[gpt=SY40100]
						ON
							SYY.ODESCTN = 'Budget Transaction Entry'
						AND
							PERIODDT <= POP.REQDATE
						AND
							PERDENDT >= POP.REQDATE
						AND
							PERIODID  NOT IN (0,13)
				GROUP BY
					POP.INVINDX
					,SYY.YEAR1
	) AS REMBUDGT
GROUP BY
	ACTINDX
	,YEAR1

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 items at each location and the different quantities on each quantity type (such as on hold, allocation and on different types of open transactions).

/*
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
	['Item Quantity Master'].ITEMNMBR
	,['Item Quantity Master'].LOCNCODE
	,SUM(['Item Quantity Master'].QTYONHND - ['Item Quantity Master'].ATYALLOC - ISNULL(['Sales Transaction Amounts Work'].QTYALLOC,0)) AS 'QTYAVAIL'
	,SUM(['Item Quantity Master'].QTYONHND) AS QTYONHND
	,SUM(['Item Quantity Master'].ATYALLOC)  AS 'QTYALLOC'
	,SUM(['Sales Transaction Amounts Work'].ORDQTYALLOC) AS 'OPENORDERS'
	,SUM(['Item Quantity Master'].QTYBKORD) AS 'OPENBACKORDERS'
	,SUM(['Sales Transaction Amounts Work'].RTNQTYALLOC) AS 'OPENRETURNS'
	,SUM(['Sales Transaction Amounts Work'].INVQTYALLOC) AS 'OPENINVOICES'
	,SUM(['Sales Transaction Amounts Work'].QTYONPO) AS 'QTYONORD'
	,SUM(['Purchase Order Line'].QTYONPO) AS QTYONPO
	,SUM(['Item Quantity Master'].QTYDMGED) AS QTYDAMAGED
	,['Item Quantity Master'].RCRDTYPE AS RCRDTYPE
FROM
	IV00102 AS ['Item Quantity Master'] --Item Quantity Master (IV00102)
LEFT JOIN
	(
		SELECT
			ITEMNMBR
			,LOCNCODE
			,SUM(CASE WHEN SOPTYPE NOT IN (1,4,5) THEN (QTYREMAI - ATYALLOC) * QTYBSUOM ELSE 0 END) AS QTYALLOC
			,SUM(CASE WHEN SOPTYPE = 2 THEN (QTYREMAI - ATYALLOC) * QTYBSUOM ELSE 0 END) AS ORDQTYALLOC
			,SUM(CASE WHEN SOPTYPE = 4 THEN (ATYALLOC) * QTYBSUOM ELSE 0 END) AS RTNQTYALLOC
			,SUM(CASE WHEN SOPTYPE = 3 THEN (ATYALLOC) * QTYBSUOM ELSE 0 END) AS INVQTYALLOC
			,SUM(CASE WHEN SOPTYPE = 5 THEN QTYONPO ELSE 0 END) AS QTYONPO
		FROM
			SOP10200 AS ['Sales Transaction Amounts Work'] --Sales Transaction Amounts Work (SOP10200)
		GROUP BY
			ITEMNMBR
			,LOCNCODE
	) AS ['Sales Transaction Amounts Work']
		ON
			['Sales Transaction Amounts Work'].ITEMNMBR = ['Item Quantity Master'].ITEMNMBR
		AND
			['Sales Transaction Amounts Work'].LOCNCODE = ['Item Quantity Master'].LOCNCODE
LEFT JOIN
	(
		SELECT
			(['Purchase Order Line'].QTYORDER - ['Purchase Order Line'].QTYCANCE) - ISNULL(['Purchasing Receipt Line Quantities'].QTYSHPPD, 0) AS QTYONPO
			,['Purchase Order Line'].ITEMNMBR
			,['Purchase Order Line'].LOCNCODE
		FROM
			POP10110 AS ['Purchase Order Line'] --Purchase Order Line (POP10110)
		LEFT JOIN
			POP10500 AS ['Purchasing Receipt Line Quantities'] --Purchasing Receipt Line Quantities (POP10500)
				ON
					['Purchasing Receipt Line Quantities'].PONUMBER = ['Purchase Order Line'].PONUMBER
				AND
					['Purchasing Receipt Line Quantities'].POLNENUM = ['Purchase Order Line'].ORD
	) AS ['Purchase Order Line']
		ON
			['Purchase Order Line'].ITEMNMBR = ['Item Quantity Master'].ITEMNMBR
		AND
			['Purchase Order Line'].LOCNCODE = ['Item Quantity Master'].LOCNCODE
WHERE
	['Item Quantity Master'].RCRDTYPE = 2
GROUP BY
	['Item Quantity Master'].ITEMNMBR
	,['Item Quantity Master'].LOCNCODE
	,['Item Quantity Master'].RCRDTYPE

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 user to check information; it returns basic information on the vendor along with bank details and email addresses for the addresses linked to the vendor as primary, remit to and purchases fields.

/*
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
	['PM Creditor Master'].VENDORID
	,['PM Creditor Master'].VENDNAME
	,CASE ['PM Creditor Master'].HOLD
		WHEN 0 THEN ''
		ELSE 'On Hold'
	END AS HOLD
	,['PM Creditor Master'].TXRGNNUM AS VatNumber
	,['PM Creditor Address Master'].PHNUMBR1 AS PhoneNumber
	,['PM Creditor Address Master'].ADDRESS1 AS AddressLineOne
	,['PM Creditor Address Master'].ADDRESS2 AS AddressLineTwo
	,['PM Creditor Address Master'].ADDRESS3 AS AddressLineThree
	,['PM Creditor Address Master'].CITY AS City
	,['PM Creditor Address Master'].STATE AS County
	,['PM Creditor Address Master'].ZIPCODE AS PostCode
	,ISNULL(['Internet Addresses'].EmailToAddress,'') AS EmailAddress
	,['Address Electronic Funds Transfer Master'].BANKNAME AS BankName
	,['Address Electronic Funds Transfer Master'].EFTBankCode AS SortCode
	,['Address Electronic Funds Transfer Master'].EFTBankAcct AS AccountNumber
	,['Internet Addresses - Remittance'].EmailToAddress AS RemittanceEmailAddress
	,['Internet Addresses - Purchase Order'].EmailToAddress AS PurchaseOrderEmailAddress
	,['PM Creditor Master'].TXRGNNUM AS CompanyRegistrationNumber
FROM
	PM00200 AS ['PM Creditor Master']
INNER JOIN
	PM00300 AS ['PM Creditor Address Master']
		ON
			['PM Creditor Address Master'].VENDORID = ['PM Creditor Master'].VENDORID
		AND
			['PM Creditor Address Master'].ADRSCODE = ['PM Creditor Master'].VADCDPAD
LEFT JOIN
	SY01200 AS ['Internet Addresses']
		ON
			['Internet Addresses'].Master_Type = 'VEN'
		AND
			['Internet Addresses'].Master_ID = ['PM Creditor Master'].VENDORID
		AND
			['Internet Addresses'].ADRSCODE = ['PM Creditor Master'].VADCDPAD
LEFT JOIN
	SY01200 AS ['Internet Addresses - Remittance']
		ON
			['Internet Addresses - Remittance'].Master_Type = 'VEN'
		AND
			['Internet Addresses - Remittance'].Master_ID = ['PM Creditor Master'].VENDORID
		AND
			['Internet Addresses - Remittance'].ADRSCODE = ['PM Creditor Master'].VADCDTRO
LEFT JOIN
	SY01200 AS ['Internet Addresses - Purchase Order']
		ON
			['Internet Addresses - Purchase Order'].Master_Type = 'VEN'
		AND
			['Internet Addresses - Purchase Order'].Master_ID = ['PM Creditor Master'].VENDORID
		AND
			['Internet Addresses - Purchase Order'].ADRSCODE = ['PM Creditor Master'].VADCDTRO
LEFT JOIN
	SY06000 AS ['Address Electronic Funds Transfer Master']
		ON
			['Address Electronic Funds Transfer Master'].SERIES = 4
		AND
			['Address Electronic Funds Transfer Master'].VENDORID = ['PM Creditor Master'].VENDORID
		AND
			['Address Electronic Funds Transfer Master'].ADRSCODE = ['PM Creditor Master'].VADCDTRO
WHERE
	['PM Creditor Master'].VENDSTTS = 1