PA Revenue, Cost, Budgets by Work Breakdown Structure

Description
Categories: BI Publisher, Financials
Application: Projects
Source: MGT: Revenue, Cost, Budgets by Work Breakdown Structure (XML)
Short Name: PAXBUBSS_XML
DB package: PA_PAXBUBSS_XMLP_PKG
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
Parameter Name SQL text Validation
Revenue Budget Type
 
LOV Oracle
Cost Budget Type
 
LOV Oracle
Explode Subtasks
 
LOV Oracle
Top Task
 
LOV Oracle
Project Type
 
LOV Oracle
Project Number
 
LOV Oracle
Project Manager
 
LOV Oracle
Project Organization
 
LOV Oracle