91 lines
2.3 KiB
SQL
91 lines
2.3 KiB
SQL
--
|
|
-- $Id$
|
|
--
|
|
-- Customer specific once-script UWVA#51205.
|
|
DEFINE thisfile = 'UWVA#51205.SQL'
|
|
DEFINE dbuser = '^UWVA'
|
|
|
|
SET ECHO ON
|
|
SET DEFINE ON
|
|
COLUMN fcltlogfile NEW_VALUE fcltlogfile NOPRINT;
|
|
WHENEVER SQLERROR EXIT;
|
|
SELECT adm.scriptspoolfile('&dbuser', '&thisfile') AS fcltlogfile FROM DUAL;
|
|
WHENEVER SQLERROR CONTINUE;
|
|
SPOOL &fcltlogfile
|
|
SET DEFINE OFF
|
|
|
|
------ payload begin ------
|
|
|
|
CREATE OR REPLACE VIEW UWVA_V_KCI
|
|
(
|
|
LOCATIE,
|
|
DATUM,
|
|
TIJDSBLOK,
|
|
REDEN_BEZOEK,
|
|
SOORT_CLIENT,
|
|
AFDELING,
|
|
KONTAKT_KCC,
|
|
BSN_NUMMER,
|
|
AFHANDELING,
|
|
KLANT_GEHOLPEN,
|
|
MELDING,
|
|
TIJDSTIP
|
|
)
|
|
AS
|
|
SELECT locatie,
|
|
TO_CHAR (aanmaak, 'DD-MM-YYYY') datum,
|
|
tijdsblok,
|
|
reden_bezoek,
|
|
soort_client,
|
|
afdeling,
|
|
kontakt_kcc,
|
|
bsn_nummer,
|
|
afhandeling,
|
|
klant_geholpen,
|
|
melding,
|
|
TO_CHAR (aanmaak, 'HH24:MI') tijdstip
|
|
FROM (SELECT locatie,
|
|
aanmaak,
|
|
tijdsblok,
|
|
reden_bezoek,
|
|
soort_client,
|
|
afdeling,
|
|
afhandeling,
|
|
kontakt_kcc,
|
|
klant_geholpen,
|
|
bsn_nummer,
|
|
melding
|
|
FROM uwva_v_spobilo
|
|
WHERE aanmaak BETWEEN TO_DATE ('10-11-2017 20:05:00', 'dd-mm-yyyy hh24:mi:ss') - 7 AND TO_DATE ('10-11-2017 20:05:00', 'dd-mm-yyyy hh24:mi:ss')
|
|
AND tijdsblok >= 8
|
|
AND tijdsblok <= 17
|
|
UNION ALL
|
|
SELECT locatie,
|
|
aanmaak,
|
|
'99' tijdsblok,
|
|
reden_bezoek,
|
|
soort_client,
|
|
afdeling,
|
|
afhandeling,
|
|
kontakt_kcc,
|
|
klant_geholpen,
|
|
bsn_nummer,
|
|
melding
|
|
FROM uwva_v_spobilo
|
|
WHERE aanmaak BETWEEN TO_DATE ('10-11-2017 20:05:00', 'dd-mm-yyyy hh24:mi:ss') - 7 AND TO_DATE ('10-11-2017 20:05:00', 'dd-mm-yyyy hh24:mi:ss')
|
|
AND (tijdsblok < 8 OR tijdsblok > 17));
|
|
|
|
COMMIT;
|
|
|
|
------ payload end ------
|
|
|
|
SET DEFINE OFF
|
|
BEGIN adm.systrackscriptId ('$Id$'); END;
|
|
/
|
|
|
|
COMMIT;
|
|
SET ECHO OFF
|
|
SPOOL OFF
|
|
SET DEFINE ON
|
|
PROMPT Logfile of this upgrade is: &fcltlogfile
|