update wmsys.wm$env_vars set value = '9.2.0.2.0' where name = 'OWM_VERSION';
commit;
create table wmsys.wm$constraints_table (
 owner                    varchar2(30),
 constraint_name          varchar2(30),
 constraint_type          varchar2(2),
 table_name               varchar2(30),
 search_condition         long,
 status                   varchar2(8),
 index_owner              varchar2(30),
 index_name               varchar2(30),
 index_type               varchar2(40),
 aliasedColumns           clob,
 numIndexCols             integer,
 constraint wm$constraints_table_pk primary key (owner, constraint_name)
) ;
create index wmsys.wm$constraints_table_tab_idx on wmsys.wm$constraints_table (owner, table_name) ;
create or replace view wmsys.user_wm_constraints as
  select /*+ ORDERED */ 
   constraint_name, constraint_type, table_name, 
   search_condition, status, index_owner, index_name, index_type
  from   wmsys.wm$constraints_table ct, user_views uv
  where  ct.owner = USER and
         ct.table_name = uv.view_name ; 
execute wmsys.wm$execSQL('grant select on wmsys.user_wm_constraints to public with grant option');
create public synonym user_wm_constraints for wmsys.user_wm_constraints;
create or replace view wmsys.all_wm_constraints as
  select /*+ ORDERED */ 
   ct.owner, constraint_name, constraint_type, table_name, 
   search_condition, status, index_owner, index_name, index_type
  from   wmsys.wm$constraints_table ct, all_views av
  where  ct.owner = av.owner and
         ct.table_name = av.view_name ; 
execute wmsys.wm$execSQL('grant select on wmsys.all_wm_constraints to public with grant option');
create public synonym all_wm_constraints for wmsys.all_wm_constraints ;
/*
 * ############################################
 * Insert the PK constraints info in the constraints table, because might
 * be coming from the upgrade path.
 * ############################################
 */
insert into wmsys.wm$constraints_table select t1.owner, t2.constraint_name, 'P', t1.table_name, null, 'ENABLED', t1.owner, t2.constraint_name, 'NORMAL', null, null from wmsys.wm$versioned_tables t1, dba_constraints t2 where t1.owner = t2.owner and t1.table_name || '_LT' = t2.table_name and t2.constraint_type = 'P'  ;
commit;
create or replace view wmsys.user_wm_ind_columns as
select /*+ ORDERED */ t2.index_name, t1.table_name, t2.column_name, t2.column_position, 
t2.column_length, t2.descend
from wmsys.wm$constraints_table t1, user_ind_columns t2
where t1.index_owner = USER 
and t1.index_name = t2.index_name 
and t1.constraint_type != 'P'
union
select /*+ ORDERED */ t2.index_name, t1.table_name, t2.column_name, t2.column_position-1, 
t2.column_length, t2.descend
from wmsys.wm$constraints_table t1, user_ind_columns t2
where t1.index_owner = USER 
and t1.index_name = t2.index_name 
and t1.constraint_type = 'P'
and t2.column_name not in ('VERSION','DELSTATUS') ;
execute wmsys.wm$execSQL('grant select on wmsys.user_wm_ind_columns to public with grant option');
create public synonym user_wm_ind_columns for wmsys.user_wm_ind_columns;
create or replace view wmsys.all_wm_ind_columns as
select /*+ USE_NL(t1 t2) */ t2.index_owner, t2.index_name, t1.owner, t1.table_name, t2.column_name, 
t2.column_position, t2.column_length,  t2.descend
from wmsys.wm$constraints_table t1, all_ind_columns t2
where t1.index_owner = t2.index_owner
and t1.index_name = t2.index_name 
and t1.constraint_type != 'P'
union
select /*+ USE_NL(t1 t2) */ t2.index_owner, t2.index_name, t1.owner, t1.table_name, t2.column_name, 
t2.column_position-1, t2.column_length, t2.descend
from wmsys.wm$constraints_table t1, all_ind_columns t2
where t1.index_owner = t2.index_owner
and t1.index_name = t2.index_name 
and t1.constraint_type = 'P'
and t2.column_name not in ('VERSION','DELSTATUS') ;
execute wmsys.wm$execSQL('grant select on wmsys.all_wm_ind_columns to public with grant option');
create public synonym all_wm_ind_columns for wmsys.all_wm_ind_columns;
create or replace view wmsys.user_wm_ind_expressions as
select /*+ ORDERED */ t2.index_name, t1.table_name, t2.column_expression, t2.column_position
from wmsys.wm$constraints_table t1, user_ind_expressions t2
where t1.index_owner = USER 
and t1.index_name = t2.index_name ;
execute wmsys.wm$execSQL('grant select on wmsys.user_wm_ind_expressions to public with grant option') ;
create public synonym user_wm_ind_expressions for wmsys.user_wm_ind_expressions ;
create or replace view wmsys.all_wm_ind_expressions as
select /*+ USE_NL(t1 t2) */ t2.index_owner,t2.index_name, t1.owner, t1.table_name, t2.column_expression, t2.column_position
from wmsys.wm$constraints_table t1, all_ind_expressions t2
where t1.index_owner = t2.index_owner 
and t1.index_name = t2.index_name ;
execute wmsys.wm$execSQL('grant select on wmsys.all_wm_ind_expressions to public with grant option') ;
create public synonym all_wm_ind_expressions for wmsys.all_wm_ind_expressions ;
create or replace view wmsys.wm$conf1_hierarchy_view as
  select * from wmsys.wm$version_hierarchy_table 
  start with version = (select current_version from wmsys.wm$workspaces_table 
                        where workspace = sys_context('lt_ctx', 'conflict_state'))
  connect by prior parent_version = version
WITH READ ONLY;
create or replace view wmsys.wm$conf2_hierarchy_view as
  select * from wmsys.wm$version_hierarchy_table 
  start with version = (select current_version from wmsys.wm$workspaces_table 
                        where workspace = sys_context('lt_ctx', 'parent_conflict_state'))
  connect by prior parent_version = version
WITH READ ONLY;
create or replace view wmsys.wm$conf_base_hierarchy_view as
  select version from wmsys.wm$version_hierarchy_table 
  start with version = sys_context('lt_ctx', 'confbasever')
  connect by prior parent_version  = version
WITH READ ONLY;
create or replace view wmsys.wm$conf1_nextver_view as
  select next_vers from wmsys.wm$nextver_table 
  where version in 
  (select version from wmsys.wm$conf1_hierarchy_view)
WITH READ ONLY;
create or replace view wmsys.wm$conf2_nextver_view as
  select next_vers from wmsys.wm$nextver_table 
  where version in 
  (select version from wmsys.wm$conf2_hierarchy_view)
WITH READ ONLY;
create or replace view wmsys.wm$conf_base_nextver_view as
  select next_vers from wmsys.wm$nextver_table
  where version in
  (select version from wmsys.wm$conf_base_hierarchy_view)
WITH READ ONLY;
create public synonym wm$conf1_hierarchy_view for wmsys.wm$conf1_hierarchy_view ;
create public synonym wm$conf2_hierarchy_view for wmsys.wm$conf2_hierarchy_view ;
create public synonym wm$conf_base_hierarchy_view for wmsys.wm$conf_base_hierarchy_view ;
create public synonym wm$conf1_nextver_view for wmsys.wm$conf1_nextver_view ;
create public synonym wm$conf2_nextver_view for wmsys.wm$conf2_nextver_view ;
create public synonym wm$conf_base_nextver_view for wmsys.wm$conf_base_nextver_view ;
execute wmsys.wm$execSQL('grant select on wmsys.wm$conf1_hierarchy_view to public with grant option');
execute wmsys.wm$execSQL('grant select on wmsys.wm$conf2_hierarchy_view to public with grant option');
execute wmsys.wm$execSQL('grant select on wmsys.wm$conf_base_hierarchy_view to public with grant option');
execute wmsys.wm$execSQL('grant select on wmsys.wm$conf1_nextver_view to public with grant option');
execute wmsys.wm$execSQL('grant select on wmsys.wm$conf2_nextver_view to public with grant option');
execute wmsys.wm$execSQL('grant select on wmsys.wm$conf_base_nextver_view to public with grant option');
create or replace view wmsys.wm$all_nextver_view as
  select version, next_vers, workspace, split
  from wmsys.wm$nextver_table 
WITH READ ONLY;
create public synonym wm$all_nextver_view for wmsys.wm$all_nextver_view ;
execute wmsys.wm$execSQL('grant select on wmsys.wm$all_nextver_view to public with grant option');
create table wmsys.wm$cons_columns (
owner                          varchar2(30),
constraint_name                varchar2(30),
table_name                     varchar2(30),
column_name                    varchar2(4000),
position                       number
) ;
create index wmsys.wm$cons_columns_idx on wmsys.wm$cons_columns(owner, constraint_name) ;
create or replace view wmsys.user_wm_cons_columns as
select /*+ ORDERED */ t1.* from 
wmsys.wm$cons_columns t1, user_views t2
where t1.owner = USER
and t1.table_name = t2.view_name;
execute wmsys.wm$execSQL('grant select on wmsys.user_wm_cons_columns to public with grant option');
create public synonym user_wm_cons_columns for wmsys.user_wm_cons_columns;
create or replace view wmsys.all_wm_cons_columns as
select /*+ ORDERED */ t1.* from 
wmsys.wm$cons_columns t1, all_views t2
where t1.owner = t2.owner
and t1.table_name = t2.view_name;
execute wmsys.wm$execSQL('grant select on wmsys.all_wm_cons_columns to public with grant option');
create public synonym all_wm_cons_columns for wmsys.all_wm_cons_columns;
drop sequence wmsys.wm$nested_columns_seq ;
alter table wmsys.wm$versioned_tables add ( bl_workspace varchar2(30), bl_version integer );
create index wmsys.wm$ver_tab_bl_indx on wmsys.wm$versioned_tables(bl_workspace,bl_version);
/* We had a bug wherein the latest versions in diff worskapces
 * were being cached in sys_context variables. So, when a 
 * savepoint was created in that workspace, this sys_context was 
 * becoming stale and incorrect. So, we are changing it so that
 * the sys_context stores a logical latest version.
 */
create or replace view wmsys.wm$diff1_hierarchy_view as
  select * from wmsys.wm$version_hierarchy_table 
  start with version = 
             decode(sys_context('lt_ctx', 'diffver1'), -1,
             (select current_version from wmsys.wm$workspaces_table
              where workspace = sys_context('lt_ctx', 'diffWspc1')),
             sys_context('lt_ctx', 'diffver1'))
  connect by prior parent_version = version
WITH READ ONLY;
create or replace view wmsys.wm$diff2_hierarchy_view as
  select version from wmsys.wm$version_hierarchy_table 
  start with version = 
             decode(sys_context('lt_ctx', 'diffver2'), -1,
             (select current_version from wmsys.wm$workspaces_table
              where workspace = sys_context('lt_ctx', 'diffWspc2')),
             sys_context('lt_ctx', 'diffver2'))
  connect by prior parent_version  = version
WITH READ ONLY;
declare
 curTrigStatus varchar2(10) := null;
 verTabName varchar2(61);
 cursor verTabsCur is 
   select owner || '.' || table_name from wmsys.wm$versioned_tables;
 badtab_exception EXCEPTION;
 PRAGMA EXCEPTION_INIT(badtab_exception, -00942);
 column_exists_exception EXCEPTION;
 PRAGMA EXCEPTION_INIT(column_exists_exception, -01430);
begin

  BEGIN
   select substr(status,1,length(status)-1) into curTrigStatus
   from all_triggers 
   where owner = 'SYS' and trigger_name = 'NO_VM_DROP';

   execute immediate 'alter trigger sys.no_vm_drop disable';

  EXCEPTION WHEN NO_DATA_FOUND THEN
    NULL;
  END;
  
  BEGIN

    open verTabsCur;
    loop
  
      fetch verTabsCur into verTabName;
      EXIT when verTabsCur%NOTFOUND;
      
      
      BEGIN
        execute immediate 'drop view ' || verTabName || '_DIF1';
      EXCEPTION WHEN badtab_exception THEN
        NULL;
      END;
  
      BEGIN
        execute immediate 'drop view ' || verTabName || '_DIF2';
      EXCEPTION WHEN badtab_exception THEN
        NULL;
      END;
      
      
      BEGIN
        execute immediate 'alter table ' || verTabName || '_AUX add (wm_opcode varchar2(3))';
      EXCEPTION WHEN column_exists_exception THEN
        NULL;
      END;
  
    end loop;
    close verTabsCur;

  EXCEPTION WHEN OTHERS THEN
    if (curTrigStatus is not null) then
      execute immediate 'alter trigger sys.no_vm_drop ' || curTrigStatus;
    end if;
    RAISE;
  END;
  if (curTrigStatus is not null) then
    execute immediate 'alter trigger sys.no_vm_drop ' || curTrigStatus;
  end if;

end;
/
/*
 * Revisit these view and indexes as might have been created
 * by others
 */
create or replace view wmsys.wm$current_child_versions_view as
select vht.version
from wmsys.wm$version_hierarchy_table vht, wmsys.wm$version_table vt
where 
(
   vht.workspace = vt.workspace and
   vt.anc_workspace = nvl(sys_context('lt_ctx','state'),'LIVE') and
   vt.anc_version   = decode(sys_context('lt_ctx','version'),
                              null,(SELECT current_version 
                                    FROM wmsys.wm$workspaces_table 
                                    WHERE workspace = 'LIVE'),
                              -1,(select current_version 
                                  from wmsys.wm$workspaces_table 
                                  where workspace = sys_context('lt_ctx','state')),
                              sys_context('lt_ctx','version')
                          )
) 
WITH READ ONLY ;
execute wmsys.wm$execSQL('grant select on wmsys.wm$current_child_versions_view to public with grant option');
create public synonym wm$current_child_versions_view for wmsys.wm$current_child_versions_view ;
create index wmsys.wm$vt_anc_idx on wmsys.wm$version_table(anc_workspace, anc_version);
create or replace view wmsys.wm$current_child_nextvers_view as
select nvt.next_vers 
from wmsys.wm$nextver_table nvt, wmsys.wm$version_table vt
where 
(
   nvt.workspace = vt.workspace and
   vt.anc_workspace = nvl(sys_context('lt_ctx','state'),'LIVE') and
   vt.anc_version   = decode(sys_context('lt_ctx','version'),
                              null,(SELECT current_version 
                                    FROM wmsys.wm$workspaces_table 
                                    WHERE workspace = 'LIVE'),
                              -1,(select current_version 
                                  from wmsys.wm$workspaces_table 
                                  where workspace = sys_context('lt_ctx','state')),
                              sys_context('lt_ctx','version')
                          )
) 
WITH READ ONLY ;
execute wmsys.wm$execSQL('grant select on wmsys.wm$current_child_nextvers_view to public with grant option');
create public synonym wm$current_child_nextvers_view for wmsys.wm$current_child_nextvers_view ;
/* Insert 0 version entry in nextver_table */
insert into wmsys.wm$nextver_table values(0,',0,','LIVE',0) ;
commit;
drop index wmsys.wm$nextver_table_nv_indx;
create unique index wmsys.wm$nextver_table_nv_indx on wmsys.wm$nextver_table(next_vers,version,workspace);
alter table wmsys.wm$workspaces_table add ( cr_status varchar2(20) );
alter table wmsys.wm$workspaces_table add ( sync_parver integer );
update wmsys.wm$workspaces_table wt
 set sync_parver = nvl( (select min(version) from wmsys.wm$version_hierarchy_table
      where version > wt.parent_version and workspace = wt.parent_workspace), wt.parent_version)
where wt.workspace != 'LIVE' ;
commit;
alter table wmsys.wm$workspaces_table add ( last_change date default sysdate);
declare
 cursor c1 is 
  select workspace, cr_status, rowid from wmsys.wm$workspaces_table;
 curWspc varchar2(30);
 curRID  ROWID;
 curCRStatus varchar2(20);
 newCRStatus varchar2(20);
 cursor childWspcCur is 
  select count(distinct isrefreshed), min(isrefreshed)
  from wmsys.wm$workspaces_table
  where parent_workspace = curWspc;
 cnt         integer;
 isRefreshed integer;
begin
 open c1;
 loop
   fetch c1 into curWspc, curCRStatus, curRID;
   EXIT when c1%NOTFOUND;
   if (curCRStatus is null) then
     open  childWspcCur;
     fetch childWspcCur into cnt, isRefreshed;

     if (cnt = 0) then
       newCRStatus := wmsys.lt_ctx_pkg.CRSTATUS_LEAF;
     elsif (cnt = 2) then
       newCRStatus := wmsys.lt_ctx_pkg.CRSTATUS_MIXED;
     else 
       if (isRefreshed = 0) then   
         newCRStatus := wmsys.lt_ctx_pkg.CRSTATUS_ALLNONCR;
       else 
         newCRStatus := wmsys.lt_ctx_pkg.CRSTATUS_ALLCR;
       end if;
     end if;
     update wmsys.wm$workspaces_table
       set cr_status = newCRStatus
     where rowid = curRID;
     close childWspcCur;
   end if;
 end loop;
 close c1;
 commit;
end;
/
create or replace view wmsys.wm$current_workspace_view as 
  select * from wmsys.wm$workspaces_table  
  where workspace = nvl(SYS_CONTEXT('lt_ctx','state'),'LIVE')
WITH READ ONLY;
create or replace view wmsys.wm$parent_workspace_view as 
  select * from wmsys.wm$workspaces_table  
  where workspace = SYS_CONTEXT('lt_ctx','parent_state')
WITH READ ONLY;
alter table wmsys.wm$workspaces_table add (depth integer) ;
alter table wmsys.wm$version_table add (anc_depth integer) ;
declare

  wspc   varchar2(30) ;
  cnt    integer ;
  rid    ROWID ;
  cursor all_workspaces is select workspace, rowid
                       from wmsys.wm$workspaces_table ;
begin

  open all_workspaces ;
  loop
    fetch all_workspaces into wspc, rid ;
    exit when all_workspaces%NOTFOUND ;

    select count(*) into cnt
    from wmsys.wm$version_table
    where workspace = wspc;

    update wmsys.wm$workspaces_table
    set depth = cnt
    where rowid = rid ;

    update wmsys.wm$version_table
    set anc_depth = cnt
    where anc_workspace = wspc ;

  end loop;
  close all_workspaces ;
end;
/
create or replace view wmsys.wm$base_version_view as
select decode(sign(vt1.anc_version - vt2.anc_version),
              1, vt2.anc_version, vt1.anc_version) version,
       decode(sys_context('lt_ctx', 'isAncestor'), 'false','NO',
              decode(decode(sign(vt1.anc_version - vt2.anc_version),
                     1, vt2.anc_version, vt1.anc_version), 
                     wmt.current_version, 'YES', 'NO')) isCRAnc
from (select vt1.anc_version 
      from wmsys.wm$version_table vt1
      where vt1.workspace = sys_context('lt_ctx', 'diffWspc1') and
            vt1.anc_workspace = sys_context('lt_ctx', 'anc_workspace')
      union all 
      select decode(sys_context('lt_ctx', 'diffver1'),
                    -1, decode(sys_context('lt_ctx','isCRChild'), 'true',
                               (select sync_parver
                                from wmsys.wm$workspaces_table
                                where workspace = sys_context('lt_ctx', 'diffWspc2')),
                                (select current_version
                                from wmsys.wm$workspaces_table
                                where workspace = sys_context('lt_ctx', 'diffWspc1'))),
                     sys_context('lt_ctx', 'diffver1'))
      from dual where sys_context('lt_ctx', 'anc_workspace') = 
                      sys_context('lt_ctx', 'diffWspc1')
      ) vt1,
      (select vt2.anc_version
       from wmsys.wm$version_table vt2
       where vt2.workspace = sys_context('lt_ctx', 'diffWspc2') and
             vt2.anc_workspace = sys_context('lt_ctx', 'anc_workspace')
       union all 
       select decode(sys_context('lt_ctx', 'diffver2'),
                    -1, decode(sys_context('lt_ctx','isCRChild'), 'true',
                               (select sync_parver
                                from wmsys.wm$workspaces_table
                                where workspace = sys_context('lt_ctx', 'diffWspc1')),
                                (select current_version
                                from wmsys.wm$workspaces_table
                                where workspace = sys_context('lt_ctx', 'diffWspc2'))),
                       sys_context('lt_ctx', 'diffver2'))
       from dual where sys_context('lt_ctx', 'anc_workspace') = 
                       sys_context('lt_ctx', 'diffWspc2')
      ) vt2,
      wmsys.wm$workspaces_table wmt
where wmt.workspace = sys_context('lt_ctx', 'anc_workspace');
execute wmsys.wm$execSQL('grant select on wmsys.wm$base_version_view to public with grant option');
create public synonym wm$base_version_view for wmsys.wm$base_version_view ;
create or replace view wmsys.wm$base_hierarchy_view as
  select -1 version from dual union all
  select version from wmsys.wm$version_hierarchy_table 
  start with version = (select version from wmsys.wm$base_version_view)
  connect by prior parent_version  = version
WITH READ ONLY;
create or replace view wmsys.wm$curConflict_parvers_view (parent_vers, vtid) as 
  select version, vtid 
  from wmsys.wm$modified_tables
  where workspace = SYS_CONTEXT('lt_ctx','conflict_state')
WITH READ ONLY;
create or replace view wmsys.wm$curConflict_nextvers_view as 
select version, next_vers, workspace, split, cpv.vtid
from wmsys.wm$nextver_table nt, wmsys.wm$curConflict_parvers_view cpv
where nt.version = cpv.parent_vers
WITH READ ONLY;
create or replace view wmsys.wm$parConflict_parvers_view (parent_vers, vtid, afterSync) 
as 
 (select version, vtid,  decode(sign(mt.version - wt.sync_parver), -1, 'NO','YES') 
  from wmsys.wm$modified_tables mt, wmsys.wm$workspaces_table wt 
  where mt.workspace = SYS_CONTEXT('lt_ctx','parent_conflict_state') and
        wt.workspace = SYS_CONTEXT('lt_ctx','conflict_state')
        and mt.version >= decode(sign(wt.parent_version - wt.sync_parver),-1,
                                 (wt.parent_version+1), sync_parver)
 )
WITH READ ONLY;
create or replace view wmsys.wm$parConflict_nextvers_view as 
select version, next_vers, workspace, split, ppv.vtid, ppv.afterSync
from wmsys.wm$nextver_table nt, wmsys.wm$parConflict_parvers_view ppv
where nt.version = ppv.parent_vers
WITH READ ONLY;
execute wmsys.wm$execSQL('grant select on wmsys.wm$version_hierarchy_table to public');
execute wmsys.wm$execSQL('grant select on wmsys.wm$version_table to public with grant option');
execute wmsys.wm$execSQL('grant select on wmsys.wm$nextver_table to public');
create or replace view wmsys.wm$mw_versions_view as
select distinct version, modified_by from 
(
select vht.version, vht.workspace modified_by from
wmsys.wm$mw_table mw, wmsys.wm$version_table vt, wmsys.wm$version_hierarchy_table vht 
where mw.workspace = vt.workspace
and vt.anc_workspace = vht.workspace 
and vht.version <= vt.anc_version
union all
select vht.version, vht.workspace modified_by from
wmsys.wm$mw_table mw, wmsys.wm$version_hierarchy_table vht
where mw.workspace = vht.workspace  
);
create or replace view wmsys.wm$mw_nextvers_view as
select nvt.next_vers
from wmsys.wm$nextver_table  nvt
where 
nvt.workspace in (select workspace from wmsys.wm$mw_table)
or 
exists
 ( select 1 from wmsys.wm$version_table vt
                    where vt.workspace in (select workspace from wmsys.wm$mw_table) and
                          nvt.workspace = vt.anc_workspace and
                          nvt.version  <= vt.anc_version ) ;
create public synonym wm$mw_versions_view for wmsys.wm$mw_versions_view;
create public synonym wm$mw_nextvers_view for wmsys.wm$mw_nextvers_view;
execute wmsys.wm$execSQL('grant select on wmsys.wm$mw_versions_view to public with grant option');
execute wmsys.wm$execSQL('grant select on wmsys.wm$mw_nextvers_view to public with grant option');
create table wmsys.wm$sysparam_all_values (
name varchar2(100),
value varchar2(512),
IsDefault varchar2(9),
constraint wm$env_sys_pk primary key(name, value) 
) ;
declare
  pess  varchar2(30) := WMSYS.LT.PESSIMISTIC_LOCKING;
  opt   varchar2(30) := WMSYS.LT.OPTIMISTIC_LOCKING;
begin
  insert into wmsys.wm$sysparam_all_values values ('CR_WORKSPACE_MODE',pess,'NO') ;
  insert into wmsys.wm$sysparam_all_values values ('CR_WORKSPACE_MODE',opt,'YES') ;
  insert into wmsys.wm$sysparam_all_values values ('NONCR_WORKSPACE_MODE', pess, 'NO') ;
  insert into wmsys.wm$sysparam_all_values values ('NONCR_WORKSPACE_MODE', opt,  'YES') ;
  insert into wmsys.wm$sysparam_all_values values ('FIRE_TRIGGERS_FOR_NONDML_EVENTS', 'ON', 'YES');
  insert into wmsys.wm$sysparam_all_values values ('FIRE_TRIGGERS_FOR_NONDML_EVENTS', 'OFF', 'NO');
  commit ;
end;
/
declare
  v varchar2(40);
begin
  select value into v
  from wmsys.wm$env_vars
  where name = 'CR_WORKSPACE_MODE';

exception when no_data_found then
  insert into wmsys.wm$env_vars values('CR_WORKSPACE_MODE', WMSYS.LT.PESSIMISTIC_LOCKING) ;
  commit;
end;
/
alter table wmsys.wm$udtrig_info add (
  internal_type varchar2(50) default 'USER_DEFINED',
  tab_merge_wo_remove_col  varchar2(4) default 'ON', 
  tab_merge_w_remove_col   varchar2(4) default 'ON',
  wspc_merge_wo_remove_col varchar2(4) default 'ON',
  wspc_merge_w_remove_col  varchar2(4) default 'ON',
  dml_col                  varchar2(4) default 'ON',
  workspace_refresh_col    varchar2(4) default 'ON',
  table_refresh_col        varchar2(4) default 'ON',
  table_rollback_col       varchar2(4) default 'ON',
  workspace_rollback_col   varchar2(4) default 'ON',
  workspace_remove_col     varchar2(4) default 'ON'
); 
alter table wmsys.wm$workspaces_table add (mp_root varchar2(30) default null);
create index wmsys.wm$workspaces_mp_idx on wmsys.wm$workspaces_table(mp_root);
alter table wmsys.wm$version_table add (refCount integer default 1);
create table wmsys.wm$mp_parent_workspaces_table (
workspace varchar2(30), 
parent_workspace varchar2(30),
parent_version integer,
creator varchar2(30),
createtime date,
workspace_lock_id integer,
isRefreshed integer,
parent_flag varchar2(2),
constraint wm$mp_parent_pk primary key(workspace, parent_workspace)
) ;
create index wmsys.wm$mp_pws_tab_pws_ind on wmsys.wm$mp_parent_workspaces_table (parent_workspace);
create index wmsys.wm$mp_pws_tab_pver_ind on wmsys.wm$mp_parent_workspaces_table (parent_version);
create table wmsys.wm$mp_graph_workspaces_table (
  mp_leaf_workspace   varchar2(30),
  mp_graph_workspace  varchar2(30),
  anc_version         integer,
  mp_graph_flag       varchar2(1),
  constraint wm$mp_graph_workspaces_pk primary key (mp_leaf_workspace,mp_graph_workspace)
) ;
create index wmsys.wm$mp_graph_workspace_idx on wmsys.wm$mp_graph_workspaces_table(mp_graph_workspace) ;
/*
 * For DML, post, commit, and refreshtable ops, the new data goes to 
 * the current_version. The new data should be checked against the data
 * in the mp graph(s) only if the current_version belongs to CR workspace.
 * But for RefreshWorkspace operation, the new data is seen by current_mp_leafs,
 * and hence should be checked for conflicts against graph versions.
 */ 
create or replace view wmsys.wm$mp_graph_cons_versions as
select vht.version, vht.workspace
from wmsys.wm$mp_graph_workspaces_table mpg, wmsys.wm$version_hierarchy_table vht
where instr(SYS_CONTEXT('lt_ctx','current_mp_leafs'), mpg.mp_leaf_workspace) > 0
and   mpg.mp_graph_flag = 'I'
and   vht.workspace = mpg.mp_graph_workspace 
and   vht.version <= mpg.anc_version
and   ( 
        ( nvl(sys_context('lt_ctx','rowlock_status'),'X') = 'F' and nvl(sys_context('lt_ctx','flip_version'),'N') = 'Y' )
        OR
        ( nvl(sys_context('lt_ctx','isrefreshed'),'0') = '1' )
      )      
WITH READ ONLY;
create or replace view wmsys.wm$current_cons_versions_view
as
 select version from wmsys.wm$current_child_versions_view
 union all
 select parent_vers from wmsys.wm$current_parvers_view 
 union all
 select version from wmsys.wm$mp_graph_cons_versions
 union all
 select version from wmsys.wm$version_hierarchy_table
 where workspace in (
   select workspace from wmsys.wm$version_table
   where anc_workspace = sys_context('lt_ctx','state')
 )
 and  ( nvl(sys_context('lt_ctx','rowlock_status'),'X') = 'F' and nvl(sys_context('lt_ctx','flip_version'),'N') = 'Y' ) 
WITH READ ONLY ;
execute wmsys.wm$execSQL('grant select on wmsys.wm$current_cons_versions_view to public with grant option');
create public synonym wm$current_cons_versions_view for wmsys.wm$current_cons_versions_view ;
/*
 * The versions in the current workspace should be included in the nextver
 * list only if you are not doing refresh workspace by flipping.
 */
create or replace view wmsys.wm$current_cons_nextvers_view as
select /*+ INDEX(nvt WM$NEXTVER_TABLE_NV_INDX) */ nvt.next_vers 
             from wmsys.wm$nextver_table nvt
where 
(
 (
   nvt.workspace = nvl(sys_context('lt_ctx','state'),'LIVE') 
   and nvt.version   <=   decode(sys_context('lt_ctx','version'),
                       null,(SELECT current_version 
                               FROM wmsys.wm$workspaces_table 
                               WHERE workspace = 'LIVE'),
                       -1,(select current_version 
                           from wmsys.wm$workspaces_table 
                           where workspace = sys_context('lt_ctx','state')),
                           sys_context('lt_ctx','version')
                          )
   and not ( nvl(sys_context('lt_ctx','rowlock_status'),'X') = 'F' and nvl(sys_context('lt_ctx','flip_version'),'N') = 'Y' )
 )
 or 
 ( exists ( select 1 from wmsys.wm$version_table vt
                    where vt.workspace  = nvl(sys_context('lt_ctx','state'),'LIVE')   and
                          nvt.workspace = vt.anc_workspace and
                          nvt.version  <= vt.anc_version )
 )
) 
WITH READ ONLY;
execute wmsys.wm$execSQL('grant select on wmsys.wm$current_cons_nextvers_view to public with grant option');
create public synonym wm$current_cons_nextvers_view for wmsys.wm$current_cons_nextvers_view ;
/* This view generates all the new versions being added by call to 
 * AddAsParentWorkspace to the mp-graph. It computes:
 * 1. the parent versions of the leaf-workspace in the graph branch being added and 
 * 2. for root workspace, any version before the anc_version 
 */
create or replace view wmsys.wm$mp_graph_new_versions as
select vht.version, vht.workspace
from wmsys.wm$version_hierarchy_table vht, wmsys.wm$version_table vt
where vt.workspace = sys_context('lt_ctx','new_mp_leaf') 
and vht.workspace = vt.anc_workspace
and vht.version <= vt.anc_version
and (vt.refCount < 0 or ( vht.workspace = sys_context('lt_ctx','new_mp_root')
                          and vht.version > sys_context('lt_ctx','old_root_anc_version') )
    ) 
WITH READ ONLY;
/*
 * This view computes all the existing versions that were there is the
 * graph before a parent-workspace was added. It includes:
 * 1. Versions in the sibling branches of the mp-graph
 * 2. Versions in the root workspace before the anc_version
 * 3. All versions in the leaf-workspace
 * 4. Child-versions of the leaf-workspace
 */
create or replace view wmsys.wm$mp_graph_other_versions as
select vht.version, vht.workspace
from wmsys.wm$version_hierarchy_table vht, wmsys.wm$version_table vt
where
(vt.workspace = sys_context('lt_ctx','new_mp_leaf') 
 and vht.workspace = vt.anc_workspace
 and vht.version <= vt.anc_version
 and vt.refCount > 0
) or
(
 (vt.anc_workspace = sys_context('lt_ctx','new_mp_leaf')
  and vht.workspace = vt.workspace
 )
) 
union all
select vht.version, vht.workspace
from wmsys.wm$version_hierarchy_table vht
where vht.workspace = sys_context('lt_ctx','new_mp_leaf') 
union all
select version, workspace
from wmsys.wm$mp_graph_cons_versions
WITH READ ONLY;
create or replace view wmsys.user_wm_tab_triggers 
(
  trigger_name,
  table_owner,
  table_name,
  trigger_type,
  status,
  when_clause,
  description,
  trigger_body,
  TAB_MERGE_WO_REMOVE,
  TAB_MERGE_W_REMOVE,
  WSPC_MERGE_WO_REMOVE,
  WSPC_MERGE_W_REMOVE,
  DML,          
  WORKSPACE_REFRESH,
  TABLE_REFRESH,
  TABLE_ROLLBACK,
  WORKSPACE_ROLLBACK,
  WORKSPACE_REMOVE
)
as 
select trig_name,
       table_owner_name,
       table_name,
       trig_type,
       status,
       when_clause,
       description,
       trig_code,       
       TAB_MERGE_WO_REMOVE_COL,
       TAB_MERGE_W_REMOVE_COL,
       WSPC_MERGE_WO_REMOVE_COL,
       WSPC_MERGE_W_REMOVE_COL,
       DML_COL,          
       WORKSPACE_REFRESH_COL,
       TABLE_REFRESH_COL,
       TABLE_ROLLBACK_COL,
       WORKSPACE_ROLLBACK_COL,
       WORKSPACE_REMOVE_COL
from   wmsys.wm$udtrig_info
where  trig_owner_name = USER
with READ ONLY;
create or replace view wmsys.all_wm_tab_triggers 
(
  trigger_owner,
  trigger_name,
  table_owner,
  table_name,
  trigger_type,
  status,
  when_clause,
  description,
  trigger_body,  
  TAB_MERGE_WO_REMOVE,
  TAB_MERGE_W_REMOVE,
  WSPC_MERGE_WO_REMOVE,
  WSPC_MERGE_W_REMOVE,
  DML,          
  WORKSPACE_REFRESH,
  TABLE_REFRESH,
  TABLE_ROLLBACK,
  WORKSPACE_ROLLBACK,
  WORKSPACE_REMOVE
)
as 
(select trig_owner_name, 
        trig_name,
        table_owner_name,
        table_name,
        trig_type,
        status,
        when_clause,
        description,
        trig_code,       
        TAB_MERGE_WO_REMOVE_COL,
        TAB_MERGE_W_REMOVE_COL,
        WSPC_MERGE_WO_REMOVE_COL,
        WSPC_MERGE_W_REMOVE_COL,
        DML_COL,          
        WORKSPACE_REFRESH_COL,
        TABLE_REFRESH_COL,
        TABLE_ROLLBACK_COL,
        WORKSPACE_ROLLBACK_COL,
        WORKSPACE_REMOVE_COL
 from   wmsys.wm$udtrig_info
 where  trig_owner_name = USER or
        table_owner_name = USER or
        EXISTS  
        ( select * 
          from   user_sys_privs
          where  privilege = 'CREATE ANY TRIGGER' ) )
with READ ONLY;
execute wmsys.wm$execSQL('grant select on wmsys.user_wm_tab_triggers to public with grant option');
create or replace public synonym user_wm_tab_triggers for wmsys.user_wm_tab_triggers; 
execute wmsys.wm$execSQL('grant select on wmsys.all_wm_tab_triggers to public with grant option');
create or replace public synonym all_wm_tab_triggers for wmsys.all_wm_tab_triggers; 
create table wmsys.wm$ric_locking_table( pt_owner varchar2(30), pt_name varchar2(30), slockNo integer, elockNo integer );
execute wmsys.wm$execSQL('grant select on wmsys.wm$ric_locking_table to sys');
alter table wmsys.wm$workspaces_table add constraint workspace_lock_id_unq unique(workspace_lock_id);
create or replace view wmsys.wm$anc_version_view as 
         select vht1.version, vht2.version parent_vers, vht1.workspace from 
           wmsys.wm$version_hierarchy_table vht1, wmsys.wm$version_hierarchy_table vht2,
           wmsys.wm$version_table vt
          where (vht1.workspace = vt.workspace and
                 vht2.workspace = vt.anc_workspace and
                 vht2.version  <= vt.anc_version)
WITH READ ONLY;
execute wmsys.wm$execSQL('grant select on wmsys.wm$anc_version_view to sys');
declare
  type_name_var   varchar2(100);
  sql_string      varchar2(32000);

  cursor adt_func_cur is 
    select func_name, type_name 
    from wmsys.wm$adt_func_table;

begin
  for adt_rec in adt_func_cur loop

    if (substr(upper(adt_rec.type_name), 1, 7) = 'REF TO ') then 
      type_name_var := replace(upper(adt_rec.type_name), 'REF TO ', 'REF ');
    else
      type_name_var := adt_rec.type_name ;
    end if;  

    sql_string := 'create or replace function OVMADT' || adt_rec.func_name || ' return ' || type_name_var || ' is
                   begin
                     return null;
                   end;';

    execute immediate sql_string;

    sql_string := 'grant execute on OVMADT' || adt_rec.func_name || ' to public with grant option';
    execute immediate sql_string;
  end loop;
end;
/
/* For self ric, the parent table name has _LT in the end -
 * fix that
 */
update wmsys.wm$ric_triggers_table set pt_name = substr(pt_name,1,instr(pt_name,'_LT',-1,1)-1)
 where ( instr(pt_name,'_LT',-1,1) + 2 = length(pt_name) )
 and length(pt_name) > 3
 and pt_owner = ct_owner and substr(pt_name,1,instr(pt_name,'_LT',-1,1)-1) = ct_name  ; 
commit ;
/* Some previous release stored the trig_names as lower
 * case, change them to upper
 */
update wmsys.wm$ric_triggers_table 
 set update_trigger_name = upper(update_trigger_name), 
     delete_trigger_name = upper(delete_trigger_name) ;
@@owmv922.plb