Sage 200 ODBC Stock Report Problem

Sage 200 ODBC Stock Report Problem

Didn't find your answer?

Hi, 

I am hoping someone can give me assistance on an ODBC tables query that even my Sage Support company cant help me with (Or are choosing not to in order to get some report writing income!!)

I am creating a stock report in excel via ODBC using transaction history table.

There are approx 10 different types of transaction and 8 of them I have identified as either increasing or decreasing stock.

However, 2 types seem to post increases AND decreases and I am unable to find a way of correctly identifying the transaction.

Adjustment to Allocation

On POP Adjustment

Somewhere in Sage, it must know whether the transaction is increasing or decreasing the stock position otherwise it would not know in its standard reports what the stock position is.

So either it is keeping a record in one of the tables of whether it should be an increase or it is keeping a record of the type of transaction.

However, I cannot find this info anywhere and my IT support company are saying it doesn't exist...

Any help greatly appreciated

Replies (2)

Please login or register to join the discussion.

avatar
By jonathan.kempson
21st Oct 2013 12:37

In fairness to your Sage support company, your question doesn't fall within a reasonable scope for fixed price support.

I've looked at adjusting allocations.  Reducing allocations generates a transaction type 13.  Increasing allocations (whether initially or after having reduced a previous allocation) generates a transaction type 12.

More widely I've checked a couple of Sage 200 datasets and the Quantity field in TransactionHistory is always positive, and this ties in with each TransactionType corresponding to single sort of change in stock status.

Lastly there is an On POP Order transaction (type 21) that corresponds to the On POP Order Adjustment (type 22) that you mention.

Jonathan

Thanks (0)
avatar
By NikLawrence
04th Dec 2013 18:57

What do you want your report to show?

HI Daryn

The movement determines the transaction type, not the other way around, so it could be that the adjustment type transactions represents both increases and decreases as a positive figure.

If you want to calculate your current stock position I'd suggest basing your report on the warehouseitem or binitem tables instead. 

 

Thanks (0)