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 |
Adult Mapping
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 |
Salesforce "triggers" will encrypt the SIN. |
rad_ow_member_id | Ontario_Works_Id__c |
sams reference id | |
to_char(rad_birthdate,'yyyy-mm-dd') | personBirthDate |
rad_email_address | personEmail |
(select rpc_area_code || rpc_local_number | Phone |
(select rpc_area_code || rpc_local_number | Secondary_Phone__c | This is the work phone number |
(select rpc_area_code || rpc_local_number | PersonMobilePhone |
| Children_Services_Household_Id__c | household id (HouseholdFileRefNumber) | |
| 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 |
| csis_action_by__c | EMP_NAME |
JSON example:
{ |
|---|
Child Mapping
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 |
sams reference id | ||
| Children_Services_Household_Id__c | household id (HouseholdFileRefNumber) | |
| chl_reference_child_id | Children_Services_Id__c |
| 012W000000016sEIAQ | RecordTypeId | This is the ID for the Child Account Record Type |
| csis_action_by__c | EMP_NAME |
JSON example:
{ |
|---|
CCFS Household Mapping
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 |
| Service_ |
| Status__c | The code is one of:
|
| 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 |
| 6 months 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 |
...
| household_income | Household_income__c | household income from table: financial_assessment |
| funding_stream | Funding_Stream__c | funding stream from table: family_funding |
For household income and funding stream logic, please refer the confluence page provided by Ramzi: /wiki/spaces/CSI/pages/2860844000
JSON example:
{ |
|---|
JSON field mapping with tables:
| Json Fields | Parent record (Table.column) | Child record (Table.column) | Household record (Table.column) |
| Children_Services_Id__c | RESPONSIBLE_ADULT.RAD_REFERENCE_APPLICANT_ID | CHILD.CHL_REFERENCE_CHILD_ID | |
| Ontario_Works_Id__c | RESPONSIBLE_ADULT.RAD_OW_MEMBER_ID | CHILD.CHL_OW_MEMBER_ID | |
| Children_Services_Household_Id__c | HOUSEHOLD.HHO_FILE_REFERENCE_NUMBER | HOUSEHOLD.HHO_FILE_REFERENCE_NUMBER | |
| household_id__c | Appointment.APP_FAMILY_ID |
Parent-Child-Household relationship (not for active Household):
| RESPONSIBLE_ADULT | HOUSEHOLD_RESP_ADULT_MEMBER | HOUSEHOLD | HOUSEHOLD_CHILD_MEMBER | CHILD |
| RAD_RESPONSIBLE_ADULT_ID | HHR_RESPONSIBLE_ADULT_ID | |||
| HHC_CHILD_ID | CHL_CHILD_ID | |||
| HHR_HOUSEHOLD_ID | HHO_HOUSEHOLD_ID | HHC_HOUSEHOLD_ID |
Update the Process Log
Once the information is successfully loaded update the log with the most recent version using an update similar to this.