Wiki: LOADING ELECTIONS AND COMPONENT AMOUNTS INTO IBEL

Here is the minimum set up required to load elections with a BC code value via LBEN. The load file is a comma delimited format.

Rec #	Field #	Name	Beg Pos	End Pos	Constant Value	Derivation Expression	Notes
10	1	HEADER RECORD*	0	0			(Optional) If there is a header record in the file than a header field must be defined in the interface.
30	1	BEL.BPN_ID.PLAN_CODE	-1	-1
30	2	BEL.EEM_ID.EID_ID.PERSON_CODE	-1	-1
30	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
30	4	BEL.ELECTION_STATUS	-1	-1			'80-Declinded' or '90-Remove' can also be used. '00-Not yet Elected' will result in no changes occuring to the record and should not be used.
30	5	BEL.BCG_ID.COVERAGE_CODE	-1	-1
30	6	BELC.BBC_ID.BC_CODE	-1	-1
30	7	BELC.AMOUNT_OVERRIDE	-1	-1			The AMOUNT_OVERRIDE is used when loading values into an enrollment record and not the AMOUNT field.
30	8	BEL.BE_EFFECTIVE_DATE	-1	-1
30	9	BEL.F_QUALIFIER***	-1	-1	TRUE	DECODE(~,'Y','TRUE','FALSE'	(Optional) The LMTD will use this field to determine if the record qualifies for this interface. If the value returned in the derivation does not match the value in the constant field than the record will be bypassed and not loaded.
70	1	TRAILER RECORD**	0	0			(Optional)If there is a trailer record in the file than a trailer field must be defined in the interface.

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'

Notes #

Click to create a new notes page