Remove Debtor Allocations

[SBarnes]

How can I using an SQL statement remove all allocations back to a given date this will be the transaction date for a debtor that is the parent account and include all the allocations on the children as well?

[Mike.Sheen]

How can I using an SQL statement remove all allocations back to a given date this will be the transaction date for a debtor that is the parent account and include all the allocations on the children as well?

Code: Select all

DECLARE @DebtorID CHAR(20) = '00000000080000000002'
DECLARE @DateLimit DATETIME = '2012-10-15'

DELETE
FROM DB_TransAlloc
WHERE (DebtorID = @DebtorID OR DebtorID IN (SELECT DebtorID FROM DB_Main WHERE ParentDebtorID = @DebtorID))
AND DateAlloc >= @DateLimit

Doing this will likely result in the period balances in DB_Main being now wrong - so you can then just run a period rollover and check the 'Recalc balances only' checkbox to have it re-calculate those balances for you.

[SBarnes]

Won't this also mean the allocated amount in DB_Trans will also now be wrong or will recalc balances fix that?

[Mike.Sheen]

Won't this also mean the allocated amount in DB_Trans will also now be wrong or will recalc balances fix that?

Yes and Yes

[SBarnes]

Thanks Mike,

I have found a weird thing with the debtor allocation screen where it over allocates and doubles up see the screenshot, this appears to be when orders may have had products that have more than two decimal places in them, I can't seem to reproduce it in demo data or on debtors where this hasn't occurred.

Are you aware of any issues with the screen?

[Mike.Sheen]

Are you aware of any issues with the screen?

No - but I'll take a look to see if I can see anything obvious in the code.

In the meantime, perhaps a temporary solution which will bring to your attention when it occurs, would be a trigger to RAISERROR if the DB_Trans allocated amount is being set to something greater than the amount?

[SBarnes]

A trigger won't do any good as it's whilst the whole thing is still in memory, I haven't even tried to save it, also I should have mentioned that once it over allocates you can't undo it you have to delete the lines out of the screen.

This is actually to do with the customer who sells seeds who you were looking at some performance issues with during last year.

It happens whether or not you tell the screen to auto allocate credits or debits or not in other words if you load the transaction in and then type in values it happens as well.

One other thing I have just discovered which might explain part of the issue where this is related to parent and child debtors, 0001314972-D01 is on a child account where there are no unaged or not fully allocated credits so it may be related to where the allocation is crossing the boundaries between the children.