SELECT
hz_p.party_name customer_name,
hz_p.party_number customer_number,
ag.agreement_id,
SUBSTR(ag.agreement_num,1,20) agreement_number,
ag.agreement_type,
ag.amount revenue_amt,
ag.expiration_date,
f.meaning revenue_limit,
ag.amount - sum(nvl(pfs.total_baselined_amount,0)) amount_not_allocated,
sum(nvl(pfs.total_accrued_amount,0)) revenue_accrued,
sum(nvl(pfs.total_billed_amount,0)) amount_invoiced,
sum(nvl(pfs.total_baselined_amount,0)) - sum(nvl(pfs.total_accrued_amount,0)) revenue_backlog,
ag.agreement_currency_code agreement_currency_code,
substr(hr.name,1,60) name,
f1.meaning invoice_limit
FROM
fnd_lookups f,
fnd_lookups f1,
pa_summary_project_fundings pfs,
pa_agreements ag,
hz_parties hz_p,
hz_cust_accounts hz_c,
hr_organization_units hr
WHERE
hz_c.cust_account_id = nvl(:cust, hz_c.cust_account_id)
and hz_c.party_id = hz_p.party_id
and f.lookup_type = 'YES_NO'
and f.lookup_code = ag.revenue_limit_flag
and f1.lookup_type = 'YES_NO'
and f1.lookup_code = ag.invoice_limit_flag
and ag.owning_organization_id = hr.organization_id(+)
and hz_c.cust_account_id = ag.customer_id
and ag.agreement_num = nvl(:agree, ag.agreement_num)
and ag.agreement_id = pfs.agreement_id
GROUP BY
hz_p.party_name,
hz_p.party_number,
ag.agreement_id,
ag.agreement_num,
ag.agreement_type,
ag.amount,
ag.expiration_date,
f.meaning,
ag.agreement_currency_code,
hr.name,
f1.meaning
order by customer_name,customer_number,agreement_currency_code, hz_p.party_name,
decode(:sort,'EXPIRATION DATE', ag.expiration_date),
decode(:sort,'AGREEMENT NUMBER',ag.agreement_num),
decode(:sort,
'REVENUE LIMIT', ag.amount,
'AMOUNT NOT ALLOCATED', ag.amount - sum(nvl(pfs.total_baselined_amount,0)),
'REVENUE BACKLOG', sum(nvl(pfs.total_baselined_amount,0)) - sum(nvl(pfs.total_accrued_amount,0))) |
