Useful Queries for Expense Reports

Useful Queries for Expense Reports:

1. Project Managers for a project on Expense Report:

SELECT ppp.person_id
             FROM ap_exp_report_dists_all aerd, pa_project_players ppp
            WHERE     aerd.report_header_id = p_transaction_id
                  AND aerd.project_id = ppp.project_id
                  AND ppp.person_id IS NOT NULL
                  AND ppp.project_role_type = 'PROJECT MANAGER'
                  AND TRUNC (SYSDATE) BETWEEN TRUNC (ppp.start_date_active)
                                          AND TRUNC (
                                                 NVL (ppp.end_date_active,
                                                      SYSDATE + 1))

         GROUP BY ppp.person_id;

2.Task managers fro a task on Expense Report:

SELECT pt.task_manager_person_id person_id
             FROM ap_exp_report_dists_all aerd, pa_tasks pt
            WHERE     aerd.report_header_id = p_transaction_id
                  AND aerd.task_id = pt.task_id
                  AND pt.task_manager_person_id IS NOT NULL
                  AND TRUNC (SYSDATE) BETWEEN TRUNC (
                                                 NVL (pt.start_date, SYSDATE))
                                          AND TRUNC (
                                                 NVL (pt.completion_date,
                                                      SYSDATE + 1))
         GROUP BY pt.task_manager_person_id;