BNE_EVNT_CLS_VW(SQL View) |
Index Back |
|---|---|
Benefit Program Plan Type InfoThis view sits at level 1 on the enrollment setup table. It holds the plan type definitions from the benefit program definition. It also holds related event rule data. |
| SELECT A.SCHED_ID ,A.EMPLID ,A.BENEFIT_RCD_NBR ,A.EVENT_ID ,A.PLAN_TYPE ,C.MIN_ANNUAL_CONTRIB ,C.MAX_ANNUAL_CONTRIB ,C.WAIVE_COVERAGE ,D.ELECT_REQUIRED ,D.DFLT_CREDIT_IND ,D.DFLT_METHOD ,D.IGNORE_DEPBEN_EDIT ,D.IGNORE_INVEST_EDIT ,D.PROOF_START_FLT ,D.WV_PROOF_START_FLT ,D.COLLECT_DEPBEN ,D.ALLOW_ADD_DEPBEN ,D.COLLECT_FUNDS ,C.SHOW_PLAN_TYPE ,C.HANDBOOK_URL_ID ,D.FSA_PLEDGE_CONTROL ,D.COVRG_CD_CTL_FLG ,D.CERTIFICATE_ID ,D.IGNORE_OVRG_DPND ,C.EVENT_RULES_ID ,B.EVENT_CLASS ,D.PRE_ENTER FROM PS_BAS_PARTIC_PLAN A , PS_BAS_PARTIC B , PS_BEN_DEFN_PLAN C , PS_BAS_EVENT_CLASS D WHERE A.SCHED_ID = B.SCHED_ID AND A.EMPLID = B.EMPLID AND A.BENEFIT_RCD_NBR = B.BENEFIT_RCD_NBR AND A.EVENT_ID = B.EVENT_ID AND A.BENEFIT_PROGRAM = C.BENEFIT_PROGRAM AND A.PLAN_TYPE = C.PLAN_TYPE AND C.EFFDT = ( SELECT MAX(CC.EFFDT) FROM PS_BEN_DEFN_PLAN CC WHERE CC.BENEFIT_PROGRAM = C.BENEFIT_PROGRAM AND CC.PLAN_TYPE = C.PLAN_TYPE AND CC.EFFDT <= A.EVENT_DT) AND D.EVENT_RULES_ID = C.EVENT_RULES_ID AND D.EFFDT = ( SELECT MAX(DD.EFFDT) FROM PS_BAS_EVENT_RULES DD WHERE DD.EVENT_RULES_ID = D.EVENT_RULES_ID AND DD.EFFDT <= A.EVENT_DT) AND (D.EVENT_CLASS = B.EVENT_CLASS OR (D.EVENT_CLASS = ' ' AND NOT EXISTS ( SELECT 'X' FROM PS_BAS_EVENT_CLASS DDD WHERE D.EVENT_RULES_ID = DDD.EVENT_RULES_ID AND D.EFFDT = DDD.EFFDT AND B.EVENT_CLASS = DDD.EVENT_CLASS))) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  SCHED_ID |
Character(6) | VARCHAR2(6) NOT NULL | Schedule ID |
| 2 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 3 | BENEFIT_RCD_NBR |
Number(3,0) | SMALLINT NOT NULL | Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number. |
| 4 | EVENT_ID |
Number(6,0) | INTEGER NOT NULL | Event Identification |
| 5 | 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. |
| 6 | MIN_ANNUAL_CONTRIB | Number(7,2) | DECIMAL(6,2) NOT NULL | Minimum Annual Contribution |
| 7 | MAX_ANNUAL_CONTRIB | Number(11,2) | DECIMAL(10,2) NOT NULL | Maximum Annual Contribution |
| 8 | WAIVE_COVERAGE | Character(1) | VARCHAR2(1) NOT NULL |
Waive Coverage
N=Not Allowed P=Allowed with Proof X=Not Applicable Y=Always Allowed |
| 9 | ELECT_REQUIRED | Character(1) | VARCHAR2(1) NOT NULL | Elect Required |
| 10 | DFLT_CREDIT_IND | Character(1) | VARCHAR2(1) NOT NULL | Provide FlexCR Upon Default |
| 11 | DFLT_METHOD | Character(1) | VARCHAR2(1) NOT NULL |
Default Method
C=Assign Cur Covrg Else Option E=Assign Cur Covrg Else Low Opt L=Default to Lowest Elig Option N=Assign Cur Covrg Else None O=Default to Option and Coverage T=Terminate Coverage X=Default Option for Exc Credit |
| 12 | IGNORE_DEPBEN_EDIT | Character(1) | VARCHAR2(1) NOT NULL | Ignore Dep/Ben Edits |
| 13 | IGNORE_INVEST_EDIT | Character(1) | VARCHAR2(1) NOT NULL | Ignore Investment Edits |
| 14 | PROOF_START_FLT | Number(8,0) | INTEGER NOT NULL | Proof Start at Flat Amount |
| 15 | WV_PROOF_START_FLT | Number(8,0) | INTEGER NOT NULL | Waive Proof Start Flat Amount |
| 16 | COLLECT_DEPBEN | Character(1) | VARCHAR2(1) NOT NULL | This field tells the system whether to collect dependents or beneficiaries with the employee's enrollment. |
| 17 | ALLOW_ADD_DEPBEN | Character(1) | VARCHAR2(1) NOT NULL | Y/N flag denoting whether employees are permitted to add new dependents or beneficiaries via the eBenefits Self-Service applications. |
| 18 | COLLECT_FUNDS | Character(1) | VARCHAR2(1) NOT NULL | This field tells the system whether to collect fund allocations with the employee's enrollment. |
| 19 | SHOW_PLAN_TYPE | Character(1) | VARCHAR2(1) NOT NULL | This flag tells the system whether to show a plan type on the enrollment form if the employee has no choice in the election. |
| 20 | HANDBOOK_URL_ID | Character(30) | VARCHAR2(30) NOT NULL | This field holds the ID of a URL object. The URL object points to the companies enrollment handbook. |
| 21 | FSA_PLEDGE_CONTROL | Character(1) | VARCHAR2(1) NOT NULL |
FSA Pledge Control - Event Porcessing - Rule that determines whether an employee is allowed to Increase, Decrease or Neither the FSA Pledge Amount. ERISA compliance modification
D=Decrease Pledge Only I=Increase Pledge Only N=Not Applicable |
| 22 | COVRG_CD_CTL_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Coverage Control Flag - Added field for ERISA compliance
Y/N Table Edit Default Value: N |
| 23 | CERTIFICATE_ID | Character(10) | VARCHAR2(10) NOT NULL | The CERTIFICATE_ID is the unique identifier for a Certificate definition. |
| 24 | IGNORE_OVRG_DPND | Character(1) | VARCHAR2(1) NOT NULL | A flag on BAS_EVENT_CLASS. If set, BenAdmin will not drop overage dependents. |
| 25 | EVENT_RULES_ID | Character(4) | VARCHAR2(4) NOT NULL | Event Rules ID |
| 26 | EVENT_CLASS | Character(3) | VARCHAR2(3) NOT NULL | Event Classification |
| 27 | PRE_ENTER | Character(1) | VARCHAR2(1) NOT NULL | Pre-enter |