FSA_HC_SRCH (SQL View)	Index Back
Search Vw-Heath FSA Particpnt FSA_HC_SRCH is a view of Health Care FSA Participants, that is, employees who have been enrolled in a FSA Health Care benefit plan using the FSA Benefit Enrollment process.

FSA_HC_SRCH

(SQL View)

Search Vw-Heath FSA Particpnt

FSA_HC_SRCH is a view of Health Care FSA Participants, that is, employees who have been enrolled in a FSA Health Care benefit plan using the FSA Benefit Enrollment process.

SELECT DISTINCT %subrec(EMPLMT_BEN_SBR,A) ,CL.PLAN_TYPE FROM PS_EMPL_BEN_SRCH A ,PS_FSA_CLAIM CL WHERE CL.EMPLID=A.EMPLID AND CL.EMPL_RCD=A.EMPL_RCD AND CL.PLAN_TYPE NOT IN ('65' ,'66') AND CL.FSA_CLAIM_STATUS='S'

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

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