HRS_JO_OPEN_I

(SQL View)
 Index Back

Open Job Openings Interface

Interface for job openings that are in an open status for current user.

SELECT A.HRS_JOB_OPENING_ID , OPR.OPRID , A.HRS_JO_TYPE , A.HRS_PRM_PST_TITLE , A.HRS_PRM_JOBCODE , A.HRS_PRM_POSITION , A.STATUS_CODE , A.OPEN_DT , A.BUSINESS_UNIT , A.COMPANY , A.DEPTID , A.HRS_PRM_LOCATION , A.HRS_PRM_LOC_AREA , A.HOT_JOB , A.ORIGINATOR_ID , A.MANAGER_ID , A.RECRUITER_ID , A.HRS_ROW_ADD_DTTM , A.HRS_ROW_ADD_OPRID , A.OPENINGS_TARGET , A.OPENINGS_CNT FROM PS_HRS_JOB_OPENING A , PSOPRDEFN OPR , PS_HRS_STS_TBL C WHERE C.STATUS_CODE = A.STATUS_CODE AND C.STATUS_AREA = '1' AND C.OPEN_STS_IND = 'Y' AND (A.HRS_JOB_OPENING_ID IN ( SELECT JO.HRS_JOB_OPENING_ID FROM PS_HRS_SJT_JO JO WHERE JO.EMPLID = OPR.EMPLID AND JO.EMPLID <> ' ' AND JO.HRS_JOB_OPENING_ID = A.HRS_JOB_OPENING_ID) OR A.HRS_JOB_OPENING_ID IN ( SELECT JO.HRS_JOB_OPENING_ID FROM PS_HRS_SJT_JO JO ,PS_SJT_CLASS_ALL CLS , PS_SJT_OPR_CLS RSC WHERE JO.HRS_JOB_OPENING_ID = A.HRS_JOB_OPENING_ID AND RSC.CLASSID = CLS.CLASSID AND RSC.OPRID = OPR.OPRID AND ((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 CLS.SCRTY_KEY1 = JO.SCRTY_KEY1 AND CLS.SCRTY_KEY2 = JO.SCRTY_KEY2 AND CLS.SCRTY_KEY3 = JO.SCRTY_KEY3 AND JO.EMPLID = ' ' AND EXISTS( SELECT 'Y' FROM PS_HRS_ROLE_DEFN A ,PSROLEUSER R WHERE A.EFF_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(T.EFFDT) FROM PS_HRS_ROLE_DEFN T WHERE T.HRS_ROLE_TYPE = A.HRS_ROLE_TYPE AND T.EFFDT <=%CurrentDateIn) AND A.HRS_APPCNT_SEC = 'N' AND A.ROLENAME = R.ROLENAME AND OPR.OPRID=R.ROLEUSER)))

  • Related Language Record: HRS_JO_SEC_LN_I

    • # 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 OPEN_DT Date(10) DATE Created
    9 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Prompt Table: HRS_BS_UNT_HR_I

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

    Prompt Table: HRS_COMPANY_I

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

    Prompt Table: HRS_DEPT_I

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

    Prompt Table: HRS_LOCATION_I

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

    Prompt Table: HRS_JO_LOC_AR_I

    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 ORIGINATOR_ID Character(11) VARCHAR2(11) NOT NULL Originator's ID

    Prompt Table: HRS_ACT_PERS_I

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

    Prompt Table: HRS_ACT_PERS_I

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

    Prompt Table: HRS_ACT_PERS_I

    18 HRS_ROW_ADD_DTTM DateTime(26) TIMESTAMP Row Added DateTime
    19 HRS_ROW_ADD_OPRID Character(30) VARCHAR2(30) NOT NULL Row Added Operator Id
    20 OPENINGS_TARGET Number(3,0) SMALLINT NOT NULL Tracks number of target openings
    21 OPENINGS_CNT Number(3,0) SMALLINT NOT NULL Tracks number of openings