-- -- $Id$ -- -- Script containing customer specific configuration sql statements for Norah (NORA) DEFINE thisfile = 'NORA.SQL' DEFINE dbuser = '^NORA' 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 ------ CREATE OR REPLACE PROCEDURE nora_processemail (pfrom IN VARCHAR2, pto IN VARCHAR2, psubject IN VARCHAR2, pbody IN VARCHAR2, psessionid IN VARCHAR2, pemailkey IN NUMBER) AS v_sender prs_perslid.prs_perslid_key%TYPE; v_kostenplaats prs_afdeling.prs_kostenplaats_key%TYPE; v_mldstat mld_melding.mld_melding_status%TYPE; v_stdmelding mld_stdmelding.mld_stdmelding_key%TYPE; v_stduitvoer mld_stdmelding.mld_stdmelding_t_uitvoertijd%TYPE; v_errormsg fac_result.fac_result_waarde%TYPE; v_from VARCHAR2 (4000); v_fromaddr VARCHAR2 (4000); v_body VARCHAR2 (4000); m_body VARCHAR2 (4000); v_mldkey mld_melding.mld_melding_key%TYPE; v_mailorigin mld_kenmerk.mld_kenmerk_key%TYPE; v_mailattach mld_kenmerk.mld_kenmerk_key%TYPE; v_locatiekey alg_locatie.alg_locatie_key%TYPE; BEGIN v_sender := 3; --Standaard de persoon Facilitor v_stdmelding := fac.safe_to_number (fac.getsetting ('defaultstdmelding')); v_mailorigin := 1; --Kenmerk_key oorspronkelijke mail v_mailattach := 2; --Kenmerk_key bijlagen -- Verwijder de Bounce Address Tag Validation v_errormsg := '(0x101)'; IF pfrom LIKE 'prvs=%' THEN v_from := SUBSTR (pfrom, INSTR (pfrom, '=', -1) + 1); ELSE v_from := pfrom; END IF; v_errormsg := '(0x102)'; -- Indien een mail is doorgestuurd, dan moet er teruggevallen worden op de v_fromaddr. -- Maar niet altijd is v_fromaddr gevuld, vandaar de IF IF v_fromaddr IS NOT NULL THEN SELECT fac_result_waarde INTO v_fromaddr FROM fac_result WHERE fac_result_sessionid = psessionid AND fac_result_naam = 'fromaddr'; END IF; v_fromaddr := REPLACE (REPLACE (v_fromaddr, '>', ''), '<', ''); v_body := pbody; m_body := pbody; -- Valideer de sender in pfrom: kennen we deze? v_errormsg := '(0x111)'; SELECT MIN (p.prs_perslid_key), MIN (d.prs_kostenplaats_key) INTO v_sender, v_kostenplaats FROM prs_perslid p, prs_afdeling d WHERE p.prs_afdeling_key = d.prs_afdeling_key AND p.prs_perslid_verwijder IS NULL AND UPPER (prs_perslid_email) = UPPER (v_from); CASE WHEN UPPER (pto) LIKE 'STORINGEN@%' THEN BEGIN v_errormsg := SUBSTR (('(0x201)' || psessionid || pfrom || m_body), 0, 4000); -- Valideer de sender in pfromaddr: kennen we deze? -- Dan mag de melding aan die persoon gekoppeld worden SELECT MIN (p.prs_perslid_key), MIN (d.prs_kostenplaats_key) INTO v_sender, v_kostenplaats FROM prs_perslid p, prs_afdeling d WHERE p.prs_afdeling_key = d.prs_afdeling_key AND p.prs_perslid_verwijder IS NULL AND UPPER (prs_perslid_email) = UPPER (v_fromaddr); EXCEPTION WHEN OTHERS THEN fac.writelog ( 'PROCESSEMAIL', 'W', 'Mail kon niet geformateerd worden afzender:' || pfrom || '[' || v_errormsg || ']', 'OTHERS (error ' || SQLCODE || '/' || SUBSTR (SQLERRM, 1, 100) || ')'); END; -- Als v_sender gevuld is (winkel?), dan proberen we de locatie te bepalen. -- Die kunnen we dan ook vast vullen in de melding. Niet erg als dat niet lukt. IF v_sender IS NOT NULL THEN BEGIN SELECT l.alg_locatie_key INTO v_locatiekey FROM alg_v_allonroerendgoed l, prs_perslid p WHERE l.alg_type = p.alg_onroerendgoed_type AND l.alg_onroerendgoed_keys = p.alg_onroerendgoed_keys AND p.prs_perslid_key = v_sender; EXCEPTION WHEN OTHERS THEN v_locatiekey := NULL; END; END IF; --Hieronder de acties die gedaan moeten worden. BEGIN v_errormsg := '(0x211)' || pfrom || psubject; v_mldstat := 2; -- Standaard de status in Nieuw (2). v_stduitvoer := mld.getstduitvoer (v_stdmelding, NULL, NULL, NULL); v_errormsg := '(0x212)' || pfrom || v_mldkey || psubject; INSERT INTO mld_melding (mld_melding_module, mld_meldbron_key, mld_melding_datum, mld_melding_onderwerp, mld_melding_omschrijving, mld_melding_status, mld_melding_t_uitvoertijd, mld_stdmelding_key, mld_alg_locatie_key, mld_melding_externnr, prs_perslid_key, prs_perslid_key_voor, prs_kostenplaats_key, mld_melding_spoed) VALUES ( 'MLD', 4, -- email SYSDATE, SUBSTR (psubject, 1, 80), SUBSTR ( REPLACE ( SUBSTR (v_body, 1, 4000), CHR (13) || CHR (10) || CHR (13) || CHR (10), CHR (13) || CHR (10)), 1, 4000), -- verwijder onnodige witregels v_mldstat, NULL, -- v_stduitvoer v_stdmelding, v_locatiekey, -- Locatie bepaald via standaardlocatie v_sender (indien ingesteld) NULL, v_sender, v_sender, v_kostenplaats, 3) RETURNING mld_melding_key INTO v_mldkey; v_errormsg := '(0x221)'; mld.setmeldingstatus (v_mldkey, v_mldstat, v_sender); INSERT INTO fac_result (fac_result_sessionid, fac_result_naam, fac_result_waarde) VALUES (psessionid, 'maillog', 'Geregistreerd onder melding ' || v_mldkey); END; IF v_mldkey IS NOT NULL THEN BEGIN -- Originele email wordt opgeslagen v_errormsg := '(0x231)' || v_mldkey || psubject; INSERT INTO fac_result (fac_result_sessionid, fac_result_naam, fac_result_waarde) VALUES ( psessionid, 'kenmerkorgmailpath', 'MLD\M' || TO_CHAR (TRUNC (v_mldkey / 1000), 'FM0000') || '___\M' || v_mldkey || '\' || v_mailorigin || '\'); END; END IF; v_errormsg := 'Fout toevoegen bijlage(n)'; IF v_mailattach IS NOT NULL THEN BEGIN -- Toevoegen bijlage(n) v_errormsg := '(0x232)' || v_mldkey || psubject; INSERT INTO fac_result (fac_result_sessionid, fac_result_naam, fac_result_waarde) VALUES ( psessionid, 'kenmerkpath', 'MLD\M' || TO_CHAR (TRUNC (v_mldkey / 1000), 'FM0000') || '___\M' || v_mldkey || '\' || v_mailattach || '\'); END; END IF; ELSE v_errormsg := '(0x900)' || v_mldkey || psubject || ' - geen pto herkend'; INSERT INTO fac_result (fac_result_sessionid, fac_result_naam, fac_result_waarde) VALUES (psessionid, 'maillog', v_errormsg); END CASE; EXCEPTION WHEN OTHERS THEN INSERT INTO fac_result (fac_result_sessionid, fac_result_naam, fac_result_waarde) VALUES (psessionid, 'maillog', v_errormsg); fac.writelog ( 'PROCESSEMAIL', 'E', 'Mail kon niet ingelezen worden afzender:' || pfrom || '[' || v_errormsg || v_mldkey || psubject || ']', 'OTHERS (error ' || SQLCODE || '/' || SUBSTR (SQLERRM, 1, 100) || ')'); END; / CREATE OR REPLACE VIEW nora_rap_planning_td AS SELECT m.mld_melding_behandelaar_key xuser_key, pf.prs_perslid_naam_full behandelaar, m.mld_melding_key || ' ' || mld_melding_onderwerp title, mld_melding_actiedatum tot, mld_melding_actiedatum van, m.mld_melding_key item_key, '#FF0000' color, '#FFFFFF' textcolor, '?u=melding' || CHR (38) || 'k=' || m.mld_melding_key url FROM mld_melding m, prs_v_perslid_fullnames_all pf WHERE mld_melding_actiedatum IS NOT NULL AND m.mld_melding_status in (0,2,3,4,7) AND m.mld_melding_behandelaar_key = pf.prs_perslid_key UNION ALL SELECT NULL xuser_key, '' behandelaar, m.mld_melding_key || ' ' || mld_melding_onderwerp title, mld_melding_actiedatum tot, mld_melding_actiedatum van, m.mld_melding_key item_key, '#FF0000' color, '#FFFFFF' textcolor, '?u=melding' || CHR (38) || 'k=' || m.mld_melding_key url FROM mld_melding m WHERE mld_melding_actiedatum IS NOT NULL AND m.mld_melding_status in (0,2,3,4,7) AND m.mld_melding_behandelaar_key IS NULL; CREATE OR REPLACE VIEW NORA_V_LOCATIESTATUS AS SELECT x.datum, x.module, x.locatie, x.referentie, x.TYPE, x.discipline, x.categorie, x.onderwerp, x.omschrijving, x.datum_afhandeling, x.opmerking_afhandeling, x.informatie FROM (SELECT m.mld_melding_datum AS datum, 'melding' AS module, l.alg_locatie_omschrijving AS locatie, 'Melding ' || TO_CHAR (m.mld_melding_key) AS referentie, sd.ins_srtdiscipline_omschrijving AS TYPE, md.ins_discipline_omschrijving AS discipline, s.mld_stdmelding_omschrijving AS categorie, m.mld_melding_onderwerp AS onderwerp, TO_CHAR(SUBSTR(m.mld_melding_omschrijving,1,4000)) AS omschrijving, fac.gettrackingdate ('MLDAFM', m.mld_melding_key) AS datum_afhandeling, m.mld_melding_opmerking AS opmerking_afhandeling, m.mld_melding_omschrijving || CHR (10) || (CASE WHEN fac.gettrackingdate ('MLDAFM', m.mld_melding_key) IS NOT NULL THEN ( 'Afgemeld op ' || TO_CHAR ( TO_DATE ( fac.gettrackingdate ( 'MLDAFM', m.mld_melding_key)), 'dd-mm-yyyy') || ': ' || m.mld_melding_opmerking) ELSE NULL END) AS informatie FROM mld_melding m, mld_stdmelding s, alg_locatie l, -- alg_district d, -- alg_regio r, -- alg_v_allonrgoed_gegevens og, mld_discipline md, ins_srtdiscipline sd WHERE m.mld_alg_locatie_key = l.alg_locatie_key AND m.mld_stdmelding_key = s.mld_stdmelding_key AND s.mld_ins_discipline_key = md.ins_discipline_key AND md.ins_srtdiscipline_key = sd.ins_srtdiscipline_key UNION ALL SELECT COALESCE (t.inspectiedatum, t.volgendedatum) AS datum, 'periodieke taak' AS module, t.locatie AS locatie, t.object AS referentie, t.taaksoort AS TYPE, t.discipline || ' - ' || t.groep AS discipline, t.taak AS categorie, t.soort AS onderwerp, t.opmerking AS omschrijving, t.inspectiedatum AS datum_afhandeling, t.inspectie_opmerking AS opmerking_afhandeling, NULL AS informatie FROM CTR_V_MONTHLY_RECURRING_TASKS t) x WHERE TRUNC (x.datum) <= TRUNC (SYSDATE) ORDER BY x.datum DESC; ------ 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