HR_SRCH_JOB_VW1

(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 DISTINCT J.EMPLID ,J.EMPL_RCD ,J.EFFDT ,J.EFFSEQ ,J.HR_STATUS ,J.SETID_DEPT ,J.DEPTID ,D.EFFDT ,D.DESCR ,J.SETID_JOBCODE ,J.JOBCODE ,JBCD.EFFDT ,JBCD.DESCR ,J.POSITION_NBR ,P.EFFDT , P.DESCR ,J.POSITION_OVERRIDE ,J.SETID_LOCATION , J.LOCATION ,L.EFFDT ,L.DESCR ,L.DESCR_AC ,L.COUNTRY ,CNTRY.DESCR ,L.ADDRESS1 ,L.ADDRESS2 ,L.ADDRESS3 ,L.ADDRESS4 ,L.CITY ,L.STATE ,ST.DESCR ,L.POSTAL , J.COMPANY ,C.EFFDT ,C.DESCR ,J.BUSINESS_UNIT ,B.DESCR ,J.LASTUPDDTTM ,JBCD.LASTUPDDTTM ,P.LASTUPDDTTM FROM PS_HR_CD_JOB_TMP J LEFT OUTER JOIN PS_HR_CD_POS_TMP P ON J.POSITION_NBR=P.POSITION_NBR , PS_COMPANY_TBL C , PS_BUS_UNIT_TBL_HR B , PS_DEPT_TBL D , PS_LOCATION_TBL L LEFT OUTER JOIN PS_COUNTRY_TBL CNTRY ON L.COUNTRY=CNTRY.COUNTRY LEFT OUTER JOIN PS_STATE_TBL ST ON L.COUNTRY=ST.COUNTRY AND L.STATE=ST.STATE ,PS_JOBCODE_TBL JBCD WHERE C.COMPANY=J.COMPANY AND B.BUSINESS_UNIT=J.BUSINESS_UNIT AND B.ACTIVE_INACTIVE='A' AND J.SETID_DEPT=D.SETID AND J.DEPTID=D.DEPTID AND D.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_DEPT_TBL D1 WHERE D1.SETID=D.SETID AND D1.DEPTID=D.DEPTID) AND D.EFF_STATUS='A' AND J.SETID_LOCATION=L.SETID AND J.LOCATION=L.LOCATION AND L.EFFDT=( SELECT MAX(L1.EFFDT) FROM PS_LOCATION_TBL L1 WHERE L1.SETID=L.SETID AND L1.LOCATION=L.LOCATION AND L1.EFFDT<=%CurrentDateIn) AND L.EFF_STATUS='A' AND J.SETID_JOBCODE=JBCD.SETID AND J.JOBCODE=JBCD.JOBCODE AND JBCD.EFFDT=( SELECT MAX(JBCD1.EFFDT) FROM PS_JOBCODE_TBL JBCD1 WHERE JBCD1.SETID=J.SETID_JOBCODE AND JBCD1.JOBCODE=J.JOBCODE AND JBCD1.EFFDT<=%CurrentDateIn) AND JBCD.EFF_STATUS='A' AND C.EFFDT=( SELECT MAX(C1.EFFDT) FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY=C.COMPANY AND C1.EFFDT<=%CurrentDateIn) AND C.EFF_STATUS='A'

  • Related Language Record: HR_SRCH_JOB_LNG

    • # 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 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
    A=Active
    I=Inactive

    Default Value: A

    6 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID

    Default Value: OPR_DEF_TBL_HR.SETID

    7 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: DEPT_TBL

    8 EFFDT_DEPT Date(10) DATE Dept Effective Date
    9 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    10 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID

    Default Value: OPR_DEF_TBL_HR.SETID

    11 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

    Prompt Table: JOBCODE_TBL

    12 DATE1 Date(10) DATE Date 1
    13 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    14 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

    Prompt Table: POSN_DATA_VW

    15 POSITION_EFFDT Date(10) DATE Report To Position Effective Date
    16 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
    17 POSITION_OVERRIDE Character(1) VARCHAR2(1) NOT NULL Override Position Data

    Y/N Table Edit

    Default Value: N

    18 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
    19 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

    Prompt Table: LOCATION_JOB_VW

    20 DATE2 Date(10) DATE Date 2
    21 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    22 DESCR_AC Character(30) VARCHAR2(30) NOT NULL Alternate Char Description
    23 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    24 COUNTRY_DESCR Character(30) VARCHAR2(30) NOT NULL Country Description
    25 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    26 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    27 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    28 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    29 CITY Character(30) VARCHAR2(30) NOT NULL City
    30 STATE Character(6) VARCHAR2(6) NOT NULL State
    31 STATE_DESCR Character(30) VARCHAR2(30) NOT NULL State Description
    32 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    33 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

    Prompt Table: COMPANY_TBL

    34 DATE3 Date(10) DATE Date 3
    35 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
    36 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT

    Prompt Table: BUSUNIT_HR_VW

    37 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description
    38 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

    Default Value: %datetime

    39 LAST_DTTM_UPDATE DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
    40 LAST_CHANGED_DTTM DateTime(26) TIMESTAMP Last Changed