PA Task - Revenue, Cost, Budgets by Resources

Description
Categories: BI Publisher, Financials
Application: Projects
Source: MGT: Task - Revenue, Cost, Budgets by Resources (XML)
Short Name: PAXMGTSD_XML
DB package: PA_PAXMGTSD_XMLP_PKG
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
Revenue Budget Type
 
LOV Oracle
Cost Budget Type
 
LOV Oracle
Task Number
 
LOV Oracle
Project Number
 
LOV Oracle
Task Manager
 
LOV Oracle
Task Organization
 
LOV Oracle
Period Name
 
LOV Oracle
Ask a question