FSA_CLAIM_VW (SQL View)	Index Back
EE Submitted FSA Claims View FSA_CLAIM_VW is a view of all claims made by employees YTD by plan type, benefit plan and claim ID, with specific processing information regarding each claim including present status, claim denied or claim processed.

FSA_CLAIM_VW

(SQL View)

EE Submitted FSA Claims View

FSA_CLAIM_VW is a view of all claims made by employees YTD by plan type, benefit plan and claim ID, with specific processing information regarding each claim including present status, claim denied or claim processed.

SELECT A.EmplID ,A.EMPL_RCD ,A.Plan_Type ,A.BENEFIT_NBR ,A.Benefit_Plan ,A.ClaimID ,A.Svc_Type ,A.Svc_From_Dt ,A.Svc_To_Dt ,A.FSA_Claim_Sub_Amt ,A.FSA_Claim_Sub_Dt ,A.FSA_Claim_Deny_Amt ,A.FSA_Claim_Deny_Dt ,A.FSA_Claim_Deny_Rsn ,A.FSA_Split_Claims ,A.FSA_Claim_Apr_Amt ,A.FSA_Claim_Pd_To_Dt ,A.FSA_Claim_Status FROM PS_FSA_CLAIM A

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: %RECNAME_EDIT

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

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_NBR Number(3,0) SMALLINT NOT NULL Note! Although this field is part of the index schema for most Base Benefits enrollment-related records, it was in fact never functionally implemented. As such, it is CRITICAL that this field always be set to zero 0.

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

6 CLAIMID Character(8) VARCHAR2(8) NOT NULL Claim ID

7 SVC_TYPE Character(1) VARCHAR2(1) NOT NULL Service Type
C=Dependent Day Care
D=Dental
H=Hearing
M=Medical
O=Orthodontia
P=Prescription Drugs
R=Retirement Counselling
V=Vision
Y=Carryforward

8 SVC_FROM_DT Date(10) DATE NOT NULL Service From Date

9 SVC_TO_DT Date(10) DATE NOT NULL Service To Date

10 FSA_CLAIM_SUB_AMT Number(8,2) DECIMAL(7,2) NOT NULL Claim Submitted Amount

11 FSA_CLAIM_SUB_DT Date(10) DATE NOT NULL Claim Submitted Date
Default Value: %date

12 FSA_CLAIM_DENY_AMT Number(8,2) DECIMAL(7,2) NOT NULL Claim Denied Amount

13 FSA_CLAIM_DENY_DT Date(10) DATE Claim Denied Date

14 FSA_CLAIM_DENY_RSN Character(1) VARCHAR2(1) NOT NULL Claim Denied Reason
A=Alredy Paid
N=Not a Covered Expense
P=Claim Pended-Exceeds Avail Bal
X=Submitted Amt Exceeds Balance

15 FSA_SPLIT_CLAIMS Character(1) VARCHAR2(1) NOT NULL Split Claims
Y/N Table Edit

16 FSA_CLAIM_APR_AMT Number(8,2) DECIMAL(7,2) NOT NULL Claim Approved Amount

17 FSA_CLAIM_PD_TO_DT Number(8,2) DECIMAL(7,2) NOT NULL FSA Claim Paid To Date

18 FSA_CLAIM_STATUS Character(1) VARCHAR2(1) NOT NULL FSA Claim Status
F=Fully Processed
J=Rejected
P=Pended
R=Ready To Pay
S=Submitted
X=Reversed Claim
Default Value: S

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: %RECNAME_EDIT
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr
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_NBR	Number(3,0)	SMALLINT NOT NULL	Note! Although this field is part of the index schema for most Base Benefits enrollment-related records, it was in fact never functionally implemented. As such, it is CRITICAL that this field always be set to zero 0.
5	BENEFIT_PLAN	Character(6)	VARCHAR2(6) NOT NULL	Benefit Plan
6	CLAIMID	Character(8)	VARCHAR2(8) NOT NULL	Claim ID
7	SVC_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Service Type C=Dependent Day Care D=Dental H=Hearing M=Medical O=Orthodontia P=Prescription Drugs R=Retirement Counselling V=Vision Y=Carryforward
8	SVC_FROM_DT	Date(10)	DATE NOT NULL	Service From Date
9	SVC_TO_DT	Date(10)	DATE NOT NULL	Service To Date
10	FSA_CLAIM_SUB_AMT	Number(8,2)	DECIMAL(7,2) NOT NULL	Claim Submitted Amount
11	FSA_CLAIM_SUB_DT	Date(10)	DATE NOT NULL	Claim Submitted Date Default Value: %date
12	FSA_CLAIM_DENY_AMT	Number(8,2)	DECIMAL(7,2) NOT NULL	Claim Denied Amount
13	FSA_CLAIM_DENY_DT	Date(10)	DATE	Claim Denied Date
14	FSA_CLAIM_DENY_RSN	Character(1)	VARCHAR2(1) NOT NULL	Claim Denied Reason A=Alredy Paid N=Not a Covered Expense P=Claim Pended-Exceeds Avail Bal X=Submitted Amt Exceeds Balance
15	FSA_SPLIT_CLAIMS	Character(1)	VARCHAR2(1) NOT NULL	Split Claims Y/N Table Edit
16	FSA_CLAIM_APR_AMT	Number(8,2)	DECIMAL(7,2) NOT NULL	Claim Approved Amount
17	FSA_CLAIM_PD_TO_DT	Number(8,2)	DECIMAL(7,2) NOT NULL	FSA Claim Paid To Date
18	FSA_CLAIM_STATUS	Character(1)	VARCHAR2(1) NOT NULL	FSA Claim Status F=Fully Processed J=Rejected P=Pended R=Ready To Pay S=Submitted X=Reversed Claim Default Value: S