Datadictionary OpenWave 3.1.11
 Previous topic Chapter index Next topic

View: vwfrminrkinderen

 

 

Schema

public

 

Owner

waveuser005

 

Descriptions

View van alle aan een inrichting gerelateerde vergunningen/zaken . Basistabellen: tbomgvergunning, tbmilvergunningen, tbhorecavergunningen, tbhandhavingen, tbbouwvergunningen, tbovvergunningen

 

Fields

Name

Data type

Description

dvzaakmoduleletter

char(1)

W = Omgeving, E = Milieu/gebruik, C = Horeca, H = Handhaving, O = Overige, B = Bouw/sloop

dvzaakmoduleoms

varchar(37)

Omgevingsvergunning, Milieu- en gebruiksvergunning/melding ,Handhavingszaak, Horecavergunning, Overige vergunning of Bouw- en sloopvergunning

dnkeymilinrichtingen

integer

Primary key van de milieu/brandweer inrichting (tbmilinrichtingen.dnkey) waaraan de zaak is gelieerd

dvinrichtingnaam

varchar(100)

De naam van de milieu/brandweer inrichting waaraan de zaak is gelieerd

dnkeyzaak

integer

De primary key van de vergunningsaanvraag of handhavingszaak (tbomgvergunning.dnkey, tbmilvergunningen.dnkey, tbhandhavingen.dnkey, tbhorecavergunningen.dnkey, tbovvergunningen.dnkey of tbbouwvergunningen.dnkey)

dvzaakcode

varchar(20)

De zaakcode of vergunningsaanvraagnummer van de vergunningsaanvraag of handhavingszaak

dvzaakoms

varchar

De zaakomschrijving van de vergunningsaanvraag of handhavingszaak

ddzaakstartdatum

timestamp

De aanvraagdatum cq ontvangst cq verzoekdatum waarmee de zaak, vergunningsaanvraag is gestart

dvstatus

varchar(50)

De status van de zaak, vergunningsaanvraag (in behandeling, verleend, ingetrokken, niet meer geldig etc.)

 

Rules

Name

Event

Instead

Condition

Description

_RETURN

SELECT

 

 

 

Definition

CREATE VIEW public.vwfrminrkinderen (
   dvzaakmoduleletter,
   dvzaakmoduleoms,
   dnkeymilinrichtingen,
   dvinrichtingnaam,
   dnkeyzaak,
   dvzaakcode,
   dvzaakoms,
   ddzaakstartdatum,
   dvstatus)
AS
       (        (        (        (
SELECT 'W'::character(1) AS dvzaakmoduleletter,
                                           'Omgevingsvergunning'::character
                                               varying(37) AS dvzaakmoduleoms,
                                           a.dnkey AS dnkeymilinrichtingen,
                                           a.dvinrichtingnaam,
                                           b.dnkey AS dnkeyzaak, b.dvzaakcode,
                                           b.dvaanvraagnaam AS dvzaakoms,
                                           b.ddaanvraag AS ddzaakstartdatum,
                                               CASE
                                                   WHEN b.ddonherroepelijk IS
                                                       NOT NULL THEN c.dvomschrijving
                                                   WHEN b.ddvernietigd IS NOT
                                                       NULL THEN 'vernietigd'::character varying
                                                   WHEN b.ddgeschorstvanaf IS
                                                       NOT NULL THEN 'geschorst'::character varying
                                                   WHEN b.ddbesluitdatum IS
                                                       NOT NULL THEN c.dvomschrijving
                                                   WHEN b.ddingetrokken IS NOT
                                                       NULL THEN 'ingetrokken (aanvrager)'::character varying
                                                   WHEN b.ddaanhoudenvanaf IS
                                                       NOT NULL AND b.ddaanhoudentotmet IS NULL THEN 'aangehouden'::character varying
                                                   ELSE
                                                       'in behandeling'::character varying
                                               END::character varying(50) AS dvstatus
FROM tbaardbesluit c
                                     LEFT JOIN tbomgvergunning b ON
                                         b.dvcodeaardbesluit = c.dvcode
                                LEFT JOIN tbmilinrichtingen a ON
                                    b.dnkeymilinrichtingen = a.dnkey
UNION
SELECT 'E'::character(1) AS dvzaakmoduleletter,
                                           'Milieu- en gebruiksvergunning/melding'::character
                                               varying(37) AS dvzaakmoduleoms,
                                           a.dnkey AS dnkeymilinrichtingen,
                                           a.dvinrichtingnaam,
                                           b.dnkey AS dnkeyzaak,
                                           b.dvvergnummer AS dvzaakcode,
                                           (((d.dvomschrijving::text ||
                                               ''::text) || b.dvpublbouwwerk::text))::character varying(200) AS dvzaakoms,
                                           b.ddontvangstdatum AS ddzaakstartdatum,
                                               CASE
                                                   WHEN b.ddvervallen <
                                                       fn_vandaag(0) THEN 'vervallen'::character varying
                                                   WHEN b.ddgeldigtotmet <
                                                       fn_vandaag(0) THEN 'niet meer geldig'::character varying
                                                   WHEN b.ddbesluitdatum IS
                                                       NOT NULL THEN c.dvomschrijving
                                                   WHEN b.ddingetrokken IS NOT
                                                       NULL THEN 'ingetrokken (aanvrager)'::character varying
                                                   ELSE
                                                       'in behandeling'::character varying
                                               END::character varying(50) AS dvstatus
FROM tbaardbesluit c
                                     LEFT JOIN tbmilvergunningen b ON
                                         b.dvcodeaardbesluit = c.dvcode
                                LEFT JOIN tbmilinrichtingen a ON
                                    b.dnkeymilinrichtingen = a.dnkey
                           LEFT JOIN tbmilwerkz d ON b.dnkeymilwerkz = d.dnkey)
UNION
SELECT 'H'::character(1) AS dvzaakmoduleletter,
                                   'Handhavingszaak'::character varying(37) AS
                                       dvzaakmoduleoms,
                                   a.dnkey AS dnkeymilinrichtingen,
                                   a.dvinrichtingnaam, b.dnkey AS dnkeyzaak,
                                   b.dvaanschrijfnr AS dvzaakcode,
                                   d.dvomschrijving AS dvzaakoms,
                                   b.ddverzoekdatum AS ddzaakstartdatum,
                                       CASE
                                           WHEN b.ddeinddatum IS NOT NULL THEN
                                               'afgehandeld'::text
                                           ELSE 'in behandeling'::text
                                       END::character varying(50) AS dvstatus
FROM tbhandhavingen b
                             LEFT JOIN tbmilinrichtingen a ON
                                 b.dnkeymilinrichtingen = a.dnkey
                        LEFT JOIN tbhandhovertreding d ON
                            b.dnkeyhandhovertreding = d.dnkey)
UNION
SELECT 'C'::character(1) AS dvzaakmoduleletter,
                           'Horecavergunning'::character varying(37) AS
                               dvzaakmoduleoms,
                           a.dnkey AS dnkeymilinrichtingen, a.dvinrichtingnaam,
                           b.dnkey AS dnkeyzaak, b.dvvergnummer AS dvzaakcode,
                           d.dvomschrijving AS dvzaakoms,
                           b.ddaanvraagdatum AS ddzaakstartdatum,
                               CASE
                                   WHEN b.ddvervaldatum >= fn_vandaag(0) THEN
                                       'verlopen'::character varying
                                   WHEN b.dddatumbesluit IS NOT NULL THEN
                                       c.dvomschrijving
                                   WHEN b.ddingetrokken IS NOT NULL THEN
                                       'ingetrokken (aanvrager)'::character varying
                                   ELSE 'in behandeling'::character varying
                               END::character varying(50) AS dvstatus
FROM tbhoraardbesluit c
                     LEFT JOIN tbhorecavergunningen b ON b.dvcodeaardbesluit = c.dvcode
                LEFT JOIN tbmilinrichtingen a ON b.dnkeymilinrichtingen = a.dnkey
           LEFT JOIN tbsoorthorverg d ON b.dnkeysoortverg = d.dnkey)
UNION
SELECT 'O'::character(1) AS dvzaakmoduleletter,
                   'Overige vergunning'::character varying(37) AS dvzaakmoduleoms,
                   a.dnkey AS dnkeymilinrichtingen, a.dvinrichtingnaam,
                   b.dnkey AS dnkeyzaak, b.dvvergunningsnr AS dvzaakcode,
                   (((d.dvomschrijving::text || ''::text) ||
                       b.dvpublbouwwerk::text))::character varying(200) AS dvzaakoms,
                   b.ddontvangstdatum AS ddzaakstartdatum,
                       CASE
                           WHEN b.ddgeldigtotmet < fn_vandaag(0) THEN
                               'niet meer geldig'::character varying
                           WHEN b.ddbesluitdatum IS NOT NULL THEN c.dvomschrijving
                           WHEN b.ddingetrokken IS NOT NULL THEN
                               'ingetrokken (aanvrager)'::character varying
                           ELSE 'in behandeling'::character varying
                       END::character varying(50) AS dvstatus
FROM tbaardbesluit c
             LEFT JOIN tbovvergunningen b ON b.dvcodeaardbesluit = c.dvcode
        LEFT JOIN tbmilinrichtingen a ON b.dnkeymilinrichtingen = a.dnkey
   LEFT JOIN tbovwerkz d ON b.dnkeyovwerkz = d.dnkey)
UNION
SELECT 'B'::character(1) AS dvzaakmoduleletter,
           'Bouw- en sloopvergunning'::character varying(37) AS dvzaakmoduleoms,
           a.dnkey AS dnkeymilinrichtingen, a.dvinrichtingnaam,
           b.dnkey AS dnkeyzaak, b.dvbouwnr AS dvzaakcode,
           (((d.dvomschrijving::text || ''::text) ||
               b.dvpublbouwwerk::text))::character varying(200) AS dvzaakoms,
           b.ddontvangstdatum AS ddzaakstartdatum,
               CASE
                   WHEN b.ddgeldigtotmet < fn_vandaag(0) THEN
                       'niet meer geldig'::character varying
                   WHEN b.ddbesluitdatum IS NOT NULL THEN c.dvomschrijving
                   WHEN b.ddingetrokken IS NOT NULL THEN
                       'ingetrokken (aanvrager)'::character varying
                   ELSE 'in behandeling'::character varying
               END::character varying(50) AS dvstatus
FROM tbaardbesluit c
     LEFT JOIN tbbouwvergunningen b ON b.dvcodeaardbesluit = c.dvcode
  LEFT JOIN tbmilinrichtingen a ON b.dnkeymilinrichtingen = a.dnkey
  LEFT JOIN tbbouwwerkz d ON b.dnkeybouwwerkz = d.dnkey;

COMMENT ON VIEW public.vwfrminrkinderen
IS 'View van alle aan een inrichting gerelateerde vergunningen/zaken . Basistabellen: tbomgvergunning, tbmilvergunningen, tbhorecavergunningen, tbhandhavingen, tbbouwvergunningen, tbovvergunningen';

COMMENT ON COLUMN public.vwfrminrkinderen.dvzaakmoduleletter
IS 'W = Omgeving, E = Milieu/gebruik, C = Horeca, H = Handhaving, O = Overige, B = Bouw/sloop';

COMMENT ON COLUMN public.vwfrminrkinderen.dvzaakmoduleoms
IS 'Omgevingsvergunning, Milieu- en gebruiksvergunning/melding ,Handhavingszaak, Horecavergunning, Overige vergunning of Bouw- en sloopvergunning';

COMMENT ON COLUMN public.vwfrminrkinderen.dnkeymilinrichtingen
IS 'Primary key van de milieu/brandweer inrichting (tbmilinrichtingen.dnkey) waaraan de zaak is gelieerd';

COMMENT ON COLUMN public.vwfrminrkinderen.dvinrichtingnaam
IS 'De naam van de milieu/brandweer inrichting waaraan de zaak is gelieerd';

COMMENT ON COLUMN public.vwfrminrkinderen.dnkeyzaak
IS 'De primary key van de vergunningsaanvraag of handhavingszaak (tbomgvergunning.dnkey, tbmilvergunningen.dnkey, tbhandhavingen.dnkey, tbhorecavergunningen.dnkey, tbovvergunningen.dnkey of tbbouwvergunningen.dnkey)';

COMMENT ON COLUMN public.vwfrminrkinderen.dvzaakcode
IS 'De zaakcode of vergunningsaanvraagnummer van de vergunningsaanvraag of handhavingszaak';

COMMENT ON COLUMN public.vwfrminrkinderen.dvzaakoms
IS 'De zaakomschrijving van de vergunningsaanvraag of handhavingszaak';

COMMENT ON COLUMN public.vwfrminrkinderen.ddzaakstartdatum
IS 'De aanvraagdatum cq ontvangst cq verzoekdatum waarmee de zaak, vergunningsaanvraag is gestart';

COMMENT ON COLUMN public.vwfrminrkinderen.dvstatus
IS 'De status van de zaak, vergunningsaanvraag (in behandeling, verleend, ingetrokken, niet meer geldig etc.)'; 


This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 22-2-2017 10:50
 Previous topic Chapter index Next topic