PA Revenue, Cost, Budgets by Resources (Project Level)

select
distinct
  substr(p.organization_name,1,60)  project_organization,  
  p.manager_name project_manager,
  p.project_id project_id7,
  p.project_type,
  p.project_number,
  p.project_name,
  NVL(pr.retn_accounting_flag,'N') retn_accounting_flag, 
  iv.total_revenue_amount,
   iv.total_invoiced_amount,
  f.meaning costs_burdened,
  pr.projfunc_currency_code, 
 iv.write_off_invoice_amount, 
iv.pfc_invoiced_amount,
iv.pfc_total_invoice_amount,
iv.pfc_write_off_invoice_amount,
iv.unbilled_retention, 
	PA_PAXMGPSD_XMLP_PKG.unbilled_receivablesformula(NVL ( pr.retn_accounting_flag , 'N' ), iv.total_revenue_amount, iv.pfc_total_invoice_amount, iv.unbilled_retention) Unbilled_Receivables, 
	PA_PAXMGPSD_XMLP_PKG.unearned_revenueformula(NVL ( pr.retn_accounting_flag , 'N' ), iv.pfc_total_invoice_amount, iv.total_revenue_amount, iv.unbilled_retention) Unearned_Revenue, 
	PA_PAXMGPSD_XMLP_PKG.unbilled_retnformula(NVL ( pr.retn_accounting_flag , 'N' ), iv.unbilled_retention) unbilled_retn
from
  pa_project_types pt,
  pa_proj_invoice_summary_view iv,
  pa_proj_info_view p,
  pa_projects pr,
   fnd_lookups f
where
        pr.template_flag != 'Y'
and pr.project_id = p.project_id
and p.project_id = iv.project_id (+)
and pt.burden_cost_flag = f.lookup_code
and f.lookup_type = 'YES_NO' 
and pt.project_type  = p.project_type
&p_project_type
&p_project
&p_project_manager
and nvl(p.organization_id,-999) =
       nvl(:org_id,nvl(p.organization_id,-999))
and pr.project_id in (select P.project_id from pa_projects P, pa_project_status_controls S
                                 where P.project_status_code = S.project_status_code and
                                       S.status_type = 'PROJECT' and
                                       S.action_code = 'STATUS_REPORTING' and
                                       S.enabled_flag='Y')
order by
project_organization, p.manager_name, p.project_number