HRS_ALL_JOBS_VW

(SQL View)
 Index Back

Job Openings

All job openings

SELECT A.HRS_JOB_OPENING_ID , A.HRS_JO_TYPE , A.HRS_PRM_PST_TITLE , A.ORIGINATOR_ID , A.MANAGER_ID , AM.NAME_DISPLAY , A.RECRUITER_ID , AR.NAME_DISPLAY , A.OPEN_DT , A.CLOSE_DT , A.STATUS_CODE , B.DESCR , A.STATUS_DT , A.HOT_JOB , A.BUSINESS_UNIT , C.DESCR , A.SETID_DEPT , A.DEPTID , ' ' , A.COMPANY , A.HRS_PRM_POSITION , A.HRS_PRM_LOCATION , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , COALESCE (E.JOB_FAMILY , A.JOB_FAMILY) , ' ' , A.HRS_PRM_JOBCODE , CASE WHEN E.DESCR IS NULL THEN A.HRS_PRM_JOBCODE ELSE A.HRS_PRM_JOBCODE %Concat ' ' %Concat %Substring(E.DESCR, 1, 23) END , %Substring(%Cast(%DateTimeOut(A.HRS_ROW_ADD_DTTM), DateTime, Character, 10), 1, 4) , %Substring(%Cast(%DateTimeOut(A.HRS_ROW_ADD_DTTM), DateTime, Character, 10), 6, 2) , A.HRS_ROW_ADD_DTTM , A.HRS_ROW_ADD_OPRID , CASE WHEN COALESCE (AIDX.HRS_ROW_UPD_DTTM , %DateTimeIn('1900-01-01-00.00.00.000000')) > A.HRS_ROW_UPD_DTTM THEN COALESCE (AIDX.HRS_ROW_UPD_DTTM , %DateTimeIn('1900-01-01-00.00.00.000000')) ELSE A.HRS_ROW_UPD_DTTM END , A.HRS_ROW_UPD_OPRID FROM PS_HRS_JOB_OPENING A LEFT OUTER JOIN PS_HRS_PERSON_NM_I AM ON (A.MANAGER_ID = AM.EMPLID) LEFT OUTER JOIN PS_HRS_PERSON_NM_I AR ON (A.RECRUITER_ID = AR.EMPLID) LEFT OUTER JOIN PS_HRS_JOBINDX_CTL AIDX ON (A.HRS_JOB_OPENING_ID = AIDX.HRS_JOB_OPENING_ID AND AIDX.INDEX_ACTION = 'U') LEFT OUTER JOIN PS_JOBCODE_TBL_VW E ON A.HRS_PRM_JOBCODE = E.JOBCODE , PS_HRS_STS_JO_I B , PS_HRS_BS_UNT_HR_J C , PS_SET_CNTRL_GROUP D WHERE B.STATUS_AREA = '1' AND A.STATUS_CODE = B.STATUS_CODE AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.BUSINESS_UNIT = D.SETCNTRLVALUE AND D.REC_GROUP_ID = 'HR_02' AND (E.SETID IS NULL OR D.SETID = E.SETID)

  • Related Language Record: HRS_ALL_JOBS_LV

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 HRS_JOB_OPENING_ID Number(15,0) DECIMAL(15) NOT NULL Job Opening Clone
    2 HRS_JO_TYPE Character(1) VARCHAR2(1) NOT NULL Job Opening Type
    P=Continuous Job Opening
    R=Standard Requisition
    3 HRS_PRM_PST_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
    4 ORIGINATOR_ID Character(11) VARCHAR2(11) NOT NULL Originator's ID

    Prompt Table: HRS_ACT_PERS_I1

    5 MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager ID

    Prompt Table: HRS_ACT_PERS_I1

    6 HRS_HIRING_MGR Character(50) VARCHAR2(50) NOT NULL Hiring manager
    7 RECRUITER_ID Character(11) VARCHAR2(11) NOT NULL Recruiter ID

    Prompt Table: HRS_ACT_PERS_I1

    8 HRS_RECRUITER Character(50) VARCHAR2(50) NOT NULL Recruiter
    9 OPEN_DT Date(10) DATE Created
    10 CLOSE_DT Date(10) DATE Date Closed
    11 STATUS_CODE Character(3) VARCHAR2(3) NOT NULL Identifies a status.

    Prompt Table: HRS_STS_JO_I

    12 HRS_STATUS Character(30) VARCHAR2(30) NOT NULL Status
    13 STATUS_DT Date(10) DATE Status Date
    14 HOT_JOB Character(1) VARCHAR2(1) NOT NULL Indicates which Job requisitions are classed as urgent to fill. Hot Jobs are identified during the develop requisition process and used as an indicator for both the organization and applicant.
    N=No
    Y=Yes

    Y/N Table Edit

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

    Prompt Table: HRS_BS_UNT_HR_J

    16 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    17 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    18 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: HRS_DEPT_I

    19 DEPTNAME Character(30) VARCHAR2(30) NOT NULL Department Name
    20 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

    Prompt Table: HRS_COMPANY_I

    21 HRS_PRM_POSITION Character(8) VARCHAR2(8) NOT NULL Position Number
    22 HRS_PRM_LOCATION Character(10) VARCHAR2(10) NOT NULL Primary Recruiting Location of Job Opening,

    Prompt Table: HRS_LOCATN_I

    23 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location
    24 CITY Character(30) VARCHAR2(30) NOT NULL City
    25 STATE Character(6) VARCHAR2(6) NOT NULL State
    26 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Prompt Table: HRS_COUNTRY_VW

    27 HRS_PRM_LOC_AREA Number(15,0) DECIMAL(15) NOT NULL Primary recruiting location area

    Prompt Table: HRS_RECR_LOC_I

    28 HRS_RECR_LOCATION Character(30) VARCHAR2(30) NOT NULL Job opening recruiting location
    29 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
    30 HRS_JOB_FAMILY Character(30) VARCHAR2(30) NOT NULL Job family description
    31 HRS_PRM_JOBCODE Character(6) VARCHAR2(6) NOT NULL Primary Job Code of Job Opening.
    32 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    33 HRS_YEAR Character(4) VARCHAR2(4) NOT NULL Year - data type character
    34 HRS_MONTH Character(2) VARCHAR2(2) NOT NULL 2 digit month (character)
    35 HRS_ROW_ADD_DTTM DateTime(26) TIMESTAMP Row Added DateTime
    36 HRS_ROW_ADD_OPRID Character(30) VARCHAR2(30) NOT NULL Row Added Operator Id
    37 HRS_ROW_UPD_DTTM DateTime(26) TIMESTAMP Row Updated DateTime
    38 HRS_ROW_UPD_OPRID Character(30) VARCHAR2(30) NOT NULL Row Updated Operator Id