Script: Remove all apply records for an account

Summary:

This script removes all apply records for a specific account. It is useful when there is an unknown apply issue or an issue that is not being fixed by rebuilding the apply records, especially when documents are applied to documents on other accounts or to documents that no longer exist.

bDocumentApply
This table holds apply records. It links invoice documents to credit documents and specifies how much is applied.
This is for direct applies only and does not hold data related to budgets or installments.
bDocumentIDPayment and bNonBudgetBalanceAdjustmentIDPayment are for credit documents. This does not necessarily need to be a payment; it could also be something like a negative quantity service document.
bDocumentIDInvoice and bNonBudgetBalanceAdjustmentID hold the ID of the debit document.
The AutoApply column tracks whether or not this apply was done by Auto Apply. A zero in this column indicates that this was a manual apply.

Troubleshooting
Use this script to correct apply issues such as documents applied across accounts or applied to missing documents.

Environment:

• All versions

Notes and Precautions:

This script does change data and does not back anything up. Use caution when running this on accounts without Auto Apply turned on, as this may make it extremely difficult or time-consuming to redo all the apply records.

This will leave all apply records on an account completely undone. In most cases you will want to run Auto Apply against the account after you have done this using:
exec Util_RecreateApplyRecords [bEntityID]

Script Variables:

Script:

declare @bEntityID int = 0

delete a 
from bdocumentapply a
left outer join bdocument d (nolock) on a.bDocumentIDInvoice = d.bDocumentID
left outer join bdocument d2 (nolock) on a.bDocumentIDPayment = d2.bDocumentID
left outer join bNonBudgetBalanceAdjustment adj (nolock) on a.bNonBudgetBalanceAdjustmentID = adj.bNonBudgetBalanceAdjustmentID
left outer join bNonBudgetBalanceAdjustment adj2 (nolock) on a.bNonBudgetBalanceAdjustmentIDPayment = adj2.bNonBudgetBalanceAdjustmentID
where d.bEntityID = @bEntityID
   or d2.bEntityID = @bEntityID
   or adj.bEntityID = @bEntityID
   or adj2.bEntityID = @bEntityID