BEN_Q_OVAGDP_VW(SQL View) |
Index Back |
|---|---|
Benefits Workcenter QueryBenefits Workcenter Query to find overage dependents |
| SELECT P.EMPLID ,O.EMPL_RCD , P.NAME , O.PLAN_TYPE , O.COVRG_CD , O.COVERAGE_BEGIN_DT , Q.DEPENDENT_BENEF , Q.RELATIONSHIP , Q.BIRTHDATE , Q.STUDENT , Q.STUDENT_STATUS_DT , Q.DISABLED , Q.MAR_STATUS , Q.MAR_STATUS_DT , Z.BENEFIT_PROGRAM , Z.EFFDT , P.NAME_PSFORMAT , D.DEP_AGE_LIMIT , D.STUDENT_AGE_LIMIT , D.EXCL_DISABLED_AGE ,%DateDiff( Q.BIRTHDATE, O.COVERAGE_BEGIN_DT ) , S.OPRID ,S.BUSINESS_UNIT ,S.DEPTID ,S.COMPANY ,S.REG_REGION FROM PS_HEALTH_BENEFIT O , PS_PERSON_NAME P , PS_DEPENDENT_BENEF Q , PS_BEN_PROG_PARTIC Y , PS_BEN_DEFN_PGM Z , PS_BEN_DEFN_PLAN B , PS_DEP_RULE_TBL D , PS_PERS_SRCH_GBL S WHERE O.COVERAGE_ELECT = 'E' AND S.EMPLID=O.EMPLID AND O.EFFDT = ( SELECT MAX(Z4.EFFDT) FROM PS_HEALTH_BENEFIT Z4 WHERE Z4.EMPLID = O.EMPLID AND Z4.COBRA_EVENT_ID = O.COBRA_EVENT_ID AND Z4.EMPL_RCD = O.EMPL_RCD AND Z4.PLAN_TYPE = O.PLAN_TYPE AND Z4.BENEFIT_NBR = O.BENEFIT_NBR AND Z4.EFFDT <= %CurrentDateIn) AND (O.COVERAGE_END_DT IS NULL OR O.COVERAGE_END_DT > %CurrentDateIn) AND P.EMPLID = O.EMPLID AND Q.EMPLID = O.EMPLID AND Y.EMPLID = O.EMPLID AND Y.COBRA_EVENT_ID = O.COBRA_EVENT_ID AND Y.EMPL_RCD = O.EMPL_RCD AND Y.EFFDT = ( SELECT MAX(Z5.EFFDT) FROM PS_BEN_PROG_PARTIC Z5 WHERE Z5.EMPLID = Y.EMPLID AND Z5.COBRA_EVENT_ID = Y.COBRA_EVENT_ID AND Z5.EMPL_RCD = Y.EMPL_RCD AND Z5.EFFDT <= %CurrentDateIn) AND Z.BENEFIT_PROGRAM = Y.BENEFIT_PROGRAM AND Z.EFFDT = ( SELECT MAX(EFFDT) FROM PS_BEN_DEFN_PGM WHERE BENEFIT_PROGRAM = Z.BENEFIT_PROGRAM AND EFFDT <= %CurrentDateIn) AND Q.RELATIONSHIP NOT IN ('SP', 'SS', 'NA') AND Q.BIRTHDATE IS NOT NULL AND EXISTS ( SELECT 'X' FROM PS_HEALTH_DEPENDNT Z6 WHERE Z6.EMPLID = O.EMPLID AND Z6.COBRA_EVENT_ID = O.COBRA_EVENT_ID AND Z6.EMPL_RCD = O.EMPL_RCD AND Z6.PLAN_TYPE = O.PLAN_TYPE AND Z6.BENEFIT_NBR = O.BENEFIT_NBR AND Z6.EFFDT = O.EFFDT AND Z6.DEPENDENT_BENEF = Q.DEPENDENT_BENEF) AND B.BENEFIT_PROGRAM = Z.BENEFIT_PROGRAM AND B.EFFDT = Z.EFFDT AND B.PLAN_TYPE = O.PLAN_TYPE AND B.DEP_RULE_ID = D.DEP_RULE_ID AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_DEP_RULE_TBL D1 WHERE D1.DEP_RULE_ID = D.DEP_RULE_ID AND D1.EFFDT <= Z.EFFDT) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Benefit Record Number |
| 3 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 4 | 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. |
| 5 | COVRG_CD | Character(2) | VARCHAR2(2) NOT NULL | Coverage Code |
| 6 | COVERAGE_BEGIN_DT | Date(10) | DATE | Coverage Begin Date |
| 7 | DEPENDENT_BENEF | Character(2) | VARCHAR2(2) NOT NULL |
Dependent Benefit
01=default |
| 8 | RELATIONSHIP | Character(2) | VARCHAR2(2) NOT NULL | Relationship to Employee |
| 9 | BIRTHDATE_DEP | Date(10) | DATE | Dependent birthdate |
| 10 | STUDENT | Character(1) | VARCHAR2(1) NOT NULL |
Student
N=No Y=Yes |
| 11 | STUDENT_STATUS_DT | Date(10) | DATE | Student Status Date |
| 12 | DISABLED | Character(1) | VARCHAR2(1) NOT NULL |
A flag that indicates whether or not (Y or N) a dependent is a disabled.
Y/N Table Edit |
| 13 | MAR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Marital Status
C=Common-Law D=Divorced E=Separated H=Head of Household L=DissDeclLost Civil Partner M=Married P=Civil Partnership S=Single T=Surviving Civil Partner U=Unknown V=Dissolved Civil Partnership W=Widowed |
| 14 | MAR_STATUS_DT | Date(10) | DATE | This is the date that the employee's Marital status is/was effective. /* Marital Status date is stored redundantly on PERS_DATA_EFFDT for two reasons. 1. To allow the date to be entered prior to the first PERS_DATA_EFFDT so that the actual date for the marital status on the hire row can be entered. This is the only time that the field is enterable by the user. 2. To allow for easier reporting on the data via SQR and Crystal. This is denormalizing the PERS_DATA_EFFDT record, but the number of requests for this redundancy coupled with the need for #1, has provided the justification for denormalizing this information and taking on the added maintenance. */ |
| 15 | BENEFIT_PROGRAM | Character(3) | VARCHAR2(3) NOT NULL | Defines a collection of benefit plans and their associated rate and calculation rules. |
| 16 |  EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 17 | NAME_PSFORMAT | Character(50) | VARCHAR2(50) NOT NULL | A Person's name in the Peoplesoft Format (basically LN,FN MI |
| 18 | DEP_AGE_LIMIT | Number(2,0) | SMALLINT NOT NULL | Dependent Age Limit |
| 19 | STUDENT_AGE_LIMIT | Number(2,0) | SMALLINT NOT NULL | Student Age Limit |
| 20 | EXCL_DISABLED_AGE | Character(1) | VARCHAR2(1) NOT NULL | Exclude Disabled from Age Limit |
| 21 | BN_DAYS_DIFF | Number(8,2) | DECIMAL(7,2) NOT NULL | Days Difference |
| 22 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 23 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: BUS_UNIT_TBL_HR |
| 24 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 25 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL |
Company
Prompt Table: COMPANY_TBL |
| 26 | REG_REGION | Character(5) | VARCHAR2(5) NOT NULL |
Regulatory Region
Prompt Table: REG_REGION_TBL |