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

GPNL_ABS_ILL_VW

(SQL View)

Absence History View

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

SELECT A.EMPLID , A.EMPL_RCD , A.BGN_DT , B.ABSENCE_TYPE , A.PIN_TAKE_NUM , D.DESCRSHORT , C.EMPL_CLASS , C.COMPANY , C.DEPTID , C.SETID_DEPT , C.BUSINESS_UNIT , C.FULL_PART_TIME , A.GPNL_ACT_RTRN_DT , A.NOTIF_START_DT_NLD , A.NOTIF_END_DT_NLD FROM PS_GPNL_ABS_EVENT A , PS_GPNL_ABS_TAKES B , PS_GP_ABS_TAKE BS , PS_JOB C , PS_GP_ABS_TYPE 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.BGN_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.PIN_TAKE_NUM = B.PIN_NUM AND B.ABSENCE_TYPE IN ('501', '502', '503', '504', '505', '506', '507') AND B.EFFDT = ( SELECT MAX(BB.EFFDT) FROM PS_GPNL_ABS_TAKES BB WHERE A.PIN_TAKE_NUM = BB.PIN_NUM AND BB.EFFDT <= A.BGN_DT AND BB.ABSENCE_TYPE IN ('501', '502', '503', '504', '505', '506', '507')) AND A.PIN_TAKE_NUM = BS.PIN_NUM AND BS.EFFDT = ( SELECT MAX(BX.EFFDT) FROM PS_GP_ABS_TAKE BX WHERE BX.PIN_NUM = A.PIN_TAKE_NUM AND BX.EFFDT <= A.BGN_DT) AND D.USED_BY = 'C' AND D.COUNTRY = 'NLD' AND D.ABS_TYPE_OPTN = BS.ABS_TYPE_OPTN AND D.EFFDT = ( SELECT MAX(DD.EFFDT) FROM PS_GP_ABS_TYPE DD WHERE DD.USED_BY = 'C' AND DD.COUNTRY = 'NLD' AND DD.ABS_TYPE_OPTN = D.ABS_TYPE_OPTN AND DD.EFFDT <= A.BGN_DT )

# 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 Rcd Nbr

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 PIN_TAKE_NUM Number(8,0) INTEGER NOT NULL Absence Take
Prompt Table: GP_PIN_ABSTK_VW

6 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description

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

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

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

10 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department SetID

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

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

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 Rcd Nbr
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	PIN_TAKE_NUM	Number(8,0)	INTEGER NOT NULL	Absence Take Prompt Table: GP_PIN_ABSTK_VW
6	DESCRSHORT	Character(10)	VARCHAR2(10) NOT NULL	Short Description
7	EMPL_CLASS	Character(3)	VARCHAR2(3) NOT NULL	Employee Classification
8	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company
9	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
10	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department SetID
11	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
12	FULL_PART_TIME	Character(1)	VARCHAR2(1) NOT NULL	Full/Part Time D=On Demand F=Full-Time P=Part-Time
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.