SET HEADING OFF
SET TERMOUT ON
SET FEEDBACK OFF
SET ECHO OFF
SET PAGESIZE 0
column fcltlogfile new_value fcltlogfile NOPRINT;
select 'DB18Sensor' || '_' || UPPER(USER || '_' || sys_context('USERENV', 'SERVICE_NAME') || '_' || sys_context('USERENV', 'SERVER_HOST')) || '_' || to_char(sysdate,'YYYYMMDD_HH24MISS') || '.lst' as fcltlogfile from dual;
spool &fcltlogfile
PROMPT
PROMPT ************************************************************************
PROMPT UPGRADE of Facilitor schema version DB18 $Revision$
PROMPT for preliminary sensor functionality of DB19. Will yield errors when upgrading
PROMPT to the DB19 final release. These then may be ignored.
PROMPT Copyright (c) 1996-2013 by SG|facilitor bv. All rights reserved.
PROMPT
SELECT 'The current version of Facilitor@' || USER || ' is: ' || fac.getdbversion()
  FROM DUAL;
PROMPT
SET DEFINE OFF
SET HEADING OFF
SET FEEDBACK ON
SET VERIFY OFF
SET LINESIZE 1000
SET TIME ON
SET SERVEROUTPUT ON SIZE UNLIMITED;
ALTER SESSION SET nls_territory='america';
PROMPT Dit script is gerund in user:
SHOW USER
SELECT 'De versie van Facilitor@' || USER || ' was ' || fac.getdbversion()
  FROM DUAL;
SET ECHO ON
COMMIT;

ALTER TABLE ins_deel ADD ins_deel_state VARCHAR2(128);
ALTER TABLE ins_deel ADD ins_deel_statedate DATE;
CREATE OR REPLACE FORCE VIEW cad_v_thema_ruimtesensor
(
    alg_ruimte_key,
    waarde
)
AS
    SELECT r.alg_ruimte_key alg_ruimte_key, initcap(d.ins_deel_state) status
      FROM alg_ruimte r, ins_deel d
     WHERE     r.alg_ruimte_verwijder IS NULL
           AND d.ins_deel_verwijder IS NULL
           AND d.ins_alg_ruimte_type = 'R'
           AND d.ins_alg_ruimte_key = r.alg_ruimte_key
           AND d.ins_deel_state IS NOT NULL;
CREATE OR REPLACE FORCE VIEW cad_v_thema_wpsensor
(
    prs_werkplek_key,
    waarde
)
AS
    SELECT wp.prs_werkplek_key prs_werkplek_key, initcap(d.ins_deel_state) status
      FROM prs_werkplek wp, ins_deel d
     WHERE     wp.prs_werkplek_verwijder IS NULL
           AND d.ins_deel_verwijder IS NULL
           AND wp.prs_werkplek_virtueel = 0
           AND d.ins_alg_ruimte_type = 'W'
           AND d.ins_alg_ruimte_key = wp.prs_werkplek_key
           AND d.ins_deel_state IS NOT NULL;

INSERT INTO fac_locale_xsl (fac_locale_xsl_module,
                            fac_locale_xsl_label,
                            fac_locale_xsl_lang,
                            fac_locale_xsl_tekst)
   VALUES( 'ASP',
          'lcl_deel_state',
          'NL',
          'Status');

BEGIN adm.systrackscript('$Workfile: DB18Sensor.sql $', '$Revision$', 1); END;
/
COMMIT;
BEGIN DBMS_UTILITY.COMPILE_SCHEMA(USER, FALSE); END;
/
SET ECHO OFF
SET HEADING OFF
SET LINESIZE 80
PROMPT List of all invalid objects after FACILITOR upgrade.
PROMPT Some errors may resolve by running the customer specific script next.
SELECT 'OR'||'A-'||'DB'||'19'||' Warning: ' || object_type || ' ' || object_name || ' is ' || status
  FROM user_objects
 WHERE object_type IN ('VIEW', 'TRIGGER', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'PACKAGE BODY')
   AND status <> 'VALID'
 ORDER BY object_name;
SET ECHO OFF
SET FEEDBACK OFF
PROMPT Logfile of this upgrade is: &fcltlogfile
SPOOL OFF
