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

November 21, 2011

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/


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

September 15, 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/


From the Newsgroups: Safe Pay and Absolute Value of all Documents

April 13, 2010

Welcome to another edition of From the Newsgroups, where you will find real life cases of everyday issues. This edition comes courtesy of the Microsoft Dynamics GP Partner forum:

Q: I have setup Safe Pay Configurator. I am having issue with the overall [file] total, which I assigned as a Standard field, Check Amount, and then Amount type, Net Total. When we were testing for the trailing record, the overall document amount is coming out as Issued – Void which is correct because it’s a net total, but the issue is our voids are coming without the negative sign so the value is off. Below is the example:

Issued: 1,000

Void: 100

Overall total: 900

Our bank is requesting absolute value [Ed: Checks + Voids] so overall total should be 1,100.

I exported the configurator and imported it to Fabrikam and it works/calculate correctly. Only thing we have different from Fabrikam is Multicurrency is not activated. When I tested it in Fabrikam, here’s the result:

Issued: 1,000

Void: -100

Overall total: 1,100

Why is my format working in Fabrikam but doesn’t on our production company?

Please advise.

Interestingly enough, it turns out this issue is related to a missing chunk file. Here is the answer from the support engineer.

A: In order to get the absolute value, you will need to install a .cnk file into the GP code folder. The .cnk file would need to be installed on all workstations that generate the Safe Pay file. The file updates the code that generates the file and adds a function that gives the total of the checks and voids. Unfortunately I can’t explain why it works in Fabrikam and not in the live company. It does not work correctly in my Fabrikam install so maybe it was the way you generated the transactions for your testing.

If you are experiencing a similar situation or simply need to submit the absolute amount of all documents in the file (checks + voids), please open a support incident with the Microsoft Dynamics GP support team.

Until next post!

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


Microsoft Dynamics GP 2010 – Service Call Management Enhancements, Part 2

February 17, 2010

You had a chance to review the first set of enhancements in my Part 1 article. Now we will review a second set of features to conclude the Microsoft Dynamics GP 2010 Service Call Management enhancements series.

Generate payables vouchers for subcontractor charges

You can automatically generate vouchers in Payables Management for subcontractor labor, expenses, or additional charges. The vouchers are created during service call billing. To enable this feature, you must specify a Vendor ID for each subcontractor technician. You also can designate subcontractor service types.

Post labor information to U.S. Payroll

You can now post labor information from Field Service to U.S. Payroll. New setup options, Payroll Integration and Post to Payroll, are added to the Service Setup window. When these options are activated, Field Service integrates to U.S. Payroll for labor or indirect labor. Payroll transactions can be based on the information that is entered in the Service Call Entry – Labor, Labor Information, and Indirect Labor Entry/Update windows, and in a new window, Field Service Labor – Payroll. This feature also integrates the cost from the employee’s pay code to the labor record in Field Service.


Service Setup window


Service Setup – Service window


Service Call Entry – Labor & Labor Information Windows

Use kit items in the Field Service Series

You can add kit items to parts lines, and you can make changes to the component lines, including quantity ordered. You can create a purchase order from a kit item or from one or more component items. If you do so, the kit item cannot be transferred, but the individual inventory components can. You can specify a kit as a part for an engineered change order. The resulting service calls that are generated include the kit item and the components that are specified in a new window, Service Parts – Kit Components.


Service Call Entry – Parts window

One thing I found interesting when entering a kit on the Service Call Entry – Parts window is, when a kit is selected you have one and only one chance at entering the quantity being ordered before the code breaks down the kit into its components on the Parts window. I also think that a validation is missing where users are warned when a component belonging to a kit is removed from the Service Call Entry – Parts window.

Use workflow to manage service call escalation steps

You can now create a standard workflow to manage the approval process for service call escalation steps. A new workflow type, Escalation Override, applies to service call documents in the Service Call Entry/Update window. You can activate the workflow by using the Service Type – Escalation window.

Hope you enjoyed this 2-part series and have had a chance to explore some of the new Service Call Management enhancements. Huge, huge are the integrations to payroll and the ability to post payables vouchers for subcontractors. This certainly reduce the amount of steps users have to engage in when closing a service call. I won’t be surprised you will see more integration with Manufacturing in a future revision of this module.

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

July 16, 2009

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/


How to calculate the number of days a vendor check takes to clear?

July 7, 2009

I am always amazed at the things you can do with Microsoft Dynamics GP. Just recently, a user pointed out that the Days for Checks to Clear field on the Vendor Credit Summary window was always blank and that they needed this field to update automatically.

According to the help file, the field is defined as “View or change the average number of days that a check you issue to the vendor usually takes to clear your bank.“, in other words, you can manually update it if you need to track this data.

The user also pointed out that they performed daily bank reconciles and that the Cleared Date column in the CM Transaction (dbo.CM20200) table was reflective of the actual check cleared date in the bank and that they would like to use this field to update the Days for Checks to Clear field in the Vendor Credit Summary window. After all, the date value of the Cleared Date column only gets updated when the check is marked as cleared during a checkbook reconciliation process and does not necessarily reflect the actual date the check cleared the bank, but with daily bank reconciles this should not be an issue, because the would be one of the same.

So, if you perform daily bank reconciliations and would like to know the average days a vendor check takes to clear your bank, you could use the following T-SQL query to update this column value:


-- Created by Mariano Gomez, MVP
WITH AVGCTE(VENDORID, AVGDAYS) AS (
SELECT A.CMLinkID, AVG(DATEDIFF(DD, A.TRXDATE, A.clearedate)) AS AVGDAYS
FROM CM20200 A
GROUP BY A.CMLinkID
)
UPDATE PM00201 SET DYCHTCLR = AVGDAYS
FROM PM00201 INNER JOIN AVGCTE ON (PM00201.VENDORID = AVGCTE.VENDORID)

Now what to do with the script? You can schedule it as part of a SQL Server job that will run daily to update this value for you! This will allow you to have accurate average days for each vendor and in turn will allow you to make better purchasing decisions to preserve your hard earned cash.

Until next post!

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


How are Payables transactions work error messages stored?

June 24, 2009

This question came up in the Dynamics GP Partner forum a few days ago, the specifics inquiried by the consultant were as follow:

I was just looking at the PM10000 table in SQL Server Management Studio. I found a column named PMWRKMSG, with a data type of binary(4), which I cannot understand. In addition, I found entries like, 0x00019000, 0x00009000, 0x00000000 as column values. I know that they represent error messages, generated on the Batch Edit List and Posting Journals, but how can I check which message is being generated by just looking at this column? Do I need to convert this entry to some other integer and refer to some other table, which in turn would have a list of all the error messages?

Answer

The ‘PM WORK Messages’ field (PM10000.PMWRKMSG column) is a Dexterity multi-select listbox control (so are the ‘PM WORK Messages 2’ and the ‘PM Distribution Messages’ fields) which contains static texts. The only way of storing such controls (with the listbox values checked or unchecked) on SQL Server is by using a binary data type. During the posting process, a number of failed validation rules will trigger any of the 32 static text values — added by the development team — to be checked in the multi-select listbox control.

The static texts (and possible errors you may receive during posting) for the ‘PM WORK Messages’ multi-select listbox control are:


No vendor record exists for this vendor ID.
This vendor is inactive.
This transaction already has been posted.
Duplicate check numbers are not allowed.
Duplicate invoice numbers are not allowed.
No record exists for this credit card.
No unique voucher numbers are available.
This transaction has been posted and fully paid.
Distributions for this transaction contain errors.
Vendor summary records cannot be updated.
This transaction is recurring; it cannot include a payment amount.
This document should not be applied to other documents.
The total applied amount is incorrect.
Tax detail information is incorrect.
Vendor ID is on hold
Withholding Vendor ID is invalid
Applied record is on hold
Batch information is invalid
GL posting date is invalid
Fiscal period for the posting date does not exist
Fiscal period for the posting date is closed
Taxes Incorrectly Distributed
This transaction contains multicurrency error(s).
This transaction contains errors. It won't be posted.
Transaction Analysis information for this transaction is incorrect or missing.
Transaction contains intercompany distributions; mark as an IC transaction.
Intercompany Processing is not registered;cannot post intercompany transactions
The currency must be either the functional currency,
The currency must be the same as the currency assigned to the checkbook
the Euro currency,
an enabled denomination currency,
or the same as the currency assigned to the checkbook

Assuming the values follow a binary storage pattern, 0x00009000, can be converted to its decimal equivalent of 36,864. In turn, this number can be represented as 2^15 + 2^12. If is the case, the error messages displayed on the batch edit list are: “Withholding Vendor ID is invalid” and “The total applied amount is incorrect”. In the case of 0x00019000, this is 2^16 + 2^15 + 2^12, this would result in the messages “Applied Record is on hold”, “Withholding Vendor ID is invalid” and “The total applied amount is incorrect”.

Of course, these errors can only be cleared via the interface, since resetting the value in SQL Server would have no direct effect on the validation rules applied by the posting process. A transaction will not move to the PM Transaction Open table (PM20000) if all the validation rules are not cleared, which in turn would render a value of 0x00000000 in the column.

The ‘PM WORK Messages 2’ multi-select listbox field stores the following static text values:


You cannot post to a tax date within a closed tax period.
You cannot post to a tax period that has not been set up.
The vendor's remit to address bank format is missing or inactive.
The bank format for the vendor and the checkbook must be the same country.
The bank format for the vendor and the checkbook must be different countries.
The bank format assigned to the checkbook is missing.
This check amount exceeds the maximum check amount for the checkbook.
Remit-To address doesn't exist; please enter a different address.

The above errors are mostly used during the validation of tax computations and vendors setup as EFT vendors.

The ‘PM Distribution Messages’ multi-select listbox field stores the following static text values:


The accounts payable distribution(s) does not equal the actual amount.
The purchases distribution(s) does not equal the actual amount.
The discount available distribution(s) does not equal the actual amount.
The trade discount distribution(s) does not equal the actual amount.
The discount taken distribution(s) does not equal the actual amount.
The misc distribution(s) does not equal the actual amount.
The freight distribution(s) does not equal the actual amount.
The tax distribution(s) does not equal the actual amount.
The cash distribution(s) does not equal the actual amount.
The write off distribution(s) does not equal the actual amount.
The other distribution(s) does not equal the actual amount.
The GST distribution(s) does not equal the actual amount.
The withholding distribution(s) does not equal the actual amount.
The debit distributions do not equal the credit distributions.
No account has been specified for one or more distributions.
The Realized Gain distribution(s) does not equal the actual amount.
The Realized Loss distribution(s) does not equal the actual amount.
The Round distribution(s) does not equal the actual amount.

Hope this helps in troubleshooting and understanding how Dynamics GP processes and manages errors when executing a Payables transaction posting validation.

Until next post!

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