View: assignment_view

Columns
  Column Name Data Type
1.   assignment_type integer
2.   assignment_key decimal(15)
3.   project_customer_key decimal(15)
4.   project_customer_code varchar(25)
5.   project_key decimal(15)
6.   project_code varchar(30)
7.   project_type_key decimal(15)
8.   project_status_key decimal(15)
9.   project_manager_key decimal(15)
10.   open_edit char(1)
11.   task_key decimal(15)
12.   task_name varchar(50)
13.   person_customer_key decimal(15)
14.   person_customer_code varchar(25)
15.   person_key decimal(15)
16.   person_active char(1)
17.   person_labor_category_key decimal(15)
18.   person_labor_category varchar(50)
19.   person_bill_rate decimal(15,5)
20.   person_cost_rate decimal(15,5)
21.   person_location_key decimal(15)
22.   person_location varchar(50)
23.   bill_rate decimal(15,5)
24.   bill_rate_source char(1)
25.   cost_rate decimal(15,5)
26.   cost_rate_source char(1)
27.   bill_customer_key decimal(15)
28.   bill_customer_code varchar(25)
29.   cost_customer_key decimal(15)
30.   cost_customer_code varchar(25)
31.   labor_category_key decimal(15)
32.   labor_category varchar(50)
33.   location_key decimal(15)
34.   location varchar(50)
35.   begin_date timestamp
36.   end_date timestamp
37.   budget_hours decimal(15,2)
38.   etc_hours decimal(15,2)
39.   exceed_budget char(1)
40.   use_wbs_dates char(1)
41.   cost_struct_labor_key decimal(15)
42.   edc timestamp
43.   last_etc_update_date timestamp
Table/Column Dependencies
Table Column
customer customer_code
  customer_key
customer_assignment customer_key
  project_key
labor_category labor_category
  labor_category_key
location location_key
  location_name
person active
  customer_key
  labor_category_key
  location_key
  person_key
person_rate begin_date
  bill_rate
  cost_rate
  end_date
  person_key
project customer_key
  pm_open
  project_code
  project_key
  project_status_key
  project_type_key
  task_level_assignment
project_assignment begin_date
  bill_customer
  bill_rate
  bill_rate_source
  budget_hours
  cost_customer
  cost_rate
  cost_rate_source
  cost_struct_labor_key
  end_date
  est_date_of_completion
  etc_hours
  exceed_budget
  labor_category_key
  last_etc_update_date
  location_key
  person_key
  project_assignment_key
  project_key
  use_wbs_dates
project_controller person_key
  primary_ind
  project_key
  role_key
task task_key
  task_name
task_assignment begin_date
  bill_customer
  bill_rate
  bill_rate_source
  budget_hours
  cost_customer
  cost_rate
  cost_rate_source
  cost_struct_labor_key
  end_date
  est_date_of_completion
  etc_hours
  exceed_budget
  labor_category_key
  last_etc_update_date
  location_key
  person_key
  project_key
  task_assignment_key
  task_key
  use_wbs_dates
SQL Server Create Statement
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,
       r.pm_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 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,
       r.pm_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 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'