create view assignment_view as
select 1 assignment_type,
null assignment_key,
ro.customer_key project_customer_key,
ro.customer_code project_customer_code,
r.project_key project_key,
r.project_code project_code,
r.project_type_key project_type_key,
r.project_status_key project_status_key,
null project_manager_key,
null open_edit,
null task_key,
null task_name,
po.customer_key person_customer_key,
po.customer_code person_customer_code,
null person_key,
null person_active,
null person_labor_category_key,
null person_labor_category,
null person_bill_rate,
null person_cost_rate,
null person_location_key,
null person_location,
null bill_rate,
null bill_rate_source,
null cost_rate,
null cost_rate_source,
null bill_customer_key,
null bill_customer_code,
null cost_customer_key,
null cost_customer_code,
null labor_category_key,
null labor_category,
null location_key,
null location,
null begin_date,
null end_date,
null budget_hours,
null etc_hours,
null exceed_budget,
null use_wbs_dates,
null cost_struct_labor_key,
null edc,
null last_etc_update_date
from customer_assignment a
join project r on r.project_key = a.project_key
join customer ro on ro.customer_key = r.customer_key
join customer po on po.customer_key = a.customer_key
union all
select 2 assignment_type,
a.project_assignment_key assignment_key,
ro.customer_key project_customer_key,
ro.customer_code project_customer_code,
r.project_key project_key,
r.project_code project_code,
r.project_type_key project_type_key,
r.project_status_key project_status_key,
pc.person_key project_manager_key,
pra.project_manager_open open_edit,
null task_key,
null task_name,
po.customer_key person_customer_key,
po.customer_code person_customer_code,
p.person_key person_key,
p.active person_active,
plc.labor_category_key person_labor_category_key,
plc.labor_category person_labor_category,
e.bill_rate person_bill_rate,
e.cost_rate person_cost_rate,
pl.location_key person_location_key,
pl.location_name person_location,
a.bill_rate bill_rate,
a.bill_rate_source,
a.cost_rate cost_rate,
a.cost_rate_source,
a.bill_customer bill_customer_key,
bo.customer_code bill_customer_code,
a.cost_customer cost_customer_key,
co.customer_code cost_customer_code,
alc.labor_category_key labor_category_key,
alc.labor_category labor_category,
al.location_key location_key,
al.location_name location,
a.begin_date begin_date,
a.end_date end_date,
a.budget_hours budget_hours,
a.etc_hours etc_hours,
a.exceed_budget exceed_budget,
a.use_wbs_dates use_wbs_dates,
a.cost_struct_labor_key,
a.est_date_of_completion edc,
a.last_etc_update_date last_etc_update_date
from project_assignment a
join project r on r.project_key = a.project_key
join project_open_access_view pra on r.project_key = pra.project_key
join customer ro on ro.customer_key = r.customer_key
join person p on p.person_key = a.person_key
join person_rate e on e.person_key = a.person_key
and a.end_date between e.begin_date and e.end_date
join customer po on po.customer_key = p.customer_key
left outer join labor_category plc on plc.labor_category_key = p.labor_category_key
left outer join location pl on pl.location_key = p.location_key
left outer join customer bo on bo.customer_key = a.bill_customer
left outer join customer co on co.customer_key = a.cost_customer
left outer join labor_category alc on alc.labor_category_key = a.labor_category_key
left outer join location al on al.location_key = a.location_key
left outer join project_controller pc on pc.project_key = r.project_key
and pc.role_key = 3
and pc.primary_ind = 'Y'
where r.task_level_assignment = 'N'
union all
select 3 assignment_type,
a.task_assignment_key assignment_key,
ro.customer_key project_customer_key,
ro.customer_code project_customer_code,
r.project_key project_key,
r.project_code project_code,
r.project_type_key project_type_key,
r.project_status_key project_status_key,
pc.person_key project_manager_key,
pra.project_manager_open open_edit,
t.task_key task_key,
t.task_name task_name,
po.customer_key person_customer_key,
po.customer_code person_customer_code,
p.person_key person_key,
p.active person_active,
plc.labor_category_key person_labor_category_key,
plc.labor_category person_labor_category,
e.bill_rate person_bill_rate,
e.cost_rate person_cost_rate,
pl.location_key person_location_key,
pl.location_name person_location,
a.bill_rate bill_rate,
a.bill_rate_source,
a.cost_rate cost_rate,
a.cost_rate_source,
a.bill_customer bill_customer_key,
bo.customer_code bill_customer_code,
a.cost_customer cost_customer_key,
co.customer_code cost_customer_code,
alc.labor_category_key labor_category_key,
alc.labor_category labor_category,
al.location_key location_key,
al.location_name location,
a.begin_date begin_date,
a.end_date end_date,
a.budget_hours budget_hours,
a.etc_hours etc_hours,
a.exceed_budget exceed_budget,
a.use_wbs_dates use_wbs_dates,
a.cost_struct_labor_key,
a.est_date_of_completion edc,
a.last_etc_update_date last_etc_update_date
from task_assignment a
join project r on r.project_key = a.project_key
join project_open_access_view pra on r.project_key = pra.project_key
join task t on t.task_key = a.task_key
join customer ro on ro.customer_key = r.customer_key
join person p on p.person_key = a.person_key
join person_rate e on e.person_key = a.person_key
and a.end_date between e.begin_date and e.end_date
join customer po on po.customer_key = p.customer_key
left outer join labor_category plc on plc.labor_category_key = p.labor_category_key
left outer join location pl on pl.location_key = p.location_key
left outer join customer bo on bo.customer_key = a.bill_customer
left outer join customer co on co.customer_key = a.cost_customer
left outer join labor_category alc on alc.labor_category_key = a.labor_category_key
left outer join location al on al.location_key = a.location_key
left outer join project_controller pc on pc.project_key = r.project_key
and pc.role_key = 3
and pc.primary_ind = 'Y'
where r.task_level_assignment = 'Y'
|