BEN_BI_PRVW_VW

(SQL View)
 Index Back

Payment Review

BEN_BI_PRVW_VW ties the payments to the charges paid by the payment. It is used on the Payment Review panel to show only those charges paid by the payment.

SELECT A.EMPLID ,A.BILL_PER_CD ,A.PLAN_TYPE ,A.BILL_CHARGE_ID ,P.BILL_PAY_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.PAY_AMT + 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, PS_BEN_BI_PAYMENT P WHERE A.EMPLID = D.EMPLID AND A.BILL_CHARGE_ID = D.BILL_CHARGE_ID AND P.EMPLID = D.EMPLID AND P.BILL_PAY_ID = D.BILL_PAY_ID GROUP BY A.EMPLID ,A.BILL_PER_CD ,A.PLAN_TYPE ,A.BILL_CHARGE_ID ,P.BILL_PAY_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 BILL_PAY_ID Number(7,0) INTEGER NOT NULL Payment ID
6 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

Default Value: PER_ORG_ASGN_VW.EMPL_RCD

7 COBRA_EVENT_ID Number(3,0) SMALLINT NOT NULL Uniquely identifies a COBRA event within an employee's benefits records, and is system-generated.
8 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

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

Prompt Table: BEN_PROG_BENPLN

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

Prompt Table: BEN_PROG_BENCVC

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

Prompt Table: BEN_PROG_BENDED

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

Default Value: A

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

Default Value: %date

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

Prompt Table: BEN_BI_CALENDAR

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

Default Value: M

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