W3EB_PRIMBEN_VW(SQL View) |
Index Back |
---|---|
EEBeneficiariesSelect the Primary Beneficiaries |
SELECT L.EMPLID , L.EMPL_RCD , L.PLAN_TYPE , L.EFFDT , E.EFFDT , D.EFFDT ,F.COVERAGE_BEGIN_DT , L.DEPENDENT_BENEF , L.CONTINGENT , L.BENEF_PCT , L.FLAT_AMOUNT , E.NAME , D.RELATIONSHIP , D.DEP_BENEF_TYPE FROM PS_DEP_BEN_NAME E , PS_DEP_BEN_EFF D , PS_LIFE_ADD_BENEFC L , PS_LIFE_ADD_BEN F WHERE E.EMPLID = L.EMPLID AND E.EMPLID = D.EMPLID AND E.DEPENDENT_BENEF = D.DEPENDENT_BENEF AND E.DEPENDENT_BENEF = L.DEPENDENT_BENEF AND L.EFFDT=F.EFFDT AND L.EMPLID = F.EMPLID AND L.PLAN_TYPE=F.PLAN_TYPE |
# | 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 | 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: 20 |
4 | EFFDT | Date(10) | DATE |
Effective Date
Default Value: %date |
5 | EFFDT_NM | Date(10) | DATE | Field for keeping the effdt for names field for a duplicate row check. |
6 | EFFDT_NEW | Date(10) | DATE | New Effective Date |
7 | COVERAGE_BEGIN_DT | Date(10) | DATE NOT NULL | Coverage Begin Date |
8 | DEPENDENT_BENEF | Character(2) | VARCHAR2(2) NOT NULL |
Dependent Benefit
01=default Default Value: 01 |
9 | CONTINGENT | Character(1) | VARCHAR2(1) NOT NULL | Contingent |
10 | BENEF_PCT | Number(3,0) | SMALLINT NOT NULL | Percent Beneficiary Paid |
11 | FLAT_AMOUNT | Signed Number(9,0) | DECIMAL(8) NOT NULL | Flat Amount |
12 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
13 | RELATIONSHIP | Character(2) | VARCHAR2(2) NOT NULL |
Relationship to Employee
Default Value: O |
14 | DEP_BENEF_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Dependent Beneficiary Type
B=Beneficiary C=COBRA Dependent Only D=Dependent E=QDRO Estate N=None O=Both Q=QDRO Representative - Employee R=QDRO Representative -Recipient |