GP_ABS_EABAL_VW(SQL View) |
Index Back |
|---|---|
Absence BalancesAbsence Balances |
| SELECT DISTINCT A.EMPLID , A.EMPL_RCD , EA.TRANSACTION_NBR , EA.TRANSACTION_NBR_EA , C.PIN_NUM , C.ACM_FROM_DT , C.ACM_THRU_DT , C.USER_KEY1 , C.USER_KEY2 , C.USER_KEY3 , C.USER_KEY4 , C.USER_KEY5 , C.USER_KEY6 , C.ACM_PRD_OPTN , F.DESCR , %Truncate(C.CALC_RSLT_VAL, 2) , SS.UNIT_TYPE , A.PRD_END_DT , %Sql(GP_ABS_NUM_TO_CHAR, %Truncate(C.CALC_RSLT_VAL, 2)) %Concat ' ' %Concat G.XLATLONGNAME FROM PS_GP_PYE_PRC_STAT A , PS_GP_PYE_SEG_STAT B , PS_GP_RSLT_ACUM C , PS_GP_PIN_CMPNT D , PS_GP_PIN E , PS_GP_BAL_SS SS , PS_GP_ABS_EA_AWE EA , PS_GP_PIN F , PSXLATITEM G WHERE A.PRC_ORD_TS = ( SELECT MAX(F.PRC_ORD_TS) FROM PS_GP_PYE_PRC_STAT F ,PS_GP_CAL_RUN G WHERE F.CALC_TYPE='A' AND F.EMPLID=A.EMPLID AND F.EMPL_RCD=A.EMPL_RCD AND G.CAL_RUN_ID=F.CAL_RUN_ID AND G.RUN_FINALIZED_IND = 'Y' ) AND A.RSLT_REV_NUM = ( SELECT MAX(PPS.RSLT_REV_NUM) FROM PS_GP_PYE_PRC_STAT PPS WHERE PPS.EMPLID=A.EMPLID AND PPS.EMPL_RCD=A.EMPL_RCD AND PPS.GP_PAYGROUP=A.GP_PAYGROUP AND PPS.CAL_ID=A.CAL_ID AND PPS.ORIG_CAL_RUN_ID=A.ORIG_CAL_RUN_ID) AND A.RSLT_VER_NUM = ( SELECT MAX(PPS.RSLT_VER_NUM) FROM PS_GP_PYE_PRC_STAT PPS WHERE PPS.EMPLID=A.EMPLID AND PPS.EMPL_RCD=A.EMPL_RCD AND PPS.GP_PAYGROUP=A.GP_PAYGROUP AND PPS.CAL_ID=A.CAL_ID AND PPS.ORIG_CAL_RUN_ID=A.ORIG_CAL_RUN_ID) AND B.RSLT_SEG_NUM = ( SELECT MAX(H.RSLT_SEG_NUM) FROM PS_GP_PYE_SEG_STAT H WHERE H.EMPLID=B.EMPLID AND H.CAL_RUN_ID=B.CAL_RUN_ID AND H.EMPL_RCD=B.EMPL_RCD AND H.GP_PAYGROUP=B.GP_PAYGROUP AND H.CAL_ID=B.CAL_ID AND H.ORIG_CAL_RUN_ID=B.ORIG_CAL_RUN_ID) AND A.EMPLID=B.EMPLID AND A.CAL_RUN_ID=B.CAL_RUN_ID AND A.EMPL_RCD=B.EMPL_RCD AND A.GP_PAYGROUP=B.GP_PAYGROUP AND A.CAL_ID=B.CAL_ID AND A.ORIG_CAL_RUN_ID=B.ORIG_CAL_RUN_ID AND B.EMPLID=C.EMPLID AND B.CAL_RUN_ID=C.CAL_RUN_ID AND B.EMPL_RCD=C.EMPL_RCD AND B.GP_PAYGROUP=C.GP_PAYGROUP AND B.CAL_ID=C.CAL_ID AND B.ORIG_CAL_RUN_ID=C.ORIG_CAL_RUN_ID AND B.RSLT_SEG_NUM=C.RSLT_SEG_NUM AND (C.EMPL_RCD = C.EMPL_RCD_ACUM OR C.ACM_TYPE = 'N') AND C.PIN_NUM=D.PIN_ENTL_BAL_NUM AND C.SLICE_END_DT >= C.ACM_FROM_DT AND (C.SLICE_END_DT <= C.ACM_THRU_DT OR C.ACM_THRU_DT IS NULL) AND D.PIN_NUM=E.PIN_NUM AND D.PIN_NUM = SS.PIN_ENTL_BAL_NUM AND SS.COUNTRY = A.COUNTRY AND (E.USED_BY = 'A' OR E.COUNTRY = SS.COUNTRY) AND (C.CALC_RSLT_VAL <> 0 OR SS.STORE_RSLT_IF_ZERO = 'Y') AND A.EMPLID = EA.EMPLID AND A.EMPL_RCD = EA.EMPL_RCD AND F.PIN_NUM = C.PIN_NUM AND G.FIELDNAME = 'ABS_UNIT_TYPE' AND G.FIELDVALUE = SS.UNIT_TYPE AND %EffdtCheck(PSXLATITEM, G, %CurrentDateIn) AND G.EFF_STATUS = 'A' |
| # | 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 | PIN_ENTL_BAL_NUM |
Number(8,0) | INTEGER NOT NULL | Element number field |
| 6 | ACM_FROM_DT |
Date(10) | DATE | From Date |
| 7 | ACM_THRU_DT |
Date(10) | DATE | Through Date |
| 8 | USER_KEY1 |
Character(25) | VARCHAR2(25) NOT NULL | User Key 1 |
| 9 | USER_KEY2 |
Character(25) | VARCHAR2(25) NOT NULL | User Key 2 |
| 10 | USER_KEY3 |
Character(25) | VARCHAR2(25) NOT NULL | User Key 3 |
| 11 | USER_KEY4 |
Character(25) | VARCHAR2(25) NOT NULL | User Key 4 |
| 12 | USER_KEY5 |
Character(25) | VARCHAR2(25) NOT NULL | User Key 5 |
| 13 | USER_KEY6 |
Character(25) | VARCHAR2(25) NOT NULL | User Key 6 |
| 14 | ACM_PRD_OPTN | Character(1) | VARCHAR2(1) NOT NULL |
Accumulator Period
1=Segment 2=Calendar Period 3=Month to Date 4=Quarter to Date 5=Year to Date 6=Custom Period |
| 15 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 16 | CALC_RSLT_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Calculation Numeric Result |
| 17 | UNIT_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Unit Type in which the absence balances are displayed
D=Days H=Hours |
| 18 | PRD_END_DT | Date(10) | DATE | Pay Period End Date |
| 19 | DESCR40 | Character(40) | VARCHAR2(40) NOT NULL | Description |