AP Web Report Workflow Status

SELECT ‘WPAY’ TYPE, gl.segment1 division, gl.segment3 dcc,
           gl.segment4 ccc, h.report_submitted_date creation_date,
           (TRUNC (SYSDATE) – TRUNC (h.report_submitted_date)) days_old,
           NULL timeout_date, NULL manager_level,
           TO_CHAR (h.report_header_id) report_header_id,
           h.invoice_num invoice_num, h.description description,
           p.full_name requestor, h.flex_concatenated district, h.total total,
           ‘Payables’ current_approver
      FROM ap_expense_report_headers_all h,
           per_people_x p,
           gl_code_combinations gl
     WHERE p.person_id = h.employee_id
       AND gl.code_combination_id = h.employee_ccid
       AND h.workflow_approved_flag = ‘M’
    UNION ALL
    SELECT ‘WSYS’ TYPE, gl.segment1 division, gl.segment3 dcc,
           gl.segment4 ccc, h.report_submitted_date creation_date,
           (TRUNC (SYSDATE) – TRUNC (h.report_submitted_date)) days_old,
           NULL timeout_date, NULL manager_level,
           wfi.item_key report_header_id, h.invoice_num invoice_num,
           h.description description, p.full_name requestor,
           h.flex_concatenated district, h.total total,
           DECODE (SUBSTR (wfi.assigned_user, 1, 3),
                   ‘FND’, ‘SYSADMIN:TO’,
                   wfi.assigned_user
                  ) current_approver
      FROM wf_item_activity_statuses wfi,
           ap_expense_report_headers_all h,
           per_people_x p,
           gl_code_combinations gl
     WHERE wfi.item_key = TO_CHAR (h.report_header_id)
       AND h.employee_ccid = gl.code_combination_id(+)
       AND p.person_id = h.employee_id
       AND wfi.item_type = ‘APEXP’
       AND wfi.activity_status = ‘NOTIFIED’
       AND wfi.end_date IS NULL
       AND (   wfi.assigned_user = ‘SYSADMIN’
            OR wfi.assigned_user LIKE ‘FND_RESP%’
           )
    UNION ALL
    SELECT ‘WTRM’ TYPE, gl.segment1 division, gl.segment3 dcc,
           gl.segment4 ccc, h.report_submitted_date creation_date,
           (TRUNC (SYSDATE) – TRUNC (h.report_submitted_date)) days_old,
           NULL timeout_date, NULL manager_level,
           wfi.item_key report_header_id, h.invoice_num invoice_num,
           h.description description, p.full_name requestor,
           h.flex_concatenated district, h.total total,
           wfi.assigned_user current_approver
      FROM wf_item_activity_statuses wfi,
           ap_expense_report_headers_all h,
           per_people_x p,
           gl_code_combinations gl,
           per_assignments_x a
     WHERE wfi.item_key = TO_CHAR (h.report_header_id)
       AND gl.code_combination_id = h.employee_ccid
       AND p.person_id = h.employee_id
       AND p.person_id = a.person_id
       AND a.primary_flag = ‘Y’
       AND wfi.item_type = ‘APEXP’
       AND wfi.activity_status = ‘NOTIFIED’
       AND wfi.end_date IS NULL
       AND wfi.assigned_user IS NOT NULL
       AND h.workflow_approved_flag IS NULL
       AND hr_person_type_usage_info.get_user_person_type (SYSDATE,
                                                           a.supervisor_id
                                                          ) LIKE
                                                                ‘Ex-employee%’
    UNION ALL
    SELECT ‘SPAY’ TYPE, gl.segment1 division, gl.segment3 dcc,
           gl.segment4 ccc, h.report_submitted_date creation_date,
           (TRUNC (SYSDATE) – TRUNC (h.report_submitted_date)) days_old,
           NULL timeout_date, NULL manager_level,
           TO_CHAR (h.report_header_id) report_header_id,
           h.invoice_num invoice_num, h.description description,
           p.full_name requestor, h.flex_concatenated district, h.total total,
           DECODE (h.workflow_approved_flag,
                   ‘Y’, ‘Complete’,
                   ‘A’, ‘Complete’,
                   ‘M’, ‘Mgr Approved’,
                   ‘S’, ‘Saved’,
                   ‘R’, ‘Rejected’,
                   ‘P’, ‘AP Approved’,
                   NULL, ‘Waiting’,
                   NULL
                  ) current_approver
      FROM ap_expense_report_headers_all h,
           per_people_x p,
           gl_code_combinations gl
     WHERE p.person_id = h.employee_id
       AND gl.code_combination_id = h.employee_ccid
       AND h.invoice_num LIKE ‘WEB%-1’
    UNION ALL
    SELECT ‘WMGR’ TYPE, gl.segment1 division, gl.segment3 dcc,
           gl.segment4 ccc, h.report_submitted_date creation_date,
           (TRUNC (SYSDATE) – TRUNC (h.report_submitted_date)) days_old,
           notif.timeout_date, notif.manager_level,
           wfi.item_key report_header_id, h.invoice_num invoice_num,
           h.description description, p.full_name requestor,
           h.flex_concatenated district, h.total total,
           DECODE (SUBSTR (wfi.assigned_user, 1, 3),
                   ‘FND’, ‘SYSADMIN:TO’,
                   wfi.assigned_user
                  ) current_approver
      FROM wf_item_activity_statuses wfi,
           ap_expense_report_headers_all h,
           fnd_user u,
           per_people_x p,
           gl_code_combinations gl,
           (SELECT SUBSTR (CONTEXT, 7, 6) CONTEXT,
                   TRUNC (due_date) timeout_date, message_name,
                   DECODE (message_name,
                           ‘OIE_REQUEST_EMPLOYEE_APPROVAL’, ‘0’,                         
                           ‘TIMED-OUT’
                          ) manager_level
              FROM wf_notifications
             WHERE MESSAGE_TYPE = ‘APEXP’ AND status = ‘OPEN’) notif
     WHERE wfi.item_key = TO_CHAR (h.report_header_id)
       AND notif.CONTEXT = h.report_header_id
       AND gl.code_combination_id = h.employee_ccid
       AND wfi.assigned_user = u.user_name(+)
       AND p.person_id = h.employee_id
       AND wfi.item_type = ‘APEXP’
       AND wfi.activity_status = ‘NOTIFIED’
       AND wfi.end_date IS NULL
       AND (h.workflow_approved_flag IS NULL OR h.workflow_approved_flag = ‘P’
           )
       AND wfi.assigned_user != ‘SYSADMIN’
       AND SUBSTR (wfi.assigned_user, 1, 3) != ‘FND’