GPES_TER_EMP_VW (SQL View)	Index Back
Terminated Employees Terminated Employees

GPES_TER_EMP_VW

(SQL View)

Terminated Employees

SELECT DISTINCT %Sql(SCRTY_SEL_PKEY, OPR,SEC) , %DateAdd(J.EFFDT, -1 ) , %Sql(SCRTY_SEL_CORSBR,NM,SEC) , %Sql(SCRTY_SEL_FLDSBR,SEC) , %Sql(SCRTY_SEL_PAYSBR,SEC) , J.ACTION , J.ACTION_REASON FROM %Sql(SCRTY_PER_NM_FROM) , PS_JOB J WHERE (J.ACTION IN ( SELECT AC.ACTION FROM PS_GPES_TERM_AC_VW AC WHERE AC.EFFDT = ( SELECT MAX(EFFDT) FROM PS_GPES_TERM_AC_VW WHERE ACTION = AC.ACTION AND EFFDT <= %CurrentDateIn)) OR J.EMPL_STATUS = 'D') AND SEC.EMPLID = J.EMPLID AND SEC.EMPL_RCD = J.EMPL_RCD AND %Sql(SCRTY_NO_APPT1) AND %Sql(SCRTY_NAME) AND %Sql(SCRTY_WHERE, 'PPLJOB')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

2 ROWSECCLASS Character(30) VARCHAR2(30) NOT NULL The class used to determine row level security

3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

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

5 TERMINATION_DT Date(10) DATE Termination Date

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

7 NAME_DISPLAY_SRCH Character(50) VARCHAR2(50) NOT NULL Uppercase version of Name to improve performance on searches.

8 NAME_PSFORMAT Character(50) VARCHAR2(50) NOT NULL A Person's name in the Peoplesoft Format (basically LN,FN MI

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

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

11 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name

12 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name

13 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name

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

15 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME

16 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country

17 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

18 POI_TYPE Character(5) VARCHAR2(5) NOT NULL Defines the Transactional usage of POIs in HRMS.

19 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

20 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department SetID
Prompt Table: SETID_TBL

21 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
Prompt Table: PERS_DEPT_VW

22 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location SetID

23 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

24 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

25 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region

26 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

27 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID

28 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive

29 MILITARY_SERVICE Character(8) VARCHAR2(8) NOT NULL Military Service
Prompt Table: MIL_SERVICE_TBL

30 MIL_RANK Character(5) VARCHAR2(5) NOT NULL Military Rank
Prompt Table: MIL_RANK_TBL

31 MIL_WORN_RANK Character(5) VARCHAR2(5) NOT NULL Worn Rank
Prompt Table: MIL_RANK_TBL

32 APPT_TYPE Character(1) VARCHAR2(1) NOT NULL This field tracks whether a row is a Main Appointment (which all rows outside Japan are), or an Additional Appointment, which is a feature only used by Japanese customers.
0=Core Appointment
1=Japan Kenmu Appointment
2=French Public Sector Appt
3=Staffing

33 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
A=Active
D=Deceased
L=Leave of Absence
P=Leave With Pay
Q=Retired With Pay
R=Retired
S=Suspended
T=Terminated
U=Terminated With Pay
V=Terminated Pension Pay Out
W=Short Work Break
X=Retired-Pension Administration

34 OTHER_ID_JPN Character(11) VARCHAR2(11) NOT NULL Other Identifier

35 FUTURE_FLG Character(1) VARCHAR2(1) NOT NULL Flag for the SJT_*** record that indicates whether this is a future dated row or not. Y = Future N = Current

36 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number

37 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID

38 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code SetID

39 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

40 PAY_SYSTEM_FLG Character(2) VARCHAR2(2) NOT NULL Payroll System
GP=Global Payroll
NA=Payroll for North America
OT=Other
PI=Payroll Interface

41 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group

42 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group

43 ACTION Character(3) VARCHAR2(3) NOT NULL Action

44 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
2	ROWSECCLASS	Character(30)	VARCHAR2(30) NOT NULL	The class used to determine row level security
3	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
4	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr
5	TERMINATION_DT	Date(10)	DATE	Termination Date
6	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
7	NAME_DISPLAY_SRCH	Character(50)	VARCHAR2(50) NOT NULL	Uppercase version of Name to improve performance on searches.
8	NAME_PSFORMAT	Character(50)	VARCHAR2(50) NOT NULL	A Person's name in the Peoplesoft Format (basically LN,FN MI
9	LAST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Last Name
10	SECOND_LAST_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Second Name
11	FIRST_NAME	Character(30)	VARCHAR2(30) NOT NULL	First Name
12	LAST_NAME	Character(30)	VARCHAR2(30) NOT NULL	Last Name
13	SECOND_LAST_NAME	Character(30)	VARCHAR2(30) NOT NULL	Second Last Name
14	NAME_AC	Character(50)	VARCHAR2(50) NOT NULL	Alternate Character Name
15	MIDDLE_NAME	Character(30)	VARCHAR2(30) NOT NULL	MIDDLE_NAME
16	NAME_DISPLAY	Character(50)	VARCHAR2(50) NOT NULL	Display Name - name formatted for Display based on the Country
17	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
18	POI_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Defines the Transactional usage of POIs in HRMS.
19	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
20	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department SetID Prompt Table: SETID_TBL
21	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: PERS_DEPT_VW
22	SETID_LOCATION	Character(5)	VARCHAR2(5) NOT NULL	Location SetID
23	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code
24	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company
25	REG_REGION	Character(5)	VARCHAR2(5) NOT NULL	Regulatory Region
26	INSTITUTION	Character(5)	VARCHAR2(5) NOT NULL	Academic Institution
27	NATIONAL_ID	Character(20)	VARCHAR2(20) NOT NULL	National ID
28	HR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	HR Status A=Active I=Inactive
29	MILITARY_SERVICE	Character(8)	VARCHAR2(8) NOT NULL	Military Service Prompt Table: MIL_SERVICE_TBL
30	MIL_RANK	Character(5)	VARCHAR2(5) NOT NULL	Military Rank Prompt Table: MIL_RANK_TBL
31	MIL_WORN_RANK	Character(5)	VARCHAR2(5) NOT NULL	Worn Rank Prompt Table: MIL_RANK_TBL
32	APPT_TYPE	Character(1)	VARCHAR2(1) NOT NULL	This field tracks whether a row is a Main Appointment (which all rows outside Japan are), or an Additional Appointment, which is a feature only used by Japanese customers. 0=Core Appointment 1=Japan Kenmu Appointment 2=French Public Sector Appt 3=Staffing
33	EMPL_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Payroll Status A=Active D=Deceased L=Leave of Absence P=Leave With Pay Q=Retired With Pay R=Retired S=Suspended T=Terminated U=Terminated With Pay V=Terminated Pension Pay Out W=Short Work Break X=Retired-Pension Administration
34	OTHER_ID_JPN	Character(11)	VARCHAR2(11) NOT NULL	Other Identifier
35	FUTURE_FLG	Character(1)	VARCHAR2(1) NOT NULL	Flag for the SJT_*** record that indicates whether this is a future dated row or not. Y = Future N = Current
36	REPORTS_TO	Character(8)	VARCHAR2(8) NOT NULL	Reports To Position Number
37	SUPERVISOR_ID	Character(11)	VARCHAR2(11) NOT NULL	Supervisor ID
38	SETID_JOBCODE	Character(5)	VARCHAR2(5) NOT NULL	Job Code SetID
39	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
40	PAY_SYSTEM_FLG	Character(2)	VARCHAR2(2) NOT NULL	Payroll System GP=Global Payroll NA=Payroll for North America OT=Other PI=Payroll Interface
41	PAYGROUP	Character(3)	VARCHAR2(3) NOT NULL	Pay Group
42	GP_PAYGROUP	Character(10)	VARCHAR2(10) NOT NULL	Global Payroll pay group
43	ACTION	Character(3)	VARCHAR2(3) NOT NULL	Action
44	ACTION_REASON	Character(3)	VARCHAR2(3) NOT NULL	Reason Code