Rem
Rem $Header: eme101.sql 04-apr-2007.20:18:35 chyu Exp $
Rem
Rem eme101.sql
Rem
Rem Copyright (c) 2004, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      eme101.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    chyu        04/04/07 - lrg 2908794: making sure em downgrade does not
Rem                           produce any error
Rem    gsbhatia    03/19/07 - lrg problem 2893924
Rem    kvsingh     02/26/07 - Setting EM_REPOS_MODE
Rem    chyu        01/23/07 - refering to emdowngrade.sql
Rem    chyu        10/25/06 - make downgrade a no-op for now
Rem    rpinnama    09/23/04 - 
Rem    rpinnama    08/26/04 - rpinnama_add_upgrade_downgrade_scripts
Rem    rpinnama    08/25/04 - Created
Rem

rem Parameters
define EMDW_HOME                       = "?"
define EM_REPOS_USER                   = "SYSMAN"
define EM_REPOS_MODE		       = "SYSAUX"
Rem derived variables
define EM_SQL_ROOT                     = "&EMDW_HOME/sysman/admin/emdrep/sql"

-- 
-- Get EM_REPOS_MODE
COLUMN :rep_mode NEW_VALUE EM_REPOS_MODE NOPRINT
VARIABLE rep_mode VARCHAR2(256)

DECLARE
  l_rep_mode VARCHAR2(28);
BEGIN
  BEGIN
    SELECT component_mode INTO l_rep_mode
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'CORE';
  EXCEPTION
    WHEN OTHERS THEN
      -- Default to CENTRAL mode
      l_rep_mode := 'CENTRAL';
  END;

  :rep_mode := l_rep_mode;
END;
/

SELECT :rep_mode FROM DUAL;

-- Contine on SQL errors
WHENEVER SQLERROR CONTINUE;


-- Mark the dbms_registry for EM component as downgrading.
DECLARE
  l_comp_name VARCHAR2(256);
BEGIN
  l_comp_name := 'EM';
  IF NOT ('&EM_REPOS_MODE' = 'CENTRAL') THEN
    EXECUTE IMMEDIATE 'BEGIN dbms_registry.downgrading(:1); END;' USING l_comp_name;
  END IF;
END;
/


-- -- 
-- Peform any SYS activity, like revoking privileges to EM_REPOS_USER


-- ALTER SESSION SET CURRENT_SCHEMA=&&EM_REPOS_USER;


-- Stop All DBMS jobs..
-- @&EM_SQL_ROOT/core/latest/admin/admin_remove_dbms_jobs.sql

-- @&EM_SQL_ROOT/emdowngrade.sql &&EMDW_HOME

-- Do data downgradeup

--
-- Downgrade OCS data (if EM_REPOS_MODE is CENTRAL)
-- COLUMN :script_name NEW_VALUE ocs_file NOPRINT
-- VARIABLE script_name VARCHAR2(256)
-- BEGIN
--   IF ('&EM_REPOS_MODE' = 'CENTRAL') THEN
--     :script_name := '&EM_SQL_ROOT/ocs/ocs_data_downgrade.sql';
--   ELSE
--     :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
--   END IF;
-- END;
-- /

-- SELECT :script_name FROM DUAL;

-- @&ocs_file


-- --
-- -- Downgrade iAS data (if EM_REPOS_MODE is CENTRAL)
-- COLUMN :script_name NEW_VALUE ias_file NOPRINT
-- VARIABLE script_name VARCHAR2(256)
-- BEGIN
--   IF ('&EM_REPOS_MODE' = 'CENTRAL') THEN
--     :script_name := '&EM_SQL_ROOT/ias/ias_data_downgrade.sql';
--   ELSE
--     :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
--   END IF;
-- END;
-- /

-- SELECT :script_name FROM DUAL;

-- @&ias_file

-- --
-- -- Downgrade DB data
-- @&EM_SQL_ROOT/db/db_data_downgrade.sql

-- --
-- -- Downgrade CORE data
-- @&EM_SQL_ROOT/core/core_data_downgrade.sql


-- -- Downgrade schema.

-- --
-- -- Downgrade OCS schema (only if EM_REPOS_MODE is CENTRAL)
-- -- 
-- COLUMN :script_name NEW_VALUE ocs_file NOPRINT
-- VARIABLE script_name VARCHAR2(256)
-- BEGIN
--   IF ('&EM_REPOS_MODE' = 'CENTRAL') THEN
--     :script_name := '&EM_SQL_ROOT/ocs/ocs_schema_downgrade.sql';
--   ELSE
--     :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
--   END IF;
-- END;
-- /

-- SELECT :script_name FROM DUAL;

-- @&ocs_file


-- --
-- -- Downgrade iAS schema (only if EM_REPOS_MODE is CENTRAL)
-- -- 
-- COLUMN :script_name NEW_VALUE ias_file NOPRINT
-- VARIABLE script_name VARCHAR2(256)
-- BEGIN
--   IF ('&EM_REPOS_MODE' = 'CENTRAL') THEN
--     :script_name := '&EM_SQL_ROOT/ias/ias_schema_downgrade.sql';
--   ELSE
--     :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
--   END IF;
-- END;
-- /

-- SELECT :script_name FROM DUAL;

-- @&ias_file



-- --
-- -- Downgrade DB data
-- @&EM_SQL_ROOT/db/db_schema_downgrade.sql

-- --
-- -- Downgrade CORE data
-- @&EM_SQL_ROOT/core/core_schema_downgrade.sql


-- Mark the dbms_registry for EM component as upgraded.
DECLARE
  l_comp_name VARCHAR2(256);
  l_comp_ver  VARCHAR2(16);
BEGIN
  l_comp_name := 'EM';
  l_comp_ver  := '10.1.0';
  IF NOT ('&EM_REPOS_MODE' = 'CENTRAL') THEN
    EXECUTE IMMEDIATE 'BEGIN dbms_registry.downgraded(:1, :2); END;' USING l_comp_name, l_comp_ver;
  END IF;
END;
/


-- Reset the session back to SYS
ALTER SESSION SET CURRENT_SCHEMA=SYS;