Chat now with support
Chat with Support

Stat 6.3 - System Administration Guide

Overview of Stat Administration Administrative Utilities Stat Security General Maintenance Tables
System Maintenance Service Domain Maintenance Department Maintenance Issue Tracking Maintenance Country Maintenance Customer Maintenance Object Type Maintenance PeopleSoft Environment Connection Maintenance Pre/Post Migration Steps Parameters Oracle Applications Configuration Oracle Applications Connection Maintenance Generic Application Connection Maintenance Schema Object Parameters Maintenance Data Object Maintenance PeopleSoft Search Configurations Stat Report Definition Maintenance Version Control Management Connection Maintenance
Service Domain-Specific Maintenance Configuring the Stat Central Agent Email Configuration Object Security Appendix: Sample Service Domain Configuration Appendix: User Class Rights Appendix: Creating a Staging Database Appendix: Database Tuning Appendix: Oracle Applications File Type Directory Appendix: Ports and Firewalls Appendix: REST Web Services API Appendix: SOAP-Based Web Services API Appendix: Troubleshooting Chart Appendix: stat.conf Configuration Appendix: Custom Report Files

Creating Tables and Views

A table or view must be created in each PeopleSoft environment in which you want to conduct searches. For example, in the case of an employee-based search, the view must contain the following fields:

EMPLID

COUNTRY

NAME

SSN

ADDRESS 1

COMMENTS

ADDRESS 2

COMPANY

ADDRESS 3

PAYGROUP

ADDRESS 4

DEPTID

CITY

DEPTNAME

STATE

WORK_PHONE

ZIP

WORK_PHONE_EXT

EMAIL_ADDR

EMAIL_ADDRESS2

CUSTOM1

CUSTOM2

SIN

 

For employee-based searches, Stat parses the NAME column based on the original PeopleSoft format of Employee table Name column: LastName, FirstName.

In addition, for any column, you may insert a space instead of a value, or indicate a default value. You may wish to populate the comment, custom1 or custom2 fields from other employee related fields.

NOTE: Newer versions of the PeopleTools employee table (8.48 and higher) store the Name field as FirstName LastName, but also have a separate FirstName and LastName column. For those versions of PeopleTools the view must concatenate FirstName and LastName as a.LAST_NAME || ',' ||a.FIRST_NAME.

The following are examples of views. The first example below displays an employee view formatted for PeopleTools versions prior to 8.48, while the second example displays the format for PeopleTools versions 8.48 and higher. In both examples the phone extension, SSN, SIN and second email address values are not being populated from PeopleSoft. The employee's job title is being placed into the comment field. This is configurable based on your site needs.

Employee View Example (Pre PeopleTools Version 8.48)

CREATE OR REPLACE FORCE VIEW sysprod.ps_stat_cust_vw
(emplid,
NAME,
deptid,
deptname,
ssn,
SIN,
work_phone,
work_phone_ext,
address1,
address2,
address3,
address4,
city,
state,
zip,
country,
email_addr,
email_address2,
comments,
custom1,
custom2,
company,
paygroup
)
AS
SELECT a.emplid, a.NAME,, a.deptid, a.deptname, a.ssn, a.SIN,
a.work_phone,' ', a.address1, a.address2, a.address3, a.address4, a.city,
a.state, a.postal, a.country, b.email_addr, ' ', a.jobtitle, ' ', ' ',
a.company, a.paygroup
FROM ps_employees a, ps_email_addresses b
WHERE a.emplid = b.emplid

Employee View Example (PeopleTools Version 8.48 and Higher)

CREATE OR REPLACE FORCE VIEW sysprod.ps_stat_cust_vw
(emplid,
NAME,
deptid,
deptname,
ssn,
SIN,
work_phone,
work_phone_ext,
address1,
address2,
address3,
address4,
city,
state,
zip,
country,
email_addr,
email_address2,
comments,
custom1,
custom2,
company,
paygroup
)
AS
SELECT a.emplid, a.LAST_NAME || ',' ||a.FIRST_NAME, a.deptid, a.deptname, a.ssn, a.SIN,
a.work_phone,' ', a.address1, a.address2, a.address3, a.address4, a.city,
a.state, a.postal, a.country, b.email_addr, ' ', a.jobtitle, ' ', ' ',
a.company, a.paygroup
FROM ps_employees a, ps_email_addresses b
WHERE a.emplid = b.emplid

Country View Example

create or replace force view ps_stat_country_vw
(COUNTRY,
DESCR,
DESCRSHORT,
ADDR1_LBL,
ADDR2_LBL,
ADDR3_LBL,
ADDR4_LBL,
CITY_LBL,
STATE_LBL,
POSTAL_LBL,
CUSTOM1_LBL,
CUSTOM2_LBL,
COMMENT_LBL )
 
as
select a.country,
a.descr,
a.descrshort,
a.addr1_lbl,
a.addr2_lbl,
a.addr3_lbl,
a.addr4_lbl,
a.city_lbl,
a.state_lbl,
a.postal_lbl,
a.country_2char,
' ',
' '
 
from ps_country_tbl a
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating