Sample Ad-Hoc Queries for Oracle Financials

January 11, 2008 on 8:06 pm | by Marian Crkon | In How To Guides | | Print This Post

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.

Active Employees and Supervisors

AP Signing Limits with Employee Information

Extract AP Signing Limits with employee information.

AP Signing Limits with Employee Information

AR AutoInvoice

Analyze the AR Interface tables used by AutoInvoice.

AR AutoInvoice

Asset Categories and Lives

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

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.

Asset Details Extracts

Employee Email Address Query

Compare employee and user email information.

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.

Employees and Users

Employees with Default Accounting Information

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

Employees with Default Accounting Information

Employees, Organizations, Users and Responsibilities

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

Employees, Organizations, Users and Responsibilities

Expense Report Template Details

Extract expense report template details.

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 OIE options.

Future-Dated Expense Report Items

HR Managers

Get a list of HR managers.

HR Managers

OIE Expense Report Import Query

Analyze project expense reports imported from iExpenses.

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.

Update Asset Life

Update Email Preference

Update a default email preference.

Update Email Preference

No Comments yet »

RSS feed for comments on this post. TrackBack URI

Leave a comment

XHTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>

Powered by WordPress with Pool theme design by Borja Fernandez.
Entries and comments feeds. Valid XHTML and CSS. ^Top^