More Data Corruption Problems with M2M. Orphan Quote Records

Brief Summary – M2M orphans records in the Quote Master and Quote Items tables when the quotes are in the Cancelled status.

As I mentioned last week, I came across a problem with orphaned records in my addressing tables. Rather than address my orphaned record problems piecemeal, I decided to create a script to check most of the major tables for orphaned records. However, when I started working on quote tables, I found almost 300 orphaned records in the Quote Items Table.

This gave me pause because I was worried that my script wasn’t correct, and that I would delete potentially viable data. After investigating further I came to the conclusion that M2M orphans cancelled quotes as a rule. What is particularly weird is that the M2M screen will actually show you the quotes. However, the data is worthless because you cannot Add and Carry from it.

How did you figure that M2M orphans cancelled quote records?

Well, by testing, of course. I took the following steps:

1. Fired up my test server and opened the practice company containing the educational database.
2. Manually deleted all of the Quote Master (QTMAST) and Quote Items (QTITEM) tables.
3. Created a Prospect (PROS) and then created a quote for said prospect.
4. Attempted to delete the prospect in Started and Open statuses and received the following errors.



5. Changed the status to Cancelled and M2M allowed me to delete the prospect.
6. Completed the previous steps using a Customer rather than a Prospect to the same effect.

I retrieved the SQL statements using Profiler while testing and found the culprit. I still intend to explain how to use SQL Profiler for M2M Administrators. If you look at the code below you’ll notice that it doesn’t mention Cancelled quotes.

SELECT @Status = RTRIM((SELECT TOP 1 fStatus
             FROM Qtmast
             WHERE fcustno = @CustNo
             AND RTRIM(UPPER(fstatus)) IN('AWAITING APPROVAL', 'STARTED', 'OPEN')))

Perhaps Consona developers intended the code to allow deletions of prospects and customers if they only had cancelled quotes, but the least they could do is clean up those records in the deletion process as well.

I’ll follow up in a week or so with the complete script to find orphaned records in your database.