SCH_PERSON_VW(SQL View) |
Index Back |
|---|---|
Current Primary Name ViewThis view retrieves the currently effective Names record for all the Primary Name. Only one row per emplid is returned. The EFFDT is included only for reference back to NAMES. No Query Security record is specified because in query this would be one of the joined in records. The main record should contain the query security. |
| SELECT OPR1.OPRID ,A.EMPLID ,%Sql(NAME_SBR_SQL,A) ,A.NAME_DISPLAY_SRCH ,A.NAME_TYPE ,A.EFFDT , A.NAME FROM PS_NAMES A , PS_JOB J , PSCLASSDEFN O , PSOPRDEFN OPR1 WHERE OPR1.ROWSECCLASS = O.CLASSID AND A.EMPLID = J.EMPLID AND A.NAME_TYPE = 'PRI' AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_NAMES B WHERE B.EMPLID = A.EMPLID AND B.NAME_TYPE = A.NAME_TYPE AND B.EFFDT <= %CurrentDateIn ) AND ((EXISTS ( SELECT 'X' FROM PS_TL_GRP_SECURITY S WHERE S.ROWSECCLASS = O.CLASSID) AND J.EMPLID IN ( SELECT F.EMPLID FROM PS_TL_GRP_SECURITY S , PS_TL_GROUP_DTL F WHERE S.TL_GROUP_ID = F.TL_GROUP_ID AND F.EMPLID = J.EMPLID AND F.EMPL_RCD = J.EMPL_RCD AND S.ROWSECCLASS = O.CLASSID)) OR (NOT EXISTS ( SELECT 'X' FROM PS_TL_GRP_SECURITY S WHERE S.ROWSECCLASS = O.CLASSID) AND J.EMPLID IN ( SELECT SEC.EMPLID FROM %Sql(SCRTY_PER_FROM) WHERE %Sql(SCRTY_NO_APPT1) AND %Sql(SCRTY_WHERE, 'PPLJOB') AND J.EMPLID = SEC.EMPLID AND J.EMPL_RCD = SEC.EMPL_RCD AND OPR.ROWSECCLASS = OPR1.ROWSECCLASS AND OPR.OPRID = OPR1.OPRID))) AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT = J.EFFDT) |
| # | 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 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: PERSON |
| 3 | COUNTRY_NM_FORMAT | Character(3) | VARCHAR2(3) NOT NULL |
Supported Name Format Types
Default Value: OPR_DEF_TBL_HR.COUNTRY_NM_FORMAT Prompt Table: NAME_FORMAT_TBL |
| 4 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 5 | NAME_INITIALS | Character(6) | VARCHAR2(6) NOT NULL | Name Initials |
| 6 | NAME_PREFIX | Character(4) | VARCHAR2(4) NOT NULL |
Name Prefix
Prompt Table: NAME_PREFIX_TBL |
| 7 | NAME_SUFFIX | Character(15) | VARCHAR2(15) NOT NULL |
Name Suffix
Prompt Table: NAME_SUFFIX_TBL |
| 8 | NAME_ROYAL_PREFIX | Character(15) | VARCHAR2(15) NOT NULL |
Name Royal Prefix
Prompt Table: NM_ROYPREF_GBL |
| 9 | NAME_ROYAL_SUFFIX | Character(15) | VARCHAR2(15) NOT NULL |
Name - Royal Suffix
Prompt Table: NM_ROYSUFF_GBL |
| 10 | NAME_TITLE | Character(30) | VARCHAR2(30) NOT NULL |
Name Title
Prompt Table: TITLE_TBL |
| 11 | LAST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 12 | FIRST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 13 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 14 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 15 | MIDDLE_NAME | Character(30) | VARCHAR2(30) NOT NULL | MIDDLE_NAME |
| 16 | SECOND_LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Second Last Name |
| 17 | SECOND_LAST_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Second Last Name |
| 18 | NAME_AC | Character(50) | VARCHAR2(50) NOT NULL | Alternate Character Name |
| 19 | PREF_FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Preferred First Name |
| 20 | PARTNER_LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | The Partner Last Name is being used by the Netherlands for the employee name formatting, where a person can choose to use the partners last name as their prefered last name. |
| 21 | PARTNER_ROY_PREFIX | Character(15) | VARCHAR2(15) NOT NULL |
The Partner Royal Prefix is being used by the Netherlands for the employee name formatting, where a person can choose to use the partners last name and prefix as their prefered last name.
Prompt Table: NM_ROYPREF_GBL |
| 22 | LAST_NAME_PREF_NLD | Character(1) | VARCHAR2(1) NOT NULL |
The field Last Name Preference is used in the Netherlands specific for people who are married to indicated their preference regarding the last name, which can be 4 choices based on the own last name and the partners last name.
1=Own Name 2=Name Partner 3=Name Partner + Own Name 4=Own Name + Name Partner Default Value: 1 |
| 23 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
| 24 | NAME_FORMAL | Character(60) | VARCHAR2(60) NOT NULL | Formal Name - name formatted for Formal Display by COuntry. Normally used in Correspondence. |
| 25 | NAME_DISPLAY_SRCH | Character(50) | VARCHAR2(50) NOT NULL | Uppercase version of Name to improve performance on searches. |
| 26 | NAME_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
Type of Name
Prompt Table: NAME_TYPE_TBL |
| 27 | ASOFDATE | Date(10) | DATE | As of Date |
| 28 | NAME_PSFORMAT | Character(50) | VARCHAR2(50) NOT NULL | A Person's name in the Peoplesoft Format (basically LN,FN MI |