View: pr_expnse_consol_approved_view

create view pr_expnse_consol_approved_view
as
select
    descr.line_id,
    descr.pr_expense_line_descriptor_key,
    line.pr_expense_line_key,
    descr.orig_pr_key,
    line.pr_key as line_pr_key,
    descr.pr_key as owning_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,
    line.begin_date,
    line.end_date,
    line.required_by_date,
    line.internal_comments,
    line.external_comments,
    summary.amount
from pr_expense_line_descriptor descr
join pr_expense_line line on line.pr_expense_line_descriptor_key = descr.pr_expense_line_descriptor_key
join purchase_requisition pr on pr.pr_key = line.pr_key
join (
    select line.pr_expense_line_descriptor_key,
        max(pr.mod_number) mod_number,
        sum(line.amount) amount
    from pr_expense_line line
    join purchase_requisition pr on pr.pr_key = line.pr_key
        and pr.status in ('APPROVED', 'OPEN', 'CLOSED')
    group by line.pr_expense_line_descriptor_key
    ) summary on summary.pr_expense_line_descriptor_key = line.pr_expense_line_descriptor_key
where summary.mod_number = pr.mod_number