GPES_SSTC_RSKVW

(SQL View)
 Index Back

TCs Generation CCC, EE & Risk

TCs Generation by CCC, Employee and Risk Code

SELECT SSN_EMPLOYER , GPES_SSTC_PAYMNT , GPES_MONTH , GPES_YEAR , GPES_SSTC_ELEM_GRP , EMPLID , GPES_SSTC_CNTRL_DT , GPES_OOD_PYMT_SUR , SLICE_BGN_DT , MAX(SLICE_END_DT) , SOC_SEC_RISK_CODE , MAX(GPES_SEC_RISK_CODE) , SOC_SEC_WRK_GRP_CD , CONTRACT_TYPE , GPES_MONTH_RECALC , GPES_YEAR_RECALC , GPES_SSTC_STRIKE , GPES_SSTC_MULTIJOB , GPES_SSTC_LGL_CUST , MAX(GPES_SSTC_MONTHLY) , GPES_SSTC_NOSALARY , GPES_SSTC_OTHERS , GPES_SSTC_MT , GPES_SSTC_CONTR_7D , GPES_SSTC_SPC_CNTB , MAX(GPES_SSTC_DAYS) , MAX(CASE WHEN GPES_SSTC_ELEMENT = 'BA01' THEN GPES_SSTC_DAYS_HRS WHEN GPES_SSTC_ELEMENT = 'BA02' THEN GPES_SSTC_DAYS_HRS WHEN GPES_SSTC_ELEMENT = 'BA21' THEN GPES_SSTC_DAYS_HRS WHEN GPES_SSTC_ELEMENT = 'BA22' THEN GPES_SSTC_DAYS_HRS ELSE 0 END) , MAX(GPES_SSTC_HRS_FLAG) , MAX(GPES_SSTC_DEC_PART) , MAX(GPES_SS_BNF_SUBTYP) , MAX(GPES_SSTC_DISABLTY) , GPES_SSTC_RED_PART , MAX(GPES_SSTC_RELSHIP) , MAX(GPES_SSTC_CNT_FLG) FROM PS_GPES_SSTC_EDLVW GROUP BY SSN_EMPLOYER , GPES_SSTC_PAYMNT , GPES_SSTC_CNTRL_DT , GPES_OOD_PYMT_SUR , GPES_MONTH , GPES_YEAR , GPES_SSTC_ELEM_GRP , EMPLID , SLICE_BGN_DT , SOC_SEC_RISK_CODE , SOC_SEC_WRK_GRP_CD , CONTRACT_TYPE , GPES_MONTH_RECALC , GPES_YEAR_RECALC , GPES_SSTC_STRIKE , GPES_SSTC_MULTIJOB , GPES_SSTC_LGL_CUST , GPES_SSTC_NOSALARY , GPES_SSTC_OTHERS , GPES_SSTC_MT , GPES_SSTC_CONTR_7D , GPES_SSTC_SPC_CNTB , GPES_SSTC_RED_PART

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SSN_EMPLOYER Character(13) VARCHAR2(13) NOT NULL Employer Social Security number
2 GPES_SSTC_PAYMNT Character(3) VARCHAR2(3) NOT NULL Payment Type. This field is used to populate the GP Write Array, which is used to generate the FAN File.
L00=Regular
L02=Litigation Salary
L03=Retroactivity wo/Surcharge
L04=Reductions Payback
L09=Retroactivity w/Surcharge
L13=Vacations
L90=Retro w/Surch. Base Incr.
L91=Retro w/Surch. New Slices
V03=Retro wo/ Surcharge Vacations
V90=Retro w/ Surcharge Vacations

Default Value: L00

3 GPES_MONTH Number(2,0) SMALLINT NOT NULL Month
4 GPES_YEAR Number(4,0) SMALLINT NOT NULL Year
5 GPES_SSTC_ELEM_GRP Character(4) VARCHAR2(4) NOT NULL Element Grouping used to separate data into different TC2 payment messages.
CDPG=Company Paid Benefit
CDXX=Reductions
XXXX=Other
6 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
7 GPES_SSTC_CNTRL_DT Date(10) DATE Social Security TCs control date
8 GPES_OOD_PYMT_SUR Number(2,0) SMALLINT NOT NULL FAN surcharge because of an out of date payment
9 SLICE_BGN_DT Date(10) DATE Element Slice Begin Date
10 SLICE_END_DT Date(10) DATE Element Slice End Date
11 SOC_SEC_RISK_CODE Character(3) VARCHAR2(3) NOT NULL Social Security Risk Code
12 GPES_SEC_RISK_CODE Character(3) VARCHAR2(3) NOT NULL Social Security secondary risk code
13 SOC_SEC_WRK_GRP_CD Character(2) VARCHAR2(2) NOT NULL Social Security Work Group
01=01-Engineers
02=02-Technicals
03=03-Administrative
04=04-Assistants
05=05-White Collar
06=06-Subordinates
07=07-Administrative assistants
08=08-1st and 2nd degree
09=09-3rd degree
10=10-Over 18 years
11=11-Under 18 years
14 CONTRACT_TYPE Character(3) VARCHAR2(3) NOT NULL Contract Type
15 GPES_MONTH_RECALC Number(2,0) SMALLINT NOT NULL Retro month
16 GPES_YEAR_RECALC Number(4,0) SMALLINT NOT NULL Retro year
17 GPES_SSTC_STRIKE Character(1) VARCHAR2(1) NOT NULL Strike indicator. This field is used to populate the GP Write Array, which is used to generate the FAN File.
18 GPES_SSTC_MULTIJOB Character(1) VARCHAR2(1) NOT NULL Multijob indicator. This field is used to populate the GP Write Array, which is used to generate the FAN File.
19 GPES_SSTC_LGL_CUST Character(1) VARCHAR2(1) NOT NULL Legal custody indicator. This field is used to populate the GP Write Array, which is used to generate the FAN File.
20 GPES_SSTC_MONTHLY Character(1) VARCHAR2(1) NOT NULL Monthly indicator. This field is used to populate the GP Write Array, which is used to generate the FAN File.
21 GPES_SSTC_NOSALARY Character(1) VARCHAR2(1) NOT NULL No salary. This field is used to populate the GP Write Array, which is used to generate the FAN File.
22 GPES_SSTC_OTHERS Character(1) VARCHAR2(1) NOT NULL Others flag. This field is used to populate the GP Write Array, which is used to generate the FAN File.
23 GPES_SSTC_MT Character(1) VARCHAR2(1) NOT NULL Maternity Flag
24 GPES_SSTC_CONTR_7D Character(1) VARCHAR2(1) NOT NULL Contracts with duration of less than seven days
25 GPES_SSTC_SPC_CNTB Character(1) VARCHAR2(1) NOT NULL Special contribution
26 GPES_SSTC_DAYS Number(4,0) SMALLINT NOT NULL Days in which EE has been. This field is used to populate the GP Write Array, which is used to generate the FAN File.
27 GPES_SSTC_DAYS_HRS Signed Number(4,0) DECIMAL(3) NOT NULL Amount of Days or Hours (depending on Hours Flag). This field is used to populate the GP Write Array, which is used to generate the FAN File.
28 GPES_SSTC_HRS_FLAG Character(1) VARCHAR2(1) NOT NULL Hour indicator. In case of hours, this field should be populated with 'H'. This field is used to populate the GP Write Array, which is used to generate the FAN File.
29 GPES_SSTC_DEC_PART Signed Number(3,0) DECIMAL(2) NOT NULL Amount of the element (Decimal part). This field is used to populate the GP Write Array, which is used to generate the FAN File.
30 GPES_SS_BNF_SUBTYP Character(4) VARCHAR2(4) NOT NULL Bonification/Reduction Subtype
31 GPES_SSTC_DISABLTY Character(1) VARCHAR2(1) NOT NULL Disability Type
32 GPES_SSTC_RED_PART Character(1) VARCHAR2(1) NOT NULL Part Time Reduction Type
0=Total Amount
1=Minimum Reduction
2=Medium Reduction
3=Maximum Reduction
33 GPES_SSTC_RELSHIP Character(4) VARCHAR2(4) NOT NULL Labor Relationship
0100=Senior Management
0409=Professional Sportsman
0900=Lawyer in Lawyer Bureau
9909=Research Staff
34 GPES_SSTC_CNT_FLG Character(1) VARCHAR2(1) NOT NULL Original contract flag for contract type 540 corresponding to pre-retired people
D=Contract of Limited duration
I=Unlimited Contract