PA Task - Revenue, Cost, Budgets by Resources

select
  substr(org.name,1,60) task_organization, 
  decode(t.task_manager_person_id,per.person_id,per.full_name,null,
   'No Task Manager') task_manager,
   t.task_id,
   t.task_number,
   t.task_name,
   t.parent_task_id,
   p.project_id project_id2,
   p.segment1 project_number,
   p.name project_name,
   f.meaning costs_burdened,
   p.project_currency_code,
   p.projfunc_currency_code, 
	PA_PAXMGTSD_XMLP_PKG.c_sum_rpt_totalsformula(t.parent_task_id) C_sum_rpt_totals,
	PA_PAXMGTSD_XMLP_PKG.CHECK_SUM_RPT_TOTALS(t.parent_task_id)
from
  pa_project_types pt,
  pa_projects p,
  per_people_f per,
  hr_all_organization_units_tl org,
  pa_tasks t,
   fnd_lookups f
where
       pt.burden_cost_flag = f.lookup_code
and f.lookup_type = 'YES_NO' 
and pt.project_type  = p.project_type
and p.template_flag != 'Y'
and p.project_id = t.project_id 
and p.project_id = :proj
and t.task_id = nvl(:task, t.task_id)
and t.task_manager_person_id = per.person_id (+)
and   sysdate between per.effective_start_date (+)
					 and  nvl(per.effective_end_date (+),sysdate + 1) 
and   per.employee_number (+) IS NOT NULL
and t.carrying_out_organization_id = org.organization_id
and org.language = userenv('lang')
and nvl(t.task_manager_person_id,-999) =
      nvl(:task_mgr,nvl(t.task_manager_person_id,-999))
and t.carrying_out_organization_id =
       nvl(:task_org,t.carrying_out_organization_id)
and :proj IS NOT NULL
and p.project_id = (select P.project_id from pa_projects P, pa_project_status_controls S
                                 where  P.project_id = :proj and
                                       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
  substr(org.name,1,60) task_organization, 
  decode(t.task_manager_person_id,per.person_id,per.full_name,null,
   'No Task Manager') task_manager,
   t.task_id,
   t.task_number,
   t.task_name,
   t.parent_task_id,
   p.project_id project_id2,
   p.segment1 project_number,
   p.name project_name,
   f.meaning costs_burdened,
   p.project_currency_code,
   p.projfunc_currency_code,
   	PA_PAXMGTSD_XMLP_PKG.c_sum_rpt_totalsformula(t.parent_task_id) C_sum_rpt_totals,
	PA_PAXMGTSD_XMLP_PKG.CHECK_SUM_RPT_TOTALS(t.parent_task_id)
from
  pa_project_types pt,
  pa_project_status_controls S,
  pa_projects p,
  per_people_f per,
  hr_all_organization_units_tl org,
  pa_tasks t,
   fnd_lookups f
where
       pt.burden_cost_flag = f.lookup_code
and f.lookup_type = 'YES_NO' 
and pt.project_type  = p.project_type
and p.template_flag != 'Y'
and p.project_id = t.project_id 
and t.task_id = nvl(:task, t.task_id)
and t.task_manager_person_id = per.person_id (+)
and   sysdate between per.effective_start_date (+)
					 and  nvl(per.effective_end_date (+),sysdate + 1) 
and   per.employee_number (+) IS NOT NULL
and t.carrying_out_organization_id = org.organization_id
and org.language = userenv('lang')
and nvl(t.task_manager_person_id,-999) =
      nvl(:task_mgr,nvl(t.task_manager_person_id,-999))
and t.carrying_out_organization_id =:task_org
and :proj IS NULL 
and :task_org IS NOT NULL
and 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
  substr(org.name,1,60) task_organization, 
  decode(t.task_manager_person_id,per.person_id,per.full_name,null,
   'No Task Manager') task_manager,
   t.task_id,
   t.task_number,
   t.task_name,
   t.parent_task_id,
   p.project_id project_id2,
   p.segment1 project_number,
   p.name project_name,
   f.meaning costs_burdened,
   p.project_currency_code,
   p.projfunc_currency_code,
   	PA_PAXMGTSD_XMLP_PKG.c_sum_rpt_totalsformula(t.parent_task_id) C_sum_rpt_totals,
	PA_PAXMGTSD_XMLP_PKG.CHECK_SUM_RPT_TOTALS(t.parent_task_id)
from
  pa_project_types pt,
 pa_project_status_controls S,
  pa_projects p,
  per_people_f per,
  hr_all_organization_units_tl org,
  pa_tasks t,
   fnd_lookups f
where
       pt.burden_cost_flag = f.lookup_code
and f.lookup_type = 'YES_NO' 
and pt.project_type  = p.project_type
and p.template_flag != 'Y'
and p.project_id = t.project_id 
and t.task_id = nvl(:task, t.task_id)
and t.task_manager_person_id = per.person_id (+)
and   sysdate between per.effective_start_date (+)
					 and  nvl(per.effective_end_date (+),sysdate + 1) 
and   per.employee_number (+) IS NOT NULL
and t.carrying_out_organization_id = org.organization_id (+)
and org.language = userenv('lang')
and nvl(t.task_manager_person_id,-999) =
      nvl(:task_mgr,nvl(t.task_manager_person_id,-999))
and :proj IS NULL 
and :task_org IS NULL
and 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  1, 2, 9, 4