PYKB_CN_TAX_VW

(SQL View)
 Index Back

Payroll Insights CN Taxes

Payroll Insights Canada Taxes View - Employee & Employer

SELECT A.COMPANY ,A.PAYGROUP ,A.PAY_END_DT ,A.OFF_CYCLE ,A.PAGE_NUM ,A.LINE_NUM ,A.SEPCHK ,'T' ,B.TAX_CLASS_CAN AS PY_CAT_ID ,A.CHECK_DT ,A.BUSINESS_UNIT ,A.DEPTID ,A.EMPLID ,A.EMPL_RCD ,A.EMPL_TYPE ,A.LOCATION ,'Taxes' ,X.XLATLONGNAME AS PY_CAT_ID_DESCR ,SUM(B.TAX_CUR) AS PY_TOT_CAT FROM PS_PAY_CHECK A ,PS_PAY_TAX_CAN B ,PSXLATITEM X 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.TAX_CUR <> 0 AND B.TAX_CLASS_CAN NOT IN ('TRV','TT4') AND X.FIELDNAME = 'TAX_CLASS_CAN' AND X.FIELDVALUE = B.TAX_CLASS_CAN AND X.EFFDT = ( SELECT MAX(X2.EFFDT) FROM PSXLATITEM X2 WHERE X2.FIELDNAME = X.FIELDNAME AND X2.FIELDVALUE = X.FIELDVALUE) 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, B.TAX_CLASS_CAN, X.XLATLONGNAME

# 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