BEN_ADMINERR_VW (SQL View)	Index Back
Insights Admin Error Rpt

BEN_ADMINERR_VW

(SQL View)

Insights Admin Error Rpt

SELECT C.SCHED_ID , D.EMPLID , D.EMPL_RCD , D.BENEFIT_RCD_NBR , D.EVENT_ID , C.DESCR , D.BAS_PROCESS_STATUS , T1.XLATLONGNAME , D.BENEFIT_PROGRAM , B1.DESCR , %Coalesce((SELECT P.PHONE FROM PS_PERSONAL_PHONE P WHERE P.EMPLID = D.EMPLID AND P.PREF_PHONE_FLAG = 'Y'),'') , C.BAS_TYPE , T3.XLATLONGNAME , D.EVENT_STATUS , %Dttm(D.STATUS_DT, %TimePart(%CurrentDateTimeIn)) FROM PS_BAS_SCHED C , PS_BAS_PARTIC D , PSXLATITEM T1 , PS_BEN_DEFN_PGM B1 , PSXLATITEM T3 WHERE C.BAS_TYPE IN ('E', 'O') AND C.SCHED_ID = D.SCHED_ID AND D.EVENT_STATUS IN ('O', 'C') AND D.STATUS_DT >= %Sql(FUNCLIB_HR_DATE_BUILD,%SQL(FUNCLIB_HR_DATE_YEAR,%CurrentDateIn)-4,1,1) AND T1.FIELDNAME = 'BAS_PROCESS_STATUS' AND T1.FIELDVALUE = D.BAS_PROCESS_STATUS AND T1.EFFDT =( SELECT MAX(T2.EFFDT) FROM PSXLATITEM T2 WHERE T2.FIELDNAME = T1.FIELDNAME AND T2.FIELDVALUE = T1.FIELDVALUE AND T1.EFFDT <= %CurrentDateIn) AND T3.FIELDNAME = 'BAS_TYPE' AND T3.FIELDVALUE = C.BAS_TYPE AND %EffdtCheck(PSXLATITEM X, T3, %CurrentDateIn) AND D.BENEFIT_PROGRAM = B1.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 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

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 BAS_SCHED_DESCR Character(30) VARCHAR2(30) NOT NULL Bas Schedule description

7 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

8 BAS_PROCESS_DESCR Character(30) VARCHAR2(30) NOT NULL Bas Process description

9 BENEFIT_PROGRAM Character(3) VARCHAR2(3) NOT NULL Defines a collection of benefit plans and their associated rate and calculation rules.

10 BENEFIT_PGM_DESCR Character(30) VARCHAR2(30) NOT NULL Benefit Program description

11 WORK_PHONE Character(24) VARCHAR2(24) NOT NULL Work Phone #

12 BAS_TYPE Character(1) VARCHAR2(1) NOT NULL Ben Admin Type
E=Event Maintenance
O=Open Enrollment
S=Snapshot

13 BAS_TYPE_DESCR Character(30) VARCHAR2(30) NOT NULL BAS Type description

14 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

15 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

#	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	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
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	BAS_SCHED_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Bas Schedule description
7	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
8	BAS_PROCESS_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Bas Process description
9	BENEFIT_PROGRAM	Character(3)	VARCHAR2(3) NOT NULL	Defines a collection of benefit plans and their associated rate and calculation rules.
10	BENEFIT_PGM_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Benefit Program description
11	WORK_PHONE	Character(24)	VARCHAR2(24) NOT NULL	Work Phone #
12	BAS_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Ben Admin Type E=Event Maintenance O=Open Enrollment S=Snapshot
13	BAS_TYPE_DESCR	Character(30)	VARCHAR2(30) NOT NULL	BAS Type description
14	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
15	LASTUPDDTTM	DateTime(26)	TIMESTAMP	Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.