Datadictionary OpenWave 3.1.32

View: vwfrmuren

Schema

public

Owner

waveuser005

Descriptions

There is no description for view vwfrmuren

Fields

Rules

Indices

There are no indices for table vwfrmuren

Definition

CREATE VIEW public.vwfrmuren (
   dnkey,
   dnkeysoorturen,
   dvomsoorturen,
   dnkeyinspecties,
   dnkeyadviezen,
   dnkeybezwaarberoep,
   dnkeyomgvergunningen,
   dnkeybouwvergunningen,
   dnkeyovvergunningen,
   dnkeyhandhavingen,
   dnkeymilvergunningen,
   dnkeymilinrichtingen,
   dnkeyinfoaanvraag,
   dnkeyhorecavergunningen,
   dfaantaluur,
   dvaantaluur,
   ddurendatum,
   dvtoelichting,
   ddgefactureerd,
   dlopdrachtgeverakkoord,
   dvkleurakkoord,
   dvcodeurengemaaktdoor,
   dvurengemaaktdoorvoluit,
   dvcodelaatstemuteerder,
   dvlaatstemuteerdervoluit,
   dvgemeenteid,
   dvwavehoofdzaakcode,
   dvhoofdzaaktypeoms,
   dvwavedeelzaakcode,
   dvzaakomschrijving,
   dvdeelzaaktypeoms,
   dfaantalmedzaakuur,
   dfaantalzaakuur,
   dnkeycompartiment,
   dlurenopdrgzb,
   ddblokkeerdatum,
   ddhoofdzaakgesloten,
   dddeelzaakgesloten)
AS
SELECT a.dnkey,
   a.dnkeysoorturen,
   b.dvomschrijving AS dvomsoorturen,
   a.dnkeyinspecties,
   a.dnkeyadviezen,
   a.dnkeybezwaarberoep,
   a.dnkeyomgvergunningen,
   a.dnkeybouwvergunningen,
   a.dnkeyovvergunningen,
   a.dnkeyhandhavingen,
   a.dnkeymilvergunningen,
   a.dnkeymilinrichtingen,
   a.dnkeyinfoaanvraag,
   a.dnkeyhorecavergunningen,
   a.dfaantaluur,
   fn_dec2ana(a.dfaantaluur) AS dvaantaluur,
   a.ddurendatum,
   a.dvtoelichting,
   a.ddgefactureerd,
   a.dlopdrachtgeverakkoord,
       CASE
           WHEN a.dlopdrachtgeverakkoord = 'N'::bpchar THEN 'wit'::text
           WHEN a.dlopdrachtgeverakkoord = 'F'::bpchar THEN 'rood'::text
           ELSE 'groen'::text
       END::character varying(5) AS dvkleurakkoord,
   a.dvcodeurengemaaktdoor,
   (((
       CASE
           WHEN btrim(c.dvvoorletters::text) IS NULL THEN ''::text
           ELSE c.dvvoorletters::text || ' '::text
       END ||
       CASE
           WHEN btrim(c.dvtussenvoegsel::text) IS NULL THEN ''::text
           ELSE c.dvtussenvoegsel::text || ' '::text
       END) || c.dvomschrijving::text))::character varying(62) AS
           dvurengemaaktdoorvoluit,
   a.dvcodelaatstemuteerder,
   (((
       CASE
           WHEN btrim(d.dvvoorletters::text) IS NULL THEN ''::text
           ELSE d.dvvoorletters::text || ' '::text
       END ||
       CASE
           WHEN btrim(d.dvtussenvoegsel::text) IS NULL THEN ''::text
           ELSE d.dvtussenvoegsel::text || ' '::text
       END) || d.dvomschrijving::text))::character varying(62) AS
           dvlaatstemuteerdervoluit,
       CASE
           WHEN tbw.dvwoonplaatsid IS NULL AND a.dnkeymilvergunningen IS NOT
               NULL THEN (
   SELECT vwfrmmilvergunningen.dvgemeenteid
   FROM vwfrmmilvergunningen
   WHERE vwfrmmilvergunningen.dnkey =
                   CASE
                       WHEN a.dnkeymilvergunningen IS NOT NULL THEN
                           a.dnkeymilvergunningen
                       ELSE a.dnkeymilinrichtingen
                   END
   )
           ELSE tbw.dvwoonplaatsid
       END AS dvgemeenteid,
       CASE
           WHEN a.dnkeyomgvergunningen IS NOT NULL THEN e.dvzaakcode
           WHEN a.dnkeybouwvergunningen IS NOT NULL THEN f.dvbouwnr
           WHEN a.dnkeyovvergunningen IS NOT NULL THEN g.dvvergunningsnr
           WHEN a.dnkeyhandhavingen IS NOT NULL THEN h.dvaanschrijfnr
           WHEN a.dnkeyhorecavergunningen IS NOT NULL THEN i.dvvergnummer
           WHEN a.dnkeymilvergunningen IS NOT NULL THEN j.dvvergnummer
           WHEN a.dnkeyinfoaanvraag IS NOT NULL THEN k.dvinfonummer
           ELSE l.dvinrichtingnr
       END AS dvwavehoofdzaakcode,
       CASE
           WHEN a.dnkeyomgvergunningen IS NOT NULL THEN srtw.dvomschrijving
           WHEN a.dnkeybouwvergunningen IS NOT NULL THEN srtb.dvomschrijving
           WHEN a.dnkeyovvergunningen IS NOT NULL THEN srto.dvomschrijving
           WHEN a.dnkeyhandhavingen IS NOT NULL THEN srth.dvomschrijving
           WHEN a.dnkeyhorecavergunningen IS NOT NULL THEN srtc.dvomschrijving
           WHEN a.dnkeymilvergunningen IS NOT NULL THEN srtm.dvomschrijving
           WHEN a.dnkeyinfoaanvraag IS NOT NULL THEN srti.dvomschrijving
           WHEN a.dnkeymilinrichtingen IS NOT NULL THEN l.dvinrichtingnaam
           ELSE NULL::character varying
       END AS dvhoofdzaaktypeoms,
       CASE
           WHEN a.dnkeyinspecties IS NOT NULL THEN m.dvwavezaakcode
           WHEN a.dnkeyadviezen IS NOT NULL THEN n.dvwavezaakcode
           WHEN a.dnkeybezwaarberoep IS NOT NULL THEN o.dvwavezaakcode
           ELSE NULL::character varying
       END AS dvwavedeelzaakcode,
       CASE
           WHEN
           CASE
               WHEN a.dnkeyinspecties IS NOT NULL THEN m.dvomschrijving
               WHEN a.dnkeyadviezen IS NOT NULL THEN n.dvomschrijving
               WHEN a.dnkeybezwaarberoep IS NOT NULL THEN o.dvomschrijving
               ELSE NULL::character varying
           END IS NOT NULL THEN
           CASE
               WHEN a.dnkeyinspecties IS NOT NULL THEN m.dvomschrijving
               WHEN a.dnkeyadviezen IS NOT NULL THEN n.dvomschrijving
               WHEN a.dnkeybezwaarberoep IS NOT NULL THEN o.dvomschrijving
               ELSE NULL::character varying
           END
           ELSE
           CASE
               WHEN a.dnkeyomgvergunningen IS NOT NULL THEN e.dvaanvraagnaam
               WHEN a.dnkeybouwvergunningen IS NOT NULL THEN f.dvinhoud
               WHEN a.dnkeyovvergunningen IS NOT NULL THEN g.dvomschrijving
               WHEN a.dnkeyhandhavingen IS NOT NULL THEN h.dvomsbouwwerk
               WHEN a.dnkeyhorecavergunningen IS NOT NULL THEN i.dvomschrijving
               WHEN a.dnkeymilvergunningen IS NOT NULL THEN j.dvpublbouwwerk
               WHEN a.dnkeyinfoaanvraag IS NOT NULL THEN k.dvomschrijving
               ELSE l.dvinrichtingnaam
           END
       END AS dvzaakomschrijving,
       CASE
           WHEN a.dnkeyinspecties IS NOT NULL THEN inspo.dvaanleiding
           WHEN a.dnkeyadviezen IS NOT NULL THEN advo.dvomschrijving
           WHEN a.dnkeybezwaarberoep IS NOT NULL THEN bezo.dvomschrijving
           ELSE NULL::character varying
       END AS dvdeelzaaktypeoms,
   uren_per_zaak_medewerker.totaal_uren AS dfaantalmedzaakuur,
   uren_per_zaak.totaal_uren AS dfaantalzaakuur,
       CASE
           WHEN a.dnkeyomgvergunningen IS NOT NULL AND a.dnkeyinspecties IS
               NULL AND a.dnkeybezwaarberoep IS NULL THEN
           CASE
               WHEN e.dnkeycompoverrule IS NULL THEN gemcompomg.dnkeycompartiment
               WHEN e.dnkeycompoverrule = 0 THEN NULL::integer
               ELSE e.dnkeycompoverrule
           END
           WHEN a.dnkeyomgvergunningen IS NOT NULL AND a.dnkeyinspecties IS
               NOT NULL AND gemcompomg.dlinclinspectie = 'T'::bpchar THEN
           CASE
               WHEN e.dnkeycompoverrule IS NULL THEN gemcompomg.dnkeycompartiment
               WHEN e.dnkeycompoverrule = 0 THEN NULL::integer
               ELSE e.dnkeycompoverrule
           END
           WHEN a.dnkeyomgvergunningen IS NOT NULL AND a.dnkeybezwaarberoep IS
               NOT NULL AND gemcompomg.dlinclfbezwaarberoep = 'T'::bpchar THEN
           CASE
               WHEN e.dnkeycompoverrule IS NULL THEN gemcompomg.dnkeycompartiment
               WHEN e.dnkeycompoverrule = 0 THEN NULL::integer
               ELSE e.dnkeycompoverrule
           END
           WHEN a.dnkeybouwvergunningen IS NOT NULL AND a.dnkeyinspecties IS
               NULL AND a.dnkeybezwaarberoep IS NULL THEN gemcompbouw.dnkeycompartiment
           WHEN a.dnkeybouwvergunningen IS NOT NULL AND a.dnkeyinspecties IS
               NOT NULL AND gemcompbouw.dlinclinspectie = 'T'::bpchar THEN gemcompbouw.dnkeycompartiment
           WHEN a.dnkeybouwvergunningen IS NOT NULL AND a.dnkeybezwaarberoep
               IS NOT NULL AND gemcompbouw.dlinclfbezwaarberoep = 'T'::bpchar THEN gemcompbouw.dnkeycompartiment
           WHEN a.dnkeyovvergunningen IS NOT NULL AND a.dnkeyinspecties IS
               NULL AND a.dnkeybezwaarberoep IS NULL THEN
           CASE
               WHEN g.dnkeycompoverrule IS NULL THEN gemcompov.dnkeycompartiment
               WHEN g.dnkeycompoverrule = 0 THEN NULL::integer
               ELSE g.dnkeycompoverrule
           END
           WHEN a.dnkeyovvergunningen IS NOT NULL AND a.dnkeyinspecties IS NOT
               NULL AND gemcompov.dlinclinspectie = 'T'::bpchar THEN
           CASE
               WHEN g.dnkeycompoverrule IS NULL THEN gemcompov.dnkeycompartiment
               WHEN g.dnkeycompoverrule = 0 THEN NULL::integer
               ELSE g.dnkeycompoverrule
           END
           WHEN a.dnkeyovvergunningen IS NOT NULL AND a.dnkeybezwaarberoep IS
               NOT NULL AND gemcompov.dlinclfbezwaarberoep = 'T'::bpchar THEN
           CASE
               WHEN g.dnkeycompoverrule IS NULL THEN gemcompov.dnkeycompartiment
               WHEN g.dnkeycompoverrule = 0 THEN NULL::integer
               ELSE g.dnkeycompoverrule
           END
           WHEN a.dnkeyhandhavingen IS NOT NULL AND a.dnkeyinspecties IS NULL
               AND a.dnkeybezwaarberoep IS NULL THEN
           CASE
               WHEN h.dnkeycompoverrule IS NULL THEN gemcomphh.dnkeycompartiment
               WHEN h.dnkeycompoverrule = 0 THEN NULL::integer
               ELSE h.dnkeycompoverrule
           END
           WHEN a.dnkeyhandhavingen IS NOT NULL AND a.dnkeyinspecties IS NOT
               NULL AND gemcomphh.dlinclinspectie = 'T'::bpchar THEN
           CASE
               WHEN h.dnkeycompoverrule IS NULL THEN gemcomphh.dnkeycompartiment
               WHEN h.dnkeycompoverrule = 0 THEN NULL::integer
               ELSE h.dnkeycompoverrule
           END
           WHEN a.dnkeyhandhavingen IS NOT NULL AND a.dnkeybezwaarberoep IS
               NOT NULL AND gemcomphh.dlinclfbezwaarberoep = 'T'::bpchar THEN
           CASE
               WHEN h.dnkeycompoverrule IS NULL THEN gemcomphh.dnkeycompartiment
               WHEN h.dnkeycompoverrule = 0 THEN NULL::integer
               ELSE h.dnkeycompoverrule
           END
           WHEN a.dnkeyhorecavergunningen IS NOT NULL AND a.dnkeyinspecties IS
               NULL AND a.dnkeybezwaarberoep IS NULL THEN
           CASE
               WHEN i.dnkeycompoverrule IS NULL THEN gemcomphor.dnkeycompartiment
               WHEN i.dnkeycompoverrule = 0 THEN NULL::integer
               ELSE i.dnkeycompoverrule
           END
           WHEN a.dnkeyhorecavergunningen IS NOT NULL AND a.dnkeyinspecties IS
               NOT NULL AND gemcomphor.dlinclinspectie = 'T'::bpchar THEN
           CASE
               WHEN i.dnkeycompoverrule IS NULL THEN gemcomphor.dnkeycompartiment
               WHEN i.dnkeycompoverrule = 0 THEN NULL::integer
               ELSE i.dnkeycompoverrule
           END
           WHEN a.dnkeyhorecavergunningen IS NOT NULL AND a.dnkeybezwaarberoep
               IS NOT NULL AND gemcomphor.dlinclfbezwaarberoep = 'T'::bpchar THEN
           CASE
               WHEN i.dnkeycompoverrule IS NULL THEN gemcomphor.dnkeycompartiment
               WHEN i.dnkeycompoverrule = 0 THEN NULL::integer
               ELSE i.dnkeycompoverrule
           END
           WHEN a.dnkeymilvergunningen IS NOT NULL AND a.dnkeybezwaarberoep IS
               NULL THEN
           CASE
               WHEN j.dnkeycompoverrule IS NULL THEN gemcompmil.dnkeycompartiment
               WHEN j.dnkeycompoverrule = 0 THEN NULL::integer
               ELSE j.dnkeycompoverrule
           END
           WHEN a.dnkeymilvergunningen IS NOT NULL AND a.dnkeybezwaarberoep IS
               NOT NULL AND gemcompmil.dlinclfbezwaarberoep = 'T'::bpchar THEN
           CASE
               WHEN j.dnkeycompoverrule IS NULL THEN gemcompmil.dnkeycompartiment
               WHEN j.dnkeycompoverrule = 0 THEN NULL::integer
               ELSE j.dnkeycompoverrule
           END
           WHEN a.dnkeyinfoaanvraag IS NOT NULL THEN
           CASE
               WHEN k.dnkeycompoverrule IS NULL THEN gemcompinf.dnkeycompartiment
               WHEN k.dnkeycompoverrule = 0 THEN NULL::integer
               ELSE k.dnkeycompoverrule
           END
           WHEN a.dnkeymilinrichtingen IS NOT NULL AND a.dnkeyinspecties IS
               NULL THEN gemcompinr.dnkeycompartiment
           WHEN a.dnkeymilinrichtingen IS NOT NULL AND a.dnkeyinspecties IS
               NOT NULL AND gemcompinr.dlinclinspectie = 'T'::bpchar THEN gemcompinr.dnkeycompartiment
           ELSE NULL::integer
       END AS dnkeycompartiment,
       CASE
           WHEN a.dnkeyomgvergunningen IS NOT NULL THEN srtw.dlurenopdrgzb
           WHEN a.dnkeybouwvergunningen IS NOT NULL THEN srtb.dlurenopdrgzb
           WHEN a.dnkeyovvergunningen IS NOT NULL THEN srto.dlurenopdrgzb
           WHEN a.dnkeyhandhavingen IS NOT NULL THEN srth.dlurenopdrgzb
           WHEN a.dnkeyhorecavergunningen IS NOT NULL THEN srtc.dlurenopdrgzb
           WHEN a.dnkeymilvergunningen IS NOT NULL THEN srtm.dlurenopdrgzb
           WHEN a.dnkeyinfoaanvraag IS NOT NULL THEN srtm.dlurenopdrgzb
           ELSE 'F'::bpchar
       END AS dlurenopdrgzb,
       CASE
           WHEN a.dnkeyomgvergunningen IS NOT NULL THEN e.ddblokkering
           WHEN a.dnkeybouwvergunningen IS NOT NULL THEN f.ddblokkering
           WHEN a.dnkeyovvergunningen IS NOT NULL THEN g.ddblokkering
           WHEN a.dnkeyhandhavingen IS NOT NULL THEN h.ddblokkering
           WHEN a.dnkeyhorecavergunningen IS NOT NULL THEN i.ddblokkering
           WHEN a.dnkeymilvergunningen IS NOT NULL THEN j.ddblokkering
           WHEN a.dnkeyinfoaanvraag IS NOT NULL THEN k.ddblokkeerdatum
           WHEN a.dnkeymilinrichtingen IS NOT NULL THEN l.ddblokkering
           ELSE NULL::timestamp without time zone
       END AS ddblokkeerdatum,
       CASE
           WHEN a.dnkeyomgvergunningen IS NOT NULL THEN
           CASE
               WHEN e.ddonherroepelijk IS NOT NULL THEN e.ddonherroepelijk
               WHEN e.ddvernietigd IS NOT NULL THEN e.ddvernietigd
               WHEN e.ddgeschorstvanaf IS NOT NULL THEN e.ddgeschorstvanaf
               WHEN e.ddnaverlingetrokken <= fn_vandaag(0) THEN e.ddnaverlingetrokken
               WHEN e.ddverlopen <= fn_vandaag(0) THEN e.ddverlopen
               WHEN e.ddvervallen < fn_vandaag(0) THEN e.ddvervallen
               WHEN e.ddbesluitdatum IS NOT NULL THEN e.ddbesluitdatum
               WHEN e.ddingetrokken IS NOT NULL THEN e.ddingetrokken
               WHEN e.ddaanhoudenvanaf IS NOT NULL AND e.ddaanhoudentotmet IS
                   NULL THEN e.ddaanhoudenvanaf
               ELSE NULL::timestamp without time zone
           END
           WHEN a.dnkeybouwvergunningen IS NOT NULL THEN
           CASE
               WHEN f.ddnaverlingetrokken <= fn_vandaag(0) THEN f.ddnaverlingetrokken
               WHEN f.ddgeldigtotmet < fn_vandaag(0) THEN f.ddgeldigtotmet
               WHEN f.ddvervallen <= fn_vandaag(0) THEN f.ddvervallen
               WHEN f.ddbesluitdatum IS NOT NULL THEN f.ddbesluitdatum
               WHEN f.ddingetrokken IS NOT NULL THEN f.ddingetrokken
               ELSE NULL::timestamp without time zone
           END
           WHEN a.dnkeyovvergunningen IS NOT NULL THEN
           CASE
               WHEN g.ddonherroepelijk IS NOT NULL THEN g.ddonherroepelijk
               WHEN g.ddvernietigd IS NOT NULL THEN g.ddvernietigd
               WHEN g.ddgeschorstvanaf IS NOT NULL THEN g.ddgeschorstvanaf
               WHEN g.ddnaverlingetrokken <= fn_vandaag(0) THEN g.ddnaverlingetrokken
               WHEN g.ddgeldigtotmet < fn_vandaag(0) THEN g.ddgeldigtotmet
               WHEN g.ddvervallen <= fn_vandaag(0) THEN g.ddvervallen
               WHEN g.ddbesluitdatum IS NOT NULL THEN g.ddbesluitdatum
               WHEN g.ddingetrokken IS NOT NULL THEN g.ddingetrokken
               ELSE NULL::timestamp without time zone
           END
           WHEN a.dnkeyhandhavingen IS NOT NULL THEN
           CASE
               WHEN h.ddonherroepelijk IS NOT NULL THEN h.ddonherroepelijk
               WHEN h.ddvernietigd IS NOT NULL THEN h.ddvernietigd
               WHEN h.ddgeschorstvanaf IS NOT NULL THEN h.ddgeschorstvanaf
               WHEN h.ddeinddatum IS NOT NULL THEN h.ddeinddatum
               ELSE NULL::timestamp without time zone
           END
           WHEN a.dnkeyhorecavergunningen IS NOT NULL THEN
           CASE
               WHEN i.ddnaverlingetrokken <= fn_vandaag(0) THEN i.ddnaverlingetrokken
               WHEN i.ddverlopen < fn_vandaag(0) THEN i.ddverlopen
               WHEN i.ddvervaldatum <= fn_vandaag(0) THEN i.ddvervaldatum
               WHEN i.dddatumbesluit IS NOT NULL THEN i.dddatumbesluit
               WHEN i.ddingetrokken IS NOT NULL THEN i.ddingetrokken
               ELSE NULL::timestamp without time zone
           END
           WHEN a.dnkeymilvergunningen IS NOT NULL THEN
           CASE
               WHEN j.ddnaverlingetrokken <= fn_vandaag(0) THEN j.ddnaverlingetrokken
               WHEN j.ddgeldigtotmet < fn_vandaag(0) THEN j.ddgeldigtotmet
               WHEN j.ddvervallen <= fn_vandaag(0) THEN j.ddvervallen
               WHEN j.ddbesluitdatum IS NOT NULL THEN j.ddbesluitdatum
               WHEN j.ddingetrokken IS NOT NULL THEN j.ddingetrokken
               ELSE NULL::timestamp without time zone
           END
           WHEN a.dnkeyinfoaanvraag IS NOT NULL THEN
           CASE
               WHEN k.ddafgehandeld IS NOT NULL THEN k.ddafgehandeld
               ELSE NULL::timestamp without time zone
           END
           WHEN a.dnkeymilinrichtingen IS NOT NULL THEN
           CASE
               WHEN l.ddbedrijfeinde IS NOT NULL THEN l.ddbedrijfeinde
               ELSE NULL::timestamp without time zone
           END
           ELSE NULL::timestamp without time zone
       END AS ddhoofdzaakgesloten,
       CASE
           WHEN a.dnkeyadviezen IS NOT NULL THEN
           CASE
               WHEN n.ddadviesdatum IS NOT NULL THEN n.ddadviesdatum
               ELSE NULL::timestamp without time zone
           END
           WHEN a.dnkeyinspecties IS NOT NULL THEN
           CASE
               WHEN m.ddcontrole IS NOT NULL THEN m.ddcontrole
               ELSE NULL::timestamp without time zone
           END
           WHEN a.dnkeybezwaarberoep IS NOT NULL THEN
           CASE
               WHEN o.dduitspraak IS NOT NULL THEN o.dduitspraak
               ELSE NULL::timestamp without time zone
           END
           ELSE NULL::timestamp without time zone
       END AS dddeelzaakgesloten
FROM tburen a
    JOIN tbsoorturen b ON a.dnkeysoorturen = b.dnkey
    LEFT JOIN tbmedewerkers c ON a.dvcodeurengemaaktdoor = c.dvcode
    LEFT JOIN tbmedewerkers d ON a.dvcodelaatstemuteerder = d.dvcode
    LEFT JOIN tbomgvergunning e ON a.dnkeyomgvergunningen = e.dnkey
    LEFT JOIN tbbouwvergunningen f ON a.dnkeybouwvergunningen = f.dnkey
    LEFT JOIN tbovvergunningen g ON a.dnkeyovvergunningen = g.dnkey
    LEFT JOIN tbhandhavingen h ON a.dnkeyhandhavingen = h.dnkey
    LEFT JOIN tbhorecavergunningen i ON a.dnkeyhorecavergunningen = i.dnkey
    LEFT JOIN tbmilvergunningen j ON a.dnkeymilvergunningen = j.dnkey
    LEFT JOIN tbinfoaanvragen k ON a.dnkeyinfoaanvraag = k.dnkey
    LEFT JOIN tbmilinrichtingen l ON a.dnkeymilinrichtingen = l.dnkey
    LEFT JOIN tbinspecties m ON a.dnkeyinspecties = m.dnkey
    LEFT JOIN tbadviezen n ON a.dnkeyadviezen = n.dnkey
    LEFT JOIN tbbezwaarberoep o ON a.dnkeybezwaarberoep = o.dnkey
    LEFT JOIN tbsoortomgverg srtw ON e.dnkeysoortomgverg = srtw.dnkey
    LEFT JOIN tbsoortplan srtb ON f.dnsoortplan = srtb.dnkey
    LEFT JOIN tbsoortovverg srto ON g.dvcodesoortovverg = srto.dvcode
    LEFT JOIN tbsoorthhzaak srth ON h.dnkeysoorthhzaak = srth.dnkey
    LEFT JOIN tbsoorthorverg srtc ON i.dnkeysoortverg = srtc.dnkey
    LEFT JOIN tbsoortmilverg srtm ON j.dnkeysoortmilverg = srtm.dnkey
    LEFT JOIN tbsoortinfoaanvraag srti ON k.dnkeysoortinfoaanvraag = srti.dnkey
    LEFT JOIN tbinspaanleiding inspo ON m.dnkeyinspaanleiding = inspo.dnkey
    LEFT JOIN tbadviesinstanties advo ON n.dvcodeadviesinstanties = advo.dvcode
    LEFT JOIN tbsoortbezwaar bezo ON o.dnkeysoortbezwaar = bezo.dnkey
    LEFT JOIN (
   SELECT
               CASE
                   WHEN b_1.dnkeyomgvergunningen IS NOT NULL THEN e_1.dvzaakcode
                   WHEN b_1.dnkeybouwvergunningen IS NOT NULL THEN f_1.dvbouwnr
                   WHEN b_1.dnkeyovvergunningen IS NOT NULL THEN g_1.dvvergunningsnr
                   WHEN b_1.dnkeyhandhavingen IS NOT NULL THEN h_1.dvaanschrijfnr
                   WHEN b_1.dnkeyhorecavergunningen IS NOT NULL THEN i_1.dvvergnummer
                   WHEN b_1.dnkeymilvergunningen IS NOT NULL THEN j_1.dvvergnummer
                   WHEN b_1.dnkeyinfoaanvraag IS NOT NULL THEN k_1.dvinfonummer
                   ELSE l_1.dvinrichtingnr
               END AS dvwavehoofdzaakcode,
           sum(b_1.dfaantaluur) AS totaal_uren
   FROM tburen b_1
            LEFT JOIN tbomgvergunning e_1 ON b_1.dnkeyomgvergunningen = e_1.dnkey
            LEFT JOIN tbbouwvergunningen f_1 ON b_1.dnkeybouwvergunningen = f_1.dnkey
            LEFT JOIN tbovvergunningen g_1 ON b_1.dnkeyovvergunningen = g_1.dnkey
            LEFT JOIN tbhandhavingen h_1 ON b_1.dnkeyhandhavingen = h_1.dnkey
            LEFT JOIN tbhorecavergunningen i_1 ON b_1.dnkeyhorecavergunningen
                = i_1.dnkey
            LEFT JOIN tbmilvergunningen j_1 ON b_1.dnkeymilvergunningen = j_1.dnkey
            LEFT JOIN tbinfoaanvragen k_1 ON b_1.dnkeyinfoaanvraag = k_1.dnkey
            LEFT JOIN tbmilinrichtingen l_1 ON b_1.dnkeymilinrichtingen = l_1.dnkey
   GROUP BY (
               CASE
                   WHEN b_1.dnkeyomgvergunningen IS NOT NULL THEN e_1.dvzaakcode
                   WHEN b_1.dnkeybouwvergunningen IS NOT NULL THEN f_1.dvbouwnr
                   WHEN b_1.dnkeyovvergunningen IS NOT NULL THEN g_1.dvvergunningsnr
                   WHEN b_1.dnkeyhandhavingen IS NOT NULL THEN h_1.dvaanschrijfnr
                   WHEN b_1.dnkeyhorecavergunningen IS NOT NULL THEN i_1.dvvergnummer
                   WHEN b_1.dnkeymilvergunningen IS NOT NULL THEN j_1.dvvergnummer
                   WHEN b_1.dnkeyinfoaanvraag IS NOT NULL THEN k_1.dvinfonummer
                   ELSE l_1.dvinrichtingnr
               END)
   ) uren_per_zaak ON
       CASE
           WHEN a.dnkeyomgvergunningen IS NOT NULL THEN e.dvzaakcode
           WHEN a.dnkeybouwvergunningen IS NOT NULL THEN f.dvbouwnr
           WHEN a.dnkeyovvergunningen IS NOT NULL THEN g.dvvergunningsnr
           WHEN a.dnkeyhandhavingen IS NOT NULL THEN h.dvaanschrijfnr
           WHEN a.dnkeyhorecavergunningen IS NOT NULL THEN i.dvvergnummer
           WHEN a.dnkeymilvergunningen IS NOT NULL THEN j.dvvergnummer
           WHEN a.dnkeyinfoaanvraag IS NOT NULL THEN k.dvinfonummer
           ELSE l.dvinrichtingnr
       END::text = uren_per_zaak.dvwavehoofdzaakcode::text
    LEFT JOIN (
   SELECT b_1.dvcodeurengemaaktdoor,
               CASE
                   WHEN b_1.dnkeyomgvergunningen IS NOT NULL THEN e_1.dvzaakcode
                   WHEN b_1.dnkeybouwvergunningen IS NOT NULL THEN f_1.dvbouwnr
                   WHEN b_1.dnkeyovvergunningen IS NOT NULL THEN g_1.dvvergunningsnr
                   WHEN b_1.dnkeyhandhavingen IS NOT NULL THEN h_1.dvaanschrijfnr
                   WHEN b_1.dnkeyhorecavergunningen IS NOT NULL THEN i_1.dvvergnummer
                   WHEN b_1.dnkeymilvergunningen IS NOT NULL THEN j_1.dvvergnummer
                   WHEN b_1.dnkeyinfoaanvraag IS NOT NULL THEN k_1.dvinfonummer
                   ELSE l_1.dvinrichtingnr
               END AS dvwavehoofdzaakcode,
           sum(b_1.dfaantaluur) AS totaal_uren
   FROM tburen b_1
            LEFT JOIN tbomgvergunning e_1 ON b_1.dnkeyomgvergunningen = e_1.dnkey
            LEFT JOIN tbbouwvergunningen f_1 ON b_1.dnkeybouwvergunningen = f_1.dnkey
            LEFT JOIN tbovvergunningen g_1 ON b_1.dnkeyovvergunningen = g_1.dnkey
            LEFT JOIN tbhandhavingen h_1 ON b_1.dnkeyhandhavingen = h_1.dnkey
            LEFT JOIN tbhorecavergunningen i_1 ON b_1.dnkeyhorecavergunningen
                = i_1.dnkey
            LEFT JOIN tbmilvergunningen j_1 ON b_1.dnkeymilvergunningen = j_1.dnkey
            LEFT JOIN tbinfoaanvragen k_1 ON b_1.dnkeyinfoaanvraag = k_1.dnkey
            LEFT JOIN tbmilinrichtingen l_1 ON b_1.dnkeymilinrichtingen = l_1.dnkey
   GROUP BY b_1.dvcodeurengemaaktdoor, (
               CASE
                   WHEN b_1.dnkeyomgvergunningen IS NOT NULL THEN e_1.dvzaakcode
                   WHEN b_1.dnkeybouwvergunningen IS NOT NULL THEN f_1.dvbouwnr
                   WHEN b_1.dnkeyovvergunningen IS NOT NULL THEN g_1.dvvergunningsnr
                   WHEN b_1.dnkeyhandhavingen IS NOT NULL THEN h_1.dvaanschrijfnr
                   WHEN b_1.dnkeyhorecavergunningen IS NOT NULL THEN i_1.dvvergnummer
                   WHEN b_1.dnkeymilvergunningen IS NOT NULL THEN j_1.dvvergnummer
                   WHEN b_1.dnkeyinfoaanvraag IS NOT NULL THEN k_1.dvinfonummer
                   ELSE l_1.dvinrichtingnr
               END)
   ) uren_per_zaak_medewerker ON
       CASE
           WHEN a.dnkeyomgvergunningen IS NOT NULL THEN e.dvzaakcode
           WHEN a.dnkeybouwvergunningen IS NOT NULL THEN f.dvbouwnr
           WHEN a.dnkeyovvergunningen IS NOT NULL THEN g.dvvergunningsnr
           WHEN a.dnkeyhandhavingen IS NOT NULL THEN h.dvaanschrijfnr
           WHEN a.dnkeyhorecavergunningen IS NOT NULL THEN i.dvvergnummer
           WHEN a.dnkeymilvergunningen IS NOT NULL THEN j.dvvergnummer
           WHEN a.dnkeyinfoaanvraag IS NOT NULL THEN k.dvinfonummer
           ELSE l.dvinrichtingnr
       END::text = uren_per_zaak_medewerker.dvwavehoofdzaakcode::text AND
           a.dvcodeurengemaaktdoor = uren_per_zaak_medewerker.dvcodeurengemaaktdoor
    LEFT JOIN tbperceeladressen perc ON perc.dnkey =
       CASE
           WHEN a.dnkeyomgvergunningen IS NOT NULL THEN e.dnkeyperceeladressen
           WHEN a.dnkeybouwvergunningen IS NOT NULL THEN f.dnkeyperceeladressen
           WHEN a.dnkeyovvergunningen IS NOT NULL THEN g.dnkeyperceeladressen
           WHEN a.dnkeyhandhavingen IS NOT NULL THEN h.dnkeyperceeladressen
           WHEN a.dnkeyhorecavergunningen IS NOT NULL THEN i.dnkeyperceeladressen
           WHEN a.dnkeymilvergunningen IS NOT NULL THEN l.dnkeyperceeladressen
           WHEN a.dnkeyinfoaanvraag IS NOT NULL THEN k.dnkeyperceeladressen
           ELSE l.dnkeyperceeladressen
       END
    LEFT JOIN tbopenbareruimte openb ON perc.dnkeyopenbruimte = openb.dnkey
    LEFT JOIN tbwoonplaats tbw ON openb.dnkeywoonplaats = tbw.dnkey
    LEFT JOIN vwfrmcubegemcompsrtomgverg gemcompomg ON e.dnkeysoortomgverg =
        gemcompomg.dnkeysoortomgverg AND gemcompomg.dvgemeenteid::text = tbw.dvwoonplaatsid::text
    LEFT JOIN tbcompartiment comp_omg ON gemcompomg.dnkeycompartiment = comp_omg.dnkey
    LEFT JOIN tbcompartiment overrulecomp_omg ON e.dnkeycompoverrule =
        overrulecomp_omg.dnkey
    LEFT JOIN vwfrmcubegemcompsrtinfoav gemcompinf ON k.dnkeysoortinfoaanvraag
        = gemcompinf.dnkeysoortinfoaanvraag AND gemcompinf.dvgemeenteid::text = tbw.dvwoonplaatsid::text
    LEFT JOIN tbcompartiment comp_info ON gemcompinf.dnkeycompartiment =
        comp_info.dnkey
    LEFT JOIN tbcompartiment overrulecomp_info ON k.dnkeycompoverrule =
        overrulecomp_info.dnkey
    LEFT JOIN vwfrmcubegemcompsrtbwvverg gemcompbouw ON f.dnsoortplan =
        gemcompbouw.dnkeysoortplan AND gemcompbouw.dvgemeenteid::text = tbw.dvwoonplaatsid::text
    LEFT JOIN tbcompartiment comp_bouw ON gemcompbouw.dnkeycompartiment =
        comp_bouw.dnkey
    LEFT JOIN vwfrmcubegemcompsrthhzaak gemcomphh ON h.dnkeysoorthhzaak =
        gemcomphh.dnkeysoorthhzaak AND gemcomphh.dvgemeenteid::text = tbw.dvwoonplaatsid::text
    LEFT JOIN tbcompartiment comp_hand ON gemcomphh.dnkeycompartiment = comp_hand.dnkey
    LEFT JOIN tbcompartiment overrulecomp_hand ON h.dnkeycompoverrule =
        overrulecomp_hand.dnkey
    LEFT JOIN vwfrmcubegemcompsrtovvverg gemcompov ON g.dvcodesoortovverg =
        gemcompov.dvcodesoortovverg AND gemcompov.dvgemeenteid::text = tbw.dvwoonplaatsid::text
    LEFT JOIN tbcompartiment comp_apv ON gemcompov.dnkeycompartiment = comp_apv.dnkey
    LEFT JOIN tbcompartiment overrulecomp_apv ON g.dnkeycompoverrule =
        overrulecomp_apv.dnkey
    LEFT JOIN vwfrmcubegemcompsrthorverg gemcomphor ON i.dnkeysoortverg =
        gemcomphor.dnkeysoorthorverg AND gemcomphor.dvgemeenteid::text = tbw.dvwoonplaatsid::text
    LEFT JOIN tbcompartiment comp_hor ON gemcomphor.dnkeycompartiment = comp_hor.dnkey
    LEFT JOIN tbcompartiment overrulecomp_hor ON i.dnkeycompoverrule =
        overrulecomp_hor.dnkey
    LEFT JOIN vwfrmcubegemcompsrtmilverg gemcompmil ON j.dnkeysoortmilverg =
        gemcompmil.dnkeysoortmilverg AND gemcompmil.dvgemeenteid::text = tbw.dvwoonplaatsid::text
    LEFT JOIN tbcompartiment comp_mil ON gemcompmil.dnkeycompartiment = comp_mil.dnkey
    LEFT JOIN tbcompartiment overrulecomp_mil ON j.dnkeycompoverrule =
        overrulecomp_mil.dnkey
    LEFT JOIN vwfrmcubegemcompsrtmilinr gemcompinr ON l.dnkeymilbedrijfsoort =
        gemcompinr.dnkeymilbedrijfsoort AND gemcompinr.dvgemeenteid::text = tbw.dvwoonplaatsid::text
    LEFT JOIN tbcompartiment comp_inr ON gemcompinr.dnkeycompartiment = comp_inr.dnkey;

COMMENT ON COLUMN public.vwfrmuren.dnkey
IS 'Primary key';

COMMENT ON COLUMN public.vwfrmuren.dnkeysoorturen
IS 'Foreign key naar tbsoorturen.dnkey';

COMMENT ON COLUMN public.vwfrmuren.dvomsoorturen
IS 'Omschrijving van de soort uren';

COMMENT ON COLUMN public.vwfrmuren.dnkeyinspecties
IS 'Foreign key naar tbinspecties.dnkey';

COMMENT ON COLUMN public.vwfrmuren.dnkeyadviezen
IS 'Foreign key naar tbadviezen.dnkey';

COMMENT ON COLUMN public.vwfrmuren.dnkeybezwaarberoep
IS 'Foreign key naar tbbezwaarberoep.dnkey';

COMMENT ON COLUMN public.vwfrmuren.dnkeyomgvergunningen
IS 'Foreign key naar tbomgvergunning.dnkey';

COMMENT ON COLUMN public.vwfrmuren.dnkeybouwvergunningen
IS 'Foreign key naar tbbouwvergunningen.dnkey';

COMMENT ON COLUMN public.vwfrmuren.dnkeyovvergunningen
IS 'Foreign key naar tbovvergunningen.dnkey';

COMMENT ON COLUMN public.vwfrmuren.dnkeyhandhavingen
IS 'Foreign key naar tbhandhavingen.dnkey';

COMMENT ON COLUMN public.vwfrmuren.dnkeymilvergunningen
IS 'Foreign key naar tbmilvergunningen.dnkey';

COMMENT ON COLUMN public.vwfrmuren.dnkeymilinrichtingen
IS 'Foreign key naar tbmilinrichtingen.dnkey';

COMMENT ON COLUMN public.vwfrmuren.dnkeyinfoaanvraag
IS 'Foreign key naar tbinfoaanvragen.dnkey';

COMMENT ON COLUMN public.vwfrmuren.dnkeyhorecavergunningen
IS 'Foreign key naar tbhorecavergunningen.dnkey';

COMMENT ON COLUMN public.vwfrmuren.dfaantaluur
IS 'Aantal uren gemaakt op ddurendatum (mag groter zijn dan 24, maar kleiner dan 100)';

COMMENT ON COLUMN public.vwfrmuren.dvaantaluur
IS 'dfaantaluren uitgedrukt in formaat HH:MM';

COMMENT ON COLUMN public.vwfrmuren.ddurendatum
IS 'Datum dat uren gemaakt zijn';

COMMENT ON COLUMN public.vwfrmuren.dvtoelichting
IS 'Toelichting';

COMMENT ON COLUMN public.vwfrmuren.ddgefactureerd
IS 'Datum dat uren klaargezet zijn tot facturatie';

COMMENT ON COLUMN public.vwfrmuren.dlopdrachtgeverakkoord
IS 'T, F of N. N = Nog niet bekend';

COMMENT ON COLUMN public.vwfrmuren.dvkleurakkoord
IS 'Wit indien dlopdrachtgeverakkoord = N, rood indien F anders groen';

COMMENT ON COLUMN public.vwfrmuren.dvcodeurengemaaktdoor
IS 'Foreign key naar tbmedewerkers.dvcode voor degene die de uren gemakt heeft';

COMMENT ON COLUMN public.vwfrmuren.dvurengemaaktdoorvoluit
IS 'Voorletters, tussenvoegsel en achternaam van dvcodeurengemaaktdoor';

COMMENT ON COLUMN public.vwfrmuren.dvcodelaatstemuteerder
IS 'Foreign key naar tbmedewerkers.dvcode voor degene die als laatste de urenkaart heeft overschreven';

COMMENT ON COLUMN public.vwfrmuren.dvlaatstemuteerdervoluit
IS 'Voorletters, tussenvoegsel en achternaam van dvcodelaatstemuteerder';

COMMENT ON COLUMN public.vwfrmuren.dvgemeenteid
IS 'Gemeenteid (tabel33) van lokatie waar de zaak/inrichting speelt';

COMMENT ON COLUMN public.vwfrmuren.dvwavehoofdzaakcode
IS 'De wave hoofdzaakcode danwel inrichtngsnummer van de hoofdzaak waarop uren zijn geschreven';

COMMENT ON COLUMN public.vwfrmuren.dvhoofdzaaktypeoms
IS 'Zaaktypeomschrijving danwel inrichtingsnaam van de hoofdzaak waarop uren zijn geschreven';

COMMENT ON COLUMN public.vwfrmuren.dvwavedeelzaakcode
IS 'De wave deelzaakcode van de deelzaak (inspectie, advies, bezwaar/beroep) waarop uren zijn geschreven';

COMMENT ON COLUMN public.vwfrmuren.dvzaakomschrijving
IS 'Mits dvwavedeelzaakcode gevuld is dan omschrijving van de deelzaak (inspectie, advies, bezwaar/beroep) waarop uren zijn geschreven, anders de Zaakomschrijving dan wel de inrichtingsnaam';

COMMENT ON COLUMN public.vwfrmuren.dvdeelzaaktypeoms
IS 'Deelzaaktypeomschrijving van de deelzaak (inspectie, advies, bezwaar/beroep) waarop uren zijn geschreven';

COMMENT ON COLUMN public.vwfrmuren.dfaantalmedzaakuur
IS 'De hoeveelheid uren die gemaakt zijn bij de hoofdzaak door de medewerker van dvcodeurengemaaktdoor';

COMMENT ON COLUMN public.vwfrmuren.dfaantalzaakuur
IS 'De totale hoeveelheid uren die gemaakt zijn bij de hoofdzaak.';

COMMENT ON COLUMN public.vwfrmuren.dnkeycompartiment
IS 'Dnkey van tbcompartiment waar geschreven uren onder valt';

COMMENT ON COLUMN public.vwfrmuren.dlurenopdrgzb
IS 'T of F. T dan is de kolom in tburen opdrachtgever akkoord zichtbaar voor het bovenliggend zaaktype';

COMMENT ON COLUMN public.vwfrmuren.ddblokkeerdatum
IS 'De datum dat hoofdzaak of inrichting geblokkeerd is';

COMMENT ON COLUMN public.vwfrmuren.ddhoofdzaakgesloten
IS 'De datum dat een hoofdzaak of inrichting gesloten is';

COMMENT ON COLUMN public.vwfrmuren.dddeelzaakgesloten
IS 'De datum dat een deelzaak gesloten is';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 2-5-2025 13:49