BN_M_EBX_JOB_VW

(SQL View)
 Index Back

eBenX employee Job

This view is used for the eBenX integration with Peoplesoft Benefits. This view joins to BN_M_SNAP_FILTR which is only populated during the EBNX_EXTRACT Application Engine Process

SELECT DISTINCT A.EMPLID , A.BENEFIT_RCD_NBR , A.COBRA_EVENT_ID , PER.HIRE_DT , PER.LAST_HIRE_DT , PER.TERMINATION_DT , B.JOB_EFFDT , D.DEPTID , D.JOBCODE , D.DEPT_ENTRY_DT , D.JOB_ENTRY_DT , D.EMPL_STATUS , D.LOCATION , D.REG_TEMP , D.FULL_PART_TIME , B.COMPANY , B.PAYGROUP , D.BEN_STATUS , D.EMPL_TYPE , D.STD_HOURS , D.OFFICER_CD , D.BUSINESS_UNIT , D.FLSA_STATUS , D.UNION_CD , D.BARG_UNIT FROM PS_BN_M_SNAP_FILTR A , PS_BN_SNAP_JOB B , PS_PER_ORG_ASGN_VW PER , PS_JOB D WHERE A.EMPLID = B.EMPLID AND A.BENEFIT_RCD_NBR = B.BENEFIT_RCD_NBR AND A.COBRA_EVENT_ID = B.COBRA_EVENT_ID AND B.EMPLID = D.EMPLID AND B.EMPL_RCD = D.EMPL_RCD AND PER.EMPLID = B.EMPLID AND PER.EMPL_RCD = B.EMPL_RCD AND B.JOB_EFFDT = D.EFFDT AND B.JOB_EFFSEQ = D.EFFSEQ

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 BENEFIT_RCD_NBR Number(3,0) SMALLINT NOT NULL Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.
3 COBRA_EVENT_ID Number(3,0) SMALLINT NOT NULL Uniquely identifies a COBRA event within an employee's benefits records, and is system-generated.
4 HIRE_DT Date(10) DATE First Start Date
5 REHIRE_DT Date(10) DATE Rehire Date
6 TERMINATION_DT Date(10) DATE Termination Date
7 EFFDT Date(10) DATE Effective Date

Default Value: %date

8 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
9 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
10 DEPT_ENTRY_DT Date(10) DATE Department Entry Date
11 JOB_ENTRY_DT Date(10) DATE Job Entry Date
12 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
13 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
14 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
R=Regular
T=Temporary
15 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
16 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
17 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
18 BEN_STATUS Character(4) VARCHAR2(4) NOT NULL Specifies an employee's status relative to benefits, and is used to determine eligibility for benefits. Some examples of benefit status are Active, Suspended, Leave with Benefits, and Terminated.
A=Active
D=Deceased
L=Leave of Absence
P=Leave With Benefits
Q=Retired With Benefits
R=Retired
S=Suspended
T=Terminated
U=Terminated With Benefits
19 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
E=Exception Hourly
H=Hourly
N=Not Applicable
S=Salaried
20 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
21 OFFICER_CD Character(1) VARCHAR2(1) NOT NULL Officer Code
C=Chairman
D=Director
N=None
O=Officer
P=President
S=Secretary
T=Treasurer
V=Vice President
22 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
23 FLSA_STATUS Character(1) VARCHAR2(1) NOT NULL FLSA Status
A=Administrative
C=Computer Employee
E=Executive
H=Highly Compensated Employees
M=Management
N=Nonexempt
O=Outside Salesperson
P=Professional
V=Nonexempt Alt Overtime
X=No FLSA Required
24 UNION_CD Character(3) VARCHAR2(3) NOT NULL Union Code
25 BARG_UNIT Character(4) VARCHAR2(4) NOT NULL Bargaining Unit
1=Bargaining Unit 1
2=Bargaining Unit 2