mpi_memelig and mpi_memelig_h
The mpi_memelig table stores member insurance eligibility information.
- Expanded name
- Member Insurance Eligibility
- Table category
- Member
- Purpose
- This member table stores insurance eligibility information and represents one of the core data model data types. Whereas it is possible to store each attribute as a separate type, creating many different segments (MEMNAME, MEMADDR and MEMATTRs), all information relating to member eligibility is stored here for performance purposes. The “_h” table supports the storage of point-in-time history.
- Cross-reference
- This table is a child table of mpi_memhead. The record must belong to one of the defined segments specified in mpi_segattr. Record creation and modification times are associated with mpi_audhead records.
| Attribute | Description |
|---|---|
| daudrecno | Deletion of this particular record, from mpi_audhead. This field only appears in the "_h" table. |
| memrecno | Unique member number from mpi_memhead |
| memseqno | Member Sequence Number is a system generated
value used to provide a unique primary key for attributes rows within
a member; obtained from mpi_memhead. The operational server permits multiple values for a given attribute type (for example, HOMEPHONE). The value is generated by looking at the value in mpi_memhead.memseqno. When a member is new, this value is 1. Each time an attribute is added for the member, the memseqno value is incremented. Later, attributes might get deleted or trimmed by the auto-history-purge mechanism and there can be gaps in the number sequence. The gap in sequence is not important; the software just cares that there is a unique value for each existing row. Currently the operational server does not reference memseqno for attribute history management. Rather common values are collapsed into a single row in the database with a date stamp for the first time the value was seen in the database. A date stamp for the last time the database knew this value to be true is also stored. By maintaining memseqno however, the ability to modify history management if required in future versions is retained. |
| caudrecno | Creation of this particular record, from mpi_audhead |
| maudrecno | Last time the record value was modified, from mpi_audhead |
| recstat | Record status; options are: A – Active. The record is current and should be used in all matching-related activities. I – Inactive. The record is not current and should not be used in matching-related activities, but is retained for retrieval due to historical significance. D – Deleted. The record is marked for deletion, pending a purge of data, and should not be used in matching-related activities. S – Shadow. The record is current, and should be used in all matching-related activities. The record is somewhat in limbo, pending verification due to workflow-related activities in the task-oriented tables. |
| attrrecno | Attribute record number, from mpi_segattr |
| asaidxno | Attribute Sparse Array Index; used to subdivide
a given attribute and permit the association of multiple occurrences
of the same attribute type. The primary function of asaidxno is to direct the placement of attribute values from source systems into slots in the MDM database. For example, multiple Name values can be received from a single source. If the source system can distinguish between the multiple names, you can use asaidxno to make sure that the MDM database keeps that same distinction. An asaidxno number is assigned to each incoming value and the value is then slotted accordingly in the MDM database, such as HOMEPHONE[1] 111-1111, HOMEPHONE[2] 222-2222, HOMEPHONE[3] 333-3333. The source system can then specify that they are changing the value for the HOMEPHONE[1], as opposed to a non-asaidxno setup where all of the phone values would be mixed together. Asaidxno further works in association with the nsActive and nsExists settings in mpi_segattr to enable the process of tracking active and inactive values at the asaidxno level (mpi_segattr). The inbound message broker process uses the asaidxno to indicate where to store the attribute value in the MDM database, while the outbound message broker process uses it to direct the return of attribute values from the MDM database to the source. |
| onmlast | Last name |
| onmfirst | First name |
| onmmiddle | Middle name |
| onmprefix | Prefix for name (for example, Dr., Mr., Ms.) |
| onmsuffix | Suffix for name (for example, II, III) |
| onmdegree | Conferred suffix (for example, PhD, MCSE) |
| stline1 | First line of street address |
| stline2 | Second line of street address |
| city | Address city |
| state | Address state or province |
| zipcode | Address Zip Code |
| country | Address country |
| geotext1 | Geographic designation, normally used for HL7 compliance |
| geocode1 | Geographic encoding, normally used for HL7 compliance |
| geocode2 | Geographic encoding, normally used for HL7 compliance |
| phicc | International Country Code |
| pharea | Area code |
| phnumber | Phone number |
| phextn | Phone extension |
| phcmnt | String |
| dob | Customer date of birth |
| ssn | Customer Social Security number |
| sex | Customer gender |
| percode | Person code |
| insgroup | Insurance group identifier |
| insplan | Insurance plan identifier |
| eligdate | Start date of eligibility for this plan |
| termdate | End date of eligibility for this plan |
mpi_memelig SQL:
CREATE TABLE mpi_memelig
(
daudrecno bigint NOT NULL,
memrecno bigint NOT NULL,
memseqno smallint NOT NULL,
caudrecno bigint NOT NULL,
maudrecno bigint NOT NULL,
recstat nchar(1) NOT NULL,
attrrecno smallint NOT NULL,
asaidxno smallint NOT NULL
onmlast nvarchar(75) NULL,
onmfirst nvarchar(30) NULL,
onmmiddle nvarchar(30) NULL,
onmprefix nvarchar(8) NULL,
onmsuffix nvarchar(8) NULL,
onmdegree nvarchar(8) NULL,
stline1 nvarchar(60) NULL,
stline2 nvarchar(60) NULL,
city nvarchar(20) NULL,
state nvarchar(15) NULL,
zipcode nvarchar(15) NULL,
country nvarchar(3) NULL,
geotext1 nvarchar(40) NULL,
geocode1 nvarchar(10) NULL,
geocode2 nvarchar(10) NULL,
phicc nvarchar(3) NULL,
pharea nvarchar(5) NULL,
phnumber nvarchar(13) NULL,
phextn nvarchar(6) NULL,
phcmnt nvarchar(20) NULL,
dob datetime NULL,
ssn nvarchar(9) NULL,
sex nvarchar(1) NULL,
percode nvarchar(3) NULL,
insgroup nvarchar(20) NULL,
insplan nvarchar(20) NULL,
eligdate datetime NULL,
termdate datetime NULL
)
CREATE UNIQUE INDEX mpi_memelig1 ON mpi_memelig (memrecno, memseqno)