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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- menu production record 4 healthy eating ca dept of
- all active pm users excluding contractors
- sample test questions test 1 university of florida
- question 3 4 e r model required question
- menu production record rcci with snack healthy eating
- current listing of additional skill identifiers asi and
- medication administration record mar
- daily attendance record licensed child care centers dcf
- module 3 examples r code
- what are coliforms centers for disease control and
Related searches
- active directory users account
- active directory users and computers install
- active directory users and computers downloads
- active directory users and computers access
- export users from active directory
- active directory export users powershell
- enable active directory users and computers
- install active directory users computers windows 10
- download active directory users windows 10
- powershell install active directory users and computers
- windows 10 active directory users and computers
- install active directory users and groups