-- Customer specific once-script UWVA86. -- -- (c) 2014 SG|facilitor bv SET ECHO ON SPOOL xuwva86.lst SET DEFINE OFF -- UWVA#27483 E-mail adres volgens schrijfwijzer! /* Formatted on 17-2-2014 11:48:12 (QP5 v5.136.908.31019) */ CREATE OR REPLACE VIEW uwva_v_email_schrijfwijzer ( prs_key, fac_id, imp_id, imp_empnum, imp_nickname, imp_lastname, imp_spousename, fac_email, imp_email, sw_email ) AS SELECT DISTINCT p.prs_perslid_key, kl.prs_kenmerklink_waarde, ip.id, ip.empnum, ip.nickname, ip.lastname, ip.spousename, p.prs_perslid_email, ip.email, DECODE ( INSTR (COALESCE (p.prs_perslid_email, ip.email), '@'), NULL, -- geen email bekend -> ongewijzigd COALESCE (p.prs_perslid_email, ip.email), 0, -- geen '@' in email -> ongewijzigd COALESCE (p.prs_perslid_email, ip.email), CASE -- email met een '@' WHEN INSTR (COALESCE (p.prs_perslid_email, ip.email) || '.', '.') < INSTR (COALESCE (p.prs_perslid_email, ip.email), '@') THEN -- . voor @ (dus meeste gevallen) REPLACE ( REPLACE ( REPLACE ( REPLACE ( INITCAP(SUBSTR (LOWER(COALESCE (p.prs_perslid_email, ip.email)), 1, INSTR (COALESCE (p.prs_perslid_email, ip.email), '.', -1, 2) - 1)) || SUBSTR (LOWER(COALESCE (p.prs_perslid_email, ip.email)), INSTR (COALESCE (p.prs_perslid_email, ip.email), '.', -1, 2)), INITCAP(REPLACE (COALESCE (nickname, vnaam), ' ', '') || '.'), REPLACE (INITCAP (COALESCE (nickname, vnaam)), ' ', '') || '.'), LOWER(SUBSTR (TRIM (COALESCE (lastname, anaam)), 1, INSTR (TRIM (COALESCE (lastname, anaam)), ' ') - 1)), SUBSTR (INITCAP (TRIM (COALESCE (lastname, anaam))), 1, INSTR (TRIM (COALESCE (lastname, anaam)), ' ') - 1)), LOWER(SUBSTR (TRIM (COALESCE (lastname, anaam)), INSTR (TRIM (COALESCE (lastname, anaam)), ' ', -1) + 1)), SUBSTR (INITCAP (TRIM (COALESCE (lastname, anaam))), INSTR (TRIM (COALESCE (lastname, anaam)), ' ', -1) + 1)), LOWER (REPLACE (spousename, ' ', '')), REPLACE (INITCAP (spousename), ' ', '')) ELSE -- geen . voor @ (bkwi.nl/uwv.local; <30!) -> ongewijzigd COALESCE (p.prs_perslid_email, ip.email) END) email_schrijfwijzer FROM (SELECT prs_perslid_key, REPLACE (prs_perslid_email, '$', '@') prs_perslid_email, prs_perslid_voornaam vnaam, prs_perslid_naam anaam FROM prs_v_aanwezigperslid) p LEFT JOIN prs_v_aanwezigkenmerklink kl ON p.prs_perslid_key = kl.prs_link_key AND kl.prs_kenmerk_key = 1232 -- ABS-ID FULL JOIN (SELECT * FROM uwva_imp_abs WHERE SUBSTR (empnum, 1, 1) IN ('C', 'I', 'E', 'F', 'D', 'K')) ip ON kl.prs_kenmerklink_waarde = ip.id; COMMIT; /* Formatted on 17-2-2014 13:05:55 (QP5 v5.136.908.31019) */ DECLARE c_commitbuffer NUMBER := 1000; oracle_err_num NUMBER; oracle_err_mes VARCHAR2 (255); v_errormsg VARCHAR2 (1024); v_errorhint VARCHAR2 (1024); v_buffercount NUMBER; v_updates NUMBER; BEGIN v_buffercount := 0; v_updates := 0; -- Loop over alle lowercase e-mail adressen in FACILITOR en formatteer die -- volgens schrijfwijzer! FOR rc IN (SELECT prs_key, fac_email, sw_email FROM uwva_v_email_schrijfwijzer WHERE fac_email = LOWER (fac_email)) -- Lowercase in FACILITOR! LOOP BEGIN v_errormsg := 'Fout bij bijwerken email'; UPDATE prs_perslid SET prs_perslid_email = rc.sw_email WHERE prs_perslid_key = rc.prs_key; v_updates := v_updates + 1; v_buffercount := v_buffercount + 1; IF (v_buffercount >= c_commitbuffer) THEN COMMIT; v_buffercount := 0; END IF; EXCEPTION WHEN OTHERS THEN oracle_err_num := SQLCODE; oracle_err_mes := SUBSTR (SQLERRM, 1, 200); v_errorhint := v_errormsg; v_errormsg := '[' || TO_CHAR (rc.prs_key) || '] ' || '(ORACLE error ' || oracle_err_num || '/' || oracle_err_mes || ')'; DBMS_OUTPUT.PUT_LINE ( 'Error: ' || v_errormsg || '; Hint: ' || v_errorhint); END; END LOOP; DBMS_OUTPUT.PUT_LINE ('#emails updated: ' || TO_CHAR (v_updates)); END; / COMMIT; --UWVA#28572: MLD: rapport op behandelgroepen, vakgroeptype, vakgroep en mld (24 = WEB_MLDBOF) CREATE OR REPLACE VIEW uwva_v_rap_mld_behandelgroep AS SELECT NULL x FROM DUAL; INSERT INTO FAC_USRRAP (fac_usrrap_omschrijving, fac_usrrap_view_name, fac_usrrap_in_huidige_locatie, fac_usrrap_template, fac_usrrap_macro, fac_usrrap_vraagbegindatum, fac_usrrap_vraageinddatum, fac_usrrap_functie, fac_usrrap_info, fac_functie_key, fac_usrrap_autorefresh) VALUES ('MLD Behandelgroepen', 'uwva_v_rap_mld_behandelgroep', NULL, NULL, NULL, 0, 0, 0, 'Overzicht van vakgroepen en meldingen per behandelgroep', 24, 0); COMMIT; -- UWVA#28625: Voortzetting KTO na pilot! UPDATE ins_tab_discipline td SET ins_discipline_ktopercentage = 20, td.ins_discipline_ktodrempel = 0 WHERE ins_discipline_verwijder IS NULL AND ins_discipline_module = 'MLD' AND ins_srtdiscipline_key IN (1, 2, 3, 9, 10) AND (COALESCE (ins_discipline_ktopercentage, -1) != 20 OR COALESCE (ins_discipline_ktodrempel, -1) != 0); UPDATE mld_stdmelding sm SET sm.mld_stdmelding_kto = 1 WHERE sm.mld_stdmelding_verwijder IS NULL AND COALESCE (sm.mld_stdmelding_vervaldatum, SYSDATE) >= TRUNC (SYSDATE) --AND sm.mld_stdmelding_notfrontend = 0 AND COALESCE (sm.mld_stdmelding_kto, -1) != 1 AND EXISTS (SELECT 1 FROM ins_tab_discipline WHERE ins_discipline_verwijder IS NULL AND ins_discipline_module = 'MLD' AND ins_srtdiscipline_key IN (1, 2, 3, 9, 10) AND ins_discipline_ktopercentage = 20 AND ins_discipline_ktodrempel = 0 AND ins_discipline_key = sm.mld_ins_discipline_key); COMMIT; -- UWVA#28494: FDO CNT noti reminder naar LFM INSERT INTO fac_notificatie_job (fac_notificatie_job_view, fac_notificatie_job_oms, fac_notificatie_job_mode, fac_notificatie_job_interval, fac_notificatie_job_nextrun, fac_notificatie_job_flags) VALUES ('UWVA_V_NOTI_CNT_LFM_REMINDER', 'Jaarlijkse LFM reminder tbv FDO', 3, 1, null, 0); INSERT INTO fac_srtnotificatie (fac_srtnotificatie_code, fac_srtnotificatie_mode, fac_srtnotificatie_oms, fac_srtnotificatie_xmlnode, fac_srtnotificatie_groep, fac_srtnotificatie_usermode, fac_srtnotificatie_delay) VALUES ('CUST23', 2, 'LFM FDO reminder', 'contract', 0, 0, 0); COMMIT; BEGIN adm.systrackscriptId('$Id$'); END; / SPOOL OFF @@uwva_prs.sql @@uwva_fin.sql --@@uwva_hv.sql @@uwva_misc.sql BEGIN fac.registercustversion('UWVA', 86); END; / COMMIT;