HRS_PG_TTF_I2

(SQL View)
 Index Back

Time to Fill View for JOs

This view displays the time taken to fill for all closed job openings.

SELECT JOB.HRS_JOB_OPENING_ID , OPR.OPRID , JOB.HRS_PRM_PST_TITLE , TM.EMPLID , JOB.BUSINESS_UNIT , BU.DESCR , JOB.JOB_FAMILY , ( SELECT JF.DESCR FROM PS_JOB_FAMILY_TBL JF WHERE JOB.JOB_FAMILY = JF.JOB_FAMILY AND JF.EFFDT = ( SELECT MAX(JF1.EFFDT) FROM PS_JOB_FAMILY_TBL JF1 WHERE JF1.JOB_FAMILY = JF.JOB_FAMILY AND JF1.EFFDT <= JOB.STATUS_DT)) , JOB.RECRUITER_ID , ( SELECT R.NAME_DISPLAY FROM PS_NAMES R WHERE R.EMPLID = JOB.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 <= JOB.STATUS_DT)) , JOB.MANAGER_ID , ( SELECT M.NAME_DISPLAY FROM PS_NAMES M WHERE M.EMPLID = JOB.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 <= JOB.STATUS_DT)) , JOB.HRS_PRM_JOBCODE , ( SELECT JC.DESCR FROM PS_JOBCODE_TBL JC WHERE JC.SETID = JOB.SETID_DEPT AND JC.JOBCODE = JOB.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 <= JOB.STATUS_DT)) , JOB.DEPTID , ( SELECT D.DESCR FROM PS_DEPT_TBL D WHERE D.DEPTID = JOB.DEPTID AND D.SETID = JOB.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 <= JOB.STATUS_DT)) , JOB.HRS_PRM_LOCATION , ( SELECT L.DESCR FROM PS_LOCATION_TBL L WHERE L.LOCATION = JOB.HRS_PRM_LOCATION AND L.SETID = JOB.SETID_DEPT AND L.EFFDT = ( SELECT MAX(L1.EFFDT) FROM PS_LOCATION_TBL L1 WHERE L1.LOCATION = L.LOCATION AND L1.SETID = L.SETID AND L1.EFFDT <= JOB.STATUS_DT)) , ( SELECT LC.COUNTRY FROM PS_LOCATION_TBL LC WHERE LC.SETID = JOB.SETID_DEPT AND LC.LOCATION = JOB.HRS_PRM_LOCATION AND LC.EFFDT = ( SELECT MAX(LC1.EFFDT) FROM PS_LOCATION_TBL LC1 WHERE LC1.LOCATION = LC.LOCATION AND LC1.SETID = LC.SETID AND LC1.EFFDT <= JOB.STATUS_DT)) , ( SELECT CN.DESCR FROM PS_COUNTRY_TBL CN WHERE CN.COUNTRY = ( SELECT CN1.COUNTRY FROM PS_LOCATION_TBL CN1 WHERE CN1.SETID = JOB.SETID_DEPT AND CN1.LOCATION = JOB.HRS_PRM_LOCATION AND CN1.EFFDT = ( SELECT MAX(CN2.EFFDT) FROM PS_LOCATION_TBL CN2 WHERE CN2.LOCATION = CN1.LOCATION AND CN2.SETID = CN1.SETID AND CN2.EFFDT <= JOB.STATUS_DT))) , JOB.STATUS_DT, 0, CASE WHEN ( SELECT B.HRS_JO_PST_OPN_DT FROM PS_HRS_JO_PST_DST B WHERE JOB.HRS_JOB_OPENING_ID = B.HRS_JOB_OPENING_ID AND B.HRS_JO_PST_SEQ = 0 AND B.HRS_SEQ_NUM = 0 AND (B.HRS_JO_PST_OPN_DT IS NULL OR B.HRS_JO_PST_OPN_DT = ( SELECT MIN(B1.HRS_JO_PST_OPN_DT) FROM PS_HRS_JO_PST_DST B1 WHERE JOB.HRS_JOB_OPENING_ID = B1.HRS_JOB_OPENING_ID AND B.HRS_JO_PST_SEQ = B1.HRS_JO_PST_SEQ))) IS NOT NULL THEN %Sql(FUNCLIB_HRS_DATE_DIFF,(SELECT B.HRS_JO_PST_OPN_DT FROM PS_HRS_JO_PST_DST B WHERE JOB.HRS_JOB_OPENING_ID = B.HRS_JOB_OPENING_ID AND B.HRS_JO_PST_SEQ = 0 AND B.HRS_SEQ_NUM = 0 AND (B.HRS_JO_PST_OPN_DT IS NULL OR B.HRS_JO_PST_OPN_DT = ( SELECT MIN(B1.HRS_JO_PST_OPN_DT) FROM PS_HRS_JO_PST_DST B1 WHERE JOB.HRS_JOB_OPENING_ID = B1.HRS_JOB_OPENING_ID AND B.HRS_JO_PST_SEQ = B1.HRS_JO_PST_SEQ))), (JOB.CLOSE_DT)) ELSE %Sql(FUNCLIB_HRS_DATE_DIFF, JOB.OPEN_DT, JOB.CLOSE_DT) END , %Sql(FUNCLIB_HRS_DATE_YEAR,JOB.STATUS_DT)%Concat '-' %Concat %Sql(FUNCLIB_HRS_DATE_MONTH,JOB.STATUS_DT) , %DateNull , %DateNull FROM PS_HRS_JO_TEAM TM , PS_BUS_UNIT_TBL_HR BU, PS_HRS_JOB_OPENING JOB, PS_HRS_PE_OPR_FLCT FLCT, PSOPRDEFN OPR WHERE JOB.HRS_JOB_OPENING_ID = TM.HRS_JOB_OPENING_ID AND JOB.STATUS_CODE = ( SELECT STS.STATUS_CODE FROM PS_HRS_STS_TBL STS WHERE STS.STATUS_AREA = '1' AND STS.COMPLETE_STS_IND = 'Y') AND JOB.HRS_OPENING_LIMIT = 'L' AND JOB.HRS_JO_TYPE = 'R' AND TM.HRS_JO_TM_TYPE IN ('M', 'R') AND JOB.CLOSE_DT IS NOT NULL AND BU.BUSINESS_UNIT = JOB.BUSINESS_UNIT AND JOB.STATUS_DT BETWEEN FLCT.FROM_DATE AND FLCT.TO_DATE AND OPR.OPRID = FLCT.OPRID AND TM.EMPLID = OPR.EMPLID

  • Related Language Record: HRS_PG_TTF2_LNG

    • # 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 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    5 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    6 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description
    7 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
    8 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description
    9 RECRUITER_ID Character(11) VARCHAR2(11) NOT NULL Recruiter ID
    10 NAME_FORMAL Character(60) VARCHAR2(60) NOT NULL Formal Name - name formatted for Formal Display by COuntry. Normally used in Correspondence.
    11 MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager ID
    12 NAME Character(50) VARCHAR2(50) NOT NULL Name
    13 HRS_PRM_JOBCODE Character(6) VARCHAR2(6) NOT NULL Primary Job Code of Job Opening.
    14 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    15 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    16 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    17 HRS_PRM_LOCATION Character(10) VARCHAR2(10) NOT NULL Primary Recruiting Location of Job Opening,
    18 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    19 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    20 COUNTRY_DESCR Character(30) VARCHAR2(30) NOT NULL Country Description
    21 STATUS_DT Date(10) DATE Status Date
    22 HRS_DAYS Signed Number(6,0) DECIMAL(5) NOT NULL Days
    23 HRS_DAYS_TO_FILL Number(5,0) INTEGER NOT NULL Days to Fill
    24 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    25 FROM_DT Date(10) DATE From Date
    26 TO_DT Date(10) DATE To Date