Updating the Parts Inventory and the Parts in Storage Locations tables

All transactions that change the inventory update the Parts Inventory (pt) table as well as the Parts in Storage Locations (pt_store_loc_pt) table. The Parts Inventory table keeps track of the cumulative inventory for all storage locations. The inventory quantities in the Parts Inventory (pt) table are updated with the net effect of all storage location inventory transactions.

For example, if you add a quantity of 30 for the Part Code BULB-2A in the DALLAS storage location, this is stored in the Parts in Storage Locations (pt_store_loc_pt) table for that location record, but the Quantity Available for that part also increases by 30 in the Parts Inventory (pt) table.

When you add a new part, the system also updates the fields “Unit Cost – Average” and “Unit Cost- Last Purchase” in the Parts Inventory (pt) table.

Calculations for the Rectify Physical Inventory action

A transaction of 'Rectify Physical Inventory' updates the Parts Inventory (pt) table with the net effect of the calculation in the specific storage location.

The following describes the calculations:

• Set pt.qty_on_hand = pt.qty_on_hand plus (NEW pt_store_loc_pt.qty_on_hand minus PRIOR pt_store_loc_pt.qty_on_hand).

  Example:

  The Parts Inventory (pt) table has a Quantity Available = 100 for Part Code BULB-AB.

  The Dallas Storage Location has a Quantity Available = 30 and a Quantity Reserved = 20 for this Part Code for a total of 50.

  A physical count reveals the Dallas Storage Location actually has 45 of these bulbs, and not 50 as the electronic quantities indicate. When you adjust the inventory, the calculation updates the Dallas Quantity Available to be 25, and updates the Parts Inventory (pt) table value of Quantity Available to be 95.

  If this transaction results in parts becoming unreserved, then the value of Quantity on Reserve (pt.qty_on_reserve) is updated with the net change in the Quantity Reserved in the specific storage location.

• The value of Physical Count Qty. (pt.qty_physical_count) is no longer used, so this field is not updated.

Calculations for transfers

The transaction type Storage Location Transfer uses these calculations:

1. Unit Cost - Average

   cost_unit_avg = [(qty_on_hand + qty_on_reserve) * cost_unit_avg + (new_qty_to_add * cost_unit_avg of “From Storage Location”)] / (qty_on_hand + qty_on_reserve + new_qty_to_add)

2. Increases Quantity Available for the 'To' location . Increases Quantity Available”(pt_store_loc_pt.qty_on_hand) of the storage location in the Storage Location Code field by the amount in the Transaction Quantity (it.trans_quantity) field
3. Decreases Quantity Available for the 'From' location . Decreases Quantity Available (pt_store_loc_pt.qty_on_hand) from the storage location in the From Storage Location field by the amount in the Transaction Quantity (it.trans_quantity) field.

   If there is not enough quantity available to satisfy the transfer, then the rest must come from the Quantity Reserved. This will actually break the existing reservations for that part at that storage location, and all the remaining quantity reserved will become the new quantity available. The quantity transferred will be added the quantity available of the To Storage Location.

   Example:

   Storage Location ID	Part Code	Quantity Available	Quantity on Reserve	Physical Count Quantity
   BOSTON	THERMOSTAT-A	50	25	75
   QUINCY	THERMOSTAT-A	10	40	50

   In this example, suppose the inventory manager wants to transfer 60 units of the THERMOSTAT-A part from the storage location in BOSTON to the storage location in QUINCY. The transfer can execute because the BOSTON location has a total of 75 units, even though only 50 are not already reserved. What will happen is that all the reserved units in BOSTON become unreserved.

   Here is the resulting totals at each location after the transfer:

   Storage Location ID	Part Code	Quantity Available	Quantity on Reserve	Physical Count Quantity
   BOSTON	THERMOSTAT-A	15	0	15
   QUINCY	THERMOSTAT-A	70	40	110

4. Adjust for parts that are unreserved . If a storage location transfer results in parts becoming unreserved, then also update the Quantity Available and Quantity on Reserve for that part in the Parts Inventory (pt) table with the net change of these quantities in the specific storage locations. Parts can become unreserved as the result of a transfer only when working from the Manage Supply Requisitions task. When working from the Adjust Inventory task, transfer, do not unreserve, parts.

Calculation for reserved parts when work requests are closed

The Quantity Available is decremented when parts are reserved. When parts get used (and the work request is closed) the Quantity Available is changed by the difference between the Quantity Used and the Quantity Reserved. In other words, the Quantity Available is only changed if you use a different amount of parts than you reserved. Here is a summary of the calculations for the Parts in Storage Locations (pt_store_loc_pt) table when a work request gets closed:

Note: For a part estimation, the Quantity on Reserve and Quantity Available totals are only adjusted by the Quantity Estimated if there are enough parts available for the reservation, where the status of that part estimate becomes “Reserved” and not “Not In Stock.”