FC_EE_PROF_VW (SQL View)	Index Back
Forecasting Employee Profile View of Employee Profile Based on Current Job. The GL business unit is derived by mapping the business unit from the Job table to the PS_BUS_UNIT_TBL_HR table.

FC_EE_PROF_VW

(SQL View)

Forecasting Employee Profile

View of Employee Profile Based on Current Job. The GL business unit is derived by mapping the business unit from the Job table to the PS_BUS_UNIT_TBL_HR table.

SELECT J.EMPLID , J.EMPL_RCD , J.EFFDT , J.EFFSEQ , J.DEPTID , J.JOBCODE , J.EMPL_STATUS , J.HOLIDAY_SCHEDULE , BU.BUSINESS_UNIT_GL , J.STD_HOURS , F.FREQ_ANNUAL_FACTOR FROM PS_JOB J , PS_BUS_UNIT_TBL_HR BU , PS_FREQUENCY_TBL F WHERE J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT <= %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT = J.EFFDT) AND J.EMPL_RCD = ( SELECT MIN(J3.EMPL_RCD) FROM PS_JOB J3 WHERE J3.EMPLID = J.EMPLID AND J3.EFFDT = ( SELECT MAX(J31.EFFDT) FROM PS_JOB J31 WHERE J31.EMPLID = J3.EMPLID AND J31.EMPL_RCD = J3.EMPL_RCD AND J31.EFFDT <= %CurrentDateIn) AND J3.EFFSEQ = ( SELECT MAX(J32.EFFSEQ) FROM PS_JOB J32 WHERE J32.EMPLID = J3.EMPLID AND J32.EMPL_RCD = J3.EMPL_RCD AND J32.EFFDT = J3.EFFDT) AND J3.EMPL_STATUS IN ('A','L','P','S','W')) AND J.EMPL_STATUS IN ('A','L','P','S','W') AND BU.BUSINESS_UNIT = J.BUSINESS_UNIT AND F.FREQUENCY_ID = J.STD_HRS_FREQUENCY AND F.EFFDT = ( SELECT MAX(F1.EFFDT) FROM PS_FREQUENCY_TBL F1 WHERE F1.FREQUENCY_ID = F.FREQUENCY_ID AND F1.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr

3 EFFDT Date(10) DATE Effective Date
Default Value: %date

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

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

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

7 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

8 HOLIDAY_SCHEDULE Character(6) VARCHAR2(6) NOT NULL Holiday Schedule

9 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit

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

11 FREQ_ANNUAL_FACTOR Number(9,4) DECIMAL(8,4) NOT NULL Specifies the factor used for annualization/deannualization in a frequency conversion. The Frequency Annualization Factor is expressed in times per year.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr
3	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
4	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
5	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
6	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
7	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
8	HOLIDAY_SCHEDULE	Character(6)	VARCHAR2(6) NOT NULL	Holiday Schedule
9	BUSINESS_UNIT_GL	Character(5)	VARCHAR2(5) NOT NULL	GL Business Unit
10	STD_HOURS	Number(7,2)	DECIMAL(6,2) NOT NULL	Standard Hours
11	FREQ_ANNUAL_FACTOR	Number(9,4)	DECIMAL(8,4) NOT NULL	Specifies the factor used for annualization/deannualization in a frequency conversion. The Frequency Annualization Factor is expressed in times per year.