GPES_CRT_LQ0_VW

(SQL View)
 Index Back

CRT Liquidation VW

Liquidation View for Creta Reporting

SELECT DISTINCT A.RUN_CNTL_ID , A.OPRID , A.SSN_EMPLOYER , B.GPES_SSTC_PAYMNT , B.GPES_YEAR , B.GPES_MONTH , B.GPES_CRT_BRK_LQ , B.GPES_SSTC_CNTRL_DT , D.GPES_MONTHFROM FROM PS_GPES_RC_CRT_CCC A , PS_GPES_CRT_PRD_VW B , PS_GPES_RC_CRT C , PS_GPES_CRT_LQ_TBL D WHERE A.RUN_CNTL_ID = C.RUN_CNTL_ID AND A.OPRID = C.OPRID AND A.SSN_EMPLOYER = B.SSN_EMPLOYER AND C.GPES_YEAR = B.GPES_YEAR AND C.GPES_MONTH = B.GPES_MONTH AND B.SSN_EMPLOYER = D.SSN_EMPLOYER AND B.GPES_YEAR = D.GPES_YEAR AND B.GPES_MONTH = D.GPES_MONTH AND B.GPES_YEAR_RECALC = D.GPES_YEAR_RECALC AND B.GPES_MONTH_RECALC = D.GPES_MONTH_RECALC AND B.GPES_SSTC_PAYMNT = D.GPES_SSTC_PAYMNT AND ((B.GPES_SSTC_PAYMNT IN ('L13', 'V03', 'V90') AND LPAD(D.GPES_MONTHFROM, 2, '0') = %Substring(%NumToChar(B.GPES_CRT_BRK_LQ), 5, 2)) OR (B.GPES_SSTC_PAYMNT NOT IN ('L13', 'V03', 'V90')) ) AND (((C.GPES_SSTC_PYMT_OPT <> 'L99' AND C.GPES_SSTC_PYMT_OPT <> 'L31' ) AND C.GPES_SSTC_PYMT_OPT = B.GPES_SSTC_PAYMNT) OR (C.GPES_SSTC_PYMT_OPT = 'L99' AND B.GPES_SSTC_PAYMNT IN ('L00', 'L13', 'V03', 'V90', 'L02', 'L03', 'L04', 'L90', 'L91')) OR (C.GPES_SSTC_PYMT_OPT = 'L31' AND B.GPES_SSTC_PAYMNT IN ('L00', 'L13')) OR (C.GPES_SSTC_PYMT_OPT = 'L03' AND B.GPES_SSTC_PAYMNT IN ('L03', 'V03')) OR (C.GPES_SSTC_PYMT_OPT = 'L09' AND B.GPES_SSTC_PAYMNT IN ('L90', 'L91', 'V90'))) AND (C.XML_TEMPLATE_ID IN ('S01', 'S03', 'S04', 'S05') OR (C.XML_TEMPLATE_ID = 'S02' AND EXISTS ( SELECT 'X' FROM PS_GPES_CRT_VER_VW V WHERE B.SSN_EMPLOYER = V.SSN_EMPLOYER AND B.GPES_SSTC_PAYMNT = V.GPES_SSTC_PAYMNT AND B.GPES_YEAR = V.GPES_YEAR AND B.GPES_MONTH = V.GPES_MONTH AND B.GPES_CRT_BRK_LQ = V.GPES_CRT_BRK_LQ AND (V.GPES_CRT_STATUS_EE = '0' AND C.GPES_CRT_STAT_NR = 'Y' OR V.GPES_CRT_STATUS_EE IN ( '1', '0', 'E', 'M', 'V') AND C.GPES_CRT_STAT_AL = 'Y' OR V.GPES_CRT_STATUS_EE = 'E' AND C.GPES_CRT_STAT_WE = 'Y' OR V.GPES_CRT_STATUS_EE = 'M' AND C.GPES_CRT_STAT_SM = 'Y') OR D.GPES_TRAINING_REDN > 0 ))) ORDER BY B.GPES_SSTC_PAYMNT , B.GPES_YEAR , B.GPES_MONTH

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 RUN_CNTL_ID Character(30) VARCHAR2(30) NOT NULL Run Control ID
2 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
3 SSN_EMPLOYER Character(13) VARCHAR2(13) NOT NULL Employer Social Security number

Prompt Table: GPES_CCC_VW

4 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
5 GPES_YEAR Number(4,0) SMALLINT NOT NULL Year
6 GPES_MONTH Number(2,0) SMALLINT NOT NULL Month
7 GPES_CRT_BRK_LQ Number(9,0) DECIMAL(9) NOT NULL Breaking Point
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