The Feature

Sample Ad-Hoc SQL Queries for Oracle Projects

October 18, 2006 on 9:33 pm | by Marian Crkon | In How To Guides | 2 Comments |  Print |  Email

Here is a compilation of several ad-hoc SQL queries I created over time to perform various analytical tasks with the Oracle Projects data. I am by no means a SQL guru, these are simple queries that provided me with information I needed at different occasions. I am posting the queries here to find them when I need them for my next assignment and share them with those of you who may find them useful.

Labor Billing Rates

Find labor bill rates used to calculate T&M labor revenue for the existing labor transactions (timecards).

Billing Rates by Job

Billable Expenditure Items by Customer

Find billable expenditure items by customer. The Expenditure Inquiry screen provides a great way to search and export expenditures, however, it lacks a search by customer.

Billed Expenses by Customer

Create OTL Tasks View

This is a sample script with an example of how to modify the list of available tasks in OTL based on Service Type.

Create OTL PA_ONLINE_TASKS_V View

Fixed Price Projects without Activity

We use the percent complete revenue recognition method for fixed price projects. We rely on the percent complete amounts to trigger the revenue accrual generation each month. This query provides revenue managers with the status of activity in the fixed price projects to determine whether the revenue should be generated, or projects be closed.

Fixed Price Projects without Activity

Future-Dated OTL Timecards

Find timecards entered into future periods.

Future-Dated Timecards

Future-Dated Expense Report Items

Find project-related distribution items in AP with Expenditure Item Dates in the future. Most expense policies do not allow submitting expenses for reimbursement until they actually occur. However, iExpenses [OIE 11i.10] lets users enter, approve and pay future-dated expense items, and they get rejected during the Interface Expense Reports from Payables process. This query should help you identify such items and extract them into a user-friendly file.

Future-Dated Expense Report Items

Project Agreements and Funding

Find existing project agreements and funding allocations.

Project Agreements and Funding

Project Agreements Created by Contingent Workers

Find all project agreements created by contractors (contingent workers). There was a bug in Oracle Projects 11i.10, which would cause agreements not to show if created by contingent workers.

Project Agreements Owned by Contingent Workers

Project Configuration Details

Find projects with their configuration details including the owning organization, status, customer, key members, etc.

Project Configuration Details

Project Cross-Charge Transaction Details

Find project transactions cross-charged between different providing and receiving organizations.

Project Cross-Charge Transactions

Project Customers

Find customers and contacts used on the existing contract projects.

Project Customers

Project Invoices

Find project invoices by status, project type, etc.

Project Invoices

Project Percent Complete Amounts

Find project percent complete amounts. Also, get a list of projects with percent complete amounts but no revenue generated.

Project Percent Complete

Project Revenue Events

Find project billing and revenue events.

Project Revenue Events

Project Key Members

Find projects with specific key members, e.g. those who need to be replaced by new ones.

Projects with Key Member to Replace

Update Transaction Import Status

Update the Transaction Status in the Projects Transactions Interface table. When importing external project transactions, they may get rejected and you have to set them to ‘Pending’ status in order to make them eligible for next import.

Set Transaction Import Status

Unbille Project Expenditures

Find billable project expenditures, which have not been billed yet. Surprisingly, there is no standard report showing this information (11i.10).

Unbilled Project Expenditures

Uninterfaced Project Expenditure Adjustmemts

Find more information expenditure distribution lines, which show up as Projects exceptions when trying to close the Projects GL and PA periods.

Uninterfaced Project Expenditure Adjustments