Benefit Administration

Primary Ben Admin Tables:-

•BAS_PARTIC - Stores information about a specific event.
•BAS_PARTIC_PLAN - Stores data about each plan type for an event.
•BAS_PARTIC_OPTN - Stores data about the options available.
•BAS_PARTIC_COST - Stores costs for each option.
•BAS_PARTIC_INVT  - Stores investment options by plan type
•BAS_PARTIC_DPND - Stores dependent information by plan type


To find the recent status of Open Enrollment
----------------------------------
SELECT BAS_PROCESS_STATUS,COUNT(*) FROM PS_BAS_PARTIC WHERE SCHED_ID = ‘XX’ GROUP BY BAS_PROCESS_STATUS

To identify who is eligible to make different plan (healh, saving, etc) for recent event maintainence.

SELECT A.EMPLID FROM PS_BAS_PARTIC A, PS_BAS_PARTIC_PLAN B 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 B.PLAN_TYPE = '20‘  /*different plans*/
AND B.ELECT_ALLOWED= 'Y‘ AND A.EVENT_DT > 'XX/XX/XXXX' /*event date*/.


Source : Web Source (OHUG).