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

[Phil D. <ph...@du...>]

> > Yeah I saw your reply which basically confirmed that we are
> > into outer joins
> > or several copies of the table to get all the movements of
> > serialised items.
> Just Left joins... much faster than Full joins or Right Outers, and the
SerialItems you start with are indexes... We could force Inner Joins by
changing the Move Table to record Moves w/o the To/From as you suggested and
inserting the Initial StockMove movement (which I'd left off since I didn't
have an initial From move).

I think that's the answer - no biggie really.

>
> > I think knowing:
> > - to whom and the date a serial item is sold
> > - when a serial number arrived (or was made)
> > - when it was returned if it was credited
> > This is basically the stock movement inquiry I was talking
> > about. I think
> > this could be a sub-inquiry from the stockmovements inquiry
> > ie to click on a
> > stockmovement record to get the lower level serial moves.
> > We also need to know:
> > - what serial numbers we have in stock
> All possible with the current structure... remember, the SerialItems table
is also holding the *current* >StockMove (not serialmove) the Item is
associated with - this allows a straight inner join with >StockMoves - the
'type' of the StockMove tells us whether it is available in inventory. It
may be better >to have SerialItems link to SerialMoves to StockMoves, but
I'll admit I kludged that w/ code to avoid >the extra join on another large
table.

The StockMoves table has the total of all serial items in stock not the
quantity of the actual serial number - this goes to the bundle/roll/batch
issue again. but even a 1 or 0 for SerialItems would be good.

>
> > Not sure how we get this from StockSerialItems ? I am not happy with a
> > requirement to retrieve all stock movements to re-calculate
> > the stock on hand - a 2 minute query - choking other activity - when
there
> > may be none! his also ignores the possibility of purging old data.
> Theoretically (ahem...bear with me) StockSerialItems should not fall out
of sync with the StockMoves >quantities so long as it is coded correctly and
people are not mucking with the database incorrectly - >those are not
scenarios I would try to worry about (well, except the coding correctly).
Really ditto with >the StockMoves table... And if you really needed to
reconcile StockSerialItems against SerialMoves >against StockMoves (I don't
see a need for that ever/often aside from development [now]), it may be
>slow.
>
> > To  my mind this would have to be an update to the SerialStockItems at
the time of
> > a movement.
> Currently there kind of is that ... see my note about the kludged
StockMove above... and the possibilities to unkludge it that I mentioned
really should alleviate any other non-inner joins.
> Then again, if we need to account for people purging records without some
'approved' purge tool that ensures you keep database integrity, alot of
things will need to be change to use left joins and a lot of extra logic
will need to be added to correctly report missing records/data.

My only point is to consider how we might proceed without the movement
records which will be substantial and how we might structure a purge process
that retains the info required.

I like the idea of a quantity against the StockSerialItems  table which
holds the quantity 0 or 1 (or hey maybe more for batch/bundle/rolls ;-) This
would mean that there is no join going on at all to get the stock status of
a particular bundleid - but an extra update at the time of the movement.

Phil