HRS_PG_TTF_LNG

(SQL View)
 Index Back

Time to Fill View for JOs

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

SELECT TTF.HRS_JOB_OPENING_ID , JOLNG.LANGUAGE_CD , JOLNG.HRS_PRM_PST_TITLE , COALESCE(( SELECT BU.DESCR FROM PS_BUS_UNIT_HR_LNG BU WHERE JO.BUSINESS_UNIT = BU.BUSINESS_UNIT AND BU.LANGUAGE_CD = JOLNG.LANGUAGE_CD),' ') , COALESCE(( SELECT JF.DESCR FROM PS_JOB_FAMILY_LNG JF WHERE JO.JOB_FAMILY = JF.JOB_FAMILY AND JF.LANGUAGE_CD = JOLNG.LANGUAGE_CD AND JF.EFFDT = ( SELECT MAX(JF1.EFFDT) FROM PS_JOB_FAMILY_LNG JF1 WHERE JF1.JOB_FAMILY = JF.JOB_FAMILY AND JF.LANGUAGE_CD = JF1.LANGUAGE_CD AND JF1.EFFDT <= TTF.STATUS_DT)),' ') , COALESCE(( SELECT JC.DESCR FROM PS_JOBCODE_LANG JC WHERE JC.SETID = JO.SETID_DEPT AND JC.JOBCODE = JO.HRS_PRM_JOBCODE AND JC.LANGUAGE_CD = JOLNG.LANGUAGE_CD AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_LANG JC1 WHERE JC1.SETID = JC.SETID AND JC1.JOBCODE = JC.JOBCODE AND JC1.LANGUAGE_CD = JC.LANGUAGE_CD AND JC1.EFFDT <= TTF.STATUS_DT)),' ') , COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JO.SETID_DEPT = DEPT.SETID AND JO.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = JOLNG.LANGUAGE_CD AND DEPT.EFFDT = ( SELECT MAX(DEPT1.EFFDT) FROM PS_DEPT_TBL_LANG DEPT1 WHERE DEPT.SETID = DEPT1.SETID AND DEPT.LANGUAGE_CD = DEPT1.LANGUAGE_CD AND DEPT.DEPTID = DEPT1.DEPTID AND DEPT1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JO.SETID_DEPT = LOCN.SETID AND JO.HRS_PRM_LOCATION = LOCN.LOCATION AND LOCN.EFFDT = ( SELECT MAX(LOCN1.EFFDT) FROM PS_LOCATION_LANG LOCN1 WHERE LOCN.SETID = LOCN1.SETID AND LOCN.LOCATION = LOCN1.LOCATION AND LOCN.EFFDT <= %CurrentDateIn) AND LOCN.LANGUAGE_CD = JOLNG.LANGUAGE_CD),' ') , COALESCE(( SELECT CN.DESCR FROM PS_COUNTRYTBL_LANG CN WHERE CN.COUNTRY = ( SELECT CN1.COUNTRY FROM PS_LOCATION_TBL CN1 WHERE CN1.SETID = JO.SETID_DEPT AND CN1.LOCATION = JO.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 <= TTF.STATUS_DT)) AND CN.LANGUAGE_CD = JOLNG.LANGUAGE_CD),' ') FROM PS_HRS_PG_TTF_I TTF , PS_HRS_JOB_OPENING JO , PS_HRS_JO_LNG JOLNG WHERE TTF.HRS_JOB_OPENING_ID = JO.HRS_JOB_OPENING_ID AND JO.HRS_JOB_OPENING_ID = JOLNG.HRS_JOB_OPENING_ID

  • Related Language Record for HRS_PG_TTF_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 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    3 HRS_PRM_PST_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
    4 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description
    5 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description
    6 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    7 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    8 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    9 COUNTRY_DESCR Character(30) VARCHAR2(30) NOT NULL Country Description