BNE_EVENT_VW

(SQL View)
 Index Back

Event Selection View

This view selects any unfinalized events. It is used to load the grid on the Event Selection page.

SELECT A.EMPLID ,A.EVENT_DT ,A.SCHED_ID ,A.BENEFIT_RCD_NBR ,A.EVENT_ID ,A.EVENT_CLASS ,A.JOB_EFFDT ,A.BAS_PROCESS_STATUS ,A.PROCESS_IND ,A.EVENT_STATUS ,A.BENEFIT_PROGRAM ,B.SETID_JOBCODE ,B.JOBCODE ,D.BAS_TYPE ,D.BAS_GROUP_ID ,D.COMPANY FROM PS_BAS_PARTIC A , PS_JOB B , PS_BAS_SCHED D 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.EVENT_STATUS <> 'V' AND A.BAS_EVT_DISCONNECT = 'N' AND A.ALLOW_SELF_SERVICE = 'Y' 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 = 'E' AND A.BAS_PROCESS_STATUS IN ('AE','AN','AS','PE','PR','RE','NT','ET','EE') ) OR ( D.BAS_TYPE = 'O' AND ( A.BAS_PROCESS_STATUS IN ('AE','AN','AS','PE','PR','RE','NT','ET','EE','FE') 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 F.GVT_OS_END_DT >= %CurrentDateIn) ) OR ( A.BAS_PROCESS_STATUS IN ('AE','AN','AS','PE','PR','RE','NT','ET','EE') AND EXISTS ( SELECT 'X' FROM PS_BAS_OE_DEFN G WHERE G.OE_DEFN_ID = D.OE_DEFN_ID AND G.GVT_OS_BEGIN_DT IS NULL AND G.GVT_OS_END_DT IS NULL) ) ) )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EVENT_DT Date(10) DATE Event Date
3 SCHED_ID Character(6) VARCHAR2(6) NOT NULL Schedule ID
4 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.
5 EVENT_ID Number(6,0) INTEGER NOT NULL Event Identification
6 EVENT_CLASS Character(3) VARCHAR2(3) NOT NULL Event Classification
7 EFFDT Date(10) DATE Effective Date

Default Value: %date

8 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
9 PROCESS_IND Character(1) VARCHAR2(1) NOT NULL Process Indicator
A=Assign Benefit Program
E=Elect Options
N=Normal Processing
P=Prepare Options
R=Re-Enter
V=Void
10 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
11 BENEFIT_PROGRAM Character(3) VARCHAR2(3) NOT NULL Defines a collection of benefit plans and their associated rate and calculation rules.
12 SETID Character(5) VARCHAR2(5) NOT NULL SetID
13 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
14 BAS_TYPE Character(1) VARCHAR2(1) NOT NULL Ben Admin Type
E=Event Maintenance
O=Open Enrollment
S=Snapshot
15 BAS_GROUP_ID Character(3) VARCHAR2(3) NOT NULL Identifies a user-defined Benefits Administration group used to facilitate benefits processing. A group can represent the entire participant population of the enterprise, or a specific group of participants. It enables you to control processing by, for example, setting up separate Open Enrollment schedules for each group. If used, a BAS Group ID is assigned to each participant in a benefit program.
16 COMPANY Character(3) VARCHAR2(3) NOT NULL Company