admin/Customer
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
Customer/BRED/bred.sql

1478 lines
64 KiB
SQL
Raw Permalink Blame History

--
-- $Id$
--
-- Script containing customer specific sql statements for the FACILITOR database BRED
DEFINE thisfile = 'BRED.SQL'
DEFINE dbuser = 'BRED'
SET ECHO ON
SET DEFINE ON
COLUMN fcltlogfile NEW_VALUE fcltlogfile NOPRINT;
COLUMN fcltcusttxt NEW_VALUE fcltcusttxt NOPRINT;
WHENEVER SQLERROR CONTINUE;
SELECT adm.getscriptspoolfile('&thisfile') AS fcltlogfile FROM DUAL;
SPOOL &fcltlogfile
WHENEVER SQLERROR EXIT;
SELECT adm.checkscriptcust('&dbuser') AS fcltcusttxt FROM DUAL;
WHENEVER SQLERROR CONTINUE;
---------------------------------------
PROMPT &fcltcusttxt
---------------------------------------
SET DEFINE OFF
------ payload begin ------
-- PROCESSEMAIL IN OPNEMEN VOOR BRED
--- svrz.sql
----- mijn basis voor ubrh, blos, wend, sinn… , echter zeer uitgebreid geworden in 2022 / 2024 aanvullingen)
----- voor nu voor BRED dus maar uit sinn.sql overnemen (in package?!?)
--- kmmo.sql
------ geen notities…, meerdere mail_adressen
--- brcf.sql
----- meldingen notities + nieuw…
--- alkm.sql
----- voor alleen opdrachtnotities…
--- hsle.sql
---- heel specifiek, met verschillende melders en default_stdmeldingen…
-- VNOG-sql is ook voor opdrachtgever - heel charmant!!!!
CREATE OR REPLACE PACKAGE BRED AS
-- PACKAGES voor de BRED specifieke rapportages
TYPE t_cursor IS REF CURSOR;
PROCEDURE processemail (pfrom IN VARCHAR2,
pto IN VARCHAR2,
psubject IN VARCHAR2,
pbody IN VARCHAR2,
psessionid IN VARCHAR2,
pemailkey IN NUMBER);
PROCEDURE schonen_accounts;
END;
/
CREATE OR REPLACE PACKAGE BODY BRED
AS
-- PACKAGES voor de BRED specifieke rapportages
-- Verwijder via SCIM geinactiveerde personen!
PROCEDURE schonen_accounts
AS
c_prs_dummy_key NUMBER (10) := -1; -- DUMMY tbv overzetten verplichtingen verwijderde personen
v_errormsg VARCHAR2 (1000);
oracle_err_num NUMBER;
oracle_err_mes VARCHAR2 (200);
v_count_del NUMBER (10);
CURSOR c1
IS
SELECT '[' || TO_CHAR (p.prs_perslid_key) || '|' || p.prs_perslid_email || '|' || pf.prs_perslid_naam_full || '] '
aanduiding,
p.prs_perslid_key,
COUNT (vp.prs_perslid_key) aant
FROM prs_v_aanwezigperslid p, prs_v_verplichting_keys vp, prs_v_perslid_fullnames pf
WHERE p.prs_perslid_externid IS NOT NULL -- SCIM-populatie!
AND COAlESCE (p.prs_perslid_inactief, SYSDATE) < TRUNC (SYSDATE)
AND p.prs_perslid_key = vp.prs_perslid_key(+) -- Actuele verplichtingen?
AND p.prs_perslid_key = pf.prs_perslid_key
GROUP BY '[' || TO_CHAR (p.prs_perslid_key) || '|' || p.prs_perslid_email || '|' || pf.prs_perslid_naam_full || '] ',
p.prs_perslid_key
ORDER BY 2;
BEGIN
v_count_del := 0;
FOR rec IN c1
LOOP
BEGIN
IF rec.aant = 0
THEN
v_errormsg := 'Fout schonen collega';
DELETE FROM prs_collega
WHERE prs_perslid_key = rec.prs_perslid_key
OR prs_perslid_key_alt = rec.prs_perslid_key;
v_errormsg := 'Fout schonen perslid';
UPDATE prs_perslid
SET prs_perslid_verwijder = SYSDATE
WHERE prs_perslid_key = rec.prs_perslid_key;
v_count_del := v_count_del + 1;
IF MOD (v_count_del, 1000) = 0
THEN
COMMIT;
END IF;
ELSE
fac.writelog ('BRED_SCHONENACCOUNTS', 'I', rec.aanduiding || '/#Verplichtingen: ' || TO_CHAR (rec.aant), 'Persoon niet geschoond');
END IF;
EXCEPTION
WHEN OTHERS
THEN -- Kennelijk heeft persoon toch nog ernstige verplichtingen!
oracle_err_num := SQLCODE;
oracle_err_mes := SUBSTR (SQLERRM, 1, 200);
v_errormsg := v_errormsg || ' ORACLE (error ' || oracle_err_num || '/' || oracle_err_mes || ')';
fac.writelog ('BRED_SCHONENACCOUNTS', 'W', rec.aanduiding || v_errormsg, 'Fout schonen persoon');
END;
END LOOP;
fac.writelog ('BRED_SCHONENACCOUNTS', 'S', 'Personen/#geschoond: ' || TO_CHAR (v_count_del), '');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
oracle_err_num := SQLCODE;
oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
v_errormsg := v_errormsg || ' ORACLE (error ' || oracle_err_num || '/' || oracle_err_mes || ')';
fac.writelog ('BRED_SCHONENACCOUNTS', 'E', v_errormsg, '');
COMMIT;
END schonen_accounts;
PROCEDURE processemail (pfrom IN VARCHAR2,
pto IN VARCHAR2,
psubject IN VARCHAR2,
pbody IN VARCHAR2,
psessionid IN VARCHAR2,
pemailkey IN NUMBER)
AS
c_onbekend_key NUMBER (10) := 9; -- Melder onbekend
c_leverancier_M2M_key NUMBER (10) := 81; -- Leverancier_key M2M
defaultstdmelding fac_setting.fac_setting_default%TYPE;
c_locatie_key NUMBER (10) := 2 ; -- Apeldoorn
c_gebouw_key NUMBER (10) := 10 ; -- Woudhuis Kantoor
c_folder_kkey NUMBER (10) := 1 ; -- standaard folder_kenmerk_key
c_folder_opdr_kkey NUMBER (10) := 121 ; -- standaard folder_kenmerk_key tbv opdrachten
v_prs_werkplek_key NUMBER (10);
v_locatie_key NUMBER (10);
v_gebouw_key NUMBER (10);
v_stdmelding_key NUMBER (10);
v_errormsg VARCHAR2 (1000);
oracle_err_num NUMBER;
oracle_err_mes VARCHAR2 (200);
v_exist_ticket VARCHAR2 (255) := '';
v_perslid_key NUMBER (10);
v_kostenplaats_key NUMBER (10);
v_discipline_key NUMBER (10);
v_melding_key NUMBER (10);
v_behandelaar2_key NUMBER (10);
v_folder_kkey NUMBER (10);
v_count NUMBER (10);
v_count_wp NUMBER (10);
v_fac_result_from VARCHAR2 (250);
v_fac_result_fromaddr VARCHAR2 (250);
v_fw_from VARCHAR2 (1000) := '';
v_from_string VARCHAR2 (1000) := NULL;
v_voor_at VARCHAR2 (1000) := '';
v_na_at VARCHAR2 (1000) := '';
v_forward_soort NUMBER (1) := 1 ; -- 0 = De mail wordt direct van aanvrager doorgestuurd naar het facilitor-emailadres
-- 1 = De mail wordt via eigen centrale klant-postbus automatisch geforward naar het facilitor-emailadres
-- 2 = De mail wordt via eigen centrale klant-postbus handmatig aangevuld en daarna geforward naar het facilitor-emailadres
v_forward_0 NUMBER (1) := 0 ;
v_forward_1 NUMBER (1) := 0 ;
v_forward_2 NUMBER (1) := 0 ;
v_mailbody_notitie VARCHAR2 (4000) := '';
v_mailbody_notitie_1 VARCHAR2 (4000) := '';
v_mailbody_notitie_2 VARCHAR2 (4000) := '';
v_mailbody_notitie_3 VARCHAR2 (4000) := '';
v_opdracht_id VARCHAR2 (255);
v_opdracht_key NUMBER (10);
v_typeopdr_key NUMBER (10);
BEGIN
-- NOG GOED TESTEN EN ZAKEN OPSCHONEN EN FINTUNEN
-- ALGEMEEN
--- 1. NOTITIES SPLITSEN
-- 1e notitie -----
IF REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 1) = 0
THEN
v_forward_0 := 1 ;
v_mailbody_notitie_1 := pbody ;
END IF;
IF REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 1) > 0
THEN
-- dan hier de eerste notitie eruit halen (= alles voor de '1e Van:-regel')
v_mailbody_notitie_1 := LTRIM(SUBSTR (pbody, 1, REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 1)-1)) ;
END IF;
-- 2e notitie
IF REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 1) > 0 AND REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 2) = 0
THEN
v_forward_1 := 1 ;
-- dan hier de notitie eruit halen (= alles na de '1e Van:-regel' tot aan einde)
v_mailbody_notitie_2 := SUBSTR (pbody, REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 1)) ;
END IF;
IF REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 1) > 0 AND REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 2) > 0
THEN
-- dan hier de tweede notitie eruit halen (= alles vanaf de '1e Van:-regel' t/m de '2e Van:-regel)
v_mailbody_notitie_2 := SUBSTR (pbody, REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 1), REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 2) - REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 1)) ;
END IF;
-- 3e notitie
IF REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 2) > 0 AND REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 3) = 0
THEN
-- v_forward_1 := 1 ;
-- dan hier de notitie eruit halen (= alles na de '2e Van:-regel' tot aan einde)
v_mailbody_notitie_3 := SUBSTR (pbody, REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 2)) ;
END IF;
IF REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 2) > 0 AND REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 3) > 0
THEN
-- dan hier de tweede notitie eruit halen (= alles vanaf de '1e Van:-regel' t/m de '2e Van:-regel)
v_mailbody_notitie_3 := SUBSTR (pbody, REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 2), REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 3) - REGEXP_INSTR (pbody, 'Van:|From:|E-mail:', 1, 2)) ;
END IF;
IF v_forward_soort = 0
THEN
v_mailbody_notitie := v_mailbody_notitie_1 ;
END IF;
IF v_forward_soort = 1
THEN
v_mailbody_notitie := v_mailbody_notitie_1 ;
END IF;
IF v_forward_soort = 2
THEN
v_mailbody_notitie := v_mailbody_notitie_1 ;
END IF;
--- 2. HET FROM-ADRES VAN DE MAILER/NOTEUR BEPALEN UIT MAILBODY (V_FW_FROM)
-- Dus eerst op zoek naar eerste VAN-regel
v_from_string :=
SUBSTR (pbody,
REGEXP_INSTR (pbody,
'Van:|From:|E-mail:',
1,
1),
INSTR (pbody,
CHR (10),
REGEXP_INSTR (pbody,
'Van:|From:|E-mail:',
1,
1),
1)
- REGEXP_INSTR (pbody,
'Van:|From:|E-mail:',
1,
1));
-- Als deze VAN-regel bestaat en er zit een @-mailadres in dan gaan we via die kant het from-adres-bepalen
IF v_from_string IS NOT NULL
AND INSTR (v_from_string, '@') > 0
THEN
-- Als in die VAN-regel de structuur is 'alias_naam <mailadres>' dan moeten we tussen de haken het emailadres bepalen..
IF INSTR (v_from_string,
'<',
1,
1) > 0
THEN
v_na_at :=
SUBSTR (v_from_string,
INSTR (v_from_string,
'@',
1,
1)
+ 1,
INSTR (v_from_string, '.com')
+ 4
- INSTR (v_from_string,
'@',
1,
1)
- 1);
v_voor_at :=
SUBSTR (v_from_string,
INSTR (v_from_string,
'<',
1,
1)
+ 1,
INSTR (v_from_string,
'@',
1,
1)
- INSTR (v_from_string,
'<',
1,
1)
- 1);
v_fw_from := v_voor_at || '@' || v_na_at;
ELSE
-- Als in die VAN-regel de structuur is zonder het emailadres tussen <> dan als volgt emailadres bepalen:
v_na_at :=
SUBSTR (v_from_string,
INSTR (v_from_string,
'@',
1,
1)
+ 1,
REGEXP_INSTR (v_from_string, '$')
- INSTR (v_from_string,
'@',
1,
1)
- 1);
v_voor_at :=
SUBSTR (v_from_string,
INSTR (v_from_string,
':',
1,
1)
+ 2,
INSTR (v_from_string,
'@',
1,
1)
- INSTR (v_from_string,
':',
1,
1)
- 2);
v_fw_from := v_voor_at || '@' || v_na_at;
END IF;
ELSE -- Als er in mailboby geen "Van: met email-adres" dan maar terugvallen op from_adress uit fac_result
SELECT fac_result_waarde
INTO v_fac_result_fromaddr
FROM fac_result
WHERE fac_result_sessionid = psessionid
AND fac_result_naam = 'fromaddr';
v_fac_result_fromaddr :=
REPLACE (REPLACE (v_fac_result_fromaddr, '>', ''),
'<',
'');
v_fw_from := v_fac_result_fromaddr ;
END IF;
-- Tot slot FROM-email-adres heeft restrictie van 200
v_fw_from := SUBSTR (v_fw_from, 1, 200);
---- MAILINSTELLING - SERVICEDESK TBV MELDINGEN
CASE
WHEN UPPER (pto) LIKE 'SERVICEDESK@%'
THEN
defaultstdmelding := fac.getsetting ('defaultstdmelding');
-- Bepaal de melder/noteur op basis van e-mail adres zoals bepaald in v_fw_from
v_errormsg :=
'Fout bepalen melder ' || COALESCE (pfrom, '???');
SELECT COUNT (*), MAX (prs_perslid_key)
INTO v_count, v_perslid_key
FROM prs_v_aanwezigperslid
WHERE UPPER (prs_perslid_email) LIKE
UPPER (v_fw_from) || '%'
AND v_fw_from IS NOT NULL;
-- Als er persoon gevonden is, dan ff checken of er ook werkplekkoppelingen onder zijn aangemaakt
IF v_count >= 1
THEN
SELECT COUNT (*)
INTO v_count_wp
FROM prs_perslidwerkplek
WHERE prs_perslid_key = v_perslid_key;
END IF;
-- Als er persoon gevonden is, dan ff checken of er op gekoppelde Afdeling ook een kostenplaats is gekoppeld
IF v_count >= 1
THEN
SELECT MIN (a.prs_kostenplaats_key)
INTO v_kostenplaats_key
FROM prs_v_aanwezigperslid p, prs_afdeling a
WHERE prs_perslid_key = v_perslid_key
AND p.prs_afdeling_key = a.prs_afdeling_key;
END IF;
-- Locatie erbij halen
IF v_count >= 1 AND v_count_wp > 0
THEN
SELECT p.prs_perslid_key, MIN (wpg.prs_werkplek_key)
INTO v_perslid_key, v_prs_werkplek_key
FROM prs_v_aanwezigperslid p,
prs_v_werkplekperslid_gegevens wpg
WHERE p.prs_perslid_key = v_perslid_key
AND p.prs_perslid_key = wpg.prs_perslid_key
GROUP BY p.prs_perslid_key;
IF v_prs_werkplek_key IS NOT NULL
THEN
SELECT v.alg_locatie_key, v.alg_gebouw_key
INTO v_locatie_key, v_gebouw_key
FROM prs_werkplek wp,
alg_v_onroerendgoed_gegevens v
WHERE wp.prs_alg_ruimte_key =
v.alg_onroerendgoed_keys
AND prs_werkplek_key = v_prs_werkplek_key;
END IF;
END IF;
-- Geen werkplek of Melder onbekend - Dan melding op standaard locatie_key en gebouw_key
IF (v_count >= 1 AND v_count_wp = 0) OR v_count = 0
THEN
v_locatie_key := c_locatie_key;
v_gebouw_key := c_gebouw_key;
END IF;
-- Melder kan niet bepaald worden, dus inlezen onder 'Melder onbekend (M2M)'
-- Op deze 'Melder onbekend' is/kan aan voorkant wel afdeling en bijbehorende kostenplaats gekoppeld. Deze kp halen we er daarom nu ook bij
IF v_count = 0
THEN
SELECT p.prs_perslid_key, a.prs_kostenplaats_key
INTO v_perslid_key, v_kostenplaats_key
FROM prs_v_aanwezigperslid p, prs_afdeling a
WHERE p.prs_afdeling_key = a.prs_afdeling_key
AND p.prs_perslid_key = c_onbekend_key;
END IF;
v_errormsg := 'Fout bepalen bestaande melding';
v_exist_ticket := psubject;
IF REGEXP_INSTR (v_exist_ticket, '\d') > 0
THEN
v_exist_ticket :=
SUBSTR (v_exist_ticket,
REGEXP_INSTR (v_exist_ticket, '\d'))
|| 'x'; -- Gedeelte vanaf eerste cijfer in v_exist_ticket plus 'x'!
v_exist_ticket :=
SUBSTR (v_exist_ticket,
1,
REGEXP_INSTR (v_exist_ticket, '\D') - 1); -- Gedeelte tot eerste niet-cijfer in v_exist_ticket!
ELSE
v_exist_ticket := 'x';
END IF;
SELECT COUNT (*)
INTO v_count
FROM mld_melding
WHERE mld_melding_key = fac.safe_to_number (v_exist_ticket);
-- Om te voorkomen dat we andere nummers in onderwerpveld ten onrechte als Facilitor-meldingsnummer gaan zien, de meldings-sequence vanag 10000 gezet en daar dus op zoeken
IF v_count = 1 AND fac.safe_to_number (v_exist_ticket) > 10000
THEN -- Notitie bij bestaande melding!
-- bestaande melding ophalen inclusief aanvrager_key omdat we deze ivm forward op bred-emailadres niet goed doorkrijgen in FROM-adres..
SELECT mld_stdmelding_key, mld_melding_key, prs_perslid_key
INTO v_stdmelding_key, v_melding_key, v_perslid_key
FROM mld_melding
WHERE mld_melding_key =
fac.safe_to_number (v_exist_ticket);
v_errormsg := 'Fout toevoegen notitie';
INSERT INTO mld_melding_note (
mld_melding_key,
prs_perslid_key,
mld_melding_note_omschrijving,
mld_melding_note_flag)
VALUES (
v_melding_key,
v_perslid_key,
v_mailbody_notitie,
0);
-- Bepalen van de actieve behandelaar van deze melding
BEGIN
SELECT mld_melding_behandelaar2_key
INTO v_behandelaar2_key
FROM mld_melding m, prs_perslid p
WHERE mld_melding_key = v_melding_key
AND mld_melding_behandelaar2_key = p.prs_perslid_key
AND p.prs_perslid_email IS NOT NULL
AND p.prs_perslid_verwijder IS NULL;
EXCEPTION
WHEN OTHERS
THEN
v_behandelaar2_key := NULL;
END;
IF v_behandelaar2_key IS NOT NULL
THEN
-- Bestaande melding en behandelaar is bekend.
-- Notificatie naar behandelaar
fac.putnotificationsrtprio (
NULL,
v_behandelaar2_key,
'MLDNOB',
v_melding_key,
'Melding ' || v_melding_key || ' is door de aanvrager via M2M aangepast.',
2,
NULL,
NULL,
NULL,
2,
NULL);
END IF; -- Bestaande melding en behandelaar is bekend.
-- Vlaggetjes zetten zoals mld_edit_note.asp dat doet!
UPDATE mld_melding
SET mld_melding_actiecode = 1 + 128 -- BO + Attentie
WHERE mld_melding_key = v_melding_key;
-- VOOR DE TEST NOG EVEN DE GESPLITSTE NOTITIES EN MAIL_ADRES_AANVRAGER-BEPALING IN KENMERKEVELDEN ZETTEN
BEGIN mld.upsertmeldingkenmerk (102, v_melding_key, v_forward_soort); END;
BEGIN mld.upsertmeldingkenmerk (103, v_melding_key, v_voor_at || '@' || v_na_at); END;
BEGIN mld.upsertmeldingkenmerk (104, v_melding_key, v_voor_at); END;
BEGIN mld.upsertmeldingkenmerk (105, v_melding_key, v_na_at); END;
BEGIN mld.upsertmeldingkenmerk (106, v_melding_key, v_fac_result_fromaddr); END;
BEGIN mld.upsertmeldingkenmerk (107, v_melding_key, v_mailbody_notitie_1); END;
BEGIN mld.upsertmeldingkenmerk (108, v_melding_key, v_mailbody_notitie_2); END;
BEGIN mld.upsertmeldingkenmerk (109, v_melding_key, v_mailbody_notitie_3); END;
INSERT INTO fac_result (fac_result_sessionid,
fac_result_naam,
fac_result_waarde)
VALUES (
psessionid,
'maillog',
'Notitie onder exist-melding '
|| v_melding_key);
ELSE
-- Geen bestaande melding - Dan gaan we NIEUWE melding toevoegen
v_errormsg := 'Fout toevoegen melding';
INSERT INTO mld_melding (mld_melding_module,
mld_meldbron_key,
mld_alg_locatie_key,
mld_alg_onroerendgoed_keys,
mld_melding_datum,
mld_melding_omschrijving,
mld_stdmelding_key,
mld_melding_t_uitvoertijd,
prs_kostenplaats_key,
prs_perslid_key,
prs_perslid_key_voor,
mld_melding_status,
mld_melding_spoed,
mld_melding_onderwerp,
mld_melding_behandelaar2_key,
mld_ins_discipline_key)
VALUES (
'MLD',
4, -- email
v_locatie_key,
v_gebouw_key,
SYSDATE,
NULL,
defaultstdmelding,
NULL,
v_kostenplaats_key,
v_perslid_key,
v_perslid_key,
NULL,
3, -- prio normaal
SUBSTR (psubject, 1, 200),
NULL,
NULL)
RETURNING mld_melding_key
INTO v_melding_key;
-- mailbody in kenmerkveld 'mailbody', key 61 zetten
BEGIN mld.upsertmeldingkenmerk (61, v_melding_key, SUBSTR (pbody, 1, 4000)); END;
-- VOOR DE TEST DE GESPLITSTE NOTITIES EN MAIL_ADRES_AANVRAGER-BEPALING IN KENMERKEVELDEN ZETTEN
BEGIN mld.upsertmeldingkenmerk (102, v_melding_key, v_forward_soort); END;
BEGIN mld.upsertmeldingkenmerk (103, v_melding_key, v_voor_at || '@' || v_na_at); END;
BEGIN mld.upsertmeldingkenmerk (104, v_melding_key, v_voor_at); END;
BEGIN mld.upsertmeldingkenmerk (105, v_melding_key, v_na_at); END;
BEGIN mld.upsertmeldingkenmerk (106, v_melding_key, v_fac_result_fromaddr); END;
BEGIN mld.upsertmeldingkenmerk (107, v_melding_key, v_mailbody_notitie_1); END;
BEGIN mld.upsertmeldingkenmerk (108, v_melding_key, v_mailbody_notitie_2); END;
BEGIN mld.upsertmeldingkenmerk (109, v_melding_key, v_mailbody_notitie_3); END;
mld.setmeldingstatus (v_melding_key, 2, v_perslid_key);
INSERT INTO fac_result (fac_result_sessionid,
fac_result_naam,
fac_result_waarde)
VALUES (
psessionid,
'maillog',
'Geregistreerd onder melding '
|| v_melding_key);
END IF;
-- Find the lowest volgnummer of the Folder-flexfield.
--- niet nodig voor BRED want moet op altijd zelfde foldermap_key landen
v_folder_kkey := c_folder_kkey ;
IF v_folder_kkey IS NOT NULL
THEN
INSERT INTO fac_result (fac_result_sessionid,
fac_result_naam,
fac_result_waarde)
VALUES (
psessionid,
'kenmerkpath',
'MLD\M'
|| TO_CHAR (TRUNC (v_melding_key / 1000),
'FM0000')
|| '___\M'
|| v_melding_key
|| '\'
|| v_folder_kkey
|| '\');
-- originele email wordt opgeslagen bij de melding
INSERT INTO fac_result (fac_result_sessionid,
fac_result_naam,
fac_result_waarde)
VALUES (
psessionid,
'kenmerkorgmailpath',
'MLD\M'
|| TO_CHAR (TRUNC (v_melding_key / 1000),
'FM0000')
|| '___\M'
|| v_melding_key
|| '\'
|| v_folder_kkey
|| '\');
END IF;
---- MAILINSTELLING - SERVICEDESK _PLANNING TBV OPDRACHTEN
WHEN UPPER (pto) LIKE 'SERVICEDESK_PLANNING@%'
THEN
-- Probeer noteur te bepalen op basis van e-mail adres tussen '<>' na eerste voorkomen 'Van:'!
v_errormsg := 'Fout bepalen noteur';
v_perslid_key := c_leverancier_M2M_key;
v_errormsg := 'Fout bepalen opdracht';
v_opdracht_id := psubject;
IF REGEXP_INSTR (v_opdracht_id, '\d') > 0
THEN
v_opdracht_id := SUBSTR (v_opdracht_id, REGEXP_INSTR (v_opdracht_id, '\d')) || ' '; -- Gedeelte vanaf eerste cijfer in v_exist_ticket plus ' '!
v_opdracht_id := SUBSTR (v_opdracht_id, 1, REGEXP_INSTR (v_opdracht_id, '\s') - 1); -- Gedeelte tot eerste white space in v_exist_ticket!
END IF;
v_errormsg := 'Fout bepalen opdracht ' || v_opdracht_id;
SELECT mld_opdr_key, mld_typeopdr_key
INTO v_opdracht_key, v_typeopdr_key
FROM mld_opdr
WHERE TO_CHAR (mld_melding_key) || '/' || TO_CHAR (mld_opdr_bedrijfopdr_volgnr) = SUBSTR (v_opdracht_id, REGEXP_INSTR (v_opdracht_id, '\d'));
v_errormsg := 'Fout toevoegen opdracht-notitie';
INSERT INTO mld_opdr_note (mld_opdr_key, prs_perslid_key, mld_opdr_note_omschrijving)
VALUES (v_opdracht_key, v_perslid_key, pfrom || CHR (13) || CHR (10) || psubject || CHR (13) || CHR (10) || SUBSTR (v_mailbody_notitie, 1, 2000));
v_errormsg := 'Fout bijwerken opdracht-kleurbolletje';
UPDATE mld_opdr
SET mld_opdr_flag = 1 -- Zwart
WHERE mld_opdr_key = v_opdracht_key;
-- Vlaggetjes zetten zoals mld_edit_note.asp dat doet! - DEZE NOG NETJES INPASSEN + NOTIFICATIE MLDNOB NAAR BEHANDELAAR DIRECT DOEN!!
UPDATE mld_opdr
SET mld_opdr_actiecode = 132 -- BO + Attentie
WHERE mld_opdr_key = v_opdracht_key;
-- De gesplitste notities ook even wegschrijven
IF v_typeopdr_key = 5 -- werkopdracht
THEN
BEGIN mld.upsertopdrachtkenmerk (243, v_opdracht_key, v_mailbody_notitie_1); END;
BEGIN mld.upsertopdrachtkenmerk (244, v_opdracht_key, v_mailbody_notitie_2); END;
BEGIN mld.upsertopdrachtkenmerk (245, v_opdracht_key, v_mailbody_notitie_3); END;
END IF;
IF v_typeopdr_key = 202 -- offerte
THEN
BEGIN mld.upsertopdrachtkenmerk (249, v_opdracht_key, v_mailbody_notitie_1); END;
BEGIN mld.upsertopdrachtkenmerk (250, v_opdracht_key, v_mailbody_notitie_2); END;
BEGIN mld.upsertopdrachtkenmerk (251, v_opdracht_key, v_mailbody_notitie_3); END;
END IF;
-- Find the lowest volgnummer of the Folder-flexfield.
--- niet nodig voor BRED want moet op altijd zelfde foldermap_key landen
v_folder_kkey := c_folder_opdr_kkey ;
IF v_folder_kkey IS NOT NULL
THEN
INSERT INTO fac_result (fac_result_sessionid, fac_result_naam, fac_result_waarde)
VALUES (psessionid, 'kenmerkpath', 'MLD\O' || TO_CHAR (TRUNC (v_opdracht_key / 1000), 'FM0000') || '___\O' || v_opdracht_key || '\' || v_folder_kkey || '\');
-- originele email wordt opgeslagen bij de opdracht - NOG TESTEN IN ACCEPT!!
INSERT INTO fac_result (fac_result_sessionid,
fac_result_naam,
fac_result_waarde)
VALUES (
psessionid,
'kenmerkorgmailpath',
'MLD\O'
|| TO_CHAR (TRUNC (v_opdracht_key / 1000),
'FM0000')
|| '___\O'
|| v_opdracht_key
|| '\'
|| v_folder_kkey
|| '\');
END IF;
ELSE
INSERT INTO fac_result (fac_result_sessionid, fac_result_naam, fac_result_waarde)
VALUES (psessionid, 'errormsg', 'Ontvanger ongeldig - Neem contact op met uw systeembeheerder ');
END CASE;
EXCEPTION
WHEN OTHERS
THEN
oracle_err_num := SQLCODE;
oracle_err_mes := SUBSTR (SQLERRM, 1, 200);
v_errormsg :=
v_errormsg
|| ' ORACLE (error '
|| oracle_err_num
|| '/'
|| oracle_err_mes
|| ')';
fac.writelog ('PROCESSEMAIL',
'E',
'BRED_processemail afgebroken!',
'[' || pfrom || '] ' || v_errormsg);
INSERT INTO fac_result (fac_result_sessionid,
fac_result_naam,
fac_result_waarde)
VALUES (
psessionid,
'errormsg',
'Database fout - Neem contact op met uw systeembeheerder ');
COMMIT;
END;
END;
/
-------------------------------
--- DOMEINVIEWS ---------------
-------------------------------
CREATE OR REPLACE VIEW bred_v_personenlijst
(
PRS_PERSLID_KEY,
NAAM,
PERSONEELSNUMMER,
AFDELING,
EMAIL,
PRS_PERSLID_VERWIJDER
)
AS
SELECT
p.prs_perslid_key,
CASE WHEN p.prs_perslid_tussenvoegsel IS NULL
THEN p.prs_perslid_voornaam || ' ' || p.prs_perslid_naam || ' (' || sp.prs_srtperslid_omschrijving || ')'
ELSE p.prs_perslid_voornaam || ' ' || p.prs_perslid_tussenvoegsel || ' ' || p.prs_perslid_naam || ' (' || sp.prs_srtperslid_omschrijving || ')'
END
naam,
p.prs_perslid_nr,
a.prs_afdeling_naam3,
p.prs_perslid_email,
p.prs_perslid_verwijder
FROM
prs_perslid p,
prs_srtperslid sp,
prs_v_afdeling a,
prs_bedrijf b
WHERE
p.prs_afdeling_key = a.prs_afdeling_key
AND a.prs_bedrijf_key = b.prs_bedrijf_key
AND b.prs_bedrijf_key = 8 -- bedrijfs_key BRED
AND p.prs_srtperslid_key = sp.prs_srtperslid_key
ORDER BY sp.prs_srtperslid_omschrijving DESC, p.prs_perslid_voornaam, p.prs_perslid_naam
;
CREATE OR REPLACE VIEW bred_v_ruimtes
(
ALG_RUIMTE_KEY,
ALG_RUIMTE_OMSCHRIJVING,
ALG_SRTRUIMTE_OMSCHRIJVING,
ALG_RUIMTE_VERWIJDER
)
AS
SELECT alg_ruimte_key,
alg_gebouw_naam || ' - ' || alg_ruimte_nr || ' - ' || alg_ruimte_omschrijving
alg_ruimte_omschrijving,
alg_srtruimte_omschrijving,
alg_ruimte_verwijder
FROM alg_v_ruimte_gegevens_all
WHERE instr(alg_ruimte_upper_nr, 'FICTIEF') = 0
ORDER BY alg_gebouw_naam, alg_ruimte_nr
;
-------------------------------
--- NOTI-JOBS ---------------
-------------------------------
-- Contract-Rappel
--- Notificatie op contractbeheerder en/of verantwoordelijke
--- Onderstaand rapport is basisview en deze fungeert als bron voor de rappel-notificatie
CREATE OR REPLACE VIEW bred_v_cnt_rappel_basis
AS
SELECT d.ins_discipline_omschrijving,
c.cnt_contract_key,
c.cnt_contract_nummer_intern
|| DECODE (c.cnt_contract_versie,
NULL, '',
'.' || c.cnt_contract_versie)
cnt_contract_nr,
c.cnt_contract_looptijd_van begindatum,
c.cnt_contract_looptijd_tot einddatum,
cnt.cnt_getopzegdatum (c.cnt_contract_key) cnt_opzegdatum,
cnt.cnt_getrappeldatum (c.cnt_contract_key) cnt_rappeldatum,
DECODE (cnt.cnt_contract_status (c.cnt_contract_looptijd_van,
cnt.cnt_getrappeldatum (c.cnt_contract_key),
cnt.cnt_getopzegdatum (c.cnt_contract_key),
c.cnt_contract_looptijd_tot),
0, lcl.l ('lcl_cnt_future'),
1, lcl.l ('lcl_cnt_topical'),
2, lcl.l ('lcl_cnt_warn'),
3, lcl.l ('lcl_cnt_cancel'),
4, lcl.l ('lcl_cnt_past'))
fase_actief,
DECODE (
c.cnt_contract_status,
0,
DECODE (fac.getsetting ('cnt_contract_approval'),
1, lcl.l ('lcl_cnt_active_approval'),
lcl.l ('lcl_cnt_active')),
1,
lcl.l ('lcl_cnt_inactive'),
2,
lcl.l ('lcl_cnt_new'),
3,
lcl.l ('lcl_cnt_forapproval'))
cnt_contract_status,
(SELECT kc.cnt_kenmerkcontract_waarde
FROM cnt_kenmerkcontract kc, cnt_kenmerk k
WHERE k.cnt_kenmerk_key = kc.cnt_kenmerk_key
AND kc.cnt_contract_key = c.cnt_contract_key
AND k.cnt_kenmerk_key = 11) -- contractbeheerder
vink_beheerder,
(SELECT p.prs_perslid_email
FROM prs_perslid p
WHERE p.prs_perslid_key = c.prs_perslid_key_beh
AND p.prs_perslid_verwijder IS NULL)
beheerder_email,
(SELECT kc.cnt_kenmerkcontract_waarde
FROM cnt_kenmerkcontract kc, cnt_kenmerk k
WHERE k.cnt_kenmerk_key = kc.cnt_kenmerk_key
AND kc.cnt_contract_key = c.cnt_contract_key
AND k.cnt_kenmerk_key = 12) -- contractverantwoordelijke
vink_verantwoordelijke,
(SELECT p.prs_perslid_email
FROM prs_perslid p
WHERE p.prs_perslid_key = c.prs_perslid_key_eig
AND p.prs_perslid_verwijder IS NULL)
veantwoordelijke_email
FROM cnt_v_aanwezigcontract c, ins_tab_discipline d
WHERE c.ins_discipline_key = d.ins_discipline_key
AND d.ins_discipline_module = 'CNT';
-- SCHG: Contractbeheerder(_eig) is Beheerder/Administrateur en de Contractverantwoordelijke (_beh) is 1e aanspreekpunt die beslist over wel/niet verlengen.
CREATE OR REPLACE VIEW bred_v_noti_cntreminder
(
code,
sender,
receiver,
text,
key,
xkey,
xemail,
xmobile
)
AS -- Op rappel-datum TER INFO-mail naar de Beheerder/Administrateur sturen
SELECT 'CNTMAI',
NULL,
c.prs_perslid_key_beh,
'TER INFO: Rappel verstuurd voor contract ' || c.cnt_contract_nummer_intern
|| DECODE (c.cnt_contract_versie, NULL, '', '.' || c.cnt_contract_versie)
|| ' (' || c.cnt_contract_omschrijving || ' ' || b.prs_bedrijf_naam || ')'
|| ' - Moet uiterlijk per ' || TO_CHAR (cnt.cnt_getopzegdatum (c.cnt_contract_key), 'dd-mm-yyyy')
|| ' worden verlengd of opgezegd.',
c.cnt_contract_key,
NULL,
NULL,
NULL
FROM bred_v_cnt_rappel_basis v, cnt_v_aanwezigcontract c, prs_bedrijf b
WHERE c.cnt_prs_bedrijf_key = b.prs_bedrijf_key
AND v.cnt_contract_key = c.cnt_contract_key
AND v.vink_beheerder = 1
AND c.cnt_contract_status = 0
AND cnt.cnt_getrappeldatum (c.cnt_contract_key) = TRUNC (SYSDATE)
AND c.prs_perslid_key_beh <> COALESCE(c.prs_perslid_key_eig, -1) -- als administrateur gelijk aan 1e aanspreekpunt dan dan sturen we de info-mail niet uit
AND NOT EXISTS -- kenmerkveld 'Opgezegd' staat uit
(SELECT km.cnt_contract_key
FROM cnt_kenmerkcontract km, cnt_kenmerk k
WHERE km.cnt_contract_key = c.cnt_contract_key AND km.cnt_kenmerk_key = k.cnt_kenmerk_key AND k.cnt_kenmerk_key = 15)
UNION ALL -- Op rappel-datum ACTIE-mail naar het 1e aanspreekpunt sturen (contractverantwoordelijke)
SELECT 'CUST01',
NULL,
c.prs_perslid_key_eig,
'ACTIE: Rappel verstuurd voor contract ' || c.cnt_contract_nummer_intern
|| DECODE (c.cnt_contract_versie, NULL, '', '.' || c.cnt_contract_versie)
|| ' (' || c.cnt_contract_omschrijving || ' ' || b.prs_bedrijf_naam || ')'
|| ' - Moet uiterlijk per ' || TO_CHAR (cnt.cnt_getopzegdatum (c.cnt_contract_key), 'dd-mm-yyyy')
|| ' worden verlengd of opgezegd.',
c.cnt_contract_key,
NULL,
NULL,
NULL
FROM bred_v_cnt_rappel_basis v, cnt_v_aanwezigcontract c, prs_bedrijf b
WHERE c.cnt_prs_bedrijf_key = b.prs_bedrijf_key
AND v.cnt_contract_key = c.cnt_contract_key
AND v.vink_verantwoordelijke = 1
AND c.cnt_contract_status = 0
AND cnt.cnt_getrappeldatum (c.cnt_contract_key) = TRUNC (SYSDATE)
AND c.prs_perslid_key_eig IS NOT NULL
AND NOT EXISTS -- kenmerkveld 'Opgezegd' staat uit
(SELECT km.cnt_contract_key
FROM cnt_kenmerkcontract km, cnt_kenmerk k
WHERE km.cnt_contract_key = c.cnt_contract_key AND km.cnt_kenmerk_key = k.cnt_kenmerk_key AND k.cnt_kenmerk_key = 15)
;
-------------------------------
--- RAPPORTAGES ---------------
-------------------------------
CREATE OR REPLACE VIEW bred_v_rap_opdrachten
(
INTERN,
TYPE,
MLD_OPDR_KEY,
MLD_UITVOERENDE_KEY,
OPDRACHTNR,
STATUS,
MELDING,
BEHANDELAAR,
LOCATIE_PLAATS,
LOCATIE_OMSCHRIJVING,
OPDRACHT_AANMAAKDATUM,
GEPLANDE_AANVANG,
GEPLANDE_AANVANG_B, -- berekend voor kalenderweergave
GEPLANDE_EINDDATUM,
GEPLANDE_EINDDATUM_B, -- berekend voor kalenderweergave
OPDRACHT_EINDDATUM,
MELDING_EINDDATUM,
OMSCHRIJVING,
PRS_PERSLID_KEY,
NAAM_UITVOERDER,
AFMELD_DATUM,
OPDRACHT_TYPE,
TD_NAAM,
PRIORITEIT,
STATUS_KALENDER -- t.b.v. kalenderweergave
)
AS
SELECT mu.intern,
CASE
WHEN mu.TYPE = 'B' AND mu.intern = 1 THEN 'Poule-opdracht'
WHEN mu.TYPE = 'P' AND mu.intern = 1 THEN 'Klus-opdracht'
WHEN mu.intern = 0 THEN 'Externe opdracht'
ELSE '<?>'
END
TYPE,
o.mld_opdr_key,
CASE
WHEN mu.TYPE = 'B' AND mu.intern = 1 THEN mu.mld_uitvoerende_key
WHEN mu.TYPE = 'P' AND mu.intern = 1 THEN o.prs_perslid_key
WHEN mu.intern = 0 THEN mu.mld_uitvoerende_key
ELSE NULL
END
mld_uitvoerende_key,
TO_CHAR (o.mld_melding_key)
|| '/'
|| o.mld_opdr_bedrijfopdr_volgnr
opdrachtnummer,
DECODE (
os.mld_statusopdr_key,
8, DECODE (o.mld_opdr_halted,
1, 'Onderbroken',
os.mld_statusopdr_omschrijving),
os.mld_statusopdr_omschrijving)
status,
std.mld_stdmelding_omschrijving,
COALESCE (p.prs_perslid_naam_full, '<nog geen behandelaar>')
behandelaar,
l.alg_locatie_plaats,
l.alg_locatie_omschrijving,
o.mld_opdr_datumbegin,
o.mld_opdr_plandatum
geplande_aanvang,
CASE WHEN o.mld_opdr_plandatum IS NOT NULL THEN o.mld_opdr_plandatum ELSE o.mld_opdr_datumbegin END
geplande_aanvang_b,
o.mld_opdr_plandatum2
geplande_einddatum,
CASE WHEN o.mld_opdr_plandatum2 IS NOT NULL AND o.mld_opdr_plandatum IS NOT NULL THEN o.mld_opdr_plandatum2
WHEN o.mld_opdr_plandatum2 IS NULL AND o.mld_opdr_plandatum IS NOT NULL THEN o.mld_opdr_plandatum + 2/24
ELSE o.mld_opdr_datumbegin + 2/24
END
geplande_einddatum_b,
o.mld_opdr_einddatum,
m.mld_melding_einddatum,
TRIM (
REGEXP_REPLACE (
REGEXP_SUBSTR (o.mld_opdr_omschrijving,
'(([^ ]*)( |$)*){10}'),
'[[:space:]]',
' '))
|| '...'
omschrijving,
p.prs_perslid_key,
mu.naam
naam_uitvoerder,
(SELECT MAX (ft.fac_tracking_datum)
FROM fac_tracking ft
WHERE ft.fac_tracking_refkey = o.mld_opdr_key
AND ft.fac_srtnotificatie_key = 80) -- ORDAFM
afmeld_datum,
ot.mld_typeopdr_omschrijving,
CASE
WHEN mu.TYPE = 'B' AND mu.intern = 1 THEN COALESCE (p.prs_perslid_naam_full, '<nog geen behandelaar>')
WHEN mu.TYPE = 'P' AND mu.intern = 1 THEN mu.naam
ELSE ''
END td_naam,
COALESCE(v.prioriteit,'3 - NORMAAL')
prioriteit,
CASE
WHEN os.mld_statusopdr_omschrijving = 'Afgemeld' THEN 'Afgemeld'
WHEN os.mld_statusopdr_omschrijving = 'Afgewezen' THEN 'Afgewezen'
WHEN mu.intern = 1 AND (os.mld_statusopdr_omschrijving = 'Toegekend' OR os.mld_statusopdr_omschrijving = 'Geaccepteerd') AND o.mld_opdr_plandatum IS NULL THEN 'Nog inplannen'
WHEN mu.intern = 1 AND (os.mld_statusopdr_omschrijving = 'Toegekend' OR os.mld_statusopdr_omschrijving = 'Geaccepteerd') AND o.mld_opdr_plandatum IS NOT NULL THEN 'Ingepland'
WHEN mu.intern = 0 AND (os.mld_statusopdr_omschrijving = 'Toegekend' OR os.mld_statusopdr_omschrijving = 'Geaccepteerd') AND o.mld_opdr_plandatum IS NULL THEN 'Nog niet bevestigd'
WHEN mu.intern = 0 AND (os.mld_statusopdr_omschrijving = 'Toegekend' OR os.mld_statusopdr_omschrijving = 'Geaccepteerd') AND o.mld_opdr_plandatum IS NOT NULL THEN 'Bevestigd'
ELSE ''
END status_kalender
FROM mld_opdr o,
mld_melding m,
mld_typeopdr ot,
prs_v_perslid_fullnames p,
ins_tab_discipline d,
mld_stdmelding std,
alg_locatie l,
alg_district d,
mld_v_uitvoerende mu,
mld_statusopdr os,
(
SELECT ko.mld_opdr_key, ud.fac_usrdata_code || ' - ' || ud.fac_usrdata_omschr prioriteit
FROM mld_kenmerkopdr ko, fac_usrdata ud
WHERE fac.safe_to_number(ko.mld_kenmerkopdr_waarde) = ud.fac_usrdata_key
AND fac_usrtab_key = 41 -- keuzelijst eigen tabel Prioriteit
AND ko.mld_kenmerk_key IN (161, 162) -- kenmerk Prioriteit
) v
WHERE o.mld_melding_key = m.mld_melding_key(+)
AND o.prs_perslid_key = p.prs_perslid_key(+)
AND o.mld_typeopdr_key = ot.mld_typeopdr_key
AND ot.mld_typeopdr_isofferte = 0 -- Alleen werkopdrachten
AND m.mld_stdmelding_key = std.mld_stdmelding_key(+)
AND std.mld_ins_discipline_key = d.ins_discipline_key(+)
AND m.mld_alg_locatie_key = l.alg_locatie_key -- alle std-meldingen hebben verplicht-niveau locatie
AND l.alg_district_key = d.alg_district_key
AND o.mld_uitvoerende_keys = mu.mld_uitvoerende_key
AND os.mld_statusopdr_key = o.mld_statusopdr_key
AND o.mld_opdr_key = v.mld_opdr_key(+);
CREATE OR REPLACE VIEW bred_v_cal_opdrachten
(
USER_KEY,
TITLE,
VAN,
TOT,
ITEM_KEY,
COLOR,
TEXTCOLOR,
NAAM_UITVOERRDER,
LOCATIE_PLAATS,
STATUS_KALENDER,
INTERN,
OPDRACHT_TYPE
)
AS
SELECT mld_uitvoerende_key,
' - ' || opdrachtnr || CHR(10)
|| naam_uitvoerder || CHR(10)
|| locatie_omschrijving || CHR(10)
|| 'Status: ' || status_kalender || CHR(10)
|| CASE
WHEN geplande_aanvang IS NOT NULL AND geplande_einddatum IS NOT NULL THEN 'Bevestigd van ' || TO_CHAR(geplande_aanvang_b) || ' tot ' || TO_CHAR(geplande_einddatum_b)
WHEN geplande_aanvang IS NOT NULL AND geplande_einddatum IS NULL THEN 'Bevestigde aanvang: ' || TO_CHAR(geplande_aanvang_b)
WHEN geplande_aanvang IS NULL AND geplande_einddatum IS NOT NULL THEN 'Bevestigde einddatum: ' || TO_CHAR(geplande_einddatum_b)
ELSE '<nog te plannen>' END
|| CHR(10) || 'Prio: ' || prioriteit
title,
geplande_aanvang_b
van,
geplande_einddatum_b
tot,
mld_opdr_key,
DECODE (intern, 1,
DECODE (status_kalender,
'Afgewezen', '#C0C0C0', -- grijs
'Afgemeld', '#C0C0C0', -- grijs
'Nog niet bevestigd', '#F5ED5D', -- lichtgeel
'Bevestigd', '#DED304', -- donkergeel
'#0000FF'),
0,
DECODE (status_kalender,
'Afgewezen', '#C0C0C0', -- grijs
'Afgemeld', '#008000', -- groen
'Nog niet bevestigd', '#FF4611', -- rood/oranje
'Bevestigd', '#FFA500', -- oranje
'#0000FF'),
'#0000FF')
color,
DECODE (intern, 1,
DECODE (status_kalender,
'Afgewezen', '#bf0b3b', -- rood
'Afgemeld', '#000000', -- zwart
'Nog inplannen', '#FFFFFF', -- wit,
'Ingepland',
DECODE (LOWER(prioriteit),
'3 - normaal', '#FFFFFF', -- wit
'4 - laag', '#FFFFFF', -- wit
'2 - hoog', '#f58a20', -- oranje
'1 - kritiek', '#bf0b3b', -- rood
'#000000'),
'#000000'),
0,
DECODE (status_kalender,
'Afgewezen', '#bf0b3b', -- rood
'Afgemeld', '#000000', -- zwart
'Nog niet bevestigd', '#FFFFFF', -- wit
'Bevestigd', '#FFFFFF', -- wit
'#0000FF'),
'#000000'
) textcolor,
naam_uitvoerder,
locatie_plaats,
status_kalender,
intern,
opdracht_type
FROM bred_v_rap_opdrachten
ORDER BY geplande_aanvang_b ;
-- Catering: bestellen (receptie) en daarna brengen en halen/opruimen (facilitaire team)
---- via calenderview in beeld houden dagelijks
CREATE OR REPLACE VIEW bred_v_rap_meldingen_cat
(
VAKGROEP_TYPE,
VAKGROEP,
STD_MELDING,
MLD_MELDING_KEY,
MELDING_NUMMER,
MELDING_ONDERWERP,
MELDING_AANVRAGER,
MELDING_PLAATS,
MELDING_RUIMTE,
MELDING_STATUS,
MELDING_BEHANDELAAR,
MELDING_BEHANDELAAR_KEY,
MELDING_BEHANDELTEAM,
MELDING_DATUM,
MELDING_EINDATUM,
MELDING_ACTIEDATUM,
-- kenmerkvelden lunch halen/brengen
TIJDSTIP_VAN,
TIJDSTIP_TOT,
TIJDSTIP_BRENGEN,
GEPLANDE_AANVANG,
GEPLANDE_AANVANG_B,
GEPLANDE_EINDDATUM,
GEPLANDE_EINDDATUM_B,
-- kenmerkvelden status
STATUS_BESTELD,
STATUS_GEBRACHT,
STATUS_OPGEHAALD,
STATUS_KALENDER
)
AS
SELECT sd.ins_srtdiscipline_omschrijving,
d.ins_discipline_omschrijving,
std.mld_stdmelding_omschrijving,
m.mld_melding_key,
sd.ins_srtdiscipline_prefix || TO_CHAR (m.mld_melding_key)
meldingnummer,
m.mld_melding_onderwerp,
p.prs_perslid_naam_full,
l.alg_locatie_plaats,
r.alg_ruimte_omschrijving,
ms.mld_statuses_omschrijving,
COALESCE (ab.prs_perslid_naam_full, '<nog geen actieve behandelaar>')
behandelaar,
ab.prs_perslid_key
melding_behandelaar_key,
COALESCE (abt.ins_discipline_omschrijving, '<nog geen behandelteam>')
behandelteam,
m.mld_melding_datum,
m.mld_melding_einddatum,
m.mld_melding_actiedatum,
-- kenmerkvelden lunch - afspraak
v.tijdstip_van
tijdstip_van,
v.tijdstip_tot
tijdstip_tot,
v.tijdstip_brengen
tijdstip_brengen,
CASE WHEN v.tijdstip_brengen IS NOT NULL
THEN TO_CHAR(v.datum_afspraak,'dd-mm-yyyy') || ' ' || v.tijdstip_brengen || ':00'
ELSE TO_CHAR(v.datum_afspraak,'dd-mm-yyyy') || ' ' || '06:00' || ':00'
END
geplande_aanvang,
CASE WHEN v.tijdstip_brengen IS NOT NULL
THEN fac.safe_to_date(TO_CHAR(v.datum_afspraak,'dd-mm-yyyy') || ' ' || v.tijdstip_brengen || ':00', 'dd-mm-yyyy hh24:mi:ss')
ELSE fac.safe_to_date(TO_CHAR(v.datum_afspraak,'dd-mm-yyyy') || ' ' || '06:00' || ':00', 'dd-mm-yyyy hh24:mi:ss')
END
geplande_aanvang_b,
CASE WHEN v.tijdstip_tot IS NOT NULL
THEN TO_CHAR(v.datum_afspraak,'dd-mm-yyyy') || ' ' || v.tijdstip_tot || ':00'
ELSE TO_CHAR(v.datum_afspraak,'dd-mm-yyyy') || ' ' || '19:00' || ':00'
END
geplande_einddatum,
CASE WHEN v.tijdstip_tot IS NOT NULL
THEN fac.safe_to_date(TO_CHAR(v.datum_afspraak,'dd-mm-yyyy') || ' ' || v.tijdstip_tot || ':00', 'dd-mm-yyyy hh24:mi:ss')
ELSE fac.safe_to_date(TO_CHAR(v.datum_afspraak,'dd-mm-yyyy') || ' ' || '19:00' || ':00', 'dd-mm-yyyy hh24:mi:ss')
END
geplande_einddatum_b,
v.besteld
besteld,
v.gebracht
gebracht,
v.opgehaald
opgehaald,
-- statussen in kalender - rood - oranje - groen
CASE
WHEN v.gebracht IS NULL AND v.opgehaald IS NULL THEN 'Open'
WHEN v.gebracht IS NOT NULL AND v.opgehaald IS NULL THEN 'Gebracht'
WHEN v.gebracht IS NOT NULL AND v.opgehaald IS NOT NULL THEN 'Gereed'
ELSE ''
END status_kalender
FROM mld_melding m,
prs_v_perslid_fullnames p, -- aanvrager
prs_v_perslid_fullnames ab, -- actieve behandelaar
ins_tab_discipline abt, -- actieve behandelteam
ins_tab_discipline d,
ins_srtdiscipline sd,
mld_stdmelding std,
alg_locatie l,
alg_district d,
mld_statuses ms,
bred_v_ruimtes r,
(SELECT
mld_melding_key,
TRUNC(mld_melding_einddatum)
datum_afspraak,
flx.getflex('MLD',363, mld_melding_key)
tijdstip_van,
flx.getflex('MLD',364, mld_melding_key)
tijdstip_tot,
flx.getflex('MLD',404, mld_melding_key)
tijdstip_brengen,
flx.getflex('MLD',484, mld_melding_key)
besteld,
flx.getflex('MLD',461, mld_melding_key)
gebracht,
flx.getflex('MLD',462, mld_melding_key)
opgehaald
FROM mld_melding
WHERE mld_stdmelding_key = 119) v -- kenmerken bij catering
WHERE
m.mld_stdmelding_key = std.mld_stdmelding_key
AND std.mld_stdmelding_key = 119
AND m.mld_alg_onroerendgoed_keys = r.alg_ruimte_key
AND std.mld_ins_discipline_key = d.ins_discipline_key
AND d.ins_srtdiscipline_key = sd.ins_srtdiscipline_key
AND m.prs_perslid_key = p.prs_perslid_key -- aanvrager
AND m.mld_melding_behandelaar2_key = ab.prs_perslid_key(+)
AND m.mld_ins_discipline_key = abt.ins_discipline_key(+)
AND m.mld_alg_locatie_key = l.alg_locatie_key -- alle std-meldingen hebben verplicht-niveau locatie
AND l.alg_district_key = d.alg_district_key
AND m.mld_melding_status = ms.mld_statuses_key
AND m.mld_melding_key = v.mld_melding_key
;
CREATE OR REPLACE VIEW bred_v_cal_meldingen_cat
(
USER_KEY,
TITLE,
VAN,
TOT,
ITEM_KEY,
COLOR,
TEXTCOLOR,
MELDING_BEHANDELTEAM,
MELDING_BEHANDELAAR,
MELDING_RUIMTE,
MELDING_ONDERWERP,
STATUS_KALENDER
)
AS
SELECT melding_behandelaar_key,
' - ' || melding_nummer || ' (' || melding_status || ')' || CHR(10)
|| melding_ruimte || CHR(10)
|| melding_behandelteam || ' / ' || melding_behandelaar || CHR(10)
|| 'Status: ' || status_kalender || CHR(10)
|| '- ' || geplande_aanvang || CHR(10)
|| '- ' || geplande_einddatum
title,
geplande_aanvang_b
van,
geplande_einddatum_b
tot,
mld_melding_key,
DECODE (status_kalender,
'Gereed', '#008000', -- groen
'Open', '#FF4611', -- rood/oranje
'Gebracht', '#FFA500', -- oranje
'#0000FF')
color,
DECODE (status_kalender,
'Gereed', '#000000', -- zwart
'Open', '#FFFFFF', -- wit
'Gebracht', '#FFFFFF', -- wit
'#000000')
textcolor,
melding_behandelteam,
melding_behandelaar,
melding_ruimte,
melding_onderwerp,
status_kalender
FROM bred_v_rap_meldingen_cat
ORDER BY geplande_aanvang_b ;
-- O.a. voor beheer nieuwe medewerkers en inactieve mederwerkers en koppeling object voor registreren van de uitgegeven middelen
CREATE OR REPLACE VIEW bred_v_rap_personenbeheer
(
PRS_PERSLID_KEY,
PRS_PERSLID_NAAM,
PRS_PERLSID_EMAIL,
PRS_PERSLID_TELNR,
PRS_PERSLID_MOBIEL,
PRS_AFDELING_OMSCHRIJVING,
PRS_PERSLID_AANMAAK,
PRS_PERSLID_INDIENST,
PRS_PERSLID_UITDIENST,
PRS_PERSLID_INACTIEF,
PRS_PERSLID_INACTIEF_V,
PRS_PERSLID_VERWIJDER,
PRS_PERSLID_VERWIJDER_V,
OBJECT_MIDDEL_ACTIEF
)
AS
SELECT p.prs_perslid_key,
p.prs_perslid_voornaam || ' ' || p.prs_perslid_naam
prs_perslid_naam,
p.prs_perslid_email,
p.prs_perslid_telefoonnr,
p.prs_perslid_mobiel,
a.prs_afdeling_omschrijving || ' (' || a.prs_afdeling_upper || ')'
prs_afdeling_omschrijving,
p.prs_perslid_aanmaak,
p.prs_perslid_ingangsdatum
prs_perslid_indienst,
p.prs_perslid_einddatum
prs_perslid_uitdienst,
p.prs_perslid_inactief,
CASE WHEN p.prs_perslid_inactief IS NOT NULL THEN 'ja' ELSE 'nee' END
prs_perslid_inactief_v,
p.prs_perslid_verwijder,
CASE WHEN p.prs_perslid_verwijder IS NOT NULL THEN 'ja' ELSE 'nee' END
prs_perslid_verwijder_v,
CASE WHEN d.prs_perslid_key IS NOT NULL THEN 'ja' ELSE 'nee' END
object_middel_actief
FROM prs_perslid p, prs_v_afdeling a, prs_bedrijf b,
(SELECT ins_alg_ruimte_key prs_perslid_key
FROM ins_deel d
WHERE d.ins_Srtdeel_key = 41 AND ins_alg_ruimte_type = 'P'
AND ins_deel_verwijder IS NULL
AND ins_deel_actief = 1
AND (ins_deel_vervaldatum IS NULL OR TRUNC(ins_deel_vervaldatum) <= TRUNC(SYSDATE) )
) d
WHERE p.prs_afdeling_key = a.prs_afdeling_key
AND a.prs_bedrijf_key = b.prs_bedrijf_key
AND b.prs_bedrijf_key = 8
AND p.prs_perslid_key = d.prs_perslid_key (+)
;
-- Dagelijkse taak
CREATE OR REPLACE PROCEDURE bred_daily
AS
BEGIN
bred.schonen_accounts;
END;
/
------ payload end ------
SET DEFINE OFF
BEGIN adm.systrackscriptId ('$Id$', 0); END;
/
COMMIT;
SET ECHO OFF
SPOOL OFF
SET DEFINE ON
PROMPT Logfile of this upgrade is: &fcltlogfile
Reference in New Issue View Git Blame Copy Permalink