Project Types in oracle apps

Project Types - base table:

 

SELECT ppta.project_type

     , ppta.creation_date

     , ppta.start_date_active

     , ppta.end_date_active

     , ppta.burden_cost_flag

  FROM pa.pa_project_types_all ppta;

 

Count summary

SELECT ppta.project_type project_type

       , COUNT (*) ct

       , MAX (ppa.creation_date) latest

    FROM pa.pa_projects_all ppa

       , pa.pa_project_statuses pps

       , pa.pa_project_types_all ppta

       , 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

GROUP BY ppta.project_type

ORDER BY 1;

 

Usage Stats

 

SELECT ppta.project_type

     , ppta.creation_date

     , ppta.start_date_active

     , ppta.end_date_active

     , ppta.burden_cost_flag

     , total_tbl.ct count_all

     , total_tbl_open.ct count_open    

     , total_tbl.latest

  FROM pa.pa_project_types_all ppta

     , (  SELECT ppta.project_type

               , COUNT (*) ct

               , MAX (ppa.creation_date) latest

            FROM pa.pa_projects_all ppa

               , pa.pa_project_statuses pps

               , pa.pa_project_types_all ppta

               , 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

        GROUP BY ppta.project_type) total_tbl

     , (  SELECT ppta.project_type

               , COUNT (*) ct

               , MAX (ppa.creation_date) latest

            FROM pa.pa_projects_all ppa

               , pa.pa_project_statuses pps

               , pa.pa_project_types_all ppta

               , 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 pps.project_status_name NOT LIKE '%Closed%'

        GROUP BY ppta.project_type) total_tbl_open

 WHERE ppta.project_type = total_tbl.project_type(+)

   AND ppta.project_type = total_tbl_open.project_type(+);