Installation and Upgrade > Windchill ESI > Installing and Configuring Windchill ESI in an ORACLE Applications Environment > Using the Middleware Installation and Configuration Utility > Creating ESI User Accounts and Configuring Oracle for Windchill ESI > Setting up an Oracle User Account for Windchill ESI
  
Setting up an Oracle User Account for Windchill ESI
To set up the required Oracle user account for Windchill ESI use the following procedures:
1. Navigate to the �<Tibco_Home>/esi/scripts/<<Oracle Apps version>> folder and locate the series of PL/SQL scripts that you need to create all required Oracle database accounts, tables, indexes, triggers, sequences, synonyms, grants, and stored procedures to support Windchill ESI.
2. Check to see if the USER-DATA table space exists before running the SQL scripts. To create a table space see the following UNIX and Windows examples:
UNIX:
CREATE TABLESPACE "USER_DATA" LOGGING DATAFILE �/d905/oracle/tstddata/dat01.dbf� SIZE 500M AUTOEXTENT ON NEXT 128K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO;
Windows:
CREATE TABLESPACE "USER_DATA" LOGGING DATAFILE �d:/oracle/tstddata/dat01.dbf� SIZE 500M AUTOEXTENT ON NEXT 128K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO;
To run the create_user_Mods.sql script, login as a System user.
3. Using your SQL database client, execute the scripts in the order indicated in the following table. These scripts will prompt you for user names and passwords for the apps account (APPS), and the ESI database schema user account (ESISYS). You must resolve any execution or compilation errors that may result due to an unsupported system configuration. The script �create_user_Mods.sql� is provided to create ESI database user schema.
The following table lists scripts and their purpose. For detailed information about the database objects created by these scripts refer to the Windchill Enterprise Systems Integration Implementation Guide - Oracle Applications
Script Number
Script Name
Purpose
Additional Information
1
common_all_Mods.sql
Creates common interfacing components for TIBCO, such as read/write grants to ESISYS users for objects that are accessible from the Applications schema. Creates the PK_TIB_LOG package inside the Applications schema. Creates TIBCO log tables inside the ESI user schema.
When prompted for TABLE_TBLSPACE, enter the following: user_data.
2
ptc_esi_parameters_do.sql
Creates the PTC_ESI_Parameters table. This table stores information for the generation of the instrumented file. This table also stores the status of the package (valid or invalid) and its creation date. Also stored in this table is the ESI version of the package.
1. The default para_value where para_name has a value of �instrumented� is N.
2. When prompted to enter the value for the file location use a location from the UTL_FILE_DIR parameter value.
3. If an INVALID LOCATION message appears then confirm that the debug file location is correct.
3
MasterConfiguration_TIBCOLogTablePublisher_all_Mods.sql
Creates the TIBCO Log Publisher intermediate table (P_tib_int_log_sub) for TIBCO and the trigger that calls the newly inserted data from the TIB_int_log_sub table to the P_TIB_int_log_sub table.
The adapter polls the P_-tib_int_log_sub table every 5 seconds for the results sent by Oracle.
4
MasterConfiguration_OpenItem_all_Mods.sql
Creates TIBCO interface tables for Open Item (Parts in Windchill) in ESI user schema. Creates pk_tib_openitem_import package in Applications chema and grants execute rights to ESI user schema.
The package triggers the concurrent manager for Open Item import in Oracle Applications.
* 
You must have CREATE SYNONYM grants to execute this script.
5
MasterConfiguration_BillsOfMtlAPI_all_Mods.sql
Creates TIBCO interface tables for Bills of Material (BOM in windchill) in ESI user schema. Creates pk_tib_openitem_import package in Applications schema and grants execute rights to ESI user schema.
This package invokes the BOM API provided by Oracle Applications.
6
MasterConfiguration_EnggChangeOrder_all_Mods.sql
Creates TIBCO interface tables for ECO data (ECN in Windchill) in ESI user schema.Creates pk_tib_engchord_importpackage in Applications schema and grants execute rights to ESI user schema.
This package invokes the ECO API provided by Oracle Applications.
7
MasterConfiguration_Routing_BOM_do.sql
Creates TIBCO interface tables for Routing data (Process Plan in Windchill) in ESI user schema. Creates PTC_RTG_BOM and PTC_RTG_API packages in Applications schema and grants execute rights to ESI user schema.
PTC_RTG_API invokes the routing API provided by Oracle Applications.
8
ESI_PartDetails_Mods.sql
Gives selected grants of objects to ESI user schema.
9
ESI_nls_parm_trg.sql
Creates a trigger that sets the NLS parameters. For more information see Configuring Oracle NLS Language Parameters for use with ESI.
10
create_user_Mods.sql
Creates the required Oracle Applications user account (ESISYS) for Windchill ESI, with the required security grants.
* 
The MasterConfiguration_Routing_BOM_do.sql script creates the following tables, indexes, and sequences in the local schema and their synonyms in the APPS schema:
• PTC_INT_RTG_API_HDR,
• PTC_INT_RTG_API_REV,
• PTC_INT_RTG_API_OPR,
• PTC_INT_RTG_API_OPR_RES
The columns used in the above tables are in the same lines of the RTG API (BOM_RTG_PUB). This means that the column name and the data type used are the same as those for the RTG API.
This script creates the following two packages viz:
1. PTC_RTG_BOM: Contains a single procedure, BILLS_ALL,that is used for BOM Transactions, and for BOM creation and updating.
2. PTC_RTG_API: Used for RTG + BOM API Transactions. This includes Atomic Transactions, which calls procedure BILLS_ALL and commits the BOM after the routing is successfully created. If it is not successfully created then the combined RTG + BOM is rolled back.
RoutingAPI_Validation picks the Routings data from the Routings Intermediate tables (where �Tibco_int_process_flag = 2�) and validates the data for Routings. This means that it internally calls the procedure BILLS_ALL for BOM creation, after which the BOM data is fetched from the Intermediate tables of the BOM where �Tibco_int_process_flag = 2� where the BOM data are validated. After the RTG and BOM are both successful, RTG + BOM are transacted to the Oracle GUI using the RTG & BOM API.
If the Routing is successfully created and the BOM is also successfully created then RTG+BOM is committed together. If RTG is successfully created & BOM fails then the whole RTG+BOM is rolled back. If RTG fails then it alone is rolled back.
* 
The ptc_esi_parameters_do.sql script creates the table PTC_ESI_PARAMETERS in the local schema and its synonym in the APPS schema, with two columns viz: PARA_NAME, and PARA_VALUE. This serves two purposes:
1. This creates the LOG file after invoking the instrumented feature which uses UTL_FILE package (Oracle Supplied Package) incorporated into OPEN_ITEM, BILLS_OF_MTL, ECO & RTG+BOM atomic scripts. This is dependent upon the flag �Y� or �N� in PARA_VALUE.
2. Prompts the user for the file location where the LOG file is to be created. This is stored in the PARA_VALUE column of the �DEBUG_FILE_LOCATION� value. This file location is passed to OPEN_ITEM, ECO, BOM, BOM+RTG (Atomic) Scripts.
These PL/SQL scripts must be used in place of the similar scripts that are installed with or generated by the native TIBCO Adapter for Oracle Applications. In many cases, the scripts have been modified from the standard TIBCO-provided versions, specifically to enable Windchill ESI functionality and to correct known TIBCO software defects. These modified scripts contain the "_Mods" designation.
Also included are a series of scripts with an "_undo" designation.
* 
You should not run these scripts now.
In the event that you need to later uninstall the Oracle Applications components, execute the "_undo" scripts in the following order:
1. MasterConfiguration_EnggChangeOrder_undo_all_Mods.sql
2. MasterConfiguration_BillsOfMtlAPI_undo_all_Mods.sql
3. MasterConfiguration_OpenItem_undo_all_Mods.sql
4. MasterConfiguration_TIBCOLogTablePublisher_undo_all.sql
5. common_all_undo.sql
To ensure that the ESISYS account security grants comply with your company's security policies, review the precise Oracle Applications access requirements required byWindchill ESI with your Oracle Applications administrators. For reference, the primary windows that correspond to the APIs used by theWindchill ESI are as follows:
Oracle Applications Navigator Path
Description
Inventory > Items > Master Items
Create/Change Parts
Bill of Materials > Bills > Bills
Create/Change BOMs
Engineering > ECOs > ECOs
Create CNs
Bill of Materials > Routing > Routing
Create/Change Routings