Archibus SaaS / Maintenance / Part Inventory
Maintenance / Corrective Maintenance / Inventory Manager
Maintenance / Preventive Maintenance / Background Data - Maintenance

Calculations for Inventory Transactions

The system performs the following calculations and updates when you transfer reserved parts, make manual adjustments to the parts inventory, or close work requests.

The Inventory Transactions (it) table records all manual inventory transactions, and transfers. Parts that are used for work orders are not recorded in this table, as parts added for work requests do not represent manual adjustments.

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:

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 CodeQuantity Available Quantity on Reserve

    Physical Count Quantity

    BOSTON THERMOSTAT-A5025

    75

    QUINCY THERMOSTAT-A 1040

    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 CodeQuantity Available Quantity on Reserve

    Physical Count Quantity

    BOSTON THERMOSTAT-A150

    15

    QUINCY THERMOSTAT-A 7040

    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:

Value Calculation
Quantity on Reserve Quantity on Reserve - Quantity Estimated (wrpt.qty_estimated)
Quantity Available Quantity Available + Quantity Estimated (wrpt.qty_estimated) - Quantity Used (wrpt.qty_actual)
Inventory Value
  • (Quantity Available + Quantity on Reserve) * Unit Cost - set by User
    		•
    Quantity Understocked If Quantity Available >= (Minimum To store + sum (unreserved parts)), then 0, else (Minimum To store – (Quantity Available – sum (unreserved parts)))
    Unreserved Parts quantity estimated for part estimates that have a status of Not In Stock or In Stock, Not Reserved (wrpt.status = ‘NI’ or ‘NR’).

    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”.