Datadictionary OpenWave 3.1.32

View: vwfrmmilvergdetailstepubliceren

Schema

public

Owner

waveuser005

Descriptions

View van te publiceren momenten (DROP) bij milieu/gebruikzaken (per zaak per medium per type)

Fields

Rules

Indices

There are no indices for table vwfrmmilvergdetailstepubliceren

Definition

CREATE VIEW public.vwfrmmilvergdetailstepubliceren (
   dnkeymilvergunning,
   dvpubltype,
   ddtriggerdatum,
   ddpublexport,
   dvpublfout,
   dvid,
   dvomsmediatype,
   dvhoofdid)
AS
WITH const AS (
SELECT COALESCE(tbinitialisatie.dfnumber1, 0::double precision)::integer AS val1
FROM tbinitialisatie
WHERE lower(tbinitialisatie.dvsectie::text) = 'droppublicaties'::text AND
   lower(tbinitialisatie.dvitem::text) = 'milieugebruik'::text
       )
   SELECT b.dnkey AS dnkeymilvergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddontvangstdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-E-OL-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'OnLine'::character varying(30) AS dvomsmediatype,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbmilvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN tbmilinrichtingen f ON b.dnkeymilinrichtingen = f.dnkey
    JOIN tbperceeladressen x ON f.dnkeyperceeladressen = x.dnkey
    JOIN tbopenbareruimte y ON x.dnkeyopenbruimte = y.dnkey
    JOIN tbwoonplaats z ON y.dnkeywoonplaats = z.dnkey
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, z.dvwoonplaatsid)::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontvangstdatum AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddontvangstdatum AND a.dlinwaterschap = 'F'::bpchar AND
           a.dlingemeenteblad = 'F'::bpchar AND a.dlingemeenschap = 'F'::bpchar AND a.dlinprovincie = 'F'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'OL'::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 b.dlzaakpubliceren = 'T'::bpchar AND
           instr(COALESCE(b.dvdroppublicatietypes, 'A'::character varying), 'A'::character varying) > 0
   UNION ALL
   SELECT b.dnkey AS dnkeymilvergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddontvangstdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-E-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbmilvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN tbmilinrichtingen f ON b.dnkeymilinrichtingen = f.dnkey
    JOIN tbperceeladressen x ON f.dnkeyperceeladressen = x.dnkey
    JOIN tbopenbareruimte y ON x.dnkeyopenbruimte = y.dnkey
    JOIN tbwoonplaats z ON y.dnkeywoonplaats = z.dnkey
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, z.dvwoonplaatsid)::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontvangstdatum AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddontvangstdatum AND a.dlingemeenteblad = 'T'::bpchar
    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 AND
           instr(COALESCE(b.dvdroppublicatietypes, 'A'::character varying), 'A'::character varying) > 0
   UNION ALL
   SELECT b.dnkey AS dnkeymilvergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddontvangstdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-E-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbmilvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN tbmilinrichtingen f ON b.dnkeymilinrichtingen = f.dnkey
    JOIN tbperceeladressen x ON f.dnkeyperceeladressen = x.dnkey
    JOIN tbopenbareruimte y ON x.dnkeyopenbruimte = y.dnkey
    JOIN tbwoonplaats z ON y.dnkeywoonplaats = z.dnkey
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, z.dvwoonplaatsid)::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontvangstdatum AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddontvangstdatum AND a.dlingemeenschap = 'T'::bpchar
    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 AND
           instr(COALESCE(b.dvdroppublicatietypes, 'A'::character varying), 'A'::character varying) > 0
   UNION ALL
   SELECT b.dnkey AS dnkeymilvergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddontvangstdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-E-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbmilvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN tbmilinrichtingen f ON b.dnkeymilinrichtingen = f.dnkey
    JOIN tbperceeladressen x ON f.dnkeyperceeladressen = x.dnkey
    JOIN tbopenbareruimte y ON x.dnkeyopenbruimte = y.dnkey
    JOIN tbwoonplaats z ON y.dnkeywoonplaats = z.dnkey
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, z.dvwoonplaatsid)::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontvangstdatum AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddontvangstdatum AND a.dlinwaterschap = 'T'::bpchar
    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 AND
           instr(COALESCE(b.dvdroppublicatietypes, 'A'::character varying), 'A'::character varying) > 0
   UNION ALL
   SELECT b.dnkey AS dnkeymilvergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddontvangstdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-E-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbmilvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN tbmilinrichtingen f ON b.dnkeymilinrichtingen = f.dnkey
    JOIN tbperceeladressen x ON f.dnkeyperceeladressen = x.dnkey
    JOIN tbopenbareruimte y ON x.dnkeyopenbruimte = y.dnkey
    JOIN tbwoonplaats z ON y.dnkeywoonplaats = z.dnkey
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, z.dvwoonplaatsid)::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddontvangstdatum AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddontvangstdatum AND a.dlinprovincie = 'T'::bpchar
    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 AND
           instr(COALESCE(b.dvdroppublicatietypes, 'A'::character varying), 'A'::character varying) > 0
   UNION ALL
   SELECT b.dnkey AS dnkeymilvergunning,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-E-OL-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'OnLine'::character varying(30) AS dvomsmediatype,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbmilvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN tbmilinrichtingen f ON b.dnkeymilinrichtingen = f.dnkey
    JOIN tbperceeladressen x ON f.dnkeyperceeladressen = x.dnkey
    JOIN tbopenbareruimte y ON x.dnkeyopenbruimte = y.dnkey
    JOIN tbwoonplaats z ON y.dnkeywoonplaats = z.dnkey
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, z.dvwoonplaatsid)::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddbesluitdatum AND a.dlinwaterschap = 'F'::bpchar AND
           a.dlingemeenteblad = 'F'::bpchar AND a.dlingemeenschap = 'F'::bpchar AND a.dlinprovincie = 'F'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'OL'::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 b.dlzaakpubliceren = 'T'::bpchar AND
           instr(COALESCE(b.dvdroppublicatietypes, 'B'::character varying), 'B'::character varying) > 0
   UNION ALL
   SELECT b.dnkey AS dnkeymilvergunning,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-E-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbmilvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN tbmilinrichtingen f ON b.dnkeymilinrichtingen = f.dnkey
    JOIN tbperceeladressen x ON f.dnkeyperceeladressen = x.dnkey
    JOIN tbopenbareruimte y ON x.dnkeyopenbruimte = y.dnkey
    JOIN tbwoonplaats z ON y.dnkeywoonplaats = z.dnkey
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, z.dvwoonplaatsid)::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddbesluitdatum AND a.dlingemeenteblad = 'T'::bpchar
    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 AND
           instr(COALESCE(b.dvdroppublicatietypes, 'B'::character varying), 'B'::character varying) > 0
   UNION ALL
   SELECT b.dnkey AS dnkeymilvergunning,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-E-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbmilvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN tbmilinrichtingen f ON b.dnkeymilinrichtingen = f.dnkey
    JOIN tbperceeladressen x ON f.dnkeyperceeladressen = x.dnkey
    JOIN tbopenbareruimte y ON x.dnkeyopenbruimte = y.dnkey
    JOIN tbwoonplaats z ON y.dnkeywoonplaats = z.dnkey
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, z.dvwoonplaatsid)::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddbesluitdatum AND a.dlingemeenschap = 'T'::bpchar
    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 AND
           instr(COALESCE(b.dvdroppublicatietypes, 'B'::character varying), 'B'::character varying) > 0
   UNION ALL
   SELECT b.dnkey AS dnkeymilvergunning,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-E-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbmilvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN tbmilinrichtingen f ON b.dnkeymilinrichtingen = f.dnkey
    JOIN tbperceeladressen x ON f.dnkeyperceeladressen = x.dnkey
    JOIN tbopenbareruimte y ON x.dnkeyopenbruimte = y.dnkey
    JOIN tbwoonplaats z ON y.dnkeywoonplaats = z.dnkey
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, z.dvwoonplaatsid)::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddbesluitdatum AND a.dlinwaterschap = 'T'::bpchar
    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 AND
           instr(COALESCE(b.dvdroppublicatietypes, 'B'::character varying), 'B'::character varying) > 0
   UNION ALL
   SELECT b.dnkey AS dnkeymilvergunning,
   'Besluit'::character varying(20) AS dvpubltype,
   b.ddbesluitdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-E-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbmilvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN tbmilinrichtingen f ON b.dnkeymilinrichtingen = f.dnkey
    JOIN tbperceeladressen x ON f.dnkeyperceeladressen = x.dnkey
    JOIN tbopenbareruimte y ON x.dnkeyopenbruimte = y.dnkey
    JOIN tbwoonplaats z ON y.dnkeywoonplaats = z.dnkey
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, z.dvwoonplaatsid)::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddbesluitdatum AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddbesluitdatum AND a.dlinprovincie = 'T'::bpchar
    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 AND
           instr(COALESCE(b.dvdroppublicatietypes, 'B'::character varying), 'B'::character varying) > 0
   UNION ALL
   SELECT b.dnkey AS dnkeymilvergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-E-OL-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'OnLine'::character varying(30) AS dvomsmediatype,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbmilvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN tbmilinrichtingen f ON b.dnkeymilinrichtingen = f.dnkey
    JOIN tbperceeladressen x ON f.dnkeyperceeladressen = x.dnkey
    JOIN tbopenbareruimte y ON x.dnkeyopenbruimte = y.dnkey
    JOIN tbwoonplaats z ON y.dnkeywoonplaats = z.dnkey
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, z.dvwoonplaatsid)::text AND a.dlingetrokpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddingetrokken AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddingetrokken AND a.dlinwaterschap = 'F'::bpchar AND
           a.dlingemeenteblad = 'F'::bpchar AND a.dlingemeenschap = 'F'::bpchar AND a.dlinprovincie = 'F'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'OL'::text
   WHERE NOT (b.dnkey IN (
       SELECT tbdroppublicaties.dnkeymilvergunningen
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeymilvergunningen IS NOT NULL AND
           tbdroppublicaties.dvpublicatietype = 'I'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL
       )) AND b.dlzaakpubliceren = 'T'::bpchar AND
           instr(COALESCE(b.dvdroppublicatietypes, 'I'::character varying), 'I'::character varying) > 0
   UNION ALL
   SELECT b.dnkey AS dnkeymilvergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-E-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbmilvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN tbmilinrichtingen f ON b.dnkeymilinrichtingen = f.dnkey
    JOIN tbperceeladressen x ON f.dnkeyperceeladressen = x.dnkey
    JOIN tbopenbareruimte y ON x.dnkeyopenbruimte = y.dnkey
    JOIN tbwoonplaats z ON y.dnkeywoonplaats = z.dnkey
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, z.dvwoonplaatsid)::text AND a.dlingetrokpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddingetrokken AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddingetrokken AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'I'::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 = 'I'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GB'::text
       )) AND b.dlzaakpubliceren = 'T'::bpchar AND
           instr(COALESCE(b.dvdroppublicatietypes, 'I'::character varying), 'I'::character varying) > 0
   UNION ALL
   SELECT b.dnkey AS dnkeymilvergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-E-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbmilvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN tbmilinrichtingen f ON b.dnkeymilinrichtingen = f.dnkey
    JOIN tbperceeladressen x ON f.dnkeyperceeladressen = x.dnkey
    JOIN tbopenbareruimte y ON x.dnkeyopenbruimte = y.dnkey
    JOIN tbwoonplaats z ON y.dnkeywoonplaats = z.dnkey
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, z.dvwoonplaatsid)::text AND a.dlingetrokpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddingetrokken AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddingetrokken AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'I'::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 = 'I'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GS'::text
       )) AND b.dlzaakpubliceren = 'T'::bpchar AND
           instr(COALESCE(b.dvdroppublicatietypes, 'I'::character varying), 'I'::character varying) > 0
   UNION ALL
   SELECT b.dnkey AS dnkeymilvergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-E-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbmilvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN tbmilinrichtingen f ON b.dnkeymilinrichtingen = f.dnkey
    JOIN tbperceeladressen x ON f.dnkeyperceeladressen = x.dnkey
    JOIN tbopenbareruimte y ON x.dnkeyopenbruimte = y.dnkey
    JOIN tbwoonplaats z ON y.dnkeywoonplaats = z.dnkey
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, z.dvwoonplaatsid)::text AND a.dlingetrokpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddingetrokken AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddingetrokken AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'I'::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 = 'I'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'WB'::text
       )) AND b.dlzaakpubliceren = 'T'::bpchar AND
           instr(COALESCE(b.dvdroppublicatietypes, 'I'::character varying), 'I'::character varying) > 0
   UNION ALL
   SELECT b.dnkey AS dnkeymilvergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-E-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   (('E'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbmilvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN tbmilinrichtingen f ON b.dnkeymilinrichtingen = f.dnkey
    JOIN tbperceeladressen x ON f.dnkeyperceeladressen = x.dnkey
    JOIN tbopenbareruimte y ON x.dnkeyopenbruimte = y.dnkey
    JOIN tbwoonplaats z ON y.dnkeywoonplaats = z.dnkey
    JOIN vwfrmmilkoppublgemzaak a ON b.dnkeysoortmilverg = a.dnkeysoortmilverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, z.dvwoonplaatsid)::text AND a.dlingetrokpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddingetrokken AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddingetrokken AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeymilvergunningen AND
        d.dvpublicatietype = 'I'::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 = 'I'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'PR'::text
       )) AND b.dlzaakpubliceren = 'T'::bpchar AND
           instr(COALESCE(b.dvdroppublicatietypes, 'I'::character varying), 'I'::character varying) > 0;

COMMENT ON VIEW public.vwfrmmilvergdetailstepubliceren
IS 'View van te publiceren momenten (DROP) bij milieu/gebruikzaken (per zaak per medium per type)';

COMMENT ON COLUMN public.vwfrmmilvergdetailstepubliceren.dnkeymilvergunning
IS 'Primary key van de zaak in tbmilvergunningen';

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

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

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

COMMENT ON COLUMN public.vwfrmmilvergdetailstepubliceren.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.vwfrmmilvergdetailstepubliceren.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.vwfrmmilvergdetailstepubliceren.dvomsmediatype
IS 'OnLine, Gemeenteblad, BladGemeenschappelijkeRegeling, waterschapblad, provinciaalblad of staatcourant';

COMMENT ON COLUMN public.vwfrmmilvergdetailstepubliceren.dvhoofdid
IS 'Moduleletter (C) + dnkeymilvergunning';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 2-5-2025 13:49