7 PeopleSoft Database Installation Tasks

This chapter includes:

• Prerequisite

• Copy PeopleSoft Specific Files

• Create Tablespaces

• Create the ODI Staging Database User

• Create Database Link

• Run the PeopleSoft Initial Database Setup

• Verification

Prerequisite

It is assumed Oracle database has already been configured and loaded with the appropriate PeopleSoft schema details for your installation. Skip the entire section if you are integrating with EBS.

Copy PeopleSoft Specific Files

Note: You can skip the next steps and proceed to section Create Tablespaces if you can connect to PeopleSoft Database from ORFI Server using Sqlplus.

1. Log in to the PeopleSoft UNIX server as a user who has sufficient access to run sqlplus from the Oracle Database installation for SYSADM schema.

2. Create the retail-financial-integration-solution/service-based-integration folder in the <INSTALL_DIR> folder.

3. From the ORFI server, copy <INSTALL_DIR>/retail-financial-integration-solution/service-based-integration/setup-data folder to <INSTALL_DIR>/retail-financial-integration-solution/service-based-integration folder in PeopleSoft server.

Create Tablespaces

Before you run the ODI Staging database user, make sure that the following tablespace has been created in the database: ODI_STAGE_DATA. Below is a sample tablespace creation statement for this tablespace. Oracle Retail recommends the use of locally managed tablespaces with automatic extent and segment space management.

Note: These tablespaces are not sized for a production environment. The customer's Database Administrator should create the tablespace based on their datacenter database architecture and size it appropriately based on expected data volume for their peak data usage.

CREATE TABLESPACE ODI_STAGE_DATA DATAFILE
'<datafile_path>/odi_stage_data01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
;

Create the ODI Staging Database User

You must create an ODI Staging schema using a create_user.sql, included with the installer distribution. This will be used to execute the ODI Interface at run time. The create_user.sql script can be used to create the schema:

$INSTALL_DIR/retail-financial-integration-solution/service-based-integration/setup-data/ddl/create_user.sql

The script takes three arguments on the command line in sqlplus: username, password, and ODI Stage tablespace.

For example:

SQL> @create_user.sql RFI_ODI_TEMP <RFI_ODI_TEMP Schema Password> <ODI Stage Tablespace name>

Please review this script and run it as a user with adequate permissions, such as SYSTEM.

Connect to RFI_ODI_TEMP schema as sysdba and grant all privileges to AP schema available on PeopleSoft Database. Grant all privileges will be used for CRUD operation in RFI_ODI_TEMP tables while running ODI interfaces.

Create Database Link

You need to create a database link to load initial data for currency rate,suppliers and payment terms from Oracle PeopleSoft Suite to the Oracle Retail Financial Integration application.

1. Log in to the SYSADM database schema in the PeopleSoft database to run create_dblink.sql script.

2. Run the create_dblink.sql script available in $INSTALL_DIR/retail-financial-integration-solution/service-based-integration/setup-data/ddl/from PeopleSoft SYSADM database schema.

   The script takes four arguments on the command line: DB link name (PSFT_RFI_DBLINK), ORFI schema name, ORFI schema password, and ORFI DB URL.

   For example:

   SQL> @create_dblink.sql <PSFT_RFI_DBLINK> <ORFI Schema> <ORFI Schema Password> <ORFI DB Host:port/SID>

   
   

   Note: This database link will be used to load initial data for Currency Rate, Suppliers and Payment Terms from PeopleSoft to ORFI Staging tables. If the database link name is different from the default (PSFT_RFI_DBLINK), then the user needs to update the database link name in the SQL scripts which are available in retail-financial-integration-solution/etl-based-integration/ retail-to-psftfin-etl-flows/setup-data/dml.

   
   

Run the PeopleSoft Initial Database Setup

This installer installs the ORFI related objects to the PeopleSoft SYADM database schema.

Note: Ensure, sqlplus is executable with correct tns configuration information to PeopleSoft database is set in your path.

1. 1. Run the load-initial-setup-data-into-psftfin-schema.sh script available in $INSTALL_DIR/ retail-financial-integration-solution/service-based-integration /bin/. After installation is completed, a detailed installation log file is created: $INSTALL_DIR/logs //<timestamp>.log.

   For example: prompt$ sh load-initial-setup-data-into-psftfin-schema.sh

   sh load-initial-setup-data-into-psftfin-schema.sh
   

2. The installer prompts for the following details:

   • Enter the PeopleSoft database server SID - Enter Database SID.

   • Enter the PeopleSoft Schema details -Enter APPS User ID/Password.

   • Enter the PeopleSoft database server name - Enter Database Server Name or IP address.

   • Enter the PeopleSoft database server port - Enter Database Port Number.

   
   

   Note: If you get the following error, make sure you have downloaded latest version of the ORFI software from Oracle download site and tns is configured. ERROR: ORA-12154: TNS: could not resolve the connect identifier specified.

   
   

Verification

Log in to the SYSADM database schema in the PeopleSoft database and verify that the RFI_PS_ORT_ACCT_ENTRY_SEQ sequence is available using below query:

"SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME='RFI_PS_ORT_ACCT_ENTRY_SEQ' AND OBJECT_TYPE='SEQUENCE';".