SVP_ATAX_VW(SQL View) |
Index Back |
|---|---|
Savings Plan ViewThis view is used by the Payroll Interface Product to enable the selection and export of after-tax savings plan deduction amounts or percentages. It is the equivalent of SAVINGS_PLAN_VW (Used for before-tax deduction). |
| SELECT B.EMPLID ,B.EMPL_RCD ,B.EFFDT ,B.PLAN_TYPE ,B.BENEFIT_NBR ,B.DEDUCTION_END_DT ,B.COVERAGE_BEGIN_DT ,B.COVERAGE_END_DT ,B.COVERAGE_ELECT ,B.COVERAGE_ELECT_DT ,F.EFFDT ,E.BENEFIT_PROGRAM ,E.EFFDT ,E.BENEFIT_PLAN ,E.DEDCD ,B.SAVINGS_COVRG ,B.FLAT_DED_AMT ,B.PCT_GROSS ,B.SAVINGS_COVRG_ATAX ,B.FLAT_DED_AMT_ATAX ,B.PCT_GROSS_ATAX ,B.ANN_EX_CREDITS ,B.PLAN_TYPE_RB ,B.BENEFIT_PLAN_RB ,B.DEDCD_RB ,B.DED_CLASS_RB ,B.PLAN_TYPE_RA ,B.BENEFIT_PLAN_RA ,B.DEDCD_RA ,B.DED_CLASS_RA FROM PS_SAVINGS_PLAN B ,PS_BEN_PROG_PARTIC F ,PS_BEN_DEFN_OPTN E WHERE B.EMPLID=F.EMPLID AND B.EMPL_RCD=F.EMPL_RCD AND B.PLAN_TYPE = E.PLAN_TYPE AND B.BENEFIT_PLAN = E.BENEFIT_PLAN AND F.EFFDT = ( SELECT MAX(F1.EFFDT) FROM PS_BEN_PROG_PARTIC F1 WHERE F.EMPLID = F1.EMPLID AND F.EMPL_RCD = F1.EMPL_RCD AND F.COBRA_EVENT_ID = F1.COBRA_EVENT_ID AND F1.EFFDT <= B.EFFDT) AND E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_BEN_DEFN_OPTN E1 WHERE E.BENEFIT_PROGRAM = E1.BENEFIT_PROGRAM AND E.PLAN_TYPE = E1.PLAN_TYPE AND E.OPTION_ID = E1.OPTION_ID AND E1.EFFDT <= B.EFFDT) AND E.BENEFIT_PROGRAM = F.BENEFIT_PROGRAM AND (B.FLAT_DED_AMT_ATAX > 0 OR B.PCT_GROSS_ATAX > 0) |
| # | 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 Record |
| 3 | EFFDT | Date(10) | DATE |
Effective Date
Default Value: %date |
| 4 | 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. |
| 5 | BENEFIT_NBR | Number(3,0) | SMALLINT NOT NULL | Note! Although this field is part of the index schema for most Base Benefits enrollment-related records, it was in fact never functionally implemented. As such, it is CRITICAL that this field always be set to zero 0. |
| 6 | DEDUCTION_END_DT | Date(10) | DATE | Deduction End Date |
| 7 | COVERAGE_BEGIN_DT | Date(10) | DATE | Coverage Begin Date |
| 8 | COVERAGE_END_DT | Date(10) | DATE | Coverage End Date |
| 9 | COVERAGE_ELECT | Character(1) | VARCHAR2(1) NOT NULL |
Coverage Election
E=Elect T=Terminate W=Waive |
| 10 | COVERAGE_ELECT_DT | Date(10) | DATE | Coverage Elect Date |
| 11 | PARTIC_EFFDT | Date(10) | DATE | Participant Effective Date |
| 12 | BENEFIT_PROGRAM | Character(3) | VARCHAR2(3) NOT NULL | Defines a collection of benefit plans and their associated rate and calculation rules. |
| 13 | BEN_EFFDT | Date(10) | DATE | Benefit Plan Effective Date |
| 14 | BENEFIT_PLAN | Character(6) | VARCHAR2(6) NOT NULL | Benefit Plan |
| 15 | DEDCD | Character(6) | VARCHAR2(6) NOT NULL | Deduction Code |
| 16 | SAVINGS_COVRG | Character(1) | VARCHAR2(1) NOT NULL |
Savings Coverage
1=Flat Amount 2=Percent of Gross |
| 17 | FLAT_DED_AMT | Number(9,2) | DECIMAL(8,2) NOT NULL | Flat Deduction Amount |
| 18 | PCT_GROSS | Number(7,3) | DECIMAL(6,3) NOT NULL | Percent of Gross |
| 19 | SAVINGS_COVRG_ATAX | Character(1) | VARCHAR2(1) NOT NULL |
Savings Coverage After-Tax
1=Flat Amount 2=Percent of Gross |
| 20 | FLAT_DED_AMT_ATAX | Number(9,2) | DECIMAL(8,2) NOT NULL | Flat Deduction Amt After-Tax |
| 21 | PCT_GROSS_ATAX | Number(7,3) | DECIMAL(6,3) NOT NULL | Percent of Gross After-Tax |
| 22 | ANN_EX_CREDITS | Number(9,2) | DECIMAL(8,2) NOT NULL | Annual Excess Credits |
| 23 | PLAN_TYPE_RB | Character(2) | VARCHAR2(2) NOT NULL | Plan Type BTAX Rollover |
| 24 | BENEFIT_PLAN_RB | Character(6) | VARCHAR2(6) NOT NULL | Before Tax Rollover |
| 25 | DEDCD_RB | Character(6) | VARCHAR2(6) NOT NULL | Deduction Code BTAX Rollover |
| 26 | DED_CLASS_RB | Character(1) | VARCHAR2(1) NOT NULL | Deduction Class BTAX Rollover |
| 27 | PLAN_TYPE_RA | Character(2) | VARCHAR2(2) NOT NULL | Plan Type ATAX Rollover |
| 28 | BENEFIT_PLAN_RA | Character(6) | VARCHAR2(6) NOT NULL | After Tax Rollover |
| 29 | DEDCD_RA | Character(6) | VARCHAR2(6) NOT NULL | Deduction Code ATAX Rollover |
| 30 | DED_CLASS_RA | Character(1) | VARCHAR2(1) NOT NULL | Deduction Class ATAX Rollover |