FINANCIAL_DATA_VIEW

  Tables Used  

SQL*Server Create Statement:

create view financial_data_view (
   customer_key,
   first_name,
   last_name,
   middle_initial,
   suffix,
   username,
   project_key,
   project_code,
   manager_key,
   open_edit,
   task_key,
   task_name,
   task_number,
   project_type_key,
   project_type,
   billable,
   person_key,
   quantity,
   bill_rate,
   expense_amount,
   cost_date,
   description,
   bill_amount,
   cost_amount,
   bill_exp,
   budget_hours,
   budget_labors,
   budget_expenses,
   t_budget_hours,
   t_budget_labors,
   t_budget_expenses,
   task_sequence,
   post_date,
   adjusted_number
)
as
select   p.customer_key,
         p.first_name,
         p.last_name,
         p.middle_initial,
         p.suffix,
         p.username,
         pr.project_key,
         pr.project_code,
         pr.manager_key,
         pr.open_edit,
         ptd.task_key,
         t.task_name,
         t.task_number,
         prt.project_type_key,
         prt.project_type,
         prt.billable,
         pt.person_key,
         ptd.quantity,
         ptd.bill_rate,
         0,
         ptd.work_date,
         prt.description,
         ptd.bill_rate * ptd.quantity * charindex(prt.billable,'Y'),
         ptd.cost_rate * ptd.quantity,
         0,
         pr.budget_hours,
         pr.budget_dollars_cap,
         pr.budget_dollars_op,
         t.budget_hours,
         t.budget_dollars_cap,
         t.budget_dollars_op,
         t.sequence,
         ptd.post_date,
         ptd.adjusted_number
  from   person_time_data ptd,
         project_type prt,
         person_time pt,
         task t,
         project pr,
         person p
 where   prt.project_type_key = ptd.project_type_key
   and   pt.person_time_key = ptd.person_time_key
   and   t.task_key =* ptd.task_key
   and   pr.project_key = ptd.project_key
   and   pt.person_key = p.person_key
union all
select   p.customer_key,
         p.first_name,
         p.last_name,
         p.middle_initial,
         p.suffix,
         p.username,
         pr.project_key,
         pr.project_code,
         pr.manager_key,
         pr.open_edit,
         eda.task_key,
         t.task_name,
         t.task_number,
         prt.project_type_key,
         prt.project_type,
         prt.billable,
         er.owner_key,
         0,
         0,
         eda.amount,
         ed.expense_date,
         prt.description,
         0,
         0,
         eda.amount * charindex(prt.billable,'Y'),
         pr.budget_hours,
         pr.budget_dollars_cap,
         pr.budget_dollars_op,
         t.budget_hours,
         t.budget_dollars_cap,
         t.budget_dollars_op,
         t.sequence,
         ed.expense_date,
         0
  from   expense_data_allocation eda,
         expense_data ed,
         project_type prt,
         expense_report er,
         expense_type et,
         task t,
         project pr,
         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   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
   and   er.owner_key = p.person_key