HRS_SA_JO_OC_LN

(SQL View)
 Index Back

Lang view for HRS_SA_JO_OC_I


SELECT JO.HRS_JOB_OPENING_ID , JO.OPRID , JOLNG.LANGUAGE_CD , JOLNG.HRS_PRM_PST_TITLE , COALESCE(( SELECT R.NAME_DISPLAY FROM PS_NAMES_LNG R WHERE R.EMPLID = JO.RECRUITER_ID AND R.LANGUAGE_CD = JOLNG.LANGUAGE_CD AND R.NAME_TYPE = 'PRI' AND R.EFFDT = ( SELECT MAX(R1.EFFDT) FROM PS_NAMES_LNG R1 WHERE R1.EMPLID = R.EMPLID AND R1.NAME_TYPE = R.NAME_TYPE AND R1.LANGUAGE_CD = R.LANGUAGE_CD AND R1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT R.NAME_DISPLAY FROM PS_NAMES_LNG R WHERE R.EMPLID = JO.MANAGER_ID AND R.LANGUAGE_CD = JOLNG.LANGUAGE_CD AND R.NAME_TYPE = 'PRI' AND R.EFFDT = ( SELECT MAX(R1.EFFDT) FROM PS_NAMES_LNG R1 WHERE R1.EMPLID = R.EMPLID AND R1.NAME_TYPE = R.NAME_TYPE AND R1.LANGUAGE_CD = R.LANGUAGE_CD AND R1.EFFDT <= %CurrentDateIn)),' ') , 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 <= %CurrentDateIn)),' ') , COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JODT.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 JC.DESCR FROM PS_JOBCODE_LANG JC WHERE JC.SETID = JODT.SETID_DEPT AND JC.JOBCODE = JO.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 <= %CurrentDateIn)),' ') , COALESCE(( SELECT L.DESCR FROM PS_HRS_LOC_LNG L WHERE L.HRS_LOCATION_ID = JODT.HRS_PRM_LOC_AREA AND L.SETID = JODT.SETID_DEPT AND L.LANGUAGE_CD = JOLNG.LANGUAGE_CD),' ') , CASE WHEN (JO.OPEN_DT = JODT.OPEN_DT) THEN ( SELECT XLT.MESSAGE_TEXT FROM PSMSGCATLANG XLT WHERE XLT.MESSAGE_SET_NBR = 18176 AND XLT.MESSAGE_NBR = 6199 AND XLT.LANGUAGE_CD = JOLNG.LANGUAGE_CD) ELSE ( SELECT XLT.MESSAGE_TEXT FROM PSMSGCATLANG XLT WHERE XLT.MESSAGE_SET_NBR = 18176 AND XLT.MESSAGE_NBR = 6200 AND XLT.LANGUAGE_CD = JOLNG.LANGUAGE_CD) END , COALESCE(( SELECT POSN.DESCR FROM PS_POSN_DATA_LANG POSN WHERE JODT.HRS_PRM_POSITION = POSN.POSITION_NBR AND POSN.LANGUAGE_CD = JOLNG.LANGUAGE_CD AND POSN.EFFDT = ( SELECT MAX(POSN1.EFFDT) FROM PS_POSN_DATA_LANG POSN1 WHERE POSN.POSITION_NBR = POSN1.POSITION_NBR AND POSN.LANGUAGE_CD = POSN1.LANGUAGE_CD AND POSN1.EFFDT <= %CurrentDateIn) ), ' ') FROM PS_HRS_SA_JO_OC_I JO, PS_HRS_JO_LNG JOLNG, PS_HRS_OPEN_JO_VW JODT WHERE JO.HRS_JOB_OPENING_ID = JOLNG.HRS_JOB_OPENING_ID AND JO.HRS_JOB_OPENING_ID = JODT.HRS_JOB_OPENING_ID

  • Related Language Record for HRS_SA_JO_OC_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 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    3 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    4 HRS_PRM_PST_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
    5 NAME Character(50) VARCHAR2(50) NOT NULL Name
    6 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    7 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description
    8 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description
    9 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    10 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    11 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    12 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    13 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description