-----------------------------------------------------------------------
-- DDL-Statement generation Mon Feb 01 14:37:49 CET 2016
-----------------------------------------------------------------------
-- drop the old view:
-----------------------------------------------------------------------
drop view filter.ERGEBNISERFASSUNG;

-----------------------------------------------------------------------
-- create the new view:
-----------------------------------------------------------------------
create view FILTER.ERGEBNISERFASSUNG as
 select
  pez.oid as OID,
  pr.probe as PROBE,
  pr.nahmedatum as PROBENAHMEDATUM,
  pr.registrierdatum as PROBENREGISTRIERDATUM,
  pr.typ AS PROBENTYP,
  pr.art as PROBENART,
  pr.status as PROBENSTATUS,
  pr.prioritaet as PROBENPRIORITAET,
  pr.reserve4 as WETTER_NAHMETAG,
  pr.reserve5 as PROBEN_ID,
  pr.reserve6 as PROBENBEZEICHNUNG,
  case when przu.bzusatz1 = 1 then 'ja' else 'nein' end as PARALLELPROBE_AN_KUNDE,
  case when przu.bzusatz2 = 1 then 'ja' else 'nein' end as STOERFALL,
  case when przu.bzusatz3 = 1 then 'ja' else 'nein' end as SONDERPROBENAHME,
  case when przu.bzusatz4 = 1 then 'ja' else 'nein' end as AN_FREMDLABOR_VERSENDET,
  case when przu.bzusatz5 = 1 then 'nein' else 'ja' end as PROBENAHME_MOEGLICH,
  an.analyse as ANALYSE,
  an.name as ANALYSENAME,
  an.sw_fremduntersuchung as FREMDLABORANALYSE,
  er.ergebnis as ERGEBNIS,
  er.name as ERGEBNISNAME,
  er.art as ERGEBNISART,
  er.typ as ERGEBNISTYP,
  pez.status as ERGEBNISSTATUS,
  ew.wiederholung as MESSWIEDERHOLUNG,
  ppl.pruefplan as PRUEFPLAN,
  ppl.name as PRUEFPLAN_NAME,
  pd.produkt as MEDIUM,
  pd.name as MEDIUMNAME,
  kd.kunde as AUFTRAGGEBER,
  kd.name as AUFTRAGGEBERNAME,
  org.organisation as PROBENSTELLE,
  org.name as PROBENSTELLENNAME,
  hs.hauptstelle as HAUPTSTELLE,
  hs.name as HAUPTSTELLENNAME,
  us.unterstelle as UNTERSTELLE,
  us.name as UNTERSTELLENNAME,
  us.reserve3 as SCHACHTNUMMER,
  angr.gruppe as ANALYSENGRUPPE,
  angr.name as ANALYSENGRUPPENNAME
 from dialims.proben_erg_zuo pez
  left outer
     join dialims.proben pr on pr.oid = pez.probe_oid
  left outer
     join dialims.analysen an on an.oid = pez.analyse_oid
  left outer
     join dialims.ergebnisse er on er.oid = pez.ergebnis_oid
  left outer
     join dialims.ergebniswerte ew on ew.proben_erg_zuo_oid = pez.oid
  left outer
     join dialims.pp_p_zuo ppp on ppp.probe_oid = pr.oid
  left outer
     join dialims.pruefplaene ppl on ppl.oid = ppp.pruefplan_oid
  left outer
     join dialims.produkte pd on pd.oid = pr.produkt_oid
  left outer
     join dialims.kunden kd on kd.oid = pr.kunde_oid
  left outer
     join dialims.organisationen org on org.oid = pr.organisation_oid
  left outer
     join dialims.hauptstellen hs on hs.oid = pr.hauptstelle_oid
  left outer
     join dialims.unterstellen us on us.oid = pr.unterstelle_oid
  left outer
     join dialims.aag_zuo aag on aag.analyse_oid = an.oid
  left outer
     join dialims.analysegruppen angr on angr.oid = aag.gruppe_oid
  left outer
     join dialims.zusaetze przu on przu.ref_oid = pr.oid
;

-----------------------------------------------------------------------
-- grants for the view:
-----------------------------------------------------------------------
grant select on filter.ERGEBNISERFASSUNG to dialims