Gas_Fluid_Analysis_DOV

CREATE OR REPLACE VIEW Gas_Fluid_Analysis_DOV(ID, UBHI, BOREHOLE_NAME, COMPLETION_NAME, TUBING_STRING_NAME, SAMPLING_DATE, ANALYSIS_DATE, ANALYZED_BY, ANALYSIS_LAB, C1, C2, C3, IC4, NC4, IC5, NC5, C6PLUS, CO2, O2, N2, H2S, GAS_SPECIFIC_GRAVITY, MOLECULAR_WEIGHT, REMARKS, INSERT_DATE, INSERT_USER, BOREHOLE_ID, WELL_COMPLETION_ID, FLUID_SAMPLE_ID, FLUID_STATE_PTY_SET_ID, FLUID_NON_STATE_PTY_SET_ID, FLUID_SAMPLE_ACQUISITION_ID, ANALYSIS_LAB_ID, C1_ID, C2_ID, C3_ID, IC4_ID, NC4_ID, IC5_ID, NC5_ID, CO2_ID, O2_ID, N2_ID, H2S_ID, C6PLUS_ID) AS SELECT
  fa.id                        Id,
  bh.UBHI                      UBHI,
  bh.Name                      Borehole_Name,
  wc.name		       Completion_Name,
  ts.name                      Tubing_String_Name,
  fsa.end_date                 Sampling_Date,
  fa.start_date                Analysis_Date,
  fa.original_source           Analyzed_By,
  co.name                      Analysis_Lab,
  mc1.mole_fraction            C1,
  mc2.mole_fraction           
 C2,
  mc3.mole_fraction            C3,
  mc4.mole_fraction            iC4,
  mc5.mole_fraction            nC4,
  mc6.mole_fraction            iC5,
  mc7.mole_fraction            nC5,
  mc12.mole_fraction           C6plus,
  mc8.mole_fraction            CO2,
  mc9.mole_fraction            O2,
  mc10.mole_fraction           N2,
  mc11.mole_fraction           H2S,
  fsps.specific_gravity        Gas_Specific_Gravity,
  fnsps.molecular_weight       Molecular_weight,
  fa.remarks                   Remarks,
  fa.insert_date               Insert_Date,
  fa.insert_user               Insert_User,
  bh.Id                        Borehole_Id,
  wc.Id                        Well_Completion_Id,
  fs.id                        Fluid_Sample_Id,
  fsps.id                      Fluid_State_Pty_Set_Id,
  fnsps.id                     Fluid_Non_State_Pty_Set_Id,
  fsa.id                       Fluid_Sample_Acquisition_Id,
  co.id                        Analysis_Lab_Id,
  mc1.id                       C1_Id,
  
mc2.id                       C2_Id,
  mc3.id                       C3_Id,
  mc4.id                       iC4_Id,
  mc5.id                       nC4_Id,
  mc6.id                       iC5_Id,
  mc7.id                       nC5_Id,
  mc8.id                       CO2_Id,
  mc9.id                       O2_Id,
  mc10.id                      N2_Id,
  mc11.id                      H2S_Id,
  mc12.id                      C6plus_Id
 from
      borehole                     bh,
      well_completion              wc,
      tubing_string                ts,
      fluid_sample_acquisition     fsa,
      fluid_sample                 fs,
      fluid_analysis               fa,
      company                      co,
      fluid_state_pty_set          fsps,
      fluid_non_state_pty_set      fnsps,
      material_composition         mc1,
      material_composition         mc2,
      material_composition         mc3,
      material_composition         mc4,
      material_composition         mc5,
      
material_composition         mc6,
      material_composition         mc7,
      material_composition         mc8,
      material_composition         mc9,
      material_composition         mc10,
      material_composition         mc11,
      material_composition         mc12
  where bh.id = wc.borehole_id
   and wc.tubing_string_id = ts.id (+)
   and wc.id = fsa.acquired_from_facility_id
   and fsa.acquired_from_facility_tbl = 'Well_Completion'
   and fsa.id = fs.fluid_acquisition_id
   and fs.material_type = 'Gas'
   and fs.id = fa.fluid_sample_id
   and fa.analysis_method = 'Gas Analysis'
   and fa.Analysis_Lab_Id = co.id(+)
   and fa.id = fsps.fluid_analysis_id (+)
   and fa.id = fnsps.fluid_analysis_id (+)
   and fa.id = mc1.fluid_analysis_id (+)
   and mc1.component_material_type(+) = 'C1'
   and fa.id = mc2.fluid_analysis_id (+)
   and mc2.component_material_type(+) = 'C2'
   and fa.id = mc3.fluid_analysis_id (+)
   and mc3.component_material_type(+) = 'C3'
   and fa.id = 
mc4.fluid_analysis_id (+)
   and mc4.component_material_type(+) = 'iC4'
   and fa.id = mc5.fluid_analysis_id (+)
   and mc5.component_material_type(+) = 'nC4'
   and fa.id = mc6.fluid_analysis_id (+)
   and mc6.component_material_type(+) = 'iC5'
   and fa.id = mc7.fluid_analysis_id (+)
   and mc7.component_material_type(+) = 'nC5'
   and fa.id = mc8.fluid_analysis_id (+)
   and mc8.component_material_type(+) = 'CO2'
   and fa.id = mc9.fluid_analysis_id (+)
   and mc9.component_material_type(+) = 'O2'
   and fa.id = mc10.fluid_analysis_id (+)
   and mc10.component_material_type(+) = 'N2'
   and fa.id = mc11.fluid_analysis_id (+)
   and mc11.component_material_type(+) = 'H2S'
   and fa.id = mc12.fluid_analysis_id (+)
   and mc12.component_material_type(+) = 'C6plus'