HRS_SA_JO_AGE_I

(SQL View)
 Index Back

View Displaying JO Age Band

This view displays the job openings according to a pre-defined age-band for each OPRID.

SELECT JOPN.HRS_JOB_OPENING_ID , OPR.OPRID , JOPN.HRS_PRM_PST_TITLE , JOPN.COMPANY , COALESCE(( SELECT CO.DESCR FROM PS_COMPANY_TBL CO WHERE CO.COMPANY = JOPN.COMPANY AND CO.EFFDT = ( SELECT MAX(CO1.EFFDT) FROM PS_COMPANY_TBL CO1 WHERE CO1.COMPANY = CO.COMPANY AND CO1.EFFDT <= %CurrentDateIn)), ' ') , JOPN.BUSINESS_UNIT , COALESCE(( SELECT BU.DESCR FROM PS_BUS_UNIT_TBL_HR BU WHERE BU.BUSINESS_UNIT = JOPN.BUSINESS_UNIT),' ') , JOPN.DEPTID , COALESCE(( SELECT D.DESCR FROM PS_DEPT_TBL D WHERE D.DEPTID = JOPN.DEPTID AND D.SETID = JOPN.SETID_DEPT AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_DEPT_TBL D1 WHERE D1.DEPTID = D.DEPTID AND D1.SETID = D.SETID AND D1.EFFDT <= %CurrentDateIn)),' ') , JOPN.JOB_FAMILY , COALESCE(( SELECT JO.DESCR FROM PS_JOB_FAMILY_TBL JO WHERE JO.JOB_FAMILY = JOPN.JOB_FAMILY AND JO.EFFDT = ( SELECT MAX(JO1.EFFDT) FROM PS_JOB_FAMILY_TBL JO1 WHERE JO1.JOB_FAMILY = JO.JOB_FAMILY AND JO1.EFFDT <= %CurrentDateIn)),' ') , JOPN.HRS_PRM_JOBCODE , COALESCE(( SELECT JC.DESCR FROM PS_JOBCODE_TBL JC WHERE JC.SETID = JOPN.SETID_DEPT AND JC.JOBCODE = JOPN.HRS_PRM_JOBCODE AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_TBL JC1 WHERE JC1.SETID = JC.SETID AND JC1.JOBCODE = JC.JOBCODE AND JC1.EFFDT <= %CurrentDateIn)),' ') , JOPN.RECRUITER_ID , COALESCE( ( SELECT R.NAME_DISPLAY FROM PS_NAMES R WHERE R.EMPLID = JOPN.RECRUITER_ID AND R.NAME_TYPE = 'PRI' AND R.EFFDT = ( SELECT MAX(R1.EFFDT) FROM PS_NAMES R1 WHERE R1.EMPLID = R.EMPLID AND R1.NAME_TYPE = R.NAME_TYPE AND R1.EFFDT <= %CurrentDateIn)),' ') , JOPN.MANAGER_ID , COALESCE( ( SELECT M.NAME_DISPLAY FROM PS_NAMES M WHERE M.EMPLID = JOPN.MANAGER_ID AND M.NAME_TYPE = 'PRI' AND M.EFFDT = ( SELECT MAX(M1.EFFDT) FROM PS_NAMES M1 WHERE M1.EMPLID = M.EMPLID AND M1.NAME_TYPE = M.NAME_TYPE AND M1.EFFDT <= %CurrentDateIn)) , ' ') , JOPN.HRS_PRM_LOC_AREA , COALESCE(( SELECT L.DESCR FROM PS_HRS_LOCATION L WHERE L.HRS_LOCATION_ID = JOPN.HRS_PRM_LOC_AREA AND L.SETID = JOPN.SETID_DEPT),' ') , JOPN.SETID_DEPT , JOPN.OPEN_DT , CASE WHEN (%DateDiff(JOPN.OPEN_DT, %CurrentDateIn) < 7) THEN '1. <7' ELSE CASE WHEN (%DateDiff(JOPN.OPEN_DT, %CurrentDateIn) BETWEEN 7 AND 15) THEN '2. 7-15' ELSE CASE WHEN (%DateDiff(JOPN.OPEN_DT, %CurrentDateIn) BETWEEN 15 AND 30) THEN '3. 15-30' ELSE CASE WHEN (%DateDiff(JOPN.OPEN_DT, %CurrentDateIn) BETWEEN 30 AND 45) THEN '4. 30-45' ELSE CASE WHEN (%DateDiff(JOPN.OPEN_DT, %CurrentDateIn) BETWEEN 45 AND 60) THEN '5. 45-60' ELSE CASE WHEN (%DateDiff(JOPN.OPEN_DT, %CurrentDateIn) BETWEEN 60 AND 90) THEN '6. 60-90' ELSE CASE WHEN (%DateDiff(JOPN.OPEN_DT, %CurrentDateIn) > 90) THEN '7. >90' END END END END END END END , JOPN.HRS_PRM_POSITION , COALESCE(( SELECT POS.DESCR FROM PS_POSITION_DATA POS WHERE POS.POSITION_NBR = JOPN.HRS_PRM_POSITION AND POS.EFFDT = ( SELECT MAX(EFFDT) FROM PS_POSITION_DATA WHERE POSITION_NBR = POS.POSITION_NBR AND EFFDT <= %CurrentDateIn) AND POS.EFF_STATUS = 'A'), ' ') , ( SELECT COUNT(DISTINCT HRS_PERSON_ID) FROM PS_HRS_RCMNT_I WHERE HRS_JOB_OPENING_ID = JOPN.HRS_JOB_OPENING_ID AND STATUS_CODE <> '112' AND STATUS_CODE NOT IN ( SELECT STATUS_CODE FROM PS_HRS_STS_TBL WHERE STATUS_AREA = '3' AND STATUS_PHASE = '9')), ( SELECT COUNT(*) FROM PS_HRS_RCMNT_I WHERE HRS_JOB_OPENING_ID = JOPN.HRS_JOB_OPENING_ID AND STATUS_CODE IN ( SELECT STATUS_CODE FROM PS_HRS_STS_TBL WHERE STATUS_AREA = '3' AND STATUS_PHASE = '1')), JOPN.HRS_JO_TYPE, COALESCE (( SELECT CAT.DESCR FROM PS_HRS_JOBCAT_USER CAT , PS_HRS_JO_JOB_CATG JOCAT WHERE CAT.OPRID = OPR.OPRID AND JOPN.HRS_JOB_OPENING_ID = JOCAT.HRS_JOB_OPENING_ID AND CAT.OPRID = JOCAT.OPRID AND CAT.HRS_JOBCAT_ID = JOCAT.HRS_JOBCAT_ID AND CAT.STATUS = 'A'), ' ') FROM PS_HRS_OPEN_JO_VW JOPN, PS_HRS_JO_TEAM TM, PS_HRS_STS_TBL B, PSOPRDEFN OPR WHERE TM.HRS_JOB_OPENING_ID = JOPN.HRS_JOB_OPENING_ID AND B.STATUS_CODE = JOPN.STATUS_CODE AND TM.HRS_JO_TM_TYPE = 'M' AND B.STATUS_AREA = '1' AND B.OPEN_STS_IND = 'Y' AND OPR.EMPLID = TM.EMPLID

  • Related Language Record: HRS_SA_JOAGE_LN

    • # 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_PRM_PST_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
    4 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
    5 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
    6 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    7 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description
    8 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    9 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    10 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
    11 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description
    12 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    13 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    14 RECRUITER_ID Character(11) VARCHAR2(11) NOT NULL Recruiter ID
    15 NAME Character(50) VARCHAR2(50) NOT NULL Name
    16 MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager ID
    17 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    18 HRS_PRM_LOC_AREA Number(15,0) DECIMAL(15) NOT NULL Primary recruiting location area
    19 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    20 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    21 OPEN_DT Date(10) DATE Created
    22 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    23 HRS_PRM_POSITION Character(8) VARCHAR2(8) NOT NULL Position Number
    24 HRS_DESCR90 Character(90) VARCHAR2(90) NOT NULL content item description
    25 HRS_TOTAL_APPS Number(5,0) INTEGER NOT NULL This field is used to track the total number of applicants that have applied to a job opening.
    26 HRS_NEW_APPL Number(5,0) INTEGER NOT NULL This field is used to track the number of applicants for a job opening that have not yet been reviewed.
    27 HRS_JO_TYPE Character(1) VARCHAR2(1) NOT NULL Job Opening Type
    P=Continuous Job Opening
    R=Standard Requisition
    28 HRS_DESCR Character(30) VARCHAR2(30) NOT NULL Description