openwave 3.1.2
Previous topic Chapter index Next topic

View: vwfrmomgorkestrator_insp

 

 

Schema

public

 

Owner

waveuser002

 

Descriptions

There is no description for view vwfrmomgorkestrator_insp

 

Fields

Name

Data type

Description

dnkeyinspecties

integer

 

ddtrajectgepland

timestamp

 

ddtrajectafgehandeld

timestamp

 

dvaanleiding

varchar(60)

 

dvtrajectinspecteur

char(5)

 

dnkeymodule

integer

 

dvmodule

char(1)

 

dnkeyinspbezoeken

integer

 

ddbezoekgepland

timestamp

 

ddbezoekafgehandeld

timestamp

 

dvbezoekinspecteur

char(5)

 

dvsoortbezoek

varchar(50)

 

dvzaakcode

varchar(121)

 

dvobject

varchar(200)

 

dvobjstraat

varchar(80)

 

dvobjhuisnummer

varchar(5)

 

dvobjplaats

varchar(80)

 

dvcodeaccountman

varchar(5)

 

dvrechtengroep

varchar(20)

 

 

Rules

Name

Event

Instead

Condition

Description

_RETURN

SELECT

 

 

 

Definition

CREATE VIEW public.vwfrmomgorkestrator_insp (
   dnkeyinspecties,
   ddtrajectgepland,
   ddtrajectafgehandeld,
   dvaanleiding,
   dvtrajectinspecteur,
   dnkeymodule,
   dvmodule,
   dnkeyinspbezoeken,
   ddbezoekgepland,
   ddbezoekafgehandeld,
   dvbezoekinspecteur,
   dvsoortbezoek,
   dvzaakcode,
   dvobject,
   dvobjstraat,
   dvobjhuisnummer,
   dvobjplaats,
   dvcodeaccountman,
   dvrechtengroep)
AS
       (        (        (        (
SELECT a.dnkey AS dnkeyinspecties,
                                           a.ddrappel AS ddtrajectgepland,
                                           a.ddcontrole AS ddtrajectafgehandeld,
                                           c.dvaanleiding,
                                           a.dvcodemedewerkers AS dvtrajectinspecteur,
                                           a.dnkeymilinrichtingen AS dnkeymodule,
                                           'E'::character(1) AS dvmodule,
                                           b.dnkey AS dnkeyinspbezoeken,
                                           b.ddgepland AS ddbezoekgepland,
                                           b.ddafgehandeld AS ddbezoekafgehandeld,
                                           b.dvcodemedewerkers AS dvbezoekinspecteur,
                                           d.dvomschrijving AS dvsoortbezoek,
                                               CASE
                                                   WHEN
a.dngrondslactw IS NOT
                                                       NULL THEN
((tbomgvergunning.dvzaakcode::text || ':'::text) || tbtoestemmingen.dvwerkzaamheden::text)::character varying
                                                   WHEN a.dngrondslverge IS
                                                       NOT NULL THEN
((tbmilvergunningen.dvvergnummer::text || ':'::text) || tbmilvergunningen.dvpublbouwwerk::text)::character varying
                                                   ELSE e.dvinrichtingnr
                                               END::character varying(121) AS
                                                   dvzaakcode,
                                           e.dvinrichtingnaam::character
                                               varying
(200) AS dvobject,
                                           e.dvobjstraat, e.dvobjhuisnummer,
                                           e.dvobjplaats,
                                           NULL::character varying(5) AS
                                               dvcodeaccountman,
                                           f.dvgroep AS dvrechtengroep
FROM tbinspbezoeken b
                                     JOIN tbinspecties a ON b.dnkeyinspecties = a.dnkey
                                LEFT JOIN tbinspaanleiding c ON
                                    a.dnkeyinspaanleiding = c.dnkey
                           LEFT JOIN tbinspsoortbezoek d ON
                               b.dnkeyinspsoortbezoek = d.dnkey
                      JOIN vwmilinrobjstraat e ON a.dnkeymilinrichtingen = e.dnkey
                 JOIN tbmedewerkers ON a.dvcodemedewerkers = tbmedewerkers.dvcode
            LEFT JOIN tbrechten f ON tbmedewerkers.dnkeyrechten = f.dnkey
       LEFT JOIN tbtoestemmingen ON a.dngrondslactw = tbtoestemmingen.dnkey
  LEFT JOIN tbomgvergunning ON tbtoestemmingen.dnkeyomgvergunningen =
      tbomgvergunning.dnkey
  LEFT JOIN tbmilvergunningen ON a.dngrondslverge = tbmilvergunningen.dnkey
WHERE (a.ddcontrole IS NULL OR a.ddcontrole > fn_vandaag(0)) AND e.ddblokkering
   IS NULL AND b.ddgepland > fn_vandaag((-365)) AND b.ddafgehandeld IS NULL
UNION
SELECT
a.dnkey AS dnkeyinspecties,
                                           a.ddrappel AS ddtrajectgepland,
                                           a.ddcontrole AS ddtrajectafgehandeld,
                                           c.dvaanleiding,
                                           a.dvcodemedewerkers AS dvtrajectinspecteur,
                                           a.dnkeybouwvergunningen AS dnkeymodule,
                                           'B'::character(1) AS dvmodule,
                                           b.dnkey AS dnkeyinspbezoeken,
                                           b.ddgepland AS ddbezoekgepland,
                                           b.ddafgehandeld AS ddbezoekafgehandeld,
                                           b.dvcodemedewerkers AS dvbezoekinspecteur,
                                           d.dvomschrijving AS dvsoortbezoek,
                                           e.dvbouwnr::character varying(121)
                                               AS dvzaakcode,
                                           e.dvpublbouwwerk::character
                                               varying
(200) AS dvobject,
                                           e.dvobjstraat, e.dvobjhuisnummer,
                                           e.dvobjplaats,
                                           NULL::character varying(5) AS
                                               dvcodeaccountman,
                                           f.dvgroep AS dvrechtengroep
FROM tbinspbezoeken b
                                     JOIN tbinspecties a ON b.dnkeyinspecties = a.dnkey
                                LEFT JOIN tbinspaanleiding c ON
                                    a.dnkeyinspaanleiding = c.dnkey
                           LEFT JOIN tbinspsoortbezoek d ON
                               b.dnkeyinspsoortbezoek = d.dnkey
                      JOIN vwbouwvergobjstrsp e ON a.dnkeybouwvergunningen = e.dnkey
                 JOIN tbmedewerkers ON a.dvcodemedewerkers = tbmedewerkers.dvcode
            LEFT JOIN tbrechten f ON tbmedewerkers.dnkeyrechten = f.dnkey
WHERE (a.ddcontrole IS NULL OR a.ddcontrole > fn_vandaag(0)) AND e.ddblokkering
   IS NULL AND b.ddgepland > fn_vandaag((-365)) AND b.ddafgehandeld IS NULL)
UNION
SELECT
a.dnkey AS dnkeyinspecties,
                                   a.ddrappel AS ddtrajectgepland,
                                   a.ddcontrole AS ddtrajectafgehandeld,
                                   c.dvaanleiding,
                                   a.dvcodemedewerkers AS dvtrajectinspecteur,
                                   a.dnkeyhandhavingen AS dnkeymodule,
                                   'H'::character(1) AS dvmodule,
                                   b.dnkey AS dnkeyinspbezoeken,
                                   b.ddgepland AS ddbezoekgepland,
                                   b.ddafgehandeld AS ddbezoekafgehandeld,
                                   b.dvcodemedewerkers AS dvbezoekinspecteur,
                                   d.dvomschrijving AS dvsoortbezoek,
                                   e.dvaanschrijfnr::character varying(121) AS
                                       dvzaakcode,
                                   e.dvomsbouwwerk::character varying(200) AS dvobject,
                                   e.dvobjstraat, e.dvobjhuisnummer,
                                   e.dvobjplaats,
                                   NULL::character varying(5) AS dvcodeaccountman,
                                   f.dvgroep AS dvrechtengroep
FROM tbinspbezoeken b
                             JOIN tbinspecties a ON b.dnkeyinspecties = a.dnkey
                        LEFT JOIN tbinspaanleiding c ON a.dnkeyinspaanleiding = c.dnkey
                   LEFT JOIN tbinspsoortbezoek d ON b.dnkeyinspsoortbezoek = d.dnkey
              JOIN vwhandhstraat e ON a.dnkeyhandhavingen = e.dnkey
         JOIN tbmedewerkers ON a.dvcodemedewerkers = tbmedewerkers.dvcode
    LEFT JOIN tbrechten f ON tbmedewerkers.dnkeyrechten = f.dnkey
WHERE (a.ddcontrole IS NULL OR a.ddcontrole > fn_vandaag(0)) AND e.ddblokkering
   IS NULL AND b.ddgepland > fn_vandaag((-365)) AND b.ddafgehandeld IS NULL)
UNION
SELECT
a.dnkey AS dnkeyinspecties,
                           a.ddrappel AS ddtrajectgepland,
                           a.ddcontrole AS ddtrajectafgehandeld,
                           c.dvaanleiding,
                           a.dvcodemedewerkers AS dvtrajectinspecteur,
                           a.dnkeyomgvergunningen AS dnkeymodule,
                           'W'::character(1) AS dvmodule,
                           b.dnkey AS dnkeyinspbezoeken,
                           b.ddgepland AS ddbezoekgepland,
                           b.ddafgehandeld AS ddbezoekafgehandeld,
                           b.dvcodemedewerkers AS dvbezoekinspecteur,
                           d.dvomschrijving AS dvsoortbezoek,
                           e.dvzaakcode::character varying(121) AS dvzaakcode,
                           e.dvaanvraagnaam::character varying(200) AS dvobject,
                           e.dvobjstraat, e.dvobjhuisnummer, e.dvobjplaats,
                           e.dvcodeaccountman::character varying(5) AS
                               dvcodeaccountman,
                           f.dvgroep AS dvrechtengroep
FROM tbinspbezoeken b
                     JOIN tbinspecties a ON b.dnkeyinspecties = a.dnkey
                LEFT JOIN tbinspaanleiding c ON a.dnkeyinspaanleiding = c.dnkey
           LEFT JOIN tbinspsoortbezoek d ON b.dnkeyinspsoortbezoek = d.dnkey
      JOIN vwomgvergobjstraat e ON a.dnkeyomgvergunningen = e.dnkey
  JOIN tbmedewerkers ON a.dvcodemedewerkers = tbmedewerkers.dvcode
  LEFT JOIN tbrechten f ON tbmedewerkers.dnkeyrechten = f.dnkey
WHERE (a.ddcontrole IS NULL OR a.ddcontrole > fn_vandaag(0)) AND e.ddblokkering
   IS NULL AND b.ddgepland > fn_vandaag((-365)) AND b.ddafgehandeld IS NULL)
UNION
SELECT
a.dnkey AS dnkeyinspecties,
                   a.ddrappel AS ddtrajectgepland,
                   a.ddcontrole AS ddtrajectafgehandeld, c.dvaanleiding,
                   a.dvcodemedewerkers AS dvtrajectinspecteur,
                   a.dnkeyhorecavergunningen AS dnkeymodule,
                   'C'::character(1) AS dvmodule, b.dnkey AS dnkeyinspbezoeken,
                   b.ddgepland AS ddbezoekgepland,
                   b.ddafgehandeld AS ddbezoekafgehandeld,
                   b.dvcodemedewerkers AS dvbezoekinspecteur,
                   d.dvomschrijving AS dvsoortbezoek,
                   e.dvvergnummer::character varying(121) AS dvzaakcode,
                   e.dvzaaknaam AS dvobject, e.dvobjstraat, e.dvobjhuisnummer,
                   e.dvobjplaats,
                   NULL::character varying(5) AS dvcodeaccountman,
                   f.dvgroep AS dvrechtengroep
FROM tbinspbezoeken b
             JOIN tbinspecties a ON b.dnkeyinspecties = a.dnkey
        LEFT JOIN tbinspaanleiding c ON a.dnkeyinspaanleiding = c.dnkey
   LEFT JOIN tbinspsoortbezoek d ON b.dnkeyinspsoortbezoek = d.dnkey
  JOIN vwhorecavergstraat e ON a.dnkeyhorecavergunningen = e.dnkey
  JOIN tbmedewerkers ON a.dvcodemedewerkers = tbmedewerkers.dvcode
  LEFT JOIN tbrechten f ON tbmedewerkers.dnkeyrechten = f.dnkey
WHERE (a.ddcontrole IS NULL OR a.ddcontrole > fn_vandaag(0)) AND e.ddblokkering
   IS NULL AND b.ddgepland > fn_vandaag((-365)) AND b.ddafgehandeld IS NULL)
UNION
SELECT
a.dnkey AS dnkeyinspecties, a.ddrappel AS ddtrajectgepland,
           a.ddcontrole AS ddtrajectafgehandeld, c.dvaanleiding,
           a.dvcodemedewerkers AS dvtrajectinspecteur,
           a.dnkeyovvergunningen AS dnkeymodule, 'O'::character(1) AS dvmodule,
           b.dnkey AS dnkeyinspbezoeken, b.ddgepland AS ddbezoekgepland,
           b.ddafgehandeld AS ddbezoekafgehandeld,
           b.dvcodemedewerkers AS dvbezoekinspecteur,
           d.dvomschrijving AS dvsoortbezoek,
           e.dvvergunningsnr::character varying(121) AS dvzaakcode,
           e.dvpublbouwwerk::character varying(200) AS dvobject, e.dvobjstraat,
           e.dvobjhuisnummer, e.dvobjplaats,
           NULL::character varying(5) AS dvcodeaccountman,
           f.dvgroep AS dvrechtengroep
FROM tbinspbezoeken b
     JOIN tbinspecties a ON b.dnkeyinspecties = a.dnkey
  LEFT JOIN tbinspaanleiding c ON a.dnkeyinspaanleiding = c.dnkey
  LEFT JOIN tbinspsoortbezoek d ON b.dnkeyinspsoortbezoek = d.dnkey
  JOIN vwovvergstraat e ON a.dnkeyovvergunningen = e.dnkey
  JOIN tbmedewerkers ON a.dvcodemedewerkers = tbmedewerkers.dvcode
  LEFT JOIN tbrechten f ON tbmedewerkers.dnkeyrechten = f.dnkey
WHERE (a.ddcontrole IS NULL OR a.ddcontrole > fn_vandaag(0)) AND e.ddblokkering
   IS NULL AND b.ddgepland > fn_vandaag((-365)) AND b.ddafgehandeld IS NULL;

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