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/

Wise up with Wiseguy

Just a few days aback, I wrote an article referencing the acclaimed 1984 movie, Ghostbusters. In this occasion, my friend and fellow MVP, Mark Polino, makes an appearance with his new short fictional story, Wiseguy.

Wiseguy is a new twist to the classic mob tale. Prospective mobster Joey is stunned to learn that the family business is going to start filing tax reports with the IRS due to the power of Microsoft Dynamics GP. With the help of Jersey girl, Isabella, Joey must learn to collect cash using Microsoft Dynamics GP, instead of the traditional baseball bat method.

Wiseguy highlights some powerful features in Microsoft Dynamics GP including the Collections module, Accounts Receivable, EFT, and Scheduled Payments. It also highlights The Closer and The Validator. The story shows how learning Microsoft Dynamics GP can be fun and entertaining.

You can download Mark’s work for free using a code from Reporting-Central – With a name like Gianmarco Salsano backing up Mark, you can understand why Wiseguy is becoming so popular.

http://www.reporting-central.com/wiseguydownload.htm

By the way, Wiseguy was one of my favorite 80’s TV series, featuring Ken Wahl as agent Vincent Terranova. You can read a bit more about that series here.

Until next post!

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

Configuring Email for Sales and Purchasing Documents in Microsoft Dynamics GP 2010

Recently, I have been receiving a number of questions on the new Sales documents email functionality in Microsoft Dynamics GP 2010 and how to get it configured and working adequately. If you know me by now, I love to translate those questions into useful articles for the Microsoft Dynamics GP community at large.

Fortunately enough, the configuration process is not that difficult. Microsoft Dynamics GP 2010 allows this feature to be configured in a top-down approach which in turn ensures that all related areas of the application are email-aware.

As a first step, there is a setup needed to be done at the company level – MSDGP > Setup > Company > E-mail Settings, or if you prefer, click on Administration on the Navigation Bar, then locate the E-mail Settings link under the Setup web-part:

The first settings you will be able to configure is whether you want the actual document embedded as part of the email body or as an attachment to the email itself. My preference is the latter as it allows for a cleaner look when the email is received by the recipient. Microsoft Dynamics GP supports 4 different attachment formats, DOCX, HTML, PDF and XPS.

Since Microsoft Word and Adobe Acrobat are widely available and have been around for a while, I strongly suggest selecting at least these 2 options. XPS has only been around as a document standard since 2009 (released in 2006) and has native support on Windows Vista and Windows 7, hence recipients running Windows XP may experience some difficulty opening attachments in this format, especially if they are unaware of the XPS viewer’s download location.

NOTE: The IE-hosted XPS viewer and the XPS Document Writer are also available to Windows XP users with the download and installation of Microsoft .NET Framework 3.0.

Once you have defined the initial settings, you may then proceed to enable the email documents that can be submitted from your Microsoft Dynamics GP application. You will be able to define settings for both the Sales and the Purchasing series, as shown below:

Sales E-mail Setup

Purchasing E-mail Setup

The Sales E-mail Setup and Purchasing E-mail Setup windows can also be accessed under the respective series setup menus.

Of special importance is to setup the different labels for each document that will be enabled for submission. Also, if you are expecting the recipient to reply to your emails, select the mail boxes to which replies will be delivered using the Select Names button. My personal preference is to setup general mailboxes on your Microsoft Exchange Server or any other email hosting application. For example, your sales orders can have a general mailbox such as orders@fabrikam.com, or accounts_receivable@fabrikam.com if you are expecting replies to things like AR credit memos or debit memos from the recipients Accounts Payable team. You may do the same for purchasing. This overall approach ensures that emails are delivered to one location or an individual in charge of monitoring these replies.

Finally, you may further tailor specific settings for customer and vendor records, which will allow you to define at a granular level wheter the recipient will receive multiple attachments per email and the specific format for each document that will be attached to the email. You may also choose to enable or disable specific documents that may be submitted to the recipient.

Customer E-mail Options

Vendor E-mail Options

A feature I find particularly useful is, if you have customers or vendors that have size limits to their mailboxes, you can tailor Microsoft Dynamics GP to not allow file sizes above those limits.

I hope this review of the email capabilties of sales and purchasing documents has been useful. Please drop your comments and let me know whether you are using these features today in your business and if it has aliviated any of the past pains you’ve experienced by not having the functionality or in turn has created new problems.

Until next post!

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

What are all those GPSFOINTEGRATIONID columns in some tables

I thought I would close the week with a very little known fact to new comers (and some not so new) to the Microsoft Dynamics GP world.

First some history

Around mid 1998 (or so), Siebel Systems formed an alliance with the then Great Plains Software Inc. to deliver a suite of front- and back-office applications. Great Plains’ back-office applications included an integrated suite of accounting, financial, and supply chain modules. Under the agreement the new suite would add Siebel’s front-office applications covering sales, marketing, and e-business functionality. The combination would allow users to complete sales transactions over the Web, for instance. Great Plains delivered the first component of its new package in November 1999 as the Sales and Marketing Series of Great Plains Siebel Front Office, with customer service and call-center applications to follow in 2000. The suite was aimed at small and mid-sized businesses.

The new Great Plains Siebel Front Office product would initially offer a module to automate sales, marketing, service and electronic business processes. With another module for customer service following in January of 2000, this was the first step toward overall front office/back office solutions.

Fast-forward to April of 2001, Microsoft completed its acquisition of Great Plains and soon began working on other plans to phase out GPSFO, pushing overall its .NET technology and its vision for a .NET based CRM solution. As a result, Microsoft CRM 1.0 was released in January of 2003… the rest is, well, history!

So, how is history related to the title of this post?

So now that you know that your beloved Microsoft Dynamics GP used to play some serious game with Siebel, it’s just about right that they had to have some way of talking back to each other, this is, integration.

As an integration mechanism between GPSFO and Great Plains Dynamics, the Dynamics dictionary (DYNAMICS.DIC) went through a few changes required to accomodate integration points between the two systems. Today, there are still vestiges of these changes in the SOP10100 (Sales Transaction Work), SOP10200 (Sales Transaction Amounts Work), RM00101 (RM Customer Master), RM00102 (RM Customer Address Master) tables. These tables all share a GPSFOINTEGRATIONID, INTEGRATIONID, and INTEGRATIONSOURCE columns, used to track and exchange information between GPSFO and Dynamics. Even the Field Service module had piece of the action with the SVC00203 (Service Call Line Detail) and the SVC_FO_ID column.

Now, can I reuse those columns to store my own data?

As the say goes, not because you can means you should. While these columns are no longer used to store data in these tables — according to the Microsoft Dynamics GP SDK they are marked as “reserved” — you should not begin to store your own data in them. Microsoft may choose in the future to re-purpose or simply drop these from the database schema and your data will be gone. Hence, it’s not recommended to use these fields for any purpose. Alternatively, you can create your own custom tables, whether in Dexterity or natively in Microsoft SQL Server to store any related sales information.

As a final request, please add your comments on your experiences (or nightmares, depending who you ask) with GPSFO. Let’s see how many of you veterans are still out there and tackled the “new brave world” (or the ‘old’ as it has been a good number of years since).

Until next post!

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

SQL – Autogenarating Customer IDs

I have been swamped lately working on 3 different projects, and it’s been difficult to get back to blogging as often as I am used to. However, this week I found myself with an interesting situation where my client needed to import a customer master list into GP, but was migrating from a system that autoassigned an internal key to each customer record. My client wanted to move away from this “unique internal number” to an alphanumeric nomenclation similar to that of Fabrikam, for example, Aaron Fitz would have a customer ID of AARONFIT0001. They thought it was a very intuitive way of identifying customers and that it would serve the AR staff well.

The customer data was staged in a SQL database and the goal was to create a query that will assign these customer IDs based on the customer name. So, here is the solution:

AutoCustomerID.sql

-- Created by Mariano Gomez, MVP
SELECT
 -- evaluates the 9 first characters of the customer name and removes any blanks
 -- other characters can be removed) in between those first 9 characters for a
 -- total of 8, adds an extra zero if needed to complete 9 characters
 CASE LEN(UPPER(REPLACE(SUBSTRING(CUSTNAME, 1, 9), ' ', ''))) 
   WHEN 8 THEN UPPER(REPLACE(SUBSTRING(CUSTNAME, 1, 9), ' ', '')) + '0'
   ELSE UPPER(REPLACE(SUBSTRING(CUSTNAME, 1, 9), ' ', '')) END +

 -- accounts for the rest of the string, uses the rank function to do the numbering,
 -- partitioning by customer name. Just in case there is more than one customer
 -- with the same starting 9 characters, rank() will number them sequentially
 SUBSTRING('000', 1, 3 - LEN(CONVERT(CHAR(3), RANK() OVER(PARTITION BY REPLACE
   (SUBSTRING(CUSTNAME, 1, 9), ' ', '') ORDER BY CUSTNAME))))
   + CONVERT(CHAR(3), RANK() OVER(PARTITION BY REPLACE(SUBSTRING(CUSTNAME, 1,
   9), ' ', '') ORDER BY CUSTNAME))
FROM RM00101
ORDER BY CUSTNAME

Run this against the TWO database to see the results.

If you ever have the need to create customer IDs based on the customer name this should give you a starting point.

Until next post!

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

New Article on MSDynamicsWorld: Michael Johnson on "Filtering Salesperson-specific data with SSRS"

“It didn’t take long to figure out that the problem wasn’t with Business Portal, but rather the way they were trying to use it and NOT using SQL Server Reporting Services (SSRS). Both tools have a place in their environment, but one is not a substitute for another”

My friend Michael Johnson, a.k.a. “The MBS Guru” has finally come out of hiding and has decided to publish a really clever solution for filtering salesperson-specific data in GP using SSRS over at MSDynamicsWorld. The good thing about Michael’s solution is, you can apply it to pretty much any report that requires such type of access and filtering capabilities. They don’t call this guy the MBS Guru for nothing!

Until next post!

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

"End of Month + Net Days" payment terms due date calculation

Folks across the pond use payment terms and due dates that are not traditionally close the ones we are familiar with on this side of the hemisphere. “End of Month + Net Days” (EOM+ND) is a typical case.

In EOM+ND payment terms, an invoice becomes due a number of net days after the last day of the month for the invoice date. For example, if an invoice date is July 16 and we are on a payment term of EOM plus 45 net days, the invoice will not be due until September 14 — or 45 days from July 31.

Of course, you cannot manage this type of payment term request in GP, not out-of-the-box anyways, which most of the time will require a customization to deal with the issue.

In this article, I will examine setting up two SQL Server triggers: one on the PM Transaction Open File (dbo.PM20000) and another on the RM Open File (dbo.RM20101) tables. The triggers will use the Net Days field in the Payment Terms Master (dbo.SY03300) table to calculate the net days after the end of month to assign the invoice due date.

So here are the triggers:

trigger pmEOMPlusNet

-- Created by Mariano Gomez, MVP
-- No warranties expressed or implied
CREATE TRIGGER pmEOMPlusNet ON dbo.PM20000 AFTER INSERT
AS
BEGIN TRANSACTION;

BEGIN TRY
  UPDATE A SET A.DUEDATE =
    DATEADD(dd, B.DUEDTDS, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.DOCDATE) + 1, 0)))
  FROM PM20000 A
    INNER JOIN INSERTED I ON (A.VCHRNMBR = I.VCHRNMBR) AND (A.DOCTYPE = I.DOCTYPE)
    LEFT OUTER JOIN SY03300 B ON (I.PYMTRMID = B.PYMTRMID)
  WHERE (I.DOCTYPE = 1) AND (I.PYMTRMID LIKE 'EOMPLUSND%')
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER() AS ErrorNumber
    , ERROR_SEVERITY() AS ErrorSeverity
    , ERROR_STATE() AS ErrorState
    , ERROR_PROCEDURE() AS ErrorProcedure
    , ERROR_LINE() AS ErrorLine
    , ERROR_MESSAGE() AS ErrorMessage;

  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
  COMMIT TRANSACTION;
GO

trigger rmEOMPlusNet

-- Created by Mariano Gomez, MVP
-- No warranties expressed or implied
CREATE TRIGGER rmEOMPlusNet ON dbo.RM20101 AFTER INSERT
AS
BEGIN TRANSACTION;

BEGIN TRY
  UPDATE A SET A.DUEDATE =
    DATEADD(dd, B.DUEDTDS, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.DOCDATE) + 1, 0)))
  FROM RM20101 A
    INNER JOIN INSERTED I ON (A.CUSTNMBR = I.CUSTNMBR) AND (A.DOCNUMBR = I.DOCNUMBR)
      AND (A.RMDTYPAL = I.RMDTYPAL)
    LEFT OUTER JOIN SY03300 B ON (I.PYMTRMID = B.PYMTRMID)
  WHERE (I.RMDTYPAL= 1) AND (I.PYMTRMID LIKE 'EOMPLUSND%')
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER() AS ErrorNumber
    , ERROR_SEVERITY() AS ErrorSeverity
    , ERROR_STATE() AS ErrorState
    , ERROR_PROCEDURE() AS ErrorProcedure
    , ERROR_LINE() AS ErrorLine
    , ERROR_MESSAGE() AS ErrorMessage;

  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
  COMMIT TRANSACTION;
GO

Setting up the payment term in Dynamics GP

Open the Payment Terms Setup window (MSDGP > Tools > Setup > Company > Payment Terms) and configure the payment term as shown below:

NOTE: You can still setup discount and discount types for the payment term, but these will be calculated based on the document date. If you need these to apply based on EOM as well, you will need to change the above triggers to reflect the discount calculation based on EOM as well.

Finally, once setup, these payment terms can be used from SOP and POP. Just keep in mind that the due dates will not be calculated while the transactions are stored in a batch, but rather when posted.

Until next post!

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

Asset-based lending and receivables factoring

Factoring receivable invoices is the sale of an asset – your company’s invoices. The sale of your invoices to a third party – known as a Factor – eliminates the sale-to-collection business cycle of waiting for a customer payment. Usually, factoring companies will purchase your invoices for up to 90% of the total amount. You get your cash now and the factor takes on the risk of collecting the payments from your customers. The creditworthiness of your customers is very important if you want to get a good rate from a factor.

What are the specific advantages?

• Immediate cash with no waiting and without incurring new debt: You receive quick payment following invoicing.
• To factor, your business credit rating is not an issue because you are not borrowing money
• Efficient handling of all your invoicing and data entry
• Relief from the responsibility for collecting no-pay and slow-pay clients
• You have expanded growth capacity through increased production and total sales
• Ability to take advantage of vendor discounts

How to implement factoring in GP?

I have done a number of implementations where accounts receivable factoring was necessary, and one way — read, they may be other methods, but this is what worked for my clients — I have found is by using the National Accounts functionality in GP, where the factoring company becomes a national account and all customers are child records of the national account. Lets take a look:

1) Setup your factoring company as a customer tied to the checkbook ID where funds from the factoring company are deposited.

*Click on image to enlarge

2) Setup your factoring company as a national account and add your customers as child records to the national account.

*Click on image to enlarge

3) If you receive check or payments from your customers, just forward them to your factoring company. This is not a common occurrence in this scenario, but if you do receive checks from your customers, there is no need to record these in Dynamics GP.

4) When you receive the funds and payment report from the factoring company, enter a cash receipt for the amount reflected on the report. When you post the cash receipt, don’t forget to do a bank deposit. This will reflect the funds in your account.

* Click on image to enlarge

5) Apply the cash receipt from the factoring company to the invoices reflected on the report. Because of factoring, invoices will be short. Writeoff the difference to the factoring expense account you have created.

* Click on image to enlarge

* Click on image to enlarge

NOTE: If you have a high volume of transactions, you can request a file from the factoring company to be used with Integration Manager.

Overall, what I like about this approach is, your company still does not loose track of the invoces owed by your customers, you can still run receivable agings that will allow you to estimate how much your outstanding invoices are worth allowing greather visibility on cash flow — remember, your fees will increase depending on the age of the invoices held by the factoring company.

Until next post!

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

How to setup "No payments, no interests until June 1, 2009" payment term in Microsoft Dynamics GP

Business Situation

The current economic climate is forcing businesses to be a bit more creative. Some of these companies are resorting to No interest, no payments terms that allow customers to walk out the stores with the merchandise and begin payments at a future date. But, how is a company using Microsoft Dynamics GP going to deal with this type of payment term? What if the promotion is only being ran for the month of December only? What if all invoice generated in December are all due simultaneously on June 1, 2009 regardless of the date the invoice was generated?

Solution Implementation

Since invoices from SOP and RM flow to the RM Open table (dbo.RM20101) after they are posted, it is safe to say we can add a SQL Server trigger to overcome the hurdle. However, we must setup a payment term ID that will serve as an identifier for our trigger to take action. Follow these steps to implement the solution to this problem:

1) Setup a new payment term called JUNE012009. Open the Payment Terms Setup window under Microsoft Dynamics GP > Tools > Setup > Company > Payment Terms. Fill in the window, as follows:

*Click on image to enlarge

2) With the payment term ready to go, we can now proceed to write our SQL Server trigger on the RM Open table (dbo.RM20101). Run the following T-SQL script against your company database.

trigger dbo.trPaymentTerm

/* Created by Mariano Gomez, MVP
   Code is delivered "AS IS". No warranties expressed or implied
*/
CREATE TRIGGER dbo.trPaymentTerm ON dbo.RM20101 AFTER INSERT
AS

 BEGIN TRAN

 UPDATE A
 WITH (ROWLOCK) SET A.DUEDATE = '2009-06-01'
 FROM dbo.RM20101 A INNER JOIN INSERTED I ON (A.DEX_ROW_ID = I.DEX_ROW_ID)
 WHERE (I.PYMTRMID = 'JUN012009') AND I.DOCDATE BETWEEN '2008-12-01' AND '2008-12-31'

 IF @@ERROR = 0
  COMMIT TRAN
 ELSE
  ROLLBACK TRAN

GO

A few things to highlight are, the UPDATE statement in the trigger uses the DEX_ROW_ID to match the records inserted against the records already committed in the table. This is a valid use of the DEX_ROW_ID column. For more information on the DEX_ROW_ID column please check my previous article on the subject.

It is also worth noting that the WHERE clause contains all the rules we originally set out to obey, that is, we would apply the payment term for any document that had the ‘JUN012009’ payment term assigned, but also, the documents must be created during the month of December. This is important, since we need to prevent our trigger from firing for months other than December.

An enhancement could be introduced in the trigger to raise an error on the Dynamics GP user interface (using the RAISERROR statement), for any invoice document that does not meet the criteria.

Hope you find this post useful and I would like to hear your comments.

Until next post!

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

How to create a Go To link to the Apply Sales Document window in SmartList Builder

A few days aback, my buddy Jim Harris at TurboChef was having an issue with creating a Go To in SLB that would link open credits and return documents displayed in SmartList to the Apply Sales Documents window in GP where he could effectively apply documents that had not been applied to invoices, etc.

I thought, how complicated could that be? You open SLB, you setup your SmartList fields then, setup your restrictions for RM Document Type-All equal or greater than 7, and add the Go To by matching the Customer Number field on the window to the Customer Number in the RM Open Table, the Document Type on the window with the RM Document Type-All field in the table, and the Document Number in the window withe the Document Number in the table, a few clicks to close and save, build and… not so quick!

At first the Apply Sales Document window filled in the Customer Number and Document Number, but screamed at the Document Type field. I figured, my restriction was wrong, so I went back in and check on the restrictions, reviewed the SQL check on my Go To fields to make sure I was matching the values and running the field scripts, a few clicks to close and save, build and… not so quick!

I got the same result as before and was starting to wonder now if I had had a long day and was not thinking straight. So I went to the kitchen, got some coffee and went back to work on the problem.

After a few hit-and-miss attempts the light bulb went off! Drop-down lists are enumerations of the items listed in them! In the case of the Apply Sales Document window, 1 – Credit Memo, 2 – Returns, 3 – Payments, which do not correspond to the actual document types in RM (7 — Credit Memo, 8 — Returns and 9 — Payments).

This meant that I would need to create a calculated field that would pickup the RM Document Type-All value from the RM Open table and translate it into the corresponding enumaration in the Drop-Down list in the Apply Sales Document window. The resulting calculation looked something like this:

case {RM Open File:RM Document Type-All} when 7 then 1 when 8 then 2 when 9 then 3 end

That’s correct! It looks and behaves just like the CASE…END statement in SQL Server!

This now meant that in the Go To window, I would have to reference my calculated field instead of the RM Document Type-All field when matching the Document Type value.

From this point on a few clicks to close and save, build and… Voila!

Until next post!

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