GPES_JOB_VW (SQL View)	Index Back
Employee Job related data Data from tables PS_JOB, PS_JOB_JR and PS_CONTRACT_DATA used along the Spanish payroll process. The retrieved data is not available by GP Core through systems elements, so, it is read at the beginning of the payroll process using this view.

GPES_JOB_VW

(SQL View)

Employee Job related data

Data from tables PS_JOB, PS_JOB_JR and PS_CONTRACT_DATA used along the Spanish payroll process. The retrieved data is not available by GP Core through systems elements, so, it is read at the beginning of the payroll process using this view.

SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ , A.JOBCODE , B.SSN_EMPLOYER , A.EMPL_CTG , A.SOC_SEC_RISK_CODE , %Round(%DecMult(B.TARGET_COMPRATE, D.FREQ_ANNUAL_FACTOR), 6) , A.CURRENCY_CD , D.FREQUENCY_TYPE , A.UNION_FEE_START_DT , A.UNION_FEE_END_DT , A.UNION_FEE_AMOUNT , A.CURRENCY_CD1 , C.SCHEME_ID_ESP , C.CONTRIB_ID_ESP , C.VACATION_PERIOD , C.VACN_PERIOD_UNIT , A.ANNL_BENEF_BASE_RT , C.PROBATION_PERIOD , C.DURATION_TYPE FROM PS_JOB A , PS_JOB_JR B , PS_CONTRACT_DATA C , PS_FREQUENCY_TBL D WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ AND C.EMPLID = A.EMPLID AND C.CONTRACT_NUM = A.CONTRACT_NUM AND D.FREQUENCY_ID = A.COMP_FREQUENCY AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_FREQUENCY_TBL WHERE FREQUENCY_ID = D.FREQUENCY_ID AND EFFDT <= A.EFFDT) AND D.EFF_STATUS <> 'I'

# 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 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

6 SSN_EMPLOYER Character(13) VARCHAR2(13) NOT NULL Employer Social Security number

7 EMPL_CTG Character(6) VARCHAR2(6) NOT NULL Employee Category

8 SOC_SEC_RISK_CODE Character(3) VARCHAR2(3) NOT NULL Social Security Risk Code

9 TARGET_COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Target Compensation Rate

10 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

11 FREQUENCY_TYPE Character(1) VARCHAR2(1) NOT NULL Indicates the type of a frequency ID.
A=Annual
B=Biweekly
C=Contract
D=Daily
F=Every Four Weeks
H=Hourly
M=Monthly
Q=Quarterly
S=Semimonthly
W=Weekly

12 UNION_FEE_START_DT Date(10) DATE Union Fee Start Date

13 UNION_FEE_END_DT Date(10) DATE Union Fee End Date

14 UNION_FEE_AMOUNT Number(9,2) DECIMAL(8,2) NOT NULL Union Fee Amount

15 CURRENCY_CD1 Character(3) VARCHAR2(3) NOT NULL Currency Code

16 SCHEME_ID_ESP Character(4) VARCHAR2(4) NOT NULL Scheme ID - ESP

17 CONTRIB_ID_ESP Character(5) VARCHAR2(5) NOT NULL Soc.Sec. Contribution ID - ESP

18 VACATION_PERIOD Number(3,0) SMALLINT NOT NULL Vacation period

19 VACN_PERIOD_UNIT Character(1) VARCHAR2(1) NOT NULL Vacation period unit
D=Work Days
M=Months
N=Natural Days
W=Weeks

20 ANNL_BENEF_BASE_RT Number(19,3) DECIMAL(18,3) NOT NULL Specifies a compensation amount used to calculate benefits and benefit deductions for an employee. The base compensation amount may include commissions and bonuses in addition to regular compensation. If no Annual Benefits Base Rate is entered, then benefits calculations are based on an employee's regular compensation.

21 PROBATION_PERIOD Number(3,0) SMALLINT NOT NULL Probation Period

22 DURATION_TYPE Character(1) VARCHAR2(1) NOT NULL Type of Duration
D=Days
M=Months
Y=Years

#	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	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
6	SSN_EMPLOYER	Character(13)	VARCHAR2(13) NOT NULL	Employer Social Security number
7	EMPL_CTG	Character(6)	VARCHAR2(6) NOT NULL	Employee Category
8	SOC_SEC_RISK_CODE	Character(3)	VARCHAR2(3) NOT NULL	Social Security Risk Code
9	TARGET_COMPRATE	Number(19,6)	DECIMAL(18,6) NOT NULL	Target Compensation Rate
10	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
11	FREQUENCY_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Indicates the type of a frequency ID. A=Annual B=Biweekly C=Contract D=Daily F=Every Four Weeks H=Hourly M=Monthly Q=Quarterly S=Semimonthly W=Weekly
12	UNION_FEE_START_DT	Date(10)	DATE	Union Fee Start Date
13	UNION_FEE_END_DT	Date(10)	DATE	Union Fee End Date
14	UNION_FEE_AMOUNT	Number(9,2)	DECIMAL(8,2) NOT NULL	Union Fee Amount
15	CURRENCY_CD1	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
16	SCHEME_ID_ESP	Character(4)	VARCHAR2(4) NOT NULL	Scheme ID - ESP
17	CONTRIB_ID_ESP	Character(5)	VARCHAR2(5) NOT NULL	Soc.Sec. Contribution ID - ESP
18	VACATION_PERIOD	Number(3,0)	SMALLINT NOT NULL	Vacation period
19	VACN_PERIOD_UNIT	Character(1)	VARCHAR2(1) NOT NULL	Vacation period unit D=Work Days M=Months N=Natural Days W=Weeks
20	ANNL_BENEF_BASE_RT	Number(19,3)	DECIMAL(18,3) NOT NULL	Specifies a compensation amount used to calculate benefits and benefit deductions for an employee. The base compensation amount may include commissions and bonuses in addition to regular compensation. If no Annual Benefits Base Rate is entered, then benefits calculations are based on an employee's regular compensation.
21	PROBATION_PERIOD	Number(3,0)	SMALLINT NOT NULL	Probation Period
22	DURATION_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Type of Duration D=Days M=Months Y=Years