RE: [Web-erp-developers] Serialised stock - how to code this up

[jesse <je...@st...>]

more comments below, I think we're close...

>
> The stock master contains info about the item ....
>
> We already have a field to define if we want to reference
> items individually
> (controlled) or not.
agreed & agreed.

> However we also need to define the level of control. If it is
> controlled -
> then how many items per bundle, lot or serial number. Lets
> call this field
> QtyControl
Umm... yes and no. It may be a good idea to limit the number of items that
can be associated with, say, a sales order - that goes back around to my
suggestion of a product catalog that templates the available options (like
max qty) ... kind of like a BOM allows, but different ... and refers just to
what you is selected on a SalesOrder. That would also lend well to adding
services later that may not be able to be put into the stock structure. It's
a lot of work, but I don't think it needs to be done yet....

> If it is only one item allowed per lot then this is serial
> number control -
> one reference per item.
> In some businesses it could also be that up to 1000 kgs are
> allowed in a
> batch of cheese production then a food manufacturer would be
> able to trace
> where the batch was received and sold to for quality control purposes.
> If up to 150 metres of cloth could be on a roll - a clothing
> manufacturer
> would want to trace where each roll of cloth was used.
StockMaster:
StockID=ROLL_RED_CLOTH
Desc=Roll of Red Cloth
Units=metres
Controlled=yes
Serialised=no

[several stock moves shown...]
StockMoves:
(1 - from PO)
StockId=ROLL_RED_CLOTH
Bundle=1234321   (some or any control number used by vendor, made up,
defaulted to 1, etc...added upon receipt of goods from PO)
Qty=150
Type=25  (po delivery)
NewQOH=150  (pretend it's the 1st order for simplicity)
(2 - to SalesOrder)
StockId=ROLL_RED_CLOTH
Bundle=A00001   (my Control # attached when I move the item anywhere. Could
be printed label attached when cloth is cut, rerolled, and shipped to
customer, warehouse, etc.)
Qty=10
Type=10 (sales invoice)
NewQOH=140
(3 - to SalesOrder)
StockId=ROLL_RED_CLOTH
Bundle=A00002  (same as #2)
Qty=50
Type=10 (sales invoice)
NewQOH=90
(4 - to SalesOrder)
StockId=ROLL_RED_CLOTH
Bundle=A00003    (same as #2)
Qty=5
Type=10 (sales invoice)
NewQOH=85

and LocStock would work as is with no changes...

> So you see the two fields Controlled and QtyControl allow the
> item to be
> serialised - ie a reference for every unit of the item, or a
> reference that
> can cover up to 999999999 units of the item - or whatever the
> limit of an
> int data type  is.
I'm still sticking with Controlled & Serialised...

> Also, while we are adding fields to the stock master - the
> number of decimal
> places to display would be a cool addition to the feature set.
This should be a User Interface change, not database change. A Standard
Weights & Measures library to handle conversions. Maybe to keep from needing
a product catalog (as referenced above), the field added in StockMaster is
SoldUnits (or the such) and Units will be PurchasedUnits (without changing
field name), and of course checking on StockMaster entries/updates that both
Units agree with each other (ie, someone hasn't entered units=kgs and
soldunits=feet  (thought units=kilograms and soldunits=ounces would be ok)

> When items are received ..... the stock master is examined to
> see if the
> item is controlled or not ... if its controlled then we need
> to use the new
> script to put the stock in and enter the reference for the
> bundle lot or
> serial number - with reference to the stock master QtyControl
> - if its 1
> (serialised stock) then the quantity would not need to be
> displayed it would
> be defaulted to 1 for each reference (serial number) entered.
> If its more
> than 1 then input of the quantity in the lot would be
> required too. Perhaps
> this screen should show a table of  reference text inputs and
> quantities.
Yes... kind of. I still have bad,bad feelings about recording every serial
number in StockMoves.


> Need to check for not pre-existing references and quantities
> less than or
> equal to QtyControl.
Um... just to be certain, I see the Qty being worked with coming from either
the Qty in GoodsReceived or ConfirmDispatch. Any script adding serialised
items would have to be very strict about requiring the number of items
input/selected to exactly match the Qty received or sold/moved.

> When items are (purchase) ordered - then there is no need for
> lot control
> (is there?) since you don't know the lot until the order
> turns up at the
> warehouse gates.
Yes, exactly. That functionality is just in GoodsReceived.

> When items are sold - at the Confirm_DispatchInvoice.php stage the
> quantities and lot numbers would need to be noted by the
> storeman/picker and
> would need to entered in this script for controlled items -
> with quantities
> (if not serialised ie QtyControl ==1). When the invoice is created it
> creates a stockmove for each lot with the quantities entered
> - we already
> have the bundle field to record the lot (or bundle or serial
> number) of the
> item sold.
Agreed... my view on how the data would be laid out is above and reflected
in the tables I've sent. What you mention here is 'controlled' - serialised
would add the same single stock move and add(on received) or update(on
moved/sold) each item's stockmove number, and for sold/moved, add tracking
records for previous stockmoves the item was involved in.


> The updates to the LocStock would need modification since
> LocStock table now
> needs a Bundle field.
> the LocStock table contains information about quantities at each stock
> location - and it should also show the quantities in each bundle - for
> serialised items each quantity would be 1 - and the LocStock
> record for the
> bundle would be deleted when the item/serial number is invoiced or
> transferred to another location.
LocStock should contain counts of StockMaster types for a Location... it
doesn't seem right to extend that to counts of sets of StockMaster types
since they are not different StockMaster types, just sets of the same
StockMaster type.

Consider this. Under the method I've proposed, you can easily break down
LocStock to get at what bundles are in a location by linking back to
StockMaster and looking at Qty's. The big difference is that your way is
adding lots of records to tables whose performance is critical and that are
oft referenced by other scripts. Mine allows those same tables to stay small
enough to gaurantee performace will stay ok and relagates actual
serialization to another table that you will rarely be running large queries
against.


> If controlled==1 then the StockStatus inquiry needs to show
> the bundles of
> stock held arriving at a total at each location. Or at least
> an option to
> show the bundles.
>
> Since the stockmoves are created for every serialised item
> then the stock
> movement inquiry will show the full detail for each bundle -
> we could even
> show just the movements relating to a specific bundle -
> coming in and going
> out. The invoices and credit notes would also display - the movements
> relating to the invoice - serial number by serial number.
Yes, all of that is good... again, though,  under my scheme, all of that is
possible, you don't have to roll up (say count(*) ) bundles, and when you
want full detail on a bundle, the query to pull it simply links against the
Items table and is a 'link' away (ie, serialised items would link to a
detail page, non-serialised would just have no link). Once again, think
about even an an invoice with 1,000 items on it. Do you really want an
Invoice that big?? I would think you would want to summarize it with a
single line and then provide details if needed. It's all tracked, so you can
get it anytime it is needed.

Also, what are your thoughts on getting 1,000 items into and out of
inventory? Someone entering each serial number? An import file works there,
but what about 10,000? 100,000? I'm really not kidding when I say that today
I will sell that many items at one time to a single customer and then have
to be able to take a serial number and tell which Customer received it.


> So I am proposing ....
>
<proposed changes removed for brevity...>
Again, I agree on the functionality, just not the implementation. If there
is something wrong with what I've proposed before, please just give me a run
down of what will not work and a sample so I can consider it and see what
you would think about how I would make that scenario work.
One note was your mention of disallowing BOMs to contain Serialised items. I
disagree... I also think what I've laid out alleviates your concerns with
this. That, and I already have BOMs setup in situations where I definitely
want one to contain both non-controlled,non-serialised items and
controlled,serialised items.
That's a great list though! All the scripts I 'inventoried' as needing
changes and I think a couple more. Some, though, wouldn't be necessary as I
don't require changes to the way StockMoves or LocStock functions today, so
many reporting functions don't break.

> I am not sure about any issues in having many millions of
> stockmoves -  SCSI
> disk size/speed/RAID and plenty of additional RAM to deal
> with the data. The
> script limits the data returned by date for stockmoves
> inquiries by date -
> retrieving moves for invoices and credits might slow from
> less than a milli
> second to a tenth or something. It would need some trial -
> with hopefully
> minimal error. The performance of the system is really quite
> good. Having
> additional tables with links etc would probably be slower
> than one biggie.
> Indexing is also a critical one to watch.

Single selects out of several million items would be quick, but something
like grouping on a stockid, location, etc. and counting would not be that
quick
I certainley will throw whatever hardware at it as necessary - that wasn't
really in question. I just think that optimizing the data layout and what is
being accessed often from the start will save on hardware necessities in the
future... and 'big hardware' doesn't necessarily fix slow running software.
Indexes are certainley important - remember, eventually even an index can
get out of hand, though. Look at StockMoves - that's not only the table
data, but indexes on 12 fields that have to be created, updated, etc when
changes are made.
And, in my mind, the data you would add to StockMoves for each serialised
item in large moves would just duplicate most of the data for the other
items involved... things not duplicated are simply numbers (qty, newqoh). So
why not just keep 1 copy of that data, and move the 1 thing (serial num)
that really needs to change, but rarely needs to be directly referenced
elsewhere and get it out of the way?

> Sorry this is a biggie - this is how I did serial number control in a
> previous client server SQL Server system - it worked well.
Did it actually handle the numbers being thrown around here? As I said,
desigining it this way makes me nervous... I'm seriously not kidding when I
say


> Variable batch
> control is a substantial undertaking and will trash the
> system throughout
> until we get it stable. It does add a fair bit of complexity
> to scripts and
> is not a project for the feint of heart!
> Are you going to take it on ?

Yes, it was my intention to start coding this... soon in fact. Keep in mind,
my original draw to this project was that you have an enormous amount of
work put forth in areas that I had determined as necessary - before I found
it, I would have ended up writing the whole thing (ouch). I was mostly
waiting to see what would come out of these discussions since they obviously
have a good deal to do with the coding...

So, I think the question here is if you actually think my proposed system
simply won't cut it, or if we are just coming to an impasse on design ideas.
If it's the later, I will try to get around to mocking up some tables,
adding lots & lots of records to them, and running exisiting queries/reports
against them to see how they will perform.

Thank you for having these discussions. I really am not trying to irritate
you and am only being this persistant b/c I like the package you've put
together and would like to help make it better - to that end, obviously I
think the method I proposed will scale better than the way you'd planned...
again, though, please show me if you see holes in it so I can rethink my
position.


jesse