153392
/*
Script: Delete AR_Deposit with Safety Checks and Reversal Logic
Purpose: Safely delete deposit records if allowed, or create reversal entries if period is closed
Author: Emeka D
*/
---This is will hep you identify the Deposit_Id to be backed out
SELECT *
FROM AR_Deposit d
WHERE not exists (select deposit_id from ar_deposit_batch where deposit_id = d.deposit_id)
AND AMOUNT <> 0
DECLARE @deposit_id INT = 57135, -----Pass the Deposit_Id you want to delete. make sure you have the right one and only one at a time
@Period_Status CHAR(1),
@branch_id INT,
@acc_ped INT,
@r INT,
@date DATE;
-- ===============================================================
-- 1. Check if Deposit belongs to a Batch
-- ===============================================================
IF EXISTS (SELECT 1 FROM AR_Deposit_Batch WHERE Deposit_Id = @deposit_id)
BEGIN
SELECT 'Cannot Delete Deposit Tied to a Batch';
RETURN;
END;
-- ===============================================================
-- 2. Check if Deposit is Reconciled
-- ===============================================================
IF EXISTS (SELECT 1
FROM AR_Deposit
WHERE Deposit_Id = @deposit_id
AND ISNULL(Reconciled, '') = 'C')
BEGIN
SELECT 'Cannot Delete Deposit that is Reconciled';
RETURN;
END;
-- ===============================================================
-- 3. Get Accounting Period Status (scoped to this Deposit ID)
-- ===============================================================
SELECT TOP 1 @Period_Status = p.Status
FROM AR_Deposit d
JOIN GL_Register g
ON d.Register_Id = g.Register_Id
JOIN GL_Accounting_Period p
ON p.Accounting_Period_Id = g.Accounting_Period_Id
WHERE d.Deposit_Id = @deposit_id
AND g.Register_Id > 1;
-- ===============================================================
-- 4. Drop temp table if exists
-- ===============================================================
IF OBJECT_ID('tempdb..#GL') IS NOT NULL
DROP TABLE #GL;
-- ===============================================================
-- 5. Backup related GL entries for this Deposit into #GL
-- ===============================================================
SELECT DISTINCT Register_Number
INTO #GL
FROM GL_REGISTER
WHERE Register_Number IN (
SELECT Register_Number
FROM GL_REGISTER
WHERE REGISTER_ID IN (
SELECT Register_Id
FROM AR_Deposit
WHERE Deposit_Id = @deposit_id AND Register_Id <> 1
UNION ALL
SELECT Register_Id
FROM AR_Deposit_Register
WHERE Deposit_Id = @deposit_id AND Register_Id <> 1
)
)
OR Primary_Register_Number IN (
SELECT Register_Number
FROM GL_REGISTER
WHERE REGISTER_ID IN (
SELECT Register_Id
FROM AR_Deposit
WHERE Deposit_Id = @deposit_id AND Register_Id <> 1
UNION ALL
SELECT Register_Id
FROM AR_Deposit_Register
WHERE Deposit_Id = @deposit_id AND Register_Id <> 1
)
);
-- ===============================================================
-- 6. Delete AR_Deposit child records first, then parent record
-- ===============================================================
DELETE FROM AR_Deposit_Register WHERE Deposit_Id = @deposit_id;
DELETE FROM AR_Deposit WHERE Deposit_Id = @deposit_id;
-- ===============================================================
-- 7. If Period is OPEN (not 'C'), delete GL entries directly
-- ===============================================================
IF @Period_Status <> 'C'
BEGIN
DELETE g
FROM GL_REGISTER g
JOIN #GL bk
ON bk.Register_Number = g.Register_Number;
END
ELSE
BEGIN
-- ===========================================================
-- 8. If Period is CLOSED, reverse the GL entries
-- ===========================================================
SET @date = GETDATE();
SET @acc_ped = (
SELECT Accounting_Period_ID
FROM GL_Accounting_Period
WHERE @date BETWEEN Start_Date AND End_Date
);
-- Generate next register number
EXEC Register_Next @r OUTPUT;
SELECT @r;
-- Insert reversal entries
INSERT INTO GL_REGISTER
(Account_Id, Register_Number, Credit_Or_Debit, Accounting_Period_Id, Date, Register_Type_Id,
Reference, Type_CVEO, Customer_Id, Vendor_Id, Employee_Id, Other_Name,
Offset_Account_Id, Splits, Amount, Mult_Sign, Memo, Status, Is_Offset, Offset_Register_Id,
Branch_Id, Job_Id, Service_Ticket_Id, Category_Id, Job_Expense_Code, Expense_Type_Id,
Post_WIP_Account_Id, Primary_Register_Number, Part_Id, Currency_Id, Exchange_Rate,
Amount_Nat, Date_Time_Stamp)
SELECT account_id, @r, Credit_Or_Debit , @acc_ped, @date, Register_Type_Id, Reference, Type_CVEO, Customer_Id,
Vendor_Id, Employee_Id, Other_Name, Account_Id, Splits, -Amount, Mult_Sign, concat('Correct Reg #: ', register_number), Status, Is_Offset, 1, branch_id, Job_Id,
Service_Ticket_Id, Category_Id, Job_Expense_Code, Expense_Type_Id, Post_WIP_Account_Id, 0, Part_Id, Currency_Id, Exchange_Rate,
-Amount, GETDATE()
FROM GL_REGISTER WHERE REGISTER_number in (Select Register_Number from #GL);
END;
EXEC Reset_Totals
