EX_EXC2500_VW

(SQL View)
 Index Back

Employee Data by Department


SELECT A.DEPTID, B.DESCR, A.EMPLID, C.NAME, C.PHONE, C.COUNTRY_CODE, A.EMPL_STATUS, A.EE_VAL_FLG, A.BUSINESS_UNIT_GL, D.DESCR, A.ADVANCE_LVL, A.ADV_MAX_AMT, A.CURRENCY_CD FROM PS_EX_AA_EE_ORG_VW A, PS_DEPT_TBL B, PS_SET_CNTRL_REC B2, PS_PERSONAL_DATA C, PS_BUS_UNIT_TBL_FS D WHERE B.DEPTID = A.DEPTID AND B2.SETCNTRLVALUE = A.BUSINESS_UNIT_GL AND B2.RECNAME = 'DEPT_TBL' AND B2.SETID = B.SETID AND B.EFFDT = (SELECT MAX(EFFDT) FROM PS_DEPT_TBL WHERE B.SETID = SETID AND B.DEPTID = DEPTID AND EFFDT <= %CURRENTDATEIN) AND B.EFF_STATUS = 'A' AND C.EMPLID = A.EMPLID AND D.BUSINESS_UNIT = A.BUSINESS_UNIT_GL

  • Related Language Record: EX_EXC2500_L_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    2 DESCR_DEPTID Character(30) VARCHAR2(30) NOT NULL (Department Name)
    3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    4 NAME Character(50) VARCHAR2(50) NOT NULL Name
    5 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
    6 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Int'l Prefix
    7 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Employee Status
    A=Active
    D=Deceased
    I=Inactive
    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 EE_VAL_FLG Character(1) VARCHAR2(1) NOT NULL Valid for Expenses
    N=No
    Y=Yes
    9 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
    10 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    11 ADVANCE_LVL Character(1) VARCHAR2(1) NOT NULL Cash Advance Level
    B=Business Unit
    N=None
    S=Specific Amount
    12 ADV_MAX_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Cash Advance Maximum
    13 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code