Home > Technical > Data > StockAudit

Stock Audit

The STOCKAUDIT table is the most important table regarding stock. It tracks all movement to stock. If there is a change to stock on hand, ordered quantity, allocated, in-transit, requested, returned or costs, the information is tracked in STOCKAUDIT.

The sections on Stock Movements and Stock Audit also provide useful information on how this information is used.

The most information field in the table is the strSource field. This determines the content of the data in the record and how it should be processed. The values used in strSource are as follows:

OPEN

The opening stock on hand. This record is created when the STORERNG record is first created for the item/location. It is also created every time a stocktake is done. The system basically takes the number various numbers ( dblStockOnHand, dblAllocatedQty, curAvgCost, etc) from STORERNG and creates a new STOCKAUDIT record to store the numbers. OPEN records are known points of valid data (that's why its created when a stocktake is accepted). When calculating totals we look for an OPEN record, which will give us the starting numbers as of a certain point in time, and we add the other records to it.

PO

Generated when a Purchase Order is sent. This will show an increase in the ordered quantity.

GR

Generated when a Goods Receipt is accepted. This will show the increase in stock of the received items. If the GR was matched to a PO, the ordered quantity will be decreased.

IM-R

This is similar to the GR type and is generated when an Invoice is accepted. It is only generated if Invoicing is also used to receive stock. If you use Goods Receipt to receive stock, you would get GR records and no IM-R records.

IM-C

These are generated when an Invoice is accepted. It shows the difference between what was expected to be paid, and what was actually paid. It is used as an adjustment for average cost calculations. If the invoice cost and the purchase order cost are the same, you will not get an IM-C record.

STKA

These are generated when a Stock Adjustment is accepted. It shows the increase/decrease in stock on hand. If stock is being decreased, the cost of the adjustment is the current average cost. If the stock is being increased, the cost will be the current buy cost.

STK

These are generated when a Stocktake is accepted. It shows the increase/decrease in stock on hand. If stock is being decreased, the cost of the adjustment is the current average cost. If the stock is being increased, the cost will be the current buy cost. You will only get these records if there is a variance for the item. If the expected and actual counts are the same, you will not get a record.

TFR

These are generated when an Internal Transfer is accepted. There will be one record generated for the receiving location and one for the sending location.

TFRI

These records are generated by the Transfer Request. If the Request is being Sent, the record will be adjusting the ordered quantity. When the Request is accepted (after stock has been received), the stock on hand will be increased and the on order will be decreased.

TFRO

These records are generated by the Transfer Request and Transfer Send. When the Transfer Request is Sent, the system will automatically create a Transfer Send. This will create a TFRO record with an increase to requested quantity. When the Transfer Send is accepted, it will show a decrease to the requested quantity and an increase to the in-transit quantity. When the Transfer Request is accepted and the receiving stores stock is adjusted, a TFRO for the sending location to show a decrease in the in-transit quantity and a decrease in stock on hand.

POS

These are generated from POS transactions and basically show a decrease in stock for a sale and an increase for a return. If this is a decrease, the cost will be the average cost. If this is an increase, the cost will depend on whether the return was matched to an original sale. If it was, the cost will be the cost from the original sale. If it was not matched, it will be the current buy cost.

The STOCKAUDIT.lintReference column points to the original transaction. This is used in conjunction with the strSource field to find the specific transaction, for example strSource = STKA and lintReference=33. This is the Stock Adjustment transaction number 33. In the case of POS, the lintReference is the transaction number ( DAILYSALES.intTransNum).

There are two 'stock' quantity fields in the system. dblStockQtyAdjust is used to adjust stock on hand and average cost calculations. dblCostQtyAdjust is used to adjust average cost calculations. The reason two numbers are stored is for cost adjustments from invoices.

Lets say you receive five units of stock. We expected to pay $50 each but we paid $55. There will be two STOCKAUDIT records. One to say we received five units at an expected cost of $50 and a second one to say we were charged an extra $5 for five units. If we simply had one quantity column we would not be able to adjust the stock on hand or average cost correctly.

For example:

Qty Adj

Total SOH

Cost

Avg Cost

5

5

$50

$50

5

10

$5

$37.50  ((5 x $50)+ (5 x $25))/(5+5)

So we need to record the quantities separately:

Stock Qty Adj

Total SOH

Cost Qty Adj

Cost

Avg Cost

5

5

5

$50

$50

0

5

5

$5

$55 ((5 x $50)+(5 x $25))/ (5+0)

Using STOCKAUDIT Records To Calculate Totals

The STOCKAUDIT records are used to calculate various figures such as stock on hand and average cost for any point in time. It does this by  looking for the first OPEN record before the required date. It then reads all the STOCKAUDIT records from this OPEN record forwards. The records are sorted by ctrCode. These records are added to that OPEN record.

Look at the following records. We want to calculate the stock on hand as of the 20/1/2006. We find the first OPEN record prior to 20/1/2006. This is the one on the 15/1/2006, for a quantity of 1. We then add the records after that OPEN record (the POS and the GR ones). Stock on hand is 1+ (-1) + 5 = 5.

Type

Date

Stock On Hand Adjustment

OPEN

10/1/2006

0

GR

11/1/2006

2

POS

12/1/2006

-1

OPEN

15/1/2006

1

POS

17/1/2006

-1

GR

19/1/2006

5

It should be possible to use any OPEN record and add forward. In this case we ignore any subsequent OPEN records. So if we took the OPEN record from 10/1/2006 we would have 0+2+(-1)+(-1)+5 = 5.

These two examples have assumed we wanted the stock on hand as of today. But we can also calculate numbers up to a specific date. If we wanted to know the stock on hand as of the 18/1/2006, we would find the OPEN record before that date and add all records up to that date (whether it was inclusive or exclusive of the 18/1/2006 would depend on whether we wanted the stock as of the start or end of day). So that would be the OPEN record for the 15/1/2006. 1 + (-1) = 0.

STOCKAUDITSUMMARY

The STOCKAUDIT table stores the low level detail for each stock movement that occurs in the system. The STOCKAUDITSUMMARY table stores a summary of this data. This is used to improve the performance of a number of reports.

A STOCKAUDITSUMMARY record will be created when a STORERNG record is created. Each time the system records a STOCKAUDIT record, the system will perform various stock counts as well as average cost/rebate calculations and update the corresponding STOCKAUDITSUMMARY records. When a stocktake is accepted, it will change the intStatus of the current record from 1 to 0, to indicate this is now a historical record, and create a new record with an intStatus of 1. There will only ever be one record for a location/item with an intStatus of 1. This is the current record and is the one that will be updated when new STOCKAUDIT records are written.

If you were to read the STOCKAUDIT records since a given stocktake and add the various values, you would get the same numbers as reading the current STOCKAUDITSUMMARY (where intStatus=1). It is this ability which allows us to use STOCKAUDITSUMMARY to significantly speed up reports.

The STOCKAUDITSUMMARY has a set of fields for quantity, average cost value, average rebates value and last invoice cost value. This set includes the following:

Open + Receive + Transfer In + Transfer Out + Sales + Returned + Adjustments = Close

Note, the above calculation will always hold true for quantity. It may not hold true for the value though. The reason for this is that the closing value is always the closing quantity multiplied by the cost. This matches the way the value would be calculated if you multiplied the STORERNG.curAvgCost x STORERNG.dblStockOnHand and if you were to add the STOCKAUDIT records.  However, if invoices for stock are entered after items are sold and there were variances between expected and actual costs, the received value will reflect this cost adjustment which the closing value won't.

There are a few fields worthy of further discussion:

intStatus

This can have a value of 1 or 0. A 1 indicates that this is the current record for the location/item. A 0 indicates an older record. There will only ever be one record with an intStatus of 1. When a stocktake is accepted, the system will change the intStatus from 1 to 0 and create a new STOCKAUDITSUMMARY record with a status of 1.

lintReference

The number of the stocktake that created this STOCKAUDITSUMMARY record. To see movements since a specific stocktake, you can look for the record with lintReference equal to the required stocktake number.

lintFinalReference

The number of the stocktake that finalised this record. When a stocktake is accepted, the system will update the current record to have an intStatus of 0 and set the lintFinalReference to the stocktake number. It will then create a new record with intStatus of 1 and lintReference to the stocktake number. To see movements leading up to a specific stocktake, you can look for the record with lintFinalReference equal to the required stocktake number.

Converted from CHM to HTML with chm2web Pro 2.85 (unicode)