HCR_SUP_ID_VW (SQL View)	Index Back
Supervisor/Manager Name Prompt Selects only Employees and Contingent Workers . Used for prompt views for Supervisor/Manager EMPLIDs where the field being prompted into contains EFFDT. This view will then return the Active as well as Inactive Supervisors for that records EFFDT. Do not make EMPL_RCD a Key or this view cannot be used to prompt for EMPLID.

HCR_SUP_ID_VW

(SQL View)

Supervisor/Manager Name Prompt

Selects only Employees and Contingent Workers . Used for prompt views for Supervisor/Manager EMPLIDs where the field being prompted into contains EFFDT. This view will then return the Active as well as Inactive Supervisors for that records EFFDT. Do not make EMPL_RCD a Key or this view cannot be used to prompt for EMPLID.

SELECT A.EMPLID ,A.EFFDT ,A.EMPL_RCD ,B.NAME ,B.LAST_NAME_SRCH ,B.SECOND_LAST_SRCH ,B.FIRST_NAME_SRCH ,B.NAME_AC ,A.PER_ORG ,A.BUSINESS_UNIT ,A.DEPTID ,A.JOBCODE ,A.HR_STATUS FROM PS_JOB A , PS_PERSON_NAME B WHERE A.PER_ORG IN ('EMP','CWR') AND A.EMPLID = B.EMPLID AND A.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = A.EMPLID AND JOB3.EMPL_RCD = A.EMPL_RCD AND JOB3.EFFDT = A.EFFDT)

Parent record: PER_ORG_ASGN

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: PERSON_NAME

2 EFFDT Date(10) DATE Effective Date
Default Value: %date

3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr

4 NAME Character(50) VARCHAR2(50) NOT NULL Name

5 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name

6 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Name

7 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name

8 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name

9 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker
EMP=Employee
POI=Person of Interest
Default Value: POI

10 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT
Prompt Table: BUSUNIT_HR_VW

11 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
Prompt Table: DEPT_TBL

12 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
Prompt Table: JOBCODE_TBL

13 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERSON_NAME
2	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
3	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr
4	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
5	LAST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Last Name
6	SECOND_LAST_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Second Name
7	FIRST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	First Name
8	NAME_AC	Character(50)	VARCHAR2(50) NOT NULL	Alternate Character Name
9	PER_ORG	Character(3)	VARCHAR2(3) NOT NULL	Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest. CWR=Contingent Worker EMP=Employee POI=Person of Interest Default Value: POI
10	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT Prompt Table: BUSUNIT_HR_VW
11	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: DEPT_TBL
12	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code Prompt Table: JOBCODE_TBL
13	EFF_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Effective Status A=Active I=Inactive