REM $Header: wffkc.sql 26.3 2003/03/14 21:49:36 dlam ship $
REM ***********************************************************************
REM   NAME
REM       wffkc.sql - WorkFlow Primary,Unique and Foreign Key constraint Create
REM   DESCRIPTION
REM       Adds constraints to all workflow tables
REM   USAGE
REM       sqlplus apps/apps @wffkc applsys fnd
REM ********************************************************************
REM Connect to base account
REM (autopatch will run all scripts in apps account)
connect &1/&2;

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

/*
** Core constraints (objects in wfcorec.sql)
*/
alter table WF_ITEM_TYPES add constraint WF_ITEM_TYPES_PK
    primary key (NAME);
alter table WF_ITEM_TYPES_TL add constraint WF_ITEM_TYPES_TL_PK
    primary key (NAME, LANGUAGE);
alter table WF_ITEM_TYPES_TL add constraint WF_ITEM_TYPES_TL_U2
    unique (DISPLAY_NAME, LANGUAGE);

alter table WF_ITEM_ATTRIBUTES add constraint WF_ITEM_ATTRIBUTES_PK
    primary key (ITEM_TYPE, NAME);
alter table WF_ITEM_ATTRIBUTES_TL add constraint WF_ITEM_ATTRIBUTES_TL_PK
    primary key (ITEM_TYPE, NAME, LANGUAGE);
alter table WF_ITEM_ATTRIBUTES_TL add constraint WF_ITEM_ATTRIBUTES_TL_U2
     unique (DISPLAY_NAME, ITEM_TYPE, LANGUAGE);

alter table WF_LOOKUP_TYPES_TL add constraint WF_LOOKUP_TYPES_TL_PK
    primary key (LOOKUP_TYPE, LANGUAGE);
alter table WF_LOOKUP_TYPES_TL add constraint WF_LOOKUP_TYPES_TL_U2
    unique (DISPLAY_NAME, LANGUAGE);

alter table WF_LOOKUPS_TL add constraint WF_LOOKUPS_TL_PK
    primary key (LOOKUP_TYPE, LOOKUP_CODE, LANGUAGE);
alter table WF_LOOKUPS_TL add constraint WF_LOOKUPS_TL_U2
    unique (LOOKUP_TYPE, MEANING, LANGUAGE);

alter table WF_RESOURCES add constraint WF_RESOURCES_PK
    primary key (TYPE, NAME, LANGUAGE);

alter table WF_ITEM_ATTRIBUTES add constraint WF_ITEM_ATTRIBUTES_FK1
    foreign key (ITEM_TYPE)
    references WF_ITEM_TYPES (NAME);
alter table WF_ITEM_ATTRIBUTES_TL add constraint WF_ITEM_ATTRIBUTES_TL_FK1
    foreign key (ITEM_TYPE, NAME)
    references WF_ITEM_ATTRIBUTES (ITEM_TYPE, NAME);

alter table WF_LOOKUP_TYPES_TL add constraint WF_LOOKUP_TYPES_TL_FK1
    foreign key (ITEM_TYPE)
    references WF_ITEM_TYPES (NAME);

alter table WF_LOOKUPS_TL add constraint WF_LOOKUPS_TL_FK1
    foreign key (LOOKUP_TYPE, LANGUAGE)
    references WF_LOOKUP_TYPES_TL (LOOKUP_TYPE, LANGUAGE);

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

alter table WF_MESSAGES add constraint WF_MESSAGES_PK
    primary key (TYPE, NAME);
alter table WF_MESSAGES_TL add constraint WF_MESSAGES_TL_PK
    primary key (TYPE, NAME, LANGUAGE);

alter table WF_MESSAGE_ATTRIBUTES add constraint WF_MESSAGE_ATTRIBUTES_PK
    primary key (MESSAGE_TYPE, MESSAGE_NAME, NAME);
alter table WF_MESSAGE_ATTRIBUTES add constraint WF_MESSAGE_ATTRIBUTES_U2
    unique (MESSAGE_TYPE, MESSAGE_NAME, SEQUENCE);
alter table WF_MESSAGE_ATTRIBUTES_TL add constraint WF_MESSAGE_ATTRIBUTES_TL_PK
    primary key (MESSAGE_TYPE, MESSAGE_NAME, NAME, LANGUAGE);
alter table WF_MESSAGE_ATTRIBUTES_TL add constraint WF_MESSAGE_ATTRIBUTES_TL_U2
    unique (DISPLAY_NAME, MESSAGE_TYPE, MESSAGE_NAME, LANGUAGE);

/*
** Removing all runtime constraints to ensure optimal performance
*/
/*************************************************************************
alter table WF_NOTIFICATIONS add constraint WF_NOTIFICATIONS_PK
    primary key (NOTIFICATION_ID);

alter table WF_NOTIFICATION_ATTRIBUTES add constraint WF_NOTIFICATIONS_ATTR_PK
    primary key (NOTIFICATION_ID, NAME);
*************************************************************************/

alter table WF_ROUTING_RULES 
  add constraint WF_ROUTING_RULES_PK
  primary key (RULE_ID);

alter table WF_ROUTING_RULE_ATTRIBUTES 
  add constraint WF_ROUTING_RULE_ATTRIBUTES_PK
  primary key (RULE_ID, NAME, TYPE);

alter table WF_ROUTING_RULE_ATTRIBUTES
  add constraint WF_ROUTING_RULE_ATTRIBUTES_FK1
  foreign key (RULE_ID)
  references WF_ROUTING_RULES(RULE_ID);

alter table WF_MESSAGES_TL add constraint WF_MESSAGES_TL_FK1
    foreign key (TYPE, NAME)
    references WF_MESSAGES (TYPE, NAME);

alter table WF_MESSAGE_ATTRIBUTES add constraint WF_MESSAGE_ATTRIBUTES_FK1
    foreign key (MESSAGE_TYPE, MESSAGE_NAME)
    references WF_MESSAGES (TYPE, NAME);
alter table WF_MESSAGE_ATTRIBUTES_TL add constraint WF_MESSAGE_ATTRIBUTES_TL_FK1
    foreign key (MESSAGE_TYPE, MESSAGE_NAME, NAME)
    references WF_MESSAGE_ATTRIBUTES (MESSAGE_TYPE, MESSAGE_NAME, NAME);

/*
** Removing all runtime constraints to ensure optimal performance
*/
/*************************************************************************
alter table WF_NOTIFICATIONS add constraint WF_NOTIFICATIONS_FK1
    foreign key (MESSAGE_NAME, MESSAGE_TYPE)
    references WF_MESSAGES (NAME, TYPE);

alter table WF_NOTIFICATION_ATTRIBUTES add constraint WF_NOTIFICATION_ATTR_FK1
    foreign key (NOTIFICATION_ID)
    references WF_NOTIFICATIONS (NOTIFICATION_ID);
*************************************************************************/


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

alter table WF_ACTIVITIES add constraint WF_ACTIVITIES_PK
    primary key (ITEM_TYPE, NAME, VERSION);
alter table WF_ACTIVITIES_TL add constraint WF_ACTIVITIES_TL_PK
    primary key (ITEM_TYPE, NAME, VERSION, LANGUAGE);

alter table WF_ACTIVITY_ATTRIBUTES add constraint WF_ACTIVITY_ATTRIBUTES_PK
    primary key (ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, ACTIVITY_VERSION, NAME);
alter table WF_ACTIVITY_ATTRIBUTES_TL add constraint
    WF_ACTIVITY_ATTRIBUTES_TL_PK primary key
    (ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, ACTIVITY_VERSION, NAME, LANGUAGE);
alter table WF_ACTIVITY_ATTRIBUTES_TL add constraint
    WF_ACTIVITY_ATTRIBUTES_TL_U2
    unique (DISPLAY_NAME, ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, ACTIVITY_VERSION, 
            LANGUAGE);

alter table WF_PROCESS_ACTIVITIES add constraint WF_PROCESS_ACTIVITIES_PK
    primary key (INSTANCE_ID);
alter table WF_PROCESS_ACTIVITIES add constraint WF_PROCESS_ACTIVITIES_U2
    unique (INSTANCE_LABEL, PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION);

alter table WF_ACTIVITY_ATTR_VALUES add constraint WF_ACTIVITY_ATTR_VALUES_PK
    primary key (PROCESS_ACTIVITY_ID, NAME);

alter table WF_ACTIVITY_TRANSITIONS add constraint WF_ACTIVITY_TRANSITIONS_PK
    primary key (FROM_PROCESS_ACTIVITY, RESULT_CODE, TO_PROCESS_ACTIVITY);

/*
** Removing all runtime constraints to ensure optimal performance
*/
/*************************************************************************
alter table WF_ITEMS add constraint WF_ITEMS_PK
    primary key (ITEM_TYPE, ITEM_KEY);

alter table WF_ITEM_ATTRIBUTE_VALUES add constraint WF_ITEM_ATTRIBUTE_VALUES_PK
    primary key (ITEM_TYPE, ITEM_KEY, NAME);

alter table WF_ITEM_ACTIVITY_STATUSES add constraint
    WF_ITEM_ACTIVITY_STATUSES_PK
    primary key (ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY);
*************************************************************************/

alter table WF_ACTIVITIES add constraint WF_ACTIVITIES_FK1
    foreign key (ITEM_TYPE)
    references WF_ITEM_TYPES (NAME);
alter table WF_ACTIVITIES add constraint WF_ACTIVITIES_FK3
    foreign key (ITEM_TYPE, MESSAGE)
    references WF_MESSAGES (TYPE, NAME);

alter table WF_ACTIVITY_ATTRIBUTES add constraint WF_ACTIVITY_ATTRIBUTES_FK1
    foreign key (ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, ACTIVITY_VERSION)
    references WF_ACTIVITIES (ITEM_TYPE, NAME, VERSION);
alter table WF_ACTIVITY_ATTRIBUTES_TL add constraint
    WF_ACTIVITY_ATTRIBUTES_TL_FK1
    foreign key (ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, ACTIVITY_VERSION, NAME)
    references WF_ACTIVITY_ATTRIBUTES (ACTIVITY_ITEM_TYPE, ACTIVITY_NAME,
                                      ACTIVITY_VERSION, NAME);

alter table WF_PROCESS_ACTIVITIES add constraint WF_PROCESS_ACTIVITIES_FK1
    foreign key (PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION)
    references WF_ACTIVITIES (ITEM_TYPE, NAME, VERSION);

alter table WF_ACTIVITY_ATTR_VALUES add constraint WF_ACTIVITY_ATTR_VALUES_FK1
    foreign key (PROCESS_ACTIVITY_ID)
    references WF_PROCESS_ACTIVITIES (INSTANCE_ID);

alter table WF_ACTIVITY_TRANSITIONS add constraint WF_ACTIVITY_TRANSITIONS_FK1
    foreign key (FROM_PROCESS_ACTIVITY)
    references WF_PROCESS_ACTIVITIES (INSTANCE_ID);
alter table WF_ACTIVITY_TRANSITIONS add constraint WF_ACTIVITY_TRANSITIONS_FK2
    foreign key (TO_PROCESS_ACTIVITY)
    references WF_PROCESS_ACTIVITIES (INSTANCE_ID);

/*
** Removing all runtime constraints to ensure optimal performance
*/
/*************************************************************************
alter table WF_ITEMS add constraint WF_ITEMS_FK1
    foreign key (ROOT_ACTIVITY, ITEM_TYPE, ROOT_ACTIVITY_VERSION)
    references WF_ACTIVITIES (ITEM_TYPE, NAME, VERSION);

alter table WF_ITEM_ATTRIBUTE_VALUES add constraint
    WF_ITEM_ATTRIBUTE_VALUES_FK1
    foreign key (ITEM_TYPE, ITEM_KEY)
    references WF_ITEMS (ITEM_TYPE, ITEM_KEY);

alter table WF_ITEM_ACTIVITY_STATUSES add constraint
    WF_ITEM_ACTIVITY_STATUSES_FK1
    foreign key (ITEM_TYPE, ITEM_KEY)
    references WF_ITEMS (ITEM_TYPE, ITEM_KEY);
alter table WF_ITEM_ACTIVITY_STATUSES add constraint
    WF_ITEM_ACTIVITY_STATUSES_FK2
    foreign key (PROCESS_ACTIVITY)
    references WF_PROCESS_ACTIVITIES (INSTANCE_ID);
alter table WF_ITEM_ACTIVITY_STATUSES add constraint
    WF_ITEM_ACTIVITY_STATUSES_FK3
    foreign key (NOTIFICATION_ID)
    references WF_NOTIFICATIONS (NOTIFICATION_ID);

alter table WF_ITEM_ACTIVITY_STATUSES_H add constraint
    WF_ITEM_ACTIVITY_STATUS_H_FK1
    foreign key (ITEM_TYPE, ITEM_KEY)
    references WF_ITEMS (ITEM_TYPE, ITEM_KEY);
alter table WF_ITEM_ACTIVITY_STATUSES_H add constraint
    WF_ITEM_ACTIVITY_STATUS_H_FK2
    foreign key (PROCESS_ACTIVITY)
    references WF_PROCESS_ACTIVITIES (INSTANCE_ID);

alter table WF_ITEM_ACTIVITY_STATUSES_H add constraint
    WF_ITEM_ACTIVITY_STATUS_H_FK3
    foreign key (NOTIFICATION_ID)
    references wf_notifications (notification_id);
*************************************************************************/

/*
** Local directory constrainst (Objects in wfdirc.sql)
*/
alter table WF_LOCAL_LANGUAGES add constraint WF_LOCAL_LANGUAGES_PK
   primary key (CODE);
alter table WF_LOCAL_LANGUAGES add constraint WF_LOCAL_LANGUAGES_U1
   unique (DISPLAY_NAME);
alter table WF_LOCAL_LANGUAGES add constraint WF_LOCAL_LANGUAGES_U2
   unique (NLS_LANGUAGE);

/*
** WF_LOCAL_USERS is obsolete, no further action will occur on this table.
**alter table WF_LOCAL_USERS add constraint WF_LOCAL_USERS_PK
**   primary key (NAME);
**alter table WF_LOCAL_USERS add constraint WF_LOCAL_USERS_U1
**    unique (NAME, ORIG_SYSTEM, ORIG_SYSTEM_ID);
**alter table WF_LOCAL_USERS add constraint WF_LOCAL_USERS_U2
**    unique (DISPLAY_NAME);
*/
/*
** Remove constraints on directory services
**alter table WF_LOCAL_ROLES add constraint WF_LOCAL_ROLES_PK
**    primary key (NAME, PARTITION_ID);
**alter table WF_LOCAL_ROLES add constraint WF_LOCAL_ROLES_U1
**    unique (NAME, ORIG_SYSTEM, ORIG_SYSTEM_ID, PARTITION_ID);
**alter table WF_LOCAL_ROLES add constraint WF_LOCAL_ROLES_U2
**    unique (DISPLAY_NAME, PARTITION_ID);
*/

commit;
exit;