HRS_JOBOPEN_VW2

(SQL View)
 Index Back

JOB OPEN HEADER VIEW

Header record for HTML email Approval - Job opening

SELECT DISTINCT J.HRS_JOB_OPENING_ID , J.HRS_PRM_PST_TITLE , J.HRS_JO_TYPE , X.XLATLONGNAME , J.HRS_PRM_POSITION , CASE WHEN INST.FED = 'Y' THEN POS.GVT_ORG_TTL_DESCR ELSE POS.DESCR END , J.BUSINESS_UNIT , BU.DESCR , J.HRS_PRM_JOBCODE , J.JOB_FAMILY , JFM.DESCR , J.COMPANY , COM.DESCR , J.SETID_DEPT , J.HRS_PRM_LOC_AREA , LOC.DESCR , J.DEPTID , DPT.DESCR , Z.NAME_DISPLAY , Z1.NAME_DISPLAY , J.OPEN_DT , J.DESIRED_START_DT , J.OPENINGS_TARGET FROM PS_HRS_JOB_OPENING J LEFT OUTER JOIN PS_HRS_PERSON_NM_I Z ON Z.EMPLID = J.RECRUITER_ID LEFT OUTER JOIN PS_HRS_PERSON_NM_I Z1 ON Z1.EMPLID = J.MANAGER_ID LEFT OUTER JOIN PS_HRS_POS_DTA_VW POS ON POS.POSITION_NBR = J.HRS_PRM_POSITION LEFT OUTER JOIN PS_JOB_FAMILY_EFVW JFM ON JFM.JOB_FAMILY = J.JOB_FAMILY LEFT OUTER JOIN PS_HRS_COMPANY_I2 COM ON COM.COMPANY = J.COMPANY LEFT OUTER JOIN PS_HRS_RECR_LOC_I LOC ON LOC.SETID = J.SETID_DEPT AND LOC.HRS_LOCATION_ID = J.HRS_PRM_LOC_AREA LEFT OUTER JOIN PS_HRS_DEPT_VW2 DPT ON DPT.SETID = J.SETID_DEPT AND DPT.DEPTID = J.DEPTID , PS_BUS_UNIT_TBL_HR BU , PSXLATITEM X , PS_INSTALLATION INST WHERE BU.BUSINESS_UNIT = J.BUSINESS_UNIT AND X.FIELDNAME = 'HRS_JO_TYPE' AND X.FIELDVALUE = J.HRS_JO_TYPE AND X.EFFDT = ( SELECT MAX(X2.EFFDT) FROM PSXLATITEM X2 WHERE X2.FIELDNAME = X.FIELDNAME AND X2.FIELDVALUE = X.FIELDVALUE AND X2.EFFDT <= %CurrentDateIn) AND X.EFF_STATUS = 'A'

  • Related Language Record: HRS_JOBOPENVW2L

    • # 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 POSTING_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
    3 HRS_JO_TYPE Character(1) VARCHAR2(1) NOT NULL Job Opening Type
    P=Continuous Job Opening
    R=Standard Requisition
    4 XLATLONGNAME Character(30) VARCHAR2(30) NOT NULL Translate Long Name
    5 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
    6 DESCR6 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    7 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    8 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    9 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    10 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
    11 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    12 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
    13 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    14 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    15 HRS_LOCATION_ID Number(15,0) DECIMAL(15) NOT NULL Recruiting location area
    16 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
    17 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    18 DESCR5 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    19 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    20 NAME_DESCR Character(50) VARCHAR2(50) NOT NULL Description
    21 OPEN_DT Date(10) DATE Created
    22 DESIRED_START_DT Date(10) DATE Desired Start Date
    23 OPENINGS_TARGET Number(3,0) SMALLINT NOT NULL Tracks number of target openings