ABS_NOTIF_NLDVW (SQL View)	Index Back
Absence History View This View is used in Monitor Absence - NLD ViaPrisma Illness data

ABS_NOTIF_NLDVW

(SQL View)

Absence History View

This View is used in Monitor Absence - NLD ViaPrisma Illness data

SELECT A.EMPLID , A.EMPL_RCD , A.BEGIN_DT , A.ABSENCE_TYPE , A.ABSENCE_CODE , C.EMPL_CLASS , C.COMPANY , C.DEPTID , C.SETID_DEPT , C.BUSINESS_UNIT , C.FULL_PART_TIME , A.REASON , A.RETURN_DT , A.NOTIF_START_DT_NLD , A.NOTIF_END_DT_NLD FROM PS_ABSENCE_HIST A , PS_JOB C , PS_ABS_TYPE_TBL D WHERE A.EMPLID = C.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_JOB E WHERE A.EMPLID = E.EMPLID AND A.EMPL_RCD = E.EMPL_RCD AND E.EFFDT <= A.BEGIN_DT) AND C.EFFSEQ =( SELECT MAX(F.EFFSEQ) FROM PS_JOB F WHERE F.EMPLID = A.EMPLID AND F.EMPL_RCD = A.EMPL_RCD AND F.EFFDT = C.EFFDT) AND A.ABSENCE_TYPE IN ('501', '502', '503', '504', '505', '506', '507') AND D.ABSENCE_TYPE=A.ABSENCE_TYPE AND D.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = C.REG_REGION AND RECNAME = 'ABS_TYPE_TBL')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: PERSON

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

3 BEGIN_DT Date(10) DATE NOT NULL Begin Date

4 ABSENCE_TYPE Character(3) VARCHAR2(3) NOT NULL Absence Type
Prompt Table: ABS_TYPE_PNL_VW
Set Control Field: REG_REGION

5 ABSENCE_CODE Character(3) VARCHAR2(3) NOT NULL Absence Code
Prompt Table: ABS_CODE_TBL
Set Control Field: REG_REGION

6 EMPL_CLASS Character(3) VARCHAR2(3) NOT NULL Employee Classification

7 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

8 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

9 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID

10 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

11 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time

12 REASON Character(30) VARCHAR2(30) NOT NULL Reason

13 RETURN_DT Date(10) DATE Return Date

14 NOTIF_START_DT_NLD Date(10) DATE Notification Start Date is used for Illness Reporting for The Netherlands tracking the actual date that a certain illness has been reported to the ARBO service.

15 NOTIF_END_DT_NLD Date(10) DATE Notification End Date is used for Illness Reporting for The Netherlands tracking the actual date that the return from a certain illness has been reported to the ARBO service.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERSON
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
3	BEGIN_DT	Date(10)	DATE NOT NULL	Begin Date
4	ABSENCE_TYPE	Character(3)	VARCHAR2(3) NOT NULL	Absence Type Prompt Table: ABS_TYPE_PNL_VW Set Control Field: REG_REGION
5	ABSENCE_CODE	Character(3)	VARCHAR2(3) NOT NULL	Absence Code Prompt Table: ABS_CODE_TBL Set Control Field: REG_REGION
6	EMPL_CLASS	Character(3)	VARCHAR2(3) NOT NULL	Employee Classification
7	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company
8	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
9	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department Set ID
10	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
11	FULL_PART_TIME	Character(1)	VARCHAR2(1) NOT NULL	Full/Part Time D=On Demand F=Full-Time P=Part-Time
12	REASON	Character(30)	VARCHAR2(30) NOT NULL	Reason
13	RETURN_DT	Date(10)	DATE	Return Date
14	NOTIF_START_DT_NLD	Date(10)	DATE	Notification Start Date is used for Illness Reporting for The Netherlands tracking the actual date that a certain illness has been reported to the ARBO service.
15	NOTIF_END_DT_NLD	Date(10)	DATE	Notification End Date is used for Illness Reporting for The Netherlands tracking the actual date that the return from a certain illness has been reported to the ARBO service.