JOB_EXPIR_NT_VW (SQL View)	Index Back
Expiration Notification This view contains the list of active employees whith their mangerial details. These details are used to in sending notificationj to the managers of the concerned employees.

JOB_EXPIR_NT_VW

(SQL View)

Expiration Notification

This view contains the list of active employees whith their mangerial details. These details are used to in sending notificationj to the managers of the concerned employees.

SELECT B.EMPLID ,B.EMPL_RCD ,B.EFFDT ,B.EFFSEQ ,B.COMPANY ,B.DEPTID ,B.POSITION_NBR ,B.EMPL_STATUS ,B.LOCATION ,B.BUSINESS_UNIT ,B.JOB_INDICATOR ,B.CONTRACT_NUM ,B.POSITION_OVERRIDE ,B.REPORTS_TO ,B.SUPERVISOR_ID ,A.MANAGER_ID ,A.MANAGER_POSN ,B.SETID_DEPT ,D.NAME_DISPLAY FROM PS_JOB B ,PS_DEPT_TBL A ,PS_NAMES D WHERE B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = B.EMPLID AND EMPL_RCD = B.EMPL_RCD AND EFFDT <= %CurrentDateIn) AND B.HR_STATUS = 'A' AND B.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = B.EMPLID AND EMPL_RCD = B.EMPL_RCD AND EFFDT = B.EFFDT) AND A.SETID = ( SELECT SETID FROM PS_SET_CNTRL_GROUP WHERE SETCNTRLVALUE = B.BUSINESS_UNIT AND REC_GROUP_ID = 'HR_01') AND B.DEPTID = A.DEPTID AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_DEPT_TBL A1 WHERE A1.SETID = A.SETID AND A1.DEPTID = A.DEPTID AND A1.EFFDT <= %CurrentDateIn) AND D.EMPLID = B.EMPLID AND D.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_NAMES C1 WHERE C1.EMPLID = D.EMPLID AND C1.NAME_TYPE = D.NAME_TYPE AND C1.EFFDT <= %CurrentDateIn) AND D.NAME_TYPE = 'PRI'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

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

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

4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence

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

6 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

7 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

8 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

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

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

11 JOB_INDICATOR Character(1) VARCHAR2(1) NOT NULL This Indicator is used on JOB to specify which employment record is considered to be the primary one for an employee. In case of Multiple Jobs per Employee, this property is being used to make a conscious decision in related features where only 1 Employment Record per Employee needs to be returned.
N=Not Applicable
P=Primary Job
S=Secondary Job

12 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number

13 POSITION_OVERRIDE Character(1) VARCHAR2(1) NOT NULL Override Position Data

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

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

16 MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager ID

17 MANAGER_POSN Character(8) VARCHAR2(8) NOT NULL Manager Position

18 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department SetID

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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr
3	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
4	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
5	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company
6	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
7	POSITION_NBR	Character(8)	VARCHAR2(8) NOT NULL	Position Number
8	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
9	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code
10	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
11	JOB_INDICATOR	Character(1)	VARCHAR2(1) NOT NULL	This Indicator is used on JOB to specify which employment record is considered to be the primary one for an employee. In case of Multiple Jobs per Employee, this property is being used to make a conscious decision in related features where only 1 Employment Record per Employee needs to be returned. N=Not Applicable P=Primary Job S=Secondary Job
12	CONTRACT_NUM	Character(25)	VARCHAR2(25) NOT NULL	Contract Number
13	POSITION_OVERRIDE	Character(1)	VARCHAR2(1) NOT NULL	Override Position Data
14	REPORTS_TO	Character(8)	VARCHAR2(8) NOT NULL	Reports To Position Number
15	SUPERVISOR_ID	Character(11)	VARCHAR2(11) NOT NULL	Supervisor ID
16	MANAGER_ID	Character(11)	VARCHAR2(11) NOT NULL	Manager ID
17	MANAGER_POSN	Character(8)	VARCHAR2(8) NOT NULL	Manager Position
18	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department SetID
19	NAME_DISPLAY	Character(50)	VARCHAR2(50) NOT NULL	Display Name - name formatted for Display based on the Country