HGA_CAN_EMHD_VW(SQL View) |
Index Back |
|---|---|
Absence Cancel Details ViewThis view contains all the details of the Absence Cancellation 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 , I3.HR_WF_ACTION , 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 ,I3.COMMENTS , BAL.PRD_END_DT , %Sql(HR_HT_EMAIL_DATE,BAL.PRD_END_DT) , A.DURATION_ABS , SS.UNIT_TYPE , %Sql(FUNCLIB_HR_CHAR, A.DURATION_ABS) %Concat ' ' %Concat G1.XLATLONGNAME , 'X' ,( SELECT R.DESCR FROM PS_HGA_CAN_RSN R WHERE R.USED_BY = D.USED_BY AND R.COUNTRY = D.COUNTRY AND R.ABS_TYPE_OPTN = T.ABS_TYPE_OPTN AND R.ABS_CAN_REASON = A.ABS_CAN_REASON AND %EffdtCheck(HGA_CAN_RSN, R, %CurrentDateIn)) 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_HGA_CAN_XREF X , PSXLATITEM G , PS_PERSON_NAME H , PS_GP_ABS_SS_STA I ,PS_GP_ABS_SS_STA I3, PSXLATITEM G1 , PS_GP_ABS_SS SS , PS_GP_ABS_TAKE T 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.EOAWPARENT_THREAD = ( SELECT MAX(Y.EOAWPARENT_THREAD) FROM PS_HGA_CAN_XREF Y WHERE X.TRANSACTION_NBR = Y.TRANSACTION_NBR AND Y.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 AND I2.HGA_APPR_PRCS='REQ') OR (I2.EMPLID_CURR_APPR <> I2.EMPLID AND I2.SEQNUM = 1))) AND I3.EMPLID = F.EMPLID AND I3.EMPL_RCD = F.EMPL_RCD AND I3.TRANSACTION_NBR = F.TRANSACTION_NBR AND I3.SEQNUM = ( SELECT MAX(I4.SEQNUM) FROM PS_GP_ABS_SS_STA I4 WHERE I3.TRANSACTION_NBR = I4.TRANSACTION_NBR AND I3.EMPLID = I4.EMPLID AND I3.EMPL_RCD = I4.EMPL_RCD AND I3.BGN_DT = I4.BGN_DT AND I3.PIN_TAKE_NUM = I4.PIN_TAKE_NUM AND I3.END_DT = I4.END_DT AND I4.HGA_APPR_PRCS='CAN' AND I4.HR_WF_ACTION='SUB') 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 = 'HR_WF_ACTION' AND G.FIELDVALUE = I3.HR_WF_ACTION 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 AND T.PIN_NUM=A.PIN_TAKE_NUM AND %EffdtCheck(GP_ABS_TAKE,T,%CurrentDateIn) |
| # | 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 | HGA_CAN_COMMENTS | Long Character | CLOB | Absence Cancellation Comments |
| 31 | PRD_END_DT | Date(10) | DATE | Pay Period End Date |
| 32 | DESCR25_2 | Character(25) | VARCHAR2(25) NOT NULL | Description part 2 |
| 33 | DURATION_ABS | Number(7,2) | DECIMAL(6,2) NOT NULL | Duration field containing 2 decimals to handle absence requirements |
| 34 | ABS_UNIT_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Unit Type in which the absence balances are displayed
D=Day(s) H=Hour(s) |
| 35 | DESCR2 | Character(30) | VARCHAR2(30) NOT NULL | Descr2 |
| 36 | DESCR_COMMENTS_HIS | Long Character | CLOB | This field is used in a complex query that shows many descr fields. |
| 37 | DESCR3 | Character(30) | VARCHAR2(30) NOT NULL | Descr 3 |