How to fix PNL and Trial Balance not matching issue

Here is how to solve the issue when debit and credit in PNL or trial balance do not match:

For example, here is an older report from A-ONE, where debit and credit do not match:

As you can see, debit & credit do not match

Ensure postings are completed

For domains: All metro domains

Run the following command to make sure that STAX postings are completed:

SELECT orders_id, SUM(amount_debit - amount_credit) FROM accounts_transaction tr
GROUP BY orders_id
HAVING SUM(amount_debit - amount_credit) <> 0

Make sure that the problem is with STAX, then run the following query:

INSERT INTO #tmp_postings
SELECT orders_id FROM accounts_transaction tr
GROUP BY orders_id
HAVING SUM(amount_debit - amount_credit) <> 0

-- Run STAX postings
DECLARE @OrderID INT

DECLARE OrderCursor CURSOR FOR
SELECT OrderID FROM #tmp_postings

OPEN OrderCursor
FETCH NEXT FROM OrderCursor INTO @OrderID

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Perform STAX postings
    EXEC INV_STAX_POSTING @OrderID

    FETCH NEXT FROM OrderCursor INTO @OrderID
END

CLOSE OrderCursor
DEALLOCATE OrderCursor


DROP TABLE #tmp_postings

Check for Deleted Customers

Run the following command

SELECT lineAccountCustomerId, * FROM accounts_transaction tr
LEFT JOIN gm_acc_w_client client ON tr.lineAccountCustomerId = client.accountCustomerId
WHERE client.accountCustomerId IS NULL

After confirming with your senior, reinsert the customers or update the transactions

UPDATE accounts_transaction SET modifiedBy = '<your name>', lineAccountCustomerId = '<new ID>' WHERE lineAccountCustomerId = '<old, deleted ID>'