Rem
Rem $Header: wfupartb.sql 26.1 2001/11/05 21:54:28 ctilley ship $
Rem
Rem wfupartb.sql
Rem
Rem  Copyright (c) Oracle Corporation 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      wfupartb.sql - This (Upgrade) script partitions the
Rem      WF tables by item type.
Rem
Rem    DESCRIPTION
Rem      This SQL script migrates existing non-partitioned WF
Rem      tables to partitioned tables (11.5).
Rem
Rem    NOTES
Rem
Rem     Partitioning the WF tables is supported only in release 11i
Rem     due to its dependence on the CBO and FND_STATS.
Rem
Rem     Customizing or altering the standard partitioning
Rem     schema of the WF tables is not supported.
Rem
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem
Rem    aalomari     11/14/1999 - Creation
Rem    dlam         12/04/1999 - Modified as a post-installed script
Rem
--
Rem   dbdrv: none
--
-- add this to exit at error
-- at the connection testing stage, we cannot use exit failure rollback
whenever sqlerror exit failure;

set serveroutput on

prompt Please review this script properly and ensure you have enough space
prompt in your tablespace.
prompt Usage:
prompt   sqlplus apps/apps @wfupartb <fnd_user> <fnd_passwd> <apps_user> <apps_passwd>

def u_fnd = &1
def p_fnd = &2
def u_app = &3
def p_app = &4

-- Testing connection to APPS
prompt Test connection to &u_app
connect &u_app/&p_app;

-- As APPLSYS
prompt Connecting to &u_fnd ...
connect &u_fnd/&p_fnd;

-- All connected properly, now we can proceed with package creation.
whenever sqlerror exit failure rollback;

-- Create Packages
prompt Creating WF_INSTALL package ...

create or replace package Wf_Install as
/* $Header: wfupartb.sql 26.1 2001/11/05 21:54:28 ctilley ship $ */
Procedure CreateTable (
  tblname    in varchar2,
  tblspcname in varchar2 default null
);

Procedure CreateIndex (
  idxname    in varchar2,
  tblspcname in varchar2 default null
);

-- objtype are like 'VIEW' and 'PACKAGE BODY'
-- objname can have wild card like 'WF%'
Procedure CompileObject (
  objtype    in varchar2,
  objname    in varchar2 default '%'
);

end Wf_Install;
/

create or replace package body Wf_Install as
/* $Header: wfupartb.sql 26.1 2001/11/05 21:54:28 ctilley ship $ */
Procedure CreateTable (
  tblname    in varchar2,
  tblspcname in varchar2 default null
)
is
  v_tablespace_name  varchar2(30);
  v_initial          number;
  v_next             number;
  v_pctinc           number;
  v_partitioned      varchar2(3);
  v_new_table        varchar2(30);
  v_old_table        varchar2(30);
  v_sql              varchar2(4000);
  v_dummy            varchar2(1);
begin
  begin
    select TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, PCT_INCREASE,
           PARTITIONED
    into   v_tablespace_name, v_initial, v_next, v_pctinc, v_partitioned
    from   USER_TABLES
    where  TABLE_NAME = tblname;
  exception
    when NO_DATA_FOUND then
      dbms_output.put_line('--Error while querying storage parameters.');
      dbms_output.put_line('-- Table '||tblname||' does not exist.');
      dbms_output.put_line('-- Please check if you login to the correct user, database,');
      dbms_output.put_line('-- and that application installation has been completed successfully.');
      raise_application_error(-20001,'Missing table '||tblname);

    when OTHERS then
      dbms_output.put_line('--Error while querying storage parameters.');
      raise;
  end;

  if (v_partitioned = 'YES') then
    -- already partitioned.  Just exit.
    dbms_output.put_line('Table '||tblname||' already partitioned ... skipped.');
    return;
  end if;

  if (tblspcname is not null) then
    v_tablespace_name := tblspcname;
  end if;

  v_new_table := substr('WFN_'||tblname, 1, 30);

  -- check if new table name exists, if so, may ask to drop it.
  begin
    select null into v_dummy from sys.dual where exists (
      select 1
      from   USER_OBJECTS
      where  OBJECT_NAME = v_new_table
    );
    dbms_output.put_line('Name conflict.  Please first drop '||v_new_table);
    raise_application_error(-20002, v_new_table||' already exists.');
  exception
    when NO_DATA_FOUND then
      null;
    when OTHERS then
      dbms_output.put_line('--Error while checking the new table name.');
      raise;
  end;

  dbms_output.put_line('Creating new table '||v_new_table);  
  v_sql :='create table '||v_new_table||
          ' pctfree 40'||
          ' pctused 60'||
          ' initrans 10'||
          ' tablespace '||v_tablespace_name||
          ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
          ' freelists 32 freelist groups 10'||
          ' pctincrease '||to_char(v_pctinc)||')' ||
          ' parallel (degree 1)'||
          ' nologging'||
          ' partition by range (item_type)' ||
          ' subpartition by hash (item_key) ('||
          ' partition wf_item1 values less than (''A1'' ) ,' ||
          ' partition wf_item2 values less than (''AM'' ) ,' ||
          ' partition wf_item3 values less than (''AP'' ) ,' ||
          ' partition wf_item4 values less than (''AR'' ) ,' ||
          ' partition wf_item5 values less than (''AZ'' ) ,' ||
          ' partition wf_item6 values less than (''BC'' ) ,' ||
          ' partition wf_item7 values less than (''BD'' ) ,' ||
          ' partition wf_item8 values less than (''BI'' ) ,' ||
          ' partition wf_item9 values less than (''BO'' ) ,' ||
          ' partition wf_item10 values less than (''BT'' ) ,' ||
          ' partition wf_item11 values less than (''BW'' ) ,' ||
          ' partition wf_item12 values less than (''CA'' ) ,' ||
          ' partition wf_item13 values less than (''CH'' ) ,' ||
          ' partition wf_item14 values less than (''CI'' ) ,' ||
          ' partition wf_item15 values less than (''CO'' ) ,' ||
          ' partition wf_item16 values less than (''CR'' ) ,' ||
          ' partition wf_item17 values less than (''CS'' ) ,' ||
          ' partition wf_item18 values less than (''CT'' ) ,' ||
          ' partition wf_item19 values less than (''CU'' ) ,' ||
          ' partition wf_item20 values less than (''DE'' ) ,' ||
          ' partition wf_item21 values less than (''EC'' ) ,' ||
          ' partition wf_item22 values less than (''ER'' ) ,' ||
          ' partition wf_item23 values less than (''FA'' ) ,' ||
          ' partition wf_item24 values less than (''FI'' ) ,' ||
          ' partition wf_item25 values less than (''FN'' ) ,' ||
          ' partition wf_item26 values less than (''GE'' ) ,' ||
          ' partition wf_item27 values less than (''GH'' ) ,' ||
          ' partition wf_item28 values less than (''GL'' ) ,' ||
          ' partition wf_item29 values less than (''GM'' ) ,' ||
          ' partition wf_item30 values less than (''GN'' ) ,' ||
          ' partition wf_item31 values less than (''HR'' ) ,' ||
          ' partition wf_item32 values less than (''IC'' ) ,' ||
          ' partition wf_item33 values less than (''IN'' ) ,' ||
          ' partition wf_item34 values less than (''IO'' ) ,' ||
          ' partition wf_item35 values less than (''IW'' ) ,' ||
          ' partition wf_item36 values less than (''JT'' ) ,' ||
          ' partition wf_item37 values less than (''JU'' ) ,' ||
          ' partition wf_item38 values less than (''KH'' ) ,' ||
          ' partition wf_item39 values less than (''KO'' ) ,' ||
          ' partition wf_item40 values less than (''LS'' ) ,' ||
          ' partition wf_item41 values less than (''MD'' ) ,' ||
          ' partition wf_item42 values less than (''MR'' ) ,' ||
          ' partition wf_item43 values less than (''MS'' ) ,' ||
          ' partition wf_item44 values less than (''NE'' ) ,' ||
          ' partition wf_item45 values less than (''NT'' ) ,' ||
          ' partition wf_item46 values less than (''OA'' ) ,' ||
          ' partition wf_item47 values less than (''OB'' ) ,' ||
          ' partition wf_item48 values less than (''OE'' ) ,' ||
          ' partition wf_item49 values less than (''OF'' ) ,' ||
          ' partition wf_item50 values less than (''OK'' ) ,' ||
          ' partition wf_item51 values less than (''OL'' ) ,' ||
          ' partition wf_item52 values less than (''OR'' ) ,' ||
          ' partition wf_item53 values less than (''PA'' ) ,' ||
          ' partition wf_item54 values less than (''PJ'' ) ,' ||
          ' partition wf_item55 values less than (''PM'' ) ,' ||
          ' partition wf_item56 values less than (''PO'' ) ,' ||
          ' partition wf_item57 values less than (''PQ'' ) ,' ||
          ' partition wf_item58 values less than (''PR'' ) ,' ||
          ' partition wf_item59 values less than (''QA'' ) ,' ||
          ' partition wf_item60 values less than (''RB'' ) ,' ||
          ' partition wf_item61 values less than (''RE'' ) ,' ||
          ' partition wf_item62 values less than (''RM'' ) ,' ||
          ' partition wf_item63 values less than (''RO'' ) ,' ||
          ' partition wf_item64 values less than (''SA'' ) ,' ||
          ' partition wf_item65 values less than (''SE'' ) ,' ||
          ' partition wf_item66 values less than (''SH'' ) ,' ||
          ' partition wf_item67 values less than (''SI'' ) ,' ||
          ' partition wf_item68 values less than (''SR'' ) ,' ||
          ' partition wf_item69 values less than (''SU'' ) ,' ||
          ' partition wf_item70 values less than (''SY'' ) ,' ||
          ' partition wf_item71 values less than (''TE'' ) ,' ||
          ' partition wf_item72 values less than (''TS'' ) ,' ||
          ' partition wf_item73 values less than (''WF'' ) ,' ||
          ' partition wf_item74 values less than (''WG'' ) ,' ||
          ' partition wf_item75 values less than (''WI'' ) ,' ||
          ' partition wf_item76 values less than (''WS'' ) ,' ||
          ' partition wf_item77 values less than (MAXVALUE))' ||
          ' as select * from '||tblname;

  begin
    execute immediate v_sql;
  exception
    when OTHERS then
      raise_application_error(-20003,'Error in SQL: '||substr(v_sql,1,3000));
  end;

  -- rename orig table to old table
  v_old_table := substr('WFO_'||tblname, 1, 30);
  dbms_output.put_line('Rename '||tblname||' to '||v_old_table);
  v_sql := 'alter table '||tblname||' rename to '||v_old_table;
  begin
    execute immediate v_sql;
  exception
    when OTHERS then
      raise_application_error(-20004,'Error in SQL: '||substr(v_sql,1,3000));
  end;

  -- rename new table to orig table
  dbms_output.put_line('Rename '||v_new_table||' to '||tblname);
  v_sql := 'alter table '||v_new_table||' rename to '||tblname;
  begin
    execute immediate v_sql;
  exception
    when OTHERS then
      raise_application_error(-20005,'Error in SQL: '||substr(v_sql,1,3000));
  end;

exception
  when OTHERS then
    dbms_output.put_line('--Error in CreateTable '||tblname);
    raise;
end CreateTable;

Procedure CreateIndex (
  idxname    in varchar2,
  tblspcname in varchar2 default null
)
is
  v_tablespace_name  varchar2(30);
  v_initial          number;
  v_next             number;
  v_pctinc           number;
  v_tblpartitioned   varchar2(3);
  v_idxpartitioned   varchar2(3);
  v_prefix           varchar2(8);
  v_new_index        varchar2(30);
  v_old_index        varchar2(30);
  v_sql              varchar2(4000);
  v_dummy            varchar2(1);
  partition_index    boolean := false;
  drop_index         boolean := false;
begin
  begin
    select I.TABLESPACE_NAME, I.INITIAL_EXTENT, I.NEXT_EXTENT, I.PCT_INCREASE,
           I.PARTITIONED, T.PARTITIONED
    into   v_tablespace_name, v_initial, v_next, v_pctinc,
           v_idxpartitioned, v_tblpartitioned
    from   USER_INDEXES I, USER_TABLES T
    where  I.INDEX_NAME = idxname
    and    I.TABLE_NAME = T.TABLE_NAME;

  exception
    when NO_DATA_FOUND then
      dbms_output.put_line('--Error while querying storage parameters.');
      dbms_output.put_line('-- Index '||idxname||' does not exist.');
      dbms_output.put_line('-- Please check if you login to the correct user, database,');
      dbms_output.put_line('-- and that application installation has been completed successfully.');
      raise_application_error(-20011,'Missing index '||idxname);

    when OTHERS then
      dbms_output.put_line('--Error while querying storage parameters.');
      raise;
  end;

  if (tblspcname is not null) then
    v_tablespace_name := tblspcname;
  end if;

  v_new_index := substr('WFN_'||idxname, 1, 30);

  -- check if new index name exists, if so, may ask to drop it.
  begin
    select null into v_dummy from sys.dual where exists (
      select 1
      from   USER_OBJECTS
      where  OBJECT_NAME = v_new_index
    );
    dbms_output.put_line('Name conflict.  Please first drop '||v_new_index);
    raise_application_error(-20012, v_new_index||' already exists.');
  exception
    when NO_DATA_FOUND then
      null;
    when OTHERS then
      dbms_output.put_line('--Error while checking the new index name.');
      raise;
  end;

  dbms_output.put_line('Recreating index '||idxname);

  v_prefix := 'WFO_';
  if (idxname = 'WF_ITEM_ACTIVITY_STATUSES_PK') then
    partition_index := true;
    v_prefix := 'WFP_';
    v_sql := 'create unique index '||v_new_index||
             ' on WF_ITEM_ACTIVITY_STATUSES (ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging'||
             ' local ('||
             ' partition wf_item1,' ||
             ' partition wf_item2,' ||
             ' partition wf_item3,' ||
             ' partition wf_item4,' ||
             ' partition wf_item5,' ||
             ' partition wf_item6,' ||
             ' partition wf_item7,' ||
             ' partition wf_item8,' ||
             ' partition wf_item9,' ||
             ' partition wf_item10,' ||
             ' partition wf_item11,' ||
             ' partition wf_item12,' ||
             ' partition wf_item13,' ||
             ' partition wf_item14,' ||
             ' partition wf_item15,' ||
             ' partition wf_item16,' ||
             ' partition wf_item17,' ||
             ' partition wf_item18,' ||
             ' partition wf_item19,' ||
             ' partition wf_item20,' ||
             ' partition wf_item21,' ||
             ' partition wf_item22,' ||
             ' partition wf_item23,' ||
             ' partition wf_item24,' ||
             ' partition wf_item25,' ||
             ' partition wf_item26,' ||
             ' partition wf_item27,' ||
             ' partition wf_item28,' ||
             ' partition wf_item29,' ||
             ' partition wf_item30,' ||
             ' partition wf_item31,' ||
             ' partition wf_item32,' ||
             ' partition wf_item33,' ||
             ' partition wf_item34,' ||
             ' partition wf_item35,' ||
             ' partition wf_item36,' ||
             ' partition wf_item37,' ||
             ' partition wf_item38,' ||
             ' partition wf_item39,' ||
             ' partition wf_item40,' ||
             ' partition wf_item41,' ||
             ' partition wf_item42,' ||
             ' partition wf_item43,' ||
             ' partition wf_item44,' ||
             ' partition wf_item45,' ||
             ' partition wf_item46,' ||
             ' partition wf_item47,' ||
             ' partition wf_item48,' ||
             ' partition wf_item49,' ||
             ' partition wf_item50,' ||
             ' partition wf_item51,' ||
             ' partition wf_item52,' ||
             ' partition wf_item53,' ||
             ' partition wf_item54,' ||
             ' partition wf_item55,' ||
             ' partition wf_item56,' ||
             ' partition wf_item57,' ||
             ' partition wf_item58,' ||
             ' partition wf_item59,' ||
             ' partition wf_item60,' ||
             ' partition wf_item61,' ||
             ' partition wf_item62,' ||
             ' partition wf_item63,' ||
             ' partition wf_item64,' ||
             ' partition wf_item65,' ||
             ' partition wf_item66,' ||
             ' partition wf_item67,' ||
             ' partition wf_item68,' ||
             ' partition wf_item69,' ||
             ' partition wf_item70,' ||
             ' partition wf_item71,' ||
             ' partition wf_item72,' ||
             ' partition wf_item73,' ||
             ' partition wf_item74,' ||
             ' partition wf_item75,' ||
             ' partition wf_item76,' ||
             ' partition wf_item77)';

  elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_N1') then
    v_prefix := 'WF1_';
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEM_ACTIVITY_STATUSES (ACTIVITY_STATUS, ITEM_TYPE)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging';
  elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_N2') then
    v_prefix := 'WF2_';
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEM_ACTIVITY_STATUSES (NOTIFICATION_ID)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging';
  elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_H_N1') then
    v_prefix := 'WFH1_';
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEM_ACTIVITY_STATUSES_H (ACTIVITY_STATUS, ITEM_TYPE)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging';
  elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_H_N2') then
    v_prefix := 'WFH2_';
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEM_ACTIVITY_STATUSES_H (NOTIFICATION_ID)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging';
  elsif (idxname = 'WF_ITEM_ATTRIBUTE_VALUES_PK') then
    partition_index := true;
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEM_ATTRIBUTE_VALUES (ITEM_TYPE, ITEM_KEY, NAME)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging'||
             ' local ('||
             ' partition wf_item1,' ||
             ' partition wf_item2,' ||
             ' partition wf_item3,' ||
             ' partition wf_item4,' ||
             ' partition wf_item5,' ||
             ' partition wf_item6,' ||
             ' partition wf_item7,' ||
             ' partition wf_item8,' ||
             ' partition wf_item9,' ||
             ' partition wf_item10,' ||
             ' partition wf_item11,' ||
             ' partition wf_item12,' ||
             ' partition wf_item13,' ||
             ' partition wf_item14,' ||
             ' partition wf_item15,' ||
             ' partition wf_item16,' ||
             ' partition wf_item17,' ||
             ' partition wf_item18,' ||
             ' partition wf_item19,' ||
             ' partition wf_item20,' ||
             ' partition wf_item21,' ||
             ' partition wf_item22,' ||
             ' partition wf_item23,' ||
             ' partition wf_item24,' ||
             ' partition wf_item25,' ||
             ' partition wf_item26,' ||
             ' partition wf_item27,' ||
             ' partition wf_item28,' ||
             ' partition wf_item29,' ||
             ' partition wf_item30,' ||
             ' partition wf_item31,' ||
             ' partition wf_item32,' ||
             ' partition wf_item33,' ||
             ' partition wf_item34,' ||
             ' partition wf_item35,' ||
             ' partition wf_item36,' ||
             ' partition wf_item37,' ||
             ' partition wf_item38,' ||
             ' partition wf_item39,' ||
             ' partition wf_item40,' ||
             ' partition wf_item41,' ||
             ' partition wf_item42,' ||
             ' partition wf_item43,' ||
             ' partition wf_item44,' ||
             ' partition wf_item45,' ||
             ' partition wf_item46,' ||
             ' partition wf_item47,' ||
             ' partition wf_item48,' ||
             ' partition wf_item49,' ||
             ' partition wf_item50,' ||
             ' partition wf_item51,' ||
             ' partition wf_item52,' ||
             ' partition wf_item53,' ||
             ' partition wf_item54,' ||
             ' partition wf_item55,' ||
             ' partition wf_item56,' ||
             ' partition wf_item57,' ||
             ' partition wf_item58,' ||
             ' partition wf_item59,' ||
             ' partition wf_item60,' ||
             ' partition wf_item61,' ||
             ' partition wf_item62,' ||
             ' partition wf_item63,' ||
             ' partition wf_item64,' ||
             ' partition wf_item65,' ||
             ' partition wf_item66,' ||
             ' partition wf_item67,' ||
             ' partition wf_item68,' ||
             ' partition wf_item69,' ||
             ' partition wf_item70,' ||
             ' partition wf_item71,' ||
             ' partition wf_item72,' ||
             ' partition wf_item73,' ||
             ' partition wf_item74,' ||
             ' partition wf_item75,' ||
             ' partition wf_item76,' ||
             ' partition wf_item77)';
  elsif (idxname = 'WF_ITEMS_PK') then
    partition_index := true;
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEMS (ITEM_TYPE, ITEM_KEY)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging'||
             ' local ('||
             ' partition wf_item1,' ||
             ' partition wf_item2,' ||
             ' partition wf_item3,' ||
             ' partition wf_item4,' ||
             ' partition wf_item5,' ||
             ' partition wf_item6,' ||
             ' partition wf_item7,' ||
             ' partition wf_item8,' ||
             ' partition wf_item9,' ||
             ' partition wf_item10,' ||
             ' partition wf_item11,' ||
             ' partition wf_item12,' ||
             ' partition wf_item13,' ||
             ' partition wf_item14,' ||
             ' partition wf_item15,' ||
             ' partition wf_item16,' ||
             ' partition wf_item17,' ||
             ' partition wf_item18,' ||
             ' partition wf_item19,' ||
             ' partition wf_item20,' ||
             ' partition wf_item21,' ||
             ' partition wf_item22,' ||
             ' partition wf_item23,' ||
             ' partition wf_item24,' ||
             ' partition wf_item25,' ||
             ' partition wf_item26,' ||
             ' partition wf_item27,' ||
             ' partition wf_item28,' ||
             ' partition wf_item29,' ||
             ' partition wf_item30,' ||
             ' partition wf_item31,' ||
             ' partition wf_item32,' ||
             ' partition wf_item33,' ||
             ' partition wf_item34,' ||
             ' partition wf_item35,' ||
             ' partition wf_item36,' ||
             ' partition wf_item37,' ||
             ' partition wf_item38,' ||
             ' partition wf_item39,' ||
             ' partition wf_item40,' ||
             ' partition wf_item41,' ||
             ' partition wf_item42,' ||
             ' partition wf_item43,' ||
             ' partition wf_item44,' ||
             ' partition wf_item45,' ||
             ' partition wf_item46,' ||
             ' partition wf_item47,' ||
             ' partition wf_item48,' ||
             ' partition wf_item49,' ||
             ' partition wf_item50,' ||
             ' partition wf_item51,' ||
             ' partition wf_item52,' ||
             ' partition wf_item53,' ||
             ' partition wf_item54,' ||
             ' partition wf_item55,' ||
             ' partition wf_item56,' ||
             ' partition wf_item57,' ||
             ' partition wf_item58,' ||
             ' partition wf_item59,' ||
             ' partition wf_item60,' ||
             ' partition wf_item61,' ||
             ' partition wf_item62,' ||
             ' partition wf_item63,' ||
             ' partition wf_item64,' ||
             ' partition wf_item65,' ||
             ' partition wf_item66,' ||
             ' partition wf_item67,' ||
             ' partition wf_item68,' ||
             ' partition wf_item69,' ||
             ' partition wf_item70,' ||
             ' partition wf_item71,' ||
             ' partition wf_item72,' ||
             ' partition wf_item73,' ||
             ' partition wf_item74,' ||
             ' partition wf_item75,' ||
             ' partition wf_item76,' ||
             ' partition wf_item77)';
  elsif (idxname = 'WF_ITEMS_N1') then
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEMS (PARENT_ITEM_TYPE, PARENT_ITEM_KEY)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging';
  elsif (idxname = 'WF_ITEMS_N2') then
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEMS (BEGIN_DATE)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging';
  elsif (idxname = 'WF_ITEMS_N3') then
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEMS (END_DATE)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging';
  end if;

  -- now check if we need to proceed
  if (partition_index) then
    if (v_idxpartitioned = 'YES') then
      -- already partitioned.  Just exit.
      dbms_output.put_line('Index '||idxname||' already partitioned ... skipped.');
      return;
    elsif (v_tblpartitioned = 'YES') then
      -- first drop the index
      drop_index := true;
      begin
        dbms_output.put_line('Drop index '||idxname);
        execute immediate 'drop index '||idxname;
      exception
        when OTHERS then
          raise_application_error(-20013,'Error in SQL: '||
                                  substr('drop index '||idxname,1,3000));
      end;

    end if;
  else
    if (v_tblpartitioned = 'YES') then
      -- Detected table partitioned meant this script had already been run.
      -- Just exit.
      dbms_output.put_line('Table for index '||idxname||' already partitioned ... skipped.');
      return;
    end if;
  end if;

  begin
    dbms_output.put_line('Create index '||v_new_index);
    execute immediate v_sql;
  exception
    when OTHERS then
      raise_application_error(-20014,'Error in SQL: '||substr(v_sql,1,3000));
  end;

  -- Rename only if we did not drop index before
  if (not drop_index) then
    -- rename orig index to old index
    v_old_index := substr(v_prefix||idxname, 1, 30);
    dbms_output.put_line('Rename '||idxname||' to '||v_old_index);
    v_sql := 'alter index '||idxname||' rename to '||v_old_index;
    begin
      execute immediate v_sql;
    exception
      when OTHERS then
        raise_application_error(-20015,'Error in SQL: '||substr(v_sql,1,3000));
    end;
  end if;

  -- rename new index to orig index
  dbms_output.put_line('Rename '||v_new_index||' to '||idxname);
  v_sql := 'alter index '||v_new_index||' rename to '||idxname;
  begin
    execute immediate v_sql;
  exception
    when OTHERS then
      raise_application_error(-20016,'Error in SQL: '||substr(v_sql,1,3000));
  end;

exception
  when OTHERS then
    dbms_output.put_line('--Error in CreateIndex '||idxname);
    raise;
end CreateIndex;

Procedure CompileObject (
  objtype    in varchar2,
  objname    in varchar2 default '%'
)
is
  cursor objcurs(objt varchar2, objn varchar2) is
    select OBJECT_NAME
    from   ALL_OBJECTS
    where  OBJECT_TYPE = objt
    and    OBJECT_NAME like objn
    and    STATUS = 'INVALID';

  v_sql varchar2(4000);

begin
  for obj in objcurs(objtype, objname) loop
    if (objtype = 'PACKAGE BODY') then
      v_sql := 'alter package '||obj.OBJECT_NAME||' compile body';
    else
      v_sql := 'alter '||objtype||' '||obj.OBJECT_NAME||' compile';
    end if;

    dbms_output.put_line('Running: '||v_sql);

    begin
      execute immediate v_sql;
    exception
      when OTHERS then
        -- continues when error is caught.
        dbms_output.put_line('--Error in SQL: '||v_sql);
    end;
  end loop;
exception
  when OTHERS then
    dbms_output.put_line('--Error in CompileObject '||objname);
    raise;
end CompileObject;

end Wf_Install;
/

-- show errors package body WF_INSTALL;

whenever sqlerror exit failure;

-- As APPS
prompt connecting to &u_app
connect &u_app/&p_app;

whenever sqlerror exit failure rollback;

create or replace package Wf_Install as
/* $Header: wfupartb.sql 26.1 2001/11/05 21:54:28 ctilley ship $ */
-- objtype are like 'VIEW' and 'PACKAGE BODY'
-- objname can have wild card like 'WF%'
Procedure CompileObject (
  objtype    in varchar2,
  objname    in varchar2 default '%'
);

end Wf_Install;
/

create or replace package body Wf_Install as
/* $Header: wfupartb.sql 26.1 2001/11/05 21:54:28 ctilley ship $ */
Procedure CompileObject (
  objtype    in varchar2,
  objname    in varchar2 default '%'
)
is
  cursor objcurs(objt varchar2, objn varchar2) is
    select OBJECT_NAME
    from   USER_OBJECTS
    where  OBJECT_TYPE = objt
    and    OBJECT_NAME like objn
    and    STATUS = 'INVALID';

  v_sql varchar2(4000);

begin
  for obj in objcurs(objtype, objname) loop
    if (objtype = 'PACKAGE BODY') then
      v_sql := 'alter package '||obj.OBJECT_NAME||' compile body';
    else
      v_sql := 'alter '||objtype||' '||obj.OBJECT_NAME||' compile';
    end if;

    dbms_output.put_line('Running: '||v_sql);

    begin
      execute immediate v_sql;
    exception
      when OTHERS then
        -- continues when error is caught.
        dbms_output.put_line('--Error in SQL: '||v_sql);
    end;
  end loop;
exception
  when OTHERS then
    dbms_output.put_line('--Error in CompileObject '||objname);
    raise;
end CompileObject;

end Wf_Install;
/


whenever sqlerror exit failure;

-- As APPLSYS
prompt Connecting to &u_fnd ...
connect &u_fnd/&p_fnd;

whenever sqlerror exit failure rollback;
set serveroutput on

prompt Recreating tables.  May take a long time...

prompt Recreating table WF_ITEM_ACTIVITY_STATUSES
begin
  dbms_output.enable(1000000);

  -- create table
  Wf_Install.CreateTable('WF_ITEM_ACTIVITY_STATUSES');

  -- create index
  Wf_Install.CreateIndex('WF_ITEM_ACTIVITY_STATUSES_PK');
  Wf_Install.CreateIndex('WF_ITEM_ACTIVITY_STATUSES_N1');
  Wf_Install.CreateIndex('WF_ITEM_ACTIVITY_STATUSES_N2');
end;
/

-- grant select,insert,delete,update on tblnm to APPS;
prompt grant select,insert,delete,update on WF_ITEM_ACTIVITY_STATUSES to APPS
grant select,insert,delete,update on WF_ITEM_ACTIVITY_STATUSES to APPS;

-- drop old table
whenever sqlerror continue;
prompt drop table WFO_WF_ITEM_ACTIVITY_STATUSES;
drop table WFO_WF_ITEM_ACTIVITY_STATUSES;

whenever sqlerror exit failure rollback;

prompt Recreating table WF_ITEM_ACTIVITY_STATUSES_H
begin
  dbms_output.enable(1000000);
  Wf_Install.CreateTable('WF_ITEM_ACTIVITY_STATUSES_H');

  Wf_Install.CreateIndex('WF_ITEM_ACTIVITY_STATUSES_H_N1');
  Wf_Install.CreateIndex('WF_ITEM_ACTIVITY_STATUSES_H_N2');
end;
/

-- grant select,insert,delete,update on tblnm to APPS;
prompt grant select,insert,delete,update on WF_ITEM_ACTIVITY_STATUSES_H to APPS
grant select,insert,delete,update on WF_ITEM_ACTIVITY_STATUSES_H to APPS;

-- drop old table
whenever sqlerror continue;
prompt drop table WFO_WF_ITEM_ACTIVITY_STATUSES_;
drop table WFO_WF_ITEM_ACTIVITY_STATUSES_;
whenever sqlerror exit failure rollback;

prompt Recreating table WF_ITEM_ATTRIBUTE_VALUES
begin
  dbms_output.enable(1000000);
  -- create table
  Wf_Install.CreateTable('WF_ITEM_ATTRIBUTE_VALUES');

  -- create index
  Wf_Install.CreateIndex('WF_ITEM_ATTRIBUTE_VALUES_PK');
end;
/

-- grant select,insert,delete,update on tblnm to APPS;
prompt grant select,insert,delete,update on WF_ITEM_ATTRIBUTE_VALUES to APPS
grant select,insert,delete,update on WF_ITEM_ATTRIBUTE_VALUES to APPS;

-- drop old table
whenever sqlerror continue;
prompt drop table WFO_WF_ITEM_ATTRIBUTE_VALUES
drop table WFO_WF_ITEM_ATTRIBUTE_VALUES;
whenever sqlerror exit failure rollback;

prompt Recreating table WF_ITEMS
begin
  dbms_output.enable(1000000);

  -- create table
  Wf_Install.CreateTable('WF_ITEMS');

  -- create index
  Wf_Install.CreateIndex('WF_ITEMS_PK');
  Wf_Install.CreateIndex('WF_ITEMS_N1');
  Wf_Install.CreateIndex('WF_ITEMS_N2');
  Wf_Install.CreateIndex('WF_ITEMS_N3');
end;
/

-- grant select,insert,delete,update on tblnm to APPS;
prompt grant select,insert,delete,update on WF_ITEMS to APPS
grant select,insert,delete,update on WF_ITEMS to APPS;

-- drop old table
whenever sqlerror continue;
prompt drop table WFO_WF_ITEMS
drop table WFO_WF_ITEMS;

-- clean up in APPLSYS
prompt Drop package WF_INSTALL in &u_fnd schema
drop package WF_INSTALL;

-- As APPS
whenever sqlerror exit failure;

prompt Connecting to &u_app ...
connect &u_app/&p_app

whenever sqlerror continue;

-- create synonym tblnm for APPLSYS.tblnm
create synonym WF_ITEM_ACTIVITY_STATUSES for APPLSYS.WF_ITEM_ACTIVITY_STATUSES;
create synonym WF_ITEM_ACTIVITY_STATUSES_H for APPLSYS.WF_ITEM_ACTIVITY_STATUSES_H;
create synonym WF_ITEM_ATTRIBUTE_VALUES for APPLSYS.WF_ITEM_ATTRIBUTE_VALUES;
create synonym WF_ITEMS for APPLSYS.WF_ITEMS;

whenever sqlerror exit failure rollback;
set serveroutput on

prompt Recompiling views
-- recompile views
begin
  dbms_output.enable(1000000);
  Wf_Install.CompileObject('VIEW');
end;
/

set serveroutput on
prompt Recompiling package bodies
-- recompile packages
begin
  dbms_output.enable(1000000);
  Wf_Install.CompileObject('PACKAGE BODY');
end;
/

-- clean up
-- prompt Drop package WF_INSTALL in &u_app schema
-- drop package WF_INSTALL;


prompt Post-install script completed, you may ignore error during create
prompt synonyms.
prompt

commit;
exit;