ABS_HIST_BEL_VW

(SQL View)
 Index Back

Absence History View Belgium

This view combines the absence data with the belgian contract data in order to be used in the contract replacement functionality.

SELECT A.EMPLID , A.CONTRACT_NUM , A.EMPL_RCD , B.BEGIN_DT , B.ABSENCE_TYPE , B.RETURN_DT , B.ABSENCE_CODE , B.MANDAT_REPL_BEL , B.END_MAN_REP_DT_BEL , A.REG_REGION FROM PS_JOB A , PS_ABSENCE_HIST B WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.ABSENCE_TYPE IN ( SELECT ABSENCE_TYPE FROM PS_ABS_TYPE_TBL WHERE SETID = 'BEL' AND ABSENCE_CLASS = 'STR') AND A.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_JOB C WHERE C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.EFFDT <= B.BEGIN_DT) AND A.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE D.EMPLID = A.EMPLID AND D.EMPL_RCD = A.EMPL_RCD AND D.EFFDT = A.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSON

2 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
4 BEGIN_DT Date(10) DATE NOT NULL Begin Date
5 ABSENCE_TYPE Character(3) VARCHAR2(3) NOT NULL Absence Type

Prompt Table: ABS_TYPE_PNL_VW
Set Control Field: REG_REGION

6 RETURN_DT Date(10) DATE Return Date
7 ABSENCE_CODE Character(3) VARCHAR2(3) NOT NULL Absence Code

Prompt Table: ABS_CODE_TBL
Set Control Field: REG_REGION

8 MANDAT_REPL_BEL Character(1) VARCHAR2(1) NOT NULL Mandatory Replacement

Y/N Table Edit

Default Value: N

9 END_MAN_REP_DT_BEL Date(10) DATE End Date Mandatory Replacement
10 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region