Preliminary Work

  1. Compile a list of agent and agency contacts along with current login credentials, sweep account information, commission structure, underwriter managing each agency, and if using a payment vendor, commission account information
  2. Notate any agencies that have more than one location
  3. Notate the head agency for those with more than one location
  4. Notate any agents who write for more than one agency
  5. Consider these best practices
    • Attach agencies to policies and provide each agent with his/her own login
    • Send email notifications to agencies rather than to individual agents
    • Convert your data rather than enter it by hand
    • Rather than send agencies individual emails for each policy deliverable, use the Send Daily "Print List" Email checkbox to send a daily digest of all policies for that agency. When using this setting, the agent/agency checkbox does not need to be selected for applicable deliverables in Settings > Deliverables
    • Do not assign the role of agency and agency group to the same contact; rather, create separate contacts

Core Concepts

Individuals and Organizations

A contact may be an individual or organization.

All the Information About the Contact

All information associated with a contact appears with that contact, including name, address, phone number, email, role within BriteCore, payment methods, and login credentials.

Changes Permeate Through the System

Updates to a contact in the Contacts module change the information throughout BriteCore. For example, if a mortgagee's address changes, the new address will appear on all policies to which the mortgagee is associated.

Suggestions

When staff or agents begin typing a name into a contact field, BriteCore will suggest possible matches among contacts from the Contacts module.

Dates Worth Remembering

Each contact has a Dates to Remember section where important dates, with alerts, can be stored. Alerts are expecially helpful when managing agent licenses. When an alert is used, an email like the below is sent to the identified receipients:

BriteCore - Date to Remember - [title of note]
[content of note]

Roles

Each contact is assigned a role, which has specific functions in BriteCore. For example, an organization with the role of mortgagee results in that contact being accessible when assigning mortgagees to policies.

A contact may have more than role, such as a staff member who is also an agent. In that case, the contact would have the roles of employee and agent.

The below table summarizes the roles available in BriteCore.

Role
Type
Portal access General purpose
Additional insured
Individual/Organization
- Add to policy, receive deliverables, and be billed
Additional interest
Individual/Organization
- Add to policy, receive deliverables, and be billed
Administrator
Individual/Organization
Admin Add other roles in the Contacts module and access all modules and data
Agency
Organization
Agent Though an agency login can access the agent portal, it is not necessary to do so
Agency group
Organization
Agent Though an agency group login can access the agent portal, it is not necessary to do so
Agent
Individual
Agent Quote, submit applications, view policies and claims, submit endorsements, and file claims
Attorney
Individual/Organization
- Add to claim
Board member
Individual/Organization
- For informational purposes only in the Contacts module
Claimant
Individual/Organization
- Add to liability claim
Claims adjuster
Individual/Organization
Admin Can only view claims to which he/she is associated; further access depends upon permissions
Claims supervisor
Individual
- Added to claim
Associate with another role for access to the administrative portal
Contractor
Individual/Organization
- Add to claim
Employee
Individual
Admin Access to all modules and data except adding a role in the Contacts module
Access can change depending upon permissions
In Care Of
Individual/Organization
- Retired
Inspector
Individual
- Add to policy
Loss payee
Individual/Organization
- Add to policy, attached to specific coverages and claims
Mortgagee
Organization
- Add to policy and claim
Named insured
Individual/Organization
- Add to policy and claim
Police department
Organization
- Add to claim
Premium finance company
Organization
- Add to policy
Primary carrier
Organization
- Name appears throughout BriteCore
Reinsurer
Organization
- Add to reinsurance contracts for claims
Underwriter
Individual
Admin Same access as employee
Makes the contact selectable during quoting when seeking underwriter approval
Unlisted payor
Individual/Organization
- Any contact who pays a policy that is not listed on the policy as a named insured, additional interest, additional insured, mortgagee, loss payee, or premium finance company
Vendor
Individual/Organization
- Used with StoneRiver

BriteCore Setup

Permissions (IWS Only)

Navigate to Settings > System Wide > Permissions.

Agency Group and/or Agency Permission

Unneeded as each agent will have his/her own login.

Agent Permission

Quoting

  • Creating a quote agent/policies/createQuote
  • Submitting a quote agent/policies/submitQuote

Policies

  • Creating an endorsement agent/policies/createEndorsement
  • Deleting an in-progress endorsement agent/policies/removeEndorsement
  • Submitting an endorsement agent/policies/submitEndorsement

Claims

  • Accessing claims agent/claims
  • Creating a claim agent/claims/createClaim
  • Editing an in-progress claim agent/claims/editClaim
  • Submitting a claim agent/claims/submitClaim
  • Accessing claim accounting agent/claims/accounting
  • Just the reserves agent/claims/accounting/reserves
  • Accessing claim Documents/Attachments agent/claims/ko_attachments

Payments (Sweep Payments)

  • The entire module agent/payments
  • The ability to add sweep payments agent/payments/scheduleSweep
  • The sweep report agent/payments/showSweepReport

Reports/Stats

  • The entire module agent/stats
  • The loss ratio report agent/stats/lossRatio
  • The commission report agent/stats/commission

Settings

  • The entire module agent/settings

Agency Principal Permission, if Applicable

  • This permission may have more access than a standard agent
  • Set up with an agent role but with an agency principal permission

CSR Permission, if Applicable

  • Typically this permission restricts the ability to submit applications, endorsements, and claims
  • Set up with an agent role but with a CSR permission

Administrator Permission

Typically administrators have full access to BriteCore. Administrators should have access to Support Tools. For access, add Read/Write permission to britecore/misc/support_tools.

Staff Permission

All staff should be restricted from

  • Accessing the Lines module britecore/lines
  • Accessing the Settings module britecore/settings

Independent Claims Adjuster

By default, the role of Claims Adjuster limits access to claims to which the adjuster is attached. Otherwise, an adjuster should be restricted from

  • Accessing all modules but Claimsbritecore/(lines|settings|contacts|reports|policies)
  • Accessing support tools britecore/misc/support_tools
  • Accessing sections of a claim other than the Information tab britecore/claims/(components|processing|notes|accounting|ko_attachments|contacts)
  • Accessing sections of the Information tab other than Policy Information, Reports, and Photos
    britecore/claims/information/(descriptionBox|options| pastClaims|lossAddress|catastrophe|coveragesFiledAgainst| reinsuranceContracts|dates)

Settings

Settings > System Wide > Login Information > Session Time > Session Length

Set the session length for each logged-in user. To accommodate quoting, set this to at least 60 minutes. Once the session time expires, the user will be logged out of the system.

Settings > System Wide > Login Information > Password Strength

Settings that Should Be Checked for Minimum Security

  • Password length must exceed 8 characters
  • Passwords must contain both letters and numbers
  • Do not allow dictionary words

Settings that Could Be Checked for Additional Security

  • Passwords must contain a punctuation character
  • Allow users to enable Multi-factor Authentication. See the Enable Multi-Factor Authentication how-to below for more information.

Avoid

  • Using words found in a dictionary or the user's personal information
  • Formatting of calendar dates, license plate numbers, telephone numbers, or other common numbers
  • Using the company name or an abbreviation
  • Matching the password to the username

Settings > System Wide > Login Information > Login Attempts

This locks a user out after a defined number of login attempts.

Minimum Security

  • Allow 4 failed login attempts
  • Freeze account for 5 minutes after limit reached

Maximum Security

  • Allow 2 failed login attempts
  • Freeze account for 30 minutes after limit reached

Settings > Modules > Contacts > Contact Pop-Up

Enforce the SSN, date of birth, and phone number when a new contact is added to BriteCore (administrative portal) or BriteQuote (agent portal).

The fields are required for contacts with the named insured role but not required for contacts with the additional interest role.

Settings > Modules > Contacts > Duplicate SSNs

By default BriteCore disallows duplicate SSNs. The tradeoff for which is for an insured to be quoted by different agencies, a user within the Administrative Portal must remove the contact's SSN each time the applicant is quoted. When enabled, the setting allows carriers to accommodate the case for applicants to receive quotes with different agencies.

Those with access may read PR 5339.

Settings > Modules > Contacts > Commissions

Learn about commissions.

Database

Geocode-Addresses

When enabled, shows the latitude and longitude for every contact's address. To enable, run

#UPDATE settings SET value=1 WHERE option = 'geocode-addresses'

Contacts Module

Enter Your Carrier Information

  1. Navigate to the Contacts module
  2. Click New Organization
    • The most important information is the name and email. The name entered is what will appear throughout the BriteCore site to all users

Enter Staff

  1. Navigate to the Contacts module
  2. Click New Individual
  3. Complete information
  4. Assign a role(s)

Enter Agents Before Agencies

  1. Navigate to the Contacts module
  2. Click New Individual
  3. Enter the name, address, phone, website, position, and background information
    Do not attach the agency in the Company section as this will be completed on the agency contact. If an agent writes for more than one agency, the agency that will appear in this area will be the primary agency
  4. If desired, track licenses in the Dates to Remember section
  5. Click Add a Role
  6. Select the Agent role
  7. Enter the Username and Password
    Do not enter an email into Email Confirmation To if you want agents to receive an email for their login credentials. An email is only sent once the site is live
  8. Select the permission level, default state, and timezone
  9. Enter the producer number (i.e., NPN#)
  10. Click Save

Enter Agencies Next

  1. Navigate to the Contacts module
  2. Click New Organization
  3. Click Payments
  4. Add the sweep account and, if applicable, commission account. Learn more about setting up sweep accounts. Ensure the payment vendor information is entered in Settings > Vendors.
  5. Click Information
  6. Enter the name, address, phone, email, website, FEIN/TaxID, contacts in this organization (all agents in agency) and background information

Important notes

  • If you want the agency number to appear on the Declaration, enter it as part of the name; otherwise, keep reading
  • You may have more than one email address but each email must be set as work to receive electronic notifications
  • Adding the Agency role
    1. Click Add a Role
    2. Select the Agency role
      • With this setup there is no need for the agency to have a username and password
    3. Select the permission level, default state, and timezone
    4. Check each applicable box for electronic notices and select the correct email address
  • Accept Policy Documents Electronically controls whether any notices are sent electronically
  • Send Daily "Print List" Email sends a daily summary email to an agency showing what printed

When using this setting, do not select the agent/agency copy in Settings > Deliverables. Doing so will create an unnecessary copy that will appear in Policies > Processing > Pending Email

  • Notify by Email All Overdue Invoices works independently from the Send Daily "Print List" Email setting. An email like the below is sent when a policy enters non-pay.

    BriteCore Notice: Overdue Invoice An invoice has been marked as overdue for policy 10-2016-1. Policy information Insured:

  • Direct Application Activity to Specified Email Addresses allows new business activity to be directed to specific underwriters for that agency; otherwise, new business activity will go to the recipients defined in Settings > System Wide > Administrative Alerts > Policies for
    • Application submitted from BriteQuote
    • Policy Change submitted from BriteQuote
    • Attachment(s) or Photo(s) Uploaded to Submitted Application
      New business activity can be directed to recipients designated in both this area and the administrative alert by checking the Direct Application Activity To" setting in contacts overrides admin email list setting in Settings > System Wide > Administrative Alerts > Policies. If unchecked, only the designated recipients in the Direct Application Activity to Specified Email Addresses area will receive an email on new business activity for the agency:
      1. Enter the agency number, if not included in the name
      2. Select the commission structure, sweep account, and commission payment
      3. Click Save

Database Only

To turn off autocomplete when logging in, run

UPDATE settings SET value=1 WHERE option='no-autocomplete'


How-Tos

Track Agency Touchpoints
Impersonate a Contact
Add a Contact
Delete a Contact
Unarchive a Contact
View Insurance Information of an Insured
Update or Add an Insured's Address
Pull a New Credit Score and Report
Enable Multi-Factor Authentication
Change the Agent or Agency of Record
Revert a Change to an Agent or Agency of Record
Remove a User from BriteCore
Track Agent License Expirations
Add Email Notices for Named Insured or Additional Interest

Track Agency Touchpoints

Setup

  1. Navigate to Settings > Modules > Contacts
  2. Locate the Dates to Remember section
  3. Add a list of touchpoints to Touchpoints (Comma separated), such as Phone Call, Email, Office Visit

Usage

  1. Navigate to the Contacts module
  2. Access an agency contact
  3. Locate the Dates to Remember section
  4. Click New
  5. Complete the form
  6. To receive an alert, complete the alert section

Reporting

  1. Create a BriteData report using the Date to Remember data points

Impersonate a Contact

  1. Access the contact within the Contacts module
  2. Click Impersonate This Contact

Important notes

  • This feature is reserved for administrators only and is intended to help agents who may have questions while working in the Agent Portal
  • If the contact has one role, specifically agent, you will be immediately redirected to the Agent Portal. If the contact has more than one role, you will be prompted to select a role
  • During the impersonation session, a banner will appear across the top of the page and all notes created in the system will be created as the impersonated contact
  • A detailed log of impersonation sessions are kept in your site's database

Add a Contact

  1. Navigate to the Contacts module
  2. Click New Individual or New Organization
  3. Add information to the top sections
  4. Click Add a Role

Only users with the Administrator role are able to add roles

  1. Select a role
  2. Add information to the role section

Delete a Contact

A contact cannot be deleted, only archived:

  1. Navigate to the Contacts module
  2. Enter the contact's name in the search preceded by a # (e.g., #John Smith)
  3. Click Go
  4. Click X next to the contact's name

Do not archive contacts who are associated with legitimate policies or claims.

Unarchive a Contact

  1. Navigate to the dashboard
  2. Click Support Tools
  3. Click Unarchive Records
  4. Select Contacts
  5. Enter the contact's name
  6. Click Unarchive Data

View Insurance Information of an Insured

The Contacts module is a central place to view insurance information related to a specific insured:

  1. Access the contact
  2. Within the Named Insured role section at the bottom of the Information tab, view the insured's
    • Driver's license
    • Date of birth
    • Credit score and credit report history if you integrate with ISO or LexisNexis Credit Scoring
    • Claims
    • Policies

Since applications tend to change, these do not appear in this list. An insured's application activity can be viewed in the Policies module if desired

  1. Click the Payments tab for the insured
  2. View and add financial accounts for the insured
  3. View any scheduled electronic payments

Update or Add an Insured's Address

  1. Access the contact
  2. Click Edit next to the Address

If the insured is attached to an application, policy, or claim, BriteCore will provide a warning indicating the address change will update for all applications, policies, and claims

  1. Amend the current address or click + to add a new address

Pull a New Credit Score and Report

  1. Integrate with ISO or LexisNexis Credit Scoring
  2. Access the contact
  3. Navigate to the Credit Reports section at the bottom of the Information tab
  4. Click Get Credit Score

Enable Multi-Factor Authentication

This is enabled per contact. It is recommended for staff but not agents. Complete the following steps to enable multi-factor authentication:

  1. Navigate to Settings > System Wide > Login Information > Password Strength
  2. Check the Allow users to enable Multi-Factor Authentication box
  3. Navigate to the Contacts module
  4. Access the contact
  5. Navigate to the Security tab for that contact
  6. Follow the prompts

Change the Agent or Agency of Record

First Steps

  1. Determine the effective date of the change

The agent/agency on the policy at the end of any given month receives the commission payment

  1. Navigate to the Contacts module
  2. Search for the current agent/agency
  3. Scroll down to the Policies section of the contact
    • These are the active policies, including uncommitted, to which the agent/agency is attached. If the status is unsubmitted or submitted, the policy number will not appear in the list. Complete the Recommended Steps then follow one of the two procedures below
  1. Select the appropriate checkbox
    • Terminate This Agent for an agent or Agency is Inactive for an agency

These checkboxes do not keep internal staff from adding the agent/agency to a policy

  1. If you no longer want the agent/agency to access the agent portal
    • Remove the Username or Password
    • Click Save
    • If access is needed for a given amount of time, create a Note Alert in the Notes tab of the contact with a reminder to remove the password at some date in the future
  2. For record keeping, add the date terminated to the Dates to Remember section
  3. At the top of the contact screen next to the contact’s name change the agent's/agency’s flag from white to orange. The orange flag will show when the contact’s name is searched
  4. Since a terminated agent can still be added to a policy put the word TERMINATED in the agent/agency's name (e.g., TERMINATED - John Smith)

Procedure 1

Manually change the agent/agency of record (limited number of policies):

  1. Once you complete the above steps, navigate to each policy
  2. Create a revision dated the effective date of the agent/agency change of record
  3. Update the agent/agency contact
  4. Ensure the new agency has the correct commission rate
  5. Uncheck the Declaration Prints checkbox at the top of the screen so a Declaration doesn’t print. Leave the checkbox checked if a new Declaration is needed
  6. Commit the revision

Procedure 2

Automatically change the agent/agency of record (number of policies prohibit manual change):

  1. Navigate to the Dashboard
  2. Click Support Tools in the footer
  3. Click Transfer Agency Policies
  4. Complete the form. A new, committed revision will be on each policy and will be effective on the date entered in the Date Effective box
  5. Click Update Policies
    • A confirmation window will appear with a list of updated policies. Consider copying then pasting the list into a new note in the Policies module for record-keeping
    • A Policies Reassigned note will be written for each affected contact and policy. The note will contain a unique ID that specifically references the changes that were made with the transfer

This interface tool uses the switch_agencies tool in the policies section of the Britecore shell.

Revert a Change to an Agent or Agency of Record

  1. Identify a contact or policy that was changed
  2. Access the contact or policy
  3. Navigate to Notes
  4. Search using the phrase Policies Reassigned
  5. Copy the entire ID at the end of the note, which is a string of numbers and letters like 01d2f57f-60ce-4f4c-8769-2bc3dc827737
  6. Navigate to the Dashboard
  7. Click Support Tools in the footer
  8. Click Revert Agency Policies Transfer
  9. Paste the ID
  10. Click Revert
    • The committed revision that was created from the original AOR change will be archived and a note will be written on each affected contact and policy indicating the change was reverted

Remove a User from BriteCore

Clients

  1. Access the contact
  2. Remove the user's password
  3. Change all necessary passwords in BriteCore
  4. Update Administrative Alerts in the Settings module
  5. Submit a ticket to IWS to update any locked-down vendor passwords and for an updated Authorized Users list. The submitter must be an Authorized User.

IWS Only

  1. Ensure the requestor is an Authorized User
  2. BriteIdeas > Admin > Users > Deactivate. This addresses BriteIdeas and Trac
  3. Deactivate the forum account
  4. The client must complete a new Authorized Users list
  5. Upload the new completed Authorized Users on the IWS home page

Track Agent License Expirations

  1. Access the agent's contact
  2. In Dates to Remember, click Edit
  3. Complete the pop-up
  4. Click Save and Close

Add Email Notices for Named Insured or Additional Interest

This feature is still under development. The setting appears on the screen but will not currently send documents electronically.

  1. Navigate to Contacts
  2. Add a new contact or locate an existing contact
  3. Navigate to Information > Role
  4. If needed, click Add a Role
  5. Add Named Insured or Additional Interest role or select the Named Insured or Additional Interest role from the drop-down list
  6. Click Done
  7. Navigate to Notices
  8. Click Accept Policy Documents Electronically
  9. Select an email address from the Email Address to Use drop-down list
  10. Click Save

FAQs

Can an agent change a named insured's address?
If the insured has only one policy, the agent will see the familar pencil icon to edit the contact's address. However, if the insured has more than one policy, the agent cannot update a contact's address from the Agent Portal.

When an agent writes for more than one agency, how does he/she know which agency to choose while quoting?
The agency's address appears along with the agency name.

During a transfer, do all policies transfer to the new agent/agency, including quotes and applications?
The table summarizes which policies are transferred based upon the policy statuses that exist in BriteCore.

Policy status Yes No
Unsubmitted, submitted, and rejected quotes/applications X
Active but never committed/issued policies X
Active and committed/issued policies X
Cancellation Pending policies X
Canceled or Expired policies X

After an agent/agency of record change, which agent/agency receives commissions?
Paid premium
The agency on the policy at the end of the month receives commissions on any qualifying premium payments.

Written premium

  • When the agent/agency of record is changed, the commission is not prorated to each agency. The old agency retains the full commissionable amount. However,
    • Any newly written premium will have commissions paid to the new agency
    • If the policy cancels, the negative Written Premium will be assessed to the new agency
    • If premium is written off, the negative Written Premium will be assessed to the new agency

I pay commissions on Written Premium. When a change occurs how do I pay commissions mid-term to the new agency?
Without changing the system, the procedure to accomplish this is to rewrite the policy:

  1. Access the policy
  2. Use the Cancel Policy button to cancel on the date of the agency of record change
  3. Once canceled, click the Rewrite Policy button
  4. Use a new policy number or the same policy number
  5. Set the Effective Date of the new policy equal to the cancel date of the old policy
  6. Change the term to Custom
  7. Ensure the Expiration Date is the original policy’s expiration date
  8. Ensure that when the policy renews next year, it will do so on a 1 Year term
  9. Once rewritten, process the new policy as you would any other rewrite (e.g., ensure any Return Premium is on the new policy, etc.)

After a change, which agency is associated with the claim?
The agency on the policy as of the loss date is the agency that will remain associated with the claim. The claim will then affect the associated agency’s loss ratio.

If I change the agency on the renewal revision, will that new agency receive the renewal Declaration when the renewal deliverables are created?
No. The old agency will continue to receive policy deliverables until the renewal revision comes to pass.

When I change the agent, is a notice sent to InsVista?
Not presently.


More Information

Audits

For those with database access, use or amend the below queries to audit the entry of agent, agency, and agency group contacts.

Shows Relationship Between Agency Group Roles and Agency Roles

SELECT t2.name AS Agency Group, t3.name AS Agency
FROM
(SELECT t1.name, x_contacts.id FROM
(SELECT contacts.name, contacts.id
FROM contacts
JOIN x_contacts_roles ON x_contacts_roles.contactId = contacts.id
WHERE x_contacts_roles.roleId = '36887a3c-f46b-11dd-bc31-000c29e52919' AND contacts.active=1) as t1
JOIN x_contacts ON t1.id = x_contacts.refContactId) as t2
JOIN
(SELECT x_contacts.id, contacts.name FROM x_contacts
join contacts ON contact.id = x_contacts.contactId
where contacts.active=1) as t3
ON t2.id = t3.id
ORDER BY t2.name, t3.name;

Shows Relationship Between Agency Roles and Agent Roles

SELECT t2.name AS Agency, t3.name AS Agent
FROM
(SELECT t1.name, x_contacts.id FROM
(SELECT contacts.name, contacts.id
FROM contacts
JOIN x_contacts_roles ON x_contacts_roles.contactId = contacts.id
WHERE x_contacts_roles.roleId = '36887a96-f46b-11dd-bc31-000c29e52919' AND contacts.active=1) as t1
JOIN x_contacts ON t1.id = x_contacts.refContactId) as t2
JOIN
(SELECT x_contacts.id, contacts.name FROM x_contacts
join contacts ON contacts.id = x_contacts.contactId
where contacts.active=1) as t3
ON t2.id = t3.id
ORDER BY t2.name, t3.name;

Agency Roles with no Associated Agency Group Role

SELECT Contact Name, id
FROM (
SELECT contacts.id, contacts.name as 'Contact Name', roles.name as 'Role Name',
COUNT(x_contacts.refContactId) as 'Count',
taco.name
FROM contacts
JOIN x_contacts_roles on x_contacts_roles.contactId = contacts.id
JOIN roles on roles.id = x_contacts_roles.roleId AND roles.id = '36887a96-f46b-11dd-bc31-000c29e52919'
JOIN x_contacts on x_contacts.contactId = contacts.id
LEFT JOIN x_contacts_roles as refRoll on refRoll.contactId = x_contacts.refContactId
LEFT JOIN roles as taco on taco.id = refRoll.roleId
GROUP BY contacts.id
ORDER BY contacts.id) as results
WHERE count = 0

Agent Roles with no Associated Agency Role

SELECT * FROM
(SELECT contacts.id, name FROM contacts
JOIN x_contacts_roles on contacts.id = x_contacts_roles.contactId
WHERE x_contacts_roles.roleId = '36887ae6-f46b-11dd-bc31-000c29e52919' and contacts.active=1) as t1
LEFT JOIN x_contacts ON t1.id = x_contacts.contactId
WHERE x_contacts.id IS NULL and name is not NULL

Agent Roles Missing Usernames or Passwords

SELECT contacts.id, name, producerNumber, username, password FROM contacts
JOIN x_contacts_roles on contacts.id = x_contacts_roles.contactId
WHERE x_contacts_roles.roleId = '36887ae6-f46b-11dd-bc31-000c29e52919' and
contacts.active=1 AND
contacts.id not like '%IWSA%'
and 'terminated'=0
and username IS NULL
and password is NULL

Determine Which Agency Roles Are Missing the Electronic Notification Setting

SELECT * FROM
(SELECT contacts.id, contacts.name, emailNotices, noticeEmailId, acceptElectronicCommStatement, commStatementEmailId, emailPrintList, notifyOfOverDueInvoices
FROM contacts
JOIN x_contacts_roles ON x_contacts_roles.contactId = contacts.id
WHERE active=1 AND x_contacts_roles.roleId = '36887a96-f46b-11dd-bc31-000c29e52919' AND NAME IS NOT NULL
ORDER BY NAME) AS t1
WHERE emailNotices=0 OR acceptElectronicCommStatement=0 OR
commStatementEmailId IS NULL OR emailPrintList=0 OR
notifyOfOverDueInvoices=0 OR emailNotices IS NULL

Addresses for Agent and Agency Roles

SELECT t1.* FROM
(SELECT contacts.id, contacts.name, addressLine1, addressLine2, addressCity, addressState, addressZip, addressCounty, addresses.type,
contacts.producerNumber, contacts.agencyNumber
FROM addresses
JOIN contacts ON contacts.id = addresses.contactId
WHERE contacts.active=1) AS t1
JOIN x_contacts_roles ON x_contacts_roles.contactId = t1.id
WHERE roleId IN
('36887a3c-f46b-11dd-bc31-000c29e52919',
'36887a96-f46b-11dd-bc31-000c29e52919',
'36887ae6-f46b-11dd-bc31-000c29e52919')
ORDER BY NAME

To Which Email Address Emails Are Sent

SELECT t1.name as 'Agency or Agency Grup Name', emails.email AS 'Send Deliverables To' , test.email AS 'Send Commission Statements To' FROM
(SELECT contacts.id, contacts.name, emailNotices, noticeEmailId, acceptElectronicCommStatement, commStatementEmailId, emailPrintList, notifyOfOverDueInvoices
FROM contacts
JOIN x_contacts_roles on x_contacts_roles.contactId = contacts.id
WHERE active=1 and x_contacts_roles.roleId IN ('36887a96-f46b-11dd- bc31-000c29e52919','36887a3c-f46b-11dd-bc31-000c29e52919') and name IS NOT NULL
ORDER BY name) as t1
JOIN emails as 'test' on test.id = t1.commStatementEmailId
JOIN emails on emails.id = t1.noticeEmailId
order by NAME

Agency Roles Missing Counties

SELECT * FROM
(SELECT contacts.id, name FROM contacts
JOIN x_contacts_roles as xr on xr.contactId = contacts.id
WHERE xr.roleId = '36887a96-f46b-11dd-bc31-000c29e52919' and contacts.active=1 and
contacts.id not like '%%IWSA%%' and name is not null) as t1
JOIN addresses on addresses.contactId = t1.id
WHERE addressCounty = ''
ORDER BY NAME

Agency Roles Missing a Commission Structure

SELECT * FROM contacts
JOIN x_contacts_roles ON x_contacts_roles.contactId = contacts.id
JOIN roles ON roles.id = x_contacts_roles.roleId
WHERE (commissionStructure IS NULL OR commissionStructure = '') AND
contacts.NAME IS NOT NULL AND
roles.NAME IN ('Agency') AND
contacts.active = 1
ORDER BY contacts.name;


Feedback

Report unclear or missing documentation.