Oracle iExpense Report Tables with Policy Violation Details

 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
59
60
61
62

--Expense Report Details
SELECT attribute_category,org_id
FROM   apps.ap_expense_report_headers_all a
WHERE  a.invoice_num = <Expense report number>

SELECT *
FROM   apps.ap_expense_report_lines_all
WHERE  report_header_id =<Report Header id>

SELECT *
FROM   apps.ap_exp_report_dists_all
WHERE  report_header_id =<Report Header id>

--Fetch Invoice corresponding for Expense report
SELECT *
FROM   apps.ap_invoices_all aia
WHERE  aia.invoice_num =  <Expense Report Number>


--Parameters for a Specific Expense Template used to fetch the Policy line id for Expense Line
SELECT expense_report_id
FROM   apps.ap_expense_reports_all
WHERE  report_type = <attribute_category from Expense header table>
AND    org_id = <org_id from Expense Header table>

SELECT *
FROM   apps.ap_expense_report_params_all aerp
WHERE  expense_report_id = <id from above query>
AND    prompt = <item_description of expense line>

--Policy Header and details
SELECT *
FROM   apps.AP_POL_HEADERS a
WHERE  policy_id = <POLICY ID>

SELECT *
FROM   apps.AP_POL_LINES
WHERE  policy_id = <POLICY ID>

--Policy Violations for Expense Report header and Line
SELECT *
FROM   apps.ap_pol_violations_all apv
WHERE  apv.report_header_id =< report header id>
AND    apv.distribution_line_number = <dist line id>
      
--Table of Policy violations without duplicate violation enteries used for accurate counts
(SELECT report_header_id, distribution_line_number, violation_type, MAX(VIOLATION_NUMBER)
 FROM   apps.ap_pol_violations_all
 GROUP BY report_header_id, distribution_line_number, violation_type)

--Find the Employee/Non Employee attendees for a Expense report line
--Employee
SELECT *
FROM   oie_attendees_all oie
WHERE  report_line_id = <Report line id>
AND    oie.employee_flag = 'N'

--Non Employee
SELECT *
FROM   oie_attendees_all oie
WHERE  report_line_id = <Report line id>
AND    oie.employee_flag = 'Y'