PA Agreement Status By Customer

           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)))