Rem
Rem $Header: eme102.sql 04-apr-2007.20:18:36 chyu Exp $
Rem
Rem eme102.sql
Rem
Rem Copyright (c) 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      eme102.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/21/07 - lrg problem 2893924
Rem    gsbhatia    03/21/07 - 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

-- Mark the dbms_registry for EM component as downgraded.
DECLARE
  l_comp_name VARCHAR2(256);
  l_comp_ver  VARCHAR2(16);
BEGIN
  l_comp_name := 'EM';
  l_comp_ver  := '10.2.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;