Skip to main content
Skip table of contents

SQL - Reverse Financial Records from DOJ Run

Description

SQL used to remove the AP Invoices and JEs created from a bad DOJ run

Usage

Can only be run where vouchers are not Posted

This only covers AP and JE not AR Receipts or AR Applies

SQL

CODE
--delete gl distributions tied to AP
DELETE gl_distributions FROM gl_distributions gld
JOIN ap_invoices ap ON ap.id = gld.object_id
	AND object_type = 'APInvoice'
WHERE ap.accounting_date ='06-01-2021'					-- Accounting Date
	AND ap.source = 'DO Journal Interface'
	and ap.status like 'Importing%';

-- delete AP invoices
DELETE ap_invoices
WHERE accounting_date in('2021-05-30', '06-01-2021')	-- Accounting Date
	AND source = 'DO Journal Interface'
	and status like 'Importing%';

-- delete gl distributions tied to JE
DELETE gl_distributions FROM gl_distributions gld
JOIN journal_entries je ON je.id = gld.object_id
	AND object_type = 'JournalEntry'
WHERE je.accounting_date ='06-01-2021'					-- Accounting Date
	AND je.source = 'DO Journal Interface'
	and je.status like 'Importing%';

--delete journal entries
DELETE journal_entries
WHERE accounting_date in('2021-05-30', '06-01-2021')	-- Accounting Date
	AND source = 'DO Journal Interface'
	and status like 'Importing%';
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.