web-erp-developers

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

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

> > Dont have head completely around the stock movement issues. In a
previous
> > system I wrote the stockmoves table held the information about the
item's
> > serial number a stock move being created for all serial number items eg
a
> > stock movement of 3 invoiced out for a controlled item would require
> > specification of each individual item so this would not be possible -
this
> > would need to be a rule enforced by reference to the stock master. Stock
> > Master also needs additional fields for the number of units of measure
per
> > bundle - this would determine whether there is only allowed one
> > item (in the
> > units of measure) per serial number or potentially 1000 of the unit of
> > measure in a lot or 99999 metres per bundle of cloth. Also, the number
of
> > decimal places to display for the stock item in inquiries. eg
> > stock held per
> > tonne but .01 of a tonne is of interest at stock checks etc.
> Ok. 1st, if I hadn't mentioned it, I don't come from a 'physical
inventory'
> world, I come from a 'service/usage world' (which, if you don't know,
you're
> not going to like :) ), so I'm probably not as familiar with that as you
are
> and you may get dumb questions on things...that said...
> I don't really see a need for that. I would consider all of those
> situtations just quantities ordered - the stockmaster item has units,
which
> I had always interpreted as usually being 'each' (as defaulted) or a
> standard unit of weight or measurement, both of which can be easily
> accounted for as a Quantity. Some user friendliness may be in order for
user
> interfaces (order entry, reports...), though,  as most people would not
want
> to have to figure out that they wanted to order 0.001 tonne of something
> when they want a kilogram, though...maybe that leads to a sort of product
> catalog, kinda like the BOMs, with 'preconfigured' StockMoves of what you
> actually sell (as opposed to what you order & stock).
>

Of course the most appropriate unit of measure is used - the point is some
things can and should be allowed to be issued and sold in fractions of a
unit and if so the system should allow the user to determine how many
decimal places to display. Currently we have no way to specify this.


> > In the eg above the stockmoves table would actually record 3 seperate
> > records each with the Bundle field set to the serial number. The invoice
> > would show the serial number of each item sold (or the bundle/lot etc)
the
> > stock quantity by location would reflect the changes effected by the 3
> > separate stockmoves. However we would need to expand the LocStock table
to
> > have bundle (or serialNo or such) in it too, the stock status
> > inquiry would
> > then sum the stock of all serial number items by location.
> Going on the above, and assuming that we can keep up accounting (tedious,
> but doable) such that the Qty in the StockMove is correctly reflected in
the
> StockItems (ie, the reconciliation program would never complain), that
extra
> work shouldn't be necessary.

I guess I am saying I don't see the necessity for a StockItems table as
distinct from the StockMaster and the StockMoves - all information about
each movement is recorded against the StockMoves and all info about the item
itself is in the StockMaster, so what is the purpose of the extra table ??

>In my thoughts, a 'StockMove' becomes an
> extension of the Invoice item - there are options in invoicing (possibly
> attached to StockMaster instead, as a 'Controlled'-only variable) that
allow
> the detail for each item/serialnum sold to be shown, or not, on an
invoice.

Yes that's it in fact on the printing of invoices and credit notes the
StockMoves are what makes up the detail of the invoice.

> The Bundle field, which could be added to the invoice, could make a great
> tracking or lot number to represent Qty sold instead of printing a
StockMove
> num, but may need to be lengthened (though we may also need further ids
for
> shipping purposes (5 of the 25 '30 yard rolls of cloth' per box??).

We already have a quantity field in the StockMoves table - the bundle field
is meant to refer to the actual serial number of the item moving. This is
defaulted to 1 where there is no bundle/bath/lot/serial number control
required.

>I can
> also see a need in various stockmodules to produce a manifest of the items
> the actually went with an invoice.

These should be spelled out on the invoice - this would simply be a listing
of the stock movements displaying the 'bundle' as well on the invoice.

> Keep in mind that I am planning for quite large tables of items - like
> creating 100,000 items and selling them 50,000 at a time, but still
wanting
> to know exactly where every single one of them is.


A fair bit of input - bar coding may well be appropriate too.

Phil

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

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

I definitely see how just using StockMaster will work. I'm trying to make it
work better in environments with large stock needs. Really, I could load
over several million items into this... think about all those stock moves.
I think both our thoughts will work together together fine, though. What if
we made StockMaster types both serialised and controlled? - controlled would
indicate that a bundle reference (stockmoves.bundle) is to be associated
with it, serialised would indicate that both a bundle ref. and
individualised serial numbers existed. best of both worlds? Maybe you could
bounce a couple scenarios off me to make sure I'm not forgetting something?
also, what about the methods of importing all those items that I mentioned?
or printing an invoice w/ even a few thousand items?


jesse

> > > Dont have head completely around the stock movement issues. In a
> previous
> > > system I wrote the stockmoves table held the information about the
> item's
> > > serial number a stock move being created for all serial
> number items eg
> a
> > > stock movement of 3 invoiced out for a controlled item would require
> > > specification of each individual item so this would not be possible -
> this
> > > would need to be a rule enforced by reference to the stock
> master. Stock
> > > Master also needs additional fields for the number of units of measure
> per
> > > bundle - this would determine whether there is only allowed one
> > > item (in the
> > > units of measure) per serial number or potentially 1000 of the unit of
> > > measure in a lot or 99999 metres per bundle of cloth. Also, the number
> of
> > > decimal places to display for the stock item in inquiries. eg
> > > stock held per
> > > tonne but .01 of a tonne is of interest at stock checks etc.
> > Ok. 1st, if I hadn't mentioned it, I don't come from a 'physical
> inventory'
> > world, I come from a 'service/usage world' (which, if you don't know,
> you're
> > not going to like :) ), so I'm probably not as familiar with that as you
> are
> > and you may get dumb questions on things...that said...
> > I don't really see a need for that. I would consider all of those
> > situtations just quantities ordered - the stockmaster item has units,
> which
> > I had always interpreted as usually being 'each' (as defaulted) or a
> > standard unit of weight or measurement, both of which can be easily
> > accounted for as a Quantity. Some user friendliness may be in order for
> user
> > interfaces (order entry, reports...), though,  as most people would not
> want
> > to have to figure out that they wanted to order 0.001 tonne of something
> > when they want a kilogram, though...maybe that leads to a sort
> of product
> > catalog, kinda like the BOMs, with 'preconfigured' StockMoves
> of what you
> > actually sell (as opposed to what you order & stock).
> >
>
> Of course the most appropriate unit of measure is used - the point is some
> things can and should be allowed to be issued and sold in fractions of a
> unit and if so the system should allow the user to determine how many
> decimal places to display. Currently we have no way to specify this.
> > > In the eg above the stockmoves table would actually record 3 seperate
> > > records each with the Bundle field set to the serial number.
> The invoice
> > > would show the serial number of each item sold (or the bundle/lot etc)
> the
> > > stock quantity by location would reflect the changes effected by the 3
> > > separate stockmoves. However we would need to expand the
> LocStock table
> to
> > > have bundle (or serialNo or such) in it too, the stock status
> > > inquiry would
> > > then sum the stock of all serial number items by location.
> > Going on the above, and assuming that we can keep up accounting
> (tedious,
> > but doable) such that the Qty in the StockMove is correctly reflected in
> the
> > StockItems (ie, the reconciliation program would never complain), that
> extra
> > work shouldn't be necessary.
>
> I guess I am saying I don't see the necessity for a StockItems table as
> distinct from the StockMaster and the StockMoves - all information about
> each movement is recorded against the StockMoves and all info
> about the item
> itself is in the StockMaster, so what is the purpose of the extra table ??
>
> >In my thoughts, a 'StockMove' becomes an
> > extension of the Invoice item - there are options in invoicing (possibly
> > attached to StockMaster instead, as a 'Controlled'-only variable) that
> allow
> > the detail for each item/serialnum sold to be shown, or not, on an
> invoice.
>
> Yes that's it in fact on the printing of invoices and credit notes the
> StockMoves are what makes up the detail of the invoice.
>
> > The Bundle field, which could be added to the invoice, could
> make a great
> > tracking or lot number to represent Qty sold instead of printing a
> StockMove
> > num, but may need to be lengthened (though we may also need further ids
> for
> > shipping purposes (5 of the 25 '30 yard rolls of cloth' per box??).
>
> We already have a quantity field in the StockMoves table - the
> bundle field
> is meant to refer to the actual serial number of the item moving. This is
> defaulted to 1 where there is no bundle/bath/lot/serial number control
> required.
>
> >I can
> > also see a need in various stockmodules to produce a manifest
> of the items
> > the actually went with an invoice.
>
> These should be spelled out on the invoice - this would simply be
> a listing
> of the stock movements displaying the 'bundle' as well on the invoice.
>
> > Keep in mind that I am planning for quite large tables of items - like
> > creating 100,000 items and selling them 50,000 at a time, but still
> wanting
> > to know exactly where every single one of them is.
>
>
> A fair bit of input - bar coding may well be appropriate too.
>
> Phil
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

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

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

Hi Jessie,

We are thinking along the same lines.

Sit back grab a coffee ... this is a biggie!

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.

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

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.

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.

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.

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.

Need to check for not pre-existing references and quantities less than or
equal to QtyControl.

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.

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.

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.

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.

So I am proposing ....

New fields in StockMaster:

QtyControlled  int
DecimalPlaces tinyint

New fields in LocStock

Bundle varchar(50)

Modification to the following scripts:

GoodsReceived.php

If controlled we need an additional form to enter bundle refs and qties or
just serial numbers if QtyControl==1 then insert the multiple stock moves
per item being received with the bundle ref against StockMoves.Bundle. We
need to be able to cache the data in an array of Bundles which is a child of
the item information in a similar way to the Item->LineItems->Bundle

Inserts into LocStock for new bundles with quantities in each.

 ConfirmDispatch_Invoice.php

if controlled need form to enter bundle refs. - need some array off the
Items->Bundles to hold quantities and bundle refs. Then insert stock
movements with bundle reference. May need to delete LocStock records if the
quantity of a controlled item gets to 0. Total quantity per line item needs
to be maintained for most of the other updates as usual.

We get into difficulties if a component of an assembly item is controlled -
we should dissallow this on the BOM.php script and check for it when an item
is modified to become controlled ie any parent parts that are assemblies.
Otherwise we would have to specify the bundle refs for assembly components
being invoiced - this kind of defeats the value of assemblies.

 CreditInvoice.php

if controlled items coming back into stock the bundle refs could be read off
the stock movements from the original invoice - need some array off the
Items->LineItems->Bundles to hold quantities and bundle refs. Then insert
stock movements for goods coming back in with bundle reference. Total
quantity per line item needs to be maintained for most of the other updates
as usual. LocStock inserts if the bundle doesn't exist.

 SelectCreditItems.php

if controlled need form to enter bundle refs. for goods coming back into
stock these need to be entered in a similar way to
ConfirmDispatch_invoice.php - the same array off the
Items->LineItems->Bundles to hold quantities and bundle refs used again.
Then insert stock movements with bundle reference. Total quantity per line
item needs to be maintained for most of the other updates as usual.

StockTransfers.php

if controlled need a form to enter the bundle refs and quantites (if
QtyControl>1) StockMoves created with bundle ref.

StockAdjustments.php

if controlled entry of bundle ref and checking QtyControl - defaulting qty
to 1 where QtyControl ==1 and not displaying the quantity field or perhaps
the adjustment quantity could be entered in total and a form for entry of
the bundle refs (or serial numbers) of all items being adjusted and
quantities being adjusted from each bundle (s/n)

All the Stock Check scripts need to allow entry/reporting of bundle refs
too. QtyControl =1 will just need to list the s/n of each item no quantity
required. QtyControl>1 will need the quantity shown next to each reference.

StockStatus.php
Display option to show bundles/lots/serial numbers of amounts held. SQL will
need to sum(Qty) WHERE LocCode='$Location' AND StockID='$StockID'  - 'cos
LocStock now contains (potentially) multiple records for each
item/location - this table now holds the total stock of the
bundle/lot/serial number (1).

ReOrderLevel.php
For controlled items i think we should still maintain a generic serial
number 1 item with a quantity of 0 that has the re-order level in it. The
re-order level can then be determined. Some of the SQL in other scripts
depends on a LocStock record existing for an item in each location even
though the quantity may be 0.

StockValuation reports - detail will need to have an option to show the
detail of controlled items - listing the references (s/n) and quantities in
each lot - this will be 1 for s/n items.

PlanningReport needs to sum the quantity at the location accross all
bundles.

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.

Sorry this is a biggie - this is how I did serial number control in a
previous client server SQL Server system - it worked well. 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 ?

Phil

----- Original Message -----
From: "jesse" <je...@st...>
To: <web...@li...>
Sent: Wednesday, March 24, 2004 5:49 PM
Subject: RE: [Web-erp-developers] Serialised stock - how to code this up


> I definitely see how just using StockMaster will work. I'm trying to make
it
> work better in environments with large stock needs. Really, I could load
> over several million items into this... think about all those stock moves.
> I think both our thoughts will work together together fine, though. What
if
> we made StockMaster types both serialised and controlled? - controlled
would
> indicate that a bundle reference (stockmoves.bundle) is to be associated
> with it, serialised would indicate that both a bundle ref. and
> individualised serial numbers existed. best of both worlds? Maybe you
could
> bounce a couple scenarios off me to make sure I'm not forgetting
something?
> also, what about the methods of importing all those items that I
mentioned?
> or printing an invoice w/ even a few thousand items?
>
>
> jesse
>

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

[Danie B. <br...@na...>]

Hi Phil

Sorry for jumping in here Phil, while you are thinking of adding
batches, why not add hold until and spoil dates and bin ids to the
batches as this is used by retail warehouses, to maintain their batch
stock. I realize this would have to allow for transfer of stock from bin
to bin as well, however no journal entries or stock valuation
functionality would be required for movement inside a warehouse. There
is a valuation issue for spoilable goods though as they should be
devalued or scraped and would be seen as a loss to the company.

Kind Regards
Danie Brink br...@na...




On Wed, 2004-03-24 at 10:23, Daintree wrote:
> Hi Jessie,
> 
> We are thinking along the same lines.
> 
> Sit back grab a coffee ... this is a biggie!
> 
> 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.
> 
> 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
> 
> 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.
> 
> 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.
> 
> 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.
> 
> 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.
> 
> Need to check for not pre-existing references and quantities less than or
> equal to QtyControl.
> 
> 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.
> 
> 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.
> 
> 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.
> 
> 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.
> 
> So I am proposing ....
> 
> New fields in StockMaster:
> 
> QtyControlled  int
> DecimalPlaces tinyint
> 
> New fields in LocStock
> 
> Bundle varchar(50)
> 
> Modification to the following scripts:
> 
> GoodsReceived.php
> 
> If controlled we need an additional form to enter bundle refs and qties or
> just serial numbers if QtyControl==1 then insert the multiple stock moves
> per item being received with the bundle ref against StockMoves.Bundle. We
> need to be able to cache the data in an array of Bundles which is a child of
> the item information in a similar way to the Item->LineItems->Bundle
> 
> Inserts into LocStock for new bundles with quantities in each.
> 
>  ConfirmDispatch_Invoice.php
> 
> if controlled need form to enter bundle refs. - need some array off the
> Items->Bundles to hold quantities and bundle refs. Then insert stock
> movements with bundle reference. May need to delete LocStock records if the
> quantity of a controlled item gets to 0. Total quantity per line item needs
> to be maintained for most of the other updates as usual.
> 
> We get into difficulties if a component of an assembly item is controlled -
> we should dissallow this on the BOM.php script and check for it when an item
> is modified to become controlled ie any parent parts that are assemblies.
> Otherwise we would have to specify the bundle refs for assembly components
> being invoiced - this kind of defeats the value of assemblies.
> 
>  CreditInvoice.php
> 
> if controlled items coming back into stock the bundle refs could be read off
> the stock movements from the original invoice - need some array off the
> Items->LineItems->Bundles to hold quantities and bundle refs. Then insert
> stock movements for goods coming back in with bundle reference. Total
> quantity per line item needs to be maintained for most of the other updates
> as usual. LocStock inserts if the bundle doesn't exist.
> 
>  SelectCreditItems.php
> 
> if controlled need form to enter bundle refs. for goods coming back into
> stock these need to be entered in a similar way to
> ConfirmDispatch_invoice.php - the same array off the
> Items->LineItems->Bundles to hold quantities and bundle refs used again.
> Then insert stock movements with bundle reference. Total quantity per line
> item needs to be maintained for most of the other updates as usual.
> 
> StockTransfers.php
> 
> if controlled need a form to enter the bundle refs and quantites (if
> QtyControl>1) StockMoves created with bundle ref.
> 
> StockAdjustments.php
> 
> if controlled entry of bundle ref and checking QtyControl - defaulting qty
> to 1 where QtyControl ==1 and not displaying the quantity field or perhaps
> the adjustment quantity could be entered in total and a form for entry of
> the bundle refs (or serial numbers) of all items being adjusted and
> quantities being adjusted from each bundle (s/n)
> 
> All the Stock Check scripts need to allow entry/reporting of bundle refs
> too. QtyControl =1 will just need to list the s/n of each item no quantity
> required. QtyControl>1 will need the quantity shown next to each reference.
> 
> StockStatus.php
> Display option to show bundles/lots/serial numbers of amounts held. SQL will
> need to sum(Qty) WHERE LocCode='$Location' AND StockID='$StockID'  - 'cos
> LocStock now contains (potentially) multiple records for each
> item/location - this table now holds the total stock of the
> bundle/lot/serial number (1).
> 
> ReOrderLevel.php
> For controlled items i think we should still maintain a generic serial
> number 1 item with a quantity of 0 that has the re-order level in it. The
> re-order level can then be determined. Some of the SQL in other scripts
> depends on a LocStock record existing for an item in each location even
> though the quantity may be 0.
> 
> StockValuation reports - detail will need to have an option to show the
> detail of controlled items - listing the references (s/n) and quantities in
> each lot - this will be 1 for s/n items.
> 
> PlanningReport needs to sum the quantity at the location accross all
> bundles.
> 
> 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.
> 
> Sorry this is a biggie - this is how I did serial number control in a
> previous client server SQL Server system - it worked well. 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 ?
> 
> Phil
> 
> ----- Original Message -----
> From: "jesse" <je...@st...>
> To: <web...@li...>
> Sent: Wednesday, March 24, 2004 5:49 PM
> Subject: RE: [Web-erp-developers] Serialised stock - how to code this up
> 
> 
> > I definitely see how just using StockMaster will work. I'm trying to make
> it
> > work better in environments with large stock needs. Really, I could load
> > over several million items into this... think about all those stock moves.
> > I think both our thoughts will work together together fine, though. What
> if
> > we made StockMaster types both serialised and controlled? - controlled
> would
> > indicate that a bundle reference (stockmoves.bundle) is to be associated
> > with it, serialised would indicate that both a bundle ref. and
> > individualised serial numbers existed. best of both worlds? Maybe you
> could
> > bounce a couple scenarios off me to make sure I'm not forgetting
> something?
> > also, what about the methods of importing all those items that I
> mentioned?
> > or printing an invoice w/ even a few thousand items?
> >
> >
> > jesse
> >
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> 

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

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

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

> 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.

I can't say I understand your schema completely as yet, but I like the sound
of what you are proposing. This stuff is hard to describe and understand -
at least for the simple like me. Especially when I had in mind a method for
how I did it before. Your points about minimising the data in stockMoves
makes sense - some kind of drill down separate table of SerialisedMoves
would add to the complexity but possibly improve performance I agree.

> 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.

Only "assembly" item BOMs - manufacturing items would of course need BOMs
that contain serialised items. Assembly items are just ghosted codes that
refer to a final assembly put together by the pickers rather than
manufacturing - there is no opportunity to enter serial numbers for the
components in this situation.

> 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?

Yes good point.

>
> Did it actually handle the numbers being thrown around here? As I said,
> desigining it this way makes me nervous...
>
>
No it never had that kind of load.

> > 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.

I'm coming round .... but still need a little more flesh on the idea ...
sorry:
 - what would this subsidiary serialstockmoves table look like?
 - how would the system deal with lots and batches?
 - would there be any control on quantities in batches/lots/bundles or could
these be any amount?
 - Don't understand you templatey idea?
 - how do we display all the serial numbers of items on hand at any one time
?
 - how do we display on invoices the serial numbers of items sold - if we
wanted to ?

I'm a pedantic b/std! Please bear with me I'll get there!

> 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.
>

Thank you for lending your knowledge to the greater good!
Certainly not irritated - enjoying the dialogue :-) .... its the only reason
I do it!
I think the discussion at this stage is great to ensure we get it right.

Phil

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

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

>
>
>Only "assembly" item BOMs - manufacturing items would of course need BOMs
>that contain serialised items. Assembly items are just ghosted codes that
>refer to a final assembly put together by the pickers rather than
>manufacturing - there is no opportunity to enter serial numbers for the
>components in this situation.
>  
>
ok, I think I need to look over how all those types work again...

>
>I'm coming round .... but still need a little more flesh on the idea ...
>sorry:
> - what would this subsidiary serialstockmoves table look like?
>  
>
Like the StockItemMoves table I sent you in those diffs. It 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 .

> - how would the system deal with lots and batches?
>  
>
non controlled, non serialised item - no conept of batches at all - 
StockMoves.Bundles defaults to 1
controlled, non serialised item - bundle serves as an id for a stock 
move. The move maybe for, say, 25 items...maybe hundreds. That can count 
as a Lot, right?

> - would there be any control on quantities in batches/lots/bundles or could
>these be any amount?
>  
>
Any amount  for now.  If needed, it could be a StockMaster field and 
something like 0 or -1 could be unlimited. Alternatively, this could be 
implemented in a Sales or Product Catalog that 'templates' the contents 
of a StockMove like a BOM does. That would, I believe, require the 
Catalog to be setup and used separate of the StockMaster... still 
something I'm thinking about.

> - Don't understand you templatey idea?
>  
>
Do you mean the above, my plan on how to allow easy extension of 
stockitem details, or the original mention of using templates for pages. 
No worries on the last, the middle is better explained once I have code 
written...

> - how do we display all the serial numbers of items on hand at any one time?
>  
>
I realized this a while ago as well. All Items will have their current 
StockMove associated with them in their table - upkeep necessary during 
adds/moves/etc. I figure to select on-hand items the base criteria will 
be based on selecting a set of StockMoves with appropriate SysTypes, 
then selecting Items still tagged with those StockMoves. Do you know the 
SysTypes necessary without me digging around to find them?

> - how do we display on invoices the serial numbers of 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 invoice, and a 
combination of those...I also forsee situations like I have where I want 
to invoice someone for 50,000 cards on a regular invoice (obviously 
without serialised line items) and have the option to 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 customer for their 
own inventory requirements.

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

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

> Like the StockItemMoves table I sent you in those diffs. It 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 .

This only contains moves of controlled items right with their reference and
stockmove id - why would it not show the qty of the batch movement - where
is this recorded?  I guess the StockMoves table shows the total movement of
all serialised items per your logic. FromStkMoveNo and ToStkMoveNo - the
stock movements of a serialised item will not be in a range .... will
they???
There's a one StockMoves.StockID to many StockItemMoves.StockID (I think
some ref to controlled items would be preferable in the naming of the table)
and the StockMoves.NewQOH would contain the sum of all serialised items in
stock - not just one serial number item .... would it not??

>
> > - how would the system deal with lots and batches?
> >
> >
> non controlled, non serialised item - no conept of batches at all -
> StockMoves.Bundles defaults to 1
> controlled, non serialised item - bundle serves as an id for a stock
> move. The move maybe for, say, 25 items...maybe hundreds. That can count
> as a Lot, right?
>

OK I get it - that seems fine, just no qty control - probably ok - I prefer
my method ;-)

> > - Don't understand you templatey idea?
> >
> Do you mean the above, my plan on how to allow easy extension of
> stockitem details,

yes that bit. I see the StockModules tables - I guess it would reduce a bit
of input but would require additional sql to get the necessary info. I don't
see it as necessary? The stock category could contain any other data
relevant to the category.

>
> > - how do we display all the serial numbers of items on hand at any one
time?
> >
> >
> I realized this a while ago as well. All Items will have their current
> StockMove associated with them in their table - upkeep necessary during
> adds/moves/etc. I figure to select on-hand items the base criteria will
> be based on selecting a set of StockMoves with appropriate SysTypes,
> then selecting Items still tagged with those StockMoves. Do 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. Individual
stockmoves contain the total balance of all stock in the location in the
newqoh field if I understand your logic so the quantity in the batch is
unknown. or do you mean to re-calcuate the quantity remaining on hand in the
ControlledStockMoves record and just pick up the last

Perhaps summing the qty ins and outs in the ControlledStockMoves - this
could be a substantial exercise with your data volumes?

Not using the LocStock table seems nice but what about items 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 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 on hand and
these variables. The table being maintained like LocStock but at the serial
number level - the total of serialised items always agreeing to the LocStock
table QOH. There seems a disturbing amount of double up with LocStock
though.

>
> > - how do we display on invoices the serial numbers of 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 invoice, and a
> combination of those...I also forsee situations like I have where I want
> to invoice someone for 50,000 cards on a regular invoice (obviously
> without serialised line items) and have the option to 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 customer for their
> own inventory requirements.
>

I am probably wasting your time with my inability to grasp the concepts.
Sorry I just can't get the logic of what you are proposing, I fear it seems
to be some way short of acheiving what you hope for. I think I should just
leave you to it - give it a go - if it works ... and I do hope so ... I'll
include it!

Thanks for trying to explain ... good luck!

Phil

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

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

Appreciate all input ...

We could add these fields to LocStock quite easily and perhaps a field or
two in StockMaster for DaysToHold, DaysToSpoil to auto calculate these based
on the date of the goods arriving - just an extension of the
goodsReceived.php script.

Gosh bin locations too!
I fear its a good point....

Maybe warehousing and bin locations should be a seperate exercise though
since we would wish to have bin locations for controlled and uncontrolled
stock (inventory). With potentially several bin locations holding one
controlled item. eg a batch of  polypropylene granuals may be stored in
several hoppers. So the ContentsOfBins table is joined on the StockID as a
many to one with the LocStock table.

Phil
----- Original Message -----
From: "Danie Brink" <br...@na...>
To: "Phil Daintree" <Web...@li...>
Sent: Wednesday, March 24, 2004 9:15 PM
Subject: Re: [Web-erp-developers] Serialised stock - how to code this up


> Hi Phil
>
> Sorry for jumping in here Phil, while you are thinking of adding
> batches, why not add hold until and spoil dates and bin ids to the
> batches as this is used by retail warehouses, to maintain their batch
> stock. I realize this would have to allow for transfer of stock from bin
> to bin as well, however no journal entries or stock valuation
> functionality would be required for movement inside a warehouse. There
> is a valuation issue for spoilable goods though as they should be
> devalued or scraped and would be seen as a loss to the company.
>
> Kind Regards
> Danie Brink br...@na...
>
>
>
>
> On Wed, 2004-03-24 at 10:23, Daintree wrote:
> > Hi Jessie,
> >
> > We are thinking along the same lines.
> >
> > Sit back grab a coffee ... this is a biggie!
> >
> > 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.
> >
> > 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
> >
> > 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.
> >
> > 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.
> >
> > 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.
> >
> > 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.
> >
> > Need to check for not pre-existing references and quantities less than
or
> > equal to QtyControl.
> >
> > 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.
> >
> > 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.
> >
> > 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.
> >
> > 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.
> >
> > So I am proposing ....
> >
> > New fields in StockMaster:
> >
> > QtyControlled  int
> > DecimalPlaces tinyint
> >
> > New fields in LocStock
> >
> > Bundle varchar(50)
> >
> > Modification to the following scripts:
> >
> > GoodsReceived.php
> >
> > If controlled we need an additional form to enter bundle refs and qties
or
> > just serial numbers if QtyControl==1 then insert the multiple stock
moves
> > per item being received with the bundle ref against StockMoves.Bundle.
We
> > need to be able to cache the data in an array of Bundles which is a
child of
> > the item information in a similar way to the Item->LineItems->Bundle
> >
> > Inserts into LocStock for new bundles with quantities in each.
> >
> >  ConfirmDispatch_Invoice.php
> >
> > if controlled need form to enter bundle refs. - need some array off the
> > Items->Bundles to hold quantities and bundle refs. Then insert stock
> > movements with bundle reference. May need to delete LocStock records if
the
> > quantity of a controlled item gets to 0. Total quantity per line item
needs
> > to be maintained for most of the other updates as usual.
> >
> > We get into difficulties if a component of an assembly item is
controlled -
> > we should dissallow this on the BOM.php script and check for it when an
item
> > is modified to become controlled ie any parent parts that are
assemblies.
> > Otherwise we would have to specify the bundle refs for assembly
components
> > being invoiced - this kind of defeats the value of assemblies.
> >
> >  CreditInvoice.php
> >
> > if controlled items coming back into stock the bundle refs could be read
off
> > the stock movements from the original invoice - need some array off the
> > Items->LineItems->Bundles to hold quantities and bundle refs. Then
insert
> > stock movements for goods coming back in with bundle reference. Total
> > quantity per line item needs to be maintained for most of the other
updates
> > as usual. LocStock inserts if the bundle doesn't exist.
> >
> >  SelectCreditItems.php
> >
> > if controlled need form to enter bundle refs. for goods coming back into
> > stock these need to be entered in a similar way to
> > ConfirmDispatch_invoice.php - the same array off the
> > Items->LineItems->Bundles to hold quantities and bundle refs used again.
> > Then insert stock movements with bundle reference. Total quantity per
line
> > item needs to be maintained for most of the other updates as usual.
> >
> > StockTransfers.php
> >
> > if controlled need a form to enter the bundle refs and quantites (if
> > QtyControl>1) StockMoves created with bundle ref.
> >
> > StockAdjustments.php
> >
> > if controlled entry of bundle ref and checking QtyControl - defaulting
qty
> > to 1 where QtyControl ==1 and not displaying the quantity field or
perhaps
> > the adjustment quantity could be entered in total and a form for entry
of
> > the bundle refs (or serial numbers) of all items being adjusted and
> > quantities being adjusted from each bundle (s/n)
> >
> > All the Stock Check scripts need to allow entry/reporting of bundle refs
> > too. QtyControl =1 will just need to list the s/n of each item no
quantity
> > required. QtyControl>1 will need the quantity shown next to each
reference.
> >
> > StockStatus.php
> > Display option to show bundles/lots/serial numbers of amounts held. SQL
will
> > need to sum(Qty) WHERE LocCode='$Location' AND StockID='$StockID'  -
'cos
> > LocStock now contains (potentially) multiple records for each
> > item/location - this table now holds the total stock of the
> > bundle/lot/serial number (1).
> >
> > ReOrderLevel.php
> > For controlled items i think we should still maintain a generic serial
> > number 1 item with a quantity of 0 that has the re-order level in it.
The
> > re-order level can then be determined. Some of the SQL in other scripts
> > depends on a LocStock record existing for an item in each location even
> > though the quantity may be 0.
> >
> > StockValuation reports - detail will need to have an option to show the
> > detail of controlled items - listing the references (s/n) and quantities
in
> > each lot - this will be 1 for s/n items.
> >
> > PlanningReport needs to sum the quantity at the location accross all
> > bundles.
> >
> > 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.
> >
> > Sorry this is a biggie - this is how I did serial number control in a
> > previous client server SQL Server system - it worked well. 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 ?
> >
> > Phil
> >
> > ----- Original Message -----
> > From: "jesse" <je...@st...>
> > To: <web...@li...>
> > Sent: Wednesday, March 24, 2004 5:49 PM
> > Subject: RE: [Web-erp-developers] Serialised stock - how to code this up
> >
> >
> > > I definitely see how just using StockMaster will work. I'm trying to
make
> > it
> > > work better in environments with large stock needs. Really, I could
load
> > > over several million items into this... think about all those stock
moves.
> > > I think both our thoughts will work together together fine, though.
What
> > if
> > > we made StockMaster types both serialised and controlled? - controlled
> > would
> > > indicate that a bundle reference (stockmoves.bundle) is to be
associated
> > > with it, serialised would indicate that both a bundle ref. and
> > > individualised serial numbers existed. best of both worlds? Maybe you
> > could
> > > bounce a couple scenarios off me to make sure I'm not forgetting
> > something?
> > > also, what about the methods of importing all those items that I
> > mentioned?
> > > or printing an invoice w/ even a few thousand items?
> > >
> > >
> > > jesse
> > >
> >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by: IBM Linux Tutorials
> > Free Linux tutorial presented by Daniel Robbins, President and CEO of
> > GenToo technologies. Learn everything from fundamentals to system
> > administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web...@li...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> >
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>