Financials

Customization Of Project Intelligence Using Client Extension

Project Management is a Web Based application for Oracle Projects, which allows project managers to supervise the full project lifecycle of a project. All the pages for Oracle Project Management have been developed using OA Framework. This module also hooks onto another module named project intelligence that provides dashboards styled reporting for Projects. For performance reasons, the SQL intensive data used for some reports is stored in de-normalized format. This article contains step by step instructions to add custom columns [called measures] in the Project Management Intelligence screen as shown below. Project Intelligence screen for reporting is an OA Framework page, but yet we do not need to use traditional approach of extending OA Framework here. This requirement can be implemented by a combination of PL/SQL and Personalization, by leveraging Client Extension feature of Project Intelligence.

Why is OA Framework extension to view object not required?

Project intelligence comes with an out of the box extension methodology, called "Client Extensions".

The values/columns shown in standard project intelligence reporting screen are called "Measures".

In case you wish to add custom measures, then you can leverage Client Extensions in Project Management.

Let’s say our business requirement is as shown in the image below

Before we get into details, let’s look at the brief steps for implementing this requirement

1. Define your Custom Measure by giving this a label.

2. Modify pl/sql package PJI_FP_CUST_PJP0. As the name suggests, Oracle expects this package to be customized. Effectively this is a custom hook

3. Personalize the Project Intelligence reporting tab, to add desired custom measures.

What is the purpose of customizing pl/sql API?

In this API, you can populate values into columns in table PJI_FP_CUST_PJP0, for the columns that begin with name CUSTOM.

There are 15 custom columns in this table, i.e. CUSTOM1....CUSTOM15

Will the project intelligence reporting screen display values from the table PJI_FP_CUST_PJP0?

Not really. Data from table is moved into PJI_FP_AGGR_PJP0. Event this table has columns with name CUSTOM1...CUSTOM15

How does data in CUSTOM Column values move from table PJI_FP_CUST_PJP0 to PJI_FP_AGGR_PJP0

You need to run following concurrent processes....

- "PRC: Refresh Project and Resource Base Summaries"

- "PRC: Refresh Project Performance Data"

- "PRC: Update Project and Resource Base Summaries"

- "PRC: Update Project Performance Data"

Do we always need to customize pl/sql package PJI_FP_CUST_PJP0 for adding custom measures?

This depends on your business requirement. Oracle Project Intelligence comes with out of the box seeded measures.

Hence, when you define a custom measure, you can also define a custom measure of type "Computation".

When creating a custom measure of type computation, you can define formulas that reference existing values from seeded measures

In case your business requirements can't be met from seeded measures, then you can customize the pl/sql API so that custom SQL Statements can be written to derive values for custom measures.

Navigate to Projects SuperUser responsibility

Select Menu Custom Measures

In this example, we will use Custom Measure 2 from the available 15 possible Custom Measures

Give a prompt for your custom measures

Given that we are modifying prompt of stored custom measure “Custom 2”, we need to populate the appropriate PTD and ITD values into this column2.

For this, you can write your logic in PL/SQL Package body PJI_PJP_SUM_CUST, as shown below. This pl/sql API is very well documented by Oracle's Project Intelligence product deevlopment team

INSERTINTO PJI_FP_CUST_PJP0 cust_i

(

WORKER_ID,

TXN_ACCUM_HEADER_ID,

PROJECT_ID,

PROJECT_ORG_ID,

………………

………………

)

SELECT

p_worker_id,

pjp0.TXN_ACCUM_HEADER_ID,

pjp0.PROJECT_ID,

pjp0.PROJECT_ORG_ID,

pjp0.PROJECT_ORGANIZATION_ID,

pjp0.PROJECT_ELEMENT_ID,

pjp0.TIME_ID,

pjp0.PERIOD_TYPE_ID,

pjp0.CALENDAR_TYPE,

pjp0.RBS_AGGR_LEVEL,

pjp0.WBS_ROLLUP_FLAG,

pjp0.PRG_ROLLUP_FLAG,

pjp0.CURR_RECORD_TYPE_ID,

pjp0.CURRENCY_CODE,

pjp0.RBS_ELEMENT_ID,

pjp0.RBS_VERSION_ID,

pjp0.PLAN_VERSION_ID,

pjp0.PLAN_TYPE_ID,

TO_NUMBER(NULL) CUSTOM1,

TO_NUMBER('13') CUSTOM2,

--Instead of hardcoding, call

--SQL or PL/SQL Function as per your business needs

--This has been hardcoded for the purpose of demo

TO_NUMBER(NULL) CUSTOM3,

TO_NUMBER(NULL) CUSTOM4,

TO_NUMBER(NULL) CUSTOM5,

TO_NUMBER(NULL) CUSTOM6,

TO_NUMBER(NULL) CUSTOM7,

TO_NUMBER(NULL) CUSTOM8,

TO_NUMBER(NULL) CUSTOM9,

TO_NUMBER(NULL) CUSTOM10,

TO_NUMBER(NULL) CUSTOM11,

TO_NUMBER(NULL) CUSTOM12,

TO_NUMBER(NULL) CUSTOM13,

TO_NUMBER(NULL) CUSTOM14,

TO_NUMBER(NULL) CUSTOM15

FROM

PJI_FP_AGGR_PJP0 pjp0

WHERE

pjp0.WORKER_ID = p_worker_id

;

Compile the package body

After compilation, run the two concurrent processes as shown below

PRC: Update Project Performance Data

PRC: Refresh Project Performance Data

All the steps for Data Preparation have been completed.

Now let us navigate to Project Manager responsibility, where we will do personalization to include the Custom Measure

Search and select a project, in this case ATZ Services

Navigate to Project Intelligence reporting tab

Select Task Summary, and click Go

Click on Personalize to create new view via personalization, so that custom measure can be added

Quickest way to create a View is by duplicating from existing view

Give this view a name, and add the two Custom Measures

 Apply the personalizations and navigate to Reporting page.

You will see the custom measures added, with their values coming as per extended pl/sql API.

Anil Passi

Add comment