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'

Oracle iExpenses flow

In this article we shall discuss the flow of an Oracle expense report. The flow comprises of the following parts.

1. End user creates an expense report and submits it for approval.
2. The expense report is approved by a manager
3. Once the expense report is approved the report is imported into Payables as an invoice

I have turned off personalization from my Oracle instance so that the standard features of Oracle iExpenses are available. Because of this reason a warning message is shown on all the screens saying that Personalization is disabled.

Step 1: Login to Oracle

Go to iExpenses responsibility

Click on Expenses Home to the Expense Home page.

You will see the iExpense home page. This page contains the list of

• The submitted expense reports on the top section of the page
• The saved and withdrawn home page in the middle section
• The notifications at the bottom section

From the home page it is possible to withdraw and delete expense reports as well.
 
Step 2a: Create Expense Report manually

Click on the Create Expense Report button on iExpense home page

Select the appropriate Approver who has to approve the expense report.. Click on Next button.

Important:
The approver needs to have Signing Limit (approval limit) for expense reports, for the

Cost center

Operating Unit

You can check the signing limit by logging into Payables SuperUser responsibility.

Navigation: Employee > Signing Limits

Query for the Approver name and the cost center

You can check the table AP_WEB_SIGNING_LIMITS_ALL table for the signing limits as well.

If the manager does not have appropriate signing limit for the cost center and the operating unit, the expense report will go to his manager for approval. The expense report will keep on going up the supervisor hierarchy until the expense report is approved by someone who has appropriate signing limits or until it has reached the top of the hierarchy. When it reaches the top of the hierarchy without an approval the expense report is automatically returned to the employee who has raised the expense report with a message “No Approver Found”.

  
Step 2b: Import the Expense Report

Oracle iExpense home page

Click on Export Spreadsheet button on the top right region.

On clicking the button Oracle will ask whether you want to save or open the spreadsheet like the following

Click on Save. Once the file is saved, open the file.

Enter the expense report details into the spreadsheet.
Note:
The key fields like Expense Cost Center, Expense Template, etc. should match the values as set up in Oracle.
Once the values are filled in the spreadsheet, it will look like this

After you have filled out the expenses in the spreadsheet go back to the Oracle iExpense home page.

Click on Import Spreadsheet button.

The Import Spreadsheet page opens. Now follow the steps

1. Clicking on Ctrl+A to select all the contents
2. Clicking on Ctrl+C to copy the selected contents
3. Click Ctrl+V to paste the spreadsheet contents into the field, Spreadsheet Import Area, on the OAF form.

Click on Continue

You are now taken to the Create Expense report page. As you can see the Purpose field is already populated with the value from the spreadsheet. Click on Next.

On the Expense report lines page the line has also been populated through the spreadsheet.
Important:
This method is useful when the user wants to enter an expense report that has a lot of lines, e.g. more than 20 lines. Then it saves a lot of time for the user as entering more than 20 lines in Oracle takes more time than entering the lines in a spreadsheet and importing those into Oracle.
  
Step 3a: Enter expense lines for Cash expenses

You will be taken to the expense line entry page

Enter an expense line as shown below

Click on the Details () button for this line.

Now the expense line details page opens. Enter an Expense Location.

We entered a location named, Abu Dhabi. This field is mandatory. The reason for this field being mandatory is because Policy has been defined for Expense Type: Dinner. Click on return to come back to the Expense lines page.

Note:
You can find out about setting up policy in this article.

You can now see that the column Reimbursable Amount has been populated. Click on Next to go to the Expense Allocations page..
  
Step 3b: Enter expense lines for Corporate Credit Card expenses

For corporate credit card transactions you cannot manually enter the transactions. Instead, these lines are automatically entered via the Credit Card interface. The user has to select the transactions and submit the lines as part of the expense report.
The Credit card expense lines will look like the following,

Select the link and click on Next

Enter a justification as shown

Click on Details to review the expense line details

Click on Return and you will go back to the Credit Card expenses page. Click on Next to go to the Expense Allocations page.
  
Step 4: Allocate and submit the expense report

After the cash and credit card expense lines are filled out, you will be taken to the Expense Allocations page. We shall continue with the example of the Cash expense as we filled out in Step 3a.

The charge account can be set over here. The default values of the charge account can be modified on this page. Click on Next once you are done.

This is the last page of creating the expense report. Review the entire expense report finally before submitting it. You can attach any document you want to by clicking on Add button.

We can attach text, URL or files. We shall attach a piece of text.

Once we are done click on Apply.

You are now taken back to the Review page.Once you are done reviewing the expense report, click on Submit button to submit the expense report.

Once the expense report is submitted a message is displayed on the top of the page with the expense report number. The expense report number generated is EY48653.
  
Step 5: Approve the expense report

Now the expense report has been submitted we have to wait for the report to be approved. On submission the expense report goes to the Approver selected on the first page of Create Expense Report. In our case the expense report will be waiting on Praveen Talekana Seetharama Rai for approval.

Let us now check the workflow. Login to Workflow Administrator and go to Status Monitor.

Search for Workflow Type: Expenses and User Key: EY48653 (the expense report number)

Press Go

You will find the workflow. Click on Activity History to check the activity list. Scroll down on the Activity History page.

You can see that the workflow is pending on the same approver. Open the notification by clicking on button.

Click on Approve to approve the report. Refresh and check the Activity History again.

The workflow shows that it has gone to the auditor now.
 
Step 6: Audit the expense report

Login to Oracle as the iExpense auditor and go to the iExpense auditor responsibility.

Note:
During the audit process we shall complete the audit assuming the auditor does not have any queries and the auditor completes the audit without any questions.

Search by the expense report number (EY48653).

Click on Go

Enter a date for Receipt Page Received Date. This is the date on which the iExpense auditor received the receipts for the expenses.

Scroll down on the page

Check the box called Receipt Verified.

Set the value of Expense Report Action to Complete Audit.

Click on Apply.

Once audit is complete the completion message is displayed on the auditor page.
   
Step 7: Import the expense report into Payables

Once the auditor completes audit the expense report approval is completed. If you check the expenses workflow you will find that the workflow has completed.

Now log in to Payables SuperUser or its equivalent responsibility.
On the menu click on View > Requests and select the option to execute a concurrent program.

Execute the seeded program named, Expense Report Import.

Enter the parameters. You can give any value in Batch Name as long as this value has not been used as Batch Name in a previous run of this program.

Once the parameters are entered press OK.

Press Submit to execute the concurrent program.

When the program completes check the output.

  
Step 8: Check the Payables Invoice
 

In a Payables responsibility in which you can view invoices navigate to the following,
Navigation: Invoices > Entry > Invoices

Query for the invoice numbered, EY48653.

You can now view the expense report which has been imported into Payables as an Invoice.
Points to note:

1. The Payables invoice number and the expense report numbers are the same
2. Supplier Name is the name of the employee as the employee is set up as a supplier in Payables.
3. Description of the Payables invoice that has been imported from iExpenses is Expense Report.
4. Document Category Name for this invoice is Expense Report Invoices.
5. Invoice distribution account will be the same as the expense report distribution account
6. Invoice line Description will be the same as the expense report Expense Type.