CBR_PROG_BENPLN

(SQL View)
 Index Back

Pln Type/Ben Plns in Ben Prog

BEN_PRPG_BENPLN is a view of Plan Types and Benefit Plans in Benefit Programs. It selects data from PS_Ben_Defn_Pgm, PS_Ben_Defn_Optn, and PS_Benef_Plan_Tbl with option type equal to 'O'ption and effective status = 'A'ctive. This view is used in many panels.

SELECT A.BENEFIT_PROGRAM ,A.EFFDT ,B.PLAN_TYPE ,B.BENEFIT_PLAN ,C.DESCR ,C.DESCRSHORT ,C.MIN_BENEFIC_ALLOC ,A.PROGRAM_TYPE ,B.OPTION_CD ,F.DEPENDENT_MARRIAGE ,F.DEP_AGE_LIMIT ,F.EXCL_DISABLED_AGE ,F.STUDENT_AGE_LIMIT FROM PS_BEN_DEFN_PGM A , PS_BEN_DEFN_OPTN B , PS_BENEF_PLAN_TBL C , PS_BEN_DEFN_PLAN E , PS_DEP_RULE_TBL F WHERE A.EFF_STATUS = 'A' AND B.BENEFIT_PROGRAM = A.BENEFIT_PROGRAM AND B.EFFDT = A.EFFDT AND B.OPTION_TYPE = 'O' AND B.OPTION_ID = ( SELECT MAX(B1.OPTION_ID) FROM PS_BEN_DEFN_OPTN B1 WHERE B1.BENEFIT_PROGRAM = B.BENEFIT_PROGRAM AND B1.OPTION_TYPE = 'O' AND B1.EFFDT = B.EFFDT AND B1.PLAN_TYPE = B.PLAN_TYPE AND B1.BENEFIT_PLAN = B.BENEFIT_PLAN) AND E.BENEFIT_PROGRAM = A.BENEFIT_PROGRAM AND E.EFFDT = A.EFFDT AND E.COBRA_PLAN = 'Y' AND C.PLAN_TYPE = B.PLAN_TYPE AND E.PLAN_TYPE = B.PLAN_TYPE AND C.BENEFIT_PLAN = B.BENEFIT_PLAN AND C.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_BENEF_PLAN_TBL D WHERE D.PLAN_TYPE = C.PLAN_TYPE AND D.BENEFIT_PLAN = C.BENEFIT_PLAN AND D.EFFDT <= A.EFFDT) AND E.DEP_RULE_ID <> ' ' AND E.DEP_RULE_ID = F.DEP_RULE_ID AND F.EFFDT = ( SELECT MAX(F1.EFFDT) FROM PS_DEP_RULE_TBL F1 WHERE F1.DEP_RULE_ID = F.DEP_RULE_ID AND F1.EFFDT <= E.EFFDT) UNION SELECT A.BENEFIT_PROGRAM ,A.EFFDT ,B.PLAN_TYPE ,B.BENEFIT_PLAN ,C.DESCR ,C.DESCRSHORT ,C.MIN_BENEFIC_ALLOC ,A.PROGRAM_TYPE ,B.OPTION_CD ,'N' ,99 ,'Y' ,99 FROM PS_BEN_DEFN_PGM A , PS_BEN_DEFN_OPTN B , PS_BENEF_PLAN_TBL C , PS_BEN_DEFN_PLAN E WHERE A.EFF_STATUS = 'A' AND B.BENEFIT_PROGRAM = A.BENEFIT_PROGRAM AND B.EFFDT = A.EFFDT AND B.OPTION_TYPE = 'O' AND B.OPTION_ID = ( SELECT MAX(B1.OPTION_ID) FROM PS_BEN_DEFN_OPTN B1 WHERE B1.BENEFIT_PROGRAM = B.BENEFIT_PROGRAM AND B1.OPTION_TYPE = 'O' AND B1.EFFDT = B.EFFDT AND B1.PLAN_TYPE = B.PLAN_TYPE AND B1.BENEFIT_PLAN = B.BENEFIT_PLAN) AND E.BENEFIT_PROGRAM = A.BENEFIT_PROGRAM AND E.EFFDT = A.EFFDT AND E.COBRA_PLAN = 'Y' AND C.PLAN_TYPE = B.PLAN_TYPE AND E.PLAN_TYPE = B.PLAN_TYPE AND C.BENEFIT_PLAN = B.BENEFIT_PLAN AND C.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_BENEF_PLAN_TBL D WHERE D.PLAN_TYPE = C.PLAN_TYPE AND D.BENEFIT_PLAN = C.BENEFIT_PLAN AND D.EFFDT <= A.EFFDT) AND E.DEP_RULE_ID = ' '

  • Related Language Record: BEN_PROGVW_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BENEFIT_PROGRAM Character(3) VARCHAR2(3) NOT NULL Defines a collection of benefit plans and their associated rate and calculation rules.
    2 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    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

    Prompt Table: BENEF_PLAN_TBL

    5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    6 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    7 MIN_BENEFIC_ALLOC Number(6,2) DECIMAL(5,2) NOT NULL This field is used to define the Minimum allocation a beneficiary ( usually the spouse ) must be assigned under a regulated benefit plan
    8 PROGRAM_TYPE Character(1) VARCHAR2(1) NOT NULL Program Type
    A=Automated
    M=Manual
    9 OPTION_CD Character(3) VARCHAR2(3) NOT NULL Option Code
    10 DEPENDENT_MARRIAGE Character(1) VARCHAR2(1) NOT NULL Dependent Ineligible if Married Flag
    11 DEP_AGE_LIMIT Number(2,0) SMALLINT NOT NULL Dependent Age Limit
    12 EXCL_DISABLED_AGE Character(1) VARCHAR2(1) NOT NULL Exclude Disabled from Age Limit
    13 STUDENT_AGE_LIMIT Number(2,0) SMALLINT NOT NULL Student Age Limit