**Distribution 1
EMAIL - REG - Dist 1 - Agent Registry
**
- Name – Active Title Agent Registry Listings
- Includes - All records with a ‘current’ Registry listing, defined as:
- Has Registry Confirmations? = TRUE
- in SUB - REG - Has Confirmations
- Output/Distribution – Email Address for Primary and Secondary Contact(s) for the ‘Uber Parent ALTA ID’ for the record (people with access to manage the records in the RMS).
- Relationship End Date is NULL or Future
- Notes: We intend to specifically exclude REA listings and UW only listing
****Distribution 2
Due to multiple SQL IN and NOT IN, this query wasn't possible via Impexium's Query Builder. Moved to Thaddeus, must be run manually.
********EMAIL - REG - Dist 2 - Agent Registry w/ RON
- Name – Active Title Agent Registry Listings with RON
- Includes – All records with a ‘current’ Registry listing w/ a RON indication, defined as:
- Has Registry Confirmations? = TRUE
- in SUB - REG - Has Confirmations
- Has selected at least 1 RON platform - (category in the custom data table)
- in SUB - REG - w/ RON
- NOTE: This query is a MANUAL list of the RON categories. If more are added, it will need to be manually updated. Impexium doesn't support sub-queries two levels deep.
- Output/Distribution - Email Address for Primary and Secondary Contact(s) for the ‘Uber Parent ALTA ID’ for the record (people with access to manage the records in the RMS).
- Relationship End Date is NULL or Future
Distribution 3
Due to multiple SQL IN and NOT IN, this query wasn't possible via Impexium's Query Builder. Moved to Thaddeus, must be run manually.
EMAIL - REG - Dist 3 - Agent Registry w/o RON
- Name – Active Title Agent Registry Listings without RON
- Includes – All records with a ‘current’ Registry listing w/ a RON indication, defined as:
- Has Registry Confirmations? = TRUE
- in SUB - REG - Has Confirmations
- Has selected 0 RON platform - (category in the custom data table)
- NOT in SUB - REG - w/ RON
- NOTE: This query is a MANUAL list of the RON categories. If more are added, it will need to be manually updated. Impexium doesn't support sub-queries two levels deep.
- Output/Distribution - Email Address for Primary and Secondary Contact(s) for the ‘Uber Parent ALTA ID’ for the record (people with access to manage the records in the RMS).
- Relationship End Date is NULL or Future
****Distribution 4
EMAIL - REG - Dist 4 - Registry Past Due LRD
- Name - Active Registry Listings with Past Due Listing Review Date
- Includes - All records with a ‘current’ Registry listing, defined as:
- In Registry = True
- Listing Review Date is LESS than (<) ‘X’ Date – can this date be made customizable? No.
- DEFAULT to one year ago. Also not possible.
- in SUB - REG - Past Due Listing Review Date
- CRITICAL NOTE: While we CAN have a variable date in the Query, this isn't supported in HL/RM, nor is a "one year ago" that rolls forward with current.
- Before using this Dist 4 query in HL/RM, someone will have to manually change the date inside the "SUB" query, and save it with that date.
- This will need to them be confirmed with testing to make sure that it actual does make the change and the list updates.
- Output/Distribution - Email Address for Primary and Secondary Contact(s) for the ‘Uber Parent ALTA ID’ for the record (people with access to manage the records in the RMS).
- Relationship End Date is NULL or Future
****Distribution 5
EMAIL - REG - Dist 5 - Compliant A&A Not in Registry
- Name – Currently Compliant Title Agents and Attorneys WITHOUT a Registry Listing
- Includes – All records with a Membership or PFL that are NOT listed in the Registry.
- All records with type: ACA or ATXA or REA or PFL Paid or PFL Waiver
- No children have In Registry = True
- NOT in SUB - REG - In Registry
- Output/Distribution - Email Address for Primary and Secondary Contact(s) for the ‘Uber Parent ALTA ID’ for the record (people with access to manage the records in the RMS)
- Relationship End Date is NULL or Future
MAIL - REG - Dist 5 - Compliant A&A Not in Registry
- Same criteria as above, except PC only for a single piece to a location.
- Includes Primary Mailing Address (if no primary mailing, no record outputs)
Distribution 6
EMAIL - REG - Dist 6 - Non-Compliant Not in Registry
- Name – Currently Non-Compliant (all types) WITHOUT a Registry Listing
- Includes – All records in 'the universe' without a current member or PFL
- Writes Title = Yes (Universe)
- WITHOUT a PFL (any type)
- No children have In Registry = True
- NOT in SUB - REG - In Registry
- Output/Distribution - Email Address for Primary and Secondary Contact(s) for the ‘Uber Parent ALTA ID’ for the record (people with access to manage the records in the RMS).
- Relationship End Date is NULL or Future
MAIL - REG - Dist 6 - Non-Compliant Not in Registry
- Same criteria as above, except PC only for a single piece to a location.
- Includes Primary Mailing Address (if no primary mailing, no record outputs)