HR_JOB_RTI_TMP1

(SQL View)
 Index Back

Job Data Temp Record

This temp record has been created to extract all the Job Data and to bypass the query security record.

SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ , A.PER_ORG , A.SETID_DEPT , A.DEPTID , A.SETID_JOBCODE , A.JOBCODE , A.POSITION_NBR , A.SETID_LOCATION , A.LOCATION , A.REG_TEMP , A.FULL_PART_TIME , A.BUSINESS_UNIT , A.REG_REGION , D.DESCR50 , A.COMPANY , %Coalesce(L.DESCR, ' ') , A.PAYGROUP , A.GP_PAYGROUP , A.PAY_SYSTEM_FLG , %Coalesce(I.DESCR, ' ') , %Coalesce(M.DESCR, ' ') , A.ESTABID , A.ABSENCE_SYSTEM_CD , %Coalesce(J.XLATLONGNAME, ' ') , K.HR_SRCH_ABS_STM_CD , %Coalesce(K.XLATLONGNAME, ' ') , A.SUPERVISOR_ID , A.REPORTS_TO , A.ACTION , A.ACTION_REASON , A.HR_STATUS , A.EMPL_STATUS , A.EMPL_TYPE , %Coalesce((SELECT N.NAME_DISPLAY FROM PS_HR_SRCH_NAME_VW N WHERE N.EMPLID = A.SUPERVISOR_ID) ,' ') , %Coalesce((SELECT P.DESCR FROM PS_HR_SRCH_POS_VW P WHERE P.POSITION_NBR = A.REPORTS_TO) ,' ') , %Coalesce((SELECT O.BUSINESS_TITLE FROM PS_PER_ORG_ASGN O WHERE A.EMPLID = O.EMPLID AND A.EMPL_RCD = O.EMPL_RCD) ,' ') , %Coalesce(M3.MILITARY_SERVICE ,' ') , %Coalesce(M1.MIL_RANK ,' ') , %Coalesce(M1.MIL_WORN_RANK ,' ') , %Coalesce((SELECT A2.DESCR FROM PS_BUS_UNIT_TBL_HR A2 WHERE A.BUSINESS_UNIT = A2.BUSINESS_UNIT) ,' ') , A.LASTUPDDTTM FROM ((((PS_HR_SRCH_JOB_VW A LEFT OUTER JOIN PS_PAYGROUP_TBL I ON I.COMPANY = A.COMPANY AND I.PAYGROUP = A.PAYGROUP ) LEFT OUTER JOIN PS_GP_SRCH_PAYSTM J ON J.PAY_SYSTEM_FLG = A.PAY_SYSTEM_FLG ) LEFT OUTER JOIN PS_HR_SRCH_ABS_STM K ON K.HR_SRCH_ABS_STM_CD = A.ABSENCE_SYSTEM_CD) LEFT OUTER JOIN PS_GP_PYGRP M ON M.GP_PAYGROUP = A.GP_PAYGROUP) LEFT OUTER JOIN PS_JOB_MIL M1 ON (A.EMPLID = M1.EMPLID AND A.EMPL_RCD = M1.EMPL_RCD AND A.EFFDT = M1.EFFDT AND A.EFFSEQ = M1.EFFSEQ) LEFT OUTER JOIN PS_JOB_MIL_ASGN_VW M2 ON (A.EMPLID = M2.EMPLID AND A.EMPL_RCD = M2.EMPL_RCD) LEFT OUTER JOIN PS_MIL_RANK_TBL M3 ON (M3.MILITARY_SERVICE = M2.MILITARY_SERVICE AND M3.MIL_RANK=M1.MIL_RANK) , PS_REG_REGION_TBL D , PS_COMPANY_TBL L WHERE D.REG_REGION = A.REG_REGION AND L.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL L_ED, L, %CurrentDateIn) AND (%EffdtCheck(PAYGROUP_TBL I_ED, I, %CurrentDateIn) OR I.EFFDT IS NULL)

  • Related Language Record: HR_JOB_RTI_TMPL

    • # 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 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    5 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

    6 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID

    Default Value: OPR_DEF_TBL_HR.SETID

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

    Prompt Table: DEPT_TBL

    8 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID

    Default Value: OPR_DEF_TBL_HR.SETID

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

    Prompt Table: JOBCODE_TBL

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

    Prompt Table: POSN_DATA_VW

    11 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
    12 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    13 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
    R=Regular
    T=Temporary

    Default Value: R

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

    Default Value: F

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

    Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT

    Prompt Table: BUSUNIT_HR_VW

    16 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region

    Default Value: OPR_DEF_TBL_HR.REG_REGION

    Prompt Table: REG_STANDARD_VW

    17 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    18 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

    Prompt Table: COMPANY_TBL

    19 HR_SRCH_CMP_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
    20 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group

    Prompt Table: %EDITTABLE5

    21 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
    22 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

    23 HR_SRCH_PYGP_DESCR Character(30) VARCHAR2(30) NOT NULL Paygroup Description
    24 HR_SRCH_GPPY_DESCR Character(30) VARCHAR2(30) NOT NULL GP paygroup Description
    25 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID

    Prompt Table: %EDITTABLE3

    26 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

    27 HR_SRCH_GP_XLAT Character(30) VARCHAR2(30) NOT NULL GP paysytem xlatlongname
    28 HR_SRCH_ABS_STM_CD Character(3) VARCHAR2(3) NOT NULL SES: Absence system code referenced in HR_SRCH_JOBDATA query
    29 HR_SRCH_ABS_XLAT Character(30) VARCHAR2(30) NOT NULL Abs paysytem xlatlongname
    30 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
    31 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
    32 ACTION Character(3) VARCHAR2(3) NOT NULL Action
    33 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code
    34 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
    A=Active
    I=Inactive
    35 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
    36 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
    E=Exception Hourly
    H=Hourly
    N=Not Applicable
    S=Salaried
    37 SUPERVISOR_NAME Character(50) VARCHAR2(50) NOT NULL Supervisor Name
    38 REPORTS_TO_DESCR Character(50) VARCHAR2(50) NOT NULL Reports To
    39 BUSINESS_TITLE Character(30) VARCHAR2(30) NOT NULL Business Title
    40 MILITARY_SERVICE Character(8) VARCHAR2(8) NOT NULL Military Service
    41 MIL_RANK Character(5) VARCHAR2(5) NOT NULL Military Rank
    42 MIL_WORN_RANK Character(5) VARCHAR2(5) NOT NULL Worn Rank
    43 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description
    44 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