1 Topic by jrock (edited by jrock 04/16/2011 08:24:03 am)

  • jrock
  • New member
  • Offline
  • From: Hong Kong
  • Registered: 04/16/2011
  • Posts: 4

Topic: Batch/Lot/Serial Inventory Tracking

Hi All,

Apologies in advance for this long post.

Depending on the response I get to this post, I am considering adding Batch/Lot/Serial Inventory Tracking to FA (collectively referred to as "Lots" henceforth).

==== BACKGROUND ====
I am an accomplished PHP developer who runs a growing Perishable Goods distribution business in Asia.  I implement ERP systems on the side.  So I have the right background to pull this off and my growing business needs it.  But I am trying to decide whether to implement it in FA or an alternative - frankly, I'd prefer doing it in FA.

After testing FA, here are my initial thoughts on how it might work.

==== FUNCTIONALITY CHANGES =====
Items & Inventory -> Maintenance -> Items
---------------------------
Add fields for:
- Serial/Batch Control Reqd? (ie. is tracking required and is it Batch or Serial - see definition further down this msg)
- Is Perishable? (ie. if tracking required, are Best-Before dates required)
- Expiry Offset (days): (ie. if Perishable, default number of days to Expiry from Receiving)

Setup -> Company Setup -> Forms Setup
---------------------------
Add ability to set Next Reference for new Lots.

Changes to Existing Inventory Transactions
----------------------------
Add ability to add/edit/delete one or more Lots (from onoe or more Locations) against the following transactions:
- PO & WO Receiving
- Issuing to WO (including when the shortcut method is used)
- Inventory Location Transfers
- Inventory Adjustments
- Direct GRN (Purchases)
- Direct Delivery (Sales)
- Delivery Against Sales Orders
- SO Returns (ie. Credit Note with "Credit Note Type" of "Items Returned to Inventory Location")
- PO Returns (ie. Credit Note - looks like this needs enhancement to allow definition of a Credit Note Type with resulting stock movement similar to SO Returns above?)

Any other Inventory Transaction trigger points I have missed?

Inquiries & Reports
-------------------------------
- Initially I would just add one or two new Lot-level reports.  Examples: Pick List with Expiration Dates (First-Expiry-First-Out "FEFO"), Lots Locations, Lots Aging.
- If others contribute to add more reports then that would be great.



==== GENERAL LEDGER ====
My understanding is that:
- FA currently offers only the Average Cost method (method 1) for Inventory Valuation
- FIFO or LIFO (methods 2 & 3) could be added regardless of whether Lot Traceability is available (but there has been no real push for this yet).
- Inventory valuation for Exact Stock Lines (method 4 - what is the correct terminology for this?) is not possible unless Lot traceability is implemented.

However, whilst I prefer to "Pick" using FEFO (which has nothing to do with accounting), I value my Inventory (and set my Sales Prices as a margin above) the Average Cost for a product (method 1).

As such, I do not plan to make any changes to the current Inventory Valuation logic (ie. it will ignore Lots as it does now).


==== NICE-TO-HAVES ====
- Add the concept of Sub-Locations (Bins) with a One-Bin-to-Many-Lots relationship
- Review all existing reports for Lot Tracking changes (and Sub-Location changes?) that may be useful


==== OTHER CONCEPTS ====
- Serial numbers will be treated as a special case of Lots where the Lot Size is hardcoded to 1.
- One Location (or sub-Location) can contain many Lots
- One Lot can only be in one Location


==== DATABASE =====
At the database level I would like to:
- Add extra fields to the Items master table(s): Batch/Serial/None, Perishable (boolean), ExpiryOffsetDays
- Keep the existing Inventory-related tables as-is and just add two new Lot Master/Transactions tables. Obviously this is conceptual and I will fit it to FA conventions (I have not reviewed field names, record versioning, etc yet).
- The Lot Master Table might look something like:

LotID (unique) | ItemID (foreign key) | ExpiryDate | LotSize (holds net qty balance) | LocationID (holds current location)

- The Lot Transactions Table might look something like:

LotTransactionID (unique) | StockTransactionID (foreign key1) | LotID (foreign key2) | TransactionTypeID (Adjust,Move,ReceiveWO, etc) | FromLocationID | ToLocationID | Qty

Alternatively, we could make this a double entry table structure with two records per Lot Transaction - eg:

LotTransactionID (unique) | StockTransactionID (foreign key1) | LotID (foreign key2) | TransactionTypeID (Adjust,Move,ReceiveWO, etc) | LocationID | Side (in/out, like debit/credit) | Qty

==========

So, feedback time.

1) How does this look?  Is this what you envisage for the first level of functionality for Lots?  Are there any important complexities/stumbling blocks/ommissions (functional or technical) that I need to consider?

2) Should I implement it as an Extension (possible?) or go straigth to the SVN?

3) Anyone willing to contribute?  Especially with the Testing and Reports & Inquiries where I'm pretty sure I'll get bored....

4) If I go ahead and deliver a stable version of this, how do we ensure it makes General Release ASAP?

Looking forward to your feedback,
js

  • Registered: 12/25/2007
  • Posts: 2,447

Re: Batch/Lot/Serial Inventory Tracking

Looks good. Personally I do not use lot control, but there was big demand for this kind of extension on the forum.

Ad 1. Seems most of the basic functionality for serial/batch control is covered by your description. Probably in final version the new item options should be included also in item category and company setup (as default setting). Also current batch/expire date inquire for controlled items should be implemented.

Ad.2 The best implementation form for such feature seems to be module. Unfortunately current FA framework does not support additional fields provided by modules in standard transaction forms. Such improvement to extension system is not trivial, so maybe better way for now is just to make the serial/batch control included in core FA source. The only condition which have to be fulfilled is additional control in company settings which would switch on/off batch control functionality. As for most  businesses batch control is not required, the related fields for batch selection,  expiry date etc.in delivery/invoice/inventory forms should be completely hidden when the feature is switched off on company level. Otherwise it would be big, annoying and not needed performance lost on data entry for most FA users. (BTW Currently we use Mercurial for version control, and not SVN).

Ad 3. You can rely on FA team help in functionality design process, we can also provide you advice on FA internals later during implementation. I'm also sure there is a lot of current FA users which will serve as testers for your code. Batch/serial control is subject not addressed so far in FA only due to lack of free resources, so if  you want to support the project with your programming skills, you are heartily welcome smile.

Ad 4. If the code you want to implement is consistent enough with core FA source style, and the extension details were consulted with us to ensure the best possible interoperability with other parts of core sources, I see no problem to make it available to public as soon as it is ready and tested.
The only real constraint we have is FA release policy which does not allow database changes in minor releases. Therefore all development should be done on unstable branch available in FA Mercurial repo, which is forthgoing code for 2.4 release.

Janusz

  • jrock
  • New member
  • Offline
  • From: Hong Kong
  • Registered: 04/16/2011
  • Posts: 4

Re: Batch/Lot/Serial Inventory Tracking

OK - thx Janusz,

1) Anyone else got some input on my proposal?

2) Janusz - Would it be viable to create a module that includes (edited) copies of all of the impacted standard forms?  ie. Do it all as a module with:

- Edited versions of existing forms
- New forms
- For a given function, Users can bring up either the Standard Form (if not using Lot Traceability) or, under a different menu, the "enhanced" form with Lot Traceability in it.

Obviously, the database design/transactions would have to be set up in such a way that both can coexist.

3) Why? My thinking is that this approach:
- Lowers my dependence on the rest of the FA development cycle
- At a later date others can take my module and migrate it into the FA core at their leisure.

Viable?

js

  • Registered: 12/25/2007
  • Posts: 2,447

Re: Batch/Lot/Serial Inventory Tracking

Yes, sure! If you are determined to create and make available such extension, I will do my best to help you with this. I can also consider changes in  FA extension system to support running standard forms counterparts from modules directory. Downside of this solution is obvious: the module needs separate maintenance, so later we can consider the best way for smooth integration of batch control option in core sources. Anyway I would like to see working batch control functionality as module first smile.

Janusz

  • From: Indonesia
  • Registered: 04/07/2011
  • Posts: 29

Re: Batch/Lot/Serial Inventory Tracking

This is interesting!
I would like to know when this module will be released. Is there any prediction?

apprayo's Website

  • From: India
  • Registered: 11/09/2009
  • Posts: 115

Re: Batch/Lot/Serial Inventory Tracking

Any update on this ?

Chaitanya
  • From: Suriname
  • Registered: 01/03/2012
  • Posts: 58

Re: Batch/Lot/Serial Inventory Tracking

I am also interested in those feautures that are described above.
I need barcode and serial number traking and the ability to scan the serienummers and make cash sales ( POS )

  • Registered: 01/17/2014
  • Posts: 2

Re: Batch/Lot/Serial Inventory Tracking

I am extremely impressed with Front Accounting however without lot/serial control is is not usable for our company.  If there are any updates to how we can track Lots it is appreciated.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Batch/Lot/Serial Inventory Tracking

Module method is preferred without tinkering with the base FA tables. Create a new lots/bins tables and if necessary make 1:1 tables instead of extra fields in existing tables.

Hooks inside the existing forms for integration of only changed code / new code for changed / new fields and for compute logic will obviate the need for extra maintenance in the module.

apmuthu's Website