Links for GL Tables in GP

[PETEHO]

I have been tasked with migrating data from a Great Plains Database to a Sage Database. I am only moving general ledger data. I was wondering if anyone had a copy or knew where I could find a diagram of how the data is linked within the GL tables. I seem to get so far then it all goes pear shaped. I basically want to extract every transaction with account code,cost centre amount,year and period. Any advice would be much appreciated.

Thanks

Pete

 

[winthropdc]

You can look at the SDK (Software Development Kit) which has Entity-relationship (ER) diagrams. It can be installed from the Tools folder of CD2 of the Microsoft Dynamics GP install disks.

Open year transactions GL_YTD_TRX_OPEN (GL20000)

Historical year transactions GL_Account_TRX_HIST (GL30000)

Account Master GL_Account_MSTR (GL00100) linked on ACTINDX field

Account Category Master GL_Account_Category_MSTR (GL00102) linked on ACCATNUM field

Why are you migrating in the wrong direction anyway?

David Musgrave [MSFT]
Escalation Engineer - Microsoft Dynamics GP
Microsoft Dynamics Support - Asia Pacific

Microsoft Dynamics (formerly Microsoft Business Solutions)

http://www.microsoft.com/Dynamics

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.

 

[PETEHO]

Thanks for that info I will have a go. I don't have the software discs to load SDK. Its the world of cost cutting. We are merging two compnies accounts accross from GP to Sage. We have 100 companies on Sage and two on GP. Sage won hands down as it needs doing by July 1st

Thanks

Pete

 

[VictoriaYudin]

Here is the code for getting the historical GL transactions:

SELECT t.JRNENTRY, g.ACTNUMST, t.DEBITAMT, t.CRDTAMNT, t.SOURCDOC, t.REFRENCE, t.DSCRIPTN, t.TRXDATE
FROM GL30000 t INNER JOIN GL00105 g ON t.ACTINDX = g.ACTINDX
WHERE (t.SOURCDOC NOT IN ('BBF', 'P/L'))

This will give you the detail for the closed years. Of course, you should double check this to make sure it's giving you the expected results, as you may have modifications that would require some changes. If this works, to get the same data for open years, change GL30000 to GL20000.

Hope that helps.

Victoria Yudin
Dynamics GP MVP
Flexible Solutions - home of GP Reports

http://www.flex-solutions.com/gpreports.html