BEN_SUM_AD_SRCH (SQL View)	Index Back
Benefits Employee Search This is the main search view in Base Benefits components. It "maps" BENEFIT_RCD_NBR into EMPL_RCD so tracks of benefits can be presented without any change in page structure. It also retrieves the EMPL_RCD of the associated benefits primary Job.

BEN_SUM_AD_SRCH

(SQL View)

Benefits Employee Search

This is the main search view in Base Benefits components. It "maps" BENEFIT_RCD_NBR into EMPL_RCD so tracks of benefits can be presented without any change in page structure. It also retrieves the EMPL_RCD of the associated benefits primary Job.

SELECT DISTINCT SEC.OPRID , SEC.ROWSECCLASS ,SEC.EMPLID ,H.EMPL_RCD ,H.BENEFIT_RCD_NBR , %Sql(SCRTY_SEL_NMSBR,SEC,SEC) ,SEC.BUSINESS_UNIT ,SEC.SETID_DEPT ,SEC.DEPTID ,SEC.EMPL_RCD FROM PS_EMPLMT_SRCH_GBL SEC , PS_PRIMARY_JOBS PRI , PS_PER_ORG_ASGN H , PS_JOB JOB WHERE SEC.EMPLID=H.EMPLID AND SEC.EMPL_RCD = H.EMPL_RCD AND JOB.EMPLID = SEC.EMPLID AND JOB.EMPL_RCD = SEC.EMPL_RCD AND ((JOB.EFFDT>=%CurrentDateIn AND NOT EXISTS ( SELECT JB1.EMPLID FROM PS_JOB JB1 WHERE JB1.EMPLID=JOB.EMPLID AND JB1.EMPL_RCD=JOB.EMPL_RCD AND JB1.EFFDT <= %CurrentDateIn)) OR JOB.EFFDT= ( SELECT MAX(EFFDT) FROM PS_JOB JB2 WHERE JB2.EMPLID=JOB.EMPLID AND JB2.EMPL_RCD=JOB.EMPL_RCD AND JB2.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 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

2 ROWSECCLASS Character(30) VARCHAR2(30) NOT NULL The class used to determine row level security

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

4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

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

6 NAME Character(50) VARCHAR2(50) NOT NULL Name

7 NAME_DISPLAY_SRCH Character(50) VARCHAR2(50) NOT NULL Uppercase version of Name to improve performance on searches.

8 NAME_PSFORMAT Character(50) VARCHAR2(50) NOT NULL A Person's name in the Peoplesoft Format (basically LN,FN MI

9 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name

10 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name

11 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name

12 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name

13 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name

14 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name

15 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME

16 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country

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

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

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

20 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	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
2	ROWSECCLASS	Character(30)	VARCHAR2(30) NOT NULL	The class used to determine row level security
3	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
4	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
5	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.
6	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
7	NAME_DISPLAY_SRCH	Character(50)	VARCHAR2(50) NOT NULL	Uppercase version of Name to improve performance on searches.
8	NAME_PSFORMAT	Character(50)	VARCHAR2(50) NOT NULL	A Person's name in the Peoplesoft Format (basically LN,FN MI
9	LAST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Last Name
10	SECOND_LAST_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Second Last Name
11	FIRST_NAME	Character(30)	VARCHAR2(30) NOT NULL	First Name
12	LAST_NAME	Character(30)	VARCHAR2(30) NOT NULL	Last Name
13	SECOND_LAST_NAME	Character(30)	VARCHAR2(30) NOT NULL	Second Last Name
14	NAME_AC	Character(50)	VARCHAR2(50) NOT NULL	Alternate Character Name
15	MIDDLE_NAME	Character(30)	VARCHAR2(30) NOT NULL	MIDDLE_NAME
16	NAME_DISPLAY	Character(50)	VARCHAR2(50) NOT NULL	Display Name - name formatted for Display based on the Country
17	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
18	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department Set ID Prompt Table: SETID_TBL
19	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: PERS_DEPT_VW
20	PRIMARY_ERN	Number(4,0)	SMALLINT NOT NULL	Benefits Pre Upgrade Step primary_ern value set by client