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
  
Creating ESI User Accounts and Configuring Oracle for Windchill ESI
This section provides an overview on how to create a Windchill ESI user account and on how to configure Oracle Applications distribution targets. It involves the following tasks:
• See the topic Setting up an Oracle User Account for Windchill ESI
• Configuring User, Responsibility, and Application for Object Processing
• Generating Unit Numbers in Oracle Applications
• Generating Departments and Resources in Oracle Applications
Assumptions
The instructions in the following sections make the following assumptions:
• You are aware of the assumptions and have applied the support packs and OSS notes listed Distribution Target Prerequisites and Assumptions. Windchill ESI may not function as expected on distribution targets that do not match these criteria.
• You are acquainted with Oracle database administration and have access to a suitable SQL database client.
• You are familiar with PL/SQL scripts, stored procedures, and database security concepts.
• You have administrator privileges.
* 
Since distribution targets can be customized some of the options presented in the following steps may not match what you have on your systems. Refer to the documentation provided with your systems for specific and detailed information.
* 
If you use the ECO/CN functionality within Windchill ESI, you must make the following changes to your Oracle Applications instance. In each of the organizations to which you will be publishing objects, the Starting Revision level must be set to a level with an ASCII value less than A; zero (0) is a good option. If you are using version padding in the ESI Middleware, a value of " 0" (<space><space>0) is recommended.
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 Using the Middleware Installation and Configuration Utility .
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
Configuring User, Responsibility, and Application for Object Processing
When installing the MasterConfiguration_OpenItem_all_Mods.sql script, you are prompted for values for user, responsibility, and application. These values are used by TIBCO to submit the Import Items concurrent request in the Oracle ERP system. The Import Item concurrent request is responsible for processing items using the Oracle Item Open Interface. The user, responsibility, and application values are used to set the security context of the Oracle ERP system. This needs to be done when connecting directly to the ERP system database instance, and allows the Import Items concurrent program to run with the appropriate permissions.
* 
You must have CREATE SYNONYM grants to execute this script.
The combination of user, responsibility, and application can be for any Oracle ERP user that can submit the Import Items concurrent program. The Import Items concurrent program is usually found on the main menu for the inventory responsibility. The navigation path is Items:Import:Import Items.
PTC recommends creating a new Oracle ERP system user, such as ESI, specifically for use with Windchill ESI. This allows information created byWindchill ESI to be easily tracked in the ERP system. Oracle retains user and timestamp information when creating or updating data. To view this information, navigate to the help menu of the main form, and select Record History.
PTC also suggests manually submitting the Import Items concurrent program with selected users to ensure the program is working properly. Any issues encountered when manually submitting the Import Items program affects Windchill ESI. The errors should be resolved before attempting to publish information using Windchill ESI.
To determine the values for user, responsibility, and application for use when running the MasterConfiguration_OpenItem_all_Mods.sql script, log into the Oracle ERP system with the user, and responsibility that you plan to use with Windchill ESI. Once logged in, select Help from the main menu, then select About Oracle Application. This displays a form that includes the user, responsibility, and application information. An example of the output from the About Oracle Applications window is shown:
Oracle Applications
Copyright 1986-1999 Oracle Corporation,
Redwood Shores, California.
All Rights Reserved
----------------------------------------------------------
Login
----------------------------------------------------------
Site: PTC Operations
Application: Oracle Inventory
Responsibility: Inventory
Security Group: Standard
User Name: ESI
In the previous example, the values of ESI and Inventory get entered for Username and Responsibility when running the script. The value for Applications, however, needs to be translated to its short name before it can be entered.
The short name for an Application is displayed on the Application:Register form. To access the form, log into the Oracle ERP system as the System Administrator. Query the Application:Register form using the Application name that was displayed in your login information. In the previous example, the value of Oracle Inventory would be entered when querying the form. The resulting query would return a value of INV. When running the MasterConfiguration_OpenItem_all_Mods.sql script for the above example, the value for Application that would be entered is INV.
Generating Unit Numbers in Oracle Applications
Oracle Applications does not support generating unit numbers from the back end or from external interfaces (like public API). In order to support unit effectivity in Oracle Applications, the serial or lot effectivity values must already exist in Oracle Applications as unit numbers before the CN containing the serial or lot effectivity values is published.
Generating Departments and Resources in Oracle Applications
Oracle Applications does not support generating departments and resources from the back end of external interfaces (like public API). In order to create operations in Oracle Applications, the department must already exist. To assign resources to operations the resources must also already exist and be assigned to a particular department.
Global Variables for Process Engines
See Preferences, Distribution Target Attributes and Global Variable Groups for a list and description of global variables. At a minimum, you must enter a value for the DSN, ESIJMSUsername and ESIJMSPassword