BN_PY_DEDN_VW (SQL View)	Index Back
Benefit Payroll Deductions This is a view of the employee's latest Benefit Deductions from North American Payroll. Data is selected from PS_PAY_CHECK and PS_PAY_DEDUCTION for the most current date.

BN_PY_DEDN_VW

(SQL View)

Benefit Payroll Deductions

This is a view of the employee's latest Benefit Deductions from North American Payroll. Data is selected from PS_PAY_CHECK and PS_PAY_DEDUCTION for the most current date.

SELECT A.EMPLID ,B.BENEFIT_RCD_NBR ,B.PLAN_TYPE ,B.BENEFIT_PLAN ,B.DEDCD ,B.DED_CLASS ,B.DED_SLSTX_CLASS ,B.CALCULATED_BASE ,B.DED_CUR ,B.PAY_END_DT ,B.PAYGROUP FROM PS_PAY_CHECK A ,PS_PAY_DEDUCTION B 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 B.PLAN_TYPE <> '00' AND B.PAY_END_DT = ( SELECT MAX(C.PAY_END_DT) FROM PS_PAY_CHECK C ,PS_PAY_DEDUCTION D WHERE C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.PAY_END_DT <= %CurrentDateIn AND D.COMPANY = C.COMPANY AND D.PAYGROUP = C.PAYGROUP AND D.PAY_END_DT = C.PAY_END_DT AND D.OFF_CYCLE = C.OFF_CYCLE AND D.PAGE_NUM = C.PAGE_NUM AND D.LINE_NUM = C.LINE_NUM AND D.SEPCHK = C.SEPCHK AND C.PAYCHECK_STATUS='F' AND D.BENEFIT_RCD_NBR = B.BENEFIT_RCD_NBR AND D.PLAN_TYPE = B.PLAN_TYPE)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: PERSON

2 BENEFIT_RCD_NBR Number(3,0) SMALLINT NOT NULL Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.

3 PLAN_TYPE Character(2) VARCHAR2(2) NOT NULL Identifies a category of benefit plan, such as Medical, Dental, and Life (Insurance). A set of plan type codes, as well as rules for creating new codes, is provided by PeopleSoft. Plan Type codes determine how the system processes and defines eligibility for the various benefit plans.

4 BENEFIT_PLAN Character(6) VARCHAR2(6) NOT NULL Benefit Plan

5 DEDCD Character(6) VARCHAR2(6) NOT NULL Deduction Code

6 DED_CLASS Character(1) VARCHAR2(1) NOT NULL Deduction Classification
A=After-Tax
B=Before-Tax
L=QC Taxable Benefit
N=Nontaxable Benefit
P=Nontaxable Btax Benefit
T=Taxable Benefit

7 DED_SLSTX_CLASS Character(1) VARCHAR2(1) NOT NULL Sales Tax Type
B=None
G=Goods and Services Tax
H=Harmonized Sales Tax
I=Provincial Sales Tax Insurance
P=Provincial Sales Tax
Q=QC Prov Sales Tax Insurance
S=QC Provincial Sales Tax
T=Provincial Premium Tax
U=QC Provincial Premium Tax

8 CALCULATED_BASE Number(11,2) DECIMAL(10,2) NOT NULL Coverage Base

9 DED_CUR Signed Number(12,2) DECIMAL(10,2) NOT NULL Current Deduction

10 PAY_END_DT Date(10) DATE Pay Period End Date

11 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERSON
2	BENEFIT_RCD_NBR	Number(3,0)	SMALLINT NOT NULL	Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.
3	PLAN_TYPE	Character(2)	VARCHAR2(2) NOT NULL	Identifies a category of benefit plan, such as Medical, Dental, and Life (Insurance). A set of plan type codes, as well as rules for creating new codes, is provided by PeopleSoft. Plan Type codes determine how the system processes and defines eligibility for the various benefit plans.
4	BENEFIT_PLAN	Character(6)	VARCHAR2(6) NOT NULL	Benefit Plan
5	DEDCD	Character(6)	VARCHAR2(6) NOT NULL	Deduction Code
6	DED_CLASS	Character(1)	VARCHAR2(1) NOT NULL	Deduction Classification A=After-Tax B=Before-Tax L=QC Taxable Benefit N=Nontaxable Benefit P=Nontaxable Btax Benefit T=Taxable Benefit
7	DED_SLSTX_CLASS	Character(1)	VARCHAR2(1) NOT NULL	Sales Tax Type B=None G=Goods and Services Tax H=Harmonized Sales Tax I=Provincial Sales Tax Insurance P=Provincial Sales Tax Q=QC Prov Sales Tax Insurance S=QC Provincial Sales Tax T=Provincial Premium Tax U=QC Provincial Premium Tax
8	CALCULATED_BASE	Number(11,2)	DECIMAL(10,2) NOT NULL	Coverage Base
9	DED_CUR	Signed Number(12,2)	DECIMAL(10,2) NOT NULL	Current Deduction
10	PAY_END_DT	Date(10)	DATE	Pay Period End Date
11	PAYGROUP	Character(3)	VARCHAR2(3) NOT NULL	Pay Group