HGA_EAHD_DTL_VW(SQL View) |
Index Back |
|---|---|
Extended Absence Header ViewExtended Absence View used for Fluid Approvals |
| SELECT AWE1.EMPLID , AWE1.EMPL_RCD , AWE1.TRANSACTION_NBR , AWE1.TRANSACTION_NBR_EA , EA.ABSENCE_REASON , ' ' ,' ' , E.SETID_JOBCODE , C.JOBCODE , C.DESCR , EA.REQUEST_DT , %Sql(HR_HT_EMAIL_DATE, EA.REQUEST_DT) , EA.BGN_DT , %Sql(HR_HT_EMAIL_DATE, EA.BGN_DT) , EA.PIN_TAKE_NUM , EA.END_DT , %Sql(HR_HT_EMAIL_DATE, EA.END_DT) , EA.LAST_UPDT_DT , D.PIN_NM , D.DESCR , CASE WHEN SS.COUNTRY = 'ALL' THEN 'A' ELSE 'C' END , EA.WF_STATUS , G.XLATLONGNAME , H.NAME , H.NAME_DISPLAY , STA.COMMENTS , ' ' , EA.RETURN_DT , %Sql(HR_HT_EMAIL_DATE, EA.RETURN_DT) , BAL.PRD_END_DT , %Sql(HR_HT_EMAIL_DATE, BAL.PRD_END_DT) , 'X' ,' ' FROM PS_GP_ABS_EA_AWE AWE1 LEFT OUTER JOIN PS_GP_ABS_BALDT_VW BAL ON BAL.EMPLID = AWE1.EMPLID AND BAL.EMPL_RCD = AWE1.EMPL_RCD , PS_GP_ABS_EA EA , PS_GP_ABS_SS SS , PS_GP_ABS_EA_STA STA , PS_JOB E , PS_JOBCODE_TBL C , PS_GP_PIN D , PSXLATITEM G , PS_PERSON_NAME H WHERE EA.EMPLID = AWE1.EMPLID AND EA.EMPL_RCD = AWE1.EMPL_RCD AND ((EA.TRANSACTION_NBR = AWE1.TRANSACTION_NBR_EA) OR (EA.TRANSACTION_NBR = AWE1.TRANSACTION_NBR AND AWE1.TRANSACTION_NBR_EA = 0)) AND ((STA.TRANSACTION_NBR = AWE1.TRANSACTION_NBR_EA) OR (STA.TRANSACTION_NBR = AWE1.TRANSACTION_NBR AND AWE1.TRANSACTION_NBR_EA = 0)) AND STA.SEQNUM = ( SELECT MAX(STA2.SEQNUM) FROM PS_GP_ABS_EA_STA STA2 WHERE STA2.TRANSACTION_NBR = STA.TRANSACTION_NBR) AND E.EMPLID = AWE1.EMPLID AND E.EMPL_RCD = AWE1.EMPL_RCD AND SS.PIN_TAKE_NUM = EA.PIN_TAKE_NUM AND SS.EFFDT = ( SELECT MAX(SS2.EFFDT) FROM PS_GP_ABS_SS SS2 WHERE SS2.COUNTRY = SS.COUNTRY AND SS2.PIN_TAKE_NUM = SS.PIN_TAKE_NUM AND SS2.EFFDT <= EA.BGN_DT) AND SS.EFF_STATUS = 'A' 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 D.PIN_NUM = EA.PIN_TAKE_NUM AND G.FIELDNAME = 'WF_STATUS' AND G.FIELDVALUE = EA.WF_STATUS AND %EffdtCheck(PSXLATITEM, G, %CurrentDateIn) AND G.EFF_STATUS = 'A' AND H.EMPLID = AWE1.EMPLID |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 3 | 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. |
| 4 | TRANSACTION_NBR_EA |
Number(15,0) | DECIMAL(15) NOT NULL | The Extendned Absence transaction number |
| 5 | ABSENCE_REASON | Character(3) | VARCHAR2(3) NOT NULL | absence reason |
| 6 | DESCR6 | Character(30) | VARCHAR2(30) NOT NULL | This field is used in a complex query that shows many descr fields. |
| 7 | DESCR5 | Character(30) | VARCHAR2(30) NOT NULL | This field is used in a complex query that shows many descr fields. |
| 8 | SETID_JOBCODE | Character(5) | VARCHAR2(5) NOT NULL | Job Code Set ID |
| 9 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 10 | JOBTITLE | Character(30) | VARCHAR2(30) NOT NULL | Job Title |
| 11 | REQUEST_DT | Date(10) | DATE | Request Date |
| 12 | DESCR3 | Character(30) | VARCHAR2(30) NOT NULL | Descr 3 |
| 13 | BGN_DT | Date(10) | DATE | Begin Date |
| 14 | DESCR20 | Character(20) | VARCHAR2(20) NOT NULL | Description |
| 15 | PIN_TAKE_NUM | Number(8,0) | INTEGER NOT NULL | Absence Take |
| 16 | END_DT | Date(10) | DATE | end date |
| 17 | DESCR25 | Character(25) | VARCHAR2(25) NOT NULL | Short description |
| 18 | LAST_UPDT_DT | Date(10) | DATE | Date of last Update |
| 19 | PIN_NM | Character(18) | VARCHAR2(18) NOT NULL | Element Name field |
| 20 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 21 | USED_BY | Character(1) | VARCHAR2(1) NOT NULL |
USED_BY
A=All Countries C=Specific Country |
| 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 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |
| 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 | COMMENTS | Long Character | CLOB | Comment |
| 27 | COMMENTS_LONG | Long Character | CLOB | Comment Text |
| 28 | RETURN_DT | Date(10) | DATE | Return Date |
| 29 | DESCR4 | Character(30) | VARCHAR2(30) NOT NULL | Descr 4 |
| 30 | PRD_END_DT | Date(10) | DATE | Pay Period End Date |
| 31 | DESCR25_2 | Character(25) | VARCHAR2(25) NOT NULL | Description part 2 |
| 32 | DESCR_COMMENTS_HIS | Long Character | CLOB | This field is used in a complex query that shows many descr fields. |
| 33 | DESCR7 | Character(30) | VARCHAR2(30) NOT NULL | This field is used in a complex query that shows many descr fields. |