Production_Test_DOV

CREATE OR REPLACE FORCE VIEW Production_Test_DOV(ID, UBHI, BOREHOLE_NAME, COMPLETION_NAME, TUBING_STRING_NAME, WELL_TEST_TYPE, SOURCE, START_DATE, END_DATE, DURATION, SAND_CUT, OIL_VOLUME, WATER_VOLUME, GAS_LIFT_VOLUME, GAS_VOLUME, CONDENSATE_VOLUME, TEST_SEPARATOR_NAME, SEPARATOR_PRESSURE, SEPARATOR_TEMPERATURE, WELL_HEAD_PRESSURE, WELL_HEAD_TEMPERATURE, CASING_PRESSURE, CHOKE_SIZE, CHOKE_SIZE_UOM, OIL_DENSITY, OIL_API_GRAVITY, GAS_SPECIFIC_GRAVITY, BSW, GOR, GLR, REMARKS, INSERT_DATE, INSERT_USER, BOREHOLE_ID, TUBING_STRING_ID, WELL_COMPLETION_ID, WELL_TEST_STAGE_ID, FLUID_SAMPLE_ID, FLUID_STATE_PTY_SET_ID, OIL_VOLUME_ID, WATER_VOLUME_ID, GAS_LIFT_VOLUME_ID, GAS_VOLUME_ID, CONDENSATE_VOLUME_ID) AS SELECT
 wt.Id                          Id,
 bh.UBHI                        UBHI,
 bh.Name                        Borehole_Name,
 wc.name                        Completion_Name,
 ts.name                        Tubing_String_Name,
 wt.well_test_type              Well_Test_Type,
 wt.source    
                  Source,
 wt.start_date                  Start_Date,
 wt.end_date                    End_Date,
 wt.duration /3600              Duration,
 wts.Sand_Cut                   Sand_Cut,
 wt_oil.volume                  Oil_Volume,
 wt_water.volume                Water_Volume,
 wt_gaslift.volume              Gas_Lift_Volume,
 wt_gas.volume                  Gas_Volume,
 wt_condensate.volume           Condensate_Volume,
 s.name                         Test_Separator_Name,
 s.Pressure                     Separator_Pressure,
 s.Temperature                  Separator_Temperature,
 wts.average_tubing_pressure    Well_Head_Pressure,
 wts.average_tubing_temperature Well_Head_Temperature,
 wts.average_casing_pressure    Casing_Pressure,
 wts.tubing_choke_size          Choke_Size,
 wts.tubing_choke_size_UOM      Choke_Size_UOM,
 fsps.density                   Oil_Density,
 fsps.api_oil_Gravity           Oil_API_Gravity,
 fsps.specific_Gravity          Gas_Specific_Gravity,
 wts.bsw      
                  BSW,
 wts.GOR                        GOR,
 wts.GLR                        GLR,
 wt.remarks                     Remarks,
 wt.insert_date                 Insert_Date,
 wt.insert_user                 Insert_User,
 bh.Id                          Borehole_Id,
 ts.Id                          Tubing_String_Id,
 wc.Id                          Well_Completion_Id,
 wts.Id                         Well_Test_Stage_Id,
 fs.Id                          Fluid_Sample_Id,
 fsps.Id                        Fluid_State_Pty_Set_Id,
 wt_oil.Id                      Oil_Volume_Id,
 wt_water.Id                    Water_Volume_Id,
 wt_gaslift.Id                  Gas_Lift_Volume_Id,
 wt_gas.Id                      Gas_Volume_Id,
 wt_condensate.Id               Condensate_Volume_Id
from borehole                 bh,
     well_completion          wc,
     well_test_stage          wts,
     tubing_string            ts,
     well_test                wt,
     well_test_fluid_recovery wt_oil,
     
well_test_fluid_recovery wt_water,
     well_test_fluid_recovery wt_gas,
     well_test_fluid_recovery wt_gaslift,
     well_test_fluid_recovery wt_condensate,
     fluid_state_pty_set      fsps,
     fluid_sample             fs,
(select distinct sep.name, gafi.pressure, gafi.temperature, wts.id
   from  well_test_stage wts, separator sep, generic_act_fac_invl gafi
  where gafi.facility_id = sep.id
    and gafi.facility_tbl = 'Separator'
    and gafi.Activity_Id = wts.Id
    and gafi.Activity_Tbl = 'Well_Test_Stage' ) s
where wt.well_test_type = 'Production Test'
  and wt.well_completion_id = wc.id
  and wc.borehole_id = bh.id
  and wc.tubing_string_id = ts.id (+)
  and wt.id = wts.well_test_id
  and wts.well_test_stage_type = 'Flowing'
  and wts.stage_number = 1
  and wts.Id = fs.Well_Test_Stage_Id (+)
  and fs.Id = fsps.Fluid_Sample_Id (+)
  and wt_oil.Well_Test_Stage_Id(+) = wts.Id
  and wt_oil.Material_Type(+) = 'Oil'
  and wt_oil.Volume_Method_Code(+) = 'Measured'
  and 
wt_oil.Flow_Direction_Code(+) = 'Out'
  and wt_water.Well_Test_Stage_Id(+) = wts.Id
  and wt_water.Material_Type(+) = 'Water'
  and wt_water.Volume_Method_Code(+) = 'Measured'
  and wt_water.Flow_Direction_Code(+) = 'Out'
  and wt_gaslift.Well_Test_Stage_Id(+) = wts.Id
  and wt_gaslift.Material_Type(+) = 'Gas Lift Gas'
  and wt_gaslift.Volume_Method_Code(+) = 'Measured'
  and wt_gaslift.Flow_Direction_Code(+) = 'In'
  and wt_gas.Well_Test_Stage_Id(+) = wts.Id
  and wt_gas.Material_Type(+) = 'Gas'
  and wt_gas.Volume_Method_Code(+) = 'Measured'
  and wt_gas.Flow_Direction_Code(+) = 'Out'
  and wt_condensate.Well_Test_Stage_Id(+) = wts.Id
  and wt_condensate.Material_Type(+) = 'Condensate'
  and wt_condensate.Volume_Method_Code(+) = 'Measured'
  and wt_condensate.Flow_Direction_Code(+) = 'Out'
  and wts.id=s.id(+)