PER_INST_POI_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_POI_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 ,B.EMPL_RCD ,A.PER_ORG ,B.BENEFIT_RCD_NBR ,J.HR_STATUS ,J.EMPL_STATUS ,B.HOME_HOST_CLASS ,J.LAST_HIRE_DT ,J.TERMINATION_DT ,J.LST_ASGN_START_DT FROM PS_PER_ORG_INST A , PS_PER_ORG_ASGN B , PS_JOB J WHERE A.EMPLID = B.EMPLID AND A.PER_ORG = 'POI' AND A.ORG_INSTANCE_ERN = B.ORG_INSTANCE_ERN AND A.EMPLID = J.EMPLID AND B.EMPL_RCD = 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 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
Default Value: 0

4 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

5 BENEFIT_RCD_NBR Number(3,0) SMALLINT NOT NULL Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.

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

7 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

8 HOME_HOST_CLASS Character(1) VARCHAR2(1) NOT NULL Home/Host Classification
M=Home
S=Host
Default Value: M

9 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.

10 TERMINATION_DT Date(10) DATE Termination Date

11 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	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr Default Value: 0
4	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
5	BENEFIT_RCD_NBR	Number(3,0)	SMALLINT NOT NULL	Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.
6	HR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	HR Status A=Active I=Inactive
7	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
8	HOME_HOST_CLASS	Character(1)	VARCHAR2(1) NOT NULL	Home/Host Classification M=Home S=Host Default Value: M
9	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.
10	TERMINATION_DT	Date(10)	DATE	Termination Date
11	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.