PA Task - Revenue, Cost, Budgets by Resources

Description
Categories: BI Publisher
Application: Projects
Source: MGT: Task - Revenue, Cost, Budgets by Resources (XML)
Short Name: PAXMGTSD_XML
DB package: PA_PAXMGTSD_XMLP_PKG
Run PA Task - Revenue, Cost, Budgets by Resources and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Period Name
 
LOV Oracle
Task Organization
 
LOV Oracle
Task Manager
 
LOV Oracle
Project Number
 
LOV Oracle
Task Number
 
LOV Oracle
Cost Budget Type
 
LOV Oracle
Revenue Budget Type
 
LOV Oracle
Blitz Report™