How to delete a fiscal year that was incorrectly created

Well is that time of the year again...

Most of you are in the midst of closing 2012 and getting Microsoft Dynamics GP configured for fiscal year 2013. If you are in this process of closing 2012, make sure to check out the Year-End closing procedures series over at the Microsoft Dynamics GP Support and Services team blog:

Microsoft Dynamics GP Year-End Closing Blog Series 2012 Schedule!!

The past few days however, I have had to address a number of calls asking for a little help deleting fiscal years that have been setup incorrectly. The following screenshot illustrates one of such issues:

Fiscal Periods Setup window

For the most part, users enter the correct start and end dates for the period, but mistype the fiscal year, which can cause a lot of headaches since there's no Delete button to remediate the situation. In addition, if the year is changed to 2013, the system will not allow you to create a new fiscal year with the start and end dates of a previously setup fiscal year.

So with no Delete button, where do you go?

Well, you can always resort to a little T-SQL to fix the problem by running the following script against the company database:

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
DELETE FROM SY40100 WHERE YEAR1 = 2103;
DELETE FROM SY40101 WHERE YEAR1 = 2103;
DELETE FROM SY40102 WHERE YEAR1 = 2103;
DELETE FROM SY40103 WHERE YEAR1 = 2103;

NOTE: in the above example the fiscal periods were properly setup to reflect 2013 start and end dates, but the year was incorrectly entered as 2103 - fat fingered if you will.

As is customary, please run this and any script against your test environment first to determine if you achieved the expected results. Backup your production databases environment prior to executing.

Once you have executed the script, please proceed to recreate the fiscal year and fiscal periods.

Until next post!

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

Read More

Microsoft Dynamics GP 2013 "Feature of the Day" - Week 19 Digest

By now, you are aware that the planned release date for Microsoft Dynamics GP 2013 is December 19, 2012. As such, the "Feature of the Day" series will be concluding soon. Inside Microsoft Dynamics GP brought 4 more features last week, ranging from HR/Payroll to Encumbrance, to Purchase Order Processing and Payables Management.

Feature of the Day: FICA Tax Sheltered Annuity Changes

Deduction Setup window

Employee Deduction Setup window

Original article: Feature of the Day: FICA Tax Sheltered Annuity Changes
Link to presentation: GP2013 FOD FICA Medicare and Social Security TSA.ppsx

Feature of the Day: Encumber General Journal Transactions

Encumbrance GL Transaction Entry and Journal Inquiry

Original article: Feature of the Day: Encumber General Journal Transactions
Link to presentation: GP2013 13740_GL Encumber FOD.ppsx

Feature of the Day: PTO Detail & History and PTO Exception Reports

PTO Detail & History Inquiry

Original article: Feature of the Day: PTO Detail & History and PTO Exception Reports
Link to presentation: GP2013 FOD PTO Detail - History and PTO Exception Report.ppsx

Feature of the Day: Computer Prepayments

Purchase Order Entry / Payables Batch Entry / Receivings Transaction Entry

Original article: Feature of the Day: Computer Prepayments
Link to presentation: GP2013 Computer Prepayment.ppsx

Until next post!

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

Read More

Microsoft Dynamics GP 2013 "Feature of the Day" - Week 10 Digest

It's been 10 weeks so far of great features in Microsoft Dynamics GP 2013. The list seems to be endless, but at the same time super informative. This shows the amount of work that went into making this one of the biggest releases ever.

Enjoy this weeks features:

Feature of the Day: Journal Entry History Inquiry

Journal Entry Inquiry

Original article: Feature of the Day: Journal Entry History Inquiry
Link to presentation: GP2013 FOD Journal Entry History Inquiry.ppsx
Learning script: N/A


Feature of the Day: Edit Remit To Address

Edit Remit to Address

Original article: Feature of the Day: Edit Remit To Address
Link to presentation: GP2013 FOD Edit Remit To Address.ppsx
Learning script: N/A


Feature of the Day: Update 1099 Information

Update 1099 Information

Edit 1099 Transaction Information

Original article: Feature of the Day: Update 1099 Information
Link to presentation: GP2013 FOD Update 1099 Information.ppsx
Learning script: N/A


Feature of the Day: Update Item Allocation Inquiry in Field Service

Service Call Entry - Parts showing allocated item

Can now inquiry Field Service allocations from Item Allocation Inquiry window

Original article: Feature of the Day: Update Item Allocation Inquiry in Field Service
Link to presentation: GP2013 FOD Update Item Allocation Inquiry from Field Service.ppsx
Learning script: N/A


Feature of the Day: Multitenant SQLReports

Can now select deployment folder for reports of a specific company

Original article: Feature of the Day: Multitenant SQLReports
Link to presentation: GP2013 FOD Multi-tenant SQL Reports Server.ppsx
Learning script: GP2013 Multitenant SQL Reports Server Learning Script

Until next post!

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

Read More

Microsoft Dynamics GP 2013 "Feature of the Day" - Week 9 Digest

Just back from the Microsoft Dynamics GP Technical Airlift 2012, I could probably write a full series with the remaining features left to be covered, but that honor goes to the Inside Microsoft Dynamics GP blog team, which was able to kick these out while the event was in full swing in Fargo, North Dakota.

Here are the highlights from last week:

Feature of the Day: Multi-Select Serial Numbers

Item Serial Number Entry

Original article: Feature of the Day: Multi-Select Serial Numbers
Link to presentation: GP2013 FOD Multi-Select Serial Numbers.ppsx
Learning script: N/A


Feature of the Day: Saved Batch Approval

Query shows Approval User ID (APRVLUSERID) and Approval Date (APPRVLDT) columns with info

Original article: Feature of the Day: Saved Batch Approval
Link to presentation: GP2013 FOD Saved Batch Approval.ppsx
Learning script: N/A


Feature of the Day: Unapply Credit Documents when Voiding a Check

We start out with a fully applied invoice for $1,070 by a payment and a credit memo

We now void the payment for $705.00

Now the invoice and the credit memo return to open after voiding the payment

Original article: Feature of the Day: Unapply Credit Documents when Voiding a Check
Link to presentation: GP2013 FOD_UnapplyCreditDocumentsWhenVoidingCheck.ppsx
Learning script: N/A


Feature of the Day:Update Back Order Item Qty in Field Service

Work Order Entry/Update generated back order quantity of 2 and updates overall item BO qty

Original article: Feature of the Day:Update Back Order Item Qty in Field Service
Link to presentation: GP2013 FOD-Update Back Ordered Item Qty from Field Service.ppsx
Learning script: N/A


Feature of the Day: Pay Code, Benefit & Deduction

Original article: Feature of the Day: Pay Code, Benefit & Deduction
Link to presentation: GP2013 FOD Pay Code, Benefit & Deduction Modifier.ppsx
Learning script: N/A

Until next post!

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

Read More

Microsoft Dynamics GP 2013 "Feature of the Day" - Week 6 Digest

Six weeks! Can you believe it? And so far 30 new features in Microsoft Dynamics GP 2013. The features have ranged across the board: payables, receivables, SOP, POP, inventory, system manager, you name it! Without further due, here are the features for Week 6 digest.


Feature of the Day: Requisition List

Requisitions Navigation List in HR & Payroll

Original article: Feature of the Day: Requisition List
Link to presentation: GP2013 FOD_Requisition_List.ppsx
Learning script: N/A

Feature of the Day: Consolidated Invoicing

Sales Bulk Confirmation window

Original article: Feature of the Day: Consolidated Invoicing
Link to presentation: GP2013 FOD Consolidated Invoicing.ppsx
Learning script: GP2013 Consolidated Invoicing Learning Script

Feature of the Day: Reconcile Bank Reconciliation to General Ledger
 

Reconcile Bank Rec to GL

Original article: Feature of the Day: Reconcile Bank Reconciliation to General Ledger
Link to presentation: GP2013 FOD Reconcile BR to GL.ppsx
Learning script: GP2013 Reconcile BR to GL Learning Script


Feature of the Day: 64-Bit Microsoft Office Compatibility
 

Configure Email Settings and Office 64-bit Compatibility

Original article: Feature of the Day: 64-Bit Microsoft Office Compatibility
Link to presentation: GP2013 FOD_64-Bit Office Compatible.ppsx
Learning script: N/A

Feature of the Day: Intercompany Asset Transfer

Fixed Assets IC Transfer window

Original article: Feature of the Day: Intercompany Asset Transfer
Link to presentation: GP2013 FOD Intercompany Asset Transfer.ppsx
Learning script: N/A

As you can tell, the Product Management team and Dynamics GP developers have been hard at work making things happen. I just can't wait to see the final product all put together.

Until next post!

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

Read More

Year End Closing... NOT!

It's been a quiet, quiet few weeks over on this blog, but trust me, it's not because I have forgotten about you out there, but rather because my work load has increased over the past weeks, overseeing 5 projects (3 of which are going live in the next few weeks), ramping up some new IntellPartners ventures - more on that later - and welcoming new implementation challenges in South America. But that's not all... I am also gearing up for the GPUG Summit 2012 in Seattle and the Microsoft Dynamics GP Technical Airlift 2012 in Fargo, North Dakota.

However, and despite the hectic schedule, I wanted to bring to your attention a case that I had a chance to work on recently. This was for one of my clients here in Atlanta with manufacturing facilities in China. My client hosts Microsoft Dynamics GP here in their Atlanta headquarters and provide Citrix access to their subsidiaries in China to enter information into the Chinese company database. Things move at a different pace there so the office in China just got around closing the 2011 year. The accounting department quickly noticed that all beginning balances for 2012 where zeroed out - including all asset accounts.

Of course, the immediate questions came to mind.

1. Did you follow KB article 888003 - Year-end closing procedures for General Ledger in Microsoft Dynamics GP. Upon close inspection of the fiscal periods configuration, it was noted that 2011 did not appear to have been closed, this is, the year was still not marked as historic.

2. Can you run a trial balance as of December 31, 2011? After running a summary trial balance as of December 31, 2011, things appeared even more puzzling. The following is a result of that trial balance (some names have been obfuscated to protect the innocent).

Trial Balance Report

The trial balanced showed all accounts - except for a few - with zero ending balances.

3. The next question at hand was simply, when did you noticed this and what changes or entries have been posted in your system lately?

After some back and forth with the Chinese accountants, it was noted that they posted "couple entries" to "close the year". A few SQL queries later and we found the culprits causing the balances to net out to zero. We removed the journals and ran a reconcile on year 2011 and we were back in business.

This time around, the accounting staff in Atlanta executed the year-end closing procedures and all balances rolled forward as expected and everyone left home just on time to beat rush hour traffic. An ensuing memo is awaiting the Chinese staff with the proper year-end closing procedures.


Until next post!

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

Read More

SSRS: GL Trial Balance Summary report returns no data - Follow Up

Back in July of last year, I wrote an article on a reported issue with the SSRS GL Trial Balance reports were not returning any data with Microsoft Dynamics GP 2010 and 2010 R2 (SP2) - see SSRS: GL Trial Balance Summary report returns no data.

Since then, this problem has been documented in KB article 2588519 - GL Trial Balance SRS Reports return no data using Microsoft Dynamics GP and a workaround provided in the same article to fix the ailing stored procedure, dbo.seeglPrintSRSTrialBalance, causing data not to be returned.

The good news is, this problem is scheduled to be fixed in Service Pack 3.

Until next post!

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

Read More

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/

Read More

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/

Read More

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/

Read More

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/

Read More