Steps to Customizing a New PeopleSoft Database for UCRFS
Steps To Customizing
A New PeopleSoft Database
For UC Riverside
About
The following describes the myriad steps one must accomplish to prepare an “out of the box” database for use with the UCRFS system.
Note: Any items below followed by an ??? imply that that feature appears important but is as yet to be determined as such.
I. On the UNIX file system
1) Check and make sure that there is enough disk space for the Application Server. For example, in UNIX type the following:
$ df -hk
Filesystem size used avail capacity Mounted on
/dev/dsk/c1t0d0s0 36G 35G 415M 99% /
/proc 0K 0K 0K 0% /proc
mnttab 0K 0K 0K 0% /etc/mnttab
fd 0K 0K 0K 0% /dev/fd
swap 43G 24K 43G 1% /var/run
swap 512M 16K 512M 1% /tmp
/dev/dsk/c1t1d0s6 67G 33G 34G 50% /u06
Note that the device /dev/dsk/c1t0d0s0 has 1% capacity remaining. When this goes to 100% capacity, then the portal page will not load at all, although the Application Designer (2 tier) will load.
2) Application Server & Process Scheduler - Locate the PeopleSoft Server Administration utility, PSADMIN, on both the Application Server and Process Scheduler UNIX machines, eg:
tux@ora02:/u06/PT8.44.10/appserv
or
/u05/PT8.44.10/appserv
and run the command
./psadmin
in each case. It is the same command for either.
For the Process Scheduler, navigate through these menus:
(2) Process Scheduler
(3) Configure a Process Scheduler Server
Mimic the following settings: (Please note that not all settings are included, rather only those of interest)
Values for config section – Startup
DBName=UCRTM2
DBType=ORACLE
UserId=VP1
ConnectId=people
ServerName=
Values for config section – Trace
TraceSQL=1
Values for config section - Process Scheduler
PrcsServerName=PSUNX
Values for config section – PSAESRV
Max Instances=6
Recycle Count=3
Allowed Consec Service Failures=3
Max Fetch Size=0
Values for config section – PSAEOSRV
Max Instances=6
Recycle Count=3
Allowed Consec Service Failures=3
Max Fetch Size=0
Values for config section – PSOPTENG
Max Instances=6
Service Timeout=10
Opt Max General Services=2
Note: The Distribution Server must be running for reports to work.
Values for config section – PSDSTSRV
Min Instances=3
Max Instances=6
Recycle Count=3
Allowed Consec Service Failures=1
Max Fetch Size=0
Note: You may need to explicitly specify the directory names and not use the aliases such as %PS_HOME%. There has been problems with this before.
Values for config section – SQR
SQRBIN=/u05/PT8.44.10/bin/sqr/ORA/bin
PSSQRFLAGS=-ZIF%PS_HOME%/sqr/pssqr.unx
Print Log=Y
Enhanced HTML=Y
PSSQR1=%PS_HOME%/UCRsqr
PSSQR2=%PS_HOME%/sqr
Values for config section - SMTP Settings
SMTPServer=smtp.ucr.edu
SMTPSender=
Do you want the Application Engines configured (y/n)? [y]:
Do you want the Master Scheduler configured (y/n)? [y]:
Do you want the Optimization Engines configured (y/n)? [y]:
and boot the Process Scheduler.
3) Using the utility PSADMIN for the Application Server, enable the Publish/Subscribe services for report distribution. If these are not running, then users will not be able to view their reports via the web.
See IV – Portal Customizations for more important information about report distribution.
4) Create a new directory for our customized SQR’s and update the Process Scheduler configuration file to point PSSQR1= to this directory (see above). For example:
tux@ora03:/u05/PT8.44/UCRsqr
5) Edit the configuration.properties UNIX file as follows:
a. Search for the text
WebProfile=DEV
and replace with our Web Profile configuration; usually the name of the database, eg:
WebProfile=UCRFS (or UCRTM2, etc)
b. Search for the text:
ReportRepositoryPath=/opt/psreports
and ensure that this directory does exist on the Application Server with Read access for everyone. You may not rename the directory /psreports/.
6) Important directories – The following is a list of important directories and what they contain. Everything necessary to accommodate PeopleSoft can be found in this list.
On the Application Server (ORA02)
/u06/PT8.44.10 / PeopleSoft Home./u06/PT8.44.10/appserv / Application Server. You can run the ./psadmin utility from here
/opt/psreports/ / Report Depository. Report and log files are transferred here from the Process Scheduler. You must know the database name, report date and report ID to locate any specific report.
/u06/PT8.44.10/appserv/ UCRTM/LOGS / Log files for each App Server process and users who are/have logged into the portal or Application Designer. Look for *.LOG or *.tracesql files (note: TraceSQL=1 setting)
If the App Server crashes, identify the process and locate its log file here to send to PeopleSoft
/u06/PT8.44.10/appserv/ UCRTM/CACHE / Cache files that may need to be deleted when problems arise
/u06/PT8.44.10/webserv/ peoplesoft/applications/peoplesoft/PORTAL/WEB-INF/psftdocs/ps / Location of configuration.properties and the web pages used to signon and logout. Look below for the section “Web Profile Configuration”.
Note: The “ps” in the path denotes the default webserver.
/u06/PT8.44.10/webserv/peoplesoft/ / The Web Server directory. Look for 2 files: stopPIA.sh and startPIA.sh to manage the Web Server. You must be root to run these shell scripts successfully
/opt/psreports/<databasename>/<DateThatReportRun>/<ReportID>/ / The directory where the reports are located.
On the Process Scheduler (ORA03)
/u05/PT8.44.10/appserv/prcs/UCRTM2 / The configuration file for the process scheduler/u05/PT8.44.10/appserv/prcs/UCRTM2/LOGS / Process Scheduler log files directory
/u05/PT8.44.10/appserv/prcs/UCRTM2/log_output / Where the Process Scheduler initially creates all the reports before being transferred over to the Report Depository on the Application Server
/u05/PT8.44.10/appserv/prcs/UCRTM2/Archive / Backup directory of the Process Scheduler configuration files.
/etc/ORACLE/WALLETS/DATABASES / Oracle wallet for Single Signon security
/u05/PT8.44/UCRsqr/ / SQR programs
II. On the database
1) Verify that the schema FSMARC from PeopleSoft v6 is in place so that we may load custom data from PeopleSoft v6 tables
2) Run script to move our customized data over
Script name: /UCRTM2/Scripts/Insert FSMARC Tables into PSv8 UCR Custom Tables.pdc
Location: Visual SourceSafe
Folder: /UCRTM2/Scripts
3) Create the Oracle sequences: PAN_NUMBER (for PAN) and PROVISION_NUMBER (for Staffing)
4) Compile the stored function sso_validation_ticket(). This code is wrapped.
a. Log into Source Safe and navigate to /UCRTM2/Scripts/SSO_Validation_Ticket.fnc
b. Check this file out and compile it in PL/SQL.
5) Run the DataMover scripts to migrate the user, role, permission list, etc, information. Please contact Marc Guerra first for his current opinion on migrating users.
a. First, run the Export script SECURITYEXPORT.DMS in the new database and save the resulting DAT file under a special name. Mark the file as “Read Only”. If anything goes wrong, use this file to restore the security structure.
b. Next, run the customized Export script UCR_SECURITYEXPORT_NO_USERS.DMS on the development database (FSQA) and save the resulting DAT file under a special name, D:\TEMP\FSQA_SECURITYEXPORT_NO_USERS.DAT.
c. Finally, run the Import script SECURITYIMPORT.DMS on the new database to read in the previously exported data, D:\TEMP\FSQA_SECURITYEXPORT_NO_USERS.DAT.
d. After running these scripts, the Access password will be corrupted. To correct this, shutdown the Application Server and Process Scheduler and log into the Data Mover (boot strap mode, SYSADM/SYSADM) and execute the following command:
CHANGE_ACCESS_PASSWORD SYSADM1 SYSADM.
Restart the Application Server and Process Scheduler and try logging into the Application Designer. If that doesn’t work, verify that the Connect ID and password are correct.
e. Script names:
· UCR_SECURITYEXPORT_NO_USERS.DMS - This customized script exports all of the PeopleTools security tables EXCEPT the user related information as the upgrade process should’ve already moved the PeopleSoft v6 users.
· SECURITYIMPORT.DMS - This script imports all of the PeopleTools security tables from the export script’s DAT file.
Note: On the file server, the directory to our customized DataMover scripts are:
\\snaptestnw\ps88\pt844\scripts_ucr\
6) After moving the UCR projects in the Application Designer, and creating the tables, drop all the stored procedures and functions and packages. Then, recompile from the source database.
7) Create the tablespaces UCR_PARENT, UCR_CHILD and UCR_GRANDCHILD. This is necessary before trying to create any tables in the Application Designer. This step is NOT complete until you go into the portal and configure these tablespaces. Refer to Section IV - PeopleTools Administration.
8) Update the PS_LEDGER table.
Run the following SQL statements to update the encumbrances in the PS_LEDGER table:
UPDATE PS_LEDGER
SET LEDGER = 'FAISAL'
WHERE BUSINESS_UNIT = 'UCR'
AND LEDGER = 'ENCUMB'
AND ( FISCAL_YEAR = 2002 OR FISCAL_YEAR = 2003 OR FISCAL_YEAR = 2004 OR FISCAL_YEAR = 2005)
/
INSERT INTO PS_LEDGER
SELECT
BUSINESS_UNIT,
LEDGER,
ACCOUNT,
' ', --ALTACCT
DEPTID,
' ', --OPERATING_UNIT
' ', --PRODUCT
FUND_CODE,
APPROPRIATION_NBR, --CLASS_FLD
PROGRAM_CODE,
' ', --BUDGET_REF
AFFILIATE,
' ', --AFFILIATE_INTRA1
' ', --AFFILIATE_INTRA2
' ', --CHARTFIELD1
' ', --CHARTFIELD2
' ', --CHARTFIELD3
PROJECT_ID,
' ', --BOOK_CODE
' ', --GL_ADJUST_TYPE
CURRENCY_CD,
STATISTICS_CODE,
FISCAL_YEAR,
ACCOUNTING_PERIOD,
SUM(ENCUMBERED_AMOUNT), --POSTED_TOTAL_AMT
0, --POSTED_BASE_AMT
0, --POSTED_TRAN_AMT
'USD', --BASE_CURRENCY,
SYSDATE, --DTTM_STAMP_SEC,
0 --PROCESS_INSTANCE
FROM FSMARC.PS_LEDGER
WHERE BUSINESS_UNIT = 'UCR'
AND LEDGER = 'ENCUMB'
AND ( FISCAL_YEAR = 2002 OR FISCAL_YEAR = 2003 OR FISCAL_YEAR = 2004 OR FISCAL_YEAR = 2005)
GROUP BY
BUSINESS_UNIT,
LEDGER,
ACCOUNT,
' ', --ALTACCT
DEPTID,
' ', --OPERATING_UNIT
' ', --PRODUCT
FUND_CODE,
APPROPRIATION_NBR, --CLASS_FLD
PROGRAM_CODE,
' ', --BUDGET_REF
AFFILIATE,
' ', --AFFILIATE_INTRA1
' ', --AFFILIATE_INTRA2
' ', --CHARTFIELD1
' ', --CHARTFIELD2
' ', --CHARTFIELD3
PROJECT_ID,
' ', --BOOK_CODE
' ', --GL_ADJUST_TYPE
CURRENCY_CD,
STATISTICS_CODE,
FISCAL_YEAR,
ACCOUNTING_PERIOD
--ENCUMBERED_AMOUNT (POSTED_TOTAL_AMT )
--POSTED_BASE_AMT
--POSTED_TRAN_AMT
--BASE_CURRENCY,
--DTTM_STAMP_SEC,
--PROCESS_INSTANCE
/
III. In the Application Designer
1) The following projects must be exported/imported or upgraded depending on the PeopleSoft version. Please see the next step before actually exporting your project over.
a. UCR_ALLTABLES
b. UCR_ACCOUNT_LEDGER
c. UCR_BEA
Note: The BEA uses a number of Message Catalog entries. These must be moved over. Go to menu PeopleTools > Utilities > Administration > Message Catalog and search for 21000. See project UCR_MESSAGE_CATALOG for more information. It appears as though PeopleTools doesn’t properly move Message Catalog entries over, so this has been scripted in
d. UCR_BEA_ZERO_DOLLAR
e. UCR_CHARTFIELD_ATTRIBUTES
Note: Two fields have been renamed since PeopleSoft hijacked FUND_TYPE and ???.
f. UCR_CHECKWRITE
g. UCR_CLOSING
h. UCR_DATAMART
i. UCR_DATA_WAREHOUSE
j. UCR_DENORM_TREE_TABLES
k. UCR_EMPLVNDR
l. UCR_FCT
m. UCR_FEDERAL_REBATE
n. UCR_FEEDER
o. UCR_ICA
This is John Kong’s project for “Indirect Cost Allocation Modifications”, which involves altering the PS_FUND_INCOST_TBL. Please see the document, “Indirect Cost Allocation Mods”
p. UCR_INDEXES
Note: This project is a collection of our PeopleSoft v6 indices recreated for PeopleSoft v8. In the case of Journal Header and Line, the upgrade process does not preserve our indices and we created anew in UCRTM2.
q. UCR_JOURNAL_TABLE_UPDATE
r. UCR_MANAGE_BATCH_PROCESSES
s. UCR_MESSAGE_CATALOG
This project does NOT seem to move over the message entries for 21000 and 21001. Consider using a SQL script to move data.
INSERT INTO PSMSGCATDEFN
SELECT * FROM D
WHERE message_set_nbr IN (21000, 21001)
/
INSERT INTO PSMSGSETDEFN
SELECT * FROM D
WHERE message_set_nbr IN (21000, 21001)
/
t. UCR_NCT
u. UCR_OPEN_ITEM
Note: Customizations based on Marc’s specifications
v. UCR_PAN
w. UCR_PCT
x. UCR_PO_JRNL
Note: This is Glen’s project that deals with encumbrances and journal generation.
y. UCR_PORTAL_REG_DEFNS
Note: After importing these definitions, you will need to run the Application Engine process PORTAL_CSS. After this has run, shutdown the App and Web Servers, delete their cache files and bring them up again.
z. UCR_PROCARD
aa. UCR_SIC_CODES
Note: Per John Kong; changes to the SIC_CODE and PARENT_SIC_CODE fields. Had to alter 2 tables: VNDR_HDR_IND_CL and SIC_CODE_TBL to accommodate field length changes. Also, ran script based on Marc’s SIC “Commodity Codes” spreadsheet and resulting SQL INSERTs (see “INSERT Commodity Codes.sql”).
bb. UCR_SQR*…
Note: These are all projects that related to SQRs.
cc. UCR_SSOAUTH
Note: This is the single signon and authorization project. Please see Section II.4
dd. UCR_STF
ee. UCR_STIP
ff. UCR_UCOP_TRANSLATION
gg. UCR_UPG_INSTRUCTIONS
Note: This project does not have anything to move, but rather some important information.
hh. UCR_PRJ151198PRE844
At John Kong’s request. This project is a patch to fix the Voucher Line VAT secondary page (VCHR_LINE_VAT_SEC1)
ii. UCR_PRJ156866
At John Kong’s request.Modified AP_VB_STGVCH AE to successfully load a PO Voucher
via EDI with blank Voucher Build Code.
2) Prior to exporting your project, run the following SQL statements to determine if the “Upgrade” tab in the Application Designer for a given project truly has all the definitions needed for the upgrade.
In the Application Designer, go to the menu “Tools – Options” and select the option for insert definitions into project when opened.
If any rows are return in the following SQL statements, then open the relevant project and insert those related definitions.
a. /**** THIS WILL DISPLAY DISTINCT RECORDS THAT ARE MISSING PEOPLECODE FROM THE UPGRADE TAB ***/
SELECT DISTINCT OBJECTVALUE1
FROM (
SELECT DISTINCT OBJECTID1,OBJECTVALUE1 ,OBJECTID2,OBJECTVALUE2,OBJECTID3,OBJECTVALUE3,OBJECTID4,OBJECTVALUE4
FROM PSPCMNAME
WHERE (OBJECTID1 = 12 OR OBJECTID2 = 12 OR OBJECTID3 = 12 OR OBJECTID4 = 12)
AND OBJECTVALUE1 IN (SELECT DISTINCT PI.OBJECTVALUE1
FROM PSPROJECTITEM PI
WHERE PI.PROJECTNAME IN ('UCR_BEA', 'UCR_BEA_ZERO_DOLLAR', 'UCR_CHARTFIELD_ATTRIBUTES', 'UCR_DENORM_TREE_TABLES',
'UCR_FCT', 'UCR_MANAGE_BATCH_PROCESSES', 'UCR_NCT', 'UCR_PAN', 'UCR_PCT', 'UCR_SQR_DETLFIN',
'UCR_STF', 'UCR_STIP')
AND PI.OBJECTID3 = 12)
AND OBJECTID3 = 12
MINUS
SELECT OBJECTID1,OBJECTVALUE1 ,OBJECTID2,OBJECTVALUE2,OBJECTID3,OBJECTVALUE3,OBJECTID4,OBJECTVALUE4
FROM PSPROJECTITEM
WHERE PROJECTNAME IN ('UCR_BEA', 'UCR_BEA_ZERO_DOLLAR', 'UCR_CHARTFIELD_ATTRIBUTES', 'UCR_DENORM_TREE_TABLES',
'UCR_FCT', 'UCR_MANAGE_BATCH_PROCESSES', 'UCR_NCT', 'UCR_PAN', 'UCR_PCT', 'UCR_SQR_DETLFIN',
'UCR_STF', 'UCR_STIP')
AND (OBJECTID1 = 12 OR OBJECTID2 = 12 OR OBJECTID3 = 12 OR OBJECTID4 = 12)
ORDER BY OBJECTID1,OBJECTVALUE1 ,OBJECTID2,OBJECTVALUE2,OBJECTID3,OBJECTVALUE3,OBJECTID4,OBJECTVALUE4 )
b. /**** This will display Record Field Events that are missing PeopleCode from the Upgrade Tab ***/
SELECT DISTINCT OBJECTID1,OBJECTVALUE1 ,OBJECTID2,OBJECTVALUE2,OBJECTID3,OBJECTVALUE3,OBJECTID4,OBJECTVALUE4
FROM PSPCMNAME
WHERE (OBJECTID1 = 12 OR OBJECTID2 = 12 OR OBJECTID3 = 12 OR OBJECTID4 = 12)
AND OBJECTVALUE1 IN (SELECT DISTINCT PI.OBJECTVALUE1
FROM PSPROJECTITEM PI
WHERE PI.PROJECTNAME IN ('UCR_BEA', 'UCR_BEA_ZERO_DOLLAR', 'UCR_CHARTFIELD_ATTRIBUTES', 'UCR_DENORM_TREE_TABLES',
'UCR_FCT', 'UCR_MANAGE_BATCH_PROCESSES', 'UCR_NCT', 'UCR_PAN', 'UCR_PCT', 'UCR_SQR_DETLFIN',
'UCR_STF', 'UCR_STIP')
AND PI.OBJECTID3 = 12)
AND OBJECTID3 = 12
MINUS
SELECT OBJECTID1,OBJECTVALUE1 ,OBJECTID2,OBJECTVALUE2,OBJECTID3,OBJECTVALUE3,OBJECTID4,OBJECTVALUE4
FROM PSPROJECTITEM
WHERE PROJECTNAME IN ('UCR_BEA', 'UCR_BEA_ZERO_DOLLAR', 'UCR_CHARTFIELD_ATTRIBUTES', 'UCR_DENORM_TREE_TABLES',
'UCR_FCT', 'UCR_MANAGE_BATCH_PROCESSES', 'UCR_NCT', 'UCR_PAN', 'UCR_PCT', 'UCR_SQR_DETLFIN',
'UCR_STF', 'UCR_STIP')
AND (OBJECTID1 = 12 OR OBJECTID2 = 12 OR OBJECTID3 = 12 OR OBJECTID4 = 12)
ORDER BY OBJECTID1,OBJECTVALUE1 ,OBJECTID2,OBJECTVALUE2,OBJECTID3,OBJECTVALUE3,OBJECTID4,OBJECTVALUE4