Previous: customer_payment_export_view Unanet Data Model Next: journal_view

View: expense_budget_view

Columns
  Column Name Data Type
1.   expense_budget_type integer
2.   customer_key decimal(15)
3.   project_org_code varchar(25)
4.   project_key decimal(15)
5.   project_code varchar(30)
6.   task_key decimal(15)
7.   task_name varchar(50)
8.   expense_type varchar(25)
9.   expense_type_name varchar(50)
10.   master_markup decimal(17,2)
11.   project_markup decimal(5,2)
12.   expense_type_key decimal(17,2)
13.   description varchar(50)
14.   begin_date timestamp
15.   end_date timestamp
16.   amount decimal(18,3)
17.   use_wbs_dates char(1)
Table/Column Dependencies
Table Column
customer customer_code
  customer_key
expense_project_budget amount
  begin_date
  description
  end_date
  expense_type_key
  project_key
  use_wbs_dates
expense_task_budget amount
  begin_date
  description
  end_date
  expense_type_key
  project_key
  task_key
  use_wbs_dates
expense_type expense_type
  expense_type_key
  expense_type_name
  markup
project customer_key
  project_code
  project_key
project_expense_type expense_type_key
  markup
  project_key
task task_key
  task_name
SQL Server Create Statement 
create view expense_budget_view as
select 1 expense_budget_type,
       c.customer_key,
       c.customer_code project_org_code,
       p.project_key,
       p.project_code,
       t.task_key,
       t.task_name,
       et.expense_type,
       et.expense_type_name,
       et.markup master_markup,
       pet.markup project_markup,
       etb.expense_type_key,
       etb.description,
       etb.begin_date,
       etb.end_date,
       etb.amount,
       etb.use_wbs_dates
  from expense_task_budget etb
       join project p on p.project_key = etb.project_key
       join customer c on c.customer_key = p.customer_key
       join expense_type et on et.expense_type_key = etb.expense_type_key
       left outer join task t on t.task_key = etb.task_key
       left outer join project_expense_type pet on pet.expense_type_key = etb.expense_type_key and pet.project_key = etb.project_key
union all
select 2 expense_budget_type,
       c.customer_key,
       c.customer_code project_org_code,
       p.project_key,
       p.project_code,
       null task_key,
       null task_name,
       et.expense_type,
       et.expense_type_name,
       epb.expense_type_key,
       et.markup master_markup,
       pet.markup project_markup,
       epb.description,
       epb.begin_date,
       epb.end_date,
       epb.amount,
       epb.use_wbs_dates
  from expense_project_budget epb
       join project p on p.project_key = epb.project_key
       join customer c on c.customer_key = p.customer_key
       join expense_type et on et.expense_type_key = epb.expense_type_key
       left outer join project_expense_type pet on pet.expense_type_key = epb.expense_type_key and pet.project_key = epb.project_key