BEN_SUM_EMP_FL (SQL View)	Index Back
Benefits Employee Used by Fluid Benefits Summary in self service to find the employees benefits primary job and retrieves the EMPL_RCD of the associated benefits primary Job.

BEN_SUM_EMP_FL

(SQL View)

Benefits Employee

Used by Fluid Benefits Summary in self service to find the employees benefits primary job and retrieves the EMPL_RCD of the associated benefits primary Job.

SELECT JOB.EMPLID ,H.EMPL_RCD ,H.BENEFIT_RCD_NBR ,JOB.BUSINESS_UNIT ,JOB.SETID_DEPT ,JOB.DEPTID ,JOB.EMPL_RCD FROM PS_PRIMARY_JOBS PRI , PS_PER_ORG_ASGN H , PS_JOB JOB WHERE JOB.EMPLID=H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND (JOB.EFFDT>=%CurrentDateIn OR JOB.EFFDT= ( SELECT MAX(EFFDT) FROM PS_JOB JB1 WHERE JB1.EMPLID=JOB.EMPLID AND JB1.EMPL_RCD=JOB.EMPL_RCD AND JB1.EFFDT <= %CurrentDateIn) ) AND JOB.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID=JOB.EMPLID AND JB2.EMPL_RCD=JOB.EMPL_RCD AND JB2.EFFDT = JOB.EFFDT) AND JOB.BENEFIT_SYSTEM<>'OT' AND PRI.EMPLID=JOB.EMPLID AND PRI.EMPL_RCD=JOB.EMPL_RCD AND PRI.PRIMARY_JOB_APP='BN' AND PRI.PRIMARY_JOB_IND='Y' AND (PRI.EFFDT>=%CurrentDateIn OR PRI.EFFDT= ( SELECT MAX(P1.EFFDT) FROM PS_PRIMARY_JOBS P1 WHERE P1.EMPLID=PRI.EMPLID AND P1.EMPL_RCD=PRI.EMPL_RCD AND P1.PRIMARY_JOB_APP=PRI.PRIMARY_JOB_APP AND P1.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 Record

3 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.

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

5 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
Prompt Table: SETID_TBL

6 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
Prompt Table: PERS_DEPT_VW

7 PRIMARY_ERN Number(4,0) SMALLINT NOT NULL Benefits Pre Upgrade Step primary_ern value set by client

#	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	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.
4	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
5	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department Set ID Prompt Table: SETID_TBL
6	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: PERS_DEPT_VW
7	PRIMARY_ERN	Number(4,0)	SMALLINT NOT NULL	Benefits Pre Upgrade Step primary_ern value set by client