Here is a compilation of several ad-hoc SQL queries I created over time to perform various analytical tasks withing Oracle Financials. I am by no means a SQL guru, I just know enough to keep myself out of trouble. These are simple queries, which at different occasions provided me with the information I needed. I am posting them here to find them again when I need them and to share them with those of you who may find them useful too.

Active Employees and Supervisors

Find a list of active employees and supervisors with their accounting detail. This is needed for instance when defining the AP approval limits for iExpenses.

[1] Active Employees and Supervisors

AP Signing Limits with Employee Information

Extract AP Signing Limits with employee information.

[2] AP Signing Limits with Employee Information

AR AutoInvoice

Analyze the AR Interface tables used by AutoInvoice.

[3] AR AutoInvoice

Asset Categories and Lives

Get a listing of asset categories with the accounting and depreciation information.

[4] Asset Categories and Lives

Asset Details Extracts

Extract asset detail information. Surprisingly, it is not easy to extract assets from Oracle Assets to convert them back to Oracle Assets.

[5] Asset Details Extracts

Employee Email Address Query

Compare employee and user email information.

[6] Employee Email Address Query

Employees and Users

Compare employee and user information. Find out which employees are not set up as users and vice versa.

[7] Employees and Users

Employees with Default Accounting Information

Select employee information from the HR tables with the default accounting information.

[8] Employees with Default Accounting Information

Employees, Organizations, Users and Responsibilities

Analyze by organization what users and responsibilities are set up for employees.

[9] Employees, Organizations, Users and Responsibilities

Expense Report Template Details

Extract expense report template details.

[10] Expense Report Template Details

Future-Dated Expense Report Items Query

Find future-dated expense report items entered incorrectly in the future. Prevent this problem by setting the respective [11] OIE options.

[12] Future-Dated Expense Report Items

HR Managers

Get a list of HR managers.

[13] HR Managers

OIE Expense Report Import Query

Analyze project expense reports imported from iExpenses.

[14] OIE Expense Reports

Update Asset Lives

Unfortunately, the Mass Additions process does not let you provide an asset life during the upload via FA_MASS_ADDITONS table. The life for converted assets always defaults to the life defined in the category. I believe this has changed in R12, but in prior release, you might need to use this script. As a workaround, we put the new life in ATTRIBUTE4 in FA_MASS_ADDTIONS, and updated FA_BOOKS table after the assets were created.

[15] Update Asset Life

Update Email Preference

Update a default email preference.

[16] Update Email Preference