Datadictionary OpenWave 3.1.25
 Previous topic Chapter index Next topic

View: vwfrmtepubliceren

 

 

Schema

public

 

Owner

waveuser005

 

Descriptions

DEPRECATED sinds databaseversie 3.1.25. Functie overgenomen door o.a. vwfrmzakentepubliceren. View van zaken die gepubliceerd dienen te worden (aanvraag, ontwerpbesluit, beschikking) uit tbomgvergunning, tbovvergunningen, tbhorecavergunningen en tbmilvergunningen

 

Fields

Name

Data type

Description

dvmodule

char(1)

Gehele view is DEPRECATED sinds databaseversie 3.1.25/ W indien de te publiceren zaak uit tbomgvergunning komt, E indien uit tbmilvergunningen, C indien uit tbhorecavergunningen en O indien uit tbovvergunningen.

dnkeyverg

integer

Primary key van de zaak in tbomgvergunning bij dvmodule = W, in tbmilvergunningen bij dvmodule = E, in tbhorecavergunningen bij dvmodule = C, in tbovvergunningen bij dvmodule = O.

dvpubltype

varchar(20)

Aanvraag, Ontwerp, Besluit afhankelijk waardoor het publiceren getriggerd wordt

ddtriggerdatum

timestamp

Aanvraagdatum, Ontwerpbesluitdatum of Besluit/afgehandeld datum afhankelijk waardoor het publiceren getriggerd wordt

ddpublexport

timestamp

Laatste datum dat een poging gedaan is om de zaak onder het publicatietype te publiceren

dvpublfout

varchar(200)

Foutcode die door DROP is geretourneerd bij de laatste keer dat een poging gedaan is om de zaak onder het publicatietype te publiceren

dvzaakcode

varchar(40)

De OpenWave zaakcode

dvzaaktypeoms

varchar

Zaaktype omschrijving

dvgemeenteid

varchar(4)

De gemeenteid van de lokatie waar de zaak aan gekoppeld is

dvbetreft

varchar

Korte omschrijving van de zaak

dvgemeentenaam

varchar(80)

De gemeentenaam van de lokatie waar de zaak aan gekoppeld is

dvobjplaats

varchar(80)

Woonplaatsnaam van de lokatie waar de zaak aan gekoppeld is

dvobjpostcode

varchar(6)

 

dvobjstraat

varchar(80)

Sraatnaam van de lokatie waar de zaak aan gekoppeld is

dvobjhuisnummer

varchar(5)

Huisnummer van de lokatie waar de zaak aan gekoppeld is

dvobjhuisletter

char(1)

Huisletter van de lokatie waar de zaak aan gekoppeld is

dvobjhuisnrtoevoeg

varchar(4)

Huisnummertoevoeging van de lokatie waar de zaak aan gekoppeld is

dnxcoordinaat

integer

x-ccordinaat van de lokatie waar de zaak aan gekoppeld is

dnycoordinaat

integer

y-ccordinaat van de lokatie waar de zaak aan gekoppeld is

dnkeycompartiment

integer

De primary key van tbcompartiment indien de combinatie gemeente/zaaktype daaraan gekoppeld is

dvid

varchar(27)

Unieke identfier van de rij door de combinatie van de eerste letter van dvpubltype, een hyphen , dvmodule, een hyphen mediatype , een hyphen en dnkeyverg

dvbron

varchar(30)

tbomgvergunning, tbovvergunningen, tbhorecavergunningen of tbmilvergunningen

dvomsmediatype

varchar(30)

Gemeenteblad, BladGemeenschappelijkeRegeling, waterschapblad, provinciaalblad of staatcourant

dvaardbesluit

varchar

De resultaat-omschrijving van het besluit bijv. verleend, gedeeltelijk geweigerd, afgehandeld

dvhoofdid

varchar(11)

Moduleletter + dnkeyverg. Tezamen foreinkey naar vwfrmzakentepubliceren.dvid

 

Rules

Name

Event

Instead

Condition

Description

_RETURN

SELECT

 

 

 

Indices

There are no indices for table vwfrmtepubliceren

 

Definition

CREATE VIEW public.vwfrmtepubliceren (
   dvmodule,
   dnkeyverg,
   dvpubltype,
   ddtriggerdatum,
   ddpublexport,
   dvpublfout,
   dvzaakcode,
   dvzaaktypeoms,
   dvgemeenteid,
   dvbetreft,
   dvgemeentenaam,
   dvobjplaats,
   dvobjpostcode,
   dvobjstraat,
   dvobjhuisnummer,
   dvobjhuisletter,
   dvobjhuisnrtoevoeg,
   dnxcoordinaat,
   dnycoordinaat,
   dnkeycompartiment,
   dvid,
   dvbron,
   dvomsmediatype,
   dvaardbesluit,
   dvhoofdid)
AS
SELECT 'W'::character(1) AS dvmodule,
   b.dnkeyomgvergunning AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraag AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvzaakcode,
   b.dvsoortaanvraag AS dvzaaktypeoms,
   b.dvgemeenteid,
   b.dvaanvraagnaam AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-W-GB-'::text || b.dnkeyomgvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbomgvergunning'::character varying(30) AS dvbron,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('W'::character(1)::text || b.dnkeyomgvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmomgvergunningen b
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraag AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtomgverg c ON b.dnkeysoortomgverg =
        c.dnkeysoortomgverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyomgvergunning =
        d.dnkeyomgvergunningen AND d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
WHERE NOT (b.dnkeyomgvergunning IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GB'::text
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'W'::character(1) AS dvmodule,
   b.dnkeyomgvergunning AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraag AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvzaakcode,
   b.dvsoortaanvraag AS dvzaaktypeoms,
   b.dvgemeenteid,
   b.dvaanvraagnaam AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-W-GS-'::text || b.dnkeyomgvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbomgvergunning'::character varying(30) AS dvbron,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('W'::character(1)::text || b.dnkeyomgvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmomgvergunningen b
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraag AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtomgverg c ON b.dnkeysoortomgverg =
        c.dnkeysoortomgverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyomgvergunning =
        d.dnkeyomgvergunningen AND d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
WHERE NOT (b.dnkeyomgvergunning IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GS'::text
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'W'::character(1) AS dvmodule,
   b.dnkeyomgvergunning AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraag AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvzaakcode,
   b.dvsoortaanvraag AS dvzaaktypeoms,
   b.dvgemeenteid,
   b.dvaanvraagnaam AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-W-WB-'::text || b.dnkeyomgvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbomgvergunning'::character varying(30) AS dvbron,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('W'::character(1)::text || b.dnkeyomgvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmomgvergunningen b
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraag AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtomgverg c ON b.dnkeysoortomgverg =
        c.dnkeysoortomgverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyomgvergunning =
        d.dnkeyomgvergunningen AND d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
WHERE NOT (b.dnkeyomgvergunning IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'WB'::text
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'W'::character(1) AS dvmodule,
   b.dnkeyomgvergunning AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraag AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvzaakcode,
   b.dvsoortaanvraag AS dvzaaktypeoms,
   b.dvgemeenteid,
   b.dvaanvraagnaam AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-W-PR-'::text || b.dnkeyomgvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbomgvergunning'::character varying(30) AS dvbron,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('W'::character(1)::text || b.dnkeyomgvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmomgvergunningen b
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraag AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtomgverg c ON b.dnkeysoortomgverg =
        c.dnkeysoortomgverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyomgvergunning =
        d.dnkeyomgvergunningen AND d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
WHERE NOT (b.dnkeyomgvergunning IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'PR'::text
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'W'::character(1) AS dvmodule,
   b.dnkeyomgvergunning AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraag AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvzaakcode,
   b.dvsoortaanvraag AS dvzaaktypeoms,
   b.dvgemeenteid,
   b.dvaanvraagnaam AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-W-SC-'::text || b.dnkeyomgvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbomgvergunning'::character varying(30) AS dvbron,
   'Staatscourant'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('W'::character(1)::text || b.dnkeyomgvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmomgvergunningen b
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraag AND a.dlinstaatscour = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtomgverg c ON b.dnkeysoortomgverg =
        c.dnkeysoortomgverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyomgvergunning =
        d.dnkeyomgvergunningen AND d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'SC'::text
WHERE NOT (b.dnkeyomgvergunning IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'SC'::text
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'W'::character(1) AS dvmodule,
   b.dnkeyomgvergunning AS dnkeyverg,
   'Ontwerp'::character varying(20) AS dvpubltype,
   b.ddontwerpbesl AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvzaakcode,
   b.dvsoortaanvraag AS dvzaaktypeoms,
   b.dvgemeenteid,
   b.dvaanvraagnaam AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('O-W-GB-'::text || b.dnkeyomgvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbomgvergunning'::character varying(30) AS dvbron,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('W'::character(1)::text || b.dnkeyomgvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmomgvergunningen b
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlontwerppubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontwerpbesl AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtomgverg c ON b.dnkeysoortomgverg =
        c.dnkeysoortomgverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyomgvergunning =
        d.dnkeyomgvergunningen AND d.dvpublicatietype = 'O'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
WHERE NOT (b.dnkeyomgvergunning IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'O'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GB'::text
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'W'::character(1) AS dvmodule,
   b.dnkeyomgvergunning AS dnkeyverg,
   'Ontwerp'::character varying(20) AS dvpubltype,
   b.ddontwerpbesl AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvzaakcode,
   b.dvsoortaanvraag AS dvzaaktypeoms,
   b.dvgemeenteid,
   b.dvaanvraagnaam AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('O-W-GS-'::text || b.dnkeyomgvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbomgvergunning'::character varying(30) AS dvbron,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('W'::character(1)::text || b.dnkeyomgvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmomgvergunningen b
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlontwerppubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontwerpbesl AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtomgverg c ON b.dnkeysoortomgverg =
        c.dnkeysoortomgverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyomgvergunning =
        d.dnkeyomgvergunningen AND d.dvpublicatietype = 'O'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
WHERE NOT (b.dnkeyomgvergunning IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'O'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GS'::text
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'W'::character(1) AS dvmodule,
   b.dnkeyomgvergunning AS dnkeyverg,
   'Ontwerp'::character varying(20) AS dvpubltype,
   b.ddontwerpbesl AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvzaakcode,
   b.dvsoortaanvraag AS dvzaaktypeoms,
   b.dvgemeenteid,
   b.dvaanvraagnaam AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('O-W-WB-'::text || b.dnkeyomgvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbomgvergunning'::character varying(30) AS dvbron,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('W'::character(1)::text || b.dnkeyomgvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmomgvergunningen b
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlontwerppubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontwerpbesl AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtomgverg c ON b.dnkeysoortomgverg =
        c.dnkeysoortomgverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyomgvergunning =
        d.dnkeyomgvergunningen AND d.dvpublicatietype = 'O'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
WHERE NOT (b.dnkeyomgvergunning IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'O'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'WB'::text
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'W'::character(1) AS dvmodule,
   b.dnkeyomgvergunning AS dnkeyverg,
   'Ontwerp'::character varying(20) AS dvpubltype,
   b.ddontwerpbesl AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvzaakcode,
   b.dvsoortaanvraag AS dvzaaktypeoms,
   b.dvgemeenteid,
   b.dvaanvraagnaam AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('O-W-PR-'::text || b.dnkeyomgvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbomgvergunning'::character varying(30) AS dvbron,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('W'::character(1)::text || b.dnkeyomgvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmomgvergunningen b
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlontwerppubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontwerpbesl AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtomgverg c ON b.dnkeysoortomgverg =
        c.dnkeysoortomgverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyomgvergunning =
        d.dnkeyomgvergunningen AND d.dvpublicatietype = 'O'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
WHERE NOT (b.dnkeyomgvergunning IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'O'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'PR'::text
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'W'::character(1) AS dvmodule,
   b.dnkeyomgvergunning AS dnkeyverg,
   'Ontwerp'::character varying(20) AS dvpubltype,
   b.ddontwerpbesl AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvzaakcode,
   b.dvsoortaanvraag AS dvzaaktypeoms,
   b.dvgemeenteid,
   b.dvaanvraagnaam AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('O-W-SC-'::text || b.dnkeyomgvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbomgvergunning'::character varying(30) AS dvbron,
   'Staatscourant'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('W'::character(1)::text || b.dnkeyomgvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmomgvergunningen b
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlontwerppubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontwerpbesl AND a.dlinstaatscour = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtomgverg c ON b.dnkeysoortomgverg =
        c.dnkeysoortomgverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyomgvergunning =
        d.dnkeyomgvergunningen AND d.dvpublicatietype = 'O'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'SC'::text
WHERE NOT (b.dnkeyomgvergunning IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'O'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'SC'::text
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'W'::character(1) AS dvmodule,
   b.dnkeyomgvergunning AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvzaakcode,
   b.dvsoortaanvraag AS dvzaaktypeoms,
   b.dvgemeenteid,
   b.dvaanvraagnaam AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-W-GB-'::text || b.dnkeyomgvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbomgvergunning'::character varying(30) AS dvbron,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('W'::character(1)::text || b.dnkeyomgvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmomgvergunningen b
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtomgverg c ON b.dnkeysoortomgverg =
        c.dnkeysoortomgverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyomgvergunning =
        d.dnkeyomgvergunningen AND d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
WHERE NOT (b.dnkeyomgvergunning IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GB'::text
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'W'::character(1) AS dvmodule,
   b.dnkeyomgvergunning AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvzaakcode,
   b.dvsoortaanvraag AS dvzaaktypeoms,
   b.dvgemeenteid,
   b.dvaanvraagnaam AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-W-GS-'::text || b.dnkeyomgvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbomgvergunning'::character varying(30) AS dvbron,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('W'::character(1)::text || b.dnkeyomgvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmomgvergunningen b
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtomgverg c ON b.dnkeysoortomgverg =
        c.dnkeysoortomgverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyomgvergunning =
        d.dnkeyomgvergunningen AND d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
WHERE NOT (b.dnkeyomgvergunning IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GS'::text
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'W'::character(1) AS dvmodule,
   b.dnkeyomgvergunning AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvzaakcode,
   b.dvsoortaanvraag AS dvzaaktypeoms,
   b.dvgemeenteid,
   b.dvaanvraagnaam AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-W-WB-'::text || b.dnkeyomgvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbomgvergunning'::character varying(30) AS dvbron,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('W'::character(1)::text || b.dnkeyomgvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmomgvergunningen b
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtomgverg c ON b.dnkeysoortomgverg =
        c.dnkeysoortomgverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyomgvergunning =
        d.dnkeyomgvergunningen AND d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
WHERE NOT (b.dnkeyomgvergunning IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'WB'::text
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'W'::character(1) AS dvmodule,
   b.dnkeyomgvergunning AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvzaakcode,
   b.dvsoortaanvraag AS dvzaaktypeoms,
   b.dvgemeenteid,
   b.dvaanvraagnaam AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-W-PR-'::text || b.dnkeyomgvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbomgvergunning'::character varying(30) AS dvbron,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('W'::character(1)::text || b.dnkeyomgvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmomgvergunningen b
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtomgverg c ON b.dnkeysoortomgverg =
        c.dnkeysoortomgverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyomgvergunning =
        d.dnkeyomgvergunningen AND d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
WHERE NOT (b.dnkeyomgvergunning IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'PR'::text
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'W'::character(1) AS dvmodule,
   b.dnkeyomgvergunning AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvzaakcode,
   b.dvsoortaanvraag AS dvzaaktypeoms,
   b.dvgemeenteid,
   b.dvaanvraagnaam AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-W-SC-'::text || b.dnkeyomgvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbomgvergunning'::character varying(30) AS dvbron,
   'Staatscourant'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('W'::character(1)::text || b.dnkeyomgvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmomgvergunningen b
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND a.dlinstaatscour = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtomgverg c ON b.dnkeysoortomgverg =
        c.dnkeysoortomgverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyomgvergunning =
        d.dnkeyomgvergunningen AND d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'SC'::text
WHERE NOT (b.dnkeyomgvergunning IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'SC'::text
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'O'::character(1) AS dvmodule,
   b.dnkeyovvergunning AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddovontvangst AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvovvergunningsnr AS dvzaakcode,
   b.dvsoortovvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvovwerkzaamheid, ''::character varying)::text || ' '::text)
       || b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-O-GB-'::text || b.dnkeyovvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbovvergunningen'::character varying(30) AS dvbron,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('O'::character(1)::text || b.dnkeyovvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmovvergunningen b
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddovontvangst AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtovvverg c ON b.dvcodesoortovverg =
        a.dvcodesoortovverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyovvergunning =
        d.dnkeyovvergunningen AND d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
WHERE NOT (b.dnkeyovvergunning IN (
   SELECT tbdroppublicaties.dnkeyovvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyovvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GB'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'O'::character(1) AS dvmodule,
   b.dnkeyovvergunning AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddovontvangst AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvovvergunningsnr AS dvzaakcode,
   b.dvsoortovvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvovwerkzaamheid, ''::character varying)::text || ' '::text)
       || b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-O-GS-'::text || b.dnkeyovvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbovvergunningen'::character varying(30) AS dvbron,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('O'::character(1)::text || b.dnkeyovvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmovvergunningen b
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddovontvangst AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtovvverg c ON b.dvcodesoortovverg =
        a.dvcodesoortovverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyovvergunning =
        d.dnkeyovvergunningen AND d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
WHERE NOT (b.dnkeyovvergunning IN (
   SELECT tbdroppublicaties.dnkeyovvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyovvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GS'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'O'::character(1) AS dvmodule,
   b.dnkeyovvergunning AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddovontvangst AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvovvergunningsnr AS dvzaakcode,
   b.dvsoortovvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvovwerkzaamheid, ''::character varying)::text || ' '::text)
       || b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-O-WB-'::text || b.dnkeyovvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbovvergunningen'::character varying(30) AS dvbron,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('O'::character(1)::text || b.dnkeyovvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmovvergunningen b
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddovontvangst AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtovvverg c ON b.dvcodesoortovverg =
        a.dvcodesoortovverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyovvergunning =
        d.dnkeyovvergunningen AND d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
WHERE NOT (b.dnkeyovvergunning IN (
   SELECT tbdroppublicaties.dnkeyovvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyovvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'WB'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'O'::character(1) AS dvmodule,
   b.dnkeyovvergunning AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddovontvangst AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvovvergunningsnr AS dvzaakcode,
   b.dvsoortovvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvovwerkzaamheid, ''::character varying)::text || ' '::text)
       || b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-O-PR-'::text || b.dnkeyovvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbovvergunningen'::character varying(30) AS dvbron,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('O'::character(1)::text || b.dnkeyovvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmovvergunningen b
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddovontvangst AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtovvverg c ON b.dvcodesoortovverg =
        a.dvcodesoortovverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyovvergunning =
        d.dnkeyovvergunningen AND d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
WHERE NOT (b.dnkeyovvergunning IN (
   SELECT tbdroppublicaties.dnkeyovvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyovvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'PR'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'O'::character(1) AS dvmodule,
   b.dnkeyovvergunning AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddovontvangst AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvovvergunningsnr AS dvzaakcode,
   b.dvsoortovvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvovwerkzaamheid, ''::character varying)::text || ' '::text)
       || b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-O-SC-'::text || b.dnkeyovvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbovvergunningen'::character varying(30) AS dvbron,
   'Staatscourant'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('O'::character(1)::text || b.dnkeyovvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmovvergunningen b
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddovontvangst AND a.dlinstaatscour = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtovvverg c ON b.dvcodesoortovverg =
        a.dvcodesoortovverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyovvergunning =
        d.dnkeyovvergunningen AND d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'SC'::text
WHERE NOT (b.dnkeyovvergunning IN (
   SELECT tbdroppublicaties.dnkeyovvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyovvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'SC'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'O'::character(1) AS dvmodule,
   b.dnkeyovvergunning AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddovbesluit AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvovvergunningsnr AS dvzaakcode,
   b.dvsoortovvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvovwerkzaamheid, ''::character varying)::text || ' '::text)
       || b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-O-GB-'::text || b.dnkeyovvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbovvergunningen'::character varying(30) AS dvbron,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('O'::character(1)::text || b.dnkeyovvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmovvergunningen b
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddovbesluit AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtovvverg c ON b.dvcodesoortovverg =
        a.dvcodesoortovverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyovvergunning =
        d.dnkeyovvergunningen AND d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
WHERE NOT (b.dnkeyovvergunning IN (
   SELECT tbdroppublicaties.dnkeyovvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyovvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GB'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'O'::character(1) AS dvmodule,
   b.dnkeyovvergunning AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddovbesluit AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvovvergunningsnr AS dvzaakcode,
   b.dvsoortovvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvovwerkzaamheid, ''::character varying)::text || ' '::text)
       || b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-O-GS-'::text || b.dnkeyovvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbovvergunningen'::character varying(30) AS dvbron,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('O'::character(1)::text || b.dnkeyovvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmovvergunningen b
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddovbesluit AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtovvverg c ON b.dvcodesoortovverg =
        a.dvcodesoortovverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyovvergunning =
        d.dnkeyovvergunningen AND d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
WHERE NOT (b.dnkeyovvergunning IN (
   SELECT tbdroppublicaties.dnkeyovvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyovvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GS'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'O'::character(1) AS dvmodule,
   b.dnkeyovvergunning AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddovbesluit AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvovvergunningsnr AS dvzaakcode,
   b.dvsoortovvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvovwerkzaamheid, ''::character varying)::text || ' '::text)
       || b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-O-WB-'::text || b.dnkeyovvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbovvergunningen'::character varying(30) AS dvbron,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('O'::character(1)::text || b.dnkeyovvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmovvergunningen b
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddovbesluit AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtovvverg c ON b.dvcodesoortovverg =
        a.dvcodesoortovverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyovvergunning =
        d.dnkeyovvergunningen AND d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
WHERE NOT (b.dnkeyovvergunning IN (
   SELECT tbdroppublicaties.dnkeyovvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyovvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'WB'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'O'::character(1) AS dvmodule,
   b.dnkeyovvergunning AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddovbesluit AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvovvergunningsnr AS dvzaakcode,
   b.dvsoortovvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvovwerkzaamheid, ''::character varying)::text || ' '::text)
       || b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-O-PR-'::text || b.dnkeyovvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbovvergunningen'::character varying(30) AS dvbron,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('O'::character(1)::text || b.dnkeyovvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmovvergunningen b
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddovbesluit AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtovvverg c ON b.dvcodesoortovverg =
        a.dvcodesoortovverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyovvergunning =
        d.dnkeyovvergunningen AND d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
WHERE NOT (b.dnkeyovvergunning IN (
   SELECT tbdroppublicaties.dnkeyovvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyovvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'PR'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'O'::character(1) AS dvmodule,
   b.dnkeyovvergunning AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddovbesluit AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvovvergunningsnr AS dvzaakcode,
   b.dvsoortovvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvovwerkzaamheid, ''::character varying)::text || ' '::text)
       || b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-O-SC-'::text || b.dnkeyovvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbovvergunningen'::character varying(30) AS dvbron,
   'Staatscourant'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('O'::character(1)::text || b.dnkeyovvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmovvergunningen b
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddovbesluit AND a.dlinstaatscour = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtovvverg c ON b.dvcodesoortovverg =
        a.dvcodesoortovverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyovvergunning =
        d.dnkeyovvergunningen AND d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'SC'::text
WHERE NOT (b.dnkeyovvergunning IN (
   SELECT tbdroppublicaties.dnkeyovvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyovvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'SC'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'C'::character(1) AS dvmodule,
   b.dnkeyhorvergunning AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraagdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvhorvergunningsnr AS dvzaakcode,
   b.dvsoorthorvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (('het uitbaten van '::text || b.dvsoortonderneming::text))::character
       varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-C-GB-'::text || b.dnkeyhorvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbhorecavergunningen'::character varying(30) AS dvbron,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('C'::character(1)::text || b.dnkeyhorvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmhorvergunningen b
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraagdatum AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrthorverg c ON b.dnkeysoortverg =
        a.dnkeysoorthorverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyhorvergunning =
        d.dnkeyhorecavergunningen AND d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
WHERE NOT (b.dnkeyhorvergunning IN (
   SELECT tbdroppublicaties.dnkeyhorecavergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyhorecavergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GB'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'C'::character(1) AS dvmodule,
   b.dnkeyhorvergunning AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraagdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvhorvergunningsnr AS dvzaakcode,
   b.dvsoorthorvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (('het uitbaten van '::text || b.dvsoortonderneming::text))::character
       varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-C-GS-'::text || b.dnkeyhorvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbhorecavergunningen'::character varying(30) AS dvbron,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('C'::character(1)::text || b.dnkeyhorvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmhorvergunningen b
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraagdatum AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrthorverg c ON b.dnkeysoortverg =
        a.dnkeysoorthorverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyhorvergunning =
        d.dnkeyhorecavergunningen AND d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
WHERE NOT (b.dnkeyhorvergunning IN (
   SELECT tbdroppublicaties.dnkeyhorecavergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyhorecavergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GS'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'C'::character(1) AS dvmodule,
   b.dnkeyhorvergunning AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraagdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvhorvergunningsnr AS dvzaakcode,
   b.dvsoorthorvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (('het uitbaten van '::text || b.dvsoortonderneming::text))::character
       varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-C-WB-'::text || b.dnkeyhorvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbhorecavergunningen'::character varying(30) AS dvbron,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('C'::character(1)::text || b.dnkeyhorvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmhorvergunningen b
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraagdatum AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrthorverg c ON b.dnkeysoortverg =
        a.dnkeysoorthorverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyhorvergunning =
        d.dnkeyhorecavergunningen AND d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
WHERE NOT (b.dnkeyhorvergunning IN (
   SELECT tbdroppublicaties.dnkeyhorecavergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyhorecavergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'WB'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'C'::character(1) AS dvmodule,
   b.dnkeyhorvergunning AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraagdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvhorvergunningsnr AS dvzaakcode,
   b.dvsoorthorvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (('het uitbaten van '::text || b.dvsoortonderneming::text))::character
       varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-C-PR-'::text || b.dnkeyhorvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbhorecavergunningen'::character varying(30) AS dvbron,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('C'::character(1)::text || b.dnkeyhorvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmhorvergunningen b
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraagdatum AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrthorverg c ON b.dnkeysoortverg =
        a.dnkeysoorthorverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyhorvergunning =
        d.dnkeyhorecavergunningen AND d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
WHERE NOT (b.dnkeyhorvergunning IN (
   SELECT tbdroppublicaties.dnkeyhorecavergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyhorecavergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'PR'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'C'::character(1) AS dvmodule,
   b.dnkeyhorvergunning AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraagdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvhorvergunningsnr AS dvzaakcode,
   b.dvsoorthorvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (('het uitbaten van '::text || b.dvsoortonderneming::text))::character
       varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-C-SC-'::text || b.dnkeyhorvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbhorecavergunningen'::character varying(30) AS dvbron,
   'Staatscourant'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('C'::character(1)::text || b.dnkeyhorvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmhorvergunningen b
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraagdatum AND a.dlinstaatscour = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrthorverg c ON b.dnkeysoortverg =
        a.dnkeysoorthorverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyhorvergunning =
        d.dnkeyhorecavergunningen AND d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'SC'::text
WHERE NOT (b.dnkeyhorvergunning IN (
   SELECT tbdroppublicaties.dnkeyhorecavergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyhorecavergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'SC'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'C'::character(1) AS dvmodule,
   b.dnkeyhorvergunning AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.dddatumbesluit AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvhorvergunningsnr AS dvzaakcode,
   b.dvsoorthorvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (('het uitbaten van '::text || b.dvsoortonderneming::text))::character
       varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-C-GB-'::text || b.dnkeyhorvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbhorecavergunningen'::character varying(30) AS dvbron,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('C'::character(1)::text || b.dnkeyhorvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmhorvergunningen b
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.dddatumbesluit AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrthorverg c ON b.dnkeysoortverg =
        a.dnkeysoorthorverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyhorvergunning =
        d.dnkeyhorecavergunningen AND d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
WHERE NOT (b.dnkeyhorvergunning IN (
   SELECT tbdroppublicaties.dnkeyhorecavergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyhorecavergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GB'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'C'::character(1) AS dvmodule,
   b.dnkeyhorvergunning AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.dddatumbesluit AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvhorvergunningsnr AS dvzaakcode,
   b.dvsoorthorvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (('het uitbaten van '::text || b.dvsoortonderneming::text))::character
       varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-C-GS-'::text || b.dnkeyhorvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbhorecavergunningen'::character varying(30) AS dvbron,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('C'::character(1)::text || b.dnkeyhorvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmhorvergunningen b
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.dddatumbesluit AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrthorverg c ON b.dnkeysoortverg =
        a.dnkeysoorthorverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyhorvergunning =
        d.dnkeyhorecavergunningen AND d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
WHERE NOT (b.dnkeyhorvergunning IN (
   SELECT tbdroppublicaties.dnkeyhorecavergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyhorecavergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GS'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'C'::character(1) AS dvmodule,
   b.dnkeyhorvergunning AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.dddatumbesluit AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvhorvergunningsnr AS dvzaakcode,
   b.dvsoorthorvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (('het uitbaten van '::text || b.dvsoortonderneming::text))::character
       varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-C-WB-'::text || b.dnkeyhorvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbhorecavergunningen'::character varying(30) AS dvbron,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('C'::character(1)::text || b.dnkeyhorvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmhorvergunningen b
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.dddatumbesluit AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrthorverg c ON b.dnkeysoortverg =
        a.dnkeysoorthorverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyhorvergunning =
        d.dnkeyhorecavergunningen AND d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
WHERE NOT (b.dnkeyhorvergunning IN (
   SELECT tbdroppublicaties.dnkeyhorecavergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyhorecavergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'WB'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'C'::character(1) AS dvmodule,
   b.dnkeyhorvergunning AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.dddatumbesluit AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvhorvergunningsnr AS dvzaakcode,
   b.dvsoorthorvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (('het uitbaten van '::text || b.dvsoortonderneming::text))::character
       varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-C-PR-'::text || b.dnkeyhorvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbhorecavergunningen'::character varying(30) AS dvbron,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('C'::character(1)::text || b.dnkeyhorvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmhorvergunningen b
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.dddatumbesluit AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrthorverg c ON b.dnkeysoortverg =
        a.dnkeysoorthorverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyhorvergunning =
        d.dnkeyhorecavergunningen AND d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
WHERE NOT (b.dnkeyhorvergunning IN (
   SELECT tbdroppublicaties.dnkeyhorecavergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyhorecavergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'PR'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'C'::character(1) AS dvmodule,
   b.dnkeyhorvergunning AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.dddatumbesluit AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvhorvergunningsnr AS dvzaakcode,
   b.dvsoorthorvergunning AS dvzaaktypeoms,
   b.dvgemeenteid,
   (('het uitbaten van '::text || b.dvsoortonderneming::text))::character
       varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-C-SC-'::text || b.dnkeyhorvergunning::character
       varying(20)::text))::character varying(27) AS dvid,
   'tbhorecavergunningen'::character varying(30) AS dvbron,
   'Staatscourant'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('C'::character(1)::text || b.dnkeyhorvergunning))::character varying(11)
       AS dvhoofdid
FROM vwfrmhorvergunningen b
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.dddatumbesluit AND a.dlinstaatscour = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrthorverg c ON b.dnkeysoortverg =
        a.dnkeysoorthorverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkeyhorvergunning =
        d.dnkeyhorecavergunningen AND d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'SC'::text
WHERE NOT (b.dnkeyhorvergunning IN (
   SELECT tbdroppublicaties.dnkeyhorecavergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyhorecavergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'SC'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'E'::character(1) AS dvmodule,
   b.dnkey AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddontvangstdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvvergnummer AS dvzaakcode,
   b.dvsoortmilverg AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvwerkzaamheid, ''::character varying)::text || ' '::text) ||
       b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-E-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'tbmilvergunningen'::character varying(30) AS dvbron,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM vwfrmmilvergunningen b
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontvangstdatum AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtmilverg c ON b.dnkeysoortmilverg =
        a.dnkeysoortmilverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeymilvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeymilvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GB'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'E'::character(1) AS dvmodule,
   b.dnkey AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddontvangstdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvvergnummer AS dvzaakcode,
   b.dvsoortmilverg AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvwerkzaamheid, ''::character varying)::text || ' '::text) ||
       b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-E-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'tbmilvergunningen'::character varying(30) AS dvbron,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM vwfrmmilvergunningen b
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontvangstdatum AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtmilverg c ON b.dnkeysoortmilverg =
        a.dnkeysoortmilverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeymilvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeymilvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GS'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'E'::character(1) AS dvmodule,
   b.dnkey AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddontvangstdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvvergnummer AS dvzaakcode,
   b.dvsoortmilverg AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvwerkzaamheid, ''::character varying)::text || ' '::text) ||
       b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-E-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'tbmilvergunningen'::character varying(30) AS dvbron,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM vwfrmmilvergunningen b
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontvangstdatum AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtmilverg c ON b.dnkeysoortmilverg =
        a.dnkeysoortmilverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeymilvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeymilvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'WB'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'E'::character(1) AS dvmodule,
   b.dnkey AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddontvangstdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvvergnummer AS dvzaakcode,
   b.dvsoortmilverg AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvwerkzaamheid, ''::character varying)::text || ' '::text) ||
       b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-E-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'tbmilvergunningen'::character varying(30) AS dvbron,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM vwfrmmilvergunningen b
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontvangstdatum AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtmilverg c ON b.dnkeysoortmilverg =
        a.dnkeysoortmilverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeymilvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeymilvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'PR'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'E'::character(1) AS dvmodule,
   b.dnkey AS dnkeyverg,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddontvangstdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvvergnummer AS dvzaakcode,
   b.dvsoortmilverg AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvwerkzaamheid, ''::character varying)::text || ' '::text) ||
       b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('A-E-SC-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'tbmilvergunningen'::character varying(30) AS dvbron,
   'Staatscourant'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM vwfrmmilvergunningen b
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontvangstdatum AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtmilverg c ON b.dnkeysoortmilverg =
        a.dnkeysoortmilverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'SC'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeymilvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeymilvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'SC'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'E'::character(1) AS dvmodule,
   b.dnkey AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvvergnummer AS dvzaakcode,
   b.dvsoortmilverg AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvwerkzaamheid, ''::character varying)::text || ' '::text) ||
       b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-E-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'tbmilvergunningen'::character varying(30) AS dvbron,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM vwfrmmilvergunningen b
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtmilverg c ON b.dnkeysoortmilverg =
        a.dnkeysoortmilverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeymilvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeymilvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GB'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'E'::character(1) AS dvmodule,
   b.dnkey AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvvergnummer AS dvzaakcode,
   b.dvsoortmilverg AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvwerkzaamheid, ''::character varying)::text || ' '::text) ||
       b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-E-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'tbmilvergunningen'::character varying(30) AS dvbron,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM vwfrmmilvergunningen b
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtmilverg c ON b.dnkeysoortmilverg =
        a.dnkeysoortmilverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeymilvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeymilvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GS'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'E'::character(1) AS dvmodule,
   b.dnkey AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvvergnummer AS dvzaakcode,
   b.dvsoortmilverg AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvwerkzaamheid, ''::character varying)::text || ' '::text) ||
       b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-E-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'tbmilvergunningen'::character varying(30) AS dvbron,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM vwfrmmilvergunningen b
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtmilverg c ON b.dnkeysoortmilverg =
        a.dnkeysoortmilverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeymilvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeymilvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'WB'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'E'::character(1) AS dvmodule,
   b.dnkey AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvvergnummer AS dvzaakcode,
   b.dvsoortmilverg AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvwerkzaamheid, ''::character varying)::text || ' '::text) ||
       b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-E-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'tbmilvergunningen'::character varying(30) AS dvbron,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM vwfrmmilvergunningen b
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtmilverg c ON b.dnkeysoortmilverg =
        a.dnkeysoortmilverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeymilvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeymilvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'PR'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT 'E'::character(1) AS dvmodule,
   b.dnkey AS dnkeyverg,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   b.dvvergnummer AS dvzaakcode,
   b.dvsoortmilverg AS dvzaaktypeoms,
   b.dvgemeenteid,
   (((COALESCE(b.dvwerkzaamheid, ''::character varying)::text || ' '::text) ||
       b.dvpublbouwwerk::text))::character varying(250) AS dvbetreft,
   a.dvgemeentenaam,
   b.dvobjplaats,
   b.dvobjpostcode,
   b.dvobjstraat,
   b.dvobjhuisnummer,
   b.dvobjhuisletter,
   b.dvobjhuisnrtoevoeg,
   b.dnxcoordinaat,
   b.dnycoordinaat,
   c.dnkeycompartiment,
   (('B-E-SC-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'tbmilvergunningen'::character varying(30) AS dvbron,
   'Staatscourant'::character varying(30) AS dvomsmediatype,
   b.dvaardbesluit,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM vwfrmmilvergunningen b
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = b.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN vwfrmcubegemcompsrtmilverg c ON b.dnkeysoortmilverg =
        a.dnkeysoortmilverg AND c.dvgemeenteid::text = b.dvgemeenteid::text
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'SC'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeymilvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeymilvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'SC'::text
   )) AND b.dlzaakpubliceren = 'T'::bpchar;

COMMENT ON VIEW public.vwfrmtepubliceren
IS 'DEPRECATED sinds databaseversie 3.1.25. Functie overgenomen door o.a. vwfrmzakentepubliceren. View van zaken die gepubliceerd dienen te worden (aanvraag, ontwerpbesluit, beschikking) uit tbomgvergunning, tbovvergunningen, tbhorecavergunningen en tbmilvergunningen';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvmodule
IS 'Gehele view is DEPRECATED sinds databaseversie 3.1.25/ W indien de te publiceren zaak uit tbomgvergunning komt, E indien uit tbmilvergunningen, C indien uit tbhorecavergunningen en O indien uit tbovvergunningen.';

COMMENT ON COLUMN public.vwfrmtepubliceren.dnkeyverg
IS 'Primary key van de zaak in tbomgvergunning bij dvmodule = W, in tbmilvergunningen bij dvmodule = E, in tbhorecavergunningen bij dvmodule = C, in tbovvergunningen bij dvmodule = O.';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvpubltype
IS 'Aanvraag, Ontwerp, Besluit afhankelijk waardoor het publiceren getriggerd wordt';

COMMENT ON COLUMN public.vwfrmtepubliceren.ddtriggerdatum
IS 'Aanvraagdatum, Ontwerpbesluitdatum of Besluit/afgehandeld datum afhankelijk waardoor het publiceren getriggerd wordt';

COMMENT ON COLUMN public.vwfrmtepubliceren.ddpublexport
IS 'Laatste datum dat een poging gedaan is om de zaak onder het publicatietype te publiceren';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvpublfout
IS 'Foutcode die door DROP is geretourneerd bij de laatste keer dat een poging gedaan is om de zaak onder het publicatietype te publiceren';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvzaakcode
IS 'De OpenWave zaakcode';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvzaaktypeoms
IS 'Zaaktype omschrijving';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvgemeenteid
IS 'De gemeenteid van de lokatie waar de zaak aan gekoppeld is';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvbetreft
IS 'Korte omschrijving van de zaak';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvgemeentenaam
IS 'De gemeentenaam van de lokatie waar de zaak aan gekoppeld is';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvobjplaats
IS 'Woonplaatsnaam van de lokatie waar de zaak aan gekoppeld is';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvobjstraat
IS 'Sraatnaam van de lokatie waar de zaak aan gekoppeld is';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvobjhuisnummer
IS 'Huisnummer van de lokatie waar de zaak aan gekoppeld is';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvobjhuisletter
IS 'Huisletter van de lokatie waar de zaak aan gekoppeld is';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvobjhuisnrtoevoeg
IS 'Huisnummertoevoeging van de lokatie waar de zaak aan gekoppeld is';

COMMENT ON COLUMN public.vwfrmtepubliceren.dnxcoordinaat
IS 'x-ccordinaat van de lokatie waar de zaak aan gekoppeld is';

COMMENT ON COLUMN public.vwfrmtepubliceren.dnycoordinaat
IS 'y-ccordinaat van de lokatie waar de zaak aan gekoppeld is';

COMMENT ON COLUMN public.vwfrmtepubliceren.dnkeycompartiment
IS 'De primary key van tbcompartiment indien de combinatie gemeente/zaaktype daaraan gekoppeld is ';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvid
IS 'Unieke identfier van de rij door de combinatie van  de eerste letter van dvpubltype, een hyphen , dvmodule, een hyphen mediatype , een hyphen en dnkeyverg';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvbron
IS 'tbomgvergunning, tbovvergunningen, tbhorecavergunningen of tbmilvergunningen';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvomsmediatype
IS 'Gemeenteblad, BladGemeenschappelijkeRegeling, waterschapblad, provinciaalblad of staatcourant';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvaardbesluit
IS 'De resultaat-omschrijving van het besluit bijv. verleend, gedeeltelijk geweigerd, afgehandeld';

COMMENT ON COLUMN public.vwfrmtepubliceren.dvhoofdid
IS 'Moduleletter + dnkeyverg. Tezamen foreinkey naar vwfrmzakentepubliceren.dvid'; 


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