Oracle iExpense Report Tables with Policy Violation Details

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

--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'


at erp, erp, oracleapps, oracleapps erp, oracle erp, oracle ebusiness, oracle application,   
about oracle, oracle developer, oracle jobs, finance software, finance accounting accounting, r12 oracle, what is oracle database, oracle database, Oracle 11g, 11g database, oracle iexpense

Oracle iExpense Setups and Common issues

Introduction:

Using Oracle I-Expense module, Employees can submits his/her expense claims for Credit card and Cash expenses to their manager for Approval. This Expense report goes through the whole set of approval process as setup in the system.  For the approval process to go through smoothly its necessary to have all the corresponding setups in place. This document gives the detail of important setups for iExpense that are necessary to take Expense report through complete approval cycle. For employee to be able to submit his/her expense claims, it is important that the employee and his approving manager have all the application setups in place. Following are important setups related to I-Expense.

1.    Employee setups

2.    Manager or Approver setups

3.    Approval limit setups

Setup Details:

Navigation: AP Manager>Employees>View Employee

Employee setups:

For employee to proceed with submission of the expense report his employee record must be present in Oracle. Employee record in Oracle must have all the below important fields populated with correct information.

1.    Email: iExpense is the workflow based application. All the approval cycle takes place through Notifications that are send to the email address of the Employee.

2.    Supervisor: Expense report of the Employee by default is submitted to the Manager or Supervisor that is setup at assignment.

3.    Expense Account: Default cost center for which employee submits the expense report depends upon the Expense Account that is setup at assignment level.

Manager or Approver setups:

When Employee submits his expense report it goes to the Manager setup at Employee record Level and he is the default approver for the Expense report.

This Manager should have all the setups similar to Employee record mentioned above.

Oracle User setup:

Navigation: System Administrator>Security>User>Define

Manager and Employee both need to have the Oracle User id with appropriate responsibility assigned to them. Also the Oracle user must be associated with

Correct employee record shown as person in below screen. To make sure that workflow notification goes to the Manager and Employee correct email address must be populated in below screen.

Approval Limit setup:

Approval limit is the authority for the Manager on employee cost center to approve the expense up to specified limits. If the Manager doesn’t have the required setup he won’t be able to approve the Employee Expense report.

Navigation: AP Manager>Employees>Signing Limits

1.    Select the document type as “AP Expense Report”

2.    From list of values select the Manager name.

3.    Select the Cost center i.e. Employee cost center.

4.    Enter value for Signing Limit.

Common Issues due to incorrect iExpense setup:

1.    Manager approves the expense report but report gets escalated to next Manager for approval:

a.    This happens if the Manger for the Employee doesn’t have approval authority on the cost center to which employee belongs.

Setup the approval limit for the Manager on the correct cost center.

b.    Employee submits the Expense report not to his direct manager but to another Manager who doesn’t have approval authority. Verify the Manager at Employee record and Withdraw the expense report, submit to correct Manager.

2.    Employee gets the error while submitting the expense report to a Manager “Approver not found”

a.    Verify if the Manager employee record is active and its not end dated.

b.    Verify if the Manager has Oracle User with iExpense responsibility.

c.    Verify if the Oracle User record is associated with correct Employee record for the Manager.

3.    Employee or Manager doesn’t receive the workflow Notifications

a.    Verify if correct email address is populated at Employee record.

b.    Verify if the correct email address is populated at Oracle User.

4.    Employee not able to see the correct iExpense details when he select the iExpense responsibility

a.    Verify if the Oracle User record is associated with correct Employee record.