BN_PI_DEDN_VW (SQL View)	Index Back
Latest Benefit Deductions This is a view of the employee's latest Benefit Deductions from Payroll Interface. Data is selected from PS_DED_CALC for the most current date.

BN_PI_DEDN_VW

(SQL View)

Latest Benefit Deductions

This is a view of the employee's latest Benefit Deductions from Payroll Interface. Data is selected from PS_DED_CALC for the most current date.

SELECT A.EMPLID ,A.EMPL_RCD ,A.PLAN_TYPE ,A.BENEFIT_PLAN ,A.DEDCD ,A.DED_CLASS ,A.CALCULATED_BASE ,A.DED_CUR ,A.PAY_END_DT , A.PAYGROUP FROM PS_DED_CALC A WHERE A.PLAN_TYPE <> '00' AND A.PAY_END_DT = ( SELECT MAX(B.PAY_END_DT) FROM PS_DED_CALC B WHERE B.COMPANY = A.COMPANY AND B.PAYGROUP = A.PAYGROUP AND B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.PLAN_TYPE = A.PLAN_TYPE AND B.BENEFIT_PLAN = A.BENEFIT_PLAN AND B.DEDCD = A.DEDCD AND B.PAY_END_DT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
Prompt Table: PER_ORG_ASGN_VW

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 CALCULATED_BASE Number(11,2) DECIMAL(10,2) NOT NULL Coverage Base

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

9 PAY_END_DT Date(10) DATE Pay Period End Date

10 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	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record Prompt Table: PER_ORG_ASGN_VW
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	CALCULATED_BASE	Number(11,2)	DECIMAL(10,2) NOT NULL	Coverage Base
8	DED_CUR	Signed Number(12,2)	DECIMAL(10,2) NOT NULL	Current Deduction
9	PAY_END_DT	Date(10)	DATE	Pay Period End Date
10	PAYGROUP	Character(3)	VARCHAR2(3) NOT NULL	Pay Group