Tracking Missing Sales Quotes Using Sales Order Number

Overview

This article outlines how to audit why Sales Quotes and Sales Orders are missing and provides a custom trigger that can be set up to track future deletions.

Information

Sales Quotes can be purged/deleted through the Sales Quote Browser on the UI, by right-clicking on a particular Sales Quote and selecting purge on the context menu. The same action can also be accomplished through Utility > Purge > Sales Quote.

In both cases, the action will result in missing Sales Orders which can be tracked as follows:

1. Use the AQB (Advanced Query Builder) to find the Sales quote number using the Sales Order number by following these steps:
   1. Create a new Advanced Query Browser, Utility | Advanced Query Builder
   2. Paste the below query to the SQL Statement tab
      

      SELECT h.TRANHIST_id, f.descript, h.tranhist_date, c.cust_code, c.name, h.doc_no, h.doc_type, h.prv_doc_no AS previous_doc_no, h.prv_doc_ty AS previous_doc_type FROM tranhist h INNER JOIN funtable f WITH (NOLOCK) ON (h.func_id = f.FUNC_ID) INNER JOIN cust c WITH (NOLOCK) ON (h.subcode = c.cust_code) LEFT OUTER JOIN invoices i WITH (NOLOCK) ON (h.doc_no = i.doc_no) AND (h.doc_type = i.status) WHERE subcode = :cust_code AND (PRV_DOC_NO = :Doc_no AND h.prv_doc_ty = 8 OR PRV_DOC_NO = :Doc_no AND h.prv_doc_ty = 7 OR h.DOC_NO = :Doc_no AND h.doc_type = 7 OR h.DOC_NO = :Doc_no AND h.doc_type = 8) ORDER BY h.tranhist_date, h.TRANHIST_ID

      
      
   3. Click on the Update SQL button​
   4. Set the Primary Key to h.doc_no and put a checkmark and Always prompt parameter dialog
      
   5. Click Find to return any Cancelled Sales Quotes based on the Sales Order number
2. Check the transaction history for SQ purge activities
   Look out for any SQL purge activities which would explain missing Sales Orders as purged quotes/orders get deleted.
   
   
   
3. Set up a custom trigger on the invoices table.
   Create a custom trigger on the invoices table which will track future Sales Quote purge activities and capture the time when the purge was done which can then be used to crosscheck with the transaction history to isolate when the Sales Quote was purged. This step is useful in determining why Sales Orders are disappearing if the issue occurs again.
   

   create table support_INVOICES_DELETE( order_no varchar(50), status int, actionTime datetime ); SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Raditya Perdhevi -- Create date: 20/10/08 -- Description: Listing the deleted entry in the invoices table with date time -- ============================================= CREATE TRIGGER deletedSQ ON INVOICES AFTER DELETE AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here insert into support_INVOICES_DELETE select doc_no, status, getdate() from deleted END GO

Back to top