Working 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[