Burden Schedules in Oracle Projects Moduel

Project Burden Schedules - base table:

 

SELECT pirsab.ind_rate_sch_name

     , pirsab.creation_date

     , pirsab.start_date_active

     , pirsab.end_date_active

  FROM pa.pa_ind_rate_schedules_all_bg pirsab;

 

Usage against Project Headers

SELECT pirsab.ind_rate_sch_name

     , TRUNC(pirsab.creation_date) creation_date

     , TRUNC(pirsab.start_date_active) start_date_active

     , TRUNC(pirsab.end_date_active) end_date_active

     , tbl_ct.ct COUNT

     , tbl_ct_open.ct COUNT_OPEN

     , TRUNC(tbl_ct.latest) latest

  FROM pa.pa_ind_rate_schedules_all_bg pirsab

     , (  SELECT pabs2.ind_rate_sch_id

               , pabs2.ind_rate_sch_name

               , COUNT (*) ct

               , MAX (ppa.creation_date) latest

            FROM pa.pa_projects_all ppa

               , pa.pa_ind_rate_schedules_all_bg pabs2

               , pa.pa_project_statuses pps

           WHERE pabs2.ind_rate_sch_id = ppa.cost_ind_rate_sch_id

             AND ppa.project_status_code = pps.project_status_code

        GROUP BY pabs2.ind_rate_sch_id

               , pabs2.ind_rate_sch_name) tbl_ct

     , (  SELECT pabs2.ind_rate_sch_id

               , pabs2.ind_rate_sch_name

               , COUNT (*) ct

               , MAX (ppa.creation_date) latest

            FROM pa.pa_projects_all ppa

               , pa.pa_ind_rate_schedules_all_bg pabs2

               , pa.pa_project_statuses pps

           WHERE pabs2.ind_rate_sch_id = ppa.cost_ind_rate_sch_id

             AND pps.project_status_name NOT LIKE '%Closed%'

             AND ppa.project_status_code = pps.project_status_code

        GROUP BY pabs2.ind_rate_sch_id

               , pabs2.ind_rate_sch_name) tbl_ct_open              

 WHERE pirsab.ind_rate_sch_id = tbl_ct.ind_rate_sch_id(+)

   AND pirsab.ind_rate_sch_id = tbl_ct_open.ind_rate_sch_id(+)

ORDER BY 1;

 

Usage against Project Tasks

SELECT pirsab.ind_rate_sch_name

     , TRUNC(pirsab.creation_date) creation_date

     , TRUNC(pirsab.start_date_active) start_date_active

     , TRUNC(pirsab.end_date_active) end_date_active

     , tbl_ct.ct COUNT

     , tbl_ct_open.ct COUNT_OPEN

     , TRUNC(tbl_ct.latest) latest

  FROM pa.pa_ind_rate_schedules_all_bg pirsab

     , (  SELECT pabs2.ind_rate_sch_id

               , pabs2.ind_rate_sch_name

               , COUNT (*) ct

               , MAX (pt.creation_date) latest

            FROM pa.pa_tasks pt

               , pa.pa_projects_all ppa

               , pa.pa_project_statuses pps

               , pa.pa_ind_rate_schedules_all_bg pabs2

           WHERE pabs2.ind_rate_sch_id = pt.cost_ind_rate_sch_id

             AND pt.project_id = ppa.project_id

             AND ppa.project_status_code = pps.project_status_code

        GROUP BY pabs2.ind_rate_sch_id

               , pabs2.ind_rate_sch_name) tbl_ct

     , (  SELECT pabs2.ind_rate_sch_id

               , pabs2.ind_rate_sch_name

               , COUNT (*) ct

               , MAX (pt.creation_date) latest

            FROM pa.pa_tasks pt

               , pa.pa_projects_all ppa

               , pa.pa_project_statuses pps

               , pa.pa_ind_rate_schedules_all_bg pabs2

           WHERE pabs2.ind_rate_sch_id = pt.cost_ind_rate_sch_id

             AND pt.project_id = ppa.project_id

             AND pps.project_status_name NOT LIKE '%Closed%'

             AND ppa.project_status_code = pps.project_status_code

        GROUP BY pabs2.ind_rate_sch_id

               , pabs2.ind_rate_sch_name) tbl_ct_open              

 WHERE pirsab.ind_rate_sch_id = tbl_ct.ind_rate_sch_id(+)

   AND pirsab.ind_rate_sch_id = tbl_ct_open.ind_rate_sch_id(+)

ORDER BY 1