--
-- $Id$
--
-- Script containing customer specific sql statements for the FACILITOR database
DEFINE thisfile = 'NMMS.SQL'
DEFINE dbuser = '^NMMS'
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 ------
-- exact verkoop
CREATE OR REPLACE VIEW nmms_v_export_exact_verkoop (RESULT, result_order) AS SELECT RESULT, result_order FROM aaxx_v_export_exact_verkoop;
CREATE OR REPLACE PROCEDURE nmms_select_exact_verkoop (
p_applname IN VARCHAR2,
p_applrun IN VARCHAR2
)
AS
BEGIN
aaxx_select_exact_verkoop(p_applname, p_applrun, to_char(sysdate, 'yyyy'), 'CORE');
END;
/
CREATE OR REPLACE PROCEDURE nmms_export_exact_verkoop (
p_applname IN VARCHAR2,
p_applrun IN VARCHAR2,
p_filedir IN VARCHAR2,
p_filename IN VARCHAR2
)
AS
BEGIN
aaxx_export_exact_verkoop(p_applname, p_applrun, p_filedir, p_filename, to_char(sysdate, 'yyyy'), 'CORE');
END;
/
-- exact verkoop over het vorige jaar
CREATE OR REPLACE VIEW nmms_v_export_exact_verkoop_vj (RESULT, result_order) AS SELECT RESULT, result_order FROM aaxx_v_export_exact_verkoop;
CREATE OR REPLACE PROCEDURE nmms_select_exact_verkoop_vj (
p_applname IN VARCHAR2,
p_applrun IN VARCHAR2
)
AS
BEGIN
aaxx_select_exact_verkoop(p_applname, p_applrun, to_char(to_number(to_char(sysdate, 'yyyy'))-1), 'CORE');
END;
/
CREATE OR REPLACE PROCEDURE nmms_export_exact_verkoop_vj (
p_applname IN VARCHAR2,
p_applrun IN VARCHAR2,
p_filedir IN VARCHAR2,
p_filename IN VARCHAR2
)
AS
BEGIN
aaxx_export_exact_verkoop(p_applname, p_applrun, p_filedir, p_filename, to_char(to_number(to_char(sysdate, 'yyyy'))-1), 'CORE');
END;
/
-- exact projecturen (XML)
CREATE OR REPLACE VIEW nmms_v_export_exact_uren (result, result_order)
AS SELECT result, result_order FROM aaxx_v_export_exact_uren_xml;
CREATE OR REPLACE PROCEDURE nmms_select_exact_uren (
p_applname IN VARCHAR2,
p_applrun IN VARCHAR2
)
AS
BEGIN
aaxx_select_exact_uren_xml(p_applname, p_applrun, '');
END;
/
-- verwerk exact projecturen (XML)
CREATE OR REPLACE PROCEDURE nmms_export_verwerk_exact_uren (
p_applname IN VARCHAR2,
p_applrun IN VARCHAR2,
p_filedir IN VARCHAR2,
p_filename IN VARCHAR2
)
AS
BEGIN
aaxx_export_verwerk_exact_uren(p_applname, p_applrun, p_filedir, p_filename, '');
END;
/
CREATE OR REPLACE PACKAGE nmms
AS
FUNCTION get_first_melding (p_melding_key IN NUMBER, p_index IN NUMBER)
RETURN NUMBER;
FUNCTION get_regime (p_stdm_key IN NUMBER,
p_alg_key IN NUMBER, -- Altijd gebouw of hoger, locatie niet ondersteund
p_ins_key IN NUMBER)
RETURN NUMBER;
PROCEDURE daily_verwerk_opdr;
END;
/
CREATE OR REPLACE PACKAGE BODY nmms
AS
FUNCTION get_first_melding (p_melding_key IN NUMBER,
p_index IN NUMBER)
RETURN NUMBER
AS
CURSOR c
IS
SELECT m2.mld_melding_datum van, m2.mld_melding_key
FROM mld_melding m1,
mld_stdmelding std1,
mld_melding m2,
mld_stdmelding std2
WHERE m1.mld_melding_key = p_melding_key
AND m1.mld_stdmelding_key = std1.mld_stdmelding_key
AND m2.mld_melding_parentkey IS NULL
AND NOT EXISTS (SELECT 1
FROM nmms_monitoring
WHERE melding_key = m2.mld_melding_key)
AND m2.mld_stdmelding_key = std2.mld_stdmelding_key
AND std2.mld_stdmelding_malus IS NOT NULL -- alleen meldingen waarvoor een malus geldt
AND m1.mld_alg_onroerendgoed_keys =
m2.mld_alg_onroerendgoed_keys -- op dezelfde ruimte
AND std1.prs_dienst_key =
std2.prs_dienst_key -- voor dezelfde dienst
AND std1.mld_stdmelding_regime =
std2.mld_stdmelding_regime -- met hetzelfde regime
AND m1.mld_melding_t_uitvoertijd.eenheid =
m2.mld_melding_t_uitvoertijd.eenheid -- in dezelfde eenheid
AND m1.mld_melding_key <> m2.mld_melding_key
AND m1.mld_melding_datum BETWEEN m2.mld_melding_datum
AND fac.gettrackingdate ('MLDAFM', m2.mld_melding_key)
ORDER BY m2.mld_melding_datum;
v_melding_key NUMBER;
l_melding_key NUMBER;
v_datum_begin DATE;
l_datum_begin DATE;
v_loop_index NUMBER;
BEGIN
v_melding_key := p_melding_key;
SELECT mld_melding_datum
INTO v_datum_begin
FROM mld_melding
WHERE mld_melding_key = p_melding_key;
v_loop_index := 1;
FOR rec IN c
LOOP
-- door de sortering op de cursor hoeven we alleen het eerste record te gebruiken.
IF p_index < 10 AND v_loop_index = 1
THEN
BEGIN
l_melding_key :=
nmms.get_first_melding (rec.mld_melding_key, p_index + 1);
END;
SELECT mld_melding_datum
INTO l_datum_begin
FROM mld_melding
WHERE mld_melding_key = l_melding_key;
IF l_datum_begin < v_datum_begin
THEN
v_datum_begin := l_datum_begin;
v_melding_key := l_melding_key;
END IF;
v_loop_index := v_loop_index + 1;
END IF;
END LOOP;
RETURN v_melding_key;
END;
FUNCTION get_regime (p_stdm_key IN NUMBER,
p_alg_key IN NUMBER, -- Altijd gebouw of hoger, locatie niet ondersteund
p_ins_key IN NUMBER)
RETURN NUMBER
AS
v_mld_stdmelding_regime NUMBER;
v_norm_uitvoertijd mld_stdmelding.mld_stdmelding_t_uitvoertijd%TYPE := MLD_T_UITVOERTIJD(NULL, NULL);
v_beginuur NUMBER;
v_einduur NUMBER;
v_werkdagen NUMBER;
v_accepttijd mld_stdmelding.mld_stdmelding_t_uitvoertijd%TYPE := MLD_T_UITVOERTIJD(NULL, NULL);
BEGIN
mld.getslaparams (p_stdm_key,
3, -- prio
p_alg_key,
NULL, -- ins key
v_mld_stdmelding_regime,
v_norm_uitvoertijd,
v_accepttijd,
v_beginuur,
v_einduur,
v_werkdagen);
-- kantoortijden of openingstijden waarbij de tijd niet gevuld is.
IF v_mld_stdmelding_regime = 1 OR (v_mld_stdmelding_regime = 2 AND (v_beginuur = -1 OR v_einduur = -1))
THEN
RETURN fac.safe_to_number (fac.getsetting ('fac_t_endofworkday'))
- fac.safe_to_number (fac.getsetting ('fac_t_startofworkday'));
END IF;
IF v_mld_stdmelding_regime = 2 -- openingstijden
THEN
RETURN v_einduur - v_beginuur;
END IF;
IF v_mld_stdmelding_regime = 3 -- 24/7
THEN
RETURN 24;
END IF;
RETURN NULL;
END;
-- onderstaande procedure wordt of iedere nacht vanuit de daily_task aangeroepen of in de exact_verwerk routine. It's up to Jonathan.
PROCEDURE daily_verwerk_opdr
AS
CURSOR c
IS
SELECT *
FROM mld_opdr o, prs_kenmerklink kl
WHERE o.mld_uitvoerende_keys = kl.prs_link_key
AND prs_kenmerk_key = 1000 -- MA/CP
AND prs_kostenplaats_key <> 1 -- AAFM
AND prs_kenmerklink_waarde = 161 -- MA
AND mld_statusopdr_key = 9;
BEGIN
FOR rec IN c
LOOP
BEGIN
-- zet de status naar verwerkt (user is 4 -> facilitor)
mld.setopdrachtstatus (rec.mld_opdr_key, 7, 4);
END;
END LOOP;
COMMIT;
END;
END;
/
CREATE OR REPLACE PROCEDURE nmms_export_daily_task (
p_applname IN VARCHAR2,
p_applrun IN VARCHAR2,
p_filedir IN VARCHAR2,
p_filename IN VARCHAR2)
AS
BEGIN
nmms.daily_verwerk_opdr;
aaxx_daily_task (p_applname, p_applrun);
END;
/
-- Selecteer oa. de MIN(begintijd) en MAX(eindtijd) van overlappende meldingen:
-- op dezelfde ruimte voor dezelfde dienst
-- met hetzelfde regime in dezelfde eenheid
-- Hierin ook opgenomen of men een fictieve gereeddatum heeft meegegeven (ticket 62463)
CREATE OR REPLACE VIEW nmms_v_startmelding_geg
(
m_start,
mld_alg_onroerendgoed_keys,
mld_melding_status,
mld_melding_afgerond,
aantal,
overlappend,
t_van,
gereed,
fictief,
mld_melding_spoed,
mld_stdmelding_key,
prs_kostenplaats_key,
max_t_actual,
min_t_sla,
max_t_respijt,
min_tht_einddatum_start,
mld_melding_indult,
mld_melding_einddatum_std,
mld_melding_einddatum,
mld_melding_omschrijving,
mld_melding_opmerking,
nmm_melding,
prs_bedrijf_key
)
AS
SELECT m.m_start,
x.mld_alg_onroerendgoed_keys,
-- als één van de onderliggende meldingen "open", dan moet ook M_START status Uitgegeven krijgen
DECODE (m.open, 0, x.mld_melding_status, 4) mld_melding_status, --- 4 = Geaccepteerd???
x.mld_melding_afgerond, -- mld_melding_afgerond van alleen M_START
m.aantal,
m.overlappend,
m.t_van, -- min_melding_datum
DECODE(m.gereed, TO_DATE('20991231', 'YYYYMMDD'), TO_DATE(NULL), m.gereed), -- max_actual_einddatum
m.fictief,
x.mld_melding_spoed, -- mld_melding_spoed van alleen M_START
x.mld_stdmelding_key, -- mld_stdmelding_key van alleen M_START
x.prs_kostenplaats_key, -- prs_kostenplaats_key van alleen M_START
mld.getactualuitvoer ( -- verschil tussen t_van en gereed/SYSDATE is de maximale - actuele - doorlooptijd!
m.t_van,
DECODE (m.gereed, TO_DATE('20991231', 'YYYYMMDD'), SYSDATE, m.gereed),
x.mld_stdmelding_key,
x.mld_melding_spoed,
x.mld_alg_onroerendgoed_keys,
NULL)
max_t_actual,
MLD_T_UITVOERTIJD (m.min_uitvoertijd, m.min_uitvoertijd_eenheid) min_t_sla,
MLD_T_UITVOERTIJD (COALESCE(m.max_respijt,0), m.max_respijt_eenheid) max_t_respijt, -- altijd dezelfde eenheid als uitvoertijd???
MLD.geteinddatum (
m.m_start,
MLD_T_UITVOERTIJD (m.min_uitvoertijd + m.max_respijt, m.min_uitvoertijd_eenheid),
m.t_van)
min_tht_einddatum_start, -- meest nabije tht-einddatum van overlappende meldingen (t_van + MIN SLA + MAX respijt)
m.mld_melding_indult, -- max_melding_indult
m.mld_melding_einddatum_std, -- min_melding_einddatum_std of min_tht_einddatum_start???
m.mld_melding_einddatum, -- max_melding_einddatum
x.mld_melding_omschrijving, -- mld_melding_oms. van alleen M_START
x.mld_melding_opmerking, -- mld_melding_opm. van alleen M_START
m.nmm_melding, -- melding gemeld door medewerker NMM
m.prs_bedrijf_key -- bedrijf key van de melder
FROM ( SELECT m.m_start,
SUM (m.OPEN) OPEN,
COUNT (m.mld_melding_key) aantal,
LISTAGG (m.mld_melding_key, ',')
WITHIN GROUP (ORDER BY m.mld_melding_key)
AS overlappend,
MIN (m.mld_melding_datum) t_van, -- min_melding_datum
MAX (m.gereed) gereed, -- max_actual_einddatum
--MIN (mld_melding_uitvoertijd) mld_melding_uitvoertijd, -- hieronder anders opgelost!
MIN (m.mld_melding_t_uitvoertijd.tijdsduur) min_uitvoertijd,
MIN (m.mld_melding_t_uitvoertijd.eenheid) min_uitvoertijd_eenheid,
--SUM (mld_melding_respijt) mld_melding_respijt, -- hieronder anders opgelost!
MAX (m.mld_melding_t_respijt.tijdsduur) max_respijt,
MAX (m.mld_melding_t_respijt.eenheid) max_respijt_eenheid, -- altijd dezelfde eenheid als uitvoertijd???
MAX (m.mld_melding_indult) mld_melding_indult,
MIN (m.mld_melding_einddatum_std) mld_melding_einddatum_std, -- min_melding_einddatum_std of min_thtsla_einddatum_start???
MAX (m.mld_melding_einddatum) mld_melding_einddatum,
MAX (m.nmm_melding) nmm_melding,
MAX (m.prs_bedrijf_key) prs_bedrijf_key,
MAX (m.fictief) fictief
FROM (SELECT nmms.get_first_melding (m.mld_melding_key, 1) m_start,
m.mld_melding_key,
DECODE (m.mld_melding_status, 4, 1, 0) open, -- open = Geaccepteerd???
m.mld_melding_datum,
COALESCE(fac.gettrackingdate ('MLDAFM', m.mld_melding_key), f.gereed_fictief, TO_DATE('20991231', 'YYYYMMDD')) gereed,
m.mld_melding_t_uitvoertijd,
m.mld_melding_t_respijt,
m.mld_melding_indult,
m.mld_melding_einddatum_std,
m.mld_melding_einddatum,
a.prs_bedrijf_key,
DECODE(a.prs_bedrijf_key, 1542, 1, 0) nmm_melding,
CASE WHEN f.mld_melding_key IS NULL THEN 0 ELSE 1 END fictief
FROM mld_melding m, mld_stdmelding std, prs_perslid p, prs_afdeling a,
(SELECT v.mld_melding_key, vv.gereed_fictief, SYSDATE
FROM (SELECT m.mld_melding_key
FROM mld_melding m, mld_kenmerkmelding km
WHERE m.mld_melding_key = km.mld_melding_key
AND km.mld_kenmerk_key = 321 -- Vinkbox aan
AND m.mld_melding_status NOT IN (1, 5, 6) -- Lopende melding
) v,
(SELECT m.mld_melding_key,
TO_DATE (
(SELECT mld_kenmerkmelding_waarde
FROM mld_kenmerkmelding km
WHERE km.mld_melding_key = m.mld_melding_key AND km.mld_kenmerk_key = 322) -- Datum
|| ' ' ||
(SELECT mld_kenmerkmelding_waarde
FROM mld_kenmerkmelding km
WHERE km.mld_melding_key = m.mld_melding_key AND km.mld_kenmerk_key = 323), -- Tijdstip
'DD-MM-YYYY HH24:MI') gereed_fictief
FROM mld_melding m, mld_kenmerkmelding km
WHERE m.mld_melding_key = km.mld_melding_key
AND km.mld_kenmerk_key = 321 -- Vinkbox aan
AND m.mld_melding_status NOT IN (1, 5, 6) -- Lopende melding
) vv
WHERE v.mld_melding_key = vv.mld_melding_key AND vv.gereed_fictief > SYSDATE
) f
WHERE m.mld_melding_parentkey IS NULL
AND m.prs_perslid_key = p.prs_perslid_key
AND p.prs_afdeling_key = a.prs_afdeling_key
-- meldingen die al geexporteerd zijn (en in de tabel nmms_monitoring staan) komen niet meer in deze view
AND NOT EXISTS
(SELECT 1
FROM nmms_monitoring
WHERE melding_key = m.mld_melding_key)
AND m.mld_stdmelding_key = std.mld_stdmelding_key
AND std.mld_stdmelding_malus IS NOT NULL -- alleen meldingen waarvoor een malus geldt
AND m.mld_melding_key = f.mld_melding_key (+)
) m
GROUP BY m.m_start) m,
mld_melding x -- gedefinieerde set gegevens bij M_START als referentie
WHERE m.m_start = x.mld_melding_key;
--
-- Basis monitoringsview voor operationele zaken. Meldingen die opgenomen zijn in de tabel nmms_monitoring komen niet meer voor in deze view.
--
CREATE OR REPLACE VIEW nmms_v_monitoring
(
melding_key,
afw,
q_vervolg,
indult,
respijt,
kostenplaats,
locatie,
gebouw,
verdieping,
ruimte,
object,
thema,
omschrijving,
begin,
gereed,
fictief,
thtijdstip,
verwerkt,
eenheid,
tht,
wht,
regime,
groep,
typekorting,
basiskorting,
variabelekorting,
uren,
kb,
prestatiekorting,
factor,
kp,
status,
afgerond,
dienst,
stdmelding_key,
discipline_key,
overlappend,
wegingsfactor,
melding_omschrijving,
melding_opmerking,
nmm_melding,
bedrijf_naam
)
AS
SELECT m_start, --mld_melding_key,
DECODE (antedateren, 0, '', 'T') -- de begintijd of eindtijd van de melding is aangepast
|| DECODE (g.respijt.tijdsduur, 0, '', 'R') -- er is een respijt ingevuld
|| DECODE (mld_melding_indult, 1, 'I', '') -- indult is aangevinkt dus melding doet niet mee
|| DECODE (aantal_beschikb, 0, '', 'B'), -- binnen de looptijd van de melding is er ook een beschikbaarheidsmelding in dezelfde ruimte
q_vervolg,
DECODE (COALESCE (mld_melding_indult, 0), 0, 'Nee', 'Ja'),
DECODE (g.respijt.tijdsduur,
0, NULL,
TO_CHAR (ROUND (g.respijt.tijdsduur, 2)))
respijt,
prs_kostenplaats_nr,
alg_locatie_code,
alg_gebouw_upper,
alg_verdieping_volgnr,
ruimte,
object,
thema,
mld_stdmelding_omschrijving,
t_van,
gereed,
fictief,
DECODE (kortingtype,
'Q', (SELECT mld_melding_einddatum FROM mld_melding mtht WHERE mtht.mld_melding_key = m_start),
thtijdstip) thtijdstip,
(SELECT MAX(fac_tracking_datum)
FROM fac_tracking
WHERE fac_tracking_refkey = m_start --mld_melding_key
AND fac_srtnotificatie_key = 36)
verwerkt,
eenheid,
DECODE (kortingtype,
'Q', to_number(NULL),
ROUND (g.tht.tijdsduur, 2))
tht,
DECODE (kortingtype,
'Q', to_number(NULL),
ROUND (g.actual.tijdsduur, 2))
wht,
DECODE (kortingtype, 'Q', TO_NUMBER (NULL), regime) regime,
mld_stdmeldinggroep_naam groep,
'K' || LOWER (kortingtype) || DECODE (q_vervolg, 1, '/q')
typekorting,
DECODE (kortingtype,
'B', mld_stdmelding_malus,
'S', mld_stdmelding_malus,
'Q', mld_stdmelding_malus)
basiskorting,
DECODE (kortingtype, 'B', 50, 'S', 1000) variabelekorting,
DECODE (
telaat,
1,
DECODE (
kortingtype,
'B',
ROUND (g.uitloop.tijdsduur, 2),
'S',
ROUND (g.uitloop.tijdsduur, 2)))
uren, -- uitloop in beschikbaarheid is altijd 50 euro per uur.
DECODE (
telaat,
1,
DECODE (
kortingtype,
'Q',
mld_stdmelding_malus,
'B',
mld_stdmelding_malus
+ (CEIL(DECODE (g.uitloop.eenheid, 'D', g.uitloop.tijdsduur * 24, g.uitloop.tijdsduur)) * 50), -- Alleen als dagen x24!
'S',
mld_stdmelding_malus
+ (CEIL (g.uitloop.tijdsduur / g.thtsla.tijdsduur) * 1000),
NULL))
kb,
DECODE (kortingtype, 'P', mld_stdmelding_malus) prestatiekorting,
DECODE (
telaat,
1,
DECODE (kortingtype, 'P', (g.uitloop.tijdsduur / g.thtsla.tijdsduur) + 1))
factor,
DECODE (
telaat,
1,
DECODE (kortingtype, 'P', CEIL (g.uitloop.tijdsduur / g.thtsla.tijdsduur) * mld_stdmelding_malus))
kp,
(SELECT mld_statuses_omschrijving
FROM mld_statuses
WHERE mld_statuses_key = mld_melding_status),
DECODE (mld_melding_afgerond, 1, 'x'),
(SELECT prs_dienst_omschrijving
FROM prs_dienst
WHERE prs_dienst_key = g.prs_dienst_key),
mld_stdmelding_key,
mld_ins_discipline_key,
overlappend,
wegingsfactor,
mld_melding_omschrijving,
mld_melding_opmerking,
nmm_melding,
(SELECT prs_bedrijf_naam FROM prs_bedrijf b WHERE b.prs_bedrijf_key = g.prs_bedrijf_key) prs_bedrijf_naam
FROM (SELECT m.m_start,
m.aantal,
DECODE (m.aantal, 1, NULL, m.overlappend) overlappend,
m.mld_melding_status,
m.mld_melding_afgerond,
m.mld_melding_indult,
m.mld_melding_omschrijving,
m.mld_melding_opmerking,
std.prs_dienst_key,
std.mld_stdmelding_key,
std.mld_ins_discipline_key,
std.mld_stdmelding_omschrijving,
std.mld_stdmelding_malus,
q.vervolg q_vervolg,
m.t_van,
m.gereed,
m.fictief,
m.mld_melding_einddatum_std thtijdstip,
stdg.mld_stdmeldinggroep_naam,
nmms.get_regime (std.mld_stdmelding_key,
m.mld_alg_onroerendgoed_keys,
NULL)
regime, -- aantal werkuren per dag
UPPER (SUBSTR (stdg.mld_stdmeldinggroep_naam, 1, 1))
kortingtype, -- de meldingsgroep bevat het type storing
kp.prs_kostenplaats_nr,
rg.alg_locatie_code,
rg.alg_gebouw_upper,
rg.alg_verdieping_volgnr,
rg.alg_ruimte_nr || '-' || rg.alg_ruimte_omschrijving
ruimte,
d.ins_deel_omschrijving object,
(SELECT ins_kenmerkdeel_waarde
FROM ins_kenmerkdeel
WHERE ins_deel_key = d.ins_deel_key
AND ins_kenmerk_key = 9)
thema,
(SELECT COUNT (mb.mld_melding_key) aantal -- kijk of er op het moment van de melding er een beschikbaarheidsstoring was
FROM mld_melding mb, mld_stdmelding std_b
WHERE (mb.mld_melding_datum BETWEEN m.t_van
AND COALESCE (m.gereed,
SYSDATE)
OR fac.gettrackingdate ('MLDAFM',
mb.mld_melding_key) BETWEEN m.t_van
AND COALESCE (
m.gereed,
SYSDATE))
AND mb.mld_alg_onroerendgoed_keys =
m.mld_alg_onroerendgoed_keys
AND mb.mld_stdmelding_key =
std_b.mld_stdmelding_key
AND std_b.mld_stdmeldinggroep_key = 63 -- beschikbaarheid
AND std.mld_stdmeldinggroep_key <> 63)
aantal_beschikb, -- beschikbaarheid
-- vervolgmelding op een inspectie zijn per definitie te laat
-- inspecties zelf zijn te laat als de geplande einddatum verstreken is.
-- anders geldt de normale formule
CASE COALESCE (q.vervolg, 0)
WHEN 1
THEN
1
ELSE
DECODE (
UPPER (
SUBSTR (stdg.mld_stdmeldinggroep_naam, 1, 1)),
'Q',
DECODE ( (SELECT COUNT (km.mld_kenmerkmelding_key)
FROM mld_kenmerkmelding km
WHERE km.mld_kenmerk_key = 261 -- TEST resultaat herkeuring
AND km.mld_melding_key = m.m_start
AND fac.safe_to_number(km.mld_kenmerkmelding_waarde) = 525), -- TEST Niet succesvol
1, 1, -- Te laat als een niet succesvol kenmerk bestaat
SIGN(COALESCE (m.gereed, SYSDATE) -- COALESCE met SYSDATE of niet???
- m.mld_melding_einddatum) -- max_actual_einddatum - max_melding_einddatum over ALLE m_start-meldingen
),
SIGN(m.max_t_actual.tijdsduur - m.min_t_sla.tijdsduur - m.max_t_respijt.tijdsduur)) -- positief, dan te laat
END
telaat,
m.max_t_actual actual, -- MAX actuele doorlooptijd
m.min_t_sla thtsla, -- MIN SLA
m.min_t_sla.eenheid eenheid,
m.max_t_respijt respijt, -- MAX respijt
MLD_T_UITVOERTIJD (
DECODE (q.vervolg, NULL, m.min_t_sla.tijdsduur, 0)
+ m.max_t_respijt.tijdsduur,
m.min_t_sla.eenheid)
tht, -- MIN SLA + MAX respijt
MLD_T_UITVOERTIJD (
m.max_t_actual.tijdsduur
- DECODE (q.vervolg, NULL, m.min_t_sla.tijdsduur, 0)
- m.max_t_respijt.tijdsduur,
m.max_t_actual.eenheid)
uitloop, -- hoeveel te laat
DECODE (
d.ins_deel_key,
NULL,
sr.alg_srtruimte_prijs4, -- wegingsfactor bij soortruimte
(SELECT ins_kenmerkdeel_waarde
FROM ins_kenmerkdeel
WHERE ins_deel_key = d.ins_deel_key
AND ins_kenmerk_key = 10)) -- wegingsfactor bij object
wegingsfactor,
-- is de tijd van de melding aangepast?
(SELECT COUNT ( * )
FROM fac_tracking t, fac_srtnotificatie sn
WHERE t.fac_tracking_refkey = m.m_start -- mld_melding_key VAN MELDING MET LAAGSTE KEY???
AND t.fac_srtnotificatie_key = sn.fac_srtnotificatie_key
AND sn.fac_srtnotificatie_code = 'MLDUPD'
AND (t.fac_tracking_oms LIKE '%Start :%'
OR t.fac_tracking_oms LIKE '%Afgemeld:%'))
antedateren,
nmm_melding,
prs_bedrijf_key
FROM nmms_v_startmelding_geg m,
mld_stdmelding std,
mld_stdmeldinggroep stdg,
prs_kostenplaats kp,
alg_v_ruimte_gegevens rg,
alg_srtruimte sr,
-- vind vervolgmeldingen op Q-meldingen (periodieke inspecties)
( SELECT m1.mld_melding_key, DECODE (m2.mld_melding_key, NULL, 0, 1) vervolg
FROM mld_melding m1,
mld_stdmelding std1,
mld_stdmeldinggroep stdg1,
mld_melding m2,
mld_stdmelding std2,
mld_stdmeldinggroep stdg2
WHERE m1.mld_stdmelding_key = std1.mld_stdmelding_key
AND std1.mld_stdmeldinggroep_key = stdg1.mld_stdmeldinggroep_key
AND UPPER (SUBSTR (stdg1.mld_stdmeldinggroep_naam, 1, 1)) <> 'Q'
AND m1.mld_melding_start_key = m2.mld_melding_key
AND m2.mld_stdmelding_key = std2.mld_stdmelding_key
AND std2.mld_stdmeldinggroep_key = stdg2.mld_stdmeldinggroep_key
AND UPPER (SUBSTR (stdg2.mld_stdmeldinggroep_naam, 1, 1)) = 'Q') q,
-- vind object met kortste doorlooptijd per melding
(SELECT mld_melding_key,
MLD.getlowestobjectuitvoer (mld_melding_key)
ins_deel_key
FROM mld_melding_object) obj,
ins_deel d
WHERE m.mld_melding_status IN (0, 2, 3, 4, 5, 6, 7, 99) -- alles behalve 1=Afgewezen
AND m.mld_stdmelding_key = std.mld_stdmelding_key
AND std.mld_stdmeldinggroep_key = stdg.mld_stdmeldinggroep_key(+)
AND m.prs_kostenplaats_key = kp.prs_kostenplaats_key(+)
AND m.mld_alg_onroerendgoed_keys = rg.alg_ruimte_key(+)
AND rg.alg_srtruimte_key = sr.alg_srtruimte_key(+)
AND (q.vervolg IS NOT NULL OR UPPER (SUBSTR (stdg.mld_stdmeldinggroep_naam, 1, 1)) = 'Q' OR nmm_melding = 1)
-- storingen op vervolgmeldingen en inspecties (Q) worden altijd getoond
-- anders alleen storingen ingelegd door NMM medewerkers
AND m.m_start = q.mld_melding_key(+)
AND m.m_start = obj.mld_melding_key(+) -- mld_melding_key VAN MELDING MET LAAGSTE KEY???
AND obj.ins_deel_key = d.ins_deel_key(+)
AND std.mld_stdmelding_slabewaken = 1) g;
--
-- Rapportage gebasseerd op de basis rapportage
--
CREATE OR REPLACE VIEW nmms_v_rap_monitoring
(
html_melding,
hide_f_melding_key,
fclt_f_afw,
q_vervolg,
indult,
respijt,
ruimte,
object,
thema,
omschrijving,
begin,
gereed,
fictief,
thtijdstip,
eenheid,
tht,
wht,
regime,
groep,
typekorting,
basiskorting,
variabelekorting,
uren,
kb,
prestatiekorting,
factor,
kp,
fclt_f_status,
fclt_f_afgerond,
fclt_f_dienst,
hide_f_stdmelding_key,
wegingsfactor,
totaal,
overlappend,
te_laat,
nmm,
bedrijf,
hide_f_melding_omschrijving,
hide_f_melding_opmerking
)
AS
SELECT ''
|| melding_key
|| ''
melding,
melding_key,
afw,
q_vervolg,
indult,
respijt,
ruimte,
object,
thema,
omschrijving,
to_char(begin, 'dd-mm-yyyy hh24:mi:ss') begin,
to_char(gereed, 'dd-mm-yyyy hh24:mi:ss') gereed,
CASE WHEN fictief = 1 THEN 'fictief' ELSE '' END fictief,
to_char(thtijdstip, 'dd-mm-yyyy hh24:mi:ss') thtijdstip,
eenheid,
tht,
wht,
regime,
groep,
typekorting,
basiskorting,
variabelekorting,
uren,
kb,
prestatiekorting,
factor,
kp,
status,
afgerond,
dienst,
stdmelding_key,
wegingsfactor,
(COALESCE(kb,0) * COALESCE(wegingsfactor,1)) + COALESCE(kp,0) totaal,
overlappend,
DECODE(SIGN(thtijdstip - COALESCE(gereed,SYSDATE)), -1, 'x', '') te_laat,
DECODE(nmm_melding, 1, 'x', ''),
bedrijf_naam,
melding_omschrijving,
melding_opmerking
FROM nmms_v_monitoring;
--
-- rapportage view tbv de opdrachtgever. In deze gestylede rapportage kan de opdrachtgever afwijkingen goedkeuren.
--
CREATE OR REPLACE VIEW nmms_v_rap_monitoring_og
(
html_melding,
hide_f_melding_key,
fclt_f_afw,
q_vervolg,
indult,
respijt,
ruimte,
object,
thema,
omschrijving,
begin,
gereed,
thtijdstip,
eenheid,
tht,
wht,
regime,
groep,
typekorting,
basiskorting,
variabelekorting,
uren,
kb,
prestatiekorting,
factor,
kp,
fclt_f_status,
fclt_f_afgerond,
fclt_f_dienst,
hide_f_stdmelding_key,
wegingsfactor,
hide_f_melding_omschrijving,
hide_f_melding_opmerking
)
AS
SELECT html_melding,
hide_f_melding_key,
fclt_f_afw,
q_vervolg,
indult,
respijt,
ruimte,
object,
thema,
omschrijving,
begin,
gereed,
thtijdstip,
eenheid,
tht,
wht,
regime,
groep,
typekorting,
basiskorting,
variabelekorting,
uren,
kb,
prestatiekorting,
factor,
kp,
fclt_f_status,
fclt_f_afgerond,
fclt_f_dienst,
hide_f_stdmelding_key,
wegingsfactor,
hide_f_melding_omschrijving,
hide_f_melding_opmerking
FROM nmms_v_rap_monitoring
WHERE fclt_f_afw IS NOT NULL OR fclt_f_afgerond IS NOT NULL;
--
-- view van nog te exporteren meldingen die afwijken of tot een korting leiden.
--
CREATE OR REPLACE VIEW nmms_v_monitoring_tot
(
melding_key,
afw,
q_vervolg,
indult,
respijt,
ruimte,
object,
thema,
omschrijving,
begin,
gereed,
thtijdstip,
verwerkt,
eenheid,
tht,
wht,
regime,
groep,
typekorting,
basiskorting,
variabelekorting,
uren,
kb,
prestatiekorting,
factor,
kp,
herhalingskorting,
aantal,
kh,
wegingsfactor,
dienst,
stdmelding_key,
totaal,
overlappend,
kinderen,
bedrijf_naam,
nmm_melding
)
AS
SELECT melding_key,
afw,
q_vervolg,
indult,
respijt,
ruimte,
object,
thema,
omschrijving,
begin,
gereed,
thtijdstip,
verwerkt,
eenheid,
tht,
wht,
regime,
groep,
typekorting,
basiskorting,
variabelekorting,
uren,
kb,
prestatiekorting,
factor,
kp,
TO_NUMBER (NULL),
TO_NUMBER (NULL),
TO_NUMBER (NULL),
wegingsfactor,
dienst,
stdmelding_key,
(COALESCE(kb,0) * COALESCE(wegingsfactor,1)) + COALESCE(kp,0),
SUBSTR(overlappend,1,100),
NULL,
bedrijf_naam,
nmm_melding
FROM nmms_v_monitoring
WHERE (kb IS NOT NULL OR kp IS NOT NULL)
AND ( (status = 'Afgemeld' AND afw IS NULL) OR (status = 'Historie'))
AND (gereed < TRUNC (SYSDATE, 'MONTH')
OR TRUNC (SYSDATE, 'MONTH') =
(SELECT TRUNC (MAX (export_datum), 'MONTH')
FROM nmms_monitoring))
UNION ALL
-- herhalingskorting op beschikbaarheid bij thema
SELECT NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
thema,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'Kh',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
250,
COUNT ( * ) - 4,
(COUNT ( * ) - 4) * 250,
NULL,
NULL,
NULL,
(COUNT ( * ) - 4) * 250,
NULL,
listagg (melding_key, ',') WITHIN GROUP (ORDER BY melding_key)
AS kinderen,
NULL,
NULL
FROM nmms_v_monitoring
WHERE typekorting = 'Kb'
AND thema IS NOT NULL
AND indult = 'Nee'
AND ( (status = 'Afgemeld' AND afw IS NULL) OR (status = 'Historie'))
AND (gereed < TRUNC (SYSDATE, 'MONTH')
OR TRUNC (SYSDATE, 'MONTH') =
(SELECT TRUNC (MAX (export_datum), 'MONTH')
FROM nmms_monitoring))
GROUP BY thema, typekorting
HAVING COUNT (ruimte) > 4
UNION ALL
-- herhalingskorting op beschikbaarheid bij ruimte
SELECT NULL,
NULL,
NULL,
NULL,
NULL,
ruimte,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'Kh',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
250,
COUNT ( * ) - 4,
(COUNT ( * ) - 4) * 250,
NULL,
NULL,
NULL,
(COUNT ( * ) - 4) * 250,
NULL,
listagg (melding_key, ',') WITHIN GROUP (ORDER BY melding_key)
AS kinderen,
NULL,
NULL
FROM nmms_v_monitoring
WHERE typekorting = 'Kb'
AND thema IS NULL
AND indult = 'Nee'
AND ( (status = 'Afgemeld' AND afw IS NULL) OR (status = 'Historie'))
AND (gereed < TRUNC (SYSDATE, 'MONTH')
OR TRUNC (SYSDATE, 'MONTH') =
(SELECT TRUNC (MAX (export_datum), 'MONTH')
FROM nmms_monitoring))
GROUP BY ruimte, typekorting
HAVING COUNT (ruimte) > 4
UNION ALL
-- Herhalingskorting op prestatiegebrek (stdmelding)
SELECT NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
(SELECT mld_stdmelding_omschrijving
FROM mld_stdmelding std
WHERE std.mld_stdmelding_key = r.stdmelding_key),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'Kh',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
250,
COUNT ( * ) - 4,
(COUNT ( * ) - 4) * 250,
NULL,
NULL,
NULL,
(COUNT ( * ) - 4) * 250,
NULL,
listagg (melding_key, ',') WITHIN GROUP (ORDER BY melding_key)
AS kinderen,
NULL,
NULL
FROM nmms_v_monitoring r
WHERE typekorting = 'Kp'
AND indult = 'Nee'
AND discipline_key <> 354 -- schoonmaak
AND ( (status = 'Afgemeld' AND afw IS NULL) OR (status = 'Historie'))
AND (gereed < TRUNC (SYSDATE, 'MONTH')
OR TRUNC (SYSDATE, 'MONTH') =
(SELECT TRUNC (MAX (export_datum), 'MONTH')
FROM nmms_monitoring))
GROUP BY stdmelding_key, typekorting
HAVING COUNT (stdmelding_key) > 4
UNION ALL
-- Herhalingskorting op prestatiegebrek schoonmaak (vakgroep key 354) en ruimte
SELECT NULL,
NULL,
NULL,
NULL,
NULL,
ruimte,
NULL,
NULL,
'Schoonmaak',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'Kh',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
250,
COUNT ( * ) - 4,
(COUNT ( * ) - 4) * 250,
NULL,
NULL,
NULL,
(COUNT ( * ) - 4) * 250,
NULL,
listagg (melding_key, ',') WITHIN GROUP (ORDER BY melding_key)
AS kinderen,
NULL,
NULL
FROM nmms_v_monitoring r
WHERE typekorting = 'Kp'
AND indult = 'Nee'
AND discipline_key = 354 -- schoonmaak
AND ( (status = 'Afgemeld' AND afw IS NULL) OR (status = 'Historie'))
AND (gereed < TRUNC (SYSDATE, 'MONTH')
OR TRUNC (SYSDATE, 'MONTH') =
(SELECT TRUNC (MAX (export_datum), 'MONTH')
FROM nmms_monitoring))
GROUP BY discipline_key, ruimte, typekorting
HAVING COUNT (ruimte) > 4;
-- rapportage view van nog te exporteren meldingen die afwijken of tot een korting leiden.
CREATE OR REPLACE VIEW nmms_v_rap_monitoring_tot
(
html_melding,
hide_f_melding_key,
fclt_f_afw,
q_vervolg,
indult,
respijt,
ruimte,
object,
thema,
omschrijving,
begin,
gereed,
thtijdstip,
eenheid,
tht,
wht,
regime,
groep,
typekorting,
basiskorting,
variabelekorting,
uren,
kb,
prestatiekorting,
factor,
kp,
herhalingskorting,
aantal,
kh,
kinderen,
wegingsfactor,
totaal
)
AS
SELECT ''
|| melding_key
|| ''
melding,
melding_key,
afw,
q_vervolg,
indult,
respijt,
ruimte,
object,
thema,
omschrijving,
to_char(begin, 'dd-mm-yyyy hh24:mi:ss') begin,
to_char(gereed, 'dd-mm-yyyy hh24:mi:ss') gereed,
to_char(thtijdstip, 'dd-mm-yyyy hh24:mi:ss') thtijdstip,
eenheid,
tht,
wht,
regime,
groep,
typekorting,
basiskorting,
variabelekorting,
uren,
kb,
prestatiekorting,
factor,
kp,
herhalingskorting,
aantal,
kh,
kinderen,
wegingsfactor,
totaal
FROM nmms_v_monitoring_tot;
-- rapport om historische informatie te tonen
CREATE OR REPLACE VIEW nmms_v_monitoring_history
( fclt_d_export_datum,
html_melding,
hide_f_melding_key,
fclt_f_afw,
q_vervolg,
indult,
respijt,
ruimte,
object,
thema,
omschrijving,
begin,
gereed,
thtijdstip,
eenheid,
tht,
wht,
regime,
groep,
typekorting,
basiskorting,
variabelekorting,
uren,
kb,
prestatiekorting,
factor,
kp,
fclt_f_dienst,
hide_f_stdmelding_key,
wegingsfactor,
totaal,
overlappend,
te_laat,
nmm,
bedrijf,
hide_f_melding_omschrijving,
hide_f_melding_opmerking
)
AS
SELECT TO_CHAR (export_datum, 'dd-mm-yyyy') export_datum,
''
|| melding_key
|| ''
melding,
melding_key,
afw,
q_vervolg,
indult,
respijt,
ruimte,
object,
thema,
omschrijving,
TO_CHAR (begin, 'dd-mm-yyyy hh24:mi:ss') begin,
TO_CHAR (gereed, 'dd-mm-yyyy hh24:mi:ss') gereed,
TO_CHAR (thtijdstip, 'dd-mm-yyyy hh24:mi:ss') thtijdstip,
eenheid,
tht,
wht,
regime,
groep,
typekorting,
basiskorting,
variabelekorting,
uren,
kb,
prestatiekorting,
factor,
kp,
dienst,
stdmelding_key,
wegingsfactor,
(COALESCE(kb,0) * COALESCE(wegingsfactor,1)) + COALESCE(kp,0) totaal,
overlappend,
DECODE(SIGN(thtijdstip - COALESCE(gereed,SYSDATE)), -1, 'x', '') te_laat,
DECODE(nmm_melding, 1, 'x', ''),
prs_bedrijf_naam,
mld_melding_omschrijving,
mld_melding_opmerking
FROM nmms_monitoring mo, mld_melding m
WHERE mo.melding_key = m.mld_melding_key
AND begin IS NOT NULL;
CREATE OR REPLACE VIEW nmms_v_dwh_monitoring
(
melding_key,
afw,
q_vervolg,
indult,
respijt,
kostenplaats,
locatie,
gebouw,
verdieping,
ruimte,
object,
thema,
omschrijving,
begin,
gereed,
thtijdstip,
tht,
wht,
regime,
groep,
typekorting,
basiskorting,
variabelekorting,
uren,
kb,
prestatiekorting,
factor,
kp,
herhalingskorting,
aantal,
kh,
status,
dienst,
stdmelding_key,
wegingsfactor,
totaal,
overlappend,
te_laat,
nmm,
bedrijf,
melding_omschrijving,
melding_opmerking,
kostensoort_oms,
verwerkt,
export_datum
)
AS
SELECT melding_key,
afw,
q_vervolg,
indult,
respijt,
kostenplaats,
locatie,
gebouw,
verdieping,
ruimte,
object,
thema,
omschrijving,
begin,
gereed,
thtijdstip,
tht,
wht,
regime,
groep,
typekorting,
basiskorting,
variabelekorting,
uren,
kb,
prestatiekorting,
factor,
kp,
herhalingskorting,
aantal,
kh,
'Historie',
dienst,
stdmelding_key,
wegingsfactor,
(COALESCE(kb,0) * COALESCE(wegingsfactor,1)) + COALESCE(kp,0) totaal,
overlappend,
DECODE(SIGN(thtijdstip - COALESCE(gereed,SYSDATE)), -1, 'x', '') te_laat,
DECODE(nmm_melding, 1, 'x', ''),
prs_bedrijf_naam,
mld_melding_omschrijving,
mld_melding_opmerking,
(SELECT prs_kostensoort_oms
FROM prs_kostensoort ks, mld_stdmelding std
WHERE m.mld_stdmelding_key = std.mld_stdmelding_key
AND std.prs_kostensoort_key = ks.prs_kostensoort_key),
verwerkt,
export_datum
FROM nmms_monitoring mo, mld_melding m
WHERE mo.melding_key = m.mld_melding_key
AND totaal IS NOT NULL;
CREATE OR REPLACE PROCEDURE nmms_export_freeze_monitoring (
p_applname IN VARCHAR2,
p_applrun IN VARCHAR2,
p_filedir IN VARCHAR2,
p_filename IN VARCHAR2)
AS
CURSOR c1
IS
SELECT melding_key
FROM nmms_v_monitoring
WHERE status = 'Afgemeld'
AND gereed < TRUNC (SYSDATE, 'MONTH')
AND afw IS NULL -- geen afwijkingen die goedgekeurd moeten worden
AND afgerond IS NULL; -- expliciete goedkeuring staat niet aan
CURSOR c2
IS
SELECT m_start,
m.mld_melding_key,
(SELECT mld_melding_status
FROM mld_melding ms
WHERE ms.mld_melding_key = m_start)
mld_melding_status
FROM (SELECT nmms.get_first_melding (mld_melding_key, 1) m_start,
fac.gettrackingdate ('MLDAFM', m.mld_melding_key) gereed,
m.*
FROM mld_melding m
WHERE m.mld_melding_parentkey IS NULL
AND fac.gettrackingdate ('MLDAFM', m.mld_melding_key) <
TRUNC (SYSDATE, 'MONTH')
AND m.mld_melding_key NOT IN
(SELECT melding_key FROM nmms_monitoring WHERE melding_key IS NOT NULL)) m,
nmms_v_monitoring mo
WHERE m_start <> m.mld_melding_key AND m_start = mo.melding_key
AND ( (status = 'Afgemeld' AND afw IS NULL) -- startmelding mag niet hangen op goedkeuring
OR (status = 'Historie'));
CURSOR c3
IS
SELECT * FROM nmms_v_monitoring_tot;
BEGIN
-- Gaan we vandaag de freeze starten? Dit mag alleen op de derde werkdag van de maand om 17:00 uur.
IF fac.count_Work_Days(trunc(sysdate, 'MON'), sysdate) = 2
THEN
-- Zet de meldingen die geen afwijking kennen en die niet op status afgerond staan naar status verwerkt
FOR rec IN c1
LOOP
BEGIN
BEGIN
mld.setmeldingstatus (rec.melding_key, 6, 4);
END;
END;
END LOOP;
-- zet slave meldingen van samenvallende meldingen ook op verwerkt
FOR rec IN c2
LOOP
BEGIN
IF rec.mld_melding_status = 6 OR rec.mld_melding_status = 5 -- verwerkt of afgemeld
THEN
BEGIN
mld.setmeldingstatus (rec.mld_melding_key, 6, 4);
END;
END IF;
END;
END LOOP;
-- Voeg de entries uit het monitoringsoverzicht toe aan de freeze tabel.
FOR rec IN c3
LOOP
BEGIN
BEGIN
INSERT INTO nmms_monitoring (melding_key,
afw,
q_vervolg,
indult,
respijt,
ruimte,
object,
thema,
omschrijving,
begin,
gereed,
thtijdstip,
verwerkt,
eenheid,
tht,
wht,
regime,
groep,
typekorting,
basiskorting,
variabelekorting,
uren,
kb,
prestatiekorting,
factor,
kp,
herhalingskorting,
aantal,
kh,
wegingsfactor,
dienst,
stdmelding_key,
totaal,
overlappend,
kinderen,
prs_bedrijf_naam,
nmm_melding,
export_datum)
VALUES (rec.melding_key,
rec.afw,
rec.q_vervolg,
rec.indult,
rec.respijt,
rec.ruimte,
rec.object,
rec.thema,
rec.omschrijving,
rec.begin,
rec.gereed,
rec.thtijdstip,
rec.verwerkt,
rec.eenheid,
rec.tht,
rec.wht,
rec.regime,
rec.groep,
rec.typekorting,
rec.basiskorting,
rec.variabelekorting,
rec.uren,
rec.kb,
rec.prestatiekorting,
rec.factor,
rec.kp,
rec.herhalingskorting,
rec.aantal,
rec.kh,
rec.wegingsfactor,
rec.dienst,
rec.stdmelding_key,
DECODE (rec.indult, 'Ja', 0, rec.totaal),
rec.overlappend,
rec.kinderen,
rec.bedrijf_naam,
rec.nmm_melding,
SYSDATE);
END;
END;
END LOOP;
INSERT INTO nmms_monitoring (melding_key, export_datum)
SELECT mld_melding_key, SYSDATE
FROM mld_melding m
WHERE m.mld_melding_status = 6 -- Historie
AND fac.gettrackingdate ('MLDAFM', m.mld_melding_key) <
TRUNC (SYSDATE, 'MONTH')
AND m.mld_melding_key NOT IN (SELECT nm.melding_key
FROM nmms_monitoring nm
WHERE nm.melding_key IS NOT NULL);
COMMIT;
END IF;
END nmms_export_freeze_monitoring;
/
CREATE OR REPLACE VIEW nmms_v_dwh_opdr_materiaal
(
opdr_id,
opdr_key,
opdr_datum,
kostenplaats,
aanvrager,
aanvrager_key,
aantal,
omschrijving,
prijs,
totaal_inkoop,
totaal_verkoop
)
AS
SELECT o.mld_melding_key || '/' || o.mld_opdr_bedrijfopdr_volgnr,
o.mld_opdr_key,
TO_CHAR (o.mld_opdr_datumbegin, 'dd-mm-yyyy'),
k.prs_kostenplaats_nr,
pf.prs_perslid_naam_full,
pf.prs_perslid_key,
mld_opdr_materiaal_aantal,
ud.fac_usrdata_omschr,
mld_opdr_materiaal_prijs,
mld_opdr_materiaal_aantal * mld_opdr_materiaal_prijs,
ROUND (
mld_opdr_materiaal_aantal * mld_opdr_materiaal_prijs
* COALESCE (
(SELECT MAX (u.uplift)
FROM aaxx_imp_uplift u
WHERE UPPER (soort_order) =
UPPER (ot.mld_typeopdr_omschrijving)
AND UPPER (u.prs_kostensoort_oms) =
ks.prs_kostensoort_upper
AND TO_CHAR (o.mld_opdr_datumbegin, 'yyyy-mm') =
u.jaar || '-' || LTRIM (TO_CHAR (u.maand, '00'))),
1),
2)
prijs_uplift
FROM mld_opdr_materiaal om,
fac_usrdata ud,
mld_opdr o,
mld_melding m,
prs_v_perslid_fullnames_all pf,
prs_kostenplaats k,
mld_stdmelding std,
prs_kostensoort ks,
mld_typeopdr ot
WHERE om.mld_opdr_key = o.mld_opdr_key
AND om.fac_usrdata_key = ud.fac_usrdata_key
AND o.mld_melding_key = m.mld_melding_key
AND o.prs_perslid_key = pf.prs_perslid_key
AND o.prs_kostenplaats_key = k.prs_kostenplaats_key
AND m.mld_stdmelding_key = std.mld_stdmelding_key
AND std.prs_kostensoort_key = ks.prs_kostensoort_key
AND o.mld_typeopdr_key = ot.mld_typeopdr_key;
--------------------------------------------------------------
--
-- Dashboard views
--
--------------------------------------------------------------
CREATE OR REPLACE VIEW nmms_v_dashboard_korting
(
melding,
omschrijving,
typekorting,
marge,
hide_f_marge,
korting,
tht_tijdstip,
tht
)
AS
SELECT melding_key,
omschrijving,
typekorting,
(SELECT ROUND (
DECODE (
typekorting,
'Kq',
fac.count_work_days (SYSDATE, mld_melding_einddatum),
(m.mld_melding_t_uitvoertijd.tijdsduur
- mld.getactualuitvoer (m.mld_melding_key).tijdsduur))
,
2)
|| ' ' || m.mld_melding_t_uitvoertijd.eenheid
FROM mld_melding m
WHERE mo.melding_key = m.mld_melding_key)
marge_txt,
(SELECT ROUND (
DECODE (
typekorting,
'Kq',
fac.count_work_days (SYSDATE, mld_melding_einddatum),
m.mld_melding_t_uitvoertijd.tijdsduur
- mld.getactualuitvoer (m.mld_melding_key).tijdsduur),
2)
FROM mld_melding m
WHERE mo.melding_key = m.mld_melding_key)
marge,
COALESCE (kb * COALESCE(wegingsfactor,1),
kp,
basiskorting,
prestatiekorting)
korting,
thtijdstip,
tht
FROM nmms_v_monitoring mo
WHERE gereed IS NULL
AND nmm_melding = 1 AND mo.fictief = 0;
--------------------------------------------------------------
--
-- Real time monitoring views
--
--------------------------------------------------------------
CREATE OR REPLACE VIEW nmms_v_graph_vakgroep_open
(
fclt_xas_,
fclt_yas_,
fclt_yas2_,
sortering,
fclt_url,
fclt_3d_discipline_key
)
AS
SELECT ins_srtdiscipline_prefix || ' '
|| lcl.x ('ins_discipline_omschrijving',
d.ins_discipline_key,
d.ins_discipline_omschrijving),
COUNT (m.mld_melding_key) - SUM (uitvoeringoptijd) telaat,
SUM (uitvoeringoptijd) op_tijd,
-1 * COUNT (m.mld_melding_key) totaal,
'appl/mld/mld_search.asp?urole=fo'
|| '&'
|| 'autosearch=1'
|| '&'
|| 'disc_key_str='
|| d.ins_discipline_key,
d.ins_discipline_key
FROM mld_melding m,
mld_stdmelding std,
ins_tab_discipline d,
ins_srtdiscipline sd,
(SELECT mld_melding_key,
CASE SIGN (mld_melding_einddatum - SYSDATE)
WHEN -1 THEN 0
ELSE 1
END
uitvoeringoptijd
FROM mld_melding m2) uitv
WHERE 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 m.mld_melding_key = uitv.mld_melding_key
AND mld_melding_status IN (2, 3, 4, 7, 0) -- 2-ingevoerd, 3-ingezien, 4-geaccepteerd, 7-uitgegeven, 0-pending
GROUP BY d.ins_discipline_key,
d.ins_discipline_omschrijving,
ins_srtdiscipline_prefix
ORDER BY COUNT (d.ins_discipline_omschrijving) DESC;
CREATE OR REPLACE VIEW nmms_v_graph_openst_behand
(
fclt_xas_,
fclt_yas_,
fclt_yas2_op_tijd
)
AS
SELECT COALESCE (behandelaar, 'Onbekend') behandelaar,
COUNT (mld_key) - SUM (uitvoeringoptijd) totaal,
SUM (uitvoeringoptijd) op_tijd
FROM (SELECT DISTINCT
m.mld_melding_key mld_key,
CASE
WHEN fac.gettrackingdate ('MLDREJ', m.mld_melding_key) IS NULL
THEN
CASE SIGN(fac.datumtijdplusuitvoertijd (
m.mld_melding_datum,
m.mld_melding_t_uitvoertijd.tijdsduur,
m.mld_melding_t_uitvoertijd.eenheid)
- COALESCE (
fac.gettrackingdate ('MLDREJ', m.mld_melding_key),
fac.gettrackingdate ('MLDAFM', m.mld_melding_key),
SYSDATE))
WHEN -1
THEN
0
ELSE
1
END
ELSE
1
END
uitvoeringoptijd,
DECODE (
mld_melding_status,
7,
(SELECT MAX (prs_bedrijf_naam)
FROM mld_opdr o, prs_bedrijf b
WHERE o.mld_melding_key = m.mld_melding_key
AND o.mld_uitvoerende_keys = b.prs_bedrijf_key
AND o.mld_statusopdr_key IN (3, 4, 5, 8)), -- 3- Ter fiattering, 4-Gefiatteerd, 5-Uitgegeven, 8-Geaccepteerd
4,
COALESCE (
(SELECT MAX (prs_bedrijf_naam)
FROM mld_opdr o, prs_bedrijf b
WHERE o.mld_melding_key = m.mld_melding_key
AND o.mld_uitvoerende_keys = b.prs_bedrijf_key
AND o.mld_statusopdr_key IN (3, 4, 5, 8)), -- 3- Ter fiattering, 4-Gefiatteerd, 5-Uitgegeven, 8-Geaccepteerd
(SELECT prs_perslid_naam_full
FROM prs_v_perslid_fullnames pf
WHERE pf.prs_perslid_key =
m.mld_melding_behandelaar_key)),
(SELECT prs_perslid_naam_full
FROM prs_v_perslid_fullnames pf
WHERE pf.prs_perslid_key =
m.mld_melding_behandelaar_key))
behandelaar
FROM mld_melding m,
mld_discipline md,
prs_perslid p,
mld_stdmelding std
WHERE m.prs_perslid_key = p.prs_perslid_key
AND md.ins_discipline_key = std.mld_ins_discipline_key
AND std.mld_stdmelding_key = m.mld_stdmelding_key
AND mld_melding_status IN (2, 3, 4, 7, 0)) -- 2-ingevoerd, 3-ingezien, 4-geaccepteerd, 7-uitgegeven, 0-pending
GROUP BY behandelaar;
--------------------------------------------------------------
--
-- Graphics
--
--------------------------------------------------------------
CREATE OR REPLACE VIEW nmms_v_thema_legenda_bt
(
ALG_RUIMTE_KEY,
WAARDE,
WAARDE_KEY
)
AS
SELECT alg_ruimte_key,
sr.alg_srtruimte_omschrijving,
sr.alg_srtruimte_key waarde_key
FROM alg_v_aanwezigruimte r, alg_onrgoedkenmerk k, alg_srtruimte sr
WHERE r.alg_ruimte_key = k.alg_onrgoed_key
AND r.alg_srtruimte_key = sr.alg_srtruimte_key
AND k.alg_onrgoed_niveau = 'R'
AND k.alg_kenmerk_key = 1100;
GRANT SELECT ON nmms_v_dwh_monitoring TO aafm;
GRANT SELECT ON nmms_v_dwh_opdr_materiaal TO aafm;
------ 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