Excel

Module Level Data Models and Table Layouts

3.1 General Rules

1. ikaSystems needs headers as the first record: needed to identify which EXACT columns are missing

2. ikaSystems need a .CSV file with individual Character Columns within “Double Quotes“

3. If more than one value inside a column are provided, they should be comma-separated, e.g., Specialty List can be submitted as – “012,234,654”

4. All IDs in the tables should be comprised of alphanumeric characters; Underscore (“_”) is the only other character allowed

5. There should not be Newline characters or tab spaces in any of the fields, not even in the

Notes/Comments fields

6. All Dates if not otherwise specified should be in MM/DD/YYYY format

7. Phone Nos. and FAX Nos. should be in XXX-XXX-XXXX format

8. SSN / Tax IDs should be in XXX-XX-XXXX format

9. ZIP codes should be in XXXXX-XXXX or just XXXXX format

10. User IDs if not available can be defaulted to 999901 – Data Load User ID

11. Date Timestamp should be in format MM/DD/YYYY HH24MI:SS; if not provided, will be defaulted to SYSTIMESTAMP

12. Unless specified otherwise, while data is loading, it uses USER_ID of 999901 (Default Data Load

User ID) and DATE_TIMESTAMP of SYSTIMESTAMP

3.2 Providers Module

3.2.1 Provider Table

File Naming Convention : <ClientName>_ika_provider_extract.csv

The Provider table contains data related to the Professional or Facility providing the service.

The following rules apply for the records in this table:

1. Primary Key: PROVIDER_ID

2. Duplicate check is based on Providers with same IDs

3. Each record belongs to either a Professional (Individual – Type ‘1’) or a Provider (Entity/Office

– Type ‘0’)

4. If Provider (Type ‘0’):

 Billing Info is required

 Business Address is required (1 record with Address Suffix ‘A’ mandatory)

 Business Contact is required

 Provider 1099 Info/Form 1099 Info is required

 Name and Tax ID are required

5. If Professional (Type ‘1’):

 Tax ID and 1099 Info is needed if paying to a Professional

 Last Name and First Name are required

6. Specialty codes:

 At least one Specialty is mandatory for a Professional

 Should be three characters long (else zero-padded to the left)

 Should be comma-separated if more than one exists

 Specialty Code is Code Type ‘11’ in CODES_USER_DEFINED (Physician Specialty)

7. There cannot be a PEND_EFFECTIVE_DATE without a PEND_FORMULA

8. BOARD_CERTIFICATION and BOARD_APPROVAL_DATE validations:

 If BOARD_CERTIFICATION_DATE is not provided, then

BOARD_CERTIFICATION_EXP_DATE should be NULL

 If BOARD_CERTIFICATION_EXP_DATE is not provided, then

BOARD_CERTIFICATION_DATE should be NULL

 If BOARD_CERTIFICATION_DATE_2 is not provided, then

BOARD_CERTIFICATION_EXP_DATE_2 should be NULL

 If BOARD_CERTIFICATION_EXP_DATE_2 is not provided, then

BOARD_CERTIFICATION_DATE_2 should be NULL

 If BOARD_CERTIFICATION_DATE_3 is not provided, then

BOARD_CERTIFICATION_EXP_DATE_3 should be NULL

 If BOARD_CERTIFICATION_EXP_DATE_3 is not provided, then

BOARD_CERTIFICATION_DATE_3 should be NULL

 If BOARD_CERTIFICATION_DATE_4 is not provided, then

BOARD_CERTIFICATION_EXP_DATE_4 should be NULL

 If BOARD_CERTIFICATION_EXP_DATE_4 is not provided, then

BOARD_CERTIFICATION_DATE_4 should be NULL

 If BOARD_APPROVED_DATE is not provided, then BOARD_APPROVED_EXP_DATE

should be NULL

 If BOARD_APPROVED_EXP_DATE is not provided, then BOARD_APPROVED_DATE

should be NULL

 If BOARD_APPROVED_DATE_2 is not provided, then BOARD_APPROVED_EXP_DATE_2 should be NULL

 If BOARD_APPROVED_EXP_DATE_2 is not provided, then BOARD_APPROVED_DATE_2 should be NULL

 If BOARD_APPROVED_DATE_3 is not provided, then BOARD_APPROVED_EXP_DATE_3 should be NULL

 If BOARD_APPROVED_EXP_DATE_3 is not provided, then BOARD_APPROVED_DATE_3 should be NULL

 If BOARD_APPROVED_DATE_4 is not provided, then BOARD_APPROVED_EXP_DATE_4 should be NULL

 If BOARD_APPROVED_EXP_DATE_4 is not provided, then BOARD_APPROVED_DATE_4 should be NULL

 BOARD_CERTIFICATION_DATE and BOARD_CERTIFICATION_EXP_DATE should not be same

 BOARD_CERTIFICATION_DATE_2 and BOARD_CERTIFICATION_EXP_DATE_2 should not be same

 BOARD_CERTIFICATION_DATE_3 and BOARD_CERTIFICATION_EXP_DATE_3 should not be same

 BOARD_CERTIFICATION_DATE_4 and BOARD_CERTIFICATION_EXP_DATE_4 should not be same

 BOARD_APPROVED_DATE and BOARD_APPROVED_EXP_DATE should not be same

 BOARD_APPROVED_DATE_2 and BOARD_APPROVED_EXP_DATE_2 should not be same

 BOARD_APPROVED_DATE_3 and BOARD_APPROVED_EXP_DATE_3 should not be same

 BOARD_APPROVED_DATE_4 and BOARD_APPROVED_EXP_DATE_4 should not be same

9. Facility Type should be valid - it is Code Type ‘52’ in CODES_STANDARD (Provider Type)

10. If there is 1 Specialty Code, then no more than 1 set of BOARD_CERTIFICATION_DATE and BOARD_APPROVED_DATE can be present; if 2 specialty codes, then BOARD_CERTIFICATION_ DATE and BOARD_APPROVED_DATE, and BOARD_CERTIFICATION_DATE_2 and BOARD_ APPROVED_DATE_2 can only be present, and so on

3.2.2 Provider Contract Table

File Naming Convention : <ClientName>_ika_provider_contract_extract.csv

This table will have the Provider Contract Details such as information related to the Provider’s

Network, Contract Effective Date, Coverage pool, etc.

The following rules apply for the records in this table:

1. Primary Key: P_NETWORK, PROVIDER_ID, PROFESSIONAL_ID, EFFECTIVE_DATE, SERVICE_PROVIDER_ID, CONTRACT_TYPE

2. Duplicate check is based on P Network, Provider ID, Professional ID, Effective Date, Service

Provider ID, Contract Type

3. Provider ID column should contain valid IDs from Provider table with Provider Type ‘0’

4. Professional ID should contain valid IDs from Provider table with Provider Type ‘1’

5. Contract Name should be present in CONTRACT table

6. P_NETWORK should be present in NETWORK_MASTER table

3.2.3 Form 1099 Info Table

File Naming Convention : <ClientName>_ika_form_1099_info_extract.csv

This table contains the Provider’s Billing Details. It will have the complete Billing Address information.

The following rules apply for the records in this table:

1. Primary Key: TIN, EFFECTIVE_DATE

2. Duplicate check is based on records with same Tax ID, Effective Date

3. There should be an entry for each Provider

4. Professionals will need 1099 Info if paying to a Professional

5. Zip Code should not have trailing ‘-’ or ‘(space) ’

6. If Phone / Fax is not available, column should be left Null

7. Phone / Fax format used should be ‘XXXXXXXXXX’

8. All Providers/Professionals (Type 0/1) having a Tax ID should have a valid entry in this table

9. Zip Code / State / Country should be valid combination

3.2.4 Billing Name Alias Table

File Naming Convention : <ClientName>_ika_billing_name_alias_extract.csv

This table contains the Provider Alias Information; these are DISTINCT Alias name(s) for the

Provider.

The following rules apply for the records in this table:

1. Primary Key: PROVIDER_ID, TAX_ID, ALIAS_BILLING_NAME

2. Duplicate check is based on records with same PROVIDER_ID, TAX_ID, ALIAS_BILLING_NAME

3. Each Provider ID should be valid, i.e., an entry in Provider table should be present

4. Each Tax ID (if present) should be valid, i.e., an entry in Form_1099_Info table should exist

5. Tax ID column is mandatory if Provider; if Professional, it’s optional (when NULL , default is ‘%’)

3.2.5 Facility Group Master Table

File Naming Convention: <ClientName>_ika_facility_group_master_extract.csv

This is the Master Table containing details about the Facility Groups. It is used to create a Facility to group Providers into.

The following rules apply for the records in this table:

1. Primary Key: FACILITY_GROUP_ID

2. Duplicate check is based on records with same Facility Group ID

3.2.6 Facility Groups Table

File Naming Convention: <ClientName>_ika_facility_groups_extract.csv

This table contains details about the Facility Groups, which consists of grouping multiple Providers

(Provider Type 0) and tying each Group to a particular Facility.

The following rules apply for the records in this table:

1. Primary Key: PROVIDER_ID, EFFECTIVE_DATE, FACILITY_GROUP_ID, ADDRESS_SUFFIX

2. Duplicate check is based on records with same Provider ID, Effective Date, Facility Group ID, Address Suffix

3. Provider ID should correspond to a valid record in Provider table

4. Facility Group ID should correspond to a valid record in Facility Group Master table

The forward to address must be configured in BUSINESS_ADDRESS and BUSINESS_CONTACT

tables as follows:

 ID_TYPE = '2'

 ID = Facility Group ID

 ADDRESS_SUFFIX = 'A' - Business Address, 'G' - IPA Claim Forwarding Address, 'H' - Hospital

Forwarding Address.

Note ADDRESS_SUFFIX ‘A’ is required for all facility groups.

3.2.7 Provider Comments Table

File Naming Convention : <ClientName>_ika_provider_comments_extract.csv

This table contains the Provider Comments.

The following rules apply for the records in this table:

1. Primary Key: PROVIDER_ID, COMMENTS_SEQUENCE

2. Duplicate check is based on records with same Provider ID and Comments Sequence

3. Provider ID should correspond to a valid record in Provider table

3.2.8 Provider Patient Info Table

File Naming Convention : <ClientName>_ika_provider_patient_info_extract.csv

This table contains the Provider Patient Info. When members are being assigned to a PCP, this table is used to do the PCP Validations.

This table is not mandatory from a Claims perspective. However, if Claims module is integrated with other Products [e.g., ikaMedicareGateway], the PCP validation of Members is done using this table.

The following rules apply for the records in this table:

1. Primary Key: PROVIDER_ID, PRODUCT_TYPE and ADDRESS_SUFFIX

2. Duplicate check is based on records with same PROVIDER_ID, PRODUCT_TYPE and

ADDRESS_SUFFIX

3. Provider ID should correspond to a valid record in Provider table

3.2.9 Provider Network Parameters

File Naming Convention : <ClientName>_ika_provider_network_parameters_extract.csv

This table contains the Provider Network Parameter info.

The following rules apply for the records in this table:

1. Primary Key: PARAM_SEQ

2. Duplicate check is based on records with same ID, ID_TYPE, PARAM_ID and BEGIN_DATE

3. Provider ID should correspond to a valid record in Provider table

(Provider/Group/Division/Department/Section)

3.3.1 Billing Info Table

File Naming Convention : <ClientName>_ika_billing_info_extract.csv

This table will be used by both the Group and Provider Modules. It contains the complete Billing Address and DFI Account-related data. This is a mandatory table. All Providers (Type 0) must have a Billing Info entry.

The following rules apply for the records in this table:

1. Primary Key: ID, ID_TYPE, EFFECTIVE_DATE

2. Duplicate check is based on records with same ID, ID Type, Effective Date

3. ID of Provider or Group should match Provider ID if ID type is 4, or Group ID if ID Type is 6

4. There should be one record for all Providers (Type 0) and for all

5. Group/Division/Department /Section

6. Zip Code should not have trailing ‘-’ or ‘(space) ’

7. If Fax is not available, column should be left Null

8. Phone / Fax format used should be ‘XXX-XXX-XXXX’

9. Zip Code / State / Country should be valid combination

10. For ID_TYPE = ‘7’ (DIVISION), ID = GROUP_ID concatenated with DIVISION_ID. For ex: if

GROUP_ID = GRP1, DIVISION_ID = DIV1 then ID = GRP1DIV1

11. For ID_TYPE = ‘8’ (DEPARTMENT), ID = GROUP_ID concatenated with DIVISION_ID and DEPARTMENT_ID. For ex: if GROUP_ID = GRP1, DIVISION_ID = DIV1, DEPARTMENT_ID = DEP1 then ID = GRP1DIV1DEP1

12. For ID_TYPE = ‘9’(SECTION), ID = GROUP_ID concatenated with DIVISION_ID, DEPARTMENT_ID and SECTION_ID. For ex: if GROUP_ID = GRP1, DIVISION_ID = DIV1, DEPARTMENT_ID = DEP1, SECTION_ID = SEC1 then ID = GRP1DIV1DEP1SEC1

3.3.2 Business Address Table

File Naming Convention : <ClientName>_ika_business_address_extract.csv

This table will be used by both the Group and Provider Modules. It contains the Business

Address(es) and daily Business hours for the Provider/Group.

The following rules apply for the records in this table:

1. Primary Key: ID_TYPE, ID, ADDRESS_SUFFIX

2. Duplicate check is based on records with same ID, ID Type, Address Suffix

3. Provider / Group should have just 1 record, with Address Suffix ‘A’

4. For IDs with two or more records, there should be one record with Address Suffix ‘A’

5. Each record should have a corresponding ID in Provider / Prime_Group table

6. There should be one record for all Providers (Type 0 and Type 1) and for all Group / Division / Department / Sections

7. Zip Code should not have trailing hyphen (‘-’) or space (‘ ’)

8. County: State FIPS + County FIPS; County is mandatory for Providers/Professionals

9. Each Provider should have a Business Address

10. Number of records in Business Address should match that in Business Contact table

11. Zip Code / State / Country should be valid combination

12. For ID_TYPE = ‘7’ (DIVISION), ID = GROUP_ID concatenated with DIVISION_ID. For ex: if

GROUP_ID = GRP1, DIVISION_ID = DIV1 then ID = GRP1DIV1

13. For ID_TYPE = ‘8’ (DEPARTMENT), ID = GROUP_ID concatenated with DIVISION_ID and DEPARTMENT_ID. For ex: if GROUP_ID = GRP1, DIVISION_ID = DIV1, DEPARTMENT_ID = DEP1 then ID = GRP1DIV1DEP1

14. For ID_TYPE = ‘9’(SECTION), ID = GROUP_ID concatenated with DIVISION_ID, DEPARTMENT_ID and SECTION_ID. For ex: if GROUP_ID = GRP1, DIVISION_ID = DIV1, DEPARTMENT_ID = DEP1, SECTION_ID = SEC1 then ID = GRP1DIV1DEP1SEC1

3.3.3 Business Contact Table

File Naming Convention : <ClientName>_ika_business_contact_extract.csv

This table will be used by both the Group and Provider Modules. It contains the Business Contact information, the point of contact, the preferable mode of contact, and other details.

Business Contact is a mandatory table.

The following rules apply for the records in this table:

1. Primary Key: ID_TYPE, ID, ADDRESS_SUFFIX, CONTACT_SEQ

2. ID of Provider or Group should match Provider ID if ID type is 4, or Group ID if ID Type is 6

3. There should be one record for all Providers (Type 0 and Type 1) and for all Group / Division / Department / Section

4. Duplicate check is based on records with same ID, ID Type, Address Suffix, Contact Seq

5. If Fax is not available, column should be left Null

6. Phone / Fax format should be ‘XXX-XXX-XXXX’

7. Each Provider should have a Business Contact

8. Number of records in Business Address should match that in Business Contact table

9. For ID_TYPE = ‘7’ (DIVISION), ID = GROUP_ID concatenated with DIVISION_ID. For ex: if

GROUP_ID = GRP1, DIVISION_ID = DIV1 then ID = GRP1DIV1

10. For ID_TYPE = ‘8’ (DEPARTMENT), ID = GROUP_ID concatenated with DIVISION_ID and DEPARTMENT_ID. For ex: if GROUP_ID = GRP1, DIVISION_ID = DIV1, DEPARTMENT_ID = DEP1 then ID = GRP1DIV1DEP1

11. For ID_TYPE = ‘9’(SECTION), ID = GROUP_ID concatenated with DIVISION_ID, DEPARTMENT_ID and SECTION_ID. For ex: if GROUP_ID = GRP1, DIVISION_ID = DIV1, DEPARTMENT_ID = DEP1, SECTION_ID = SEC1 then ID = GRP1DIV1DEP1SEC1

3.3.4 Groups Module

3.4.1 Prime Group Table

File Naming Convention : <ClientName>_ika_prime_group_extract.csv

This table contains the Group details. There should be entry in business address, business contact and billing info table for ID type 6.

The following rules apply for the records in this table:

1. Primary Key: GROUP_ID

2. Duplicate check is based on records with same Group ID

3.4.2 Group Division Table

File Naming Convention : <ClientName>_ika_group_division_extract.csv

This table contains the Group Division details. There should be entry in business address, business contact and billing info table for ID type 7.

The following rules apply for the records in this table:

1. Primary Key: GROUP_ID, DIVISION_ID

2. Duplicate check is based on records with same Group ID, Division ID

3.4.3 Group Department Table

File Naming Convention : <ClientName>_ika_group_department_extract.csv

This table contains the Group Department details. There should be entry in business address, business contact and billing info table for ID type 8.

The following rules apply for the records in this table:

1. Primary Key: GROUP_ID, DIVISION_ID, DEPARTMENT_ID

2. Duplicate check is based on records with same Group ID, Division ID, Department ID

3.4.4 Group Section Table

File Naming Convention : <ClientName>_ika_group_section_extract.csv

This table contains the Group Section details. There should be entry in business address, business contact and billing info table for ID type 9.

The following rules apply for the records in this table:

1. Primary Key: GROUP_ID, DIVISION_ID, DEPARTMENT_ID, SECTION_ID

2. Duplicate check is based on records with same Group ID, Division ID, Department ID, Section

ID

3.4.5 Group Benefit Table

File Naming Convention : <ClientName>_ika_group_benefit_extract.csv

This table contains the Group and Benefit plan history.

The following rules apply for the records in this table:

1. Primary Key: GROUP_ID, EFFECTIVE_DATE, BENEFIT_PLAN_ID, DIVISION_ID

2. Duplicate check is based on records with same Group ID, Effective Date, Division ID and Benefit

Plan ID

3. Group ID should be a valid Group in Prime Group table

4. Division ID should be a valid Division in Group Division table

5. Benefit Plan ID should be a valid Plan

3.4.6 Group Comments Table

File Naming Convention : <ClientName>_ika_group_comments_extract.csv

This table contains the Group Comments.

The following rules apply for the records in this table:

1. Primary Key: ID, ID_TYPE, COMMENTS_SEQUENCE

2. Duplicate check is based on records with same ID, ID Type, Comments Sequence

3. ID should be a valid Group / Division / Department / Section ID

3.4.7 Group Riders History Table

File Naming Convention : <ClientName>_ika_group_riders_history_extract.csv

This table contains the Group Riders history.

Following rules apply for the records in this table:

1. Primary Key: GROUP_PLAN_ID_SEQ, BEN_CAT_ID, RIDER_ID

2. Duplicate check is based on records same GROUP_PLAN_ID_SEQ, BEN_CAT_ID, RIDER_ID

3. BEN_CAT_ID should be a valid Benefit Category set up in the BEN_CAT_MASTER table

4. RIDER_ID should be a valid Rider set up in RIDER_MASTER table

5. GROUP_PLAN_ID_SEQUENCE should match with a valid GROUP_PLAN_ID SEQUENCE in

GROUP_BENEFIT table

3.4.8 Group Other Parameters Table

File Naming Convention: <ClientName>_ika_group_other_parameters_extract.csv

This table contains any parameters which the client needs configured at the group or division level.

The following rules apply for the records in this table:

1. Primary Key

 PARAM_SEQ – This is a unique ID for each entry in the table

2. Unique value combinations

 If Begin Date exists, then

 GROUP_ID, DIVISION_ID, BEGIN_DATE, PARAM_TYPE

 Else

 GROUP_ID, DIVISION_ID, PARAM_TYPE

3. PARAM_TYPE should be valid and present in Codes System, Code Type ‘GRPPARAM’ (Group

Parameters)

3.5 Members Module

3.5.1 Member Table

File Naming Convention : <ClientName>_ika_member_extract.csv

This table contains the details of the Member Demographics.

The following rules apply for the records in this table:

1. Primary Key: SUBSCRIBER_ID, MEMBER_SUFFIX

2. Duplicate check is based on records with same Subscriber ID, Member Suffix

3. Member Suffix should be two characters - e.g., ‘01’-> ikaSystems defaults to ‘01’

4. Each Member should have a valid entry in the MEMBER_ADDRESS, MEMBER_HISTORY and

FAMILY_HISTORY tables

5. If Pend Exp Date exists, then Pend Reason is mandatory

6. If Student field exists, then Student Validation Date is required

3.5.2 LIS Member History Table

File Naming Convention : <ClientName>_ika_lis_member_history_extract.csv

This table contains LIS Member History for Members

Following rules apply for the records in this table:

1. Primary Key: SUBSCRIBER_ID, MEMBER_SUFFIX, DATE_TIMESTAMP

2. Duplicate check is based on records with same SUBSCRIBER_ID, MEMBER_SUFFIX, DATE_TIMESTAMP

3. All Subscriber ID, Member Suffix should also be present in Member table

4. Member Suffix should be two characters - e.g., ‘01’

3.5.3 Member Address Table

File Naming Convention : <ClientName>_ika_member_address_extract.csv

This table contains all the Member Addresses.

The following rules apply for the records in this table:

1. Primary Key: SUBSCRIBER_ID, ADDRESS_SUFFIX, MEMBER_SUFFIX

2. Duplicate check is based on records with same Subscriber ID, Address Suffix

3. All Subscriber IDs should also be present in the Member table

4. All Subscriber IDs should also have an entry in MEMBER HISTORY and FAMILY HISTORY tables

5. Member Suffix should be two characters – e.g., ‘01’

6. Zip Code should not have trailing hyphen (‘-‘) or space (‘ ‘)

7. If Phone / Fax is not available, column should be left Null and not ‘ - - ‘ or ‘-‘

8. Phone / Fax format should be ‘XXX-XXX-XXXX’

9. Zip Code / State / Country should be valid combination

3.5.4 Member History Table

File Naming Convention : <ClientName>_ika_member_history_extract.csv

This table contains details about the Member eligibility history.

The following rules apply for the records in this table:

1. Primary Key: SUBSCRIBER_ID, MEMBER_SUFFIX, EFFECTIVE_DATE, ASSIGNMENT_TYPE

2. Duplicate check is based on records with same Subscriber ID, Member Suffix, Effective Date, Assignment Type

3. All Subscriber IDs should also be present in Member table

4. All Subscriber IDs should also have entry in MEMBER ADDRESS and FAMILY HISTORY tables

5. Member Suffix should be two characters - e.g., ‘01’

6. All Professional IDs - i.e., PCP - should have entry in Provider table with type ‘1’

Notes If record is termed in Family History, then all records in Member History need to be termed

If there are no dependents, then if record is termed in Member History, Family

History should also be termed

If all members in family are termed in Member History, then all members should be termed in Family History

3.5.5 Accumulators Table

File Naming Convention : <ClientName>_ika_accumulators_extract.csv

This table contains the member’s Accumulator details.

The following rules apply for the records in this table:

1. Primary Key: ACCUM_ID, SUBSCRIBER_ID, MEMBER_SUFFIX, BENEFIT_PERIOD_FROM

2. Duplicate check is based on records with same Accumulator ID, Subscriber ID, Member Suffix, Benefit Period From

3. All Subscriber IDs should also be present in Member table

4. All Accumulators should have Accumulator Type – ACCUM_TYPE should be same as

CODE_PARAM for ACCUM_ID value in CODES USER DEFINED table

5. Member Suffix should be two characters - e.g., ‘01’

3.5.6 Family History Table

File Naming Convention : <ClientName>_ika_family_history_extract.csv

This table contains the Subscriber / Family eligibility history.

The following rules apply for the records in this table:

1. Primary Key: SUBSCRIBER_ID, EFFECTIVE_DATE

2. Duplicate check is based on records with same Subscriber ID, Effective Date

3. All Subscriber ID should also be present in the Member table

4. All Subscriber IDs should also have an entry in MEMBER HISTORY and MEMBER ADDRESS

tables

5. Member Suffix should be two characters - e.g. ‘01’

6. Group ID should be valid entry in PRIME GROUP table

7. Benefit Plan ID should be a valid Plan ID

Notes: If Subscriber is termed in Family History, then it should also be termed in

Member History

If there are no dependents, then if record is termed in Member History, Family

History should also be termed

If all members in family are termed in Member History, then all members should be termed in Family History

Reason for Termination is required for records where member is termed

Group – Benefit association of member should be also present in

GROUP_BENEFIT table

Member Effective Date MUST fall within the Group – Benefit Effective and

Expiration Dates

“No*” in the following table indicates that the field is conditionally required per the above validations.

3.5.7 Double (Other) Coverage Table

File Naming Convention : <ClientName>_ika_double_coverage_extract.csv

This table contains the details for a member which has a Double (Other) Coverage.

The following rules apply for the records in this table:

1. Primary Key: SUBSCRIBER_ID, MEMBER_SUFFIX, EFFECTIVE_DATE, INSURANCE_ID

2. Duplicate check is based on Records with same Subscriber ID, Member Suffix, Effective Date, Insurance ID

3. All Subscriber IDs should also be present in Member table

4. Member Suffix should be two characters - e.g., ‘01’

3.5.8 Member Broker History Table

File Naming Convention : <ClientName>_ika_member_broker_history_extract.csv

This table contains the Members Broker History information.

The following rules apply for the records in this table:

1. Duplicate check is based on records with same Subscriber ID, Member Suffix, Effective Date

2. All Subscriber IDs should also be present in Member table

3. Member Suffix should be two characters - e.g., ‘01’

3.5.9 COBRA Subscriber Table

File Naming Convention : <ClientName>_ika_cobra_subscriber_extract.csv

This table contains the Cobra Subscriber Information. It defines the Cobra Subscribers’

Configuration.

The following rules apply for the records in this table:

 Duplicate check is based on records with same Subscriber ID, Events Date, Group ID

3.5.10 Member Other Parameters Table

File Naming Convention : <ClientName>_ika_Member_Other_Parameters_extract.csv

This table contains any parameters which the client needs configured for Members.

The following rules apply for the records in this table:

1. Duplicate check is based on records with same SUBSCRIBER_ID, PARAM_TYPE, PARAM_VALUE

2. If Begin Date is empty, then duplicate check should be based on: SUBSCRIBER_ID, MEMBER_SUFFIX, PARAM_TYPE, PARAM_VALUE

Else

SUBSCRIBER_ID, MEMBER_SUFFIX, PARAM_TYPE, PARAM_VALUE, BEGIN_DATE

3. PARAM_TYPE should be valid and present in Codes System, Code Type ‘MEMPARAM’

(Member Other Parameter Types)

3.5.11 Pre Cond Master Table

File Naming Convention : <ClientName>_ika_pre_cond_master_extract.csv

This table is used to store all pre-existing condition codes.

The following rules apply for the records in this table:

1. Primary Key: PRE_COND_ID

2. Duplicate check is based on records with same PRE_COND_ID

3. All PRE_COND_ID should be present in PRE_COND_MASTER table

3.5.12 Pre Cond Member Table

File Naming Convention : <ClientName>_ika_pre_cond_member_extract.csv

This table is used to store all members with pre-existing conditions.

The following rules apply for the records in this table:

1. Primary Key: SUBSCRIBER_ID, MEMBER_SUFFIX, PRE_COND_ID, START_DATE

2. Duplicate check is based on records with same SUBSCRIBER_ID, MEMBER_SUFFIX, PRE_COND_ID, START_DATE

3. All SUBSCRIBER_ID and MEMBER_SUFFIX should be present in MEMBER table

4. All PRE_COND_ID should be present in PRE_COND_MASTER table

3.5.13 LEP Member History Table

File Naming Convention : <ClientName>_ika_lep_member_history_extract.csv

This table is used to store Member late enrollment penalty history.

The following rules apply for the records in this table:

1. Primary Key: MEMBER_ID, BEGIN_DATE

2. Duplicate check is based on records with same MEMBER_ID, BEGIN_DATE

3. All MEMBER_ID, SUBSCRIBER_ID & MEMBER_SUFFIX should exist in MEMBER table

4. BEGIN_DATE and END_DATE are usually the first and last days of same month; occasionally, their span will cover multiple months, in which case PARTD_LEP_AMT will be considered cumulative, and will be divided by number of months to arrive at monthly amount

3.5.14 Secondary Plan History

File Naming Convention : <ClientName>_ika_secondary_plan_history_extract.csv

This table contains the secondary plan coverage

The following rules apply for the records in this table:

1. Primary Key: SUBSCRIBER_ID, MEMBER_SUFFIX, EFFECTIVE_DATE

2. Duplicate check is based on records with same Subscriber ID, Member Suffix, Effective

Date

3. All Subscriber ID and member suffix should also be present in the Member table

4. Member Suffix should be two characters - e.g. ‘01’

5. Benefit Plan ID should be a valid Plan ID

Notes: If the member is terminated in Family History, the member must be terminated here as well.

3.5.15 Member Dental History

File Naming Convention : <ClientName>_ika_member_dental_history_extract.csv

This table contains the secondary plan coverage

The following rules apply for the records in this table:

1. Primary Key: SUBSCRIBER_ID, MEMBER_SUFFIX, EFFECTIVE_DATE

2. Duplicate check is based on records with same Subscriber ID, Member Suffix, Effective

Date

3. All Subscriber ID and member suffix should also be present in the Member table

4. Member Suffix should be two characters - e.g. ‘01’

3.6 Authorizations Module

3.6.1 Authorization Table

File Naming Convention : <ClientName>_ika_authorization_extract.csv

This table contains the Authorization Header information.

The following rules apply for the records in this table:

1. Primary Key: AUTH_NUMBER

2. Duplicate check is based on records with same Auth Number

3. For all Service Types, Receive Date, Begin Date, Member ID, Ordering Facility ID, Service

Location, Match Method and Primary Diagnosis are required

4. Subscriber ID must be present in Member table – if not present, invalid

5. Member must have coverage as of Begin Date

6. Member must have coverage as of Admit Date

7. Member must have valid coverage – i.e., Member’s plan must not have expired

8. Ordering Professional if present must exist in Provider table (type = ‘1’)

9. If ordering professional is not PCP, Authorized Prof ID is required

10. Authorized Prof ID must be present in Provider table (Type = ‘1’) - if not present, invalid

11. Ordering Prof ID must be present in Provider table (Type = ‘1’) - if not present, invalid

12. Authorized Prov ID must be present in Provider table (Type = ‘0’) - if not present, invalid

13. For Service Type:

 If INPATIENT (‘I’)

□ Requires Authorized Facility ID

□ Requires Admit Date

 If OUTPATIENT (‘O’)

□ Requires Authorized Facility ID

□ Requires an entry in Authorization Services table

□ If any Service Code exists, Units are required in Authorization Services table

□ Match Method cannot be ‘0’ (default)

 If OTHER or OTHER (PROVIDER) (‘R’ or ‘P’)

□ Requires Authorized Prov ID

□ Requires an entry in Authorization Services table

□ If Service Code exists, Units are required

 If OBSERVATION (‘C’)

□ Requires Authorized Facility ID

14. For Admit Date:

 If Admit Date exists, then Begin Date and End Date are required

 Must be later than the Member’s ‘Coverage from’ Date

 Must not be later than the Member’s ‘Coverage to’ Date

15. For Discharge Date:

 If Discharge Date exists, Admit Date is required

 If Discharge Date exists, it must be later than Admit Date

16. Length of Stay must be difference between Discharge Date and Admit Date in days

17. If Authorization Type = ‘DME’, then Service Modifier must be present in Authorization table, and appended to Service Code in Authorization Services table

18. If Match Method = ‘2’ (Match Service Code and Units) and any Service Code is present, then

Units is required in Authorization Services table

19. For AUTH_STATUS:

 If present, it should be in CODES_SYSTEM, CODE_TYPE = ‘02’ (Authorization Status)

 If present, STATUS_REASON is required

 If present, DECISION_DATE is required

20. STATUS_REASON should correspond to CODES_USER_DEFINED, CODE_TYPE = ‘63’

(Authorization Reason Code) and CODE_PARAM = AUTH_STATUS code

Note: “No*” in the following table indicates that the field is conditionally required per the above validations.

3.6.2 Authorization Services Table

File Naming Convention : <ClientName>_ika_authorization_services_extract.csv

This table contains the Authorization line level information.

The following rules apply for the records in this table:

1. Primary Key: AUTH_NUMBER, LINE_NUMBER

2. If Authorization Type = ‘DME’ in Authorization table, then Service Modifier must be present in

Authorization table, and appended to Service Code in Authorization Services table

3. If Match Method = ‘2’ (Match Service Code and Units) and any Service Code is present in

Authorization table, then Units is required in Authorization Services table

4. If AUTH_CLASS exists, it must be present in RANGE_LIST in AUTHORIZATION_CLASS table

5. UNIT TYPE can be ‘01’ – per Visit,

 ‘02’ – per Min

 ‘03’ – per Day

 ‘04’ – per Month

 ‘05’ – each

 ‘06’ – by line item

 ‘07’ – by visit

3.6.3 Authorization Comments Table

File Naming Convention : <ClientName>_ika_authorization_comments_extract.csv

This table contains the Authorization Comments.

The following rules apply for the records in this table:

1. Primary Key: AUTH_NUMBER, COMMENTS_SEQUENCE

2. Duplicate check is based on Records with same Auth Number, Comments Sequence

3.7 Claims Module

3.7.1 Claim Table

File Naming Convention : <ClientName>_ika_claim_extract.csv

This table contains information related to the Claim, Claim Header (common to HCFA/UB claims)

information. It should contain one row per claim.

The following rules apply for the records in this table:

1. Primary Key: CLAIM_ID

2. Duplicate check is based on records with same Claim ID

3. Form Type should be proper – ‘1’ for Prof (HCFA Claim), 2 for ‘Inst’ (UB Claim)

4. If a HCFA Claim, there should be a record in Prof Claim table

5. If a UB Claim, there should be a record in Inst Claim table

6. If a HCFA Claim, there should be a record in Prof Claim Detail table

7. If a UB Claim, there should be a record in Inst Claim Detail table

8. All Claims should have an entry in Claim Detail table

9. Data in Pay To Provider ID, Provider ID columns should correspond to entries in Provider table

10. Data in Subscriber ID should correspond to entries in Member table

11. For all Claims in PAID Status (‘6’), there SHOULD be a record in Payment Detail table

12. For each Claim to be a valid Claim, it should follow either of these structures:

 Claim

 Claim Detail

 Inst Claim

 Inst Claim Detail

or

 Claim

 Claim Detail

 Prof Claim

 Prof Claim Detail

i.e., A claim should have an entry in four tables to be a valid claim; if it is missing any of these, then all corresponding entries in related tables will NOT be loaded

13. Correction Level in CLAIM / CLAIM DETAIL, (INST CLAIM / INST CLAIM DETAIL or PROF CLAIM / PROF CLAIM DETAIL) and PAYMENT DETAIL should match

14. PROVIDER_ID should only be populated with a PROVIDER_ID that has a PROVIDER_TYPE = ‘1’

for Professional (Individual).

15. PROVIDER_ID should only be populated on HCFA claims which are FORM_TYPE = ‘1’ for

Professional (HCFA) Claim. This represents the individual that rendered services.

16. PROVIDER_ID should not be populated on UB claims which are FORM_TYPE = ‘ 2’ for Institutional (UB) Claim. If a Professional exists on a UB claim it should be populated in the ATTENDING_PHYSICIAN_ID in the INST_CLAIM table.

3..7.2 Claim Detail Table

File Naming Convention : <ClientName>_ika_claim_detail_extract.csv

This table contains the Line level payment information. This table should contain one or more line items. Every claim in Claim sheet should have at least one line.

The following rules apply for the records in this table:

1. Primary Key: CLAIM_ID, LINE_NUMBER

2. Duplicate check is based on records with same Claim ID and Line Number

3. Each Claim ID should have record in Claim table

3.7.3 Claim Notes Table

File Naming Convention : <ClientName>_ika_claim_notes_extract.csv

This table contains the Claim notes. It may contain zero or more lines per claim.

The following rules apply for the records in this table:

1. Primary Key: CLAIM_ID, SEQ_NUM

2. Duplicate check is based on records with same Claim ID and Sequence Number

3. Each Claim ID should have corresponding record in Claim table

3.7.4 Prof Claim Table

File Naming Convention : <ClientName>_ika_prof_claim_extract.csv

This table contains the HCFA Claim Header information. This table should contain exactly one record for each record in Claim table if that claim is a HCFA claim.

The following rules apply for the records in this table:

1. Primary Key: CLAIM_ID

2. Duplicate check is based on records with same Claim ID

3. Each Claim ID should have record in Claim table

4. For each Claim table record of professional type (‘1’), there should be record in Prof Claim table (i.e., HCFA Claim)

5. Each entry should have corresponding record in Prof Claim Detail table

3.7.5 Prof Claim Detail Table

File Naming Convention : <ClientName>_ika_prof_claim_detail_extract.csv

This table contains the HCFA Claim line level information. This table should contain at least one record for each record in Prof Claim table.

The following rules apply for the records in this table:

1. Primary Key: CLAIM_ID, LINE_NUMBER

2. Duplicate check is based on records with same Claim ID and Line number

3. Each Claim ID should have record in Claim table

4. Each Claim ID should have record in Prof Claim table

3.7.6 Inst Claim Table

File Naming Convention : <ClientName>_ika_inst_claim_extract.csv

This table contains the UB Claim Header information. This table should contain exactly one record for each record in Claim table if that claim is a UB claim.

The following rules apply for the records in this table:

1. Primary Key: CLAIM_ID

2. Duplicate check is based on records with same Claim ID

3. Each Claim ID should have record in Claim table

4. For each Claim table record of institutional type (‘0’), there should be record in Inst Claim table

(i.e., UB Claim)

5. Each entry should have corresponding record in Inst Claim Detail table

3.7.7 Inst Claim Detail Table

File Naming Convention : <ClientName>_ika_inst_claim_detail_extract.csv

This table contains the UB Claim line level information. This table should contain at least one record for each record in Inst Claim table.

The following rules apply for the records in this table:

1. Primary Key: CLAIM_ID, LINE_NUMBER

2. Duplicate check is based on records with same Claim ID and Line number

3. Each Claim ID should have record in Claim table

4. Each Claim ID should have record in Inst Claim table

3.7.8 Payment Table

File Naming Convention : <ClientName>_ika_payment_extract.csv

This table keeps track of all the payment posted for the invoice.

The following rules apply for the records in this table:

1. Primary Key: PAYMENT_SEQ

2. Duplicate check is based on records with same Check Number and Payer ID

3. Payment Seq should be unique

4. Each Check Number should have record in Payment Detail table

5. For same Check_Number, sum of all corresponding Check_Contribution_Amount in

PAYMENT_DETAIL table should equal Check_Amount in PAYMENT table

3.7.9 Payment Detail Table

File Naming Convention : <ClientName>_ika_payment_detail_extract.csv

This table contains the Claim payment information.

The following rules apply for the records in this table:

1. Primary Key: CLAIM_ID, CORRECTION_LEVEL

2. Each Claim ID should have record in CLAIM table

3. For every Claim in PAYMENT table, there SHOULD be record in PAYMENT_DETAIL table

4. Each Check Number should have record in PAYMENT table

5. For same Check_Number, sum of all corresponding Check_Contribution_Amount in

PAYMENT_DETAIL table should equal Check_Amount in PAYMENT table

6. Each Subscriber ID - Member Suffix pair should have record in MEMBER table

7. Payee ID can be either valid Subscriber (i.e., entry in MEMBER table) or valid Provider (i.e., entry in PROVIDER table)

3.7.11 Claim COB Table

File Naming Convention : <ClientName>_ika_claim_cob_extract.csv

The following rules apply for the records in this table:

1. Primary Key: CLAIM_ID

2. Duplicate check is based on records with same Claim ID

3. Each Claim ID should have corresponding record in Claim table

4. If amount present in PAID_BY_PRIMARY_PAYER field within CLAIM table, this indicates existence of COB, and record should exist in CLAIM_COB table

5. All fields with “PRI” (primary) prefix are input to ikaSystems; all fields with “SEC” (secondary)

prefix are calculated by ikaSystems’ Adjudication system, based on Member benefits

6. For every Claim ID in CLAIM_COB table, there SHOULD be record in CLAIM_DETAIL_COB table

7. For same CLAIM_ID, sum of all amount fields in CLAIM_DETAIL_COB table should equal corresponding amounts in CLAIM_COB table – e.g., sum of all PRI_PAID amounts in CLAIM_DETAIL_COB should equal PRI_PAID amount in CLAIM_COB

3.7.12 Claim Detail COB Table

File Naming Convention: <ClientName>_ika_claim_detail_cob_extract.csv

The following rules apply for the records in this table:

1. Primary Key: CLAIM_ID, LINE_NUMBER

2. Duplicate check is based on records with same Claim ID, LINE_NUMBER

3. Each Claim ID should have corresponding record in Claim table

4. All fields with “PRI” (primary) prefix are input to ikaSystems; all fields with “SEC” (secondary)

prefix are calculated by ikaSystems’ Adjudication system, based on Member benefits

5. For every Claim ID in CLAIM_COB table, there SHOULD be record in CLAIM_DETAIL_COB table

6. For same CLAIM_ID, sum of all amount fields in CLAIM_DETAIL_COB table should equal corresponding amounts in CLAIM_COB table – e.g., sum of all PRI_PAID amounts in CLAIM_DETAIL_COB should equal PRI_PAID amount in CLAIM_COB

3.8 Enterprise Service Module

3.8.1 Cctr Contacts Table

File Naming Convention : <ClientName>_ika_cctr_contacts_extract.csv

The Call Center Contacts are recorded here. A Contact is each call made by a customer. This table contains the contact time details and customer details of every call received by the Call Center.

The following rules apply for the records in this table:

1. Primary Key: CONTACT_ID

2. Duplicate check is based on Records with same Contact ID

3.8.2 Cctr Inquiries Table

File Naming Convention : <ClientName>_ika_cctr_inquiries_extract.csv

The Call Center Inquiries are stored here. Inquiries are cases made by the customer during a contact. One or more inquiries can be recorded during a given contact. This table contains all the details regarding the case, such as Category/Sub-Category to which it belongs, current status, priority, case details, etc.

The following rules apply for the records in this table:

1. Primary Key: INQUIRY_ID, CONTACT_ID

2. Duplicate check is based on Records with same Inquiry ID and Contact ID

3. Each Contact id should have a record in the CCTR Contacts table

3.8.3 Cctr Actions Table

File Naming Convention : <ClientName>_ika_cctr_actions_extract.csv

The Call Center Actions details are stored here. Actions are the various steps taken to close an Inquiry. An Inquiry can have many actions. This table stores all the action-related information, such as comments, status, who took the action, etc.

The following rules apply for the records in this table:

1. Primary Key: ACTION_ID

2. Duplicate check is based on records with same Action ID

3. Each Inquiry ID, Contact ID pair should have a record in the CCTR Inquiry table

3.9 Billing Module

3.9.1 Instructions for Initial Configuration

BNC_GROUPS and BNC_DIVISIONS Configuration Type

If the Rates are configured at the Group Level, the Config Type in the Group Table or the Division

Table (one of the Two) should be populated as described below.

3.9.2 BNC Billing Information Table

File Naming Convention : <ClientName>_ika_bnc_billing_info_extract.csv

This table contains billing address data related to Groups and Division only. The following rules apply for the records in this table:

 Primary Key: ID, ID_TYPE, EFFECTIVE_DATE

Note If BNC Billing Information is specific to Division, then concatenate Division ID with Group ID - e.g., If Division: ID = GROUP_ID||DIVISION_ID ; Same for Departments and Sections

3.9.3 BNC Business Address Table

File Naming Convention : <ClientName>_ika_bnc_business_address_extract.csv

This table will have the business address details of an Agency or Agent.

The following rules apply for the records in this table:

 Primary Key: ID, ID_TYPE, ADDRESS_SUFFIX

3.9.4 BNC Premium Rates Table

File Naming Convention : <ClientName>_ika_bnc_premium_rates_extract.csv

This table contains the BNC Medicare Premium Rates only. This table should not be used for

Commercial Rates.

The following rules apply for the records in this table:

1. Primary Key: PMR_RATE_ID

2. Duplicate check is based on records with same BENEFIT_PLAN_ID, EFFECTIVE_DATE, GROUP_ID, DIVISION_ID

3. All RIDERS should also be present in BNC_RIDERS table

4. All BENEFIT_PLAN_ID should also be present in BNC_BENEFIT_PLANS table

3.9.5 BNC Risk Rate Table

File Naming Convention : <ClientName>_ika_bnc_risk_rates_extract.csv

This table contains the Risk Rate information for Commercial Groups. The APPLY_GROUP_PREM (‘P’ or ‘G’ or ‘D’) and the Invoice_Level (‘G’ or ’M’ or D’) in the BNC Groups Table should be populated before the BNC Risk Rates are populated.

The following rules apply for the records in this table:

1. Primary Key: RATE_ID

2. Duplicate check is based on records with same BENEFIT_PLAN_ID, EFFECTIVE_DATE, GROUP_ID, DIVISION_ID , COVERAGE_TYPE, TIER, DEP_RELATION, AGE_FROM, GENDER, RISK_FLAG

3. All BENEFIT_PLAN_ID should also be present in BENEFIT_PLANS table

4. All GROUP_ID should also be present in Groups table

5. All Division ID should also be present in Division table

6. All RELATIONSHIP and RISK CODES should also be configured in system

7. ‘-’ and ‘N’ would be considered as Not Applicable for the following fields within the Tables.

Coverage_Type

Tier

Group ID and Division ID Gender

Rider ID

8. Clients will have to send as ‘-’ instead of NULL/blank if there is no value for these fields.

9. If not applicable ‘Age from’, ‘Age to’ should be defaulted ‘-1’

4121 Cox Road, Suite 200 | Glen Allen, VA 23060 | 804 977 0500

advantasure.com