BNE_PLAN_SUM_VW

(SQL View)
 Index Back

BAS Partic Inquiry- Plan

BAS_PAR_PLAN_VW is a view of plan types. It is used with the Benefits Administration Inquiry.

SELECT DISTINCT A.SCHED_ID ,A.EMPLID ,A.BENEFIT_RCD_NBR ,A.EVENT_ID ,A.DISPLAY_PLN_SEQ ,A.PLAN_TYPE ,A.PLAN_ENROLL_DT ,A.COVERAGE_BEGIN_DT ,A.DEDUCTION_BEGIN_DT ,A.COVERAGE_ELECT ,A.COVERAGE_ELECT_DT ,A.ELECT_ALLOWED ,A.CUR_ELECT_IND ,A.APAY_EFFDT ,A.ENROLL_ACTION ,A.BAS_ERROR ,A.DEFAULT_ASSIGNED ,A.ELECTION_MADE ,A.PROOF_RCVD , CASE WHEN A.OPTION_CD = ' ' THEN B.OPTION_CD ELSE A.OPTION_CD END ,A.FLAT_AMOUNT ,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 ,A.EMPL_CONTRBUTN_AMT ,A.ANN_EX_CREDIT_FSA ,A.ANNUAL_PLEDGE ,A.PENSION_BASE ,A.VOLUNTARY_AMT ,A.VOLUNTARY_PCT ,A.VACN_HOURS ,A.VACN_BUY_FLAT_AMT ,A.VACN_SELL_FLAT_AMT ,A.GOAL_AMT ,A.IGNORE_PLAN ,A.BILL_ACTION ,A.PERCENT_CALC ,A.RATE_QUALIFIER ,A.RATE_PERCENT ,A.FLAT_CALC ,A.RATE_AMOUNT ,A.BILL_EFFDT ,A.HLTH_PROVIDER_ID ,A.PREVIOUSLY_SEEN ,A.EVENT_DT ,C.SHOW_PLAN_TYPE ,C.COLLECT_DEPBEN ,C.COLLECT_FUNDS ,C.EVENT_RULES_ID FROM PS_BAS_PARTIC_PLAN A LEFT OUTER JOIN PS_BAS_PARTIC_OPTN B ON A.SCHED_ID = B.SCHED_ID AND A.EMPLID = B.EMPLID AND A.BENEFIT_RCD_NBR = B.BENEFIT_RCD_NBR AND A.EVENT_ID = B.EVENT_ID AND A.PLAN_TYPE = B.PLAN_TYPE AND B.DEFAULT_IND = 'Y' , PS_BEN_DEFN_PLAN C WHERE A.BENEFIT_PROGRAM = C.BENEFIT_PROGRAM AND A.PLAN_TYPE = C.PLAN_TYPE AND C.EFFDT = ( SELECT MAX(CC.EFFDT) FROM PS_BEN_DEFN_PLAN CC WHERE CC.BENEFIT_PROGRAM = C.BENEFIT_PROGRAM AND CC.PLAN_TYPE = C.PLAN_TYPE AND CC.EFFDT <= A.EVENT_DT)

# 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 DISPLAY_PLN_SEQ Character(2) VARCHAR2(2) NOT NULL Display Plan-type Sequence
6 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.
7 PLAN_ENROLL_DT Date(10) DATE Plan Enroll Date
8 COVERAGE_BEGIN_DT Date(10) DATE Coverage Begin Date
9 DEDUCTION_BEGIN_DT Date(10) DATE Deduction Begin Date
10 COVERAGE_ELECT Character(1) VARCHAR2(1) NOT NULL Coverage Election
E=Elect
T=Terminate
W=Waive
11 COVERAGE_ELECT_DT Date(10) DATE Coverage Elect Date
12 ELECT_ALLOWED Character(1) VARCHAR2(1) NOT NULL Elections Allowed

Y/N Table Edit

13 CUR_ELECT_IND Character(1) VARCHAR2(1) NOT NULL Current Election Indicator
F=Future
N=No
O=Only
Y=Yes
14 APAY_EFFDT Date(10) DATE Additional Pay Date
15 ENROLL_ACTION Character(1) VARCHAR2(1) NOT NULL Enroll Action
E=Enroll
N=None
T=Terminate
16 BAS_ERROR Character(1) VARCHAR2(1) NOT NULL BAS Error Type

Y/N Table Edit

17 DEFAULT_ASSIGNED Character(1) VARCHAR2(1) NOT NULL Default Assigned

Y/N Table Edit

18 ELECTION_MADE Character(1) VARCHAR2(1) NOT NULL Employee Made an Election

Y/N Table Edit

19 PROOF_RCVD Character(1) VARCHAR2(1) NOT NULL Proof Received

Y/N Table Edit

20 OPTION_CD Character(3) VARCHAR2(3) NOT NULL Option Code

Prompt Table: BAS_PAR_OPTN_V2

21 FLAT_AMOUNT Signed Number(9,0) DECIMAL(8) NOT NULL Flat Amount
22 SAVINGS_COVRG Character(1) VARCHAR2(1) NOT NULL Savings Coverage
1=Flat Amount
2=Percent of Gross
23 FLAT_DED_AMT Number(9,2) DECIMAL(8,2) NOT NULL Flat Deduction Amount
24 PCT_GROSS Number(7,3) DECIMAL(6,3) NOT NULL Percent of Gross
25 SAVINGS_COVRG_ATAX Character(1) VARCHAR2(1) NOT NULL Savings Coverage After-Tax
1=Flat Amount
2=Percent of Gross
26 FLAT_DED_AMT_ATAX Number(9,2) DECIMAL(8,2) NOT NULL Flat Deduction Amt After-Tax
27 PCT_GROSS_ATAX Number(7,3) DECIMAL(6,3) NOT NULL Percent of Gross After-Tax
28 ANN_EX_CREDITS Number(9,2) DECIMAL(8,2) NOT NULL Annual Excess Credits
29 PLAN_TYPE_RB Character(2) VARCHAR2(2) NOT NULL Plan Type BTAX Rollover
30 BENEFIT_PLAN_RB Character(6) VARCHAR2(6) NOT NULL Before Tax Rollover
31 DEDCD_RB Character(6) VARCHAR2(6) NOT NULL Deduction Code BTAX Rollover
32 DED_CLASS_RB Character(1) VARCHAR2(1) NOT NULL Deduction Class BTAX Rollover
33 PLAN_TYPE_RA Character(2) VARCHAR2(2) NOT NULL Plan Type ATAX Rollover
34 BENEFIT_PLAN_RA Character(6) VARCHAR2(6) NOT NULL After Tax Rollover
35 DEDCD_RA Character(6) VARCHAR2(6) NOT NULL Deduction Code ATAX Rollover
36 DED_CLASS_RA Character(1) VARCHAR2(1) NOT NULL Deduction Class ATAX Rollover
37 EMPL_CONTRBUTN_AMT Number(7,2) DECIMAL(6,2) NOT NULL Employee Contribution Amount
38 ANN_EX_CREDIT_FSA Number(8,2) DECIMAL(7,2) NOT NULL Annual Credit Rollover FSA
39 ANNUAL_PLEDGE Signed Number(10,2) DECIMAL(8,2) NOT NULL Annual Pledge
40 PENSION_BASE Number(11,2) DECIMAL(10,2) NOT NULL Salary for Pension Calculation
41 VOLUNTARY_AMT Number(11,2) DECIMAL(10,2) NOT NULL Voluntary Contribution Amount
42 VOLUNTARY_PCT Number(7,3) DECIMAL(6,3) NOT NULL Voluntary Contrib Pct
43 VACN_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Vacation Buy/Sell Hours
44 VACN_BUY_FLAT_AMT Number(8,2) DECIMAL(7,2) NOT NULL Vacation Buy Flat Amount
45 VACN_SELL_FLAT_AMT Number(8,2) DECIMAL(7,2) NOT NULL Vacation Sell Flat Amount
46 GOAL_AMT Signed Number(12,2) DECIMAL(10,2) NOT NULL Goal Amount
47 IGNORE_PLAN Character(1) VARCHAR2(1) NOT NULL Ignore Plan
C=Excess Credit Only
N=No
Y=Yes

Y/N Table Edit

48 BILL_ACTION Character(1) VARCHAR2(1) NOT NULL Effect On Billing
E=End Billing
N=None
S=Start Billing
49 PERCENT_CALC Character(1) VARCHAR2(1) NOT NULL Percent Calculation

Y/N Table Edit

50 RATE_QUALIFIER Character(1) VARCHAR2(1) NOT NULL Rate Qualifier
E=Employee Rate
T=Total Rate
51 RATE_PERCENT Number(3,0) SMALLINT NOT NULL Rate Percent
52 FLAT_CALC Character(1) VARCHAR2(1) NOT NULL Flat Amount

Y/N Table Edit

53 RATE_AMOUNT Signed Number(9,2) DECIMAL(7,2) NOT NULL Rate Amount
54 BILL_EFFDT Date(10) DATE Billing Begin Date
55 HLTH_PROVIDER_ID Character(30) VARCHAR2(30) NOT NULL Health Provider ID
56 PREVIOUSLY_SEEN Character(1) VARCHAR2(1) NOT NULL Previously Seen

Y/N Table Edit

57 EVENT_DT Date(10) DATE Event Date
58 SHOW_PLAN_TYPE Character(1) VARCHAR2(1) NOT NULL This flag tells the system whether to show a plan type on the enrollment form if the employee has no choice in the election.
59 COLLECT_DEPBEN Character(1) VARCHAR2(1) NOT NULL This field tells the system whether to collect dependents or beneficiaries with the employee's enrollment.

Y/N Table Edit

Default Value: Y

60 COLLECT_FUNDS Character(1) VARCHAR2(1) NOT NULL This field tells the system whether to collect fund allocations with the employee's enrollment.

Y/N Table Edit

Default Value: Y

61 EVENT_RULES_ID Character(4) VARCHAR2(4) NOT NULL Event Rules ID