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

Delete a Company in Microsoft Dynamics GP – Compatible With GP 2013


We have a SQL script named ClearCompanies.sql, which is available on Customer Source or Partner Source. This script removes all references to those companies that are not available in SQL Server, but pretty much exist in GP records.

It’s an all important script for all implementers, developers and consultants. Now this script has been updated to cater for also GP 2013. I had not used this script for a long time, so never realised it till today. This is particularly important as GP 2013 now support multi-tenant architecture (multiple GP System DB on same SQL instance).

You can download this script from here (provided you have a Customer Source / Partner Source account): ClearCompanies.sql.

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

Infamous “Sales distribution amount is incorrect” Error


I received an email from one of my users seeking my help in clearing this issue. While posting an invoice, this error message popped up:

SDE-01

 

Upon running the Edit List for this invoice, I realized that it was due to an imbalance in Markdown Amount and corresponding distribution line. Below was the error report that I got:

SDE-02

There seemed to be Markdown entered on one or more line item(s), but there was no distribution line for that amount. But the issue was not THAT simple and didn’t stop there.

When I ran thru’ all line items, none contained a Markdown. Now that’s the problem. After some minutes of thinking, I realized something must be stranded on header record’s Markdown field, for which GP requires a Markdown distribution line, but since line items do not contain any Markdown, it’s not creating one. Strange.

I decided to query the records from SOP10100 (SOP Header) and SOP10200 (SOP Line) to understand the issue. Below is what I found:

SDE-03

SERIOUSLY…?!?!?!?!

But yes, that was the issue. And most baffling thing is, when I tried to reconcile this sales document, this major mishap didn’t get cleared at all.

Obvious solution for this abnormal situation is as follows:

1. Take backup of this SOP10100 record.

2. Update Markdown fields with ZERO.

3. Reconcile this sales document again to see if the above update had caused any imbalance.

Happy new year and happy troubleshooting…!!!

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

Implicit Conversions Causing Deadlocks in SQL Server


Quite an interesting but crucial post up there on Sans SQL blog.

The post explains that Implicit conversions in SQL Server could actually trigger a deadlock. And there are definitions for each important term (such as deadlock, implicit conversion, etc.) with an example.

I believe this post would be useful for those who work extensively on SQL Server.

VAIDY