Aim:
The below query is used to extract the Expense Details for the employees with the payment details, Current Approver, Status of the Expense Report, expense Submitted Date etc.
Script:
SELECT a.report_header_id expense_report_number, a.item_description,
a.amount, a.currency_code,
(SELECT full_name
FROM apps.per_all_people_f
WHERE person_id = b.employee_id
AND (effective_end_date IS NULL OR effective_end_date >= SYSDATE
)) employee_full_name,
(SELECT NAME
FROM hr_organization_units
WHERE organization_id = a.org_id) ORGANIZATION, justification,
category_code, start_expense_date, end_expense_date,
(SELECT full_name
FROM per_all_people_f
WHERE person_id IN (
SELECT employee_id
FROM fnd_user
WHERE user_id =
b.created_by)
AND (effective_end_date IS NULL OR effective_end_date >= SYSDATE
)) expense_created_by,
b.creation_date expense_created_date,
report_submitted_date expense_submitted_date,
override_approver_name approver_name, expense_status_code status,
(SELECT MAX (check_date)
FROM ap_checks_all
WHERE check_id IN (
SELECT check_id
FROM ap_invoice_payments_all
WHERE invoice_id IN (
SELECT invoice_id
FROM ap_invoices_all
WHERE invoice_num =
TO_CHAR
(b.report_header_id))))
payment_date,
(SELECT full_name
FROM per_all_people_f
WHERE person_id IN (
SELECT employee_id
FROM fnd_user
WHERE user_id =
a.last_updated_by)
AND (effective_end_date IS NULL OR effective_end_date >= SYSDATE
)) last_updated_by,
b.last_update_date
FROM ap_expense_report_lines_all a, ap_expense_report_headers_all b
WHERE 1 = 1
AND a.report_header_id = b.report_header_id
AND a.start_expense_date >= :expense_date
AND b.employee_id = ‘41039’
ORDER BY a.report_header_id
For any queries, you can message us at contact@staging.doyensys.com
