View: po_expnse_consol_all_view

create view po_expnse_consol_all_view
as
select
    descr.line_id,
    descr.po_expense_line_descriptor_key,
    line.po_expense_line_key,
    descr.orig_po_key,
    line.po_key as line_po_key,
    descr.po_key as owning_po_key,
    descr.pr_expense_line_descriptor_key,
    po.pr_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.begin_date,
    line.end_date,
    line.required_by_date,
    line.internal_comments,
    line.external_comments,
    line.vi_overage,
    summary.amount
from po_expense_line_descriptor descr
join po_expense_line line on line.po_expense_line_descriptor_key = descr.po_expense_line_descriptor_key
join purchase_order po on po.po_key = line.po_key
join (
    select line.po_expense_line_descriptor_key,
        max(po.mod_number) mod_number,
        max(case when po.mod_number is null then document_number else null end) document_number,
        sum(line.amount) amount
    from po_expense_line line
    join purchase_order po on po.po_key = line.po_key
        and po.status != 'CANCELED'
    group by line.po_expense_line_descriptor_key
    ) summary on summary.po_expense_line_descriptor_key = line.po_expense_line_descriptor_key
where summary.mod_number = po.mod_number
    and (
        (
            summary.document_number is null
            and summary.mod_number = po.mod_number
            )
        or summary.document_number = po.document_number
        )