HR_SRCHPRSJOBLG

(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 ,COALESCE(X.XLATLONGNAME ,M.MESSAGE_TEXT) ,COALESCE(X1.XLATLONGNAME ,M.MESSAGE_TEXT) ,DEP.DESCR ,JBCD.DESCR ,COALESCE(POS.DESCR ,M.MESSAGE_TEXT) ,LOC.DESCR ,LOC.DESCR_AC ,COMP.DESCR ,BU.DESCR ,RR.DESCR50 ,PG.DESCR ,GP.DESCR ,UC.DESCR ,COALESCE(MIL.DESCR ,M.MESSAGE_TEXT) ,COALESCE(CLS.DESCR ,M.MESSAGE_TEXT) FROM PS_HR_SRCH_PERSJOB J LEFT OUTER JOIN PSXLATITEMLANG X ON (X.FIELDNAME='HR_STATUS' AND X.FIELDVALUE=J.HR_STATUS) LEFT OUTER JOIN PSXLATITEMLANG X1 ON (X1.FIELDNAME='EMPL_STATUS' AND X1.FIELDVALUE=J.EMPL_STATUS) LEFT OUTER JOIN PS_MIL_RANK_LNG MIL ON (MIL.MILITARY_SERVICE=J.MILITARY_SERVICE AND MIL.MIL_RANK=J.MIL_RANK ) LEFT OUTER JOIN PS_EMPL_CL_TBL_LNG CLS ON(J.SETID_EMPL_CLASS=CLS.SETID AND J.EMPL_CLASS=CLS.EMPL_CLASS AND J.DATE4=CLS.EFFDT ) LEFT OUTER JOIN PS_POSN_DATA_LANG POS ON ( J.POSITION_NBR=POS.POSITION_NBR AND POS.EFFDT=J.POSITION_EFFDT ) LEFT OUTER JOIN PS_REG_REGION_LANG RR ON(RR.REG_REGION = J.REG_REGION) LEFT OUTER JOIN PS_GP_PYGRP_LANG GP ON(GP.GP_PAYGROUP=J.GP_PAYGROUP) LEFT OUTER JOIN PS_UNION_TBL_LANG UC ON ( UC.UNION_CD=J.UNION_CD AND UC.EFFDT= J.EFFDT_UNIONCD) LEFT OUTER JOIN PS_PAYGROUP_LANG PG ON( PG.COMPANY = J.COMPANY_ALT AND PG.PAYGROUP = J.PAYGROUP AND PG.EFFDT = J.EFFDT_PAY_GROUP) ,PS_DEPT_TBL_LANG DEP , PS_JOBCODE_LANG JBCD , PS_LOCATION_LANG LOC , PS_COMPNY_TBL_LANG COMP , PS_BUS_UNIT_HR_LNG BU ,PSMSGCATLANG M 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_ALT=COMP.COMPANY AND COMP.EFFDT=J.DATE3 AND J.BUSINESS_UNIT2=BU.BUSINESS_UNIT AND M.MESSAGE_SET_NBR=1000 AND M.MESSAGE_NBR=1746 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 AND (DEP.LANGUAGE_CD=MIL.LANGUAGE_CD OR MIL.LANGUAGE_CD IS NULL) AND (DEP.LANGUAGE_CD=CLS.LANGUAGE_CD OR CLS.LANGUAGE_CD IS NULL) AND (DEP.LANGUAGE_CD=RR.LANGUAGE_CD OR RR.LANGUAGE_CD IS NULL) AND (DEP.LANGUAGE_CD=PG.LANGUAGE_CD OR PG.LANGUAGE_CD IS NULL) AND (DEP.LANGUAGE_CD=GP.LANGUAGE_CD OR GP.LANGUAGE_CD IS NULL) AND DEP.LANGUAGE_CD=M.LANGUAGE_CD AND DEP.LANGUAGE_CD=COALESCE(X.LANGUAGE_CD,DEP.LANGUAGE_CD) AND DEP.LANGUAGE_CD=COALESCE(X1.LANGUAGE_CD,DEP.LANGUAGE_CD)

  • Related Language Record for HR_SRCH_PERSJOB

    • # 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 HR_STATUS_DESCR Character(30) VARCHAR2(30) NOT NULL HR Status
    7 HR_SRCH_PYSTSDESCR Character(30) VARCHAR2(30) NOT NULL Payroll Status
    8 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    9 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    10 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
    11 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    12 DESCR_AC Character(30) VARCHAR2(30) NOT NULL Alternate Char Description
    13 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
    14 DESCR_BU2 Character(50) VARCHAR2(50) NOT NULL Stores both "After' BU description & code for Fluid Approval
    15 REG_REGION_DESCR Character(50) VARCHAR2(50) NOT NULL Regulatory Region
    16 PG_DESCR Character(30) VARCHAR2(30) NOT NULL Pay Group Description
    17 GP_PAYGROUP_DESCR Character(30) VARCHAR2(30) NOT NULL GP Paygroup
    18 UNION_CODE_DESCR Character(50) VARCHAR2(50) NOT NULL Description of length 50
    19 HR_SRCH_RANK_DESCR Character(30) VARCHAR2(30) NOT NULL Rank
    20 HR_SRCH_EMPL_CLASS Character(30) VARCHAR2(30) NOT NULL Employee Class