HRS_JO_PSTBTH_I

(SQL View)
 Index Back

Ext and Int Job Openings

Job openings with postings that are available for external and internal applicants.

SELECT A.HRS_JOB_OPENING_ID ,B.HRS_JO_PST_SEQ ,C.HRS_JO_PST_TYPE ,D.HRS_SOURCE_ID ,D.HRS_SOURCE_TYPE ,B.POSTING_TITLE ,B.HRS_JO_PRM_PST_FLG ,C.HRS_JO_PST_OPN_DT ,C.HRS_JO_PST_CLS_DT ,C.HRS_JO_PST_RVW_DT ,C.HRS_PRM_PST_DST ,A.HRS_JO_TYPE ,A.HRS_PRM_JOBCODE ,A.JOB_FAMILY ,A.HRS_PRM_POSITION ,A.STATUS_CODE ,A.BUSINESS_UNIT ,A.COMPANY ,A.SETID_DEPT ,A.DEPTID ,A.HRS_PRM_LOCATION ,A.HRS_PRM_LOC_AREA ,A.HOT_JOB ,A.ORIGINATOR_ID ,A.MANAGER_ID ,A.RECRUITER_ID ,CASE WHEN (%DatePart(F.HRS_ROW_UPD_DTTM) >= C.HRS_JO_PST_OPN_DT) THEN %Dttm(%DatePart(F.HRS_ROW_UPD_DTTM), %TimePart(F.HRS_ROW_UPD_DTTM)) ELSE %Dttm(C.HRS_JO_PST_OPN_DT, %TimeNull) END FROM PS_HRS_JOB_OPENING A LEFT OUTER JOIN PS_HRS_JOBINDX_CTL F ON A.HRS_JOB_OPENING_ID = F.HRS_JOB_OPENING_ID AND F.HRS_JO_PST_SEQ = 0 , PS_HRS_JO_POSTING B , PS_HRS_JO_PST_DST C , PS_HRS_SOURCE D , PS_HRS_STS_TBL E WHERE A.HRS_JOB_OPENING_ID = B.HRS_JOB_OPENING_ID AND A.HRS_JOB_OPENING_ID = C.HRS_JOB_OPENING_ID AND B.HRS_JO_PST_SEQ = C.HRS_JO_PST_SEQ AND (C.HRS_JO_PST_TYPE = 'E' OR C.HRS_JO_PST_TYPE = 'I') AND (C.HRS_JO_PST_CLS_DT > %CurrentDateIn OR C.HRS_JO_PST_CLS_DT IS NULL) AND C.HRS_JO_PST_OPN_DT <= %CurrentDateIn AND C.HRS_JO_PST_OPN_DT IS NOT NULL AND C.HRS_SOURCE_ID = D.HRS_SOURCE_ID AND D.HRS_SOURCE_TYPE = '40' AND A.STATUS_CODE = E.STATUS_CODE AND E.OPEN_STS_IND = 'Y' AND E.STATUS_AREA = '1' AND D.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_HRS_SOURCE X WHERE X.HRS_SOURCE_ID = D.HRS_SOURCE_ID AND X.EFFDT <= %CurrentDateIn AND HRS_SOURCE_STATUS = '10') AND C.HRS_SEQ_NUM=( SELECT MAX(C1.HRS_SEQ_NUM) FROM PS_HRS_JO_PST_DST C1 WHERE C.HRS_JOB_OPENING_ID=C1.HRS_JOB_OPENING_ID AND C.HRS_JO_PST_SEQ=C1.HRS_JO_PST_SEQ AND C.HRS_JO_PST_TYPE=C1.HRS_JO_PST_TYPE AND C.HRS_SOURCE_ID=C1.HRS_SOURCE_ID AND (C1.HRS_JO_PST_CLS_DT > %CurrentDateIn OR C1.HRS_JO_PST_CLS_DT IS NULL) AND C1.HRS_JO_PST_OPN_DT <= %CurrentDateIn AND C1.HRS_JO_PST_OPN_DT IS NOT NULL )

  • Related Language Record: HRS_JO_PST_LN_I

  • Parent record: HRS_JO_PST_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

    Prompt Table: HRS_JO_I

    2 HRS_JO_PST_SEQ Number(3,0) SMALLINT NOT NULL Job opening posting sequence

    Prompt Table: HRS_JO_PST_I

    3 HRS_JO_PST_TYPE Character(1) VARCHAR2(1) NOT NULL Job Opening Posting Type
    C=Contingent Workforce
    E=External Posting
    I=Internal Posting
    L=Labor Office
    R=Recruitment
    4 HRS_SOURCE_ID Number(15,0) DECIMAL(15) NOT NULL Source ID
    5 HRS_SOURCE_TYPE Character(3) VARCHAR2(3) NOT NULL Recruiting Source Type
    10=Vendor
    20=Marketing
    30=Employee
    40=Company
    50=D-List
    6 POSTING_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
    7 HRS_JO_PRM_PST_FLG Character(1) VARCHAR2(1) NOT NULL Primary posting title flag

    Y/N Table Edit

    Default Value: N

    8 OPEN_DT Date(10) DATE Created
    9 CLOSE_DT Date(10) DATE Date Closed
    10 HRS_JO_PST_RVW_DT Date(10) DATE Job opening posting next review date
    11 HRS_PRM_PST_DST Character(1) VARCHAR2(1) NOT NULL Primary Posting Destination
    N=No
    Y=Yes

    Default Value: N

    12 HRS_JO_TYPE Character(1) VARCHAR2(1) NOT NULL Job Opening Type
    P=Continuous Job Opening
    R=Standard Requisition
    13 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

    Prompt Table: HRS_JOBCODE_I

    14 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
    15 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

    Prompt Table: HRS_POS_DTA_C_I

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

    Prompt Table: HRS_STS_JO_VW

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

    Prompt Table: HRS_BS_UNT_HR_I

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

    Prompt Table: HRS_COMPANY_I

    19 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    20 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: HRS_DEPT_I

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

    Prompt Table: HRS_LOCATION_I

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

    Prompt Table: HRS_JO_LOC_AR_I

    23 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

    24 ORIGINATOR_ID Character(11) VARCHAR2(11) NOT NULL Originator's ID

    Prompt Table: HRS_ACT_PERS_I

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

    Prompt Table: HRS_ACT_PERS_I

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

    Prompt Table: HRS_ACT_PERS_I

    27 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.