AFI_JOB_ESP_VW

(SQL View)
 Index Back

AFI Data dates VW

AFI Data dates VW

SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ , A.PER_ORG , A.DEPTID , A.JOBCODE , A.SETID_LOCATION , A.LOCATION , A.COMPANY , A.DEPTID , A.MATRICULA_NBR , A.SOC_SEC_RISK_CODE , A.LABOR_AGREEMENT , A.EMPL_CTG , A.EMPL_CTG_L1 , A.ESTABID , A.HIRE_DT , A.TERMINATION_DT , B.SSN_EMPLOYER , A.ACTION , A.ACTION_REASON , C.AFI_SS_ACTION_ESP , C.AFI_FILE_NAME_ESP , C.AFI_REPORTBY_ESP FROM PS_JOB A , PS_JOB_JR B , PS_AFI_DATA_ESP C WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ AND C.EMPLID = B.EMPLID AND C.EMPL_RCD = B.EMPL_RCD AND A.EFFDT = ( SELECT MAX(ED.EFFDT) FROM PS_JOB ED WHERE ED.EMPLID = C.EMPLID AND ED.EMPL_RCD = C.EMPL_RCD AND ED.EFFDT <= C.EFFDT)

# 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 Record
3 EFFDT Date(10) DATE Effective Date
4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 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
6 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
7 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
8 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
9 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
10 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
11 DEPARTMENT Character(10) VARCHAR2(10) NOT NULL Department
12 MATRICULA_NBR Number(5,0) INTEGER NOT NULL Matricula Number
13 SOC_SEC_RISK_CODE Character(3) VARCHAR2(3) NOT NULL Social Security Risk Code
14 LABOR_AGREEMENT Character(6) VARCHAR2(6) NOT NULL Labor Agreement
15 EMPL_CTG Character(6) VARCHAR2(6) NOT NULL Employee Category
16 EMPL_CTG_L1 Character(6) VARCHAR2(6) NOT NULL Employee Subcategory
17 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
18 HIRE_DT Date(10) DATE First Start Date
19 TERMINATION_DT Date(10) DATE Termination Date
20 SSN_EMPLOYER Character(13) VARCHAR2(13) NOT NULL Employer Social Security number
21 ACTION Character(3) VARCHAR2(3) NOT NULL Action

Prompt Table: ACTION_TBL

22 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code

Prompt Table: ACTN_REASON_TBL

23 AFI_SS_ACTION_ESP Character(3) VARCHAR2(3) NOT NULL Actions to report to the Social Security through the affiliation report
24 AFI_FILE_NAME_ESP Character(20) VARCHAR2(20) NOT NULL Social Security affiliation report file name
25 AFI_REPORTBY_ESP Character(1) VARCHAR2(1) NOT NULL Node Status
0=AFI File
1=On Line