GPCH_SI_ADDL_VW

(SQL View)
 Index Back

Additional Insurance View

Additional Insurance View

SELECT E1.EMPLID , E1.EMPL_RCD , E1.EFFDT , S1.COMPANY , E2.GPCH_SI_PROV_TYPE , E2.GPCH_SI_TYPE_ID , E2.GPCH_SI_ALT_ID , E2.AMOUNT , E2.CURRENCY_CD , S2.GPCH_RATE_M , S2.GPCH_RATE_F , S2.GPCH_RATE2_M , S2.GPCH_RATE2_F , S2.GPCH_LOW_SALARY , S2.GPCH_HIGH_SALARY , S2.GPCH_PIN_NUM , S2.GPCH_PIN_NUM01 , S2.GPCH_PIN_NUM02 , S2.GPCH_PIN_NUM03 , S2.GPCH_PIN_NUM04 , S2.GPCH_PIN_NUM05 , S2.GPCH_PIN_NUM06 , S2.GPCH_PIN_NUM07 , S2.GPCH_PIN_NUM08 , S3.GPCH_PREM_AN_M_PCT , S3.GPCH_PREM_AN_F_PCT , S3.GPCH_PREM_PERS_M , S3.GPCH_PREM_PERS_F , S3.GPCH_PROPOSE , S3.GPCH_PREMIUM_MIN , S3.GRADE FROM PS_GPCH_SI_VARIAN1 E1 , PS_GPCH_SI_VARIAN2 E2 , PS_GPCH_SI_ADDTNL1 S1 , PS_GPCH_SI_ADDTNL2 S2 , PS_GPCH_SI_SPLIT S3 WHERE E2.STATUS = 'A' AND E1.EMPLID = E2.EMPLID AND E1.EMPL_RCD = E2.EMPL_RCD AND E1.EFFDT = E2.EFFDT AND E2.GPCH_CALCULATE = 'A' AND S1.EFF_STATUS = 'A' AND E2.GPCH_SI_PROV_TYPE = S1.GPCH_SI_PROV_TYPE AND S1.EFFDT = ( SELECT MAX(S4.EFFDT) FROM PS_GPCH_SI_ADDTNL2 S4 WHERE S1.COMPANY = S4.COMPANY AND S1.GPCH_SI_PROV_TYPE = S4.GPCH_SI_PROV_TYPE AND S4.EFFDT <= E1.EFFDT) AND E2.GPCH_SI_PROV_TYPE = S2.GPCH_SI_PROV_TYPE AND E2.GPCH_SI_TYPE_ID = S2.GPCH_SI_TYPE_ID AND E2.GPCH_SI_ALT_ID = S2.GPCH_SI_ALT_ID AND S1.EFFDT = S2.EFFDT AND S2.COMPANY = S3.COMPANY AND S2.GPCH_SI_PROV_TYPE = S3.GPCH_SI_PROV_TYPE AND S2.EFFDT = S3.EFFDT AND S2.GPCH_SI_TYPE_ID = S3.GPCH_SI_TYPE_ID AND S2.GPCH_SI_ALT_ID = S3.GPCH_SI_ALT_ID AND S3.GPCH_CALCULATE = 'A'

# 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 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
5 GPCH_SI_PROV_TYPE Character(1) VARCHAR2(1) NOT NULL Provider Type
0=Identify
1=Others
2=UVG-LAA
3=KTG-AMC
4=UVGZ-LAAC
5=FAK-CAF
6=BVG-LPP
7=AHV-AVS
8=LAW-CDS
9=BFS-OFS
A=QST-IS
B=Write File
E=EO-APG
6 GPCH_SI_TYPE_ID Character(3) VARCHAR2(3) NOT NULL Insurance Type ID
010=010-UVG
030=030 UV CureCost
040=040 UV Special Risk
050=050 UV-Daily Pay 80%
060=060 UV Invalidity Fund
061=061 UV Invalidity Fund 100'000
070=070 Death Fund
071=071 Death Fund 10'000
080=080 Health Daily 80%
090=090 Health Base EE
100=100 Health Additional EE
110=110 Health Dependends
999=999 Additional Ins N/A
K00=Health Insurance 00
U00=Add. Accident Insurance 00
7 GPCH_SI_ALT_ID Character(8) VARCHAR2(8) NOT NULL Insurance Alternative ID
00=Group 0
01=Group 1
02=Group 2
03=(03) from 91st Day
06=(06) from 181st Day
09=(09) from 271st Day
10=(10)Group 1 Category 0
11=(11)Group 1 Category 1
12=(12)Group 1 Category 2
20=(20)Group 2 Category 0
21=(21)Group 2 Category 1
22=(22)Group 2 Category 2
51=(51) 1-times Annual Rate
52=(52) 2-times Annual Rate
53=(53) 3-times Annual Rate
54=(54) 4-times Annual Rate
55=(55) 5-times Annual Rate
99=(99) No Alternatives
A0=Group A Category 0
A1=Group A Category 1
A2=Group A Category 2
B0=Group B Category 0
B1=Group B Category 1
B2=Group B Category 2
C0=Group C Category 0
C1=Group C Category 1
C2=Group C Category 2
F0=Group F Category 0
8 AMOUNT Signed Number(20,3) DECIMAL(18,3) NOT NULL Commuting Allowance Paid Per Cycle.
9 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
10 GPCH_RATE_M Number(8,4) DECIMAL(7,4) NOT NULL Total Premium Rate Male
11 GPCH_RATE_F Number(8,4) DECIMAL(7,4) NOT NULL Total Premium Rate Female
12 GPCH_RATE2_M Number(7,2) DECIMAL(6,2) NOT NULL Total Premium/Person Male
13 GPCH_RATE2_F Number(7,2) DECIMAL(6,2) NOT NULL Total Premium/Person Female
14 GPCH_LOW_SALARY Number(10,2) DECIMAL(9,2) NOT NULL Salary Lower Limit
15 GPCH_HIGH_SALARY Number(10,2) DECIMAL(9,2) NOT NULL Salary Higher Level
16 GPCH_PIN_NUM Number(8,0) INTEGER NOT NULL PIN Number
17 GPCH_PIN_NUM01 Number(8,0) INTEGER NOT NULL PIN Number
18 GPCH_PIN_NUM02 Number(8,0) INTEGER NOT NULL PIN Number
19 GPCH_PIN_NUM03 Number(8,0) INTEGER NOT NULL PIN Number
20 GPCH_PIN_NUM04 Number(8,0) INTEGER NOT NULL PIN Number
21 GPCH_PIN_NUM05 Number(8,0) INTEGER NOT NULL PIN Number
22 GPCH_PIN_NUM06 Number(8,0) INTEGER NOT NULL PIN Number
23 GPCH_PIN_NUM07 Number(8,0) INTEGER NOT NULL PIN Number
24 GPCH_PIN_NUM08 Number(8,0) INTEGER NOT NULL PIN Number
25 GPCH_PREM_AN_M_PCT Number(8,4) DECIMAL(7,4) NOT NULL Premium AN Male %
26 GPCH_PREM_AN_F_PCT Number(8,4) DECIMAL(7,4) NOT NULL Premium AN Female %
27 GPCH_PREM_PERS_M Number(8,4) DECIMAL(7,4) NOT NULL AN Premium/Person % Male
28 GPCH_PREM_PERS_F Number(8,4) DECIMAL(7,4) NOT NULL AN Premium/Person % Female
29 GPCH_PROPOSE Character(1) VARCHAR2(1) NOT NULL Proposition
A=Automatically
O=Mandatory
30 GPCH_PREMIUM_MIN Number(6,2) DECIMAL(5,2) NOT NULL Minimum Premium
31 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade