DFW_HCM_COBR_VW(SQL View) |
Index Back |
|---|
| SELECT A.EMPLID , A.BENEFIT_RCD_NBR , A.COBRA_EVENT_ID , A.BENEFIT_PROGRAM , A.COBRA_EVENT_DT FROM PS_CBR_PARTIC A , PS_CBR_PARTIC_OPTN B , PS_CBR_PARTIC_PLAN C WHERE A.EMPLID = B.EMPLID AND A.BENEFIT_RCD_NBR = B.BENEFIT_RCD_NBR AND A.COBRA_EVENT_ID = B.COBRA_EVENT_ID AND A.DEPENDENT_BENEF = B.DEPENDENT_BENEF AND A.EMPLID = C.EMPLID AND A.BENEFIT_RCD_NBR = C.BENEFIT_RCD_NBR AND A.COBRA_EVENT_ID = C.COBRA_EVENT_ID AND A.DEPENDENT_BENEF = C.DEPENDENT_BENEF AND NOT EXISTS ( SELECT 'X' FROM PS_COBRA_EVENT D , PS_JOB E WHERE D.EMPLID = E.EMPLID AND E.EMPL_RCD = D.EMPL_RCD AND E.EFFDT = ( SELECT MAX(E_ED.EFFDT) FROM PS_JOB E_ED WHERE E.EMPLID = E_ED.EMPLID AND E.EMPL_RCD = E_ED.EMPL_RCD AND E_ED.EFFDT <= SYSDATE) AND E.EFFSEQ = ( SELECT MAX(E_ES.EFFSEQ) FROM PS_JOB E_ES WHERE E.EMPLID = E_ES.EMPLID AND E.EMPL_RCD = E_ES.EMPL_RCD AND E.EFFDT = E_ES.EFFDT) ) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | 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. |
| 3 | COBRA_EVENT_ID | Number(3,0) | SMALLINT NOT NULL | Uniquely identifies a COBRA event within an employee's benefits records, and is system-generated. |
| 4 | BENEFIT_PROGRAM | Character(3) | VARCHAR2(3) NOT NULL | Defines a collection of benefit plans and their associated rate and calculation rules. |
| 5 | COBRA_EVENT_DT | Date(10) | DATE | COBRA Event Date |