AFI_DATA_SRCH(SQL View) |
Index Back |
|---|---|
Search VW for AFI Data - ESPSearch VW for AFI Data - ESP |
| SELECT DISTINCT %Sql(SCRTY_SEL_PKEY, OPR,SEC) , AFI.EFFDT , AFI.CONTRACT_NUM , AFI.CNT_EVENT_ESP , AFI.EFFSEQ , AFI.CNT_EVENT_ESP , AFI.CNT_EVENT_RSN_ESP , AFI.TIME_STAMP_ESP , AFI.AFI_REPORTBY_ESP , AFI.AFI_NODE_STATS_ESP , AFI.AFI_FILE_NAME_ESP , JOB.DEPTID , JOB.COMPANY , JOB.SSN_EMPLOYER FROM %Sql(SCRTY_PER_FROM) , PS_WKF_CNT_INC_ESP AFI , PS_JOB_CNT_ESP_VW JOB WHERE %Sql(SCRTY_NO_APPT1) AND %Sql(SCRTY_WHERE, 'PPLJOB') AND SEC.EMPLID = JOB.EMPLID AND SEC.EMPL_RCD = JOB.EMPL_RCD AND AFI.EMPLID = JOB.EMPLID AND AFI.CONTRACT_NUM = JOB.CONTRACT_NUM AND JOB.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED WHERE JOB.EMPLID = B_ED.EMPLID AND JOB.EMPL_RCD = B_ED.EMPL_RCD AND B_ED.EFFDT <= AFI.EFFDT) AND JOB.EFFSEQ = ( SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES WHERE JOB.EMPLID = B_ES.EMPLID AND JOB.EMPL_RCD = B_ES.EMPL_RCD AND JOB.EFFDT = B_ES.EFFDT) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 2 | ROWSECCLASS | Character(30) | VARCHAR2(30) NOT NULL | The class used to determine row level security |
| 3 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 4 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 5 | ACTION_DT |
Date(10) | DATE | Action Date |
| 6 | CONTRACT_NUM |
Character(25) | VARCHAR2(25) NOT NULL | Contract Number |
| 7 | AFI_SS_ACTION_ESP | Character(3) | VARCHAR2(3) NOT NULL | Actions to report to the Social Security through the affiliation report |
| 8 | EFFSEQ | Number(3,0) | SMALLINT NOT NULL | Effective Sequence |
| 9 | CNT_EVENT_ESP | Character(3) | VARCHAR2(3) NOT NULL |
Contract Action
001=Affiliation 002=Unregister 003=Change in Contract 004=Erase Previous Data 005=Labor Agreement Communictn 006=Additional Situations 007=Worked Days Registration 008=Suspension Period 009=Additional Bases Communication |
| 10 | CNT_EVENT_RSN_ESP | Character(4) | VARCHAR2(4) NOT NULL | Contract Action |
| 11 | TIME_STAMP_ESP | DateTime(26) | TIMESTAMP | Time Stamp |
| 12 | AFI_REPORTBY_ESP | Character(1) | VARCHAR2(1) NOT NULL |
Node Status
0=AFI File 1=On Line |
| 13 | AFI_NODE_STATS_ESP | Character(1) | VARCHAR2(1) NOT NULL |
Node Status
0=AFI Sent Confirmed 1=Included in AFI 2=Not Communicated |
| 14 | AFI_FILE_NAME_ESP |
Character(20) | VARCHAR2(20) NOT NULL | Social Security affiliation report file name |
| 15 | DEPARTMENT | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 16 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 17 | SSN_EMPLOYER | Character(13) | VARCHAR2(13) NOT NULL | Employer Social Security number |