Expense report Extract Queries

/* Formatted on 7/7/2014 4:05:31 PM (QP5 v5.115.810.9015) */
-- To extract Expense reports for Companies that are based on France and Italy Operating Units

SELECT hou.name organization_name,
       ven.vendor_name,
       ven.vendor_type_lookup_code vendor_type,
       inv.doc_sequence_value voucher_number,
       inv.gl_date gl_date,
       inv.invoice_num invoice_number,
       inv.invoice_currency_code currency,
       inv.invoice_amount,
       invd.amount distribution_amount,
       invd.description distribution_description,
       gcc.code_combination_id,
       gcc.segment1 company,
       gcc.segment2 department,
       gcc.segment3 account,
       gcc.segment5 product,
       gcc.segment4 region,
       gcc.segment6 future,
       DECODE (aip.invoice_payment_type,
          'PREPAY', inv2.invoice_num,
          ac.check_number)
          document_number,
       invd.period_name,
       jh.je_source,
       jh.name journal_entry,
       jl.description line_description,
       jl.accounted_dr,
       jl.accounted_cr,
       gps.period_year gl_date_year,
       gps.quarter_num gl_date_quarter,
       TO_CHAR (inv.gl_date, 'MON') gl_date_month,
       TO_CHAR (inv.gl_date, 'DD') gl_date_day
FROM ap_invoices_all inv,
     ap_invoice_distributions_all invd,
     hr_organization_units hou,
     po_vendors ven,
     gl_code_combinations gcc,
     ap_invoices_all inv2,
     ap_invoice_payments_all aip,
     ap_checks_all ac,
     ax_events ae,
     ax_sle_headers ash,
     ax_sle_lines asl,
     gl_je_lines jl,
     gl_je_headers jh,
     hr_operating_units ou,
     gl_period_statuses gps
WHERE     inv.invoice_id = invd.invoice_id
      AND hou.organization_id = invd.org_id
      AND ven.vendor_id = inv.vendor_id
      --AND inv.invoice_num IN ( 'TEST_197525' , '215245')
      AND inv.invoice_type_lookup_code = 'EXPENSE REPORT'
      AND gcc.code_combination_id = invd.dist_code_combination_id
      AND gcc.segment1 = '38'
      AND inv.gl_date BETWEEN '01-JAN-2009' AND '31-DEC-2009'
      AND inv.invoice_id = aip.invoice_id(+)
      AND aip.other_invoice_id = inv2.invoice_id(+)
      AND ac.check_id(+) = aip.check_id
      AND ae.event_id = invd.accounting_event_id
      AND asl.source_id = invd.invoice_distribution_id
      AND ash.event_id = ae.event_id
      AND asl.sle_header_id = ash.sle_header_id
      AND jl.reference_10 = asl.reference_10
      AND jl.reference_9 = asl.reference_9
      AND jl.subledger_doc_sequence_value = asl.sle_header_id
      AND jl.set_of_books_id = asl.set_of_books_id
      AND jh.je_header_id = jl.je_header_id
      AND ou.organization_id = hou.organization_id
      AND (inv.gl_date BETWEEN gps.start_date AND gps.end_date)
      AND gps.set_of_books_id = ou.set_of_books_id
      AND gps.application_id = 200
UNION ALL
SELECT hou.name organization_name,
       ven.vendor_name,
       ven.vendor_type_lookup_code vendor_type,
       inv.doc_sequence_value voucher_number,
       inv.gl_date gl_date,
       inv.invoice_num invoice_number,
       inv.invoice_currency_code currency,
       inv.invoice_amount,
       NULL distribution_amount,
       NULL distribution_description,
       gcc.code_combination_id,
       gcc.segment1 company,
       gcc.segment2 department,
       gcc.segment3 account,
       gcc.segment5 product,
       gcc.segment4 region,
       gcc.segment6 future,
       DECODE (aip.invoice_payment_type,
          'PREPAY', inv2.invoice_num,
          ac.check_number)
          document_number,
       gps.period_name,
       jh.je_source,
       jh.name journal_entry,
       jl.description line_description,
       jl.accounted_dr,
       jl.accounted_cr,
       gps.period_year gl_date_year,
       gps.quarter_num gl_date_quarter,
       TO_CHAR (inv.gl_date, 'MON') gl_date_month,
       TO_CHAR (inv.gl_date, 'DD') gl_date_day
FROM ap_invoices_all inv,
     hr_organization_units hou,
     po_vendors ven,
     gl_code_combinations gcc,
     ap_invoices_all inv2,
     ap_invoice_payments_all aip,
     ap_checks_all ac,
     ax_sle_lines asl,
     gl_je_lines jl,
     gl_je_headers jh,
     hr_operating_units ou,
     gl_period_statuses gps
WHERE     1 = 1
      AND hou.organization_id = inv.org_id
      AND ven.vendor_id = inv.vendor_id
      AND inv.invoice_type_lookup_code = 'EXPENSE REPORT'
      AND gcc.code_combination_id = asl.code_combination_id
      AND gcc.segment1 = '38'
      AND inv.gl_date BETWEEN '01-JAN-2009' AND '31-DEC-2009'
      AND inv.invoice_id = aip.invoice_id(+)
      AND aip.other_invoice_id = inv2.invoice_id(+)
      AND ac.check_id(+) = aip.check_id
      AND asl.source_table = 'AP_INVOICES'
      AND asl.source_id = inv.invoice_id
      AND jl.reference_10 = asl.reference_10
      AND jl.reference_9 = asl.reference_9
      AND jl.subledger_doc_sequence_value = asl.sle_header_id
      AND jl.set_of_books_id = asl.set_of_books_id
      AND jh.je_header_id = jl.je_header_id
      AND ou.organization_id = hou.organization_id
      AND (inv.gl_date BETWEEN gps.start_date AND gps.end_date)
      AND gps.set_of_books_id = ou.set_of_books_id
      AND gps.application_id = 200
ORDER BY 5, 6, 9

=========================================================
-- To extract Expense reports for all Companies (Except France and Italy Operating Units) and Date Range
/* Formatted on 7/7/2014 4:05:15 PM (QP5 v5.115.810.9015) */
/* Formatted on 7/7/2014 4:05:43 PM (QP5 v5.115.810.9015) */
SELECT hou.name organization_name,
       ven.vendor_name,
       ven.vendor_type_lookup_code vendor_type,
       inv.doc_sequence_value voucher_number,
       inv.gl_date gl_date,
       inv.invoice_num invoice_number,
       inv.invoice_currency_code currency,
       inv.invoice_amount,
       invd.amount distribution_amount,
       invd.description distribution_description,
       gcc.code_combination_id,
       gcc.segment1 company,
       gcc.segment2 department,
       gcc.segment3 account,
       gcc.segment5 product,
       gcc.segment4 region,
       gcc.segment6 future,
       DECODE (aip.invoice_payment_type,
          'PREPAY', inv2.invoice_num,
          ac.check_number)
          document_number,
       invd.period_name,
       jh.je_source,
       jh.name journal_entry,
       jl.description line_description,
       jl.accounted_dr,
       jl.accounted_cr,
       gps.period_year gl_date_year,
       gps.quarter_num gl_date_quarter,
       TO_CHAR (inv.gl_date, 'MON') gl_date_month,
       TO_CHAR (inv.gl_date, 'DD') gl_date_day
FROM ap_invoices_all inv,
     ap_invoice_distributions_all invd,
     hr_organization_units hou,
     po_vendors ven,
     gl_code_combinations gcc,
     ap_invoices_all inv2,
     ap_invoice_payments_all aip,
     ap_checks_all ac,
     ap_accounting_events_all ae,
     ap_ae_headers_all aeh,
     ap_ae_lines_all ael,
     gl_je_lines jl,
     gl_je_headers jh,
     hr_operating_units ou,
     gl_period_statuses gps
WHERE     inv.invoice_id = invd.invoice_id
      AND hou.organization_id = invd.org_id
      AND ven.vendor_id = inv.vendor_id
      --AND inv.invoice_num in ( '226685','227482','227650')
      AND inv.invoice_type_lookup_code = 'EXPENSE REPORT'
      AND gcc.code_combination_id = invd.dist_code_combination_id
      AND gcc.segment1 = '37'
      AND inv.gl_date BETWEEN '01-APR-2009' AND '31-MAR-2010'
      AND inv.invoice_id = aip.invoice_id(+)
      AND aip.other_invoice_id = inv2.invoice_id(+)
      AND ac.check_id(+) = aip.check_id
      AND ae.accounting_event_id = invd.accounting_event_id
      AND aeh.accounting_event_id = ae.accounting_event_id
      AND ael.ae_header_id = aeh.ae_header_id
      AND ael.source_id = invd.invoice_distribution_id
      AND jl.gl_sl_link_id = ael.gl_sl_link_id
      AND jh.je_header_id = jl.je_header_id
      AND ou.organization_id = hou.organization_id
      AND (inv.gl_date BETWEEN gps.start_date AND gps.end_date)
      AND gps.set_of_books_id = ou.set_of_books_id
      AND gps.application_id = 200
UNION ALL
SELECT hou.name organization_name,
       ven.vendor_name,
       ven.vendor_type_lookup_code vendor_type,
       inv.doc_sequence_value voucher_number,
       inv.gl_date gl_date,
       inv.invoice_num invoice_number,
       inv.invoice_currency_code currency,
       inv.invoice_amount,
       NULL distribution_amount,
       NULL distribution_description,
       gcc.code_combination_id,
       gcc.segment1 company,
       gcc.segment2 department,
       gcc.segment3 account,
       gcc.segment5 product,
       gcc.segment4 region,
       gcc.segment6 future,
       DECODE (aip.invoice_payment_type,
          'PREPAY', inv2.invoice_num,
          ac.check_number)
          document_number,
       gps.period_name period_name,
       jh.je_source,
       jh.name journal_entry,
       jl.description line_description,
       jl.accounted_dr,
       jl.accounted_cr,
       gps.period_year gl_date_year,
       gps.quarter_num gl_date_quarter,
       TO_CHAR (inv.gl_date, 'MON') gl_date_month,
       TO_CHAR (inv.gl_date, 'DD') gl_date_day
FROM ap_invoices_all inv,
     hr_organization_units hou,
     po_vendors ven,
     gl_code_combinations gcc,
     ap_invoices_all inv2,
     ap_invoice_payments_all aip,
     ap_checks_all ac,
     ap_ae_lines_all ael,
     gl_je_lines jl,
     gl_je_headers jh,
     hr_operating_units ou,
     gl_period_statuses gps
WHERE     1 = 1
      AND hou.organization_id = inv.org_id
      AND ven.vendor_id = inv.vendor_id
      AND inv.invoice_type_lookup_code = 'EXPENSE REPORT'
      AND gcc.code_combination_id = ael.code_combination_id
      AND gcc.segment1 = '37'
      AND inv.gl_date BETWEEN '01-APR-2009' AND '31-MAR-2010'
      AND inv.invoice_id = aip.invoice_id(+)
      AND aip.other_invoice_id = inv2.invoice_id(+)
      AND ac.check_id(+) = aip.check_id
      AND ael.source_table = 'AP_INVOICES'
      AND ael.source_id = inv.invoice_id
      AND jl.gl_sl_link_id = ael.gl_sl_link_id
      AND jh.je_header_id = jl.je_header_id
      AND ou.organization_id = hou.organization_id
      AND (inv.gl_date BETWEEN gps.start_date AND gps.end_date)
      AND gps.set_of_books_id = ou.set_of_books_id
      AND gps.application_id = 200
ORDER BY 5, 6, 9