Here is an example of an interface that was done for a specific client. The load file is a fixed format, and is used to load employee elections and component amounts into IBEL.
| Rec # | Field # | Name | Beg Pos | End Pos | Constant Value | Derivation Expression | Notes |
|---|---|---|---|---|---|---|---|
| 0 | 0 | HEADER RECORD* | 0 | 0 | Since there is 1 header record in the file, one field must be defined in the interface for the header record. | ||
| 1 | 1 | BEL.BPN_ID.PLAN_CODE | -1 | -1 | P2K_SMGLX('GWRS_PLAN_TRNSLT',~) | The derivation will translate the policy number in the file to the plan code using a user defined lexicon. | |
| 1 | 2 | BEL.EEM_ID.EID_ID.PERSON_CODE | -1 | -1 | LTRIM(~,'0') | The person code will be trimmed of any leading zeros. | |
| 1 | 3 | BEL.EEM_ID.TERMINATION_DATE | -1 | -1 | ‘’ | The constant value of two quotation marks (no space) is used to determine a NULL termination date which will be the ACTIVE employment | |
| 1 | 4 | BEL.ELECTION_STATUS | -1 | -1 | 00 | The election status will be hard coded to Not Yet Elected, LBEN will then elect and enroll the employee. Since this field does not have a value within the file, it has been defined with -1. | |
| 1 | 5 | BEL.BCG_ID.COVERAGE_CODE | -1 | -1 | P2K_SMGLX('GWRS_PLAN_TRNSLT',~) | Since both coverage code and plan code are the same for these plans, the derivation expression from plan code field may be used. Since this field does not have a value within the file, it has been defined with -1. | |
| 1 | 6 | BELC.BBC_ID.BC_CODE | -1 | -1 | B1000 | Hard coded to B1000 so the system know which BC Code to update. Since this field does not have a value within the file, it has been defined with -1. | |
| 1 | 7 | BELC.AMOUNT_OVERRIDE | -1 | -1 | TO_NUMBER(~)/10 | This field will pick up the amount from the flat file. | |
| 1 | 8 | BEL.BE_EFFECTIVE_DATE | -1 | -1 | NLV2(~,(to_char(to_date(rtrim(~),’YYYMMDD’),’DD-Mon-YYYY’)),’01-Jan-0001’) | ||
| 1 | 9 | BEL.F_QUALIFIER*** | -1 | -1 | TRUE | CASE When (:BELC.AMOUNT_OVERRIDE)=’0000’ then ‘FALSE’ else ‘TRUE’ end | Do not process records with zeros – these are for unenrollments |
| 2 | 0 | TRAILER RECORD** | 0 | 0 | Since there is 1 trailer record in the file, one field must be defined in the interface for the trailer record. |
- *Record 0, Field 0 must have a Record Type of 'Header Record'
- **Record 2, Field 0 must have a Record Type of 'Trailer Record'
- ***Record 1, Field 9 must have a Record Type of 'Qualify Record'
