PRIM_EMPL_SRCH

(SQL View)
 Index Back

Primary Employment Search

PRIM_EMPL_SRCH is a derivative of EMPLMT_SRCH_GBL with the additional restricting of returning only the Current Primary Jobs for an employee. Use it to select employee records for you to use when you access a panel. This view shows you records only for employees in departments that your operator class is allowed to access. Use this view to access records with primary key of EMPLID and where Job Information is required, but EMPL_RCD is not a key. So, the system can make a logical choice between multiple job records.

SELECT %Sql(SCRTY_SEL_PKEY,SEC,SEC) , %Sql(SCRTY_SEL_PERSBR,SEC,SEC) , %Sql(SCRTY_SEL_FLDSBR,SEC) , SEC.NAME_AC FROM PS_JOB JOB , PS_EMPLMT_SRCH_GBL SEC WHERE JOB.EMPLID = SEC.EMPLID AND JOB.EMPL_RCD = SEC.EMPL_RCD AND JOB.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = JOB.EMPLID AND EMPL_RCD = JOB.EMPL_RCD AND EFFDT <= %CurrentDateIn AND EFFSEQ = JOB.EFFSEQ) AND JOB.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = JOB.EMPLID AND EMPL_RCD = JOB.EMPL_RCD AND EFFDT = JOB.EFFDT) AND JOB.EMPL_RCD = ( SELECT MIN(JOB2.EMPL_RCD) FROM PS_JOB JOB2 WHERE JOB2.EMPLID = JOB.EMPLID AND JOB2.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = JOB2.EMPLID AND EMPL_RCD = JOB2.EMPL_RCD AND EFFDT <= JOB.EFFDT) AND JOB2.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = JOB2.EMPLID AND EMPL_RCD = JOB2.EMPL_RCD AND EFFDT = JOB2.EFFDT) AND ((JOB2.EMPL_STATUS IN('A','L','P','S','W') AND JOB2.JOB_INDICATOR = 'P') OR NOT EXISTS ( SELECT 'X' FROM PS_JOB JOB3 WHERE JOB3.EMPLID = JOB2.EMPLID AND JOB3.EMPL_RCD <> JOB2.EMPL_RCD AND JOB3.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = JOB3.EMPLID AND EMPL_RCD = JOB3.EMPL_RCD AND EFFDT <= JOB.EFFDT) AND JOB3.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = JOB3.EMPLID AND EMPL_RCD = JOB3.EMPL_RCD AND EFFDT = JOB3.EFFDT) AND ((JOB3.EMPL_STATUS IN('A','L','P','S','W') AND (JOB2.EMPL_STATUS NOT IN('A','L','P','S','W') OR (JOB3.JOB_INDICATOR = 'P' AND JOB2.JOB_INDICATOR <> 'P'))) OR (JOB3.JOB_INDICATOR = 'P' AND JOB2.JOB_INDICATOR <> 'P' AND JOB2.EMPL_STATUS NOT IN('A','L','P','S','W'))))))

# 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 Rcd Nbr
5 NAME Character(50) VARCHAR2(50) NOT NULL Name
6 NAME_DISPLAY_SRCH Character(50) VARCHAR2(50) NOT NULL Uppercase version of Name to improve performance on searches.
7 NAME_PSFORMAT Character(50) VARCHAR2(50) NOT NULL A Person's name in the Peoplesoft Format (basically LN,FN MI
8 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
9 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Name
10 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
11 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
12 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
13 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
14 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
15 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
16 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker
EMP=Employee
POI=Person of Interest
17 POI_TYPE Character(5) VARCHAR2(5) NOT NULL Defines the Transactional usage of POIs in HRMS.
18 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
19 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department SetID

Prompt Table: SETID_TBL

20 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: PERS_DEPT_VW

21 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location SetID
22 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
23 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
24 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
25 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
26 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
27 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive
28 MILITARY_SERVICE Character(8) VARCHAR2(8) NOT NULL Military Service

Prompt Table: MIL_SERVICE_TBL

29 MIL_RANK Character(5) VARCHAR2(5) NOT NULL Military Rank

Prompt Table: MIL_RANK_TBL

30 MIL_WORN_RANK Character(5) VARCHAR2(5) NOT NULL Worn Rank

Prompt Table: MIL_RANK_TBL

31 APPT_TYPE Character(1) VARCHAR2(1) NOT NULL This field tracks whether a row is a Main Appointment (which all rows outside Japan are), or an Additional Appointment, which is a feature only used by Japanese customers.
0=Core Appointment
1=Japan Kenmu Appointment
2=French Public Sector Appt
3=Staffing
32 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
33 OTHER_ID_JPN Character(11) VARCHAR2(11) NOT NULL Other Identifier
34 FUTURE_FLG Character(1) VARCHAR2(1) NOT NULL Flag for the SJT_*** record that indicates whether this is a future dated row or not. Y = Future N = Current
35 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
36 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
37 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code SetID
38 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
39 NAME_AC_SEC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name