Acumen Warehouse API
Version 1.0 Beta
Can you build a whole company based on the structure of a single database table? You can when your Acumen and you are changing the philosophy of storing inventory data.
The a_ITEM_INVENTORY table fundamentally changes the way inventory software and data structures hold the existence, movement and sale of inventory. It’s the absolute core object of our system! Traditional software holds inventory as a summary. For example, the software might say, we bought and received 3 widgets, sold 2, so we have 1 left in stock and they are in this location. Acumen software serializes this inventory and tracks each item as it is received into the warehouse, is allocated to demand (orders), gets moved, then picked, and shipped.
OLD METHOD
INVENTORY_RECEIVED (table)
po_number: 123
part_description: Widget
qty_received: 3
INVENTORY_SOLD (table)
part_description: Widget
qty_sold: 2
Calculation to find in stock quantities:
INVENTORY_RECEIVED.qty_received – INVENTORY_SOLD.qty_sold = 1
OUR METHOD
Each lot received into the warehouse is broken down (serialized) into individually tracked items. By tracking inventory this way we have much better control and understanding of the flow of individual items into the warehouse and out to the customer, not to mention a simplified data model.
a_ITEM_INVENTORY (table)
|
BIN_NO |
PART_NO |
PO_NUMBER |
ORDER_NO |
PICK_DATE |
|
012-145 |
WIDGET |
123 |
123456 |
Jan, 20 2002 |
|
012-146 |
WIDGET |
123 |
134567 |
Jan, 22 2002 |
|
123-098 |
WIDGET |
123 |
NULL |
NULL |
As you can see each item brought into the warehouse is a single line item in the database. This gives us much greater flexibility in tracking what exactly happened to each piece of inventory that was brought into our clients warehouse. First we can see that the 3 widgets that were brought in under purchase orders 123 were put away in 3 different locations (bins) in the warehouse. We also see that two of the items were assigned (allocated) to orders that came into the system. We also see the dates for which the item left the shelf and was picked for the order. By adding columns such as cost and ship date, we know exactly how much the part on the shelf is worth and/or when it was shipped making reporting on things such as COGS easy and efficient.
In addition to storing items received into the warehouse, a_ITEM_INVENTORY also stores the demand of items based on the orders outstanding for that part.
The general outline of the table below illustrates the functions and concepts necessary to fully understand the a_ITEM_INVENTORY table.
|
DESCRIPTION |
BIN_NO |
ORDER_NO |
SHIP_DATE |
|
SUPPLY ITEM |
001-001 |
NULL |
NULL |
|
DEMAND ITEM |
NULL |
12345 |
NULL |
|
ALLOCATED ITEM |
001-001 |
12345 |
NULL |
|
SHIPPED ITEM |
001-001 |
12345 |
Jan, 20 2002 |
To know how much of a part you have in stock you would code:
select count(1)
from a_ITEM_INVENTORY (nolock)
where bin_no is not null and
order_no is null and
ship_date is null and
part_num = @part_num
To know how much of a part is demanded by customers, not in stock and needed to be placed on the next replenishment process (po creation) you would code the following:
select count(1)
from a_ITEM_INVENTORY (nolock)
where bin_no is null and
order_no is not null and
ship_date is null and
part_num = @part_num
To know if a particular item is ready to be picked you would code:
select count(1)
from a_ITEM_INVENTORY (nolock)
where bin_no is not null and
order_no is not null and
ship_date is null and
part_num = @part_num
To know how much of an item has left the warehouse you would code:
select count(1)
from a_ITEM_INVENTORY (nolock)
where bin_no is not null and
order_no is not null and
ship_date is not null and
part_num = @part_num
ALLOCATION METHOD
NEW DEMAND
ALLOCATE SUPPLY LINES
CREATE DEMAND LINES
REMOVE DEMAND
DELETE DEMAND LINES
UN-ALLOCATE SUPPLY LINES
NEW SUPPLY
ALLOCATE DEMAND LINES
CREATE SUPPLY LINES
REMOVE SUPPLY
DELETE SUPPLY LINES
UN-ALLOCATE DEMAND LINES
OTHER TABLES needed for version 1.0 (Beta)
a_ITEM_INVENTORY_DETAIL
a_WAREHOUSE_USERS
a_BIN_MASTER
a_BIN_TYPE
a_ORDERS
a_ORDER_LIST
a_CUSTOMER
a_ADDRESS
a_PO
a_PO_LIST
a_PART_MASTER
a_PART_LIST
a_VENDOR
BASIC SCREENS and database objects needed for VERSION 1.0 (Beta)
ORDER ENTRY
Get order header
Get order detail
Add/Update/Delete order header
Add/Update/Delete order detail
PO CREATION
Get part demand
Get Min Instock
Create PO Header
Create PO Detail
PO EDIT
Get PO header
Get PO detail
Add/Update/Delete po header
Add/Update/Delete po detail
RECEIVE
Get PO_LIST
Get Valid Bin list.
Get list of PO’s
Receive to Bin
Receive to PO_LIST
Adjust PO receipt
PO SUMMARY / CLOSE
Get receive summary
Modify order status & line items
Close PO.
PUT AWAY / BIN TO BIN MOVE
Suggest Bin
Bin to Bin Move
CYCLE COUNT
Get Inventory
Adjust inventory
PICK SCREEN
Get and Assign next order
Pick-able orders. Order Pick Sequence.
Pick item
Item pick sequence
Print Invoice
Pick Complete
BATCH PICKING
Select similar orders
Display pick information.
Batch Pick.
Print Individual Invoice
Batch Pick Complete.
WORK ORDER CREATION
Get Manufactured Part Demand
Get Manufactured Part Supplies
Compare Supply / Demand. à
Find Supply / Demand of components à
Create Demand Lines for components when Supply < Demand
When Supply < Demand create work orders for as many manufactured parts as can be created with components on hand.
WORK ORDER
Get and Assign next work order.
Build
Build Complete
BATCH WORKORDERS
Select similar work orders.
Display Build information
Batch Build
Batch Build Complete
SHIPPING SCREEN
Confirm order 100% allocated and picked.
Bill Credit Card
Create Shipping Label
Ship order in a_ORDERS and a_ITEM_DETAIL
ASSIGNED ORDERS
Get assigned orders all or per user
Assign order
USER MANAGEMENT
Add, Update, Delete User