HP_PAY_DED_VW

(SQL View)
 Index Back

Payroll Deduction

This view summarizes deduction amount per deduction code and deduction class per pay check.

SELECT A.COMPANY , A.PAYGROUP , A.PAY_END_DT , A.OFF_CYCLE , A.PAGE_NUM , A.LINE_NUM , A.SEPCHK , A.PLAN_TYPE , A.BENEFIT_PLAN , A.DEDCD , A.DED_CLASS , A.DED_SLSTX_CLASS , SUM(A.DED_CUR) , A.AP_STATUS , D.SUPPRESS_GL FROM PS_PAY_DEDUCTION A , PS_DEDUCTION_TBL B , PS_DEDUCTION_CLASS D WHERE B.PLAN_TYPE = A.PLAN_TYPE AND B.DEDCD = A.DEDCD AND B.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_DEDUCTION_TBL C WHERE C.PLAN_TYPE = B.PLAN_TYPE AND C.DEDCD = B.DEDCD AND C.EFFDT <= A.PAY_END_DT) AND D.PLAN_TYPE = B.PLAN_TYPE AND D.DEDCD = B.DEDCD AND D.EFFDT = B.EFFDT AND D.DED_CLASS = A.DED_CLASS AND D.DED_SLSTX_CLASS = A.DED_SLSTX_CLASS GROUP BY A.COMPANY, A.PAYGROUP, A.PAY_END_DT, A.OFF_CYCLE, A.PAGE_NUM, A.LINE_NUM, A.SEPCHK, A.PLAN_TYPE, A.BENEFIT_PLAN, A.DEDCD, A.DED_CLASS, A.DED_SLSTX_CLASS, A.AP_STATUS, D.SUPPRESS_GL

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

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

Prompt Table: PAYGROUP_TBL

3 PAY_END_DT Date(10) DATE NOT NULL Pay Period End Date

Prompt Table: PAY_CALENDAR

4 OFF_CYCLE Character(1) VARCHAR2(1) NOT NULL Off Cycle ?

Y/N Table Edit

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 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.
9 BENEFIT_PLAN Character(6) VARCHAR2(6) NOT NULL Benefit Plan

Prompt Table: BEN_PROG_BENPLN

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

Prompt Table: BEN_PROG_BENDED

11 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
12 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

Default Value: B

13 DED_CUR Signed Number(12,2) DECIMAL(10,2) NOT NULL Current Deduction
14 AP_STATUS Character(1) VARCHAR2(1) NOT NULL AP Status
S=Sent to AP
X=Extracted
15 SUPPRESS_GL Character(1) VARCHAR2(1) NOT NULL Suppress Posting to GL