GP_ABS_EMHDR_VW

(SQL View)
 Index Back

Absence Details View

This view contains all the details of the absence request to be displayed in the HTML Emails.

SELECT A.TRANSACTION_NBR , A.EMPLID , A.EMPL_RCD , A.BGN_DT , A.PIN_TAKE_NUM , A.END_DT , %Sql(HR_HT_EMAIL_DATE,A.BGN_DT) , %Sql(HR_HT_EMAIL_DATE,A.END_DT) , A.ABSENCE_REASON , ' ' , E.SETID_DEPT , B.DEPTID , B.DESCR , C.SETID , C.JOBCODE , C.DESCR , D.PIN_NM , D.DESCR , CASE WHEN SS.COUNTRY = 'ALL' THEN 'A' ELSE 'C' END , SS.COUNTRY , SS.FCST_REQ_APPROVAL , A.WF_STATUS , G.XLATLONGNAME , H.NAME , H.NAME_DISPLAY , %Coalesce(%Sql(FUNCLIB_HR_STRING_RTRIM, A.ABS_EVT_FCST_VAL), 'Not Available') , A.FCST_DTTM , %Coalesce(%Sql(FUNCLIB_HR_STRING_RTRIM, %Sql(GP_ABS_EMAIL_DTTM, A.FCST_DTTM) ), 'Not Available') , I.COMMENTS , BAL.PRD_END_DT , %Sql(HR_HT_EMAIL_DATE,BAL.PRD_END_DT) , A.DURATION_ABS , SS.UNIT_TYPE , %Sql(GP_ABS_NUM_TO_CHAR_HTML, A.DURATION_ABS) %Concat ' ' %Concat G1.XLATLONGNAME , 'X' FROM PS_GP_ABS_EVENT A LEFT OUTER JOIN PS_GP_ABS_BALDT_VW BAL ON BAL.EMPLID = A.EMPLID AND BAL.EMPL_RCD = A.EMPL_RCD , PS_DEPT_TBL B , PS_JOBCODE_TBL C , PS_GP_PIN D , PS_JOB E , PS_GP_ABS_SS_DAT F , PS_GP_ABSSS_V_XREF X , PSXLATITEM G , PS_PERSON_NAME H , PS_GP_ABS_SS_STA I , PSXLATITEM G1 , PS_GP_ABS_SS SS WHERE E.EMPLID = A.EMPLID AND E.EMPL_RCD = A.EMPL_RCD AND F.EMPLID = X.EMPLID AND F.EMPL_RCD = X.EMPL_RCD AND F.TRANSACTION_NBR = X.TRANSACTION_NBR AND X.EOAWTHREAD_STATUS <> 'T' AND A.EMPLID = F.EMPLID AND A.EMPL_RCD = F.EMPL_RCD AND A.TRANSACTION_NBR = F.TRANSACTION_NBR AND I.EMPLID = F.EMPLID AND I.EMPL_RCD = F.EMPL_RCD AND I.TRANSACTION_NBR = F.TRANSACTION_NBR AND I.SEQNUM = ( SELECT MAX(I2.SEQNUM) FROM PS_GP_ABS_SS_STA I2 WHERE I2.TRANSACTION_NBR = I.TRANSACTION_NBR AND I2.EMPLID = I.EMPLID AND I2.EMPL_RCD = I.EMPL_RCD AND I2.BGN_DT = I.BGN_DT AND I2.PIN_TAKE_NUM = I.PIN_TAKE_NUM AND I2.END_DT = I.END_DT AND ((I2.EMPLID_CURR_APPR = I2.EMPLID) OR (I2.EMPLID_CURR_APPR <> I2.EMPLID AND I2.SEQNUM = 1))) AND E.EFFDT=( SELECT MAX(JOB.EFFDT) FROM PS_JOB JOB WHERE JOB.EMPLID=E.EMPLID AND JOB.EMPL_RCD=E.EMPL_RCD AND JOB.EFFDT<=%CurrentDateIn) AND E.EFFSEQ = ( SELECT MAX(JOB2.EFFSEQ) FROM PS_JOB JOB2 WHERE JOB2.EMPLID = E.EMPLID AND JOB2.EMPL_RCD = E.EMPL_RCD AND JOB2.EFFDT = E.EFFDT) AND C.SETID = E.SETID_JOBCODE AND C.JOBCODE = E.JOBCODE AND C.EFFDT = ( SELECT MAX(JC.EFFDT) FROM PS_JOBCODE_TBL JC WHERE JC.SETID = C.SETID AND JC.JOBCODE=C.JOBCODE AND JC.EFFDT <=%CurrentDateIn) AND B.SETID = E.SETID_DEPT AND B.DEPTID = E.DEPTID AND B.EFFDT = ( SELECT MAX(DEPT.EFFDT) FROM PS_DEPT_TBL DEPT WHERE DEPT.SETID = B.SETID AND DEPT.DEPTID=B.DEPTID AND DEPT.EFFDT <=%CurrentDateIn) AND A.PIN_TAKE_NUM = D.PIN_NUM AND G.FIELDNAME = 'WF_STATUS' AND G.FIELDVALUE = A.WF_STATUS AND %EffdtCheck(PSXLATITEM, G, %CurrentDateIn) AND G.EFF_STATUS = 'A' AND SS.COUNTRY = F.COUNTRY AND SS.PIN_TAKE_NUM = A.PIN_TAKE_NUM AND %EffdtCheck(GP_ABS_SS, SS, A.BGN_DT) AND G1.FIELDNAME = 'ABS_UNIT_TYPE' AND G1.FIELDVALUE = SS.UNIT_TYPE AND %EffdtCheck(PSXLATITEM, G1, %CurrentDateIn) AND G1.EFF_STATUS = 'A' AND H.EMPLID = A.EMPLID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 TRANSACTION_NBR Number(15,0) DECIMAL(15) NOT NULL 08/02/99 NAS: Changed the size of this field from 10 to 15. Based on Jim Hillman's decision to make the field at a standard size.
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
4 BGN_DT Date(10) DATE Begin Date
5 PIN_TAKE_NUM Number(8,0) INTEGER NOT NULL Absence Take
6 END_DT Date(10) DATE end date
7 DESCR20 Character(20) VARCHAR2(20) NOT NULL Description
8 DESCR25 Character(25) VARCHAR2(25) NOT NULL Short description
9 ABSENCE_REASON Character(3) VARCHAR2(3) NOT NULL absence reason
10 DESCR5 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
11 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
12 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
13 DEPTNAME Character(30) VARCHAR2(30) NOT NULL Department Name
14 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
15 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
16 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
17 PIN_NM Character(18) VARCHAR2(18) NOT NULL Element Name field
18 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
19 USED_BY Character(1) VARCHAR2(1) NOT NULL USED_BY
A=All Countries
C=Specific Country
20 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
21 FCST_REQ_APPROVAL Character(1) VARCHAR2(1) NOT NULL This field indicates whether Forecasting is required to approve an absence request.
22 WF_STATUS Character(1) VARCHAR2(1) NOT NULL Workflow Status
A=Approved
C=Cancelled
D=Denied
E=Error. Contact Administrator.
F=Awaiting final approval
I=In Approval Process
M=Administrator is Processing
N=Not Available
P=Rework
S=Submitted
V=Data Saved
23 DESCR Character(30) VARCHAR2(30) NOT NULL Description
24 NAME Character(50) VARCHAR2(50) NOT NULL Name
25 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
26 ABS_EVT_FCST_VAL Character(30) VARCHAR2(30) NOT NULL This field is used on the GP_ABS_EVENT record to record the value of the resolved related forecast element.
27 FCST_DTTM DateTime(26) TIMESTAMP This field is used on the GP_ABS_EVENT to record the last time forecasting was run for this event.
28 STRING_DTTM Character(100) VARCHAR2(100) NOT NULL This Field is used in TL_RI_RESOLV Process
29 COMMENTS Long Character CLOB Comment
30 PRD_END_DT Date(10) DATE Pay Period End Date
31 DESCR25_2 Character(25) VARCHAR2(25) NOT NULL Description part 2
32 DURATION_ABS Number(7,2) DECIMAL(6,2) NOT NULL Duration field containing 2 decimals to handle absence requirements
33 ABS_UNIT_TYPE Character(1) VARCHAR2(1) NOT NULL Unit Type in which the absence balances are displayed
D=Day(s)
H=Hour(s)
34 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
35 DESCR_COMMENTS_HIS Long Character CLOB This field is used in a complex query that shows many descr fields.