{$page}

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+ please visit this link .

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.

code_formatsql

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.

code

• Revised : 02/01/2013
• Version : 2.0

• Backlinks include_pagepage_componentbacklinks