All Active PM users (excluding contractors)



MASS EMAIL GROUPS – SEPTEMBER 26, 2007

SUPPLEMENTAL DOCUMENTATION

Current groups (2.90.81):

[pic]

Recommend changing names so they all start with a program area designation (“All” is assumed and probably not necessary”) and alphabetizing the list:

All Active PM users (including Contractors) → PM Active Users (including contractors)

All Inactive PM users → PM Inactive users (including contractors)

All PM “Master Account” Holders” → PM Master Account Holders

All Challenge Participants → Challenge Participants

All Active PM Users (excluding contractors) → PM Active Users (excluding contractors)

Groups to be added in 2.90.90

□ Service & Product Provider (Primary Contact, Spokesperson, CEO)

□ Service & Product Provider (Primary Contact, Spokesperson)

□ Service & Product Provider (Primary Contact)

□ C&I EEPS (Primary Contact, Spokesperson, CEO)

□ C&I EEPS (Primary Contact, Spokesperson)

□ C&I EEPS (Primary Contact)

□ Labeled Buildings (Primary Point of Contact for Applications)

□ Labeled Buildings (Primary Point of Contact for Facilities)

□ Labeled Buildings (Plaque Recipients)

□ Labeled Buildings (Property Managers)

This document includes:

- Matrix with Group Name (without the name changes) and description of the requirement/criteria for the group.

- SQL query for each group

- (not included is the access which is documented in the PRD)

The names and descriptions should be reflected in the PRD

The SQL is maintained in the ref_mass_email_group table (one row per group).

(Note: all queries also include the condition of:

- Contact must have an email that is not blank/null (the email format is not checked at the query level, it is included in the mass email and shown as part of the “Failed” list in Mass Email), and

- Where a contact record is available, contact must have flag set to allow mass emails to be sent.

- Duplicate emails addresses may appear in the same group and are handled prior to the send (the queries include the duplicates).

|NAME |DESCRIPTION |

|All Active PM Users (excluding contractors) |PM users, not including contractors (ICF, SRA, ), who have logged into |

| |the tool within the past year. |

|All Active PM users (including Contractors) |PM users, including contractors, who have logged into the tool within the past |

| |year. |

|All Challenge Participants |Active Contacts with a Challenge Contact role in one of four programs (C&I REPS,|

| |Buildings, Small Business, or Service & Product Provider) and where the |

| |organization is associated to the challenge in iSTAR in the Challenge |

| |Participant list. |

|All Inactive PM users |PM users, including contractors, who have not logged into the tool within the |

| |past year. |

|All PM “Master Account” Holders” |PM Master Account Holders |

|Buildings (Primary Contact) |Active contacts with a Primary Contact role in an active Buildings Partner |

| |program. |

|Buildings (Primary Contact, Spokesperson) |Active contacts with a Primary Contact or Spokesperson role in an active |

| |Buildings Partner program. |

|Buildings (Primary Contact, Spokesperson, CEO) |Active contacts with a Primary Contact or Spokesperson role in an active |

| |Buildings Partner program, or any role in the active Buildings Partner program |

| |and a CEO role in the organization. |

|C&I EEPS (Primary Contact) |Active contacts with a Primary Contact role in an active C&I EEPS Partner |

| |program. |

|C&I EEPS (Primary Contact, Spokesperson) |Active contacts with a Primary Contact or Spokesperson role in an active C&I |

| |EEPS Partner program. |

|C&I EEPS (Primary Contact, Spokesperson, CEO) |Active contacts with a Primary Contact or Spokesperson role in an active C&I |

| |EEPS Partner program, or any role in the active C&I EEPS Partner program and a |

| |CEO role in the organization. |

|Labeled Buildings (Plaque Recipients) |Contacts listed as the Plaque Recipient (i.e., “mail plaque to”) on any Label |

| |Application that has been Received & Approved. |

|Labeled Buildings (Primary Point of Contact for |Contacts listed as the Primary Point of Contact on any Label Application that |

|Applications) |has been Received & Approved. |

|Labeled Buildings (Primary Point of Contact for |Contacts listed as the Facility’s Primary Point of Contact (from the SEP) on any|

|Facilities) |Label Application that has been Received & Approved. |

|Labeled Buildings (Property Managers) |Contacts listed as the Property Manager (i.e., “mail plaque to”) on any Label |

| |Application that has been Received & Approved. |

|Leaders Participants |Active contacts with the Leaders Contact role (in any program). |

|PM Users (Active Hospitals/Medical Offices) |PM users, not including contractors (ICF, SRA, ), that are building |

| |administrators for active Hospitals or Medical Offices buildings (based on |

| |defined spaces) with building names that do not include the words “test”, |

| |“sample” or “example”. |

|Service & Product Provider (Primary Contact) |Active contacts with a Primary Contact role in an active Service & Product |

| |Provider Partner program. |

|Service & Product Provider (Primary Contact, |Active contacts with a Primary Contact or Spokesperson role in an active Service|

|Spokesperson) |& Product Provider Partner program. |

|Service & Product Provider (Primary Contact, |Active contacts with a Primary Contact or Spokesperson role in an active Service|

|Spokesperson, CEO) |& Product Provider Partner program, or any role in the active Service & Product |

| |Provider Partner program and a CEO role in the organization. |

--All Active PM users (excluding contractors)

select distinct dbid, first_name, last_name, email, phone

from (

select distinct u.userid dbid, su_first_name first_name, su_last_name last_name, su_email email, su_phone phone, max(ss_login_date) last_login

from star_user u, su_session s

where u.userid=s.ss_userid

and su_email is not null

and m_code='PMPAM'

and upper(su_email) not like '%%'

and upper(su_email) not like '%%'

and upper(su_email) not like '%%'

and upper(su_email) not like '%%'

group by u.userid, su_first_name, su_last_name, su_email, su_phone)

where last_login>sysdate-365

-- All Active PM users (including Contractors)

select distinct dbid, first_name, last_name, email, phone

from (

select distinct u.userid dbid, su_first_name first_name, su_last_name last_name, su_email email, su_phone phone, max(ss_login_date) last_login

from star_user u, su_session s

where u.userid=s.ss_userid

and su_email is not null

and m_code='PMPAM'

group by u.userid, su_first_name, su_last_name, su_email, su_phone)

where last_login>sysdate-365

---All Challenge Participants

select distinct c.c_id dbid, c_first_name first_name, c_last_name last_name, c_email email, c_phone1 phone

from contact c, role ro, relation re

where re.re_id = ro.re_id

and ro.c_id = c.c_id

and c.c_email is not null

and re.p_code in ('ESB','CIREPS','SP','ESSB')

and re.o_id in (select o_id from challenge c)

and ro.r_code = 'CPR'

and c.c_active_yn = 'Y' and NVL(C_MASS_MAIL_YN,'N') = 'N'

---All Inactive PM users

select distinct dbid, first_name, last_name, email, phone

from (

select distinct u.userid dbid, su_first_name first_name, su_last_name last_name, su_email email, su_phone phone, max(ss_login_date) last_login

from star_user u, su_session s

where u.userid=s.ss_userid

and su_email is not null

and m_code='PMPAM'

group by u.userid, su_first_name, su_last_name, su_email, su_phone)

where last_login ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download