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

[Jesse P. <jes...@st...>]

> > Like the StockItemMoves table I sent you in those diffs. It=20
> would not
> > hold a record for the inital move (in stockmoves and breaks ref.
> > integrity), and would hole the item#,orig_stkmove, and new_stkmove .
>=20
> This only contains moves of controlled items right with their=20
> reference and
> stockmove id - why would it not show the qty of the batch=20
> movement - where
> is this recorded?  I guess the StockMoves table shows the=20
> total movement of
> all serialised items per your logic. FromStkMoveNo and=20
> ToStkMoveNo - the
> stock movements of a serialised item will not be in a range .... will
> they???
> There's a one StockMoves.StockID to many=20
> StockItemMoves.StockID (I think
> some ref to controlled items would be preferable in the=20
> naming of the table)
> and the StockMoves.NewQOH would contain the sum of all=20
> serialised items in
> stock - not just one serial number item .... would it not??
Yes, ItemMoves would only shows moves between StockMoves. And yes, Qty =
should not be needed there as it should be correct in StockMoves. From & =
To are not ranges... probably OrigStkMove and NewStkMove are better =
names.
Yes, 1 StockMove to many StockItemMoves - and StockMoves.NewQOH works as =
it does today, which is as you mentioned.=20
Remember, though, Controlled & Serialised would be StockMaster fields. =
Serialised implies controlled, but Controlled does not imply Serialised. =
Controlled just forces someone to give some Ref number to each StockMove =
of a StockMaster type.
Possibly we call the tables SerialisedStock & SerialisedStockMoves ??

> OK I get it - that seems fine, just no qty control - probably=20
> ok - I prefer
> my method ;-)
I will keep this in mind and find another way to provide that =
functionality.

> > > - Don't understand you templatey idea?
> > >
> > Do you mean the above, my plan on how to allow easy extension of
> > stockitem details,
>=20
> yes that bit. I see the StockModules tables - I guess it=20
> would reduce a bit
> of input but would require additional sql to get the=20
> necessary info. I don't
> see it as necessary? The stock category could contain any other data
> relevant to the category.
It does require more sql queries to get all the data and maybe a little =
confusing. The thought is, that I actually need to store, say 5 other =
fields fo data for *each* item inventoried, and it will change between =
lots of items. So, StockCat won't do for that... . For example, for an =
IP Phone, I may call the MAC address my serial number and put the =
devices' admin user&pass, assigned phone number, and other Phone =
specific data in a separate table. The Module would act as the 'glue' =
between the SerialisedItems.SerialNo and IPPhones.MAC . The majority of =
items would not need that, though. For example, the GenericStockItem =
module would not have an extra table, but the code would allow it to =
work just like the other one.
Adding a new business specific Stock type now becomes a simple task of =
creating a StockModule and a Table to hold the data... the StockModule =
would also help control the display, making it easier to present data in =
a way that is more specific to a product than general enough to cover =
all of them.

> >
> > > - how do we display all the serial numbers of items on=20
> hand at any one
> time?
> > >
> > >
> > I realized this a while ago as well. All Items will have=20
> their current
> > StockMove associated with them in their table - upkeep=20
> necessary during
> > adds/moves/etc. I figure to select on-hand items the base=20
> criteria will
> > be based on selecting a set of StockMoves with appropriate SysTypes,
> > then selecting Items still tagged with those StockMoves. Do=20
> you know the
> > SysTypes necessary without me digging around to find them?
> I don't understand this. The SysTypes table refers to the various
> transaction types - not all of which create stock movements.=20
It looks to me that finding all SerialisedStock-on-Hand can be done by: =
Get all StockMoves where Type=3D25, then get all SerialisedItems with =
those StockMoveNo's. Limits could easily be put to select all for just a =
Location, that a Customer have, etc... and just require determining =
which Types in StockMoves reflect the Stock you want. Make sense? The =
last part is where i was wondering about which ones should be checked.

> Individual
> stockmoves contain the total balance of all stock in the=20
> location in the
> newqoh field if I understand your logic so the quantity in=20
> the batch is
> unknown. or do you mean to re-calcuate the quantity remaining=20
> on hand in the
> ControlledStockMoves record and just pick up the last
The Qty for a batch of SerialisedItems would simply be the Qty in the =
StockMove they refer to. We may need to talk about exactly how to =
account for StockMove Qty's, but it looks like the same as above, where =
certain Types can be referenced to get the total(s) we are looking for.

> Perhaps summing the qty ins and outs in the=20
> ControlledStockMoves - this
> could be a substantial exercise with your data volumes?
Yes, it could. I did not intend on needing to do this.
=20
> Not using the LocStock table seems nice but what about items=20
> that need to
> mature before they are sold - so they need to be held or products -
> serialised items that must be sold before a given date as per=20
> Danie's note.
> I think if we are doing it, it would be a shame to limit the scope to
> exclude this.
> Are we looking at another table to hold serialised quantites=20
> on hand and
> these variables. The table being maintained like LocStock but=20
> at the serial
> number level - the total of serialised items always agreeing=20
> to the LocStock
> table QOH. There seems a disturbing amount of double up with LocStock
> though.
Agreed, I wouldn't want to double up like that... I read Danie's =
requests - I've never really thought about having to do that, so I =
will... Right now, I'm thinking a StockMaster field for SellAfterDays =
and SellByDays, and then determining Stock status (as above, by Type) =
should just be an exercise of totalling StockMoves and examining date =
differences. What do you think?

> >
> > > - how do we display on invoices the serial numbers of=20
> items sold - if we
> > >wanted to ?
> > >
> > >
> > This can be an option specified in the StockMaster table, in
> > StockModules, or even at the time of printing/viewing the=20
> invoice, and a
> > combination of those...I also forsee situations like I have=20
> where I want
> > to invoice someone for 50,000 cards on a regular invoice (obviously
> > without serialised line items) and have the option to=20
> provide/retrieve
> > the invoice detail in some other format, say a csv list of serial
> > numbers and possibly other data that can be sent to a=20
> customer for their
> > own inventory requirements.
> >
>=20
> I am probably wasting your time with my inability to grasp=20
> the concepts.
> Sorry I just can't get the logic of what you are proposing, I=20
> fear it seems
> to be some way short of acheiving what you hope for. I think=20
> I should just
> leave you to it - give it a go - if it works ... and I do=20
> hope so ... I'll
> include it!
You apologize too much. :) I'm going to get to work on things... if you =
come up with any other concerns of how something might work, bring it up =
so I can think about it as well. I may also end up differing from the =
plan a little once I get to coding since that happens sometimes. I'll =
mention it if I do, though.