GPCH_SI_SPLT_VW

(SQL View)
 Index Back

View to prompt valid entries

View to prompt valid entries for EE's ,Company and Grade

SELECT J.EMPLID , S.COMPANY , S.GPCH_SI_TYPE_ID , T1.XLATLONGNAME ,S.GPCH_SI_ALT_ID ,S.GPCH_SI_PROV_TYPE FROM PS_GPCH_SI_SPLIT S , PS_JOB J , PSXLATITEM T , PSXLATITEM T1 ,PSXLATITEM T2 WHERE J.COMPANY = S.COMPANY AND J.GRADE = S.GRADE AND S.EFFDT = ( SELECT MAX(A.EFFDT) FROM PS_GPCH_SI_ADDTNL1 A WHERE S.COMPANY = A.COMPANY AND S.GPCH_SI_PROV_TYPE = A.GPCH_SI_PROV_TYPE AND A.EFF_STATUS = 'A' AND A.EFFDT <= J.EFFDT ) AND T.FIELDNAME = 'GPCH_SI_PROV_TYPE' AND T.FIELDVALUE = S.GPCH_SI_PROV_TYPE AND T1.FIELDNAME = 'GPCH_SI_TYPE_ID' AND T1.FIELDVALUE = S.GPCH_SI_TYPE_ID AND T2.FIELDNAME = 'GPCH_SI_ALT_ID' AND T2.FIELDVALUE = S.GPCH_SI_ALT_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
3 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
4 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
5 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
6 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