Resolving Dynamics GP General Ledger Account Indexes to Account Numbers in SQL Queries

Many records in Dynamics GP contain references to accounts. As an example, master records like Items, Customers, Vendors, Fixed Assets have default posting accounts. If you use tools like SQL Server Reporting Services (SSRS) and / or SQL Server Management Studio (SSMS) you have probably noticed values for Account Numbers in most GP Tables are stored as account indexes (in many tables ACTINDX).

Since Dynamics GP accounts can be up to 66 characters long with 10 segments, developers saved precious space by storing account indexes rather than the full Account Number in most tables.  The challenge of presenting the actual account number in queries and reports where tables contain only account indexes requires a solution. Enter the GL00105 Table (Account Index Master). This table contains the Account Index, Segments and full Account Numbers.

Using this table and Left Joins, it is possible to display the Account Number, where only an Account Index exists. The following query is an example of a complicated query meant to demonstrate the usage of joins in concert with the Account Index Master table (GL00105). This query returns the default posting accounts, and alternate posting accounts for Vendors in Dynamics GP.

Select

V.VENDORID Vendor_ID,

V.VENDNAME Vendor_Name,

PMAP.ACTNUMST AP,

PMCSH.ACTNUMST Cash,

PMDAV.ACTNUMST Discounts_Avail,

PMDTK.ACTNUMST Discounts_Taken,

PMFIN.ACTNUMST Finance_Charges,

PMMSCH.ACTNUMST Miscellaneous,

PMFRT.ACTNUMST Freight,

PMTAX.ACTNUMST Tax,

PMWRT.ACTNUMST Writeoffs,

PMPRCH.ACTNUMST Purchases,

PMTDSC.ACTNUMST Trade_Discount,

ACPUR.ACTNUMST Accrued_Purchases,

PURPV.ACTNUMST Purchase_Price_Variance,

GLD.ACTNUMST Alternate_Payables_Acct

from PM00200 V

LEFT join PM00203 PA on V.VENDORID = PA.VENDORID

Left join GL00105 PMAP on PMAP.ACTINDX = V.PMAPINDX

Left Join GL00105 PMCSH on PMCSH.ACTINDX = V.PMCSHIDX

Left Join GL00105 PMDAV on PMDAV.ACTINDX = V.PMDAVIDX

Left Join GL00105 PMDTK on PMDTK.ACTINDX = V.PMDTKIDX

Left Join GL00105 PMFIN on PMFIN.ACTINDX = V.PMFINIDX

Left Join GL00105 PMMSCH on PMMSCH.ACTINDX = V.PMMSCHIX

Left Join GL00105 PMFRT on PMFRT.ACTINDX = V.PMFRTIDX

Left Join GL00105 PMTAX on PMTAX.ACTINDX = V.PMTAXIDX

Left Join GL00105 PMWRT on PMWRT.ACTINDX = V.PMWRTIDX

Left Join GL00105 PMPRCH on PMPRCH.ACTINDX = V.PMPRCHIX

Left Join GL00105 PMTDSC on PMTDSC.ACTINDX = V.PMTDSCIX

Left Join GL00105 ACPUR on ACPUR.ACTINDX = V.ACPURIDX

Left Join GL00105 PURPV on PURPV.ACTINDX = V.PURPVIDX

Left Join GL00105 GLD on PA.ACTINDX = GLD.ACTINDX