FSA_CLAIMDTL_VW(SQL View) |
Index Back |
|---|---|
Vw - Submited FSA ClaimsFSA_CLAIMDTL_VW is a full view of FSA_CLAIM, 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. |
| SELECT EMPLID ,EMPL_RCD ,PLAN_TYPE ,BENEFIT_NBR ,BENEFIT_PLAN ,SVC_FROM_DT ,SVC_TO_DT ,SVC_TYPE ,FSA_CLAIM_SUB_AMT ,FSA_CLAIM_SUB_DT ,CLAIMID ,SVC_PROVIDER ,PATIENT_CD ,NAME ,FSA_CLAIM_DENY_AMT ,FSA_CLAIM_DENY_DT ,FSA_CLAIM_DENY_RSN ,FSA_CLAIM_APR_AMT ,FSA_CLAIM_PD_TO_DT ,FSA_CLAIM_COMMENT ,FSA_CLAIM_STATUS ,ACTION_DT ,SETID ,VENDOR_ID FROM PS_FSA_CLAIM |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: PERS_SRCH_GBL |
| 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 | 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 | FSA_CLAIM_SUB_DT | Date(10) | DATE NOT NULL | Claim Submitted Date |
| 11 | CLAIMID | Character(8) | VARCHAR2(8) NOT NULL | Claim ID |
| 12 | SVC_PROVIDER | Character(15) | VARCHAR2(15) NOT NULL | Service Provider |
| 13 | PATIENT_CD | Character(1) | VARCHAR2(1) NOT NULL |
Patient Code
D=Dependent E=Employee S=Spouse |
| 14 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 15 | FSA_CLAIM_DENY_AMT | Number(8,2) | DECIMAL(7,2) NOT NULL | Claim Denied Amount |
| 16 | FSA_CLAIM_DENY_DT | Date(10) | DATE | Claim Denied Date |
| 17 | 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 |
| 18 | FSA_CLAIM_APR_AMT | Number(8,2) | DECIMAL(7,2) NOT NULL | Claim Approved Amount |
| 19 | FSA_CLAIM_PD_TO_DT | Number(8,2) | DECIMAL(7,2) NOT NULL | FSA Claim Paid To Date |
| 20 | FSA_CLAIM_COMMENT | Long Character | CLOB | Claim Comments |
| 21 | 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 |
| 22 | ACTION_DT | Date(10) | DATE | Action Date |
| 23 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 24 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |