GP 2015 DYNAMICS DB Upgrade – Issue & Resolution


This issue is probably remote and may not be faced by many of you out there. But I thought I would share this information with you all, just in case.

Issue: I was upgrading my GP 2013 to GP 2015. By the time, GP Utilities (almost) finished, it got stuck with an error while updating the table SY_Company_MSTR (Company Master) as shown below. I have tried to capture the entire error details on one screen:

Screen Shot 2015-01-03 at 8.34.42 PM

Error Explanation on Table Error Details had more information that I could not capture above. Below is that additional information. I’d rather say, that’s the exact error message:

Screen Shot 2015-01-03 at 8.35.16 PM

While that greyed portion denotes a company DB name.

Reason: I copied one company database (out of 8) backup from my production environment, restored it on my test machine and ran the utilities to upgrade to GP 2015. When Utilities tried to update Company Master table (SY01500, SY_Company_MSTR), it found records pertaining to companies other than one that’s existing on my test machine while it could not find the respective databases physically.

Resolution: If you are going to have less companies on your upgrade scheme than what’s there on your original GP environment, then you must run Clear Companies script. This script will clear all records pertaining to companies that are not physically available on your SQL instance.

Once I cleaned up the company records, my upgrade process went on successfully.

VAIDY

Average Days To Pay Calculation – History & Open (SQL Script)


In my previous post, Average Days to Pay Calculation (SQL Code), I had provided a SQL stored procedure that calculates a customer’s ADTP for a given point of time (between two dates). While this was perfect, it does not include fully applied but open invoices.

Some of the readers (particularly Tim and Steve Pena) requested to amend the script to consider open invoices that are fully applied. An invoice remains open even after fully applied only when we do not run paid transaction removal (PTR). I wanted to work on this script as soon as possible, but somehow I could not.

Better late than never, isn’t it?

Please find the link below to download the SQL procedure that calculates a customer’s ADTP for a given point of time, but looks at both history (RM30101) and open (RM20101) tables, take invoices that satisfy following criteria:

  • Invoices that are fully applied.
    • If invoices are in history table, by default, current transaction amount would be zero.
    • If invoices are in open table, then take those invoices whose receivable outstanding amount is zero.
  • Invoices that are not voided.
  • Invoices that have a document amount, meaning non-zero.

I have verified this script against some sample customers for whom, invoices were either in history (RM30101) or in open (RM20101) or in both.

As always, feedbacks are welcome.

RM_AverageDaysToPay.sql

VAIDY

Simulate DEX_ROW_ID in a SQL View Using ROW_NUMBER() – #MSDYNGP


I have a requirement in which I have to access a SQL view from within my customisation dictionary, in order to create a custom lookup for users to select a value based on an Extender form and an Extender lookup. Easiest option is to create an Extender view (which in turn creates a SQL view for us).

Now, this is the view that I am suppose to refer to from my custom dictionary. Dexterity allows us to refer to any SQL object by simply create a TABLE definition and mention the SQL object (table or view) name as the physical name.

Dex View

Everything looks perfect till you actually see below error messages at runtime:

Dex View - Error 1

Dex View - Error 2

Error message is quite obvious; you do not have DEX_ROW_ID in that SQL view that you are referring to. Every single Dexterity table must have DEX_ROW_ID at the backend. It cannot afford to not have one.

So how am I going to resolve this? By simply adding a record number dynamically to the SQL view created by Extender. How to do that? By adding the T-SQL function ROW_NUMBER(). This is how I achieved it:

ROW_NUMBER USAGE

 

Definition of ROW_NUMBER() can be found here: ROW_NUMBER (Transact-SQL).

A simple yet powerful SQL function has given me the power to do what I wanted in no time. Oh, and my custom lookup referring to this view is working like a charm. Users are happy and so am I.

VAIDY

Fixed Assets Depreciate Button Disabled For User – Solution


This issue was a bit tricky but not THAT tricky, though.

One of the users reported to me that he was not able to run depreciation because when he opened the Depreciation Process Information window (Microsoft Dynamics GP -> Tools -> Routines -> Fixed Assets -> Depreciate), the Depreciate button was disabled. Below was the screenshot that he sent to me (disabled button is highlighted in RED box):

Depreciate Window

 

Reason: After quite a bit of analysis (Dex Script Log & SQL Profiling using SDT), I found that there is a table where FA module temporarily records the depreciation activity initiated by any user and will retain that record the process is complete.

User confirmed to me that last time when he ran the depreciation process, it got stuck at the time of printing the results.

Solution:

The table which records the activity is FA40203 (display name: Book Setup for Depreciation). The table contains three fields: User ID, FA Book Index & FA Book ID.

I took a backup of this table (a simple T-SQL SELECT * INTO…) and removed that stranded activity record for that user.

User confirmed that he could then start depreciation process.

Its always good to start a day with some brainy troubleshooting. But not certainly good for users to start with an issue, though.

VAIDY

Move Expired SOP Quotes To History – Leslie Vail


Leslie Vail has posted an article at a time when I am currently working on closing down thousands and thousands of SOP quotes which users failed to close down. It’s about a SQL Script which moves all expired SOP quotes to history.

(Some really lame) reasons I use to hear for not closing down quotes:

1. We don’t know when that quote would be materialised.

Seriously? Gotta be kidding me. If it’s a quote of age 2+ years and you still don’t know whether it would get materialised or not, then something is wrong fundamentally.

2. If it’s voided, we won’t be able to copy the line items of that quote.

Wrong. You can. Copying line items from an SOP document is very much possible, even if it’s transferred or voided.

3. We don’t know whether the comments that we had added specific to that quote would be available for us to reuse it.

Answer is a positive YES. You can reuse the comments, because comments are NOT stored on that document but on Comments Master. Just select the ID and there it is. Unless, you have edited that comment on the document. But again, come on, it’d take 5 mins for you to inquire the voided one, copy the comment text and paste it on new one.

And so on…

VAIDY

Seven Sins Against T-SQL Performance – Grant Fritchey on Simple Talk


UPDATE: this was drafted long ago, but couldn’t really got to post it till now. You might find this post a bit old, BUT it’s quite relevant even now and extremely informative post.

I came across this gem of an article which lists out 7 things that affect T-SQL performance.

I do not want to take anything out from that article and post it here, as you would realize by reading the original post, you would learn some very important concepts.

Read it here: The Seven Sins Against T-SQL Performance.

VAIDY

T-SQL Function for Proper Case Format


We do not have a built in T-SQL function to convert any string (or a statement) to Proper Case format, also known as Title Case. This I feel is a very simple feature that SQL Server could have provided us, but missing even in it’s latest version, SQL Server 2012.

I thought I would highlight some links which would be useful for T-SQL programmers in our community:

  1. David Wiseman’s Solution
  2. Jeff Smith’s Solution
  3. Pinal Dave’s Solution

There are many more solutions, but above are noteworthy.

VAIDY

Formatting SQL Procedures


Have you ever opened a standard GP stored procedure?

I do it at least on a weekly basis and have always found it impossible to read as it is. So I end up aligning the procedure first and then read it to understand the logic.

Not anymore. David has shared information on some portals which does this in seconds and give us an aligned code.

Standing out from his list is Poor SQL, from what I learned from my usage.

And there is a plugin for SSMS which does this from within SSMS. This tool is called SSMS Tools Pack.

Happy aligning.

VAIDY

Item Decimal Places Currency & Inventory Adjustments


Q: From where does the Inventory Adjustment entry retrieve a product’s Decimal Places Currency value?

A: When we create a product, this value will be defaulted from the Functional Currency of that company. While entering an Inventory Adjustment for this product, the decimal places currency value will then be retrieved from the product master record.

I realized this when I faced an issue couple of days back. I got a requirement where, I have to facilitate an automated program that will replicate a product information (Master, Quantities/Site, Vendors, Currencies & Price List) from one company to another. Of course, with necessary changes that are specific to the destination company.

It worked merrily till both companies had functional currencies with same number of decimal places. I had to extend this program on to another company, whose functional currency supports 3 decimal places. Now you might have realized the issue. My program, quite honestly, was written with a hardcoded value of 2 decimal places.

When I created some products on my new company using this program, I could not enter Inventory Adjustments with 3 decimal places. It was always 2. Upon spending some time on this, I realized what I have mentioned at the start of this post. You cannot override this at all.

So those who write customization like what I have explained above, beware of all such nuances which will play very crucial role in day-to-day transactions.

VAIDY

Fiscal Year Start Date & End Date – SQL Query


I am currently working on custom SSRS dashboards performance tuning and related exercises. One task among all is to automate the Fiscal Year Start Date and Fiscal Year End Date based on which Fiscal Year we are in.

If the fiscal year is the same as calendar year, we can hard code the values to 1-Jan-[current year] and 31-Dec-[current year]. Since it’s not in my case, I had to dynamically get the dates from somewhere.

The simplest way for me is to query this from GP Fiscal Periods Setup table, which is SY40101.

Following is the query, if anyone would like to know how the dates are retrieved:

SELECT FSTFSCDY Fiscal_Start_Date,

LSTFSCDY Fiscal_End_Date

FROM SY40101 

WHERE YEAR1 = CASE WHEN MONTH(GETDATE()) >= [first month of your company fiscal yearTHEN YEAR(GETDATE()) ELSE YEAR(GETDATE()) – 1 END

With above, I can now be rest assured that by the time a new fiscal year is started, my dashboards would automatically get refreshed with new start & end dates.

This query would also work if the fiscal year is as good as the calendar year.

VAIDY