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
>