REM $Header: wfidxc.sql 26.25 2004/10/05 18:41:27 htay ship $
REM ***********************************************************************
REM   NAME
REM       wfidxc.sql - WorkFlow InDeX Create
REM   DESCRIPTION
REM       Adds indexes to all workflow tables
REM   USAGE
REM       sqlplus apps/apps @wfidxc applsys fnd
REM ********************************************************************
REM Connect to base account
REM (autopatch will run all scripts in apps account)

REM  dbdrv: none

connect &1/&2;

REM Continue in case of error where constraints aready exist
WHENEVER SQLERROR CONTINUE;


/*
** Core indices (objects in wfcorec.sql)
*/

create unique index WF_ITEM_TYPES_PK
    on WF_ITEM_TYPES (NAME)
    storage (pctincrease 100);
create unique index WF_ITEM_TYPES_TL_PK
    on WF_ITEM_TYPES_TL (NAME, LANGUAGE)
    storage (pctincrease 100);
create unique index WF_ITEM_TYPES_TL_U2
    on WF_ITEM_TYPES_TL (DISPLAY_NAME, LANGUAGE)
    storage (pctincrease 100);

create unique index WF_ITEM_ATTRIBUTES_PK
    on WF_ITEM_ATTRIBUTES (ITEM_TYPE, NAME)
    storage (pctincrease 100);
create unique index WF_ITEM_ATTRIBUTES_TL_PK
    on WF_ITEM_ATTRIBUTES_TL (ITEM_TYPE, NAME, LANGUAGE)
    storage (pctincrease 100);
create unique index WF_ITEM_ATTRIBUTES_TL_U2
    on WF_ITEM_ATTRIBUTES_TL (DISPLAY_NAME, ITEM_TYPE, LANGUAGE)
    storage (pctincrease 100);

create unique index WF_LOOKUP_TYPES_TL_PK
    on WF_LOOKUP_TYPES_TL(LOOKUP_TYPE, LANGUAGE)
    storage (pctincrease 100);
create unique index WF_LOOKUP_TYPES_TL_U2
    on WF_LOOKUP_TYPES_TL(DISPLAY_NAME, LANGUAGE)
    storage (pctincrease 100);
create unique index WF_LOOKUPS_TL_PK
    on WF_LOOKUPS_TL(LOOKUP_TYPE, LOOKUP_CODE, LANGUAGE)
    storage (pctincrease 100);
create unique index WF_LOOKUPS_TL_U2
    on WF_LOOKUPS_TL(LOOKUP_TYPE, MEANING, LANGUAGE)
    storage (pctincrease 100);

create unique index WF_RESOURCES_PK
    on WF_RESOURCES (TYPE, NAME, LANGUAGE)
    storage (pctincrease 100);


/*
** Notification indices (objects in wfntfc.sql)
*/

create unique index WF_MESSAGES_PK
    on WF_MESSAGES (TYPE, NAME)
    storage (pctincrease 100);
create unique index WF_MESSAGES_TL_PK
    on WF_MESSAGES_TL (TYPE, NAME, LANGUAGE)
    storage (pctincrease 100);

create unique index WF_MESSAGE_ATTRIBUTES_PK
    on WF_MESSAGE_ATTRIBUTES (MESSAGE_TYPE, MESSAGE_NAME, NAME)
    storage (pctincrease 100);
create unique index WF_MESSAGE_ATTRIBUTES_U2
    on WF_MESSAGE_ATTRIBUTES (MESSAGE_TYPE, MESSAGE_NAME, SEQUENCE)
    storage (pctincrease 100);
create unique index WF_MESSAGE_ATTRIBUTES_TL_PK
    on WF_MESSAGE_ATTRIBUTES_TL (MESSAGE_TYPE, MESSAGE_NAME, NAME, LANGUAGE)
    storage (pctincrease 100);
create unique index WF_MESSAGE_ATTRIBUTES_TL_U2
    on WF_MESSAGE_ATTRIBUTES_TL
        (DISPLAY_NAME, MESSAGE_TYPE, MESSAGE_NAME, LANGUAGE)
    storage (pctincrease 100);

create unique index WF_NOTIFICATIONS_PK
    on WF_NOTIFICATIONS (NOTIFICATION_ID)
    storage (pctincrease 100);
create index WF_NOTIFICATIONS_N1
    on WF_NOTIFICATIONS (RECIPIENT_ROLE,STATUS)
    storage (pctincrease 100);
create index WF_NOTIFICATIONS_N2
    on WF_NOTIFICATIONS(GROUP_ID, NOTIFICATION_ID)
    storage (pctincrease 100);
create index WF_NOTIFICATIONS_N3
    on WF_NOTIFICATIONS(MAIL_STATUS, STATUS)
    storage (pctincrease 100);
create index WF_NOTIFICATIONS_N4
    on WF_NOTIFICATIONS (ORIGINAL_RECIPIENT)
    storage (pctincrease 100);
create index WF_NOTIFICATIONS_N5
    on WF_NOTIFICATIONS (STATUS)
    storage (pctincrease 100);
create index WF_NOTIFICATIONS_N6
    on WF_NOTIFICATIONS (MORE_INFO_ROLE,STATUS)
    storage (pctincrease 100);
create index WF_NOTIFICATIONS_N7
    on WF_NOTIFICATIONS (FROM_ROLE,STATUS)
    storage (pctincrease 100);


create unique index WF_NOTIFICATIONS_ATTR_PK
    on WF_NOTIFICATION_ATTRIBUTES (NOTIFICATION_ID, NAME)
    storage (pctincrease 100);

create unique index WF_ROUTING_RULES_PK
  on WF_ROUTING_RULES(RULE_ID)
  storage (pctincrease 100);

create unique index WF_ROUTING_RULE_ATTRIBUTES_PK
  on WF_ROUTING_RULE_ATTRIBUTES(RULE_ID, NAME, TYPE)
  storage (pctincrease 100);


/*
** Engine indices (objects in wfengc.sql)
*/

create unique index WF_ACTIVITIES_PK 
    on WF_ACTIVITIES (ITEM_TYPE, NAME, VERSION)
    storage (pctincrease 100);
create unique index WF_ACTIVITIES_TL_PK 
    on WF_ACTIVITIES_TL (ITEM_TYPE, NAME, VERSION, LANGUAGE)
    storage (pctincrease 100);
/**
 Removed to remain consistent to APPS
 But afwf.odf has it though 11.5.9 OWFG env does not show this index 
create unique index WF_ACTIVITIES_TL_U2 
    on WF_ACTIVITIES_TL (DISPLAY_NAME, ITEM_TYPE, VERSION, LANGUAGE)
    storage (pctincrease 100);
**/
create index WF_ACTIVITIES_N1
    on WF_ACTIVITIES (ERROR_ITEM_TYPE, ERROR_PROCESS)
    storage (pctincrease 100);

create unique index WF_ACTIVITY_ATTRIBUTES_PK 
    on WF_ACTIVITY_ATTRIBUTES 
	(ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, ACTIVITY_VERSION, NAME)
    storage (pctincrease 100);
create unique index WF_ACTIVITY_ATTRIBUTES_TL_PK 
    on WF_ACTIVITY_ATTRIBUTES_TL
	(ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, ACTIVITY_VERSION, NAME, LANGUAGE)
    storage (pctincrease 100);
create unique index WF_ACTIVITY_ATTRIBUTES_TL_U2 
    on  WF_ACTIVITY_ATTRIBUTES_TL 
        (DISPLAY_NAME, ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, ACTIVITY_VERSION,
	 LANGUAGE)
    storage (pctincrease 100);

create unique index WF_PROCESS_ACTIVITIES_PK 
    on WF_PROCESS_ACTIVITIES (INSTANCE_ID)
    storage (pctincrease 100);
create unique index WF_PROCESS_ACTIVITIES_U2 
    on WF_PROCESS_ACTIVITIES (INSTANCE_LABEL, PROCESS_ITEM_TYPE,
			      PROCESS_NAME, PROCESS_VERSION)
    storage (pctincrease 100);
create index WF_PROCESS_ACTIVITIES_N1
    on WF_PROCESS_ACTIVITIES (ACTIVITY_ITEM_TYPE, ACTIVITY_NAME)
    storage (pctincrease 100);
create index WF_PROCESS_ACTIVITIES_N2
    on WF_PROCESS_ACTIVITIES (PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION)
    storage (pctincrease 100);

create unique index WF_ACTIVITY_ATTR_VALUES_PK 
    on WF_ACTIVITY_ATTR_VALUES(PROCESS_ACTIVITY_ID, NAME)
    storage (pctincrease 100);

create unique index WF_ACTIVITY_TRANSITIONS_PK 
    on WF_ACTIVITY_TRANSITIONS 
	(FROM_PROCESS_ACTIVITY, RESULT_CODE, TO_PROCESS_ACTIVITY)
    storage (pctincrease 100);
create index WF_ACTIVITY_TRANSITIONS_N1 
    on WF_ACTIVITY_TRANSITIONS (TO_PROCESS_ACTIVITY)
    storage (pctincrease 100);

create unique index WF_ITEMS_PK 
    on WF_ITEMS (ITEM_TYPE, ITEM_KEY)
    storage (pctincrease 100);
create index WF_ITEMS_N1
    on WF_ITEMS (PARENT_ITEM_TYPE, PARENT_ITEM_KEY)
    storage (pctincrease 100);
create index WF_ITEMS_N2
    on WF_ITEMS (BEGIN_DATE)
    storage (pctincrease 100);
create index WF_ITEMS_N3
    on WF_ITEMS (END_DATE)
    storage (pctincrease 100);
create index WF_ITEMS_N4
    on WF_ITEMS (ITEM_TYPE, ROOT_ACTIVITY, OWNER_ROLE)
    storage (pctincrease 100);
create index WF_ITEMS_N5
    on WF_ITEMS (USER_KEY)
    storage (pctincrease 100);    
create index WF_ITEMS_N6
    on WF_ITEMS (OWNER_ROLE)
    storage (pctincrease 100);
create unique index WF_ITEMS_U1
    on WF_ITEMS (HA_MIGRATION_FLAG, ITEM_TYPE, ITEM_KEY)
    storage (pctincrease 100);

create unique index WF_ITEM_ATTRIBUTE_VALUES_PK 
    on WF_ITEM_ATTRIBUTE_VALUES (ITEM_TYPE, ITEM_KEY, NAME)
    storage (
        pctincrease 100
        initial       4K
        next          1M
        maxextents   50
    );


create unique index WF_ITEM_ACTIVITY_STATUSES_PK 
    on WF_ITEM_ACTIVITY_STATUSES (ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY)
    pctfree 25
    storage (
        pctincrease 100
        initial       4K
        next          1M
        maxextents   50
    );

create index WF_ITEM_ACTIVITY_STATUSES_N1
    on WF_ITEM_ACTIVITY_STATUSES (ACTIVITY_STATUS, ITEM_TYPE)
    storage (
        pctincrease 100
        initial       4K
        next          1M
        maxextents   50
    );

create index WF_ITEM_ACTIVITY_STATUSES_N2
    on WF_ITEM_ACTIVITY_STATUSES (NOTIFICATION_ID)
    storage (
        pctincrease 100
        initial       4K
        next          1M
        maxextents   50
    );

create index WF_ITEM_ACTIVITY_STATUSES_N3
    on WF_ITEM_ACTIVITY_STATUSES (DUE_DATE, ITEM_TYPE)
    storage (
        pctincrease 100
        initial       4K
        next          1M
        maxextents   50
    );

create index WF_ITEM_ACTIVITY_STATUSES_H_N1
    on WF_ITEM_ACTIVITY_STATUSES_H (ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY)
    storage (pctincrease 100);

create index WF_ITEM_ACTIVITY_STATUSES_H_N2
    on WF_ITEM_ACTIVITY_STATUSES_H (NOTIFICATION_ID)
    storage (pctincrease 100);
    
create unique index WF_QUEUES_U1
    on WF_QUEUES (PROTOCOL, INBOUND_OUTBOUND);


/*
** Local directory indices (Objects in wfdirc.sql)
*/

create unique index WF_LOCAL_LANGUAGES_PK
    on WF_LOCAL_LANGUAGES (CODE)
    storage (pctincrease 100);
create unique index WF_LOCAL_LANGUAGES_U1
    on WF_LOCAL_LANGUAGES (DISPLAY_NAME)
    storage (pctincrease 100);
create unique index WF_LOCAL_LANGUAGES_U2
    on WF_LOCAL_LANGUAGES (NLS_LANGUAGE)
    storage (pctincrease 100);
create index WF_LOCAL_LANGUAGES_N1
    on WF_LOCAL_LANGUAGES (INSTALLED_FLAG)
    storage (pctincrease 100);

/*
** WF_LOCAL_USERS is obsolete, no further action will occur on this table.
**create unique index WF_LOCAL_USERS_PK
**    on WF_LOCAL_USERS (NAME)
**    storage (pctincrease 100);
**create unique index WF_LOCAL_USERS_U1
**    on WF_LOCAL_USERS (NAME, ORIG_SYSTEM, ORIG_SYSTEM_ID)
**    storage (pctincrease 100);
**create unique index WF_LOCAL_USERS_U2
**    on WF_LOCAL_USERS (DISPLAY_NAME)
**    storage (pctincrease 100);
*/
/* WF_LOCAL_ROLES_PK is now WF_LOCAL_ROLES_U1
create unique index WF_LOCAL_ROLES_PK
    on WF_LOCAL_ROLES (NAME, PARTITION_ID)
    storage (pctincrease 100);
*/
create unique index WF_LOCAL_ROLES_U1
    on WF_LOCAL_ROLES (NAME, ORIG_SYSTEM, ORIG_SYSTEM_ID, PARTITION_ID)
    storage (pctincrease 100);
/*
**create unique index WF_LOCAL_ROLES_U2
**    on WF_LOCAL_ROLES (DISPLAY_NAME, PARTITION_ID)
**    storage (pctincrease 100) LOCAL (partition WF_LOCAL_ROLES);
*/
create index WF_LOCAL_ROLES_N1
    on WF_LOCAL_ROLES (ORIG_SYSTEM, ORIG_SYSTEM_ID)
    storage (pctincrease 100);
create index WF_LOCAL_ROLES_N2
    on WF_LOCAL_ROLES (DISPLAY_NAME)
    storage (pctincrease 100);
create index WF_LOCAL_ROLES_N3
    on WF_LOCAL_ROLES (EXPIRATION_DATE)
    storage (pctincrease 100);
/** 
Bug 3090738
Create function index on email_address column to
aid in serach for role based on e-mail address 
**/
create index WF_LOCAL_ROLES_N4
    on WF_LOCAL_ROLES (UPPER(EMAIL_ADDRESS))
    storage (pctincrease 100);

/**
Create indexes for wf_local_roles_tl table 
**/
create unique index WF_LOCAL_ROLES_TL_U1
    on WF_LOCAL_ROLES (NAME, ORIG_SYSTEM, ORIG_SYSTEM_ID, LANGUAGE,PARTITION_ID)
    storage (pctincrease 100);

create index WF_LOCAL_ROLES_TL_N1
    on WF_LOCAL_ROLES (DISPLAY_NAME,LANGUAGE)
    storage (pctincrease 100);


/* WF_LOCAL_USER_ROLES_PK is now WF_LOCAL_USER_ROLES_U1
create unique index WF_LOCAL_USER_ROLES_PK
    on WF_LOCAL_USER_ROLES (USER_NAME, ROLE_NAME, PARTITION_ID)
    storage (pctincrease 100);
*/

create unique index WF_LOCAL_USER_ROLES_U1
on WF_LOCAL_USER_ROLES (USER_NAME, ROLE_NAME,
  USER_ORIG_SYSTEM,USER_ORIG_SYSTEM_ID,ROLE_ORIG_SYSTEM,ROLE_ORIG_SYSTEM_ID,
  PARTITION_ID)
storage (pctincrease 100);

/*
create index WF_LOCAL_USER_ROLES_N1
on WF_LOCAL_USER_ROLES (USER_NAME)
storage (pctincrease 100);
*/

create index WF_LOCAL_USER_ROLES_N2
on WF_LOCAL_USER_ROLES (ROLE_ORIG_SYSTEM_ID, ROLE_ORIG_SYSTEM)
storage (pctincrease 100);
/*
create index WF_LOCAL_USER_ROLES_N3
on WF_LOCAL_USER_ROLES (ROLE_NAME)
storage (pctincrease 100);
*/

create index WF_LOCAL_USER_ROLES_N4
on WF_LOCAL_USER_ROLES (USER_ORIG_SYSTEM_ID, USER_ORIG_SYSTEM)
storage (pctincrease 100);

create index WF_LOCAL_USER_ROLES_N5
on WF_LOCAL_USER_ROLES (PARENT_ORIG_SYSTEM, PARENT_ORIG_SYSTEM_ID)
storage (pctincrease 100);

create index WF_LOCAL_USER_ROLES_N6
on WF_LOCAL_USER_ROLES (EXPIRATION_DATE)
storage (pctincrease 100);

create index WF_LOCAL_USER_ROLES_N7
on WF_LOCAL_USER_ROLES (ROLE_ORIG_SYSTEM_ID, ROLE_ORIG_SYSTEM)
storage (pctincrease 100);



REM +======================================================================+
REM     Index creation for workflow business event system.
REM +======================================================================+

/*
** Event Manager Indices (objects in wfeventc.sql)
*/

create unique index WF_EVENTS_U1
    on WF_EVENTS (GUID);

create unique index WF_EVENTS_U2
    on WF_EVENTS (NAME);

create unique index WF_EVENTS_TL_U1
    on WF_EVENTS_TL (GUID, LANGUAGE);

create unique index WF_EVENT_GROUPS_U1
    on WF_EVENT_GROUPS (MEMBER_GUID, GROUP_GUID);


create unique index WF_SYSTEMS_U1
    on WF_SYSTEMS (GUID);
create unique index WF_SYSTEMS_U2
    on WF_SYSTEMS (NAME);


create unique index WF_AGENTS_U1
    on WF_AGENTS (GUID);
create unique index WF_AGENTS_U2
    on WF_AGENTS (NAME, SYSTEM_GUID);
create index WF_AGENTS_N1
    on WF_AGENTS (SYSTEM_GUID, PROTOCOL);


create unique index WF_EVENT_SUBSCRIPTIONS_U1
    on WF_EVENT_SUBSCRIPTIONS (GUID);
create index WF_EVENT_SUBSCRIPTIONS_N1
    on WF_EVENT_SUBSCRIPTIONS (SYSTEM_GUID, SOURCE_TYPE, EVENT_FILTER_GUID);

create unique index WF_AGENT_GROUPS_U1
    on WF_AGENT_GROUPS (GROUP_GUID, MEMBER_GUID);

create unique index WF_ATTRIBUTE_CACHE_U1
    on WF_ATTRIBUTE_CACHE (ENTITY_TYPE, ENTITY_KEY_VALUE, ATTRIBUTE_NAME);

create unique index WF_BES_SUBSCRIBER_PINGS_U1
on WF_BES_SUBSCRIBER_PINGS (PING_NUMBER, QUEUE_NAME, SUBSCRIBER_NAME)
    storage (
        pctincrease 100
        initial     4K
        next        1M
        maxextents  50
    );

create index WF_BES_SUBSCRIBER_PINGS_N1
on WF_BES_SUBSCRIBER_PINGS (PING_TIME);

create index WF_BES_SUBSCRIBER_PINGS_N2
on WF_BES_SUBSCRIBER_PINGS (STATUS);

/** index for WF_DIRECTORY_PARTITIONS */
create unique index WF_DIRECTORY_PARTITIONS_U1
on WF_DIRECTORY_PARTITIONS (ORIG_SYSTEM)
    storage (
        pctincrease 100
        initial     4K 
        next        1M 
        maxextents  50
    );

/** Index WF_ITEM_ACTIVITY_STATUSES_N4 the pctfree etc are taken from their
values in afwf.odf 
**/

create index WF_ITEM_ACTIVITY_STATUSES_N4
on WF_ITEM_ACTIVITY_STATUSES (ASSIGNED_USER, ITEM_TYPE)
    storage (
        pctincrease 100
        initial     4K
        next        1M
        maxextents  50
    );

/* Index for WF_COMMENTS table */
create index WF_COMMENTS_N1
    on WF_COMMENTS (NOTIFICATION_ID)
    storage (
        pctincrease 100
        initial     4K
        next        1M
        maxextents  50
    );

/* Function-based Indices for WF_LOCAL_ROLES/TL tables */
create index WF_LOCAL_ROLES_F1
    on WF_LOCAL_ROLES (UPPER(DISPLAY_NAME))
    storage (
        pctincrease 100
        initial     4K
        next        1M
        maxextents  50
    );

create index WF_LOCAL_ROLES_TL_F1
    on WF_LOCAL_ROLES_TL (UPPER(DISPLAY_NAME))
    storage (
        pctincrease 100
        initial     4K
        next        1M
        maxextents  50
    );

commit;
exit;