The following SQL provides a list of all expense reports. This report is essentially for auditors and administrators to find out how many reports are pending with manager or auditor.
The script takes 2 parameters
- Date from which the list has to be generated till date (date format is DD-MON-YYYY)
- The Set of Books name
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
| SELECT gsob.NAME "SOB Name", aeh.invoice_num "Report Number", ppx.full_name, ppx.employee_number, (SELECT ap_web_policy_utils.get_lookup_meaning ('EXPENSE REPORT STATUS', DECODE (ai.cancelled_date, NULL, aeh.expense_status_code, 'CANCELLED' )) FROM ap_invoices_all ai WHERE ai.invoice_id(+) = aeh.vouchno) "Report status", NVL (aeh.report_submitted_date, aeh.creation_date) "Report submitted on", CASE WHEN NVL2 (aap.entered_date, 'Y', 'N' ) = 'Y' THEN aap.entered_date END "Manager Approved on", aeh.expense_status_code, TRUNC (aeh.week_end_date) "Report entered date", aca.employee_number "Auditor Emp Num", aca.full_name "Auditor Name", aca.entered_date "Audited on" FROM gl_sets_of_books gsob, ap_expense_report_headers_all aeh, per_people_x ppx, (SELECT ROW_NUMBER () OVER (PARTITION BY source_object_id ORDER BY note_id DESC) srl, an.note_id, an.source_object_id, an.entered_by, an.entered_date, ppx.employee_number, ppx.full_name FROM ap_notes an, fnd_user fu, per_people_x ppx WHERE an.entered_by = fu.user_id AND fu.employee_id = ppx.person_id AND an.source_object_code = 'OIE_EXPENSE_REPORT' AND TO_CHAR (SUBSTR (an.notes_detail, 1, 24 )) = 'Approver Action: Approve') aap, (SELECT ROW_NUMBER () OVER (PARTITION BY source_object_id ORDER BY note_id DESC) srl, ans.note_id, ans.source_object_id, ans.entered_by, ans.entered_date, pplx.employee_number, pplx.full_name FROM ap_notes ans, fnd_user fur, per_people_x pplx WHERE ans.entered_by = fur.user_id AND fur.employee_id = pplx.person_id AND ans.source_object_code = 'OIE_EXPENSE_REPORT' AND TO_CHAR (SUBSTR (ans.notes_detail, 1, 15 )) = 'Complete Audit.') aca WHERE 1 = 1 AND aeh.report_header_id = aca.source_object_id(+) AND aeh.report_header_id = aap.source_object_id(+) AND aeh.employee_id = ppx.person_id AND gsob.set_of_books_id = aeh.set_of_books_id -- AND aeh.expense_status_code NOT IN ('INVOICED', 'PAID', 'WITHDRAWN', 'REJECTED') AND NVL (aap.srl, 1) = 1 AND NVL (aca.srl, 1) = 1 AND aeh.creation_date > TO_DATE (:date_in_ddmonyyyy, 'DD-MON-YYYY') AND TRUNC (SYSDATE) BETWEEN TRUNC (ppx.effective_start_date) AND TRUNC (ppx.effective_end_date) AND gsob.NAME IN :sob_name |
The previous query only gives the report header details. If we want to include the line information such as, the amount, currency and distribution account, then the previous query will be modified into the one below.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
| SELECT gsob.NAME, aeh.invoice_num "Report Number", ppx.full_name, ppx.employee_number, (SELECT ap_web_policy_utils.get_lookup_meaning ('EXPENSE REPORT STATUS', DECODE (ai.cancelled_date, NULL, aeh.expense_status_code, 'CANCELLED' )) FROM ap_invoices_all ai WHERE ai.invoice_id(+) = aeh.vouchno) "Report status", ael.currency_code, ael.amount, NVL (aeh.report_submitted_date, aeh.creation_date) "Report submitted on", CASE WHEN NVL2 (aap.entered_date, 'Y', 'N' ) = 'Y' THEN aap.entered_date END "Manager Approved on", gcc.concatenated_segments, aeh.expense_status_code, TRUNC (aeh.week_end_date) "Report entered date", aca.employee_number "Auditor Emp Num", aca.full_name "Auditor Name", aca.entered_date "Audited on" FROM gl_sets_of_books gsob, ap_expense_report_headers_all aeh, per_people_x ppx, gl_code_combinations_kfv gcc, ap_expense_report_lines_all ael, (SELECT ROW_NUMBER () OVER (PARTITION BY source_object_id ORDER BY note_id DESC) srl, an.note_id, an.source_object_id, an.entered_by, an.entered_date, ppx.employee_number, ppx.full_name FROM ap_notes an, fnd_user fu, per_people_x ppx WHERE an.entered_by = fu.user_id AND fu.employee_id = ppx.person_id AND an.source_object_code = 'OIE_EXPENSE_REPORT' AND TO_CHAR (SUBSTR (an.notes_detail, 1, 24 )) = 'Approver Action: Approve') aap, (SELECT ROW_NUMBER () OVER (PARTITION BY source_object_id ORDER BY note_id DESC) srl, ans.note_id, ans.source_object_id, ans.entered_by, ans.entered_date, pplx.employee_number, pplx.full_name FROM ap_notes ans, fnd_user fur, per_people_x pplx WHERE ans.entered_by = fur.user_id AND fur.employee_id = pplx.person_id AND ans.source_object_code = 'OIE_EXPENSE_REPORT' AND TO_CHAR (SUBSTR (ans.notes_detail, 1, 15 )) = 'Complete Audit.') aca, ap_exp_report_dists_all ada WHERE ael.report_header_id = aeh.report_header_id AND ada.report_header_id = ael.report_header_id AND ada.report_line_id = ael.report_line_id AND aeh.report_header_id = aca.source_object_id(+) AND aeh.report_header_id = aap.source_object_id(+) AND aeh.employee_id = ppx.person_id AND ada.code_combination_id = gcc.code_combination_id AND gsob.set_of_books_id = aeh.set_of_books_id -- AND aeh.attribute_category <> 'Credit Card Expenses' --AND aeh.expense_status_code NOT IN ('INVOICED', 'PAID', 'WITHDRAWN', 'REJECTED') AND NVL (aap.srl, 1) = 1 AND NVL (aca.srl, 1) = 1 AND aeh.creation_date > TO_DATE (:date_in_ddmonyyyy, 'DD-MON-YYYY') AND TRUNC (SYSDATE) BETWEEN TRUNC (ppx.effective_start_date) AND TRUNC (ppx.effective_end_date) AND gsob.NAME IN :sob_name |
0 comments