View: invoice_extract_view

  create view invoice_extract_view as
select 'L' journal_type,
       jtp.customer_key project_customer_key,
       jtp.project_key project_key,
       jtp.project_code project_code,
       jtt.task_key,
       jtt.task_name,
       jta.account_key,
       jta.account_code,
       jta.description,
       jta.type,
       jt.journal_key,
       jt.journal_trans_date,
       jt.time_data_key transaction_data_key,
       jt.post_history_key,
       jt.invoice_key,
       jt.category,
       jt.amount,
       jt.extract_date,
       pt.person_key person_key,
       null item_description
  from journal_time jt
       join account jta on jta.account_key = jt.account_key
       join person_time_data ptd on ptd.time_data_key = jt.time_data_key
       join person_time pt on ptd.person_time_key = pt.person_time_key
       join project jtp on jtp.project_key = ptd.project_key
       left outer join task jtt on jtt.task_key = ptd.task_key
union all 
select 'E' journal_type,
       jep.customer_key project_customer_key,
       jep.project_key project_key,
       jep.project_code project_code,
       jet.task_key,
       jet.task_name,
       jea.account_key,
       jea.account_code,
       jea.description,
       jea.type,
       je.journal_key,
       je.journal_trans_date,
       je.expense_data_allocation_key transaction_data_key,
       je.post_history_key,
       je.invoice_key,
       je.category,
       je.amount,
       je.extract_date,
       er.owner_key person_key,
       null item_description
  from journal_expense je
       join account jea on jea.account_key = je.account_key
       join expense_data_allocation eda on eda.expense_data_allocation_key = je.expense_data_allocation_key
       join expense_data ed on eda.expense_data_key = ed.expense_data_key
       join expense_report er on ed.expense_report_key = er.expense_report_key
       join project jep on jep.project_key = eda.project_key
       left outer join task jet on jet.task_key = eda.task_key
union all 
select 'C' journal_type,
       jcpp.customer_key project_customer_key,
       jcpp.project_key project_key,
       jcpp.project_code project_code,
       null task_key,
       null task_name,
       jcpa.account_key,
       jcpa.account_code,
       jcpa.description,
       jcpa.type,
       jcp.journal_key,
       jcp.journal_trans_date,
       jcp.cost_plus_post_summary_key transaction_data_key,
       jcp.post_history_key,
       jcp.invoice_key,
       jcp.category,
       jcp.amount,
       jcp.extract_date,
       null person_key,
       null item_description
  from journal_cost_plus jcp
       join account jcpa on jcpa.account_key = jcp.account_key
       join cost_plus_post_summary cpps on cpps.cost_plus_post_summary_key = jcp.cost_plus_post_summary_key
       join project jcpp on jcpp.project_key = cpps.project_key
union all 
select 'F' journal_type,
       jfpp.customer_key project_customer_key,
       jfpp.project_key project_key,
       jfpp.project_code project_code,
       jfpt.task_key,
       jfpt.task_name,
       jfpa.account_key,
       jfpa.account_code,
       jfpa.description,
       jfpa.type,       
       jfp.journal_key,
       jfp.journal_trans_date,
       jfp.fixed_price_key transaction_data_key,
       jfp.post_history_key,
       jfp.invoice_key,
       jfp.category,
       jfp.amount,
       jfp.extract_date,
       null person_key,
       fp.description item_description
  from journal_fixed_price jfp
       join account jfpa on jfpa.account_key = jfp.account_key
       join fixed_price fp on fp.fixed_price_key = jfp.fixed_price_key
       join project jfpp on jfpp.project_key = fp.project_key
       left outer join task jfpt on jfpt.task_key = fp.task_key
union all 
select 'P' journal_type,
       jplp.customer_key project_customer_key,
       jplp.project_key project_key,
       jplp.project_code project_code,
       null task_key,
       null task_name,
       jpla.account_key,
       jpla.account_code,
       jpla.description,
       jpla.type,   
       jpl.journal_key,
       jpl.journal_trans_date,
       jpl.prebilled_labor_key transaction_data_key,
       jpl.post_history_key,
       jpl.invoice_key,
       jpl.category,
       jpl.amount,
       jpl.extract_date,
       null person_key,
       pl.description item_description
  from journal_prebilled_labor jpl
       join account jpla on jpla.account_key = jpl.account_key
       join prebilled_labor pl on pl.prebilled_labor_key = jpl.prebilled_labor_key
       join project jplp on jplp.project_key = pl.project_key
union all 
select 'O' journal_type,
       jocp.customer_key project_customer_key,
       jocp.project_key project_key,
       jocp.project_code project_code,
       null task_key,
       null task_name,
       joca.account_key,
       joca.account_code,
       joca.description,
       joca.type,          
       joc.journal_key,
       joc.journal_trans_date,
       joc.onetime_charge_key transaction_data_key,
       null post_history_key,
       oc.invoice_key,
       joc.category,
       joc.amount,
       joc.extract_date,
       null person_key,
       oc.description item_description
  from journal_onetime_charge joc
       join account joca on joca.account_key = joc.account_key
       join onetime_charge oc on oc.onetime_charge_key = joc.onetime_charge_key
       join project jocp on jocp.project_key = oc.project_key