Previous	Next		Contents	Index	Navigation	Glossary	Library

Definitions of Terms Used in This Section:

cursor A pointer to a row in the database.

derived column A PSI column in which the displayed amount is derived from stored amounts using a subset of SQL expressions.

lower-level PSI views Views that were designed to improve PSI performance. These 40 or more views are used by the default PSI columns and any others that you define in the Column Setup window.

The lower-level PSI views use function calls and other sophisticated technical devices. Because of their complexity and the fact that they may change from release to release, the lower-level PSI views are not documented in the Oracle Projects Technical Reference Manual (TRM).

super views Views that contain all summarized values. The super views each contain up to 126 columns. Each super view contains all available accumulation columns for a project, task, or resource. (Some columns, such as quantities, are only applicable for resources.)

Following are the super views:

PA_STATUS_PROJ_V Project Status Window view

PA_STATUS_TASK_V Task Status Window view

PA_STATUS_RSRC_V Resource Status Window view

PSI Setup Tips

1. Keep the following facts in mind when writing the PSI client extension:

• You do not need to enter a column definition for any column if your PSI extension will calculate the column's value.

• Even if you use a PSI extension to calculate a column's value, the column's prompt always comes from the PSI Columns window.

• If you change only the PSI extension, you do not need to generate a new view for the Project Status window to reflect your changes.

• If your extension returns a NULL column value, the Project Status window will display the column value defined in the PSI Columns window.

• Since using the PSI extension degrades the product's performance, you should limit the extension's scope as much as possible.

2. Define as many columns as possible using the Column Setup window rather than the PSI extension.

The Column Setup window creates views that yield better performance than calls to the PSI client extension, which is called by Post-Query triggers in PSI. This means that as each row is displayed on the PSI screen, a trigger calls the extension! This can cause performance delays, since the system will be sitting and thinking before displaying each row.

For best performance, define as many of the PSI columns as possible in the Column Setup window.

There are restrictions to using the Column Setup window for derived numbers:

• The expression for each column must use the column names defined in the column definition list of values. No other columns or tables may be referenced in the Column Setup window.

• Conditional logic is limited to the SQL "decode", "sign", and "nvl" functions. These functions are described in the Project Status Inquiry Setup section of the Oracle Projects User's Guide.

If the PSI window you want to design requires very complex conditional logic or select statements on column names or tables other than those displayed in the column definition list of values, then use the PSI client extension.

3. Use the "super views" when customizing the PSI Client Extension

Use the super views when you customize the PSI client extension. The super views probably contain all the information you want to display, and they are documented in the Oracle Projects Technical Reference Manual (TRM).

Attention: Each of the super views contains amounts for all levels of the WBS (work breakdown structure) and all resources. Therefore, you only need one cursor for each status window. For example, if budgets are maintained for the lowest-level tasks on a given project, then the parent task budget amounts will be the sum of their corresponding child task budget amounts. The sample code in the PSI client extension demonstrates how to execute one cursor in each view.

The resource super view (PA_STATUS_RSRC_V) contains two sets of data: project-level resources and task-level resources. The sample code in the PSI client extension demonstrates how to point to project-level or task-level data.

4. You only need to use one cursor for multiple levels in the WBS.

Example: Suppose you want your PSI client extension to calculate ten percent of Accumulated Cost ITD. The project has the following three tasks:

As shown in the sample code below, you can display these amounts by defining one cursor and calculation for each row. Because the amounts are stored for all levels of the Task WBS, only one cursor and calculation needs to be executed for each task in the WBS.

Sample code to calculate 10% of Accumulated Cost ITD:

The select statement includes the following code:

SELECT	nvl(burdened_cost_itd, 0)

INTO		p_burdened_cost_itd

FROM 		pa_status_rsrc_v 

WHERE		project_id = x_project_id

AND			task_id    = x_task_id

AND			resource_list_member_id = x_resource_list_member_id;

The following line does the calculation and assigns the result to column 30:

x_derived_col_30 := ROUND(p_burdened_cost_itd * .1)

Remember, for each row in the WBS, you only need to execute a calculation once in the PSI client extension.

5. Make sure that default columns prompts are defined in the Column Setup window.

If you have made changes in the Column Setup window and subsequently generated views that have errors, you must correct the definitions in the Column Setup window and choose Generate. Even if you are using the PSI client extension, valid base views and default column prompts must have been generated in the Column Setup window.

6. You can select which of the three status windows uses the PSI client extension.

You can have one PSI window that calls the extension and another that does not call the extension.

• If you want a PSI window to call the client extension for any of its columns, set the extension switch for that window type (project, task, or resource) to Y.

• If you do not want a PSI window to call the client extension for any of its columns, set the extension switch for that window type to N (the default setting).

The switches are indicated in the client extension as follows:

• Project Status Window: ProjCustomExtn

• Task Status Window: TaskCustomExtn

• Resource Status Window: RsrcCustomExtn

This information is also described under Project Status Inquiry Setup, Oracle Projects User's Guide.

7. You can combine Column Setup window definitions and PSI client extension calls.

Within the same PSI window, you can define some columns in the Column Setup window and define other columns in the PSI client extension. You can also define a column in both the Column Setup window and the client extension. For example:

1. If the client extension switch for a PSI window is set to N, then PSI uses the column definitions in the Column Setup window.

2. If the following conditions are true, then PSI use the client extension value:

• The client extension switch for a PSI window is set to Y

• A column definition is defined in the Column Setup window

• A column definition is defined in the client extension

• The client extension returns a non-null value

3. If the following conditions are true, then PSI uses the Column Setup window value:

• The client extension switch for a PSI window is set to Y

• A column definition is defined in the Column Setup window

• A column definition is defined in the client extension

• The client extension returns a null value

4. If the column definition in Column Setup is defined, and a column definition in the client extension is defined, and the switch is set to Y, the value that shows in PSI is determined as follows:

• If the client extension returns a non-null value for the PSI column, the client extension non-null value is displayed in PSI.

• If the client extension returns a null value, then whatever is defined in Column Setup is displayed in PSI.

5. If you want to display a blank in the PSI window for a numeric column, you must type the following in the Column Setup column definition: TO_NUMBER(null)

If either nothing, blank or null is specified in the Column Setup window for a numeric column definition, then the PSI window displays a zero.

8. You can display two text column values in one text column.

You cannot set up the PSI status windows to display more than three text columns. Instead, use the PSI client extension to concatenate two values (such as two dates) in a string. Assign the string to one of the three text columns.