GPES_CRT_LIQ_VW

(SQL View)
 Index Back

CRT Liquidation VW

Liquidation View for Creta Reporting

SELECT RUN_CNTL_ID , OPRID , SSN_EMPLOYER , GPES_SSTC_PAYMNT , GPES_YEAR , GPES_MONTH , GPES_CRT_BRK_LQ , GPES_SSTC_CNTRL_DT , GPES_MONTHFROM , GPES_YEARFROM , GPES_MONTHTO , GPES_YEARTO , XMLFILE_ID , GPES_CRT_DATA , GPES_CRT_DATA_TYPE , GPES_CRT_CODE , GPES_REUSE_PRV_BS , GPES_CRT_CALC_DTL , GPES_MONTH_PMNT , GPES_YEAR_PMNT , GPES_LQ_NUMBER , GPES_CRT_RECTF_OOD , GPES_CRT_RNT_RQST FROM ( 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 , CASE WHEN C.GPES_MONTHCD = '1T' THEN '01' WHEN C.GPES_MONTHCD = '2T' THEN '04' WHEN C.GPES_MONTHCD = '3T' THEN '07' WHEN C.GPES_MONTHCD = '4T' THEN '10' ELSE D.GPES_MONTHFROM END GPES_MONTHFROM , D.GPES_YEARFROM , CASE WHEN C.GPES_MONTHCD = '1T' THEN '03' WHEN C.GPES_MONTHCD = '2T' THEN '06' WHEN C.GPES_MONTHCD = '3T' THEN '09' WHEN C.GPES_MONTHCD = '4T' THEN '12' ELSE D.GPES_MONTHTO END GPES_MONTHTO , D.GPES_YEARTO , C.XMLFILE_ID , CASE WHEN D.GPES_TRAINING_REDN > 0 THEN %NumToChar(D.GPES_TRAINING_REDN*100) ELSE ' ' END GPES_CRT_DATA , CASE WHEN D.GPES_TRAINING_REDN > 0 THEN 'C' ELSE ' ' END GPES_CRT_DATA_TYPE , CASE WHEN D.GPES_TRAINING_REDN > 0 THEN '763' ELSE ' ' END GPES_CRT_CODE , CASE WHEN B.GPES_SSTC_PAYMNT = 'L00' THEN C.GPES_REUSE_PRV_BS ELSE ' ' END GPES_REUSE_PRV_BS , C.GPES_CRT_CALC_DTL , C.GPES_MONTH_PMNT , C.GPES_YEAR_PMNT , CASE WHEN C.GPES_CRT_RECTF_OOD = 'Y' THEN D.GPES_LQ_NUMBER ELSE ' ' END GPES_LQ_NUMBER , CASE WHEN C.GPES_CRT_RECTF_OOD = 'Y' THEN '1' ELSE ' ' END GPES_CRT_RECTF_OOD , CASE WHEN C.XML_TEMPLATE_ID = 'S01' THEN A.GPES_CRT_RNT_RQST WHEN C.XML_TEMPLATE_ID = 'S02' THEN A.GPES_CRT_RNT_RQST WHEN C.XML_TEMPLATE_ID = 'S05' THEN A.GPES_CRT_RNT_RQST ELSE 'N' END GPES_CRT_RNT_RQST , MAX(B.GPES_MONTH) OVER (PARTITION BY B.SSN_EMPLOYER) AS MAX_MONTH 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 OR ( C.GPES_MONTHCD='1T' AND B.GPES_MONTH IN (1,2,3)) OR (C.GPES_MONTHCD='2T' AND B.GPES_MONTH IN (4,5,6)) OR (C.GPES_MONTHCD='3T' AND B.GPES_MONTH IN (7,8,9)) OR (C.GPES_MONTHCD='4T' AND B.GPES_MONTH IN (10,11,12)) ) AND B.SSN_EMPLOYER = D.SSN_EMPLOYER AND B.GPES_YEAR = D.GPES_YEAR AND ( C.GPES_MONTH = D.GPES_MONTH OR ( C.GPES_MONTHCD='1T' AND D.GPES_MONTH IN (1,2,3)) OR (C.GPES_MONTHCD='2T' AND D.GPES_MONTH IN (4,5,6)) OR (C.GPES_MONTHCD='3T' AND D.GPES_MONTH IN (7,8,9)) OR (C.GPES_MONTHCD='4T' AND D.GPES_MONTH IN (10,11,12)) ) 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 %Sql(FUNCLIB_HR_NBR_PAD,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 ))) ) TEMP WHERE MAX_MONTH = 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
10 GPES_YEARFROM Character(4) VARCHAR2(4) NOT NULL Year From
11 GPES_MONTHTO Character(2) VARCHAR2(2) NOT NULL Month To
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
12 GPES_YEARTO Character(4) VARCHAR2(4) NOT NULL Year To
13 XMLFILE_ID Character(50) VARCHAR2(50) NOT NULL XML File ID
14 GPES_CRT_DATA Character(11) VARCHAR2(11) NOT NULL Data for Creta reporting
15 GPES_CRT_DATA_TYPE Character(1) VARCHAR2(1) NOT NULL Type of Data for Creta reporting: C-concept, H-hours, I-Identifier
C=Amount
H=Hour
I=Indicator
16 GPES_CRT_CODE Character(3) VARCHAR2(3) NOT NULL Code for Creta reporting
17 GPES_REUSE_PRV_BS Character(1) VARCHAR2(1) NOT NULL Reuse Previous Month Bases for Creta reporting
18 GPES_CRT_CALC_DTL Character(1) VARCHAR2(1) NOT NULL Employee calculation data break down

Y/N Table Edit

Default Value: N

19 GPES_MONTH_PMNT Number(2,0) SMALLINT NOT NULL Month in which an earning is paid.
20 GPES_YEAR_PMNT Number(4,0) SMALLINT NOT NULL Year in which an earning is paid.
21 GPES_LQ_NUMBER Character(17) VARCHAR2(17) NOT NULL Error Code
22 GPES_CRT_RECTF_OOD Character(1) VARCHAR2(1) NOT NULL Out of Date Rectification flag

Y/N Table Edit

Default Value: N

23 GPES_CRT_RNT_RQST Character(1) VARCHAR2(1) NOT NULL Request RNT XML file flag.