View: journal_view

Columns
  Column Name Data Type
1.   journal_type varchar(2)
2.   project_customer_key decimal(15)
3.   project_customer_code varchar(25)
4.   project_key decimal(15)
5.   project_code varchar(30)
6.   task_key decimal(15)
7.   task_name varchar(50)
8.   account_key decimal(15)
9.   account_code varchar(25)
10.   description varchar(128)
11.   type char(1)
12.   journal_key decimal(15)
13.   journal_trans_date timestamp
14.   transaction_data_key decimal(15)
15.   time_data_key decimal(15)
16.   expense_data_allocation_key decimal(15)
17.   post_history_key decimal(15)
18.   invoice_key decimal(15)
19.   category tinyint
20.   amount decimal(18,2)
21.   extract_date timestamp
22.   general_ledger_key decimal(15)
23.   organization_key decimal(15)
24.   org_code varchar(25)
25.   org_name varchar(128)
26.   person_key decimal(15)
27.   item_description varchar(128)
28.   writeoff_amount decimal(18,2)
29.   writeoff_quantity decimal(15,2)
30.   writeoff_bill_rate decimal(15,5)
31.   writeoff_cost decimal(18,2)
32.   writeoff_markup decimal(5,2)
Table/Column Dependencies
Table Column
account account_code
  account_key
  description
  type
cost_plus_post_detail cost_plus_post_detail_key
  expense_data_allocation_key
  fin_document_detail_key
  time_data_key
customer customer_code
  customer_key
  customer_name
expense_data expense_data_key
  expense_report_key
expense_data_allocation expense_data_allocation_key
  expense_data_key
  task_key
expense_report expense_report_key
  owner_key
fin_document_detail description
  fin_document_detail_key
  person_key
fin_document_detail_project fin_document_detail_key
  task_key
fixed_price description
  fixed_price_key
  task_key
funding_cap_adjustment funding_cap_adjustment_key
  task_key
invcd_cost_plus_post_detail cost_plus_post_detail_key
  invoice_key
  writeoff_amount
  writeoff_quantity
invcd_document_expense fin_document_detail_key
  invoice_key
  writeoff_amount
  writeoff_cost
  writeoff_markup
invcd_expense expense_data_allocation_key
  invoice_key
  writeoff_amount
  writeoff_cost
  writeoff_markup
invcd_fee_cap_adjustment fee_cap_adjustment_key
  invoice_key
  writeoff_amount
invcd_fixed_price fixed_price_key
  invoice_key
  writeoff_amount
invcd_funding_cap_adjustment funding_cap_adjustment_key
  invoice_key
  writeoff_amount
invcd_time invoice_key
  time_data_key
  writeoff_amount
  writeoff_bill_rate
  writeoff_quantity
invoice invoice_key
  voided_invoice_key
journal_cost_plus_detail account_key
  amount
  category
  cost_plus_post_detail_key
  extract_date
  general_ledger_key
  invoice_key
  journal_key
  journal_trans_date
  organization_key
  post_history_key
  project_key
journal_document_expense account_key
  amount
  category
  extract_date
  fin_document_detail_key
  general_ledger_key
  invoice_key
  journal_key
  journal_trans_date
  organization_key
  post_history_key
  project_key
journal_expense account_key
  amount
  category
  expense_data_allocation_key
  extract_date
  general_ledger_key
  invoice_key
  journal_key
  journal_trans_date
  organization_key
  post_history_key
  project_key
journal_fee_cap_adjustment account_key
  amount
  category
  extract_date
  fee_cap_adjustment_key
  general_ledger_key
  invoice_key
  journal_key
  journal_trans_date
  organization_key
  post_history_key
  project_key
journal_fixed_price account_key
  amount
  category
  extract_date
  fixed_price_key
  general_ledger_key
  invoice_key
  journal_key
  journal_trans_date
  organization_key
  post_history_key
  project_key
journal_funding_cap_adjustment account_key
  amount
  category
  extract_date
  funding_cap_adjustment_key
  general_ledger_key
  invoice_key
  journal_key
  journal_trans_date
  organization_key
  post_history_key
  project_key
journal_onetime_charge account_key
  amount
  category
  extract_date
  general_ledger_key
  journal_key
  journal_trans_date
  onetime_charge_key
  organization_key
  project_key
journal_prebilled_labor account_key
  amount
  category
  extract_date
  general_ledger_key
  invoice_key
  journal_key
  journal_trans_date
  organization_key
  post_history_key
  prebilled_labor_key
  project_key
journal_time account_key
  amount
  category
  extract_date
  general_ledger_key
  invoice_key
  journal_key
  journal_trans_date
  organization_key
  post_history_key
  project_key
  time_data_key
onetime_charge description
  invoice_key
  onetime_charge_key
  task_key
person_time person_key
  person_time_key
person_time_data person_time_key
  task_key
  time_data_key
prebilled_labor description
  prebilled_labor_key
project customer_key
  project_code
  project_key
task task_key
  task_name
SQL Server Create Statement
create view journal_view as
select 'L' journal_type,
       prc.customer_key project_customer_key,
       prc.customer_code project_customer_code,
       pr.project_key project_key,
       pr.project_code project_code,
       t.task_key,
       t.task_name,
       a.account_key,
       a.account_code,
       a.description,
       a.type,
       j.journal_key,
       j.journal_trans_date,
       j.time_data_key transaction_data_key,
       j.time_data_key,
       null expense_data_allocation_key,
       j.post_history_key,
       j.invoice_key,
       j.category,
       j.amount,
       j.extract_date,
       j.general_ledger_key,
       j.organization_key,
       porg.customer_code org_code,
       porg.customer_name org_name,
       pt.person_key person_key,
       null item_description,
       case
         when i.voided_invoice_key is not null then -invcd.writeoff_amount
         else invcd.writeoff_amount
       end writeoff_amount,
       case
         when i.voided_invoice_key is not null then -invcd.writeoff_quantity
         else invcd.writeoff_quantity
       end writeoff_quantity,
       invcd.writeoff_bill_rate,
       null writeoff_cost,
       null writeoff_markup
  from journal_time j
       join account a on a.account_key = j.account_key
       join person_time_data ptd on ptd.time_data_key = j.time_data_key
       join person_time pt on pt.person_time_key = ptd.person_time_key
       join project pr on pr.project_key = j.project_key
       join customer prc on prc.customer_key = pr.customer_key
       left outer join invcd_time invcd on invcd.time_data_key = ptd.time_data_key and invcd.invoice_key = j.invoice_key
       left outer join invoice i on i.invoice_key = j.invoice_key
       left outer join customer porg on porg.customer_key = j.organization_key
       left outer join task t on t.task_key = ptd.task_key
union all
select 'E' journal_type,
       prc.customer_key project_customer_key,
       prc.customer_code project_customer_code,
       pr.project_key project_key,
       pr.project_code project_code,
       t.task_key,
       t.task_name,
       a.account_key,
       a.account_code,
       a.description,
       a.type,
       j.journal_key,
       j.journal_trans_date,
       j.expense_data_allocation_key transaction_data_key,
       null time_data_key,
       j.expense_data_allocation_key,
       j.post_history_key,
       j.invoice_key,
       j.category,
       j.amount,
       j.extract_date,
       j.general_ledger_key,
       j.organization_key,
       porg.customer_code org_code,
       porg.customer_name org_name,
       er.owner_key person_key,
       null item_description,
       case
         when i.voided_invoice_key is not null then -invcd.writeoff_amount
         else invcd.writeoff_amount
       end writeoff_amount,
       null writeoff_quantity,
       null writeoff_bill_rate,
       case
         when i.voided_invoice_key is not null then -invcd.writeoff_cost
         else invcd.writeoff_cost
       end writeoff_cost,
       invcd.writeoff_markup
  from journal_expense j
       join account a on a.account_key = j.account_key
       join expense_data_allocation eda on eda.expense_data_allocation_key = j.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 pr on pr.project_key = j.project_key
       join customer prc on prc.customer_key = pr.customer_key
       left outer join invcd_expense invcd on invcd.expense_data_allocation_key = eda.expense_data_allocation_key and invcd.invoice_key = j.invoice_key
       left outer join invoice i on i.invoice_key = j.invoice_key
       left outer join customer porg on porg.customer_key = j.organization_key
       left outer join task t on t.task_key = eda.task_key
union all
select 'C' journal_type,
       prc.customer_key project_customer_key,
       prc.customer_code project_customer_code,
       pr.project_key project_key,
       pr.project_code project_code,
       t.task_key task_key,
       t.task_name task_name,
       a.account_key,
       a.account_code,
       a.description,
       a.type,
       j.journal_key,
       j.journal_trans_date,
       j.cost_plus_post_detail_key transaction_data_key,
       ptd.time_data_key,
       null expense_data_allocation_key,
       j.post_history_key,
       j.invoice_key,
       j.category,
       j.amount,
       j.extract_date,
       j.general_ledger_key,
       j.organization_key,
       porg.customer_code org_code,
       porg.customer_name org_name,
       pt.person_key person_key,
       null item_description,
       case
         when i.voided_invoice_key is not null then -invcd.writeoff_amount
         else invcd.writeoff_amount
       end writeoff_amount,
       case
         when i.voided_invoice_key is not null then -invcd.writeoff_quantity
         else invcd.writeoff_quantity
       end writeoff_quantity,
       null writeoff_bill_rate,
       null writeoff_cost,
       null writeoff_markup
  from journal_cost_plus_detail j
       join cost_plus_post_detail cppd on cppd.cost_plus_post_detail_key = j.cost_plus_post_detail_key
       join account a on a.account_key = j.account_key
       join project pr on pr.project_key = j.project_key
       join customer prc on prc.customer_key = pr.customer_key
       left outer join invcd_cost_plus_post_detail invcd on invcd.cost_plus_post_detail_key = j.cost_plus_post_detail_key and invcd.invoice_key = j.invoice_key
       left outer join invoice i on i.invoice_key = j.invoice_key
       left outer join customer porg on porg.customer_key = j.organization_key
       join person_time_data ptd on ptd.time_data_key = cppd.time_data_key
       join person_time pt on pt.person_time_key = ptd.person_time_key
       left outer join task t on t.task_key = ptd.task_key
union all
select 'C' journal_type,
       prc.customer_key project_customer_key,
       prc.customer_code project_customer_code,
       pr.project_key project_key,
       pr.project_code project_code,
       t.task_key task_key,
       t.task_name task_name,
       a.account_key,
       a.account_code,
       a.description,
       a.type,
       j.journal_key,
       j.journal_trans_date,
       j.cost_plus_post_detail_key transaction_data_key,
       null time_data_key,
       eda.expense_data_allocation_key,
       j.post_history_key,
       j.invoice_key,
       j.category,
       j.amount,
       j.extract_date,
       j.general_ledger_key,
       j.organization_key,
       porg.customer_code org_code,
       porg.customer_name org_name,
       er.owner_key person_key,
       null item_description,
       case
         when i.voided_invoice_key is not null then -invcd.writeoff_amount
         else invcd.writeoff_amount
       end writeoff_amount,
       null writeoff_quantity,
       null writeoff_bill_rate,
       null writeoff_cost,
       null writeoff_markup
  from journal_cost_plus_detail j
       join cost_plus_post_detail cppd on cppd.cost_plus_post_detail_key = j.cost_plus_post_detail_key
       join account a on a.account_key = j.account_key
       join project pr on pr.project_key = j.project_key
       join customer prc on prc.customer_key = pr.customer_key
       left outer join invcd_cost_plus_post_detail invcd on invcd.cost_plus_post_detail_key = j.cost_plus_post_detail_key and invcd.invoice_key = j.invoice_key
       left outer join invoice i on i.invoice_key = j.invoice_key
       left outer join customer porg on porg.customer_key = j.organization_key
       join expense_data_allocation eda on eda.expense_data_allocation_key = cppd.expense_data_allocation_key
       join expense_data ed on ed.expense_data_key = eda.expense_data_key
       join expense_report er on er.expense_report_key = ed.expense_report_key
       left outer join task t on t.task_key = eda.task_key
union all
select 'C' journal_type,
       prc.customer_key project_customer_key,
       prc.customer_code project_customer_code,
       pr.project_key project_key,
       pr.project_code project_code,
       t.task_key task_key,
       t.task_name task_name,
       a.account_key,
       a.account_code,
       a.description,
       a.type,
       j.journal_key,
       j.journal_trans_date,
       j.cost_plus_post_detail_key transaction_data_key,
       null time_data_key,
       null expense_data_allocation_key,
       j.post_history_key,
       j.invoice_key,
       j.category,
       j.amount,
       j.extract_date,
       j.general_ledger_key,
       j.organization_key,
       porg.customer_code org_code,
       porg.customer_name org_name,
       fdd.person_key person_key,
       fdd.description item_description,
       case
         when i.voided_invoice_key is not null then -invcd.writeoff_amount
         else invcd.writeoff_amount
       end writeoff_amount,
       null writeoff_quantity,
       null writeoff_bill_rate,
       null writeoff_cost,
       null writeoff_markup
  from journal_cost_plus_detail j
       join cost_plus_post_detail cppd on cppd.cost_plus_post_detail_key = j.cost_plus_post_detail_key
       join account a on a.account_key = j.account_key
       join project pr on pr.project_key = j.project_key
       join customer prc on prc.customer_key = pr.customer_key
       left outer join invcd_cost_plus_post_detail invcd on invcd.cost_plus_post_detail_key = j.cost_plus_post_detail_key and invcd.invoice_key = j.invoice_key
       left outer join invoice i on i.invoice_key = j.invoice_key
       left outer join customer porg on porg.customer_key = j.organization_key
       join fin_document_detail fdd on fdd.fin_document_detail_key = cppd.fin_document_detail_key
       join fin_document_detail_project fddp on fddp.fin_document_detail_key = fdd.fin_document_detail_key
       left outer join task t on t.task_key = fddp.task_key
union all
select 'F' journal_type,
       prc.customer_key project_customer_key,
       prc.customer_code project_customer_code,
       pr.project_key project_key,
       pr.project_code project_code,
       t.task_key,
       t.task_name,
       a.account_key,
       a.account_code,
       a.description,
       a.type,
       j.journal_key,
       j.journal_trans_date,
       j.fixed_price_key transaction_data_key,
       null time_data_key,
       null expense_data_allocation_key,
       j.post_history_key,
       j.invoice_key,
       j.category,
       j.amount,
       j.extract_date,
       j.general_ledger_key,
       j.organization_key,
       porg.customer_code org_code,
       porg.customer_name org_name,
       null person_key,
       fp.description item_description,
       case
         when i.voided_invoice_key is not null then -invcd.writeoff_amount
         else invcd.writeoff_amount
       end writeoff_amount,
       null writeoff_quantity,
       null writeoff_bill_rate,
       null writeoff_cost,
       null writeoff_markup
  from journal_fixed_price j
       join account a on a.account_key = j.account_key
       join fixed_price fp on fp.fixed_price_key = j.fixed_price_key
       join project pr on pr.project_key = j.project_key
       join customer prc on prc.customer_key = pr.customer_key
       left outer join invcd_fixed_price invcd on invcd.fixed_price_key = j.fixed_price_key and invcd.invoice_key = j.invoice_key
       left outer join invoice i on i.invoice_key = j.invoice_key
       left outer join customer porg on porg.customer_key = j.organization_key
       left outer join task t on t.task_key = fp.task_key
union all
select 'P' journal_type,
       prc.customer_key project_customer_key,
       prc.customer_code project_customer_code,
       pr.project_key project_key,
       pr.project_code project_code,
       null task_key,
       null task_name,
       a.account_key,
       a.account_code,
       a.description,
       a.type,
       j.journal_key,
       j.journal_trans_date,
       j.prebilled_labor_key transaction_data_key,
       null time_data_key,
       null expense_data_allocation_key,
       j.post_history_key,
       j.invoice_key,
       j.category,
       j.amount,
       j.extract_date,
       j.general_ledger_key,
       j.organization_key,
       porg.customer_code org_code,
       porg.customer_name org_name,
       null person_key,
       pl.description item_description,
       null writeoff_amount,
       null writeoff_quantity,
       null writeoff_bill_rate,
       null writeoff_cost,
       null writeoff_markup
  from journal_prebilled_labor j
       join account a on a.account_key = j.account_key
       join prebilled_labor pl on pl.prebilled_labor_key = j.prebilled_labor_key
       join project pr on pr.project_key = j.project_key
       join customer prc on prc.customer_key = pr.customer_key
       left outer join invoice i on i.invoice_key = j.invoice_key
       left outer join customer porg on porg.customer_key = j.organization_key
union all
select 'O' journal_type,
       prc.customer_key project_customer_key,
       prc.customer_code project_customer_code,
       pr.project_key project_key,
       pr.project_code project_code,
       t.task_key,
       t.task_name,
       a.account_key,
       a.account_code,
       a.description,
       a.type,
       j.journal_key,
       j.journal_trans_date,
       j.onetime_charge_key transaction_data_key,
       null time_data_key,
       null expense_data_allocation_key,
       null post_history_key,
       oc.invoice_key,
       j.category,
       j.amount,
       j.extract_date,
       j.general_ledger_key,
       j.organization_key,
       porg.customer_code org_code,
       porg.customer_name org_name,
       null person_key,
       oc.description item_description,
       null writeoff_amount,
       null writeoff_quantity,
       null writeoff_bill_rate,
       null writeoff_cost,
       null writeoff_markup
  from journal_onetime_charge j
       join account a on a.account_key = j.account_key
       join onetime_charge oc on oc.onetime_charge_key = j.onetime_charge_key
       join project pr on pr.project_key = j.project_key
       join customer prc on prc.customer_key = pr.customer_key
       left outer join customer porg on porg.customer_key = j.organization_key
       left outer join task t on t.task_key = oc.task_key
union all
select 'D' journal_type,
       prc.customer_key project_customer_key,
       prc.customer_code project_customer_code,
       pr.project_key project_key,
       pr.project_code project_code,
       t.task_key task_key,
       t.task_name task_name,
       j.account_key,
       a.account_code,
       a.description,
       a.type,
       j.journal_key,
       j.journal_trans_date,
       j.fin_document_detail_key transaction_data_key,
       null time_data_key,
       null expense_data_allocation_key,
       j.post_history_key,
       j.invoice_key,
       j.category,
       j.amount,
       j.extract_date,
       j.general_ledger_key,
       j.organization_key,
       porg.customer_code org_code,
       porg.customer_name org_name,
       fdd.person_key,
       fdd.description item_description,
       case
         when i.voided_invoice_key is not null then -invcd.writeoff_amount
         else invcd.writeoff_amount
       end writeoff_amount,
       null writeoff_quantity,
       null writeoff_bill_rate,
       case
         when i.voided_invoice_key is not null then -invcd.writeoff_cost
         else invcd.writeoff_cost
       end writeoff_cost,
       invcd.writeoff_markup
  from journal_document_expense j
       join fin_document_detail fdd on fdd.fin_document_detail_key = j.fin_document_detail_key
       join fin_document_detail_project fddp on fddp.fin_document_detail_key = fdd.fin_document_detail_key
       join account a on a.account_key = j.account_key
       join project pr on pr.project_key = j.project_key
       join customer prc on prc.customer_key = pr.customer_key
       left outer join invcd_document_expense invcd on invcd.fin_document_detail_key = j.fin_document_detail_key and invcd.invoice_key = j.invoice_key
       left outer join invoice i on i.invoice_key = j.invoice_key
       left outer join customer porg on porg.customer_key = j.organization_key
       left outer join task t on t.task_key = fddp.task_key
union all
select 'FE' journal_type,
       prc.customer_key project_customer_key,
       prc.customer_code project_customer_code,
       pr.project_key project_key,
       pr.project_code project_code,
       null task_key,
       null task_name,
       a.account_key,
       a.account_code,
       a.description,
       a.type,
       j.journal_key,
       j.journal_trans_date,
       j.fee_cap_adjustment_key transaction_data_key,
       null time_data_key,
       null expense_data_allocation_key,
       j.post_history_key,
       j.invoice_key,
       j.category,
       j.amount,
       j.extract_date,
       j.general_ledger_key,
       j.organization_key,
       porg.customer_code org_code,
       porg.customer_name org_name,
       null person_key,
       null item_description,
       case
         when i.voided_invoice_key is not null then -invcd.writeoff_amount
         else invcd.writeoff_amount
       end writeoff_amount,
       null writeoff_quantity,
       null writeoff_bill_rate,
       null writeoff_cost,
       null writeoff_markup
  from journal_fee_cap_adjustment j
       join account a on a.account_key = j.account_key
       join project pr on pr.project_key = j.project_key
       join customer prc on prc.customer_key = pr.customer_key
       left outer join invcd_fee_cap_adjustment invcd on invcd.fee_cap_adjustment_key = j.fee_cap_adjustment_key and invcd.invoice_key = j.invoice_key
       left outer join invoice i on i.invoice_key = j.invoice_key
       left outer join customer porg on porg.customer_key = j.organization_key
union all
select 'FC' journal_type,
       prc.customer_key project_customer_key,
       prc.customer_code project_customer_code,
       pr.project_key project_key,
       pr.project_code project_code,
       t.task_key,
       t.task_name,
       a.account_key,
       a.account_code,
       a.description,
       a.type,
       j.journal_key,
       j.journal_trans_date,
       j.funding_cap_adjustment_key transaction_data_key,
       null time_data_key,
       null expense_data_allocation_key,
       j.post_history_key,
       j.invoice_key,
       j.category,
       j.amount,
       j.extract_date,
       j.general_ledger_key,
       j.organization_key,
       porg.customer_code org_code,
       porg.customer_name org_name,
       null person_key,
       null item_description,
       case
         when i.voided_invoice_key is not null then -invcd.writeoff_amount
         else invcd.writeoff_amount
       end writeoff_amount,
       null writeoff_quantity,
       null writeoff_bill_rate,
       null writeoff_cost,
       null writeoff_markup
  from journal_funding_cap_adjustment j
       join account a on a.account_key = j.account_key
       join funding_cap_adjustment fca on fca.funding_cap_adjustment_key = j.funding_cap_adjustment_key
       join project pr on pr.project_key = j.project_key
       join customer prc on prc.customer_key = pr.customer_key
       left outer join invcd_funding_cap_adjustment invcd on invcd.funding_cap_adjustment_key = j.funding_cap_adjustment_key and invcd.invoice_key = j.invoice_key
       left outer join invoice i on i.invoice_key = j.invoice_key
       left outer join customer porg on porg.customer_key = j.organization_key
       left outer join task t on t.task_key = fca.task_key