Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Local Currency issue PPM 8.00 after upgrade from 7.5

SOLVED
Go to solution
Highlighted
roxor
Member

Local Currency issue PPM 8.00 after upgrade from 7.5

Hello friends,

 

I have upgraded PPM form 7.5 to 8.03 and one of my custom portlet is throwing SQL error. After a lot of investigation I realized that problem is caused by local currency settings in 'Open/Administration/Edit My profile'

 

For testing purposes I created now this small data source:

 

select 
fcst.plan_total_bse
, fs.currency_code TOTAL_BUDGET_ID
, fcst.plan_total_lcl
from 
FM_FINANCIAL_SUMMARY fs
inner join FM_FORECAST_ACTUAL_PERIOD_SUM fcst
on fs.forecast_actual_id = fcst.forecast_actual_id
where rownum < 50

 

This column is configured in the way as I was using in 7.5 PPM - in workbench data source editing, it is currency a all three columns from SQl query are used for set base and local value.

 

My local currency is EUR and Base currency is USD. Portlet with this data source shows one column. When I have set Default currency (USD) portlet is working. When I set my settings to local currency (EUR) this error is showing:

 

Cannot load this portlet's data due to a misconfigured SQL Query. (KNTA-10836) Please contact your administrator with the following information:
-- The following error is thrown by the database:
Fail to convert to internal representation
-- The SQL query that's being executed:
SELECT fcst.plan_total_bse TOTAL_BUDGET_BASE, fs.currency_code TOTAL_BUDGET_ID, fcst.plan_total_lcl TOTAL_BUDGET_LOCAL
FROM fm_financial_summary fs
left join fm_forecast_actual_period_sum fcst
on fs.forecast_actual_id = fcst.forecast_actual_id
WHERE rownum < 50
and 1=1
AND ROWNUM <= 400 ORDER BY TOTAL_BUDGET_LOCAL ASC
 

I will appreciate any suggestion how to set local currency in 8.00 PPM.

 

Thanks a lot,

r.

7 REPLIES
dirkf
Honored Contributor

Re: Local Currency issue PPM 8.00 after upgrade from 7.5

Hi Roxor,

 

I cannot help you much on your custom SQL but wanted to tell you my knowledge about the 'local' currency, since there seems to be widespread misunderstanding of the issue:

 

BASE currency in 'edit my profile' is pretty much clear - this is set upon installation.

 

LOCAL currency:  the currency associated with the region of the project

 

Example:

Base currency set to US $; region set to EMEA. EMEA currency is set to Euro.

A user that uses base currency in my profile will see all costs displayed in US$

A user that uses local currency in my profile will see all costs displayed in the regional currency Euro.

 

The design behind this is simple: A project is normally located in a region or country where the users are based. You wouldn’t create a project based in Australia if all the resources associated with this project are based in Romania. Therefore, a US-based company with a base currency of US $ would use the region Australia (or region APJ – you can split regions up in that you created regions AND countries) with the currency AUS $. Any resource working on this project ‘down-under’ now has the choice if he or she wants to view the projects cost in US $ or AUS $.

 

Maybe this will help - not sure.

Best regards,

Dirk

roxor
Member

Re: Local Currency issue PPM 8.00 after upgrade from 7.5

Hello, thanks for response. My problem is that after upgrade of PPM my local currency settings is not working. When I (or anyone) set profile to local currency, portlet crashes.

 

So my question is mainly about that, if there are some additional settings needs to be done after upgrade. I am really confused why it is not working.

 

My user inherit Region which is set to EUR. So I suppose that should be OK. Or there are some additional and I don't know about them.

 

Maybe is this somehow connected with multiligual installation? I have nothing installed except basic PPM.

 

Thanks a lot,

r.

dirkf
Honored Contributor

Re: Local Currency issue PPM 8.00 after upgrade from 7.5

Hi roxor,

 

please understand that USER region and PROJECT region are two different pairs of shoes. The setting of the region in the user profile is ONLY for the digits and the way that time and currency is displayed but NOT for the display of the currency used in the portlet.

 

It may well be that there is an issue displaying values in the portlets when for instance the delimiter is not as expected (example: A dot used where a comma is expected).

 

Check if the out-of-the-box portlets work. If yes, there is something wrong with the custom portlets.

 

Best regards,

Dirk

roxor
Member

Re: Local Currency issue PPM 8.00 after upgrade from 7.5

Hmmm, thanks, good point. OOTB portlet seems to work fine also in EUR currency. So I would say that you are right that my custom portlet is wrong or delimiter is not as expected...

 

I would say that problem is that my portlet is showing financial data which was in PPM 8.00 structurally changed (tables and approach to budget and benefits). And I did some changes in data source to be able to see all these budget and CAPEX and OPEX data. 

 

To be honest I am little bit confused with these changes when I need all the data but structure was changed. My queries are ok and working on databse but they are not working through PPM 8.00. That easy one on my first post is nice example of query which I have changed due to changes in PPM 8.00 and which is ok when you run it on database but don't work in PPM now.

dirkf
Honored Contributor

Re: Local Currency issue PPM 8.00 after upgrade from 7.5

Hi Roxor,

 

the query that you initially reported:

 

select
fcst.plan_total_bse
, fs.currency_code TOTAL_BUDGET_ID
, fcst.plan_total_lcl
from
FM_FINANCIAL_SUMMARY fs
inner join FM_FORECAST_ACTUAL_PERIOD_SUM fcst
on fs.forecast_actual_id = fcst.forecast_actual_id
where rownum < 50

 

runs on my 8.02 test-instance ;-) See attachment

 

Best regards,

Dirk

 

roxor
Member

Re: Local Currency issue PPM 8.00 after upgrade from 7.5

Hello Dirk, thanks for patience:)

 

That query is also running on my PPM SQL runner. This is why I think that my query is OK. Problem is that it is not working when I make portlet from it and change my profile settings to local currency.

 

Attached is that simple portlet which is not working when I have local currency in my profile.

 

Now I found one difference. In PPM 7.5 was that TOTAL_BUDGET_ID number and now in PPM 8.00 it is string like GBP, USD or EUR. Maybe this is problem!

 

roxor
Member
Solution

Re: Local Currency issue PPM 8.00 after upgrade from 7.5

OK so finally I have found solution! In my query I was still trying to use CURRENCY_CODE but for right local currency display there is need of CURRENCY_ID.

 

Thanks for your help Dirk.

//Add this to "OnDomLoad" event