HR Orgs Basic - Project Orgs linked to Projects

Project Orgs linked to Projects

SELECT *

  FROM apps.pa_organizations_v

 WHERE EXISTS

          (SELECT ppa.project_id

             FROM pa.pa_projects_all ppa

            WHERE ppa.carrying_out_organization_id =

                     pa_organizations_v.organization_id);

 

 

Count of Projects by Carrying out Org

 

  SELECT pov1.name

       , COUNT (ppa.project_id)

    FROM pa.pa_projects_all ppa

       , apps.pa_organizations_v pov1

   WHERE ppa.carrying_out_organization_id = pov1.organization_id

     AND ppa.carrying_out_organization_id IN

            (SELECT pov.organization_id

               FROM apps.pa_organizations_v pov)

GROUP BY pov1.name;

 

 

  another version

 

 

  SELECT haou.name org

       , COUNT(*) 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

       , applsys.fnd_user fu

       , hr.hr_all_organization_units haou

   WHERE 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 ppa.cost_ind_rate_sch_id = pirsa.ind_rate_sch_id(+)

GROUP BY haou.name

ORDER BY 2 DESC;