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…



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.


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.


Stored Procedure as Data Source in PowerPivot – Issue & Solution

I am working on a PowerPivot based analysis design and my data source is a SQL Stored Procedure which does the following:

1. Inserts set of records from one DB into a Table Variable.
2. Inserts similarly structured set of records from another DB into the same Table Variable.
3. Finally retrieves records by SELECT…GROUP BY… statement based on necessary criteria.

The above is to ensure that I don’t end up troubleshooing Temp table issues or data redundancy or performance issues for that matter.

PowerPivot understands the above stored procedure very clearly and it also validates the execute query without any issues. It even shows the result set in preview. But it throws the following error message after all the steps involved in setting up SP as data source:

The above error message is quite less informative. I initially thought Stored Procedures are not properly supported by PowerPivot (how silly I am…!!!). But I just gave myself some more time to search forums as usual. After all, I spent almost a day in getting that Stored Procedure completed with 100% accurate results.
The following is the SIMPLE FIX to that menacing issue:
Yeah that’s it. SET NOCOUNT ON is that simple fix. Now my PowerPivot understands this SP and works merrily.
The Post I got this answer from: PowerPivot and Stored Procedure as a SQL Source.
The answer is given by Microsoft Product Team and that was conveyed by Lisa Liu CSS, a Microsoft Moderator.
The reason for why we ought to set NOCOUNT ON is given in the same post by Devarajan KM. The reason is: Set NOCOUNT to ON so that you get only one result set arrived after execution.