Daily_Prod_Vol_DOV

CREATE OR REPLACE FORCE VIEW Daily_Prod_Vol_DOV(ID, UBHI, BOREHOLE_NAME, COMPLETION_NAME, TUBING_STRING_NAME, PRODUCTION_DATE, OIL_VOLUME, OIL_HOURS_ON, WATER_VOLUME, WATER_HOURS_ON, GAS_VOLUME, GAS_HOURS_ON, CONDENSATE_VOLUME, CONDENSATE_HOURS_ON, REMARKS, INSERT_DATE, INSERT_USER, BOREHOLE_ID, WELL_COMPLETION_ID, PRODUCTION_ENTITY_ID, TUBING_STRING_ID, OIL_VOLUME_ID, WATER_VOLUME_ID, GAS_VOLUME_ID, CONDENSATE_VOLUME_ID) AS SELECT
 ph.Id                                  Id,
 bh.UBHI                                UBHI,
 bh.Name                                Borehole_Name,
 wc.name                                Completion_Name,
 ts.name                                Tubing_String_Name,
 ph.start_date                          Production_Date,
 pv_oil.volume                          Oil_Volume,
 pv_oil.productive_duration/3600        Oil_Hours_On,
 pv_water.volume                        Water_Volume,
 pv_water.productive_duration/3600      Water_Hours_On,
 pv_gas.volume               
           Gas_Volume,
 pv_gas.productive_duration/3600        Gas_Hours_On,
 pv_condensate.volume                   Condensate_Volume,
 pv_condensate.productive_duration/3600 Condensate_Hours_On,
 ph.remarks                             Remarks,
 ph.insert_date                         Insert_Date,
 ph.insert_user                         Insert_User,
 bh.Id                                  Borehole_Id,
 wc.Id                                  Well_Completion_Id,
 pe.Id                                  Production_Entity_Id,
 ts.Id                                  Tubing_String_Id,
 pv_oil.Id                              Oil_Volume_Id,
 pv_water.Id                            Water_Volume_Id,
 pv_gas.Id                              Gas_Volume_Id,
 pv_condensate.Id                       Condensate_Volume_Id
from production_header  ph,
     borehole           bh,
     well_completion    wc,
     tubing_string      ts,
     production_entity  pe,
     production_volume  pv_oil,
     
production_volume  pv_water,
     production_volume  pv_gas,
     production_volume  pv_condensate
where bh.id = wc.borehole_id
  and pe.well_completion_id = wc.id
  and ph.production_entity_id = pe.id
  and ph.Volume_Method_Code = 'Allocated'
  and ph.Reporting_Period_Code = 'Daily'
  and ph.Version = '1'
  and wc.tubing_string_id = ts.id(+)
  and ph.id = pv_oil.production_header_id (+)
  and pv_oil.material_type(+) = 'Oil'
  and pv_oil.flow_direction_code(+) = 'Out'
  and ph.id = pv_water.production_header_id (+)
  and pv_water.material_type(+) = 'Water'
  and pv_water.flow_direction_code(+) = 'Out'
  and ph.id = pv_gas.production_header_id (+)
  and pv_gas.material_type(+) = 'Gas'
  and pv_gas.flow_direction_code(+) = 'Out'
  and ph.id = pv_condensate.production_header_id (+)
  and pv_condensate.material_type(+) = 'Condensate'
  and pv_condensate.flow_direction_code(+) = 'Out'