PA Revenue, Cost, Budgets by Work Breakdown Structure

select 
 pi.project_id,
 substr(pi.organization_name,1,60) project_organization, 
 pi.manager_name project_manager,
 pi.project_type,
 pi.project_name,
 pi.project_number,
 iv.total_invoiced_amount,
 iv.total_revenue_amount,
 p.project_currency_code,
 p.projfunc_currency_code,
iv.pfc_total_invoice_amount,
iv.unbilled_retention,
NVL(p.retn_accounting_flag,'N') retn_accounting_flag, 
	PA_PAXBUBSS_XMLP_PKG.unearned_revformula(NVL ( p.retn_accounting_flag , 'N' ), iv.pfc_total_invoice_amount, iv.unbilled_retention, iv.total_revenue_amount) Unearned_rev, 
	PA_PAXBUBSS_XMLP_PKG.accounts_receivableformula(pi.project_id) Accounts_receivable, 
	PA_PAXBUBSS_XMLP_PKG.unbilled_recformula(NVL ( p.retn_accounting_flag , 'N' ), iv.total_revenue_amount, iv.pfc_total_invoice_amount, iv.unbilled_retention) Unbilled_rec, 
	PA_PAXBUBSS_XMLP_PKG.unbilled_retnformula(NVL ( p.retn_accounting_flag , 'N' ), iv.unbilled_retention) unbilled_retn
from 
  pa_proj_info_view pi,
  pa_projects p,
  pa_proj_invoice_summary_view iv
where
       p.project_id = :proj
and p.template_flag !='Y'
and p.project_id = pi.project_id
and nvl(pi.manager_person_id, -999) = nvl(:mgr, nvl(pi.manager_person_id,-999))
and p.carrying_out_organization_id = nvl(:org,p.carrying_out_organization_id)
and pi.project_type = nvl(:proj_type,pi.project_type)
and p.project_id = iv.project_id (+)
and iv.project_id (+) = :proj
and :proj IS NOT NULL
and p.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')
UNION ALL
select 
 pi.project_id,
 substr(pi.organization_name,1,60) project_organization, 
 pi.manager_name project_manager,
 pi.project_type,
 pi.project_name,
 pi.project_number,
 iv.total_invoiced_amount,
 iv.total_revenue_amount,
 p.project_currency_code,
 p.projfunc_currency_code,
iv.pfc_total_invoice_amount,
iv.unbilled_retention,
NVL(p.retn_accounting_flag,'N'), 
	PA_PAXBUBSS_XMLP_PKG.unearned_revformula(NVL ( p.retn_accounting_flag , 'N' ), iv.pfc_total_invoice_amount, iv.unbilled_retention, iv.total_revenue_amount) Unearned_rev, 
	PA_PAXBUBSS_XMLP_PKG.accounts_receivableformula(pi.project_id) Accounts_receivable, 
	PA_PAXBUBSS_XMLP_PKG.unbilled_recformula(NVL ( p.retn_accounting_flag , 'N' ), iv.total_revenue_amount, iv.pfc_total_invoice_amount, iv.unbilled_retention) Unbilled_rec, 
	PA_PAXBUBSS_XMLP_PKG.unbilled_retnformula(NVL ( p.retn_accounting_flag , 'N' ), iv.unbilled_retention) unbilled_retn
from 
  pa_proj_info_view pi,
  pa_projects p,
  pa_proj_invoice_summary_view iv
 where
    p.template_flag !='Y'
and p.project_id = pi.project_id
and nvl(pi.manager_person_id, -999) = nvl(:mgr, nvl(pi.manager_person_id,-999))
and p.carrying_out_organization_id = nvl(:org,p.carrying_out_organization_id)
and pi.project_type = nvl(:proj_type,pi.project_type)
and p.project_id = iv.project_id (+)
and :proj IS NULL
and p.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 2, 3, 6