Datadictionary OpenWave 3.1.25
 Previous topic Chapter index Next topic

View: vwfrmomgdetailstepubliceren

 

 

Schema

public

 

Owner

waveuser005

 

Descriptions

View van te publiceren momenten (DROP) bij omgevingzaken (per zaak. per medium per type)

 

Fields

Name

Data type

Description

dnkeyomgvergunning

integer

Primary key van de zaak in tbomgvergunning

dvpubltype

varchar(20)

Aanvraag, Verlengd, Opgeschort, Ingetrokken, Ontwerp, Besluit afhankelijk waardoor het publiceren getriggerd wordt

ddtriggerdatum

timestamp

Aanvraagdatum, Ontwerpbesluitdatum of Besluit/afgehandeld datum of opgeschortvanafdatum of verrdaagdvanafdatum of ingetrokken 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

dvid

varchar(27)

Unieke identfier van de rij door de combinatie van de eerste letter van dvpubltype (A,V,S,O,I of B), een hyphen , moduleletter: W, een hyphen mediatype (OL,GB,GS,PR,WB.SC) , een hyphen en dnkeyverg

dvomsmediatype

varchar(30)

OnLine, Gemeenteblad, BladGemeenschappelijkeRegeling, waterschapblad, provinciaalblad of staatcourant

dvhoofdid

varchar(11)

Moduleletter (W) + dnkeyomgvergunning.

 

Rules

Name

Event

Instead

Condition

Description

_RETURN

SELECT

 

 

 

Indices

There are no indices for table vwfrmomgdetailstepubliceren

 

Definition

CREATE VIEW public.vwfrmomgdetailstepubliceren (
   dnkeyomgvergunning,
   dvpubltype,
   ddtriggerdatum,
   ddpublexport,
   dvpublfout,
   dvid,
   dvomsmediatype,
   dvhoofdid)
AS
SELECT b.dnkey AS dnkeyomgvergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraag AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-W-OL-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'OnLine'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraag AND a.dlinwaterschap = 'F'::bpchar AND a.dlingemeenteblad = 'F'::bpchar AND a.dlinstaatscour = 'F'::bpchar AND a.dlingemeenschap = 'F'::bpchar AND a.dlinprovincie = 'F'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'OL'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT b.dnkey AS dnkeyomgvergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraag AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-W-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraag AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
WHERE NOT (b.dnkey 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 b.dnkey AS dnkeyomgvergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraag AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-W-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraag AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
WHERE NOT (b.dnkey 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 b.dnkey AS dnkeyomgvergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraag AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-W-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraag AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
WHERE NOT (b.dnkey 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 b.dnkey AS dnkeyomgvergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraag AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-W-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraag AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
WHERE NOT (b.dnkey 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 b.dnkey AS dnkeyomgvergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraag AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-W-SC-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Staatscourant'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraag AND a.dlinstaatscour = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'SC'::text
WHERE NOT (b.dnkey 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 b.dnkey AS dnkeyomgvergunning,
   'Ontwerp'::character varying(20) AS dvpubltype,
   b.ddontwerpbesl AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('O-W-OL-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'OnLine'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlontwerppubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontwerpbesl AND a.dlinwaterschap = 'F'::bpchar AND a.dlingemeenteblad = 'F'::bpchar AND a.dlinstaatscour = 'F'::bpchar AND a.dlingemeenschap = 'F'::bpchar AND a.dlinprovincie = 'F'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'O'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'OL'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'O'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT b.dnkey AS dnkeyomgvergunning,
   'Ontwerp'::character varying(20) AS dvpubltype,
   b.ddontwerpbesl AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('O-W-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlontwerppubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontwerpbesl AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'O'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
WHERE NOT (b.dnkey 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 b.dnkey AS dnkeyomgvergunning,
   'Ontwerp'::character varying(20) AS dvpubltype,
   b.ddontwerpbesl AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('O-W-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlontwerppubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontwerpbesl AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'O'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
WHERE NOT (b.dnkey 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 b.dnkey AS dnkeyomgvergunning,
   'Ontwerp'::character varying(20) AS dvpubltype,
   b.ddontwerpbesl AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('O-W-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlontwerppubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontwerpbesl AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'O'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
WHERE NOT (b.dnkey 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 b.dnkey AS dnkeyomgvergunning,
   'Ontwerp'::character varying(20) AS dvpubltype,
   b.ddontwerpbesl AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('O-W-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlontwerppubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontwerpbesl AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'O'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
WHERE NOT (b.dnkey 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 b.dnkey AS dnkeyomgvergunning,
   'Ontwerp'::character varying(20) AS dvpubltype,
   b.ddontwerpbesl AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('O-W-SC-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Staatscourant'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlontwerppubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontwerpbesl AND a.dlinstaatscour = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'O'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'SC'::text
WHERE NOT (b.dnkey 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 b.dnkey AS dnkeyomgvergunning,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-W-OL-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'OnLine'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND a.dlinwaterschap = 'F'::bpchar AND a.dlingemeenteblad = 'F'::bpchar AND a.dlinstaatscour = 'F'::bpchar AND a.dlingemeenschap = 'F'::bpchar AND a.dlinprovincie = 'F'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'OL'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT b.dnkey AS dnkeyomgvergunning,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-W-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
WHERE NOT (b.dnkey 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 b.dnkey AS dnkeyomgvergunning,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-W-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
WHERE NOT (b.dnkey 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 b.dnkey AS dnkeyomgvergunning,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-W-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
WHERE NOT (b.dnkey 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 b.dnkey AS dnkeyomgvergunning,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-W-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
WHERE NOT (b.dnkey 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 b.dnkey AS dnkeyomgvergunning,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-W-SC-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Staatscourant'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND a.dlinstaatscour = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'SC'::text
WHERE NOT (b.dnkey 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 b.dnkey AS dnkeyomgvergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-W-OL-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'OnLine'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlingetrokpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddingetrokken AND a.dlinwaterschap = 'F'::bpchar AND a.dlingemeenteblad = 'F'::bpchar AND a.dlinstaatscour = 'F'::bpchar AND a.dlingemeenschap = 'F'::bpchar AND a.dlinprovincie = 'F'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'OL'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'I'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT b.dnkey AS dnkeyomgvergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-W-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlingetrokpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddingetrokken AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'I'::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 b.dnkey AS dnkeyomgvergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-W-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlingetrokpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddingetrokken AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'I'::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 b.dnkey AS dnkeyomgvergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-W-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlingetrokpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddingetrokken AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'I'::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 b.dnkey AS dnkeyomgvergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-W-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlingetrokpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddingetrokken AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'I'::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 b.dnkey AS dnkeyomgvergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-W-SC-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Staatscourant'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlingetrokpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddingetrokken AND a.dlinstaatscour = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'SC'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'I'::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 b.dnkey AS dnkeyomgvergunning,
   'Verlengd'::character varying(20) AS dvpubltype,
   b.ddverdaagdvanaf AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('V-W-OL-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'OnLine'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlverlengingpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddverdaagdvanaf AND a.dlinwaterschap = 'F'::bpchar AND a.dlingemeenteblad = 'F'::bpchar AND a.dlinstaatscour = 'F'::bpchar AND a.dlingemeenschap = 'F'::bpchar AND a.dlinprovincie = 'F'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'V'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'OL'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'V'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT b.dnkey AS dnkeyomgvergunning,
   'Verlengd'::character varying(20) AS dvpubltype,
   b.ddverdaagdvanaf AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('V-W-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlverlengingpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddverdaagdvanaf AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'V'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'V'::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 b.dnkey AS dnkeyomgvergunning,
   'Verlengd'::character varying(20) AS dvpubltype,
   b.ddverdaagdvanaf AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('V-W-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlverlengingpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddverdaagdvanaf AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'V'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'V'::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 b.dnkey AS dnkeyomgvergunning,
   'Verlengd'::character varying(20) AS dvpubltype,
   b.ddverdaagdvanaf AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('V-W-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlverlengingpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddverdaagdvanaf AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'V'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'V'::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 b.dnkey AS dnkeyomgvergunning,
   'Verlengd'::character varying(20) AS dvpubltype,
   b.ddverdaagdvanaf AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('V-W-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlverlengingpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddverdaagdvanaf AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'V'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'V'::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 b.dnkey AS dnkeyomgvergunning,
   'Verlengd'::character varying(20) AS dvpubltype,
   b.ddverdaagdvanaf AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('V-W-SC-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Staatscourant'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlverlengingpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddverdaagdvanaf AND a.dlinstaatscour = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'V'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'SC'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'V'::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 b.dnkey AS dnkeyomgvergunning,
   'Opgeschort'::character varying(20) AS dvpubltype,
   b.ddopschortingvanaf AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('S-W-OL-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'OnLine'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlopschortpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddopschortingvanaf AND a.dlinwaterschap = 'F'::bpchar AND a.dlingemeenteblad = 'F'::bpchar AND a.dlinstaatscour = 'F'::bpchar AND a.dlingemeenschap = 'F'::bpchar AND a.dlinprovincie = 'F'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'S'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'OL'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'S'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar
UNION ALL
SELECT b.dnkey AS dnkeyomgvergunning,
   'Opgeschort'::character varying(20) AS dvpubltype,
   b.ddopschortingvanaf AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('S-W-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlopschortpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddopschortingvanaf AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'S'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'S'::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 b.dnkey AS dnkeyomgvergunning,
   'Opgeschort'::character varying(20) AS dvpubltype,
   b.ddopschortingvanaf AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('S-W-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlopschortpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddopschortingvanaf AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'S'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'S'::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 b.dnkey AS dnkeyomgvergunning,
   'Opgeschort'::character varying(20) AS dvpubltype,
   b.ddopschortingvanaf AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('S-W-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlopschortpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddopschortingvanaf AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'S'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'S'::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 b.dnkey AS dnkeyomgvergunning,
   'Opgeschort'::character varying(20) AS dvpubltype,
   b.ddopschortingvanaf AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('S-W-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlopschortpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddopschortingvanaf AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'S'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'S'::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 b.dnkey AS dnkeyomgvergunning,
   'Opgeschort'::character varying(20) AS dvpubltype,
   b.ddopschortingvanaf AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('S-W-SC-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Staatscourant'::character varying(30) AS dvomsmediatype,
   (('W'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
FROM tbomgvergunning b
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmomgkoppublgemzaak a ON b.dnkeysoortomgverg = a.dnkeysoortomgverg
        AND a.dvgemeenteid::text = e.dvgemeenteid::text AND a.dlopschortpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddopschortingvanaf AND a.dlinstaatscour = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyomgvergunningen AND
        d.dvpublicatietype = 'S'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'SC'::text
WHERE NOT (b.dnkey IN (
   SELECT tbdroppublicaties.dnkeyomgvergunningen
   FROM tbdroppublicaties
   WHERE tbdroppublicaties.dnkeyomgvergunningen IS NOT NULL AND
       tbdroppublicaties.dvpublicatietype = 'S'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'SC'::text
   )) AND b.dnkeyparentverg IS NULL AND b.dlzaakpubliceren = 'T'::bpchar;

COMMENT ON VIEW public.vwfrmomgdetailstepubliceren
IS 'View van te publiceren momenten (DROP) bij omgevingzaken (per zaak. per medium per type)';

COMMENT ON COLUMN public.vwfrmomgdetailstepubliceren.dnkeyomgvergunning
IS 'Primary key van de zaak in tbomgvergunning ';

COMMENT ON COLUMN public.vwfrmomgdetailstepubliceren.dvpubltype
IS 'Aanvraag, Verlengd, Opgeschort, Ingetrokken, Ontwerp, Besluit afhankelijk waardoor het publiceren getriggerd wordt';

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

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

COMMENT ON COLUMN public.vwfrmomgdetailstepubliceren.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.vwfrmomgdetailstepubliceren.dvid
IS 'Unieke identfier van de rij door de combinatie van  de eerste letter van dvpubltype (A,V,S,O,I of B), een hyphen , moduleletter: W, een hyphen mediatype (OL,GB,GS,PR,WB.SC) , een hyphen en dnkeyverg';

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

COMMENT ON COLUMN public.vwfrmomgdetailstepubliceren.dvhoofdid
IS 'Moduleletter (W) + dnkeyomgvergunning.'; 


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