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

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;