EXP_GAE_PROS_MID_DOV

CREATE OR REPLACE FORCE VIEW EXP_GAE_PROS_MID_DOV(ID, MID_DEPTH) AS SELECT w.id Id, (MIN (bhp_top.MD) + MAX (bhp_base.MD)) / 2 MID_DEPTH
    FROM Borehole_Zone z,
         borehole_point bhp_top,
         borehole_point bhp_base,
         Borehole bh,
         well w,
         Well_Completion wc,
         Reservoir r,
         Stratigraphic_Layer sl,
         Well_Completion_Reservoir wcr,
         Facility_Connection fc,
         (SELECT gafi.Id,
                 ap.Id Generic_Borehole_Act_Id,
                 gafi.Facility_Id,
                 gafi.Activity_Id,
                 ap.Internal_Reference,
                 ap.Activity_Class,
                 ap.activity_type,
                 ap.Start_Date,
                 ap.End_Date
            FROM Generic_Act_Fac_Invl gafi,
                 (SELECT ID,
                         internal_reference,
                         Start_Date,
                         End_Date,
                         activity_type,
                         
Activity_Class,
                         EXISTENCE_KIND
                    FROM generic_borehole_activity
                  UNION
                  SELECT ID,
                         internal_reference,
                         Start_Date,
                         End_Date,
                         activity_program_type activity_type,
                         Activity_Class,
                         EXISTENCE_KIND
                    FROM activity_program
                  UNION
                  SELECT ID,
                         TO_CHAR (water_depth) internal_reference,
                         Start_Date,
                         End_Date,
                         activity_type,
                         Activity_Class,
                         EXISTENCE_KIND
                    FROM rig_planning) ap
           WHERE     gafi.Activity_Id = ap.Id
                 AND gafi.Activity_Role = 'STRING_ZONE'
                 AND NVL (ap.EXISTENCE_KIND, '~') <> 'DERIVED') ga,
         
Tubing_String ts
   WHERE     w.id = bh.well_id
         AND bh.id = bhp_top.borehole_id
         AND bhp_top.id = z.zone_top_id
         AND bh.id = bhp_base.borehole_id
         AND bhp_base.id = z.zone_base_id
         AND bh.ID = wc.Borehole_Id
         AND ts.ID = fc.To_Facility_Id
         AND fc.From_Facility_Id = wc.ID
         AND fc.Connection_Type = 'STRING_ZONE'
         AND wc.Well_Completion_Type = 'STRING_ZONE'
         AND wcr.Well_Completion_Id = wc.ID
         AND wcr.Reservoir_Id = r.ID
         AND sl.ID = r.Geologic_Layer_Id
         AND z.geologic_layer_id = sl.id
         AND wc.ID = ga.Facility_Id(+)
   GROUP BY w.id