Rem
Rem $Header: db_schema_downgrade.sql 27-may-2008.15:02:13 chyu Exp $
Rem
Rem db_schema_downgrade.sql
Rem
Rem Copyright (c) 2006, 2008, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      db_schema_downgrade.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        05/27/08 - using the normalized version with 5 digits
Rem    kvsingh     02/26/07 - 
Rem    kdas        01/05/07 - fix for bug 5656244
Rem    ganessub    12/12/06 - Added TextManager downgrade call.
Rem    kvsingh     11/19/06 - Created
Rem


define CURR_VER                       = "&1"
define PREV_VER                       = "&2"

Rem Select downgrade script to run
Rem
COLUMN :script_name NEW_VALUE db_schema_downgrade NOPRINT
VARIABLE script_name VARCHAR2(256)
BEGIN
     IF '&CURR_VER' = '0.0.0.0.0'  OR
        '&PREV_VER' = '0.0.0.0.0'
     THEN
        :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
     ELSE
        :script_name := '&EM_SQL_ROOT/db/downgrade/db/' ||  'db_schema_downgrade' || '_' || '&CURR_VER' || '_' || '&PREV_VER' || '.sql';
    END IF;
END;
/
SELECT :script_name FROM DUAL;
@&db_schema_downgrade

COLUMN :hdm_script NEW_VALUE hdm_downgrade_file NOPRINT
VARIABLE hdm_script VARCHAR2(256)

DECLARE
  l_ver VARCHAR2(16) := '&PREV_VER';
BEGIN
  IF (substr(l_ver, 1, 3) = '10.') THEN
     -- Downgrading to 10.x
    :hdm_script := '&EM_SQL_ROOT/db/downgrade/hdm/hdm_schema_downgrade_11.1_10.x.sql';
  ELSE
    :hdm_script := '&EM_SQL_ROOT/db/downgrade/downgrade_nothing.sql';
  END IF;
END;
/

SELECT :hdm_script FROM DUAL;

@&hdm_downgrade_file

COLUMN :eml_script NEW_VALUE eml_downgrade_file NOPRINT
VARIABLE eml_script VARCHAR2(256)

DECLARE
  l_ver VARCHAR2(16) := '&PREV_VER';
BEGIN
  IF (substr(l_ver, 1, 4) = '10.1') THEN
     -- Downgrading to 10.1.x
    :eml_script := '&EM_SQL_ROOT/db/downgrade/eml/eml_schema_downgrade_10.1.sql';
  ELSE
    :eml_script := '&EM_SQL_ROOT/db/downgrade/downgrade_nothing.sql';
  END IF;
END;
/

SELECT :eml_script FROM DUAL;

@&eml_downgrade_file

execute emd_maintenance.set_version('DB', '&PREV_VER'||'.0', '&PREV_VER'||'.0', '&EM_REPOS_MODE');