Search code examples
sqlsage-erp

Sage 200 database

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.)

Solution

  • 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.