Sage 200 database

Question

I've noticed that in many Sage 200 databases, looking at the sales side, there aren't enough allocations to make up the allocated value recorded for some rows in the table SLPostedCustomerTran. In other words, the following query returns a non-zero result for some databases:

SELECT count(*)
FROM SLPostedCustomerTran
   INNER JOIN SLAllocationTran on SLPostedCustomerTran.SLPostedCustomerTranID = SLAllocationTran.SLPostedCustomerTranID
GROUP BY SLPostedCustomerTran.SLPostedCustomerTranID, SLPostedCustomerTran.AllocatedValue
HAVING sum(SLAllocationTran.AllocationValue) <> SLPostedCustomerTran.AllocatedValue

Is there another table I should be looking at, perhaps one containing purged data? If not, would it be a fair assumption that the database is corrupted? (These are databases belonging to certain companies and I don't know exactly how the data was entered.)

score 2 · Accepted Answer · answered Jul 14 '16 at 10:33

2

SLPostedCustomerTran holds Invoice/CreditNote/Payment/Receipt all as positive values. Look at SysTraderTranType Table then filter your query by a single TranType to get a more meaningful result.

The Accounting System Manager > System Utilities > Balance Ledgers > Financials should correct any anomalies with allocations.

answered Jul 14 '16 at 10:33

Adam Byrne

71
2

That would make sense. We deal with this for another system where, when reporting, we constantly have to fix up the notional sign of the transaction based on its type and other properties. – underscore_d Jul 14 '16 at 10:37

Sage 200 database

1 Answers1