TRW_PY_OT_ERNS

(SQL View)
 Index Back

NA Payroll OT Earnings View

This view retrieves overtime earnings pay detail from North American Payroll.

SELECT PC.COMPANY , PC.PAYGROUP , PC.PAY_END_DT , PC.OFF_CYCLE , PC.PAGE_NUM , PC.LINE_NUM , PC.SEPCHK , PE.ADDL_NBR , 4 , PC.EMPLID , PC.EMPL_RCD , PE.ERNCD_OT_HRS , PE.OT_HRLY_EARNS , PG.CURRENCY_CD , PC.PAYCHECK_NBR , PC.CHECK_DT FROM PS_PAY_CHECK PC , PS_PAYGROUP_TBL PG , PS_PAY_EARNINGS PE WHERE PC.PAYCHECK_STATUS IN ('F','R','A') AND PC.COMPANY = PG.COMPANY AND PC.PAYGROUP = PG.PAYGROUP AND PG.EFFDT = ( SELECT MAX(PG_ED.EFFDT) FROM PS_PAYGROUP_TBL PG_ED WHERE PG.COMPANY = PG_ED.COMPANY AND PG.PAYGROUP = PG_ED.PAYGROUP AND PG_ED.EFFDT <= PC.PAY_END_DT) AND PC.COMPANY = PE.COMPANY AND PC.PAYGROUP = PE.PAYGROUP AND PC.PAY_END_DT = PE.PAY_END_DT AND PC.OFF_CYCLE = PE.OFF_CYCLE AND PC.PAGE_NUM = PE.PAGE_NUM AND PC.LINE_NUM = PE.LINE_NUM AND PC.SEPCHK = PE.SEPCHK AND PE.OT_HRLY_EARNS <> 0

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
2 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
3 PAY_END_DT Date(10) DATE Pay Period End Date
4 OFF_CYCLE Character(1) VARCHAR2(1) NOT NULL Off Cycle ?
5 PAGE_NUM Number(5,0) INTEGER NOT NULL Page Nbr
6 LINE_NUM Number(3,0) SMALLINT NOT NULL Line Nbr
7 SEPCHK Number(2,0) SMALLINT NOT NULL Separate check number.
8 ADDL_NBR Number(3,0) SMALLINT NOT NULL Additional Pay Line Nbr
9 SEQNUM Number(3,0) SMALLINT NOT NULL Sequence Number
10 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
11 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
12 ERNCD_OT_HRS Character(3) VARCHAR2(3) NOT NULL OT Hours Earnings Type

Default Value: PAYGROUP_TBL.ERNCD_OT_HRS

13 OT_HRLY_EARNS Signed Number(12,2) DECIMAL(10,2) NOT NULL Overtime Hourly Earnings
14 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
15 PAYCHECK_NBR Number(15,0) DECIMAL(15) NOT NULL Paycheck number.
16 CHECK_DT Date(10) DATE Check date.