Monthly_Prod_Vol_DOV

CREATE OR REPLACE FORCE VIEW Monthly_Prod_Vol_DOV(ID, UBHI, BOREHOLE_NAME, COMPLETION_NAME, TUBING_STRING_NAME, PRODUCTION_DATE, OIL_VOLUME, OIL_DAYS_ON, WATER_VOLUME, WATER_DAYS_ON, GAS_VOLUME, GAS_DAYS_ON, CONDENSATE_VOLUME, CONDENSATE_DAYS_ON, REMARKS, INSERT_DATE, INSERT_USER, BOREHOLE_ID, TUBING_STRING_ID, WELL_COMPLETION_ID, OIL_VOLUME_ID, WATER_VOLUME_ID, GAS_VOLUME_ID, CONDENSATE_VOLUME_ID, PRODUCTION_ENTITY_ID) AS SELECT
 ph.Id                                       Id,  --Pk / Unique
 bh.UBHI                                     UBHI,--Nk Mandatory
 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*24)        Oil_Days_On,
 pv_water.volume                             Water_Volume,
 
pv_water.productive_duration/(3600*24)      Water_Days_On,
 pv_gas.volume                               Gas_Volume,
 pv_gas.productive_duration/(3600*24)        Gas_Days_On,
 pv_condensate.volume                        Condensate_Volume,
 pv_condensate.productive_duration/(3600*24) Condensate_Days_On,
 ph.remarks                                  Remarks,
 ph.insert_date                              Insert_Date,
 ph.insert_user                              Insert_User,
 bh.Id                                       Borehole_Id,
 ts.Id                                       Tubing_String_Id,
 wc.Id                                       Well_Completion_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,
 pe.id					     Production_Entity_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 wc.tubing_string_id = ts.id(+)
  and wc.id = pe.well_completion_id
  and pe.id = ph.production_entity_id
  and pe.Production_Entity_Subtype = 'Well_Completion'
  and ph.Volume_Method_Code = 'Allocated'
  and ph.Reporting_Period_Code = 'Monthly'
  and ph.Version = '1'
  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'