admin/Customer
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
Customer/PCHW/pchw.sql
Sander Schepers a33a4d505c PCHW#85273 Problemen met synchronisatie van reserveringen in een reeks 
svn path=/Customer/trunk/; revision=66112
2024-09-03 07:12:25 +00:00

2630 lines
101 KiB
MySQL
Raw Permalink Blame History

--
-- $Id$
--
-- Script containing customer specific configuration sql statements for PCHW: PCH Gemeente Westland
DEFINE thisfile = 'PCHW.SQL'
DEFINE dbuser = '^PCHW'
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 pchw_import_perslid (p_import_key IN NUMBER)
AS
v_seq_of_columns VARCHAR(255);
BEGIN
v_seq_of_columns := '0;0;0;4;5;6;7;8;9;11;10;0;0;0;0;13;14;15;0;17;16;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46';
prs.import_perslid(p_import_key, v_seq_of_columns, 'LOCATIECODE;GEBOUWCODE;BOUWLAAGVOLGNUMMER;RUIMTENUMMER;WERKPLEKVOLGNUMMER;OMSCHRIJVING;AFDELINGSCODE;PERSOONACHTERNAAM;TUSSENVOEGSEL;VOORLETTERS;VOORNAAM;TITEL/AANHEF;TELEFOONNUMMER;MOBIEL;EMAIL;FUNCTIE;PERSONEELSNUMMER;LOGINNAAM;PASSWORD%');
-- Het personeelsnummer gebruiken we ook als login. Inloggen via SSO vindt plaats op emailadres.
-- Doordat de import soms door Excel gehaald wordt zetten we zelf de voorloopnullen er weer in.
UPDATE fac_imp_perslid
SET prs_perslid_oslogin = prs_perslid_nr,
prs_perslid_nr = TO_CHAR (prs_perslid_nr, 'FM000000000');
END pchw_import_perslid;
/
CREATE OR REPLACE PROCEDURE pchw_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
FROM fac_imp_perslid i, prs_perslid p, prs_v_perslid_fullnames_all pf
WHERE p.prs_perslid_nr = i.prs_perslid_nr(+)
AND pf.prs_perslid_key = p.prs_perslid_key
AND p.prs_perslid_nr IS NOT NULL
AND i.prs_perslid_nr IS NULL
AND p.prs_perslid_verwijder IS NULL
AND p.prs_srtperslid_key <> 57055 -- Functie 'Functioneel account (niet verwijderen)' uitsluiten ivm PCHW#85273
ORDER BY 2;
CURSOR c_imp
IS
SELECT *
FROM fac_imp_file
WHERE fac_import_key = p_import_key;
v_perslid_key NUMBER;
v_perslid_nr VARCHAR2(100);
v_kostenplaats_nr VARCHAR2(100);
v_count NUMBER;
BEGIN
-- generic update
SELECT count(*)
INTO v_count
FROM fac_imp_perslid;
IF v_count < 500
THEN
fac.imp_writelog (p_import_key,
'E',
'Het aantal te importeren personen is te klein ('
|| TO_CHAR (v_count) || ')',
'Zie Specificatie'
);
RETURN;
END IF;
prs.update_perslid (p_import_key, 'NR', NULL);
-- 2 maanden na het zetten van de uitdienst datum worden de medewerkers daadwerkelijk verwijderd.
FOR rec IN c_del
LOOP
BEGIN
prs.delete_perslid (p_import_key, rec.prs_perslid_key);
END;
END LOOP;
INSERT INTO prs_perslidkostenplaats (prs_perslid_key, prs_perslidkostenplaats_boeken)
SELECT prs_perslid_key, 1
FROM prs_v_aanwezigperslid p
WHERE NOT EXISTS (
SELECT pk.prs_perslid_key
FROM prs_perslidkostenplaats pk
WHERE p.prs_perslid_key = pk.prs_perslid_key);
END pchw_update_perslid;
/
CREATE OR REPLACE PROCEDURE pchw_import_organisatie (p_import_key IN NUMBER)
AS
BEGIN
fac_import_organisatie (p_import_key);
END pchw_import_organisatie;
/
CREATE OR REPLACE PROCEDURE pchw_update_organisatie (p_import_key IN NUMBER)
AS
CURSOR c_del
IS
SELECT prs_afdeling_key, prs_afdeling_upper, prs_afdeling_omschrijving
FROM prs_v_afdeling
WHERE prs_afdeling_verwijder IS NULL
AND prs_bedrijf_key = 121
AND prs_afdeling_upper NOT IN (SELECT prs_afdeling_naam FROM fac_imp_organisatie)
ORDER BY niveau DESC;
CURSOR c_kpn
IS
SELECT b.prs_afdeling_key, a1.prs_kostenplaats_key, prs_afdeling_naam, prs_afdeling_omschrijving
FROM prs_v_afdeling_boom b, prs_afdeling a1
WHERE b.prs_bedrijf_key = 121 -- Gemeente Westland
AND b.prs_afdeling_key1 = a1.prs_afdeling_key
AND b.niveau <> 1;
BEGIN
fac_update_organisatie (p_import_key);
-- Vervallen afdeling proberen we te verwijderen
FOR rec IN c_del
LOOP
BEGIN
UPDATE prs_afdeling
SET prs_afdeling_verwijder = SYSDATE
WHERE prs_afdeling_key = rec.prs_afdeling_key;
EXCEPTION
WHEN OTHERS
THEN
fac.imp_writelog (p_import_key,
'W',
'Afdeling kan niet verwijderd worden:' || rec.prs_afdeling_key || ' Code:' || rec.prs_afdeling_upper || ' Omschrijving:' || rec.prs_afdeling_omschrijving,
'');
END;
END LOOP;
-- aanpassen vqan kostenplaatsen bij afdelingen. De kostenplaatsen worden bij het top element vastgelegd.
-- De kinderen moeten deze kostenplaats dus erven.
FOR rec IN c_kpn
LOOP
BEGIN
UPDATE prs_afdeling
SET prs_kostenplaats_key = rec.prs_kostenplaats_key
WHERE prs_afdeling_key = rec.prs_afdeling_key;
EXCEPTION
WHEN OTHERS
THEN
fac.imp_writelog (p_import_key,
'W',
'Kostenplaats kan niet aangepast worden: Code:' || rec.prs_afdeling_naam || ' Omschrijving:' || rec.prs_afdeling_omschrijving,
'');
END;
END LOOP;
END pchw_update_organisatie;
/
-- Importfuncties onroerendgoed vanaf verdieping.
CREATE OR REPLACE PROCEDURE pchw_import_onrgoed2 (p_import_key IN NUMBER)
AS
BEGIN
UPDATE fac_imp_file
SET fac_imp_file_line = REPLACE (fac_imp_file_line, CHR (160), ' ')
WHERE fac_import_key = p_import_key;
fac_import_onrgoed2 (p_import_key);
UPDATE fac_imp_onrgoed2 SET alg_srtruimte_omschrijving = TRIM(alg_srtruimte_omschrijving);
END;
/
CREATE OR REPLACE PROCEDURE pchw_update_onrgoed2 (p_import_key IN NUMBER)
AS
BEGIN
fac_update_onrgoed2 (p_import_key);
-- plaats de ruimte type codering in het code veld.
UPDATE alg_srtruimte
SET alg_srtruimte_code = SUBSTR (alg_srtruimte_omschrijving, 1, 7)
WHERE alg_srtruimte_code IS NULL;
-- pas de openingstijden van de ruimten aan waar een 24/7 regime geldt. Verder geldt voor
-- deze ruimten ook dat de sla in het weekend door telt.
--UPDATE alg_ruimte
-- SET
-- (alg_ruimte_beginuur,
-- alg_ruimte_einduur,
-- alg_ruimte_werkdagen
-- ) =
-- (SELECT DECODE (SUBSTR (alg_onrgoedkenmerk_waarde, 1, 2), '24', 0, NULL),
-- DECODE (SUBSTR (alg_onrgoedkenmerk_waarde, 1, 2), '24', 24, NULL),
-- DECODE (SUBSTR (alg_onrgoedkenmerk_waarde, 1, 2), '24', 0, NULL)
-- FROM alg_onrgoedkenmerk
-- WHERE alg_kenmerk_key = 1000 AND alg_onrgoed_key = alg_ruimte_key);
END;
/
-- Importfunctie voor THT waardes bij meldingen
CREATE OR REPLACE PROCEDURE pchw_import_storing_regime (
p_import_key IN NUMBER)
AS
CURSOR c_cursor
IS
SELECT *
FROM fac_imp_file
WHERE fac_import_key = p_import_key
ORDER BY fac_imp_file_index;
v_newline VARCHAR2 (1000);
v_aanduiding VARCHAR2 (1000); -- Import line
v_fielddelimitor VARCHAR2 (1); -- Field seperator
v_errormsg VARCHAR (200);
v_errorhint VARCHAR (200);
oracle_err_num NUMBER;
oracle_err_mes VARCHAR2 (200);
header_found BOOLEAN;
v_error NUMBER(1);
-- De importvelden
v_dummy VARCHAR2(100);
v_r_codering VARCHAR2(100);
v_omschrijving VARCHAR2(100);
v_ruimten VARCHAR2(100);
v_storing_os VARCHAR2(100);
v_stdmelding VARCHAR2(100);
v_vakgroep VARCHAR2(100);
v_vakgroeptype VARCHAR2(100);
v_tht_os VARCHAR2(100);
v_tht_regime VARCHAR2(100);
v_tht VARCHAR2(100);
v_tht_eenheid VARCHAR2(100);
v_bk VARCHAR2(100);
v_vk VARCHAR2(100);
v_srtruimte_key NUMBER;
v_stdmelding_key NUMBER;
v_discipline_key NUMBER;
v_srtdiscipline_key NUMBER;
BEGIN
-- Verwijder nbsp karakters.
UPDATE fac_imp_file
SET fac_imp_file_line = REPLACE (fac_imp_file_line, CHR (160), ' ')
WHERE fac_import_key = p_import_key;
-- Init
header_found := FALSE;
v_fielddelimitor := ';';
-- Clear my previous imported rows
DELETE FROM pchw_imp_storing_regime;
FOR rec1 IN c_cursor
LOOP
BEGIN
v_newline := REPLACE(rec1.fac_imp_file_line, CHR(160), ' ');
v_aanduiding := SUBSTR (v_newline, 1, 200);
v_error := 0;
IF SUBSTR (v_newline, 1, 3) = '?'
THEN
-- EF BB BF aangetroffen
fac.imp_writelog (p_import_key,
'W',
'Byte Order Mark aangetroffen',
'Bestand heeft onbehandeld UTF-8 formaat.');
v_newline := SUBSTR (v_newline, 4);
END IF;
v_errormsg := 'Inlezen velden';
fac.imp_getfield (v_newline, v_fielddelimitor, v_r_codering);
fac.imp_getfield (v_newline, v_fielddelimitor, v_omschrijving);
fac.imp_getfield (v_newline, v_fielddelimitor, v_ruimten);
fac.imp_getfield (v_newline, v_fielddelimitor, v_storing_os);
fac.imp_getfield (v_newline, v_fielddelimitor, v_stdmelding);
fac.imp_getfield (v_newline, v_fielddelimitor, v_vakgroep);
fac.imp_getfield (v_newline, v_fielddelimitor, v_vakgroeptype);
fac.imp_getfield (v_newline, v_fielddelimitor, v_tht_os);
fac.imp_getfield (v_newline, v_fielddelimitor, v_tht_regime);
fac.imp_getfield (v_newline, v_fielddelimitor, v_dummy); -- regime eenheid
fac.imp_getfield (v_newline, v_fielddelimitor, v_tht);
fac.imp_getfield (v_newline, v_fielddelimitor, v_tht_eenheid);
fac.imp_getfield (v_newline, v_fielddelimitor, v_bk);
fac.imp_getfield (v_newline, v_fielddelimitor, v_vk);
-- Skip until a valid header is found
v_errormsg := 'Controleer de header';
IF UPPER (rec1.fac_imp_file_line) LIKE
'R CODERING;OMSCHRIJVING;RUIMTEN;STORING OS;STANDAARD MELDING;VAKGROEP;VAKGROEPTYPE;THT OS;HERSTELREGIME;REGIME EENHEID;THT;THT EENHEID;BK;VK%'
THEN
-- Sla de kopregel over.
header_found := TRUE;
ELSIF (header_found AND v_vakgroep IS NOT NULL)
THEN
-- Kennen we de ruimtecodering
v_errormsg := 'Controleer ruimtesoort';
v_r_codering := REPLACE(v_r_codering, CHR(160));
IF v_r_codering IS NOT NULL
THEN
v_r_codering := TRIM(v_r_codering);
BEGIN
SELECT alg_srtruimte_key
INTO v_srtruimte_key
FROM alg_srtruimte sr
WHERE alg_srtruimte_verwijder IS NULL
AND UPPER (sr.alg_srtruimte_code) = UPPER (v_r_codering);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fac.imp_writelog (
p_import_key,
'E',
'Ruimtecodering niet gevonden: ' || v_r_codering,
v_aanduiding);
v_srtruimte_key := NULL;
v_error := 1;
END;
ELSE
v_srtruimte_key := NULL;
END IF;
v_errormsg := 'Controleer vakgroeptype';
BEGIN
v_vakgroeptype := TRIM(v_vakgroeptype);
SELECT ins_srtdiscipline_key
INTO v_srtdiscipline_key
FROM ins_srtdiscipline sd
WHERE sd.ins_srtdiscipline_module = 'MLD'
AND sd.ins_srtdiscipline_verwijder IS NULL
AND UPPER (sd.ins_srtdiscipline_omschrijving) =
UPPER (v_vakgroeptype);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fac.imp_writelog (
p_import_key,
'E',
'Vakgroeptype niet gevonden: ' || v_vakgroeptype,
v_aanduiding);
v_srtdiscipline_key := NULL;
v_error := 1;
END;
v_errormsg := 'Controleer vakgroep';
BEGIN
v_vakgroep := TRIM(v_vakgroep);
SELECT ins_discipline_key
INTO v_discipline_key
FROM ins_tab_discipline d
WHERE d.ins_discipline_module = 'MLD'
AND d.ins_discipline_verwijder IS NULL
AND d.ins_srtdiscipline_key = v_srtdiscipline_key
AND UPPER (d.ins_discipline_omschrijving) =
UPPER (v_vakgroep);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fac.imp_writelog (
p_import_key,
'E',
'Vakgroep niet gevonden: ' || v_vakgroep,
v_aanduiding);
v_discipline_key := NULL;
v_error := 1;
END;
v_errormsg := 'Controleer stdmelding';
IF v_stdmelding IS NOT NULL
THEN
v_stdmelding := SUBSTR(TRIM(v_stdmelding),1,60);
BEGIN
SELECT mld_stdmelding_key
INTO v_stdmelding_key
FROM mld_stdmelding std
WHERE std.mld_stdmelding_verwijder IS NULL
AND std.mld_ins_discipline_key = v_discipline_key
AND UPPER (std.mld_stdmelding_omschrijving) =
UPPER (v_stdmelding);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fac.imp_writelog (
p_import_key,
'E',
'Standaard melding niet gevonden: ' || v_stdmelding,
v_aanduiding);
v_stdmelding_key := NULL;
v_error := 1;
END;
ELSE
v_stdmelding_key := NULL;
END IF;
v_errormsg := 'Controleer THT';
IF fac.safe_to_number (v_tht) IS NULL AND v_tht IS NOT NULL
THEN
fac.imp_writelog (p_import_key,
'E',
'THT niet geldig: ' || v_tht,
v_aanduiding);
v_error := 1;
END IF;
v_errormsg := 'Controleer THT eenheid';
v_tht_eenheid := TRIM(UPPER(v_tht_eenheid));
IF v_tht_eenheid NOT IN ('UUR', 'MIN', 'DAG') AND v_tht_eenheid IS NOT NULL
THEN
fac.imp_writelog (
p_import_key,
'E',
'THT eehheid niet geldig: ' || v_tht_eenheid,
v_aanduiding);
v_error := 1;
END IF;
v_errormsg := 'Controleer basiskorting';
IF fac.safe_to_number (v_bk) IS NULL AND v_bk IS NOT NULL
THEN
fac.imp_writelog (p_import_key,
'E',
'Basiskorting niet geldig: ' || v_bk,
v_aanduiding);
v_error := 1;
END IF;
v_errormsg := 'Controleer variabele korting';
IF fac.safe_to_number (v_vk) IS NULL AND v_vk IS NOT NULL
THEN
fac.imp_writelog (p_import_key,
'E',
'variabele korting niet geldig: ' || v_vk,
v_aanduiding);
v_error := 1;
END IF;
IF v_error = 0
THEN
INSERT INTO pchw_imp_storing_regime (r_codering,
omschrijving,
ruimten,
storing_os,
stdmelding,
vakgroep,
vakgroeptype,
tht_os,
tht_regime,
tht,
tht_eenheid,
bk,
vk,
alg_srtruimte_key,
mld_stdmelding_key,
mld_discipline_key,
ins_srtdiscipline_key)
VALUES (v_r_codering,
v_omschrijving,
v_ruimten,
v_storing_os,
v_stdmelding,
v_vakgroep,
v_vakgroeptype,
v_tht_os,
v_tht_regime,
v_tht,
v_tht_eenheid,
v_bk,
v_vk,
v_srtruimte_key,
v_stdmelding_key,
v_discipline_key,
v_srtdiscipline_key);
END IF;
COMMIT;
END IF;
END;
END loop;
IF NOT header_found
THEN
fac.imp_writelog (
p_import_key,
'E',
'Geen geldige header aangetroffen',
'Bestand is geen geldig melding regime importbestand.');
END IF;
EXCEPTION
WHEN OTHERS
THEN
v_aanduiding := v_errormsg || ' - ' || v_aanduiding;
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', SUBSTR (v_errormsg, 1, 1000), v_aanduiding);
-- maak de importtabel leeg om te voorkomen dat er halve bestanden ingelezen worden.
DELETE FROM pchw_imp_storing_regime;
END;
/
CREATE OR REPLACE PROCEDURE pchw_update_storing_regime (
p_import_key IN NUMBER)
AS
CURSOR c
IS
SELECT sr.r_codering,
stdmelding,
vakgroep,
vakgroeptype,
fac.safe_to_number (bk) bk,
fac.safe_to_number (vk) vk,
alg_srtruimte_key,
mld_stdmelding_key,
mld_discipline_key,
ins_srtdiscipline_key,
tht_regime,
DECODE (tht_eenheid, 'UUR', 'U', 'MIN', 'U', 'DAG', 'D') eenheid,
DECODE (tht_eenheid,
'UUR', tht,
'MIN', ROUND (tht / 60, 5),
'DAG', tht)
tijdsduur
FROM pchw_imp_storing_regime sr;
CURSOR c_del
IS
SELECT ssr.mld_stdmsrtruimte_key,
d.ins_discipline_omschrijving,
std.mld_stdmelding_omschrijving,
sr.alg_srtruimte_code
FROM mld_stdmsrtruimte ssr,
mld_stdmelding std,
ins_tab_discipline d,
ins_srtdiscipline sd,
alg_srtruimte sr
WHERE NOT EXISTS
(SELECT mld_stdmelding_key
FROM pchw_imp_storing_regime sm
WHERE sm.mld_stdmelding_key = ssr.mld_stdmelding_key
AND sm.alg_srtruimte_key = ssr.alg_srtruimte_key)
AND NOT EXISTS
(SELECT std.mld_stdmelding_key
FROM pchw_imp_storing_regime sm, mld_stdmelding std
WHERE sm.mld_discipline_key = std.mld_ins_discipline_key
AND std.mld_stdmelding_key = ssr.mld_stdmelding_key
AND sm.alg_srtruimte_key = ssr.alg_srtruimte_key
AND sm.mld_stdmelding_key IS NULL)
AND ssr.mld_stdmelding_key = std.mld_stdmelding_key
AND std.mld_ins_discipline_key = d.ins_discipline_key
AND d.ins_srtdiscipline_key = sd.ins_srtdiscipline_key
AND sd.ins_srtdiscipline_omschrijving IN (SELECT DISTINCT vakgroeptype FROM pchw_imp_storing_regime)
AND ssr.alg_srtruimte_key = sr.alg_srtruimte_key;
CURSOR c_upd_std
IS
SELECT DISTINCT
mld_stdmelding_key,
sr1.mld_stdmsrtruimte_t_uitvtijd.eenheid eenheid,
sr1.mld_stdmsrtruimte_t_uitvtijd.tijdsduur tijdsduur
FROM (SELECT mld_stdmelding_key,
DECODE (sr.mld_stdmsrtruimte_t_uitvtijd.eenheid, 'U', 0, 'D', 100)
+ sr.mld_stdmsrtruimte_t_uitvtijd.tijdsduur
duur,
sr.mld_stdmsrtruimte_t_uitvtijd
FROM mld_stdmsrtruimte sr) sr1
WHERE NOT EXISTS (SELECT *
FROM (SELECT mld_stdmelding_key,
DECODE (sr.mld_stdmsrtruimte_t_uitvtijd.eenheid,
'U', 0,
'D', 100)
+ sr.mld_stdmsrtruimte_t_uitvtijd.tijdsduur
duur
FROM mld_stdmsrtruimte sr) sr2
WHERE sr1.mld_stdmelding_key = sr2.mld_stdmelding_key AND sr1.duur < sr2.duur);
v_newline VARCHAR2 (1000);
v_aanduiding VARCHAR2 (1000); -- Import line
v_fielddelimitor VARCHAR2 (1); -- Field seperator
v_errormsg VARCHAR (200);
v_errorhint VARCHAR (200);
oracle_err_num NUMBER;
oracle_err_mes VARCHAR2 (200);
v_count NUMBER;
v_tijdsduur NUMBER (10, 5);
v_eenheid VARCHAR2 (1);
v_basiskorting NUMBER;
v_varkorting NUMBER;
v_stdmsrtruimte_key NUMBER;
v_stdmelding_omschrijving VARCHAR2 (60);
v_begin NUMBER;
v_eind NUMBER;
v_stdmelding_regime NUMBER;
BEGIN
SELECT COUNT ( * ) INTO v_count FROM pchw_imp_storing_regime;
IF v_count < 1000 AND 1=0 -- Toch maar even niet testen. De lijst met Kq meldingen bevat maar 17 records
THEN
fac.imp_writelog (
p_import_key,
'E',
'Aantal ingelezen regels: ' || v_count || ' is te weing.',
NULL);
RETURN;
END IF;
FOR rec IN c
LOOP
v_aanduiding :=
rec.r_codering
|| '-'
|| rec.vakgroep
|| '/'
|| rec.stdmelding
|| ') '
|| v_tijdsduur
|| ' '
|| v_eenheid
|| ' '
|| v_basiskorting
|| '-'
|| v_varkorting;
IF rec.tht_regime = '13'
THEN
v_begin := 7;
v_eind := 20;
v_stdmelding_regime := 2; -- openingstijden
ELSE
v_begin := 0;
v_eind := 24;
v_stdmelding_regime := 3; -- 24/7
END IF;
IF rec.mld_stdmelding_key IS NOT NULL
THEN
-- de hersteltijden gelden niet voor alle meldingen in de vakgroep.
IF rec.alg_srtruimte_key IS NULL
THEN
-- deze melding is gelijk voor alle ruimtesoorten. We gaan dit dus bij de stdmelding aanpassen.
v_errormsg := 'Zoeken stdmelding';
SELECT std.mld_stdmelding_t_uitvoertijd.tijdsduur tijdsduur,
std.mld_stdmelding_t_uitvoertijd.eenheid eenheid,
COALESCE (mld_stdmelding_malusbasis, 0)
mld_stdmelding_malusbasis,
COALESCE (mld_stdmelding_malus, 0) mld_stdmelding_malus
INTO v_tijdsduur,
v_eenheid,
v_basiskorting,
v_varkorting
FROM mld_stdmelding std
WHERE std.mld_stdmelding_key = rec.mld_stdmelding_key
AND std.mld_stdmelding_verwijder IS NULL;
IF v_tijdsduur <> rec.tijdsduur
OR v_eenheid <> rec.eenheid
OR v_basiskorting <> rec.bk
OR v_varkorting <> rec.vk
THEN
fac.imp_writelog (
p_import_key,
'I',
'Gewijzigd stdm1 ('
|| rec.r_codering
|| '-'
|| rec.vakgroep
|| '/'
|| rec.stdmelding
|| ')',
v_tijdsduur
|| ' '
|| v_eenheid
|| ' '
|| v_basiskorting
|| '-'
|| v_varkorting
|| ' -> '
|| rec.tijdsduur
|| ' '
|| rec.eenheid
|| ' '
|| rec.bk
|| '-'
|| rec.vk);
v_errormsg := 'Aanpassen stdmelding';
UPDATE mld_stdmelding
SET mld_stdmelding_t_uitvoertijd =
mld_t_uitvoertijd (rec.tijdsduur, rec.eenheid),
mld_stdmelding_malus = rec.vk,
mld_stdmelding_malusbasis = rec.bk,
mld_stdmelding_regime = v_stdmelding_regime
WHERE mld_stdmelding_key = rec.mld_stdmelding_key;
FOR rec_sr
IN (SELECT *
FROM mld_stdmsrtruimte
WHERE mld_stdmelding_key = rec.mld_stdmelding_key)
LOOP
fac.imp_writelog (
p_import_key,
'I',
'Verwijderd ('
|| rec.r_codering
|| '-'
|| rec.vakgroep
|| '/'
|| rec.stdmelding
|| ')',
NULL);
v_errormsg := 'Verwijderen stdmsrtruimte';
DELETE mld_stdmsrtruimte
WHERE mld_stdmsrtruimte_key = rec_sr.mld_stdmsrtruimte_key;
END LOOP;
END IF;
ELSE
-- Stdmelding en srtruimte zijn ingevuld. Dus een upsert op het mld_stdmsrtruimte record uitvoeren
BEGIN
v_errormsg := 'Zoeken stdmsrtruimte';
SELECT stdsr.mld_stdmsrtruimte_t_uitvtijd.tijdsduur tijdsduur,
stdsr.mld_stdmsrtruimte_t_uitvtijd.eenheid eenheid,
COALESCE (mld_stdmsrtruimte_malusbasis, 0)
mld_stdmsrtruimte_malusbasis,
COALESCE (mld_stdmsrtruimte_malus, 0)
mld_stdmsrtruimte_malus,
mld_stdmsrtruimte_key
INTO v_tijdsduur,
v_eenheid,
v_basiskorting,
v_varkorting,
v_stdmsrtruimte_key
FROM mld_stdmsrtruimte stdsr
WHERE stdsr.mld_stdmelding_key = rec.mld_stdmelding_key
AND stdsr.alg_srtruimte_key = rec.alg_srtruimte_key;
IF v_tijdsduur <> rec.tijdsduur
OR v_eenheid <> rec.eenheid
OR v_basiskorting <> rec.bk
OR v_varkorting <> rec.vk
THEN
fac.imp_writelog (
p_import_key,
'I',
'Gewijzigd stdmsr1 ('
|| rec.r_codering
|| '-'
|| rec.vakgroep
|| '/'
|| rec.stdmelding
|| ')',
v_tijdsduur
|| ' '
|| v_eenheid
|| ' '
|| v_basiskorting
|| '-'
|| v_varkorting
|| ' -> '
|| rec.tijdsduur
|| ' '
|| rec.eenheid
|| ' '
|| rec.bk
|| '-'
|| rec.vk);
v_errormsg := 'Aanpassen stdmsrtruimte';
UPDATE mld_stdmsrtruimte
SET mld_stdmsrtruimte_t_uitvtijd =
mld_t_uitvoertijd (rec.tijdsduur, rec.eenheid),
mld_stdmsrtruimte_malus = rec.vk,
mld_stdmsrtruimte_malusbasis = rec.bk,
mld_stdmsrtruimte_beginuur1 = v_begin,
mld_stdmsrtruimte_einduur1 = v_eind
WHERE mld_stdmsrtruimte_key = v_stdmsrtruimte_key;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fac.imp_writelog (
p_import_key,
'I',
'Toegevoegd ('
|| rec.r_codering
|| '-'
|| rec.vakgroep
|| '/'
|| rec.stdmelding
|| ')',
rec.tijdsduur
|| ' '
|| rec.eenheid
|| ' '
|| rec.bk
|| '-'
|| rec.vk);
v_errormsg := 'Toevoegen stdmsrtruimte';
INSERT INTO mld_stdmsrtruimte (mld_stdmsrtruimte_t_uitvtijd,
mld_stdmsrtruimte_malus,
mld_stdmsrtruimte_malusbasis,
alg_srtruimte_key,
mld_stdmelding_key,
mld_stdmsrtruimte_beginuur1,
mld_stdmsrtruimte_einduur1)
VALUES (mld_t_uitvoertijd (rec.tijdsduur, rec.eenheid),
rec.vk,
rec.bk,
rec.alg_srtruimte_key,
rec.mld_stdmelding_key,
v_begin,
v_eind);
END;
END IF;
ELSE
-- de standaardmelding is null. De aanpassingen gelden dus voor alle meldingen die niet in de lijst voorkomen.
IF rec.alg_srtruimte_key IS NULL
THEN
-- deze melding is gelijk voor alle ruimtesoorten. We gaan dit dus bij de stdmelding aanpassen.
FOR rec_std
IN (SELECT *
FROM mld_stdmelding std
WHERE std.mld_ins_discipline_key = rec.mld_discipline_key
AND std.mld_stdmelding_verwijder IS NULL
AND std.mld_stdmelding_key NOT IN
(SELECT i.mld_stdmelding_key
FROM pchw_imp_storing_regime i
WHERE COALESCE (i.mld_stdmelding_key, -1) =
std.mld_stdmelding_key))
LOOP
v_errormsg := 'Zoeken stdmelding';
SELECT std.mld_stdmelding_t_uitvoertijd.tijdsduur tijdsduur,
std.mld_stdmelding_t_uitvoertijd.eenheid eenheid,
COALESCE (mld_stdmelding_malusbasis, 0)
mld_stdmelding_malusbasis,
COALESCE (mld_stdmelding_malus, 0) mld_stdmelding_malus,
mld_stdmelding_omschrijving
INTO v_tijdsduur,
v_eenheid,
v_basiskorting,
v_varkorting,
v_stdmelding_omschrijving
FROM mld_stdmelding std
WHERE std.mld_stdmelding_key = rec_std.mld_stdmelding_key;
IF v_tijdsduur <> rec.tijdsduur
OR v_eenheid <> rec.eenheid
OR v_basiskorting <> rec.bk
OR v_varkorting <> rec.vk
THEN
fac.imp_writelog (
p_import_key,
'I',
'Gewijzigd stdm2 ('
|| rec.r_codering
|| '-'
|| rec.vakgroep
|| '/'
|| v_stdmelding_omschrijving
|| ')',
v_tijdsduur
|| ' '
|| v_eenheid
|| ' '
|| v_basiskorting
|| '-'
|| v_varkorting
|| ' -> '
|| rec.tijdsduur
|| ' '
|| rec.eenheid
|| ' '
|| rec.bk
|| '-'
|| rec.vk);
v_errormsg := 'Aanpassen stdmelding';
UPDATE mld_stdmelding
SET mld_stdmelding_t_uitvoertijd =
mld_t_uitvoertijd (rec.tijdsduur, rec.eenheid),
mld_stdmelding_malus = rec.vk,
mld_stdmelding_malusbasis = rec.bk,
mld_stdmelding_regime = v_stdmelding_regime
WHERE mld_stdmelding_key = rec_std.mld_stdmelding_key;
END IF;
END LOOP;
ELSE
-- Ruimte is gevuld maar standaardmelding niet. We gaan nu voor alle bestaande standaardmeldingen en ruimtesoorten mld_stdmsrtruimte records aanmaken of bijwerken.
FOR rec_stdsr
IN (SELECT std.mld_stdmelding_key,
std.mld_stdmelding_omschrijving,
stdmsr.mld_stdmsrtruimte_t_uitvtijd.tijdsduur
tijdsduur,
stdmsr.mld_stdmsrtruimte_t_uitvtijd.eenheid eenheid,
COALESCE (mld_stdmsrtruimte_malusbasis, 0)
mld_stdmsrtruimte_malusbasis,
COALESCE (mld_stdmsrtruimte_malus, 0)
mld_stdmsrtruimte_malus,
mld_stdmsrtruimte_key
FROM mld_stdmelding std,
(SELECT *
FROM mld_stdmsrtruimte
WHERE alg_srtruimte_key = rec.alg_srtruimte_key)
stdmsr
WHERE std.mld_ins_discipline_key = rec.mld_discipline_key
AND std.mld_stdmelding_verwijder IS NULL
AND std.mld_stdmelding_key =
stdmsr.mld_stdmelding_key(+))
LOOP
v_tijdsduur := rec_stdsr.tijdsduur;
v_eenheid := rec_stdsr.eenheid;
v_basiskorting := rec_stdsr.mld_stdmsrtruimte_malusbasis;
v_varkorting := rec_stdsr.mld_stdmsrtruimte_malus;
v_stdmelding_omschrijving :=
rec_stdsr.mld_stdmelding_omschrijving;
v_stdmsrtruimte_key := rec_stdsr.mld_stdmsrtruimte_key;
IF rec_stdsr.mld_stdmsrtruimte_key IS NOT NULL
THEN
-- Update
IF v_tijdsduur <> rec.tijdsduur
OR v_eenheid <> rec.eenheid
OR v_basiskorting <> rec.bk
OR v_varkorting <> rec.vk
THEN
fac.imp_writelog (
p_import_key,
'I',
'Gewijzigd stdmsr2(' || v_stdmsrtruimte_key || '*'
|| rec.r_codering
|| '-'
|| rec.vakgroep
|| '/'
|| v_stdmelding_omschrijving
|| ')',
v_tijdsduur
|| ' '
|| v_eenheid
|| ' '
|| v_basiskorting
|| '-'
|| v_varkorting
|| ' -> '
|| rec.tijdsduur
|| ' '
|| rec.eenheid
|| ' '
|| rec.bk
|| '-'
|| rec.vk);
v_errormsg := 'Aanpassen stdmsrtruimte';
UPDATE mld_stdmsrtruimte
SET mld_stdmsrtruimte_t_uitvtijd =
mld_t_uitvoertijd (rec.tijdsduur, rec.eenheid),
mld_stdmsrtruimte_malus = rec.vk,
mld_stdmsrtruimte_malusbasis = rec.bk,
mld_stdmsrtruimte_beginuur1 = v_begin,
mld_stdmsrtruimte_einduur1 = v_eind
WHERE mld_stdmsrtruimte_key = v_stdmsrtruimte_key;
END IF;
ELSE
-- Insert
fac.imp_writelog (
p_import_key,
'I',
'Toegevoegd ('
|| rec.r_codering
|| '-'
|| rec.vakgroep
|| '/'
|| v_stdmelding_omschrijving
|| ')',
rec.tijdsduur
|| ' '
|| rec.eenheid
|| ' '
|| rec.bk
|| '-'
|| rec.vk);
v_errormsg := 'Toevoegen stdmsrtruimte';
INSERT INTO mld_stdmsrtruimte (mld_stdmsrtruimte_t_uitvtijd,
mld_stdmsrtruimte_malus,
mld_stdmsrtruimte_malusbasis,
alg_srtruimte_key,
mld_stdmelding_key,
mld_stdmsrtruimte_beginuur1,
mld_stdmsrtruimte_einduur1)
VALUES (mld_t_uitvoertijd (rec.tijdsduur, rec.eenheid),
rec.vk,
rec.bk,
rec.alg_srtruimte_key,
rec_stdsr.mld_stdmelding_key,
v_begin,
v_eind);
END IF;
END LOOP;
END IF;
END IF;
END LOOP;
FOR rec IN c_del
LOOP
fac.imp_writelog (
p_import_key,
'I',
'Verwijderd ('
|| rec.alg_srtruimte_code
|| '-'
|| rec.ins_discipline_omschrijving
|| '/'
|| rec.mld_stdmelding_omschrijving
|| ')',
NULL);
DELETE mld_stdmsrtruimte WHERE mld_stdmsrtruimte_key = rec. mld_stdmsrtruimte_key;
END LOOP;
-- Als de hersteltijd per ruimtesoort geldt dan moeten we de vlag afhankelijk ook zetten bij de stdmelding.
UPDATE mld_stdmelding std
SET mld_stdmelding_afhankelijk =
(SELECT DECODE (COUNT ( * ), 0, 0, 1)
FROM mld_stdmsrtruimte sr
WHERE sr.mld_stdmelding_key = std.mld_stdmelding_key);
-- Als er een uitsplitsing is naar ruimtesoort dan moeten we toch de default uitvoertijd bij de stdmelding
-- invullen. Anders wordt de verkeerde eenheid bij respijt getoond.
FOR rec IN c_upd_std
LOOP
UPDATE mld_stdmelding
SET mld_stdmelding_t_uitvoertijd = mld_t_uitvoertijd (rec.tijdsduur, rec.eenheid)
WHERE mld_stdmelding_key = rec.mld_stdmelding_key;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
v_aanduiding := v_errormsg || ' - ' || v_aanduiding;
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',
SUBSTR (v_errormsg, 1, 1000),
v_aanduiding);
END;
/
CREATE OR REPLACE PROCEDURE pchw_import_kenmerken (
p_import_key IN NUMBER)
AS
CURSOR c_cursor
IS
SELECT *
FROM fac_imp_file
WHERE fac_import_key = p_import_key
ORDER BY fac_imp_file_index;
v_newline VARCHAR2(1000);
v_aanduiding VARCHAR2(1000); -- Import line
v_fielddelimitor VARCHAR2(1); -- Field seperator
v_errormsg VARCHAR(200);
v_errorhint VARCHAR(200);
oracle_err_num NUMBER;
oracle_err_mes VARCHAR2(200);
header_found BOOLEAN;
v_error NUMBER(1);
v_dummy VARCHAR2(100);
-- De importvelden
v_stdmelding VARCHAR2(100);
v_vakgroep VARCHAR2(100);
v_vakgroeptype VARCHAR2(100);
v_kenmerk VARCHAR2(100);
v_usrtab_omschrijving VARCHAR2(30);
v_stdmelding_key NUMBER;
v_discipline_key NUMBER;
v_srtdiscipline_key NUMBER;
BEGIN
-- Init
header_found := FALSE;
v_fielddelimitor := ';';
-- Clear my previous imported rows
DELETE FROM pchw_imp_kenmerken;
FOR rec1 IN c_cursor
LOOP
BEGIN
v_newline := REPLACE(rec1.fac_imp_file_line, CHR(160), ' ');
v_aanduiding := SUBSTR (v_newline, 1, 200);
v_error := 0;
IF SUBSTR (v_newline, 1, 3) = '?'
THEN
-- EF BB BF aangetroffen
fac.imp_writelog (p_import_key,
'W',
'Byte Order Mark aangetroffen',
'Bestand heeft onbehandeld UTF-8 formaat.');
v_newline := SUBSTR (v_newline, 4);
END IF;
v_errormsg := 'Inlezen velden';
fac.imp_getfield (v_newline, v_fielddelimitor, v_dummy); -- OS code
fac.imp_getfield (v_newline, v_fielddelimitor, v_vakgroep);
fac.imp_getfield (v_newline, v_fielddelimitor, v_stdmelding);
fac.imp_getfield (v_newline, v_fielddelimitor, v_kenmerk);
-- fac.imp_getfield (v_newline, v_fielddelimitor, v_dummy);
fac.imp_getfield (v_newline, v_fielddelimitor, v_vakgroeptype);
-- Skip until a valid header is found
v_errormsg := 'Controleer de header';
IF UPPER (rec1.fac_imp_file_line) LIKE
'OS_CODE;VAKGROEP;STANDAARDMELDING;ONDERDEEL;VAKGROEPTYPE%'
THEN
-- Sla de kopregel over.
header_found := TRUE;
ELSIF (header_found AND v_vakgroep IS NOT NULL)
THEN
v_errormsg := 'Controleer vakgroeptype';
BEGIN
v_vakgroeptype := TRIM(v_vakgroeptype);
SELECT ins_srtdiscipline_key
INTO v_srtdiscipline_key
FROM ins_srtdiscipline sd
WHERE sd.ins_srtdiscipline_module = 'MLD'
AND sd.ins_srtdiscipline_verwijder IS NULL
AND UPPER (sd.ins_srtdiscipline_omschrijving) =
UPPER (v_vakgroeptype);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fac.imp_writelog (
p_import_key,
'E',
'Vakgroeptype niet gevonden: ' || v_vakgroeptype,
v_aanduiding);
v_srtdiscipline_key := NULL;
v_error := 1;
END;
v_errormsg := 'Controleer vakgroep';
BEGIN
v_vakgroep := TRIM(v_vakgroep);
SELECT ins_discipline_key
INTO v_discipline_key
FROM ins_tab_discipline d
WHERE d.ins_discipline_module = 'MLD'
AND d.ins_discipline_verwijder IS NULL
AND d.ins_srtdiscipline_key = v_srtdiscipline_key
AND UPPER (d.ins_discipline_omschrijving) =
UPPER (v_vakgroep);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fac.imp_writelog (
p_import_key,
'E',
'Vakgroep niet gevonden: ' || v_vakgroep,
v_aanduiding);
v_discipline_key := NULL;
v_error := 1;
END;
v_usrtab_omschrijving := SUBSTR(v_vakgroeptype,1,2) || '/' || SUBSTR(v_vakgroep,1,6) || '/' || SUBSTR(v_stdmelding,1,6);
v_errormsg := 'Controleer stdmelding';
IF v_stdmelding IS NOT NULL
THEN
v_stdmelding := SUBSTR(TRIM(v_stdmelding),1,60);
BEGIN
SELECT mld_stdmelding_key
INTO v_stdmelding_key
FROM mld_stdmelding std
WHERE std.mld_stdmelding_verwijder IS NULL
AND std.mld_ins_discipline_key = v_discipline_key
AND UPPER (std.mld_stdmelding_omschrijving) =
UPPER (v_stdmelding);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fac.imp_writelog (
p_import_key,
'E',
'Standaard melding niet gevonden: ' || v_stdmelding,
v_aanduiding);
v_stdmelding_key := NULL;
v_error := 1;
END;
ELSE
v_stdmelding_key := NULL;
END IF;
IF v_error = 0
THEN
INSERT INTO pchw_imp_kenmerken (mld_stdmelding_key,
fac_usrtab_omschrijving,
fac_usrdata_omschrijving)
VALUES (v_stdmelding_key,
v_usrtab_omschrijving,
SUBSTR(v_kenmerk,1,60));
END IF;
COMMIT;
END IF;
END;
END loop;
IF NOT header_found
THEN
fac.imp_writelog (
p_import_key,
'E',
'Geen geldige header aangetroffen',
'Bestand is geen geldig kenmerken importbestand.');
END IF;
EXCEPTION
WHEN OTHERS
THEN
v_aanduiding := v_errormsg || ' - ' || v_aanduiding;
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', SUBSTR (v_errormsg, 1, 1000), v_aanduiding);
-- maak de importtabel leeg om te voorkomen dat er halve bestanden ingelezen worden.
DELETE FROM pchw_imp_kenmerken;
END;
/
CREATE OR REPLACE PROCEDURE pchw_update_kenmerken (p_import_key IN NUMBER)
AS
CURSOR c
IS
SELECT * FROM pchw_imp_kenmerken;
CURSOR c_hint
IS
SELECT std.mld_stdmelding_key, kd.fac_usrtab_key
FROM mld_srtkenmerk sk,
mld_kenmerk k,
fac_kenmerkdomein kd,
mld_stdmelding std
WHERE mld_srtkenmerk_omschrijving LIKE 'Type %'
AND k.mld_srtkenmerk_key = sk.mld_srtkenmerk_key
AND sk.fac_kenmerkdomein_key = kd.fac_kenmerkdomein_key
AND k.mld_stdmelding_key = std.mld_stdmelding_key;
v_aanduiding VARCHAR2 (1000); -- Import line
v_errormsg VARCHAR (200);
v_errorhint VARCHAR (200);
oracle_err_num NUMBER;
oracle_err_mes VARCHAR2 (200);
v_usrtab_key fac_usrtab.fac_usrtab_key%TYPE;
v_usrdata_key fac_usrdata.fac_usrdata_key%TYPE;
v_kenmerkdomein_key fac_kenmerkdomein.fac_kenmerkdomein_key%TYPE;
v_srtkenmerk_key mld_srtkenmerk.mld_srtkenmerk_key%TYPE;
v_kenmerk_key mld_kenmerk.mld_kenmerk_key%TYPE;
v_srtkenmerk_omschrijving mld_srtkenmerk.mld_srtkenmerk_omschrijving%TYPE;
v_hint VARCHAR2 (4000);
BEGIN
FOR rec IN c
LOOP
v_aanduiding :=
rec.mld_stdmelding_key
|| '-'
|| rec.fac_usrtab_omschrijving
|| '/'
|| rec.fac_usrdata_omschrijving;
IF rec.mld_stdmelding_key IS NOT NULL
THEN
v_errorhint := 'Toevoegen of bepalen fac_usrtab';
BEGIN
SELECT fac_usrtab_key
INTO v_usrtab_key
FROM fac_usrtab
WHERE fac_usrtab_naam = rec.fac_usrtab_omschrijving;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
INSERT INTO fac_usrtab (fac_usrtab_naam, fac_usrtab_omschrijving)
VALUES (rec.fac_usrtab_omschrijving, rec.fac_usrtab_omschrijving)
RETURNING fac_usrtab_key
INTO v_usrtab_key;
END;
v_errorhint := 'Toevoegen of bepalen fac_kenmerkdomein';
BEGIN
SELECT fac_kenmerkdomein_key
INTO v_kenmerkdomein_key
FROM fac_kenmerkdomein
WHERE fac_kenmerkdomein_verwijder IS NULL
AND fac_kenmerkdomein_module = 'MLD'
AND fac_usrtab_key = v_usrtab_key;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
INSERT INTO fac_kenmerkdomein (fac_kenmerkdomein_omschrijving,
fac_kenmerkdomein_module,
fac_kenmerkdomein_kolomnaam,
fac_kenmerkdomein_kolomtxt,
fac_kenmerkdomein_objectnaam,
fac_usrtab_key)
VALUES (rec.fac_usrtab_omschrijving,
'MLD',
'FAC_USRDATA_KEY',
'FAC_USRDATA_OMSCHR',
'FAC_USRDATA',
v_usrtab_key)
RETURNING fac_kenmerkdomein_key
INTO v_kenmerkdomein_key;
END;
v_errorhint := 'Toevoegen of bepalen srtkenmerk_omschrijving';
SELECT SUBSTR('Type ' || ins_discipline_omschrijving || '/' || mld_stdmelding_omschrijving, 1, 50)
INTO v_srtkenmerk_omschrijving
FROM mld_discipline d, mld_stdmelding std
WHERE d.ins_discipline_key = std.mld_ins_discipline_key
AND std.mld_stdmelding_key = rec.mld_stdmelding_key;
v_errorhint := 'Toevoegen of bepalen mld_srtkenmerk';
BEGIN
SELECT mld_srtkenmerk_key
INTO v_srtkenmerk_key
FROM mld_srtkenmerk
WHERE mld_srtkenmerk_verwijder IS NULL
AND fac_kenmerkdomein_key = v_kenmerkdomein_key
AND mld_srtkenmerk_omschrijving = v_srtkenmerk_omschrijving;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
INSERT INTO mld_srtkenmerk (mld_srtkenmerk_omschrijving,
mld_srtkenmerk_kenmerktype,
fac_kenmerkdomein_key)
VALUES (v_srtkenmerk_omschrijving, 'R', v_kenmerkdomein_key)
RETURNING mld_srtkenmerk_key
INTO v_srtkenmerk_key;
END;
v_errorhint := 'Toevoegen of bepalen mld_kenmerk';
BEGIN
SELECT mld_kenmerk_key
INTO v_kenmerk_key
FROM mld_kenmerk
WHERE mld_kenmerk_verwijder IS NULL AND mld_srtkenmerk_key = v_srtkenmerk_key;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
INSERT INTO mld_kenmerk (mld_srtkenmerk_key,
mld_stdmelding_key,
mld_kenmerk_niveau,
mld_kenmerk_volgnummer)
VALUES (v_srtkenmerk_key,
rec.mld_stdmelding_key,
'S',
400)
RETURNING mld_kenmerk_key
INTO v_kenmerk_key;
END;
v_errorhint := 'Toevoegen of bepalen fac_usrdata';
BEGIN
SELECT fac_usrdata_key
INTO v_usrdata_key
FROM fac_usrdata
WHERE fac_usrdata_code = SUBSTR (rec.fac_usrdata_omschrijving, 1, 40)
AND fac_usrtab_key = v_usrtab_key;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
INSERT INTO fac_usrdata (fac_usrtab_key, fac_usrdata_code, fac_usrdata_omschr)
VALUES (v_usrtab_key, SUBSTR (rec.fac_usrdata_omschrijving, 1, 40), rec.fac_usrdata_omschrijving)
RETURNING fac_usrdata_key
INTO v_usrdata_key;
END;
END IF;
END LOOP;
FOR rec IN c_hint
LOOP
BEGIN
SELECT listagg (u.fac_usrdata_omschr, ' ') WITHIN GROUP (ORDER BY u.fac_usrdata_omschr)
AS hint
INTO v_hint
FROM fac_usrdata u
WHERE fac_usrtab_key = rec.fac_usrtab_key;
UPDATE mld_stdmelding
SET mld_stdmelding_hint = v_hint
WHERE mld_stdmelding_key = rec.mld_stdmelding_key;
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
v_aanduiding := v_errormsg || ' - ' || v_aanduiding;
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',
SUBSTR (v_errormsg, 1, 1000),
v_aanduiding || ' - ' || v_errorhint);
END;
/
CREATE OR REPLACE PROCEDURE pchw_import_mld (p_import_key IN NUMBER)
AS
BEGIN
-- Verwijder nbsp karakters.
UPDATE fac_imp_file
SET fac_imp_file_line = REPLACE (fac_imp_file_line, CHR (160), ' ')
WHERE fac_import_key = p_import_key;
fac_import_mld (p_import_key);
UPDATE fac_imp_mld SET mld_stdmelding_groep = TRIM(mld_stdmelding_groep);
END;
/
CREATE OR REPLACE PROCEDURE pchw_update_mld (p_import_key IN NUMBER)
AS
CURSOR c
IS
SELECT i.*,
sd.ins_srtdiscipline_key,
d.ins_discipline_key,
std.mld_stdmelding_key
FROM fac_imp_file i,
ins_tab_discipline d,
ins_srtdiscipline sd,
mld_stdmelding std
WHERE sd.ins_srtdiscipline_key = d.ins_srtdiscipline_key
AND d.ins_discipline_key = std.mld_ins_discipline_key
AND i.fac_imp_file_line LIKE
ins_srtdiscipline_omschrijving
|| ';%;'
|| ins_discipline_omschrijving
|| ';'
|| mld_stdmelding_omschrijving
|| ';%'
AND mld_stdmelding_verwijder IS NULL
AND ins_discipline_verwijder IS NULL
AND sd.ins_srtdiscipline_verwijder IS NULL
AND fac_import_key = p_import_key;
v_aanduiding VARCHAR2 (400);
v_errormsg VARCHAR (200);
v_errorhint VARCHAR (200);
oracle_err_num NUMBER;
oracle_err_mes VARCHAR2 (200);
v_regime VARCHAR2 (40);
BEGIN
fac_update_mld (p_import_key);
FOR rec IN c
LOOP
BEGIN
fac.imp_getfield_nr (rec.fac_imp_file_line, ';', 20, v_regime);
DBMS_OUTPUT.put_line (v_regime);
IF v_regime LIKE '%24%'
THEN
-- 24/7
UPDATE mld_stdmelding SET mld_stdmelding_regime = 3 WHERE mld_stdmelding_key = rec.mld_stdmelding_key;
ELSE
-- Openingstijden
UPDATE mld_stdmelding SET mld_stdmelding_regime = 2 WHERE mld_stdmelding_key = rec.mld_stdmelding_key;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Fout bij bepalen regime uit melding: ' || rec.fac_imp_file_line);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
v_aanduiding := v_errormsg || ' - ' || v_aanduiding;
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',
SUBSTR (v_errormsg, 1, 1000),
v_aanduiding);
END;
/
CREATE OR REPLACE PROCEDURE pchw_select_freeze_cat (
p_applname IN VARCHAR2,
p_applrun IN VARCHAR2)
AS
CURSOR c
IS
SELECT rra.res_rsv_artikel_key, rra.res_rsv_artikel_prijs
FROM res_rsv_artikel rra, res_artikel ra
WHERE rra.res_artikel_key = ra.res_artikel_key
AND SUBSTR (ra.res_artikel_groep, 1, 3) <> 'SVD'
AND res_status_bo_key = 5
AND res_rsv_artikel_levering < TRUNC (SYSDATE, 'MONTH')
AND res_rsv_artikel_verwijder IS NULL;
v_exportdate DATE;
v_lastexport DATE;
v_cat_totaal NUMBER(11,2);
BEGIN
v_exportdate := SYSDATE;
v_cat_totaal := 0;
-- Als ik deze maand al een freeze heb uitgevoerd dan mag ik dat niet nog een keer doen.
SELECT MAX(res_rsv_artikel_verwerkt)
INTO v_lastexport
FROM res_rsv_artikel rra, res_artikel ra
WHERE rra.res_artikel_key = ra.res_artikel_key
AND SUBSTR (ra.res_artikel_groep, 1, 3) <> 'SVD';
IF TRUNC (v_lastexport, 'MONTH') <> TRUNC(v_exportdate, 'MONTH') OR v_lastexport IS NULL
THEN
FOR rec IN c
LOOP
BEGIN
UPDATE res_rsv_artikel
SET res_status_bo_key = 6
WHERE res_rsv_artikel_key = rec.res_rsv_artikel_key;
v_cat_totaal := v_cat_totaal + rec.res_rsv_artikel_prijs;
END;
END LOOP;
INSERT INTO fac_rapport (fac_rapport_node, fac_rapport_regel, fac_rapport_volgnr)
VALUES ('FREEZE_CAT', 'Catering totaal: ' || v_cat_totaal, 1);
ELSE
INSERT INTO fac_rapport (fac_rapport_node, fac_rapport_regel, fac_rapport_volgnr)
VALUES ('FREEZE_CAT', 'Catering is al bevroren deze maand op ' || TO_CHAR(v_lastexport, 'dd-mm-yyyy hh24:mi:ss'), 1);
END IF;
COMMIT;
END pchw_select_freeze_cat;
/
CREATE OR REPLACE PROCEDURE pchw_export_freeze_cat (
p_applname IN VARCHAR2,
p_applrun IN VARCHAR2,
p_filedir IN VARCHAR2,
p_filename IN VARCHAR2)
AS
BEGIN
DELETE fac_rapport WHERE fac_rapport_node = 'FREEZE_CAT';
END;
/
CREATE OR REPLACE VIEW pchw_v_export_freeze_cat
( result,
result_order)
AS
SELECT fac_rapport_regel, fac_rapport_volgnr
FROM fac_rapport
WHERE fac_rapport_node = 'FREEZE_CAT';
CREATE OR REPLACE VIEW pchw_v_cat_factuur
(
res_rsv_ruimte_nr,
res_rsv_ruimte_omschrijving,
res_rsv_ruimte_van,
res_rsv_ruimte_bezoekers,
res_rsv_ruimte_extern,
prs_perslid_naam_full,
prs_afdeling_omschrijving,
fcl,
res_discipline_omschrijving,
res_rsv_artikel_aantal,
res_rsv_artikel_omschrijving,
res_rsv_artikel_prijs,
res_artikel_groep,
res_rsv_artikel_verwerkt,
periode
)
AS
SELECT rrr.res_reservering_key || '/' || rrr.res_rsv_ruimte_volgnr,
res_rsv_ruimte_omschrijving,
res_rsv_ruimte_van,
res_rsv_ruimte_bezoekers,
(SELECT fac.safe_to_number (res_kenmerkreservering_waarde)
FROM res_kenmerkwaarde kw
WHERE kw.res_kenmerk_key = 22
AND kw.res_rsv_ruimte_key = rrr.res_rsv_ruimte_key)
extern,
prs_perslid_naam_full,
prs_afdeling_omschrijving,
fcl.res_kenmerkreservering_waarde fcl,
d.ins_discipline_omschrijving,
rra.res_rsv_artikel_aantal,
ra.res_artikel_omschrijving,
res_rsv_artikel_prijs,
DECODE (SUBSTR (ra.res_artikel_groep, 1, 3),
'SVD', 'SVD',
'Banqueting')
groep,
rra.res_rsv_artikel_verwerkt,
TO_CHAR (
ADD_MONTHS (TRUNC (rra.res_rsv_artikel_verwerkt, 'month'), -1),
'YYYY-month')
periode
FROM res_rsv_artikel rra,
prs_perslid p,
prs_v_perslid_fullnames pf,
prs_afdeling a,
(SELECT res_rsv_ruimte_key, res_kenmerkreservering_waarde
FROM res_kenmerkwaarde kw, res_kenmerk k
WHERE kw.res_kenmerk_key = k.res_kenmerk_key
AND k.res_srtkenmerk_key = 23) fcl, -- FCL
res_artikel ra,
res_rsv_ruimte rrr,
res_discipline d
WHERE rra.res_artikel_key = ra.res_artikel_key
AND ra.res_discipline_key = d.ins_discipline_key
AND rra.res_status_bo_key = 6
AND rrr.res_rsv_ruimte_contact_key = p.prs_perslid_key
AND p.prs_perslid_key = pf.prs_perslid_key
AND p.prs_afdeling_key = a.prs_afdeling_key
AND rrr.res_rsv_ruimte_key = fcl.res_rsv_ruimte_key(+)
AND rra.res_rsv_ruimte_key = rrr.res_rsv_ruimte_key;
-- Procedure om mailberichten van meldingen buiten kantooruren toch in een melding op te kunnen slaan.
CREATE OR REPLACE PROCEDURE pchw_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_melding_key mld_melding.mld_melding_key%TYPE;
v_stdmelding_key mld_stdmelding.mld_stdmelding_key%TYPE;
errormsg fac_result.fac_result_waarde%TYPE;
v_uitvoertijd mld_stdmelding.mld_stdmelding_t_uitvoertijd%TYPE;
v_ruimte_key alg_ruimte.alg_ruimte_key%TYPE;
v_locatie_key alg_locatie.alg_locatie_key%TYPE;
v_count NUMBER;
v_error NUMBER;
v_errormsg VARCHAR2 (100);
v_from VARCHAR2 (4000);
v_body VARCHAR2 (4000);
BEGIN
errormsg := '(0x143)';
v_error := 0;
v_errormsg := '';
v_locatie_key := 41; -- LGS
v_ruimte_key := 8392; -- L.002 Entreehal incl.wachtruimte
-- Verwijder de Bounce Address Tag Validation
IF pfrom LIKE 'prvs=%'
THEN
v_from := SUBSTR (pfrom, INSTR (pfrom, '=', -1) + 1);
ELSE
v_from := pfrom;
END IF;
v_body := pbody;
-- afzender van een gbs bericht is altijd 4881 (Key van gebruiker Calamiteiten servicedesk)
v_sender := 4881;
CASE
WHEN UPPER (pto) LIKE 'DEGROENESCHAKEL@%'
THEN
errormsg := '(0x145)';
-- filter nu de essentiele velden uit de mailbody
-- vervang cr door lf
v_stdmelding_key := fac.safe_to_number (fac.getsetting ('defaultstdmelding'));
v_body := REPLACE (v_body, CHR (13), CHR (10));
-- verwijder dubbele lf's
v_body := REPLACE (v_body, CHR (10) || CHR (10), CHR (10));
errormsg := '(0x1451)';
-- vind de uitvoertijd bij de stdmelding
SELECT mld_stdmelding_t_uitvoertijd
INTO v_uitvoertijd
FROM mld_stdmelding
WHERE mld_stdmelding_key = v_stdmelding_key;
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,
prs_perslid_key,
prs_perslid_key_voor,
prs_kostenplaats_key,
mld_melding_spoed,
mld_alg_locatie_key,
mld_alg_onroerendgoed_keys)
VALUES ('MLD',
4, -- email
SYSDATE,
psubject,
v_body,
NULL,
v_uitvoertijd,
v_stdmelding_key,
v_sender,
v_sender,
NULL,
3,
v_locatie_key,
v_ruimte_key)
RETURNING mld_melding_key
INTO v_melding_key;
errormsg := '(0x165)';
mld.setmeldingstatus (v_melding_key, 2, v_sender);
END CASE;
EXCEPTION
WHEN OTHERS
THEN
INSERT INTO fac_result (fac_result_sessionid, fac_result_naam, fac_result_waarde)
VALUES (psessionid,
'maillog',
'Database fout - Neem contact op met uw systeembeheerder ' || errormsg);
fac.writelog ('PROCESSEMAIL',
'E',
'Mail kon niet ingelezen worden afzender:' || pfrom || '[' || errormsg || ']',
'OTHERS (error ' || SQLCODE || '/' || SUBSTR (SQLERRM, 1, 100) || ')');
END;
/
CREATE OR REPLACE VIEW pchw_v_prs_kostenplaats
(
prs_kostenplaats_omschrijving,
prs_kostenplaats_key,
prs_kostenplaats_eind
)
AS
SELECT prs_kostenplaats_key,
prs_kostenplaats_nr || ' ' || prs_kostenplaats_omschrijving,
prs_kostenplaats_eind
FROM prs_kostenplaats
WHERE prs_kostenplaats_verwijder IS NULL;
CREATE OR REPLACE VIEW pchw_v_prs_srtperslid
(
prs_srtperslid_key,
prs_srtperslid_omschrijving
)
AS
SELECT prs_srtperslid_key, prs_srtperslid_omschrijving
FROM prs_srtperslid
WHERE prs_srtperslid_verwijder IS NULL;
CREATE OR REPLACE VIEW pchw_v_prs_afdeling
(
prs_afdeling_key,
prs_afdeling_omschrijving
)
AS
SELECT prs_afdeling_key,
prs_afdeling_naam || ' ' || prs_afdeling_omschrijving
FROM prs_afdeling
WHERE prs_afdeling_verwijder IS NULL;
CREATE OR REPLACE VIEW pchw_v_rap_indices (volgorde, svd, index_waarde) as
SELECT 0, 'MON', pchx_mon.get_index_value (SYSDATE,'MON')
FROM DUAL
UNION ALL
SELECT DISTINCT
FAC.SAFE_TO_NUMBER(SUBSTR (COALESCE (res_artikel_groep, 'SVD1'), 4)),
COALESCE (res_artikel_groep, 'SVD1'),
pchx_mon.get_index_value (SYSDATE,
COALESCE (res_artikel_groep, 'SVD1'))
FROM res_artikel;
-- Dashboard views
-- Dashboard rapport voor nieuwe meldingen.
CREATE OR REPLACE VIEW pchw_v_dashboard_new
AS
SELECT sd.ins_srtdiscipline_prefix || m.mld_melding_key mld_melding_key,
pf.prs_perslid_naam_full,
mld_melding_datum,
std.mld_stdmelding_omschrijving,
d.ins_discipline_omschrijving
FROM mld_melding m,
prs_perslid p,
prs_v_perslid_fullnames pf,
mld_stdmelding std,
ins_tab_discipline d,
ins_srtdiscipline sd
WHERE m.prs_perslid_key = p.prs_perslid_key
AND p.prs_perslid_key = pf.prs_perslid_key
AND m.mld_stdmelding_key = std.mld_stdmelding_key
AND std.mld_ins_discipline_key = d.ins_discipline_key
AND d.ins_srtdiscipline_key = sd.ins_srtdiscipline_key
AND pchx_mon.klant_melding (m.mld_melding_key) = 1
AND mld_melding_status IN (2, 3);
CREATE OR REPLACE VIEW pchw_melding_optijd
AS
SELECT gereed,
DECODE (SIGN (mld_melding_einddatum_std - gereed), -1, 0, 100)
optijd
FROM (SELECT m.*,
fac.gettrackingdate ('MLDAFM', m.mld_melding_key) gereed
FROM mld_melding m)
WHERE gereed IS NOT NULL
AND ADD_MONTHS(TRUNC(gereed, 'MONTH'),6) > SYSDATE;
--Klant specifieke view voor het tonen van het totaal aantal korting per onderwerp over 1 maand
CREATE OR REPLACE VIEW pchw_v_totaal_korting_ond
(
onderwerp,
totaal_index,
maand
)
AS
SELECT onderwerp, SUM (totaal_index), TRUNC (gereed, 'MONTH')
FROM (SELECT ins_discipline_omschrijving,
mld_stdmeldinggroep_oms onderwerp,
totaal_index,
gereed
FROM (SELECT melding_key,
COALESCE (gereed, SYSDATE) gereed,
totaal_index,
stdmelding_key,
indult
FROM pchx_v_monitoring
UNION ALL
SELECT melding_key,
gereed,
totaal_index,
stdmelding_key,
indult
FROM pchx_monitoring) m,
mld_discipline d,
mld_stdmelding std,
mld_stdmeldinggroep gr
WHERE m.stdmelding_key = std.mld_stdmelding_key
AND std.mld_ins_discipline_key = d.ins_discipline_key
AND std.mld_stdmeldinggroep_key =
gr.mld_stdmeldinggroep_key(+)
AND indult = 'Nee'
AND totaal_index IS NOT NULL
AND totaal_index != 0
AND ADD_MONTHS (TRUNC (gereed, 'MONTH'), 6) > SYSDATE)
GROUP BY onderwerp, TRUNC (gereed, 'MONTH');
CREATE OR REPLACE VIEW pchw_v_overschrijding_doorbel
(
MAAND,
EXPORT_DATUM,
BEDRIJF,
GROEP,
VARIABELEKORTING,
MLD_MELDING_ONDERWERP,
MLD_MELDING_OMSCHRIJVING,
MLD_MELDING_KEY,
MLD_MELDING_OPMERKING,
TOTAAL,
TOTAAL_INDEX
)
BEQUEATH DEFINER
AS
SELECT TO_CHAR (TRUNC (export_datum, 'MONTH') - 1, 'yyyy-month')
maand,
export_datum,
bedrijf,
groep,
variabelekorting,
MLD_MELDING_ONDERWERP,
TO_CHAR(SUBSTR(m.mld_melding_omschrijving,1,4000)) MLD_MELDING_OMSCHRIJVING,
m.MLD_MELDING_KEY,
MLD_MELDING_OPMERKING,
( totaal
/ ( (totaal / variabelekorting)
+ DECODE (variabelekorting, 50, 20, 250, 10, 0)))
* COUNT (*)
totaal,
( totaal_index
/ ( (totaal / variabelekorting)
+ DECODE (variabelekorting, 50, 20, 250, 10, 0)))
* COUNT (*)
totaal_index
FROM pchx_monitoring mo,
mld_melding m,
(SELECT km.mld_melding_key, fac_usrdata_omschr bedrijf
FROM mld_kenmerkmelding km, fac_usrdata ud
WHERE mld_kenmerk_key = 121
AND fac.safe_to_number (mld_kenmerkmelding_waarde) =
ud.fac_usrdata_key) b
WHERE kinderen IS NOT NULL
AND ',' || mo.kinderen || ',' LIKE
'%,' || m.mld_melding_key || ',%'
AND COALESCE (m.mld_melding_parentkey, m.mld_melding_key) =
b.mld_melding_key(+) -- bij meldingen die onder een andere melding vallen moeten we bij de parent kijken
AND totaal > 0 -- Soms zijn er wel kind meldingen maar hebben we geen korting te pakken.
GROUP BY export_datum,
groep,
variabelekorting,
MLD_MELDING_ONDERWERP,
TO_CHAR(SUBSTR(m.mld_melding_omschrijving,1,4000)),
m.MLD_MELDING_KEY,
MLD_MELDING_OPMERKING,
totaal_index,
totaal,
bedrijf;
-- Rapportages ten behoeve van de verslaglegging naar de financiers.
CREATE OR REPLACE VIEW pchw_v_melding_p_maand
AS
SELECT maand,
(SELECT COUNT (*)
FROM mld_melding
WHERE mld_melding_datum BETWEEN ADD_MONTHS (maand, -11)
AND ADD_MONTHS (maand, 1))
pmaandjaar,
(SELECT COUNT (*)
FROM mld_melding
WHERE mld_melding_datum BETWEEN maand AND ADD_MONTHS (maand, 1))
pmaand
FROM ( SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), (LEVEL * -1)) maand
FROM DUAL
CONNECT BY LEVEL <= 12);
CREATE OR REPLACE VIEW pchw_v_onderdeel_p_maand
AS
SELECT maand,
mld_stdmeldinggroep_oms,
(SELECT COUNT (*)
FROM mld_melding m, mld_stdmelding std
WHERE m.mld_stdmelding_key = std.mld_stdmelding_key
AND std.mld_stdmeldinggroep_key = gr.mld_stdmeldinggroep_key
AND mld_melding_datum BETWEEN maand AND ADD_MONTHS (maand, 1))
pmaand
FROM ( SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), (LEVEL * -1)) maand
FROM DUAL
CONNECT BY LEVEL <= 12) m,
mld_stdmeldinggroep gr
WHERE mld_stdmeldinggroep_oms IS NOT NULL;
CREATE OR REPLACE VIEW pchw_v_malus_bedrijf
(
maand,
bedrijf_malus,
totaal
)
AS
SELECT m.maand,
COALESCE (mon.bedrijf_malus, 'PCH'),
SUM (mon.totaal) totaal
FROM (SELECT ADD_MONTHS (TRUNC (export_datum, 'MONTH'), -1) maand,
mo.bedrijf_malus,
mo.totaal
FROM pchx_monitoring mo
WHERE kinderen IS NULL -- geen overschrijdingskorting
AND totaal IS NOT NULL
UNION ALL
SELECT ADD_MONTHS (TRUNC (export_datum, 'MONTH'), -1) maand,
bedrijf
bedrijf_malus,
totaal
FROM pchw_v_overschrijding_doorbel) mon,
( SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), (LEVEL * -1)) maand
FROM DUAL
CONNECT BY LEVEL <= 12) m
WHERE m.maand = mon.maand(+)
GROUP BY m.maand, mon.bedrijf_malus;
CREATE OR REPLACE VIEW pchw_v_meldingen_top10_mnd
AS
SELECT 1 - aantal hide_f_sort,
mld_stdmelding_omschrijving,
ins_discipline_omschrijving,
aantal
FROM ( SELECT mld_stdmelding_omschrijving,
d.ins_discipline_omschrijving,
COUNT ( * ) aantal
FROM mld_melding m, mld_stdmelding std, ins_tab_discipline d
WHERE m.mld_stdmelding_key = std.mld_stdmelding_key
AND std.mld_ins_discipline_key = d.ins_discipline_key
AND d.ins_srtdiscipline_key = 101
AND mld_melding_datum BETWEEN ADD_MONTHS (
TRUNC (SYSDATE, 'MONTH'),
-1)
AND TRUNC (SYSDATE, 'MONTH')
GROUP BY mld_stdmelding_omschrijving,
d.ins_discipline_omschrijving
ORDER BY COUNT ( * ) DESC)
WHERE ROWNUM <= 10;
CREATE OR REPLACE VIEW pchw_v_meldingen_top10_jaar
AS
SELECT 1 - aantal hide_f_sort,
mld_stdmelding_omschrijving,
ins_discipline_omschrijving,
aantal
FROM ( SELECT mld_stdmelding_omschrijving,
d.ins_discipline_omschrijving,
COUNT (*) aantal
FROM mld_melding m, mld_stdmelding std, ins_tab_discipline d
WHERE m.mld_stdmelding_key = std.mld_stdmelding_key
AND std.mld_ins_discipline_key = d.ins_discipline_key
AND d.ins_srtdiscipline_key = 101
AND mld_melding_datum BETWEEN ADD_MONTHS (
TRUNC (SYSDATE, 'MONTH'),
-12)
AND TRUNC (SYSDATE, 'MONTH')
GROUP BY mld_stdmelding_omschrijving,
d.ins_discipline_omschrijving
ORDER BY COUNT (*) DESC)
WHERE ROWNUM <= 10;
CREATE OR REPLACE VIEW pchw_v_meldingen_top10_hlfjr
AS
SELECT 1 - aantal hide_f_sort,
mld_stdmelding_omschrijving,
ins_discipline_omschrijving,
aantal
FROM ( SELECT mld_stdmelding_omschrijving,
d.ins_discipline_omschrijving,
COUNT ( * ) aantal
FROM mld_melding m, mld_stdmelding std, ins_tab_discipline d
WHERE m.mld_stdmelding_key = std.mld_stdmelding_key
AND std.mld_ins_discipline_key = d.ins_discipline_key
AND d.ins_srtdiscipline_key = 101
AND mld_melding_datum BETWEEN TRUNC (
ADD_MONTHS (
SYSDATE - 182,
(1
+ 6
* ROUND (
ROUND (
EXTRACT (
MONTH FROM (SYSDATE
- 182))
/ 12,
1)
- 0.1,
0))
- EXTRACT (
MONTH FROM (SYSDATE
- 182))),
'Month')
AND TRUNC (
ADD_MONTHS (
SYSDATE,
(1
+ 6
* ROUND (
ROUND (
EXTRACT (
MONTH FROM (SYSDATE))
/ 12,
1)
- 0.1,
0))
- EXTRACT (
MONTH FROM (SYSDATE))),
'Month')
GROUP BY mld_stdmelding_omschrijving,
d.ins_discipline_omschrijving
ORDER BY COUNT ( * ) DESC)
WHERE ROWNUM <= 10;
-- Reminder naar de gastheer van de reservering tenzij
-- de gastheer een functie burgermeester of wethouder heeft (124/130)
-- of tenzij de afdeling van gastheer Directie betreft (359/262) dan naar naar contactpersoon
CREATE OR REPLACE VIEW pchw_v_noti_resreminder
(
code,
sender,
receiver,
text,
key,
xkey
)
AS
SELECT 'RESINF',
NULL,
DECODE (
prs_afdeling_key,
124,
rsvr.res_rsv_ruimte_contact_key,
130,
rsvr.res_rsv_ruimte_contact_key,
DECODE (prs_srtperslid_key,
359, rsvr.res_rsv_ruimte_contact_key,
262, rsvr.res_rsv_ruimte_contact_key,
rsvr.res_rsv_ruimte_host_key))
receiver,
'Reservering '
|| res.res_reservering_key
|| ': U heeft op '
|| TO_CHAR (rsvr.res_rsv_ruimte_van, 'DD-MM')
|| ' van '
|| TO_CHAR (rsvr.res_rsv_ruimte_van, 'HH24:MI')
|| ' tot '
|| TO_CHAR (rsvr.res_rsv_ruimte_tot, 'HH24:MI')
|| ' ruimte '
|| r.res_ruimte_nr
|| ' gereserveerd.',
res.res_reservering_key,
rsvr.res_rsv_ruimte_key
FROM res_v_aanwezigreservering res,
res_v_aanwezigrsv_ruimte rsvr,
res_ruimte_opstelling opst,
res_disc_params rdp,
res_ruimte r,
prs_perslid p
WHERE r.res_discipline_key = rdp.res_ins_discipline_key
AND rdp.res_disc_params_noti_dagen IS NOT NULL
AND res.res_reservering_key = rsvr.res_reservering_key
AND opst.res_ruimte_opstel_key = rsvr.res_ruimte_opstel_key
AND opst.res_ruimte_key = r.res_ruimte_key
AND rsvr.res_rsv_ruimte_host_key = p.prs_perslid_key
AND rsvr.res_status_fo_key <= 5
AND rsvr.res_rsv_ruimte_van BETWEEN fac.datumtijdplusuitvoertijd (
SYSDATE,
res_disc_params_noti_dagen,
'DAGEN')
AND fac.datumtijdplusuitvoertijd (
SYSDATE,
res_disc_params_noti_dagen
+ 1,
'DAGEN')
AND fac.getweekdaynum (SYSDATE) NOT IN (1, 7)
AND rsvr.res_rsv_ruimte_van >= SYSDATE
UNION ALL
SELECT 'RESINF',
NULL,
DECODE (
prs_afdeling_key,
124,
res_rsv_ruimte_contact_key,
130,
res_rsv_ruimte_contact_key,
DECODE (prs_srtperslid_key,
359, res_rsv_ruimte_contact_key,
262, res_rsv_ruimte_contact_key,
res_rsv_ruimte_host_key))
receiver,
'Reservering '
|| res_reservering_key
|| ': U heeft op '
|| TO_CHAR (MIN (van), 'dd-mm')
|| DECODE (
MIN (van),
MAX (tot),
' om ' || TO_CHAR (MIN (van), 'hh24:mi'),
' van '
|| TO_CHAR (MIN (van), 'hh24:mi')
|| ' tot '
|| TO_CHAR (MAX (tot), 'hh24:mi'))
|| ' in ruimte ('
|| (SELECT alg_gebouw_code
|| '-'
|| alg_verdieping_code
|| '-'
|| alg_ruimte_nr
FROM alg_v_onroerendgoed_gegevens arg
WHERE arg.alg_ruimte_key = ruimte_key)
|| ') '
|| COUNT (res_reservering_key)
|| ' voorziening(en) gereserveerd.',
res_reservering_key,
res_rsv_ruimte_key
FROM (SELECT rsvr.res_rsv_ruimte_host_key,
rsvr.res_rsv_ruimte_contact_key,
p.prs_srtperslid_key,
p.prs_afdeling_key,
res.res_reservering_key,
rsvd.res_rsv_deel_van van,
rsvd.res_rsv_deel_tot tot,
rsvr.res_rsv_ruimte_key,
rsvr.alg_ruimte_key ruimte_key
FROM res_v_aanwezigreservering res,
res_v_aanwezigrsv_ruimte rsvr,
res_disc_params rdp,
res_rsv_deel rsvd,
res_deel rd,
prs_perslid p
WHERE rsvd.res_rsv_ruimte_key = rsvr.res_rsv_ruimte_key
AND rd.res_deel_key = rsvd.res_deel_key
AND rd.res_discipline_key = rdp.res_ins_discipline_key
AND rdp.res_disc_params_noti_dagen IS NOT NULL
AND res.res_reservering_key = rsvr.res_reservering_key
AND rsvr.res_ruimte_opstel_key IS NULL
AND rsvr.res_rsv_ruimte_host_key = p.prs_perslid_key
AND rsvr.res_status_fo_key <= 5
AND rsvd.res_rsv_deel_verwijder IS NULL
AND rsvd.res_rsv_deel_van BETWEEN fac.datumtijdplusuitvoertijd (
SYSDATE,
res_disc_params_noti_dagen,
'DAGEN')
AND fac.datumtijdplusuitvoertijd (
SYSDATE,
res_disc_params_noti_dagen
+ 1,
'DAGEN')
AND fac.getweekdaynum (SYSDATE) NOT IN (1, 7)
AND rsvd.res_rsv_deel_van >= SYSDATE
UNION ALL
SELECT rsvr.res_rsv_ruimte_host_key,
rsvr.res_rsv_ruimte_contact_key,
p.prs_srtperslid_key,
p.prs_afdeling_key,
res.res_reservering_key,
rsva.res_rsv_artikel_levering van,
rsva.res_rsv_artikel_levering tot,
rsvr.res_rsv_ruimte_key,
rsvr.alg_ruimte_key ruimte_key
FROM res_v_aanwezigreservering res,
res_v_aanwezigrsv_ruimte rsvr,
res_disc_params rdp,
res_rsv_artikel rsva,
res_artikel ra,
prs_perslid p
WHERE rsva.res_rsv_ruimte_key = rsvr.res_rsv_ruimte_key
AND ra.res_discipline_key = rdp.res_ins_discipline_key
AND rsva.res_artikel_key = ra.res_artikel_key
AND rdp.res_disc_params_noti_dagen IS NOT NULL
AND res.res_reservering_key = rsvr.res_reservering_key
AND rsvr.res_ruimte_opstel_key IS NULL
AND rsvr.res_rsv_ruimte_host_key = p.prs_perslid_key
AND rsvr.res_status_fo_key <= 5
AND rsva.res_rsv_artikel_verwijder IS NULL
AND rsva.res_rsv_artikel_levering BETWEEN fac.datumtijdplusuitvoertijd (
SYSDATE,
res_disc_params_noti_dagen,
'DAGEN')
AND fac.datumtijdplusuitvoertijd (
SYSDATE,
res_disc_params_noti_dagen
+ 1,
'DAGEN')
AND fac.getweekdaynum (SYSDATE) NOT IN (1, 7)
AND rsva.res_rsv_artikel_levering >= SYSDATE)
GROUP BY res_rsv_ruimte_host_key,
res_rsv_ruimte_contact_key,
prs_srtperslid_key,
prs_afdeling_key,
res_reservering_key,
res_rsv_ruimte_key,
ruimte_key;
CREATE OR REPLACE VIEW pchw_v_catering_gegevens
(
res_rsv_ruimte_nummer,
res_rsv_ruimt_key,
alg_locatie_omschrijving,
alg_ruimte_aanduiding,
prs_afdeling_naam,
prs_afdeling_omschrijving,
prs_perslid_naam_full,
res_status_bo_omschrijving,
res_rsv_ruimte_datum,
res_rsv_ruimte_van,
res_rsv_ruimte_tot,
res_rsv_ruimte_bezoekers,
res_rsv_ruimte_opmerking,
res_artikel_btw,
res_artikel_omschrijving,
res_rsv_artikel_aantal,
res_artikel_prijs,
res_artikel_prijs_totaal,
res_rsv_artikel_key,
res_artikel_groep,
res_artikel_soortcat,
prs_kostenplaats_nr,
fcl
)
AS
SELECT res_reservering_key || '/' || res_rsv_ruimte_volgnr,
rrr.res_rsv_ruimte_key,
rg.alg_locatie_omschrijving,
rg.alg_ruimte_nr || ' - ' || rg.alg_ruimte_omschrijving,
a.prs_afdeling_naam,
a.prs_afdeling_omschrijving,
pf.prs_perslid_naam_full,
b.res_status_bo_omschrijving,
res_rsv_ruimte_van
datum,
res_rsv_ruimte_van
van,
res_rsv_ruimte_tot
tot,
rrr.res_rsv_ruimte_bezoekers,
rrr.res_rsv_ruimte_opmerking,
ra.res_artikel_btw,
ra.res_artikel_omschrijving,
rra.res_rsv_artikel_aantal,
ra.res_artikel_prijs,
res.getartikelprijs (rra.res_rsv_artikel_key),
rra.res_rsv_artikel_key,
ra.res_artikel_groep,
DECODE (SUBSTR (ra.res_artikel_groep, 1, 3),
'SVD', 'SVD',
'Banqueting')
soortcat,
k.prs_kostenplaats_nr,
(SELECT res_kenmerkreservering_waarde
FROM res_kenmerkwaarde kw, res_kenmerk k
WHERE kw.res_kenmerk_key = k.res_kenmerk_key
AND k.res_srtkenmerk_key = 23
AND kw.res_rsv_ruimte_key = rrr.res_rsv_ruimte_key)
fcl -- FCL
FROM res_rsv_ruimte rrr,
( SELECT res_rsv_ruimte_key, MAX (alg_ruimte_key) alg_ruimte_key
FROM (SELECT DISTINCT res_rsv_ruimte_key, alg_ruimte_key
FROM RES_V_RSV_RUIMTE_2_ALG_RUIMTE
WHERE res_alg_ruimte_verwijder IS NULL
UNION
SELECT res_rsv_ruimte_key, alg_ruimte_key
FROM res_rsv_ruimte
WHERE alg_ruimte_key IS NOT NULL
AND res_rsv_ruimte_verwijder IS NULL)
GROUP BY res_rsv_ruimte_key) r2a,
alg_v_ruimte_gegevens rg,
res_rsv_artikel rra,
res_artikel ra,
res_status_bo b,
prs_kostenplaats k,
prs_afdeling a,
prs_perslid p,
prs_v_perslid_fullnames_all pf
WHERE rrr.res_rsv_ruimte_key = rra.res_rsv_ruimte_key
AND rrr.res_rsv_ruimte_key = r2a.res_rsv_ruimte_key
AND r2a.alg_ruimte_key = rg.alg_ruimte_key
AND rra.res_artikel_key = ra.res_artikel_key
AND rra.res_status_bo_key = b.res_status_bo_key
AND rrr.prs_kostenplaats_key = k.prs_kostenplaats_key(+)
AND rra.res_rsv_artikel_verwijder IS NULL
AND rrr.res_rsv_ruimte_host_key = p.prs_perslid_key
AND p.prs_afdeling_key = a.prs_afdeling_key
AND p.prs_perslid_key = pf.prs_perslid_key;
CREATE OR REPLACE PROCEDURE pchw_import_prs_westland (p_import_key IN NUMBER)
AS
oracle_err_num NUMBER;
oracle_err_mes VARCHAR2 (200);
v_errormsg VARCHAR2 (400);
v_errorhint VARCHAR2 (400);
v_aantal_in_fclt NUMBER;
BEGIN
v_errorhint := 'Generieke update';
-- de sequence array staat beschreven in PRS_PAC.SRC bij de prs.import_perslid procedure
-- Functie staat even op 30 (ipv 21), omdat die niet altijd gevuld blijkt. Functie vullen we dus even met LastName
prs.import_perslid (
p_import_key,
'0;0;0;0;0;0;5;2;3;1;'
|| '0;0;0;0;11;12;13;10;0;0;'
|| '2;4;0;0;0;0;6;8;9;7;'
|| '0;0;0;0;0;0;0;0;0;0;'
|| '0;0;0;0;0;0',
'FirstName;Last Name;Middle Name;Login;Department Code;Department Name;Job Name;Location name;Cost Center Number;Email Adress;Gender;Work Phone;Mobile');
-- Functie bepalen
UPDATE fac_imp_perslid
SET prs_srtperslid_omschrijving = COALESCE (SUBSTR(prs_kenmerk4, 0, 60), 'Onbekend');
-- Loginnaam afleiden van emailadres
UPDATE fac_imp_perslid
SET prs_perslid_oslogin =
(SUBSTR (prs_perslid_email,
0,
INSTR (prs_perslid_email, '@') - 1))
WHERE UPPER (prs_perslid_email) LIKE '%GEMEENTEWESTLAND.NL%';
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,
v_errorhint);
END pchw_import_prs_westland;
/
CREATE OR REPLACE PROCEDURE pchw_update_prs_westland (p_import_key IN NUMBER)
IS
-- Alle personen verwijderen die niet meer in het import import bestand voorkomen
-- Alleen personen verwijderen met emailadres met @GemeenteWestland.nl. Omdat anders bijv. DGS gebruikers verwijderd zouden worden.
-- Personen in de juiste autorisatiegroep zetten.
-- Match bij PCHW is Email.
-- Geen acties tenzij het aantal records in de importtabel meer dan 800 medewerkers betreft.
-- Enkele functionele accounts (srtperslid 57055) niet verwijderen (PCHW#85273)
CURSOR c_del
IS
SELECT p.prs_perslid_key, p.prs_perslid_nr, pf.prs_perslid_naam_full
FROM prs_perslid p, prs_v_perslid_fullnames pf
WHERE UPPER (p.prs_perslid_email) LIKE '%GEMEENTEWESTLAND.NL%'
AND UPPER (p.prs_perslid_email) NOT IN
(SELECT UPPER (prs_perslid_email) FROM fac_imp_perslid)
AND p.prs_srtperslid_key <> 57055 -- Functie 'Functioneel account (niet verwijderen)' uitsluiten ivm PCHW#85273
AND pf.prs_perslid_key = p.prs_perslid_key;
v_count NUMBER;
oracle_err_num NUMBER;
oracle_err_mes VARCHAR2 (200);
v_errormsg VARCHAR2 (400);
v_errorhint VARCHAR2 (400);
BEGIN
v_errorhint := 'Niet genoeg personen in bestand, import afgebroken';
SELECT COUNT ( * ) INTO v_count FROM fac_imp_perslid;
IF v_count >= 800
THEN
v_errorhint := 'Verwerken persoonsgegevens';
-- Geldig importbestand wat betreft aantal personen
-- Eerst de regels die niet van toepassing zijn verwijderen. Dit zijn bijv. raadsleden/vrijwilligers die nooit hoeven in te loggen in Facilitor.
DELETE FROM fac_imp_perslid
WHERE prs_perslid_email IS NULL;
COMMIT;
-- generic update
-- 'EMAIL' betekent dat op basis van Email wordt gematched.
-- 'NULL' betekent altijd geen werkplekken verwijderen
prs.update_perslid (p_import_key, 'EMAIL', NULL);
v_errorhint := 'Verwijderen persoonsgegevens';
-- Verwijder personen die niet meer in de import voorkomen.
FOR rec IN c_del
LOOP
BEGIN
prs.delete_perslid (p_import_key, rec.prs_perslid_key);
END;
END LOOP;
ELSE
fac.imp_writelog (p_import_key,
'E',
v_errormsg,
v_errorhint);
COMMIT;
END IF;
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,
v_errorhint);
END pchw_update_prs_westland;
/
-- Rapport om SVD's goed te keuren door contractteam
CREATE OR REPLACE VIEW pchw_v_goedkeuring_svd
(
prs_perslid_naam_full,
prs_perslid_key,
mld_melding_key,
mld_opdr_key,
mld_opdr_bedrijfopdr_volgnr,
mld_melding_onderwerp,
id
)
AS
SELECT pf.prs_perslid_naam_full,
m.prs_perslid_key,
o.mld_melding_key,
mld_opdr_key,
o.mld_opdr_bedrijfopdr_volgnr,
m.mld_melding_onderwerp,
sd.ins_srtdiscipline_prefix
|| m.mld_melding_key
|| '/'
|| o.mld_opdr_bedrijfopdr_volgnr
id
FROM mld_opdr o,
mld_melding m,
prs_v_perslid_fullnames_all pf,
mld_stdmelding std,
mld_discipline d,
ins_srtdiscipline sd
WHERE o.mld_typeopdr_key = 161 -- SVD
AND o.mld_statusopdr_key = 9 -- afgerond
AND m.mld_melding_key = o.mld_melding_key
AND m.prs_perslid_key = pf.prs_perslid_key
AND m.mld_stdmelding_key = std.mld_stdmelding_key
AND std.mld_ins_discipline_key = d.ins_discipline_key
AND d.ins_srtdiscipline_key = sd.ins_srtdiscipline_key
AND NOT EXISTS
(SELECT mld_kenmerkopdr_key
FROM mld_kenmerkopdr ko
WHERE ko.mld_kenmerk_key = 441 -- goedgekeurd door aanvrager
AND ko.mld_opdr_key = o.mld_opdr_key);
CREATE OR REPLACE VIEW PCHW_V_CATERING_CANCELDAGEN
(
res_rsv_ruimte_key,
reserveringnummer,
omschrijving,
datum_reservering,
van,
tot,
datum_aanmaak,
datum_verwijder,
fo_status,
artikel_omschrijving,
artikel_eenheid,
artikel_prijs,
artikel_aantal,
dagen
)
AS
SELECT rrr.res_rsv_ruimte_key,
rrr.res_reservering_key || '/' || rrr.res_rsv_ruimte_volgnr,
rrr.res_rsv_ruimte_omschrijving,
rrr.res_rsv_ruimte_van,
rrr.res_rsv_ruimte_van,
rrr.res_rsv_ruimte_tot,
rrr.res_rsv_ruimte_aanmaak,
rrr.res_rsv_ruimte_verwijder,
rrr.res_status_fo_key,
ra.res_artikel_omschrijving,
ra.res_artikel_eenheid,
COALESCE (ra.res_artikel_prijs, rra.res_rsv_artikel_prijs)
AS prijs,
rra.res_rsv_artikel_aantal,
TRUNC (rrr.res_rsv_ruimte_van)
- TRUNC (rrr.res_rsv_ruimte_verwijder)
AS dagen
FROM res_rsv_artikel rra, res_rsv_ruimte rrr, res_artikel ra
WHERE rrr.res_rsv_ruimte_key = rra.res_rsv_ruimte_key
AND rra.res_rsv_artikel_verwijder IS NOT NULL
AND res_rsv_ruimte_externnr IS NOT NULL
AND rra.res_artikel_key = ra.res_artikel_key
ORDER BY rrr.res_rsv_ruimte_verwijder 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
Reference in New Issue View Git Blame Copy Permalink