|
SELECT EMPLID ,CAL_RUN_ID ,EMPL_RCD ,GP_PAYGROUP ,CAL_ID ,ORIG_CAL_RUN_ID ,RSLT_SEG_NUM ,SLICE_BGN_DT ,SLICE_END_DT ,GPES_SSTC_PAYMNT ,SSN_EMPLOYER ,SOC_SEC_RISK_CODE ,GPES_SEC_RISK_CODE ,GPES_SSTC_ELEMENT ,GPES_SSTC_ELEM_NUM ,(CASE WHEN GPES_SSTC_PAYMNT = 'L04' THEN (CASE WHEN GPES_SSTC_ELEMENT = 'CD01' THEN 'CDpg' WHEN GPES_SSTC_ELEMENT = 'CD03' THEN 'CDpg' ELSE 'CDxx' END) ELSE 'XXxx' END) ,SOC_SEC_WRK_GRP_CD ,CONTRACT_TYPE ,CONTRACT_NUM ,GPES_SSTC_CNT_FLG ,GPES_YEAR ,GPES_MONTH ,GPES_YEAR_RECALC ,GPES_MONTH_RECALC ,GPES_SSTC_STRIKE ,GPES_SSTC_MULTIJOB ,GPES_SSTC_MONTHLY ,GPES_SSTC_LGL_CUST ,GPES_SSTC_NOSALARY ,GPES_SSTC_OTHERS ,GPES_SSTC_MT ,GPES_SSTC_CONTR_7D ,GPES_SSTC_SPC_CNTB ,GPES_SSTC_DAYS_HRS ,GPES_SSTC_HRS_FLAG ,GPES_SSTC_DEC_PART ,GPES_SSTC_DAYS ,GPES_SSTC_AMOUNT ,GPES_SSTC_SIGN ,GPES_SSTC_RES_TYPE ,GPES_SSTC_RESLN_DT ,GPES_SSTC_BEGIN_DT ,GPES_SSTC_END_DT ,GPES_SS_AUTH_NBR ,GPES_SSTC_PCT_FLG ,GPES_SSTC_PCT_DEC ,GPES_SSTC_PCT_ENT ,GPES_SSTC_CT_EE ,GPES_SSTC_CT_ER ,GPES_SSTC_TC2GEN ,GPES_CHK_SEND ,RSLT_VER_NUM ,RSLT_REV_NUM ,GPES_SS_BNF_SUBTYP ,GPES_SSTC_DISABLTY ,GPES_SSTC_RED_PART ,GPES_SSTC_RELSHIP ,GPES_SSTC_EXCL_FAN ,GPES_SSTC_CNTRL_DT ,GPES_OOD_PYMT_SUR ,GPES_SSTC_MNG_OPT FROM PS_GPES_SSTC_RSLT D WHERE %Sql(GPES_SS_MAX_VER_REV_NUM, PS_GP_PYE_PRC_STAT, D)
|
| # |
PeopleSoft Field Name |
PeopleSoft Field Type |
Database Column Type |
Description |
| 1 |
 EMPLID |
Character(11) |
VARCHAR2(11) NOT NULL |
Employee ID
|
| 2 |
CAL_RUN_ID |
Character(18) |
VARCHAR2(18) NOT NULL |
Calendar Run Id
|
| 3 |
EMPL_RCD |
Number(3,0) |
SMALLINT NOT NULL |
Empl Rcd Nbr
|
| 4 |
GP_PAYGROUP |
Character(10) |
VARCHAR2(10) NOT NULL |
Global Payroll pay group
|
| 5 |
CAL_ID |
Character(18) |
VARCHAR2(18) NOT NULL |
calendar id
|
| 6 |
ORIG_CAL_RUN_ID |
Character(18) |
VARCHAR2(18) NOT NULL |
Original Calendar Group ID
|
| 7 |
RSLT_SEG_NUM |
Number(4,0) |
SMALLINT NOT NULL |
Result Segment Number
|
| 8 |
SLICE_BGN_DT |
Date(10) |
DATE |
Element Slice Begin Date
|
| 9 |
SLICE_END_DT |
Date(10) |
DATE |
Element Slice End Date
|
| 10 |
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/Surcharge Bases Increa
L91=Retro w/Surchage new Slices
|
| 11 |
SSN_EMPLOYER |
Character(13) |
VARCHAR2(13) NOT NULL |
Employer Social Security number
|
| 12 |
SOC_SEC_RISK_CODE |
Character(3) |
VARCHAR2(3) NOT NULL |
Occupation Code
|
| 13 |
GPES_SEC_RISK_CODE |
Character(3) |
VARCHAR2(3) NOT NULL |
Social Security secondary risk code
|
| 14 |
GPES_SSTC_ELEMENT |
Character(4) |
VARCHAR2(4) NOT NULL |
This code indicates if the element represents a base or a deduction. This field is used to populate the GP Write Array, which is used to generate the FAN File.
|
| 15 |
GPES_SSTC_ELEM_NUM |
Number(3,0) |
SMALLINT NOT NULL |
This field indicates the number of the CD22 elements in FAN file.
|
| 16 |
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
|
| 17 |
SOC_SEC_WRK_GRP_CD |
Character(2) |
VARCHAR2(2) NOT NULL |
Social Security Work Group
|
| 18 |
CONTRACT_TYPE |
Character(3) |
VARCHAR2(3) NOT NULL |
Contract Type
|
| 19 |
CONTRACT_NUM |
Character(25) |
VARCHAR2(25) NOT NULL |
Contract Number
|
| 20 |
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
|
| 21 |
GPES_YEAR |
Number(4,0) |
SMALLINT NOT NULL |
Year
|
| 22 |
GPES_MONTH |
Number(2,0) |
SMALLINT NOT NULL |
Month
|
| 23 |
GPES_YEAR_RECALC |
Number(4,0) |
SMALLINT NOT NULL |
Retro year
|
| 24 |
GPES_MONTH_RECALC |
Number(2,0) |
SMALLINT NOT NULL |
Retro month
|
| 25 |
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.
|
| 26 |
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.
|
| 27 |
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.
|
| 28 |
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.
|
| 29 |
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.
|
| 30 |
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.
|
| 31 |
GPES_SSTC_MT |
Character(1) |
VARCHAR2(1) NOT NULL |
Maternity Flag
|
| 32 |
GPES_SSTC_CONTR_7D |
Character(1) |
VARCHAR2(1) NOT NULL |
Contracts with duration of less than seven days
|
| 33 |
GPES_SSTC_SPC_CNTB |
Character(1) |
VARCHAR2(1) NOT NULL |
Special contribution
|
| 34 |
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.
|
| 35 |
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.
|
| 36 |
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.
|
| 37 |
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.
|
| 38 |
GPES_SSTC_AMOUNT |
Signed Number(11,2) |
DECIMAL(9,2) NOT NULL |
Amount of the element.(Entire part). This field is used to populate the GP Write Array, which is used to generate the FAN File.
|
| 39 |
GPES_SSTC_SIGN |
Character(1) |
VARCHAR2(1) NOT NULL |
Contribution sign. In case of a negative amount, this field should be populated with '-' This field is used to populate the GP Write Array, which is used to generate the FAN File.
|
| 40 |
GPES_SSTC_RES_TYPE |
Character(2) |
VARCHAR2(2) NOT NULL |
Type or resolution. This field is used to populate the GP Write Array, which is used to generate the FAN File.
1=IT
10=Multiple-Employment
11=Structural Overtime
2=Reduction
3=Bonification
6=Partial Unemployment
|
| 41 |
GPES_SSTC_RESLN_DT |
Date(10) |
DATE |
Resolution Date in case of Reduction/MultiJob. This field is used to populate the GP Write Array, which is used to generate the FAN File.
|
| 42 |
GPES_SSTC_BEGIN_DT |
Date(10) |
DATE |
Start Date for employee situations. This field is used to populate the GP Write Array, which is used to generate the FAN File.
|
| 43 |
GPES_SSTC_END_DT |
Date(10) |
DATE |
End date for employee situations. This field is used to populate the GP Write Array, which is used to generate the FAN File.
|
| 44 |
GPES_SS_AUTH_NBR |
Character(17) |
VARCHAR2(17) NOT NULL |
Social Security authorization number
|
| 45 |
GPES_SSTC_PCT_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
Decimal part indicator. This field is used to populate the GP Write Array, which is used to generate the FAN File.
|
| 46 |
GPES_SSTC_PCT_DEC |
Number(6,0) |
INTEGER NOT NULL |
Social Security Employee Percentage.
This field houses the percentage that has to be aplied to a particular base for contibuting to the Social Security. This information is from the Employee perspective.
|
| 47 |
GPES_SSTC_PCT_ENT |
Number(6,0) |
INTEGER NOT NULL |
Social Security Employee Percentage.
This field houses the percentage that has to be aplied to a particular base for contibuting to the Social Security. This information is from the Employee perspective.
|
| 48 |
GPES_SSTC_CT_EE |
Signed Number(17,2) |
DECIMAL(15,2) NOT NULL |
Employee Social Security Contributions. This field is used to populate the GP Write Array, which is used to generate the FAN File.
|
| 49 |
GPES_SSTC_CT_ER |
Signed Number(15,2) |
DECIMAL(13,2) NOT NULL |
Employer Social Security Contributions. This field is used to populate the GP Write Array, which is used to generate the FAN File.
|
| 50 |
GPES_SSTC_TC2GEN |
Character(1) |
VARCHAR2(1) NOT NULL |
FAN File generated indicator. This field is used to control the FAN File generation.
|
| 51 |
GPES_CHK_SEND |
Character(1) |
VARCHAR2(1) NOT NULL |
Sent
|
| 52 |
RSLT_VER_NUM |
Number(4,0) |
SMALLINT NOT NULL |
Result Version Number
|
| 53 |
RSLT_REV_NUM |
Number(4,0) |
SMALLINT NOT NULL |
Result Revision Number
|
| 54 |
GPES_SS_BNF_SUBTYP |
Character(4) |
VARCHAR2(4) NOT NULL |
Bonification/Reduction Subtype
|
| 55 |
GPES_SSTC_DISABLTY |
Character(1) |
VARCHAR2(1) NOT NULL |
Disability Type
|
| 56 |
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
|
| 57 |
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
|
| 58 |
GPES_SSTC_EXCL_FAN |
Character(2) |
VARCHAR2(2) NOT NULL |
Exclude row from processing flag.
-2=Negative Base Only
00=Processed
01=Overdue Payment
02=Negative Base Full EE
03=Bases Value Zero
04=Negative Contributions
05=CD 1d Abs. in 31d month
06=DAT with CDs but no BAs
07=Negative CD (negative base)
Default Value: 00
|
| 59 |
GPES_SSTC_CNTRL_DT |
Date(10) |
DATE |
Social Security TC�s control date
|
| 60 |
GPES_OOD_PYMT_SUR |
Number(2,0) |
SMALLINT NOT NULL |
FAN surcharge because of an out of date payment
|
| 61 |
GPES_SSTC_MNG_OPT |
Character(1) |
VARCHAR2(1) NOT NULL |
Option to show included/excluded rows.
C=Prepared
M=Manually Managed
N=Not Managed
P=System Processed
|