GPES_CRT_PRD3VW

(SQL View)
 Index Back

CRT Liquidation Periods VW

Liquidation Periods View for Creta Reporting. Same view as GPES_CRT_PRD2VW, but, without logic for retro/correction for performance purposes. BUG 33069885. We are allowing to access to liquidations without the need of loading the liquidations from Social Security first (GPES_CRT_LQ_TBL). This forces to read from GPES_CRT_LQ_EE which is a big table. As far as we just need the liquidation keys (liquidation header info, GPES_CRT_LQ_TBL), we don't need the logic for retro/correction rows.

SELECT DISTINCT A.SSN_EMPLOYER ,A.GPES_SSTC_PAYMNT ,A.GPES_YEAR ,A.GPES_MONTH ,A.GPES_MONTH_RECALC ,A.GPES_YEAR_RECALC ,B.COMPANY ,A.GPES_SSTC_CNTRL_DT ,L.GPES_MONTHFROM FROM PS_GPES_CRT_LQ_EE A LEFT OUTER JOIN PS_GPES_CRT_LQ_TBL L ON L.SSN_EMPLOYER = A.SSN_EMPLOYER AND A.GPES_SSTC_PAYMNT = L.GPES_SSTC_PAYMNT AND A.GPES_YEAR = L.GPES_YEAR AND A.GPES_MONTH = L.GPES_MONTH AND A.GPES_MONTH_RECALC = L.GPES_MONTH_RECALC AND A.GPES_YEAR_RECALC = L.GPES_YEAR_RECALC , PS_GPES_CCC_VW B , PS_GPES_SYS_CALEND C WHERE A.SSN_EMPLOYER = B.SSN_EMPLOYER AND A.GPES_YEAR = C.CALYEAR AND A.GPES_MONTH = C.GPES_MONTH AND A.GPES_CRT_DATA_TYPE IN ('I','C','H') AND B.EFFDT = ( SELECT MAX(ED.EFFDT) FROM PS_GPES_CCC_VW ED WHERE ED.SSN_EMPLOYER = B.SSN_EMPLOYER AND ED.EFFDT <= C.GPES_MONTH_BGN_DT)

# 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
3 GPES_YEAR Number(4,0) SMALLINT NOT NULL Year
4 GPES_MONTH Number(2,0) SMALLINT NOT NULL Month
5 GPES_MONTH_RECALC Number(2,0) SMALLINT NOT NULL Retro month
6 GPES_YEAR_RECALC Number(4,0) SMALLINT NOT NULL Retro year
7 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

8 GPES_SSTC_CNTRL_DT Date(10) DATE Social Security TCs control date
9 GPES_MONTHFROM Character(2) VARCHAR2(2) NOT NULL Month From
01=01 - January
02=02 - February
03=03 - March
04=04 - April
05=05 - May
06=06 - June
07=07 - July
08=08 - August
09=09 - September
10=10 - October
11=11 - November
12=12 - December