EXP_GAE_SCH_EVENT_DOV

CREATE OR REPLACE FORCE VIEW EXP_GAE_SCH_EVENT_DOV(ID, WELL_ID, UBHI, BOREHOLE_ID, TUBING_STRING_ID, STRING_TYPE, ACTIVITY_TYPE, START_DATE, BASE_MD, TOP_MD, PERMEABILITY_THICKNESS, SKIN, INNER_DIAMETER, OUTER_DIAMETER, EVENT_TYPE, ALIAS_STRING_TYPE) AS (SELECT wt.id,
           b.well_id,
           b.ubhi UBHI,
           b.id Borehole_id,
           ts.id Tubing_string_id,
           b.ubhi || ':' || NVL (TS.STRING_TYPE, 0) string_type,
           'welltest' ACTIVITY_TYPE,
           WT.START_DATE START_DATE,
           NULL BASE_MD,
           NULL TOP_MD,
           WCI.PERMEABILITY_THICKNESS PERMEABILITY_THICKNESS,
           NULL SKIN,
           NULL INNER_DIAMETER,
           NULL OUTER_DIAMETER,
           'Well tests' EVENT_TYPE,
           BA.ALIAS_NAME || ':' || NVL (TS.STRING_TYPE, 0) alias_string_type
      FROM WELL_TEST WT,
           WELL_COMPLETION WC,
           WELL_COMPLETION WC1,
           Facility_Composition fc,
           TUBING_STRING TS,
           
WELL_TEST_INTERPRETATION WCI,
           BOREHOLE B,
           BOREHOLE B1,
           Borehole_Alias ba
     WHERE WC.ID = WT.WELL_COMPLETION_ID(+)
           AND WT.WELL_TEST_TYPE NOT IN
                  ('FT',
                   'COMM-INJ',
                   'COMM',
                   'COMM-PROD',
                   'RFT',
                   'FIT',
                   'SFT',
                   'MDT')
           AND EXISTS
                  (SELECT code
                     FROM R_ACTIVITY_CATEGORY
                    WHERE SUPER_TYPE = 'TEST_OPERATION'
                          AND CODE = WT.WELL_TEST_TYPE)
           AND WCI.WELL_TEST_ID(+) = WT.ID
           AND (NVL (WCI.SKIN_EFFECT, WCI.TRUE_SKIN_FACTOR) != 0
                AND NVL (WCI.SKIN_EFFECT, WCI.TRUE_SKIN_FACTOR) IS NOT NULL)
           AND TS.ID(+) = WC.TUBING_STRING_ID
           AND WCI.UTILIZED_FACILITY_ID IS NOT NULL
           AND WC.BOREHOLE_ID = B.ID
           AND B1.WELL_ID = B.WELL_ID
           AND 
FC.WHOLE_FACILITY_ID(+) = WC.ID
           AND FC.PART_FACILITY_ID = WC1.ID
           AND WC1.BOREHOLE_ID = B1.ID
           AND BA.BOREHOLE_ID(+) = B.ID
           AND BA.ALIAS_TYPE(+) = 'SIMULATION'
    UNION
      SELECT TO_NUMBER (wt.id || 1),
             b1.well_id,
             b1.ubhi UBHI,
             b1.ID Borehole_id,
             ts.ID Tubing_string_id,
             b1.ubhi || ':' || NVL (TS.STRING_TYPE, 0) string_type,
             'acidise' ACTIVITY_TYPE,
             WT.START_DATE START_DATE,
             (MIN (BP_TOP.MD) - NVL (AE.ELEVATION, 0)) TOP_MD,
             (MAX (bp_base.MD) - NVL (AE.ELEVATION, 0)) BASE_MD,
             WCI.PERMEABILITY_THICKNESS PERMEABILITY_THICKNESS,
             NVL (WCI.SKIN_EFFECT, WCI.TRUE_SKIN_FACTOR) SKIN,
             NULL INNER_DIAMETER,
             NULL OUTER_DIAMETER,
             'Treatment operations' EVENT_TYPE,
             BA.ALIAS_NAME || ':' || NVL (TS.STRING_TYPE, 0) alias_string_type
        FROM WELL_TEST WT,
        
     WELL_COMPLETION WC,
             WELL_COMPLETION WC1,
             TUBING_STRING TS,
             WELL_TEST_INTERPRETATION WCI,
             BOREHOLE B,
             BOREHOLE B1,
             WELL W,
             Facility_Composition fc,
             Well_Completion_Reservoir wcr,
             Reservoir r,
             Borehole_Zone bz,
             Alternate_Elevation ae,
             Borehole_Point bp_top,
             Borehole_Point bp_base,
             stratigraphic_layer sl,
             reservoir_alias ra,
             Borehole_Alias ba
       WHERE WC.ID = WT.WELL_COMPLETION_ID(+)
             AND WT.WELL_TEST_TYPE NOT IN
                    ('FT',
                     'COMM-INJ',
                     'COMM',
                     'COMM-PROD',
                     'RFT',
                     'FIT',
                     'SFT',
                     'MDT')
             AND EXISTS
                    (SELECT code
                       FROM R_ACTIVITY_CATEGORY
                 
     WHERE SUPER_TYPE = 'TEST_OPERATION'
                            AND CODE = WT.WELL_TEST_TYPE)
             AND WCI.WELL_TEST_ID = WT.ID
             AND (NVL (WCI.SKIN_EFFECT, WCI.TRUE_SKIN_FACTOR) != 0
                  AND NVL (WCI.SKIN_EFFECT, WCI.TRUE_SKIN_FACTOR) IS NOT NULL)
             AND TS.ID(+) = WC.TUBING_STRING_ID
             AND WCI.UTILIZED_FACILITY_ID IS NOT NULL
             AND FC.WHOLE_FACILITY_ID = WC.ID
             AND FC.PART_FACILITY_ID = WC1.ID
             AND WC1.BOREHOLE_ID = B.ID
             AND WCR.WELL_COMPLETION_ID = WC1.ID
             AND R.ID(+) = WCR.RESERVOIR_ID
             AND bz.geologic_layer_id = sl.ID
             AND sl.standard_layer_name = ra.alias_name
             AND r.ID = ra.reservoir_id
             AND ra.alias_type = 'Reservoir_Name'
             AND BZ.ZONE_TOP_ID = BP_TOP.ID
             AND BZ.ZONE_BASE_ID = BP_BASE.ID
             AND B.Working_Coord_System_Id = AE.COORDINATE_SYSTEM_ID(+)
             AND 
AE.VERTICAL_REFERENCE(+) = 'KB'
             AND BP_TOP.BOREHOLE_ID = B.ID
             AND BP_BASE.BOREHOLE_ID = B.ID
             AND B1.WELL_ID = B.WELL_ID
             AND B1.WELL_ID = W.ID
             AND B1.UBHI = W.UWI
             AND BA.BOREHOLE_ID(+) = B1.ID
             AND BA.ALIAS_TYPE(+) = 'SIMULATION'
    GROUP BY WT.ID,
             WCI.ID,
             AE.ELEVATION,
             b1.well_id,
             b1.ubhi,
             b1.ID,
             ts.ID,
             TS.STRING_TYPE,
             WT.START_DATE,
             WCI.PERMEABILITY_THICKNESS,
             WCI.SKIN_EFFECT,
             WCI.TRUE_SKIN_FACTOR,
             BA.ALIAS_NAME
    UNION
    SELECT pis.id,
           b.well_id,
           b.ubhi UBHI,
           b.id Borehole_id,
           NULL Tubing_string_id,
           b.ubhi string_type,
           'perforation' Activity_Type,
           wcp.start_date START_DATE,
           pi.base_interval_md - NVL (AE.ELEVATION, 0) BASE_MD,
           
pi.top_interval_md - NVL (AE.ELEVATION, 0) TOP_MD,
           NULL PERMEABILITY_THICKNESS,
           0 SKIN,
           NVL (pi.INNER_DIAMETER, 0) INNER_DIAMETER,
           NVL (pi.Maximum_Diameter, 0) OUTER_DIAMETER,
           'Installation operations (Perforations)' EVENT_TYPE,
           BA.ALIAS_NAME alias_string_type
      FROM well_completion_perf wcp,
           Perforated_Interval pi,
           Perf_Interval_Status pis,
           Generic_Borehole_Activity gba,
           Generic_Act_Fac_Invl gafi,
           Well_Completion wc,
           Borehole b,
           WELL W,
           Alternate_Elevation ae,
           Borehole_Alias ba
     WHERE     PI.BOREHOLE_ID = b.id
           AND wcp.Perforated_Interval_Id = pi.id
           AND pis.Perforated_Interval_Id = pi.id
           AND GBA.BOREHOLE_ID = B.ID
           AND GBA.ACTIVITY_TYPE IN
                  (SELECT code
                     FROM R_ACTIVITY_CATEGORY
                    WHERE SUPER_TYPE = 'MAJOR_ACTIVITY'
   
                       AND code = GBA.ACTIVITY_TYPE)
           AND GAFI.ACTIVITY_ID(+) = GBA.ID
           AND GAFI.FACILITY_ID = WC.ID(+)
           AND WCP.WELL_COMPLETION_ID = WC.ID
           AND B.WELL_ID = W.ID
           AND B.Working_Coord_System_Id = AE.COORDINATE_SYSTEM_ID(+)
           AND AE.VERTICAL_REFERENCE(+) = 'KB'
           AND BA.BOREHOLE_ID(+) = B.ID
           AND BA.ALIAS_TYPE(+) = 'SIMULATION'
    UNION
    SELECT pis.id,
           b.well_id,
           b.ubhi UBHI,
           b.id Borehole_id,
           NULL Tubing_string_id,
           b.ubhi string_type,
           'perforation' Activity_Type,
           wcp.start_date START_DATE,
           pi.base_interval_md - NVL (AE.ELEVATION, 0) BASE_MD,
           pi.top_interval_md - NVL (AE.ELEVATION, 0) TOP_MD,
           NULL PERMEABILITY_THICKNESS,
           0 SKIN,
           NVL (pi.INNER_DIAMETER, 0) INNER_DIAMETER,
           NVL (pi.Maximum_Diameter, 0) OUTER_DIAMETER,
           'Installation operations 
(Perforations)' EVENT_TYPE,
           BA.ALIAS_NAME alias_string_type
      FROM well_completion_perf wcp,
           Perforated_Interval pi,
           Perf_Interval_Status pis,
           Activity_Program gba,
           Generic_Act_Fac_Invl gafi,
           Well_Completion wc,
           Borehole b,
           WELL W,
           Alternate_Elevation ae,
           Borehole_Alias ba
     WHERE     PI.BOREHOLE_ID = b.id
           AND wcp.Perforated_Interval_Id = pi.id
           AND pis.Perforated_Interval_Id = pi.id
           AND GBA.WELL_ID = B.WELL_ID
           AND GAFI.ACTIVITY_ID(+) = GBA.ID
           AND GAFI.FACILITY_ID = WC.ID(+)
           AND WCP.WELL_COMPLETION_ID = WC.ID
           AND B.WELL_ID = W.ID
           AND B.Working_Coord_System_Id = AE.COORDINATE_SYSTEM_ID(+)
           AND AE.VERTICAL_REFERENCE(+) = 'KB'
           AND BA.BOREHOLE_ID(+) = B.ID
           AND BA.ALIAS_TYPE(+) = 'SIMULATION'
    UNION
    SELECT TO_NUMBER (pis.id || 1),
           
b.well_id,
           b.ubhi UBHI,
           b.id Borehole_id,
           NULL Tubing_string_id,
           b.ubhi string_type,
           'squeeze' Activity_Type,
           pis.start_date START_DATE,
           pi.base_interval_md - NVL (AE.ELEVATION, 0) BASE_MD,
           pi.top_interval_md - NVL (AE.ELEVATION, 0) TOP_MD,
           NULL PERMEABILITY_THICKNESS,
           NULL SKIN,
           NVL (pi.INNER_DIAMETER, 0) INNER_DIAMETER,
           NVL (pi.Maximum_Diameter, 0) OUTER_DIAMETER,
           'Installation operations (Perforations)' EVENT_TYPE,
           BA.ALIAS_NAME alias_string_type
      FROM well_completion_perf wcp,
           Perforated_Interval pi,
           Perf_Interval_Status pis,
           Generic_Borehole_Activity gba,
           Generic_Act_Fac_Invl gafi,
           Well_Completion wc,
           Borehole b,
           WELL W,
           Alternate_Elevation ae,
           Borehole_Alias ba
     WHERE     PI.BOREHOLE_ID = b.id
           AND 
wcp.Perforated_Interval_Id = pi.id
           AND pis.Perforated_Interval_Id = pi.id
           AND GBA.BOREHOLE_ID = B.ID
           AND GBA.ACTIVITY_TYPE IN
                  (SELECT code
                     FROM R_ACTIVITY_CATEGORY
                    WHERE SUPER_TYPE = 'MAJOR_ACTIVITY'
                          AND code = GBA.ACTIVITY_TYPE)
           AND GAFI.ACTIVITY_ID(+) = GBA.ID
           AND GAFI.FACILITY_ID = WC.ID(+)
           AND WCP.WELL_COMPLETION_ID = WC.ID
           AND PIS.STATUS IN ('CLOSED', 'C', 'SQUEEZED')
           AND B.WELL_ID = W.ID
           AND B.Working_Coord_System_Id = AE.COORDINATE_SYSTEM_ID(+)
           AND AE.VERTICAL_REFERENCE(+) = 'KB'
           AND BA.BOREHOLE_ID(+) = B.ID
           AND BA.ALIAS_TYPE(+) = 'SIMULATION'
    UNION
    SELECT TO_NUMBER (pis.id || 1),
           b.well_id,
           b.ubhi UBHI,
           b.id Borehole_id,
           NULL Tubing_string_id,
           b.ubhi string_type,
           'squeeze' Activity_Type,
    
       pis.start_date START_DATE,
           pi.base_interval_md - NVL (AE.ELEVATION, 0) BASE_MD,
           pi.top_interval_md - NVL (AE.ELEVATION, 0) TOP_MD,
           NULL PERMEABILITY_THICKNESS,
           NULL SKIN,
           NVL (pi.INNER_DIAMETER, 0) INNER_DIAMETER,
           NVL (pi.Maximum_Diameter, 0) OUTER_DIAMETER,
           'Installation operations (Perforations)' EVENT_TYPE,
           BA.ALIAS_NAME alias_string_type
      FROM well_completion_perf wcp,
           Perforated_Interval pi,
           Perf_Interval_Status pis,
           Activity_Program gba,
           Generic_Act_Fac_Invl gafi,
           Well_Completion wc,
           Borehole b,
           WELL W,
           Alternate_Elevation ae,
           Borehole_Alias ba
     WHERE     PI.BOREHOLE_ID = b.id
           AND wcp.Perforated_Interval_Id = pi.id
           AND pis.Perforated_Interval_Id = pi.id
           AND GBA.WELL_ID = B.WELL_ID
           AND GAFI.ACTIVITY_ID(+) = GBA.ID
           AND 
GAFI.FACILITY_ID = WC.ID(+)
           AND WCP.WELL_COMPLETION_ID = WC.ID
           AND PIS.STATUS IN ('CLOSED', 'C', 'SQUEEZED')
           AND B.WELL_ID = W.ID
           AND B.Working_Coord_System_Id = AE.COORDINATE_SYSTEM_ID(+)
           AND AE.VERTICAL_REFERENCE(+) = 'KB'
           AND BA.BOREHOLE_ID(+) = B.ID
           AND BA.ALIAS_TYPE(+) = 'SIMULATION')