create view po_expnse_consol_view
as
select
po.po_key as current_po_key,
descr.line_id,
descr.po_expense_line_descriptor_key,
descr.orig_po_key,
descr.po_key owning_po_key,
descr.pr_expense_line_descriptor_key,
descr.account_key,
descr.organization_key,
descr.reference,
descr.description,
descr.project_key,
descr.task_key,
descr.expense_type_key,
descr.person_key,
descr.closed_date,
line.po_expense_line_key,
line.po_key line_po_key,
line.begin_date,
line.end_date,
line.required_by_date,
line.internal_comments,
line.external_comments,
line.vi_overage,
summary.amount
from purchase_order po
join (
select history.po_key,
line.po_expense_line_descriptor_key,
max(history.mod_number) mod_number,
max(case when history.mod_number is null then history.document_number else null end) document_number,
sum(line.amount) amount
from po_expense_line line
join (
select po.po_key po_key,
po.pr_key,
history.po_key predecessor_key,
history.mod_number,
history.document_number
from purchase_order po
join purchase_order history on history.orig_po_key = po.orig_po_key
where (
po.po_key = history.po_key
or (
po.status in ('APPROVED', 'OPEN', 'CLOSED')
and history.status in ('APPROVED', 'OPEN', 'CLOSED')
and history.mod_number < po.mod_number
)
or (
po.status not in ('APPROVED', 'OPEN', 'CLOSED')
and history.status in ('APPROVED', 'OPEN', 'CLOSED')
)
)
) history on history.predecessor_key = line.po_key
group by history.po_key,
line.po_expense_line_descriptor_key
) summary on summary.po_key = po.po_key
join purchase_order po_family on po_family.orig_po_key = po.orig_po_key
and (
po_family.document_number = summary.document_number
or (
po_family.mod_number = summary.mod_number
and summary.document_number is null
)
)
join po_expense_line line on line.po_expense_line_descriptor_key = summary.po_expense_line_descriptor_key
and line.po_key = po_family.po_key
join po_expense_line_descriptor descr on descr.po_expense_line_descriptor_key = summary.po_expense_line_descriptor_key
|