EMPLOYMENT

(SQL View)
 Index Back

EE General Employment Data

EMPLOYMENT is a view of the main Job/assignment information. It joins PER_ORG_ASGN, PER_ORG_INST, current JOB, and JOB_DATES. All persons with an EMPL_RCD are returned regardless of their HR_STATUS or EMPL_STATUS. If an EMPLID/EMPL_RCD only has a future dated JOB row, then that row is used instead of the current row.

SELECT A.EMPLID ,A.EMPL_RCD ,A.PER_ORG ,A.BENEFIT_RCD_NBR ,A.HOME_HOST_CLASS ,D.LAST_DATE_WORKED ,D.HIRE_DT ,D.LAST_HIRE_DT ,D.TERMINATION_DT ,A.SENIORITY_PAY_DT ,A.CMPNY_SENIORITY_DT ,A.SERVICE_DT ,A.PROF_EXPERIENCE_DT ,A.LAST_VERIFICATN_DT ,D.EXPECTED_RETURN_DT ,A.LAST_INCREASE_DT ,A.OWN_5PERCENT_CO ,A.PROBATION_DT ,C.NEE_PROVIDER_ID ,A.POSITION_PHONE ,A.BUSINESS_TITLE ,D.REPORTS_TO ,D.SUPERVISOR_ID FROM PS_PER_ORG_ASGN A ,PS_PER_ORG_INST C , PS_JOB D WHERE A.EMPLID = C.EMPLID AND A.ORG_INSTANCE_ERN = C.ORG_INSTANCE_ERN AND A.EMPLID = D.EMPLID AND A.EMPL_RCD = D.EMPL_RCD AND D.EFFDT = ( SELECT MAX(JOB2.EFFDT) FROM PS_JOB JOB2 WHERE D.EMPLID = JOB2.EMPLID AND D.EMPL_RCD = JOB2.EMPL_RCD AND ((JOB2.EFFDT <= %CurrentDateIn) OR (D.EFFDT > %CurrentDateIn AND %CurrentDateIn < ( SELECT MIN(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = D.EMPLID AND J2.EMPL_RCD = D.EMPL_RCD) ) )) AND D.EFFSEQ = ( SELECT MAX(JOB3.EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = D.EMPLID AND JOB3.EMPL_RCD = D.EMPL_RCD AND JOB3.EFFDT = D.EFFDT )

  • Parent record: PER_ORG_ASGN

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Default Value: PERSON.EMPLID

    Prompt Table: PERSON

    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 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
    4 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.
    5 HOME_HOST_CLASS Character(1) VARCHAR2(1) NOT NULL Home/Host Classification
    M=Home
    S=Host

    Default Value: M

    6 LAST_DATE_WORKED Date(10) DATE Last Date Worked
    7 HIRE_DT Date(10) DATE First Start Date
    8 REHIRE_DT Date(10) DATE Rehire Date
    9 TERMINATION_DT Date(10) DATE Termination Date
    10 SENIORITY_PAY_DT Date(10) DATE Seniority Pay Calc Date
    11 CMPNY_SENIORITY_DT Date(10) DATE Company Seniority Date
    12 SERVICE_DT Date(10) DATE Service Date
    13 PROF_EXPERIENCE_DT Date(10) DATE Professional Experience Date
    14 LAST_VERIFICATN_DT Date(10) DATE Last Verification Date
    15 EXPECTED_RETURN_DT Date(10) DATE Expected Return Date
    16 LAST_INCREASE_DT Date(10) DATE Date Last Increase
    17 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

    Default Value: N

    18 PROBATION_DT Date(10) DATE Probation Date
    19 NEE_PROVIDER_ID Character(10) VARCHAR2(10) NOT NULL The ID for the Non Employee Provider, Agency, or Employer.

    Prompt Table: NEE_PROVIDR_TBL

    20 POSITION_PHONE Character(24) VARCHAR2(24) NOT NULL Phone number for the Position
    21 BUSINESS_TITLE Character(30) VARCHAR2(30) NOT NULL Business Title
    22 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
    23 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID