TRW_PY_CPR_TAX (SQL View)	Index Back
NA Payroll Can. CPR Tax View This view generates the Employer Paid Canadian Pension Plan pay tax detail by using the Employee Paid Canadian Pension Plan pay tax detail. The Employee Paid Canadian Pension Plan (Canadian Tax Class Code: CPP) tax amount equals the Employer Paid Canadian Pension Plan tax amount. (Canadian Tax Class Code: CPR) This is necessary since NA Payroll no longer calculates Canadian Tax Class code, CPR.

TRW_PY_CPR_TAX

(SQL View)

NA Payroll Can. CPR Tax View

This view generates the Employer Paid Canadian Pension Plan pay tax detail by using the Employee Paid Canadian Pension Plan pay tax detail. The Employee Paid Canadian Pension Plan (Canadian Tax Class Code: CPP) tax amount equals the Employer Paid Canadian Pension Plan tax amount. (Canadian Tax Class Code: CPR) This is necessary since NA Payroll no longer calculates Canadian Tax Class code, CPR.

SELECT PC.COMPANY , PC.PAYGROUP , PC.PAY_END_DT , PC.OFF_CYCLE , PC.PAGE_NUM , PC.LINE_NUM , PC.SEPCHK , PC.EMPLID , PC.EMPL_RCD , PT.PROVINCE , 'CPR' , 0 , PT.TAX_CUR , PG.CURRENCY_CD , PC.PAYCHECK_NBR , PC.CHECK_DT , 'CAN' FROM PS_PAY_CHECK PC , PS_PAYGROUP_TBL PG , PS_PAY_TAX_CAN PT WHERE PC.PAYCHECK_STATUS IN ('F','R') 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 = PT.COMPANY AND PC.PAYGROUP = PT.PAYGROUP AND PC.PAY_END_DT = PT.PAY_END_DT AND PC.OFF_CYCLE = PT.OFF_CYCLE AND PC.PAGE_NUM = PT.PAGE_NUM AND PC.LINE_NUM = PT.LINE_NUM AND PC.SEPCHK = PT.SEPCHK AND PT.TAX_CUR <> 0 AND PT.TAX_CLASS_CAN IN ('CPP', 'CP2')

# 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 PROVINCE Character(6) VARCHAR2(6) NOT NULL Province

11 TAX_CLASS_CAN Character(3) VARCHAR2(3) NOT NULL Canadian Tax Class
ALL=Total Gross
CBT=Canadian Bonus Tax
CIT=Canadian Income Tax (T4)
CP2=CPP Second Additional Employee
CPA=Pension Adjustment
CPP=Canada Pension Plan Employee
CPR=Canada Pension Plan Employer
CR2=CPP Second Additional Employer
EIE=Employment Insurance Employee
EIR=Employment Insurance Employer
HTX=Provincial Health Tax
PYT=Payroll Tax
QBT=Quebec Bonus Tax
QCD=Quebec Tax Deduction
QIE=Quebec Parental Plan Employee
QIR=Quebec Parental Plan Employer
QIT=Quebec Income Tax (RL-1)
QP2=QPP Second Additional Employee
QPP=Quebec Pension Plan Employee
QPR=Quebec Pension Plan Employer
QR2=QPP Second Additional Employer
RV2=Quebec Income Tax (RL-2)
T4A=Canadian Income Tax (T4A)
TRV=True RL
TT4=True T4

12 TRW_CUR_EE_AMT Signed Number(12,2) DECIMAL(10,2) NOT NULL Employee Current Deduction

13 TRW_CUR_ER_AMT Signed Number(12,2) DECIMAL(10,2) NOT NULL Employer Current Amount

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.

17 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

#	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	PROVINCE	Character(6)	VARCHAR2(6) NOT NULL	Province
11	TAX_CLASS_CAN	Character(3)	VARCHAR2(3) NOT NULL	Canadian Tax Class ALL=Total Gross CBT=Canadian Bonus Tax CIT=Canadian Income Tax (T4) CP2=CPP Second Additional Employee CPA=Pension Adjustment CPP=Canada Pension Plan Employee CPR=Canada Pension Plan Employer CR2=CPP Second Additional Employer EIE=Employment Insurance Employee EIR=Employment Insurance Employer HTX=Provincial Health Tax PYT=Payroll Tax QBT=Quebec Bonus Tax QCD=Quebec Tax Deduction QIE=Quebec Parental Plan Employee QIR=Quebec Parental Plan Employer QIT=Quebec Income Tax (RL-1) QP2=QPP Second Additional Employee QPP=Quebec Pension Plan Employee QPR=Quebec Pension Plan Employer QR2=QPP Second Additional Employer RV2=Quebec Income Tax (RL-2) T4A=Canadian Income Tax (T4A) TRV=True RL TT4=True T4
12	TRW_CUR_EE_AMT	Signed Number(12,2)	DECIMAL(10,2) NOT NULL	Employee Current Deduction
13	TRW_CUR_ER_AMT	Signed Number(12,2)	DECIMAL(10,2) NOT NULL	Employer Current Amount
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.
17	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country