GPES_SSTC_EDL

(SQL View)
 Index Back

TCs Generation View

TCs Generation View

SELECT DISTINCT N.COMPANY , D.SSN_EMPLOYER , D.GPES_YEAR , D.GPES_MONTH , D.GPES_SSTC_PAYMNT , D.EMPLID , D.EMPL_RCD , MAX(F.NATIONAL_ID) , D.SOC_SEC_RISK_CODE , D.GPES_SEC_RISK_CODE , D.CONTRACT_TYPE , D.GPES_SSTC_MNG_OPT , D.SOC_SEC_WRK_GRP_CD , D.SLICE_BGN_DT , D.GPES_YEAR_RECALC , D.GPES_MONTH_RECALC , MAX(F.LAST_NAME) , MAX(F.SECOND_LAST_NAME) , MAX(F.FIRST_NAME) , SUM(D.GPES_SSTC_DAYS_HRS) , D.GPES_SSTC_STRIKE , D.GPES_SSTC_MULTIJOB , D.GPES_SSTC_MONTHLY , D.GPES_SSTC_LGL_CUST , D.GPES_SSTC_NOSALARY , D.GPES_SSTC_OTHERS , D.GPES_SSTC_MT , D.GPES_SSTC_CONTR_7D , D.GPES_SSTC_SPC_CNTB , D.GPES_SSTC_ELEMENT , D.GPES_SSTC_ELEM_NUM , SUM(D.GPES_SSTC_AMOUNT) , MAX(D.GPES_SSTC_PCT_ENT) , MAX(D.GPES_SSTC_PCT_DEC) , SUM(D.GPES_SSTC_CT_EE) , SUM(D.GPES_SSTC_CT_ER) , MAX(D.GPES_SSTC_RES_TYPE) , MAX(D.GPES_SSTC_RESLN_DT) , MIN(D.GPES_SSTC_BEGIN_DT) FROM PS_GPES_SSTC_RSLT D , PS_JOB N , PS_GPES_NID_SSN_VW F WHERE N.EMPLID = D.EMPLID AND N.EMPL_RCD = D.EMPL_RCD AND N.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = N.EMPLID AND EMPL_RCD = N.EMPL_RCD AND EFFDT <= ( SELECT MAX(SEG_END_DT) FROM PS_GP_PYE_SEG_STAT WHERE EMPLID = D.EMPLID AND CAL_RUN_ID = D.CAL_RUN_ID AND EMPL_RCD = D.EMPL_RCD AND CAL_ID = D.CAL_ID AND GP_PAYGROUP = D.GP_PAYGROUP AND ORIG_CAL_RUN_ID = D.ORIG_CAL_RUN_ID AND RSLT_SEG_NUM = D.RSLT_SEG_NUM AND SEG_BGN_DT <= D.SLICE_BGN_DT)) AND N.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = N.EMPLID AND EMPL_RCD = N.EMPL_RCD AND EFFDT = N.EFFDT) AND D.EMPLID = F.EMPLID AND %Sql(GPES_SS_MAX_VER_REV_NUM, PS_GP_PYE_PRC_STAT, D) GROUP BY N.COMPANY, D.SSN_EMPLOYER, D.GPES_YEAR, D.GPES_MONTH, D.GPES_YEAR_RECALC, D.GPES_MONTH_RECALC, D.GPES_SSTC_PAYMNT, D.EMPLID, D.EMPL_RCD, D.GPES_SSTC_MNG_OPT, D.SOC_SEC_RISK_CODE, D.GPES_SEC_RISK_CODE, D.CONTRACT_TYPE, D.SOC_SEC_WRK_GRP_CD, D.SLICE_BGN_DT, D.GPES_SSTC_STRIKE, D.GPES_SSTC_MULTIJOB, D.GPES_SSTC_MONTHLY, D.GPES_SSTC_LGL_CUST, D.GPES_SSTC_NOSALARY, D.GPES_SSTC_OTHERS, D.GPES_SSTC_MT, D.GPES_SSTC_CONTR_7D, D.GPES_SSTC_SPC_CNTB, D.GPES_SSTC_ELEMENT, D.GPES_SSTC_ELEM_NUM

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
2 SSN_EMPLOYER Character(13) VARCHAR2(13) NOT NULL Employer Social Security number
3 GPES_YEAR Number(4,0) SMALLINT NOT NULL Year
4 GPES_MONTH Number(2,0) SMALLINT NOT NULL Month
5 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
6 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
7 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
8 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
9 SOC_SEC_RISK_CODE Character(3) VARCHAR2(3) NOT NULL Social Security Risk Code
10 GPES_SEC_RISK_CODE Character(3) VARCHAR2(3) NOT NULL Social Security secondary risk code
11 CONTRACT_TYPE Character(3) VARCHAR2(3) NOT NULL Contract Type
12 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
13 GPES_SS_WRK_GRP_CD Character(2) VARCHAR2(2) NOT NULL Social Security work group. Equivalent to SOC_SEC_WRK_GRP_CD.
14 SLICE_BGN_DT Date(10) DATE Element Slice Begin Date
15 GPES_YEAR_RECALC Number(4,0) SMALLINT NOT NULL Retro year
16 GPES_MONTH_RECALC Number(2,0) SMALLINT NOT NULL Retro month
17 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
18 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
19 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
20 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.
21 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.
22 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.
23 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.
24 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.
25 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.
26 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.
27 GPES_SSTC_MT Character(1) VARCHAR2(1) NOT NULL Maternity Flag
28 GPES_SSTC_CONTR_7D Character(1) VARCHAR2(1) NOT NULL Contracts with duration of less than seven days
29 GPES_SSTC_SPC_CNTB Character(1) VARCHAR2(1) NOT NULL Special contribution
30 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.
31 GPES_SSTC_ELEM_NUM Number(3,0) SMALLINT NOT NULL This field indicates the number of the CD22 elements in FAN file.
32 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.
33 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.
34 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.
35 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.
36 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.
37 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
38 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.
39 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.