select
substr(p.organization_name,1,60) project_organization,
p.member_name,
p.project_role,
p.project_number,
p.project_name,
i.invoice_status,
i.draft_invoice_num invoice_number,
i.draft_invoice_num_credited credit_of,
i.bill_through_date bill_thru_date,
i.creation_date,
i.approved_date,
i.released_date,
i.days_awaiting_approval awaiting_approval,
i.invoice_amount amount,
i.invoice_curr_amount inv_amount,
i.invoice_currency_code
from
pa_proj_members_view p,
pa_org_reporting_view vw,
pa_proj_invoices_view i,
pa_lookups lk
where
p.organization_id = vw.organization_id
and nvl(:project_role_type ,'PROJECT MANAGER') =
p.project_role_type
and nvl(:project_member,p.member_person_id) =
p.member_person_id
and p.project_id =i.project_id
and i.invoice_amount between :low_range and :high_range
and decode(:invoice_status,
null,lk.lookup_code,
'ALL',lk.lookup_code,
:invoice_status)=
lk.lookup_code
and lk.lookup_type='INVOICE STATUS'
and lk.meaning(+)=i.invoice_status
and nvl(trunc(i.creation_date),sysdate) between nvl(:creation_date_from,nvl(trunc(i.creation_date),sysdate-1))
and nvl(:creation_date_to,nvl(trunc(i.creation_date),sysdate+1))
and decode(:include_released_status,'N',released_date,null)
is null
order by p.organization_name,p.member_name,
i.invoice_status,p.project_number,i.draft_invoice_num |