On The Peoplesoft Road : Peoplesoft Database creation - Part II

After loading the Peoplesoft Application (including PeopleTools objects) in the database creation part I, we should upgrade the database to the latest release we curretly use. This step is not required if your application has been built (I mean the Appliatin database version) on the same PeopleTools which you use.
Since we currently install HRMS9.0 on PT8.49, and like for all the version 9.0 it has been built on PT8.48, we should upgrade the Applicatin database version. It consists on several scripts (sql, dms) and application of projects. Some of the following steps below are also for multilingual application, don't apply these step if your only in english.
The scripts to be apply are under $PS_HOME/scripts, the projects under $PS_HOME/projects

1. Run the sql script update of maintenance table (/apps/psoft/hrms9/scripts) :
[hrms9@orion:/apps/psoft/hrms9/scripts]$ sqlplus sysadm/sysadm@DMOHRMS9

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 6 20:58:53 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @rel849un.sql
SQL> UPDATE PSSTATUS SET TOOLSREL='8.49',
2 LASTREFRESHDTTM = SYSDATE
3 ;

1 row updated.

SQL>
SQL> CREATE UNIQUE INDEX PSAPSROLECLASS ON PSROLECLASS (CLASSID,
2 ROLENAME) TABLESPACE PSINDEX STORAGE (INITIAL 40000 NEXT 100000
3 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PARALLEL NOLOGGING
4 ;

Index created.

SQL> ALTER INDEX PSAPSROLECLASS NOPARALLEL LOGGING
2 ;

Index altered.

SQL>
SQL> CREATE INDEX PSCPSTREELEAF ON PSTREELEAF
2 (SETID,
3 TREE_NAME,
4 EFFDT DESC,
5 TREE_NODE_NUM,
6 RANGE_FROM,
7 RANGE_TO) TABLESPACE PSINDEX STORAGE (INITIAL 40000 NEXT 100000
8 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PARALLEL NOLOGGING
9 ;

Index created.

SQL> ALTER INDEX PSCPSTREELEAF NOPARALLEL LOGGING
2 ;

Index altered.

SQL>
SQL> COMMIT
2 ;

Commit complete.

SQL> SPOOL OFF
SQL>

2. Run the grant script to allow the PEOPLE user to read the tables as well (PSSTATUS, PSOPRDEFN, PSACCESSPRFL) :
[hrms9@orion:/apps/psoft/hrms9/scripts]$ sqlplus sysadm/sysadm@DMOHRMS9 @grant.sql

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 6 21:03:03 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options


Grant succeeded.


Grant succeeded.


Grant succeeded.


Commit complete.

SQL>

3. As user PS, open the DataMover, modify and run storeddl.dms (comment out the database you are not running on) :

4. In bootstrap mode (user which is the owner of objests), SYSADM in the current case, open DataMover, modify (only the log output file) and run msgtlsupg.dms

5. In Application Designer, as PS user (you cannot connect in bootstrap mode - SYSADM - within the AppDesigner), copy the project PPLTLS84CUR
=> Don't build it now !
Take care, the project is very big and take age to copy (network avaibility !)
Some messages can be ignore. Don't build the project now !

6. In Application Designer, as PS user, copy the project PPLTLS84CURML or if you installed French, copy the project PPLTLSML
=> Don't build it !
Don't build the project !

7. In Application Designer, as PS user, copy the project PPLTLS84CURDEL
=> Don't build it !
Don't build the project !

8. In Application Designer, as PS user, open and build the project PPLTLS84CUR
Open the settings to set all the parameters as well :Name a sql script to be run after project building :Take care to all the alter and create tabs options :Some warning are raised, you can ignore them.

9. Run the SQL script created by the previous project's build in SQL*Plus
At this step, you shouldn't have any error, check the spool file as well.

10. In Application Designer, as PS user, copy and build the project PT84TBLSPC
In the copy option, take care to check the DDL from source option :Build the project :Take care of the build settings, they are a different than previously :
No warnings, no errors.

11. Run the SQL script created by the previous project's build in SQL*Plus

12. Update the Peopletools system data, run the following DMS scripts in bootstrap mode (from $PS_HOME/scripts) : pt849tls.dms, pslanguages.dms, tlsupgnoncomp.dms

13. For ML application, run the following statement in DMS (take from the install doc the three letter code of your language, FRA is for french) :
UPDATE PSLANGUAGES SET INSTALLED = 1 WHERE LANGUAGE_CD='FRA';

14. In DMS, as PS user, run the following DMS scripts (from $PS_HOME/scripts):
pt849tlsfra.dms (if you installed French db), msgtleng.dms, ptstreng.dms, $PS_HOME/src/cbl/base/storept.dms, ptdefnsec.dms
and createvw.dms, this last one will (re)create around 18000 views, so take care to the time to apply.

15. Run application engine from your client, go to $PS_HOME/bin/client/winx86:
This query define the user who can run the AEs
SQL> select roleuser, rolename from psroleuser where rolename in ('PeopleSoft Administrator', 'Portal Administrator');

ROLEUSER ROLENAME
------------------------------ ------------------------------
PSEM PeopleSoft Administrator
PSHC PeopleSoft Administrator
PS Portal Administrator
PSEM Portal Administrator
Better to use PS, then in a DOS Window :
psae -CD DMOHRMS9 -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPG844PORTAL
psae -CD DMOHRMS9 -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPGQRYDUPHED
psae -CD DMOHRMS9 -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPGPTSMDAT
psae -CD DMOHRMS9 -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPGPT846PP
psae -CD DMOHRMS9 -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPGPT848PP
psae -CD DMOHRMS9 -CT ORACLE -CO PS -CP PS -R INSTALL -AI UPGPT848IBUG (addendum : you should NOT run this AE if the Application you are installing has been built on Peopletools 8.48 or later)
***note CD => dbname, CT => dbtype, CO => user, CP => pwd, R => run control, AI => AppEngine name***

16. In Application Designer, as PS user, open the project PTUPGIBCLONE and copy to a file

(addendum : if the Application you are installing has been built on Peopletools 8.48 or later go directly to step 20)

17. In DMS, as PS user, run $PS_HOME/scripts/ptupg_trx_export.dms

18. In Application Designer, as PS user, open the project PTUPGIBDELETE and copy to a file.

19. In Application Designer, as PS user, copy the project PTUPGIBDELETE from the file previously created, since the project already exists in database, you should be sure to check "From File"
20. Within SQL*Plus, run the grant.sql script.

The PeopleTools upgrade is finished.