FSA_CLAIM_SRCH (SQL View)	Index Back
Search Vw- FSA Claims/Security FSA_CLAIM_SRCH is a view of all claim payment data based on the CLAIM ID from the FSA_CLAIM table. This view is accessed by FSA Claims Administration. Joins PERS_SRCH_GBL with FSA_CLAIM

FSA_CLAIM_SRCH

(SQL View)

Search Vw- FSA Claims/Security

FSA_CLAIM_SRCH is a view of all claim payment data based on the CLAIM ID from the FSA_CLAIM table. This view is accessed by FSA Claims Administration. Joins PERS_SRCH_GBL with FSA_CLAIM

SELECT %subrec(EMPLMT_BEN_SBR, SEC) ,FC.PLAN_TYPE ,FC.BENEFIT_PLAN ,FC.CLAIMID ,FC.FSA_CLAIM_STATUS ,FC.SVC_FROM_DT FROM PS_EMPL_BEN_SRCH SEC , PS_FSA_CLAIM FC WHERE FC.EMPLID=SEC.EMPLID AND FC.EMPL_RCD=SEC.EMPL_RCD

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

2 ROWSECCLASS Character(30) VARCHAR2(30) NOT NULL The class used to determine row level security

3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Benefit Record Number

5 BENEFIT_RCD_NBR Number(3,0) SMALLINT NOT NULL Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.

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

7 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name

8 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

9 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department SetID
Prompt Table: SETID_TBL

10 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
Prompt Table: PERS_DEPT_VW

11 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker
EMP=Employee
POI=Person of Interest

12 PRIMARY_ERN Number(4,0) SMALLINT NOT NULL Benefits Pre Upgrade Step primary_ern value set by client

13 POI_TYPE Character(5) VARCHAR2(5) NOT NULL Defines the Transactional usage of POIs in HRMS.
Prompt Table: POI_TYPE_TBL

14 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.

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

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

17 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

18 SVC_FROM_DT Date(10) DATE Service From Date

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
2	ROWSECCLASS	Character(30)	VARCHAR2(30) NOT NULL	The class used to determine row level security
3	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
4	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Benefit Record Number
5	BENEFIT_RCD_NBR	Number(3,0)	SMALLINT NOT NULL	Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.
6	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
7	LAST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Last Name
8	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
9	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department SetID Prompt Table: SETID_TBL
10	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: PERS_DEPT_VW
11	PER_ORG	Character(3)	VARCHAR2(3) NOT NULL	Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest. CWR=Contingent Worker EMP=Employee POI=Person of Interest
12	PRIMARY_ERN	Number(4,0)	SMALLINT NOT NULL	Benefits Pre Upgrade Step primary_ern value set by client
13	POI_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Defines the Transactional usage of POIs in HRMS. Prompt Table: POI_TYPE_TBL
14	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.
15	BENEFIT_PLAN	Character(6)	VARCHAR2(6) NOT NULL	Benefit Plan
16	CLAIMID	Character(8)	VARCHAR2(8) NOT NULL	Claim ID
17	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
18	SVC_FROM_DT	Date(10)	DATE	Service From Date