BEN_BI_CHG_VW

(SQL View)
 Index Back

Charges with accounting info

BEN_BI_CHG_VW ties the charge accounting information on BEN_BI_DETAIL to the other charge information on BEN_BI_CHARGE. This view eliminates the need for accounting fields on BEN_BI_CHARGE that must be updated and kept in balance with the detail. This record is the best source of total information about charges and in most cases should be used in place of BEN_BI_CHARGE.

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) ,MIN(A.PROCESS_PERIOD) ,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.CHARGE_AMT + D.CHRG_ADJUST - D.PAY_AMT - D.PMT_ADJUST) ,A.DED_SLSTX_CLASS ,A.COUNTRY ,A.PROVINCE 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 ,A.COUNTRY ,A.PROVINCE

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

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 PROCESS_PERIOD Character(4) VARCHAR2(4) NOT NULL Processing Period
18 CHARGE Signed Number(9,2) DECIMAL(7,2) NOT NULL Original Charge
19 CHRG_ADJUST Signed Number(9,2) DECIMAL(7,2) NOT NULL Charge Adjustments
20 TOTAL_CHARGE Signed Number(9,2) DECIMAL(7,2) NOT NULL Total Charge
21 AMOUNT_PAID Signed Number(9,2) DECIMAL(7,2) NOT NULL Total Payments For Charge
22 PMT_ADJUST Signed Number(9,2) DECIMAL(7,2) NOT NULL Payment Adjustments
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
25 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
26 PROVINCE Character(6) VARCHAR2(6) NOT NULL Province