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.
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:
- Use the AQB (Advanced Query Builder) to find the Sales quote number using the Sales Order number by following these steps:
- Create a new Advanced Query Browser, Utility | Advanced Query Builder
- Paste the below query to the SQL Statement tab
h.prv_doc_no AS previous_doc_no,
h.prv_doc_ty AS previous_doc_type
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)
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)
- Click on the Update SQL button
- Set the Primary Key to h.doc_no and put a checkmark and Always prompt parameter dialog
- Click Find to return any Cancelled Sales Quotes based on the Sales Order number
- 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.
- 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(
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: Raditya Perdhevi
-- Create date: 20/10/08
-- Description: Listing the deleted entry in the invoices table with date time
CREATE TRIGGER deletedSQ
-- 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