High Level Design

Synchronizing the RGI client information with the CRM follows this approach.

Error rendering macro 'excerpt-include' : No link could be created for 'HSIF:RGI Data to Salesforce Detailed Solution Proposal'.

RGI Data File

SSHA provides a file with all the eligible clients on listed on the file. To determine any changes the current file must be compared to the previous day's file. Any additional records should be added to the

Item	Type	Source	Destination	Comment
Applicant Code	String	RC_Applicant_Code	RGI_Applicant_Code__c	New household id for a family after Yardi migration
Household ID	number	Household Ref#	RGI_Houshold_Id__c	For the Household ID for the member
System ID	number	Member ID	SSHA_id__c
Applicant ID	number	Member_ref	RGI_Member_Id__c
Given Name	String 50	First Name	first_name__c
Last Name	String 50	Last Name	last_name__c
~~Gender~~	~~String 6~~	~~Gender~~	~~gender__c~~	M, F, U
Date of Birth	Date	Date Of Birth	date_of_birth__c
Application ID	number	Application ID		Not needed
Application Status	String 6	Application Current Status Code	rgi_status__c	Legacy status
Email Address	String 255	Member eMail address	email__c
Telephone Contact (Home)	String 20	Home Phone	phone
		Home Phone Ext	Phone_Ext__c
Telephone Contact (Mobile)	String 20	Mobile Phone	Mobile_phone__c
		Mobile Phone Ext	Mobile_Phone_Ext__c
Telephone Contact (Work)	String 20	Bus Phone	Other_phone__c
		Bus Phone Ext	Other_Phone_Ext__c
Postal Address
Street Number	String 15	Member Mailing Address Street Number	BillingStreet	Combine Unit, street number and street name
Street Name	String 80	Member Mailing Address Text	BillingStreet
Unit Number	String 10	Member Mailing Address Unit Number	BillingStreet
City	String 30	Member Mailing Address City	BillingCity
Province Code	String 6	Member Mailing Address Province	BillingState
Postal Code	String 6	Member Mailing Address Postal Code	BillingPostalCode
applicationCreateDate	Date	Application Create Date	OW_start_date__c	Keep OW's prefix, will be re-mapped with RGI in SF.
accountSource	String		"Housing/Shelter"	Hard code for RGI indicator

JSON example:

{
"allOrNone" : false,
"compositeRequest" : [
{
     "method" : "POST",
      "url" : "/services/data/v45.0/sobjects/Legacy_System_Record__e",
      "referenceId" : "265573",
       "body" : {
              "JSON_Message__c" : "{
                       "BillingStreet":"",
                       "BillingCity":"TORONTO",
                       "BillingState":"ON",
                       "BillingPostalCode":"M8V ",
                       "ApplicationId":"422368",
                       "RecordTypeId":"0126A000000yrPjQAI",
                       "AccountSource":"Housing/Shelter",
                       "first_name__c":"",
                       "last_name__c":"",
                       "date_of_birth__c":"1948-",
                       "Mobile_phone__c":"",
                       "RGI_Houshold_Id__c":"911613768",
                       "SSHA_id__c":"265573",
                       "RGI_Member_Id__c":"911613768",
                       "OW_start_date__c":"2017-06-30",
                       "rgi_status__c":"ELG"
                }",
              "Object_Name__c" : "Account"
       }
}...
]
}

TAWL - Yardi/Rent Cafe Status Mapping:

TAWL Application Statuses		Rent Café Status	Notes (Bring notes for all the canceled Rent Café statuses; Yardi will mass update into Secondary Status field later)
CX1	Cancelled - Arrears	Canceled	For ex. Notes type - 'Cancelled Reason'; Note: 'Cancelled \| Arrears' Yardi will update 'Arrears' into Secondary Status field
CX5	Cancelled - Chronological- COHB
CX4	Cancelled - Indigenous- COHB
CX3	Cancelled - Modified- COHB
CXG	Cancelled - SPP Portable Housing Benefit
CX2	Cancelled - SPP-COHB
CXR	Cancelled-3 Refusals
CXB	Cancelled-Administration
CXE	Cancelled-Applicant not Eligible
CXC	Cancelled-Applicant Request
CXA	Cancelled-Deceased
CXD	Cancelled-Documents Required
CXU	Cancelled-Duplicate Application
CXH	Cancelled-Homeless
CXX	Cancelled-Housed By Provider
CXF	Cancelled-Housed Humewood Partnership
CXP	Cancelled-Housed R/S (Next Steps)
CXQ	Cancelled-Housed R/S (Tent City)
CXI	Cancelled-Information Not Complete
CXT	Cancelled-Internal Transfer
CXM	Cancelled-Mail Returned
CXN	Cancelled-No Response
CXY	Cancelled-Not able to move
CXO	Cancelled-Other
CXJ	Cancelled-Reapplied in Error
CXS	Cancelled-Split
CXV	Cancelled-Unsatisfactory History
CXZ	Cancelled-Zero Members
ELG	*Eligible (Not on hold and no offers)	Eligible
	*Eligible (On hold for SPP Reconciliation)	Inactive
	*Eligible (On hold for COHB)	Canceled
	*Eligible (On Offer, Verbal Acceptance offer, and Accepted offer but not yet leased files)	On Offer	with Verbal Acceptance as Offer Secondary Status
HOU	Housed	Housed
CIF	Inactive - Housed Humewood Partnership	Housed	Notes type - 'Data from TAWL'; Note: 'Temp Housed'
CIG	Inactive - SPP Portable Housing Benefit	Housed	Notes type - 'Data from TAWL'; Note: 'Temp Housed'
CIA	Inactive - Arrears	Canceled
CI5	Inactive - Chronological- COHB		For ex. Notes type - 'Cancelled Reason'; Note: 'Inactive \| Chronological- COHB' Yardi will update 'Chronological- COHB' into Secondary Status field
CI4	Inactive - Indigenous- COHB
CI3	Inactive - Modified- COHB
CIW	Inactive - Refused to Withdraw
CI2	Inactive - SPP-COHB
CIR	Inactive-3 Refusals
CID	Inactive-Documents Required
CIH	Inactive-Homeless
CII	Inactive-Information Not Complete
CIM	Inactive-Mail Returned
CIN	Inactive-No Response
CIY	Inactive-Not able to move		*Convert cases prior to Jan 2020 to 'Eligible' and the rest to 'Canceled'
CIO	Inactive-Other
CIS	Inactive-Split
CIE	Inactive - Not Eligible	Ineligble
PEN	Pending	Applied

TAWL Copy and Processed Tables

On a daily basis, SSHA receives a copy of the TAWL clients and loads it into the MemberExtraction_tawl table. This table can be considered as the current version of the member information. The MemberExtraction_sf table contains the member information that has been uploaded to the CRM, this table can be considered the previous version

Initially the MemberExtraction_sf table is empty and the MemberExtraction_tawl table contains all the active RGI members. As the HSI RGI synch process runs, the differences between the MemberExtraction_tawl and the MemberExtraction_sf are sent to the CRM.

HSI has read access to the MemberExtraction_tawl table and read, insert, delete and update access to the MemberExtraction_sf table.

The MemberExtraction_tawl table does not appear to have any primary keys or indexes and does have some duplicates.

Views

SSHA has provided these views to support this process:

sf_delete, sf_insert, sf_update

Database Connectivity

jdbc:jtds:sqlserver://torms201.corp.toronto.ca:1433/

Database is HSI_RO

Browser not supported