FSA_ADJUST_SRCH (SQL View)	Index Back
Search FSA Adjustment FSA_CLAIM_SRCH is a view of employee with data in FSA_SUMMARY and plan type is 60 and the carryover option is used.

FSA_ADJUST_SRCH

(SQL View)

Search FSA Adjustment

FSA_CLAIM_SRCH is a view of employee with data in FSA_SUMMARY and plan type is 60 and the carryover option is used.

SELECT %subrec(EMPLMT_BEN_SBR, SEC) , FC.BALANCE_YEAR , A.FSA_CARRYOVER_LMT , B.ANNUAL_PLEDGE , B.FSA_ACCT_STATUS FROM PS_EMPL_BEN_SRCH SEC , PS_FSA_SUMMARY FC , PS_FSA_BENEF_TBL A , PS_FSA_BENEFIT B WHERE FC.EMPLID=SEC.EMPLID AND FC.PLAN_TYPE = '60' AND A.PLAN_TYPE = FC.PLAN_TYPE AND A.PLAN_TYPE = B.PLAN_TYPE AND B.EMPLID = SEC.EMPLID AND B.EMPLID = FC.EMPLID AND A.PLAN_YEAR = FC.BALANCE_YEAR AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_FSA_BENEF_TBL A1 WHERE A1.PLAN_TYPE = A.PLAN_TYPE AND A1.PLAN_YEAR = FC.BALANCE_YEAR ) AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_FSA_BENEFIT B1 WHERE B1.EMPLID = B.EMPLID AND B1.PLAN_TYPE = B.PLAN_TYPE AND B1.COBRA_EVENT_ID = B.COBRA_EVENT_ID AND B1.EFFDT BETWEEN A.SRVC_DT_FROM AND A.SRVC_DT_THRU ) AND A.BENEFIT_PLAN = B.BENEFIT_PLAN AND A.FSA_CARRYOVER_LMT > 0

# 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 BALANCE_YEAR Number(4,0) SMALLINT NOT NULL Balances for Year

15 FSA_CARRYOVER_LMT Number(9,2) DECIMAL(8,2) NOT NULL The field will contain the carryover limit for health spending accounts

16 ANNUAL_PLEDGE Signed Number(10,2) DECIMAL(8,2) NOT NULL Annual Pledge

17 FSA_CLOSE Character(1) VARCHAR2(1) NOT NULL FSA Close

#	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	BALANCE_YEAR	Number(4,0)	SMALLINT NOT NULL	Balances for Year
15	FSA_CARRYOVER_LMT	Number(9,2)	DECIMAL(8,2) NOT NULL	The field will contain the carryover limit for health spending accounts
16	ANNUAL_PLEDGE	Signed Number(10,2)	DECIMAL(8,2) NOT NULL	Annual Pledge
17	FSA_CLOSE	Character(1)	VARCHAR2(1) NOT NULL	FSA Close