PA Agreement Status By Customer
Description
Categories: BI Publisher
Application: Projects
Source: MGT: Agreement Status By Customer (XML)
Short Name: PAXAGAST_XML
DB package: PA_PAXAGAST_XMLP_PKG
Source: MGT: Agreement Status By Customer (XML)
Short Name: PAXAGAST_XML
DB package: PA_PAXAGAST_XMLP_PKG
Run
PA Agreement Status By Customer and other Oracle EBS reports with Blitz Report™ on our demo environment
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))) |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Sort By |
|
LOV Oracle | |
Customer Name |
|
LOV Oracle | |
Agreement Number |
|
LOV Oracle |