create view margin_view (
customer_key,
project_key,
project_code,
manager_key,
open_edit,
quantity,
bill_amount,
cost_amount,
person_key,
exp_bill,
exp_cost,
work_date,
primary_key,
first_name,
last_name,
middle_initial,
suffix,
username,
post_date,
adjusted_number
)
as
select pr.customer_key,
pr.project_key,
pr.project_code,
pr.manager_key,
pr.open_edit,
ptd.quantity,
ptd.bill_rate * ptd.quantity * charindex(prt.billable, 'Y'),
ptd.cost_rate * ptd.quantity,
pt.person_key,
0,
0,
ptd.work_date,
ptd.time_data_key,
p.first_name,
p.last_name,
p.middle_initial,
p.suffix,
p.username,
ptd.post_date,
ptd.adjusted_number
from person_time_data ptd,
task t,
project pr,
project_type prt,
person_time pt,
person p
where prt.project_type_key = ptd.project_type_key
and pt.person_time_key = ptd.person_time_key
and p.person_key = pt.person_key
and t.task_key =* ptd.task_key
and pr.project_key = ptd.project_key
union
select pr.customer_key,
pr.project_key,
pr.project_code,
pr.manager_key,
pr.open_edit,
0,
0,
0,
er.owner_key,
eda.amount * charindex(prt.billable, 'Y'),
eda.amount,
ed.expense_date,
eda.expense_data_allocation_key,
p.first_name,
p.last_name,
p.middle_initial,
p.suffix,
p.username,
ed.expense_date,
0
from expense_data_allocation eda,
expense_data ed,
task t,
expense_report er,
project pr,
project_type prt,
expense_type et,
person p
where ed.expense_data_key = eda.expense_data_key
and prt.project_type_key = ed.project_type_key
and er.expense_report_key = ed.expense_report_key
and p.person_key = er.owner_key
and et.expense_type_key = ed.expense_type_key
and et.expense_type not in ('ADVANCE', 'CASH-RETURN')
and t.task_key =* eda.task_key
and pr.project_key = eda.project_key |