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
>