HR_ECOM_EE_HIST

(SQL View)
Index Back

Compensation History Data

Main View to hold all the history data of the employee, including Base Salary, Variable Cash, Variable Non-Cash, Stock Option

SELECT JOBDATA.EMPLID , JOBDATA.empl_rcd , JOBDATA.EFFDT , BASE.EFFSEQ , BASE.SS_CHANGE_DATE , BASE.ANNUAL_RT , BASE.CHANGE_AMT , BASE.Currency_CD , BASE.COMPRATE , BASE.COMP_FREQUENCY , BASE.Action , ' ' , 'B' , BASE.DESCR , BASE.REG_REGION , BASE.PAID_HOURS , BASE.PAID_HRS_FREQUENCY , BASE.STD_HOURS , BASE.STD_HRS_FREQUENCY , BASE.CHANGE_PCT , ' ' , 0 , 0 FROM PS_HR_SS_EE_RCD_VW JOBDATA , PS_HR_SS_BASE_VW BASE WHERE JOBDATA.EMPLID = BASE.EMPLID AND JOBDATA.empl_rcd = BASE.empl_rcd UNION SELECT JOBDATA.EMPLID , JOBDATA.empl_rcd , JOBDATA.EFFDT , JOBDATA.EFFSEQ , MVC.VC_AWARD_DT , MVC.VC_AWARD_value , 0 , MVC.Currency_CD , 0 , ' ' , ' ' , MVC.VC_PLAN_TYPE , 'C' , JOBDATA.DESCR , ' ' , 0 , ' ' , 0 , ' ' , 0 , MVC.VC_PLAN_ID , 0 ,MVC.VC_PERF_FACTOR FROM PS_HR_SS_EE_MVC_VW MVC , PS_HR_SS_BASE2_VW JOBDATA WHERE JOBDATA.EMPLID = MVC.EMPLID AND JOBDATA.empl_rcd = MVC.empl_rcd AND JOBDATA.EFFDT = ( SELECT MAX(ED.EFFDT) FROM PS_HR_SS_BASE2_VW ED WHERE ED.EMPLID=JOBDATA.EMPLID AND ED.EMPL_RCD=JOBDATA.EMPL_RCD AND ED.EFFDT <= MVC.VC_AWARD_DT ) AND JOBDATA.EFFSEQ = ( SELECT MAX(ES.EFFSEQ) FROM PS_HR_SS_BASE2_VW ES WHERE ES.EMPLID = JOBDATA.EMPLID AND ES.EMPL_RCD = JOBDATA.EMPL_RCD AND ES.EFFDT = JOBDATA.EFFDT ) UNION SELECT JOBDATA.EMPLID , JOBDATA.empl_rcd , JOBDATA.EFFDT , JOBDATA.EFFSEQ , NVC.VC_AWARD_DT , NVC.VC_AWARD_value , 0 , NVC.Currency_CD , 0 , ' ' , ' ' , NVC.VC_PLAN_TYPE , 'N' , JOBDATA.DESCR , ' ' , 0 , ' ' , 0 , ' ' , 0 , NVC.VC_PLAN_ID , NVC.VC_VALUE_AMOUNT ,0 FROM PS_HR_SS_EE_NVC_VW NVC , PS_HR_SS_BASE2_VW JOBDATA WHERE JOBDATA.EMPLID = NVC.EMPLID AND JOBDATA.empl_rcd = NVC.empl_rcd AND JOBDATA.EFFDT = ( SELECT MAX(ED.EFFDT) FROM PS_HR_SS_BASE2_VW ED WHERE ED.EMPLID=JOBDATA.EMPLID AND ED.EMPL_RCD=JOBDATA.EMPL_RCD AND ED.EFFDT <= NVC.VC_AWARD_DT ) AND JOBDATA.EFFSEQ = ( SELECT MAX(ES.EFFSEQ) FROM PS_HR_SS_BASE2_VW ES WHERE ES.EMPLID = JOBDATA.EMPLID AND ES.EMPL_RCD = JOBDATA.EMPL_RCD AND ES.EFFDT = JOBDATA.EFFDT ) UNION SELECT JOBDATA.EMPLID , JOBDATA.empl_rcd , JOBDATA.EFFDT , JOBDATA.EFFSEQ , SVC.VC_AWARD_DT , SVC.VC_AWARD_value , 0 , SVC.Currency_CD ,0 ,' ' ,' ' , SVC.VC_PLAN_TYPE , 'S' , JOBDATA.DESCR , ' ' , 0 , ' ' , 0 , ' ' , 0 , SVC.VC_PLAN_ID , SVC.VC_VALUE_AMOUNT , 0 FROM PS_HR_SS_EE_SVC_VW SVC , PS_HR_SS_BASE2_VW JOBDATA WHERE JOBDATA.EMPLID = SVC.EMPLID AND JOBDATA.empl_rcd = SVC.empl_rcd AND JOBDATA.EFFDT = ( SELECT MAX(ED.EFFDT) FROM PS_HR_SS_BASE2_VW ED WHERE ED.EMPLID=JOBDATA.EMPLID AND ED.EMPL_RCD=JOBDATA.EMPL_RCD AND ED.EFFDT <= SVC.VC_AWARD_DT ) AND JOBDATA.EFFSEQ = ( SELECT MAX(ES.EFFSEQ) FROM PS_HR_SS_BASE2_VW ES WHERE ES.EMPLID = JOBDATA.EMPLID AND ES.EMPL_RCD = JOBDATA.EMPL_RCD AND ES.EFFDT = JOBDATA.EFFDT )

# 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 Record
3 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 VC_AWARD_DT Date(10) DATE Award Date
6 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
7 CHANGE_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Change Amount
8 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
9 COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Compensation Rate
10 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
11 ACTION Character(3) VARCHAR2(3) NOT NULL Action

Prompt Table: ACTION_TBL

12 VC_PLAN_TYPE Character(2) VARCHAR2(2) NOT NULL VC Plan Type
D=Discretionary Bonus
G=Goal Sharing
I=Stock - ISO
K=Stock - NSAR
N=Non-Monetary
O=Other
P=Profit Sharing
Q=Stock - NQ
R=Stock - RSA
S=Stock- ISAR
13 COMP_SOURCE Character(1) VARCHAR2(1) NOT NULL Compensation Source Filter showed on Compensation History Charts
B=Base Salary
C=Variable Cash
N=Variable Non-Cash
S=Stock Option
14 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
15 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
16 PAID_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Paid Hours
17 PAID_HRS_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Paid Work Period
18 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
19 STD_HRS_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Standard Work Period
20 CHANGE_PCT Signed Number(8,3) DECIMAL(6,3) NOT NULL Change Percent
21 VC_PLAN_ID Character(10) VARCHAR2(10) NOT NULL VC Plan ID
22 VC_VALUE_AMOUNT Number(19,6) DECIMAL(18,6) NOT NULL Amount
23 VC_PERF_FACTOR Number(20,4) DECIMAL(19,4) NOT NULL Performance Factor