HR_JOB_RTI_DAT1

(SQL View)
 Index Back

Job Data DAT Record for Search


SELECT A.EMPLID , A.EMPL_RCD , A.DEPTID , A.JOBCODE , A.POSITION_NBR , A.REG_TEMP , A.FULL_PART_TIME , A.BUSINESS_UNIT , A.PER_ORG , A.LOCATION , %Coalesce((SELECT B.DESCR FROM PS_POSITION_DATA B WHERE B.POSITION_NBR = A.POSITION_NBR AND %EffdtCheck(POSITION_DATA B_ED, B, %CurrentDateIn)), ' ') , %Coalesce(C.DESCR , ' ') , A.REG_REGION , %Coalesce(E.DESCR , ' ') , %Coalesce(F.DESCR , ' ') , G.NAME , G.LAST_NAME , G.FIRST_NAME , G.MIDDLE_NAME , G.SECOND_LAST_NAME , G.PARTNER_LAST_NAME , G.PREF_LAST_NAME , G.PREF_MIDDLE_NAME , G.PREF_SEC_LAST_NAME , G.NAME_AC , G.NAME_DISPLAY , G.NAME_FORMAL , G.NAME_FORMAL_PSF , %CurrentDateTimeIn , A.DESCR50 , A.PAYGROUP , A.GP_PAYGROUP , A.PAY_SYSTEM_FLG , %Coalesce(A.HR_SRCH_PYGP_DESCR , ' ') , %Coalesce(A.HR_SRCH_GPPY_DESCR , ' ') , A.ABSENCE_SYSTEM_CD , A.COMPANY , %Coalesce(A.HR_SRCH_GP_XLAT , ' ') , A.ESTABID , A.HR_SRCH_ABS_STM_CD , %Coalesce(A.HR_SRCH_ABS_XLAT, ' ') , %Coalesce(A.HR_SRCH_CMP_DESCR, ' ') , G.LAST_NAME_SRCH , G.FIRST_NAME_SRCH , G.MIDDLE_NAME_SRCH , G.SECOND_LAST_SRCH , G.PARTN_LAST_NM_SRCH , G.PREF_LAST_NM_SRCH , G.PREF_FIRST_NM_SRCH , G.PREF_MIDL_NM_SRCH , G.PREF_LAST_SEC_SRCH , G.NAME_DISPLAY_SRCH , G.NAME_FORMAL_SRCH , A.SUPERVISOR_ID , A.REPORTS_TO , A.ACTION , A.ACTION_REASON , A.HR_STATUS , A.EMPL_STATUS , A.EMPL_TYPE , G.PREF_FIRST_NAME , A.SUPERVISOR_NAME , A.REPORTS_TO_DESCR , A.BUSINESS_TITLE , A.MILITARY_SERVICE , A.MIL_RANK , A.MIL_WORN_RANK , A.BUSINESS_DESCR FROM PS_HR_JOB_RTI_TMP1 A , PS_LOCATION_TBL C , PS_DEPT_TBL E , PS_JOBCODE_TBL F , PS_NAMES G WHERE C.SETID = A.SETID_LOCATION AND C.LOCATION = A.LOCATION AND %EffdtCheck(LOCATION_TBL C_ED, C, %CurrentDateIn) AND E.SETID = A.SETID_DEPT AND E.DEPTID = A.DEPTID AND %EffdtCheck(DEPT_TBL E_ED, E, %CurrentDateIn) AND F.SETID = A.SETID_JOBCODE AND F.JOBCODE = A.JOBCODE AND %EffdtCheck(JOBCODE_TBL F_ED, F, %CurrentDateIn) AND A.EMPLID = G.EMPLID AND G.NAME_TYPE='PRI' AND %EffdtCheck(NAMES G_ED, G, %CurrentDateIn)

  • Related Language Record: HR_JOB_RTI_DATL

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Default Value: PER_ORG_ASGN.EMPLID

    Prompt Table: PERS_SRCH_GBL

    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: DEPT_TBL

    4 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

    Prompt Table: JOBCODE_TBL

    5 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

    Prompt Table: POSN_DATA_VW

    6 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
    R=Regular
    T=Temporary

    Default Value: R

    7 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
    D=On Demand
    F=Full-Time
    P=Part-Time

    Default Value: F

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

    Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT

    Prompt Table: BUSUNIT_HR_VW

    9 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

    Default Value: PER_ORG_ASGN.PER_ORG

    10 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    11 HR_SRCH_POS_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
    12 HR_SRCH_LOC_DESCR Character(30) VARCHAR2(30) NOT NULL Location
    13 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region

    Default Value: OPR_DEF_TBL_HR.REG_REGION

    Prompt Table: REG_STANDARD_VW

    14 HR_SRCH_DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department
    15 HR_SRCH_JBCD_DESCR Character(30) VARCHAR2(30) NOT NULL Job Title
    16 NAME Character(50) VARCHAR2(50) NOT NULL Name
    17 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    18 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
    19 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
    20 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
    21 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.
    22 PREF_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred Last Name
    23 PREF_MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL Preferred Middle Name
    24 PREF_SEC_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred Second Last Name
    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 NAME_FORMAL Character(60) VARCHAR2(60) NOT NULL Formal Name - name formatted for Formal Display by COuntry. Normally used in Correspondence.
    28 NAME_FORMAL_PSF Character(60) VARCHAR2(60) NOT NULL Preferred Name (PS FORMAT)
    29 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

    Default Value: %datetime

    30 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    31 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group

    Prompt Table: %EDITTABLE5

    32 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
    33 PAY_SYSTEM_FLG Character(2) VARCHAR2(2) NOT NULL Payroll System
    GP=Global Payroll
    NA=Payroll for North America
    OT=Other
    PI=Payroll Interface

    Default Value: OPR_DEF_TBL_HR.PAY_SYSTEM_FLG

    34 HR_SRCH_PYGP_DESCR Character(30) VARCHAR2(30) NOT NULL Paygroup Description
    35 HR_SRCH_GPPY_DESCR Character(30) VARCHAR2(30) NOT NULL GP paygroup Description
    36 ABSENCE_SYSTEM_CD Character(3) VARCHAR2(3) NOT NULL What Absence System this Assignment is tied to.
    AM=Absence Management
    OT=Other

    Default Value: OT

    37 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

    Prompt Table: COMPANY_TBL

    38 HR_SRCH_GP_XLAT Character(30) VARCHAR2(30) NOT NULL GP paysytem xlatlongname
    39 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
    40 HR_SRCH_ABS_STM_CD Character(3) VARCHAR2(3) NOT NULL SES: Absence system code referenced in HR_SRCH_JOBDATA query
    41 HR_SRCH_ABS_XLAT Character(30) VARCHAR2(30) NOT NULL Abs paysytem xlatlongname
    42 HR_SRCH_CMP_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
    43 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    44 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
    45 MIDDLE_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Middle Name uppercase search field
    46 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
    47 PARTN_LAST_NM_SRCH Character(30) VARCHAR2(30) NOT NULL Uppercase version of partner last name to improve performance on searches.
    48 PREF_LAST_NM_SRCH Character(30) VARCHAR2(30) NOT NULL Uppercase version of preferred last name to improve performance on searches.
    49 PREF_FIRST_NM_SRCH Character(30) VARCHAR2(30) NOT NULL Preferred First Name
    50 PREF_MIDL_NM_SRCH Character(30) VARCHAR2(30) NOT NULL Uppercase version of preferred middle name to improve performance on searches.
    51 PREF_LAST_SEC_SRCH Character(30) VARCHAR2(30) NOT NULL Uppercase version of preferred second last name to improve performance on searches.
    52 NAME_DISPLAY_SRCH Character(50) VARCHAR2(50) NOT NULL Uppercase version of Name to improve performance on searches.
    53 NAME_FORMAL_SRCH Character(60) VARCHAR2(60) NOT NULL Uppercase version of formal name to improve performance on searches.
    54 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
    55 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
    56 ACTION Character(3) VARCHAR2(3) NOT NULL Action
    57 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code
    58 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
    A=Active
    I=Inactive
    59 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
    60 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
    E=Exception Hourly
    H=Hourly
    N=Not Applicable
    S=Salaried
    61 PREF_FIRST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred First Name
    62 SUPERVISOR_NAME Character(50) VARCHAR2(50) NOT NULL Supervisor Name
    63 REPORTS_TO_DESCR Character(50) VARCHAR2(50) NOT NULL Reports To
    64 BUSINESS_TITLE Character(30) VARCHAR2(30) NOT NULL Business Title
    65 MILITARY_SERVICE Character(8) VARCHAR2(8) NOT NULL Military Service
    66 MIL_RANK Character(5) VARCHAR2(5) NOT NULL Military Rank
    67 MIL_WORN_RANK Character(5) VARCHAR2(5) NOT NULL Worn Rank
    68 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description