Overview

Inventory Distribution in xTuple ERP is a vehicle for users to report inventory transaction details for the movement of a specific item including location and lot/serial control information, the former being availble in the open source Post Books edition and the latter being available in the commercial editions of xTuple.  The main objectives of this enhancement are to improve inventory distribution performance in high transaction volume environments and to allow job costed items to be lot and serial tracked.

Functional Requirements

The requirements for this enhancements are as follows:

• Allow lot and serial tracking of the receipt and issue of Job cost items.
• The job cost flag will be moved to the item site level to allow an item to be handled as a job cost item at one site, but not at others.
• Inventory distribution should occur when initial quantities are recorded, rather then at posting including:
  • Receipts
  • Credit Memos
• Reduce the number of screens presented to the user during the course of inventory distribution.
• Allow lot and serial numbers to be automatically generated on receipt and print corresponding labels.
• Transactions that have relationships to other transactions should be aware of the previous posts
  • Transfer Order receipts should only allow control numbers to be received that are associated with an original Transfer Order shipment.
  • Immediate transfers between sites should infer trace information from the original outbound transaction for the inbound transaction.
  • Work Order Returns should only allow items to be returned that were issued.
  • Purchase Order Returns should only allow items to be returned that were issued.
• Any outstanding bugs related to inventory distribution should be resolved.
• Physical Inventory adjustments should support all costing methods.

New Terms and Definitions 

Configurable Item

• In previous editions only Job and Reference item types could be made configurable.  In 3.5 Job items will become Manufactured items and Manufactured items may be set to configured with any type of costing.  It will be strongly recommended, however, that configured items use job costing or serialized costing so a one for one relationship is maintained between the cost of an item that is built and inventory records.

Job Costing

• Job items were formerly known as a specific item type.  Job costing was introduced in 3.0, but only as a place holder.  In 3.4 the notion of Job as a costing will completely replace the notion of Job item types.  Job costing will be an available cost method on the item site record for manufactured items.  The behavior of Job items will continue to be that are non-inventory and work orders for job items are hard linked to sales orders.

Related Existing Functionality

Currently Inventory Distribution is called in the following windows:

In addition the physical inventory functionality that has different processing means to acheive inventory distribution.  This also includes a sophisticated mechanism to "freeze" inventory that is being counted so that any transactions submitted during a count are not completely distributed until after the count is posted.

Similar and Related Requests

The following Mantis issues are related to the development:

0009868		Enhanced Inventory Distribution
0006642		Duplicate serial numbers possible on inventory count
0008760		Distribution screen holds Postgre
0009780		Serialized Job Items
0009779		Automatically Generate Serial Numbers
0009371		Auto Populate Lot/Serial
0004955		Preserve lot # during Inter-Warehouse Transfer
0009663		Only one itemsite for any item may ship at any time
0009943		blocks on db when certain screens are open?
0009737		Idle timeout does not roll back partial transactions
0009463		default focus for Distribute Stock for serialized items should be on Lot/Serial input box
0009311		Incomplete Lot/Serial transaction leaves orphaned itemlocdist entries
0008432		Possibility to Create Blank Lot Numbers
0008234		Cannot manually enter negative itemloc distribution
0007317		Detailed Inventory History by Lot/Serial #
0007438		Improve lot/serial awareness at receipt
0007444		Cancel TO line should roll all lines back to Shipping
0009347		Inventory distribution should present list of existing lots for lot controlled items being adjusted in.
0007823		Create Lot/Serial# Screen - Enter Qty in Vend UOM
0007480		Ability to Affect Existing Lot Number when Distributing Lot
0007049		Eliminate itemsite_qtyonhand
0006472		inventory checking before transactions
0004441		Restrict Returns to Items/Lots Received
0006766		Balance Item Site Does Nothing

User-Level Functionality

Configure Inventory

The Job costing type which was previously always check and disabled, will now be enabled and checkable or uncheckable.

As with previously available cost methods, any cost method that is currently in use on an itemsite record will be checked and disabled when this screen is open so it may not be unchecked.

Item

The notion of Job item type will be replaced with the Job costing method.   The item type "Job" will be eliminated and all current Job items will be changed to the Manufactured item type.

The following client files reference the job item type in one way or another.  They will either need to be modified to remove the reference, or the reference will need to be changed to the item site cost method of job, rather than the item type job:

• bomItem.cpp
• closeWo.cpp
• correctProductionPosting.cpp
• createItemSitesByClassCode.cpp
• dspJobCosting.cpp
• items.cpp
• postProduction.cpp
• returnAuthorizationItem.cpp
• salesOrder.cpp
• salesOrderItem.cpp
• searchForItem.cpp
• workOrder.cpp

The following database functions that refer to the job item type will need to be modified along the same lines:

• closewo.sql  
• convertquote.sql
• createwo.sql
• deletesoitem.sql
• initialdistribution.sql
• invadjustment.sql
• invexpense.sql
• invreceipt.sql
• invreceiptissuetowomatl.sql
• invscrap.sql
• issuetoshipping.sql
• postinvtrans.sql
• postproduction.sql
• relocateinventory.sql
• resetqohbalance.sql
• returnitemshipments.sql
• returnshipmenttransaction.sql
• sufficientinventorytoshipitem.sql
• updatecharassignment.sql
• updateitemsiteleadtime.sql

Finally, the following triggers refer to job items and will need to be modified as well:

• coitem.sql
• item.sql
• itemsite.sql

Item Site

Item site will support the ability for a job costed item to be lot and serial tracked.  Several additional costing methods will be offered.  Due to changes to support Drop Ship Purchase Orders, the item site will be re-arranged as pictured below.

A an additional group box will be added where users may enable auto-numbering for lot/serial controlled items.  This group box will be hidden if the global metric "LotSerialControl" is false.  It will only be enabled if the control method is Lot or Serial.  If checked, users will be able to enter a number that will be used as the next automatically assigned serial number.

Create Item Sites by Class Code

The Create Item Sites by Class Code window will be modified to include the same functionality that is added to the item sites window.  Note that "Allow automatic ABC updates","Requires Warranty when Purchased" and "Auto Register Lot/Serial at Shipping" were previously missing from this window and should be added in as well.

Inventory Distribution

The basic functionality of inventory distribution will be unchanged.   The most dramatic change to the end user will be the user interface.  In the current implementation users may be presented with up to 3 different distribution windows per item transacted. This number of screens is multiplied by the number of items transacted and further compounded if items are both lot/serial and location controlled to the extent that a user may be confronted with dozens of windows to complete a transaction.  This inventory distribution enhancement will reduce all activity to a single window as shown:

The new Distribute Inventory window will feature a reduction of widgets and buttons. 

• The top will display the site the transaction is being performed on, and the Order number of the driving document if applicable. 
• The data formerly entered in multiple windows will be consolidated into three list widgets contained by two splitter layouts. 
• The left most center widget will be an XTreeWidget that lists all the items being transacted.  For a simple adjustment transaction or a single issue to shipping transaction only one item will be listed.  For batch transactions such as Issue Material by Batch to Work Order or Issue All Balance in Shipping a list of all items involved in the transaction will be listed. It will show a transaction quantity, which is the quantity that was expected to be transacted, and a Distributed Quantity column that shows the total quantity of items distributed.  The distributed quantity will display in read until it is equal to the transacted quantity.  The Post (Ok) button will be disabled until all quantities have been distributed.
• The upper right center widget will be an XTableView that allows the user to build a list of lot/serial control numbers to be entered. 
  • If the Item is lot controlled one row will be filled that defaults with the distribution quantity equal to the full quantity transacted.  If the user changes the quantity to less than the transacted quantity a new row will be created to populate with the remainder.
  • If the item is serial controlled, a number rows will be created equal to the quantity transacted.  The user will populate each row with an appropriate serial number and expiration information if applicable.  If the item site is set to auto-serialize, then all the serial numbers will be populated automatically.  The user will only able and required to enter expiration information if it is applicable.
  • The user will not be able to enter quantities whose total is greater than the transaction total on the item list.
  • If the item is not controlled, then this window will be hidden
• The lower right center widget will be an XTableView that allows the user to select which locations are being distributed to or from
  • If the selected item is serial controlled, then the locations list will apply to the control number selected, otherwise it will apply to the item.
  • The user will be able to type a distribution quantity directly into a row.
  • If a default location existis, that location will have the full quantity distributed by default.
  • The user will not be able to distribute more quantity than his been distributed to the control number, if applicable, or the item if not.
  • If the selected item is not location controlled, this widget will be hidden.

Several windows have been modified recently to process batch transactions iteratively to reduce locking problems in the current paradigm.  To gain the full benefit of using this approach those windows should be reverted back to use the previous methodology that processed transactions in a batch including:

• Issue to Shipping - Issue All (Changed on issue 8157).
• Enter PO Reciept - Post (Changed on issue 8331).
• Issue and Return Work Order Batch (Changed on issue 8342).

Internal Design

Basic Algorithms

Schema Changes