Datadictionary OpenWave 3.1.32

View: vwfrmovdetailstepubliceren

Schema

public

Owner

waveuser005

Descriptions

View van te publiceren momenten (DROP) bij APV/Overigzaken (per zaak per medium per type)

Fields

Rules

Indices

There are no indices for table vwfrmovdetailstepubliceren

Definition

CREATE VIEW public.vwfrmovdetailstepubliceren (
   dnkeyovvergunning,
   dvpubltype,
   ddtriggerdatum,
   ddpublexport,
   dvpublfout,
   dvid,
   dvomsmediatype,
   dvhoofdid)
AS
WITH const AS (
SELECT COALESCE(tbinitialisatie.dfnumber1, 0::double precision)::integer AS val1,
           COALESCE(tbinitialisatie.dvstring, 'ddbesluitdatum'::character
               varying) AS val2
FROM tbinitialisatie
WHERE lower(tbinitialisatie.dvsectie::text) = 'droppublicaties'::text AND
   lower(tbinitialisatie.dvitem::text) = 'apvoverig'::text
       )
   SELECT b.dnkey AS dnkeyovvergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddontvangstdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-O-OL-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'OnLine'::character varying(30) AS dvomsmediatype,
   (('O'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbovvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::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.dnkeyovvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'OL'::text
   WHERE NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyovvergunningen = b.dnkey 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 dnkeyovvergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddontvangstdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-O-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   (('O'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbovvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::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.dnkeyovvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
   WHERE NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyovvergunningen = b.dnkey 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 dnkeyovvergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddontvangstdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-O-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   (('O'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbovvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::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.dnkeyovvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
   WHERE NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyovvergunningen = b.dnkey 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 dnkeyovvergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddontvangstdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-O-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   (('O'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbovvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::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.dnkeyovvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
   WHERE NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyovvergunningen = b.dnkey 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 dnkeyovvergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddontvangstdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-O-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   (('O'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbovvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::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.dnkeyovvergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
   WHERE NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyovvergunningen = b.dnkey 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 dnkeyovvergunning,
   'Besluit'::character varying(20) AS dvpubltype,
       CASE
           WHEN (((
       SELECT const.val2
       FROM const
       ))::text) = 'ddbesluitdatum'::text THEN b.ddbesluitdatum
           ELSE b.ddverzenddatum
       END AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-O-OL-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'OnLine'::character varying(30) AS dvomsmediatype,
   (('O'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbovvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf <
       CASE
           WHEN (((
       SELECT const.val2
       FROM const
       ))::text) = 'ddbesluitdatum'::text THEN b.ddbesluitdatum
           ELSE b.ddverzenddatum
       END AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) >
       CASE
           WHEN (((
       SELECT const.val2
       FROM const
       ))::text) = 'ddbesluitdatum'::text THEN b.ddbesluitdatum
           ELSE b.ddverzenddatum
       END 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.dnkeyovvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'OL'::text
   WHERE NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyovvergunningen = b.dnkey 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 dnkeyovvergunning,
   'Besluit'::character varying(20) AS dvpubltype,
       CASE
           WHEN (((
       SELECT const.val2
       FROM const
       ))::text) = 'ddbesluitdatum'::text THEN b.ddbesluitdatum
           ELSE b.ddverzenddatum
       END AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-O-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   (('O'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbovvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf <
       CASE
           WHEN (((
       SELECT const.val2
       FROM const
       ))::text) = 'ddbesluitdatum'::text THEN b.ddbesluitdatum
           ELSE b.ddverzenddatum
       END AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) >
       CASE
           WHEN (((
       SELECT const.val2
       FROM const
       ))::text) = 'ddbesluitdatum'::text THEN b.ddbesluitdatum
           ELSE b.ddverzenddatum
       END AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyovvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
   WHERE NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyovvergunningen = b.dnkey 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 dnkeyovvergunning,
   'Besluit'::character varying(20) AS dvpubltype,
       CASE
           WHEN (((
       SELECT const.val2
       FROM const
       ))::text) = 'ddbesluitdatum'::text THEN b.ddbesluitdatum
           ELSE b.ddverzenddatum
       END AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-O-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   (('O'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbovvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf <
       CASE
           WHEN (((
       SELECT const.val2
       FROM const
       ))::text) = 'ddbesluitdatum'::text THEN b.ddbesluitdatum
           ELSE b.ddverzenddatum
       END AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) >
       CASE
           WHEN (((
       SELECT const.val2
       FROM const
       ))::text) = 'ddbesluitdatum'::text THEN b.ddbesluitdatum
           ELSE b.ddverzenddatum
       END AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyovvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
   WHERE NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyovvergunningen = b.dnkey 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 b.dnkey AS dnkeyovvergunning,
   'Besluit'::character varying(20) AS dvpubltype,
       CASE
           WHEN (((
       SELECT const.val2
       FROM const
       ))::text) = 'ddbesluitdatum'::text THEN b.ddbesluitdatum
           ELSE b.ddverzenddatum
       END AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-O-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   (('O'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbovvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf <
       CASE
           WHEN (((
       SELECT const.val2
       FROM const
       ))::text) = 'ddbesluitdatum'::text THEN b.ddbesluitdatum
           ELSE b.ddverzenddatum
       END AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) >
       CASE
           WHEN (((
       SELECT const.val2
       FROM const
       ))::text) = 'ddbesluitdatum'::text THEN b.ddbesluitdatum
           ELSE b.ddverzenddatum
       END AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyovvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
   WHERE NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyovvergunningen = b.dnkey 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 dnkeyovvergunning,
   'Besluit'::character varying(20) AS dvpubltype,
       CASE
           WHEN (((
       SELECT const.val2
       FROM const
       ))::text) = 'ddbesluitdatum'::text THEN b.ddbesluitdatum
           ELSE b.ddverzenddatum
       END AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-O-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   (('O'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbovvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf <
       CASE
           WHEN (((
       SELECT const.val2
       FROM const
       ))::text) = 'ddbesluitdatum'::text THEN b.ddbesluitdatum
           ELSE b.ddverzenddatum
       END AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) >
       CASE
           WHEN (((
       SELECT const.val2
       FROM const
       ))::text) = 'ddbesluitdatum'::text THEN b.ddbesluitdatum
           ELSE b.ddverzenddatum
       END AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyovvergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
   WHERE NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyovvergunningen = b.dnkey 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 dnkeyovvergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-O-OL-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'OnLine'::character varying(30) AS dvomsmediatype,
   (('O'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbovvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::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.dnkeyovvergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'OL'::text
   WHERE NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyovvergunningen = b.dnkey 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 dnkeyovvergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-O-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   (('O'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbovvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::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.dnkeyovvergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
   WHERE NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyovvergunningen = b.dnkey 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 dnkeyovvergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-O-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   (('O'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbovvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::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.dnkeyovvergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
   WHERE NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyovvergunningen = b.dnkey 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 dnkeyovvergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-O-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   (('O'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbovvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::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.dnkeyovvergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
   WHERE NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyovvergunningen = b.dnkey 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 dnkeyovvergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-O-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   (('O'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbovvergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmovkoppublgemzaak a ON b.dvcodesoortovverg = a.dvcodesoortovverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::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.dnkeyovvergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
   WHERE NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyovvergunningen = b.dnkey 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.vwfrmovdetailstepubliceren
IS 'View van te publiceren momenten (DROP) bij APV/Overigzaken (per zaak per medium per type)';

COMMENT ON COLUMN public.vwfrmovdetailstepubliceren.dnkeyovvergunning
IS 'Primary key van de zaak in tbovvergunningen';

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

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

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

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

COMMENT ON COLUMN public.vwfrmovdetailstepubliceren.dvhoofdid
IS 'Moduleletter (C) + dnkeyovgvergunning';

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