HR_SRCH_PERSJOB

(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 ,X.XLATLONGNAME ,J.EMPL_STATUS ,X2.XLATLONGNAME ,J.SETID_DEPT ,J.DEPTID ,D.EFFDT ,D.DESCR ,J.SETID_JOBCODE ,J.JOBCODE ,J.REG_REGION ,COALESCE(( SELECT RR.DESCR50 FROM PS_REG_REGION_TBL RR WHERE RR.REG_REGION = J.REG_REGION),' ') ,COALESCE(J.UNION_CD ,'NON') ,UT.EFFDT ,UT.DESCR ,J.PAYGROUP ,COALESCE(PGRP.DESCR,' ') ,PGRP.EFFDT ,J.GP_PAYGROUP ,COALESCE(( SELECT GPPG.DESCR FROM PS_GP_PYGRP GPPG WHERE GPPG.GP_PAYGROUP = J.GP_PAYGROUP),' ') ,JBCD.EFFDT ,JBCD.DESCR ,J.POSITION_NBR ,P.EFFDT , COALESCE(P.DESCR ,M.MESSAGE_TEXT) ,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 ,M3.MILITARY_SERVICE ,M1. MIL_RANK ,COALESCE( M3.DESCR ,M.MESSAGE_TEXT) ,J.SETID_EMPL_CLASS ,J.EMPL_CLASS ,CLS.EFFDT ,COALESCE(CLS.DESCR ,M.MESSAGE_TEXT) ,COALESCE(J.REG_TEMP ,'NON') , COALESCE(J.FULL_PART_TIME ,'NON') ,J.LASTUPDDTTM ,JBCD.LASTUPDDTTM ,P.LASTUPDDTTM FROM PS_JOB J LEFT OUTER JOIN PS_POSITION_DATA P ON J.POSITION_NBR=P.POSITION_NBR LEFT OUTER JOIN PS_JOB_MIL M1 ON (J.EMPLID=M1.EMPLID AND J.EMPL_RCD=M1.EMPL_RCD AND J.EFFDT=M1.EFFDT AND J.EFFSEQ=M1.EFFSEQ) LEFT OUTER JOIN PS_JOB_MIL_ASGN_VW M2 ON (J.EMPLID=M2.EMPLID AND J.EMPL_RCD=M2.EMPL_RCD) LEFT OUTER JOIN PS_MIL_RANK_TBL M3 ON (M3.MILITARY_SERVICE=M2.MILITARY_SERVICE AND M3.MIL_RANK=M1.MIL_RANK) LEFT OUTER JOIN PS_EMPL_CLASS_TBL CLS ON (J.EMPL_CLASS=CLS.EMPL_CLASS AND J.SETID_EMPL_CLASS=CLS.SETID) LEFT OUTER JOIN PS_UNION_TBL UT ON (J.UNION_CD=UT.UNION_CD) LEFT OUTER JOIN PS_PAYGROUP_TBL PGRP ON ( J.COMPANY =PGRP.COMPANY AND J.PAYGROUP =PGRP.PAYGROUP ) , 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 , psxlatitem x , psxlatitem x2 , PSMSGCATDEFN M 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' AND (CLS.EMPL_CLASS IS NULL OR (CLS.EFFDT= ( SELECT MAX(CLS1.EFFDT) FROM PS_EMPL_CLASS_TBL CLS1 WHERE CLS1.EMPL_CLASS=CLS.EMPL_CLASS AND CLS1.SETID=CLS.SETID AND CLS1.EFFDT<=%CurrentDateIn) AND CLS.EFF_STATUS='A') ) AND x.fieldname='HR_STATUS' AND x.FIELDVALUE=J.HR_STATUS AND x.effdt=( SELECT MAX(x1.effdt) FROM psxlatitem x1 WHERE x1.fieldname=x.fieldname AND X1.FIELDVALUE=X.FIELDVALUE AND X1.effdt<=%CurrentDateIn) AND X.EFF_STATUS='A' AND x2.fieldname='EMPL_STATUS' AND x2.FIELDVALUE=J.EMPL_STATUS AND x2.effdt=( SELECT MAX(x3.effdt) FROM psxlatitem x3 WHERE x3.fieldname=x2.fieldname AND X3.FIELDVALUE=X2.FIELDVALUE AND X3.effdt<=%CurrentDateIn) AND X2.EFF_STATUS='A' AND M.MESSAGE_SET_NBR=1000 AND M.MESSAGE_NBR=1746 AND (UT.UNION_CD IS NULL OR UT.EFFDT=( SELECT MAX(UT1.EFFDT) FROM PS_UNION_TBL UT1 WHERE UT1.UNION_CD=J.UNION_CD AND UT1.EFFDT<=%CurrentDateIn) AND UT.EFF_STATUS='A' ) AND (PGRP.PAYGROUP IS NULL OR PGRP.EFFDT=( SELECT MAX(PGRP1.EFFDT) FROM PS_PAYGROUP_TBL PGRP1 WHERE PGRP1.COMPANY = PGRP.COMPANY AND PGRP1.PAYGROUP = PGRP.PAYGROUP AND PGRP1.EFFDT <= %CurrentDateIn) AND PGRP.EFF_STATUS = 'A' ) UNION SELECT P.EMPLID ,0 ,%DateIn('1900-01-01') ,1 ,' ' ,M.MESSAGE_TEXT ,' ' ,' ' ,' ' ,' ' ,%DateIn('1900-01-01') ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,%DateIn('1900-01-01') ,' ' ,' ' ,' ' ,%DateIn('1900-01-01') ,' ' ,' ' ,%DateIn('1900-01-01') ,' ' ,M.MESSAGE_TEXT ,%DateIn('1900-01-01') , M.MESSAGE_TEXT ,' ' ,' ' , ' ' ,%DateIn('1900-01-01') ,M.MESSAGE_TEXT ,M.MESSAGE_TEXT ,' ' ,M.MESSAGE_TEXT ,' ' ,' ' ,' ' ,' ' ,M.MESSAGE_TEXT ,' ' ,M.MESSAGE_TEXT ,' ' , ' ' ,%DateIn('1900-01-01') ,M.MESSAGE_TEXT ,' ' ,' ' ,M.MESSAGE_TEXT ,' ' , M.MESSAGE_TEXT ,' ' ,' ' ,%DateIn('1900-01-01') ,M.MESSAGE_TEXT , 'NON' , 'NON' ,%DateTimeIn('1900-01-01-00.00.00.000000') , %DateTimeIn('1900-01-01-00.00.00.000000') , %DateTimeIn('1900-01-01-00.00.00.000000') FROM PS_PERSON P ,PSMSGCATDEFN M WHERE NOT EXISTS ( SELECT 'X' FROM PS_JOB X WHERE X.EMPLID=P.EMPLID) AND M.MESSAGE_SET_NBR=1000 AND M.MESSAGE_NBR=1746

  • Related Language Record: HR_SRCHPRSJOBLG

    • # 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 HR_STATUS_DESCR Character(30) VARCHAR2(30) NOT NULL HR Status
    7 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
    A=Active
    D=Deceased
    L=Leave of Absence
    P=Leave With Pay
    Q=Retired With Pay
    R=Retired
    S=Suspended
    T=Terminated
    U=Terminated With Pay
    V=Terminated Pension Pay Out
    W=Short Work Break
    X=Retired-Pension Administration
    8 HR_SRCH_PYSTSDESCR Character(30) VARCHAR2(30) NOT NULL Payroll Status
    9 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID

    Default Value: OPR_DEF_TBL_HR.SETID

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

    Prompt Table: DEPT_TBL

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

    Default Value: OPR_DEF_TBL_HR.SETID

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

    Prompt Table: JOBCODE_TBL

    15 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
    16 REG_REGION_DESCR Character(50) VARCHAR2(50) NOT NULL Regulatory Region
    17 UNION_CD Character(3) VARCHAR2(3) NOT NULL Union Code
    18 EFFDT_UNIONCD Date(10) DATE Union Code Effective Date
    19 UNION_CODE_DESCR Character(50) VARCHAR2(50) NOT NULL Description of length 50
    20 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
    21 PG_DESCR Character(30) VARCHAR2(30) NOT NULL Pay Group Description
    22 EFFDT_PAY_GROUP Date(10) DATE Pay Group Effective Date
    23 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
    24 GP_PAYGROUP_DESCR Character(30) VARCHAR2(30) NOT NULL GP Paygroup
    25 DATE1 Date(10) DATE Date 1
    26 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Title
    27 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

    Prompt Table: POSN_DATA_VW

    28 POSITION_EFFDT Date(10) DATE Report To Position Effective Date
    29 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Title
    30 POSITION_OVERRIDE Character(1) VARCHAR2(1) NOT NULL Override Position Data

    Y/N Table Edit

    Default Value: N

    31 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
    32 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

    Prompt Table: LOCATION_JOB_VW

    33 DATE2 Date(10) DATE Date 2
    34 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location
    35 DESCR_AC Character(30) VARCHAR2(30) NOT NULL Alternate Char Description
    36 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    37 COUNTRY_DESCR Character(30) VARCHAR2(30) NOT NULL Country Description
    38 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    39 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    40 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    41 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    42 CITY Character(30) VARCHAR2(30) NOT NULL City
    43 STATE Character(6) VARCHAR2(6) NOT NULL State
    44 STATE_DESCR Character(30) VARCHAR2(30) NOT NULL State Description
    45 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    46 COMPANY_ALT Character(3) VARCHAR2(3) NOT NULL Company Code
    47 DATE3 Date(10) DATE Date 3
    48 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company
    49 BUSINESS_UNIT2 Character(5) VARCHAR2(5) NOT NULL Business Unit Code
    50 DESCR_BU2 Character(50) VARCHAR2(50) NOT NULL Stores both "After' BU description & code for Fluid Approval
    51 MILITARY_SERVICE Character(8) VARCHAR2(8) NOT NULL Military Service
    52 MIL_RANK Character(5) VARCHAR2(5) NOT NULL Military Rank
    53 HR_SRCH_RANK_DESCR Character(30) VARCHAR2(30) NOT NULL Rank
    54 SETID_EMPL_CLASS Character(5) VARCHAR2(5) NOT NULL Employee class Set ID
    55 EMPL_CLASS Character(3) VARCHAR2(3) NOT NULL Employee Classification
    56 DATE4 Date(10) DATE Date 4
    57 HR_SRCH_EMPL_CLASS Character(30) VARCHAR2(30) NOT NULL Employee Class
    58 HR_SRCH_REG_TEMP Character(3) VARCHAR2(3) NOT NULL Regular or Temporary
    NON=No Value
    R=Regular
    T=Temporary
    59 HR_SRCH_FULL_PART Character(3) VARCHAR2(3) NOT NULL Full or Part Time
    D=On Demand
    F=Full-Time
    NON=No Value
    P=Part-Time
    60 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

    61 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.
    62 LAST_CHANGED_DTTM DateTime(26) TIMESTAMP Last Changed