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) - UPC Code

disc1 decimal(14,4), - Price Level

disc2 decimal(14,4), - Price Level

disc3 decimal(14,4), - Price Level

disc4 decimal(14,4), - Price Level

disc5 decimal(14,4), - Price Level

handling_fee decimal(8,4), - Handlinng Fee

special_order char(1), - Special Order

uom_list_code char(10) - UOM List Code

ext_desc varchar(255), - Ext Description

fmd_required char(1), - RoHS Compliance

mfg_name char(25), - Mfg Name

mfg_item char(25), - Mfg Item

rohs_compliant char(1), - RoHS Compliance

warr_days smallint - Warranty Days

------------------------------------------------------------------------

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

handling_acct_no integer - Special Handling Charges

pref_bcno char(2), - Prefix Barcode

auto_post_trf char(1), - Auto Post Transfer

auto_post_adj char(1), - Auto Post Adjustment

ticket_print char(1), - Ticket Print

transfer_print char(1), - Transfer Print

auto_post_tsf char(1), - Auto Post Transfer

auto_post_adj_d char(1), - Auto Post Adjustment

auto_post_tsf_d char(1) - Auto Post Transfer

------------------------------------------------------------------

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

mfg_alloc_qty_iw decimal(10,3),

standard_cost_iw decimal(11),

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),

primary_bin char(15), - Primary Bin Location

secondary_bin char(15) - Secondary Bin Location

date_added date, - Date Added (RL Calculation)

added_by char(8) - Added By (RL Calculation)

----------------------------------------------------------------------

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

comm_code char(6), - Commision Code

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(11),

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),

td_disc_allowed char(1) - Tray discount apply for item Y/N

tax char(1) - Taxable item? Y/N

upc_code char(15), - UPC Code

disc1 decimal(14,4), - Price Level1

disc2 decimal(14,4), - Price Level2

disc3 decimal(14,4), - Price Level3

disc4 decimal(14,4), - Price Level4

disc5 decimal(14,4), - Price Level5

handling_fee decimal(8,4), - Handling Charge

special_order char(1), - Special Order

uom_list_code char(10) - Add UOM List for multiple units of measure

-----------------------------------------------------------------------

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

qty_on_hand decimal(10) - Qty on Hand

----------------------------------------------------------------------

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)

disc1 decimal(14,4),

disc2 decimal(14,4),

disc3 decimal(14,4),

disc4 decimal(14,4),

disc5 decimal(14,4)

handling_fee decimal(8,4),

special_order char(1),

ext_desc varchar(255),

fmd_required char(1),

mfg_name char(25),

mfg_item char(25),

rohs_compliant char(1),

warr_days smallint,

uom_list_code char(10),

td_disc_allowed char(1),

tax char(1)

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)

primary_bin char(15),

secondary_bin char(15)

date_added date,

added_by char(8)

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

cost decimal(14,4), - Cost

seq_no integer - Seq_no to join with stiserld

--------------------------------------------------------

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

dflt_rec_loc char(15) - Default Receiving location

-------------------------------------------------------

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)

----------------------------------------------------------

sticommd - Variable commissions rate

item_code char(20), - Item Code

begin_price decimal(14), - Begin Price

end_price decimal(14), - End Price

comm_pct decimal(14) - Commision Percent

----------------------------------------------------------

sticrsld - Cross Sells (Deatil)

item_code char(20), - Item Code

line_no smallint, - Line Number

cross_item_code char(20) - Cross Item Code

----------------------------------------------------------

sticrsle - Cross Sells (Header)

item_code char(20) - Item Code

----------------------------------------------------------

stiprsle - Sales projection screen (Header)

item_class char(6) - Item Class

----------------------------------------------------------

stiprsld - Sales projection screen (Detail)

item_class char(6), - Item Class

period char(2), - Period

period_year char(4), - Period Year

sales_projection decimal(14) - Sales Projection Amount

----------------------------------------------------------

stiprslr - Sales projection (Period Definition)

period char(2), - Period

period_year char(4) - Period Year

----------------------------------------------------------

stitemse - Alternate Items (aka substitutes)

item_code char(20) - Item Code (Header)

----------------------------------------------------------

stitemsd - Alternate Items (aka substitutes) (Detail)

item_code char(20), - Item Code

sub_item_code char(20) - Alternate Item Code

----------------------------------------------------------

stiuomcr - Add UOM List feature for multiple units of measure

uom_code char(2), - UOM Code

uom_desc char(30)) - UOM Description

----------------------------------------------------------

stiuomld - Add UOM List feature for multiple units of measure

uom_list_code char(10), - UOM List Code

line_no integer, - Line Number

uom_code char(2), - UOM code

ref_unit_code char(2), - Reference Unit Code

quantity decimal(14), - Quantity

uom_type char(1) - UOM type

----------------------------------------------------------

stiuomlr - Add UOM List feature for multiple units of measure

uom_list_code char(10), - UOM List Code

uom_list_desc char(30), - UOM List Description

base_unit_code char(2), - Base Unit Code

qty_dec_places integer - Qty decimal places

----------------------------------------------------------

stiqcend FR3347 - New table to capture quality information

item_code char(20), -- Item Code

trans_seq integer, -- Transaction sequence number

serial_no char(20), -- Serial Number

lot_no char(20), -- Lot Number

msmt_qty decimal(10,3), -- Unit count related to measure taken (ie sample size)

pass_qty decimal(10,3), -- Passed Unit count related to measure taken

fail_qty decimal(10,3), -- No Pass Unit count related to measure taken

warn_qty decimal(10,3), -- Pass with warning count related to measure taken

msmt_data_type char(1), -- Type of data collected: N=numeric, C=Character/numeric

msmt_val_num decimal(10,3), -- Quality measurement taken, when msmt_data_type_w1=N

msmt_val_char char(20), -- Quality measurement taken, when msmt_data_type_w1=C

comment char(255), -- Free form comment

pass_type char(1), -- Y=pass, N=fail, W=pass with warning

fail_reason char(10), -- Validate against table 'reason'

warn_reason char(10), -- Validate against table 'reason'

hold_order char(1), -- Y=order to be held when pass_type_w1=N, N=no hold

hold_code char(3), -- Production order hold code to be used when hold_order_w1=Y

user_num1 decimal(14,7), -- User-defined field - numeric 1

user_num2 decimal(14,7), -- User-defined field - numeric 2

user_num3 decimal(14,7), -- User-defined field - numeric 3

user_char1 char(20), -- User-defined field - character 1

user_char2 char(20), -- User-defined field - character 2

user_char3 char(20)); -- User-defined field - character 3

----------------------------------------------------------

stiqcene - FR3347 - New table to capture quality information

item_code char(20),

warehouse_code char(10),

order_no char(20), -- Production or Purchase Order

order_rel char(3), -- Production Order release

line_no decimal(4,0), -- Purchase order line

trans_seq integer, -- Transaction sequence number

collect_type char(1), -- S=production order summary, O=routing step/operation, C=component, P=purchase receipt

collect_stage char(1), -- R=Prod Rect, L=Labor reporting, C=component issue, Q=Quality transaction Entry, P=Purch recpt

routing_seq char(10), -- Production Order Routing Sequence

comp_sequence char(10), -- Production Order Component Sequence

component_item char(20), -- Production Order Component Item

inspect_date date, -- Date of collection

msmt_desc char(30), -- Measurement description

msmt_qty decimal(10,3), -- Unit count related to measure taken (ie sample size)

pass_qty decimal(10,3), -- Passed Unit count related to measure taken

fail_qty decimal(10,3), -- No Pass Unit count related to measure taken

warn_qty decimal(10,3), -- Pass with warning count related to measure taken

msmt_data_type char(1), -- Type of data collected: N=numeric, C=Character/numeric

msmt_val_num decimal(10,3), -- Quality measurement taken, when msmt_data_type_w1=N

msmt_val_char char(20), -- Quality measurement taken, when msmt_data_type_w1=C

comment char(255), -- Free form comment

fail_reason char(10), -- Validate against table 'reason'

warn_reason char(10), -- Validate against table 'reason'

hold_order char(1), -- Y=order to be held when pass_type_w1=N, N=no hold

hold_code char(3), -- Production order hold code to be used when hold_order_w1=Y

car_code char(10), -- Corrective Action Required code

car_compl date, -- Corrective Action Completion date

car_comment char(255), -- Corrective Action comment

user_num1 decimal(14,7), -- User-defined field - numeric 1

user_num2 decimal(14,7), -- User-defined field - numeric 2

user_num3 decimal(14,7), -- User-defined field - numeric 3

user_char1 char(20), -- User-defined field - character 1

user_char2 char(20), -- User-defined field - character 2

user_char3 char(20), -- User-defined field - character 3

car_no integer, -- Next sequential # for CAR alert emailed

entered_by char(8), -- Display only user id

completed_by char(8), -- Display only user id

date_added datetime year to second, -- Date/Time added to table

added_by char(20)); -- Added to table by user

----------------------------------------------------------

stiqcmsr FR3347 - New table to capture quality information

item_code char(20),

collect_type char(1), -- S=production order summary, O=routing step/operation, C=component, P=Purchase receipt

collect_stage char(1), -- R=Prod Receipt, L=Labor reporting, C=component issue, Q=Quality Transaction Entry, P=Purchase receipt

routing_seq char(10), -- For collect_type O, this is required

comp_sequence char(10), -- For collect_type C, this is required

component_item char(20), -- For collect_type C, this is required

msmt_desc char(30), -- Measurement description

msmt_data_type char(1), -- Type of data collected: N=numeric, C=Character/numeric

low_tol_num decimal(14,7), -- Quality low tolerance, when msmt_data_type_w1=N

hi_tol_num decimal(14,7), -- Quality high tolerance, when msmt_data_type_w1=N

low_tol_char char(20), -- Quality low tolerance, when msmt_data_type_w1=C

hi_tol_char char(20), -- Quality high tolerance, when msmt_data_type_w1=C

detail_reqd char(1), -- Y=Yes(entries needed in woquald also), N=No

hold_order char(1), -- Y=order to be held when pass_type_w1=N, N=no hold

hold_code char(3), -- Production order hold code to be used when hold_order_w1=Y

car_code char(10), -- Corrective Action Required code

user_hdg_num1 char(30), -- User-defined field title - numeric 1

user_hdg_num2 char(30), -- User-defined field title - numeric 2

user_hdg_num3 char(30), -- User-defined field title - numeric 3

user_hdg_char1 char(30), -- User-defined field title - character 1

user_hdg_char2 char(30), -- User-defined field title - character 2

user_hdg_char3 char(30)); -- User-defined field title - character 3

----------------------------------------------------------

stiserlb - FR2932 - Create new table for Serial/Lot Composition Inquiry

item_code char(20),

warehouse_code char(10),

seq_no integer,

lot_no char(20),

serial_no char(20),

lot_qty decimal(10),

cost decimal(12),

vend_code char(20),

expiry_date date,

po_no char(20),

rec_doc_no integer,

receipt_date date,

recv_qty decimal(10),

recv_cost decimal(12),

bin_location char(15),

so_doc_no integer,

so_line_no decimal(4,0),

cust_code char(20),

shipped_date date,

shipped_qty decimal(10,3),

mfr_serial char(30),

prod_order char(7),

prod_order_lot char(3),

warr_expire date,

warr_matl_cost decimal(10,2),

warr_labor_cost decimal(10,2),

warr_sales_amt decimal(10,2)