This query will delete all Transactions and their activities that are linked to the 'Test Company'. The 'Test Company' was added to the OOB v2.72 dataset, so this query is only available for OOB v2.72 to OOB v2.78C. Transactions and activities linked to other companies will remain untouched.

For a query that works with OOB v2.78D+ purge_all_transactions_for_test_company_oobv2.78d .

HIGH. This is a series of delete queries which will permanently delete data from your database. Be sure to create a backup of your database before running these queries.

IF EXISTS (SELECT *
  FROM sys.foreign_keys
  WHERE object_id = OBJECT_ID(N'dbo.FK_TransPart_TransDetail')
  AND parent_object_id = OBJECT_ID(N'dbo.TransPart')
)
ALTER TABLE [dbo.TransPart] DROP CONSTRAINT [dbo.FK_TransPart_TransDetail]
GO
DECLARE @Confirmation VARCHAR(10);
SET @Confirmation = 'Yes' -- Comment this line if you are inserting directly into Control's report setup.
-- SET @Confirmation = '' -- Remove comments from this line if you are inserting directly into Control's report setup.
UPDATE Account
SET CreditBalance = 0
WHERE ID = 1001 AND (@Confirmation = 'Yes')
;
UPDATE JOURNAL
SET AccountID = (SELECT AccountID FROM Transheader WHERE ID = Journal.TransactionID)
WHERE TransactionID IS NOT NULL AND (@Confirmation = 'Yes')
;
UPDATE LEDGER
SET AccountID = (SELECT AccountID FROM Transheader WHERE ID = Ledger.TransactionID)
WHERE TransactionID IS NOT NULL AND (@Confirmation = 'Yes')
;
DELETE FROM Ledger
WHERE ID > 1000 AND AccountID = 1001 AND (@Confirmation = 'Yes')
;
DELETE FROM Journal
WHERE ID > 1000 AND AccountID = 1001 AND (@Confirmation = 'Yes')
;
DELETE FROM ContactActivity
WHERE ID > 1000 AND ID NOT IN(SELECT ID FROM Journal) AND (@Confirmation = 'Yes')
;
DELETE FROM Payment
WHERE ID > 1000 AND ID NOT IN(SELECT ID FROM Journal) AND (@Confirmation = 'Yes')
;
DELETE FROM TransPart
WHERE ID > 1000 AND TransheaderID IN(SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = 1001) AND (@Confirmation = 'Yes')
;
DELETE FROM TransDetailParam
WHERE ID > 1000
    AND ParentID IN( SELECT ID FROM TransDetail
                        WHERE ID > 1000 AND TransheaderID IN
                    (SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = 1001)
                    )
     AND (@Confirmation = 'Yes')
;
DELETE FROM TransMod
WHERE ID > 1000 AND TransheaderID IN(SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = 1001) AND (@Confirmation = 'Yes')
;
DELETE FROM TransDetail
WHERE ID > 1000 AND TransheaderID IN(SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = 1001) AND (@Confirmation = 'Yes')
;
DELETE FROM TransVariation
WHERE ParentID IN (SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = 1001) AND (@Confirmation = 'Yes')
;
DELETE FROM Transheader WHERE ID > 1000 AND AccountID = 1001 AND (@Confirmation = 'Yes')
;
DELETE FROM VendorTransDetail
WHERE ID > 1000 AND TransheaderID NOT IN(SELECT ID FROM Transheader) AND (@Confirmation = 'Yes')
;
DELETE FROM TransDetailGraphic
WHERE ID > 1000 AND ID NOT IN(SELECT ID FROM Transdetail) AND (@Confirmation = 'Yes')
;
DELETE FROM TransHeaderUserField
WHERE ID > 1000 AND ID NOT IN(SELECT ID FROM Transheader) AND (@Confirmation = 'Yes')
;
DELETE FROM TransTax
WHERE ID > 1000 AND TransheaderID NOT IN(SELECT ID FROM Transheader) AND (@Confirmation = 'Yes')
;
DELETE FROM Shipments
WHERE ID > 1000 AND TransHeaderID NOT IN(SELECT ID FROM Transheader) AND (@Confirmation = 'Yes')
;
DELETE FROM PartUsageCard
WHERE ID > 1000 AND TransheaderID NOT IN(SELECT ID FROM Transheader) AND (@Confirmation = 'Yes')
;
DELETE FROM InventoryLog
WHERE ID > 1000 AND TransDetailID NOT IN (SELECT ID FROM TransDetail) AND (@Confirmation = 'Yes')
;
DECLARE @NextID INT;
SET @NextID = (SELECT COALESCE(MAX(ID)+1,1) FROM SetupStepsLog);
INSERT INTO SetupStepsLog
VALUES(
  @NextID
  , GetDate()
  , HOST_Name()
  , SUSER_NAME()
  , 'Purge All Info Related to Test Company'
  )
;
-- Select 'Yes' -- Remove comments from this line if you are inserting directly into Control's report setup.
  • Revised : 02/01/2013
  • Version : 2.0
You could leave a comment if you were logged in.