Marker_Rank_DOV

CREATE OR REPLACE FORCE VIEW Marker_Rank_DOV(ID, UBHI, BOREHOLE_NAME, SURFACE_NAME, INFORMAL_SURFACE_NAME, INTERPRETER, ORIGINAL_SOURCE, PICK_DEPTH, APPLICATION_NAME, WELL_MARKER_SURFACE_ID, MARKER_XYZ_POSITION_ID, UPDATE_DATE, CONFORMITY_RELATIONSHIP, DIP_AZIMUTH, DIP, REMARKS, INTERPRETER_RANK, MARKER_RANK, INSERT_DATE, INSERT_USER, BOREHOLE_ID, WELL_ID, STRATIGRAPHIC_SURFACE_ID) AS SELECT
          bp.Id Id,
          bh.UBHI UBHI,
          bh.Name Borehole_Name,
          ss.standard_geologic_surface Surface_Name,
          ss.Name Informal_Surface_Name,
          bp.source Interpreter,
          bp.original_source Original_Source,
          bp.MD Pick_Depth,
          bp.produced_by Application_Name,
          bp.well_marker_surface_id Well_Marker_Surface_Id,
          bp.marker_xyz_position_id Marker_XYZ_Position_Id,
          bp.Update_date Update_Date,
          ss.strat_surface_type Conformity_Relationship,
          bp.dip_azimuth Dip_Azimuth,
          bp.dip_angle Dip,
   
       bp.remarks Remarks,
          col.rank interpreter_rank,
          rank() OVER (partition by bp.borehole_id, bp.well_marker_surface_id order by col.rank) marker_rank,
          bp.insert_date Insert_Date,
          bp.insert_user Insert_User,
          bh.Id Borehole_Id,
          bh.well_Id Well_Id,
          ss.Id Stratigraphic_Surface_Id
          from      borehole bh,
                       borehole_point bp
                       LEFT OUTER JOIN person p  ON  p.name=bp.source AND NVL(p.source,' ')=NVL(bp.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,
                       
stratigraphic_surface ss
          where  bh.id=bp.borehole_id
            AND bp.well_marker_surface_id = ss.id