PER_INST_EMP_VW (SQL View)	Index Back
Per Org Inst for Employment View of PER_ORG_ASGN joined to PER_ORG_INST, and the current row in JOB and JOB_DATES

PER_INST_EMP_VW

(SQL View)

Per Org Inst for Employment

View of PER_ORG_ASGN joined to PER_ORG_INST, and the current row in JOB and JOB_DATES

SELECT A.EMPLID ,A.ORG_INSTANCE_ERN ,A.PER_ORG ,J.HR_STATUS ,J.EMPL_STATUS ,J.LAST_HIRE_DT ,J.TERMINATION_DT ,J.LST_ASGN_START_DT FROM PS_PER_ORG_INST A , PS_JOB J WHERE A.PER_ORG = 'EMP' AND A.EMPLID = J.EMPLID AND A.ORG_INSTANCE_ERN = J.EMPL_RCD AND J.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JOB2 WHERE J.EMPLID = JOB2.EMPLID AND J.EMPL_RCD = JOB2.EMPL_RCD AND (( JOB2.EFFDT <= %CurrentDateIn) OR (JOB2.EFFDT > %CurrentDateIn AND %CurrentDateIn < ( SELECT MIN(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD) ) )) AND J.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = J.EMPLID AND JOB3.EMPL_RCD = J.EMPL_RCD AND JOB3.EFFDT = J.EFFDT )

Parent record: PERSON

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 ORG_INSTANCE_ERN Number(3,0) SMALLINT NOT NULL Indicates which EMPL_RCD (ERN) is considered the Organizational Instance for this particular ERN. An Organizational Instance defines an Instance of Employment or an Instance of Contingent Workforce. Hire, Termination, and Service dates are captured at the Organiational Instance level.

3 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
Prompt Table: PER_ORG_VW

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

5 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

6 LAST_HIRE_DT Date(10) DATE Latest Start Date - The latest time that an Employee or a Contingent Worker has started - based on EMPLID/EMPL_RCD.

7 TERMINATION_DT Date(10) DATE Termination Date

8 LST_ASGN_START_DT Date(10) DATE Lastest start date of the specific EMPL_RCD assignment. This is different from the Person's organizational instance Last Hire Date.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERSON
2	ORG_INSTANCE_ERN	Number(3,0)	SMALLINT NOT NULL	Indicates which EMPL_RCD (ERN) is considered the Organizational Instance for this particular ERN. An Organizational Instance defines an Instance of Employment or an Instance of Contingent Workforce. Hire, Termination, and Service dates are captured at the Organiational Instance level.
3	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 Prompt Table: PER_ORG_VW
4	HR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	HR Status A=Active I=Inactive
5	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
6	LAST_HIRE_DT	Date(10)	DATE	Latest Start Date - The latest time that an Employee or a Contingent Worker has started - based on EMPLID/EMPL_RCD.
7	TERMINATION_DT	Date(10)	DATE	Termination Date
8	LST_ASGN_START_DT	Date(10)	DATE	Lastest start date of the specific EMPL_RCD assignment. This is different from the Person's organizational instance Last Hire Date.