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	~~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	sams reference id
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
	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:

{
"allOrNone" : false,
"compositeRequest" : [
{
     "method" : "POST",
      "url" : "/services/data/v45.0/sobjects/Legacy_System_Record__e",
      "referenceId" : "A9594843",
       "body" : {
              "JSON_Message__c" : "{
                         "Name":"*",
                      "Phone":"*",
                       "BillingStreet":"* RD",
                         "BillingCity":"TORONTO",
                         "BillingState":"ON",
                         "BillingPostalCode":"M4W ???",
                       "BillingCountry":"CA",
                     "BillingGeocodeAccuracy":"Address",
                     "RecordTypeId":"0126A000000yrPjQAI",
                     "AccountSource":"CSIS2",
                     "EmpUserId":"*",
                         "first_name__c":"*",
                         "last_name__c":"*",
                         "Ontario_Works_Id__c":"224421743",
                         "date_of_birth__c":"1976-01-01",
                         "email__c":"*",
                        "Mobile_phone__c":"*",
                        "Is_Aboriginal__c":false,
                        "Children_Services_Household_Id__c":"1139333",
                        "Children_Services_Id__c":"A9594843",
                        "csis_action_by__c":"MICHELE BUTTON"
                }",
              "Object_Name__c" : "Account"
       }
} ...
]
}

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:

{
"allOrNone" : false,
"compositeRequest" : [
{
     "method" : "POST",
      "url" : "/services/data/v45.0/sobjects/Legacy_System_Record__e",
      "referenceId" : "C9677210",
       "body" : {
              "JSON_Message__c" : "{
                        "Name":"*",
                        "RecordTypeId":"0123s000000aJkPAAU",
"AccountSource":"CSIS2",
                        "EmpUserId":"*",
                        "first_name__c":"*",
                        "last_name__c":"*",
                        "Ontario_Works_Id__c":"305366401",
                        "date_of_birth__c":"2018-",
                        "Is_Aboriginal__c":false,
                        "Children_Services_Household_Id__c":"1180236",
                        "Children_Services_Id__c":"C9677210",
                        "csis_action_by__c":"*"                }",
              "Object_Name__c" : "Account"
       }
} ...
]
}

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: 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 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:

{
"allOrNone" : false,
"compositeRequest" : [
{
     "method" : "POST",
      "url" : "/services/data/v45.0/sobjects/Legacy_System_Record__e",
      "referenceId" : "1068320",
       "body" : {
              "JSON_Message__c" : "{
                      "ReferenceId":"1068320",
                      "household_id__c":"1068320",
                      "Service_Status__c":"INACTIVE",
                      "Start_Date__c":"2018-",
                      "End_Date__c":"2021-",
                      "Household_income__c":"0",
                      "Funding_Stream__c":"REG"
             }",
             "Object_Name__c" : "Household"
       }
} ...
]
}

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.

Browser not supported