HRS_SCHAPPL_JVW

(SQL View)
 Index Back

Search Applications JO View

Returns search results for the Search Applications component and joins with Job Opening table. The fields on this ecord must be kept in synch with the following records: HRS_SCHAPPL_VW HRS_SCHAPPL_MVW In addition, HRS_SUBMITTED_DTTM should be the last field on the record for proper sorting at runtime.

SELECT DISTINCT RCM.HRS_PERSON_ID , RCM.HRS_RCMNT_ID , RCM.HRS_PROFILE_SEQ , RCM.HRS_JOB_OPENING_ID , OPR.OPRID , NM.NAME_DISPLAY , NM.FIRST_NAME_SRCH , NM.LAST_NAME_SRCH , RCM.STATUS_CODE , RCM.HRS_APP_INTEREST , APP.APP_PER_STATUS , APP.STATUS_CODE , JOB.STATUS_CODE , JOB.STATUS_DT , JOB.HRS_JO_TYPE , JOB.RECRUITER_ID , JOB.MANAGER_ID , JOB.BUSINESS_UNIT , JOB.DEPTID , JOB.HRS_PRM_POSITION , JOB.HRS_PRM_PST_TITLE , JOB.OPEN_DT , JOB.ORIGINATOR_ID , CATG.HRS_JOBCAT_ID , JOB.HOT_JOB , JOB.HRS_CONTACT_ID , APPL.HRS_RESUME_ID , APPL.HRS_SUBMITTED_BY , APPL.HRS_SUBMITTED_DTTM FROM PS_HRS_APPLICANT APP , PS_HRS_RCMNT RCM , PS_HRS_JOB_OPENING JOB , PS_HRS_JO_JOB_CATG CATG , PSOPRDEFN OPR , PS_HRS_APP_PROFILE APPL , PS_HRS_APP_NAMES NM WHERE RCM.HRS_PERSON_ID = APP.HRS_PERSON_ID AND JOB.HRS_JOB_OPENING_ID = RCM.HRS_JOB_OPENING_ID AND APPL.HRS_PERSON_ID = RCM.HRS_PERSON_ID AND APPL.HRS_PROFILE_SEQ = RCM.HRS_PROFILE_SEQ AND NM.HRS_PERSON_ID = APP.HRS_PERSON_ID AND CATG.HRS_JOB_OPENING_ID = JOB.HRS_JOB_OPENING_ID AND CATG.OPRID = OPR.OPRID AND NM.NAME_TYPE = 'PRI' AND RCM.HRS_LOGICAL_DELETE = 'N' AND JOB.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 <> ' ' UNION SELECT JO.HRS_JOB_OPENING_ID FROM PS_HRS_SJT_JO JO ,PS_SJT_CLASS_ALL CLS , PS_SJT_OPR_CLS RSC WHERE RSC.CLASSID = CLS.CLASSID AND RSC.OPRID = OPR.OPRID AND CLS.SCRTY_SET_CD = 'RSOPN' AND CLS.SCRTY_TYPE_CD = JO.SCRTY_TYPE_CD AND CLS.SCRTY_KEY1 = JO.SCRTY_KEY1 AND CLS.SCRTY_KEY2 = JO.SCRTY_KEY2 AND CLS.SCRTY_KEY3 = JO.SCRTY_KEY3 AND JO.EMPLID = ' ' UNION SELECT JO.HRS_JOB_OPENING_ID FROM PS_HRS_SJT_JO JO ,PS_SJT_CLASS_ALL CLS , PS_SJT_OPR_CLS RSC WHERE RSC.CLASSID = CLS.CLASSID AND RSC.OPRID = OPR.OPRID AND CLS.SCRTY_SET_CD = 'PPLJOB' AND CLS.SCRTY_TYPE_CD = '001' AND CLS.SCRTY_KEY1 = JO.SCRTY_KEY1 AND CLS.SCRTY_KEY2 = JO.SCRTY_KEY2 AND CLS.SCRTY_KEY3 = JO.SCRTY_KEY3 AND JO.EMPLID = ' ' ) UNION SELECT DISTINCT RCM.HRS_PERSON_ID , RCM.HRS_RCMNT_ID , RCM.HRS_PROFILE_SEQ , RCM.HRS_JOB_OPENING_ID , OPR.OPRID , NM.NAME_DISPLAY , NM.FIRST_NAME_SRCH , NM.LAST_NAME_SRCH , RCM.STATUS_CODE , RCM.HRS_APP_INTEREST , APP.APP_PER_STATUS , APP.STATUS_CODE , JOB.STATUS_CODE , JOB.STATUS_DT , JOB.HRS_JO_TYPE , JOB.RECRUITER_ID , JOB.MANAGER_ID , JOB.BUSINESS_UNIT , JOB.DEPTID , JOB.HRS_PRM_POSITION , JOB.HRS_PRM_PST_TITLE , JOB.OPEN_DT , JOB.ORIGINATOR_ID , 'NO' , JOB.HOT_JOB , JOB.HRS_CONTACT_ID , APPL.HRS_RESUME_ID , APPL.HRS_SUBMITTED_BY , APPL.HRS_SUBMITTED_DTTM FROM PS_HRS_APPLICANT APP , PS_HRS_RCMNT RCM , PS_HRS_JOB_OPENING JOB , PSOPRDEFN OPR , PS_HRS_APP_PROFILE APPL , PS_HRS_APP_NAMES NM WHERE RCM.HRS_PERSON_ID = APP.HRS_PERSON_ID AND JOB.HRS_JOB_OPENING_ID = RCM.HRS_JOB_OPENING_ID AND APPL.HRS_PERSON_ID = RCM.HRS_PERSON_ID AND APPL.HRS_PROFILE_SEQ = RCM.HRS_PROFILE_SEQ AND NM.HRS_PERSON_ID = APP.HRS_PERSON_ID AND NM.NAME_TYPE = 'PRI' AND RCM.HRS_LOGICAL_DELETE = 'N' AND JOB.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 <> ' ' UNION ALL SELECT JO.HRS_JOB_OPENING_ID FROM PS_HRS_SJT_JO JO ,PS_SJT_CLASS_ALL CLS , PS_SJT_OPR_CLS RSC WHERE RSC.CLASSID = CLS.CLASSID AND RSC.OPRID = OPR.OPRID AND CLS.SCRTY_SET_CD = 'RSOPN' AND CLS.SCRTY_TYPE_CD = JO.SCRTY_TYPE_CD AND CLS.SCRTY_KEY1 = JO.SCRTY_KEY1 AND CLS.SCRTY_KEY2 = JO.SCRTY_KEY2 AND CLS.SCRTY_KEY3 = JO.SCRTY_KEY3 AND JO.EMPLID = ' ' UNION ALL SELECT JO.HRS_JOB_OPENING_ID FROM PS_HRS_SJT_JO JO ,PS_SJT_CLASS_ALL CLS , PS_SJT_OPR_CLS RSC WHERE RSC.CLASSID = CLS.CLASSID AND RSC.OPRID = OPR.OPRID AND CLS.SCRTY_SET_CD = 'PPLJOB' AND CLS.SCRTY_TYPE_CD = '001' 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 NOT EXISTS( SELECT 'X' FROM PS_HRS_JO_JOB_CATG CATG WHERE CATG.HRS_JOB_OPENING_ID = JOB.HRS_JOB_OPENING_ID AND CATG.OPRID = OPR.OPRID)

  • Related Language Record: HRS_APPLRCMTLV1

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 HRS_PERSON_ID Number(15,0) DECIMAL(15) NOT NULL Person Id

    Prompt Table: HRS_APPLICANT_I

    2 HRS_RCMNT_ID Number(15,0) DECIMAL(15) NOT NULL App Tracking Recruitment ID

    Prompt Table: HRS_RCMNT_I

    3 HRS_PROFILE_SEQ Number(3,0) SMALLINT NOT NULL Profile sequece

    Prompt Table: HRS_APP_PROF_I

    4 HRS_JOB_OPENING_ID Number(15,0) DECIMAL(15) NOT NULL Job Opening Clone

    Prompt Table: HRS_JO_OPEN_I

    5 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    6 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    7 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
    8 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    9 STATUS_CODE Character(3) VARCHAR2(3) NOT NULL Identifies a status.

    Prompt Table: HRS_STS_REC_I

    10 HRS_APP_INTEREST Character(1) VARCHAR2(1) NOT NULL Applicant Interest
    1=Low
    2=Medium
    3=High
    11 APP_PER_STATUS Character(1) VARCHAR2(1) NOT NULL Identifies the type of applicant - External Applicant, Employee or Non-Employee
    A=External Applicant
    E=Employee
    N=Non-Employee
    12 HRS_STATUS_CODE Character(3) VARCHAR2(3) NOT NULL Applicant Status

    Prompt Table: HRS_STS_APP_I

    13 JOB_STATUS Character(3) VARCHAR2(3) NOT NULL Job Status

    Prompt Table: HRS_STS_JO_I

    14 STATUS_DT Date(10) DATE Status Date
    15 HRS_JO_TYPE Character(1) VARCHAR2(1) NOT NULL Job Opening Type
    P=Continuous Job Opening
    R=Standard Requisition
    16 RECRUITER_ID Character(11) VARCHAR2(11) NOT NULL Recruiter ID
    17 MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager ID
    18 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    19 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: HRS_DEPT_I

    20 HRS_PRM_POSITION Character(8) VARCHAR2(8) NOT NULL Position Number

    Prompt Table: %EDITTABLE2

    21 HRS_PRM_PST_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
    22 OPEN_DT Date(10) DATE Created
    23 ORIGINATOR_ID Character(11) VARCHAR2(11) NOT NULL Originator's ID
    24 HRS_JOBCAT_ID Character(4) VARCHAR2(4) NOT NULL Job Categoriation Id
    25 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
    26 HRS_CONTACT_ID Number(15,0) DECIMAL(15) NOT NULL Contact Id
    27 HRS_RESUME_ID Number(15,0) DECIMAL(15) NOT NULL Resume id

    Prompt Table: HRS_APP_RES

    28 HRS_SUBMITTED_BY Character(15) VARCHAR2(15) NOT NULL submitted by
    29 HRS_SUBMITTED_DTTM DateTime(26) TIMESTAMP Submitted Date Time