GPES_CRT_LIQ_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 , D.GPES_MONTHFROM , D.GPES_YEARFROM , D.GPES_MONTHTO , D.GPES_YEARTO , C.XMLFILE_ID , CASE WHEN B.GPES_SSTC_PAYMNT = 'L00' THEN A.GPES_TRAINING_REDN ELSE 0 END , CASE WHEN B.GPES_SSTC_PAYMNT = 'L00' AND A.GPES_TRAINING_REDN > 0 THEN 'C' ELSE ' ' END , CASE WHEN B.GPES_SSTC_PAYMNT = 'L00' AND A.GPES_TRAINING_REDN > 0 THEN '763' ELSE ' ' END , B.GPES_SSTC_CNTRL_DT , CASE WHEN B.GPES_SSTC_PAYMNT = 'L00' THEN C.GPES_REUSE_PRV_BS ELSE ' ' END 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 (((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', '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 = 'L09' AND B.GPES_SSTC_PAYMNT IN ('L90', 'L91'))) AND (C.XML_TEMPLATE_ID = 'S01' 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') )))

# 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/Surcharge Bases Increa
L91=Retro w/Surchage new Slices
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(6,0) INTEGER NOT NULL Breaking Point
8 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
9 GPES_YEARFROM Character(4) VARCHAR2(4) NOT NULL Year From
10 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
11 GPES_YEARTO Character(4) VARCHAR2(4) NOT NULL Year To
12 XMLFILE_ID Character(50) VARCHAR2(50) NOT NULL XML File ID
13 GPES_TRAINING_REDN Number(18,2) DECIMAL(17,2) NOT NULL Continuos Training Reduction
14 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
15 GPES_CRT_CODE Character(3) VARCHAR2(3) NOT NULL Code for Creta reporting
01=Part Time Contract Hours
02=Complementary Hours
03=Presential Training Hours
04=Distance Training Hours
05=Part Time Coefficient in ERE
500=General Contingencies Base
501=Structural Overtime
502=Non-structural Overtime
509=CC Base for Maternity/Risk
51=Salary Modality
52=Option 126 Epigraph
535=CC Base for Maternity/Risk
536=CC Base for Maternity/Risk
537=Complementary Hours
563=IT Illness & Accident
601=Professional Contingences Base
603=CP Base for Maternity/Risk
611=Professional Contingences Base
613=Professional Contingences Base
634=CP Base for Maternity/Risk
635=CP Base for Maternity/Risk
636=CP Base for Maternity/Risk
637=CP Base for Maternity/Risk
663=IT by IA and PI
763=Bonif. INEM Cont. Form.
16 GPES_SSTC_CNTRL_DT Date(10) DATE Social Security TC�s control date
17 GPES_REUSE_PRV_BS Character(1) VARCHAR2(1) NOT NULL Reuse Previous Month Bases for Creta reporting