-- The column ordering here and in the loader (LoadManufacturing.java) must match, 
-- so that flat file generation properly maps to columns

DROP TABLE M_PARTS;
CREATE TABLE M_PARTS
(
   P_ID           char(20) not null,
   P_NAME         char(35),
   P_DESC         varchar(100),
   P_REV          char(6),
   P_PLANNER      integer,
   P_TYPE         integer,
   P_IND          integer,
   P_LOMARK       integer,
   P_HIMARK       integer,
   P_VERSION      integer default 0
);

CREATE UNIQUE INDEX M_PARTS_IDX ON M_PARTS (P_ID);

DROP TABLE M_BOM;
CREATE TABLE M_BOM
(
   B_COMP_ID      char(20) not null,
   B_ASSEMBLY_ID  char(20) not null,
   B_LINE_NO      integer,
   B_QTY          integer,
   B_OPS          integer,
   B_ENG_CHANGE   char(10),
   B_OPS_DESC     varchar(100),
   B_VERSION      integer default 0
);

CREATE UNIQUE INDEX M_BOM_IDX ON M_BOM (B_ASSEMBLY_ID, B_COMP_ID, B_LINE_NO);

DROP TABLE M_WORKORDER;
CREATE TABLE M_WORKORDER
(
   WO_NUMBER      integer not null,
   WO_LOCATION    integer not null,
   WO_O_ID        integer,
   WO_OL_ID       integer,
   WO_STATUS      integer,
   WO_ASSEMBLY_ID char(20),
   WO_ORIG_QTY    integer,
   WO_COMP_QTY    integer,
   WO_DUE_DATE    timestamp,
   WO_START_DATE  timestamp,
   WO_VERSION     integer default 0
);

CREATE UNIQUE INDEX M_WO_IDX ON M_WORKORDER (WO_LOCATION, WO_NUMBER);
CREATE INDEX M_WO_OOL_IDX ON M_WORKORDER (WO_LOCATION, WO_O_ID, WO_OL_ID);

DROP TABLE M_INVENTORY;
CREATE TABLE M_INVENTORY
(
   IN_P_ID        char(20) not null,
   IN_QTY         integer,
   IN_ORDERED     integer,
   IN_LOCATION    integer not null,
   IN_ACC_CODE    integer,
   IN_ACT_DATE    timestamp,
   IN_VERSION     integer default 0
);

CREATE UNIQUE INDEX M_INV_IDX ON M_INVENTORY (IN_LOCATION, IN_P_ID);

