-- -- $Id$ -- -- Script containing customer specific sql statements for the FACILITOR database DEFINE thisfile = 'CVGZ.SQL' DEFINE dbuser = '^CVGZ' 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 PACKAGE CVGZ AS FUNCTION change_delimitor (p_string VARCHAR2, p_delimitor VARCHAR2) RETURN VARCHAR2; PROCEDURE update_organisatie (p_import_key IN NUMBER); PROCEDURE anonimiseer_prs; PROCEDURE anonimiseer_bezoeker; PROCEDURE anonimiseer_mld; END; / CREATE OR REPLACE PACKAGE BODY CVGZ AS FUNCTION change_delimitor (p_string VARCHAR2, p_delimitor VARCHAR2) RETURN VARCHAR2 AS v_field VARCHAR2 (1000) := 'QueQuLeQue'; v_result VARCHAR2 (2000); v_line VARCHAR2 (2000); BEGIN v_line := p_string; WHILE v_line IS NOT NULL LOOP fac.imp_getfield (v_line, p_delimitor, v_field); v_result := v_result || '"' || v_field || '";'; END LOOP; RETURN v_result; EXCEPTION WHEN OTHERS THEN RETURN NULL; END; PROCEDURE update_organisatie ( p_import_key IN NUMBER ) IS c_fielddelimitor VARCHAR2 (1) := ';'; v_newline fac_imp_file.fac_imp_file_line%TYPE; -- Input line v_errormsg VARCHAR2 (1000); v_errorhint VARCHAR2 (1000); oracle_err_num NUMBER; oracle_err_mes VARCHAR2 (200); v_aanduiding VARCHAR2 (200); v_count NUMBER(10); v_ongeldig NUMBER(1); v_count_update NUMBER(10); v_count_error NUMBER(10); v_count_tot NUMBER(10); -- v_bedrijf_key NUMBER(10); v_kostenplaats_key NUMBER(10); v_prs_afdeling_parentkey NUMBER(10); v_afdeling_key NUMBER(10); CURSOR cBedrijf IS SELECT min(prs_bedrijf_naam) prs_bedrijf_naam FROM fac_imp_organisatie WHERE prs_bedrijf_naam is not null GROUP BY upper(prs_bedrijf_naam); -- Bij 1-op-1 relatie tussen kostenplaats en afdeling de kostenplaatsomschrijving zetten op -- de afdelingsomschrijving, ander de kostenplaatsomschrijving leeg laten. CURSOR cKPN IS SELECT MIN(prs_kostenplaats_nr) prs_kostenplaats_nr, DECODE(COUNT(*),1,MAX(prs_afdeling_omschrijving),'') prs_kostenplaats_omschrijving, 'PRS' prs_kostenplaats_module FROM FAC_IMP_ORGANISATIE WHERE prs_kostenplaats_nr IS NOT NULL GROUP BY UPPER(prs_kostenplaats_nr); CURSOR cAfdeling1 IS SELECT MIN(prs_bedrijf_naam) prs_bedrijf_naam ,UPPER(prs_bedrijf_naam) prs_bedrijf_naam_upper ,MIN(prs_afdeling_naam) prs_afdeling_naam ,UPPER(prs_afdeling_naam) prs_afdeling_upper ,MAX(prs_afdeling_omschrijving) prs_afdeling_omschrijving ,MIN(prs_afdeling_naam_parent) prs_afdeling_naam_parent ,MIN(UPPER(prs_afdeling_naam_parent)) prs_afdeling_naam_parent_upper ,MAX(prs_kostenplaats_nr) prs_kostenplaats_nr ,MAX(UPPER(prs_kostenplaats_nr)) prs_kostenplaats_upper ,NIVEAU FROM (SELECT 1 NIVEAU, a1.prs_bedrijf_naam, a1.prs_afdeling_naam, a1.prs_afdeling_omschrijving, a1.prs_kostenplaats_nr, '' PRS_AFDELING_NAAM_PARENT FROM FAC_IMP_ORGANISATIE a1 WHERE a1.prs_afdeling_naam_parent IS NULL UNION ALL SELECT 2, a2.prs_bedrijf_naam, a2.prs_afdeling_naam, a2.prs_afdeling_omschrijving, a2.prs_kostenplaats_nr, a1.prs_afdeling_naam FROM FAC_IMP_ORGANISATIE a1, FAC_IMP_ORGANISATIE a2 WHERE a2.prs_afdeling_naam_parent = a1.prs_afdeling_naam AND a1.prs_afdeling_naam_parent IS NULL UNION ALL SELECT 3, a3.prs_bedrijf_naam, a3.prs_afdeling_naam, a3.prs_afdeling_omschrijving, a3.prs_kostenplaats_nr, a2.prs_afdeling_naam FROM FAC_IMP_ORGANISATIE a1, FAC_IMP_ORGANISATIE a2, FAC_IMP_ORGANISATIE a3 WHERE a3.prs_afdeling_naam_parent = a2.prs_afdeling_naam AND a2.prs_afdeling_naam_parent = a1.prs_afdeling_naam AND a1.prs_afdeling_naam_parent IS NULL UNION ALL SELECT 4, a4.prs_bedrijf_naam, a4.prs_afdeling_naam, a4.prs_afdeling_omschrijving, a4.prs_kostenplaats_nr, a3.prs_afdeling_naam FROM FAC_IMP_ORGANISATIE a1, FAC_IMP_ORGANISATIE a2, FAC_IMP_ORGANISATIE a3, FAC_IMP_ORGANISATIE a4 WHERE a4.prs_afdeling_naam_parent = a3.prs_afdeling_naam AND a3.prs_afdeling_naam_parent = a2.prs_afdeling_naam AND a2.prs_afdeling_naam_parent = a1.prs_afdeling_naam AND a1.prs_afdeling_naam_parent IS NULL UNION ALL SELECT 5, a5.prs_bedrijf_naam, a5.prs_afdeling_naam, a5.prs_afdeling_omschrijving, a5.prs_kostenplaats_nr, a4.prs_afdeling_naam FROM FAC_IMP_ORGANISATIE a1, FAC_IMP_ORGANISATIE a2, FAC_IMP_ORGANISATIE a3, FAC_IMP_ORGANISATIE a4, FAC_IMP_ORGANISATIE a5 WHERE a5.prs_afdeling_naam_parent = a4.prs_afdeling_naam AND a4.prs_afdeling_naam_parent = a3.prs_afdeling_naam AND a3.prs_afdeling_naam_parent = a2.prs_afdeling_naam AND a2.prs_afdeling_naam_parent = a1.prs_afdeling_naam AND a1.prs_afdeling_naam_parent IS NULL) WHERE ((prs_bedrijf_naam IS NOT NULL) AND (prs_afdeling_naam IS NOT NULL)) -- geen lege regels GROUP BY UPPER(prs_bedrijf_naam), UPPER(prs_afdeling_naam), NIVEAU ORDER BY NIVEAU; BEGIN v_count_update := 0; v_count_tot := 0; v_count_error := 0; FOR recBedrijf IN cBedrijf LOOP BEGIN v_count_tot := v_count_tot + 1; v_aanduiding := recBedrijf.PRS_BEDRIJF_NAAM||'| '; v_errormsg := ''; v_errorhint := ''; v_errorhint := 'Kijken of bedrijf al bestaat'; SELECT count(*) INTO v_count FROM prs_bedrijf WHERE upper(prs_bedrijf_naam_upper) = upper(recBedrijf.prs_bedrijf_naam) AND prs_bedrijf_verwijder is null; IF (v_count = 0) THEN v_errorhint := 'Fout bij toevoegen bedrijf'; INSERT INTO PRS_BEDRIJF ( prs_bedrijf_naam ,prs_bedrijf_intern ) VALUES ( recBedrijf.prs_bedrijf_naam ,1 ); commit; v_count_update := v_count_update + 1; END IF; EXCEPTION WHEN OTHERS THEN v_count_error := v_count_error + 1; oracle_err_num := SQLCODE; oracle_err_mes := SUBSTR(SQLERRM, 1, 200); IF (v_errormsg IS NULL) THEN v_errormsg := 'Fout tijdens verwerken van bedrijf ['||recBedrijf.PRS_BEDRIJF_NAAM||']'; END IF; v_errorhint := v_errorhint||': ' || oracle_err_mes; fac.imp_writelog (p_import_key, 'E', v_aanduiding||v_errormsg, v_errorhint ); commit; -- tbv logging END; END LOOP; fac.imp_writelog (p_import_key, 'S', 'Bedrijf: verwerkte regels zonder foutmelding: '||to_char(v_count_tot-v_count_error), '' ); fac.imp_writelog (p_import_key, 'S', 'Bedrijf: verwerkte regels met foutmelding: '||to_char(v_count_error), '' ); v_count_update := 0; v_count_tot := 0; v_count_error := 0; FOR recKPN IN cKPN LOOP BEGIN v_aanduiding := recKPN.PRS_KOSTENPLAATS_NR||'| '; v_errormsg := ''; v_errorhint := ''; v_count_tot := v_count_tot + 1; v_errorhint := 'Kijken of de kostenplaats al bestaat'; SELECT count(*) INTO v_count FROM prs_kostenplaats WHERE prs_kostenplaats_verwijder is null AND upper(prs_kostenplaats_upper) = upper(recKPN.prs_kostenplaats_nr); IF (v_count = 0) THEN v_errorhint := 'Fout bij toevoegen kostenplaats'; INSERT INTO PRS_KOSTENPLAATS (PRS_KOSTENPLAATS_NR ,PRS_KOSTENPLAATS_OMSCHRIJVING ,PRS_KOSTENPLAATS_MODULE) VALUES (recKPN.prs_kostenplaats_nr ,recKPN.prs_kostenplaats_omschrijving ,recKPN.prs_kostenplaats_module); commit; v_count_update := v_count_update + 1; END IF; EXCEPTION WHEN OTHERS THEN v_count_error := v_count_error + 1; oracle_err_num := SQLCODE; oracle_err_mes := SUBSTR(SQLERRM, 1, 200); IF (v_errormsg IS NULL) THEN v_errormsg := 'Fout tijdens verwerken van kostenplaats ['||recKPN.PRS_KOSTENPLAATS_NR||']'; END IF; v_errorhint := v_errorhint||': ' || oracle_err_mes; fac.imp_writelog (p_import_key, 'E', v_aanduiding||v_errormsg, v_errorhint ); commit; -- tbv logging END; END LOOP; fac.imp_writelog (p_import_key, 'S', 'Kostenplaats: verwerkte regels zonder foutmelding: '||to_char(v_count_tot-v_count_error), '' ); fac.imp_writelog (p_import_key, 'S', 'Kostenplaats: verwerkte regels met foutmelding: '||to_char(v_count_error), '' ); v_count_update := 0; v_count_tot := 0; v_count_error := 0; FOR recAfdeling1 IN cAfdeling1 LOOP BEGIN v_count_tot := v_count_tot + 1; v_aanduiding := recAfdeling1.prs_bedrijf_naam||'|'||recAfdeling1.prs_afdeling_naam||'|'||recAfdeling1.prs_afdeling_naam_parent ||'|'||recAfdeling1.prs_kostenplaats_upper ||'| '; v_errormsg := ''; v_errorhint := ''; IF (recAfdeling1.niveau = 1) THEN v_errorhint := 'Bedrijf niet gevonden bij afdeling'; v_prs_afdeling_parentkey := to_number(NULL); SELECT prs_bedrijf_key INTO v_bedrijf_key FROM prs_bedrijf WHERE prs_bedrijf_verwijder IS NULL AND prs_bedrijf_naam_upper = recAfdeling1.prs_bedrijf_naam_upper; ELSE v_errorhint := 'Parent van de afdeling niet gevonden'; v_bedrijf_key := to_number(NULL); SELECT prs_afdeling_key INTO v_prs_afdeling_parentkey FROM prs_afdeling WHERE prs_afdeling_verwijder IS NULL AND prs_afdeling_upper = recAfdeling1.prs_afdeling_naam_parent_upper; END IF; v_errorhint := 'Kostenplaats bij afdeling bepalen'; SELECT COUNT(*), NVL(MAX(prs_kostenplaats_key),to_number(NULL)) INTO v_count, v_kostenplaats_key FROM PRS_KOSTENPLAATS WHERE prs_kostenplaats_verwijder IS NULL AND prs_kostenplaats_upper = UPPER(trim(recAfdeling1.prs_kostenplaats_upper)) AND UPPER(prs_kostenplaats_module) = 'PRS'; v_errorhint := 'Bepalen of afdeling bestaat'; SELECT count(*), MAX(prs_afdeling_key) INTO v_count, v_afdeling_key FROM prs_afdeling WHERE upper(rtrim(prs_afdeling_naam)) = upper(rtrim(recAfdeling1.prs_afdeling_naam)) AND prs_afdeling_verwijder is null; IF (v_count = 0) THEN v_errorhint := 'Fout bij toevoegen afdeling'; insert into PRS_AFDELING (prs_afdeling_naam ,prs_afdeling_omschrijving ,prs_bedrijf_key ,prs_afdeling_parentkey ,prs_kostenplaats_key ) values (recAfdeling1.prs_afdeling_naam ,recAfdeling1.prs_afdeling_omschrijving ,v_bedrijf_key ,v_prs_afdeling_parentkey ,v_kostenplaats_key ); v_count_update := v_count_update + 1; ELSIF v_count = 1 THEN v_errorhint := 'Fout bij aanpassen afdeling'; UPDATE prs_afdeling SET prs_afdeling_omschrijving = recAfdeling1.prs_afdeling_omschrijving, prs_afdeling_parentkey = v_prs_afdeling_parentkey, prs_kostenplaats_key = v_kostenplaats_key, prs_bedrijf_key = v_bedrijf_key WHERE prs_afdeling_key = v_afdeling_key; v_count_update := v_count_update + 1; ELSE fac.imp_writelog (p_import_key, 'E', v_aanduiding|| 'Fout bij het aanmaken / bijwerken van afdeling', 'Meerdere afdelingen gevonden.' ); END IF; EXCEPTION WHEN OTHERS THEN v_count_error := v_count_error + 1; oracle_err_num := SQLCODE; oracle_err_mes := SUBSTR(SQLERRM, 1, 200); IF (v_errormsg IS NULL) THEN v_errormsg := 'Fout tijdens verwerken van afdeling ['||recAfdeling1.PRS_AFDELING_NAAM||']'; END IF; v_errorhint := v_errorhint||': ' || oracle_err_mes; fac.imp_writelog (p_import_key, 'E', v_aanduiding||v_errormsg, v_errorhint ); commit; -- tbv logging END; END LOOP; fac.imp_writelog (p_import_key, 'S', 'Afdeling: verwerkte regels zonder foutmelding: '||to_char(v_count_tot-v_count_error), '' ); fac.imp_writelog (p_import_key, 'S', 'Afdeling: verwerkte regels met foutmelding: '||to_char(v_count_error), '' ); END; PROCEDURE anonimiseer_prs AS v_van DATE; v_tot DATE; anonymize_period NUMBER := fac.safe_to_number (fac.getsetting ('anonymize_period')); BEGIN -- Als een persoon minimaal een jaar geleden verwijderd is anonimiseren we de gegevens SELECT datum_van INTO v_van FROM (SELECT MIN (p.prs_perslid_verwijder) datum_van FROM prs_perslid p WHERE p.prs_perslid_verwijder < (TRUNC (SYSDATE) - anonymize_period) ); SELECT TRUNC (SYSDATE - anonymize_period) INTO v_tot FROM DUAL; IF v_van IS NOT NULL THEN ano.anonymizeprs (v_van, v_tot); END IF; END; PROCEDURE anonimiseer_mld AS v_van DATE; v_tot DATE; anonymize_period NUMBER := fac.safe_to_number (fac.getsetting ('anonymize_period')); BEGIN SELECT datum_van INTO v_van FROM (SELECT MIN (TRUNC (m.mld_melding_einddatum)) datum_van FROM mld_melding m WHERE m.mld_melding_status IN (1, 5, 6) -- melding is afgewezen, afgemeld of historisch AND m.mld_melding_einddatum < (TRUNC (SYSDATE) - anonymize_period) -- de melding was gereed voor de anonimiseer periode AND NOT EXISTS --- nog niet geanonimiseerd (SELECT ft.fac_tracking_key FROM fac_tracking ft WHERE ft.fac_tracking_refkey = m.mld_melding_key AND ft.fac_srtnotificatie_key = (SELECT fac_srtnotificatie_key FROM fac_srtnotificatie WHERE fac_srtnotificatie_code = 'MLDANO'))); SELECT TRUNC (SYSDATE - anonymize_period) INTO v_tot FROM DUAL; IF v_van IS NOT NULL THEN ano.anonymizemld (v_van, v_tot); END IF; END; PROCEDURE anonimiseer_bezoeker AS v_van DATE; v_tot DATE; anonymize_period NUMBER := fac.safe_to_number (fac.getsetting ('anonymize_period')); BEGIN SELECT datum_van INTO v_van FROM (SELECT MIN (TRUNC (a.bez_afspraak_datum)) datum_van FROM bez_afspraak a WHERE a.bez_afspraak_datum < (TRUNC (SYSDATE) - anonymize_period) -- de afspraak vindt plaats voor de anonimiseer periode AND NOT EXISTS --- nog niet geanonimiseerd (SELECT ft.fac_tracking_key FROM fac_tracking ft WHERE ft.fac_tracking_refkey = a.bez_afspraak_key AND ft.fac_srtnotificatie_key = (SELECT fac_srtnotificatie_key FROM fac_srtnotificatie WHERE fac_srtnotificatie_code = 'BEZANO'))); SELECT TRUNC (SYSDATE - anonymize_period) INTO v_tot FROM DUAL; IF v_van IS NOT NULL THEN ano.anonymizebez (v_van, v_tot); END IF; END; END; / -- Organisatie import -- Aangeleverde structuur en mapping -- Bedrijfsnaam --> wordt niet gebruikt -- Divisiecode --> afdeling_naam niveau 1 -- Divisieomschrijving --> afdeling_omschrijving niveau 1 -- Bedrijfsonderdeelcode --> afdeling_naam niveau 2 -- Bedrijfsonderdeelomschrijving --> afdeling_omschrijving niveau 2 -- Afdelingscode --> afdeling_naam niveau 3 -- Afdelingsomschrijving --> afdeling_omschrijving niveau 3 -- Teamcode --> afdeling_naam niveau 4 -- Teamomschrijving --> afdeling_omschrijving niveau 4 -- Kostenplaatscode --> kostenplaats nummer -- Kostenplaatsomschrijving --> kostenplaats omschrijving CREATE OR REPLACE PROCEDURE cvgz_import_organisatie (p_import_key IN NUMBER) AS c_fielddelimitor VARCHAR2 (1) := ','; v_newline VARCHAR2 (1000); -- Input line v_errormsg VARCHAR2 (1000); oracle_err_num NUMBER; oracle_err_mes VARCHAR2 (200); header_is_valid NUMBER; v_count NUMBER; v_ongeldig NUMBER (1); v_mode NUMBER (1); v_aanduiding VARCHAR2 (200); v_all_null BOOLEAN; v_count_error NUMBER (10); v_count_tot NUMBER (10); v_count_import NUMBER (10); -- De importvelden: v_bedrijf_naam VARCHAR2 (100); v_afdeling_naam VARCHAR2 (100); v_afdeling_oms VARCHAR2 (100); v_afdeling_naam_parent VARCHAR2 (100); v_kostenplaats_omschrijving VARCHAR2 (100); v_kostenplaats_nr VARCHAR2 (100); i NUMBER; CURSOR c IS SELECT * FROM fac_imp_file WHERE fac_import_key = p_import_key ORDER BY fac_imp_file_index; BEGIN v_bedrijf_naam := 'VGZ'; v_count_error := 0; header_is_valid := 0; v_count_tot := 0; v_count_import := 0; FOR i IN 1..4 LOOP FOR rec IN c LOOP BEGIN v_errormsg := 'Fout FETCH te importeren rij'; v_newline := rec.fac_imp_file_line; v_errormsg := 'Fout opvragen te importeren rij'; v_aanduiding := ''; -- Insert geformatteerde import record IF header_is_valid = 0 THEN IF i = 1 AND rec.fac_imp_file_index = 1 THEN IF REPLACE(v_newline, '"') LIKE 'Bedrijfsnaam,Divisiecode,Divisieomschrijving,Bedrijfsonderdeelcode,Bedrijfsonderdeelomschrijving,Afdelingscode,Afdelingsomschrijving,Teamcode,Teamomschrijving,Kostenplaatscode,Kostenplaatsomschrijving%' THEN header_is_valid := 1; DELETE FROM fac_imp_organisatie; ELSE fac.imp_writelog (p_import_key, 'E', 'Geen geldige header aangetroffen', v_newline); END IF; END IF; ELSIF rec.fac_imp_file_index = 1 THEN -- De header slaan we na de eerste keer over NULL; ELSE -- Lees alle veldwaarden fac.imp_getfield_nr (v_newline, c_fielddelimitor, (i*2), v_afdeling_naam); fac.imp_getfield_nr (v_newline, c_fielddelimitor, (i*2)+1, v_afdeling_oms); fac.imp_getfield_nr (v_newline, c_fielddelimitor, ((i-1)*2), v_afdeling_naam_parent); IF i = 4 THEN fac.imp_getfield_nr (v_newline, c_fielddelimitor, 11, v_kostenplaats_omschrijving); fac.imp_getfield_nr (v_newline, c_fielddelimitor, 10, v_kostenplaats_nr); END IF; v_aanduiding := v_afdeling_naam || '|' || v_afdeling_oms || '|' || v_kostenplaats_nr || '| '; -- soms komt dezelfde afdeling op meerdere niveau's voor. Vandaar dat bij de insert een conditie staat IF v_afdeling_naam IS NOT NULL THEN v_count_tot := v_count_tot + 1; v_errormsg := 'Fout bij toevoegen te impoteren bedrijf/afdeling [' || v_aanduiding || ']'; INSERT INTO fac_imp_organisatie (prs_bedrijf_naam, prs_afdeling_omschrijving, prs_afdeling_naam, prs_afdeling_naam_parent, prs_kostenplaats_nr) SELECT v_bedrijf_naam, v_afdeling_oms, v_afdeling_naam, v_afdeling_naam_parent, v_kostenplaats_nr FROM DUAL WHERE NOT EXISTS ( SELECT 1 FROM fac_imp_organisatie WHERE prs_afdeling_naam = v_afdeling_naam); -- Het koppelen van de kostenplaats aan de organisatie gaan we doen bij de personen import END IF; IF i = 4 AND v_kostenplaats_nr IS NOT NULL AND v_kostenplaats_omschrijving IS NOT NULL THEN INSERT INTO fac_imp_kpn (prs_kostenplaats_nr, prs_kostenplaats_omschrijving, prs_kostenplaats_module) SELECT v_kostenplaats_nr, v_kostenplaats_omschrijving, 'PRS' FROM DUAL WHERE NOT EXISTS ( SELECT 1 FROM fac_imp_kpn WHERE prs_kostenplaats_nr = v_kostenplaats_nr); END IF; END IF; END; END LOOP; END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN oracle_err_num := SQLCODE; oracle_err_mes := SUBSTR (SQLERRM, 1, 100); v_errormsg := 'error ' || oracle_err_num || '/' || oracle_err_mes; fac.imp_writelog (p_import_key, 'E', v_errormsg, ''); END cvgz_import_organisatie; / CREATE OR REPLACE PROCEDURE cvgz_update_organisatie (p_import_key IN NUMBER) AS CURSOR c_del_kpn IS SELECT k.prs_kostenplaats_key, k.prs_kostenplaats_nr, k.prs_kostenplaats_omschrijving FROM prs_kostenplaats k WHERE prs_kostenplaats_module = 'PRS' AND prs_kostenplaats_eind IS NULL AND NOT EXISTS (SELECT 1 FROM fac_imp_kpn i WHERE i.prs_kostenplaats_nr = k.prs_kostenplaats_nr); CURSOR c_del_afd IS SELECT prs_afdeling_key, prs_afdeling_naam, prs_afdeling_omschrijving FROM prs_v_afdeling WHERE prs_afdeling_verwijder IS NULL AND prs_bedrijf_key = 1 -- Alleen afdelingen onder het bedrijf VGZ worden verwijderd. AND prs_afdeling_upper NOT IN (SELECT prs_afdeling_naam FROM fac_imp_organisatie) AND prs_afdeling_upper NOT IN (SELECT prs_afdeling_naam FROM fac_imp_perslid) ORDER BY niveau DESC; v_errormsg VARCHAR (200); v_errorhint VARCHAR (200); oracle_err_mes VARCHAR2 (150); oracle_err_num NUMBER; v_aanduiding VARCHAR (100); v_bedrijf_key NUMBER; v_kostenplaats_key NUMBER; v_afdeling_key NUMBER; v_count NUMBER; v_status VARCHAR2 (1); BEGIN fac_update_kpn (p_import_key); CVGZ.update_organisatie (p_import_key); FOR rec IN c_del_kpn LOOP BEGIN v_aanduiding := rec.prs_kostenplaats_nr || '-' || rec.prs_kostenplaats_omschrijving; v_errorhint := 'Kijk we de kostenplaats kunnen verwijderen'; UPDATE prs_kostenplaats SET prs_kostenplaats_eind = SYSDATE WHERE prs_kostenplaats_key = rec.prs_kostenplaats_key; EXCEPTION WHEN OTHERS THEN oracle_err_mes := SUBSTR (SQLERRM, 1, 100); v_errormsg := 'error ' || SQLCODE || '/' || oracle_err_mes; fac.imp_writelog (p_import_key, 'E', v_errormsg, 'Het lukt niet om de kostenplaats te verwijderen:' || '-' || v_aanduiding ); END; END LOOP; FOR rec IN c_del_afd LOOP BEGIN v_aanduiding := rec.prs_afdeling_naam || '-' || rec.prs_afdeling_omschrijving; v_errorhint := 'Kijk we de afdeling kunnen verwijderen'; UPDATE prs_afdeling SET prs_afdeling_verwijder = SYSDATE WHERE prs_afdeling_key = rec.prs_afdeling_key; EXCEPTION WHEN OTHERS THEN oracle_err_mes := SUBSTR (SQLERRM, 1, 100); IF INSTR(oracle_err_mes, 'prs_m015') > 0 THEN v_status := 'W'; v_errormsg := 'Afdeling kan niet verwijderd worden. Er zijn nog personen aanwezig'; ELSE v_errormsg := 'error ' || SQLCODE || '/' || oracle_err_mes; v_status := 'E'; END IF; fac.imp_writelog (p_import_key, v_status, v_errormsg, 'Het lukt niet om de afdeling te verwijderen:' || '-' || v_aanduiding ); END; END LOOP; END cvgz_update_organisatie; / CREATE OR REPLACE PROCEDURE cvgz_import_perslid (p_import_key IN NUMBER) AS v_seq_of_columns VARCHAR (255); oracle_err_num NUMBER; oracle_err_mes VARCHAR2 (200); v_errormsg VARCHAR2 (200); v_errorhint VARCHAR2 (200); BEGIN -- Import formaat: -- Bedrijfsnaam 1 -- Loginnaam 2 -- Weergavenaam 3 -- Achternaam 4 -- Tussenvoegsel 5 -- Voorletters 6 -- Voornaam 7 -- Afdelingscode 8 -- Kostenplaatscode 9 -- Functie 10 -- Email 11 -- Mobiel 12 -- Achternaam;1 -- Department code;2 -- E-mail;3 -- Functie;4 -- Gebruikersnaam;5 -- Personeelsnummer;6 -- Voornaam;7 -- Weergavenaam;8 v_seq_of_columns := '0;0;0;0;0;0;8;4;5;7;' -- v_alg_locatie_code 1 -- v_alg_gebouw_code 2 -- v_alg_verdieping_volgnr 3 -- v_alg_ruimte_nr 4 -- v_prs_werkplek_volgnr 5 -- v_prs_werkplek_omschrijving 6 -- v_prs_afdeling_naam 7 -- v_prs_perslid_naam 8 -- v_prs_perslid_tussenvoegsel 9 -- v_prs_perslid_voornaam 10 || '6;0;0;0;0;0;0;11;0;0;' -- v_prs_perslid_voorletters 11 -- v_prs_perslid_partner_naam 12 -- v_prs_perslid_partner_tussenv 13 -- v_prs_naamgebruik_code 14 0 (default) perslid_naam en perslid_tussenvoegsel worden gebruikt -- 1 personen die de geboortenaam van de partner icm de eigen naam willen gebruiken -- 2 personen die de geboortenaam van de partner willen gebruiken -- 3 personen die de eigen naam icm de geboortenaam van de partner willen gebruiken -- v_prs_perslid_geslacht 15 Gebruik 'man', 'm' of 1 voor man en 'vrouw', 'v' of 0 voor vrouw case insensitive geen default -- v_prs_perslid_telefoonnr 16 -- v_prs_perslid_mobiel 17 -- v_prs_perslid_email 18 -- v_prs_perslid_dienstverband 19 -- v_prs_perslid_nr 20 || '10;2;0;0;0;0;3;9;12;0;' -- v_prs_srtperslid_omschrijving 21 -- v_prs_perslid_oslogin 22 -- v_prs_perslid_wachtwoord 23 -- v_prs_perslid_titel 24 -- v_prs_perslid_apikey 25 -- v_dummy 26 -- v_prs_kenmerk1 27 -- v_prs_kenmerk2 28 -- v_prs_kenmerk3 29 -- v_prs_kenmerk4 30 || '0;0;0;0;0;0;0;0;0;0;' || '0;0;0;0;0;0'; -- Het aangeleverde bestand heeft een komma als scheidingsteken. Deze wordt hier maar even vervangen. UPDATE fac_imp_file SET fac_imp_file_line = cvgz.change_delimitor(fac_imp_file_line, ',') WHERE fac_import_key = p_import_key; prs.import_perslid(p_import_key, v_seq_of_columns, '"Bedrijfsnaam";"Loginnaam";"Weergavenaam";"Achternaam";"Tussenvoegsel";"Voorletters";"Voornaam";"Afdelingscode";"Kostenplaatscode";"Functie";"Email";"Mobiel"%'); UPDATE fac_imp_perslid SET prs_perslid_oslogin = UPPER(prs_perslid_oslogin); END cvgz_import_perslid; / CREATE OR REPLACE PROCEDURE cvgz_update_perslid (p_import_key IN NUMBER) IS CURSOR c_del IS SELECT p.prs_perslid_key, p.prs_perslid_nr, pf.prs_perslid_naam_full, p.prs_perslid_oslogin FROM fac_imp_perslid i, prs_perslid p, prs_v_perslid_fullnames_all pf, prs_v_afdeling a WHERE p.prs_perslid_oslogin = i.prs_perslid_oslogin(+) AND p.prs_afdeling_key = a.prs_afdeling_key AND a.prs_bedrijf_key = 1 AND INSTR(UPPER(p.prs_perslid_email), '@VGZ.NL') > 0 AND pf.prs_perslid_key = p.prs_perslid_key AND ( SUBSTR(p.prs_perslid_oslogin,1,1) <> '_' OR (p.prs_perslid_oslogin IS NULL AND p.prs_perslid_inactief IS NOT NULL) ) AND i.prs_perslid_oslogin IS NULL AND p.prs_perslid_verwijder IS NULL; CURSOR c_tel IS SELECT p.prs_perslid_oslogin, pp.prs_perslid_telefoonnr, pp.prs_perslid_mobiel FROM fac_imp_perslid p, prs_perslid pp WHERE p.prs_perslid_oslogin = pp.prs_perslid_oslogin AND ( pp.prs_perslid_telefoonnr IS NOT NULL OR pp.prs_perslid_mobiel IS NOT NULL); CURSOR c_man IS SELECT v.prs_perslid_key, v.prs_perslid_naam || ', ' || v.prs_perslid_voorletters || ' (' || v.prs_perslid_voornaam || ')' as prs_perslid_naam_full, v.prs_perslid_nr, v.prs_perslid_oslogin FROM fac_imp_perslid i, prs_v_aanwezigperslid v, prs_afdeling a WHERE i.prs_perslid_key = v.prs_perslid_key AND v.prs_afdeling_key = a.prs_afdeling_key AND a.prs_bedrijf_key = 1 -- VGZ AND INSTR(v.prs_perslid_naam,'INACTIEF') = 0 ; CURSOR c_org IS SELECT DISTINCT i.prs_afdeling_naam, prs_kenmerk2 FROM fac_imp_perslid i WHERE NOT EXISTS (SELECT 1 FROM prs_afdeling a WHERE i.prs_afdeling_naam || '-' || prs_kenmerk2 = a.prs_afdeling_naam AND a.prs_afdeling_verwijder IS NULL); v_count_mandaat NUMBER (10); v_count_man_prs NUMBER (10) := 0; v_afdeling_key PRS_AFDELING.PRS_AFDELING_KEY%TYPE; v_afdeling_omschrijving PRS_AFDELING.PRS_AFDELING_OMSCHRIJVING%TYPE; v_kostenplaats_key PRS_KOSTENPLAATS.PRS_KOSTENPLAATS_KEY%TYPE; v_aanduiding VARCHAR2 (100); v_errorhint VARCHAR2 (1000); v_errormsg VARCHAR2 (1000); v_count NUMBER; oracle_err_num NUMBER; oracle_err_mes VARCHAR2 (200); BEGIN SELECT COUNT(*) INTO v_count FROM fac_imp_perslid WHERE fac_import_key = p_import_key; IF v_count > 2000 THEN FOR rec IN c_del LOOP BEGIN v_errorhint := 'Persoon verwijderen: ' || rec.prs_perslid_naam_full || '-' || rec.prs_perslid_nr || '-' || rec.prs_perslid_oslogin; prs.delete_perslid (p_import_key, rec.prs_perslid_key); END; END LOOP; END IF; FOR rec IN c_tel LOOP -- Telefoonnummers voert men zelf in Facilitor handmatig in en zitten dus niet in importbestand. -- We vullen daarom deze aan in de fac_imp_perslid v_errorhint := 'Telefoonnummer toevoegen aan import-tabel' || '-' || '-' || rec.prs_perslid_oslogin; IF rec.prs_perslid_telefoonnr IS NOT NULL THEN UPDATE fac_imp_perslid p SET p.prs_perslid_telefoonnr = rec.prs_perslid_telefoonnr WHERE p.prs_perslid_oslogin = rec.prs_perslid_oslogin; END IF; IF rec.prs_perslid_mobiel IS NOT NULL THEN UPDATE fac_imp_perslid p SET p.prs_perslid_mobiel = rec.prs_perslid_mobiel WHERE p.prs_perslid_oslogin = rec.prs_perslid_oslogin; END IF; END LOOP; FOR rec IN c_org LOOP BEGIN v_errorhint := 'Zoeken afdeling: ' || rec.prs_afdeling_naam; SELECT prs_afdeling_key, prs_afdeling_omschrijving INTO v_afdeling_key, v_afdeling_omschrijving FROM prs_afdeling WHERE prs_afdeling_naam = rec.prs_afdeling_naam AND prs_afdeling_verwijder IS NULL; v_errorhint := 'Zoeken kostenplaats: ' || rec.prs_kenmerk2; SELECT prs_kostenplaats_key INTO v_kostenplaats_key FROM prs_kostenplaats WHERE prs_kostenplaats_nr = rec.prs_kenmerk2 AND prs_kostenplaats_verwijder IS NULL; v_errorhint := 'Toevoegen afdeling/kostenplaats: ' || rec.prs_afdeling_naam || '/' ||rec.prs_kenmerk2; INSERT INTO prs_afdeling (prs_afdeling_naam, prs_afdeling_omschrijving, prs_afdeling_parentkey, prs_kostenplaats_key) VALUES ( rec.prs_afdeling_naam || '-' || rec.prs_kenmerk2, rec.prs_kenmerk2 || ' - ' || v_afdeling_omschrijving, v_afdeling_key, v_kostenplaats_key); EXCEPTION WHEN OTHERS THEN oracle_err_num := SQLCODE; oracle_err_mes := SUBSTR (SQLERRM, 1, 100); v_errormsg := 'OTHERS (error ' || oracle_err_num || '/' || oracle_err_mes || ')'; fac.imp_writelog (p_import_key, 'E', v_aanduiding || v_errormsg, v_errorhint); END; END LOOP; -- Pas de afdelingscode aan aan de nieuw aangemaakte structuur. UPDATE fac_imp_perslid SET prs_afdeling_naam = prs_afdeling_naam || '-' || prs_kenmerk2; -- Nu kunnen we de standaard facilitor-update doorvoeren prs.update_perslid (p_import_key, 'LOGIN', NULL); -- Gaan nu de mandetering toevoegen indien nodig FOR rec IN c_man LOOP v_errorhint := 'Mandatering toevoegen || ' || rec.prs_perslid_naam_full || '-' || rec.prs_perslid_nr || '-' || rec.prs_perslid_oslogin; -- Is er al mandatering voor die persoon vastgelegd SELECT COUNT(*) INTO v_count_mandaat FROM prs_perslidkostenplaats WHERE prs_perslid_key = rec.prs_perslid_key -- AND prs_kostenplaats_key IS NULL -- volledig mandaat ; IF v_count_mandaat = 0 THEN INSERT INTO prs_perslidkostenplaats (prs_perslidkostenplaats_boeken, prs_perslidkostenplaats_inzage, prs_perslid_key, prs_kostenplaats_key) VALUES (1, 1, rec.prs_perslid_key, NULL); v_count_man_prs := v_count_man_prs + 1 ; END IF; END LOOP; fac.imp_writelog ( p_import_key, 'S', 'Mandatering - doorgevoerd op ' || TO_CHAR (v_count_man_prs) || ' personen' , ''); COMMIT; EXCEPTION WHEN OTHERS THEN oracle_err_num := SQLCODE; oracle_err_mes := SUBSTR (SQLERRM, 1, 100); v_errormsg := 'OTHERS (error ' || oracle_err_num || '/' || oracle_err_mes || ')'; fac.imp_writelog (p_import_key, 'E', v_aanduiding || v_errormsg, v_errorhint); END cvgz_update_perslid; / -- Via CUST07 om styling te kunnen opmaken CREATE OR REPLACE VIEW cvgz_v_noti_leider_mldnew ( sender, receiver, text, code, key, xkey ) AS SELECT NULL, pk.prs_perslid_key, 'Registratie ' || sm.mld_stdmelding_omschrijving || ' van/door ' || pm.prs_perslid_voornaam || ' ' || pm.prs_perslid_naam || ' (nr. ' || m.mld_melding_key || ')', 'CUST07', m.mld_melding_key, NULL FROM mld_melding m, prs_perslid pm, mld_stdmelding sm, mld_kenmerkmelding km, mld_kenmerk k, prs_perslid pk, ( SELECT fac_tracking_refkey, MAX (fac_tracking_datum) fac_tracking_datum FROM fac_tracking ft, fac_srtnotificatie fsn WHERE fsn.fac_srtnotificatie_key = ft.fac_srtnotificatie_key AND fac_srtnotificatie_code = 'MLDNEW' GROUP BY fac_tracking_refkey) ft WHERE m.mld_stdmelding_key = sm.mld_stdmelding_key AND m.mld_melding_key = km.mld_melding_key AND km.mld_kenmerk_key = k.mld_kenmerk_key AND k.mld_srtkenmerk_key = 246 AND m.prs_perslid_key_voor = pm.prs_perslid_key AND fac.safe_to_number (km.mld_kenmerkmelding_waarde) = pk.prs_perslid_key AND m.mld_melding_key = ft.fac_tracking_refkey AND ft.fac_tracking_datum > (SELECT fac_notificatie_job_nextrun - fac_notificatie_job_interval / 24 FROM fac_notificatie_job WHERE fac_notificatie_job_view = 'CVGZ_V_NOTI_LEIDER_MLDNEW') AND ft.fac_tracking_datum < (SELECT fac_notificatie_job_nextrun FROM fac_notificatie_job WHERE fac_notificatie_job_view = 'CVGZ_V_NOTI_LEIDER_MLDNEW') ; -- View om het mogelijk te maken om de badges voor de bezoekers in bulk te kunnen printen CREATE OR REPLACE VIEW cvgz_v_rap_bezoek_bulk AS SELECT bez_afspraak_datum, a.bez_afspraak_key, bez_afspraak_naam, bez_afspraak_bedrijf, TO_CHAR (bez_afspraak_datum, 'dd-mm-yyyy') bez_afspraak_datum_datum, bez_afspraak_gastheer, bez_afspraak_ruimte FROM bez_bezoekers b, bez_afspraak a WHERE b.bez_afspraak_key = a.bez_afspraak_key; --de daily task draait elke ochtend om 5 uur. CREATE OR REPLACE PROCEDURE cvgz_daily AS v_errormsg VARCHAR2 (1000); oracle_err_num NUMBER; oracle_err_mes VARCHAR2 (200); BEGIN -- anonimiseer personen cvgz.anonimiseer_prs (); -- anonimiseer bezoekers cvgz.anonimiseer_bezoeker (); -- anonimiseer meldingen cvgz.anonimiseer_mld (); EXCEPTION WHEN OTHERS THEN oracle_err_num := SQLCODE; oracle_err_mes := SUBSTR (SQLERRM, 1, 200); v_errormsg := 'ORACLE (error ' || oracle_err_num || '/' || oracle_err_mes || ')'; fac.writelog ('DAILY', 'E', 'Proces afgebroken!', v_errormsg); END cvgz_daily; / ------ 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