HR_SRCH_JOB_LNG

(SQL View)
 Index Back

Job data view

View created for Person Global Search (Company Directory). Joins to get descriptions from Department, Jobcode, Position, Location, Company, Business Unit.

SELECT J.EMPLID ,J.EMPL_RCD ,DEP.LANGUAGE_CD ,J.EFFDT , J.EFFSEQ ,DEP.DESCR ,JBCD.DESCR ,POS.DESCR ,LOC.DESCR ,LOC.DESCR_AC ,COMP.DESCR ,BU.DESCR FROM PS_HR_SRCH_JOB_VW1 J LEFT OUTER JOIN PS_POSN_DATA_LANG POS ON ( J.POSITION_NBR=POS.POSITION_NBR AND POS.EFFDT=J.POSITION_EFFDT ) , PS_DEPT_TBL_LANG DEP , PS_JOBCODE_LANG JBCD , PS_LOCATION_LANG LOC , PS_COMPNY_TBL_LANG COMP , PS_BUS_UNIT_HR_LNG BU WHERE J.SETID_DEPT=DEP.SETID AND J.DEPTID=DEP.DEPTID AND DEP.EFFDT=J.EFFDT_DEPT AND J.SETID_JOBCODE=JBCD.SETID AND J.JOBCODE=JBCD.JOBCODE AND JBCD.EFFDT=J.DATE1 AND J.SETID_LOCATION=LOC.SETID AND J.LOCATION=LOC.LOCATION AND LOC.EFFDT=J.DATE2 AND J.COMPANY=COMP.COMPANY AND COMP.EFFDT=J.DATE3 AND J.BUSINESS_UNIT=BU.BUSINESS_UNIT AND DEP.LANGUAGE_CD=JBCD.LANGUAGE_CD AND (DEP.LANGUAGE_CD=POS.LANGUAGE_CD OR POS.LANGUAGE_CD IS NULL) AND DEP.LANGUAGE_CD=LOC.LANGUAGE_CD AND DEP.LANGUAGE_CD=COMP.LANGUAGE_CD AND DEP.LANGUAGE_CD=BU.LANGUAGE_CD

  • Related Language Record for HR_SRCH_JOB_VW1

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

    Default Value: %date

    5 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    6 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    7 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    8 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
    9 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    10 DESCR_AC Character(30) VARCHAR2(30) NOT NULL Alternate Char Description
    11 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
    12 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description