OW Synchronization with CRM

Toronto Employment and Social Services (TESS) is providing a web service that returns client profiles. This approach eliminates the need for HSIO to temporarily save any client information.

TESS has documented the service here and the OpenAPI definition of the service is provided here.

OW Client Service to CRM Platform Events Mapping

Adult Mapping

The source is the JSON from the GetClientProfile method in the TESS-HSI service. The destination is the JSON representation of the Salesforce Account sobject.

Source	Destination	Comment
GivenName	FirstName
LastName	LastName
MiddleName	MiddleName
	Suffix	Not needed
ReferenceNum	Ontario_Works_Id__c
DoB	personBirthDate	there may be the need to transform the date: the input format is yyyymmdd
	personEmail	No email address
telephoneNumber	Phone
	Secondary_Phone__c	Not provided by OW
	PersonMobilePhone	Not provided by OW
streetNumber\|\| ' ' \|\| streetName	street
city	city
province	state_code
postalCode	postal_code
'CA'	country_code
'Address'	geocodeAccuracy
businessUnitId	household_id__c
StatusCode	case_status__c
DomesticViolenceIndicator

Salesforce Sync JSON Data Mapping:

Salesforce Fields	OW API Fields	OW API Example	Comment
JSON_Message__c
first_name__c	GivenName	Bibiana
last_name__c	LastName	MKMOPPJXK
	~~MiddleName~~
case_status__c	~~StatusCode~~	CS10001
SAMS_Status__c	Status	Completed
	~~Active~~	1
Name	firstname + ' ' +MiddleName + ' '+ lastname
	~~GenderCode~~	F
gender__c	Gender	Female
salutation__c	~~Title~~	Mrs.
date_of_birth__c (yyyy-mm-dd)	DoB	19820428
email__c	(null)
phone	Telephone	555-5555555
Secondary_Phone__c	(null)
Other_phone__c	(null)
Mobile_phone__c	(null)
Is_Aboriginal__c	(null)
BillingStreet(Primary)	UnitNumber + '-' +streetNumber + ' ' + streetName	12-1254 Street
BillingCity	city	SCARBOROUGH
BillingState	province	SCARBOROUGH
BillingPostalCode	postalCode	M5N 5E3
BillingCountry	"CA"
ShippingStreet(Secondary)	(null)
ShippingCity	(null)
ShippingState	(null)
ShippingPostalcode	(null)
ShippingCountry	(null)
RecordTypeId	0121h0000004kgaAAA (Parent) (age >=18 based on birthday) 012W000000016sEIAQ (Child) (age <18 based on birthday)
Ontario_Works_Id__c	ReferenceNum	15146436109 SAMS client reference number Purpose: Call client search Call client ID verification
Children_Services_Id__c	(null)
CSIS_household_Id__c	(null)
AccountSource	"Ontario Works"
Children_Services_Household_Id__c	(null)
RGI_Houshold_Id__c	(null)
OW_Household_id__c	ICCaseID	1212724857222 BU ID Purpose: To link together members of an OW household
	~~IndigenousInd~~	0
	~~IndigenousGroup~~
Is_sensitive_client__c(Boolean/Integer)	DomesticViolenceIndicator	0
	~~DomesticViolenceStart~~
	~~DomesticViolenceEnd~~
OW_start_date__c	StatusChangeDate	20190826
	~~RecordChangeDate~~	20191002
WP_Ind__c	WPInd	1 or null	Welcome Policy Indicator / Consent
WP_Start_Date__c	WPStartDate	20210608	Welcome Start Date format:YYYYMMDD
WP_End_Date__c	WPEndDate	20220609	Welcome End Date format:YYYYMMDD
Object_Name__c	"Account"

JSON example:

{
"allOrNone" : false,
"compositeRequest" : [
{
     "method" : "POST",
      "url" : "/services/data/v45.0/sobjects/Legacy_System_Record__e",
      "referenceId" : "101025047",
       "body" : {
              "JSON_Message__c" : "{
                           "Type":"Account",
                           "ReferenceId":"101025047",
                           "_type":"ClientProfile",
                          "Ontario_Works_Id__c":"101025047",
                          "case_status__c":"CS1",
                            "SAMS_Status__c":"Active",
                            "first_name__c":"",
                            "last_name__c":"",
                            "Name":"",
                            "gender__c":"Female",
                            "date_of_birth__c":"1980-",
                            "Phone":"6476785523",
                             "BillingStreet":"-",
                             "BillingCity":"SCARBOROUGH",
                             "BillingState":"Ontario",
                             "BillingPostalCode":"M1E ",
                             "OW_start_date__c":"2021-",
                             "BillingCountry":"CA",
                             "AccountSource":"Ontario Works",
                             "RecordTypeId":"0126A000000yrPjQAI",
                             "OW_Household_id__c":"103474441214",
                             "Is_sensitive_client__c":0"
                }",
              "Object_Name__c" : "Account"
       }
}...
]
}

Process Log

The process log table is used to manage the date ranges when calling the service.

Process Log for OW Client

-- CRM Synch OW Client
Update PROCESS_LOG 
   set PRL_LAST_SUCCESS_RUN_START_TS = to_timestamp('2018-01-01','YYYY-MM-DD')
 where prl_process_log_type_code = 'SFSOWC';