EMPLMT_SRNM_COR(SQL View) |
Index Back |
---|---|
Employee Search - COREEMPLMT_SRNM_GBL is an Employment Search record containing all of the localized alternate search keys. None of the localized search keys are maked as alternate searches. However, it's easy for a customer to mark any/all of them as alternate search keys if they choose to. Adds APPT_TYPE = 0 to select only Core records. |
SELECT DISTINCT %Sql(SCRTY_SEL_PKEY, OPR,SEC) , FIRST_NAME_SRCH , PREF_FIRST_NM_SRCH , %Sql(SCRTY_SEL_CNMSBR,NM,SEC) , %Sql(SCRTY_SEL_FLDSBR,SEC) , J.REG_TEMP , J.FULL_PART_TIME , J.EMPL_TYPE , J.PAYGROUP , J.POSITION_NBR , PORG.BUSINESS_TITLE , PORG.BUSN_TITLE_SRCH FROM %Sql(SCRTY_PER_NM_FROM) , PS_JOB J , PS_PER_ORG_ASGN PORG WHERE SEC.APPT_TYPE IN ('0', '3') AND %Sql(SCRTY_NAME) AND %Sql(SCRTY_WHERE, 'PPLJOB') AND J.EMPLID = SEC.EMPLID AND J.EMPL_RCD=SEC.EMPL_RCD AND J.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE J.EMPLID = A_ED.EMPLID AND J.EMPL_RCD = A_ED.EMPL_RCD AND (( A_ED.EFFDT <= %CurrentDateIn) OR (A_ED.EFFDT > %CurrentDateIn AND %CurrentDateIn < ( SELECT MIN(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD) ) )) AND J.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE J.EMPLID = A_ES.EMPLID AND J.EMPL_RCD = A_ES.EMPL_RCD AND J.EFFDT = A_ES.EFFDT) AND J.EMPLID=PORG.EMPLID AND J.EMPL_RCD=PORG.EMPL_RCD |
# | 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 | FIRST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | First Name |
6 | PREF_FIRST_NM_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Preferred First Name |
7 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
8 | NAME_DISPLAY_SRCH | Character(50) | VARCHAR2(50) NOT NULL | Uppercase version of Name to improve performance on searches. |
9 | NAME_FORMAL | Character(60) | VARCHAR2(60) NOT NULL | Formal Name - name formatted for Formal Display by COuntry. Normally used in Correspondence. |
10 | NAME_FORMAL_SRCH | Character(60) | VARCHAR2(60) NOT NULL | Uppercase version of formal name to improve performance on searches. |
11 | NAME_PSFORMAT | Character(50) | VARCHAR2(50) NOT NULL | A Person's name in the Peoplesoft Format (basically LN,FN MI |
12 | NAME_FORMAL_PSF | Character(60) | VARCHAR2(60) NOT NULL | Preferred Name (PS FORMAT) |
13 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
14 | MIDDLE_NAME | Character(30) | VARCHAR2(30) NOT NULL | MIDDLE_NAME |
15 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
16 | LAST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
17 | SECOND_LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Second Last Name |
18 | SECOND_LAST_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Second Last Name |
19 | PREF_FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Preferred First Name |
20 | PREF_MIDDLE_NAME | Character(30) | VARCHAR2(30) NOT NULL | Preferred Middle Name |
21 | PREF_LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Preferred Last Name |
22 | PREF_LAST_NM_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Uppercase version of preferred last name to improve performance on searches. |
23 | PREF_SEC_LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Preferred Second Last Name |
24 | PREF_LAST_SEC_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Uppercase version of preferred second last name to improve performance on searches. |
25 | NAME_AC | Character(50) | VARCHAR2(50) NOT NULL | Alternate Character Name |
26 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
27 | 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 |
28 | POI_TYPE | Character(5) | VARCHAR2(5) NOT NULL | Defines the Transactional usage of POIs in HRMS. |
29 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
30 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL |
Department Set ID
Prompt Table: SETID_TBL |
31 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL |
Department
Prompt Table: PERS_DEPT_VW |
32 | SETID_LOCATION | Character(5) | VARCHAR2(5) NOT NULL | Location Set ID |
33 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
34 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
35 | REG_REGION | Character(5) | VARCHAR2(5) NOT NULL | Regulatory Region |
36 | INSTITUTION | Character(5) | VARCHAR2(5) NOT NULL | Academic Institution |
37 | NATIONAL_ID | Character(20) | VARCHAR2(20) NOT NULL | National ID |
38 | HR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
HR Status
A=Active I=Inactive |
39 | MILITARY_SERVICE | Character(8) | VARCHAR2(8) NOT NULL |
Military Service
Prompt Table: MIL_SERVICE_TBL |
40 | MIL_RANK | Character(5) | VARCHAR2(5) NOT NULL |
Military Rank
Prompt Table: MIL_RANK_TBL |
41 | MIL_WORN_RANK | Character(5) | VARCHAR2(5) NOT NULL |
Worn Rank
Prompt Table: MIL_RANK_TBL |
42 | 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 |
43 | 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 |
44 | OTHER_ID_JPN | Character(11) | VARCHAR2(11) NOT NULL | Other Identifier |
45 | 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 |
46 | REPORTS_TO | Character(8) | VARCHAR2(8) NOT NULL | Reports To Position Number |
47 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL | Supervisor ID |
48 | SETID_JOBCODE | Character(5) | VARCHAR2(5) NOT NULL | Job Code Set ID |
49 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
50 | REG_TEMP | Character(1) | VARCHAR2(1) NOT NULL |
Regular/Temporary
R=Regular T=Temporary |
51 | FULL_PART_TIME | Character(1) | VARCHAR2(1) NOT NULL |
Full/Part Time
D=On Demand F=Full-Time P=Part-Time |
52 | EMPL_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Employee Type
E=Exception Hourly H=Hourly N=Not Applicable S=Salaried |
53 | PAYGROUP | Character(3) | VARCHAR2(3) NOT NULL | Pay Group |
54 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL | Position Number |
55 | BUSINESS_TITLE | Character(30) | VARCHAR2(30) NOT NULL | Business Title |
56 | BUSN_TITLE_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Business Title |