--create the table--

	CREATE TABLE "OWNING_ROAD" 
   ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"EMP_RAILROAD_IDENTIFIER" VARCHAR2(4 BYTE), 
	"RAILROAD_NAME" VARCHAR2(50 BYTE), 
	"SCAC" VARCHAR2(4 BYTE) NOT NULL ENABLE,
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"UPDATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"VERSION" NUMBER(19,0),
	 PRIMARY KEY ("ID"),
	 UNIQUE ("RAILROAD_NAME"),
	 UNIQUE ("SCAC")
	 );
	 
CREATE TABLE "DIVISION" 
   (	"ID" NUMBER(19,0) NOT NULL ENABLE, 
	"AD_LINK" VARCHAR2(50 BYTE), 
	"CONTACT_EMAIL" VARCHAR2(50 BYTE), 
	"CONTACT_NAME" VARCHAR2(50 BYTE), 
	"CONTACT_PHONE_1" VARCHAR2(50 BYTE), 
	"CONTACT_PHONE_2" VARCHAR2(50 BYTE), 
	"DIVISION_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
	"OWNING_ROAD_ID" NUMBER(19,0),
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"UPDATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"VERSION" NUMBER(19,0),
	 PRIMARY KEY ("ID"),
  	 FOREIGN KEY ("OWNING_ROAD_ID")
	 REFERENCES  "OWNING_ROAD" ("ID") ENABLE
  	 );

CREATE TABLE "SUBDIVISION" 
   (	"ID" NUMBER(19,0) NOT NULL ENABLE, 
	"AD_LINK" VARCHAR2(50 BYTE), 
	"CONTACT_EMAIL" VARCHAR2(50 BYTE), 
	"CONTACT_NAME" VARCHAR2(50 BYTE), 
	"CONTACT_PHONE_1" VARCHAR2(50 BYTE), 
	"CONTACT_PHONE_2" VARCHAR2(50 BYTE), 
	"DEPLOYMENT_STATUS" VARCHAR2(255 BYTE), 
	"SUBDIVISION_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
	"DIVISION_ID" NUMBER(19,0),
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"UPDATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"VERSION" NUMBER(19,0),
	 PRIMARY KEY ("ID"),
	 FOREIGN KEY ("DIVISION_ID")
	 REFERENCES "DIVISION" ("ID") ENABLE
	 );
	 
CREATE TABLE "AREA" 
   	("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"AREA_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	"ATCS_ADDRESS" NUMBER(19,0), 
	"EMP_ADDRESS" VARCHAR2(63 BYTE),
	"AREA_TYPE" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"SUBDIVISION_ID" NUMBER(19,0),
	"OWNING_ROAD_ID" NUMBER(19,0) NOT NULL ENABLE, 
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"UPDATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"VERSION" NUMBER(19,0),
	 PRIMARY KEY ("ID"),
	 FOREIGN KEY ("SUBDIVISION_ID")
	 REFERENCES "SUBDIVISION" ("ID") ENABLE,
  	 FOREIGN KEY ("OWNING_ROAD_ID")
	 REFERENCES  "OWNING_ROAD" ("ID") ENABLE
	 );
	 
CREATE TABLE "CUSTOM_AREA_FIELDS" 
    ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"KEY" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	"VALUE" VARCHAR2(255 BYTE), 
	"AREA_ID" NUMBER(19,0),
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"UPDATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"VERSION" NUMBER(19,0),
	 PRIMARY KEY ("ID"),
	 FOREIGN KEY ("AREA_ID")
	 REFERENCES "AREA" ("ID") ENABLE
	 );

 CREATE TABLE "ASSET" 
   ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"ASSET_TYPE" NUMBER(10,0), 
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
	"DEPLOYMENT_DATE" DATE, 
	"DEPLOYMENT_STATUS" VARCHAR2(255 BYTE), 
	"EMP_ADDRESS" VARCHAR2(63 BYTE) NOT NULL ENABLE, 
	"GPS_COORDINATES" SDO_GEOMETRY, 
	"HARDWARE_VERSION" VARCHAR2(255 BYTE), 
	"IN_SERVICE_DATE" DATE, 
	"LOCATION" NUMBER(10,0), 
	"MANUFACTURER" VARCHAR2(255 BYTE), 
	"PREVIOUS_ASSET_LINK" VARCHAR2(255 BYTE), 
	"SERIAL_NUMBER" NUMBER(19,2) NOT NULL ENABLE, 
	"SMID" VARCHAR2(32 BYTE) NOT NULL ENABLE, 
	"SOFTWARE_VERSION" VARCHAR2(255 BYTE), 
	"STATUS" NUMBER(10,0), 
	"STATE" NUMBER(10,0),
	"STATUS_STATE_UPDATED_DATE" DATE,
	"UPDATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
	"AREA_ID" NUMBER(19,0), 
	"VERSION" NUMBER(19,0),
	 PRIMARY KEY ("ID"),
  	 UNIQUE ("SMID"),
     FOREIGN KEY ("AREA_ID")
	 REFERENCES "AREA" ("ID") ENABLE
   );

CREATE TABLE "CUSTOM_ASSET_FIELDS" 
    ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"KEY" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
	"VALUE" VARCHAR2(255 BYTE), 
	"ASSET_ID" NUMBER(19,0),
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"UPDATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"VERSION" NUMBER(19,0),
	 PRIMARY KEY ("ID"),
	 FOREIGN KEY ("ASSET_ID")
	 REFERENCES "ASSET" ("ID") ENABLE
	 );

CREATE TABLE "ASSET_EVENT" 
   ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"CREATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"UPDATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"VERSION" NUMBER(19,0), 
	"ASSET_LOCATION" VARCHAR2(255 CHAR), 
	"COMPONENT_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"CONDITION_FLAG" NUMBER(10,0) NOT NULL ENABLE, 
	"DATE_TIME" TIMESTAMP (6) NOT NULL ENABLE, 
	"EVENT_INSTANCE_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"EVENT_TYPE_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"ASSET_ID" NUMBER(19,0) NOT NULL ENABLE, 
	 PRIMARY KEY ("ID"),
     FOREIGN KEY ("ASSET_ID")
	 REFERENCES "ASSET" ("ID") ENABLE
   );

   CREATE TABLE "ASSET_STATUS" 
   ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"CREATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"UPDATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"VERSION" NUMBER(19,0), 
	"ASSET_LOCATION" VARCHAR2(255 CHAR), 
	"COMPONENT_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"RESPONSE_CODE" NUMBER(10,0), 
	"STATE" NUMBER(10,0), 
	"STATUS" NUMBER(10,0), 
	"STATUS_STATE_UPDATED_DATE" TIMESTAMP (6), 
	"ASSET_ID" NUMBER(19,0) NOT NULL ENABLE, 
	 PRIMARY KEY ("ID"),
	 FOREIGN KEY ("ASSET_ID")
	 REFERENCES "ASSET" ("ID") ENABLE
   );
   
   CREATE TABLE "EVENT_VARIABLE" 
   ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"CREATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"UPDATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"VERSION" NUMBER(19,0), 
	"VALUE" BLOB, 
	"VARIABLE_ID" NUMBER(10,0), 
	"EVENT_ID" NUMBER(19,0) NOT NULL ENABLE, 
	 PRIMARY KEY ("ID"),
	 FOREIGN KEY ("EVENT_ID")
	 REFERENCES "ASSET_EVENT" ("ID") ENABLE
   );
   
   CREATE TABLE "STATUS_VARIABLE" 
   ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"CREATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"UPDATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"VERSION" NUMBER(19,0), 
	"VALUE" BLOB, 
	"VARIABLE_ID" NUMBER(10,0), 
	"STATUS_ID" NUMBER(19,0) NOT NULL ENABLE, 
	 PRIMARY KEY ("ID"),
	 FOREIGN KEY ("STATUS_ID")
	 REFERENCES "ASSET_STATUS" ("ID") ENABLE
   );
   
   CREATE TABLE "ASSET_VARIABLES" 
   ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"CREATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"UPDATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"VERSION" NUMBER(19,0),
    "VARIABLE_ID" NUMBER(10,0) NOT NULL ENABLE,
    "VALUE" BLOB,
	"ASSET_ID" NUMBER(19,0)NOT NULL ENABLE, 
	 PRIMARY KEY ("ID"),
     CONSTRAINT ASSET_VARIABLE_ID_SMID UNIQUE ("VARIABLE_ID","ASSET_ID"),
	 FOREIGN KEY ("ASSET_ID")
	 REFERENCES "ASSET" ("ID") ENABLE
   );
   
   CREATE SEQUENCE  "HIBERNATE_SEQUENCE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOPARTITION;