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 Record
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
C=Base AL08
D=GRFI-FRFI
E=EO-APG
I=BNK_BNQ
6 GPCH_SI_TYPE_ID Character(3) VARCHAR2(3) NOT NULL Insurance Type ID
7 GPCH_SI_ALT_ID Character(8) VARCHAR2(8) NOT NULL Insurance Alternative ID
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