/* Oracle script to create extensible tables. Biotics Support Clinic 03/10/2009 */

/* first create the tables */

CREATE TABLE CAT_EXT (
       cat_ext_id           NUMBER NOT NULL,
       cat_name             VARCHAR2(20) NULL,
       cat_breed            VARCHAR2(2000) NULL,
       date_of_birth        DATE NULL,
       favorite_nap_spots   VARCHAR2(4000) NULL,
       d_size_ext_id        NUMBER NULL,
       element_national_id  NUMBER NOT NULL,
       DISPLAY_ORDER	    NUMBER NULL,
       CONSTRAINT XPKCAT_EXT 
              PRIMARY KEY (cat_ext_id)
);


CREATE TABLE D_SIZE_EXT (
       d_size_ext_id        NUMBER NOT NULL,
       size_cd              CHAR(1) NULL,
       size_desc            VARCHAR2(20) NULL,
       display_value        VARCHAR2(30) NULL,
       display_order        NUMBER NULL,
       CONSTRAINT XPKD_SIZE_EXT 
              PRIMARY KEY (d_size_ext_id)
);




/* now create the FK constraints */

ALTER TABLE CAT_EXT
       ADD  ( CONSTRAINT tax_natl_cat
              FOREIGN KEY (element_national_id)
                             REFERENCES TAXON_NATIONAL
                             ON DELETE CASCADE ) ;


ALTER TABLE CAT_EXT
       ADD  ( CONSTRAINT d_size_cat
              FOREIGN KEY (d_size_ext_id)
                             REFERENCES D_SIZE_EXT ) ;




/* populate the domain table */
insert into D_SIZE_EXT values (1, 'S', 'Small', 'S - Small', 1);
insert into D_SIZE_EXT values (2, 'M', 'Medium', 'M - Medium', 2);
insert into D_SIZE_EXT values (3, 'L', 'Large', 'L - Large', 3);
insert into D_SIZE_EXT values (4, 'K', 'King-Size', 'K - King-Size', 4);

COMMIT;

/* update NEXT_SEQ with your new tables */


INSERT INTO NEXT_SEQ(KEYWORD, NEXT_ID) VALUES (
'CAT_EXT',
(SELECT NVL(MAX(CAT_EXT_ID)+1, 1) FROM CAT_EXT)); 

INSERT INTO NEXT_SEQ(KEYWORD, NEXT_ID) VALUES (
'D_SIZE_EXT',
(SELECT NVL(MAX(D_SIZE_EXT_ID)+1, 1) FROM D_SIZE_EXT)); 


COMMIT;

/* UPDATE HDMS_TABLE */



insert into hdms_table (hdms_table_id, table_name, table_type, audit_comment, next_seq_keyword) values (
(SELECT MAX(hdms_table_id)+1 FROM hdms_table), 
'CAT_EXT', 'B', NULL, NULL);

insert into hdms_table (hdms_table_id, table_name, table_type, audit_comment, next_seq_keyword) values (
(SELECT MAX(hdms_table_id)+1 FROM hdms_table), 
'D_SIZE_EXT', 'D', NULL, NULL);

commit;