I-Expense Ageing Report Query

SELECT DISTINCT aa.org_id org_id,
                aa.person_id,
                aa.claim_id,
                aa.invoice_num,
                aa.vendor_name,
                aa.vendor_code,
                aa.vendor_site_code,
                aa.claim_status,
                aa.claim_date,
                aa.claim_submission_date,
                CEIL (aa.claim_submission_date - aa.claim_date) age_cl_sub,
                aa.approval_date,
                aa.status,
                aa.age_appl_sub,
                aa.finance_approval_date,
                aa.age_fin_appl,
                aa.paid_date,
                aa.person_id,
                aa.report_date
  FROM (SELECT aerha.org_id,
               aerha.week_end_date report_date,
               employee_id person_id,
               aerha.report_header_id claim_id,
               aerha.invoice_num,
               expense_status_code claim_status,
               TRUNC (
                  DECODE (
                     TO_CHAR (aerha.report_header_id),
                     aerha.invoice_num, ael.claim_date,
                     (SELECT MIN (creation_date)
                        FROM ap_expense_report_lines_all
                       WHERE report_header_id =
                                SUBSTR (aerha.invoice_num,
                                        1,
                                        INSTR (aerha.invoice_num, '-') - 1))))
                  claim_date,
               TRUNC (report_submitted_date) claim_submission_date,
               TRUNC (
                  DECODE (ame.status, 'APPROVE', ame.row_timestamp, NULL))
                  approval_date,
               ame.status,
               CEIL (
                    DECODE (ame.status,
                            'APPROVE', TRUNC (ame.row_timestamp),
                            NULL)
                  - TRUNC (report_submitted_date))
                  age_appl_sub,
               TRUNC (
                  DECODE (aerha.expense_status_code,
                          'INVOICED', aerha.expense_last_status_date,
                          NULL))
                  finance_approval_date,
               CEIL (
                    DECODE (
                       aerha.expense_status_code,
                       'INVOICED', TRUNC (aerha.expense_last_status_date),
                       NULL)
                  - DECODE (ame.status,
                            'APPROVE', TRUNC (ame.row_timestamp),
                            NULL))
                  age_fin_appl,
               inv.vendor_site_code,
               inv.vendor_name,
               inv.vendor_code,
               inv.payment_date paid_date
          FROM ap_expense_report_headers_all aerha,
               (SELECT pvsa.vendor_site_code,
                       pov.vendor_name,
                       pov.segment1 vendor_code,
                       aia.invoice_num,
                       aia.org_id,
                       aipa.accounting_date payment_date
                  FROM ap_invoices_all aia,
                       ap_supplier_sites_all pvsa,
                       ap_suppliers pov,
                       ap_invoice_payments_all aipa
                 WHERE     1 = 1
                       AND pvsa.vendor_id = aia.vendor_id
                       AND pvsa.vendor_site_id = aia.vendor_site_id
                       AND aia.org_id = :p_org_id
                       AND aia.SOURCE = 'EXPENSE_REPORT'
                       AND aia.vendor_id = pov.vendor_id
                       AND aia.invoice_id = aipa.invoice_id
               ) inv,
               (  SELECT MIN (creation_date) claim_date, report_header_id
                    FROM ap_expense_report_lines_all aerla
                GROUP BY report_header_id) ael,
               (SELECT row_timestamp, transaction_id report_header_id, status
                  FROM ame_trans_approval_history
                 WHERE     application_id = -104
                       AND trans_history_id IN (  SELECT MAX (trans_history_id)
                                                    FROM ame_trans_approval_history
                                                   WHERE application_id = -104
                                                GROUP BY transaction_id)) ame
         WHERE     1 = 1
               AND aerha.report_header_id = ael.report_header_id
               AND inv.invoice_num LIKE aerha.report_header_id || '_%'
               AND inv.org_id = aerha.org_id
               AND aerha.report_header_id = ame.report_header_id(+)
               AND (   aerha.expense_status_code NOT IN ('MGRAPPR',
                                                         'INVOICED')
                    OR (    expense_status_code = 'PENDMGR'
                        AND status NOT IN ('REJECT', 'APPROVE')))
               AND TRUNC (aerha.report_submitted_date) BETWEEN :p_from_date
                                                           AND :p_to_date
               AND aerha.org_id = :p_org_id) aa
UNION
SELECT DISTINCT aa.org_id org_id,
                aa.person_id,
                aa.claim_id,
                aa.invoice_num,
                aa.vendor_name,
                aa.vendor_code,
                aa.vendor_site_code,
                aa.claim_status,
                aa.claim_date,
                aa.claim_submission_date,
                CEIL (aa.claim_submission_date - aa.claim_date) age_cl_sub,
                aa.approval_date,
                aa.status,
                aa.age_appl_sub,
                aa.finance_approval_date,
                aa.age_fin_appl,
                aa.paid_date,
                aa.person_id,
                aa.report_date
  FROM (SELECT aerha.org_id,
               aerha.week_end_date report_date,
               employee_id person_id,
               aerha.report_header_id claim_id,
               aerha.invoice_num,
               expense_status_code claim_status,
               TRUNC (
                  DECODE (
                     TO_CHAR (aerha.report_header_id),
                     aerha.invoice_num, ael.claim_date,
                     (SELECT MIN (creation_date)
                        FROM ap_expense_report_lines_all
                       WHERE report_header_id =
                                SUBSTR (aerha.invoice_num,
                                        1,
                                        INSTR (aerha.invoice_num, '-') - 1))))
                  claim_date,
               TRUNC (report_submitted_date) claim_submission_date,
               TRUNC (
                  DECODE (ame.status, 'APPROVE', ame.row_timestamp, NULL))
                  approval_date,
               ame.status,
               CEIL (
                    DECODE (ame.status,
                            'APPROVE', TRUNC (ame.row_timestamp),
                            NULL)
                  - TRUNC (report_submitted_date))
                  age_appl_sub,
               TRUNC (
                  DECODE (aerha.expense_status_code,
                          'INVOICED', aerha.expense_last_status_date,
                          NULL))
                  finance_approval_date,
               CEIL (
                    DECODE (
                       aerha.expense_status_code,
                       'INVOICED', TRUNC (aerha.expense_last_status_date),
                       NULL)
                  - DECODE (ame.status,
                            'APPROVE', TRUNC (ame.row_timestamp),
                            NULL))
                  age_fin_appl,
               inv.vendor_site_code,
               inv.vendor_name,
               inv.vendor_code,
               inv.payment_date paid_date
          FROM ap_expense_report_headers_all aerha,
               (SELECT pvsa.vendor_site_code,
                       pov.vendor_name,
                       pov.segment1 vendor_code,
                       aia.invoice_num,
                       aia.org_id,
                       NULL payment_date
                  FROM ap_invoices_all aia,
                       ap_supplier_sites_all pvsa,
                       ap_suppliers pov
                 WHERE     1 = 1
                       AND pvsa.vendor_id = aia.vendor_id
                       AND pvsa.vendor_site_id = aia.vendor_site_id
                       AND aia.org_id = :p_org_id
                       AND aia.SOURCE = 'EXPENSE_REPORT'
                       AND aia.vendor_id = pov.vendor_id
               ) inv,
               (  SELECT MIN (creation_date) claim_date, report_header_id
                    FROM ap_expense_report_lines_all aerla
                GROUP BY report_header_id) ael,
               (SELECT row_timestamp, transaction_id report_header_id, status
                  FROM ame_trans_approval_history
                 WHERE     application_id = -104
                       AND trans_history_id IN (  SELECT MAX (trans_history_id)
                                                    FROM ame_trans_approval_history
                                                   WHERE application_id = -104
                                                GROUP BY transaction_id)) ame
         WHERE     1 = 1
               AND aerha.report_header_id = ael.report_header_id
               AND inv.invoice_num LIKE aerha.report_header_id || '_%'
               AND inv.org_id = aerha.org_id
               AND aerha.report_header_id = ame.report_header_id(+)
               AND (   aerha.expense_status_code IN ('MGRAPPR', 'INVOICED')
                    OR (    expense_status_code = 'PENDMGR'
                        AND status NOT IN ('REJECT', 'APPROVE')))
               AND TRUNC (aerha.report_submitted_date) BETWEEN :p_from_date
                                                           AND :p_to_date
               AND aerha.org_id = :p_org_id) aa