PERSONNEL

(SQL View)
 Index Back

Employees only - ASOFDATE

PERSONNEL is a view that combines personal/demographic data, employment data, and job/salary history for an employee for a specified date. This view is used primarily for reporting.

SELECT DISTINCT A.EMPLID ,B.EMPL_RCD ,C.EFFDT ,C.EFFSEQ ,A.NAME ,A.NAME_PREFIX ,C.PER_ORG ,O.ORIG_HIRE_DT ,A.SEX ,A.BIRTHDATE ,A.BIRTHPLACE ,A.DT_OF_DEATH ,A.MAR_STATUS ,A.HIGHEST_EDUC_LVL ,A.FT_STUDENT ,B.BENEFIT_RCD_NBR ,B.HOME_HOST_CLASS ,B.HIRE_DT ,B.LAST_HIRE_DT ,B.CMPNY_SENIORITY_DT ,B.SERVICE_DT ,B.EXPECTED_RETURN_DT ,B.TERMINATION_DT ,B.LAST_DATE_WORKED ,B.LAST_INCREASE_DT ,B.OWN_5PERCENT_CO ,B.BUSINESS_TITLE ,C.REPORTS_TO ,C.SUPERVISOR_ID ,B.PROBATION_DT ,B.POSITION_PHONE ,C.DEPTID ,C.JOBCODE ,C.POSITION_NBR ,C.EMPL_STATUS ,C.ACTION ,C.ACTION_DT ,C.ACTION_REASON ,C.LOCATION ,C.TAX_LOCATION_CD ,C.JOB_ENTRY_DT ,C.DEPT_ENTRY_DT ,C.POSITION_ENTRY_DT ,C.SHIFT ,C.REG_TEMP ,C.FULL_PART_TIME ,C.FLSA_STATUS ,C.OFFICER_CD ,C.COMPANY ,C.PAYGROUP ,C.EMPL_TYPE ,C.HOLIDAY_SCHEDULE ,C.STD_HOURS ,C.EEO_CLASS ,C.SAL_ADMIN_PLAN ,C.GRADE ,C.GRADE_ENTRY_DT ,C.STEP ,C.STEP_ENTRY_DT ,C.GL_PAY_TYPE ,C.COMP_FREQUENCY ,C.COMPRATE ,C.CHANGE_AMT ,C.CHANGE_PCT ,C.ANNUAL_RT ,C.MONTHLY_RT ,C.HOURLY_RT ,C.ANNL_BENEF_BASE_RT ,C.SHIFT_RT ,C.SHIFT_FACTOR ,C.CURRENCY_CD ,C.BUSINESS_UNIT ,D.DESCR ,D.DESCRSHORT ,D.EEO1CODE ,D.EEO4CODE ,D.EEO5CODE ,D.EEO6CODE ,D.EEO_JOB_GROUP ,D.JOB_FAMILY ,D.JOB_KNOWHOW_POINTS ,D.JOB_ACCNTAB_POINTS ,D.JOB_PROBSLV_POINTS ,D.JOB_POINTS_TOTAL ,D.JOB_KNOWHOW_PCT ,D.JOB_ACCNTAB_PCT ,D.JOB_PROBSLV_PCT ,E.DESCR ,E.DESCRSHORT ,E.MANAGER_ID ,E.EEO4_FUNCTION ,F.FROMDATE ,F.ASOFDATE ,D.IPEDSSCODE ,C.ADDS_TO_FTE_ACTUAL ,C.CLASS_INDC ,C.FICA_STATUS_EE ,C.FTE ,C.PRORATE_CNT_AMT ,C.ENCUMB_OVERRIDE ,C.PAY_SYSTEM_FLG ,C.REG_REGION ,RG.COUNTRY ,C.JOB_INDICATOR ,C.CONTRACT_NUM ,D.WORKERS_COMP_CD FROM PS_PERSONAL_DT_FST A , PS_PER_ORG_ASGN_VW B , PS_JOB C , PS_JOBCODE_TBL D , PS_DEPT_TBL E , PS_ORIG_HIR_PER_VW O , PSASOFDATE F , PS_REG_REGION_TBL RG WHERE C.PER_ORG = 'EMP' AND B.EMPLID = A.EMPLID AND C.EMPLID = B.EMPLID AND O.EMPLID = C.EMPLID AND C.EMPL_RCD = B.EMPL_RCD AND C.EFFDT = ( SELECT MAX(H.EFFDT) FROM PS_JOB H WHERE H.EMPLID = C.EMPLID AND H.EMPL_RCD = C.EMPL_RCD AND H.EFFDT <= F.ASOFDATE ) AND C.EFFSEQ = ( SELECT MAX(I.EFFSEQ) FROM PS_JOB I WHERE I.EMPLID = C.EMPLID AND I.EMPL_RCD = C.EMPL_RCD AND I.EFFDT = C.EFFDT ) AND D.JOBCODE = C.JOBCODE AND D.EFFDT = ( SELECT MAX(J.EFFDT) FROM PS_JOBCODE_TBL J WHERE J.JOBCODE = D.JOBCODE AND J.EFFDT <= C.EFFDT AND J.SETID = C.SETID_JOBCODE ) AND D.SETID = C.SETID_JOBCODE AND E.DEPTID = C.DEPTID AND E.EFFDT = ( SELECT MAX(K.EFFDT) FROM PS_DEPT_TBL K WHERE K.DEPTID = E.DEPTID AND K.EFFDT <= C.EFFDT AND K.SETID =C.SETID_DEPT ) AND E.SETID = C.SETID_DEPT AND RG.REG_REGION = C.REG_REGION

  • Parent record: PER_ORG_ASGN_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
    3 EFFDT Date(10) DATE NOT NULL Effective Date
    4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    5 NAME Character(50) VARCHAR2(50) NOT NULL Name
    6 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix
    7 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
    CWR=Contingent Worker
    EMP=Employee
    POI=Person of Interest
    8 ORIG_HIRE_DT Date(10) DATE Original Start Date
    9 SEX Character(1) VARCHAR2(1) NOT NULL Gender
    F=Female
    M=Male
    U=Unknown
    X=Indeterminate/Intersex/Unspec
    10 BIRTHDATE Date(10) DATE Date of Birth
    11 BIRTHPLACE Character(30) VARCHAR2(30) NOT NULL Birth Location
    12 DT_OF_DEATH Date(10) DATE Date of Death
    13 MAR_STATUS Character(1) VARCHAR2(1) NOT NULL Marital Status
    C=Common-Law
    D=Divorced
    E=Separated
    H=Head of Household
    L=DissDeclLost Civil Partner
    M=Married
    P=Civil Partnership
    S=Single
    T=Surviving Civil Partner
    U=Unknown
    V=Dissolved Civil Partnership
    W=Widowed
    14 HIGHEST_EDUC_LVL Character(2) VARCHAR2(2) NOT NULL Highest Education Level
    15 FT_STUDENT Character(1) VARCHAR2(1) NOT NULL Full-Time Student

    Y/N Table Edit

    16 BENEFIT_RCD_NBR Number(3,0) SMALLINT NOT NULL Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.
    17 HOME_HOST_CLASS Character(1) VARCHAR2(1) NOT NULL Home/Host Classification
    M=Home
    S=Host
    18 HIRE_DT Date(10) DATE First Start Date
    19 REHIRE_DT Date(10) DATE Rehire Date
    20 CMPNY_SENIORITY_DT Date(10) DATE Company Seniority Date
    21 SERVICE_DT Date(10) DATE Service Date
    22 EXPECTED_RETURN_DT Date(10) DATE Expected Return Date
    23 TERMINATION_DT Date(10) DATE Termination Date
    24 LAST_DATE_WORKED Date(10) DATE Last Date Worked
    25 LAST_INCREASE_DT Date(10) DATE Date Last Increase
    26 OWN_5PERCENT_CO Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) an employee owns 5% or more of the company. This value is used in nondiscrimination testing.

    Y/N Table Edit

    27 BUSINESS_TITLE Character(30) VARCHAR2(30) NOT NULL Business Title
    28 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
    29 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID

    Prompt Table: PERSON

    30 PROBATION_DT Date(10) DATE Probation Date
    31 WORK_PHONE Character(24) VARCHAR2(24) NOT NULL Work Phone #
    32 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: DEPT_TBL

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

    Prompt Table: JOBCODE_TBL

    34 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
    35 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
    36 ACTION Character(3) VARCHAR2(3) NOT NULL Action

    Prompt Table: ACTION_TBL

    37 ACTION_DT Date(10) DATE Action Date
    38 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code

    Prompt Table: ACTN_REASON_TBL

    39 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

    Prompt Table: LOCATION_TBL

    40 TAX_LOCATION_CD Character(10) VARCHAR2(10) NOT NULL Tax Location Code

    Prompt Table: TAX_LOCATION1

    41 JOB_ENTRY_DT Date(10) DATE Job Entry Date
    42 DEPT_ENTRY_DT Date(10) DATE Department Entry Date
    43 POSITION_ENTRY_DT Date(10) DATE Position Entry Date
    44 SHIFT Character(1) VARCHAR2(1) NOT NULL Regular Shift
    1=1
    2=2
    3=3
    A=Any
    C=Compressed
    N=Not Applicable
    R=Rotating
    45 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
    R=Regular
    T=Temporary
    46 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
    D=On Demand
    F=Full-Time
    P=Part-Time
    47 FLSA_STATUS Character(1) VARCHAR2(1) NOT NULL FLSA Status field
    A=Administrative
    C=Computer Employee
    E=Executive
    H=Highly Compensated Employees
    M=Management
    N=Nonexempt
    O=Outside Salesperson
    P=Professional
    V=Nonexempt Alt Overtime
    X=No FLSA Required
    48 OFFICER_CD Character(1) VARCHAR2(1) NOT NULL Officer Code
    C=Chairman
    D=Director
    N=None
    O=Officer
    P=President
    S=Secretary
    T=Treasurer
    V=Vice President
    49 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

    Prompt Table: COMPANY_TBL

    50 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group

    Prompt Table: PAYGROUP_TBL

    51 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
    E=Exception Hourly
    H=Hourly
    N=Not Applicable
    S=Salaried

    Prompt Table: PAYGRP_EMPLTYPE

    52 HOLIDAY_SCHEDULE Character(6) VARCHAR2(6) NOT NULL Holiday Schedule

    Prompt Table: HOLIDAY_TBL

    53 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
    54 EEO_CLASS Character(1) VARCHAR2(1) NOT NULL EEO Classification
    E=Exclude from Count
    N=None of the Above
    P=Production Trainee
    W=White-Collar Trainee
    55 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan
    56 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade

    Prompt Table: SAL_GRADE_TBL

    57 GRADE_ENTRY_DT Date(10) DATE Grade Entry Date
    58 STEP Number(2,0) SMALLINT NOT NULL Step

    Prompt Table: SAL_STEP_TBL

    59 STEP_ENTRY_DT Date(10) DATE Step Entry Date
    60 GL_PAY_TYPE Character(6) VARCHAR2(6) NOT NULL General Ledger Pay Type
    61 COMP_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Compensation Frequency
    A=Annual
    B=Biweekly
    C=Contract
    D=Daily
    H=Hourly
    M=Monthly
    S=Semimonthly
    W=Weekly

    Prompt Table: COMP_FREQ_VW

    62 COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Compensation Rate
    63 CHANGE_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Change Amount
    64 CHANGE_PCT Signed Number(8,3) DECIMAL(6,3) NOT NULL Change Percent
    65 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
    66 MONTHLY_RT Number(19,3) DECIMAL(18,3) NOT NULL Monthly Rate
    67 HOURLY_RT Number(19,6) DECIMAL(18,6) NOT NULL Hourly Rate
    68 ANNL_BENEF_BASE_RT Number(19,3) DECIMAL(18,3) NOT NULL Specifies a compensation amount used to calculate benefits and benefit deductions for an employee. The base compensation amount may include commissions and bonuses in addition to regular compensation. If no Annual Benefits Base Rate is entered, then benefits calculations are based on an employee's regular compensation.
    69 SHIFT_RT Number(19,6) DECIMAL(18,6) NOT NULL Shift Differential Rate
    70 SHIFT_FACTOR Number(5,3) DECIMAL(4,3) NOT NULL Shift Differential Factor
    71 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

    Prompt Table: CURRENCY_CD_TBL

    72 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    73 JOBTITLE Character(30) VARCHAR2(30) NOT NULL Job Title
    74 JOBTITLE_ABBRV Character(10) VARCHAR2(10) NOT NULL Short Name
    75 EEO1CODE Character(1) VARCHAR2(1) NOT NULL EEO-1 Job Category
    1=Officials and Managers
    2=Professionals
    3=Technicians
    4=Sales Workers
    5=Administrative Support Workers
    6=Craft Workers
    7=Operatives
    8=Laborers and Helpers
    9=Service Workers
    A=Executive/Sr Level Officials
    B=First/Mid Level Officials
    N=No EEO-1 Reporting
    76 EEO4CODE Character(1) VARCHAR2(1) NOT NULL EEO-4 Job Category
    1=Officials and Administrators
    2=Professionals
    3=Technicians
    4=Protective Service
    5=Paraprofessionals
    6=Office/Clerical
    7=Skilled Craft
    8=Service Maintenance
    N=No EEO-4 Reporting
    77 EEO5CODE Character(2) VARCHAR2(2) NOT NULL EEO-5 Job Category
    1=Officials,Administrators,Mgrs
    10=Librarian/Audiovisual
    11=Consultants/Supv.of Instructn
    12=Other Professional Staff
    13=Teacher Aides
    14=Technicians
    15=Clerical/Secretarial
    16=Service Workers
    17=Craft Workers (Skilled)
    18=Laborers (Unskilled)
    2=Principals
    3=Asst. Principals-Teaching
    4=Asst. Principals-Nonteaching
    5=Elementary Classroom Teachers
    6=Secondary Classroom Teachers
    7=Other Classroom Teachers
    8=Guidance
    9=Psychological
    N=No EEO-5 Reporting
    78 EEO6CODE Character(1) VARCHAR2(1) NOT NULL EEO-6 Job Category
    1=Executive/Admin/Managerial
    2=Faculty
    3=Professional Nonfaculty
    4=Secretarial/Clerical
    5=Technical/Paraprofessional
    6=Skilled Crafts
    7=Service Maintenance Workers
    N=No EEO-6 Reporting
    79 EEO_JOB_GROUP Character(4) VARCHAR2(4) NOT NULL EEO Job Group
    80 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
    81 JOB_KNOWHOW_POINTS Number(5,0) INTEGER NOT NULL Knowhow-Points
    82 JOB_ACCNTAB_POINTS Number(5,0) INTEGER NOT NULL Accountability-Points
    83 JOB_PROBSLV_POINTS Number(5,0) INTEGER NOT NULL Problem-Solving-Points
    84 JOB_POINTS_TOTAL Number(5,0) INTEGER NOT NULL Total Points
    85 JOB_KNOWHOW_PCT Number(5,1) DECIMAL(4,1) NOT NULL Knowhow-Percent
    86 JOB_ACCNTAB_PCT Number(5,1) DECIMAL(4,1) NOT NULL Accountability-Percent
    87 JOB_PROBSLV_PCT Number(5,1) DECIMAL(4,1) NOT NULL Problem-Solving-Percent
    88 DEPTNAME Character(30) VARCHAR2(30) NOT NULL Department
    89 DEPTNAME_ABBRV Character(10) VARCHAR2(10) NOT NULL Short Name
    90 MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager ID

    Prompt Table: PERSON

    91 EEO4_FUNCTION Character(2) VARCHAR2(2) NOT NULL U.S. EEO4 Function
    01=Financial Administration
    02=Streets and Highways
    03=Public Welfare
    04=Police Protection
    05=Fire Protection
    06=Natural Resources
    07=Hospitals & Sanatoriums
    08=Health
    09=Housing
    10=Community Development
    11=Corrections
    12=Utilities & Transportation
    13=Sanitation & Sewage
    14=Employment Security
    15=Other
    99=Not Counted for EEO Reporting
    92 FROMDATE Date(10) DATE From Date
    93 ASOFDATE Date(10) DATE As of Date
    94 IPEDSSCODE Character(1) VARCHAR2(1) NOT NULL IPEDS-S Job Category
    95 ADDS_TO_FTE_ACTUAL Character(1) VARCHAR2(1) NOT NULL Adds to FTE Actual Count
    96 CLASS_INDC Character(1) VARCHAR2(1) NOT NULL Classified/Unclassified Indc
    B=All
    C=Classified
    T=Temporary
    U=Unclassified
    97 FICA_STATUS_EE Character(1) VARCHAR2(1) NOT NULL FICA Status-Employee
    E=Exempt
    H=ER FICA Exmpt
    M=Medicare only
    N=Subject
    98 FTE Number(8,6) DECIMAL(7,6) NOT NULL This field represent Full Time Equivalence
    99 PRORATE_CNT_AMT Character(1) VARCHAR2(1) NOT NULL Prorate Contract Change Amount
    C=Prorate Over Contract Period
    N=No Proration of Change Amt.
    P=Prorate over Payment Period
    100 ENCUMB_OVERRIDE Character(1) VARCHAR2(1) NOT NULL Encumbrance Override
    101 PAY_SYSTEM_FLG Character(2) VARCHAR2(2) NOT NULL Payroll System
    GP=Global Payroll
    NA=Payroll for North America
    OT=Other
    PI=Payroll Interface
    102 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
    103 REG_REGION_COUNTRY Character(3) VARCHAR2(3) NOT NULL Reg Region Country
    104 JOB_INDICATOR Character(1) VARCHAR2(1) NOT NULL This Indicator is used on JOB to specify which employment record is considered to be the primary one for an employee. In case of Multiple Jobs per Employee, this property is being used to make a conscious decision in related features where only 1 Employment Record per Employee needs to be returned.
    N=Not Applicable
    P=Primary Job
    S=Secondary Job
    105 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
    106 WORKERS_COMP_CD Character(4) VARCHAR2(4) NOT NULL Workers' Comp Code