W3EB_HSAXCVG_VW

(SQL View)
 Index Back

HSA Excluded Coverages

This table defines "excluded coverages" for HSA Plans. That is, Plan Type/Benefit Plan combinations whose elections are mutually exclusive (not permitted) with an HSA election.

SELECT A.SCHED_ID ,A.EMPLID ,A.BENEFIT_RCD_NBR ,A.EVENT_ID , C.PLAN_TYPE , D.EFFDT , C.CNTRL_PLAN_TYPE , C.CNTRL_BENEFIT_PLAN FROM PS_BAS_PARTIC_OPTN A , PS_FSA_BENEF_TBL B , PS_HSA_EXC_CVG_PLN C , PS_BENEF_PLAN_TBL D WHERE A.PLAN_TYPE LIKE '6%' AND A.OPTION_TYPE = 'O' AND A.HISTORY_ONLY = 'N' AND B.PLAN_TYPE = A.PLAN_TYPE AND B.BENEFIT_PLAN = A.BENEFIT_PLAN AND B.BN_SPEND_ACCT_TYPE = 'HSA' AND B.EFFDT <= A.EVENT_DT AND B.EFFDT = ( SELECT MAX(BB.EFFDT) FROM PS_FSA_BENEF_TBL BB WHERE BB.PLAN_TYPE = B.PLAN_TYPE AND BB.BENEFIT_PLAN = B.BENEFIT_PLAN AND BB.EFFDT <= A.EVENT_DT) AND C.PLAN_TYPE = A.PLAN_TYPE AND C.EFFDT <= A.EVENT_DT AND C.EFFDT = ( SELECT MAX(CC.EFFDT) FROM PS_HSA_EXC_CVG_PLN CC WHERE CC.PLAN_TYPE = C.PLAN_TYPE AND CC.EFFDT <= A.EVENT_DT) AND D.PLAN_TYPE = A.PLAN_TYPE AND D.BENEFIT_PLAN = A.BENEFIT_PLAN AND D.EFFDT <= A.EVENT_DT AND D.EFFDT = ( SELECT MAX(DD.EFFDT) FROM PS_BENEF_PLAN_TBL DD WHERE DD.PLAN_TYPE = D.PLAN_TYPE AND DD.BENEFIT_PLAN = D.BENEFIT_PLAN AND DD.EFFDT <= A.EVENT_DT)

  • Parent record: HSA_EXC_CVG_TBL

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SCHED_ID Character(6) VARCHAR2(6) NOT NULL Schedule ID
    2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    3 BENEFIT_RCD_NBR Number(3,0) SMALLINT NOT NULL Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.
    4 EVENT_ID Number(6,0) INTEGER NOT NULL Event Identification
    5 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.

    Prompt Table: HSA_PLAN_VW

    6 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    7 CNTRL_PLAN_TYPE Character(2) VARCHAR2(2) NOT NULL This is used to identify the controlling Plan Type on setup tables, when PLAN_TYPE is already a record key.

    Prompt Table: BN_PLAN_TYPE_VW

    8 CNTRL_BENEFIT_PLAN Character(6) VARCHAR2(6) NOT NULL This is used to identify the controlling Benefit Plan on setup tables, when BENEFIT_PLAN is already a record key.

    Prompt Table: CNTRL_BENPLN_VW