How to determine the first fiscal year in a Dynamics GP Company in SQL

For Dynamics GP users who are familiar with the Fiscal Period setup window, which exists at the company level, it should come as no surprise, rapidly identifying the First year an entity was established can be a time-consuming endeavor. 

To do this manually, one would need to go to Tools > Setup > Company > Fiscal Periods, and select the Year from the drop down list, in order to see the oldest year in each company in Dynamics GP. For organizations with numerous entities, especially those who have performed such an exercise, they know manually performing this task is daunting.

Users have to keep track of where they are in the process, make a list, validate the list, etc. Alternatively, the following query has proven useful in identifying the first year in Fiscal Period setup, in each of the organization's entities.

select 'Fabrikam' Company_Name, MIN(YEAR1) First_Year from TWO..sy40101

UNION 

select 'Company 1' Company_Name, MIN(YEAR1) First_Year from CO1..sy40101

UNION

select 'Company 2' Company_Name, MIN(YEAR1) First_Year from CO2..sy40101

It is important to note, the Company Name must be manually updated in the query above, and the Entity/Database ID must also be edited.

How to identify the SQL Database from the corresponding Company Name in Dynamics GP

Since SQL database names are restricted to 5 characters by the Dynamics GP Company Creation utility, SQL database names tend to be far less instructive than the Company Name, which appears in the drop down list during the company selection process at login.

In order to ensure your technical resources understand the relationship between these two data points, it is important they understand where this corollary can be determined in SQL. The following query can be run on demand by technical resources:

SELECT 

INTERID 'Database',

CMPNYNAM 'Company_Name' 

FROM DYNAMICS..SY01500

This query will display a list of company databases and the corresponding company names, so when the finance department asks for a backup or restore operation to be completed for a particular company, mistakes can be avoided. See image one below.

Image 1 - Company Query and Results

I will not run a SQL Update query without a backup

I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup
I will not run a SQL Update query without a backup

Resetting Pending Standard Cost Changes in Dynamics GP Manufacturing.

Awhile back, a dynamics community post posed an interesting question:

Running 2013 SP2 with Manufacturing.  In process of setting up to use Standard Costing updating after it not being maintained in 4 years.

Issue is we have thousands of pending changes in table ICIV0323 that need to be cleared out. Additionally, many have an Override set. I understand the interaction with CT00003 and the 3 windows controlling entries of pending, proposed, and override updates.

Not sure of proper fields to clear in ICIV0323 and what is the relationship between the various effective dates and the current cost effective date field "_1".

Here is a link to the post:

https://community.dynamics.com/gp/f/32/p/137897/303850#303850

I have since seen a number of posts regarding this same issue, so a blog post seemed like a good idea. So, let's get down to it. There are two tables, CT00003 and ICIV0323 in Dynamics GP, which contain pending standard cost changes. 

The Standard Cost Maintenance Window displays data from the ICIV0323 table. See image one below.

Image 1 - Standard Cost Maintenance Window

The Standard Cost Changes Window displays data from the CT00003 table. See image two below.

Image 2 - Standard Cost Changes Window

Attached is an image, which shows the relationships of the windows above to the tables, which display their data. See image three below.

Image 3 - SQL Query on CT00003 and ICIV0323 Tables

If you were to take the following Knowledge Base article at its word, you would believe clearing pending standard costs was a simple matter.

http://support.microsoft.com/kb/883471

Part of the article is correct, in that table CT00003 can be quickly cleaned up by clicking on the Delete All button, which will confirm you want to delete all pending standard cost changes (just click yes). 

On the other hand table ICIV0323 cannot be rapidly cleaned up. If you write a macro for the window, you can check the Override boxes for any pending standard cost tables, and replace them with the original values, and the same effective date, and you will Override these recommended changes when you Roll and Revalue. See image four below.

There have been multiple reports of users rebuilding standard costs using SQL. Likely, folks rebuilding standard costs, are updating the MATCOSTI_# fields with 0.00000 and MATCOSTEFFDATEI_# fields with 1900-01-01 00:00:00.000. 

Since it is possible to use a Macro in the Standard Cost Maintenance window, I am reluctant to recommend someone use a SQL script to rebuild pending standard costs (reset to zero) in the ICIV0323 table.

In the end, the choice is up to you; however, should you proceed with either approach to the problem at hand, the first step would be the same. This is as ever, make a back up! The second step is to refresh your test company and test the fix there, prior to implementing it in your live database.

I believe Microsoft might also be of assistance, should you desire to pursue a fix using SQL, based on some of the posts I have reviewed online.

Batch Stuck in Dynamics GP

In all financial systems, Dynamics GP included, from time-to-time batches will get stuck during posting. I have experienced this problem in every financial system I have supported, and I've supported a few. In most systems, this problem is something you need to contact technical support to resolve.

A few versions ago, Microsoft included a feature in Dynamics GP meant to manage this issue at the user level - very cool. The tool is known as the Batch Recovery Routine. Accessed in Dynamics GP by clicking on Tools > Routines > Batch Recovery -- see image one below

Image 1 - Batch Recovery Routine Navigation

When a batch is not balanced, a computer or network connection goes to sleep, or someone trips over a power cord...  a batch gets stuck in posting. When this happens, the first step in resolving the problem is to access the Batch Recovery window -- see image two below.

Image 2 - Batch Recovery window

From the Batch Recovery window, stuck batches can be reviewed, and steps can be taken to resolve the problem(s) preventing posting. Selecting the batch and clicking continue will either continue the posting process or push the batch back to the Batch Entry window for editing. If the batch cannot be opened for editing, and displays any number of errors about being edited by other users, or selected for posting, etc., more finesse will be required. 


The next step in the troubleshooting process is to get everyone out of Dynamics GP, then view the contents of the following tables to verify that all users are logged off: DYNAMICS..ACTIVITY, DYNAMICS..SY00800, DYNAMICS..SY00801, TEMPDB..DEX_LOCK, and TEMPDB..DEX_SESSION.  

This is important, because sometimes, there really is another user locking the batch, and in other cases the lock is a glitch/remnant of a connected user.  Run the following queries in SQL query analyzer (SQL Server Management Studio) - the queries call out the databases they access.

SELECT * FROM DYNAMICS..ACTIVITY
SELECT * FROM DYNAMICS..SY00800 
SELECT * FROM DYNAMICS..SY00801 
SELECT * FROM TEMPDB..DEX_LOCK 
SELECT * FROM TEMPDB..DEX_SESSION

Note, when all users are logged off from Microsoft Dynamics GP, these quiries will not produce any results. So, if there are still records in these tables, they need to be removed. To clear the stuck records use all of the following scripts - if a table is empty already, they will do no harm.

DELETE DYNAMICS..ACTIVITY 
DELETE DYNAMICS..SY00800 
DELETE DYNAMICS..SY00801 
DELETE TEMPDB..DEX_LOCK 
DELETE TEMPDB..DEX_SESSION

If no results were returned, then the batch isn't locked, and only needs to be reset in the SY00500 table -- this table exists at the company, not system level. Replace XXX with the batch number or the name of the batch that you are trying to post or select in Microsoft Dynamics GP and run the query below against the company database (when folks get no results here, they typically find they are connected to the master or DYNAMICS database.

UPDATE SY00500 SET MKDTOPST=0, BCHSTTUS=0 where BACHNUMB='XXX'

In order to implement the fixes described above you will require SQL Skills and access to SQL Query Analyzer. If those are foreign concepts, be nice to the nerds in your life. Your GP Partner, Company DBA or Outsourced IT resource will likely be able to run these queries for you.

A bit of a shake up for Dynamics ERP and CRM; Shifting from Applications & Services Group to Cloud & Enterprise

You heard it hear first, or second or...

ZD Net Article - Microsoft ERP/CRM Strategic Shift

An Ounce of Prevention; Dynamics GP Year-End Close Routine and Misclassified Accounts

Every year, Microsoft, Numerous Bloggers and SBS Group Central California, the place I call home, dust off, and revise our Year-End Closing procedures for Dynamics GP and distribute them far and wide. The purpose is obvious, this procedure happens once a year and things sometimes change, so guidance is a good idea.

Even so, we ultimately see forum entries and receive technical support calls asking what to do if an account, or worse yet, some accounts were misclassified at the time of the Year-End Closing. Let me tell you, if we aren't talking about restoring a back up, the fix for this problem requires some finesse.

The root problem is, typically, at some time during the year, a user or users have quickly created a new expense account, or again, worse yet many expense accounts to post to, and while doing so, they accepted the default Posting Type. Dynamics GP's default Posting Type is Balance Sheet; of course, Expense Accounts should be classified as PNL or Profit and Loss accounts. 

When Dynamics GP Year-End closing procedure is initiated, Balance Sheet Accounts are zeroed and their Year-End Balances are brought forward as the beginning balances for the next year. Profit and Loss accounts are also zeroed, but their balances are cleared to Retained Earnings. When an account is misclassified it is handled incorrectly by Dynamics GP throwing off both the Balance Sheet and Profit and Loss.

There is a preventative measure we implement for our clients, which can prevent this problem from happening. In short, we create SmartLists with a reminder, which let users know when accounts have been misclassified well prior to the Year-End Closing Routine being run. Here is a how-to article on this process.

First, browse to SmartLists > Financial > Accounts and select the default (*) SmartList Favorite. You'll want to add the Main Account Segment to this view, so click on the Columns Button, select Add, then Find Next: Main Account Segment and click Ok. See image one below.

Note: as an aside, it is a really good idea to add the Main Account Segment to Account related SmartLists, as selecting a range of accounts, which include sub-account segments like company, department of division is impossible. Additionally, other segments, like department and division can be added to restrict results for these criteria as well.

Image 1 - Add Main Account Segment

Optional: You may find it useful to move the Main Account Segment to the first position/column in your SmartList. This can be accomplished by Selecting the Main Account Segment in the Change Column Display window, and clicking on the top arrow to the right of the column list. This will promote the Main Account Segment to the first position/column. See image two

Image 2 - Make Main Account Segment First Column in SmartList

Once you have added the Main Segment, you can select the Search button and enter the following criteria:

Main Account Segment > Is Greater Than > 3999

AND

Posting Type > Is Equal To > Balance Sheet

IMPORTANT: This assumes 3999 is your highest Balance Sheet Account. This may not be the case! It is uncommon, but not unheard of for the 3XXX series of accounts to be included in the Profit and Loss category (sometimes as revenue accounts). It is also possible you use more than 4 characters in your main account segment. So, adapt this criteria to your situation!!! Additionally, it is possible to do the reverse of this criteria; below 4000 and Profit and Lost, just to be thorough...

See Image Three below

Image 3 - Search Criteria for Misclassified Accounts

You will see a list of accounts, which are suspect. It is possible, there are accounts in this range which are Balance Sheet accounts (as account numbers are discretionary). So now the real work begins. It is up to the Finance Team to determine which of these accounts truly belong in this range. Once they make the determination, all other accounts should be reclassified. At this point, a tally of the results should be done to determine the number of Balance Sheet accounts are within this range - this will be important when you set your reminder, and when you give instructions to folks who will address the problem of misclassified accounts.

The final step in this process is to save this SmartList as a Favorite, which is accomplished by clicking on the Favorite Button and entering in a unique name for the Favorite. We typically call these Favorites _Misclassified PNL Accounts and _Misclassified Balance Sheet Accounts. Using the underscore brings these lists to the top of the SmartList Favorites list. See image four below

Image 4 - Save Favorite with Reminder

RECOMMENDATION:  For extra credit, it is wise to create these Favorites with Reminder. Doing so will tickle people when there are accounts that meet this criteria. It is possible and recommended to set the reminder at a number greater than the legitimate misclassified accounts, so the reminder only activates when new accounts meet this criteria. This will prevent the reminder from becoming "white noise" and consequently being ignored by users. See image five below

Image 5 - Set the Number of Records Greater than Legitimate Accounts in this range

Now you have your SmartList favorite, and reminder set up, when a new account is created, which meets your criteria, all you need to do is double-click on the problem account, select the correct Posting Type and click save. 

Remember, an ounce of prevention is worth a pound of cure. Once your reminder is set, when the number of valid accounts is exceeded, users will see there are misclassified accounts, which need to be addressed.  See image six below.

Image 6 - Reminder on Home Page

Dynamics GP Stuck Process and Process Monitor

From time to time, I will receive a call from a customer who has posted a transaction, and is deeply concerned that nothing appears to be happening. More often than not, the problem turns out to be a report sent to screen, which was minimized and is blocking further processing.

It is not uncommon for a user who is about to post a transaction in Dynamics GP to print an edit list of the transaction to screen. If the user does not close the window containing the report, all future reports and processes will be held, until it is closed.

Imagine if you will, a user in Finance prints an edit list for a General Ledger transaction in preparation for posting.

Now the same user receives a phone call, and has to open another Dynamics GP window or External Application to answer a question or two, or three, etc. etc. etc. Several minutes pass, and the user returns to Dynamics GP to post the transaction.

Upon posting, nothing appears to happen. The Posting Journal report does not open. The user, being a savvy Dynamics GP user immediately opens the Process Monitor to view the status of the Batch.

The report open on the screen does not show in the Process Monitor window because technically, it has completed processing (technically). This is the first sign an open report printed to screen is the culprit -- the General Posting Journal is the next process in the cue. At this point the user should be looking at the open windows in Dynamics GP in order to find the window causing the hang up.

Once the window is identified, it must be closed. Immediately after the window closed, the processes being held up will continue until completion. In this case the General Posting Journal is printed and the batch posting is completed.

Once the processes complete, the Process Monitor window will be empty, and normal processing in Dynamics GP can be continued.

This type of problem is an infrequent occurrence and has an easy diagnosis and solution; therefore it has not been a priority addressed in recent releases. I for one would like to see the problem rectified, but understand the cascading problem it represents.

A report being run to screen prevents other reports from printing. A posting report being printed is a requirement of the posting process being completed. You see where this is going, right...

My humble suggest would be to enable Dynamics GP to render multiple reports to screen, instead of just one.

Want Free Technical Support for Microsoft Dynamics Products, Join the Dynamics Community Online

Microsoft maintains a number of support options for the Dynamics family of products; CRM, AX, NAV, GP and SL (Customer Relationship Management, Axapta, Navision, Great Plains and Solomon respectively).

Most enhancement plans come bundled with Microsoft Technical Support incidents. For customers current on their enhancement plans there is a searchable Knowledge Base (KB), which is an excellent source of support and how-to information.

There are more blogs, you-tube channels and online forums than can easily be counted, most offering their services free of charge, as-is, no warranties. There are excellent User Groups, and lest we forget the stalwart Partner and Independent Software Vendor channels.

If you are the type of person who starts troubleshooting with a healthy dose of Internet search engine, then you've probably already discovered the Dynamics community sites.

If you have stopped at merely reading content and implementing recommendations, then you are missing out on a resource of limitless potential. 

Every day, some of the brightest minds in the Dynamics community answer questions posed to them by Dynamics customers and partners who pay nothing for the service. These questions range wildly from the simple to the complex, and typically result in numerous suggestions/options to help resolve the issue at hand in a timely fashion.

If you haven't joined the Community, please consider doing so. You can find out more, and sign up at the following link:

https://community.dynamics.com/

You do not have permission to revalue inventory

Recently, there was a forum post that caught my attention. Mainly because I have previously posted a blog article to address a similar security issue with the Dynamics GP Manufacturing Series. Here is a link to the original post.

You do not have access privileges needed to change this quantity

When I read the following post below, I had to resist the urge to send a link to the post above, as though the issues are related, they are not the same issue.

https://community.dynamics.com/gp/f/32/t/163299

Cudos to Dynamics Community forum user ELEAZER for writing such a cogent post, and putting in the work on the Dynamics GP Security setting side.

So I have this weird situation I know that to give a user security access for the Replace Cost button in Standard Cost Changes I would have to give the user the MFG ADMIN* and that will enable the feature. 

Image 1 - Standard Cost Changes; Replace Costs Button

After giving the role to a couple users the button was no longer grayed out as you see in the picture above, but they cannot Replace Costs.The person who was performing this task is the only person who can Replace Costs with no issues. In addition, the first thing I did was give these user the same security as the employee who is able to perform this task and still did not work. Even I tried to performed this task as SA and was denied as well with the following message.

In her post, ELEAZER makes a very good point. The SA user cannot perform the Replace Costs process - Yikes!

In Dynamics GP, as a general rule, a user assigned the POWERUSER role or the SA account are "all powerful," and can perform any task. What happens when the SA or a POWERUSER tries to Replace Costs in the Standard Costs Changes window and they receive the following error. "You do not have permission to revalue inventory?" 

There is a simple cause and a simple solution to this problem. Dynamics GP Manufacturing was once a third party product (a very long time ago), and a programmer, was likely asked to make sure that a user could not just revalue inventory.  So this programmer created a feature, outside typical GP security setup to control access to the Replace Costs button.

In the end, what was produced is security more akin to Hold Processing or Tolerances in various Financial and Distribution Modules; a security setting that exists at the module configuration level and not at the System Security level.

When the user receives the "You do not have permission to revalue inventory." error, either they have not be granted access, or this feature hasn't been set up at all - it is typical the latter.  If you implement GP Manufacturing, you should add explanation and configuration of this feature to your setup checklist.

The security setting for Revaluation is maintained in Manufacturing > Setup >System Defaults > Costing window

The Costing Preferences Defaults window has a Revaluation Security Set field, located at the bottom of the window. You likely have a Security Set, setup here already, as some users can perform this action.

Clicking on the Revaluation Security Set hyperlink to the left of the field will open the Process Security Setup window, where you can use the User ID's lookup field to add users to this security group.

There is an alternative configuration to use a password, which has the advantage of stopping unwanted changes, and requiring anyone desiring to make such a change to hunt down the person who has the password and explain why they are making the changes.

Configuring based on User ID versus an overall password is a tough decision. Empowering a user means the user can make changes without oversight, and passwords escape into the wild - either way, there are pros and cons.

I hope this post gets you where you're going.

Dynamics GP 2015 Management Reporter Integration Features

With the release of Dynamics GP 2015 the integration of Management Reporter has been significantly expanded. A new Navigation List has been added which allows users to open Management Reporter reports from within Dynamics GP. Management Reporter reports can be added to My Reports, and of course Management Reporter Designer can be opened from within Dynamics GP as well. Image 1 below is a preview of the Navigation List:

Image 1 - Management Reporter Navigation List

To add the Navigation List displayed in Image 1 above, the administrative task of deploying it must be performed in Reporting Tools Setup, which is accessed via the following navigation: Microsoft Dynamics GP > Tools > Setup > System > Reporting Tools Setup (or) Administration > Setup > System > Reporting Tools Setup.

Image 2 - Reporting Tools Setup

The URL for the Management Reporter Service should be the same as the URL entered when installing the Management Reporter Client, including the port number (the default port number is 4712).

The Location of the Report Designer.exe must be consistent, as this setting is maintained in this one location. So, it is best to use the default directory when installing the Management Reporter Client application on user workstations.

You must select the company or companies to deploy the Navigation List to.

Image 3 - Company Selection in Reporting Tools Setup

Finally, after successfully deploying the Navigation List, users, must log out and back into Dynamics GP to view it. 

The tighter integration between Dynamics GP and Management Reporter is a vast improvement over the integration between Dynamics GP and FRx and let's hope a sign of things to come.

Paste General Ledger Transactions from Excel into Dynamics GP 2013 R2 and Later

There were so many features introduced in Dynamics GP 2013 and 2013 R2, it was genuinely hard to keep track of them all. One of these features was the ability to Paste from Excel to the General Ledger Transaction Entry window.

Pasting from Excel to the General Ledger Transaction Entry window is accomplished by clicking on the Paste button (complete with Excel logo), as shown in Image 1 below. The Transaction Entry window navigation is Financial > Transactions > Financial > General, and will look much like the image below. 

NOTE: Your user experience may vary - I have some 3rd party products installed, so my Paste button was small and hidden, yours may very well be front and center.

Image 1

Data cannot be pasted into the Header fields of a General Ledger Transaction - seem Image 2 below. So, it is important these required fields be filled out manually prior to pasting the transaction details in the Transaction Line entry portion of the window.

Image 2

Once the header information has been manually entered, you can select the data from Excel and paste it into the Transaction Line entry section of the Transaction Entry window. It is important to initiate the paste in the first field of the Line Entry section - see Image 3 below.

Image 3

Image 4 below, is a 'Paste from Excel Template'. The headers are simply to help identify what data goes where.  When pasting, only the data needs to be selected and copied.  

NOTE: The first column maps, in my opinion, counter-intuitively to the Distribution Reference (not the Account Description - I have seen some conflicting information online).

Image 4

Once you have your desired General Ledger transaction data is selected in Excel, simply right click and choose Copy from the menu (or use Ctrl + C). Then move to the first field of the Line entry section of the Transaction Entry window (make sure you have already filled in the required header data), and click the paste button, which is located in the Menu Bar or Action Pane in the header (right clicking and pasting will not work). If you are unsure of where to start your paste, refer to Image 3 above. If you can't find the Paste button, refer to Image 1 above.

Image 5

The data should look substantially like Image 5 above. I have collapsed the lines, using the chevron icons to the extreme right of the headers in the line entry section of the window in order to show the matching transaction. Expanding the lines will show the Distribution Reference data was also successfully imported - see Image 6 below.

Image 6

ERRORS & TROUBLESHOOTING

If you have your Excel file format wrong, or selected the headers during the copy process, you will be instantly rewarded with a Validation report indicating what's wrong with your data.

Image 7

In the example displayed in Image 7 above the description column of my Excel template file was pasted into the account field by the utility, which caused a cascading failure of all other data, putting it out of place.

NOTE: The Template format is critical to successfully copying data from Excel. It is also important, not to select the column headers when copying, as they are unnecessary, and will throw the import off. It is also unnecessary to have dollar values in every import field; therefore, zero dollar values should not be entered in the debit or credit columns. Do not select lines below your data set, creating blank rows when pasting data from Excel can cause errors when saving and posting

Infrequently for most, and more frequently for beginners you may encounter the following error: 

The stored procedure glpPostBatch returned the following results: DBMS: 0, Microsoft Dynamics GP: 20052. The "More info" button displays: glpPostAccount: Error occurred NULL parameter.

The typical cause for this error is the inclusion of a line or lines below the pasted data in the Transaction Entry Line entry section, typically blank line(s).  It is cheap insurance to right click and delete the last row in this window, after pasting your data from Excel.

Recently, a user reached out to me to report they had received the following error when attempting to paste a transaction:

Here is a link to a blog article, which ultimately resolved the issue:

Cut and Paste Blog Article

The core problem was this "new users" was not granted access to the error report in Image 7 above.

I hope this How To on the Excel Paste feature gets you where you're going.

Manufacturing Detail Labor Reporting

Dynamics GP has multiple mechanisms to record labor and machine transactions to calculate costs during manufacturing production. Time and Machine transactions can be entered through either the Time Card Entry or Data Collection Window. The tables populated by these two windows are different tables altogether.

Work In Process reporting and inquiry tools begin with the selection of the source of the data and provides the ability to sort and filter data by key fields, which makes WIP reporting intuitive from within Dynamics GP. 

That being said, it is not uncommon for users to desire very specific report designs to meet users' needs.  Additionally, SQL Server Reporting Services, Analysis Cubes and other tools are provided expressly because, not every constituent needs nor has access to Dynamic GP. 

Consequently, when generating tools-based reports on WIP labor detail, it is important to know which tool is being used to collect the data, and which tables contain the associated records.

The Data Collection window is a common way to enter labor (direct and indirect) as well as machine costs for work orders. With a simple wedge scanner and some well-placed barcode font, entering data in this window is fast and accurate. This approach is commonly used when there is a central location in the shop, where work order data is collected. The Data Collection window can be accessed using the following navigation: 

(Manufacturing > Transactions > WIP > Data Collection).

Data entered in the Data Collection window is stored in the SF010014 - Data Collection Master and SF010115 - Data Collection Line Items tables (most of the relevant data is stored in the SF010115 table). When joined to the UPR00100 - Employee Master and LC010014 - Labor Code Master tables, a complete picture of detailed labor transactions is achieved.  Here is a basic query, which returns all labor related transactions from the SF010115 table.

NOTE: The Labor Code Master table contains the Shop Rate for the Labor Code, which will be used if the Employee does not have a specific value assigned for the Labor Code in Payroll Setup. The query below assumes the shop rate is used.

-- Labor Detail when entered in Data Collection Window

Select

DCT.MANUFACTUREORDER_I

,DCT.RTSEQNUM_I

,CASE DCT.DATAENTRYTYPE_I

      when 1 then 'Direct_Labor'

      when 2 then 'Machine_Cost'

      when 3 then 'Indirect_Labor'

ELSE 'ERROR'

End Entry_Type   

,DCT.EMPLOYID Employee_ID

,RTRIM(RTRIM(EMP.LASTNAME)+', '+RTRIM(EMP.FRSTNAME)+' '+RTRIM(EMP.MIDLNAME)) Employee_Name

,LCD.COST_I ShopRate

,DCT.ELAPSEDTIME_I Elapsed_Time

,DCT.SEQUENCECOST_I Labor_Cost

,DCT.FIXOVERMARK_I Fixed_Overhead

,DCT.Variable_Overhead_Amount Variable_Overhead

,DCT.PIECES_I Pieces

,DCT.REJECTS_I Rejects

,DCT.PIECECOST_I Piece_Cost

,CONVERT(varchar(10),DCT.ACTUALFINISHDATE_I,101) Finish_Date

from SF010115 DCT

left join LC010014 LCD on DCT.LABORCODE_I = LCD.LABORCODE_I

Left join UPR00100 EMP on DCT.EMPLOYID = EMP.EMPLOYID

Where DCT.DATAENTRYTYPE_I in (1,3)

Alternatively, the Time Card Entry window can be used to enter Detailed Labor data into Dynamics GP. The Time Card Entry window is an employee based entry system, and is more frequently used when each work-center/employee has access to the window, and updates their time data throughout the day. The Time Card Entry window can be accessed using the following navigation:

(Manufacturing > Transactions > WIP > Time Card Entry)

Time Card Entry data is stored in the SF010600 - DC_Labor_Batch_Entry_Hdr and SF010601 - DC_Labor_Batch_Entry_Dtl (Data Collection Labor Batch Entry Header and Detail) tables. These tables contain a wealth of data and therefore do not require links to additional tables. 

-- Labor Detail when entered in Time Entry Window

select * from SF010600 TEH

left join SF010601 TED on TEH.DCHDRNUM_I = TED.DCHDRNUM_I

Feel free to reach out to me, should you have questions about Detailed Labor Reporting for WIP in Dynamics GP Manufacturing.

Management Reporter Budget Data Not Displayed

A client recently created a budget using Excel Based Budgeting in Dynamics GP. The budget was based on a prior year with nominal increases to revenue and expense accounts.  The budget could successfully be opened in both Excel and Dynamics GP.

Unfortunately, when we added the budget to a column definition in Management reporter to do budget to actual comparisons, no budget data displayed.

So, we went into troubleshooting mode:

1  Validate budget in Dynamics GP Budget Maintenance Window
2. Management Reporter > Tools > Refresh Cached Financial Data
3. Check Column Definition thoroughly
4. Stop and Restart Management Reporter Services (Configuration Console)
5. Run ERP Data Validation under ERP Integrations (Configuration Console)
6. Rebuild the Data Mart

No Joy!

I particularly dislike rebuilding the Data Mart, and if I had this to do all over again, I would have held fire on this step. Having to rebuild the Data Mart, in my opinion is like using a hammer to force a fit.

As I was walking from IT to Finance to explain we hadn't resolved the problem, it occurred to me FRx used to have a similar problem, prior to the introduction of Management Reporter... Something to do with the YEAR1 field in the GL00201 table. 

Sure enough, a quick query on the GL00201 table found the value in the YEAR1 field in the GL00201 table for this budget was 0 in every row. This is not a good thing.

For the uninitiated, financial reporting tools like FRx and Management Report use the notion of a BASE period to fundamentally format financial statements in a very flexible manner; users need not change components of financial reports as time elapses. 

So, missing the fiscal year data, found in the YEAR1 file leaves no way for Management Reporter to know what period the financial data for the budget is in.

The query below will help identify problem records.

--Identify problem records in the Budget Master Detail file

select BMH.YEAR1, BMD.YEAR1,* from GL00200 BMH

left join GL00201 BMD on BMH.BUDGETID = BMD.BUDGETID 

where BMD.YEAR1 = 0

The query below will fix the problem records.

--Correct problem records in the Budget Master Detail file

update BMD set BMD.YEAR1 = BMH.YEAR1 from GL00201 BMD join GL00200 BMH on BMH.BUDGETID = BMD.BUDGETID where BMD.YEAR1 = 0

In extreme cases, it is possible the GL00200 table will also have a 0 value in the YEAR1 column. If this happens, you will need to update this value prior to running the scripts above. 

To be clear YEAR1 is the four character year you are budgeting for (i.e. 2015), and you should replace XXXX in the query below with the actual BUDGETID.

Update GL00200 set YEAR1 ='2015' where BUDGETID = 'XXXX'

My recommendation, should you encounter this problem I would change the troubleshooting order as follows:

1  Validate budget in Dynamics GP Budget Maintenance Window
2. Management Reporter > Tools > Refresh Cached Financial Data
3. Check Column Definition thoroughly
4. Run queries to determine if GL00201.YEAR1 data contains 0 values.
5. Stop and Restart Management Reporter Services (Configuration Console)
6. Run ERP Data Validation under ERP Integrations (Configuration Console)
7. Rebuild the Data Mart