JOB_ASOFDATE_VW (SQL View)	Index Back
Job AsOfDate View JOB_ASOFDATE_VW is used within the Inquiry on Multiple Jobs to sel

JOB_ASOFDATE_VW

(SQL View)

Job AsOfDate View

JOB_ASOFDATE_VW is used within the Inquiry on Multiple Jobs to sel

SELECT A.EMPLID ,A.ASOFDATE ,B.EMPL_RCD ,B.EFFDT ,B.EFFSEQ ,B.COMPANY ,B.DEPTID ,B.JOBCODE ,B.POSITION_NBR ,B.EMPL_STATUS ,B.LOCATION ,B.BUSINESS_UNIT ,B.JOB_INDICATOR ,B.STD_HOURS ,B.STD_HRS_FREQUENCY FROM PS_JOB_DATE_VW A , PS_JOB B WHERE B.EMPLID = A.EMPLID AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = B.EMPLID AND EMPL_RCD = B.EMPL_RCD AND EFFDT <= A.ASOFDATE) AND B.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = B.EMPLID AND EMPL_RCD = B.EMPL_RCD AND EFFDT = B.EFFDT)

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 ASOFDATE Date(10) DATE As of Date

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

4 EFFDT Date(10) DATE Effective Date

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

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

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

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

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

10 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

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

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

13 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

14 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours

15 STD_HRS_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Standard Work Period
Prompt Table: STDHRS_FREQ_VW

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	ASOFDATE	Date(10)	DATE	As of Date
3	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
4	EFFDT	Date(10)	DATE	Effective Date
5	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
6	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company
7	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
8	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
9	POSITION_NBR	Character(8)	VARCHAR2(8) NOT NULL	Position Number
10	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
11	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code
12	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
13	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
14	STD_HOURS	Number(7,2)	DECIMAL(6,2) NOT NULL	Standard Hours
15	STD_HRS_FREQUENCY	Character(5)	VARCHAR2(5) NOT NULL	Standard Work Period Prompt Table: STDHRS_FREQ_VW