BENEFITS_VW(SQL View) |
Index Back |
|---|---|
Last Ben Deduction/Calced BaseBENEFITS_VW is a view of the employee's latest Benefits Deductions. Data is selected from PS_PAY_CHECK and PS_PAY_DEDUCTION for plan types greater than or equal to 10. Records with the most current date are selected. This view is used in the following panels: Benefits_Summary1, Benefits_Summary2, Benefits_Summary9, Benefits_SummaryI. |
| SELECT A.EMPLID ,A.EMPL_RCD ,B.PLAN_TYPE ,B.BENEFIT_PLAN ,B.DED_CLASS ,B.PAY_END_DT ,B.DED_CUR ,B.CALCULATED_BASE ,B.DED_SLSTX_CLASS 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.BENEFIT_RCD_NBR = B.BENEFIT_RCD_NBR 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 D.BENEFIT_RCD_NBR = C.BENEFIT_RCD_NBR AND D.PLAN_TYPE = B.PLAN_TYPE AND D.BENEFIT_PLAN = B.BENEFIT_PLAN) AND B.DED_CLASS = ( SELECT MIN(E.DED_CLASS) FROM PS_PAY_DEDUCTION E WHERE B.COMPANY = E.COMPANY AND B.PAYGROUP = E.PAYGROUP AND B.PAY_END_DT = E.PAY_END_DT AND B.OFF_CYCLE = E.OFF_CYCLE AND B.PAGE_NUM = E.PAGE_NUM AND B.LINE_NUM = E.LINE_NUM AND B.SEPCHK = E.SEPCHK AND B.BENEFIT_RCD_NBR = E.BENEFIT_RCD_NBR AND B.PLAN_TYPE = E.PLAN_TYPE AND B.BENEFIT_PLAN = E.BENEFIT_PLAN) |
| # | 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 Rcd Nbr
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 | 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 |
| 6 | PAY_END_DT | Date(10) | DATE | Pay Period End Date |
| 7 | DED_CUR | Signed Number(12,2) | DECIMAL(10,2) NOT NULL | Current Deduction |
| 8 | CALCULATED_BASE | Number(11,2) | DECIMAL(10,2) NOT NULL | Coverage Base |
| 9 | 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 |