The Not So New World Order- Cost Variances and Dynamics GP

Once upon a time, Dynamics GP calculated cost variances for FIFO/LIFO perpetual valuation method items in purchase order processing, sales order processing, and/or inventory control and didn't always post to the general ledger.  This was widely, I think, understood and the importance of printing and reviewing the Cost Variance Journal was drummed in to users collective noggins everywhere.  And then came Dynamics GP 9.0.  Yes.  9. Point. O.

Why am I writing about this now?  Well, although there are definitely articles out there on cost variances, it seems like most of them are from the pre-9.0 world or have not been completely updated correctly.  So, after a lovely chat with Pam Peterson at Microsoft today, I thought...heck, why not, let's lay it all out for everyone!

In the purchase order processing training manual, there is a section (at the end of Receivings Transaction Entry) that outlines three different kinds of cost variances related to FIFO/LIFO perpetual items.  Here they are in a nutshell.

Scenario #1

• Item has a quantity on hand of 5 with a current cost of $1
• A quantity of 15 is sold, causing the item to go negative, this sale would be recorded with a unit cost of $1
• So, in the IV10200 (Inventory Purchase Receipts table), you would now have an override layer for the amount that was overriden (in this case, 10)
• A quantity of 20 is now received and invoiced in purchasing, with a unit cost of $1.25
• This creates a variance of $2.50 (.25 variance X 10) for COGS and Inventory
• This variance will print on the Cost Variance Journal when the receipt is posted
• Now, here is where it gets tricky...
• In GP 8.0 and earlier, this variance would NOT be automatically created in the GL
• In GP 9.0 and later, this variance IS automatically created in its own GL entry

On a side note, the adjustment outlined above is only created automatically if the transaction that caused the override was recorded in the system in GP 9.0 or later.  This ensures that the "outflow" record is properly recorded in the IV10201 (Inventory Purchase Receipts DetaiL) table.  When upgrading from versions earlier than 9.0, the system arbitrarily selects the most recent receipt for an item and creates a dummy outflow for it only.  For this reason, if you test this process using an item in Fabrikam that is already negative, it will most likely NOT create the adjustment automatically.  This is due to the fact that the data in Fabrikam has been upgraded from version to version.  If you want to test out this scenario, make sure you do the whole process, and don't just start with an item that is already negative.

Scenario #2

• Item has a positive quantity on hand of 10
• A quantity of 10 is received in purchasing, with a unit cost of $1
• A quantity of 10 is then invoiced in purchasing, with a unit cost of $1.25
• This results in a $2.50 variance between the original cost posted and the the invoice (.25 variance X 10)
• In this case, the difference is handled in the posting of the invoice.  It will be recorded to the inventory account or to the purchase price variance account (depending on if revalue has been selected on the Item Purchasing Options, Cards-Inventory-Item Purchasing Options).
• The Purchasing Invoice Cost Variance Journal will print to detail the variance and will note that no manual adjustment needs to be made to COGS/Inventory.

Scenario #3

• Item has a positive quantity on hand of 10
• A quantity of 10 is received in purchasing, with a unit cost of $1
• A quantity of 15 is sold to a customer, with a unit cost of $1
• A quantity of 10 is then invoiced in purchasing, with a unit cost of $1.25 (price to customer $3/each)
• This results in a $1.25 variance between the original cost posted and the the invoice (.25 variance X 5)
• In this case, the difference is also handled in the posting of the invoice.  It will be recorded to cost of goods sold, inventory, and purchase price variance (if the item is not marked to revalue).
• The Purchasing Invoice Cost Variance Journal will print to detail the variance and will note that no manual adjustment needs to be made to COGS/Inventory.

Hope this helps to simplify cost variances, and the associated GL impact in GP.

For more information on the GL entry that is created by the variances, refer to TK #2448193.  And for more information on cost variances and perpetual valuation methods, including average perpetual, refer to this article.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Sherlock Holmes I Am Not- Finding Missing Deductions and Benefits

This week I am spending a couple of days back in the old neighborhood (Denver, Colorado) training a client on reporting tools (Report Writer, SmartList Builder, and SQL Reporting Services).  After spending this morning discussing table structure and selecting a reporting tool, we dove in to working on reports this afternoon.  In our brainstorming session for reporting ideas/issues, I was asked for a way to "check" if employees had been set up for the occupational tax here in Denver.  We do this by assigning to a benefit and deduction for the "head tax", rather than using the local tax feature in GP as there are some unique aspects to how Denver calculates the tax.

It is easy enough with SmartList or even Report Writer to test for those that do have the benefit or deduction.  But it becomes a little more difficult to test for something that is not there.  Especially when the employees would have other deductions and benefits, so we really only want to know those that are not set up for these specific codes.  So after a lovely visit to the new IKEA store here in Colorado (oh, my, yes I love me IKEA), and a decent dinner, I came back to the hotel to chew on how to best test for something not existing.  And in that, I found my answer...the NOT EXISTS function in SQL.  Ah, yes. 

Here is what I came up with to test for both scenarios.  The first part tests for the OCCPAT deduction, and displays the employee ID, name, and a message if it does not exist for a record.  And then the second part tests for the corresponding OOCCPUP benefit and returns a similar set of data.  One lovely list.  To be included in my lovely SQL report.

SELECT a.employid, a.frstname, a.lastname, 'NO OCCPAT Deduction' from UPR00100 as a where not exists (select * from UPR00500 as b join UPR00100 as c on c.employID=b.employID where c.employid=a.employid and b.deducton='OCCPAT') and a.inactive=0
UNION
SELECT a.employid, a.frstname, a.lastname, 'NO OOCCUP Benefit' from UPR00100 as a where not exists (select * from UPR00600 as b join UPR00100 as c on c.employID=b.employID where c.employid=a.employid and b.benefit='OOCCUP') and a.inactive=0

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Payroll 941 Mystery Solved (or The Case of Missing Table)

If I was asked by a payroll person for one piece of advice, I would tell them to always make sure they have a backup before printing and posting payroll.  So much trauma could be avoided if restoring a backup after a payroll posting gone wrong was possible.

And then, if they allowed me, I would provide two more:

1.  If you run in to problems printing and/or processing the payroll prior to posting, DO NOT go ahead and post it.  Cancel out of it.  Your transactions will be available to be included in a new build, and you won't have to worry about cleaning up a partial posting.

2. Any time you use manual checks to record adjustments or corrections, always make sure you check and double check the taxable wages and employer FICA taxes (as these are not detailed on the edit list).  Mistakes in these two fields when manually entering payroll information account for many 941 and payroll summary reconciliation issues.

But, let's get back to payroll posting gone wrong :)  Recently I had a case where the payroll printing was interrupted when the server was shut down accidentally.  When the server came back up, the user attempted to post the payroll and received errors.  Fortunately, they were able to send out the checks since they had already been printed successfully.  And then we were able to set about fixing the posting.

We found that the posting interruption appeared to occur between updating the check history (UPR30100) and transaction history (UPR30300) tables.  The check history had the checks listed, but the transaction history table was blank for the audit trail code.  So we removed the stranded records from the UPR30100, and the users could then re-enter the payroll through manual checks (Transactions>>Payroll>>Manual Checks).  Fortunately, they had all of the information to do this and a small number of employees.

After completing this task, though, the 941 was still off.  Payroll Summary (Reports>>Payroll>>Period End) looked great.  As did the wage amounts from the 941 (Reports>>Payroll>>Quarter End).  It was all in the taxes, they appeared to be double what they should be, and the 941 schedule B also showed double for that payroll date.

Head-scratcher, huh?  Probably not for those of you who know better.  So I went looking in Utilities>>Payroll>>Edit Liabililties and I saw it!  The check run had posted its information in the Payroll Tax Liabilities table (UPR30200).  So once we removed the record from there as well, all was right with the world.  So the lesson here is not forget about the UPR30200, particularly with posting interruptions as it seems that it posts the summary level info before the detail info.

As someone who has avoiding getting in to the realm of "fixing" payrolls that have gone wrong, it has been quite a learning experience for me to move beyond insisting users restore backups when these issues are encountered.  I am still not sure what is better, though...losing the work associated with restoring a backup or the time involved (usually with a consultant) to clean it up if you don't.  And then you add in the concern of making sure that the client understands what you are doing and why so that questions don't come up later regarding all the "back-end" fixin'.  Jury is still out for me, but maybe I am more old-school than I thought ;)

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Reporting Puzzle (2 of 2)- Report Options and Report Groups

(UPDATED 8/9/2011) Thanks to Mariano Gomez for a bit of clarification on this one :)

As is noted in the user guides, there is a limit to how many reports can be put in a report group (for example, Reports-Financial-Groups).  Each report group can only contain 32 reports.  This isn't too big of a deal, since you can simply set up additional groups, right?

But, there is a bit more of a dilemma.  Let's say that I have a report that has 33 report options set up for it.  In this case, Reports-Financial-Trial Balance:

Reports-Financial-Trial Balance

Trial Balance Report window

Note the 33 report options set up for the Detailed report.  So, then, let's say I want to add some of these report options (particularly the 33rd option) to a report group.  Reports-Financial-Groups:

Reports-Financial-Groups

Financial Groups window

In the Financial Groups window, I start a new report group.  But, notice that the 33rd option is not even available for selection.  So, although I can create more than 32 options for a report, only the 1st 32 report options are available for selection in a group.  The lesson learned is that you have to set up the report options you want in groups first, so that they are among the first 32 options on any given report.

Although this can be confusing, this is NOT the same as the fact that a report group can only contain 32 report options.  In the case noted above, my report group is brand new with no report options in it...and yet, I still can't pick to include the 33rd report option from the Detailed trial balance report to the group.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Reporting Puzzle (1 of 2)- Dynamics GP SQL Financial Trial Balances

Along with a coworker, I have been chewing on a SQL Reporting Services (or so I thought) issue for the past few weeks.  Users had reported that both of the financial trial balances (Detail and Summary) in SQL Reporting Services were not returning results.  The report worked fine for them in TWO (Fabrikam), but not in either their live or test database.  Strange.  And even stranger, the report worked fine if we changed the data source to use the 'sa' user and password.

I felt like we had checked everything, including:

• Confirmed access to reports in Report Manager, and access to Data Source- other reports work fine (including other financial reports)
• Confirmed the database roles- all affected users had rpt_poweruser roles to both the company and DYNAMICS databases
• Placed users in to SYSADMIN server role for testing- still didn't return results
• Recreated stored procedures used by report- no luck
• Recreated report itself- running out of ideas

What we found through all of this testing is that a) no errors were being returned (yes, we did a trace to confirm this) and b) when running the stored procedure in SQL Server directly, we still did not get results.  We found any number of blog posts related to similar issues, but these all had to do with GP2010 R2 and the temp table not populating correctly.  We were on GP2010 SP1, so it didn't seem likely that it was the same issue.  We started a case with Microsoft, and after a couple more weeks of working on it back and forth we uncovered the rather embarrassingly simply cause. Three words.  Account. Level. Security.

So, the SSRS financial trial balances use the same stored procedure that is used for the trial balance reports in Dynamics GP.  This stored procedure incorporates account level security.  In our company (and test company), account level security (organizational structures) is activated (Tools-Setup-Company-Company).  So when the report is generated, it cannot locate any accounts based on the user's security (since the SSRS reports use windows credentials, not the GP user accounts). This causes the report to not return any results, and does not produce any errors.

Simple cause.  The workaround is to change the data source for the report to use the 'sa' user and password.  If you choose to do this, keep in mind that all users who have access to the report will in turn have access to all data.  In our situation, this works since the only users who can access the report are allowed access to all accounts.  In a more restrictive environment, I think you would have to look at modifying the report to map the windows accounts to GP accounts.  If you did this, you would need to take care not to affect the stored procedure used by the standard Dynamics GP reports.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

A Matter of Degrees? Activity Tracking and Dynamics GP

More and more, it seems like the answer to most Dynamics GP questions is "well, it depends."  It might depend on how much money you want to spend on services and/or software, or how much you care about a particular piece of functionality.  And, lately, it seems like I am getting a lot of questions related to tracking changes within Microsoft Dynamics GP.  Sometimes this is due to "damage control", or because a prior system had the capability, and in most cases I completely understand the concern and the request.  And, like I have suggested, the answer is not a simple one.  There are several options available to you, depending on how much time/effort/money you want to spend.

At the very basic level, GP offers a few inherent tracking tools, for example:

• Many transactions table provide tracking of the user who posted the transaction (not who entered it, but who posted it).  These fields are visible in Smartlist (Microsoft Dynamics GP menu-Smartlist), for Financial-Account Transactions the field is called User Who Posted and for Purchasing-Payables Transactions it is called Posted User ID.  Some records also provide a modified date, although it does not show who (Purchasing-Vendors, for example).
• With the Human Resources module, tracking is enabled in the form of a "reason for change" on pay and position changes.  This enables reporting of who, what, and why on the changes.  Although it does not track the before/after values (which could be inferred to some degree based on the change)  Depending on your configuration in Microsoft Dynamics GP menu-Tools-Setup-System-HR Preferences, entering a "reason for change" may not be required. 
• There are controls available for batch approval and posting permissions through posting setup (Microsoft Dynamics GP menu-Tools-Setup-Posting-Posting) and security setup (Microsoft Dynamics GP menu-Tools-Setup-System-User Security) to control who can post transactions.  This is less tracking, but does help with the "damage control" requirement as security can also be used to limit access to master records, utilities, etc.

These are often some of the first options I look at, but GP also offers a couple additional options depending on your needs. One is included with GP, the other is an additional purchase:

• Activity Tracking- Tracks a number of tasks in GP, with basic information related to who, what and when.  This is included in core Dynamics GP.
• Audit Trails- This is an additional module that tracks the who, what, and when in detail including the before and after values of the field.  Audit Trails actually creates a separate database to track the audit information, and is configured to track changes based on your specific needs.  For more information, refer to this Microsoft Dynamics site on compliance, which includes a link to a fact sheet on the Audit Trails and Electronic Signatures modules.

We are going to take a look at the capability provided by the Activity Tracking feature included in core Dynamics GP.  First, let's look at the setup:

Activity Tracking Setup window

Microsoft Dynamics GP-Tools-Setup-System-Activity Tracking



Use this window to configure Activity Tracking, but be careful to only activate those items that you actually think will be useful (as this can add ALOT of records to your database very quickly).  There are five different types of activities you can track:

• Login/Logout Tracking- Tracks unsuccessful and successful attempts to log in to Dynamics GP
• Access Tracking- Tracks unsuccessful and successful attemps to access a file, window, report, or the Modifer and Report Writer tools
• File Tracking- Tracks additions, deletions, and modifications to Setup, Master, and Transaction files
• Process Tracking- Tracks use of File Maintenance, Utililties, and Routines
• Posting Tracking- Tracks posting by window/transaction origin

Select the Activity Type you wish to track, and select the User and Company to track.  Then mark the specific activities you want to include in the tracking, taking care only to select those items that you feel will be useful as noted above.  Repeat for additional activities, users, and companies as needed. Click OK when complete.

The results of the activity tracking can be viewed easily:

Activity Tracking Inquiry window

Inquiry-System-Activity Tracking

Note that the activities can be filtered by company, user, activity type, and activity.  The information available includes the company, user ID, date, time, and description of the change.  The description includes the record that was affected, but does not include any details of the specific change (this is the major difference between activity tracking and the Audit Trails module-- the level of detail about the change available).  You can also report on this information through Reports-System-General-Activity Tracking Detail.

If you choose to use activity tracking, make sure you set up a recurring task for yourself to remind you to remove the detail on a regular basis.  Keeping the activity detail in your database indefinitely can increase your database size quickly depending on the types and number of activites being tracked.  To remove activity detail:

Remove Activity Tracking Detail window

Microsoft Dynamics GP menu-Tools-Utilities-System-Activity Detail

Use this window to remove the activity detail based on parameters you specify, including activity type, activity, and ranges by date, user, and company. Generally, you would run this process after a full backup of your databases.  Select and insert the necessary ranges (don't forget to mark the activities to remove, and to mark the "Remove Records" and "Print Report" checkboxes), and then click Process to remove the records.

Although activity tracking may not necessarily meet all tracking and audit needs, it can be a useful tool with minimal investment of time and/or money to see results immediately.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Programmatically Checking Company Database eConnect Version Numbers

Back in November 2008, I wrote a post about checking eConnect version numbers programmatically.

At that time, I had found that the eConnect Release Info application simply called the stored procedure DYNAMICS..taeConnectVersionInfoDYNAMICS.  That stored procedure returned a list of databases and the eConnect version for each database.

The problem with that utility is that if a database is offline or not accessible for some reason, the Release Info app will display an error and will not return any results.  If you have a client with dozens or hundreds of company databases, and has one or more database offline, this error can be quite annoying.

While troubleshooting a new eConnect 2010 issue, I had to do some extensive eConnect version sleuthing, and finally discovered how the Release Info app gets version numbers from the database.

The eConnect Release Info application calls the taeConnectVersionInfoDYNAMICS procedure.  That stored procedure gets a list of all INTERID values from the Dynamics SY01500 table--basically a list of all of the company databases.

For each company database, it then queries the taeConnectVersionInfo function, like:

SELECT dbo.taeConnectVersionInfo()

This returns the eConnect version for the database.

So, the obvious question is:  How does this function get the eConnect version number for the database?

That's easy--it's hard coded!

The function simply returns a hard coded version number string, such as 11.0.1.0.  So there is no table or object or magic query that is being run to extract the version number from the depths of the company database.  It is simply the taeConnectVersionInfo function that is updated with each eConnect release.

So, if you are looking for a programmatic way to check the eConnect version number for individual databases, you can just select the taeConnectVersionInfo function for each database.

Pretty simple.  Wish they would have just documented this somewhere.

Although that will provide you with the database's eConnect version, please note that with eConnect 2010, there are different versions of the eConnect DLLs and eConnect Service with each service pack and hotfix.  So for instance, eConnect 2010 SP2 has DLLs with version 11.0.1761.0, but if you install the eConnect June 2011 Hotfix (KB2561289), the DLL versions will change to 11.0.1812.0.

So it still probably makes sense to verify the eConnect DLL versions as well as the DB versions.


Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com