Expense report Extract Queries

-- 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_num IN ( 'TEST_197525' , '215245')
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
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_num in ( '226685','227482','227650')
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