AnsweredAssumed Answered

task_id = -1 in nbi_proj_res_rates_and_costs

Question asked by jcs1951 on Apr 27, 2011
Latest reply on May 17, 2011 by another_martink
I've created a view that calculates the total cost of all etc's on a project. I'm starting to see now that a few projects are not calculating properly.

I found that the nbi_proj_res_rates_and_costs.task_id is -1 for most task_id's of the projects in question. When I calculate the etc's for the rows that have the proper task_id listed, it matches the output of the view.

Can anyone explain how the task_id gets populated with -1 instead of the real task ID number? I've tried to follow the datamart stored procedures to find what will generate this but just get lost.

Here's the actual view I've created:

select
l.proj_internal_id,
l.proj_name,
l.proj_id
sum(l.etc * l.rate) etc_cost

from

(select distinct
i.is_active proj_status,
i.id Proj_internal_id,
i.name proj_name,
i.code proj_id,
i.manager_id mgr_id,
tc.description transclass,
r.full_name res_name,
t.prname task_name,
t.prid task_id,
sum(a.prestsum/3600) etc,
nbi.cost_rate rate

from inv_investments i
inner join prtast t on i.id = t.prprojectid
inner join prassignment a on t.prid = a.prtaskid
inner join srm_resources r on a.prresourceid = r.id
inner join pac_mnt_resources p on r.unique_name = p.initials
inner join nbi_proj_res_rates_and_costs nbi on t.prprojectid = nbi.project_id
and a.prresourceid = nbi.resource_id
and t.prid = nbi.task_id
inner join transclass tc on p.transclass = tc.transclass

where r.is_active = 1
and a.prestsum/3600 > 0
and GETDATE() BETWEEN nbi.from_date and nbi.to_date
and i.chargecode = 5000001

group by i.is_active, i.id, i.name, i.code, r.full_name, a.prestsum, nbi.cost_rate, tc.description, i.manager_id, p.transclass, r.full_name, t.prname, t.prid
) l

group by
l.proj_internal_id,
l.proj_name,
l.proj_id

Thank you all very much for any help you can provde!

Jim

Outcomes