| Name | Zone_Rank_DOV |
|---|---|
| Entity Type | ObjectView |
| Description | Markers ranked based on interpreter preference |
| Submodel | Data_Object |
| Entity Scope | Project |
| Primary Key | Id |
| Natural Key | |
| Generalizations | |
| Specializations |
| Name | Value Domain | Property / Storage Unit | Description | Required / Default | Type |
|---|---|---|---|---|---|
| Base_Depth | NUMBER | Depth m |
_ | No |
NUMBER |
| Borehole_Id | ID | No_Unit |
_ | No |
NUMBER(38) |
| Borehole_Name | STRING255 | NA |
_ | No |
VARCHAR2(255) |
| Borehole_Point_Base_Id | ID | No_Unit |
_ | No |
NUMBER(38) |
| Borehole_Point_Top_Id | ID | No_Unit |
_ | No |
NUMBER(38) |
| Geologic_Layer_Id | ID | No_Unit |
_ | No |
NUMBER(38) |
| Id | ID | No_Unit |
_ | Yes |
NUMBER(38) |
| Informal_Zone_Name | STRING255 | NA |
_ | No |
VARCHAR2(255) |
| Insert_Date | DATE | NA |
_ | No |
DATE |
| Insert_User | STRING255 | NA |
_ | No |
VARCHAR2(255) |
| Interpreter | STRING255 | NA |
_ | No |
VARCHAR2(255) |
| Interpreter_Rank | int | NA |
_ | No |
NUMBER(10) |
| Original_Source | STRING255 | NA |
_ | No |
VARCHAR2(255) |
| Remarks | STRING2000 | NA |
_ | No |
VARCHAR2(2000) |
| Top_Depth | NUMBER | Depth m |
_ | Yes |
NUMBER |
| UBHI | WELL_IDENTIFIER | NA |
_ | Yes |
VARCHAR2(64) |
| Well_Id | ID | No_Unit |
_ | No |
NUMBER(38) |
| Well_Marker_Surface_Id_Base | ID | No_Unit |
_ | No |
NUMBER(38) |
| Well_Marker_Surface_Id_Top | ID | No_Unit |
_ | No |
NUMBER(38) |
| Zone_Name | STRING255 | NA |
_ | Yes |
VARCHAR2(255) |
| Zone_Rank | int | NA |
_ | No |
NUMBER(10) |
CREATE OR REPLACE FORCE VIEW Zone_Rank_DOV(ID, UBHI, BOREHOLE_NAME, ZONE_NAME, INFORMAL_ZONE_NAME, INTERPRETER, ORIGINAL_SOURCE, TOP_DEPTH, BASE_DEPTH, INTERPRETER_RANK, ZONE_RANK, REMARKS, INSERT_DATE, INSERT_USER, BOREHOLE_ID, WELL_ID, BOREHOLE_POINT_TOP_ID, WELL_MARKER_SURFACE_ID_TOP, BOREHOLE_POINT_BASE_ID, WELL_MARKER_SURFACE_ID_BASE, GEOLOGIC_LAYER_ID) AS SELECT BHZ.ID ID,
BH.UBHI UBHI,
BH.NAME BOREHOLE_NAME,
STRAT.STANDARD_LAYER_NAME ZONE_NAME,
BHZ.NAME INFORMAL_ZONE_NAME,
BHZ.SOURCE INTERPRETER,
BHZ.ORIGINAL_SOURCE ORIGINAL_SOURCE,
BHP_TOP.MD TOP_DEPTH,
BHP_BASE.MD BASE_DEPTH,
COL.RANK INTERPRETER_RANK,
RANK ()
OVER (PARTITION BY BHP_TOP.BOREHOLE_ID, BHZ.GEOLOGIC_LAYER_ID
ORDER BY COL.RANK)
ZONE_RANK,
BHZ.REMARKS REMARKS,
BHZ.INSERT_DATE INSERT_DATE,
BHZ.INSERT_USER INSERT_USER,
BH.ID BOREHOLE_ID,
BH.WELL_ID WELL_ID,
BHP_TOP.ID BOREHOLE_POINT_TOP_ID,
BHP_TOP.WELL_MARKER_SURFACE_ID WELL_MARKER_SURFACE_ID_TOP,
BHP_BASE.ID BOREHOLE_POINT_BASE_ID,
BHP_BASE.WELL_MARKER_SURFACE_ID WELL_MARKER_SURFACE_ID_BASE,
STRAT.ID GEOLOGIC_LAYER_ID
FROM BOREHOLE_ZONE BHZ
LEFT OUTER JOIN PERSON P
ON P.NAME = BHZ.SOURCE
AND NVL (P.SOURCE, ' ') = NVL (BHZ.ORIGINAL_SOURCE, ' ')
LEFT OUTER JOIN
(SELECT C.ID, CR.PROPERTY_ID, CR.RANK
FROM COLLECTION_REF CR, COLLECTION C
WHERE CR.ENTITY_ID = C.ID
AND C.COLLECTION_TYPE = 'Well_Top'
AND ( UPPER (C.SOURCE) = USER
OR ( USER NOT IN (SELECT UPPER (
NVL (C.SOURCE, ' '))
FROM COLLECTION_REF CR,
COLLECTION C
WHERE CR.ENTITY_ID = C.ID
AND C.COLLECTION_TYPE =
'Well_Top')
AND C.SOURCE IS NULL))) COL
ON COL.PROPERTY_ID = P.ID
LEFT OUTER JOIN ROCK_PTY_SET ROPS ON BHZ.ID = ROPS.ROCK_PTY_ZONE_ID
JOIN STRATIGRAPHIC_LAYER STRAT ON BHZ.GEOLOGIC_LAYER_ID = STRAT.ID
JOIN BOREHOLE_POINT BHP_TOP ON BHZ.ZONE_TOP_ID = BHP_TOP.ID
JOIN BOREHOLE_POINT BHP_BASE ON BHZ.ZONE_BASE_ID = BHP_BASE.ID
LEFT OUTER JOIN BOREHOLE BH
ON BHP_TOP.BOREHOLE_ID = BH.ID AND BH.ID = BHP_BASE.BOREHOLE_ID
|