BEN_BI_BLNC_VW

(SQL View)
 Index Back

Employee Balances

BEN_BI_BLNC_VW provides information about charges and balances on the Employee Balance Summary panel.

SELECT A.EMPLID ,A.BILL_PER_CD ,A.PLAN_TYPE ,A.BILL_CHARGE_ID ,MIN(A.EMPL_RCD) ,MIN(A.COBRA_EVENT_ID) ,MIN(A.BENEFIT_PROGRAM) ,MIN(A.BENEFIT_PLAN) ,MIN(A.COVRG_CD) ,MIN(A.DEDCD) ,MIN(A.BILLING_REASON) ,MIN(A.DATE_DUE) ,MIN(A.DATE_OVERDUE) ,MIN(A.BILL_PROCESS_DT) ,MIN(A.PRINT_PERIOD) ,MIN(A.BILL_SOURCE) ,SUM(D.CHARGE_AMT) ,SUM(D.CHRG_ADJUST) ,SUM(D.CHARGE_AMT + D.CHRG_ADJUST) ,SUM(D.PAY_AMT) ,SUM(D.PMT_ADJUST) ,SUM(D.PAY_AMT + D.PMT_ADJUST) ,SUM(D.CHARGE_AMT + D.CHRG_ADJUST - D.PAY_AMT - D.PMT_ADJUST) ,A.DED_SLSTX_CLASS FROM PS_BEN_BI_CHARGE A ,PS_BEN_BI_DETAIL D WHERE A.EMPLID = D.EMPLID AND A.BILL_CHARGE_ID = D.BILL_CHARGE_ID GROUP BY A.EMPLID ,A.BILL_PER_CD ,A.PLAN_TYPE ,A.BILL_CHARGE_ID ,A.DED_SLSTX_CLASS

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Default Value: PERSON.EMPLID

Prompt Table: PERSON

2 BILL_PER_CD Character(4) VARCHAR2(4) NOT NULL Billing Period

Prompt Table: BEN_BI_CALENDAR

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 BILL_CHARGE_ID Number(7,0) INTEGER NOT NULL Billing Charge ID

Prompt Table: BEN_BI_CHG_VW

5 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr

Default Value: PER_ORG_ASGN_VW.EMPL_RCD

6 COBRA_EVENT_ID Number(3,0) SMALLINT NOT NULL Uniquely identifies a COBRA event within an employee's benefits records, and is system-generated.
7 BENEFIT_PROGRAM Character(3) VARCHAR2(3) NOT NULL Defines a collection of benefit plans and their associated rate and calculation rules.

Prompt Table: BEN_DEFN_PGM

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

Prompt Table: BEN_PROG_BENPLN

9 COVRG_CD Character(2) VARCHAR2(2) NOT NULL Coverage Code

Prompt Table: BEN_PROG_BENCVC

10 DEDCD Character(6) VARCHAR2(6) NOT NULL Deduction Code

Prompt Table: BEN_PROG_BENDED

11 BILLING_REASON Character(1) VARCHAR2(1) NOT NULL Billing Reason
A=Arrears
B=Benefits Administration
C=COBRA
M=Manual Entry

Default Value: A

12 DATE_DUE Date(10) DATE NOT NULL Date Due
13 DATE_OVERDUE Date(10) DATE Date Overdue
14 BILL_PROCESS_DT Date(10) DATE Date Entered

Default Value: %date

15 PRINT_PERIOD Character(4) VARCHAR2(4) NOT NULL Print Period

Prompt Table: BEN_BI_CALENDAR

16 BILL_SOURCE Character(1) VARCHAR2(1) NOT NULL Billing Source
A=Automated
M=Manual

Default Value: M

17 CHARGE Signed Number(9,2) DECIMAL(7,2) NOT NULL Original Charge
18 CHRG_ADJUST Signed Number(9,2) DECIMAL(7,2) NOT NULL Charge Adjustments
19 TOTAL_CHARGE Signed Number(9,2) DECIMAL(7,2) NOT NULL Total Charge
20 AMOUNT_PAID Signed Number(9,2) DECIMAL(7,2) NOT NULL Total Payments For Charge
21 PMT_ADJUST Signed Number(9,2) DECIMAL(7,2) NOT NULL Payment Adjustments
22 TOTAL_PAYMENT Signed Number(9,2) DECIMAL(7,2) NOT NULL Total Payment
23 AMOUNT_DUE Signed Number(9,2) DECIMAL(7,2) NOT NULL Amount Due
24 DED_SLSTX_CLASS Character(1) VARCHAR2(1) NOT NULL Sales Tax Type
B=None
G=Goods and Services Tax
H=Harmonized Sales Tax
I=Provincial Sales Tax Insurance
P=Provincial Sales Tax
Q=QC Prov Sales Tax Insurance
S=QC Provincial Sales Tax
T=Provincial Premium Tax
U=QC Provincial Premium Tax