--
-- Copyright 2005-2016 The Kuali Foundation
--
-- Licensed under the Educational Community License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
-- http://www.opensource.org/licenses/ecl2.php
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--

CREATE TABLE KRIM_HIST_GRP_T  (
  GRP_HIST_ID   DECIMAL(19,0) NOT NULL,
	GRP_ID      	varchar2(40) NOT NULL,
	OBJ_ID      	varchar2(36) NOT NULL,
	VER_NBR     	Number(8,0) NOT NULL DEFAULT '1',
	GRP_NM      	varchar2(80) NOT NULL,
	NMSPC_CD    	varchar2(40) NOT NULL,
	GRP_DESC    	varchar2(4000) NULL,
	KIM_TYP_ID  	varchar2(40) NOT NULL,
	ACTV_IND    	varchar2(1) NULL DEFAULT 'Y',
	ACTV_FRM_DT 	date NULL,
	ACTV_TO_DT  	date NULL,
	LAST_UPDT_DT	date NULL,
	PRIMARY KEY(GRP_HIST_ID),
	CONSTRAINT KRIM_HIST_GRP_FK1
    FOREIGN KEY (KIM_TYP_ID )
    REFERENCES KRIM_TYP_T (KIM_TYP_ID )
)
/
ALTER TABLE KRIM_HIST_GRP_T
	ADD CONSTRAINT KRIM_HIST_GRP_TC0
	UNIQUE (OBJ_ID)
/
CREATE INDEX KRIM_HIST_GRP_TI1 ON KRIM_HIST_GRP_T (GRP_ID, ACTV_FRM_DT, ACTV_TO_DT)
/
CREATE INDEX KRIM_HIST_GRP_TI2 ON KRIM_HIST_GRP_T (NMSPC_CD, GRP_NM, ACTV_FRM_DT, ACTV_TO_DT)
/
CREATE SEQUENCE KRIM_HIST_GRP_ID_S INCREMENT BY 1 START WITH 10000 NOMAXVALUE NOCYCLE NOCACHE ORDER
/
CREATE TABLE KRIM_HIST_GRP_ATTR_DATA_T  (
  HIST_ID           varchar2(40) NOT NULL,
	ATTR_DATA_ID    	varchar2(40) NOT NULL,
	OBJ_ID          	varchar2(36) NOT NULL,
	VER_NBR         	number(8,0) NOT NULL DEFAULT '1',
	GRP_ID          	varchar2(40) NULL,
	GRP_HIST_ID       varchar2(40) NOT NULL,
	KIM_TYP_ID      	varchar2(40) NOT NULL,
	KIM_ATTR_DEFN_ID	varchar2(40) NULL,
	ATTR_VAL        	varchar2(400) NULL,
	PRIMARY KEY(HIST_ID),
	CONSTRAINT KRIM_HIST_GRP_ATTR_DATA_FK1
    FOREIGN KEY (KIM_TYP_ID )
    REFERENCES KRIM_TYP_T (KIM_TYP_ID )
)
/
ALTER TABLE KRIM_HIST_GRP_ATTR_DATA_T
	ADD CONSTRAINT KRIM_HIST_GRP_ATTR_DATA_TC0
	UNIQUE (OBJ_ID)
/
ALTER TABLE KRIM_HIST_GRP_ATTR_DATA_T
	ADD CONSTRAINT KRIM_HIST_GRP_ATTR_DATA_TR3
	FOREIGN KEY(GRP_HIST_ID)
	REFERENCES krim_hist_grp_t(GRP_HIST_ID)
	ON DELETE CASCADE
	ON UPDATE RESTRICT
/
ALTER TABLE KRIM_HIST_GRP_ATTR_DATA_T
	ADD CONSTRAINT KRIM_HIST_GRP_ATTR_DATA_TR2
	FOREIGN KEY(KIM_ATTR_DEFN_ID)
	REFERENCES krim_attr_defn_t(KIM_ATTR_DEFN_ID)
	ON DELETE RESTRICT
	ON UPDATE RESTRICT
/
CREATE SEQUENCE KRIM_HIST_GRP_ATTR_DATA_ID_S INCREMENT BY 1 START WITH 10000 NOMAXVALUE NOCYCLE NOCACHE ORDER
/
