Budget Entry Methods Usage against Projects

Project Budget Entry Methods - base table:

 SELECT * FROM pa.pa_budget_entry_methods;

 Query :

SELECT pbem.budget_entry_method_code

     , pbem.budget_entry_method

     , pbem.creation_date

     , pbem.start_date_active

     , pbem.end_date_active

     , tbl_ct.ct

     , tbl_ct_open.ct ct_open

     , tbl_ct.latest

  FROM pa.pa_budget_entry_methods pbem

     , (  SELECT pbem.budget_entry_method_code

               , COUNT (*) ct

               , MAX (pbv.creation_date) latest

            FROM pa.pa_budget_versions pbv

               , pa.pa_budget_entry_methods pbem

               , pa.pa_projects_all ppa

               , pa.pa_project_statuses pps

           WHERE pbv.budget_entry_method_code = pbem.budget_entry_method_code

             AND ppa.project_status_code = pps.project_status_code

             AND PBV.PROJECT_ID = ppa.project_id

             AND budget_type_code = 'AC'

             AND budget_status_code IN ('W', 'S')

             AND version_number = 1

        GROUP BY pbem.budget_entry_method_code) tbl_ct

     , (  SELECT pbem.budget_entry_method_code

               , COUNT (*) ct

               , MAX (pbv.creation_date) latest

            FROM pa.pa_budget_versions pbv

               , pa.pa_budget_entry_methods pbem

               , pa.pa_projects_all ppa

               , pa.pa_project_statuses pps

           WHERE pbv.budget_entry_method_code = pbem.budget_entry_method_code

             AND ppa.project_status_code = pps.project_status_code

             AND PBV.PROJECT_ID = ppa.project_id

             AND budget_type_code = 'AC'

             AND budget_status_code IN ('W', 'S')

             AND version_number = 1

             AND pps.project_status_name NOT LIKE '%Closed%'

        GROUP BY pbem.budget_entry_method_code) tbl_ct_open       

 WHERE pbem.budget_entry_method_code = tbl_ct.budget_entry_method_code(+)

   AND pbem.budget_entry_method_code = tbl_ct_open.budget_entry_method_code(+);

 

-- details against an individual project

 

  SELECT ppa.org_id

       , ppa.segment1

       , pbem.budget_entry_method

       , pbv.version_number

       , pbv.budget_status_code

    FROM pa.pa_budget_versions pbv

       , pa.pa_budget_entry_methods pbem

       , pa.pa_projects_all ppa

   WHERE ppa.project_id = pbv.project_id

     AND pbv.budget_entry_method_code = pbem.budget_entry_method_code

     AND segment1 = :projectnum

     AND budget_type_code = 'AC'

     AND budget_status_code IN ('W', 'S')

     AND version_number = 1

ORDER BY org_id

       , segment1;