BEN_ENR_HIST_VW(SQL View) |
Index Back |
|---|---|
EE EnrollmentUsed in Insights for OE |
| SELECT A.SCHED_ID ,A.EMPLID ,A.BENEFIT_RCD_NBR ,A.EVENT_ID , A.STATUS_DT ,A.BAS_PROCESS_STATUS ,A.EVENT_STATUS ,A.BENEFIT_PROGRAM ,A.EMPL_RCD ,A.JOB_EFFDT ,A.JOB_EFFSEQ ,A.BAS_EVT_DISCONNECT ,A.ALLOW_SELF_SERVICE FROM PS_BAS_PARTIC A , PS_JOB B , PS_BAS_SCHED D , PS_BEN_DEFN_PGM B1 , PS_BAS_OE_DEFN F WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.JOB_EFFDT = B.EFFDT AND A.JOB_EFFSEQ = B.EFFSEQ AND A.SCHED_ID = D.SCHED_ID AND A.BAS_EVT_DISCONNECT = 'N' AND A.ALLOW_SELF_SERVICE = 'Y' AND F.OE_DEFN_ID = D.OE_DEFN_ID AND F.GVT_OS_BEGIN_DT <= %CurrentDateIn AND %DateAdd(F.PERIOD_BEGIN_DT, 30) >= %CurrentDateIn AND EXISTS ( SELECT 'X' FROM PS_BAS_PARTIC_PLAN E WHERE E.SCHED_ID = A.SCHED_ID AND E.EMPLID = A.EMPLID AND E.BENEFIT_RCD_NBR = A.BENEFIT_RCD_NBR AND E.EVENT_ID = A.EVENT_ID AND E.ELECT_ALLOWED = 'Y') AND D.BAS_TYPE = 'O' AND EXISTS ( SELECT 'X' FROM PS_BAS_OE_DEFN F WHERE F.OE_DEFN_ID = D.OE_DEFN_ID AND F.GVT_OS_BEGIN_DT <= %CurrentDateIn AND %DateAdd(F.PERIOD_BEGIN_DT, 30) >= %CurrentDateIn) AND B1.BENEFIT_PROGRAM = A.BENEFIT_PROGRAM AND B1.EFFDT =( SELECT MAX(B2.EFFDT) FROM PS_BEN_DEFN_PGM B2 WHERE B2.BENEFIT_PROGRAM = B1.BENEFIT_PROGRAM AND B2.EFFDT <= %CurrentDateIn) |
| # | 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 | STATUS_DT |
Date(10) | DATE | Status Date |
| 6 | BAS_PROCESS_STATUS | Character(2) | VARCHAR2(2) NOT NULL |
Process Status
AE=Program Elig Assign Error AN=Program Elig Assign None AS=Program Elig Assigned EE=Election Error ET=Entered FA=Finalized - Benefit Pgm None FE=Finalized - Enrolled FP=Finalized - Prepared None NT=Notified PE=Prepare Error PR=Prepared RE=Re-Enter |
| 7 | EVENT_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Event Status
C=Closed to Processing D=Disconnected from Job Record O=Open for Processing V=Void |
| 8 | BENEFIT_PROGRAM | Character(3) | VARCHAR2(3) NOT NULL | Defines a collection of benefit plans and their associated rate and calculation rules. |
| 9 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 10 | JOB_EFFDT | Date(10) | DATE | Job Effective Date |
| 11 | JOB_EFFSEQ | Number(3,0) | SMALLINT NOT NULL | Job Effective Sequence |
| 12 | BAS_EVT_DISCONNECT | Character(1) | VARCHAR2(1) NOT NULL | Event Disconnected |
| 13 | ALLOW_SELF_SERVICE | Character(1) | VARCHAR2(1) NOT NULL |
Available through Self Service
N=No Self Service X=Event Created Prior to HCM 7.5 Y=Self Service Allowed |