HRS_POSDTA_JF_I

(SQL View)
 Index Back

Position Data

Position data limited by business unit and job family

SELECT A.BUSINESS_UNIT , B.JOB_FAMILY , A.POSITION_NBR , A.EFFDT , A.EFF_STATUS , A.DESCR , A.DESCRSHORT , A.GVT_ORG_TTL_DESCR , A.JOBCODE FROM PS_HRS_POSN_DTA_I2 A , PS_HRS_JOBCODE_I B , PS_SET_CNTRL_REC C WHERE A.EFF_STATUS = 'A' AND A.POSN_STATUS = 'A' AND A.JOBCODE = B.JOBCODE AND A.BUSINESS_UNIT = C.SETCNTRLVALUE AND C.RECNAME = 'HRS_JOBCODE_I' AND B.SETID = C.SETID AND B.JOB_FAMILY <> ' ' AND B.EFFDT=( SELECT MAX(EFFDT) FROM PS_HRS_JOBCODE_I D WHERE D.SETID=B.SETID AND D.JOBCODE=B.JOBCODE) AND ((%EffdtCheck(HRS_POSN_DTA_I2, A, %CurrentDateIn) AND A.EFF_STATUS = 'A') OR (A.EFFDT=( SELECT MAX(EFFDT) FROM PS_HRS_POSN_DTA_I2 E WHERE E.POSITION_NBR=A.POSITION_NBR AND E.EFFDT > %CurrentDateIn AND E.EFF_STATUS = 'A') AND NOT EXISTS ( SELECT 'Y' FROM PS_HRS_POSN_DTA_I2 F WHERE F.POSITION_NBR=A.POSITION_NBR AND %EffdtCheck(HRS_POSN_DTA_I2, F, %CurrentDateIn) AND F.EFF_STATUS = 'A')))

  • Related Language Record: HRS_PSNDTJF_LNI

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT

    2 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
    3 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

    Default Value: 00000000

    4 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    5 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive
    6 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    7 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    8 GVT_ORG_TTL_DESCR Character(70) VARCHAR2(70) NOT NULL Organization Position Title
    9 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code