GPFR_CONT_REP1

(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.DCL_POP_CTG_FRA , P.BASE_CALC_TYPE_FRA , I.DUCS_CD_FRA , I.DADS_CD_FRA FROM PS_FUND_INST_FRA I , PS_CNTRCT_FUND_FRA CN , PS_POPC_CNTRCT_FRA P , PS_GP_PYE_STAT_WRK S WHERE 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 DCL_POP_CTG_FRA Character(3) VARCHAR2(3) NOT NULL Declared Population - France
7 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
8 DUCS_CD_FRA Character(10) VARCHAR2(10) NOT NULL DUCS Code - France
9 DADS_CD_FRA Character(10) VARCHAR2(10) NOT NULL DADS Code - France