In this blog, you will find the SQL query to extract the Employee expense payments in Oracle apps R12.1.3
Query:
select
aps.vendor_name “Supplier”,
aca.AMOUNT “Payment amount”,
aca.currency_code “Currency”,
aca.check_date “Payment date”,
fu.user_name “Expensing user name”,
aia.invoice_date “Invoice Date”,
aia.invoice_num “Invoice Number”
from apps.ap_invoices_all aia,
apps.ap_suppliers aps,
apps.ap_supplier_sites_all apss,
apps.ap_invoice_payments_all aipa,
apps.ap_checks_all aca,
apps.fnd_user fu
where aia.vendor_id=aps.vendor_id
and aps.employee_id = fu.user_id
and aca.check_date between fu.start_date and nvl(end_date,aca.check_date)
and aia.VENDOR_SITE_ID=APSS.VENDOR_SITE_ID
AND aps.vendor_id=apss.VENDOR_ID
and aipa.invoice_id(+)=aia.invoice_id
and aca.check_id (+)=aipa.check_id
and aia.ORG_ID= 82– Hard coded value for the Operatin Unit
and aia.SOURCE = ‘SelfService’
AND aia.INVOICE_TYPE_LOOKUP_CODE = ‘EXPENSE REPORT’
;
