Datadictionary OpenWave 3.1.32
 Previous topic Chapter index Next topic

View: vwfrmhordetailstepubliceren

 

 

Schema

public

 

Owner

waveuser005

 

Descriptions

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

 

Fields

Name

Data type

Description

dnkeyhorecavergunning

integer

Primary key van de zaak in tbhorecavergunningen

dvpubltype

varchar(20)

Aanvraag, Ingetrokken, Besluit afhankelijk waardoor het publiceren getriggerd wordt

ddtriggerdatum

timestamp

Aanvraagdatum, of Besluit/afgehandeld datum 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 (C) + dnkeyhorecagvergunning

 

Rules

Name

Event

Instead

Condition

Description

_RETURN

SELECT

 

 

 

Indices

There are no indices for table vwfrmhordetailstepubliceren

 

Definition

CREATE VIEW public.vwfrmhordetailstepubliceren (
   dnkeyhorecavergunning,
   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) = 'horeca'::text
       )
   SELECT b.dnkey AS dnkeyhorecavergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraagdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-C-OL-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'OnLine'::character varying(30) AS dvomsmediatype,
   (('C'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbhorecavergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraagdatum AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddaanvraagdatum 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.dnkeyhorecavergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'OL'::text
   WHERE b.dlzaakpubliceren = 'T'::bpchar AND
       instr(COALESCE(b.dvdroppublicatietypes, 'A'::character varying), 'A'::character varying) > 0 AND NOT (EXISTS (
       SELECT 1
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyhorecavergunningen = b.dnkey AND
           tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL
       ))
   UNION ALL
   SELECT b.dnkey AS dnkeyhorecavergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraagdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-C-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   (('C'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbhorecavergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraagdatum AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddaanvraagdatum AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyhorecavergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
   WHERE b.dlzaakpubliceren = 'T'::bpchar AND
       instr(COALESCE(b.dvdroppublicatietypes, 'A'::character varying), 'A'::character varying) > 0 AND NOT (EXISTS (
       SELECT 1
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyhorecavergunningen = b.dnkey AND
           tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GB'::text
       ))
   UNION ALL
   SELECT b.dnkey AS dnkeyhorecavergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraagdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-C-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   (('C'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbhorecavergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraagdatum AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddaanvraagdatum AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyhorecavergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
   WHERE b.dlzaakpubliceren = 'T'::bpchar AND
       instr(COALESCE(b.dvdroppublicatietypes, 'A'::character varying), 'A'::character varying) > 0 AND NOT (EXISTS (
       SELECT 1
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyhorecavergunningen = b.dnkey AND
           tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GS'::text
       ))
   UNION ALL
   SELECT b.dnkey AS dnkeyhorecavergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraagdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-C-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   (('C'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbhorecavergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraagdatum AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddaanvraagdatum AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyhorecavergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
   WHERE b.dlzaakpubliceren = 'T'::bpchar AND
       instr(COALESCE(b.dvdroppublicatietypes, 'A'::character varying), 'A'::character varying) > 0 AND NOT (EXISTS (
       SELECT 1
       FROM tbdroppublicaties
       WHERE (tbdroppublicaties.dnkeyhorecavergunningen = b.dnkey) IS NOT NULL
           AND tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'WB'::text
       ))
   UNION ALL
   SELECT b.dnkey AS dnkeyhorecavergunning,
   'Aanvraag'::character varying(20) AS dvpubltype,
   b.ddaanvraagdatum AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('A-C-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   (('C'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbhorecavergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::text AND a.dlaanvraagpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.ddaanvraagdatum AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.ddaanvraagdatum AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyhorecavergunningen AND
        d.dvpublicatietype = 'A'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
   WHERE b.dlzaakpubliceren = 'T'::bpchar AND
       instr(COALESCE(b.dvdroppublicatietypes, 'A'::character varying), 'A'::character varying) > 0 AND NOT (EXISTS (
       SELECT 1
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyhorecavergunningen = b.dnkey AND
           tbdroppublicaties.dvpublicatietype = 'A'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'PR'::text
       ))
   UNION ALL
   SELECT b.dnkey AS dnkeyhorecavergunning,
   'Besluit'::character varying(20) AS dvpubltype,
   b.dddatumbesluit AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-C-OL-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'OnLine'::character varying(30) AS dvomsmediatype,
   (('C'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbhorecavergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.dddatumbesluit AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.dddatumbesluit 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.dnkeyhorecavergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'OL'::text
   WHERE b.dlzaakpubliceren = 'T'::bpchar AND
       instr(COALESCE(b.dvdroppublicatietypes, 'B'::character varying), 'B'::character varying) > 0 AND NOT (EXISTS (
       SELECT 1
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyhorecavergunningen = b.dnkey AND
           tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL
       ))
   UNION ALL
   SELECT b.dnkey AS dnkeyhorecavergunning,
   'Besluit'::character varying(20) AS dvpubltype,
   b.dddatumbesluit AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-C-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   (('C'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbhorecavergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.dddatumbesluit AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.dddatumbesluit AND a.dlingemeenteblad = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyhorecavergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
   WHERE b.dlzaakpubliceren = 'T'::bpchar AND
       instr(COALESCE(b.dvdroppublicatietypes, 'B'::character varying), 'B'::character varying) > 0 AND NOT (EXISTS (
       SELECT 1
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyhorecavergunningen = b.dnkey AND
           tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GB'::text
       ))
   UNION ALL
   SELECT b.dnkey AS dnkeyhorecavergunning,
   'Besluit'::character varying(20) AS dvpubltype,
   b.dddatumbesluit AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-C-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   (('C'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbhorecavergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.dddatumbesluit AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.dddatumbesluit AND a.dlingemeenschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyhorecavergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
   WHERE b.dlzaakpubliceren = 'T'::bpchar AND
       instr(COALESCE(b.dvdroppublicatietypes, 'B'::character varying), 'B'::character varying) > 0 AND NOT (EXISTS (
       SELECT 1
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyhorecavergunningen = b.dnkey AND
           tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GS'::text
       ))
   UNION ALL
   SELECT b.dnkey AS dnkeyhorecavergunning,
   'Besluit'::character varying(20) AS dvpubltype,
   b.dddatumbesluit AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-C-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   (('C'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbhorecavergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.dddatumbesluit AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.dddatumbesluit AND a.dlinwaterschap = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyhorecavergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
   WHERE b.dlzaakpubliceren = 'T'::bpchar AND
       instr(COALESCE(b.dvdroppublicatietypes, 'B'::character varying), 'B'::character varying) > 0 AND NOT (EXISTS (
       SELECT 1
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyhorecavergunningen = b.dnkey AND
           tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'WB'::text
       ))
   UNION ALL
   SELECT b.dnkey AS dnkeyhorecavergunning,
   'Besluit'::character varying(20) AS dvpubltype,
   b.dddatumbesluit AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('B-C-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   (('C'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbhorecavergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        AND a.dvgemeenteid::text = COALESCE(oinbg.dvgemeenteid, e.dvgemeenteid)::text AND a.dlbesluitpubliceren = 'T'::bpchar AND a.ddpublicerenvanaf < b.dddatumbesluit AND fn_vandaag((
       SELECT const.val1
       FROM const
       )) > b.dddatumbesluit AND a.dlinprovincie = 'T'::bpchar
    LEFT JOIN tbdroppublicaties d ON b.dnkey = d.dnkeyhorecavergunningen AND
        d.dvpublicatietype = 'B'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
   WHERE b.dlzaakpubliceren = 'T'::bpchar AND
       instr(COALESCE(b.dvdroppublicatietypes, 'B'::character varying), 'B'::character varying) > 0 AND NOT (EXISTS (
       SELECT 1
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyhorecavergunningen = b.dnkey AND
           tbdroppublicaties.dvpublicatietype = 'B'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'PR'::text
       ))
   UNION ALL
   SELECT b.dnkey AS dnkeyhorecavergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-C-OL-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'OnLine'::character varying(30) AS dvomsmediatype,
   (('C'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbhorecavergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        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.dnkeyhorecavergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'OL'::text
   WHERE b.dlzaakpubliceren = 'T'::bpchar AND
       instr(COALESCE(b.dvdroppublicatietypes, 'I'::character varying), 'I'::character varying) > 0 AND NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyhorecavergunningen = b.dnkey AND
           tbdroppublicaties.dvpublicatietype = 'I'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL
       ))
   UNION ALL
   SELECT b.dnkey AS dnkeyhorecavergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-C-GB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Gemeenteblad'::character varying(30) AS dvomsmediatype,
   (('C'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbhorecavergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        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.dnkeyhorecavergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GB'::text
   WHERE b.dlzaakpubliceren = 'T'::bpchar AND
       instr(COALESCE(b.dvdroppublicatietypes, 'I'::character varying), 'I'::character varying) > 0 AND NOT (EXISTS (
       SELECT 1
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyhorecavergunningen = b.dnkey AND
           tbdroppublicaties.dvpublicatietype = 'I'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GB'::text
       ))
   UNION ALL
   SELECT b.dnkey AS dnkeyhorecavergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-C-GS-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'BladGemeenschappelijkeRegeling'::character varying(30) AS dvomsmediatype,
   (('C'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbhorecavergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        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.dnkeyhorecavergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'GS'::text
   WHERE b.dlzaakpubliceren = 'T'::bpchar AND
       instr(COALESCE(b.dvdroppublicatietypes, 'I'::character varying), 'I'::character varying) > 0 AND NOT (EXISTS (
       SELECT 1
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyhorecavergunningen = b.dnkey AND
           tbdroppublicaties.dvpublicatietype = 'I'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'GS'::text
       ))
   UNION ALL
   SELECT b.dnkey AS dnkeyhorecavergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-C-WB-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Waterschapsblad'::character varying(30) AS dvomsmediatype,
   (('C'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbhorecavergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        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.dnkeyhorecavergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'WB'::text
   WHERE b.dlzaakpubliceren = 'T'::bpchar AND
       instr(COALESCE(b.dvdroppublicatietypes, 'I'::character varying), 'I'::character varying) > 0 AND NOT (EXISTS (
       SELECT 1
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyhorecavergunningen = b.dnkey AND
           tbdroppublicaties.dvpublicatietype = 'I'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'WB'::text
       ))
   UNION ALL
   SELECT b.dnkey AS dnkeyhorecavergunning,
   'Ingetrokken'::character varying(20) AS dvpubltype,
   b.ddingetrokken AS ddtriggerdatum,
   d.ddexportdatum AS ddpublexport,
   d.dverror AS dvpublfout,
   (('I-C-PR-'::text || b.dnkey::character varying(20)::text))::character
       varying(27) AS dvid,
   'Provinciaalblad'::character varying(30) AS dvomsmediatype,
   (('C'::character(1)::text || b.dnkey))::character varying(11) AS dvhoofdid
   FROM tbhorecavergunningen b
    LEFT JOIN tboin oinbg ON b.dnkeyoinbevgez = oinbg.dnkey
    JOIN vwfrmlokaties e ON b.dnkeyperceeladressen = e.dnkeyperceeladressen
    JOIN vwfrmhorkoppublgemzaak a ON b.dnkeysoortverg = a.dnkeysoorthorverg
        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.dnkeyhorecavergunningen AND
        d.dvpublicatietype = 'I'::bpchar AND d.dvdossiernr IS NULL AND d.dvmediatype::text = 'PR'::text
   WHERE b.dlzaakpubliceren = 'T'::bpchar AND
       instr(COALESCE(b.dvdroppublicatietypes, 'I'::character varying), 'I'::character varying) > 0 AND NOT (EXISTS (
       SELECT tbdroppublicaties.dnkey
       FROM tbdroppublicaties
       WHERE tbdroppublicaties.dnkeyhorecavergunningen = b.dnkey AND
           tbdroppublicaties.dvpublicatietype = 'I'::bpchar AND tbdroppublicaties.dvdossiernr IS NOT NULL AND tbdroppublicaties.dvmediatype::text = 'PR'::text
       ));

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

COMMENT ON COLUMN public.vwfrmhordetailstepubliceren.dnkeyhorecavergunning
IS 'Primary key van de zaak in tbhorecavergunningen';

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

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

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

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

COMMENT ON COLUMN public.vwfrmhordetailstepubliceren.dvhoofdid
IS 'Moduleletter (C) + dnkeyhorecagvergunning'; 


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