BEN_SAVING_SRCH

(SQL View)
 Index Back

Rec for component srch

SAVINGS_PLAN is a record which defines the savings-related benefit plan for which an employee is enrolled. These are plans in the '4x' series, such as 401(k), 403(b), and ESPP plans. Details of an employee's investment allocations are stored in a child record, SAVINGS_INVEST. Warning! For technical reasons, this record's schema shows that it is keyed by EMPL_RCD. This is not logically or functionally accurate. Throughout all of Base Benefits, when in the context of benefit enrollment, EMPL_RCD must be interpreted as an alias for BENEFIT_RCD_NBR. All benefit enrollment-related records are logically keyed by BENEFIT_RCD_NBR, so here EMPL_RCD actually contains the value of the appropriate BENEFIT_RCD_NBR. It is CRITICAL that all joins, comparisons, and updates to this record be against BENEFIT_RCD_NBR. Thus a typical driving join for benefit participation is PS_PER_ORG_ASGN.BENEFIT_RCD_NBR = PS_BEN_PROG_PARTIC.EMPL_RCD, and a typical join for benefit enrollment is PS_PER_ORG_ASGN.BENEFIT_RCD_NBR = PS_HEALTH_BENEFIT.EMPL_RCD. The security and search views used in Base Benefits perform this "mapping" internally.

SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.PLAN_TYPE , A.BENEFIT_NBR , A.EFFDT , A.DEDUCTION_END_DT , A.COVERAGE_BEGIN_DT , A.COVERAGE_END_DT , A.COVERAGE_ELECT , A.COVERAGE_ELECT_DT , A.BENEFIT_PLAN , A.SAVINGS_COVRG , A.FLAT_DED_AMT , A.PCT_GROSS , A.SAVINGS_COVRG_ATAX , A.FLAT_DED_AMT_ATAX , A.PCT_GROSS_ATAX , A.ANN_EX_CREDITS , A.PLAN_TYPE_RB , A.BENEFIT_PLAN_RB , A.DEDCD_RB , A.DED_CLASS_RB , A.PLAN_TYPE_RA , A.BENEFIT_PLAN_RA , A.DEDCD_RA , A.DED_CLASS_RA , T.XLATLONGNAME ,B.DESCR ,B.EFFDT FROM PSXLATITEM T ,(PS_SAVINGS_PLAN A LEFT OUTER JOIN PS_BEN_H_PLAN_SRCH B ON A.PLAN_TYPE=B.PLAN_TYPE AND A.BENEFIT_PLAN =B.BENEFIT_PLAN) WHERE A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_SAVINGS_PLAN A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A.PLAN_TYPE = A_ED.PLAN_TYPE AND A.BENEFIT_NBR = A_ED.BENEFIT_NBR AND A_ED.EFFDT <= %CurrentDateIn) AND T.FIELDNAME='PLAN_TYPE' AND T.FIELDVALUE=A.PLAN_TYPE

  • Parent record: BENEFIT_PARTIC

    • # 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 Record

    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.

    Default Value: 40

    4 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.
    5 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: SAVINGS_PLAN.COVERAGE_BEGIN_DT

    6 DEDUCTION_END_DT Date(10) DATE Deduction End Date
    7 COVERAGE_BEGIN_DT Date(10) DATE NOT NULL 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

    Default Value: E

    10 COVERAGE_ELECT_DT Date(10) DATE NOT NULL Coverage Elect Date

    Default Value: %date

    11 BENEFIT_PLAN Character(6) VARCHAR2(6) NOT NULL Benefit Plan

    Prompt Table: BEN_PROG_BENPLN

    12 SAVINGS_COVRG Character(1) VARCHAR2(1) NOT NULL Savings Coverage
    1=Flat Amount
    2=Percent of Gross

    Default Value: 1

    13 FLAT_DED_AMT Number(9,2) DECIMAL(8,2) NOT NULL Flat Deduction Amount
    14 PCT_GROSS Number(7,3) DECIMAL(6,3) NOT NULL Percent of Gross
    15 SAVINGS_COVRG_ATAX Character(1) VARCHAR2(1) NOT NULL Savings Coverage After-Tax
    1=Flat Amount
    2=Percent of Gross

    Default Value: 1

    16 FLAT_DED_AMT_ATAX Number(9,2) DECIMAL(8,2) NOT NULL Flat Deduction Amt After-Tax
    17 PCT_GROSS_ATAX Number(7,3) DECIMAL(6,3) NOT NULL Percent of Gross After-Tax
    18 ANN_EX_CREDITS Number(9,2) DECIMAL(8,2) NOT NULL Annual Excess Credits
    19 PLAN_TYPE_RB Character(2) VARCHAR2(2) NOT NULL Plan Type BTAX Rollover

    Prompt Table: PLAN_TYPE_RB_VW

    20 BENEFIT_PLAN_RB Character(6) VARCHAR2(6) NOT NULL Before Tax Rollover

    Prompt Table: BEN_PLN_RB_VW

    21 DEDCD_RB Character(6) VARCHAR2(6) NOT NULL Deduction Code BTAX Rollover

    Prompt Table: DEDCD_RB_VW

    22 DED_CLASS_RB Character(1) VARCHAR2(1) NOT NULL Deduction Class BTAX Rollover

    Prompt Table: DEDCLASS_RB_VW

    23 PLAN_TYPE_RA Character(2) VARCHAR2(2) NOT NULL Plan Type ATAX Rollover

    Prompt Table: PLAN_TYPE_RA_VW

    24 BENEFIT_PLAN_RA Character(6) VARCHAR2(6) NOT NULL After Tax Rollover

    Prompt Table: BEN_PLN_RA_VW

    25 DEDCD_RA Character(6) VARCHAR2(6) NOT NULL Deduction Code ATAX Rollover

    Prompt Table: DEDCD_RA_VW

    26 DED_CLASS_RA Character(1) VARCHAR2(1) NOT NULL Deduction Class ATAX Rollover

    Prompt Table: DEDCLASS_RA_VW

    27 XLATLONGNAME Character(30) VARCHAR2(30) NOT NULL Translate Long Name
    28 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    29 EFFDT_PLAN Date(10) DATE Plan Effective Date