GPNZ_RA_EAM_VW

(SQL View)
 Index Back

Rolling average end amt/Hrs

Rolling average end amount and hours

SELECT A.EMPLID ,A.CAL_RUN_ID ,A.EMPL_RCD ,A.GP_PAYGROUP ,A.CAL_ID ,A.ORIG_CAL_RUN_ID ,A.RSLT_SEG_NUM ,A.PIN_NUM ,A.EMPL_RCD_ACUM ,A.ACM_FROM_DT ,A.ACM_THRU_DT ,A.SLICE_BGN_DT ,A.SLICE_END_DT ,A.CALC_RSLT_VAL ,%DateTimeOut(B.RUN_FINALIZED_TS) FROM PS_GP_RSLT_ACUM A ,PS_GP_CAL_RUN B WHERE A.COUNTRY='NZL' AND A.ACM_THRU_DT IS NULL AND A.PIN_NUM IN ( SELECT B.PIN_NUM FROM PS_GP_PIN B WHERE B.PIN_TYPE='AC' AND B.PIN_CODE IN ('LVE AC ROLLAVG AMT NZL','LVE AC ROLLAVG HRS NZL')) AND A.RSLT_SEG_NUM = ( SELECT MAX(AC.RSLT_SEG_NUM) FROM PS_GP_RSLT_ACUM AC WHERE AC.EMPLID=A.EMPLID AND AC.CAL_RUN_ID=A.CAL_RUN_ID AND AC.EMPL_RCD= A.EMPL_RCD AND AC.GP_PAYGROUP =A.GP_PAYGROUP AND AC.CAL_ID=A.CAL_ID AND AC.ORIG_CAL_RUN_ID =A.ORIG_CAL_RUN_ID AND AC.PIN_NUM=A.PIN_NUM) AND A.SEQ_NUM8 = ( SELECT MAX(AC.SEQ_NUM8) FROM PS_GP_RSLT_ACUM AC WHERE AC.EMPLID=A.EMPLID AND AC.CAL_RUN_ID=A.CAL_RUN_ID AND AC.EMPL_RCD= A.EMPL_RCD AND AC.GP_PAYGROUP =A.GP_PAYGROUP AND AC.CAL_ID=A.CAL_ID AND AC.ORIG_CAL_RUN_ID =A.ORIG_CAL_RUN_ID AND AC.PIN_NUM=A.PIN_NUM) AND A.CAL_RUN_ID=A.ORIG_CAL_RUN_ID AND A.CAL_RUN_ID=B.CAL_RUN_ID AND B.RUN_FINALIZED_IND = 'Y' AND B.OFF_CYCLE='Y' UNION SELECT A.EMPLID ,A.CAL_RUN_ID ,A.EMPL_RCD ,A.GP_PAYGROUP ,A.CAL_ID ,A.ORIG_CAL_RUN_ID ,A.RSLT_SEG_NUM ,A.PIN_NUM ,A.EMPL_RCD_ACUM , A.ACM_FROM_DT ,A.ACM_THRU_DT ,A.SLICE_BGN_DT ,A.SLICE_END_DT ,A.CALC_RSLT_VAL ,%DateTimeOut(B.RUN_FINALIZED_TS) FROM PS_GP_RSLT_ACUM A ,PS_GP_CAL_RUN B ,PS_GP_CAL_RUN_DTL E WHERE A.COUNTRY='NZL' AND A.ACM_THRU_DT IS NULL AND A.PIN_NUM IN ( SELECT B.PIN_NUM FROM PS_GP_PIN B WHERE B.PIN_TYPE='AC' AND B.PIN_CODE IN ('LVE AC ROLLAVG AMT NZL','LVE AC ROLLAVG HRS NZL')) AND A.RSLT_SEG_NUM = ( SELECT MAX(AC.RSLT_SEG_NUM) FROM PS_GP_RSLT_ACUM AC WHERE AC.EMPLID=A.EMPLID AND AC.CAL_RUN_ID=A.CAL_RUN_ID AND AC.EMPL_RCD= A.EMPL_RCD AND AC.GP_PAYGROUP =A.GP_PAYGROUP AND AC.CAL_ID=A.CAL_ID AND AC.ORIG_CAL_RUN_ID =A.ORIG_CAL_RUN_ID AND AC.PIN_NUM=A.PIN_NUM) AND A.SEQ_NUM8 = ( SELECT MAX(AC.SEQ_NUM8) FROM PS_GP_RSLT_ACUM AC WHERE AC.EMPLID=A.EMPLID AND AC.CAL_RUN_ID=A.CAL_RUN_ID AND AC.EMPL_RCD= A.EMPL_RCD AND AC.GP_PAYGROUP =A.GP_PAYGROUP AND AC.CAL_ID=A.CAL_ID AND AC.ORIG_CAL_RUN_ID =A.ORIG_CAL_RUN_ID AND AC.PIN_NUM=A.PIN_NUM) AND A.CAL_RUN_ID=A.ORIG_CAL_RUN_ID AND A.CAL_RUN_ID=B.CAL_RUN_ID AND B.RUN_FINALIZED_IND = 'Y' AND B.CAL_RUN_ID=E.CAL_RUN_ID AND A.GP_PAYGROUP=E.GP_PAYGROUP AND A.CAL_ID=E.CAL_ID AND A.CAL_ID IN ( SELECT C.CAL_ID FROM PS_GP_CALENDAR C ,PS_GP_RUN_TYPE D WHERE C.RUN_TYPE=D.RUN_TYPE AND D.CALC_TYPE ='P' AND A.GP_PAYGROUP=C.GP_PAYGROUP )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
4 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
5 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
6 ORIG_CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Original Calendar Group ID
7 RSLT_SEG_NUM Number(4,0) SMALLINT NOT NULL Result Segment Number
8 PIN_NUM Number(8,0) INTEGER NOT NULL PIN Number
9 EMPL_RCD_ACUM Number(3,0) SMALLINT NOT NULL Accumulator Empl record
10 ACM_FROM_DT Date(10) DATE From Date
11 ACM_THRU_DT Date(10) DATE Through Date
12 SLICE_BGN_DT Date(10) DATE Element Slice Begin Date
13 SLICE_END_DT Date(10) DATE Element Slice End Date
14 CALC_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculation Numeric Result
15 GPNZ_DESCR Character(30) VARCHAR2(30) NOT NULL This object is a copy of the standard DESCR field, however it contains additional labels not available on the standard.