GPES_PP_INC_VW(SQL View) |
Index Back |
|---|---|
Affiliation Events ESPAffiliation Events during contract period |
| SELECT A1.COMPANY , A1.GPES_YEAR , A1.GPES_MONTH , A1.EMPLID , J.CONTRACT_NUM , %Sql(FUNCLIB_HR_DATE_BUILD, %SQL(FUNCLIB_HRS_DATE_YEAR, A1.PAY_END_DT), %SQL(FUNCLIB_HRS_DATE_MONTH, A1.PAY_END_DT), '01') , %Coalesce( (SELECT MAX(W.EFFSEQ) + 1 FROM PS_WKF_CNT_INC_ESP W WHERE W.EMPLID = A1.EMPLID AND W.CONTRACT_NUM = J.CONTRACT_NUM AND W.EFFDT = %Sql(FUNCLIB_HR_DATE_BUILD, %SQL(FUNCLIB_HRS_DATE_YEAR, A1.PAY_END_DT), %SQL(FUNCLIB_HRS_DATE_MONTH, A1.PAY_END_DT), '01') ) , 0 ) , '006' , '0067' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , %DateNull , 0 , ' ' , %DateNull , %DateNull , 'P' , '0' , %CurrentDateTimeIn , ' ' , '2' , ' ' , 0 , ' ' , ' ' , 0 , ' ' , %DateNull , ' ' , %DateNull , ' ' , %DateNull , ' ' , 0 , %DateNull , %DateNull , ' ' , ' ' , %DateNull , ' ' , ' ' , %DateNull , %DateNull , 0 , 'ASA' , ' ' , %DateNull , %DateNull , ' ' , 0 , 0 , %DateNull , %DateNull , %DateNull , %DateNull , ' ' , ' ' , ' ' , A1.GPES_PP_FUND_PRVDR , A1.GPES_SALARY_INCOME , A1.GPES_PP_REDCTN_OPT , NULL FROM ( SELECT A.COMPANY , J.SSN_EMPLOYER , A.GPES_YEAR , A.GPES_MONTH , A.EMPLID , A.EMPL_RCD , MAX(A.PAY_END_DT) PAY_END_DT , A.GPES_PP_FUND_PRVDR , SUM(A.GPES_SALARY_INCOME) GPES_SALARY_INCOME , A.GPES_PP_REDCTN_OPT FROM PS_GPES_PP_RSLT A , PS_JOB_JR J WHERE J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT <= A.PAY_END_DT) AND J.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND EFFDT = J.EFFDT) AND %Sql(GPES_TX_MAX_VER_REV_NUM, PS_GPES_PP_RSLT, A) GROUP BY A.COMPANY, J.SSN_EMPLOYER, A.EMPLID, A.EMPL_RCD, A.GPES_YEAR, A.GPES_MONTH, A.GPES_PP_FUND_PRVDR, A.GPES_PP_REDCTN_OPT ) A1 , PS_JOB J , PS_JOB_JR JR WHERE J.EMPLID = JR.EMPLID AND J.EMPL_RCD = JR.EMPL_RCD AND J.EFFDT = JR.EFFDT AND J.EFFSEQ = JR.EFFSEQ AND J.EMPLID = A1.EMPLID AND J.EMPL_RCD = A1.EMPL_RCD AND J.EFFDT = ( SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <= A1.PAY_END_DT) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT = J.EFFDT) AND JR.SSN_EMPLOYER = A1.SSN_EMPLOYER |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 2 | GPES_YEAR | Number(4,0) | SMALLINT NOT NULL | Year |
| 3 | GPES_MONTH | Number(2,0) | SMALLINT NOT NULL | Month |
| 4 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: PERSON |
| 5 | CONTRACT_NUM | Character(25) | VARCHAR2(25) NOT NULL | Contract Number |
| 6 | EFFDT | Date(10) | DATE | Effective Date |
| 7 | EFFSEQ | Number(3,0) | SMALLINT NOT NULL | Effective Sequence |
| 8 | 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 |
| 9 | CNT_EVENT_RSN_ESP | Character(4) | VARCHAR2(4) NOT NULL | Contract Action |
| 10 | REASON | Character(30) | VARCHAR2(30) NOT NULL | Reason |
| 11 | INACTIVITY_TYP_ESP | Character(2) | VARCHAR2(2) NOT NULL | Inactivity Type |
| 12 | INACTIVITY_TY2_ESP | Character(2) | VARCHAR2(2) NOT NULL |
Inactivity Type 2
A1=SUS ERE ETOP23 RDL8 NO JUNE 30 A2=RED ERE ETOP23 RDL8 NO JUNE 30 A3=TOT ACT AFTER ETOP23 RDL8 A4=PAR ACT AFTER ETOP23 RDL8 A5=TOT SUS AFTER A3/A4 A6=PAR SUS AFTER A3/A4 A7=SUS ERE ETOP L24/20 > ERE FM A8=RED ERE ETOP L24/20 > ERE FM A9=TOT ACT AFTER ERE ETOP>ERE FM B1=PAR ACT AFTER ERE ETOP>ERE FM B2=TOT SUS AFTER A9/B1 B3=PAR SUS AFTER A9/B1 B4=SUS ERE FM NEW RESTRICTIONS B5=RED ERE FM NEW RESTRICTIONS B6=TOT ACT AFTER ERE FM NEW RESTR B7=PAR ACT AFTER ERE FM NEW RESTR B8=TOT SUS AFTER B6/B7 B9=PAR SUS AFTER B6/B7 |
| 13 | PART_STRK_ACT_ESP | Character(3) | VARCHAR2(3) NOT NULL | Partial Strike Activity Coeff. |
| 14 | SSN_EMPLOYER | Character(13) | VARCHAR2(13) NOT NULL |
Employer Social Security number
Prompt Table: SOCS_CCC_ESP_VW |
| 15 | JOB_CHANGE_RSN_ESP | Character(2) | VARCHAR2(2) NOT NULL |
Social Security Job Change Reasons. This info is needed for AFI File.
Valid Values
01 due Risk in Pregnancy/Brestfeeding
02 due Professional Illness
01=due Risk Pregnancy/Brestfeedng 02=due Professional Illness 03=Worker from ETT |
| 16 | VACN_END_DT_ESP | Date(10) | DATE | Vacation Not Taken Days |
| 17 | VACN_NOT_TAKEN_ESP | Number(2,0) | SMALLINT NOT NULL | Vacation Not Taken Days |
| 18 | LEGAL_CUSTODY_ESP | Character(3) | VARCHAR2(3) NOT NULL |
Social Security Legal Custody
000=None 016=Legal Custody 017=Legal Custody due Disability 018=Legal Custody due Relatives 019=Sched Red due Breastfeeding 020=Sched Red due Violence 025=Sched Red due Premature Baby 026=Sched Red due Underage Illness 027=Red Underage Illness + Other Default Value: 000 |
| 19 | END_DT | Date(10) | DATE | end date |
| 20 | BGN_DT | Date(10) | DATE | Begin Date |
| 21 | AFI_DATASOURCE_ESP | Character(1) | VARCHAR2(1) NOT NULL |
Node Status
0=Other C=Contract J=Job Data Message P=Pension Plan Default Value: 0 |
| 22 | AFI_REPORTBY_ESP | Character(1) | VARCHAR2(1) NOT NULL |
Node Status
0=AFI File 1=On Line Default Value: 0 |
| 23 | TIME_STAMP_ESP | DateTime(26) | TIMESTAMP | Time Stamp |
| 24 | AFI_FILE_NAME_ESP | Character(20) | VARCHAR2(20) NOT NULL | Social Security affiliation report file name |
| 25 | AFI_NODE_STATS_ESP | Character(1) | VARCHAR2(1) NOT NULL |
Node Status
0=AFI Sent Confirmed 1=Included in AFI 2=Not Communicated Default Value: 2 |
| 26 | SOCS_LBR_AGRMT_ESP | Character(14) | VARCHAR2(14) NOT NULL |
Social Security Labor Agreement.
Special codification following Social Security tables.
This info is needed for AFI File.
Prompt Table: SOCS_AGRMNT_ESP |
| 27 | AFI_WORK_DAYS_ESP | Number(3,0) | SMALLINT NOT NULL | Social Security Part Time Worked Days. Part Time employees must report the number of worked days per month. This info is needed for AFI File. |
| 28 | SOCS_TERM_RSN_ESP | Character(2) | VARCHAR2(2) NOT NULL |
AFI Termination Reason
51=Voluntary Termination 54=Non Voluntary Termination 56=Termination by Death 58=Termination by Retirement 61=Termination by Military Serv 63=Termination by Voluntary Leave 65=Termination by End of IT 67=Termination by Seasonal Layoff 68=Termination by Maternity 73=Termination by Dependent Care 74=Other Term. by Suspension 76=Term by Domestic Violence 80=Susp by Domestic Violence 89=Termination due CCC change 91=TER Objective Reasons. Worker 92=TER Objective Reasons. Company 93=TER END of Contract 94=Inactivity in Permanent Interm 99=Other Termination Reasons |
| 29 | SCHED_RDTN_RSN_ESP | Character(3) | VARCHAR2(3) NOT NULL |
Social Security Schedule Reduction Reason - OLD FIELD
000=None 019=Sched Red due Breastfeeding 020=Sched Red due Violence 025=Sched Red due Premature Baby |
| 30 | PART_TIME_PERC_ESP | Number(3,0) | SMALLINT NOT NULL | Percentage of Part Time |
| 31 | ERE_ID_ESP | Character(10) | VARCHAR2(10) NOT NULL |
ERE ID
Prompt Table: ERE_DATA_ESP |
| 32 | NOTIFCATN_DT_ESP | Date(10) | DATE | Vacation Not Taken Days |
| 33 | READMISSION_ESP | Character(1) | VARCHAR2(1) NOT NULL |
Re-Joined Disabled Employee
Y/N Table Edit |
| 34 | LEAVE_BGN_DT_ESP | Date(10) | DATE | Vacation Not Taken Days |
| 35 | LEAVE_CCC_ESP | Character(13) | VARCHAR2(13) NOT NULL |
Employer Social Security number
Prompt Table: SOCS_CCC_ESP_VW |
| 36 | ACTION_DT | Date(10) | DATE | Action Date |
| 37 | SCHD_RED_RSN_ESP | Character(3) | VARCHAR2(3) NOT NULL |
Schedule Reduction Reason
000=None 001=Care Child 002=Care Disabled 003=Family Care 004=Breastfeeding 005=Gender Violence 006=Premature Child Birth 007=Care Child with serious illnes 008=Care Child w Illness + Oth Red 009=Partial Reduction COVID-19 010=Total Reduction COVID-19 |
| 38 | RED_PCT_INI_ESP | Number(3,0) | SMALLINT NOT NULL | Percentage of Part Time |
| 39 | ABS_BGN_DT | Date(10) | DATE | Absence Begin Date |
| 40 | ABS_END_DT | Date(10) | DATE | Absence End Date |
| 41 | PIT_PRST_TYPE_ESP | Character(2) | VARCHAR2(2) NOT NULL |
Eligibility Youth Support for SS
IT=Temp Disability SI=Risk in Pregnancy SL=Risk in Lactancy SM=Maternity SP=Paternity |
| 42 | PIT_REASON_ESP | Character(1) | VARCHAR2(1) NOT NULL |
Period IT Reason
1=Common Disease 2=Accident 3=Accident at Work 4=Occupatnl Disease 5=Observtn Period OD |
| 43 | PIT_ECO_EFF_DT_ESP | Date(10) | DATE | Period IT Reason |
| 44 | PIT_MAPA_RSN_ESP | Character(1) | VARCHAR2(1) NOT NULL |
Period IT Maternity Reason
1=Maternity/Childbirth 2=Multiple Birth 3=Death of Mother 4=Adoption 5=Placement 6=Other Progenitor |
| 45 | PIT_PAY_TYPE_ESP | Character(1) | VARCHAR2(1) NOT NULL |
Period IT Pay Type
1=Direct Pay 3=Colaborate Ent Pay 4=Benefit Excluded 8=Other 9=No Benefit Rights |
| 46 | PIT_ATEP_DT_ESP | Date(10) | DATE |
Period IT Reason
1=Common Disease 2=Accident 3=Accident at Work 4=Occupational Disease 5=Observation Period Occ Disease |
| 47 | PIT_PRST_ENDDT_ESP | Date(10) | DATE | Period IT Reason |
| 48 | ERE_RED_PERC_ESP | Number(3,0) | SMALLINT NOT NULL | Percentage of Temporary ERE reduction |
| 49 | AFI_SS_ACTION_ESP | Character(3) | VARCHAR2(3) NOT NULL | Actions to report to the Social Security through the affiliation report |
| 50 | AFI_EE_STATUS_ESP | Character(2) | VARCHAR2(2) NOT NULL |
Social Security AFI Employee Status
01=Hire 51=Voluntary / Resignation 53=Individual Disciplinary Dismis 54=Non Voluntary Termination 55=Termination by Takeover 56=Termination by Death 58=Termination by Retirement 61=Termination by Military Serv 63=Termination by Voluntary Leave 65=Termination by End of IT 67=Termination by Seasonal Layoff 68=Termination by Maternity 69=Termination by ERE Suspension 73=Termination by Dependent Care 74=Other Term. by Suspension 76=Term by Domestic Violence 77=Mass dismissal 80=Susp by Domestic Violence 85=End of Test Period 91=TER Objective Reasons. Worker 92=TER Objective Reason. Company 93=TER End of Contract 94=Inactivity in Permanent Interm 99=Other Termination Reasons |
| 51 | NEW_BGN_DT_ESP | Date(10) | DATE | New Begin Date |
| 52 | NEW_END_DT_ESP | Date(10) | DATE | New End Date |
| 53 | DBA_INFO_TYPE_ESP | Character(3) | VARCHAR2(3) NOT NULL |
Additional Bases Communication Type
001=Yearly Data 002=Activity Periods |
| 54 | HRS_PER_YEAR | Number(7,2) | DECIMAL(6,2) NOT NULL | Hours per year |
| 55 | COMPRATE | Number(19,6) | DECIMAL(18,6) NOT NULL | Compensation Rate |
| 56 | CNT_END_DT_ESP | Date(10) | DATE | Contract End Date |
| 57 | COMUNICATN_DT_ESP | Date(10) | DATE | Vacation Not Taken Days |
| 58 | ACT_BGN_DT_ESP | Date(10) | DATE | Activity Period Begin Date |
| 59 | ACT_END_DT_ESP | Date(10) | DATE | Activity Period End Date |
| 60 | AFI_ASA_IND_ESP | Character(3) | VARCHAR2(3) NOT NULL |
ASA Types for AFI
001=Vacation 015=Paid Vacation 150=No International Institution 151=No Ambit Int. Inst. 152=Exceeded Max Period 153=Denied Extension 154=Int Inst no Displacement |
| 61 | AFI_ERTE_EXEMP_ESP | Character(1) | VARCHAR2(1) NOT NULL |
Disability Type
Y/N Table Edit |
| 62 | AFI_TRN_OFFER_ESP | Character(1) | VARCHAR2(1) NOT NULL |
Training Offered
1=Offered 2=Not Offered Y/N Table Edit |
| 63 | PP_FUND_PRVDR_ESP | Character(5) | VARCHAR2(5) NOT NULL |
Pension plan provider
Prompt Table: %EDITTABLE2 |
| 64 | PP_CNTB_AMOUNT_ESP | Number(18,2) | DECIMAL(17,2) NOT NULL | Pension plan contribution amount |
| 65 | GPES_PP_REDCTN_OPT | Character(1) | VARCHAR2(1) NOT NULL | RDL 12/22 pension plan reducion flag |
| 66 | COMMENTS | Long Character | CLOB | Comment |