EXP_GAE_RAW_TEST_DOV

CREATE OR REPLACE FORCE VIEW EXP_GAE_RAW_TEST_DOV(ID, GEOLOGIC_LAYER_ID, GENERIC_ACT_FAC_INVL_ID, WELL_TEST_ID, TESTED_LAYER, RESERVOIR_ID, UWI, FIELD, TEST_DATE, TEST_TYPE, STRING_TYPE, TESTED_COMPANY, SERIAL_NO, POSITION, GAUGE_TYPE, RECORDER_ID, DEPTH, DEPTH_REF, WELL_ID, TUBING_STRING_ID, TODAY) AS SELECT DISTINCT TO_NUMBER (gafi.id || wc2.id) Id,
                   gl.id Geologic_Layer_Id,
                   gafi.id Generic_Act_Fac_Invl_Id,
                   wt.id AS well_test_id,
                   gl.Standard_Layer_Name Tested_Layer,
                   res.id Reservoir_Id,
                   w.uwi UWI,
                   fa.alias_name Field,
                   wt.start_date Test_Date,
                   wt.well_test_type Test_Type,
                   ts.string_type String_Type,
                   c.UNIQUE_IDENTIFIER Tested_Company,
                   rec.Inventory_Id Serial_No,
                   gafi.Relative_Position Position,
                   r_rec.name Gauge_Type,
       
            rec.id Recorder_Id,
                   gafi.top_md DEPTH,
                   gafi.vertical_reference Depth_Ref,
                   W.ID AS well_id,
                   TS.ID AS tubing_string_id,
                   TO_CHAR (CURRENT_TIMESTAMP, 'HH24:MI Dy Mon DD YYYY')
     FROM Well w,
          Field_Alias fa,
          Borehole b,
          Well_Completion wc1,
          Well_Completion wc2,
          Tubing_String ts,
          Well_Test wt,
          Generic_Act_Fac_Invl gafi,
          Recorder rec,
          Facility_Composition fc,
          Well_Completion_Reservoir wcr,
          Reservoir res,
          Geologic_layer gl,
          Activity_BA_Invl abi,
          Company c,
          R_Recorder r_rec
    WHERE     b.ubhi = w.uwi
          AND w.field_id = fa.field_id
          AND fa.alias_type = 'Long Name'
          AND wc1.tubing_string_id = ts.id(+)
          AND wc1.borehole_id = b.id
          AND wc1.well_completion_type = 'TEST_COMPLETION'
          AND 
fc.part_facility_tbl = 'Well_Completion'
          AND fc.whole_facility_tbl = 'Well_Completion'
          AND fc.whole_facility_id = wc1.id
          AND fc.part_facility_id = wc2.id
          AND wcr.well_completion_id = wc2.id
          AND res.id = wcr.reservoir_id
          AND res.geologic_layer_id = gl.id
          AND wt.well_completion_id = wc1.id
          AND WT.WELL_TEST_TYPE IN
                 (SELECT code
                    FROM r_activity_category
                   WHERE super_type IN
                            ('PROD_PBU',
                             'INJ_PFO',
                             'STATIC_GRAD',
                             'FLOWING_GRAD',
                             'DST'))
          AND wt.id = abi.activity_id(+)
          AND c.id(+) = abi.Business_Associate_Id
          AND gafi.activity_id = wt.id
          AND GAFI.FACILITY_TBL = 'Recorder'
          AND GAFI.FACILITY_ROLE = 'GAUGE'
          AND rec.id = gafi.facility_id
          AND 
rec.recorder_type = r_rec.code
          AND EXISTS
                 (SELECT 'X'
                    FROM Well_Test_Trace wtt, Well_Test_Stage wts
                   WHERE     WTT.WELL_TEST_STAGE_ID = WTS.ID
                         AND WTS.WELL_TEST_ID = WT.ID
                         AND ROWNUM < 2)
   UNION
   SELECT TO_NUMBER (
                SUBSTR (WT.id, -3)
             || SUBSTR (REC.id, -3)
             || SUBSTR (GL.id, -3)
             || SUBSTR (TS.ID, -3))
             Id,
          gl.id Geologic_Layer_Id,
          gafi.id Generic_Act_Fac_Invl_Id,
          wt.id AS well_test_id,
          gl.Standard_Layer_Name Tested_Layer,
          res.id Reservoir_Id,
          w.uwi UWI,
          fa.alias_name Field,
          wt.start_date Test_Date,
          wt.well_test_type Test_Type,
          ts.string_type String_Type,
          c.UNIQUE_IDENTIFIER Tested_Company,
          rec.Inventory_Id Serial_No,
          gafi.Relative_Position Position,
          r_rec.name 
Gauge_Type,
          rec.id Recorder_Id,
          gafi.top_md DEPTH,
          gafi.vertical_reference Depth_Ref,
          W.ID AS well_id,
          TS.ID AS tubing_string_id,
          TO_CHAR (CURRENT_TIMESTAMP, 'HH24:MI Dy Mon DD YYYY')
     FROM Well w,
          Field_Alias fa,
          Borehole b,
          Well_Completion wc1,
          Well_Completion wc2,
          Tubing_String ts,
          Well_Test wt,
          Generic_Act_Fac_Invl gafi,
          Generic_Act_Fac_Invl gafi1,
          Recorder rec,
          Facility_Composition fc,
          Facility_Composition fc1,
          Well_Completion_Reservoir wcr,
          Reservoir res,
          Geologic_layer gl,
          Activity_BA_Invl abi,
          Company c,
          R_Recorder r_rec,
          Facility_Connection fcom
    WHERE     b.ubhi = w.uwi
          AND w.field_id = fa.field_id
          AND fa.alias_type = 'Long Name'
          AND ts.borehole_id = b.id
          AND wc1.borehole_id = b.id
          
AND wc1.well_completion_type = 'TEST_COMPLETION'
          AND fc.part_facility_tbl = 'Well_Completion'
          AND fc.whole_facility_tbl = 'Well_Completion'
          AND fc.whole_facility_id = wc1.id
          AND fc1.whole_facility_id = fc.part_facility_id
          AND fc1.part_facility_id = wc2.id
          AND wcr.well_completion_id = wc2.id
          AND res.id = wcr.reservoir_id
          AND res.geologic_layer_id = gl.id
          AND wt.well_completion_id = wc1.id
          AND WT.WELL_TEST_TYPE IN (SELECT code
                                      FROM r_activity_category
                                     WHERE super_type = 'INTERFERENCE')
          AND wt.id = abi.activity_id(+)
          AND c.id(+) = abi.Business_Associate_Id
          AND gafi.activity_id = wt.id
          AND gafi1.activity_id(+) = wt.id
          AND GAFI1.FACILITY_TBL = 'Tubing_String'
          AND GAFI1.FACILITY_ROLE IN ('TEST_STRING_1', 'TEST_STRING_2')
          AND GAFI.FACILITY_TBL = 
'Recorder'
          AND GAFI.FACILITY_ROLE = 'GAUGE'
          AND TS.ID = GAFI1.FACILITY_ID
          AND GAFI.FACILITY_TBL = 'Recorder'
          AND GAFI.FACILITY_ROLE = 'GAUGE'
          AND FCOM.TO_FACILITY_TBL = 'Tubing_String'
          AND FCOM.FROM_FACILITY_TBL = 'Recorder'
          AND FCOM.TO_FACILITY_ID = TS.ID
          AND FCOM.FROM_FACILITY_ID = REC.ID
          AND rec.id = gafi.facility_id
          AND rec.recorder_type = r_rec.code
          AND EXISTS
                 (SELECT 'X'
                    FROM Well_Test_Trace wtt, Well_Test_Stage wts
                   WHERE     WTT.WELL_TEST_STAGE_ID = WTS.ID
                         AND WTS.WELL_TEST_ID = WT.ID
                         AND ROWNUM < 2)