Datadictionary OpenWave 3.1.25

View: vwhulpgewijzigdezaken

Schema

public

Owner

waveuser005

Descriptions

Hulpview van wijzigingen in tbcorrespondentie, tbtermijnbewstappen en tbadviezen in afgelopen week

Fields

Rules

Indices

There are no indices for table vwhulpgewijzigdezaken

Definition

CREATE VIEW public.vwhulpgewijzigdezaken (
   dnkeymodule,
   dvmodule,
   dvcodewiegewijzigd,
   dvsoortwijziging,
   ddgewijzigd)
AS
SELECT COALESCE(a.dnkeybouwvergunningen, 0) + COALESCE(a.dnkeyovvergunningen,
   0) + COALESCE(a.dnkeyhandhavingen, 0) + COALESCE(a.dnkeyhorecavergunningen, 0) + COALESCE(a.dnkeymilvergunningen, 0) + COALESCE(a.dnkeyomgvergunningen, 0) + COALESCE(a.dnkeyinfoaanvraag, 0) AS dnkeymodule,
       CASE
           WHEN a.dnkeybouwvergunningen IS NOT NULL THEN 'B'::text
           WHEN a.dnkeyovvergunningen IS NOT NULL THEN 'O'::text
           WHEN a.dnkeyhandhavingen IS NOT NULL THEN 'H'::text
           WHEN a.dnkeyhorecavergunningen IS NOT NULL THEN 'C'::text
           WHEN a.dnkeymilvergunningen IS NOT NULL THEN 'E'::text
           WHEN a.dnkeyomgvergunningen IS NOT NULL THEN 'W'::text
           ELSE 'I'::text
       END::character(1) AS dvmodule,
   a.dvcodemedewerkers AS dvcodewiegewijzigd,
   'processtap'::character varying(17) AS dvsoortwijziging,
   a.ddafgehandeld AS ddgewijzigd
FROM tbtermijnbewstappen a
WHERE a.ddafgehandeld > fn_vandaag('-7'::integer) AND a.ddafgehandeld <= fn_vandaag(0)
UNION
SELECT COALESCE(a.dnkeybouwvergunningen, 0) + COALESCE(a.dnkeyovvergunningen,
   0) + COALESCE(a.dnkeyhandhavingen, 0) + COALESCE(a.dnkeyhorecavergunningen, 0) + COALESCE(a.dnkeymilvergunningen, 0) + COALESCE(a.dnkeyomgvergunningen, 0) + COALESCE(a.dnkeyinfoaanvraag, 0) AS dnkeymodule,
       CASE
           WHEN a.dnkeybouwvergunningen IS NOT NULL THEN 'B'::text
           WHEN a.dnkeyovvergunningen IS NOT NULL THEN 'O'::text
           WHEN a.dnkeyhandhavingen IS NOT NULL THEN 'H'::text
           WHEN a.dnkeyhorecavergunningen IS NOT NULL THEN 'C'::text
           WHEN a.dnkeymilvergunningen IS NOT NULL THEN 'E'::text
           WHEN a.dnkeyomgvergunningen IS NOT NULL THEN 'W'::text
           ELSE 'I'::text
       END::character(1) AS dvmodule,
   a.dvcoderegistreerder AS dvcodewiegewijzigd,
   'docregistratie'::character varying(17) AS dvsoortwijziging,
   a.ddgewijzigd
FROM tbcorrespondentie a
WHERE a.ddgewijzigd > fn_vandaag('-7'::integer) AND a.ddgewijzigd <= fn_vandaag(0)
UNION
SELECT COALESCE(a.dnkeybouwvergunningen, 0) + COALESCE(a.dnkeyovvergunningen,
   0) + COALESCE(a.dnkeyhandhavingen, 0) + COALESCE(a.dnkeyhorecavergunningen, 0) + COALESCE(a.dnkeymilvergunningen, 0) + COALESCE(a.dnkeyomgvergunningen, 0) + COALESCE(a.dnkeyinfoaanvraag, 0) AS dnkeymodule,
       CASE
           WHEN a.dnkeybouwvergunningen IS NOT NULL THEN 'B'::text
           WHEN a.dnkeyovvergunningen IS NOT NULL THEN 'O'::text
           WHEN a.dnkeyhandhavingen IS NOT NULL THEN 'H'::text
           WHEN a.dnkeyhorecavergunningen IS NOT NULL THEN 'C'::text
           WHEN a.dnkeymilvergunningen IS NOT NULL THEN 'E'::text
           WHEN a.dnkeyomgvergunningen IS NOT NULL THEN 'W'::text
           ELSE 'I'::text
       END::character(1) AS dvmodule,
   'XXXXX'::character(5) AS dvcodewiegewijzigd,
   'docverstuurd'::character varying(17) AS dvsoortwijziging,
   a.ddbriefdatum AS ddgewijzigd
FROM tbcorrespondentie a
WHERE a.ddbriefdatum > fn_vandaag('-7'::integer) AND a.ddbriefdatum <= fn_vandaag(0)
UNION
SELECT COALESCE(a.dnkeybouwvergunningen, 0) + COALESCE(a.dnkeyovvergunningen,
   0) + COALESCE(a.dnkeyhandhavingen, 0) + COALESCE(a.dnkeyhorecavergunningen, 0) + COALESCE(a.dnkeymilvergunningen, 0) + COALESCE(a.dnkeyomgvergunningen, 0) + COALESCE(a.dnkeyinfoaanvraag, 0) AS dnkeymodule,
       CASE
           WHEN a.dnkeybouwvergunningen IS NOT NULL THEN 'B'::text
           WHEN a.dnkeyovvergunningen IS NOT NULL THEN 'O'::text
           WHEN a.dnkeyhandhavingen IS NOT NULL THEN 'H'::text
           WHEN a.dnkeyhorecavergunningen IS NOT NULL THEN 'C'::text
           WHEN a.dnkeymilvergunningen IS NOT NULL THEN 'E'::text
           WHEN a.dnkeyomgvergunningen IS NOT NULL THEN 'W'::text
           ELSE 'I'::text
       END::character(1) AS dvmodule,
   'XXXXX'::character(5) AS dvcodewiegewijzigd,
   (((a.dvcodeadviesinstanties::text || '-'::text) ||
       'adviesbinnen'::text))::character varying(17) AS dvsoortwijziging,
   COALESCE(a.dddateringadvies, a.ddadviesdatum) AS ddgewijzigd
FROM tbadviezen a
WHERE COALESCE(a.dddateringadvies, a.ddadviesdatum) > fn_vandaag('-7'::integer)
   AND COALESCE(a.dddateringadvies, a.ddadviesdatum) <= fn_vandaag(0)
UNION
SELECT COALESCE(a.dnkeybouwvergunningen, 0) + COALESCE(a.dnkeyovvergunningen,
   0) + COALESCE(a.dnkeyhandhavingen, 0) + COALESCE(a.dnkeyhorecavergunningen, 0) + COALESCE(a.dnkeymilvergunningen, 0) + COALESCE(a.dnkeyomgvergunningen, 0) + COALESCE(a.dnkeyinfoaanvraag, 0) AS dnkeymodule,
       CASE
           WHEN a.dnkeybouwvergunningen IS NOT NULL THEN 'B'::text
           WHEN a.dnkeyovvergunningen IS NOT NULL THEN 'O'::text
           WHEN a.dnkeyhandhavingen IS NOT NULL THEN 'H'::text
           WHEN a.dnkeyhorecavergunningen IS NOT NULL THEN 'C'::text
           WHEN a.dnkeymilvergunningen IS NOT NULL THEN 'E'::text
           WHEN a.dnkeyomgvergunningen IS NOT NULL THEN 'W'::text
           ELSE 'I'::text
       END::character(1) AS dvmodule,
   'XXXXX'::character(5) AS dvcodewiegewijzigd,
   (((a.dvcodeadviesinstanties::text || '-'::text) ||
       'adviesrappel'::text))::character varying(17) AS dvsoortwijziging,
   a.ddrappeldatum AS ddgewijzigd
FROM tbadviezen a
WHERE a.ddrappeldatum > fn_vandaag('-7'::integer) AND a.ddrappeldatum <=
   fn_vandaag(0) AND a.dddateringadvies IS NULL AND a.ddadviesdatum IS NULL
UNION
SELECT COALESCE(a.dnkeybouwvergunningen, 0) + COALESCE(a.dnkeyovvergunningen,
   0) + COALESCE(a.dnkeyhandhavingen, 0) + COALESCE(a.dnkeyhorecavergunningen, 0) + COALESCE(a.dnkeymilvergunningen, 0) + COALESCE(a.dnkeyomgvergunningen, 0) + COALESCE(a.dnkeyinfoaanvraag, 0) AS dnkeymodule,
       CASE
           WHEN a.dnkeybouwvergunningen IS NOT NULL THEN 'B'::text
           WHEN a.dnkeyovvergunningen IS NOT NULL THEN 'O'::text
           WHEN a.dnkeyhandhavingen IS NOT NULL THEN 'H'::text
           WHEN a.dnkeyhorecavergunningen IS NOT NULL THEN 'C'::text
           WHEN a.dnkeymilvergunningen IS NOT NULL THEN 'E'::text
           WHEN a.dnkeyomgvergunningen IS NOT NULL THEN 'W'::text
           ELSE 'I'::text
       END::character(1) AS dvmodule,
   'XXXXX'::character(5) AS dvcodewiegewijzigd,
   (((a.dvcodeadviesinstanties::text || '-'::text) ||
       'adviesverval'::text))::character varying(17) AS dvsoortwijziging,
   a.ddvervallen AS ddgewijzigd
FROM tbadviezen a
WHERE a.ddvervallen > fn_vandaag('-7'::integer) AND a.ddvervallen <= fn_vandaag(0);

COMMENT ON VIEW public.vwhulpgewijzigdezaken
IS 'Hulpview van wijzigingen in tbcorrespondentie, tbtermijnbewstappen en tbadviezen in afgelopen week';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 5-1-2022 14:13