Oracle iExpense Report Tables with Policy Violation Details

Below are queries for Oracle iExpense to find out the Policy violation detail for iExpense reports.

 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'

 

Ref: http://www.oracleappsdeveloper.com/2012/09/expense-report-tables-with-policy.html

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s