Codename GP "12" Preliminary Features Series – 3 of 4

Codename GP “12” Preliminary Features – Part 3 

This is article is part 3 of 4 from the series Codename GP “12” Preliminary Features. Some images and content reproduced with express permission from Microsoft Business Solutions, a division of Microsoft Corporation.

DISCLAIMER: These features are subject to change.

---

If you were taken by the Simplicity and Productivity features, then you will be more impressed with the this new list of features aimed at enhancing the Microsoft Dynamics GP product depth.

Product Depth features

Receivables Management Enhancements

As of the current release, it has always been necessary to enter and apply multicurrency cash receipts as a two-step process. MC Apply in Cash Receipts will now allow you to take a cash receipt entered in originating currency and apply it against an invoice all in the same transaction entry process.

Payables Management

Void of Check Return Applied Credits (return/credit memo) to reusable state – For example, if you have an invoice ($100) with a payment of $50 and a credit memo for $50, both applied to the invoice, you will be able to void the check and have the credit memo become available to either use against the same invoice or apply it to another document – the credit memo returns to an unapplied state. If the original payment was made by credit card, the invoice created for the credit card vendor will also be voided automatically.
Fixed Assets Enhancements

The Fixed Assets module has seen considerable improvements. New also is the Fixed Assets Historical Depreciation Reports. If you have tried to run a historical report, it was not possible and the system would retrieve all the previous depreciation information. Now you can select a specific depreciation date for your report.

System Enhancements

Document Attach. Are you tired of trying to fight your way with the Dexterity OLE Notes Container? First I should clarify, the OLE Notes Container will remain an integral part of Microsoft Dynamics GP. After all, there are a number of you using out there. However, now you will have a more powerful tool in Document Attach.

Document Attach

General Ledger Enhancements

GL Year End Close Options. This feature will provide an option to clear out balance for unit accounts.  Currently, unit account balances automatically carry over from the previous year. The year-end process now features a progress bar that will indicate how far you are into the process when executing it. GP “12” will also provide an option to NOT delete budget account with balances.

I hope you are enjoying some of these preliminary features. My next article will discuss some of the product innovations and what you can expect from a technology perspective..

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Reconciling unchanged bank statements

Just recently, I worked on a case with a partner, whose client had a checkbook setup. For the past serveral months, the bank account associated to the checkbook has had no transaction activity. The client still receives a bank statement each month with no transactions.

When the client would try to enter the cutoff date information into the Reconcile Bank Statment window, even though the difference is zero, the system is not letting them reconcile the statement.

Reconcile Bank Statement window

They keep receiving a message that they need to mark the items they want to clear.

Select Bank Transctions window – error when attempting to reconcile

As a result of not being able to complete the reconcile, the Last Reconciled Date field is not being updated on the Checkbook Maintenance window.

Checkbook Maintenance window

To overcome this issue, we had the client enter both an interest income and an other expense adjustment for a penny ($0.01), as shown below:

Reconcile Bank Adjustments

Once we returned to the Reconcile Bank Statements window and clicked on the Reconcile button, the process went through. Since Microsoft Dynamics GP does not post zero balance transactions to the same account in the General Ledger, then we were able to effectively not affect the GL. The added bonus, of course, and the problem needed to be solved – updating the last reconciled date on the checkbook – was taken care of with this workaround.

Reconciled Checkbook

Of course, we could have made the changes directly in SQL by updating the Checkbook Master table (CM00100), but that would have left no audit trail of the reconciliation for the accounting department. As for those pennies… they are just that, pennies. The adjustments were documented with notes that explained clearly that they served just as a workaround so auditors would not throw a fit.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

SSRS: GL Trial Balance Summary report returns no data

This one comes courtesy of my friend Steve Sieber at McGladrey.

After installing Microsoft Dynamics GP 2010 R2 and deploying the SQL Server Reporting Services reports, you will encounter an issue when printing the GL Trial Balance Summary SRS report located under Financial.

1. Launch Report Manager and click on the company for which you would like to run the report (the issue can also be reproduced in the Fabrikam (TWO) company database). Click on Financial | Trial Balance Summary, enter all the parameters and options for the report the click on View Report, the following is returned:

GL Trial Balance Summary – SSRS

As you can see, even though the correct parameters are selected, the report returns no records.

2. If the GL Trial Balance Summary report is executed from GP with the same parameters, the report correctly delivers the expected records and result:

GL Trial Balance Summary – GP

From a technical perspective, the GL Trial Balance Summary SSRS report executes the dbo.seeGLPrintSRSTrialBalance stored procedure. The issue appears to be that the #GLTBDTemp temp table does not get populated with the records needed to render the report. You can test the stored procedure by executing the following statement from SQL Server Management Studio against any company database.

exec seeglPrintSRSTrialBalance 0,0,0,‘000-0000-00’,‘999-9999-99’,’01/01/2017′,’12/31/2017′,2017,0,1,1

This issue has been reproduced by Microsoft Support and they are currently researching the problem for a solution. 

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Disabling Multiple Ledgers functionality in Microsoft Dynamics GP 2010…after the fact

Let’s face it! Like many things in life, configuration decisions are revisited even after going live (rightfully so!) with your system. What was viewed and considered a requirement a few months aback and worked during User Acceptance Testing turns out to be something the business no longer needs today, due to changes in direction, or changes in business conditions.

Just recently, I came across a request for disabling the new reporting ledgers functionality in Microsoft Dynamics GP 2010. While this implementation was not live, this issue was clearly affecting the consulting teams ability to move forward.

The following script should disable the reporting ledgers function:

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

UPDATE GL40000 SET Allow_Reporting_Ledgers = 0, UseLedgersForAcctBalance = 0;
DELETE FROM GL40001;

Once the script is executed, go back to the General Ledger Setup window. You will notice that a BASE ledger is created by default, but also notice that the Allow flag is unchecked.



General Ledger Setup

Click the Ok button to continue.

Now, if you open the GL Transaction Entry screen, you will notice that the Ledger ID field is no longer present.

Transaction Entry

Hope you found this post useful.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Integration Manager: Integrating journal entries with Analytical Accounting Information

My good friend, David Musgrave, somehow manages to get me involved in interesting topics circulating in his inbox. Just recently, he came across a fairly long thread among his peers, needing to work out some Integration Manager issues for journal entries with Analytical Accounting information. David was kind enough to involve me, as I had posted an answer on the newsgroups a long time ago on this same issue.

If you are one of the fervourous Integration Manager fans out there and have had to work on integrating journal entries with Analytical Accounting information, you may know this is only possible with the eConnect Adapter, not the Standard Adapter.

The eConnect Adapter was introduced with Integration Manager version 10, and replaces the old SQL Optimized Adapter available in prior versions of Integration Manager. The eConnect Adapter in turn, leverages eConnect components to deliver a robust transactional environment for high volume integrations using ADO.NET to access Microsoft Dynamics GP company databases.

eConnect Adapter – Journal Entry# field with Use Default rule value

However, the eConnect Adapter, though, while it provides a Use Default rule value for the Journal Entry# field, this setting causes the integration to fail, as eConnect (the component) requires a value to be supplied, this is, the actual journal number.

Of course the question now is, how do you retrieve the next journal number from your company database to supply this value to the Journal Entry# field to allow the integration to be successful and thereby, preventing you from having to manually reserve ? The answer is, scripting, of the VBScript type.

You can add VBScript code to the Before Document event script in Integration Manager to retrieve the next journal number from your company database, as follows:

' Created by: Mariano Gomez, MVP
' This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
Option Explicit

Const adCmdStoredProc = 4
Const adParamInput = 1
Const adParamOutput = 2
Const adParamInputOutput = 3
Const adInteger = 3
Const adVarchar = 200
Const adBoolean = 11
Const adChar = 129
Const adDate = 7
Const adNumeric = 131

Dim SqlStmt
Dim objConnection, objCommand, NextJournal

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")

objConnection.Open _
    "Provider=SQLNCLI10;Server=MGB001\GP11;Database=TWO; Trusted_Connection=yes;" 

With objCommand
 .ActiveConnection = objConnection
 .CommandType = adCmdStoredProc
 .CommandText = "glGetNextJEWrapper" 'our wrapper stored proc

 .Parameters.Append .CreateParameter ("@IO_iOUTJournalEntry", adInteger, adParamOutput, 4)
 .Parameters.Append .CreateParameter ("@O_iErrorState", adInteger, adParamOutput, 4)

 .Execute
 NextJournal = objCommand.Parameters("@IO_iOUTJournalEntry").Value
End With

SetVariable "gblJounal", NextJournal

Set objCommand = Nothing
Set objConnection = Nothing

The above code calls the stored procedure dbo.glGetNextNumberWrapper, which leverages the existing Microsoft Dynamics GP’s dbo.glGetNextJournalEntry stored procedure to retrieve the next journal number, stored in the dbo.GL40100 (General Ledger Setup) table. As this is a call to a standard Microsoft Dynamics GP stored procedure, we are avoiding the use of custom code to retrieve the journal number and increment the value at the same time.

It is also necessary to note that the above code uses a Trusted Connection to connect to the company database. You can change the connection string as you see fit, just keep in mind that if you are going to use a SQL login, it cannot be a Microsoft Dynamics GP user login as the password for these logins are encrypted on SQL Server.

The following is the code for the dbo.glGetNextNumberWrapper stored procedure called by the Before Document script:

IF OBJECT_ID('dbo.glGetNextJEWrapper') IS NOT NULL
 DROP PROCEDURE glGetNextJEWrapper;
GO
CREATE PROCEDURE glGetNextJEWrapper
 @IO_iOUTJournalEntry int OUTPUT,
 @O_iErrorState int OUTPUT
AS
DECLARE @l_tINCheckWORKFiles tinyint = 1, @I_iSQLSessionID int = USER_SID(), @O_tOUTOK tinyint;

IF @IO_iOUTJournalEntry IS NULL
 SET @IO_iOUTJournalEntry = 0

EXECUTE glGetNextJournalEntry
   @l_tINCheckWORKFiles
  ,@I_iSQLSessionID
  ,@IO_iOUTJournalEntry OUTPUT
  ,@O_tOUTOK OUTPUT
  ,@O_iErrorState OUTPUT
GO
GRANT EXECUTE ON glGetNextJEWrapper TO DYNGRP;

For more information on connection strings, visit http://www.connectionstrings.com/. Also, check the following article over at Developing for Dynamics GP on why does Microsoft Dynamics GP encrypts passwords.

Once the Before Document event script is implemented, you can then add a small field script to the Journal Number# field to retrieve the value stored in the gblJournal Integration Manager global variable, as follows:

' Created by: Mariano Gomez, MVP
' This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
CurrentField.Value = GetVariable("gblJournal")

Integration Manager has great import capabilities when combined with the power of scripting and when you have a clear understanding of the underlaying technologies that support it.

Please enter your comments on this article or any methods you have used in the past to overcome similar issues.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

From the Newsgroups: What are those GL entries with reference SALESASMxxxx?

Welcome to another edition of From the Newsgroups.

The Microsoft Dynamics GP Online Partner Technical Community forum is one of those virtual places where you get to see and experience it all. One gets to become familiar with real life issues experienced by us partners keen enough to share our implementation and support issues. The following is a thread from the Online Partner Technical Community:

Good afternoon. Our customer keeps getting these “mystery” GL postings [SALESASMxxxx] and wants to know why they are being generated but I cannot find any article or search that shows this document prefix for GP. Does anyone know what type of doc this is and why it may be generated automatically into GL?

The answer comes courtesy of Tristan Thor Clores, a Microsoft Support Engineer.

For transactions that have the SALESASM prefix in their description: These may mean that a sales invoice with a shortage override of a top level BOM (also known as finished good) was posted at one cost. Then when the BOM that fulfilled the shortage was posted its unit cost was different from what the invoice held.

————————————————————————————
Example:
Let’s pretend that we have a $0.00 balance in the Inventory-Finished Goods account. An invoice was posted with a shortage override for 10 units of finished good item HARD DISK. Its unit cost is $10.00 and its unit price is $20. Its journal entry will look like the following:

Debit: Accounts Receivables $200.00
Debit: Cost of Sales $100.00

Credit: Sales $200.00
Credit: Inventory-Finished Goods $100.00

However, when a BOM assembly was posted for 10 units of HARD DISK, its unit cost was actually at $12.00 per base unit. Let’s pretend that it has the following account distributions:

Debit: Inventory-Finished Goods $120.00

Credit: Inventory-Assembly Component #1 $50.00
Credit: Inventory-Assembly Component #2 $70.00

This journal entry will result in you having 0 units On Hand for the item but with a debit balance of $20.00 in the General Ledger. So now, Microsoft Dynamics GP will create the following cost variance journal entry to remove this balance and help your GL recognize the true cost of the sale. This change though will never hit Sales Order Processing history and so the SOP Document Analysis report will not print this additional cost and will then print an incorrect profit margin afterwards:

Debit: Cost of Sales $20.00
Credit: Inventory $20.00
————————————————————————————

I would also like to share the following list with you. It is a list of the transaction reference prefixes that Microsoft Dynamics GP support engineers have encountered with cost variance journal entries so far:

1. BOM – assembly transaction
2. INV – invoice from the Invoicing module
3. IVT – inventory transfer
4. IVA – inventory adjustment
5. IVV – inventory variance
6. SALES – sales invoice from Sales Order Processing
7. PRTN – purchase return
8. MCTE – for a transaction that originated in Manufacturing Component Transaction Entry
9. MRCT – manufacturing receipt
10. MCLS – manufacturing close, including regular close and Quick MO
11. STCK – stock count variance
12. FSSC – field service Service Call
13. FSRMA – field service RMA
14. FSRTV – field service RTV
15. FSWO – field service Work Order
16. PA – project accounting
17. POP – close a PO line in Edit PO Status
18. RECON – created by Inventory Reconcile
19. CONV – created by an upgrade conversion
20. MCTERCT – If the items were issued in the Component Transaction Entry window and then the invoice is completed the adjustment will have a prefix of MCTERCT
21. MRCTRCV – If the items were issued in Mo Receipt Entry (they are “backflushed”) and then the invoice is complete the adjustment will have a prefix of MRCTRCV.
22. MCLSRCV – If the items were issued in the MO Close process (the MO was partially received and more “backflushed” items were issued during the close) and then the invoice is completed the adjustment will have a prefix of MCLSRCV.

I recommend going through KB Article 869470 – “Cost Variance for Inventory,” “Sales Order Processing,” and “Purchase Order Processing” for mor details regarding cost variance in the system.

Let me know how this goes.

I hope you find this article very interesting.

Related Articles

What do those strage reference codes in GL mean?

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Post through from Microsoft Dynamics GP Manufacturing

As if Post Through wasn’t hard enough to understand for the core financial and distribution modules (take a look at my previous article Why my transactions don’t post through GL even when I have the flag selected?) here comes Manufacturing. Take a look at these comments from Microsoft’s Becky Berginski:

“Within Microsoft Dynamics GP there is a place to setup your modules posting options. However, there is not an option specifically for Manufacturing. Manufacturing looks at the Inventory and General Ledger options to determine the MFG posting options. If the transactions you enter in Manufacturing post adjustments to Inventory then the system looks at the settings for your Inventory module. (i.e. To determine if it is stops in GL or posts through GL.) If the transactions don’t update inventory (i.e. closing MO), but GL adjustments are made then it looks at your GL settings to determine how it posts.

Go to Tools Setup Posting Posting. Review the settings for both Inventory and General ledger transaction entry options.The posting journals that print after a posting of an MO Receipt are those associated with Inventory Adjustments. You can turn these off (or have them saved to a file) using the Posting Setup window (Tools – Setup – Posting – Posting). Be sure to choose the Inventory series and the Transaction Entry origin. Keep in mind that these settings are now on those reports for regular Inventory posting also and not just the posting of Manufacturing Components.

There are certain labor transactions that will not post through the GL. Data Collection transaction never post Through the GL. You would need to locate the Journal Entry and adjust the Transaction Date. Data Collection entries will have a Source Doc code of ‘DC_ADJ’ and reference to the MO number.”

Becky then goes on to clarify that,

“There are some transactions that will not post to GL. Most of them do. Below is a listing of what does. There are various postings that will come from Manufacturing. Some of these postings go directly to the GL. Some of the postings go through Inventory first and then update the GL. The transactions that go through Inventory utilize the posting setup for that series. The transactions that post directly from manufacturing to GL utilize the posting setup for the financial series. Here are some examples of the various places that one can post from in Manufacturing:

1. If they are using the Quick MO Entry window, postings will occur when they click on the Close MO button.

A. They will have one adjustment posted in Inventory to reduce quantities for the components. This entry will update GL when it finishes posting in IV.

B. They will have a second adjustment posted in Inventory for the receipt of the finished goods. This also will update GL when it finishes posting in IV.

C. If there is labor or machine time set up for the finished good, a journal entry for those costs will post directly to the GL.

D. Also if there are any variances, a journal entry to record those will post directly to the GL.

2. If they are using the basic MO Entry window, postings may occur a number of different times and from different windows:

A. Components can be issued from the Component Transaction Entry window. This posts a decrease adjustment in IV and then GL when the posting completes in IV.

B. Any labor and machine time recorded in any of the WIP windows will post costs directly to the GL.

C. The posting of MO receipts will post an increase adjustment for the finished good in IV and then will update GL. If components are backflushed a decrease adjustment will be posted to IV and GL will be update after the IV posting is complete. Labor and machine costs may post to GL if they are backflushed.

D. When closing the MO, variances will be posted directly to GL. Also additional component quantities may be included–this means a decrease adjustment in IV and a related posting to GL.

One other setting that sometimes prevents users from posting thru GL is in the Security Setup window (Tools – Setup – System – Security). Choose product of Microsoft Dynamics GP, Type of Windows, and Series of Financial. Be sure that the users posting in Mfg have access to the following two windows:

GL Open Files
GL Open Financial Files – internal

As I mentioned transactions stemming from labor or machine data collection will never post through the GL.“

Hope you find this information useful and a good 2010 opening article.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

MVP Mark Polino on Transaction Posting vs Batch Posting

If you thought Post to GL and Post through GL were confusing, wait until you have to explain batch posting versus transaction posting and the implications of either to your client. Fortunately, MVP Mark Polino breaks it down in easy terms in his new Weekly Dynamic article on the subject.

Remember that every implementation is different and that the options you select during configuration will impact the way the business conducts its operations, so go on and read Mark’s article to get some insight on how these options work.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Understanding the "Posting Accounts from Customer or Item" option in SOP

I have been a Microsoft Dynamics GP consultant for more than a decade now and have always found that a big point of contention (and confusion) during the implementation of Sales Order Processing in any environment has always been selecting whether posting accounts will default from the customer or from the item for the effects of creating an accounting distribution for transactions entered and processed.

From a business perspective…

Typically, organizations use one of these two criteria — there may be many more — for reporting profitability: by customer or by product, these two terms used in the broader sense.

Posting Accounts From can be found the Sales Order Processing Setup
window by going to MSDGP > Tools > Setup > Sales > Sales Order Processing

Accounting wise, Customer may refer to a segment in the chart of accounts that represent geographical market location (for example, sales territories, regions; or domestic customers vs international customers), or specific customer activity (for example, retail customers, wholesale customers, not-for-profits, etc). The end goal with this type of revenue and COGS segregation is to report profitability in the different markets where the organization does business. This will in turn allow the organization’s executives to implement certain market strategies that are geared towards improving its bottom line in these markets, or in extreme cases, determine whether having a presence in a specific market makes business sense. It’s also true that organizations that choose this revenue and COGS breakdown typically carry a small amount of product lines, with product costs and prices that do not vary that often.

Accounting wise, Item, may refer to a segment in the chart of account that represent different product lines (for example, analog, digital, embedded processing; or frozen, perishables, dry goods, meats, etc.), or a specific type of fabrication (for example, pinbrazing, vacuum brazing, induction brazing, etc). The end goal with this type of revenue and COGS segregation is to report profitability by the different products carried by the company. This will in turn allow the organization’s executives to implement certain business strategies geared towards minimizing production costs, or find better suppliers of raw material or finished goods, or in extreme cases, determine whether having a certain product line makes business sense. It’s also true that organizations that choose this revenue and COGS breakdown typically carry a wide array of products, where, contrary to the customer scenario, costs and prices may vary often due to market conditions.

Making the decision as to what option is best for the organization should always take into account the financial reporting structure for revenue and cost of sales.

From a technical perspective…

From a technical standpoint, Microsoft Dynamics GP’s chart of account is static, meaning, the chart of account is configured and stored (“hardcoded”) as a pre-requisite to any transaction activity in the system. This in turn imposes certain limitations in the way the system selects accounts at transaction time, but the most notorious limitation is the inability to match chart of account segments based on rules as opposed to selecting defaults. These limitations have given way to third party solutions that implement a rules based system to determine the account that must be used, for example eOne Integrated Business Solutions’ Flexicoder. Flexicoder allows organizations with complex revenue and COGS reporting requirements to build rules for GL accounts used in Sales Order Processing transactions.

I hope you’ve found this article useful and that you understand how each posting account configuration option is related to an organization’s financial reporting requirements.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

MVP Frank Hamelly on Account Rollups and Organizing SmartLists

After a long writing hiatus, MVP Frank Hamelly on his gp2themax blog explains how to setup the often overlooked Accounts Rollup feature which he describes as “not as tasty as fruit rollups, but equally satisfying”.

Frank also explains how to use Microsoft Dynamics GP’s Navigation Pane to organize SmartLists for the end-users. His narrative is based on a requirement posed by the client at an actual project we both had the opportunity to work and collaborate on.

Please be sure to read Frank’s articles as you will find a lot of interesting things from his personal experience.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/