Defining Non-Default Columns
You can use one of three methods to alter your PSI column setup during implementation to better suit your company's needs:
1. Select different text or numeric column definitions using the list of values in the Project Status Inquiry Columns window. See: Using Lists of Values.
2. Manually modify the predefined column definitions in the PSI Columns window using SQL expressions. See: Using SQL Expressions.
3. Write a client extension using PL/SQL procedures. See: PSI Extension.
Attention: If you make any changes in the PSI Columns window, you must save your changes and choose Generate View before the Project Status window will reflect your changes. You do not have to generate a view if you have changed only your client extension.
If PSI determines that a column value is blank (Definition field in the PSI Columns window left blank) or a NULL value (PSI unable to process SQL expression), the Project Status window will display a zero (for numeric column types) or leave the field blank (for text columns). To leave the value field blank for a numeric column, you must enter TO_NUMBER(null) in the Definition field in the PSI Columns window.
Column Setup Window
You can accomplish the first two methods by using the PSI Columns window (choose Setup, Project Status Columns from the Navigator). These are not only simpler than the third option; they will also give better performance results.
Regardless of which method you choose to change your PSI column configuration, you enter column prompts in the PSI Column window. The Project Status window always reads column prompts from the view generated by this window.
Using Lists of Values
Using the first method, you select from 120 column definitions in the list of values for the column definition you want to change. Oracle Projects displays a list of values for either text or numeric column definitions, depending on the column type of the active field. Numeric column definitions are preceded by one of the following letters:
- A = actual amounts (cost and revenue)
- R = revenue budget amounts
Attention: When you have made and saved your changes, choose Generate View to ensure that the appropriate project status folder (project, task, or resource) will reflect your changes.
Using SQL Expressions
The second method involves selecting a column definition from the list of values in the PSI Columns window, then modifying it with SQL expressions, as described in the examples below.
Example 1: Hours Percentage Complete
The definition of the Hours Percentage Complete column, one of the default PSI columns provided with Oracle Projects, is:
- ROUND(DECODE(C.BASELINE_LABOR_HOURS_TOT,0,0, (A.LABOR_HOURS_ITD/C.BASELINE_LABOR_HOURS_TOT) * 100),0)
The formula divides ITD labor hours by the total budgeted labor hours to obtain the percent complete. If total budgeted labor hours equal zero, zero is returned as the result. Following is an explanation of each element of this definition:
Round the expression x to y decimal places. If no value is given for y, round to 0 decimal places.
If the expression w results in the value x, return the value y; otherwise, return the value z.
Divide x by y. Other common operators are: * (multiply), + (add), and - (subtract).
Multiply x by 100. This is added to the definition to move the decimal point for a percentage expression.
Example 2: Over Budget
The Over Budget column, another default PSI column provided with Oracle Projects displays an asterisk if the project is more than 10% over its budget, and is blank otherwise. The definition for the column is:
- DECODE ((SIGN((NVL(C.BASELINE_BURDENED_COST_ITD,0) * 1.1) - (NVL (A.BURDENED_COST_ITD,0) + NVL (A.BURDENED_COST_ITD,0) + NVL (M.CMT_BURDENED_COST_PTD,0)))),-1,"*",NULL)
This definition contains some additional elements:
If x is a positive number or 0, SIGN(x) = 1. If x is a negative number, SIGN(x) = -1.
If x is not null, return x. Otherwise, return y.
You can use the Project Status Column Setup window to mark selected column definitions as currency amounts. Columns marked as currency amounts are displayed in Project Status Inquiry according to the functional currency format.
To mark columns as currency amounts:
1. Navigate to the Project Status Inquiry Columns window. Select Project, Task, or Resource in the Folder region.
2. Check the Currency check box for each column definition you want to mark as a currency amount.
