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
>