RE_OCPNT_RPT_VW (SQL View)	Index Back
Maintain Occupants Property Occupants Details View

RE_OCPNT_RPT_VW

(SQL View)

Maintain Occupants Property

Occupants Details View

SELECT A.EMPLID , A.NAME , C.DEPTID , C.DESCR , B.EMPL_STATUS FROM PS_PERSONAL_DATA A , PS_JOB B , PS_DEPT_TBL C WHERE A.EMPLID = B.EMPLID AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED WHERE B_ED.EMPLID = B.EMPLID AND B_ED.EMPL_RCD = B.EMPL_RCD AND B.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(B_ED.EFFSEQ) FROM PS_JOB B_ED WHERE B_ED.EMPLID = B.EMPLID AND B_ED.EMPL_RCD = B.EMPL_RCD AND B_ED.EFFDT = B.EFFDT) AND C.DEPTID = B.DEPTID AND C.SETID = B.SETID_DEPT AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_DEPT_TBL C_ED WHERE C.SETID = C_ED.SETID AND C.DEPTID = C_ED.DEPTID AND C_ED.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

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

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

4 DESCR Character(30) VARCHAR2(30) NOT NULL Description

5 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Employee Status
A=Active
D=Deceased
I=Inactive
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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
3	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
4	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
5	EMPL_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Employee Status A=Active D=Deceased I=Inactive 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