Oracle Project Headers and Tasks Details

Project Headers

 

SELECT ppa.segment1 pnum

       , fu.description created_by

       , ppa.creation_date

       , ppa.name

       , ppa_template.name template_name

       , pps.project_status_name status

       , ppta.project_type project_type

       , ppta.burden_cost_flag allow_burdening

       , haou.name org

       , ppa.distribution_rule

       , ppa.start_date

       , ppa.completion_date

       , ppa.long_name

       , pirsa.ind_rate_sch_name proj_level_burden_schedule

    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(+)

     AND SYSDATE BETWEEN ppa.start_date AND ppa.completion_date -- only return active projects

     AND pps.project_status_name NOT LIKE '%Close%'             -- only return open projects

     AND ppa.creation_date >= '31-JAN-2012'

ORDER BY ppa.creation_date DESC;

 

Project Task Details

 

SELECT ppa.segment1 pnum

       , fu.description created_by

       , ppa.creation_date

       , ppa.name

       , pt.task_number

       , pt.task_name

       , pt.chargeable_flag

       , pirsa.ind_rate_sch_name task_level_burden_schedule

       , ppa_template.name template_name

       , pps.project_status_name status

       , ppta.project_type project_type

       , haou.name org

       , ppa.distribution_rule

       , ppa.start_date

       , ppa.completion_date

       , ppa.long_name

       , pt.service_type_code

    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(+)

     AND SYSDATE BETWEEN ppa.start_date AND ppa.completion_date -- only return active projects

     AND SYSDATE BETWEEN pt.start_date AND pt.completion_date   -- only return active tasks

     AND pps.project_status_name NOT LIKE '%Clos%'              -- only return open projects

     AND ppa.segment1 = :projectnum

ORDER BY ppa.segment1

       , pt.task_number;