Archive for the ‘Great Plains’ Category
SQL Script for Budgets vs. Actuals in Amounts in Analytical Accounting – By Dimension Code
Hi all Today, I am posting a script for analyzing Budgets vs. Actuals (using Amounts) in Analytical Accounting. However, there are two methods of analyzing the budget information in Analytical Accounting. They are: Node Level Dimension Code Level This script is for viewing Budgets vs. Actuals at Dimension Code level. I will be posting the [...]
June 16, 2011 · 
veeyeskay · 
5 Comments
Tags: Analytical Accounting Budgets, Budgets vs. Actuals, Dimension Code Level · Posted in: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 629
Analytical Accounting Information Flow from Purchase Order to Receipts
Hi all This article is to explain how Analytical Accounting (AA) information is transferred from Purchase Orders to Receiving Transaction Entry. The scenario explained below is on an environment where there is one transaction dimension configured in Analytical Accounting. Create a Purchase Order with 2 line items as explained below. PO Number Line Seq. No [...]
June 2, 2011 · veeyeskay · One Comment
Tags: assigned percentage, purchase-order, receipt, Transaction Dimensions · Posted in: Analytical Accounting, Dynamics, Great Plains, Purchase Order Processing Total Views: 310
SQL Script for POP Receiving Transactions with AA Information
Hi all My last post had a script for Purchase Orders with AA information. Today, I am posting a script for the following POP transactions with AA information. Shipments Shipment/Invoice Invoice IF EXISTS ( SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N’[dbo].[vw_POPRcptInformation]‘) ) DROP VIEW [dbo].[vw_POPRcptInformation] GO S Read the rest of this entry
May 20, 2011 · veeyeskay · 3 Comments
Tags: Dimension Codes, Receiving Transaction Entry, shipment/invoice · Posted in: Analytical Accounting, Dynamics, Great Plains, Purchase Order Processing, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 237
Dynamics GP Technical Database Guides – GP 10
Hi all In this article, I have posted the link of a detailed database reference guide for Microsoft Dynamics GP 10. I have posted the link to the guide for the system database (DYNAMICS) and the sample company (TWO) database. This guide provides detailed specifications of the various tables, views and stored procedures present in [...]
May 18, 2011 · veeyeskay · 2 Comments
Tags: Database Guide, DYNAMICS database, TWO database · Posted in: Documentation, Dynamics, Great Plains, SQL Server, SQL Server 2005 Total Views: 230
Table Structure Definitions – Fiscal Period Setup
Hi all This is the second article that I have decided to post in the table structure definition series. This time, I am going to explain the table structure of the Fiscal Period Setup (SY40100) table in Dynamics GP. The structure of the table is as follows: Column Name SQL Column Name Data Type Length [...]
May 17, 2011 · veeyeskay · 3 Comments
Tags: Fiscal Period Setup, fiscal-periods, SY40100 · Posted in: Dynamics, Fiscal Periods, Great Plains, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 351
Updated SQL Script for fixing Primary Key Errors in Analytical Accounting
Hi all I had posted an article in my blog some time back which updates the indexes in the AAG00102 table to fix the primary keys in analytical accounting. This article can be viewed here. Even though that was useful to update the account indexes in all companies at once to synchronize it, it does [...]
May 15, 2011 · veeyeskay · 5 Comments
Tags: Analytical Accounting, Primary Key Error · Posted in: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 432
SQL View for Purchase Order Details with AA Information
Hi all Based on a request from a community user on my blog, I have decided to post these SQL Views for fetching the purchase order information with analytical accounting information. IF EXISTS ( SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N’[dbo].[vw_POInformation]‘) ) DROP VIEW [dbo].[vw_POInformation] GO SET ANSI_NULLS ON GO SET QU Read [...]
May 9, 2011 · veeyeskay · One Comment
Tags: Dimension Codes, Purchase Order Processing, purchase-order, Transaction Dimensions · Posted in: Analytical Accounting, Dynamics, Great Plains, Purchase Order Processing, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 301
Understanding Voucher Numbers in Receivables & Payables
All of us know that the document number field in the Receivables Transaction Entry and the voucher number field in the Payables Transaction Entry has a maximum of 20 characters. However, on the transaction entry windows, these fields ONLY allow us to enter 17 characters. But the actual table field allows 20 characters. So where [...]
May 8, 2011 · veeyeskay · One Comment
Tags: Document Numbers, Recurring Batches, Voucher Numbers · Posted in: Accounts Payables, Accounts Receivables, Dynamics, Great Plains Total Views: 387
Summary of Transaction Statuses in GP
All transaction in Dynamics GP, goes through 3 statuses before its purged from the system. They are Work Status – Transactions which have not been posted Open Status – Transactions which have been posted Historical Status – Transactions which have moved to history tables Each transaction moves through each of these status in various scenarios, [...]
May 8, 2011 · veeyeskay · No Comments
Tags: Historical Status, Open Status, Transaction Status, Work Status · Posted in: Dynamics, Great Plains, System Total Views: 193
Rebuilding Corrupt Resource Descriptions in Dynamics GP
Hi all Today, I was working with one of my colleague on an issue he faced in using the Resource Description functionality to view table and field information for a few tables. When he was browsing through the table details, he noticed that the actual SQL table definition and the table definition in the Resource [...]
May 6, 2011 · veeyeskay · No Comments
Tags: Corruption of Indexes, Index Rebuild, OLFD001.dat, OLFD001.idx, Resource Descriptions · Posted in: Dynamics, Great Plains, System Total Views: 204