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 PO and OE modules.
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

type_it char(1), - BOM Columns
prod_type_it char(1),
abc_code_it char(1),
product_it char(3),
acctcd_it char(13),
department_it char(3),
low_level_it smallint,
revision_level_it char(3),
num_eng_change_it char(6),
date_eng_change_it date,
eng_drawing_it char(15),
bill_chng_date_it date,
rout_chng_date_it date,
standard_cost_it decimal(14,4),
date_last_cost_it date,
order_policy_it char(1),
cur_order_qty_it decimal(10),
eoq_order_qty_it decimal(10),
min_order_qty_it decimal(10),
max_order_qty_it decimal(10),
mult_order_qty_it decimal(10),
safety_stock_it decimal(10),
shrinkage_it decimal(10),
planner_it char(5),
buyer_it char(5),
component_count_it smallint,
routing_count_it smallint,
mfg_alloc_qty_it decimal(10),
mfg_order_qty_it decimal(10),
loc_control_it char(1),
fifo_control_it char(1),
lot_control_it char(1),
serial_control_it char(1),
group_it char(3),
cost_method_it char(3),
cur_suhr_std_it decimal(10),
cur_runhr_std_it decimal(10),
cur_machr_std_it decimal(10),
mrp_interval_it char(1),
issue_method_it char(1),
phantom_it char(1),
cur_last_roll_it date,
days_supply_it smallint,
mrp_qty_work_it decimal(10,3),
mfg_sched_rcp_it decimal(10,3),
cost_roll_sts_it smallint,
est_annual_usg_it decimal(10,3),
master_schedule_it char(1),
mps_group_it char(15),
mps_interval_it char(2),
interval_ofst_it smallint,
auto_rsc_build_it char(1),
rough_rsc_id_it char(10),
rough_conv_it decimal(8,4),
demand_source_it char(1),
forecast_it char(1),
fcst_group_it char(15),
fcst_interval_it char(2),
qty_or_amount_it char(1),
default_bom_it char(5),
default_rtg_it char(5),
last_rsc_gen_it date,
rev_prod_lt_it decimal(12,6),
fix_prod_lt_it decimal(12,6),
var_prod_lt_it decimal(12,6),
cumulative_lt_it decimal(12,6),
auto_msg_prod_it char(6),
pur_rel_type_it char(1),
configurable_it char(1),
config_group_it char(15)
upc_code char(15)

------------------------------------------------------------------------
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(14,4), - 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.
mfg_alloc_qty_iw decimal(10,3), - BOM Columns
standard_cost_iw decimal(14,4),
mfg_sched_rcp_iw decimal(10,3),
type_iw char(1),
acctcd_iw char(13),
department_iw char(3),
stock_uom_iw char(2),
prod_type_iw char(1),
lead_time_iw smallint,
cuml_lead_time_iw smallint,
planner_iw char(5),
buyer_iw char(5),
order_policy_iw char(1),
cur_order_qty_iw decimal(10,3),
eoq_order_qty_iw decimal(10,3),
min_order_qty_iw decimal(10,3),
max_order_qty_iw decimal(10,3),
mult_order_qty_iw decimal(10,3),
safety_stock_iw decimal(10,3),
days_supply_iw smallint,
mrp_qty_work_iw decimal(10,3),
cost_method_iw char(3),
tl_mtl_std_iw decimal(11),
tl_labor_std_iw decimal(11),
tl_setup_std_iw decimal(11),
tl_ovh_std_iw decimal(11),
tl_outs_std_iw decimal(11),
ll_mtl_std_iw decimal(11),
ll_labor_std_iw decimal(11),
ll_setup_std_iw decimal(11),
ll_ovh_std_iw decimal(11),
ll_outs_std_iw decimal(11),
default_bom_iw char(5),
default_rtg_iw char(5),
issue_method_iw char(1),
mrp_interval_iw char(1),
est_annual_usg_iw decimal(10,3),
shrinkage_iw decimal(8,4),
master_schedule_iw char(1),
mps_group_iw char(15),
mps_interval_iw char(2),
interval_ofst_iw smallint,
auto_rsc_build_iw char(1),
rough_rsc_id_iw char(10),
rough_conv_iw decimal(8,4),
demand_source_iw char(1),
forecast_iw char(1),
fcst_group_iw char(15),
fcst_interval_iw char(2),
qty_or_amount_iw char(1),
last_rsc_gen_iw date,
rev_prod_lt_iw decimal(12,6),
fix_prod_lt_iw decimal(12,6),
var_prod_lt_iw decimal(12,6),
cumulative_lt_iw decimal(12,6),
pur_rel_type_iw char(1),
mrp_chg_flag_iw char(1),
configurable_iw char(1),
config_group_iw char(15),
prod_line_iw char(5),
loc_control_iw char(1),
lot_control_iw char(1),
serial_control_iw char(1),
fifo_control_iw char(1),
serial_auto_iw char(1),
next_serial_iw decimal(10,0),
lot_auto_iw char(1),
next_lot_iw decimal(10,0)
------------------------------------------------------------------
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
use_batch_adj char(1), - Use Batch control in IC Adjustment
use_approve_post char(1) - Use Post Approval Code
approval_code char(8) - Approval Code
auto_serialize char(1),- Auto Serialization
next_ser_num integer, - Next Serial number
serl_prefix char(20), - Serial Prefix
serl_suffix char(20)); - Serial Suffix
disp_item_notes char(1)- Display Item Notes

------------------------------------------------------------------
stipurce - purchase/receive inventory entry header table

doc_no serial, - document number
doc_date date, - document date
po_no char(20), - 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(14,4) - 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(20), - 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(14,4), - 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(14,4) - 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(14,4), - average unit cost - you can enter
cost when you initially setup item.
Then it is system maintained.
purch_unit_cost decimal(14,4),- purchase unit cost
last_cost decimal(14,4), - last purchase cost
comm_code char(6), - commodity code
price decimal(14,4), - 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
primary_bin char(15), - Primary Bin Location
secondary_bin char(15)); - Secondary Bin Location
----------------------------------------------------------------------
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
td_disc_allowed char(1) - Tray discount apply for item Y/N
tax char(1) - Taxable item? Y/N

-----------------------------------------------------------------------
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)
batch_id integer - Batch Id

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(14,4) - adjustment cost
adj_type char(1) - adjustment type
lot_cost decimal(14,4) - Lot Cost
----------------------------------------------------------------------
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)
type_it char(1),
prod_type_it char(1),
abc_code_it char(1),
product_it char(3),
acctcd_it char(13),
department_it char(3),
low_level_it smallint,
revision_level_it char(3),
num_eng_change_it char(6),
date_eng_change_it date,
eng_drawing_it char(15),
bill_chng_date_it date,
rout_chng_date_it date,
standard_cost_it decimal(14,4),
date_last_cost_it date,
order_policy_it char(1),
cur_order_qty_it decimal(10),
eoq_order_qty_it decimal(10),
min_order_qty_it decimal(10),
max_order_qty_it decimal(10),
mult_order_qty_it decimal(10),
safety_stock_it decimal(10),
shrinkage_it decimal(10),
planner_it char(5),
buyer_it char(5),
component_count_it smallint,
routing_count_it smallint,
mfg_alloc_qty_it decimal(10),
mfg_order_qty_it decimal(10),
loc_control_it char(1),
fifo_control_it char(1),
lot_control_it char(1),
serial_control_it char(1),
group_it char(3),
cost_method_it char(3),
cur_suhr_std_it decimal(10),
cur_runhr_std_it decimal(10),
cur_machr_std_it decimal(10),
mrp_interval_it char(1),
issue_method_it char(1),
phantom_it char(1),
cur_last_roll_it date,
days_supply_it smallint,
mrp_qty_work_it decimal(10,3),
mfg_sched_rcp_it decimal(10),
cost_roll_sts_it smallint,
est_annual_usg_it decimal(10,3),
master_schedule_it char(1),
mps_group_it char(15),
mps_interval_it char(2),
interval_ofst_it smallint,
auto_rsc_build_it char(1),
rough_rsc_id_it char(10),
rough_conv_it decimal(8,4),
demand_source_it char(1),
forecast_it char(1),
fcst_group_it char(15),
fcst_interval_it char(2),
qty_or_amount_it char(1),
default_bom_it char(5),
default_rtg_it char(5),
last_rsc_gen_it date,
rev_prod_lt_it decimal(12,6),
fix_prod_lt_it decimal(12,6),
var_prod_lt_it decimal(12,6),
cumulative_lt_it decimal(12,6),
auto_msg_prod_it char(6),
pur_rel_type_it char(1),
configurable_it char(1),
config_group_it char(15)
upc_code char(15)

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(14,4),
purch_unit_cost decimal(14,4),
last_cost decimal(14,4),
comm_code char(6),
price decimal(14,4),
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)
mfg_alloc_qty_iw decimal(10,3),
standard_cost_iw decimal(14,4),
mfg_sched_rcp_iw decimal(10,3),
type_iw char(1),
acctcd_iw char(13),
department_iw char(3),
stock_uom_iw char(2),
prod_type_iw char(1),
lead_time_iw smallint,
cuml_lead_time_iw smallint,
planner_iw char(5),
buyer_iw char(5),
order_policy_iw char(1),
cur_order_qty_iw decimal(10,3),
eoq_order_qty_iw decimal(10,3),
min_order_qty_iw decimal(10,3),
max_order_qty_iw decimal(10,3),
mult_order_qty_iw decimal(10,3),
safety_stock_iw decimal(10,3),
days_supply_iw smallint,
mrp_qty_work_iw decimal(10,3),
cost_method_iw char(3),
default_bom_iw char(5),
default_rtg_iw char(5),
issue_method_iw char(1),
mrp_interval_iw char(1),
est_annual_usg_iw decimal(10,3),
shrinkage_iw decimal(8,4),
master_schedule_iw char(1),
mps_group_iw char(15),
mps_interval_iw char(2),
interval_ofst_iw smallint,
auto_rsc_build_iw char(1),
rough_rsc_id_iw char(10),
rough_conv_iw decimal(8,4),
demand_source_iw char(1),
forecast_iw char(1),
fcst_group_iw char(15),
fcst_interval_iw char(2),
qty_or_amount_iw char(1),
last_rsc_gen_iw date,
rev_prod_lt_iw decimal(12,6),
fix_prod_lt_iw decimal(12,6),
var_prod_lt_iw decimal(12,6),
cumulative_lt_iw decimal(12,6),
pur_rel_type_iw char(1),
mrp_chg_flag_iw char(1),
configurable_iw char(1),
config_group_iw char(15),
prod_line_iw char(5),
loc_control_iw char(1),
lot_control_iw char(1),
serial_control_iw char(1),
fifo_control_iw char(1),
serial_auto_iw char(1),
next_serial_iw decimal(10,0),
lot_auto_iw char(1),
next_lot_iw decimal(10,0)

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(14,4),
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(14,4), - transaction cost
price decimal(14,4), - 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(14,4), - cost per item
vend_code char(20) - vendor code
po_no char(20) - PO number
rec_doc_no integer - Receipt doc number
receipt_date date - Receipt date
recv_qty decimal(10) - Qty received
recv_cost decimal(14,4) - Received Landed Cost
----------------------------------------------------------
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(14,4),
cost

in_out char(3)
in or out

bin_location char(15)
Bin Location
---------------------------------------------------------
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(14,4), - cost
vend_code char(20), - vendor code
expiry_date date - Expiry Date
po_no char(20) - PO number
rec_doc_no integer - Receipt doc number
receipt_date date - Receipt date
recv_qty decimal(10) - Qty received
recv_cost decimal(14,4) - Received Landed Cost
bin_location char(15) - Bin Location
--------------------------------------------------------
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
bin_location char(15) - Bin Location
--------------------------------------------------------
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), - email address
fax_phone char(20) - Fax phone number
location_control char(1)- Location Control? Y/N
-------------------------------------------------------
stilccat - Landed Cost Category
category char(30), - Category
var_acct_no integer, - Variance account number
alloc_method char(1) - Allocation method
(C) Cost
(W) Weight
(V) Volume
(Q) Quantity
(M) Manual

unique index i1ilccat on stilccat(category);

----------------------------------------------------------
stibinlr - Multi Bin reference table
warehouse_code char(10), - Warehouse Code
bin_location char(15) - Bin Location
----------------------------------------------------------
sticadjs - Physical/Cycle count entry via scanner
doc_no integer,
line_no smallint,
item_code char(20),
warehouse_code char(10),
bin_location char(12),
serial_no char(20),
lot_no char(20),
expire_date date,
qty_on_hand decimal(10),
adj_qty decimal(10),
count_qty decimal(10),
l_mod_date date,
l_mod_time char(8),
l_mod_id char(8),
edit_ok char(1),
disposition char(3),
alt_warehouse char(10)
----------------------------------------------------------
stilocap - MRP Module
item_code char(20),
warehouse_code char(10),
source_type char(1),
source_id char(20),
qty_min decimal(10,3),
qty_max decimal(10,3)