RGI Client CRM Synchronization
- Ramzi Henein (Unlicensed)
- Vincent Li (Unlicensed)
High Level Design
Synchronizing the RGI client information with the CRM follows this approach.
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 | |
|
| 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:
{ |
|---|
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