FSA_CLAIM (SQL Table)	Index Back
FSA Submitted Claim FSA_CLAIM is a record that contains information about claims that have been submitted by an employee. Service type, service dates, service providers, and amounts approved or denied with reasons and status are some of the items stored here. Claim Entry Data and Claim Partic panels for both Health Care and Dependent Care FSAs populate this table. FSA001 updates this record; FSA003 and FSA005 access the record. Warning! For technical reasons, this record's schema shows that it is keyed by EMPL_RCD. This is not logically or functionally accurate. Throughout all of Base Benefits, when in the context of benefit enrollment, EMPL_RCD must be interpreted as an alias for BENEFIT_RCD_NBR. All benefit enrollment-related records are logically keyed by BENEFIT_RCD_NBR, so here EMPL_RCD actually contains the value of the appropriate BENEFIT_RCD_NBR. It is CRITICAL that all joins, comparisons, and updates to this record be against BENEFIT_RCD_NBR. Thus a typical driving join for benefit participation is PS_PER_ORG_ASGN.BENEFIT_RCD_NBR = PS_BEN_PROG_PARTIC.EMPL_RCD, and a typical join for benefit enrollment is PS_PER_ORG_ASGN.BENEFIT_RCD_NBR = PS_HEALTH_BENEFIT.EMPL_RCD. The security and search views used in Base Benefits perform this "mapping" internally.

FSA_CLAIM

(SQL Table)

FSA Submitted Claim

FSA_CLAIM is a record that contains information about claims that have been submitted by an employee. Service type, service dates, service providers, and amounts approved or denied with reasons and status are some of the items stored here. Claim Entry Data and Claim Partic panels for both Health Care and Dependent Care FSAs populate this table. FSA001 updates this record; FSA003 and FSA005 access the record. Warning! For technical reasons, this record's schema shows that it is keyed by EMPL_RCD. This is not logically or functionally accurate. Throughout all of Base Benefits, when in the context of benefit enrollment, EMPL_RCD must be interpreted as an alias for BENEFIT_RCD_NBR. All benefit enrollment-related records are logically keyed by BENEFIT_RCD_NBR, so here EMPL_RCD actually contains the value of the appropriate BENEFIT_RCD_NBR. It is CRITICAL that all joins, comparisons, and updates to this record be against BENEFIT_RCD_NBR. Thus a typical driving join for benefit participation is PS_PER_ORG_ASGN.BENEFIT_RCD_NBR = PS_BEN_PROG_PARTIC.EMPL_RCD, and a typical join for benefit enrollment is PS_PER_ORG_ASGN.BENEFIT_RCD_NBR = PS_HEALTH_BENEFIT.EMPL_RCD. The security and search views used in Base Benefits perform this "mapping" internally.

Parent record: PER_ORG_ASGN_VW

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
Prompt Table: FSA_PARTIC_SRCH

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.
Prompt Table: FSA2_BENEFIT_VW

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 SVC_FROM_DT Date(10) DATE NOT NULL Service From Date

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

8 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

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

10 CLAIMID Character(8) VARCHAR2(8) NOT NULL Claim ID
Default Value: NEW

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

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

13 SVC_PROVIDER Character(15) VARCHAR2(15) NOT NULL Service Provider

14 PATIENT_CD Character(1) VARCHAR2(1) NOT NULL Patient Code
D=Dependent
E=Employee
S=Spouse

15 NAME Character(50) VARCHAR2(50) NOT NULL Name

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

17 FSA_CLAIM_DENY_DT Date(10) DATE Claim Denied Date

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

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

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

21 FSA_CLAIM_COMMENT Long Character CLOB Claim Comments

22 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

23 ACTION_DT Date(10) DATE Action Date
Default Value: %date

24 SETID Character(5) VARCHAR2(5) NOT NULL SetID

25 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: FSA_PARTC2_SRCH
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record Prompt Table: FSA_PARTIC_SRCH
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. Prompt Table: FSA2_BENEFIT_VW
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	SVC_FROM_DT	Date(10)	DATE NOT NULL	Service From Date
7	SVC_TO_DT	Date(10)	DATE NOT NULL	Service To Date
8	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
9	FSA_CLAIM_SUB_AMT	Number(8,2)	DECIMAL(7,2) NOT NULL	Claim Submitted Amount
10	CLAIMID	Character(8)	VARCHAR2(8) NOT NULL	Claim ID Default Value: NEW
11	FSA_CLAIM_SUB_DT	Date(10)	DATE NOT NULL	Claim Submitted Date Default Value: %date
12	FSA_SPLIT_CLAIMS	Character(1)	VARCHAR2(1) NOT NULL	Split Claims Y/N Table Edit
13	SVC_PROVIDER	Character(15)	VARCHAR2(15) NOT NULL	Service Provider
14	PATIENT_CD	Character(1)	VARCHAR2(1) NOT NULL	Patient Code D=Dependent E=Employee S=Spouse
15	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
16	FSA_CLAIM_DENY_AMT	Number(8,2)	DECIMAL(7,2) NOT NULL	Claim Denied Amount
17	FSA_CLAIM_DENY_DT	Date(10)	DATE	Claim Denied Date
18	FSA_CLAIM_DENY_RSN	Character(1)	VARCHAR2(1) NOT NULL	Claim Denied Reason A=Already Paid N=Not a Covered Expense P=Claim Pended-Exceeds Avail Bal X=Submitted Amt Exceeds Balance
19	FSA_CLAIM_APR_AMT	Number(8,2)	DECIMAL(7,2) NOT NULL	Claim Approved Amount
20	FSA_CLAIM_PD_TO_DT	Number(8,2)	DECIMAL(7,2) NOT NULL	FSA Claim Paid To Date
21	FSA_CLAIM_COMMENT	Long Character	CLOB	Claim Comments
22	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
23	ACTION_DT	Date(10)	DATE	Action Date Default Value: %date
24	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
25	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier