Terradotta Travel Registry

Purpose

The terradotta.TravelRegistry view provides user information specifically formatted for Terradotta's travel registry integration. This view maps user data from the base user data view to the specific fields required by Terradotta's travel registry system, including personal information, academic details, and custom fields.

View Structure

Field Name	Data Type	Description	Source Table	Source Field	Notes
UUUID	VARCHAR	User identifier	BaseUserData	UUUID
LAST_NAME	VARCHAR	Last name	BaseUserData	LAST_NAME
FIRST_NAME	VARCHAR	First name	BaseUserData	FIRST_NAME
MIDDLE_NAME	VARCHAR	Middle name	BaseUserData	MIDDLE_NAME
EMAIL	VARCHAR	Email address	BaseUserData	EMAIL
DOB	VARCHAR	Date of birth	BaseUserData	DOB	Formatted as MM/dd/yyyy
GENDER	VARCHAR	Gender	BaseUserData	GENDER
HR_FLAG	VARCHAR	Employee flag	BaseUserData	RECORD_TYPE	'Y' for employees
PREFERRED_NAME	VARCHAR	Preferred name	BaseUserData	PREFERRED_NAME
ETHNICITY	VARCHAR	Ethnicity	N/A	N/A	Not mapped
ROLE	VARCHAR	Role	N/A	N/A	Not mapped
TITLE	VARCHAR	Title	BaseUserData	TITLE
CLASS	VARCHAR	Class level	BaseUserData	CLASS_LEVEL
COLLEGE	VARCHAR	College	BaseUserData	SCHOOL_ENROLLMENT1
DEPARTMENT	VARCHAR	Department	BaseUserData	DEPARTMENT
MAJOR	VARCHAR	Major	BaseUserData	MAJOR1_DESC
CAMPUS	VARCHAR	Campus	N/A	N/A	Not mapped
EMERG1_ADDR_PHONE	VARCHAR	Emergency phone	N/A	N/A	Not mapped
EMERG1_ADDR_NAME	VARCHAR	Emergency name	N/A	N/A	Not mapped
EMERG1_ADDR_REL	VARCHAR	Emergency relation	N/A	N/A	Not mapped
CUSTOM1	VARCHAR	ID Number	BaseUserData	ID
CUSTOM2	VARCHAR	Country of Citizenship	BaseUserData	COUNTRY_CITIZENSHIP
CUSTOM3	VARCHAR	Home Address	BaseUserData	PERM_ADDR_LINE1
CUSTOM4	VARCHAR	Phone Number	BaseUserData	STUDENT_MOBILE
CUSTOM5	VARCHAR	Personal Email	N/A	N/A	Not mapped
CUSTOM6-CUSTOM25	VARCHAR	Custom fields	N/A	N/A	Not mapped

Source System Mapping

Primary Source:

BaseUserData View
- Core user information
- Personal details
- Academic information
- Contact data

Data Transformation Rules

Date Formatting

DOB = CASE
  WHEN B.DOB IS NULL THEN ''
  ELSE FORMAT(CAST(B.DOB AS DATE), 'MM/dd/yyyy')
END

Employee Flag

HR_FLAG = CASE
  WHEN B.RECORD_TYPE = 'EMPLOYEE' THEN 'Y'
  ELSE 'N'
END

Null Handling

GENDER = ISNULL(B.GENDER, '')
PREFERRED_NAME = ISNULL(B.PREFERRED_NAME, '')

Data Quality Rules

Required Fields
- UUUID
- LAST_NAME
- FIRST_NAME
- EMAIL
- DOB
Validation Rules
- UUUID must not be null
- Email must be valid
- DOB must be in correct format
- Required fields must be populated
Business Rules
- Employee flag correctly set
- Academic information for students
- Department information for employees
- Contact information complete

Usage Notes

Integration Considerations
- Used for travel registry synchronization
- Maps to Terradotta's specific field requirements
- Includes header row for file format
Performance Considerations
- Simple mapping from base view
- Minimal transformations
- Efficient null handling
Maintenance
- Monitor field mappings
- Update as Terradotta requirements change
- Validate data format compliance

Terradotta.TravelRegistry View Documentation