OPRIDEMPLIDEMPL_RCDPrompt Table: SETID_TBL
Prompt Table: PERS_DEPT_VW
CWR=Contingent Worker
EMP=Employee
POI=Person of Interest
Prompt Table: POI_TYPE_TBL
BALANCE_YEARFSA_ADJUST_SRCH(SQL View) |
Index Back |
|---|---|
Search FSA AdjustmentFSA_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 | NAME_DISPLAY_SRCH | Character(50) | VARCHAR2(50) NOT NULL | Uppercase version of Name to improve performance on searches. |
| 8 | LAST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 9 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 10 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL |
Department Set ID
Prompt Table: SETID_TBL |
| 11 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL |
Department
Prompt Table: PERS_DEPT_VW |
| 12 | 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 |
| 13 | PRIMARY_ERN | Number(4,0) | SMALLINT NOT NULL | Benefits Pre Upgrade Step primary_ern value set by client |
| 14 | POI_TYPE | Character(5) | VARCHAR2(5) NOT NULL |
Defines the Transactional usage of POIs in HRMS.
Prompt Table: POI_TYPE_TBL |
| 15 | BALANCE_YEAR |
Number(4,0) | SMALLINT NOT NULL | Balances for Year |
| 16 | FSA_CARRYOVER_LMT | Number(9,2) | DECIMAL(8,2) NOT NULL | The field will contain the carryover limit for health spending accounts |
| 17 | ANNUAL_PLEDGE | Signed Number(10,2) | DECIMAL(8,2) NOT NULL | Annual Pledge |
| 18 | FSA_CLOSE | Character(1) | VARCHAR2(1) NOT NULL | FSA Close |