Project Transaction Sources

-- ##############################################################################
--      TRANSACTION SOURCES
-- ##############################################################################

  SELECT pts.user_transaction_source
       , pts.transaction_source
       , pts.batch_size
       , pts.purgeable_flag
       , pts.allow_adjustments_flag
       , pts.gl_accounted_flag
       , pts.allow_duplicate_reference_flag
       , pts.modify_interface_flag
       , pts.creation_date
       , fu1.user_name created_by
       , pts.last_update_date
       , fu2.user_name updated_by
    FROM pa.pa_transaction_sources pts
    JOIN applsys.fnd_user fu1 ON pts.created_by =      fu1.user_id
    JOIN applsys.fnd_user fu2 ON pts.last_updated_by = fu2.user_id
--   WHERE lower(pts.user_transaction_source) like '%web%'
ORDER BY 2;

Project Service Types

-- ##############################################################################
--     PA SERVICE TYPES
-- ##############################################################################

-- PROJECT SERVICE TYPES (HELD AT TASK LEVEL > Task Options > Task Details)

-- grouped by service type code

      SELECT pt.service_type_code
           , COUNT (DISTINCT pt.task_id) task_ct
           , COUNT (DISTINCT ppa.project_id) project_ct
        FROM pa.pa_projects_all ppa
        JOIN pa.pa_projects_all ppa_template       ON ppa.created_from_project_id =      ppa_template.project_id
        JOIN pa.pa_project_statuses pps            ON ppa.project_status_code =          pps.project_status_code
        JOIN pa.pa_project_types_all ppta          ON ppa.project_type =                 ppta.project_type
        JOIN pa.pa_tasks pt                        ON ppa.project_id =                   pt.project_id
        JOIN applsys.fnd_user fu                   ON ppa.created_by =                   fu.user_id
        JOIN hr.hr_all_organization_units haou     ON ppa.carrying_out_organization_id = haou.organization_id
   LEFT JOIN pa.pa_ind_rate_schedules_all_bg pirsa ON pt.cost_ind_rate_sch_id =          pirsa.ind_rate_sch_id
       WHERE ppa.segment1 = 'PROJECT1'
    GROUP BY pt.service_type_code
    ORDER BY pt.service_type_code;

      SELECT pt.task_number
           , pt.service_type_code
           , pt.billable_flag
           , pt.chargeable_flag
        FROM pa.pa_projects_all ppa
        JOIN pa.pa_projects_all ppa_template       ON ppa.created_from_project_id =      ppa_template.project_id
        JOIN pa.pa_project_statuses pps            ON ppa.project_status_code =          pps.project_status_code
        JOIN pa.pa_project_types_all ppta          ON ppa.project_type =                 ppta.project_type
        JOIN pa.pa_tasks pt                        ON ppa.project_id =                   pt.project_id
        JOIN applsys.fnd_user fu                   ON ppa.created_by =                   fu.user_id
        JOIN hr.hr_all_organization_units haou     ON ppa.carrying_out_organization_id = haou.organization_id
   LEFT JOIN pa.pa_ind_rate_schedules_all_bg pirsa ON pt.cost_ind_rate_sch_id =          pirsa.ind_rate_sch_id
       WHERE ppa.segment1 = 'PROJECT1'
    ORDER BY pt.service_type_code;

-- ##############################################################################
--     COUNTS AGAINST SERVICE TYPES
-- ##############################################################################

  SELECT DISTINCT 
         pt.service_type_code
       , total_tbl.latest last_used
       , total_tbl.cta count_tasks_all
       , total_tbl.cpa count_projects_all
       , total_tbl_open.cto count_tasks_open
       , total_tbl_open.cpo count_projects_open
    FROM pa.pa_projects_all ppa
    JOIN pa.pa_tasks pt ON ppa.project_id = pt.project_id
    JOIN (  SELECT pt.service_type_code
                 , COUNT (DISTINCT pt.task_id) cta
                 , COUNT (DISTINCT ppa.project_id) cpa
                 , MAX (ppa.creation_date) latest
              FROM pa.pa_projects_all ppa
                 , pa.pa_tasks pt
                 , pa.pa_project_statuses pps
             WHERE ppa.project_id = pt.project_id
               AND ppa.project_status_code = pps.project_status_code
          GROUP BY pt.service_type_code) total_tbl ON pt.service_type_code = total_tbl.service_type_code
    JOIN (  SELECT pt.service_type_code
                 , COUNT (DISTINCT pt.task_id) cto
                 , COUNT (DISTINCT ppa.project_id) cpo
              FROM pa.pa_projects_all ppa
                 , pa.pa_tasks pt
                 , pa.pa_project_statuses pps
             WHERE ppa.project_id = pt.project_id
               AND ppa.project_status_code = pps.project_status_code
               AND pps.project_status_name NOT LIKE '%Closed%'
          GROUP BY pt.service_type_code) total_tbl_open ON pt.service_type_code = total_tbl_open.service_type_code
ORDER BY pt.service_type_code;

-- ##############################################################################
--     COUNT OF DISTINCT SERVICE TYPES PER PROJECT, HIGHEST FIRST
-- ##############################################################################

      SELECT ppa.segment1
           , COUNT (DISTINCT pt.task_id) task_ct
           , COUNT (DISTINCT pt.service_type_code) svc_type_ct
        FROM pa.pa_projects_all ppa
        JOIN pa.pa_projects_all ppa_template       ON ppa.created_from_project_id =      ppa_template.project_id
        JOIN pa.pa_project_statuses pps            ON ppa.project_status_code =          pps.project_status_code
        JOIN pa.pa_project_types_all ppta          ON ppa.project_type =                 ppta.project_type
        JOIN pa.pa_tasks pt                        ON ppa.project_id =                   pt.project_id
        JOIN applsys.fnd_user fu                   ON ppa.created_by =                   fu.user_id
        JOIN hr.hr_all_organization_units haou     ON ppa.carrying_out_organization_id = haou.organization_id
        JOIN pa.pa_ind_rate_schedules_all_bg pirsa ON pt.cost_ind_rate_sch_id =          pirsa.ind_rate_sch_id
    GROUP BY ppa.segment1
    ORDER BY 3 DESC;

Project Types

-- ##############################################################################
--     PROJECT TYPES - DETAILS
-- ##############################################################################

-- summary

  SELECT ppta.project_type project_type
       , ppta.service_type_code
       , tbl_svc_type.meaning svc_type
    FROM pa.pa_project_types_all ppta
    JOIN (select lookup_code, meaning from apps.pa_lookups where lookup_type = 'SERVICE TYPE') tbl_svc_type ON ppta.service_type_code = tbl_svc_type.lookup_code
ORDER BY 1;

-- ##############################################################################
--     PROJECT TYPES - SUMMARY VIEWS
-- ##############################################################################

-- summary

  SELECT ppta.project_type project_type
       , COUNT (*) ct
       , MAX (ppa.creation_date) latest
    FROM pa.pa_projects_all ppa
    JOIN pa.pa_project_statuses pps        ON ppa.project_status_code =          pps.project_status_code
    JOIN pa.pa_project_types_all ppta      ON ppa.project_type =                 ppta.project_type
    JOIN applsys.fnd_user fu               ON ppa.created_by =                   fu.user_id
    JOIN hr.hr_all_organization_units haou ON ppa.carrying_out_organization_id = haou.organization_id
GROUP BY ppta.project_type
ORDER BY 1;

-- more detailed summary view

    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
 LEFT JOIN (  SELECT ppta.project_type
                   , COUNT (*) ct
                   , MAX (ppa.creation_date) latest
                FROM pa.pa_projects_all ppa
                JOIN pa.pa_project_statuses pps        ON ppa.project_status_code =          pps.project_status_code
                JOIN pa.pa_project_types_all ppta      ON ppa.project_type =                 ppta.project_type
                JOIN applsys.fnd_user fu               ON ppa.created_by =                   fu.user_id
                JOIN hr.hr_all_organization_units haou ON ppa.carrying_out_organization_id = haou.organization_id
            GROUP BY ppta.project_type) total_tbl ON ppta.project_type = total_tbl.project_type
 LEFT JOIN (  SELECT ppta.project_type
                   , COUNT (*) ct
                   , MAX (ppa.creation_date) latest
                FROM pa.pa_projects_all ppa
                JOIN pa.pa_project_statuses pps        ON ppa.project_status_code =          pps.project_status_code
                JOIN pa.pa_project_types_all ppta      ON ppa.project_type =                 ppta.project_type
                JOIN applsys.fnd_user fu               ON ppa.created_by =                   fu.user_id
                JOIN hr.hr_all_organization_units haou ON ppa.carrying_out_organization_id = haou.organization_id
                 AND pps.project_status_name NOT LIKE '%Closed%'
            GROUP BY ppta.project_type) total_tbl_open ON ppta.project_type = total_tbl_open.project_type
       ORDER BY 1;

       SELECT ppta.project_type
            , ppta.creation_date
            , ppta.start_date_active
            , ppta.end_date_active
            , ppta.burden_cost_flag
            , pbco.*
         FROM pa.pa_project_types_all ppta
    LEFT JOIN pa.pa_budgetary_control_options pbco ON ppta.project_type = pbco.project_type;

Project Templates

-- ##############################################################################
--        PROJECT TEMPLATES
-- ##############################################################################

  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
    JOIN pa.pa_projects_all ppa     ON ppa_template.project_id = ppa.created_from_project_id
   WHERE ppa_template.template_flag = 'Y'
GROUP BY ppa_template.segment1
       , ppa_template.creation_date;

Project Organizations

-- ##############################################################################
--     PA ORGANIZATIONS
-- ##############################################################################

    SELECT haou.organization_id org_id
         , haou.name
         , haou.creation_date org_cr_date
         , fu1.description org_cr_by
         , haou.last_update_date org_upd_date
         , fu2.description org_upd_by
         , tbl_inv.chk "Invoice Collection Org"
         , tbl_proj.chk "Task Owning Org"
      FROM hr.hr_all_organization_units haou
      JOIN applsys.fnd_user fu1 ON haou.created_by =      fu1.user_id
      JOIN applsys.fnd_user fu2 ON haou.last_updated_by = fu2.user_id
 LEFT JOIN (SELECT 1 chk, organization_id FROM apps.HR_ORGANIZATION_INFORMATION_V WHERE org_information1 = 'PA_INVOICE_ORG') tbl_inv  ON haou.organization_id = tbl_inv.organization_id
 LEFT JOIN (SELECT 1 chk, organization_id FROM apps.HR_ORGANIZATION_INFORMATION_V WHERE org_information1 = 'PA_PROJECT_ORG') tbl_proj ON haou.organization_id = tbl_proj.organization_id
    ORDER BY haou.creation_date DESC;

-- ##############################################################################
--     PROJECTS BY HR ORG
-- ##############################################################################
      SELECT haou.name org
           , COUNT(*) ct
        FROM pa.pa_projects_all ppa
        JOIN pa.pa_projects_all ppa_template       ON ppa.created_from_project_id =      ppa_template.project_id
        JOIN pa.pa_project_statuses pps            ON ppa.project_status_code =          pps.project_status_code
        JOIN pa.pa_project_types_all ppta          ON ppa.project_type =                 ppta.project_type
   LEFT JOIN pa.pa_ind_rate_schedules_all_bg pirsa ON ppa.cost_ind_rate_sch_id =         pirsa.ind_rate_sch_id
        JOIN applsys.fnd_user fu                   ON ppa.created_by =                   fu.user_id
        JOIN hr.hr_all_organization_units haou     ON ppa.carrying_out_organization_id = haou.organization_id
    GROUP BY haou.name
    ORDER BY 2 DESC;

-- details

      SELECT ppa.segment1
           , haou.name
           , ppa_template.name
           , ppa.project_status_code
           , ppa.description
        FROM pa.pa_projects_all ppa
   LEFT JOIN pa.pa_projects_all ppa_template       ON ppa.created_from_project_id =      ppa_template.project_id
        JOIN pa.pa_project_statuses pps            ON ppa.project_status_code =          pps.project_status_code
        JOIN pa.pa_project_types_all ppta          ON ppa.project_type =                 ppta.project_type
   LEFT JOIN pa.pa_ind_rate_schedules_all_bg pirsa ON ppa.cost_ind_rate_sch_id =         pirsa.ind_rate_sch_id
        JOIN applsys.fnd_user fu                   ON ppa.created_by =                   fu.user_id
        JOIN hr.hr_all_organization_units haou     ON ppa.carrying_out_organization_id = haou.organization_id
       WHERE haou.name = 'The Office of Bob Hope'
    ORDER BY 2 DESC;

-- ##############################################################################
--      HR ORGS LINKED TO PROJECTS
-- ##############################################################################

SELECT *
  FROM apps.pa_organizations_v
 WHERE EXISTS
          (SELECT ppa.project_id
             FROM pa.pa_projects_all ppa
            WHERE ppa.carrying_out_organization_id =
                     pa_organizations_v.organization_id);

-- ##############################################################################
--      COUNT OF PROJECTS BY CARRYING OUT ORG
-- ##############################################################################

  SELECT SUBSTR (pov1.name
               , 1
               , 80)
       , COUNT (ppa.project_id)
    FROM pa.pa_projects_all ppa
    JOIN apps.pa_organizations_v pov1 ON ppa.carrying_out_organization_id = pov1.organization_id
   WHERE ppa.carrying_out_organization_id IN
            (SELECT pov.organization_id
               FROM apps.pa_organizations_v pov)
GROUP BY SUBSTR (pov1.name
               , 1
               , 80);

Project Key Members

-- ##############################################################################
--     KEY MEMBERS
-- ##############################################################################

      SELECT DISTINCT
             ppa.segment1
           , ppa.name
           , ppa.description
           , ppa.creation_date
           , haou.name hr_org
           , pps.project_status_name status
           , pps.project_system_status_code st2
           , papf.full_name key_member
           , papf.employee_number
           , fu.employee_id
           , ppp.start_date_active member_start
           , ppa.start_date pr_start
           , ppp.end_date_active member_end
           , ppp.creation_date km_cr_date
           , ppp.last_update_date km_up_date
           , ppa.creation_date pr_cr_date
           , pprtt.meaning role_
           , ppp.project_role_id
           , pprtt.creation_date role_ct_date
           , fu.user_name key_member_user_name
           , fu.end_date key_member_user_end
           , fu2.description km_cr_by
           , fu3.description km_up_by
        FROM pa.pa_project_parties ppp
        JOIN pa.pa_project_role_types_b pprt   ON ppp.project_role_id =              pprt.project_role_id
        JOIN pa.pa_role_controls prc           ON ppp.project_role_id =              prc.project_role_id
        JOIN pa.pa_project_role_types_tl pprtt ON pprtt.project_role_id =            pprt.project_role_id
        JOIN pa.pa_projects_all ppa            ON ppp.object_id =                    ppa.project_id
        JOIN pa.pa_project_statuses pps        ON ppa.project_status_code =          pps.project_status_code
   LEFT JOIN hr.per_all_people_f papf          ON ppp.resource_source_id =           papf.person_id AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        JOIN hr.hr_all_organization_units haou ON ppa.carrying_out_organization_id = haou.organization_id
   LEFT JOIN applsys.fnd_user fu               ON fu.employee_id =                   papf.person_id
        JOIN applsys.fnd_user fu2              ON ppp.created_by =                   fu2.user_id
        JOIN applsys.fnd_user fu3              ON ppp.last_updated_by =              fu3.user_id
       WHERE 1 = 1
--         AND ppp.start_date_active > ppa.start_Date
--         AND papf.last_name = 'Hope'
--         AND papf.full_name LIKE '%Hope%Bob%'
--         AND length(papf.employee_number) > 7
--         AND papf.first_name = 'Bob'
--         AND ppa.segment1 IN ('PROJECT1')
--         AND ppa.creation_date > '01-JAN-2016'
--         AND papf.full_name LIKE '%Hope%'
--         AND pps.project_system_status_code = 'APPROVED'
--         AND pprtt.meaning = 'Principal Investigator'
--         AND ppp.end_date_active IS NULL
         AND fu.user_name = 'SYSADMIN'
         AND 1 = 1;

-- ##############################################################################
--     BASIC LIST OF ROLES
-- ##############################################################################
 
  SELECT pprtb.project_role_type
       , pprtt.meaning oracle_meaning
       , pprtb.creation_date cr_date
       , fu.description ct_by
    FROM pa.pa_project_role_types_b pprtb
       , pa.pa_project_role_types_tl pprtt
       , applsys.fnd_user fu
   WHERE pprtb.project_role_id = pprtt.project_role_id
     AND pprtb.created_by = fu.user_id;

Project Classifications

-- ##############################################################################
--      CLASSIFICATIONS - DETAILS ON PROJECT
-- ############################################################################*/

    SELECT ppa.segment1
         , ppa.name
         , ppa.creation_date pa_cr_date
         , pps.project_system_status_code sys_stat
         , ppcv.class_category
         , ppcv.class_code
      FROM apps.pa_project_classes_v ppcv
      JOIN pa.pa_projects_all ppa            ON ppcv.project_id =                  ppa.project_id
      JOIN pa.pa_project_statuses pps        ON ppa.project_status_code =          pps.project_status_code
     WHERE 1 = 1
       AND ppa.segment1 = 'PROJECT1'
--       AND ppcv.class_code = 'RE'
       AND pps.project_system_status_code = 'APPROVED'
  ORDER BY ppa.creation_date DESC;

-- ##############################################################################
--      CLASSIFICATIONS - COUNTING
-- ############################################################################*/

    SELECT ppcv.class_category
         , ppcv.class_code
         , COUNT(*) ct
      FROM apps.pa_project_classes_v ppcv
      JOIN pa.pa_projects_all ppa            ON ppcv.project_id =                  ppa.project_id
      JOIN pa.pa_project_statuses pps        ON ppa.project_status_code =          pps.project_status_code
     WHERE 1 = 1
    --   AND ppa.segment1 = 'R115549'
--       AND ppcv.class_code = 'FP7 - Small CP - RES'
       AND pps.project_system_status_code = 'APPROVED'
  GROUP BY ppcv.class_category
         , ppcv.class_code;

-- COUNT PER CATEGORY

  SELECT class_category
       , class_code
       , COUNT (*) ct
    FROM apps.pa_project_classes_v ppcv
GROUP BY class_category
       , class_code
ORDER BY 3 DESC;

Project Burden Schedules

Sections:

  1. Burden Schedules – Count against Projects
  2. Burden Schedules – Count against Tasks
  3. Burden Schedules – Details on Project
  4. Burden Schedules – Details on Task
  5. Burden Schedules – Count Summary

Burden Schedules Against Projects

-- ##############################################################################
--     BURDEN SCHEDULES - COUNT AGAINST PROJECTS
-- ##############################################################################

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
            JOIN pa.pa_ind_rate_schedules_all_bg pabs2 ON ppa.cost_ind_rate_sch_id = pabs2.ind_rate_sch_id
            JOIN pa.pa_project_statuses pps            ON 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
            JOIN pa.pa_ind_rate_schedules_all_bg pabs2 ON ppa.cost_ind_rate_sch_id = pabs2.ind_rate_sch_id
            JOIN pa.pa_project_statuses pps            ON ppa.project_status_code =  pps.project_status_code
           WHERE pps.project_status_name NOT LIKE '%Closed%'
        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;

Burden Schedules Against Tasks

-- ##############################################################################
--     BURDEN SCHEDULES - COUNT AGAINST 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 
            JOIN pa.pa_projects_all ppa                ON ppa.project_id =          pt.project_id
            JOIN pa.pa_project_statuses pps            ON ppa.project_status_code = pps.project_status_code
            JOIN pa.pa_ind_rate_schedules_all_bg pabs2 ON pabs2.ind_rate_sch_id =   pt.cost_ind_rate_sch_id
        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
            JOIN pa.pa_projects_all ppa                ON ppa.project_id =          pt.project_id
            JOIN pa.pa_project_statuses pps            ON ppa.project_status_code = pps.project_status_code
            JOIN pa.pa_ind_rate_schedules_all_bg pabs2 ON pabs2.ind_rate_sch_id =   pt.cost_ind_rate_sch_id
           WHERE pps.project_status_name NOT LIKE '%Closed%'
        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 2;

Burden Schedules – Details on Project

-- ##############################################################################
--     BURDEN SCHEDULES - DETAILS ON PROJECT
-- ##############################################################################

  SELECT pabs2.ind_rate_sch_id
       , pabs2.ind_rate_sch_name
       , ppa.segment1
    FROM pa.pa_projects_all ppa
    JOIN pa.pa_project_statuses pps            ON ppa.project_status_code = pps.project_status_code
    JOIN pa.pa_ind_rate_schedules_all_bg pabs2 ON pabs2.ind_rate_sch_id =   ppa.cost_ind_rate_sch_id
   WHERE ppa.segment1 = 'PROJECT1';

Burden Schedules – Details on Task

-- ##############################################################################
--     BURDEN SCHEDULES - DETAILS ON TASK
-- ##############################################################################

  SELECT pabs2.ind_rate_sch_id
       , pabs2.ind_rate_sch_name
       , ppa.segment1
       , pt.task_number
    FROM pa.pa_tasks pt
    JOIN pa.pa_projects_all ppa                ON ppa.project_id =          pt.project_id
    JOIN pa.pa_project_statuses pps            ON ppa.project_status_code = pps.project_status_code
    JOIN pa.pa_ind_rate_schedules_all_bg pabs2 ON pabs2.ind_rate_sch_id =   pt.cost_ind_rate_sch_id
   WHERE ppa.segment1 = 'PROJECT1';

Burden Schedules – Count Summary

-- ##############################################################################
--     BURDEN SCHEDULES - COUNT SUMMARY
-- ##############################################################################

  SELECT pabs2.ind_rate_sch_name
       , COUNT (*) ct
       , MAX (ppa.creation_date) latest
    FROM pa.pa_projects_all ppa
    JOIN pa.pa_ind_rate_schedules_all_bg pabs2 ON pabs2.ind_rate_sch_id = ppa.cost_ind_rate_sch_id
GROUP BY pabs2.ind_rate_sch_name;

Project Budget Entry Methods

-- ##############################################################################
--     BUDGET ENTRY METHODS
-- ##############################################################################

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
            JOIN pa.pa_budget_entry_methods pbem ON pbv.budget_entry_method_code = pbem.budget_entry_method_code
            JOIN pa.pa_projects_all ppa          ON pbv.project_id = ppa.project_id
            JOIN pa.pa_project_statuses pps      ON ppa.project_status_code = pps.project_status_code
           WHERE 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
            JOIN pa.pa_budget_entry_methods pbem ON pbv.budget_entry_method_code = pbem.budget_entry_method_code
            JOIN pa.pa_projects_all ppa          ON pbv.project_id = ppa.project_id
            JOIN pa.pa_project_statuses pps      ON ppa.project_status_code = pps.project_status_code
           WHERE 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(+)
ORDER BY pbem.budget_entry_method_code;

  SELECT pbem.budget_entry_method_code
       , COUNT (*) ct
    FROM pa.pa_budget_versions pbv
    JOIN pa.pa_budget_entry_methods pbem ON pbv.budget_entry_method_code = pbem.budget_entry_method_code
GROUP BY pbem.budget_entry_method_code;

-- ##############################################################################
--     DETAILS AGAINST A PROJECT
-- ##############################################################################

  SELECT ppa.segment1, ppa.project_id
       , pbt.budget_type
       , pps.project_status_name status
       , pbem.budget_entry_method
       , pbv.creation_date
       , pbv.version_number
       , pbv.budget_status_code
    FROM pa.pa_budget_versions pbv
    JOIN pa.pa_budget_entry_methods pbem ON pbv.budget_entry_method_code = pbem.budget_entry_method_code
    JOIN pa.pa_projects_all ppa          ON ppa.project_id =               pbv.project_id
    JOIN pa.pa_project_statuses pps      ON ppa.project_status_code =      pps.project_status_code
    JOIN pa.pa_budget_types pbt          ON pbv.budget_type_code =         pbt.budget_type_code
   WHERE segment1 = 'PROJECT1'
--     AND budget_type_code = 'AC'
--     AND budget_status_code IN ('W', 'S')
--     AND version_number = 1
--     AND pbv.budget_status_code = 'B'
--     AND pbem.budget_entry_method = 'Task Level Baseline'
ORDER BY pbv.creation_date DESC;

Project Auto Accounting Rules

-- #######################################################################
-- AUTOACCOUNTING RULES
-- #######################################################################

SELECT pr.rule_name
     , pr.rule_id
     , pr.rule_type
     , pr.key_source
     , pr.constant_value
     , pr.select_statement sql
--     , dbms_lob.instr(pr.select_statement,'SELECT') fff
     , pr.creation_date cr_dt
     , fu1.description cr_by
     , pr.last_update_date upd_dt
     , fu2.description upd_by
     , (SELECT COUNT(psrra.rule_id) FROM pa.PA_SEGMENT_RULE_PAIRINGS_ALL psrra WHERE psrra.rule_id = pr.rule_id) assigned_count
  FROM pa.pa_rules pr
  JOIN applsys.fnd_user fu1 ON pr.created_by = fu1.user_id
  JOIN applsys.fnd_user fu2 ON pr.last_updated_by = fu2.user_id
 WHERE 1 = 1
--   AND LOWER(pr.rule_name) LIKE '%inv%'
   AND 1 = 1;

-- #######################################################################
-- ASSIGN AUTOACCOUNTING RULES
-- #######################################################################

        select pf.function_name
--             , pf.description
             , pfta.function_transaction_name function_transaction
--             , pfta.description function_description
             , psrra.segment_num segment
             , tbl_gl.segment_name
             , pr.rule_name
             , '''' || pr.constant_value
             , DECODE (pr.key_source, 'S', 'SQL', 'P', 'Parameter', 'C', 'Constant') source
             , psvls.segment_value_lookup_set_name lookup_name
             , pr.select_statement
          from pa.PA_FUNCTIONS pf
          JOIN pa.PA_SEGMENT_RULE_PAIRINGS_ALL psrra ON pf.application_id = psrra.application_id AND pf.function_code = psrra.function_code
          JOIN PA.PA_FUNCTION_TRANSACTIONS_ALL pfta ON pfta.application_id = pf.application_id AND pfta.function_code = psrra.function_code AND pfta.function_transaction_code = psrra.function_transaction_code
          JOIN pa.pa_rules pr ON psrra.rule_id = pr.rule_id
          JOIN (SELECT segment_num , segment_name FROM applsys.FND_ID_FLEX_SEGMENTS A WHERE A.APPLICATION_ID = 101 AND A.ENABLED_FLAG = 'Y' AND id_flex_code = 'GL#') tbl_gl ON tbl_gl.segment_num - 1 = psrra.segment_num
     LEFT JOIN pa.PA_SEGMENT_VALUE_LOOKUP_SETS psvls ON pr.segment_value_lookup_set_id = psvls.segment_value_lookup_set_id
         WHERE 1 = 1
           AND pfta.enabled_flag = 'Y'
--           AND LOWER(pfta.function_transaction_name) LIKE '%rev%'
--           AND psrra.segment_num = 0
--           AND pr.rule_name LIKE '%Activity%'
--           AND pf.function_name = 'Misc Trans Cost Account'
           AND 1 = 1
      ORDER BY pf.function_name
             , pfta.function_transaction_name
             , psrra.segment_num;