HR_JOB_RTI_DATL

(SQL View)
 Index Back

Job Data Lng Record for Search


SELECT A.EMPLID , A.EMPL_RCD , LNG.LANGUAGE_CD , %Coalesce((SELECT COALESCE((SELECT BL.DESCR FROM PS_HR_SRCH_POS_LNG BL WHERE B.POSITION_NBR = BL.POSITION_NBR AND BL.LANGUAGE_CD = LNG.LANGUAGE_CD), B.DESCR) FROM PS_POSITION_DATA B WHERE B.POSITION_NBR = A.POSITION_NBR AND %EffdtCheck(POSITION_DATA B_ED, B, %CurrentDateIn)), ' ') , %Coalesce((SELECT CL.DESCR FROM PS_LOCATION_LANG CL WHERE C.SETID = CL.SETID AND C.LOCATION = CL.LOCATION AND CL.LANGUAGE_CD = LNG.LANGUAGE_CD AND C.EFFDT = CL.EFFDT AND A.LOCATION = CL.LOCATION), C.DESCR, ' ') , %Coalesce((SELECT EL.DESCR FROM PS_DEPT_TBL_LANG EL WHERE E.SETID = EL.SETID AND E.DEPTID = EL.DEPTID AND EL.LANGUAGE_CD = LNG.LANGUAGE_CD AND E.EFFDT = EL.EFFDT AND A.DEPTID = EL.DEPTID), E.DESCR, ' ') , %Coalesce((SELECT FL.DESCR FROM PS_JOBCODE_LANG FL WHERE F.SETID = FL.SETID AND F.JOBCODE = FL.JOBCODE AND FL.LANGUAGE_CD = LNG.LANGUAGE_CD AND F.EFFDT = FL.EFFDT AND A.JOBCODE = FL.JOBCODE), F.DESCR, ' ') , %Coalesce((SELECT AL.DESCR50 FROM PS_HR_JOB_RTI_TMPL AL WHERE A.EMPLID = AL.EMPLID AND A.EMPL_RCD = AL.EMPL_RCD AND A.EFFDT= AL.EFFDT AND AL.LANGUAGE_CD = LNG.LANGUAGE_CD), A.DESCR50, ' ') , G.NAME , G.LAST_NAME , G.FIRST_NAME , G.MIDDLE_NAME , G.SECOND_LAST_NAME , G.PARTNER_LAST_NAME , G.PREF_LAST_NAME , G.PREF_MIDDLE_NAME , G.PREF_SEC_LAST_NAME , G.NAME_AC , G.NAME_DISPLAY , G.NAME_FORMAL , G.NAME_FORMAL_PSF , %Coalesce((SELECT IL.HR_SRCH_PYGP_DESCR FROM PS_HR_JOB_RTI_TMPL IL WHERE A.EMPLID = IL.EMPLID AND A.EMPL_RCD = IL.EMPL_RCD AND A.EFFDT = IL.EFFDT AND IL.LANGUAGE_CD = LNG.LANGUAGE_CD), A.HR_SRCH_PYGP_DESCR, ' ') , %Coalesce((SELECT ML.HR_SRCH_GPPY_DESCR FROM PS_HR_JOB_RTI_TMPL ML WHERE A.EMPLID = ML.EMPLID AND A.EMPL_RCD = ML.EMPL_RCD AND A.EFFDT = ML.EFFDT AND ML.LANGUAGE_CD = LNG.LANGUAGE_CD), A.HR_SRCH_GPPY_DESCR, ' ') , %Coalesce((SELECT LL.HR_SRCH_CMP_DESCR FROM PS_HR_JOB_RTI_TMPL LL WHERE A.EMPLID = LL.EMPLID AND A.EMPL_RCD = LL.EMPL_RCD AND A.EFFDT = LL.EFFDT AND LL.LANGUAGE_CD = LNG.LANGUAGE_CD), A.HR_SRCH_CMP_DESCR, ' ') , G.LAST_NAME_SRCH , G.FIRST_NAME_SRCH , G.MIDDLE_NAME_SRCH , G.SECOND_LAST_SRCH , G.PARTN_LAST_NM_SRCH , G.PREF_LAST_NM_SRCH , G.PREF_FIRST_NM_SRCH , G.PREF_MIDL_NM_SRCH , G.PREF_LAST_SEC_SRCH , G.NAME_DISPLAY_SRCH , G.NAME_FORMAL_SRCH , G.PREF_FIRST_NAME , %Coalesce((SELECT SL.SUPERVISOR_NAME FROM PS_HR_JOB_RTI_TMPL SL WHERE A.EMPLID = SL.EMPLID AND A.EMPL_RCD = SL.EMPL_RCD AND A.EFFDT = SL.EFFDT AND SL.LANGUAGE_CD = LNG.LANGUAGE_CD), A.SUPERVISOR_NAME, ' ') , %Coalesce((SELECT RL.REPORTS_TO_DESCR FROM PS_HR_JOB_RTI_TMPL RL WHERE A.EMPLID = RL.EMPLID AND A.EMPL_RCD = RL.EMPL_RCD AND A.EFFDT = RL.EFFDT AND RL.LANGUAGE_CD = LNG.LANGUAGE_CD), A.REPORTS_TO_DESCR, ' ') , %Coalesce((SELECT BL1.BUSINESS_TITLE FROM PS_HR_JOB_RTI_TMPL BL1 WHERE A.EMPLID = BL1.EMPLID AND A.EMPL_RCD = BL1.EMPL_RCD AND A.EFFDT = BL1.EFFDT AND BL1.LANGUAGE_CD = LNG.LANGUAGE_CD), A.BUSINESS_TITLE, ' ') , %Coalesce((SELECT BL2.BUSINESS_DESCR FROM PS_HR_JOB_RTI_TMPL BL2 WHERE A.EMPLID = BL2.EMPLID AND A.EMPL_RCD = BL2.EMPL_RCD AND A.EFFDT = BL2.EFFDT AND BL2.LANGUAGE_CD = LNG.LANGUAGE_CD), A.BUSINESS_DESCR, ' ') FROM PS_HR_JOB_RTI_TMP1 A , PS_LOCATION_TBL C , PS_DEPT_TBL E , PS_JOBCODE_TBL F , PS_HR_NAME_RTI_LVW G , PSLANGUAGES LNG WHERE C.SETID = A.SETID_LOCATION AND C.LOCATION = A.LOCATION AND %EffdtCheck(LOCATION_TBL C_ED, C, %CurrentDateIn) AND E.SETID = A.SETID_DEPT AND E.DEPTID = A.DEPTID AND %EffdtCheck(DEPT_TBL E_ED, E, %CurrentDateIn) AND F.SETID = A.SETID_JOBCODE AND F.JOBCODE = A.JOBCODE AND %EffdtCheck(JOBCODE_TBL F_ED, F, %CurrentDateIn) AND A.EMPLID = G.EMPLID AND G.LANGUAGE_CD = LNG.LANGUAGE_CD AND LNG.INSTALLED = 1

  • Related Language Record for HR_JOB_RTI_DAT1

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Default Value: PER_ORG_ASGN.EMPLID

    Prompt Table: PERS_SRCH_GBL

    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    4 HR_SRCH_POS_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
    5 HR_SRCH_LOC_DESCR Character(30) VARCHAR2(30) NOT NULL Location
    6 HR_SRCH_DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department
    7 HR_SRCH_JBCD_DESCR Character(30) VARCHAR2(30) NOT NULL Job Title
    8 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    9 NAME Character(50) VARCHAR2(50) NOT NULL Name
    10 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    11 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
    12 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
    13 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
    14 PARTNER_LAST_NAME Character(30) VARCHAR2(30) NOT NULL The Partner Last Name is being used by the Netherlands for the employee name formatting, where a person can choose to use the partners last name as their prefered last name.
    15 PREF_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred Last Name
    16 PREF_MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL Preferred Middle Name
    17 PREF_SEC_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred Second Last Name
    18 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
    19 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    20 NAME_FORMAL Character(60) VARCHAR2(60) NOT NULL Formal Name - name formatted for Formal Display by COuntry. Normally used in Correspondence.
    21 NAME_FORMAL_PSF Character(60) VARCHAR2(60) NOT NULL Preferred Name (PS FORMAT)
    22 HR_SRCH_PYGP_DESCR Character(30) VARCHAR2(30) NOT NULL Paygroup Description
    23 HR_SRCH_GPPY_DESCR Character(30) VARCHAR2(30) NOT NULL GP paygroup Description
    24 HR_SRCH_CMP_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
    25 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    26 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
    27 MIDDLE_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Middle Name uppercase search field
    28 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
    29 PARTN_LAST_NM_SRCH Character(30) VARCHAR2(30) NOT NULL Uppercase version of partner last name to improve performance on searches.
    30 PREF_LAST_NM_SRCH Character(30) VARCHAR2(30) NOT NULL Uppercase version of preferred last name to improve performance on searches.
    31 PREF_FIRST_NM_SRCH Character(30) VARCHAR2(30) NOT NULL Preferred First Name
    32 PREF_MIDL_NM_SRCH Character(30) VARCHAR2(30) NOT NULL Uppercase version of preferred middle name to improve performance on searches.
    33 PREF_LAST_SEC_SRCH Character(30) VARCHAR2(30) NOT NULL Uppercase version of preferred second last name to improve performance on searches.
    34 NAME_DISPLAY_SRCH Character(50) VARCHAR2(50) NOT NULL Uppercase version of Name to improve performance on searches.
    35 NAME_FORMAL_SRCH Character(60) VARCHAR2(60) NOT NULL Uppercase version of formal name to improve performance on searches.
    36 PREF_FIRST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred First Name
    37 SUPERVISOR_NAME Character(50) VARCHAR2(50) NOT NULL Supervisor Name
    38 REPORTS_TO_DESCR Character(50) VARCHAR2(50) NOT NULL Reports To
    39 BUSINESS_TITLE Character(30) VARCHAR2(30) NOT NULL Business Title
    40 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description