HGA_BALSS_P_VW

(SQL View)
 Index Back

Absence Balances

Current Absence Balances from processed periods.

SELECT DISTINCT A.EMPLID , A.EMPL_RCD , C.PIN_NUM , D.PIN_NUM , C.ACM_FROM_DT , C.ACM_THRU_DT , C.ACM_PRD_OPTN , C.USER_KEY1 , C.USER_KEY2 , C.USER_KEY3 , C.USER_KEY4 , C.USER_KEY5 , C.USER_KEY6 , C.CALC_RSLT_VAL , SS.UNIT_TYPE , A.PRD_END_DT 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 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 A.RSLT_REV_NUM = ( SELECT MAX(PPS.RSLT_REV_NUM) FROM PS_GP_PYE_PRC_STAT PPS ,PS_GP_CAL_RUN G 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 G.CAL_RUN_ID=PPS.CAL_RUN_ID ) AND A.RSLT_VER_NUM = ( SELECT MAX(PPS.RSLT_VER_NUM) FROM PS_GP_PYE_PRC_STAT PPS ,PS_GP_CAL_RUN G 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 G.CAL_RUN_ID=PPS.CAL_RUN_ID ) AND B.RSLT_SEG_NUM = ( SELECT MAX(H.RSLT_SEG_NUM) FROM PS_GP_PYE_SEG_STAT H ,PS_GP_CAL_RUN G 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 G.CAL_RUN_ID=H.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 %EffdtCheck(GP_BAL_SS,SS,%CurrentDateIn) AND (E.USED_BY = 'A' OR E.COUNTRY = SS.COUNTRY) AND (C.CALC_RSLT_VAL <> 0 OR SS.STORE_RSLT_IF_ZERO = 'Y')

# 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 PIN_ENTL_BAL_NUM Number(8,0) INTEGER NOT NULL Element number field
4 PIN_PARENT_NUM Number(8,0) INTEGER NOT NULL Parent Element Number
5 ACM_FROM_DT Date(10) DATE From Date
6 ACM_THRU_DT Date(10) DATE Through Date
7 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
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 CALC_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculation Numeric Result
15 UNIT_TYPE Character(1) VARCHAR2(1) NOT NULL Unit Type in which the absence balances are displayed
D=Days
H=Hours
16 PRD_END_DT Date(10) DATE Pay Period End Date