EXP_GAE_ZONE_DOV

CREATE OR REPLACE FORCE VIEW exp_gae_zone_dov(UWI, ID, UBHI, BOREHOLE_NAME, ZONE_NAME, INFORMAL_ZONE_NAME, TOP_DEPTH, BASE_DEPTH, INSERT_DATE, INSERT_USER, ADJUSTED_THICKNESS, CORED_FLAG, TESTED_FLAG, BOREHOLE_ID, BOREHOLE_POINT_TOP_ID, BOREHOLE_POINT_BASE_ID, GEOLOGIC_LAYER_ID, STRAT_SCOPE, LAYER_TYPE, STRATNAME, ORIGINAL_SOURCE, SOURCE, FIELD_NAME, GROSS_SAND, NET_SAND, INTERPRETER_RANK, ZONE_RANK, PARENT_ZONE_ID, BOREHOLE_ZONE_TYPE, WELL_STATUS) AS SELECT
  w.uwi                          uwi,
  bhz.Id                         Id,
  bh.UBHI                        UBHI,
  bh.Name                        Borehole_Name,
  strat.Standard_Layer_Name      Zone_Name,
  bhz.Name                       Informal_Zone_Name,
  decode(bhz.zone_penetration, 'I', decode(bhp_base.Strat_Exception,'', bhp_base.Md, NULL),decode(bhp_top.Strat_Exception, '', bhp_top.Md, NULL)) Top_Depth,
  decode(bhz.zone_penetration, 'I', decode(bhp_top.Strat_Exception, '', bhp_top.Md, NULL), 
decode(bhp_base.Strat_Exception, '', bhp_base.Md, NULL)) Base_Depth,
  bhz.Insert_Date                Insert_Date,
  bhz.Insert_User                Insert_User,
  bhz.Adjusted_Thickness         Adjusted_Thickness,
  bhz.Cored_Flag                 Cored_Flag,
  bhz.Tested_Flag                Tested_Flag,
  bh.Id                          Borehole_Id,
  bhp_top.Id                     Borehole_Point_Top_Id,
  bhp_base.Id                    Borehole_Point_Base_Id,
  strat.Id                       Geologic_Layer_Id,
  strat.strat_scope              Strat_Scope,
  strat.strat_layer_unit         Layer_Type,
  strat.name                     stratname,
  strat.original_source          original_source,
  strat.source                   Source,
  f.name                         Field_Name,
  decode(bhp_top.Strat_Exception, '', (decode(bhp_base.Strat_Exception, '', r.gross_sand, NULL)), NULL) Gross_Sand,
  r.net_sand                     Net_Sand,
  col.RANK interpreter_rank,
  RANK () OVER 
(PARTITION BY bhp_top.borehole_id, strat.standard_layer_name ORDER BY col.RANK) ZONE_RANK,
  bhz.Parent_Zone_Id             Parent_Zone_Id,
  bhz.Borehole_Zone_Type         Borehole_Zone_Type,
  bh.Current_Status              Well_Status
   FROM
  borehole_zone bhz
  LEFT JOIN person p ON p.NAME = bhz.SOURCE AND NVL (p.SOURCE, ' ') = NVL (bhz.original_source, ' ')
  LEFT 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 JOIN rock_pty_set r ON bhz.id =r.rock_pty_zone_id
  LEFT JOIN stratigraphic_layer strat ON bhz.geologic_layer_id = strat.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 bh ON bhp_top.borehole_id =   bh.id
  JOIN borehole_point bhp_base ON bhz.zone_base_id = bhp_base.id
  JOIN borehole bh ON bhp_base.borehole_id = bh.id
  JOIN well w ON w.id = bh.well_id
  JOIN field f ON f.id = w.field_id