HRS_JO_ALL_I

(SQL View)
 Index Back

All Job Openings Interface

Interface for all job openings for current user.

SELECT X.HRS_JOB_OPENING_ID , OPR.OPRID , X.HRS_JO_TYPE , X.HRS_PRM_PST_TITLE , X.HRS_PRM_JOBCODE , X.HRS_PRM_POSITION , X.STATUS_CODE , X.STATUS_REASON , X.OPEN_DT , X.STATUS_DT , X.BUSINESS_UNIT , X.COMPANY , X.SETID_DEPT , X.DEPTID , X.HRS_PRM_LOCATION , X.HRS_PRM_LOC_AREA , X.HOT_JOB , X.OPENINGS_TARGET , X.OPENINGS_CNT , X.ORIGINATOR_ID , X.MANAGER_ID , X.RECRUITER_ID , X.HRS_CONTACT_ID , X.DESIRED_START_DT , X.PROJECTED_FILL_DT , X.HRS_ROW_ADD_DTTM , X.HRS_ROW_ADD_OPRID , X.JOB_FAMILY , COALESCE (CATG.HRS_JOBCAT_ID , 'NO') , COALESCE (D.DESCR , ' ') , ' ' , ' ' FROM (PS_HRS_JOB_OPENING X INNER JOIN PS_HRS_SJT_JO JO ON JO.HRS_JOB_OPENING_ID = X.HRS_JOB_OPENING_ID INNER JOIN PSOPRDEFN OPR ON JO.EMPLID = OPR.EMPLID AND JO.EMPLID <> ' ' LEFT OUTER JOIN PS_HRS_JO_LOC_AR_I D ON D.HRS_JOB_OPENING_ID = X.HRS_JOB_OPENING_ID AND D.HRS_LOCATION_ID = X.HRS_PRM_LOC_AREA) LEFT OUTER JOIN PS_HRS_JO_JOB_CATG CATG ON X.HRS_JOB_OPENING_ID = CATG.HRS_JOB_OPENING_ID AND CATG.OPRID = OPR.OPRID UNION SELECT X.HRS_JOB_OPENING_ID , OPR.OPRID , X.HRS_JO_TYPE , X.HRS_PRM_PST_TITLE , X.HRS_PRM_JOBCODE , X.HRS_PRM_POSITION , X.STATUS_CODE , X.STATUS_REASON , X.OPEN_DT , X.STATUS_DT , X.BUSINESS_UNIT , X.COMPANY , X.SETID_DEPT , X.DEPTID , X.HRS_PRM_LOCATION , X.HRS_PRM_LOC_AREA , X.HOT_JOB , X.OPENINGS_TARGET , X.OPENINGS_CNT , X.ORIGINATOR_ID , X.MANAGER_ID , X.RECRUITER_ID , X.HRS_CONTACT_ID , X.DESIRED_START_DT , X.PROJECTED_FILL_DT , X.HRS_ROW_ADD_DTTM , X.HRS_ROW_ADD_OPRID , X.JOB_FAMILY , COALESCE (CATG.HRS_JOBCAT_ID , 'NO') , COALESCE (D.DESCR , ' ') , ' ' , ' ' FROM (PS_HRS_JOB_OPENING X INNER JOIN PS_HRS_SJT_JO JO ON JO.HRS_JOB_OPENING_ID = X.HRS_JOB_OPENING_ID INNER JOIN PS_SJT_CLASS_ALL CLS ON CLS.SCRTY_KEY1 = JO.SCRTY_KEY1 AND CLS.SCRTY_KEY2 = JO.SCRTY_KEY2 AND CLS.SCRTY_KEY3 = JO.SCRTY_KEY3 INNER JOIN PS_SJT_OPR_CLS RSC ON RSC.CLASSID = CLS.CLASSID INNER JOIN PSOPRDEFN OPR ON RSC.OPRID = OPR.OPRID AND JO.EMPLID = ' ' LEFT OUTER JOIN PS_HRS_JO_LOC_AR_I D ON D.HRS_JOB_OPENING_ID = X.HRS_JOB_OPENING_ID AND D.HRS_LOCATION_ID = X.HRS_PRM_LOC_AREA) LEFT OUTER JOIN PS_HRS_JO_JOB_CATG CATG ON X.HRS_JOB_OPENING_ID = CATG.HRS_JOB_OPENING_ID AND CATG.OPRID = OPR.OPRID WHERE ((CLS.SCRTY_SET_CD = 'RSOPN' AND CLS.SCRTY_TYPE_CD = JO.SCRTY_TYPE_CD) OR (CLS.SCRTY_SET_CD = 'PPLJOB' AND CLS.SCRTY_TYPE_CD = '001' AND JO.SCRTY_TYPE_CD = '012' )) AND EXISTS ( SELECT 'Y' FROM PS_HRS_ROLE_DEFN A , PSROLEUSER R WHERE A.EFF_STATUS = 'A' AND %EffdtCheck(HRS_ROLE_DEFN T, A, %CurrentDateIn) AND A.HRS_APPCNT_SEC = 'N' AND A.ROLENAME = R.ROLENAME AND OPR.OPRID = R.ROLEUSER )

  • Related Language Record: HRS_JO_SEC_LN_3

    • # 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 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    3 HRS_JO_TYPE Character(1) VARCHAR2(1) NOT NULL Job Opening Type
    P=Continuous Job Opening
    R=Standard Requisition
    4 POSTING_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
    5 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

    Prompt Table: HRS_JOBCODE_I

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

    Prompt Table: HRS_POS_DTA_C_I

    7 STATUS_CODE Character(3) VARCHAR2(3) NOT NULL Identifies a status.

    Prompt Table: HRS_STS_JO_VW

    8 STATUS_REASON Character(3) VARCHAR2(3) NOT NULL Identifies a reason associated with a particular status.
    9 OPEN_DT Date(10) DATE Created
    10 STATUS_DT Date(10) DATE Status Date
    11 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Prompt Table: HRS_BS_UNT_HR_I

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

    Prompt Table: HRS_COMPANY_I

    13 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    14 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: HRS_DEPT_I

    15 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

    Prompt Table: HRS_LOCATION_I

    16 HRS_LOCATION_ID Number(15,0) DECIMAL(15) NOT NULL Recruiting location area

    Prompt Table: HRS_JO_LOC_AR_I

    17 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

    18 OPENINGS_TARGET Number(3,0) SMALLINT NOT NULL Tracks number of target openings
    19 HRS_OPENINGS_AVLBL Number(3,0) SMALLINT NOT NULL Tracks number of available openings
    20 ORIGINATOR_ID Character(11) VARCHAR2(11) NOT NULL Originator's ID

    Prompt Table: HRS_ACT_PERS_I

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

    Prompt Table: HRS_ACT_PERS_I

    22 RECRUITER_ID Character(11) VARCHAR2(11) NOT NULL Recruiter ID

    Prompt Table: HRS_ACT_PERS_I

    23 HRS_CONTACT_ID Number(15,0) DECIMAL(15) NOT NULL Contact Id

    Prompt Table: HRS_RO_CNTCT_I

    24 DESIRED_START_DT Date(10) DATE Desired Start Date
    25 PROJECTED_FILL_DT Date(10) DATE Projected Fill Date
    26 HRS_ROW_ADD_DTTM DateTime(26) TIMESTAMP Row Added DateTime
    27 HRS_ROW_ADD_OPRID Character(30) VARCHAR2(30) NOT NULL Row Added Operator Id
    28 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
    29 HRS_JOBCAT_ID Character(4) VARCHAR2(4) NOT NULL Job Categoriation Id
    30 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    31 DESCR_LOCATION Character(30) VARCHAR2(30) NOT NULL Description
    32 DESCR_DEPT Character(30) VARCHAR2(30) NOT NULL Description