Removing invoice and PO data from AP database [before a go-live]

Created by Jeremy Burgess, Modified on Thu, 16 Nov, 2023 at 2:29 PM by Brett Cox

Symptoms

You are using the WfD AP database and wish to clear down test data before a go-live event.

Cause

n/a

Resolution

If your matching data (Suppliers, Purchase Orders and Purchase Order Lines) is loaded into a single database and then split into business unit (operating company) specific databases this script will need to be run on both databases.

USE [wfd.ap.prod] -- Replace with your database of choice

declare @bu INT = 0; -- Replace with your business unit of choice

BEGIN TRAN;

DELETE FROM [InvoiceCustomField] WHERE InvoiceId IN (SELECT InvoiceId FROM [Invoice] WHERE BusinessUnitId = @bu);
DELETE FROM [InvoiceLine] WHERE InvoiceId IN (SELECT InvoiceId FROM [Invoice] WHERE BusinessUnitId = @bu);
DELETE FROM [InvoicePage] WHERE InvoiceId IN (SELECT InvoiceId FROM [Invoice] WHERE BusinessUnitId = @bu);
DELETE FROM [InvoiceVatBreakdown] WHERE InvoiceId IN (SELECT InvoiceId FROM [Invoice] WHERE BusinessUnitId = @bu);
DELETE FROM [Invoice] WHERE BusinessUnitId = @bu;

DELETE FROM [PurchaseOrderLine] WHERE PurchaseOrderId IN (SELECT PurchaseOrderId FROM [PurchaseOrder] WHERE BusinessUnitId = @bu);
DELETE FROM [PurchaseOrder] WHERE BusinessUnitId = @bu;

ROLLBACK TRAN;
--COMMIT TRAN;