Database www.dggs.alaska.gov by roni bombardero

/******* HARD ROCK INVENTORY DATABASE CHANGES ***********/

/*******************
*
* REF_ALASKA_PLACE_NAMES MODIFICATIONS
* Prospects will allgo here, regardless of ARDF
*
********************/

--ALTER TABLE DGGS_ANALYSIS.REF_ALASKA_PLACE_NAMES ADD ("PLACE_STATUS" VARCHAR2(20));  --TODO: create domain values and CK constraint  --doc done
--ALTER TABLE DGGS_ANALYSIS.REF_ALASKA_PLACE_NAMES DROP ("PLACE_STATUS");              -- get rid of this new column, add values to existing domain
ALTER TABLE DGGS_ANALYSIS.REF_ALASKA_PLACE_NAMES ADD ("REMARKS" VARCHAR2(2000));

ALTER TABLE DGGS_ANALYSIS.REF_ALASKA_PLACE_NAMES MODIFY "PLACE_FEATURE_TYPE" VARCHAR2(20);   --NEW

ALTER TABLE DGGS_ANALYSIS.REF_ALASKA_PLACE_NAMES ADD ("ALIASES" VARCHAR2(300));   --doc done    --consider renaming to alias_or_associations
ALTER TABLE DGGS_ANALYSIS.REF_ALASKA_PLACE_NAMES ADD ("REGION_ID" INTEGER);       --doc done
GRANT REFERENCES ON DGGS_MINERAL_ACTIVITY.REF_MINERAL_PROP_REGION TO DGGS_ANALYSIS WITH GRANT OPTION;
ALTER TABLE DGGS_ANALYSIS.REF_ALASKA_PLACE_NAMES ADD CONSTRAINT "FK_REF_ALASKA_P_R_REF_MIN_REGI" FOREIGN KEY("REGION_ID") REFERENCES DGGS_MINERAL_ACTIVITY.REF_MINERAL_PROP_REGION("MINERAL_PROPERTY_REGION_ID");

/*******************
*
* BOREHOLE MODIFICATIONS
*
********************/

ALTER TABLE DGGS_ANALYSIS.BOREHOLE DROP("BORE_NUMBER");           --doc done
ALTER TABLE DGGS_ANALYSIS.BOREHOLE ADD ("DRILL_RECORD_BORE_NAME" VARCHAR2(25)); --doc done
ALTER TABLE DGGS_ANALYSIS.BOREHOLE ADD ("DRILL_RECORD_LOCATION" VARCHAR2(2000));   --doc done
ALTER TABLE DGGS_ANALYSIS.BOREHOLE ADD ("DRILL_RECORD_CORE_SIZE" VARCHAR2(20)); -- TODO: create domain values and CK constraint   --doc done
ALTER TABLE DGGS_ANALYSIS.BOREHOLE ADD ("RECOMMENDED_RESEARCH" VARCHAR2(2000));  --doc done

ALTER TABLE DGGS_ANALYSIS.BOREHOLE DROP("DRILLING_COMPANY_ID") CASCADE CONSTRAINTS;    --doc done
ALTER TABLE DGGS_ANALYSIS.BOREHOLE RENAME COLUMN BOREHOLE_OPERATOR_ID TO ORGANIZATION_ID;  --doc done
ALTER TABLE DGGS_ANALYSIS.BOREHOLE ADD ("ORGANIZATION_ROLE" VARCHAR2(20));
ALTER TABLE DGGS_ANALYSIS.BOREHOLE ADD (CONSTRAINT "CK_BOREHOLE_ORGANIZATION_ROLE" CHECK(ORGANIZATION_ROLE in ('owner','operator','driller','unknown')));
INSERT INTO DGGS_PROJECT.DOMAIN_LIST (DOMAIN_NAME, VALID_VALUE_DEF, DOMAIN_CHAR) VALUES ('Organization_Role', 'Owner','owner');
INSERT INTO DGGS_PROJECT.DOMAIN_LIST (DOMAIN_NAME, VALID_VALUE_DEF, DOMAIN_CHAR) VALUES ('Organization_Role', 'Operator','operator');
INSERT INTO DGGS_PROJECT.DOMAIN_LIST (DOMAIN_NAME, VALID_VALUE_DEF, DOMAIN_CHAR) VALUES ('Organization_Role', 'Driller','driller');
INSERT INTO DGGS_PROJECT.DOMAIN_LIST (DOMAIN_NAME, VALID_VALUE_DEF, DOMAIN_CHAR) VALUES ('Organization_Role', 'Unknown','unknown');

/*******************
*
* BOREHOLE CITATION MODIFICATIONS
*
********************/
ALTER TABLE DGGS_ANALYSIS.BOREHOLE_CITATION_XRC ADD ("SOURCE_DATA_TYPE" VARCHAR2(30)); -- domain field
ALTER TABLE DGGS_ANALYSIS.BOREHOLE_CITATION_XRC ADD ("REMARKS" VARCHAR2(2000));
ALTER TABLE DGGS_ANALYSIS.BOREHOLE_CITATION_XRC DROP CONSTRAINT PK_BOREHOLE_CITATION_XRC;
/*******************
*
* CREATE REF_CONTAINER_TYPE
*
********************/

CREATE TABLE DGGS_ANALYSIS.REF_CONTAINER_TYPE
("CONTAINER_TYPE_ID" INTEGER NOT NULL,
"CONTAINER_TYPE_ABBREV" VARCHAR2(20),
"CONTAINER_TYPE" VARCHAR2(100),
"LAST_USER" VARCHAR2(30),
"LAST_MODIFIED" DATE,
"CLASSIFICATION_SOURCE_ID" INTEGER
);

ALTER TABLE DGGS_ANALYSIS.REF_CONTAINER_TYPE ADD CONSTRAINT "PK_REF_CONTAINER_TYPE" PRIMARY KEY("CONTAINER_TYPE_ID");
CREATE SEQUENCE "DGGS_ANALYSIS"."CONTAINER_TYPE_SEQ" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
GRANT SELECT ON DGGS_ANALYSIS.CONTAINER_TYPE_SEQ TO ANALYSIS_DBA;
GRANT SELECT ON DGGS_ANALYSIS.CONTAINER_TYPE_SEQ TO ANALYSIS_EDIT;

ALTER TABLE DGGS_ANALYSIS.REF_CONTAINER_TYPE ADD CONSTRAINT "FK_REF_CONTAINE_R_CLASSIFICATI" FOREIGN KEY("CLASSIFICATION_SOURCE_ID") REFERENCES DGGS_PUBLICATIONS.CLASSIFICATION_SOURCE_FOR_REFS("CLASSIFICATION_SOURCE_ID");

CREATE OR REPLACE TRIGGER "DGGS_ANALYSIS"."REF_CONTAINER_TYPE_IBTR1" BEFORE
INSERT ON "DGGS_ANALYSIS"."REF_CONTAINER_TYPE" FOR EACH ROW WHEN (new.container_type_id is null) BEGIN

SELECT container_type_seq.nextval
INTO :new.container_type_id
FROM DUAL;

end;
/

CREATE OR REPLACE TRIGGER "DGGS_ANALYSIS"."REF_CONTAINER_TYPE_IUBTR2" BEFORE
INSERT
OR UPDATE ON "DGGS_ANALYSIS"."REF_CONTAINER_TYPE" FOR EACH ROW

begin
SELECT USER
INTO :new.last_user
FROM DUAL;

SELECT SYSDATE
INTO :new.last_modified
FROM DUAL;

end;
/

GRANT SELECT ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO DGGS_AIRBORNE_GEOPHYSICAL WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO DGGS_METADATA WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO DGGS_COAL_RESOURCES WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO DGGS_MINERAL_ACTIVITY WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO DGGS_PROJECT WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO DGGS_PUBLICATIONS WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO DGGS_SPATIAL_DESC WITH GRANT OPTION;

GRANT ALTER ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO ANALYSIS_DBA;
GRANT FLASHBACK ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO ANALYSIS_DBA;
GRANT DEBUG ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO ANALYSIS_DBA;
GRANT QUERY REWRITE ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO ANALYSIS_DBA;
GRANT ON COMMIT REFRESH ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO ANALYSIS_DBA;
GRANT UPDATE ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO ANALYSIS_DBA;
GRANT SELECT ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO ANALYSIS_DBA;
GRANT DELETE ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO ANALYSIS_DBA;
GRANT INSERT ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO ANALYSIS_DBA;
GRANT DELETE ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO ANALYSIS_EDIT;
GRANT INSERT ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO ANALYSIS_EDIT;
GRANT SELECT ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO ANALYSIS_EDIT;
GRANT UPDATE ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO ANALYSIS_EDIT;
GRANT SELECT ON DGGS_ANALYSIS.REF_CONTAINER_TYPE TO ANALYSIS_VIEW;



/*******************
*
* CREATE GMC_INVENTORY_RECORD
*
********************/

CREATE TABLE DGGS_ANALYSIS.GMC_INVENTORY_RECORD
("GMC_INVENTORY_RECORD_ID" INTEGER NOT NULL,
"FIELD_STATION_ID" INTEGER NOT NULL,
"DATA_SOURCE_ID" INTEGER NOT NULL,
"DONOR_ID" INTEGER,
--"COLLECTION_ID" INTEGER,  -- is this needed?
"STORAGE_ROOM_ID" INTEGER,
"STORAGE_LOCATION_ID" INTEGER,
"RECEIVED_DATE" DATE NOT NULL,
"SHELF" VARCHAR2(30),
"BOX_COUNT" INTEGER,
"SORTED" CHAR(1),
"CORE_DIAMETER"  VARCHAR2(20),
"CONTAINER_TYPE_ID"  INTEGER,
"SAMPLE_METHOD_ID" INTEGER,
"CURATOR_REMARKS" VARCHAR2(2000),
"INVENTORY_REMARKS" VARCHAR2(2000),
"LAST_USER" VARCHAR2(30),
"LAST_MODIFIED" DATE
);

ALTER TABLE DGGS_ANALYSIS.GMC_INVENTORY_RECORD ADD CONSTRAINT "PK_GMC_INVENTORY_RECORD" PRIMARY KEY("GMC_INVENTORY_RECORD_ID");
CREATE SEQUENCE "DGGS_ANALYSIS"."GMC_INVENTORY_RECORD_SEQ" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD_SEQ TO ANALYSIS_DBA;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD_SEQ TO ANALYSIS_EDIT;

CREATE OR REPLACE TRIGGER "DGGS_ANALYSIS"."GMC_INVENTORY_RECORD_IBTR1" BEFORE
INSERT ON "DGGS_ANALYSIS"."GMC_INVENTORY_RECORD" FOR EACH ROW WHEN (new.gmc_inventory_record_id is null) BEGIN

SELECT gmc_inventory_record_seq.nextval
INTO :new.gmc_inventory_record_id
FROM DUAL;

end;
/

CREATE OR REPLACE TRIGGER "DGGS_ANALYSIS"."GMC_INVENTORY_RECORD_IUBTR2" BEFORE
INSERT
OR UPDATE ON "DGGS_ANALYSIS"."GMC_INVENTORY_RECORD" FOR EACH ROW

begin
SELECT USER
INTO :new.last_user
FROM DUAL;

SELECT SYSDATE
INTO :new.last_modified
FROM DUAL;

end;
/

GRANT SELECT ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO DGGS_AIRBORNE_GEOPHYSICAL WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO DGGS_METADATA WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO DGGS_COAL_RESOURCES WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO DGGS_MINERAL_ACTIVITY WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO DGGS_PROJECT WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO DGGS_PUBLICATIONS WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO DGGS_SPATIAL_DESC WITH GRANT OPTION;

GRANT ALTER ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO ANALYSIS_DBA;
GRANT FLASHBACK ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO ANALYSIS_DBA;
GRANT DEBUG ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO ANALYSIS_DBA;
GRANT QUERY REWRITE ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO ANALYSIS_DBA;
GRANT ON COMMIT REFRESH ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO ANALYSIS_DBA;
GRANT UPDATE ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO ANALYSIS_DBA;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO ANALYSIS_DBA;
GRANT DELETE ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO ANALYSIS_DBA;
GRANT INSERT ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO ANALYSIS_DBA;
GRANT DELETE ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO ANALYSIS_EDIT;
GRANT INSERT ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO ANALYSIS_EDIT;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO ANALYSIS_EDIT;
GRANT UPDATE ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO ANALYSIS_EDIT;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INVENTORY_RECORD TO ANALYSIS_VIEW;


ALTER TABLE DGGS_ANALYSIS.GMC_INVENTORY_RECORD ADD CONSTRAINT "FK_GMC_INVENTOR_R_DATA_SOURCE" FOREIGN KEY("FIELD_STATION_ID", "DATA_SOURCE_ID") REFERENCES DGGS_ANALYSIS.DATA_SOURCE("FIELD_STATION_ID", "DATA_SOURCE_ID");
ALTER TABLE DGGS_ANALYSIS.GMC_INVENTORY_RECORD ADD CONSTRAINT "FK_GMC_INVENTOR_R_CONTACT" FOREIGN KEY("DONOR_ID") REFERENCES DGGS_PROJECT.CONTACT("CONTACT_ID");
ALTER TABLE DGGS_ANALYSIS.GMC_INVENTORY_RECORD ADD CONSTRAINT "FK_GMC_INVENTOR_R_REF_SAMPLE_M" FOREIGN KEY("SAMPLE_METHOD_ID") REFERENCES DGGS_ANALYSIS.REF_SAMPLE_METHOD("SAMPLE_METHOD_ID");
ALTER TABLE DGGS_ANALYSIS.GMC_INVENTORY_RECORD ADD CONSTRAINT "FK_GMC_INVENTOR_R_REF_STORAGE_" FOREIGN KEY("STORAGE_ROOM_ID","STORAGE_LOCATION_ID") REFERENCES DGGS_PUBLICATIONS.REF_STORAGE_ROOM("STORAGE_ROOM_ID","STORAGE_LOCATION_ID");
ALTER TABLE DGGS_ANALYSIS.GMC_INVENTORY_RECORD ADD CONSTRAINT "FK_GMC_INVENTOR_R_REF_CONTAINE" FOREIGN KEY("CONTAINER_TYPE_ID") REFERENCES DGGS_ANALYSIS.REF_CONTAINER_TYPE("CONTAINER_TYPE_ID");
ALTER TABLE DGGS_ANALYSIS.GMC_INVENTORY_RECORD ADD CONSTRAINT "CK_GMC_INVENTORY_RECORD_SORTED" CHECK(SORTED in ('Y','N'));
ALTER TABLE DGGS_ANALYSIS.GMC_INVENTORY_RECORD ADD CONSTRAINT "CK_GMC_INVENTORY_RECORD_CORE_D" CHECK(CORE_DIAMETER in ('21.6/.85 (mm/in) - E','27.0/1.1 (mm/in) - A','36.5/1.4 (mm/in) - B','47.6/1.9 (mm/in) - N','63.5/2.5 (mm/in) - H','85.0/3.3 (mm/in) - P','Mixed Core Sizes','Other'));

INSERT INTO DGGS_PROJECT.DOMAIN_LIST (DOMAIN_NAME, VALID_VALUE_DEF, DOMAIN_CHAR) VALUES ('Core_Diameter', '21.6/.85 (mm/in) - E','E');
INSERT INTO DGGS_PROJECT.DOMAIN_LIST (DOMAIN_NAME, VALID_VALUE_DEF, DOMAIN_CHAR) VALUES ('Core_Diameter', '27.0/1.1 (mm/in) - A','A');
INSERT INTO DGGS_PROJECT.DOMAIN_LIST (DOMAIN_NAME, VALID_VALUE_DEF, DOMAIN_CHAR) VALUES ('Core_Diameter', '36.5/1.4 (mm/in) - B','B');
INSERT INTO DGGS_PROJECT.DOMAIN_LIST (DOMAIN_NAME, VALID_VALUE_DEF, DOMAIN_CHAR) VALUES ('Core_Diameter', '47.6/1.9 (mm/in) - N','N');
INSERT INTO DGGS_PROJECT.DOMAIN_LIST (DOMAIN_NAME, VALID_VALUE_DEF, DOMAIN_CHAR) VALUES ('Core_Diameter', '63.5/2.5 (mm/in) - H','H');
INSERT INTO DGGS_PROJECT.DOMAIN_LIST (DOMAIN_NAME, VALID_VALUE_DEF, DOMAIN_CHAR) VALUES ('Core_Diameter', '85.0/3.3 (mm/in) - P','P');
INSERT INTO DGGS_PROJECT.DOMAIN_LIST (DOMAIN_NAME, VALID_VALUE_DEF, DOMAIN_CHAR) VALUES ('Core_Diameter', 'Mixed Core Sizes','M');
INSERT INTO DGGS_PROJECT.DOMAIN_LIST (DOMAIN_NAME, VALID_VALUE_DEF, DOMAIN_CHAR) VALUES ('Core_Diameter', 'Other','O');


/*******************
*
* CREATE GMC_INV_RECORD_MATERIAL_XRC
*
********************/


CREATE TABLE DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC
("GMC_INVENTORY_RECORD_ID" INTEGER NOT NULL,
"SAMPLE_MATERIAL_ID" INTEGER NOT NULL
);

ALTER TABLE DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC ADD CONSTRAINT "PK_GMC_INV_RECORD_MATERIAL_XRC" PRIMARY KEY(GMC_INVENTORY_RECORD_ID, SAMPLE_MATERIAL_ID);
ALTER TABLE DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC ADD CONSTRAINT "FK_GMC_INV_RECO_R_GMC_INVENTOR" FOREIGN KEY("GMC_INVENTORY_RECORD_ID") REFERENCES DGGS_ANALYSIS.GMC_INVENTORY_RECORD("GMC_INVENTORY_RECORD_ID");
ALTER TABLE DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC ADD CONSTRAINT "FK_GMC_INV_RECO_R_REF_SAMPLE_M" FOREIGN KEY("SAMPLE_MATERIAL_ID") REFERENCES DGGS_ANALYSIS.REF_SAMPLE_MATERIAL("SAMPLE_MATERIAL_ID");

GRANT SELECT ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO DGGS_AIRBORNE_GEOPHYSICAL WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO DGGS_METADATA WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO DGGS_COAL_RESOURCES WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO DGGS_MINERAL_ACTIVITY WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO DGGS_PROJECT WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO DGGS_PUBLICATIONS WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO DGGS_SPATIAL_DESC WITH GRANT OPTION;

GRANT ALTER ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO ANALYSIS_DBA;
GRANT FLASHBACK ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO ANALYSIS_DBA;
GRANT DEBUG ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO ANALYSIS_DBA;
GRANT QUERY REWRITE ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO ANALYSIS_DBA;
GRANT ON COMMIT REFRESH ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO ANALYSIS_DBA;
GRANT UPDATE ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO ANALYSIS_DBA;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO ANALYSIS_DBA;
GRANT DELETE ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO ANALYSIS_DBA;
GRANT INSERT ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO ANALYSIS_DBA;
GRANT DELETE ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO ANALYSIS_EDIT;
GRANT INSERT ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO ANALYSIS_EDIT;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO ANALYSIS_EDIT;
GRANT UPDATE ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO ANALYSIS_EDIT;
GRANT SELECT ON DGGS_ANALYSIS.GMC_INV_RECORD_MATERIAL_XRC TO ANALYSIS_VIEW;


/*** GMC CONTAINER ****/
--container_id
--field_station_id
--data_source_id
--collection_id
--storage_room_id
--storage_location_id
ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER ADD ("RECEIVED_DATE" DATE);
--shelf
ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER ADD ("CONTAINER_TYPE_ID"  INTEGER);
ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER ADD ("SAMPLE_METHOD_ID" INTEGER);
ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER ADD ("CURATOR_REMARKS" VARCHAR2(2000));
UPDATE DGGS_ANALYSIS.GMC_CONTAINER SET CONTAINER_REMARKS = CONTAINER_REMARKS || '; ' || STORAGE_REMARKS WHERE STORAGE_REMARKS IS NOT NULL AND CONTAINER_REMARKS IS NOT NULL;
UPDATE DGGS_ANALYSIS.GMC_CONTAINER SET CONTAINER_REMARKS = STORAGE_REMARKS WHERE STORAGE_REMARKS IS NOT NULL AND CONTAINER_REMARKS IS NULL;
ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER DROP ("STORAGE_REMARKS");
ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER RENAME COLUMN "CONTAINER_REMARKS" TO "INVENTORY_REMARKS";
ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER ADD ("MATERIAL_MISSING" CHAR(1));
ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER ADD ("MATERIAL_IMPAIRED" CHAR(1));
ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER RENAME COLUMN "MATERIAL_IMPARIED" TO "MATERIAL_IMPAIRED";

ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER ADD ("CONTAINER_DAMAGED" CHAR(1));
ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER ADD ("DATA_MISSING" CHAR(1));
ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER ADD ("IMAGE" BLOB);

ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER ADD CONSTRAINT "FK_GMC_CONTAINE_R_REF_SAMPLE_M" FOREIGN KEY("SAMPLE_METHOD_ID") REFERENCES DGGS_ANALYSIS.REF_SAMPLE_METHOD("SAMPLE_METHOD_ID");
ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER ADD CONSTRAINT "FK_GMC_CONTAINE_R_REF_CONTAINE" FOREIGN KEY("CONTAINER_TYPE_ID") REFERENCES DGGS_ANALYSIS.REF_CONTAINER_TYPE("CONTAINER_TYPE_ID");
ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER ADD CONSTRAINT "CK_GMC_CONTAINER_MATERIAL_MISS" CHECK(MATERIAL_MISSING in ('Y','N'));
ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER ADD CONSTRAINT "CK_GMC_CONTAINER_MATERIAL_IMPA" CHECK(MATERIAL_IMPARIED in ('Y','N'));
ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER ADD CONSTRAINT "CK_GMC_CONTAINER_CONTAINER_DAM" CHECK(CONTAINER_DAMAGED in ('Y','N'));
ALTER TABLE DGGS_ANALYSIS.GMC_CONTAINER ADD CONSTRAINT "CK_GMC_CONTAINER_DATA_MISSING" CHECK(DATA_MISSING in ('Y','N'));

/**** CREATE SUBTYPE TABLE???? ***/
/* This may be adding unnecessary complexity, while its a good design idea, it may just end up confusing other users */


CREATE TABLE DGGS_ANALYSIS.CORE_BOX
("GMC_CONTAINER_ID" INTEGER NOT NULL,
"CORE_ORDER_NUMBER" INTEGER,
"CORE_DIAMETER" VARCHAR2(20),
"CORE_SKELETON" CHAR(1),
"CORE_COMPOSITE" CHAR(1),
"LAST_USER" VARCHAR2(30),
"LAST_MODIFIED" DATE
);

ALTER TABLE DGGS_ANALYSIS.CORE_BOX ADD CONSTRAINT "PK_CORE_BOX" PRIMARY KEY("GMC_CONTAINER_ID");
ALTER TABLE DGGS_ANALYSIS.CORE_BOX ADD CONSTRAINT "FK_CORE_BOX_R_GMC_CONTAINE" FOREIGN KEY("GMC_CONTAINER_ID") REFERENCES DGGS_ANALYSIS.GMC_CONTAINER("GMC_CONTAINER_ID");


CREATE OR REPLACE TRIGGER "DGGS_ANALYSIS"."CORE_BOX_IUBTR2" BEFORE
INSERT
OR UPDATE ON "DGGS_ANALYSIS"."CORE_BOX" FOR EACH ROW

begin
SELECT USER
INTO :new.last_user
FROM DUAL;

SELECT SYSDATE
INTO :new.last_modified
FROM DUAL;

end;
/

GRANT SELECT ON DGGS_ANALYSIS.CORE_BOX TO DGGS_AIRBORNE_GEOPHYSICAL WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.CORE_BOX TO DGGS_METADATA WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.CORE_BOX TO DGGS_COAL_RESOURCES WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.CORE_BOX TO DGGS_MINERAL_ACTIVITY WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.CORE_BOX TO DGGS_PROJECT WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.CORE_BOX TO DGGS_PUBLICATIONS WITH GRANT OPTION;
GRANT SELECT ON DGGS_ANALYSIS.CORE_BOX TO DGGS_SPATIAL_DESC WITH GRANT OPTION;

GRANT ALTER ON DGGS_ANALYSIS.CORE_BOX TO ANALYSIS_DBA;
GRANT FLASHBACK ON DGGS_ANALYSIS.CORE_BOX TO ANALYSIS_DBA;
GRANT DEBUG ON DGGS_ANALYSIS.CORE_BOX TO ANALYSIS_DBA;
GRANT QUERY REWRITE ON DGGS_ANALYSIS.CORE_BOX TO ANALYSIS_DBA;
GRANT ON COMMIT REFRESH ON DGGS_ANALYSIS.CORE_BOX TO ANALYSIS_DBA;
GRANT UPDATE ON DGGS_ANALYSIS.CORE_BOX TO ANALYSIS_DBA;
GRANT SELECT ON DGGS_ANALYSIS.CORE_BOX TO ANALYSIS_DBA;
GRANT DELETE ON DGGS_ANALYSIS.CORE_BOX TO ANALYSIS_DBA;
GRANT INSERT ON DGGS_ANALYSIS.CORE_BOX TO ANALYSIS_DBA;
GRANT DELETE ON DGGS_ANALYSIS.CORE_BOX TO ANALYSIS_EDIT;
GRANT INSERT ON DGGS_ANALYSIS.CORE_BOX TO ANALYSIS_EDIT;
GRANT SELECT ON DGGS_ANALYSIS.CORE_BOX TO ANALYSIS_EDIT;
GRANT UPDATE ON DGGS_ANALYSIS.CORE_BOX TO ANALYSIS_EDIT;
GRANT SELECT ON DGGS_ANALYSIS.CORE_BOX TO ANALYSIS_VIEW;

set linesize 280
set heading off
set pagesize 0
--generate the alter trigger compile statements for invalid triggers
SPOOL recompile_invalid_trigger.sql
Select
	'alter trigger '
	||trig.OWNER
	||'.'
	||trig.TRIGGER_NAME
	||' compile;'
from
	DBA_TRIGGERS trig
	, DBA_OBJECTS obj
where
	(trig.owner like 'DGGS_%'
	and not trig.owner like '$SDE_$')
	AND trig.trigger_name = obj.object_name
	AND trig.owner = obj.owner
	AND obj.object_type = 'TRIGGER'
	and not obj.status = 'VALID';
Spool off
--run the generated alter table statements
SPOOL invalid_trigger_results.txt
@recompile_invalid_trigger.sql

Spool off

set linesize 280
set heading off
set pagesize 0
--generate the alter trigger compile statements for invalid views
SPOOL recompile_invalid_views.sql
Select
	'alter view '
	||v.OWNER
	||'.'
	||v.VIEW_NAME
	||' compile;'
from
	DBA_VIEWS v
	, DBA_OBJECTS obj
where
	(v.owner like 'DGGS_%'
	and not v.owner like '$SDE_$')
	AND v.view_name = obj.object_name
	AND v.owner = obj.owner
	AND obj.object_type = 'VIEW'
	and not obj.status = 'VALID'
;
Spool off
--run the generated alter table statements
SPOOL invalid_view_results.txt
@recompile_invalid_views.sql
Spool off