Revenue Accruals in Project Accounting

The conversation about revenue recognition in project accounting for Microsoft Dynamics GP often goes like this...




"How do you do revenue recognition?"




"Oh, we don't.  Just hit revenue when we bill the expenses and fees."




"Oh, okay, so no need for revenue recognition?"




"No.  But we need to accrue at month end for unbilled."







Or, sometimes, it devolves in a conversation of how to use the revenue recognition module to handle the accrual.  Which is neither fun, nor simple. Particularly because GP revenue recognition
effectively calculates one of three ways...




1. Based on contract/project/cost category progress (either by cost, or by hours)
2. When the project is complete
3. Over time when dealing with a service fee (based on duration of service fee)




But many folks don't realize that GP Project Accounting has built in functionality to deal with accruing unbilled revenue (and therefore the offset, unbilled AR as well).




In this case, you would use a Time and Materials Project.  But the difference is that you use a When Performed accounting method.  The When Performed account method, generates a credit to Unbilled Project Revenue and debit to Unbilled Accounts Receivable when a cost is posted to the project.  The amounts posted to these accounts are determined based on the budgeted profit type and project amount/percent.  Then when the expenses are billed, the amounts move from unbilled to actual revenue and AR.  At any point, the unbilled AR and unbilled revenue represent your accrual.




Of course, this works great for expenses, but what about fees?  And the need to accrue them?  You can use this same logic, but instead of using a fee - we use a zero cost miscellaneous log.  So you set up the miscellaneous log cost category with no cost, a quantity equal to the total amount of fee (if known), a profit type of billing rate, and a billing rate of $1.  Yes, $1. So then, at month end, you can enter a miscellaneous log with zero cost, where the quantity is equal to the amount of the fee you want to accrue.  It will then calculate the accrued revenue and accrued AR for the GL posting for you.  This is particularly helpful when the accrual of the fee is not predetermined, but rather calculated based on criteria outside of the system (project milestones, etc). 



The neat part of handling the fees as zero cost miscellaneous logs is that it them goes in to the WIP queue for billing at the amount you want to bill.


Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing 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.

Using the Dynamics GP PM00400 table for Payables Imports

By Steve Endow

I'm developing two different custom Dynamics GP Payables eConnect integrations for two different customers.  One of the shared requirements is to validate the data for each Payables transaction before it is imported.

The import will confirm among other things, that the vendor exists in GP, the GL distribution accounts are valid, the amounts are valid, and lastly, that the transaction does not already exist in GP.  So if Invoice 12345 for vendor ACME is already present in GP, the import should flag the record as a duplicate and skip it.

To check for a duplicate PM transaction, you could check the PM transaction tables directly.  While definitely possible, the downside to this approach is that an invoice or credit memo may be in one of three different tables:  Work, Open, and History (PM10000, PM20000, and PM30200).

All three of the tables could be queried with a single SQL statement, but there is an alternative.  Instead of querying those three tables, you can query the PM00400 "PM Key Master" table instead.

PM00400 should contain every voucher number and vendor document number process by the Payables module, so you should be able to query it to determine if a transaction already exists, and the table should tell you the document status (work, open, or history).

Above is a sample transaction, with each row being a snapshot of the data in PM00400 as the invoice moved from work to open to history.  Note that the CDSTATUS field changes from 1 to 2 to 3, and the TRXSOURCE value is populated once the transaction is posted.

So PM00400 can be a handy option to check whether a Payables transaction already exists in GP, and a quick way to verify the status of the document.

The Receivables module has a similar "RM Key" table, RM00401.

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

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Adding a Visual Studio snippet for a C# "public string" property

By Steve Endow

When I am developing in Visual Studio and adding properties to a class, by far the most common property I add is a string type property.  When developing for GP, over 90% of my properties are strings.

While I have been using the "prop" snippet to automatically type out most of the property declaration, when you have a few dozen properties, it gets tedious and repetitive having to press specify "string" over and over.

Typing "prop" and pressing tab twice will automatically create a property line ("automatically implemented property").

But, the default data type is "int", so I usually have to press TAB and then type "string", then press TAB again to name the property.

So my typing laziness finally overcame my research laziness and I actually looked up how to create a custom snippet in Visual Studio.  It's pretty easy.  Should have done it years ago.

The location of the snippets may vary by Visual Studio version and other factors, but on my machine, the snippet files are located at:

C:\Program Files (x86)\Microsoft Visual Studio 12.0\VC#\Snippets\1033\Visual C#

I located the "prop.snippet" file, copied it to my desktop, and edited it.

Here is the original prop.snippet.


<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<CodeSnippet Format="1.0.0">
<Header>
<Title>prop</Title>
<Shortcut>prop</Shortcut>
<Description>Code snippet for an automatically implemented property
Language Version: C# 3.0 or higher</Description>
<Author>Microsoft Corporation</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<ID>type</ID>
<ToolTip>Property type</ToolTip>
<Default>int</Default>
</Literal>
<Literal>
<ID>property</ID>
<ToolTip>Property name</ToolTip>
<Default>MyProperty</Default>
</Literal>
</Declarations>
<Code Language="csharp"><![CDATA[public $type$ $property$ { get; set; }$end$]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>




I edited the highlighted items, and removed the lines in red.


<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<CodeSnippet Format="1.0.0">
<Header>
<Title>props</Title>
<Shortcut>props</Shortcut>
<Description>Code snippet for an automatically implemented property
Language Version: C# 3.0 or higher</Description>
<Author>Steve Endow</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<ID>type</ID>
<ToolTip>Property type</ToolTip>
<Default>int</Default>
</Literal>
<Literal>
<ID>property</ID>
<ToolTip>Property name</ToolTip>
<Default>MyProperty</Default>
</Literal>
</Declarations>
<Code Language="csharp"><![CDATA[public string $property$ { get; set; }$end$]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>


Once you have your new snippet, you can go to Tools -> Code Snippets Manager -> Import.  One recommendation when you import--only select one category.  For example, either use only My Snippets, or select only C#, etc., otherwise Intellisense will detect multiple snippets with the same name.


UPDATE: While trying to perform this process on a new Server 2012 machine, I was only offered the Location choices of "My Code Snippets" and "ASP.NET MVC 4". I was unable to choose "Visual C#" like on my other server.  I figured out that I had to launch Visual Studio 2012 using Run As Administrator.  Once I did that, I saw the entire list of Location options.

With my new "props" snippet imported, I save a press of the TAB key and don't have to type "string" every time.  The word "string" is now hard coded in the output and it jumps straight to the property name.

If you code properties, I recommend looking into customizing your snippets.  And I'm sure there are tons of other cool things you are do with snippets, but for now, my typing laziness is content.

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

You can also find him on Google+ and Twitter

http://www.precipioservices.com

SQL Server name limitation with GPConnNet and VS Tools

By Steve Endow

I previously wrote about a situation where the Dynamics GP GPConnNet library is unable to connect to a SQL Server instance if a port number must be specified.

This week I encountered a new limitation with GPConnNet and VS Tools.  A customer has been successfully using a Dynamics GP AddIn for several years, and they are now upgrading to GP 2015.  When they tried to test my AddIn on their GP 2015 test server, they received this error.

The error message says GP login failed.  But since this is a VS Tools AddIn that runs inside of GP after a user has logged in, that message doesn't make much sense.  We know that the username and password are correct.  Very odd.

We then noticed that the server name was incomplete.  The final "T" in the name was missing, and the value displayed is exactly 15 characters--more than a coincidence.  So it looks like the 16 character server name is being truncated to 15 characters, and that is likely the cause of the problem.

But wait!  If the server name is being truncated, then the server name would be incorrect.  And when the AddIn attempted to connect to that non-existent server to authenticate, the connection attempt would fail, right?  The error message would be different for a connection failure.

So back to the original error message.  It says "GP login failed", not "failed to connect" or something similar.  So this would seem to tell us that the connection was successful, but that the login subsequently failed.

What in the world?

But it gets better.

If the customer logs in to Dynamics GP using the 'sa' login, the AddIn works and does not give the "GP login failed" message.

So the sa account works, but GP users don't work.  What does that tell us?  In theory, it is a confirmation that the AddIn connection process is working, but that there is something about the GP logins that is failing.

So why would sa work, but not a GP user login?

My guess is Dynamics GP password encryption.

When you create a new user in Dynamics GP, it "encrypts" the password before sending it to SQL Server.  This prevents a user from connecting directly to the SQL Server.

My guess is that GPConnNet uses the SQL Server name in the "encryption" process, but it is truncating the server name at 15 characters for some reason, and that is the cause of this issue.  Presumably Dynamics GP does not do this, since my client is able to login to GP just fine.

So how do you work around this issue?

The best option is to make sure that your SQL Server instance names are no more than 15 characters.

The only other option I was able to come up with was to have the client create a shorter SQL Server Alias.  I then had to hard-code that shorter alias name in my AddIn.  Once I hard coded the shorter alias for the server name, the AddIn worked fine.

Why hard code, you ask?

Well, VS Tools uses the Dynamics GP Backup / Restore form in order to get the name of the SQL Server.  Even if the Dynamics GP ODBC DSN is set to use a short alias name, the Backup / Restore window will return the actual SQL Server name.  So even after the Alias was setup and the GP ODBC DSN was using it, my AddIn was still receiving a SQL Server name of MCCGP15DB01-TEST, and the login would still fail.  Fortunately, they only have this issue with their Test database server--their GP 2015 production SQL Server has a shorter name.

So, like I said, just make sure your SQL Server instance names are 15 characters or less if you are using GPConnNet.

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

You can also find him on Google+ and Twitter

http://www.precipioservices.com