Project Template Count Summary in oracle apps

Project Template Query in oracle apps

 

 SELECT ppa_template.segment1 template_project_num

       , ppa_template.creation_date template_creation_date

       , COUNT (*) ct

       , MAX (ppa.creation_date) last_used

    FROM pa.pa_projects_all ppa_template

       , pa.pa_projects_all ppa

       , pa.pa_project_statuses pps

   WHERE ppa_template.project_id = ppa.created_from_project_id

     AND ppa.project_status_code = pps.project_status_code

     AND ppa_template.template_flag = 'Y'

GROUP BY ppa_template.segment1

       , ppa_template.creation_date;