W3EB_LIBENEF_VW(SQL View) |
Index Back |
---|---|
Life InsuranceLife Insurance beneficiary information for 2x plans |
SELECT L.EMPLID , L.EMPL_RCD , L.PLAN_TYPE , L.BENEFIT_NBR , L.EFFDT , L.DEPENDENT_BENEF , L.BENEF_PCT , L.FLAT_AMOUNT FROM PS_LIFE_ADD_BENEFC L WHERE L.EFFDT = ( SELECT MAX(L1.EFFDT) FROM PS_LIFE_ADD_BENEFC L1 WHERE L1.EMPLID = L.EMPLID AND L1.EMPL_RCD = L.EMPL_RCD AND L.PLAN_TYPE = L1.PLAN_TYPE AND L.BENEFIT_NBR = L1.BENEFIT_NBR AND L.DEPENDENT_BENEF = L1.DEPENDENT_BENEF AND L1.EFFDT <= %CurrentDateIn) AND (L.BENEF_PCT > 0 OR L.FLAT_AMOUNT > 0) |
# | 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
Prompt Table: PER_ORG_ASGN_VW |
3 | PLAN_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
Identifies a category of benefit plan, such as Medical, Dental, and Life (Insurance). A set of plan type codes, as well as rules for creating new codes, is provided by PeopleSoft. Plan Type codes determine how the system processes and defines eligibility for the various benefit plans.
Default Value: 40 |
4 | BENEFIT_NBR | Number(3,0) | SMALLINT NOT NULL | Note! Although this field is part of the index schema for most Base Benefits enrollment-related records, it was in fact never functionally implemented. As such, it is CRITICAL that this field always be set to zero 0. |
5 | EFFDT | Date(10) | DATE NOT NULL |
Effective Date
Default Value: SAVINGS_PLAN.COVERAGE_BEGIN_DT |
6 | DEPENDENT_BENEF | Character(2) | VARCHAR2(2) NOT NULL |
Dependent Benefit
01=default Prompt Table: DEPENDENT_VW |
7 | BENEF_PCT | Number(3,0) | SMALLINT NOT NULL | Percent Beneficiary Paid |
8 | FLAT_AMOUNT | Signed Number(9,0) | DECIMAL(8) NOT NULL | Flat Amount |