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_det_expense_key
join fin_document_det_proj_expense 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 '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,
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 fin_document_detail fdd on fdd.fin_document_detail_key = cppd.fin_document_det_labor_key
join fin_document_det_proj_labor 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 '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,
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 fin_document_detail fdd on fdd.fin_document_detail_key = cppd.fin_document_det_item_key
join fin_document_det_proj_item 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_det_proj_expense 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
union all
select 'DL' 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,
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_document_labor j
join fin_document_detail fdd on fdd.fin_document_detail_key = j.fin_document_detail_key
join fin_document_det_proj_labor fddpl on fddpl.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_labor 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 = fddpl.task_key
union all
select 'DI' 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,
case
when i.voided_invoice_key is not null then -invcd.writeoff_quantity
else invcd.writeoff_quantity
end writeoff_quantity,
invcd.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_item j
join fin_document_detail fdd on fdd.fin_document_detail_key = j.fin_document_detail_key
join fin_document_det_proj_item fddpi on fddpi.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_item 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 = fddpi.task_key
|