Year End Stock Counts/Line item Files

[RStewart]

We are in the planning stages of a new live inventory system for my company... My question is two fold:

When dealing with "line items" that will effect inventory levels, is it a good idea to have the line items share a common file? (so that purchase orders and sales orders both store line item data in the same file) Or is better to use separate line item files?

Second question: Does anyone have a pointers (or links) to some ideas on how to handle year end inventory/stock counts? The inventory levels need to be reset to the qtys that were counted at year end; but, we still need to refer back to the previous year's data. I would like to avoid cloning the file set and starting fresh.

The first time through i used a transaction number to calculate if a given transaction should be included when tallying inventory (example:For each transaction "IF CutoffNumber < TransactionNo then StockQty=Qty else StockQty=0)

This of course became quite a burdensome overhead on the system to calculate after 70K line item records had been added.

I was thinking about just using a field to flag records and then perform changes via scripting (since it would only be done once a year anyway)

I am curious, however, to see if anyone has a different approach on this issue or even some recommendations on archiving practices or methods.

Thanks in advance for your efforts.

[LiveOak]

I would probably use separate files for sales order and invoice transactions, unless there is some really good functional reason they need to be combined in the same file. Either approach will work.

The purest way to do inventory is to post credit and debit transaction to a file. The inventory quantity is never reset, it is just the sum of all the transactons, just like the balance in your checking account. Your account balance is just the addition of all the transactions. A year end inventory adjustment is just a credit or debit correction which results in a sum which agrees with the counted quantity, againk, just like an accounting file adjustment.

With this type of transaction file, you can recreate the inventory level at ANY point in time by adding up the proper subset of transactions.

The only downside is the time it takes to calculate this "hot" number. Many systems use a single quantity number for working calculations. This single number for item inventory quantity is set to the sum of all inventory transactions for this item calculated during down time the night before. Daily transactions are applied to this working number, rather than adding up all historical inventory transaction on the fly.

Many inventory systems count 1/12 of the inventory items every month, rather than counting all items once a year.

-bd

[RStewart]

So basically i should keep line items separate and then use scripts to post inventory debits/credits to a separate stock file?

The one down side with adjustments, i have found, is that if you make an adjustment and then someone comes along and eventually corrects the cause of the initial variance, then you get a situation where you are over corrected (so to speak) I suppose there are work around for this (aside from requiring people to think about something while doing it )

Great idea about using a daily qty for the stock levels....Definitely going to explore that option further...Thanks

[LiveOak]

That's the beauty of a transaction based system. To correct the cause of the initial variance, the user must also post an adjustment. No going back and changing any transaction after it is posted. It's easy to find an incorrect adjustment with a note attached, it's hard to discover which of yesterdays 1000 transactions has been altered.

-bd

[belgiumbruno]

I am also making an inventory system and I totaly agree with the suggestions.

The only problem you have to taken into account is the fact that with this system your system becomes very big after a few years (or maybe already after a year). Also a lot of data is old and not frequently used anymore.

But you can't delete this data because you work with calculated totals.

I included an temp_archive and an archive field in my stock for the year end.

At the end of the year I put the total of the stock in the temp_archive.

then I delete the old data. Now my calculated stock is not correct anymore because of this deletings. So I made a script which puts the difference

temp_archive(=total stock before delete)- stock in archive.

The formula for the stock = purchase-sales-archive.

With this trick you can delete old items so that your db doesn't become too

big (and sloooooooooooowww)

[BobWeaver]

If you are concerned that the file may get too many records, it is always possible to run a 'compress' script which replaces all records older than a certain date with a single 'opening balance' record.

[Ugo DI LUCA]

Well, I cannot find out why :

- we should have different line items for purchase and client orders/invoices

- we should have a separate file for Inventory, as it is "strictly" attached to the result from the operations made into the line items using records from the product file.

Why not keeping the flues into a Relational Database, flagging the records that wouldn't be changed (to prevent errors) ?

I agree that the year/end count should be an adjustment record (specially flagged with specific message). For accounting purpose, even if we could retrieve this count from the line items, I would set this record into the product file.

The Inventory wouldn't be a big flat file, as it would only use "active" records from the line items. I've just finished the job for my new inventory and I cannot see what could be the problem. I mainly used selfjoins into the line items to calculate the inventory levels per article (and subcategory) and it gives me good results for the moment (as a test file).

Please give me some advice if I'm going the wrong way.