BENEFITS_SAV_VW(SQL View) |
Index Back |
|---|---|
Last Savings DeductionsBENEFITS_SAV_VW is a view of the employee's latest Saving Deductions. The view selects data from PS_PAY_CHECK and PS_PAY_DEDUCTION for plan types between 40 and 50 where the pay end date is the maximum pay end date less than today. This view is used in the following panels: 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.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 >= '40' AND B.PLAN_TYPE < '50' 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 IN ('A','B') |
| # | 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 | 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 |