Product Warehouse Inventory Audit Report overview

Product > Reports > IC Reports > ICROW

Function acronym: ICROW

Use this report to balance each warehouse and the products contained in it to the outstanding sales orders, purchase orders, warehouse transfers, and Kit Production work orders.

If the quantities in inventory records do not match Sales, Purchase, Warehouse Transfer, and Kit Production, you can correct the discrepancies in the Product module.

If you need to adjust the inventory by a positive amount, the following entry is created:

  DR CR
IC Control $200.00  
Physical Adjustments   $200.00

The sort order for the report is by warehouse then by product. Products are checked; labor products and build-on-demand kits are not eligible for the report.

The following quantities from Product Warehouse Product Setup-Costs for each product within each warehouse are selected and compared to the totals for the outstanding orders, purchase orders, transfers, and work orders.

  • Reserved
  • Committed
  • Back Order
  • On Order
  • Unavailable
  • WT In Transit
  • WT Ship Requests
  • WT Rcv Requests
  • Demand
  • Available quantity (On Hand + Received)

The available and unavailable quantities computed for serial and lot products will supersede those based on FIFO. Serial products are only considered if you assign serial numbers during Purchase Receipt of Inventory Entry. This is determined by the Assign Serial # During Receiving option in SA Administrator Options-Product-Defaults.

On the report, the On Hand and Unavailable columns only contain values when a negative quantity exists on the warehouse product record. This indicates that there is a discrepancy that should be adjusted.

Available and unavailable quantities are calculated for all Product Warehouse Product Setup records if the Use FIFO option in SA Administrator Options-Products-Costs is selected. If a product is both serial or lot and FIFO, the calculation are based on serial/lot quantities. If FIFO quantities do not match serial/lot quantities, a message is printed. Reconciling adjustments must be performed manually.

For each serialized product, all existing Product Extended Serial Number Setup records are added to the available quantity or unavailable quantity depending on the status of the record.

For each lot product and all existing Product Extended Lot Number Setup records:

  • In Sales Order Entry, if the order is not a return, all active Product Extended Lot # Setup lot available and unavailable quantities are totaled and the Committed plus Reserved quantities from Product Warehouse Product Setup-Costs less the unallocated Sales Order Entry quantities are calculated.
  • In Sales Order Entry, if the order is a return Counter Sale (CS), Stock Order (SO), or Return Merchandise (RM), all active Product Extended Lot Number Setup lot available and unavailable quantities are totaled and the Committed plus Reserved quantities from Product Warehouse Product Setup-Costs, less the allocated Sales Order Entry quantities are calculated.
  • In Purchase, if the order type is a return merchandise and the purchase order has not been received or it is currently being received but the update function has not been performed to complete the receiving, the order quantity minus the allocated quantity is the unavailable quantity on the order for serials and lots. This total is then added to the Product Extended Serial Number Setup unavailable quantity or Product Extended Lot Number Setup unavailable quantity.

Sales Order Entry

The following criteria are used to determine which sales orders qualify to be used in balancing to Product transactions:

  • Stage is less than Stage 4 (Invoiced)

For each line item:

  • The special/non-stock type for products and build-on-demand kit components is Special or Blank.
  • The back order type is not an accumulative purchase order or a line DO.
  • It is not a returned line and a SO, CS, or BR order type.
  • It is a returned line, a lot product, and a CS, SO, or RM.
  • The order is not open. If the order is open, the information is included on the report but no updating occurs. A message to that effect is displayed.
  • The order is suspended. If the order is suspended, the information is included on the report and updating occurs. A message to that effect is displayed.

After the order is qualified, the quantity shipped for each line item is added to the following temporary quantities. After all line items for all orders have been added together, the quantities are compared to the Product record quantities for discrepancies.

Committed  If the order stage is not a return and is greater than Stage 1 (Ordered), the quantity shipped is added to the Committed quantity in Product Warehouse Product Setup-Costs.

Reserved 

 

If the order stage is Stage 1 (Ordered), and is not a return, the quantity shipped is added to the Reserved quantity in Product Warehouse Product Setup-Costs.
Back order If the order number suffix is greater than "-00", or the order disposition is Ship Complete, a Tag and Hold, or JIT, and the order type is not a blanket release, or the order type is a blanket release and the BO Release Flag = Yes, and the back order number from line item is equal to 0 (zero), the quantity ordered less the quantity shipped is added to the Back Order quantity in Product Warehouse Product Setup-Costs.

The following conditions update the B/O field:

  • If the product is not a return line item; and
  • If a Ship Complete or Tag and Hold and back order not created; or
  • If a back order, and the back order is not created yet.

If a Sales Counter Sale (CS) is back ordered, the Back Order amount in Product Warehouse Product Setup-Costs is updated when the line item is entered, unless the order has been suspended. The actual back order is not created until the order has been taken off suspended status. The Product Warehouse Inventory Audit Report should only be generated when all suspended orders have been released.

If a line item is a build-on-demand kit, the quantity shipped for each component is added to the Committed, Reserved, or Back Order quantities in Product Warehouse Product Setup-Costs depending on the criteria listed above.

Purchase

The following criteria are used to determine which purchase orders qualify to be used in balancing to Product records.

  • Order type is a purchase order, blanket release, or return merchandise. Because accumulative purchase orders do not affect inventory, they are not included. This report checks for dirty core quantities that should be in the committed bucket for RM purchase orders because inventory for dirty cores is committed when you enter them on a purchase order.
  • For each line item, the special/non-stock type is Blank, and the status is active and has not been costed (stage less than 6).

Once the order is qualified, the quantity ordered for each line item is added to the following temporary quantity. Once all line items for all orders have been added together, the quantities are compared to the Product record quantities for discrepancies.

On Order  If the order stage is 1 or 2, or if the stage is equal to Stage 5 (Received) and the received initials are blank, (order currently being received through Purchase Receipt of Inventory Entry, and the order type is not a return merchandise, the quantity ordered is added to the On Order quantity in Product Warehouse Product Setup-Costs). The received initials are blank until the transaction has been updated.

Warehouse Transfer

The following criteria are used to determine which warehouse transfers qualify to be used in balancing to Product records. Warehouse Transfer combines the qualifications from Sales and Purchase, depending on whether the transfer is outgoing or incoming. The status for shipping and receiving warehouses is Active and the transfer is in Stage 0 (Requested), Stage 1 (Ordered), or Stage 2 (Printed); Stage 3 (Shipped); or "I" received Stage 4 (Exception) or Stage 5 (Received). Unallocated lots are totaled in the Warehouse Transfer shipping warehouse when the stage is less than Stage 3 (Shipped). The total of the unallocated lots is then subtracted from the Product Extended Lot # Setup total plus the total committed plus the total reserved.

To warehouse (incoming)

  • Stage is not received.
  • Ship to warehouse is equal to the Product Warehouse Product Setup warehouse.
  • Ship to company number is the login company.
  • The transfer is approved.
  • The status is active.
  • For each line item, the special/nonstock type is Blank or Special. Nonstocks are excluded.

Once the transfer is qualified, and all line items for all transfers have been added together, the quantities are compared to the Product record quantities for discrepancies.

Request  The transfer approve type is Request, the quantity ordered is added.
On Order  If the order stage is 1, 2, or 3, or if the stage is equal to Stage 4 and the receiving journal number is blank, (order currently being received through WT Receipt of Inventory Entry, the quantity ordered is added to the On Order quantity in Product Warehouse Product Setup-Costs).

From warehouse (outgoing):

  • Stage is less than or equal to 5.
  • From company number is the login company number.
  • Ship from warehouse equals Product Warehouse Product Setup warehouse.
  • Order is not approved.
  • Order is active.
  • Transaction type is Direct Order (DO) or Warehouse Transfer (WT).

Once the transfer is qualified, all line items for all transfers have been added together, the quantities are compared to the Product record quantities for discrepancies.

Approve type is Request

  • Request ship

Approve type is Yes. For each line item, the:

  • WT In Transit - Transfer is in Stage 3 (Shipped) and ship date is Blank. If the ship date is blank, the warehouse transfer has not been updated. An exception exists in the Product Warehouse Product Setup-Costs WT In Transit and Reserved quantities caused by a transfer in shipping which has not yet been updated.
  • Commit - If the transfer is in Stage 2 (Printed), and the approve type is Yes, the quantity shipped is added to the Committed quantity in Product Warehouse Product Setup-Costs.
  • Reserved - If the transfer is in Stage 1 (Ordered), and the approve type is Yes, the quantity shipped is added to the Reserved quantity in Product Warehouse Product Setup-Costs.
  • Demand - If the transfer is not shipped and the quantity shipped is less than the order quantity, then the order quantity minus the quantity shipped is added to the Demand quantity in Product Warehouse Product Setup-Costs. This also includes prebuilt kit work orders that have been back ordered.
  • WT In Transit - Transfer is in Stage 3 (Shipped) and shipped date is not Blank, then it equals stock quantity shipped or if in Stage 4 (Exception) quantity shipped minus quantity received.

Kit Production

Prebuilt kits and prebuilt kit components are also included in the audit.

  • The work order is in Stage 1 (Ordered.)

When the kit is assembled, the On Order quantity in Product Warehouse Product Setup-Costs for the kit increases, and the Reserved quantity for each component increases.

If the work order number suffix is greater than "-00", or all items on a work order ("-00" suffix) are back ordered, the quantity ordered - quantity shipped is added to the Demand quantity in Product Warehouse Product Setup-Costs. This quantity also includes warehouse transfers that have been back ordered.

If a prebuilt kit is disassembled, the Reserved quantity in Product Warehouse Product Setup-Costs for the kit increases and the On Order quantity for each component increases. If the kit is assembled and a component is replaced, the On Order quantity for the kit increases, the Reserved quantity for the new component increases, and the Reserved quantity for the old component decreases.

The same logic applies to work orders in Stage 2 (Committed).

Customer product reservations

The Net Available for a product with active customer product reservations is adjusted by the Reserved Quantity to accurately reflect what is available to sell. Active customer product reservations are listed on the report, sorted first by product, then by warehouse. The reservation record information includes the Reserved Quantity.

It may be useful to run the Product Warehouse Inventory Audit Report to determine if any force shipments of a product caused an out-of-balance situation, where reserved stock was used to fulfill the ordered amount. If your warehouse personnel force ship a quantity on an order line because they found available quantity in the warehouse (even though it was reserved), this may not cause the On Hand quantity to be negative. When you have determined this is the cause of the out-of-balance condition, you must adjust the Reserved Quantity on the reservation by the forced amount. This removes it from the Product Warehouse Product Setup reserved as well.

Exception messages

The following table lists exception messages that may appear on the report. It also includes the action that needs to be taken.

Message Action to be Taken
Warning: This product contains partially updated orders, Sales, Purchase, or WT Use the Sales Exceptions Report to locate any open or suspended orders.
Calculated FIFO and Serial/Lot Quantities Do Not Match, Adjust FIFO Manually Make the necessary adjustments in Product Extended FIFO Setup.
Net Available Quantity is Negative - Please review Research the cause of the negative quantity and make the necessary adjustments through Product Adjustments Entry.
Warning: ICSOU Unavailable Quantities Do Not Match Unavailable Quantity in ICSW Research the cause of the imbalance and make the necessary adjustments through Product Adjustments Entry or Product Unavailable Inventory Entry.