PYKB_CN_ERN_VW

(SQL View)
 Index Back

Payroll Insights CN Earns

Payroll Insights Canada Earnings View

SELECT A.COMPANY ,A.PAYGROUP ,A.PAY_END_DT ,A.OFF_CYCLE ,A.PAGE_NUM ,A.LINE_NUM ,A.SEPCHK ,'E' ,C.PY_CATEGORY_ID AS PY_CAT_ID ,A.CHECK_DT ,A.BUSINESS_UNIT ,A.DEPTID ,A.EMPLID ,A.EMPL_RCD ,A.EMPL_TYPE ,A.LOCATION ,'Earnings' ,C.DESCR AS PY_CAT_ID_DESCR ,SUM(B.REG_EARNS+B.REG_HRLY_EARNS) AS PY_TOT_CAT FROM PS_PAY_CHECK A ,PS_PAY_EARNINGS B ,PS_PY_CTGYSEL_TBL C WHERE A.COMPANY = B.COMPANY AND A.PAYGROUP = B.PAYGROUP AND A.PAY_END_DT = B.PAY_END_DT AND A.OFF_CYCLE = B.OFF_CYCLE AND A.PAGE_NUM = B.PAGE_NUM AND A.LINE_NUM = B.LINE_NUM AND A.SEPCHK = B.SEPCHK AND A.PAYCHECK_STATUS = 'F' AND A.COUNTRY = 'CAN' AND (B.REG_EARNS <> 0 OR B.REG_HRLY_EARNS <> 0) AND C.PY_CATGRY_TYPE = 'E' AND C.COUNTRY = A.COUNTRY AND C.PY_CTGRY_USED_BY = 'K' AND C.PY_DATA_TYPE_CD IN ('Y','N') AND EXISTS ( SELECT 'X' FROM PS_PY_CAT_CODE_TBL D WHERE D.PY_CATGRY_TYPE = C.PY_CATGRY_TYPE AND D.COUNTRY = C.COUNTRY AND D.PY_CTGRY_USED_BY = C.PY_CTGRY_USED_BY AND D.PY_CATEGORY_ID = C.PY_CATEGORY_ID AND D.CODE_SEL = B.ERNCD_REG_EARNS) GROUP BY A.COMPANY ,A.PAYGROUP ,A.PAY_END_DT, A.OFF_CYCLE, A.PAGE_NUM, A.LINE_NUM, A.SEPCHK, A.CHECK_DT, A.BUSINESS_UNIT, A.DEPTID, A.EMPLID, A.EMPL_RCD, A.EMPL_TYPE, A.LOCATION, C.PY_CATEGORY_ID, C.DESCR

# 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 PY_CAT_TYPE Character(1) VARCHAR2(1) NOT NULL Category Type
D=Deductions
E=Earnings
O=Other Earnings
T=Taxes
V=Overtime
9 PY_CAT_ID Character(6) VARCHAR2(6) NOT NULL Category ID
10 CHECK_DT Date(10) DATE Check date.
11 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
12 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
13 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
14 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
15 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
E=Exception Hourly
H=Hourly
N=Not Applicable
S=Salaried
16 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
17 PY_CAT_TYPE_DESCR Character(30) VARCHAR2(30) NOT NULL Category Type Description
18 PY_CAT_ID_DESCR Character(30) VARCHAR2(30) NOT NULL Category ID Description
19 PY_TOT_CAT Signed Number(19,2) DECIMAL(17,2) NOT NULL Total by Category