PY_EHRS_WC_VW

(SQL View)
 Index Back

Hours on paycheck

This view shows the hours on pay earnings and pay other earnings for a check.

SELECT A.COMPANY , A.PAYGROUP , A.PAY_END_DT , B.OFF_CYCLE , B.PAGE_NUM , B.LINE_NUM , B.SEPCHK , B.EMPLID , B.EMPL_RCD , B.NAME , B.DEPTID , B.BUSINESS_UNIT , SUM(B.TOTAL_ERN_HRS) AS TEMP_HRS FROM PS_PAY_CALENDAR A , PS_PAY_EARNS_WC_VW B , PS_PY_WC_PAYGRP D WHERE A.COMPANY = D.COMPANY AND A.PAYGROUP = D.PAYGROUP AND D.PY_INCL_PAYGRP = 'Y' AND A.PAY_END_DT = ( SELECT MAX(CAL.PAY_END_DT) FROM PS_PAY_CALENDAR CAL WHERE CAL.COMPANY = A.COMPANY AND CAL.PAYGROUP = A.PAYGROUP AND (CAL.PAY_PRECALC_RUN = 'Y' OR CAL.PAY_CALC_RUN = 'Y') AND CAL.PAY_CONFIRM_RUN = 'N') AND B.COMPANY = A.COMPANY AND B.COMPANY = A.COMPANY AND B.PAYGROUP = A.PAYGROUP AND B.PAY_END_DT = A.PAY_END_DT GROUP BY A.COMPANY, A.PAYGROUP, A.PAY_END_DT, B.OFF_CYCLE, B.PAGE_NUM, B.LINE_NUM, B.SEPCHK, B.EMPLID, B.EMPL_RCD, B.NAME , B.DEPTID , B.BUSINESS_UNIT UNION ALL SELECT A.COMPANY , A.PAYGROUP , A.PAY_END_DT , B.OFF_CYCLE , B.PAGE_NUM , B.LINE_NUM , B.SEPCHK , B.EMPLID , B.EMPL_RCD , B.NAME , B.DEPTID , B.BUSINESS_UNIT , SUM(B.OTH_HRS) AS TEMP_HRS FROM PS_PAY_CALENDAR A , PS_PAY_OERNS_WC_VW B , PS_PY_WC_PAYGRP D WHERE A.COMPANY = D.COMPANY AND A.PAYGROUP = D.PAYGROUP AND D.PY_INCL_PAYGRP = 'Y' AND A.PAY_END_DT = ( SELECT MAX(CAL.PAY_END_DT) FROM PS_PAY_CALENDAR CAL WHERE CAL.COMPANY = A.COMPANY AND CAL.PAYGROUP = A.PAYGROUP AND (CAL.PAY_PRECALC_RUN = 'Y' OR CAL.PAY_CALC_RUN = 'Y') AND CAL.PAY_CONFIRM_RUN = 'N') AND B.COMPANY = A.COMPANY AND B.COMPANY = A.COMPANY AND B.PAYGROUP = A.PAYGROUP AND B.PAY_END_DT = A.PAY_END_DT AND EXISTS ( SELECT 'X' FROM PS_PY_WC_CO_ERN E WHERE E.COMPANY = B.COMPANY AND E.ERNCD = B.ERNCD ) GROUP BY A.COMPANY, A.PAYGROUP, A.PAY_END_DT, B.OFF_CYCLE, B.PAGE_NUM, B.LINE_NUM, B.SEPCHK, B.EMPLID, B.EMPL_RCD, B.NAME , B.DEPTID, B.BUSINESS_UNIT

# 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 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
9 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
10 NAME Character(50) VARCHAR2(50) NOT NULL Name
11 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
12 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
13 TOTAL_ERN_HRS Signed Number(12,2) DECIMAL(10,2) NOT NULL Sum of earn hours, reg hours and OT hours on pay earnings.