GP_PSLP_PRI_VW (SQL View)	Index Back
Payslip Primary Job View - HR PRIMARY_JOB_VW is used to return the most current Primary Job information at a certain Effective Date for an Employee. (1) This view returns the 'active' job with the lowest employment record number where the Job_Indicator is equal to "P"; (2) If no row found under (1), it will return the active job with the lowest empl_rcd#; (3) If no row found under (2), it will return the lowest empl_rcd# where the Job_Indicator is equal to "P"; (4) If no row found under (3), it will return the lowest empl_rcd#. Note: This view will always return 1 row per employee as there should be only one Primary Job.

GP_PSLP_PRI_VW

(SQL View)

Payslip Primary Job View - HR

PRIMARY_JOB_VW is used to return the most current Primary Job information at a certain Effective Date for an Employee. (1) This view returns the 'active' job with the lowest employment record number where the Job_Indicator is equal to "P"; (2) If no row found under (1), it will return the active job with the lowest empl_rcd#; (3) If no row found under (2), it will return the lowest empl_rcd# where the Job_Indicator is equal to "P"; (4) If no row found under (3), it will return the lowest empl_rcd#. Note: This view will always return 1 row per employee as there should be only one Primary Job.

SELECT JOB.EMPLID ,JOB.EFFDT ,JOB.EMPL_RCD ,JOB.EFFSEQ ,JOB.EMPL_TYPE ,JOB.SETID_JOBCODE ,JOB.JOBCODE ,JOB.SUPERVISOR_ID ,JOB.BUSINESS_UNIT ,JOB.COMPRATE ,JOB.COMP_FREQUENCY ,JOB.JOB_INDICATOR FROM PS_JOB JOB , PS_PER_ORG_ASGN_VW PER WHERE JOB.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JB1 WHERE JB1.EMPLID = JOB.EMPLID AND JB1.EMPL_RCD = JOB.EMPL_RCD AND JB1.EFFDT <= %CurrentDateIn) AND JOB.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = JOB.EMPLID AND JB2.EFFDT = JOB.EFFDT AND JB2.EMPL_RCD = JOB.EMPL_RCD) AND PER.EMPLID = JOB.EMPLID AND PER.EMPL_RCD = JOB.EMPL_RCD

Parent record: JOB_DATE_VW

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 EFFDT Date(10) DATE Effective Date

3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

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

5 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
E=Exception Hourly
H=Hourly
N=Not Applicable
S=Salaried

6 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID

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

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

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

10 COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Compensation Rate

11 COMP_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Compensation Frequency
A=Annual
B=Biweekly
C=Contract
D=Daily
H=Hourly
M=Monthly
S=Semimonthly
W=Weekly

12 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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	EFFDT	Date(10)	DATE	Effective Date
3	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
4	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
5	EMPL_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Employee Type E=Exception Hourly H=Hourly N=Not Applicable S=Salaried
6	SETID_JOBCODE	Character(5)	VARCHAR2(5) NOT NULL	Job Code Set ID
7	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
8	SUPERVISOR_ID	Character(11)	VARCHAR2(11) NOT NULL	Supervisor ID
9	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
10	COMPRATE	Number(19,6)	DECIMAL(18,6) NOT NULL	Compensation Rate
11	COMP_FREQUENCY	Character(5)	VARCHAR2(5) NOT NULL	Compensation Frequency A=Annual B=Biweekly C=Contract D=Daily H=Hourly M=Monthly S=Semimonthly W=Weekly
12	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