GPNL_EE_S_PR_VW

(SQL View)
 Index Back

EE Insurance Premium Table NLD

GPNL Employee Insurance Premium Table NLD

SELECT A.EMPLID , A.EMPL_RCD , B.GPNL_INS_CD , B.GPNL_INS_NBR , A.GPNL_ZW_CNTR_MTHD , A.GPNL_WAO_CNTR_MTHD , A.GPNL_WW_CNTR_MTHD , A.GPNL_ZFW_CNTR_MTHD , '00' , 'N' , D.GPNL_INS_TYPE , D.GPNL_EE_PCT1 , 0 , D.GPNL_EE_FRN1 , D.GPNL_EE_MIN_1 , D.GPNL_EE_MAX_1 , D.GPNL_EE_PCT2 , 0 , D.GPNL_EE_FRN2 , D.GPNL_EE_MIN_2 , D.GPNL_EE_MAX_2 , D.GPNL_TTL_PCT1 ,0 , D.GPNL_TTL_FRN1 , D.GPNL_TTL_MIN_1 , D.GPNL_TTL_MAX_1 , D.GPNL_TTL_PCT2 , 0 , D.GPNL_TTL_FRN2 , D.GPNL_TTL_MIN_2 , D.GPNL_TTL_MAX_2 , D.GPNL_SOC_FREQ , D.GPNL_INC_PCT_EE , 0 , D.GPNL_INC_PCT_ER , 0 , D.GPNL_FTE_MTHD , D.GPNL_MIN_AGE , D.GPNL_MAX_AGE , D.GPNL_DTRMNTN_DT FROM PS_GPNL_EE_SOC_INS A ,PS_GPNL_INS_GR_DTL B ,PS_GPNL_INS_PREMM D WHERE A.GPNL_S_INS_GRP = B.GPNL_INS_GRP AND B.GPNL_INS_CD = D.GPNL_INS_CD AND B.GPNL_INS_NBR = D.GPNL_INS_NBR AND D.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_GPNL_INS_PREMM C WHERE C.GPNL_INS_CD = D.GPNL_INS_CD AND C.EFFDT <= %CurrentDateIn) AND B.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_GPNL_INS_GRP E WHERE E.GPNL_INS_GRP = B.GPNL_INS_GRP AND E.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSON

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
3 GPNL_INS_CD Character(6) VARCHAR2(6) NOT NULL GPNL_INS_CD

Prompt Table: GPNL_INS_CD

4 GPNL_INS_NBR Character(3) VARCHAR2(3) NOT NULL Insurance Number
5 GPNL_ZW_CNTR_MTHD Character(2) VARCHAR2(2) NOT NULL GPNL_ZW_CNTR_MTHD
00=No Premium
01=Employee/Total
02=No Employee/Total
03=No EE/Employer

Default Value: 01

6 GPNL_WAO_CNTR_MTHD Character(2) VARCHAR2(2) NOT NULL GPNL_WAO_CNTR_MTHD
00=No Premium
01=Employee/Total
02=No Employee/Total
03=No Employee/ Employer

Default Value: 01

7 GPNL_WW_CNTR_MTHD Character(2) VARCHAR2(2) NOT NULL GPNL_WW_CNTR_MTHD
00=No Premium
01=Employee/Total
02=No Employee/Total
03=No Employee / Employer

Default Value: 01

8 GPNL_ZFW_CNTR_MTHD Character(2) VARCHAR2(2) NOT NULL ZFW Contribution Method
00=No Premium
01=Employee/Total
02=No Employee/Total
03=No Employee/Employer
04=Private Health Insurance

Default Value: 01

9 GPNL_ADDT_CNT_MTHD Character(2) VARCHAR2(2) NOT NULL ZFW Contribution Method
00=No Premium
01=Employee/Total
02=No Employee/Total
03=No Employee/Employer
10 GPNL_INS_OVRD Character(1) VARCHAR2(1) NOT NULL GPNL_INS_OVRD
11 GPNL_INS_TYPE Character(2) VARCHAR2(2) NOT NULL GPNL_INS_TYPE
1=Social
2=Additional
12 GPNL_EE_PCT1 Number(7,3) DECIMAL(6,3) NOT NULL Employee Percentage 1
13 GPNL_EE_PCT1_OVR Number(7,3) DECIMAL(6,3) NOT NULL Employee Percentage 1
14 GPNL_EE_FRN1 Number(9,2) DECIMAL(8,2) NOT NULL Employee Franchise 1
15 GPNL_EE_MIN_1 Number(9,2) DECIMAL(8,2) NOT NULL GPNL_EE_MIN_1
16 GPNL_EE_MAX_1 Number(9,2) DECIMAL(8,2) NOT NULL Employee Maximum by Frequency 1
17 GPNL_EE_PCT2 Number(7,3) DECIMAL(6,3) NOT NULL GPNL_EE_PCT2
18 GPNL_EE_PCT2_OVR Number(7,3) DECIMAL(6,3) NOT NULL GPNL_EE_PCT2
19 GPNL_EE_FRN2 Number(9,2) DECIMAL(8,2) NOT NULL Employee Franchise 2
20 GPNL_EE_MIN_2 Number(9,2) DECIMAL(8,2) NOT NULL Employee Minimum by Frequency 2
21 GPNL_EE_MAX_2 Number(9,2) DECIMAL(8,2) NOT NULL Employee Maximum by Frequency 2
22 GPNL_TTL_PCT1 Number(7,3) DECIMAL(6,3) NOT NULL Total Percentage 1
23 GPNL_TTL_PCT1_OVR Number(7,3) DECIMAL(6,3) NOT NULL Total Percentage 1
24 GPNL_TTL_FRN1 Number(9,2) DECIMAL(8,2) NOT NULL Total Franchise 1
25 GPNL_TTL_MIN_1 Number(9,2) DECIMAL(8,2) NOT NULL Total Minimum by Day 1
26 GPNL_TTL_MAX_1 Number(9,2) DECIMAL(8,2) NOT NULL Total Maximum by Day 1
27 GPNL_TTL_PCT2 Number(7,3) DECIMAL(6,3) NOT NULL Total Percentage 2
28 GPNL_TTL_PCT2_OVR Number(7,3) DECIMAL(6,3) NOT NULL Total Percentage 2
29 GPNL_TTL_FRN2 Number(9,2) DECIMAL(8,2) NOT NULL Total Franchise 2
30 GPNL_TTL_MIN_2 Number(9,2) DECIMAL(8,2) NOT NULL GPNL_TTL_MIN_2
31 GPNL_TTL_MAX_2 Number(9,2) DECIMAL(8,2) NOT NULL Total Maximum by Day 2
32 GPNL_SOC_FREQ Character(1) VARCHAR2(1) NOT NULL GPNL_SOZ_FREQ
1=Day
2=Payroll Period
3=Year

Default Value: 1

33 GPNL_INC_PCT_EE Number(7,3) DECIMAL(6,3) NOT NULL GPNL_INC_PCT_EE
34 GPNL_INC_PCT_EE_OV Number(7,3) DECIMAL(6,3) NOT NULL GPNL_INC_PCT_EE
35 GPNL_INC_PCT_ER Number(7,3) DECIMAL(6,3) NOT NULL Increase Percentage Employer Premium
36 GPNL_INC_PCT_ER_OV Number(7,3) DECIMAL(6,3) NOT NULL Increase Percentage Employer Premium
37 GPNL_FTE_MTHD Character(1) VARCHAR2(1) NOT NULL FTE Calculation Method
1=Social Security Days Based
2=Worked Days Based
3=Payment Percentage Based

Default Value: 1

38 GPNL_MIN_AGE Number(3,0) SMALLINT NOT NULL Insurance Minimum Age

Default Value: 1

39 GPNL_MAX_AGE Number(3,0) SMALLINT NOT NULL Insurance Maximum Age

Default Value: 999

40 GPNL_DTRMNTN_DT Character(1) VARCHAR2(1) NOT NULL Employee Determination Date
0=First Day Payroll Period
1=Last Date Payroll Period

Default Value: 0