--- title: "Extracting GL Transaction History from Dynamics GP: SQL Guide" slug: extracting-gl-transaction-history-from-dynamics-gp-sql-guide date: 2026-06-24 author: Raaj categories: [Microsoft Dynamics GP] excerpt: "A SQL reference guide for extracting complete GL transaction history from Dynamics GP — covering table architecture, year-end close handling, AA dimensions, and multicurrency." tldr: "GP splits GL data across GL10000 (work), GL20000 (open year), and GL30000 (history). Always UNION ALL them together and filter out BBF/P-L entries to avoid double-counting balances." canonical: https://clonepartner.com/blog/extracting-gl-transaction-history-from-dynamics-gp-sql-guide/ --- # Extracting GL Transaction History from Dynamics GP: SQL Guide Dynamics GP stores general ledger data across multiple SQL Server tables, split by transaction lifecycle stage. If you query only one table, you get an incomplete picture. A full GL extraction requires combining **GL10000/GL10001** (unposted work), **GL20000** (open year posted), and **GL30000** (historical year posted) using `UNION ALL` — and filtering out the synthetic year-end closing entries that GP auto-generates. That table split is why so many GP exports miss lines or double-count balances. If you treat this as a basic CSV dump, you will duplicate balances, orphan your Analytical Accounting dimensions, and fail your target ERP's trial balance reconciliation. With [Dynamics GP end of life approaching](https://clonepartner.com/blog/blog/dynamics-gp-end-of-life-20252031-timeline-migration-plan/), this is the most frequently requested data extraction for both migration and long-term archival. This guide covers the exact table architecture, SQL extraction patterns, year-end close gotchas, Analytical Accounting joins, multicurrency handling, intercompany extraction, and the common tool limitations we see on every GP migration project. ## The Dynamics GP GL Table Architecture GP fragments its general ledger across tables organized by transaction state. Understanding this split is the single most important prerequisite for any extraction. | Table | Physical Name | What It Holds | |---|---|---| | **Account Master** | GL00100 | Chart of accounts: description (ACTDESCR), posting type, account type, user-defined fields | | **Account Index Master** | GL00105 | Concatenated account number string (ACTNUMST) linked to ACTINDX | | **Account Category Master** | GL00102 | Account category number and description | | **Segment Description Master** | GL40200 | Descriptions for each account segment — stored separately from GL00100 | | **Work (Unposted) Header** | GL10000 | Unposted transaction headers | | **Work (Unposted) Detail** | GL10001 | Unposted transaction distribution lines | | **Open Year Posted Transactions** | GL20000 | All posted GL distributions for fiscal years not yet year-end closed | | **Historical Year Transactions** | GL30000 | All posted GL distributions for fiscal years that have been year-end closed | | **Open Year Summary** | GL10110 | Period-level summary balances (debits, credits, net change) for open years | | **Historical Year Summary** | GL10111 | Period-level summary balances for closed years | | **Budget Master** | GL00200 / GL00201 | Budget header and detail records | | **GL Setup** | GL40000 | General Ledger setup — **not** transaction data | | **Multicurrency Setup** | MC40000 | Company-level multicurrency configuration including functional currency | The key relationship connecting everything: **ACTINDX**. This integer index links transactions in GL20000/GL30000 back to the account string in GL00105 and the account metadata in GL00100. Transaction tables carry ACTINDX, not the formatted account string users expect. You must join GL00105 to resolve the human-readable ACTNUMST. > [!WARNING] > **Do not confuse GL30000 with GL40000.** GL30000 is posted transaction history. GL40000 is General Ledger Setup. If a script or consultant points you at GL40000 for detailed GL activity, the model is wrong before the query even runs. ([learn.microsoft.com](https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/copy-setup-tables-from-one-company-to-another)) > [!NOTE] > **Segment descriptions live in GL40200, not GL00100.** GP stores the account description (e.g., "Office Supplies") in GL00100.ACTDESCR, but individual segment descriptions (e.g., what department code "200" means) are in GL40200. If your target ERP uses dimensions (Business Central, NetSuite, Sage Intacct), the GL40200 data maps GP segments to those dimensions. See [Dynamics GP to NetSuite Migration](https://clonepartner.com/blog/blog/dynamics-gp-to-netsuite-migration-the-cto-guide-to-data-integrity/) for a worked example of this mapping. ### Unposted vs. Posted: The Two-Table Split Unposted transactions live in two tables — GL10000 (header) and GL10001 (line detail). Posted transactions collapse into a single table: GL20000 for open years, GL30000 for closed years. Extracting unposted work requires a JOIN between GL10000 and GL10001, while posted transactions are self-contained rows in GL20000/GL30000. ## How the Year-End Close Process Impacts Data Extraction GP's year-end close is the single biggest source of extraction errors. Here is exactly what happens when the routine runs: 1. **All posted transactions move from GL20000 to GL30000.** The year being closed is marked as historical. 2. **Balance Brought Forward (BBF) entries are created** in GL20000 for the new open year, carrying forward balance sheet account balances. 3. **Profit and Loss (P/L) entries are created** that close income and expense accounts to Retained Earnings. 4. **Summary data moves** from GL10110 to GL10111 in parallel. The BBF and P/L entries are synthetic — they are not real business transactions. They exist solely to carry balances forward. If you include them in your extraction and load them into a modern ERP, you will **double-count** every balance sheet account's opening balance and inflate retained earnings. The modern ERP will calculate rolling balances from your historical transaction lines, then add the GP-generated BBF entry on top. > [!CAUTION] > **Always filter out BBF and P/L source documents for migration loads.** Any extraction query against GL20000 or GL30000 must include `WHERE SOURCDOC NOT IN ('BBF','P/L')` to exclude year-end closing entries. This is the #1 cause of out-of-balance trial balances after migration. For archival purposes, keep these entries but label them clearly so controllers can separate operating activity from carry-forward logic. ([learn.microsoft.com](https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/financial-report-do-not-match-gl-trial-balance-report)) The unique record key for GL20000 is the combination of **OPENYEAR + JRNENTRY + SEQNUMBR + RCTRXSEQ**. For GL30000, it is **HSTYEAR + JRNENTRY + SEQNUMBR + RCTRXSEQ**. Journal entry numbers alone are not unique across years. ### Edge Cases That Break Extractions **Period Consolidation.** If anyone has run the Period Consolidation utility in GP (Financial → Routines → Period Consolidation), the detailed transactions for those periods have been permanently removed and replaced with a single net "BF" (Balance Forward) entry per account. This is different from the year-end BBF. If you see BF source documents in your data, those periods have lost their transaction-level detail — it cannot be recovered without a backup. **Late Adjustments.** GP can post one historical year back if Posting to History is enabled. Microsoft notes that posting into the most recent historical year automatically creates a second entry to update current-year beginning balances. A year can be closed and still change. ([learn.microsoft.com](https://learn.microsoft.com/en-GB/previous-versions/troubleshoot/dynamics/gp/year-end-closing-procedures-gl)) **Table location is process-driven, not date-driven.** Which table a fiscal year's data lives in depends on whether the year-end close has been run, not the calendar date. GP supports non-calendar fiscal years, and years can be explicitly marked historical in Fiscal Periods Setup without running the year-end close routine. > [!TIP] > **Check which years live where before writing your query.** Run `SELECT DISTINCT OPENYEAR FROM GL20000 ORDER BY OPENYEAR` and `SELECT DISTINCT HSTYEAR FROM GL30000 ORDER BY HSTYEAR` to see exactly which fiscal years are in each table. On older GP installations with 15+ years of history, GL30000 can contain tens of millions of rows. ## SQL Query: Extracting a Complete GL Transaction History The following query combines unposted, open year, and historical year transactions into a single flat result set. This is the foundation for any migration or archival extract. ```sql -- Complete GL Transaction Extract -- Combines Work (unposted), Open Year, and Historical Year -- Excludes year-end closing entries (BBF, P/L) -- Excludes voided transactions -- Returns functional currency amounts SELECT 'Work' AS Trx_Status, h.TRXDATE, d.JRNENTRY, m.ACTNUMST AS Account_Number, a.ACTDESCR AS Account_Description, d.DEBITAMT, d.CRDTAMNT, d.DSCRIPTN, h.REFRENCE, d.SOURCDOC, d.ORTRXSRC, d.ORMSTRID, d.ORMSTRNM, d.ORDOCNUM, d.CURNCYID FROM GL10000 h INNER JOIN GL10001 d ON h.JRNENTRY = d.JRNENTRY AND h.RCTRXSEQ = d.RCTRXSEQ INNER JOIN GL00105 m ON d.ACTINDX = m.ACTINDX INNER JOIN GL00100 a ON m.ACTINDX = a.ACTINDX WHERE h.VOTEFLAG = 0 -- not voided UNION ALL SELECT 'Open' AS Trx_Status, TRXDATE, JRNENTRY, m.ACTNUMST, a.ACTDESCR, gl.DEBITAMT, gl.CRDTAMNT, gl.DSCRIPTN, gl.REFRENCE, gl.SOURCDOC, gl.ORTRXSRC, gl.ORMSTRID, gl.ORMSTRNM, gl.ORDOCNUM, gl.CURNCYID FROM GL20000 gl INNER JOIN GL00105 m ON gl.ACTINDX = m.ACTINDX INNER JOIN GL00100 a ON m.ACTINDX = a.ACTINDX WHERE gl.SOURCDOC NOT IN ('BBF','P/L') AND gl.VOIDED = 0 UNION ALL SELECT 'History' AS Trx_Status, TRXDATE, JRNENTRY, m.ACTNUMST, a.ACTDESCR, gl.DEBITAMT, gl.CRDTAMNT, gl.DSCRIPTN, gl.REFRENCE, gl.SOURCDOC, gl.ORTRXSRC, gl.ORMSTRID, gl.ORMSTRNM, gl.ORDOCNUM, gl.CURNCYID FROM GL30000 gl INNER JOIN GL00105 m ON gl.ACTINDX = m.ACTINDX INNER JOIN GL00100 a ON m.ACTINDX = a.ACTINDX WHERE gl.SOURCDOC NOT IN ('BBF','P/L') AND gl.VOIDED = 0 ORDER BY TRXDATE, JRNENTRY; ``` To filter for a **specific fiscal year**, add `AND gl.OPENYEAR = 2024` to the GL20000 block and `AND gl.HSTYEAR = 2024` to the GL30000 block. Filter on the fiscal year column, not just TRXDATE — that keeps late historical postings and non-calendar fiscal years from falling through the cracks. If the target system only needs posted data, drop the Work branch. If you need an archive that mirrors what users can still see in GP, keep it. ### Key Fields in the Extract - **SOURCDOC** — Two-character audit trail code identifying the transaction's origin (GJ = General Journal, PMTRX = Payables, RMCSH = Receivables Cash Receipt, etc.) - **ORTRXSRC** — Originating transaction source; identifies the module and serves as GP's audit trail code - **ORMSTRID / ORMSTRNM** — Originating master ID and name (vendor ID, customer ID) - **ORDOCNUM** — Originating document number from the subledger - **DSCRIPTN** — Line-level description, often contains payee or memo text These fields are your audit trail. Every migration target needs at minimum the journal entry number, date, account, amount, and a reference back to the source document. Losing the SOURCDOC or ORDOCNUM means losing the ability to trace a GL entry back to its originating invoice or payment. ### Validating the Extract You cannot assume the data is ready for migration just because the query runs. Generate a Detailed Trial Balance report in GP for a specific historical period. Sum the debits and credits in your SQL extract for that exact date range. If the numbers do not match to the penny, you likely have: - Custom third-party ISV modules writing directly to the GL - A corrupted GL00105 index table - A ledger ID mismatch (reporting ledger entries pulled into the base extract) - Unfiltered BBF/P/L entries Reconcile against at least three fiscal years before declaring the extraction complete. Audit your GP environment for custom tables and triggers before running production extracts — see [How to Audit Dynamics GP Customizations Before Migration](https://clonepartner.com/blog/blog/how-to-audit-dynamics-gp-customizations-before-migration/) for a systematic approach. ## Extracting Period Balances for Trial Balance Reconstruction If you need account balances by period rather than individual transaction detail — for example, to load summary opening balances into a new ERP — use the summary tables: ```sql -- Trial Balance by Period: Open + Historical Years SELECT m.ACTNUMST AS Account_Number, a.ACTDESCR AS Account_Description, s.YEAR1 AS Fiscal_Year, s.PERIODID AS Period, s.DEBITAMT AS Period_Debits, s.CRDTAMNT AS Period_Credits, s.PERDBLNC AS Period_Net_Change FROM GL10110 s -- Open year summary INNER JOIN GL00105 m ON s.ACTINDX = m.ACTINDX INNER JOIN GL00100 a ON m.ACTINDX = a.ACTINDX WHERE s.PERIODID > 0 -- Exclude period 0 (BBF) UNION ALL SELECT m.ACTNUMST, a.ACTDESCR, s.YEAR1, s.PERIODID, s.DEBITAMT, s.CRDTAMNT, s.PERDBLNC FROM GL10111 s -- Historical year summary INNER JOIN GL00105 m ON s.ACTINDX = m.ACTINDX INNER JOIN GL00100 a ON m.ACTINDX = a.ACTINDX WHERE s.PERIODID > 0 ORDER BY Fiscal_Year, Period, Account_Number; ``` **Period 0 in GL10110/GL10111 holds the Balance Brought Forward amount** — the opening balance for the year. If you need ending balances, calculate a running sum of PERDBLNC from Period 0 through the target period. Accounts with no activity in a given period will not have a row in the summary table — this is how GP works, not a bug. Use a `LEFT OUTER JOIN` from GL00100 if you want every account represented for every period. ## Extracting Analytical Accounting (AA) Dimensions If your organization uses Analytical Accounting to track dimensions (Cost Center, Project, Fund) without expanding the core chart of accounts, your extraction complexity doubles. AA data is **not stored in the main GL tables**. It lives in a parallel set of tables that mirror the GL's Work/Open/History split: | AA Table Series | Corresponds To | Content | |---|---|---| | AAG10000–AAG10003 | GL10000/GL10001 (Work) | Unposted AA data | | AAG30000–AAG30003 | GL20000 (Open Year) | Posted open year AA data | | AAG40000–AAG40003 | GL30000 (History) | Historical year AA data | Each series follows a four-table hierarchy: - **AAG_0000** (Header) — Transaction-level: JRNENTRY, posted date, ledger ID - **AAG_0001** (Distribution) — Account distribution lines with amounts - **AAG_0002** (Assignment) — AA-specific amount splits per distribution - **AAG_0003** (Code) — Dimension code assignments per assignment The **aaGLHdrID** field connects records within each series. The **JRNENTRY** field connects AA headers to the corresponding GL transaction. To extract open year GL transactions with their AA dimension codes: ```sql SELECT ah.YEAR1, ah.JRNENTRY, m.ACTNUMST AS Account_Number, ad.DEBITAMT AS AA_Debit, ad.CRDTAMNT AS AA_Credit, dim.aaTrxDimDescr AS Dimension_Name, code.aaTrxDimCode AS Dimension_Code, code.aaTrxDimCodeDescr AS Code_Description FROM AAG30000 ah INNER JOIN AAG30001 ad ON ah.aaGLHdrID = ad.aaGLHdrID INNER JOIN AAG30002 aa ON ad.aaGLHdrID = aa.aaGLHdrID AND ad.aaGLDistID = aa.aaGLDistID INNER JOIN AAG30003 ac ON aa.aaGLHdrID = ac.aaGLHdrID AND aa.aaGLDistID = ac.aaGLDistID AND aa.aaGLAssignID = ac.aaGLAssignID INNER JOIN AAG00401 code ON ac.aaTrxDimID = code.aaTrxDimID AND ac.aaTrxCodeID = code.aaTrxDimCodeID INNER JOIN AAG00400 dim ON code.aaTrxDimID = dim.aaTrxDimID INNER JOIN GL00105 m ON ad.ACTINDX = m.ACTINDX WHERE ah.SOURCDOC NOT IN ('BBF','P/L'); ``` For historical data, replace the AAG30000 series with AAG40000 series and adjust the join fields accordingly. > [!WARNING] > **AA data must align with GL data by year.** The same fiscal years should exist in AAG30000 as in GL20000, and in AAG40000 as in GL30000. Run `SELECT DISTINCT YEAR1 FROM AAG30000` and `SELECT DISTINCT YEAR1 FROM AAG40000` to verify. If years overlap between the AA open and history tables, you have a data integrity issue that must be resolved before extraction. During year-end close, AA data moves from AAG30000 to AAG40000 automatically — there is no separate AA close procedure. If AA dimensions are marked for "Include in year end close," BBF entries are also created in the AA tables and must be filtered the same way. > [!CAUTION] > **AA and GL can diverge.** If AA was not installed on every posting workstation, users disabled it, or someone imported directly into GL tables, GL and AA records will not match. Microsoft explicitly documents these failure modes. Reconcile AA versus GL years before trusting any AA-based extraction. ([learn.microsoft.com](https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/financial-report-do-not-match-gl-trial-balance-report)) To correctly map AA dimensions to a modern system — a critical step whether you are targeting [Business Central](https://clonepartner.com/blog/blog/dynamics-gp-to-business-central-migrating-20-years-of-history/), [NetSuite](https://clonepartner.com/blog/blog/dynamics-gp-to-netsuite-migration-the-cto-guide-to-data-integrity/), or [Sage Intacct](https://clonepartner.com/blog/blog/dynamics-gp-to-sage-intacct-migration-data-mapping-timeline/) — you must extract these dimension codes alongside GL data and map them to the target ERP's dimensional structure. > [!NOTE] > **Performance note:** Running multi-table joins across millions of historical GL and AA records will cause heavy locking on your SQL Server. Run extraction scripts during off-hours, or preferably against a restored backup of the GP database rather than the live production environment. ## Handling Multicurrency and Exchange Rates In multicurrency GP companies, every GL transaction stores both **functional** (home) currency amounts and **originating** currency amounts: - `DEBITAMT` / `CRDTAMNT` — Functional currency amounts - `ORDBTAMT` / `ORCRDAMT` — Originating currency amounts - `CURNCYID` — The originating currency ID - `XCHGRATE` — The exchange rate applied - `RATEFPID` — The rate type ID The company's functional currency is defined in **MC40000** (Multicurrency Setup). GP will not let you change the functional currency once transactions have been posted. Exchange rate tables are stored in the DYNAMICS system database in MC40200 and MC40300. ([learn.microsoft.com](https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/copy-setup-tables-from-one-company-to-another)) ```sql -- Multicurrency GL extract showing both amount sets SELECT gl.JRNENTRY, gl.TRXDATE, m.ACTNUMST, gl.DEBITAMT AS Functional_Debit, gl.CRDTAMNT AS Functional_Credit, gl.ORDBTAMT AS Originating_Debit, gl.ORCRDAMT AS Originating_Credit, gl.CURNCYID AS Originating_Currency, gl.XCHGRATE AS Exchange_Rate, gl.SOURCDOC FROM GL20000 gl INNER JOIN GL00105 m ON gl.ACTINDX = m.ACTINDX WHERE gl.SOURCDOC NOT IN ('BBF','P/L') AND gl.ORDBTAMT + gl.ORCRDAMT > 0; -- Only rows with originating amounts ``` In single-currency companies, ORDBTAMT and ORCRDAMT are typically zero. If Multicurrency Management is not registered, MC40000 may still exist but will not contain meaningful configuration. For migration, extract both sets of amounts and let the target ERP determine which to use. Do not assume you can reconstruct historical FX context later — export currency ID, originating amounts, functional amounts, and any available rate metadata together. That said, when migrating to a modern ERP, attempting to push originating currency amounts for historical journal entries forces the new system to apply its own historical exchange rates, which will almost certainly cause rounding variances and break your trial balance. For historical GL, migrating **functional currency only** is usually the safer path. ## Intercompany Transactions Across GP Company Databases GP stores each company in a **separate SQL database**. Intercompany transactions post to both the originating and destination company databases. There is no single cross-company GL table. To inventory your GP company databases: ```sql SELECT INTERID AS company_db, CMPNYNAM AS company_name FROM DYNAMICS..SY01500; ``` To extract intercompany GL detail, query each company database separately and stamp every row with a company identifier before reconciliation. The intercompany relationship uses the ICTRXSRC field in GL20000/GL30000 and the originating document number. Journal entry numbers are not unique across company databases — you need company context on every row. For more than a handful of companies, generate the `UNION ALL` dynamically from SY01500. For migration, extract per company and reconcile intercompany balances in a staging layer before loading into the target. ## Common Gotchas: What Breaks During GP GL Extraction **Fiscal period setup varies by company.** GP supports non-calendar fiscal years (e.g., February–January). The SY40100 and SY40101 tables in each company database define the fiscal period calendar. A "2024" year in GP may not align with calendar year 2024. **Ledger IDs matter.** If the company uses reporting ledgers or IFRS ledgers, GL20000 and GL30000 contain records for multiple ledger IDs. The Ledger_ID field indicates which ledger a row belongs to, and GL40001 maps Ledger_IDs to descriptions. Filter on the correct Ledger_ID or you will pull reporting entries into your base ledger extract. **Voided transactions stay in the table.** GP does not delete voided transactions. They remain in GL20000/GL30000 with VOIDED = 1. Always filter `WHERE VOIDED = 0` unless you specifically need the void history. **MDA (Multidimensional Analysis) is separate from AA.** If the company uses MDA instead of (or alongside) Analytical Accounting, that data lives in DTA10100 (Transaction Groups) and DTA10200 (Transaction Codes). These are distinct from the AAG tables. **Transaction history must be maintained.** If "Maintain Transaction History" was not enabled in GP, historical detail may not exist in GL30000 the way your project assumes. Confirm this setting before planning your extraction. ([learn.microsoft.com](https://learn.microsoft.com/en-GB/previous-versions/troubleshoot/dynamics/gp/year-end-closing-procedures-gl)) **GL10110/GL10111 may not have rows for every account-period.** If an account has no activity in a period, no row exists. Your trial balance reconstruction must account for this by starting from the full account list in GL00100 and using outer joins. **Third-party ISV data is invisible to standard GL queries.** Products like Binary Stream's Multi-Entity Management, Fastpath Audit Trail, or Mekorma add custom tables that extend GL data. If any ISV products touch your GL, check for additional tables before declaring your extraction complete. ## Why Standard Migration Tools Fall Short on GP GL History **Microsoft's Business Central Cloud Migration Tool** creates GL account summary transactions for open and historical years based on GP fiscal periods. It can also move optional GP Historical Snapshot data into Business Central extension tables. But it does not migrate historical GL detail from GL30000 in a way that preserves full audit-trail traceability — it is designed for cutover, not for archiving 15 years of transaction history. It also struggles with heavy Analytical Accounting configurations and does not allow granular transformation of segmented accounts into modern dimensions during the flight. See [Dynamics GP to Business Central: Migrating 20 Years of History](https://clonepartner.com/blog/blog/dynamics-gp-to-business-central-migrating-20-years-of-history/) for a deep dive on this limitation. ([learn.microsoft.com](https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/migrate-dynamics-gp)) **SSIS / KingswaySoft** packages give you full SQL access and can extract anything. But they require significant development effort to handle GP-specific logic — BBF filtering, AA joins, multicurrency field mapping, cross-company queries — and provide no built-in validation. On databases with 10M+ rows in GL30000, SSIS package performance degrades without careful batching and indexing. **eOne Solutions (Popdock)** virtualizes access to GP history through an Azure Data Lake rather than migrating the records. This works for read-only access scenarios but does not solve the migration problem — your data still lives in the legacy structure, and you pay for the integration layer indefinitely. The gap in every tool-based approach: **none of them handle the semantic translation** from GP's segmented account structure to the target ERP's dimensional model. That mapping — GP segment 2 becomes a department dimension, segment 3 becomes a cost center — requires domain knowledge of both systems. No tool automates that correctly without human review. ## What to Do Next Start with the diagnostic queries: check which years are in GL20000 vs. GL30000, verify AA table year alignment, confirm transaction history was maintained, and count row volumes. If GL30000 has over 5 million rows, plan for a phased extraction. If you find Period Consolidation BF entries, document which periods have lost detail — that data is unrecoverable without a backup. Run the UNION ALL extract against a restored test database. Compare the resulting trial balance against GP's native Trial Balance report for at least three fiscal years. Any variance means you have a filtering issue, a ledger ID mismatch, or a year-end close anomaly that needs investigation. Our process at ClonePartner follows these same patterns. Every GP migration we run starts with direct SQL extraction against the company database — not eConnect, SmartList exports, or GP's built-in export tools, which are too slow for the volume and too limited in scope. We extract GL detail, AA dimensions, multicurrency amounts, budget data, and subledger cross-references in a single coordinated pass. Automated BBF/P-L filtering with per-period reconciliation against GP's own Trial Balance report is built into every engagement. For the broader migration context — how this GL extraction fits into a full GP-to-target migration — see our guides for [Dynamics GP to Acumatica](https://clonepartner.com/blog/blog/dynamics-gp-to-acumatica-migration-a-practical-guide/), [GP to Sage Intacct](https://clonepartner.com/blog/blog/dynamics-gp-to-sage-intacct-migration-data-mapping-timeline/), and [GP to QuickBooks Enterprise](https://clonepartner.com/blog/blog/dynamics-gp-to-quickbooks-enterprise-the-cto-migration-guide/). If you are planning a migration off Dynamics GP and need to extract decades of GL history with full audit-trail integrity, this is exactly what we do. > ClonePartner has extracted GL data from hundreds of GP databases — some with 20+ years of history and tens of millions of transactions. If you need a clean, reconciled GL extract for migration or archival, book a 30-minute call and we will scope it with you. > > [Talk to us](https://cal.com/clonepartner/meet?duration=30) ## Frequently asked questions ### Which SQL tables store GL transactions in Dynamics GP? GL10000/GL10001 store unposted (work) transactions. GL20000 stores posted transactions for open (unclosed) fiscal years. GL30000 stores posted transactions for years that have been year-end closed. You need to UNION ALL these tables for a complete GL extract. ### How do I exclude year-end closing entries from a Dynamics GP SQL extract? Add WHERE SOURCDOC NOT IN ('BBF','P/L') to your GL20000 and GL30000 queries. BBF (Balance Brought Forward) and P/L entries are synthetic transactions created by the year-end close and will double-count balances if included in a migration load. ### Where is Analytical Accounting data stored in Dynamics GP SQL? AA data is in the AAG30000 series (open year) and AAG40000 series (history), not in the main GL tables. Each series has four tables: header (AAG_0000), distribution (AAG_0001), assignment (AAG_0002), and code (AAG_0003). Join to GL via the JRNENTRY field. ### How does the Dynamics GP year-end close affect which SQL table has my data? The year-end close moves all posted transactions from GL20000 to GL30000 and creates BBF entries in GL20000 for the new year. Which table a fiscal year's data lives in depends on whether the close has been run, not the calendar date. ### Is GL40000 a transaction history table in Dynamics GP? No. GL40000 is General Ledger Setup. Historical posted GL detail is in GL30000. If a script or consultant references GL40000 for transaction data, the approach is wrong.