BAS_ON_DEM_SRCH

(SQL View)
 Index Back

On-Demand Event Maint Search

BAS_ON_DEM_SRCH is a clone of EMPLMT_SRCH_GBL, and then is adding a conditon to exclude the "Other" Benefit System from the list. Use it to select person records for a specific EMPL_RCD (ERN) for you to use when you access a panel. This view shows you records only for persons in departments that your operator class is allowed to access. Use this view to access records with primary keys of EMPLID and EMPL_RCD#. To access records keyed by EMPLID without EMPL_RCD#, use one of ther Personal Searches.

SELECT %Sql(SCRTY_SEL_PKEY,SEC,SEC) , %Sql(SCRTY_SEL_NMSBR,SEC,SEC) ,SEC.BUSINESS_UNIT ,SEC.SETID_DEPT ,SEC.DEPTID ,SEC.PER_ORG ,SEC.COMPANY ,SEC.SETID_JOBCODE ,SEC.JOBCODE ,SEC.MILITARY_SERVICE ,SEC.MIL_RANK ,SEC.MIL_WORN_RANK FROM PS_EMPLMT_SRCH_GBL SEC WHERE EXISTS ( SELECT 'X' FROM PS_JOB JOB WHERE SEC.EMPLID = JOB.EMPLID AND SEC.EMPL_RCD = JOB.EMPL_RCD AND ( JOB.EFFDT > %CurrentDateIn OR (JOB.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = JOB.EMPLID AND J2.EMPL_RCD = JOB.EMPL_RCD AND J2.EFFDT <= %CurrentDateIn)) AND JOB.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB J3 WHERE J3.EMPLID = JOB.EMPLID AND J3.EMPL_RCD = JOB.EMPL_RCD AND J3.EFFDT = JOB.EFFDT) ) AND JOB.BENEFIT_SYSTEM <> 'OT')

# 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 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
17 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department SetID
18 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
19 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
20 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
21 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code SetID
22 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
23 MILITARY_SERVICE Character(8) VARCHAR2(8) NOT NULL Military Service

Prompt Table: MIL_SERVICE_TBL

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

Prompt Table: MIL_RANK_TBL

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

Prompt Table: MIL_RANK_TBL