CREATE SCHEMA "MSTR";

--DROP TABLE "MSTR"."CITY_MDA";

CREATE COLUMN TABLE "MSTR"."CITY_MDA" 

( "ID" INTEGER CS_INT NOT NULL,
  "CITY_ID" BIGINT CS_FIXED NOT NULL,        
  "STATE_ID" BIGINT,
  "LATITUDE" DECIMAL(17,6) CS_FIXED,
  "LONGITUDE" DECIMAL(17,6) CS_FIXED,
  "REGION_ID" BIGINT,
  "COUNTRY_ID" BIGINT,
 PRIMARY KEY ("CITY_ID"));

--DROP TABLE "MSTR"."CITY_TXT";

CREATE COLUMN TABLE "MSTR"."CITY_TXT" 

( "ID" INTEGER CS_INT NOT NULL,
  "CITY_ID" BIGINT CS_FIXED NOT NULL,        
  "DESC" VARCHAR(35),
 PRIMARY KEY ("CITY_ID"));

--DROP TABLE "MSTR"."STATE_TXT";

CREATE COLUMN TABLE "MSTR"."STATE_TXT" 

( "ID" INTEGER CS_INT NOT NULL,
  "STATE_ID" BIGINT CS_FIXED NOT NULL,        
  "DESC" VARCHAR(35),
 PRIMARY KEY ("STATE_ID"));

--DROP TABLE "MSTR"."COUNTRY_TXT";

CREATE COLUMN TABLE "MSTR"."COUNTRY_TXT" 

( "ID" INTEGER CS_INT NOT NULL,
  "COUNTRY_ID" BIGINT CS_FIXED NOT NULL,        
  "DESC" VARCHAR(35),
 PRIMARY KEY ("COUNTRY_ID"));

--DROP TABLE "MSTR"."REGION_TXT";

CREATE COLUMN TABLE "MSTR"."REGION_TXT" 

( ID INTEGER CS_INT NOT NULL,
  REGION_ID BIGINT CS_FIXED NOT NULL,         
  DESC VARCHAR(35),
 PRIMARY KEY (REGION_ID));

--DROP TABLE "MSTR"."VENDOR_MDA";

CREATE COLUMN TABLE "MSTR"."VENDOR_MDA" 

( "ID" INTEGER CS_INT NOT NULL,
  "VENDOR_ID" BIGINT CS_FIXED NOT NULL,         
  "CITY_ID" BIGINT,
 PRIMARY KEY ("VENDOR_ID"));

--DROP TABLE "MSTR"."VENDOR_TXT";

CREATE COLUMN TABLE "MSTR"."VENDOR_TXT" 

( "ID" INTEGER CS_INT NOT NULL,
  "VENDOR_ID" BIGINT CS_FIXED NOT NULL,         
  "DESC" VARCHAR(35),
 PRIMARY KEY ("VENDOR_ID"));

--DROP TABLE "MSTR"."PO_ORG_MDA";

CREATE COLUMN TABLE "MSTR"."PO_ORG_MDA" 

( "ID" INTEGER CS_INT NOT NULL,
  "PO_ORG_ID" BIGINT CS_FIXED NOT NULL,       
  "CITY_ID" BIGINT,
 PRIMARY KEY ("PO_ORG_ID"));

--DROP TABLE "MSTR"."PO_ORG_TXT";

CREATE COLUMN TABLE "MSTR"."PO_ORG_TXT" 

( "ID" INTEGER CS_INT NOT NULL,
  "PO_ORG_ID" BIGINT CS_FIXED NOT NULL,         
  "DESC" VARCHAR(40),
 PRIMARY KEY ("PO_ORG_ID"));

--DROP TABLE "MSTR"."MATCL_TXT";

CREATE COLUMN TABLE "MSTR"."MATCL_TXT" 

( "ID" INTEGER CS_INT NOT NULL,
  "MATCL_ID" BIGINT CS_FIXED NOT NULL,         
   "DESC" VARCHAR(35),
 PRIMARY KEY ("MATCL_ID"));

--DROP TABLE "MSTR"."MATERIAL_MDA";

CREATE COLUMN TABLE "MSTR"."MATERIAL_MDA" 

( "ID" INTEGER CS_INT NOT NULL,
  "MATERIAL_ID" BIGINT CS_FIXED NOT NULL,         
  "MATCL_ID" BIGINT,
  "VENDOR_ID" BIGINT,
  "UNIT_PRICE" DOUBLE CS_DOUBLE,
 PRIMARY KEY ("MATERIAL_ID"));

--DROP TABLE "MSTR"."MATERIAL_TXT";

CREATE COLUMN TABLE "MSTR"."MATERIAL_TXT" 

( "ID" INTEGER CS_INT NOT NULL, 
  "MATERIAL_ID" BIGINT CS_FIXED NOT NULL,          
   "DESC" VARCHAR(80), 
 PRIMARY KEY ("MATERIAL_ID"));

--DROP TABLE "MSTR"."TIME_MDA";

CREATE COLUMN TABLE "MSTR"."TIME_MDA" ( 
"ID" INTEGER CS_INT NOT NULL,
"DAY_DATE" DAYDATE CS_DAYDATE, 
"MONTH_ID" VARCHAR(6),
"QUARTER_ID" VARCHAR(5),
"YEAR" VARCHAR(4),
"PREV_DAY_DATE" DAYDATE CS_DAYDATE,
"LM_DAY_DATE" DAYDATE CS_DAYDATE,
"LQ_DAY_DATE" DAYDATE CS_DAYDATE,
"LY_DAY_DATE" DAYDATE CS_DAYDATE,
PRIMARY KEY ("DAY_DATE"));

--DROP TABLE "MSTR"."PO_HEADER";

CREATE COLUMN TABLE "MSTR"."PO_HEADER" (
"PO_NBR" BIGINT CS_FIXED NOT NULL , 
"PO_DATE" DAYDATE CS_DAYDATE, 
"PO_ORG_ID" BIGINT,
"VENDOR_ID" BIGINT, 
"NUMBER" BIGINT, 
PRIMARY KEY ("PO_NBR"));

--DROP TABLE "MSTR"."PO_ITEM";

CREATE COLUMN TABLE "MSTR"."PO_ITEM" (
"PO_NBR" BIGINT CS_FIXED NOT NULL,
"PO_ITEM" BIGINT CS_FIXED NOT NULL,
"MATERIAL_ID" BIGINT,
"QUANTITY" DOUBLE CS_DOUBLE, 
"PRICE" DOUBLE CS_DOUBLE,
"VALUE" DOUBLE CS_DOUBLE,
"CURR_ID" NVARCHAR(5),
"UNIT_ID" NVARCHAR(5),
"REQ_DEL_DATE" DAYDATE CS_DAYDATE,  
PRIMARY KEY ("PO_NBR", "PO_ITEM"));

--DROP TABLE "MSTR"."DELIVERY";

CREATE COLUMN TABLE "MSTR"."DELIVERY" (
"DELIVERY" BIGINT CS_FIXED NOT NULL,
"PO_NBR" BIGINT CS_FIXED NOT NULL,
"PO_ITEM" BIGINT CS_FIXED NOT NULL,
"MATERIAL_ID" BIGINT,
"DEL_QUANTITY" DOUBLE CS_DOUBLE, 
"UNIT_ID" NVARCHAR(5),
"ACT_DEL_DATE" DAYDATE CS_DAYDATE,  
PRIMARY KEY ("PO_NBR", "PO_ITEM", "DELIVERY"));