Mapping

The source is from the attached SQL queries one for Adult and one for Child. The destination Java Object classes are in the attached compress file.

Database

Environment	Connecion String
Training (scrambled personal data)	jdbc:oracle:thin:@//dbtrain.csd.toronto.ca:1521/CSISTR

The source is the RESPONSIBLE_ADULT table. The destination is the JSON representation of the Salesforce Account sobject.

Source	Destination	Comment
trim(rad_given_name)	FirstName
trim (rad_surname)	LastName
trim(rad_middle_name)	MiddleName
trim(rad_suffix_name)	Suffix
rad_social_insurance_number	Social_Insurance_Number__c	~~There's some magic that needs to be done to create the encrypted user ID~~ Salesforce "triggers" will encrypt the SIN.
rad_ow_member_id	Ontario_Works_Id__c
to_char(rad_birthdate,'yyyy-mm-dd')	personBirthDate
rad_email_address	personEmail
(select rpc_area_code \|\| rpc_local_number from resp_adult_phone_cntct where rpc_responsible_adult_id = rad_responsible_adult_id and rpc_phone_type_code = 'HOME')	Phone
(select rpc_area_code \|\| rpc_local_number from resp_adult_phone_cntct where rpc_responsible_adult_id = rad_responsible_adult_id and rpc_phone_type_code = 'WORK')	Secondary_Phone__c	This is the work phone number
(select rpc_area_code \|\| rpc_local_number from resp_adult_phone_cntct where rpc_responsible_adult_id = rad_responsible_adult_id and rpc_phone_type_code = 'MOBILE')	PersonMobilePhone
cast (rad_reference_applicant_id,varchar2(20))	Children_Services_Id__c
pad_street_nbr \|\| ' ' \|\| pad_street_name	street
pad_city	city
pad_province_code	state_code
pad_postal_code	postal_code
pad_country_code	country_code
'Address'	geocodeAccuracy
012W000000016s9IAA	RecordTypeId	This is the ID for the Adult Account Record Type

The source is the CHILD table. The destination is the JSON representation of the Salesforce Account sobject.

Source	Destination	Comment
trim(chl_given_name)	FirstName
trim (chl_surname)	LastName
trim(chl_middle_name)	MiddleName
trim(chl_suffix_name)	Suffix
to_char(chl_birthdate,'yyyy-mm-dd')	personBirthDate
chl_social_insurance_number	Social_Insurance_Number__c
chl_ow_member_id	Ontario_Works_Id__c
chl_reference_child_id	Children_Services_Id__c
012W000000016sEIAQ	RecordTypeId	This is the ID for the Child Account Record Type

The source is a number of tables identifying the status of the Child Care Fee Subsidy case. See this sample SQL.

Source	Destination	Comment
household_id	household_id__c	The TCS Household ID
household_status	case_status__c	The code is one of: Waiting Allocated Enrolled Inactive
start_date	case_start__c	The case start date
inactive_date	case_inactive_date__c	If the household status is inactive, this is the start of the inactive status, after 60 days this case expires
last_update_date_time	Not uploaded to Salesforce	Used to determine the maximum date time is used for this run is used as the start of the next run Must be saved in the Process Log

Once the information is successfully loaded update the log with the most recent version using an update similar to this.