Select Microsoft Dynamics GP GL Accounts for Import

Microsoft Dynamics GPWorking on a recent project for a client we needed to extract the chart of accounts from one Microsoft Dynamics GP company and import it into a couple of others (altering the first segment during the process. The below script was created to extract all of the chart of account data in the format required for import through Integration Manager:


SELECT
	['Account Index Master'].ACTNUMST AS 'Account Number'
	,['Account Master'].ACTDESCR AS 'Account Description'
	,CASE WHEN ['Account Master'].ACTIVE = 1 THEN 'Active' ELSE 'Inactive' END AS 'Active'
	,['Account Master'].ACCTENTR AS 'Allow Account Entry'
	,['Account Master'].ACTALIAS AS 'Account Alias'
	,['Account Category Master'].ACCATDSC AS 'Account Category Description'
	,CASE WHEN ['Account Master'].PSTNGTYP = 0 THEN 'Balance Sheet' ELSE 'Profit and Loss' END AS 'Posting Type'
	,CASE WHEN ['Account Master'].TPCLBLNC = 0 THEN 'Debit' ELSE 'Credit' END AS 'Typical Balance'
	,['Account Master'].USERDEF1 AS 'User Defined 1'
	,['Account Master'].USERDEF2 AS 'User Defined 2'
	,['Account Master'].USRDEFS1 AS 'User Defined 3'
	,['Account Master'].USRDEFS2 AS 'User Defined 4'
FROM
	GL00100 AS ['Account Master']
INNER JOIN
	GL00105 AS ['Account Index Master']
		ON
			['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX
INNER JOIN
	GL00102 AS ['Account Category Master']
		ON
			['Account Category Master'].ACCATNUM = ['Account Master'].ACCATNUM[

Can ISC Software help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you would like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.

Your Name

Your Email

Subject

Your Enquiry

What should we write about next?

Your Name

Your Email

Suggested Topic

Suggestion Details

Leave a Reply

Your email address will not be published. Required fields are marked *