Re: [Web-erp-developers] Re: some serialised work

[Daintree <p.d...@pa...>]

> I believe it satisifies this... you'll probably find that soon. Basically
the SerialisedStockMoves >tables does what you want. First, though, realize
that the current StockMoveNo for a Serial Items >is stored in the (soon to
be renamed) StockSerialItems table. That gives us the current 'status' of
>the Item at a moments glance. For a Historical view, there will be multiple
entries in the >SerialisedMoves table per SerialItem that maintains links to
the previous StockMove and the >CurrentStock move. For example, here's what
it might look like:
>
> 5 - PO/GoodsReceived
> 6 - LocationTransfer
> 7,8,9 - SalesOrders
>
> StockId  from    to
> 10  5       6
> 11  5       6
> 12  5       6
> 10  6       7
> 12  6       8
> 11  6       9
> * Note there is no initial record for the PO Received (or what ever the
1st move is) aside from the StockSerialItems entry w/ the CurrentStockMove.
>

So to get the the actual stock movments of a Serialised items Stock Moves we
have some grotesque, miserable, SLOW SQL summat like ...

SELECT  DISTINCT StockMoves.Type, StockMoves.LocCode, StockMoves.TranDate,
StockMoves.Qty (we need to know whether the stock was going in or out of
this location so the sign is important) FROM StockMoves INNER JOIN
SerailisedMoves ON StockMoves.ID=SerialisedMoves.From INNER JOIN
SerialisedMoves AS SerialisedMovesAgain ON
StockMoves.ID=SerialisedMovesAgain.To WHERE
SerialisedMoves.SerialStockID=$SerialNumberWeAreAfter AND
SerialisedMovesAgain.SerialStockID=$SerialNumberWeAreAfter

Not sure if there would be another more efficient way of retrieving the
stock movements of a serial item if not then I believe we need to think
again :-(

StockMoves is already a busy table in its own right and I suspect
SerialMoves will be substanital too - these inquiries will be S L O W.

>This can get hairy, but there's really no option as we need to track all
the data. Consider 1000 >items purchased at a main warehouse, sent to 10
different warehouses, and then sold separarately >to 200 customers. That's
alot of moves and a fairly large complicated report for some of the >options
above.
>
Yeah I agree ... very hairy.

Why couldn't the SerialiasedMoves table look like:

SerialMoveID, Primary auto_increment
StockMoveID,  Foreign key to StockMoves
SerialID, Foreign key to StockSerialItems
Qty - I still think we should build this in although I appreciate your only
interested in 1's

This would be a straight many to 1 with the existing StockMoves table.

I don't believe there is significant extra overhead in this strucutre and it
sure makes getting the required data from the db easier and I would suspect
quicker.

SELECT StockMoves.Type, StockMoves.LocCode, StockMoves.TranDate,
SerailisedMoves.Qty FROM StockMoves INNER JOIN SerailisedMoves ON
StockMoves.ID=SerialisedMoves.StockMoveID WHERE
SerialisedMoves.SerialID=$SerialNumberWeAreAfter AND
StockMoves.StockID=$StockItemWeAreAfter;

I am still obviously to get to the bottom of what you are doing, and because
I don't see it all yet .... I do have some worries :-(

Phil