FSA_HC_SRCH_CAN(SQL View) |
Index Back |
|---|---|
Search Vw-Heath FSA Partic CanThis is the multiple-jobs version of FSA_HC_SRCH_CAN for use as a search view in Benefits components. It "maps" BENEFIT_RCD_NBR into EMPL_RCD so tracks of benefits can be presented without any change in page structure. |
| SELECT %subrec(EMPLMT_BEN_SBR, SEC) ,CL.CLAIMID ,CL.PLAN_TYPE ,CL.BENEFIT_NBR ,CL.BENEFIT_PLAN ,CL.SVC_FROM_DT ,CL.SVC_TO_DT ,CL.SVC_TYPE ,CL.FSA_CLAIM_SUB_AMT FROM PS_EMPL_BEN_SRCH SEC , PS_FSA_CLAIM CL WHERE CL.EMPLID=SEC.EMPLID AND CL.EMPL_RCD=SEC.EMPL_RCD AND CL.PLAN_TYPE='65' 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 | 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 | CLAIMID |
Character(8) | VARCHAR2(8) NOT NULL | Claim ID |
| 16 | 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. |
| 17 | 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. |
| 18 | BENEFIT_PLAN |
Character(6) | VARCHAR2(6) NOT NULL | Benefit Plan |
| 19 | SVC_FROM_DT |
Date(10) | DATE | Service From Date |
| 20 | SVC_TO_DT |
Date(10) | DATE | Service To Date |
| 21 | 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 |
| 22 | FSA_CLAIM_SUB_AMT |
Number(8,2) | DECIMAL(7,2) NOT NULL | Claim Submitted Amount |