Datadictionary OpenWave 3.1.34

View: vwzaaktoegangmw

Schema

public

Owner

waveuser005

Descriptions

View van alle zaken met alle medewerkers die toegang hebben tot de zaak. Als een zaak géén beperkte toegang heeft, staat de zaak wel genoemd maar dan zonder medewerker. Deze view wordt gebruikt om de toegang te regelen tot de zaak

Columns

Rules

Indices

There are no indices for table vwzaaktoegangmw

Definition

CREATE VIEW public.vwzaaktoegangmw (
   dnkeyomgvergunningen,
   dvcodemw,
   dvredentoegang)
AS
WITH bt AS (
SELECT omgverg.dnkey,
           omgverg.dvcodeaccountman,
           omgverg.dnkeyteamszaakverantw
FROM tbomgvergunning omgverg
WHERE omgverg.dlbeperktetoegang = 'T'::bpchar
       )
   SELECT ibb.dnkeyomgvergunningen,
   ibb.dvcodemedewerker AS dvcodemw,
   'Zaak behandelaar'::text AS dvredentoegang
   FROM tbinbehandelingbij ibb
    JOIN bt ON ibb.dnkeyomgvergunningen = bt.dnkey
   WHERE ibb.dlactief = 'T'::bpchar
   UNION ALL
   SELECT bt.dnkey AS dnkeyomgvergunningen,
   bt.dvcodeaccountman AS dvcodemw,
   'Zaak verantwoordelijke'::text AS dvredentoegang
   FROM bt
   WHERE bt.dvcodeaccountman IS NOT NULL
   UNION ALL
   SELECT bt.dnkey AS dnkeyomgvergunningen,
   t1.dvcodeteamleider AS dvcodemw,
   'Zaakverantwoordelijk teamleider '::text || t1.dvteamnaam::text AS dvredentoegang
   FROM bt
    JOIN tbteams t1 ON t1.dnkey = bt.dnkeyteamszaakverantw
   WHERE t1.dvcodeteamleider IS NOT NULL
   UNION ALL
   SELECT adv.dnkeyomgvergunningen,
   adv.dvcodeadviseur AS dvcodemw,
   'Advies'::text AS dvredentoegang
   FROM tbadviezen adv
    JOIN bt ON adv.dnkeyomgvergunningen = bt.dnkey
   WHERE adv.dvcodeadviseur IS NOT NULL
   UNION ALL
   SELECT adv.dnkeyomgvergunningen,
   medewisadv.dvcodemedewerkers AS dvcodemw,
   'Advies instantie '::text || ai.dvomschrijving::text AS dvredentoegang
   FROM tbadviezen adv
    JOIN bt ON bt.dnkey = adv.dnkeyomgvergunningen
    JOIN tbmedewisadviseurvoor medewisadv ON medewisadv.dvcodeadviesinstanties
        = adv.dvcodeadviesinstanties
    JOIN tbadviesinstanties ai ON ai.dvcode = medewisadv.dvcodeadviesinstanties
   WHERE adv.dvcodeadviseur IS NULL AND adv.dnkeyteamsverantw IS NULL
   UNION ALL
   SELECT adv.dnkeyomgvergunningen,
   t.dvcodeteamleider AS dvcodemw,
   'Advies teamleider '::text || t.dvteamnaam::text AS dvredentoegang
   FROM tbadviezen adv
    JOIN bt ON adv.dnkeyomgvergunningen = bt.dnkey
    JOIN tbteams t ON t.dnkey = adv.dnkeyteamsverantw
   WHERE t.dvcodeteamleider IS NOT NULL AND adv.dvcodeadviseur IS NULL
   UNION ALL
   SELECT c.dnkeyomgvergunningen,
   cct.dvcodemwvoorwie AS dvcodemw,
   'Collegiale toets'::text AS dvredentoegang
   FROM tbcorrespcollegtoets cct
    JOIN tbcorrespondentie c ON c.dnkey = cct.dnkeycorrespondentie
    JOIN bt ON c.dnkeyomgvergunningen = bt.dnkey
   WHERE cct.dvcodemwvoorwie IS NOT NULL
   UNION ALL
   SELECT c.dnkeyomgvergunningen,
   t.dvcodeteamleider AS dvcodemw,
   'Collegiale toets teamleider '::text || t.dvteamnaam::text AS dvredentoegang
   FROM tbcorrespcollegtoets cct
    JOIN tbcorrespondentie c ON c.dnkey = cct.dnkeycorrespondentie
    JOIN bt ON c.dnkeyomgvergunningen = bt.dnkey
    JOIN tbteams t ON t.dnkey = cct.dnkeyteamsverantw
   WHERE t.dvcodeteamleider IS NOT NULL AND cct.dvcodemwvoorwie IS NULL
   UNION ALL
   SELECT insp.dnkeyomgvergunningen,
   ib.dvcodemedewerkers AS dvcodemw,
   'Inspectie bezoek'::text AS dvredentoegang
   FROM tbinspbezoeken ib
    JOIN tbinspecties insp ON insp.dnkey = ib.dnkeyinspecties
    JOIN bt ON insp.dnkeyomgvergunningen = bt.dnkey
   WHERE ib.dvcodemedewerkers IS NOT NULL
   UNION ALL
   SELECT insp.dnkeyomgvergunningen,
   insp.dvcodemedewerkers AS dvcodemw,
   'Inspectie traject'::text AS dvredentoegang
   FROM tbinspecties insp
    JOIN bt ON insp.dnkeyomgvergunningen = bt.dnkey
   UNION ALL
   SELECT bb.dnkeyomgvergunningen,
   bb.dvcodejurist AS dvcodemw,
   'Bezwaar/beroep'::text AS dvredentoegang
   FROM tbbezwaarberoep bb
    JOIN bt ON bb.dnkeyomgvergunningen = bt.dnkey
   WHERE bb.dvcodejurist IS NOT NULL
   UNION ALL
   SELECT tbs.dnkeyomgvergunningen,
   tbs.dvcodevoorwie AS dvcodemw,
   'Processtap'::text AS dvredentoegang
   FROM tbtermijnbewstappen tbs
    JOIN bt ON tbs.dnkeyomgvergunningen = bt.dnkey
   WHERE tbs.dvcodevoorwie IS NOT NULL
   UNION ALL
   SELECT tbs.dnkeyomgvergunningen,
   mt.dvcodemw,
   'Processtap team '::text || t.dvteamnaam::text AS dvredentoegang
   FROM tbtermijnbewstappen tbs
    JOIN bt ON tbs.dnkeyomgvergunningen = bt.dnkey
    JOIN tbteams t ON t.dnkey = tbs.dnkeyteams
    JOIN tbmwteams mt ON mt.dnkeyteam = tbs.dnkeyteams
   UNION ALL
   SELECT ib.dnkeyomgvergunningen,
   ib.dvcodemedewerkers AS dvcodemw,
   'Intern betrokkene '::text || r.dvrolomschrijving::text AS dvredentoegang
   FROM tbomgvergintbetrokkenennn ib
    JOIN bt ON ib.dnkeyomgvergunningen = bt.dnkey
    JOIN tbinternerollen r ON r.dnkey = ib.dnkeyinternerol
   WHERE ib.dvcodemedewerkers IS NOT NULL
   UNION ALL
   SELECT ib.dnkeyomgvergunningen,
   mt.dvcodemw,
   (('Intern betrokkene '::text || r.dvrolomschrijving::text) ||
       ' team '::text) || t.dvteamnaam::text AS dvredentoegang
   FROM tbomgvergintbetrokkenennn ib
    JOIN bt ON ib.dnkeyomgvergunningen = bt.dnkey
    JOIN tbteams t ON t.dnkey = ib.dnkeyteams
    JOIN tbmwteams mt ON mt.dnkeyteam = ib.dnkeyteams
    JOIN tbinternerollen r ON r.dnkey = ib.dnkeyinternerol
   WHERE ib.dvcodemedewerkers IS NULL
   UNION ALL
   SELECT bt.dnkey AS dnkeyomgvergunningen,
   medew.dvcode AS dvcodemw,
   'Recht op alle BT-zaken'::text AS dvredentoegang
   FROM bt
    JOIN (
       SELECT tbmedewerkers.dvcode
       FROM tbmedewerkers
       WHERE tbmedewerkers.dlmagallebtzien = 'T'::bpchar
       ) medew ON 1 = 1
   WHERE medew.dvcode IS NOT NULL
   UNION ALL
   SELECT a.dnkey AS dnkeyomgvergunningen,
   NULL::character(5) AS dvcodemw,
   'Niet beperkt toegankelijk'::text AS dvredentoegang
   FROM tbomgvergunning a
   WHERE a.dlbeperktetoegang = 'F'::bpchar;

COMMENT ON VIEW public.vwzaaktoegangmw
IS 'View van alle zaken met alle medewerkers die toegang hebben tot de zaak. Als een zaak géén beperkte toegang heeft, staat de zaak wel genoemd maar dan zonder medewerker. Deze view wordt gebruikt om de toegang te regelen tot de zaak';

COMMENT ON COLUMN public.vwzaaktoegangmw.dnkeyomgvergunningen
IS 'Foreign key naar de dnkey van de onderliggende aanvraag/zaak (tbomgvergunning.dnkey)';

COMMENT ON COLUMN public.vwzaaktoegangmw.dvcodemw
IS 'Foreign key naar tbmedewerkers.dvcode';

COMMENT ON COLUMN public.vwzaaktoegangmw.dvredentoegang
IS 'Beschrijving waarom deze medewerker toegang zou moeten hebben. Bijvoorbeeld de medewerker is adviseur in een gevraagd advies';

This file was generated with SQL Manager for PostgreSQL (www.sqlmanager.net/products/postgresql/manager) at 18/05/2026 15:10