GP_DIAG_EMP_VW(SQL View) |
Index Back |
---|---|
Employment InformationDerivated record to select fields that could be sent by customer to PeopleSoft |
SELECT A.EMPLID ,A.EMPL_RCD ,A.PER_ORG ,A.BENEFIT_RCD_NBR ,A.HOME_HOST_CLASS ,D.LAST_DATE_WORKED ,D.ASGN_START_DT ,D.LST_ASGN_START_DT ,D.ASGN_END_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 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(EFFDT) FROM PS_JOB JOB2 WHERE D.EMPLID = JOB2.EMPLID AND D.EMPL_RCD = JOB2.EMPL_RCD AND (( JOB2.EFFDT <= %CurrentDateIn) OR (JOB2.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(EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = D.EMPLID AND JOB3.EMPL_RCD = D.EMPL_RCD AND JOB3.EFFDT = D.EFFDT ) |
# | 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 Rcd Nbr |
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 | CMPNY_SENIORITY_DT | Date(10) | DATE | Company Seniority Date |
11 | SERVICE_DT | Date(10) | DATE | Service Date |
12 | PROF_EXPERIENCE_DT | Date(10) | DATE | Professional Experience Date |
13 | LAST_VERIFICATN_DT | Date(10) | DATE | Last Verification Date |
14 | EXPECTED_RETURN_DT | Date(10) | DATE | Expected Return Date |
15 | LAST_INCREASE_DT | Date(10) | DATE | Date Last Increase |
16 | 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 |
17 | PROBATION_DT | Date(10) | DATE | Probation Date |
18 | NEE_PROVIDER_ID | Character(10) | VARCHAR2(10) NOT NULL |
The ID for the Non Employee Provider, Agency, or Employer.
Prompt Table: NEE_PROVIDR_TBL |