--------------------------------------------------------
--  File created - Wednesday-September-30-2015   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table MD_PROGRAM
--------------------------------------------------------

  CREATE TABLE "STORETW"."MD_PROGRAM" 
   (	"PK_ISN" NUMBER(12,0), 
	"ORGANIZATION_ID" VARCHAR2(256 BYTE), 
	"PROGRAM_IS_NUMBER" NUMBER(12,0), 
	"PROGRAM_TYPE" VARCHAR2(256 CHAR), 
	"PROGRAM_NAME" VARCHAR2(256 CHAR), 
	"DESCRIPTION" VARCHAR2(4000 CHAR), 
	"ORGANIZATION_IS_NUMBER" NUMBER(20,0), 
	"PROJECT_ID" VARCHAR2(256 CHAR), 
	"PROJECT_NAME" VARCHAR2(256 CHAR), 
	"FK_ORG" NUMBER(12,0), 
	"SOURCE_SYSTEM" VARCHAR2(256 CHAR), 
	"SOURCE_UID" NUMBER(20,0)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 90 INITRANS 1 MAXTRANS 255 
 COMPRESS FOR OLTP NOLOGGING
  STORAGE(INITIAL 10485760 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STORETDATA_STORMODP" ;

   COMMENT ON COLUMN "STORETW"."MD_PROGRAM"."PK_ISN" IS 'Primary Key. Generated using sequence object SEQ_PROGRAM.';
   COMMENT ON COLUMN "STORETW"."MD_PROGRAM"."ORGANIZATION_ID" IS 'The user-defined code that uniquely identifies an Organization.  Organization IDs are registered centrally with STORET so that they are guaranteed to be unique on the National level.  They become part of the Oracle Key on every row in the STORET database that belongs to the Organization.';
   COMMENT ON COLUMN "STORETW"."MD_PROGRAM"."PROGRAM_IS_NUMBER" IS 'Program internal serial number';
   COMMENT ON COLUMN "STORETW"."MD_PROGRAM"."PROGRAM_TYPE" IS 'program type';
   COMMENT ON COLUMN "STORETW"."MD_PROGRAM"."PROGRAM_NAME" IS 'program name';
   COMMENT ON COLUMN "STORETW"."MD_PROGRAM"."DESCRIPTION" IS 'A text description of the referenced Method or Datum.';
   COMMENT ON COLUMN "STORETW"."MD_PROGRAM"."ORGANIZATION_IS_NUMBER" IS 'A system-generated value used to uniquely identify an occurrence of this table.';
   COMMENT ON COLUMN "STORETW"."MD_PROGRAM"."PROJECT_ID" IS 'Project identifier. This is uniquer with in an organization.';
   COMMENT ON COLUMN "STORETW"."MD_PROGRAM"."PROJECT_NAME" IS 'The name assigned by the owning Organization to the Project or Survey.';
   COMMENT ON COLUMN "STORETW"."MD_PROGRAM"."FK_ORG" IS 'Foreign Key referencing DI_ORG.';
   COMMENT ON COLUMN "STORETW"."MD_PROGRAM"."SOURCE_SYSTEM" IS 'Metadata source system acronym';
   COMMENT ON COLUMN "STORETW"."MD_PROGRAM"."SOURCE_UID" IS 'Metadata source record unique identifier';
   COMMENT ON TABLE "STORETW"."MD_PROGRAM"  IS 'Program metadata table';
REM INSERTING into STORETW.MD_PROGRAM
SET DEFINE OFF;
--------------------------------------------------------
--  DDL for Index PK_PROGRAM
--------------------------------------------------------

  CREATE UNIQUE INDEX "STORETW"."PK_PROGRAM" ON "STORETW"."MD_PROGRAM" ("PK_ISN") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 327680 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STORETINDX_STORMODP" ;
--------------------------------------------------------
--  DDL for Index IPRGORGID
--------------------------------------------------------

  CREATE INDEX "STORETW"."IPRGORGID" ON "STORETW"."MD_PROGRAM" ("ORGANIZATION_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 589824 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STORETINDX_STORMODP" ;
--------------------------------------------------------
--  DDL for Index IPROFK
--------------------------------------------------------

  CREATE INDEX "STORETW"."IPROFK" ON "STORETW"."MD_PROGRAM" ("FK_ORG") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 589824 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STORETINDX_STORMODP" ;
--------------------------------------------------------
--  Constraints for Table MD_PROGRAM
--------------------------------------------------------

  ALTER TABLE "STORETW"."MD_PROGRAM" ADD CONSTRAINT "PK_PROGRAM" PRIMARY KEY ("PK_ISN")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 327680 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STORETINDX_STORMODP"  ENABLE;
  ALTER TABLE "STORETW"."MD_PROGRAM" MODIFY ("PROJECT_NAME" NOT NULL ENABLE);
  ALTER TABLE "STORETW"."MD_PROGRAM" MODIFY ("PROJECT_ID" NOT NULL ENABLE);
  ALTER TABLE "STORETW"."MD_PROGRAM" MODIFY ("PROGRAM_NAME" NOT NULL ENABLE);
  ALTER TABLE "STORETW"."MD_PROGRAM" MODIFY ("PROGRAM_TYPE" NOT NULL ENABLE);
  ALTER TABLE "STORETW"."MD_PROGRAM" MODIFY ("ORGANIZATION_ID" NOT NULL ENABLE);
--------------------------------------------------------
--  Ref Constraints for Table MD_PROGRAM
--------------------------------------------------------

  ALTER TABLE "STORETW"."MD_PROGRAM" ADD CONSTRAINT "FK_MD_PGM" FOREIGN KEY ("FK_ORG")
	  REFERENCES "STORETW"."DI_ORG" ("PK_ISN") ENABLE;
