Country Code Mapping

The country codes mapping tables facilitate reporting and analysis by country across disparate collections in the Warehouse. Data Administration staff administer a process in which country code values from various administrative source systems (e.g., ATLAS, OASIS, PennERA, PennWorks/Payroll, SRS, and others) are regularly compared, reviewed, and mapped to values in a master table. The results of this process are exported to the CCMAINT_ADMIN schema in the Data Warehouse on weekday nights, and stored in the following tables, which are available to all users of the Warehouse.

Refer to the Country Code Mapping collection diagram to see all sources, extracts, mapping tables, and the COUNTRY_CODES_MASTER table.

COUNTRY_CODES_MASTER

Contains one row per country, identified by COUNTRY_CODES_MASTER_ID. In addition, COUNTRY_CODES_MASTER stores the country name and region used for reporting and analysis across data collections in cases where name formats may vary. COUNTRY_CODES_MASTER also includes related ISO (International Organization for Standardization) codes.

Data element

Definition

ACTIVE_FLAG

Indexed - no
Format - char(1)
May be null - no

Indicates whether the COUNTRY_CODES_MASTER record is active.

COUNTRY_CODES_MASTER_ID

Indexed - yes (primary key)
Format - number
May be null? no

Uniquely identifies a country record in the COUNTRY_CODES_MASTER table.

The associated 2-character ISO (International Organization for Standardization) identifier for the country.

ISO_ALPHA3

Indexed - no
Format - char(3)
May be null? yes

The associated 3-character ISO (International Organization for Standardization) identifier for the country.

ISO_NUMERIC

Indexed - no
Format - char(3)
May be null? yes

The associated numeric ISO (International Organization for Standardization) identifier for the country.

LAST_UPDATED_BY

Indexed - no
Format - varchar2 (20)
May be null? no

The PennKey of the user who last updated the COUNTRY_CODES_MASTER record.

LAST_UPDATE_DATE

Indexed - no
Format - date (7)
May be null? no

The date on which the COUNTRY_CODES_MASTER record was last updated.

LAST_UPDATE_TIMESTAMP

Indexed - no
Format - timestamp (6)
May be null? no

The timestamp on which the COUNTRY_CODES_MASTER record was last updated.

REGION_GROUP

Indexed - no
Format - varchar2 (200)
May be null? yes

The geographical region associated with the country.

RELATED COUNTRY CODES TABLES

In the course of the mapping process, records for countries in various administrative system lookup tables or external references are related to the COUNTRY_CODES_MASTER table by the COUNTRY_CODES_MASTER_ID. The mapped relationships are then exported to the Warehouse and stored in "LOCAL" tables which associate the COUNTRY_CODES_MASTER_ID with the country code records particular to the various source systems. The LOCAL tables are comprised as follows.