HR_JOB_RTI_TMPL

(SQL View)
 Index Back

Job Data Temp Record

This temp record has been created to extract all the Job Data and to bypass the query security record.

SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ , LNG.LANGUAGE_CD , %Coalesce((SELECT DL.DESCR50 FROM PS_REG_REGION_LANG DL WHERE D.REG_REGION = DL.REG_REGION AND DL.LANGUAGE_CD = LNG.LANGUAGE_CD), D.DESCR50,' ') , %Coalesce((SELECT LL.DESCR FROM PS_COMPNY_TBL_LANG LL WHERE L.COMPANY = LL.COMPANY AND LL.LANGUAGE_CD = LNG.LANGUAGE_CD AND L.EFFDT = LL.EFFDT), L.DESCR,' ') , %Coalesce((SELECT IL.DESCR FROM PS_PAYGROUP_LANG IL WHERE IL.COMPANY = I.COMPANY AND IL.PAYGROUP = I.PAYGROUP AND IL.LANGUAGE_CD = LNG.LANGUAGE_CD AND I.EFFDT = IL.EFFDT AND IL.PAYGROUP = A.PAYGROUP),I.DESCR,' ') , %Coalesce((SELECT ML.DESCR FROM PS_GP_PYGRP_LANG ML WHERE ML.GP_PAYGROUP = M.GP_PAYGROUP AND ML.GP_PAYGROUP = A. GP_PAYGROUP AND ML.LANGUAGE_CD = LNG.LANGUAGE_CD ),M.DESCR,' ') , %Coalesce((SELECT N.NAME_DISPLAY FROM PS_NAMES_LNG N WHERE N.EMPLID = A.SUPERVISOR_ID AND N.NAME_TYPE = 'PRI' AND N.LANGUAGE_CD = LNG.LANGUAGE_CD AND N.EFFDT = ( SELECT MAX(N1.EFFDT) FROM PS_NAMES_LNG N1 WHERE N1.EMPLID = N.EMPLID AND N1.NAME_TYPE = 'PRI' AND N1.EFFDT <= %CurrentDateIn AND N1.LANGUAGE_CD = N.LANGUAGE_CD)),' ') , %Coalesce (( SELECT P.DESCR FROM PS_POSN_DATA_LANG P WHERE P.POSITION_NBR=A.REPORTS_TO AND P.LANGUAGE_CD = LNG.LANGUAGE_CD AND P.EFFDT=( SELECT MAX(EFFDT) FROM PS_POSN_DATA_LANG P2 WHERE P.POSITION_NBR=P2.POSITION_NBR AND P2.EFFDT<=%CurrentDateIn AND P.LANGUAGE_CD = P2.LANGUAGE_CD)),' ') , %Coalesce(( SELECT PORG.BUSINESS_TITLE FROM PS_PER_ORG_ASG_LNG PORG WHERE A.EMPLID = PORG.EMPLID AND A.EMPL_RCD = PORG.EMPL_RCD AND PORG.LANGUAGE_CD = LNG.LANGUAGE_CD),' ') , %Coalesce(( SELECT BU.DESCR FROM PS_BUS_UNIT_HR_LNG BU WHERE A.BUSINESS_UNIT = BU.BUSINESS_UNIT AND BU.LANGUAGE_CD = LNG.LANGUAGE_CD),' ') FROM ((((PS_HR_SRCH_JOB_VW A LEFT OUTER JOIN PS_PAYGROUP_TBL I ON I.COMPANY = A.COMPANY AND I.PAYGROUP = A.PAYGROUP) LEFT OUTER JOIN PS_GP_SRCH_PAYSTM J ON J.PAY_SYSTEM_FLG = A.PAY_SYSTEM_FLG) LEFT OUTER JOIN PS_HR_SRCH_ABS_STM K ON K.HR_SRCH_ABS_STM_CD = A.ABSENCE_SYSTEM_CD) LEFT OUTER JOIN PS_GP_PYGRP M ON M.GP_PAYGROUP = A.GP_PAYGROUP) , PS_REG_REGION_TBL D , PS_COMPANY_TBL L , PSLANGUAGES LNG WHERE D.REG_REGION = A.REG_REGION AND L.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL L_ED, L, %CurrentDateIn) AND %EffdtCheck(PAYGROUP_TBL I_ED, I, %CurrentDateIn) AND LNG.INSTALLED = 1

  • Related Language Record for HR_JOB_RTI_TMP1

    • # 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 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    5 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    6 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    7 HR_SRCH_CMP_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
    8 HR_SRCH_PYGP_DESCR Character(30) VARCHAR2(30) NOT NULL Paygroup Description
    9 HR_SRCH_GPPY_DESCR Character(30) VARCHAR2(30) NOT NULL GP paygroup Description
    10 SUPERVISOR_NAME Character(50) VARCHAR2(50) NOT NULL Supervisor Name
    11 REPORTS_TO_DESCR Character(50) VARCHAR2(50) NOT NULL Reports To
    12 BUSINESS_TITLE Character(30) VARCHAR2(30) NOT NULL Business Title
    13 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description