Well_Summary_DOV

CREATE OR REPLACE FORCE VIEW Well_Summary_DOV(ID, UWI, WELL_NAME, SHORT_NAME, PRIMARY_SOURCE, SHAPE, TOP_X, TOP_Y, ORIGINAL_COORD_SYSTEM_ID, ORIGINAL_COORD_SYSTEM_NAME, ORIGINAL_CRS, ORIGINAL_TOP_X, ORIGINAL_TOP_Y, CLASS, OPERATOR, PERMANENT_ELEVATION, PERMANENT_ELEVATION_REF, PROJECT_ELEVATION_REF, CURRENT_STATUS, SPUD_DATE, COUNTRY, FIELD, REMARKS, INSERT_DATE, INSERT_USER, FIELD_ID, WELL_BA_INVOLVEMENT_ID, COMPANY_ID, WELL_ALIAS_ID, PERMANENT_COORD_SYSTEM_ID, POSITION_ID, GEOPOLITICAL_POSITION_ID, STORAGE_COORDINATE_SYSTEM_ID, PROJECT_DEFAULT_ID) AS select
w.id                         Id,
w.uwi                        UWI,
w.name                       Well_Name,
wa.alias_name                Short_Name,
w.source                     Primary_Source,
p.shape                      shape,
decode(sds_spatial.sde_enabled(),1,nvl(p.normal_longitude,SDE.ST_X(p.shape)),p.normal_longitude) Top_X,
decode(sds_spatial.sde_enabled(),1,nvl(p.normal_latitude,SDE.ST_Y(p.shape)),p.normal_latitude) 
Top_Y,
p.original_coord_system_id   Original_Coord_System_Id,
cs3.name                     Original_Coord_System_Name,
cs3.crs                      Original_CRS,
p.original_x_longitude       Original_Top_X,
p.original_y_latitude        Original_Top_Y,
w.lahee_class                Class,
c.name                       Operator,
cs1.z_offset                 Permanent_Elevation,
cs1.vertical_reference       Permanent_Elevation_Ref,
cs2.vertical_reference       Project_Elevation_Ref,
w.current_status             Current_Status,
w.spud_date                  Spud_Date,
gp.country_name              Country,
f.name                       Field,
w.remarks                    Remarks,
w.insert_date                Insert_Date,
w.insert_user                Insert_User,
f.id                         Field_Id,
wbi.id                       Well_BA_Involvement_Id,
c.id                         Company_Id,
wa.id                        Well_Alias_Id,
cs1.id                       
Permanent_Coord_System_Id,
p.id                         Position_Id,
gp.position_id               Geopolitical_Position_Id,
cs2.id                       Storage_Coordinate_System_Id,
pd.id                        Project_Default_Id
from  well w,
      field f,
      well_ba_involvement wbi,
      company c,
      well_alias wa,
      coordinate_system cs1,
      position p,
      geopolitical_position gp,
      coordinate_system cs2,
      coordinate_system cs3,
      project_default pd
where w.field_id  = f.id(+)
  and w.id = wbi.well_id (+)
  and 'Operator' = wbi.involvement_role (+)
  and wbi.business_associate_id = c.id (+)
  and w.id = wa.well_id (+)
  and 'Short Name' = wa.alias_type (+)
  and w.permanent_coord_system_id = cs1.id(+)
  and w.surface_location_id = p.id (+)
  and p.id = gp.position_id (+)
  and p.original_coord_system_id=cs3.id(+)
  and pd.storage_coord_sys_id = cs2.id
  and w.version = '1'
  and w.existence_kind = 'Actual'
  and wbi.end_date(+) is null