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:
{ "method" : "POST", "url" : "/services/data/v45.0/sobjects/Legacy_System_Record__e", |
|---|
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:
{ "method" : "POST", "url" : "/services/data/v45.0/sobjects/Legacy_System_Record__e", |
|---|
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 |
JSON example:
{ "method" : "POST", "url" : "/services/data/v45.0/sobjects/Legacy_System_Record__e", |
|---|
Update the Process Log
Once the information is successfully loaded update the log with the most recent version using an update similar to this.