Inventory Control Tables
========================
------------------------------------------------------------------------
stiinvtr - inventory item table
item_code char(20),
- uniq code to identify given item
item_type
char(1), - item type: [S]-stock,
[N]-non-stock
currently, all item type will be stock
we will not store non-stock items.
non-stock history will be kept at
the
item_class
char(6), - product class
(used for reporting and item grouping)
price_group
char(6), - group items for price discount.
(.iX group_pricing in oe)
desc1
char(30), - item
description line 1
desc2
char(30), - item
description line 2
weight decimal(8,3), - weight of item
weight_unit
char(2), - weight unit lable
- "OZ", "LB"
note: all items should be defined
with the same unit lable. there
are currently no conversion program
to associate the different lables.
volume decimal(8,3), - volume of unit
inv_acct_no
integer, - inventory asset account number
cog_acct_no
integer, - cost of good account number
sales_acct_no integer, -
sales account number
sell_unit
char(2), - selling unit lable - "BX", "CT", "EA"
bill_unit
char(2), - billing unit lable - "BX", "CT", "EA"
stock_unit
char(2), - stocking unit lable - "BX", "CT", "EA"
purch_unit
char(2), - purchase unit lable - "BX", "CT", "EA"
sell_factor decimal(6), - selling
unit / stocking unit
bill_factor decimal(6), - billing
unit / stocking unit
purch_factor decimal(6) - purchase
unit / stocking unit
serialized char(1), -
serialized? [ ]-null for non-serial
[S]-serial
control
[L]-lot control
[B]-both lot and serial
market_price char(1)
- subject to market price.
this field will let oe
change the price
at the shipment phase.
commodity_code
char(10) - reference only: standardized federal or
state code.
vend_code char(20),
incr_sell_unit decimal(10),
incr_purch_unit decimal(10),
comm_code
char(6) - Commission Code
------------------------------------------------------------------------
stistatd - inventory period statistic table
This is the table used for calculating Usage Rate. It is also use
to summarize the purchase and sales of the item/warehouse for a
given period.
item_code
char(20), - item code
warehouse_code
char(10), - warehouse code
period smallint,
- period
yr smallint,
- year
cost
decimal(12), - total
actual purchase cost
sale
decimal(12), - total
actual sales amount
cqty
decimal(10), - total
actual purchase qty
sqty
decimal(10), - total
actual sales qty
usage_rate decimal(10),
- obsolete, rate will be calculated in
stilocar
day_ostk smallint,
- days in the period out of stock
num_stk_outs smallint,
- number of stock outs for this given
period
dup_sqty
decimal(10), - obsolete/renamed to dup_recurr_usage
recurr_usage decimal(10), -
usage that is defined as recurring
dup_recurr_usage decimal(10) - if
this is null, then the usage rate
is calculated off of the
recurr_usage.
This is the field that the user will
enter a value into if user
interaction is required.
------------------------------------------------------------------
sticntrc - inventory control table
item_type char(1), -
default item type: [S]-stock [N]-non stock
item_class char(3), -
default item product class
inv_acct_no integer, - default
inventory asset account number
sales_acct_no integer, - default sales account
number
cog_acct_no integer, - default
cost of goods number
adj_acct_no integer, - default
adjustment account number
count_acct_no integer, - default count
adjustment account number
count_cycle char(1), - default
count cycle code
comm_code char(6), -
default commission code
allow_bo
char(1), - default to allow backorder
taxable char(1), - default to tax on
item
terms_disc char(1), -
default to take terms discount
trade_disc char(1), -
default to take trade discount
ic_setup_done char(1), - inventory setup
complete flag
doc_no
integer, - inventory transaction
document number
post_no
integer, - inventory transaction posting
number
use_department char(1) - use warehouse
department for asset accounts
cost_method
- cost method "F"=FIFO "L"=LIFO
"A"=Average
ilocar_ina_days
- inactive days
ilocar_ret_days
- retention days
class1
- ABC analysis class 1 type percent
class2
- ABC analysis class 2 type percent
class3
- ABC analysis class 3 type percent
class4
- ABC analysis class 4 type percent
class5
- ABC analysis class 5 type percent
class6
- ABC analysis class 6 type percent
class7
- ABC analysis class 7 type percent
class8
- ABC analysis class 8 type percent
class9
- ABC analysis class 9 type percent
class10
- ABC analysis class 10 type percent
class11
- ABC analysis class 11 type percent
class12
- ABC analysis class 12 type percent
class13 -
minimum $ amount for inclusion in class
------------------------------------------------------------------
stipurce - purchase/receive inventory entry header
table
doc_no
serial, - document
number
doc_date
date, - document
date
po_no
char(10), - purchase
order number
purch_desc char(30), -
purchase description
vend_code
char(20), - vendor code
ok_post
char(1) - ok to post flag
------------------------------------------------------------------
stipurcd - purchase/receive inventory entry detail
table
doc_no
integer,
- document number
line_no smallint,
- line number
item_code
char(20), - item code
warehouse_code char(10), -
warehouse code
purch_qty
decimal(10), - purchase quantity in purchase
units
purch_unit_cost decimal(12) - purchase unit
cost
purch_factor
decimal(6) - purchase factor
-----------------------------------------------------------------
stiselle - sale/shipped inventory entry table
doc_no
serial, - document
number
doc_date
date, - document
date
order_no
char(10), - order number
sell_desc
char(30), - sale description
cust_code char(20),
- customer code
ok_post char(1)
- ok to post flag
-----------------------------------------------------------------
stiselld - sale/shipped inventory detail table
doc_no
integer, - document
number
line_no smallint,
- line number
item_code
char(20), - item code
warehouse_code char(10), - warehouse code
sell_qty decimal(10), -
quantity sold/shipped in selling units
price decimal(12), - selling price
per selling unit
sell_factor decimal(6) - sell factor
is_recurr
char(1) - is this transaction
recurring
----------------------------------------------------------------
stitrane - inventory transfer entry table
doc_no
serial,
- document number
doc_date
date, -
document date
tran_no
char(10), - transfer
number
tran_desc
char(30), - transfer description
ok_post
char(1), - ok to
post flag
from_wh
char(10), - default from
warehouse
to_wh
char(10), -
default to warehouse
eta_date
date, -
eta date
freight_amt decimal(12,2), - $ to ship between
warehouses
freight_acct
integer, - GL account
freight_dept
char(3) - GL department
----------------------------------------------------------------
stitrand - inventory transfer detail table
doc_no
integer, - document
number
line_no smallint,
- line number
item_code
char(20), - item code
from_wh
char(3), - from warehouse
to_wh
char(3), - to
warehouse
tran_qty decimal(10), -
transfer quantity
is_recurr
char(1), - is this transaction a recurring
unit_cost decimal(12) -
transferred from stilocar avg_unit_cost
---------------------------------------------------------------
sticadje - count adjustment header table
doc_no serial not
null, - document number
doc_date
date, -
document date
count_desc
char(30), - description
count_acct
integer, - count adjustment account number
ok_post char(1),
- ok to post
blind char(1),
- "B"lind count, "C"ycle
count
warehouse_code
char(10), - warehouse location
l_mod_date
date, - last modification
date
l_mod_time
char(8), - last modification time
l_mod_id
char(8), - last modification
user
orig_journal
char(2), - drill down reference
trans_doc_no
integer, - drill down reference
posted char(1)
index i2sticadje on sticadje(orig_journal,
trans_doc_no)
---------------------------------------------------------------
sticadjd - count adjustment detail table
doc_no
integer, -
document number
page_no
integer, - page
number
line_no smallint,
- line number
item_code
char(20), - item code
qty_on_hand
decimal(10), - quantity on hand
adj_qty
decimal(10), - adjusted quantity
l_mod_date
date, - last
modification date
l_mod_time
char(8), - last modification time
l_mod_id
char(8), - last
modification user
warehouse_code
char(10) - warehouse code
--------------------------------------------------------------
stilocar - main inventory location record
# several of the fields here are for Replenishment Control Module
item_code
char(20), - main
key field
warehouse_code
char(10), - warehouse location
line_no smallint,
- no longer used
count_cycle
char(1), - count cycle
purchase_date
date, - last
purchase date
count_date
date,
- last count date
sold_date
date,
- last sold date
obsolete
char(1),
- is this item obsolete?
inactive_date
date, - inactive
date
lst_act_date
date, - last
active date
loc_aisle
char(4), -
aisle in warehouse
loc_row
char(3),
- row in warehouse
loc_bin
char(3),
- bin in warehouse
stock_location
char(12), - combination of above three fields
avg_unit_cost
decimal(12), - average unit cost - you can enter
cost when you initially setup item.
Then it is system maintained.
purch_unit_cost
decimal(12), - purchase unit cost
last_cost
decimal(12), - last purchase cost
comm_code
char(6), -
commodity code
price
decimal(12),
- list selling price
allow_bo
char(1),
- can this item go on backorder?
taxable char(1),
- is this item taxable?
terms_disc
char(1), - subject
to terms discount?
trade_disc
char(1), - subject
to trade discount?
vend_code
char(20), - vendor
code NOT used by PO
vend_prod_no char(20),
- vendor's description
abc_code
char(1),
- ABC code
reorder_point
decimal(10), - reorder point(used with
Replenishment Module)
qty_reorder
decimal(10), - quantity to reorder
safety_stock
decimal(10), - safety stock
safety_factor
decimal(6), - safety factor
qty_on_hand
decimal(10), - quantity on hand
last_qty
decimal(10), - last quantity
stk_out_date
date, - date
of last stock out
seasonal
char(1),
- is this a seasonal item?
avg_ld_tm
decimal(5,2), - average lead time
lst_ld_tm smallint,
- last lead time
pri_ld_tm smallint,
- previous lead time
freez_flag char(1),
- freeze flag
freez_date
date,
- freeze start date
freez_expir
date, -
freeze expiration date
min_sell_qty
decimal(10), - minimum sell quantity
usage_rate
decimal(10), - computed from the stistatd record.
it is the average usage for a given
period. if the user overrides this
field, the must use the freeze flag
to retain this value, otherwise,
the monthly usage calculation
program will override this field
each month.
req_profit_pct
decimal(6) - price margin warning
----------------------------------------------------------------------
stiinvtr - inventory item code header
item_code
char(20), - key
field
item_type
char(1), -
Stock or Non stock
item_class
char(6), - item
class
price_group
char(6), - price group
desc1
char(30),
- item description
desc2
char(30),
- item description
weight
decimal(8,3), - weight of
the item
weight_unit
char(2), - weight unit (LB,KG,OZ,etc)
volume
decimal(8,3), - volume
inv_acct_no
integer, - inventory
account number
cog_acct_no
integer, - cost of goods
account number
sales_acct_no integer,
- sales account number
sell_unit
char(2), -
selling unit
bill_unit
char(2), -
billing unit
stock_unit
char(2), - stocking
unit
purch_unit
char(2), -
purchasing unit
sell_factor
decimal(6), - sell conversion factor
bill_factor
decimal(6), - billing conversion factor
purch_factor
decimal(6), - purchasing conversion factor
serialized
char(1), -
serialized, lot, or both.
market_price
char(1), - charge market price
for this item?
commodity_code
char(10), - commodity code
vend_code
char(20) -
vendor code
incr_sell_unit
decimal(10), - incr. sell unit
incr_purch_unit
decimal(10) - incr. puchase unit
-----------------------------------------------------------------------
stiadjme - inventory adjustment entry table
doc_no
serial, - document
number
doc_date
date, - document
date
adj_no
char(10), - adjustment number
adj_desc
char(30), - adjustment description
adj_acct
integer, - adjustment account number
ok_post
char(1), - ok to post flag
orig_journal char(2), - for
drill down reference
trans_doc_no integer, - for
drill down reference
posted char(1)
index i2stiadjme on stiadjme(orig_journal,
trans_doc_no)
----------------------------------------------------------------------
stiadjmd - inventory adjustment detail table
doc_no
integer, - document
number
line_no smallint,
- line number
item_code
char(20), - item code
warehouse_code char(10), - warehouse code
adj_qty decimal(10), -
adjustment quantity
adj_cost decimal(10) -
adjustment cost
adj_type
char(1) - adjustment type
----------------------------------------------------------------------
stiarinv - archived inventory items (mirror of stiinvtr)
item_code char(20) not null ,
item_type char(1),
item_class char(6),
price_group char(6),
desc1 char(30),
desc2 char(30),
weight decimal(8,3),
weight_unit char(2),
volume decimal(8,3),
inv_acct_no integer,
cog_acct_no integer,
sales_acct_no integer,
sell_unit char(2),
bill_unit char(2),
stock_unit char(2),
purch_unit char(2),
sell_factor decimal(6),
bill_factor decimal(6),
purch_factor decimal(6),
serialized char(1),
market_price char(1),
commodity_code char(10),
vend_code char(20),
incr_sell_unit decimal(10),
incr_purch_unit decimal(10),
last_purchased date,
last_sold date,
last_activity date,
comm_code char(6)
unique index i1iarinv on stiarinv (item_code)
index i2iarinv on stiarinv (last_purchased)
index i3iarinv on stiarinv (last_sold)
index i4iarinv on stiarinv (last_activity)
----------------------------------------------------------------------
stiarloc - archived inventory location record (mirror
of stilocar)
item_code char(20) not null ,
warehouse_code char(10),
line_no smallint,
count_cycle char(1),
purchase_date date,
count_date date,
sold_date date,
obsolete char(1),
inactive_date date,
lst_act_date date,
loc_aisle char(4),
loc_row char(3),
loc_bin char(3),
stock_location char(12),
avg_unit_cost decimal(12),
purch_unit_cost decimal(12),
last_cost decimal(12),
comm_code char(6),
price decimal(12),
allow_bo char(1),
taxable char(1),
terms_disc char(1),
trade_disc char(1),
vend_code char(20),
vend_prod_no char(20),
abc_code char(1),
reorder_point decimal(10),
qty_reorder decimal(10),
safety_stock decimal(10),
safety_factor decimal(6),
qty_on_hand decimal(10),
last_qty decimal(10),
stk_out_date date,
seasonal char(1),
avg_ld_tm decimal(5,2),
lst_ld_tm smallint,
pri_ld_tm smallint,
freez_flag char(1),
freez_date date,
freez_expir date,
min_sell_qty decimal(10),
usage_rate decimal(10),
req_profit_pct decimal(6)
index i1iarloc on stiarloc (item_code,warehouse_code)
----------------------------------------------------------------------
stiartat - archived inventory item statistics (mirror
or stistatd)
item_code char(20),
warehouse_code char(10),
period smallint,
yr smallint,
cost decimal(12),
sale decimal(12),
cqty decimal(10),
sqty decimal(10),
usage_rate decimal(10),
day_ostk smallint,
num_stk_outs smallint,
dup_sqty decimal(10),
recurr_usage decimal(10),
dup_recurr_usage decimal(10)
index i1iartat on stiartat (item_code,
warehouse_code)
----------------------------------------------------------------------
stitranr - inventory transaction reference table
orig_journal char(2), - originating journal
(IC)
doc_no
integer, - document number (unique
identifier)
doc_type char(2), -
document type:
[PU]-purchase/receive,
[SH]-sale/shipped, [TR]-transfer
[CT]-count adjustment, [AJ]-adjustment
ref_no
char(10) - transfer reference number
----------------------------------------------------------------------
stiactvd - inventory transaction activity table
This is the true activity history (see stistatd
above). All activity,
including transfers, is recorded here. stistatd is a subset of
stiactvd.
orig_journal char(2),
- origination journal (IC)
doc_no
integer, - document number
(unique identifier)
line_no smallint,
- line number
item_code
char(20), - item code
warehouse_code char(10),-
warehouse code
qty decimal(10), -
transaction quantity
cost decimal(10), -
transaction cost
price decimal(10), -
transaction price
comm_code
char(6), - transaction commission code
item_class char(3)
- item product class
in_statistics char(1)
- transaction used for Usage calculations
-----------------------------------------------------------------------
stiactvv - Inventory Transaction Activity View Table
orig_journal
char(2) -Original Journal
doc_no
integer
-Document Number
post_no
integer -Post Number
post_date
date -Post Date
doc_date
date -Document Date
ref_code
char(20) -Reference Code
doc_desc
char(30) -Document Description
doc_type
char(2) -Document Type
ref_no
char(10) -Reference Number
line_no
smallint -Line Number
item_code
char(20) -Item Code
warehouse_code
char(10) -Warehouse Code
qty
decimal(10) -Quantity
cost
decimal(12) -Cost
create view "root".stiactvv (orig_journal,doc_no,post_no,post_date,
doc_date,ref_code,doc_desc,doc_type,ref_no,line_no,item_code,
warehouse_code,qty,cost,price,comm_code,item_class,in_statistics)
as
select x1.orig_journal ,x1.doc_no ,x1.post_no
,x1.post_date
,x1.doc_date ,x1.ref_code ,x1.doc_desc ,x0.doc_type
,x0.ref_no
,x2.line_no ,x2.item_code ,x2.warehouse_code ,x2.qty
,x2.cost
,x2.price ,x2.comm_code ,x2.item_class
,x2.in_statistics
from "root".stitranr x0
,"root".stxtranr x1 ,"root".stiactvd x2
where (((x0.doc_no = x1.doc_no ) AND (x0.orig_journal =
x1.orig_journal ) ) AND ((x0.doc_no = x2.doc_no ) AND
(x0.orig_journal =
x2.orig_journal
----------------------------------------------------------
sticstvr - inventory control stack table
hierarchy_no serial not null , -
number given a purchase at a unique
price
item_code
char(20), -
item code for purchased item
warehouse_code
char(10), - warehouse code for storing item
quantity
decimal(10), - quantity
of item purchased
cost
decimal(12),
- cost per item
vend_code
char(20)
- vendor code
----------------------------------------------------------
stiserla - Serial Audit Table
orig_journal char(2),
original journal
doc_no integer,
document number
line_no smallint,
line number
ship_no smallint,
shipment number
lot_no char(20),
lot number
serial_no char(20),
serial number
lot_qty decimal(10),
lot quantity
cost decimal(12),
cost
in_out char(3)
in or out
---------------------------------------------------------
stiserld - inventory item serial detail table
item_code char(20),
- inventory item code
warehouse_code
char(10), - warehouse code
seq_no serial not null
, - sequence number
lot_no
char(20), -
lot number
serial_no
char(20), - serial number
lot_qty
decimal(10), - lot quantity
cost
decimal(12), - cost
vend_code char(20),
- vendor code
expiry_date date
- Expiry Date
--------------------------------------------------------
stiserle - inventory item serial header table
orig_journal
char(2), - original journal
doc_no
integer,
- document number
line_no smallint,
- line number
ship_no smallint,
- shipment number
lot_no
char(20), -
lot number
serial_no
char(20), - serial number
lot_qty
decimal(10), - lot quantity
item_code
char(20), - item code
warehouse_code
char(10), - warehouse code
in_out char(3),
- in or out
expiry_date date
- Expiry Date
--------------------------------------------------------
stishipr - This table is not in use
item_code char(20),
intl_prod_class char(5),
prod_ver char(2),
release_date date,
f_ord_date date,
f_ship_date date,
eol_date date,
l_ord_date date,
l_ship_date date,
stop_ship char(1),
stop_ship_start date,
stop_ship_end date,
need_lic char(1)
-------------------------------------------------------
stitranv
create view "root".stitranv (orig_journal,doc_no,post_no,post_date,doc_date,
ref_code,doc_desc,doc_type,ref_no) as
select x1.orig_journal ,x1.doc_no ,x1.post_no ,x1.post_date
,x1.doc_date ,x1.ref_code ,x1.doc_desc ,x0.doc_type
,x0.ref_no
from "root".stitranr x0
,"root".stxtranr x1 where
((x0.orig_journal
= x1.orig_journal ) AND (x0.doc_no = x1.doc_no ) ) ;
-------------------------------------------------------
stiwhser - Inventory Warehouse Table
whse_code
char(10), - Warehouse Code
description char(30), - Warehouse Description
department char(3), - Warehouse
Department
address1 char(30), - Warehouse
Address 1
address2 char(30), - Warehouse
Address 2
city
char(20), - Warehouse
City
state
char(2), - Warehouse
State
zip
char(10), -
Warehouse Zip
country char(20), -
Warehouse Country
phone
char(20), - Warehouse Telephone
Number
email char(50),
fax_phone char(20)