GPFR_CONT_REP2

(SQL View)
 Index Back

View for contingency

Employee Level

SELECT DISTINCT S.EMPLID , S.EMPL_RCD , CN.MEMBERSHIP_NBR_FRA ,I.FUND_INST_FRA ,I.FUND_CD_FRA , P.POPULATION_CTG_FRA , P.DCL_POP_CTG_FRA , P.BASE_CALC_TYPE_FRA , P.ER_RATE_FRA , P.EE_RATE_FRA , CN.MEMBERSHIP_IND_FRA , P.FORMULA_PIN_FRA , I.DUCS_CD_FRA , I.DADS_CD_FRA , E.COVERED_POP_CD_FRA , E.CONTCT_KIND_CD_FRA FROM PS_FUND_INST_FRA I , PS_CNTRCT_FUND_FRA CN , PS_POPC_CNTRCT_FRA P , PS_GP_PYE_STAT_WRK S , PS_EMPL_CNTRCT_FRA E WHERE S.EMPLID=E.EMPLID AND CN.MEMBERSHIP_NBR_FRA = E.MEMBERSHIP_NBR_FRA AND CN.MEMBERSHIP_NBR_FRA = P.MEMBERSHIP_NBR_FRA AND CN.EFFDT = P.EFFDT AND CN.EFFDT= ( SELECT MAX(EFFDT) FROM PS_CNTRCT_FUND_FRA WHERE MEMBERSHIP_NBR_FRA=CN.MEMBERSHIP_NBR_FRA AND EFFDT <=S.SEG_END_DT) AND CN.EFF_STATUS = 'A' AND P.EFFDT = CN.EFFDT AND CN.FUND_INST_FRA = I.FUND_INST_FRA AND I.FND_INST_TYPE_FRA = 'P'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
3 GPFR_MEMBERSHIP Character(35) VARCHAR2(35) NOT NULL Membership number for AGIRC, ARRCO and others
4 FUND_INST_FRA Character(4) VARCHAR2(4) NOT NULL Fund Institution Number
5 FUND_CD_FRA Character(6) VARCHAR2(6) NOT NULL Fund Code - France
6 POPULATION_CTG_FRA Character(3) VARCHAR2(3) NOT NULL Population Category - FRA
7 DCL_POP_CTG_FRA Character(3) VARCHAR2(3) NOT NULL Declared Population - France
8 BASE_CALC_TYPE_FRA Character(2) VARCHAR2(2) NOT NULL Bse Calculation Type - France
00=Other
01=Ceiling A or 1
02=Ceiling 2
03=Ceiling 1+2
04=Ceiling B
05=B-Ceiling Isolated Sums
06=Ceiling B + B Ceiling Isolated
07=C-Ceiling
08=C-Ceiling Isolated Sums
09=Ceiling C + C Ceiling Isolated
10=CET
11=C-Ceiling Lump-sum
12=GMP
13=Lump-Sum Basis
14=Guaranty
15=Social Security Ceiling
16=Total Salary
17=Ceiling 4
18=SMIC
19=Ceiling B+C
20=APEC Lump-Sum
21=GMP Lump-Sum
31=T1 AGFF
32=T2 AGFF
34=TB AGFF
35=SITB AGFF
36=Ceiling B + SITB AGFF
37=Lump Sum Amount
9 ER_RATE_FRA Number(6,2) DECIMAL(5,2) NOT NULL Employee Rate - France
10 EE_RATE_FRA Number(6,2) DECIMAL(5,2) NOT NULL Entry Rate - France
11 MEMBERSHIP_IND_FRA Character(1) VARCHAR2(1) NOT NULL Membership Indicator - France
M=Mandatory
O=Optional
12 FORMULA_PIN_FRA Number(8,0) INTEGER NOT NULL Formula Name - France
13 DUCS_CD_FRA Character(10) VARCHAR2(10) NOT NULL DUCS Code - France
14 DADS_CD_FRA Character(10) VARCHAR2(10) NOT NULL DADS Code - France
15 COVERED_POP_CD_FRA Character(2) VARCHAR2(2) NOT NULL Covered Population - FRA
16 CONTCT_KIND_CD_FRA Character(2) VARCHAR2(2) NOT NULL Kind of Contract - FRA