243 lines
9.0 KiB
SQL
243 lines
9.0 KiB
SQL
-- 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;
|