FSA_CLAIM_DET (SQL Table)	Index Back
FSA Claim Detail Information FSA CLAIM DETAIL is a child record of FSA CLAIM. It is currently used only in the Canadian FSA Claims Administration product, and is used to detail each line-item within a submitted FSA claim. 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_DET

(SQL Table)

FSA Claim Detail Information

FSA CLAIM DETAIL is a child record of FSA CLAIM. It is currently used only in the Canadian FSA Claims Administration product, and is used to detail each line-item within a submitted FSA claim. 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: FSA_CLAIM

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

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

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

4 DETAIL_SEQ Number(3,0) SMALLINT NOT NULL FSA Claim Detail Sequence Nbr

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

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

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 FSA_CLAIM_SUB_DT Date(10) DATE NOT NULL Claim Submitted Date
Default Value: %date

9 SVC_PROVIDER_DET Character(40) VARCHAR2(40) NOT NULL Detail Level Service Provider

10 SETID Character(5) VARCHAR2(5) NOT NULL SetID
Prompt Table: SETID_TBL

11 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
Prompt Table: VENDOR

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

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

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

15 FSA_CLAIM_BILL_AMT Number(8,2) DECIMAL(7,2) NOT NULL FSA Total Billed Amount

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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERSON
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record Prompt Table: PER_ORG_ASGN_VW
3	CLAIMID	Character(8)	VARCHAR2(8) NOT NULL	Claim ID
4	DETAIL_SEQ	Number(3,0)	SMALLINT NOT NULL	FSA Claim Detail Sequence Nbr
5	SVC_FROM_DT	Date(10)	DATE NOT NULL	Service From Date
6	SVC_TO_DT	Date(10)	DATE NOT NULL	Service To Date
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	FSA_CLAIM_SUB_DT	Date(10)	DATE NOT NULL	Claim Submitted Date Default Value: %date
9	SVC_PROVIDER_DET	Character(40)	VARCHAR2(40) NOT NULL	Detail Level Service Provider
10	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID Prompt Table: SETID_TBL
11	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier Prompt Table: VENDOR
12	PATIENT_CD	Character(1)	VARCHAR2(1) NOT NULL	Patient Code D=Dependent E=Employee S=Spouse
13	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
14	FSA_CLAIM_SUB_AMT	Number(8,2)	DECIMAL(7,2) NOT NULL	Claim Submitted Amount
15	FSA_CLAIM_BILL_AMT	Number(8,2)	DECIMAL(7,2) NOT NULL	FSA Total Billed Amount
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