if exists (select 1 from sysobjects where name = 'C_customer')
    drop table C_customer;

create table C_customer
(
    c_zip varchar(9) null,
    c_ytd_payment numeric(12,2) null,
    c_street1 varchar(20) null,
    c_phone varchar(16) null,
    c_street2 varchar(20) null,
    c_state char(2) null,
    c_contact varchar(25) null,
    c_last varchar(16) null,
    c_credit_limit numeric(12,2) null,
    c_id integer not null,
    c_country varchar(10) null,
    c_first varchar(16) null,
    c_balance numeric(12,2) null,
    c_since datetime null,
    c_city varchar(20) null,
    c_credit char(2) null,
    version integer default 0 not null,
    primary key (c_id)
);

if exists (select 1 from sysobjects where name = 'C_customerinventory')
    drop table C_customerinventory;

create table C_customerinventory
(
    ci_itemid char(20) null,
    ci_customerid integer null,
    ci_id integer not null,
    ci_value numeric(12,2) null,
    ci_quantity integer null,
    version integer default 0 not null,
    primary key (ci_id)
);

create index C_ci_customerid_index on C_customerinventory (ci_customerid);

if exists (select 1 from sysobjects where name = 'C_parts')
    drop table C_parts;

create table C_parts
(
    p_id char(20) not null,
    p_name varchar(35),
    p_desc varchar(100),
    p_rev varchar(6),
    p_unit varchar(10),
    p_cost numeric(12,2),
    p_price numeric(12,2),
    p_planner integer,
    p_type integer,
    p_ind integer,
    p_lomark integer,
    p_himark integer,
    version integer default 0 not null,
    primary key (p_id)
);

if exists (select 1 from sysobjects where name = 'C_site')
    drop table C_site;

create table C_site
(
    site_id integer not null,
    site_name varchar(16),
    site_street1 varchar(20),
    site_street2 varchar(20),
    site_city varchar(20),
    site_state char(2),
    site_country varchar(10),
    site_zip varchar(9),
    version integer default 0 not null,
    primary key (site_id)
);

if exists (select 1 from sysobjects where name = 'C_supplier')
    drop table C_supplier;

create table C_supplier
(
    supp_id integer not null,
    supp_name varchar(16),
    supp_street1 varchar(20),
    supp_street2 varchar(20),
    supp_city varchar(20),
    supp_state char(2),
    supp_country varchar(10),
    supp_zip varchar(9),
    supp_phone varchar(16),
    supp_contact varchar(25),
    version integer default 0 not null,
    primary key (supp_id)
);

if exists (select 1 from sysobjects where name = 'M_bom')
    drop table M_bom;

create table M_bom
(
    b_assembly_id char(20) not null,
    b_line_no integer not null,
    b_ops integer null,
    b_eng_change varchar(10) null,
    b_comp_id char(20) not null,
    b_qty integer null,
    b_ops_desc varchar(100) null,
    version integer default 0 not null,
    primary key (b_assembly_id, b_line_no, b_comp_id)
);

if exists (select 1 from sysobjects where name = 'M_inventory')
    drop table M_inventory;

create table M_inventory
(
    in_qty integer null,
    in_ordered integer null,
    in_acc_code integer null,
    in_location char(20) null,
    in_p_id char(20) not null,
    in_act_date datetime null,
    for_update tinyint default 0 not null,
    primary key (in_p_id)
);

if exists (select 1 from sysobjects where name = 'M_largeorder')
    drop table M_largeorder;

create table M_largeorder
(
    lo_assembly_id char(20) null,
    lo_ol_id integer null,
    lo_category integer null,
    lo_qty integer null,
    lo_due_date datetime null,
    lo_o_id integer null,
    lo_id integer not null,
    version integer default 0 not null,
    primary key (lo_id)
);

create index M_lo_category_index on M_largeorder (lo_category);
create unique index M_ol_o_index on M_largeorder (lo_o_id, lo_ol_id);

if exists (select 1 from sysobjects where name = 'M_parts')
    drop table M_parts;

create table M_parts
(
    p_name varchar(35) null,
    p_rev varchar(6) null,
    p_type integer null,
    p_planner integer null,
    p_desc varchar(100) null,
    p_lomark integer null,
    p_ind integer null,
    p_id char(20) not null,
    p_himark integer null,
    version integer default 0 not null,
    primary key (p_id)
);

if exists (select 1 from sysobjects where name = 'M_workorder')
    drop table M_workorder;

create table M_workorder
(
    wo_assembly_id char(20) null,
    wo_comp_qty integer null,
    wo_orig_qty integer null,
    wo_status integer null,
    wo_start_date datetime null,
    wo_due_date datetime null,
    wo_number integer not null,
    wo_o_id integer null,
    wo_ol_id integer null,
    for_update tinyint default 0 not null,
    primary key (wo_number)
);

if exists (select 1 from sysobjects where name = 'O_item')
    drop table O_item;

create table O_item
(
    i_name varchar(35) null,
    i_desc varchar(100) null,
    i_discount numeric(6,4) null,
    i_price numeric(12,2) null,
    i_id char(20) not null,
    i_category integer not null,
    version integer default 0 not null,
    primary key (i_id)
);

create index O_i_category_index on O_item (i_category);

if exists (select 1 from sysobjects where name = 'O_orders')
    drop table O_orders;

create table O_orders
(
    o_entry_date datetime null,
    o_ship_date datetime null,
    o_discount numeric(4,2) null,
    o_ol_cnt integer null,
    o_total numeric(12,2) null,
    o_status integer null,
    o_c_id integer null,
    o_id integer not null,
    version integer default 0 not null,
    primary key (o_id)
);

create index O_o_c_id_index on O_orders (o_c_id);

if exists (select 1 from sysobjects where name = 'O_orderline')
    drop table O_orderline;

create table O_orderline
(
    ol_ship_date datetime null,
    ol_status integer null,
    ol_total_value numeric(12,2) null,
    ol_qty integer null,
    ol_i_id char(20) null,
    ol_o_id integer not null,
    ol_msrp numeric(12,2) null,
    ol_id integer not null,
    version integer default 0 not null,
    primary key (ol_o_id, ol_id)
);

if exists (select 1 from sysobjects where name = 'S_component')
    drop table S_component;

create table S_component
(
    qty_demanded integer null,
    comp_desc varchar(100) null,
    comp_name varchar(10) null,
    qty_on_order integer null,
    comp_unit varchar(10) null,
    comp_id char(20) not null,
    lead_time integer null,
    comp_cost numeric(12,2) null,
    container_size integer null,
    version integer default 0 not null,
    primary key (comp_id)
);

if exists (select 1 from sysobjects where name = 'S_purchase_order')
    drop table S_purchase_order;

create table S_purchase_order
(
    po_number integer not null,
    po_supp_id integer null,
    po_site_id integer null,
    version integer default 0 not null,
    primary key (po_number)
);

if exists (select 1 from sysobjects where name = 'S_purchase_orderline')
    drop table S_purchase_orderline;

create table S_purchase_orderline
(
    pol_number integer not null,
    pol_balance numeric(12,2) null,
    pol_po_id integer not null,
    pol_qty integer null,
    pol_message varchar(100) null,
    pol_p_id char(20) null,
    pol_deldate datetime null,
    version integer default 0 not null,
    primary key (pol_number, pol_po_id)
);

if exists (select 1 from sysobjects where name = 'S_site')
    drop table S_site;

create table S_site
(
    site_id integer not null,
    site_name varchar(16),
    site_street1 varchar(20),
    site_street2 varchar(20),
    site_city varchar(20),
    site_state char(2),
    site_country varchar(10),
    site_zip varchar(9),
    version integer default 0 not null,
    primary key (site_id)
);

if exists (select 1 from sysobjects where name = 'S_supplier')
    drop table S_supplier;

create table S_supplier
(
    supp_city varchar(20) null,
    supp_street2 varchar(20) null,
    supp_name varchar(16) null,
    supp_phone varchar(16) null,
    supp_contact varchar(25) null,
    supp_id integer not null,
    supp_zip varchar(9) null,
    supp_street1 varchar(20) null,
    supp_country varchar(10) null,
    supp_state char(2) null,
    version integer default 0 not null,
    primary key (supp_id)
);

if exists (select 1 from sysobjects where name = 'S_supp_component')
    drop table S_supp_component;

create table S_supp_component
(
    sc_price numeric(12,2) null,
    sc_p_id char(20) not null,
    sc_supp_id integer not null,
    sc_qty integer null,
    sc_del_date integer null,
    sc_discount numeric(6,4) null,
    version integer default 0 not null,
    primary key (sc_p_id, sc_supp_id)
);

if exists (select 1 from sysobjects where name = 'U_sequences')
    drop table U_sequences;

create table U_sequences
(
    s_blocksize integer null,
    s_nextnum integer null,
    s_id varchar(50) not null,
    for_update tinyint default 0 not null,
    primary key (s_id)
);
