BEN_PROG_DED_VW (SQL View)	Index Back
Cur General Deducts in BenProg This view shows all general deductions active in all the benefit programs an employee can participate in.

BEN_PROG_DED_VW

(SQL View)

Cur General Deducts in BenProg

This view shows all general deductions active in all the benefit programs an employee can participate in.

SELECT P.EMPLID ,P.EMPL_RCD , A.Benefit_Program ,B.Dedcd ,C.Descr ,C.Descrshort FROM PS_BEN_DEFN_PGM A, PS_Ben_Defn_Optn B, PS_Deduction_Tbl C, PS_BEN_PROG_PARTIC P WHERE P.COBRA_EVENT_ID = 0 AND A.Benefit_Program = B.Benefit_Program AND A.Benefit_Program = P.Benefit_Program AND B.Benefit_Program = P.Benefit_Program AND A.Effdt = B.Effdt AND A.Eff_Status = 'A' AND B.Option_Type = 'D' AND C.Plan_Type = B.Plan_Type AND C.Dedcd = B.Dedcd AND C.EffDt = (SELECT MAX(D.EffDt) FROM PS_Deduction_Tbl D WHERE D.Plan_Type = C.Plan_Type AND D.Dedcd = C.Dedcd AND D.EffDt <= A.EffDt)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr

3 BENEFIT_PROGRAM Character(3) VARCHAR2(3) NOT NULL Defines a collection of benefit plans and their associated rate and calculation rules.

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

5 DESCR Character(30) VARCHAR2(30) NOT NULL Description

6 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr
3	BENEFIT_PROGRAM	Character(3)	VARCHAR2(3) NOT NULL	Defines a collection of benefit plans and their associated rate and calculation rules.
4	DEDCD	Character(6)	VARCHAR2(6) NOT NULL	Deduction Code
5	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
6	DESCRSHORT	Character(10)	VARCHAR2(10) NOT NULL	Short Description