SELECT ppa.segment1
, COUNT (DISTINCT pt.task_id) task_ct
, COUNT (DISTINCT pt.service_type_code) svc_type_ct
FROM pa.pa_projects_all ppa
, pa.pa_projects_all ppa_template
, pa.pa_project_statuses pps
, pa.pa_project_types_all ppta
, pa.pa_ind_rate_schedules_all_bg pirsa
, pa.pa_tasks pt
, applsys.fnd_user fu
, hr.hr_all_organization_units haou
WHERE ppa.project_id = pt.project_id
AND ppa.created_by = fu.user_id
AND ppa.carrying_out_organization_id = haou.organization_id
AND ppa.project_status_code = pps.project_status_code
AND ppa.project_type = ppta.project_type
AND ppa.created_from_project_id = ppa_template.project_id(+)
AND pt.cost_ind_rate_sch_id = pirsa.ind_rate_sch_id(+)
GROUP BY ppa.segment1
ORDER BY 3 DESC;
Usage Stats
SELECT DISTINCT pt.service_type_code
, total_tbl.latest last_used
, total_tbl.cta count_tasks_all
, total_tbl.cpa count_projects_all
, total_tbl_open.cto count_tasks_open
, total_tbl_open.cpo count_projects_open
FROM pa.pa_projects_all ppa
, pa.pa_tasks pt
, ( SELECT pt.service_type_code
, COUNT (DISTINCT pt.task_id) cta
, COUNT (DISTINCT ppa.project_id) cpa
, MAX (ppa.creation_date) latest
FROM pa.pa_projects_all ppa
, pa.pa_tasks pt
, pa.pa_project_statuses pps
WHERE ppa.project_id = pt.project_id
AND ppa.project_status_code = pps.project_status_code
GROUP BY pt.service_type_code) total_tbl
, ( SELECT pt.service_type_code
, COUNT (DISTINCT pt.task_id) cto
, COUNT (DISTINCT ppa.project_id) cpo
FROM pa.pa_projects_all ppa
, pa.pa_tasks pt
, pa.pa_project_statuses pps
WHERE ppa.project_id = pt.project_id
AND ppa.project_status_code = pps.project_status_code
AND pps.project_status_name NOT LIKE '%Closed%'
GROUP BY pt.service_type_code) total_tbl_open
WHERE ppa.project_id = pt.project_id
AND pt.service_type_code = total_tbl_open.service_type_code
AND pt.service_type_code = total_tbl.service_type_code
ORDER BY pt.service_type_code;
No comments:
Post a Comment