openwave 3.1.2
Previous topic Chapter index Next topic

View: vwfrmomgorkestrator_ing_adv

 

 

Schema

public

 

Owner

waveuser002

 

Descriptions

There is no description for view vwfrmomgorkestrator_ing_adv

 

Fields

Name

Data type

Description

dnkeyadvies

integer

 

dddateringadvies

timestamp

 

dvomschradviesinstantie

varchar(40)

 

dvomschrijving

varchar(40)

 

dvadviesbehandelaar

char(5)

 

dnkeymodule

integer

 

dvmodule

char(1)

 

dvinrichtingnaam

varchar(200)

 

dvzaakcode

varchar(20)

 

dvsoortverg

varchar(200)

 

dvomsaanvraag

varchar(250)

 

dvobjstraat

varchar(80)

 

dvobjhuisnummer

varchar(5)

 

dvobjplaats

varchar(80)

 

dvrechtengroep

varchar(1)

 

dvdossierbehandelaar

char(5)

 

 

Rules

Name

Event

Instead

Condition

Description

_RETURN

SELECT

 

 

 

Definition

CREATE VIEW public.vwfrmomgorkestrator_ing_adv (
   dnkeyadvies,
   dddateringadvies,
   dvomschradviesinstantie,
   dvomschrijving,
   dvadviesbehandelaar,
   dnkeymodule,
   dvmodule,
   dvinrichtingnaam,
   dvzaakcode,
   dvsoortverg,
   dvomsaanvraag,
   dvobjstraat,
   dvobjhuisnummer,
   dvobjplaats,
   dvrechtengroep,
   dvdossierbehandelaar)
AS
       (        (        (        (        (
SELECT a.dnkey AS dnkeyadvies,
                                                   a.dddateringadvies,
                                                   d.dvomschrijving AS
                                                       dvomschradviesinstantie,
                                                   a.dvomschrijving,
                                                       CASE
                                                           WHEN

                                                               a.dvcodemedewerkers IS NULL THEN e.dvcodemedewerker
                                                           ELSE a.dvcodemedewerkers
                                                       END AS dvadviesbehandelaar,
                                                   a.dnkeymilvergunningen AS
                                                       dnkeymodule,
                                                   'E'::character(1) AS dvmodule,
                                                   b.dvinrichtingnaam::character
                                                       varying
(200) AS dvinrichtingnaam,
                                                   b.dvvergnummer AS dvzaakcode,
                                                   b.dvsoortmilverg::character
                                                       varying
(200) AS dvsoortverg,
                                                   
(((b.dvwerkzaamheid::text
                                                       || ''::text) || b.dvpublbouwwerk::text))::character varying(250) AS dvomsaanvraag,
                                                   b.dvobjstraat,
                                                   b.dvobjhuisnummer,
                                                   b.dvobjplaats,
                                                   NULL::character varying(1)
                                                       AS dvrechtengroep,
                                                   e.dvcodemedewerker AS
                                                       dvdossierbehandelaar
FROM tbadviezen a
                                             JOIN vwfrmmilvergunningen b ON
                                                 a.dnkeymilvergunningen = b.dnkey
                                        JOIN tbadviesinstanties d ON
                                            a.dvcodeadviesinstanties = d.dvcode
                                   JOIN tbinbehandelingbij e ON
                                       a.dnkeymilvergunningen = e.dnkeymilvergunningen
WHERE a.dddateringadvies > fn_vandaag((-365)) AND a.ddvervallen IS NULL AND
   b.ddblokkering IS NULL AND b.ddingetrokken IS NULL AND e.dlactief = 'T'::bpchar
UNION ALL
SELECT
a.dnkey AS dnkeyadvies,
                                                   a.dddateringadvies,
                                                   d.dvomschrijving AS
                                                       dvomschradviesinstantie,
                                                   a.dvomschrijving,
                                                       CASE
                                                           WHEN

                                                               a.dvcodemedewerkers IS NULL THEN e.dvcodemedewerker
                                                           ELSE a.dvcodemedewerkers
                                                       END AS dvadviesbehandelaar,
                                                   a.dnkeyomgvergunningen AS
                                                       dnkeymodule,
                                                   'W'::character(1) AS dvmodule,
                                                   b.dvinrichtingnaam::character
                                                       varying
(200) AS dvinrichtingnaam,
                                                   b.dvzaakcode,
                                                   b.dvsoortaanvraag::character
                                                       varying
(200) AS dvsoortverg,
                                                   b.dvaanvraagnaam::character
                                                       varying
(250) AS dvomsaanvraag,
                                                   b.dvobjstraat,
                                                   b.dvobjhuisnummer,
                                                   b.dvobjplaats,
                                                   NULL::character varying(1)
                                                       AS dvrechtengroep,
                                                   e.dvcodemedewerker AS
                                                       dvdossierbehandelaar
FROM vwfrmomgvergunningen b
                                             JOIN tbadviezen a ON
                                                 a.dnkeyomgvergunningen = b.dnkeyomgvergunning
                                        JOIN tbadviesinstanties d ON
                                            a.dvcodeadviesinstanties = d.dvcode
                                   JOIN tbinbehandelingbij e ON
                                       a.dnkeyomgvergunningen = e.dnkeyomgvergunningen
WHERE a.dddateringadvies > fn_vandaag((-365)) AND a.ddvervallen IS NULL AND
   b.ddblokkering IS NULL AND b.ddingetrokken IS NULL AND e.dlactief = 'T'::bpchar)
UNION ALL
SELECT
a.dnkey AS dnkeyadvies,
                                           a.dddateringadvies,
                                           d.dvomschrijving AS dvomschradviesinstantie,
                                           a.dvomschrijving,
                                               CASE
                                                   WHEN
a.dvcodemedewerkers IS
                                                       NULL THEN
e.dvcodemedewerker
                                                   ELSE a.dvcodemedewerkers
                                               END AS dvadviesbehandelaar,
                                           a.dnkeyhorecavergunningen AS dnkeymodule,
                                           'C'::character(1) AS dvmodule,
                                           b.dvzaaknaam AS dvinrichtingnaam,
                                           b.dvhorvergunningsnr AS dvzaakcode,
                                           b.dvsoorthorvergunning::character
                                               varying
(200) AS dvsoortverg,
                                           b.dvsoortonderneming::character
                                               varying
(250) AS dvomsaanvraag,
                                           b.dvobjstraat, b.dvobjhuisnummer,
                                           b.dvobjplaats,
                                           NULL::character varying(1) AS
                                               dvrechtengroep,
                                           e.dvcodemedewerker AS dvdossierbehandelaar
FROM vwfrmhorvergunningen b
                                     JOIN tbadviezen a ON
                                         a.dnkeyhorecavergunningen = b.dnkeyhorvergunning
                                JOIN tbadviesinstanties d ON
                                    a.dvcodeadviesinstanties = d.dvcode
                           JOIN tbinbehandelingbij e ON
                               a.dnkeyhorecavergunningen = e.dnkeyhorecavergunningen
WHERE a.dddateringadvies > fn_vandaag((-365)) AND a.ddvervallen IS NULL AND
   b.ddblokkering IS NULL AND b.ddingetrokken IS NULL AND e.dlactief = 'T'::bpchar)
UNION ALL
SELECT
a.dnkey AS dnkeyadvies,
                                   a.dddateringadvies,
                                   d.dvomschrijving AS dvomschradviesinstantie,
                                   a.dvomschrijving,
                                       CASE
                                           WHEN
a.dvcodemedewerkers IS NULL
                                               THEN
e.dvcodemedewerker
                                           ELSE a.dvcodemedewerkers
                                       END AS dvadviesbehandelaar,
                                   a.dnkeybouwvergunningen AS dnkeymodule,
                                   'B'::character(1) AS dvmodule,
                                   b.dvinrichtingnaam::character varying(200)
                                       AS dvinrichtingnaam,
                                   b.dvbouwnr AS dvzaakcode,
                                   b.dvsoortplan::character varying(200) AS
                                       dvsoortverg,
                                   
(((b.dvbouwwerkzaamheid::text || ''::text)
                                       || b.dvpublbouwwerk::text))::character varying(250) AS dvomsaanvraag,
                                   b.dvobjstraat, b.dvobjhuisnummer,
                                   b.dvobjplaats,
                                   NULL::character varying(1) AS dvrechtengroep,
                                   e.dvcodemedewerker AS dvdossierbehandelaar
FROM vwfrmbouwvergunningen b
                             JOIN tbadviezen a ON a.dnkeybouwvergunningen =
                                 b.dnkeybouwverg
                        JOIN tbadviesinstanties d ON a.dvcodeadviesinstanties
                            = d.dvcode
                   JOIN tbinbehandelingbij e ON a.dnkeybouwvergunningen =
                       e.dnkeybouwvergunningen
WHERE a.dddateringadvies > fn_vandaag((-365)) AND a.ddvervallen IS NULL AND
   b.ddbouwblokkering IS NULL AND b.ddbouwingetrokken IS NULL AND e.dlactief = 'T'::bpchar)
UNION ALL
SELECT
a.dnkey AS dnkeyadvies, a.dddateringadvies,
                           d.dvomschrijving AS dvomschradviesinstantie,
                           a.dvomschrijving,
                               CASE
                                   WHEN
a.dvcodemedewerkers IS NULL THEN
                                       e.dvcodemedewerker
                                   ELSE a.dvcodemedewerkers
                               END AS dvadviesbehandelaar,
                           a.dnkeyovvergunningen AS dnkeymodule,
                           'O'::character(1) AS dvmodule,
                           b.dvinrichtingnaam::character varying(200) AS
                               dvinrichtingnaam,
                           b.dvovvergunningsnr AS dvzaakcode,
                           b.dvsoortovvergunning::character varying(200) AS
                               dvsoortverg,
                           
(((b.dvovwerkzaamheid::text || ''::text) ||
                               b.dvpublbouwwerk::text))::character varying(250) AS dvomsaanvraag,
                           b.dvobjstraat, b.dvobjhuisnummer, b.dvobjplaats,
                           NULL::character varying(1) AS dvrechtengroep,
                           e.dvcodemedewerker AS dvdossierbehandelaar
FROM vwfrmovvergunningen b
                     JOIN tbadviezen a ON a.dnkeyovvergunningen = b.dnkeyovvergunning
                JOIN tbadviesinstanties d ON a.dvcodeadviesinstanties = d.dvcode
           JOIN tbinbehandelingbij e ON a.dnkeyovvergunningen = e.dnkeyovvergunningen
WHERE a.dddateringadvies > fn_vandaag((-365)) AND a.ddvervallen IS NULL AND
   b.ddovblokkering IS NULL AND b.ddovingetrokken IS NULL AND e.dlactief = 'T'::bpchar)
UNION ALL
SELECT
a.dnkey AS dnkeyadvies, a.dddateringadvies,
                   d.dvomschrijving AS dvomschradviesinstantie,
                   a.dvomschrijving,
                       CASE
                           WHEN
a.dvcodemedewerkers IS NULL THEN e.dvcodemedewerker
                           ELSE a.dvcodemedewerkers
                       END AS dvadviesbehandelaar,
                   a.dnkeyhandhavingen AS dnkeymodule,
                   'H'::character(1) AS dvmodule,
                   b.dvinrichtingnaam::character varying(200) AS dvinrichtingnaam,
                   b.dvhandhnummer AS dvzaakcode, b.dvwetbasis AS dvsoortverg,
                   b.dvovertreding::character varying(250) AS dvomsaanvraag,
                   b.dvobjstraat, b.dvobjhuisnummer, b.dvobjplaats,
                   NULL::character varying(1) AS dvrechtengroep,
                   e.dvcodemedewerker AS dvdossierbehandelaar
FROM vwfrmhandhavingen b
             JOIN tbadviezen a ON a.dnkeyhandhavingen = b.dnkeyhandhaving
        JOIN tbadviesinstanties d ON a.dvcodeadviesinstanties = d.dvcode
   JOIN tbinbehandelingbij e ON a.dnkeyhandhavingen = e.dnkeyhandhavingen
WHERE a.dddateringadvies > fn_vandaag((-365)) AND a.ddvervallen IS NULL AND
   b.ddhandhblokkering IS NULL AND e.dlactief = 'T'::bpchar)
UNION ALL
SELECT
a.dnkey AS dnkeyadvies, a.dddateringadvies,
           d.dvomschrijving AS dvomschradviesinstantie, a.dvomschrijving,
               CASE
                   WHEN
a.dvcodemedewerkers IS NULL THEN e.dvcodemedewerker
                   ELSE a.dvcodemedewerkers
               END AS dvadviesbehandelaar,
           a.dnkeyinfoaanvraag AS dnkeymodule, 'I'::character(1) AS dvmodule,
           b.dvinrichtingnaam::character varying(200) AS dvinrichtingnaam,
           b.dvinfonummer AS dvzaakcode,
           b.dvsoortinfoaanvraag::character varying(200) AS dvsoortverg,
           b.dvomschrijving AS dvomsaanvraag, b.dvobjstraat, b.dvobjhuisnummer,
           b.dvobjplaats, NULL::character varying(1) AS dvrechtengroep,
           e.dvcodemedewerker AS dvdossierbehandelaar
FROM vwfrminfoaanvragen b
     JOIN tbadviezen a ON a.dnkeyinfoaanvraag = b.dnkeyinfoaanvraag
  JOIN tbadviesinstanties d ON a.dvcodeadviesinstanties = d.dvcode
  JOIN tbinbehandelingbij e ON a.dnkeyinfoaanvraag = e.dnkeyinfoaanvraag
WHERE a.dddateringadvies > fn_vandaag((-365)) AND a.ddvervallen IS NULL AND
   b.ddblokkeerdatum IS NULL AND e.dlactief = 'T'::bpchar;

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 22-01-2013 17:28
Previous topic Chapter index Next topic