Datadictionary OpenWave 3.1.25

View: vwfrmdistinctprocchklistinf

Schema

public

Owner

waveuser005

Descriptions

View van distinct checklistnamen bij een infoaanraagzaak gekoppeld aan procedure

Fields

Rules

Indices

There are no indices for table vwfrmdistinctprocchklistinf

Definition

CREATE VIEW public.vwfrmdistinctprocchklistinf (
   dnkeychecklistnaam,
   dnkeyinfoaanvragen,
   dvomschrijving,
   dnkeyprocedure,
   dvprocnaam,
   dvchkonbofnietok,
   dvkeycheckproc)
AS
SELECT DISTINCT a.dnkeychecklistnaam,
   a.dnkeyinfoaanvragen,
   b.dvomschrijving,
   a.dnkeyprocedure,
   c.dvprocnaam,
       CASE
           WHEN COALESCE(checkfalse.dnchkaantfalse, 0::bigint) > 0 THEN 'rood'::text
           WHEN COALESCE(checkonb.dnchkaantonb, 0::bigint) > 0 THEN 'wit'::text
           ELSE 'groen'::text
       END::character varying(6) AS dvchkonbofnietok,
   (((a.dnkeychecklistnaam::character varying(10)::text || '-'::text) ||
       a.dnkeyprocedure::character varying(10)::text))::character varying(21) AS dvkeycheckproc
FROM tbchkitwerk a
    JOIN tbchecklistnaam b ON a.dnkeychecklistnaam = b.dnkey
    JOIN tbprocedures c ON a.dnkeyprocedure = c.dnkey
    LEFT JOIN (
   SELECT count(1) AS dnchkaantonb,
           tbchkitwerk.dnkeychecklistnaam,
           tbchkitwerk.dnkeyinfoaanvragen,
           tbchkitwerk.dnkeyprocedure
   FROM tbchkitwerk
   WHERE tbchkitwerk.dlstatus = 'N'::bpchar
   GROUP BY tbchkitwerk.dnkeychecklistnaam, tbchkitwerk.dnkeyprocedure,
       tbchkitwerk.dnkeyinfoaanvragen
   ) checkonb ON checkonb.dnkeychecklistnaam = a.dnkeychecklistnaam AND
       checkonb.dnkeyinfoaanvragen = a.dnkeyinfoaanvragen AND checkonb.dnkeyprocedure = a.dnkeyprocedure AND a.dnkeytermijnbewstappen IS NULL
    LEFT JOIN (
   SELECT count(1) AS dnchkaantfalse,
           tbchkitwerk.dnkeychecklistnaam,
           tbchkitwerk.dnkeyinfoaanvragen,
           tbchkitwerk.dnkeyprocedure
   FROM tbchkitwerk
   WHERE tbchkitwerk.dlstatus = 'F'::bpchar
   GROUP BY tbchkitwerk.dnkeychecklistnaam, tbchkitwerk.dnkeyprocedure,
       tbchkitwerk.dnkeyinfoaanvragen
   ) checkfalse ON checkfalse.dnkeychecklistnaam = a.dnkeychecklistnaam AND
       checkfalse.dnkeyinfoaanvragen = a.dnkeyinfoaanvragen AND checkfalse.dnkeyprocedure = a.dnkeyprocedure AND a.dnkeytermijnbewstappen IS NULL
WHERE a.dnkeyinfoaanvragen IS NOT NULL AND a.dnkeyprocedure IS NOT NULL AND
   a.dnkeytermijnbewstappen IS NULL;

COMMENT ON VIEW public.vwfrmdistinctprocchklistinf
IS 'View van distinct checklistnamen bij een infoaanraagzaak gekoppeld aan procedure';

COMMENT ON COLUMN public.vwfrmdistinctprocchklistinf.dnkeychecklistnaam
IS 'Foreign key naar tbchecklistnaam.dnkey';

COMMENT ON COLUMN public.vwfrmdistinctprocchklistinf.dnkeyinfoaanvragen
IS 'Foreign key naar tbinfoaanvragen.dnkey waar (procedure)-checlist aan verbonden is';

COMMENT ON COLUMN public.vwfrmdistinctprocchklistinf.dvomschrijving
IS 'Checklistnaam';

COMMENT ON COLUMN public.vwfrmdistinctprocchklistinf.dnkeyprocedure
IS 'Foreign key naar tbprocedures.dnkey';

COMMENT ON COLUMN public.vwfrmdistinctprocchklistinf.dvprocnaam
IS 'Naam van proces waar checklist aan verbonden is';

COMMENT ON COLUMN public.vwfrmdistinctprocchklistinf.dvchkonbofnietok
IS 'Rood indien ten minste eem van de bijbehreoden checlistitems is afgekeurd, wit indien temninste éém obekend,en groen indien alle items ok of nvt';

COMMENT ON COLUMN public.vwfrmdistinctprocchklistinf.dvkeycheckproc
IS 'dnkeyinfoaanvragen + een hyphen + dnkeyprocedure';

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