Adding solicitors to custom header

Hi,

i use a view of Plans that is then referenced in the header. if this makes sense great. if not ask me questions.

PLANS view

/* plan and plan worker(s) TX_WRKR_PLAN (WRKR_PLAN) .primary = 'Y' is the primary worker on the plan*/

select
--plan
PLANS.plan_no as PLANS_plan_no,
PLANS.campaign_no as PLANS_campaign_no,
PLANS.customer_no as PLANS_customer_no,
PLANS.customer_no as customer_no,
PLANS.cont_designation as PLANS_cont_designation,
PLANS.fund_no as PLANS_fund_no,
PLANS.original_source as PLANS_original_source,
PLANS.status as PLANS_status,
PLANS.type as PLANS_type,
PLANS.notes as PLANS_notes,
PLANS.goal_amt as PLANS_goal_amt,
PLANS.ask_amt as PLANS_ask_amt,
PLANS.cont_amt as PLANS_cont_amt,
PLANS.recorded_amt as PLANS_recorded_amt,
PLANS.start_dt as PLANS_start_dt,
PLANS.complete_by_dt as PLANS_complete_by_dt,
PLANS.priority as PLANS_priority,
PLANS.probability as PLANS_probability,
PLANS.custom_1 as PLANS_custom_1,
PLANS.custom_2 as PLANS_custom_2,
PLANS.custom_3 as PLANS_custom_3,
PLANS.custom_4 as PLANS_custom_4,
PLANS.custom_5 as PLANS_custom_5,
PLANS.custom_6 as PLANS_custom_6,
PLANS.custom_7 as PLANS_custom_7,
PLANS.custom_8 as PLANS_custom_8,
PLANS.custom_9 as PLANS_custom_9,
PLANS.custom_10 as PLANS_custom_10,
/*
PLANS.create_loc as PLANS_create_loc,
PLANS.create_dt as PLANS_create_dt,
PLANS.created_by as PLANS_created_by,
PLANS.last_update_dt as PLANS_last_update_dt,
PLANS.last_updated_by as PLANS_last_updated_by,

*/

--WRKR_PLANs
WRKR_PLAN.id as WRKR_PLAN_id,
WRKR_PLAN.plan_no as WRKR_PLAN_plan_no,
WRKR_PLAN.customer_no as WRKR_PLAN_customer_no,
WRKR_PLAN.role_no as WRKR_PLAN_role_no,
WRKR_PLAN.primary_ind as WRKR_PLAN_primary_ind,
/*
WRKR_PLAN.show_in_portfolio as WRKR_PLAN_show_in_portfolio,
WRKR_PLAN.create_dt as WRKR_PLAN_create_dt,
WRKR_PLAN.create_loc as WRKR_PLAN_create_loc,
WRKR_PLAN.created_by as WRKR_PLAN_created_by,
WRKR_PLAN.last_update_dt as WRKR_PLAN_last_update_dt,
WRKR_PLAN.last_updated_by as WRKR_PLAN_last_updated_by,

*/

--worker roles
WRKR_RL.id as WRKR_RL_id,
WRKR_RL.description as WRKR_RL_description,
/*
,
WRKR_RL.inactive as WRKR_RL_inactive,
WRKR_RL.create_dt as WRKR_RL_create_dt,
WRKR_RL.create_loc as WRKR_RL_create_loc,
WRKR_RL.created_by as WRKR_RL_created_by,
WRKR_RL.last_updated_by as WRKR_RL_last_updated_by,
WRKR_RL.show_in_portfolio_def as WRKR_RL_show_in_portfolio_def,
WRKR_RL.control_group as WRKR_RL_control_group
*/
WRKR_RL.last_update_dt as WRKR_RL_last_update_dt,
d.display_name as WorkerDisplayName,
d.display_name_short as WorkerDisplayNameShort,
d.display_name_tiny as WorkerDisplayNameTiny

from
T_PLAN plans
left outer join TX_CUST_PLAN WRKR_PLAN on plans.plan_no = WRKR_PLAN.plan_no
LEFT OUTER JOIN [dbo].FT_CONSTITUENT_DISPLAY_NAME() d ON WRKR_PLAN.customer_no = d.customer_no
left outer join TR_WORKER_ROLE WRKR_RL on WRKR_PLAN.role_no = WRKR_RL.id

reference in header

select
distinct
top 1
--/*
--b.description as campaign,
@WRKR_RL_last_update_dt = a.WRKR_RL_last_update_dt,
@Plans_plan_no = a.plans_plan_no,
@portfolio = e.fname + ' ' + upper(left(e.lname,1)) + '.',
@sol_type =
case
when a.PLANS_notes like '%sub |%' or rtrim(ltrim(a.PLANS_notes)) = 'Sub' then c.description + ' (Sub)'
else c.description
end
--*/
--a.*
from
LV_Plans a
join T_CAMPAIGN b (nolock) on a.PLANS_campaign_no = b.campaign_no
join TR_PLAN_TYPE c (nolock) on a.PLANS_type = c.id
join TR_PLAN_STATUS d (nolock) on a.PLANS_status = d.id
join T_CUSTOMER e (nolock) on a.WRKR_PLAN_customer_no = e.customer_no
where
PLANS_customer_no = @customer_no
--and WRKR_PLAN_primary_ind = 'Y'
and WRKR_RL_id = 2 --2=Worker, 1=Solicitor
and d.description like '%ongoing%'
--and c.description like 'wave %'

order by a.plans_plan_no desc, WRKR_RL_last_update_dt desc

Marissa,

We use a specific Affiliation type to record this.  For example, if my colleague Mary Jones is the primary contact at our organisation for the John Smith Foundation, Mary is affiliated with the foundation using this Affiliation type.  We use Flex Headers - the SQL for the sql_code field in TR_FLEX_HEADER_ELEMENT is:

select fname + ' ' + lname from T_AFFILIATION a join T_CUSTOMER c on c.customer_no=a.individual_customer_no where a.affiliation_type_id=10027 and a.group_customer_no=@customer_no

Obviously, you'd need to swap the value 10027 value for the appropriate ID from your TR_AFFILIATION_TYPE system table.

Martin