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[
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
Looking for support or consultancy with Microsoft Dynamics GP?
I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.