Clarity

There are slices details for most CLOB fields but not PFM_CLOB_CURVES? How do I access the data for Targets (Budget, Role Allocations etc.)?

There was some discussion on this

starting 13.2 pma_financial_values is gone. do you know which table has replaced it?

Jorgen started a thread in LinkedIn, too.

It discuss on same topic, but I have not find any info that says how you can query in DB!

These queries might help you to incorporate the results you're looking for into your own query.  This is just for demonstration, and you should change the hard-coded values based upon what you actually have in your PFM_CLOB_CURVES table (i.e. replace 'PFM_INVESTMENTS', 'ROLE_DEMAND', and 5005004 below to values relevant to your own system and data).

This technique may also differ depending on whether you are using Oracle or MSSQL, so please use each one as appropriate.

Oracle:

select clobs.*

from pfm_clob_curves p, table( pfm_segments_fct( p.id, p.clob_curve) ) clobs

where p.id = clobs.id

     and p.table_name ='PFM_INVESTMENTS'

     and p.attribute = 'ROLE_DEMAND'

     and p.id = 5005004

Microsoft SQL Server:

select clobs.*

  from pfm_clob_curves p cross apply pfm_segments_fct( p.id, p.clob_curve, default, default ) clobs

where p.id = clobs.id

     and p.table_name ='PFM_INVESTMENTS'

     and p.attribute = 'ROLE_DEMAND'

     and p.id = 5005004

This isn't a published API as such, and the behaviour or existence of the pfm_segments_fct() used here from the database may change in future, but this is a way you can start to look into the data for your needs.

There is also a function that will take dates as inputs allowing you to create columns covering specific periods.  Leaving those dates as null parameters can also be used to give you the totals instead:

Oracle:

select p.id,

  pfm_sum_fct(p.clob_curve, trunc(sysdate), trunc(sysdate + 30)) as rate_for_next_30_days,

  pfm_sum_fct(p.clob_curve, null, null) as rate_total

from pfm_clob_curves p

where p.table_name ='PFM_INVESTMENTS'

     and p.attribute = 'ROLE_DEMAND'

     and p.id = 5005004

For Microsoft SQL Server the query is the same but you replace trunc() and sysdate with MSSQL date/time calls instead.  The rest is the same.

I hope that helps to get you started.

Thanks so much Nick!

So I tried sqltrace and did not find the actual query being executed.

I checked PFM functions you mentioned but did not try to put them the way you explained. This helps to get the data at least.

With regards to first query you mentioned, If I run first one, I did not get Operating Costs. It gave me Segment Start, Segment Finish and Rate. Not sure how to interpret that? Could you please help?

Segment Start: 42005

Segment Finish: 42370

Rate: 0.031709792

Second query was able to get me results similar to what I'm looking for.

I'm trying this and still it is not coming close to what I see in UI. Few cents are missing :-(

SELECT

TRUNC (SYSDATE, 'MM') MONTH_START,

ADD_MONTHS(TRUNC (SYSDATE, 'MON'), 1) MONTH_FINISH, p.id,

       pfm_sum_fct (p.clob_curve, TRUNC (SYSDATE, 'MM'), ADD_MONTHS(TRUNC (SYSDATE, 'MON'), 1))

          AS rate_for_this_month,

       pfm_sum_fct (p.clob_curve, NULL, NULL) AS rate_total

  FROM pfm_clob_curves p

WHERE     p.table_name = 'PFM_PORTFOLIOS'

       AND p.attribute = 'TOTAL_COST'

       AND

       p.id = 5004000

Figured out Segments. They are Julian dates. Still not sure what to do with Rate from first query...

SELECT p.*,

       clobs.*,

       PFM_DATE_FROM_JULIAN_FCT (clobs.segment_start) start_date,

       PFM_DATE_FROM_JULIAN_FCT (segment_finish) finish_date

  FROM pfm_clob_curves p, TABLE (pfm_segments_fct (p.id, p.clob_curve)) clobs

WHERE     p.id = clobs.id

       AND p.table_name = 'PFM_PORTFOLIOS'

       AND p.attribute = 'TOTAL_COST'

       AND p.id = 5004000

Guessing... Segment Sliced Rate is per Second.

Rate * 24 * 3600 * (PFM_DATE_FROM_JULIAN_FCT(segment_finish) - PFM_DATE_FROM_JULIAN_FCT( clobs.segment_start)) = total_cost

Yes, the rate is for a 24 hour period within the segment (it remains constant for each of the 24 hour day periods in each segment.. you can think of all the segments in a clob record as looking like a histogram visually when laid down next to eachother).  That was the factor that needed to be multiplied in that I refer to in my reply that overlapped the posting time with your own.

Segment start/finish values are Julian dates.  I meant to modify the query before I submitted it to include converting them back to regular looking (Gregorian calendar) dates so they would make more sense, but overlooked it before I posted:

...

  pfm_date_from_julian_fct(clobs.segment_start) as segment_start,

  pfm_date_from_julian_fct(clobs.segment_finish) as segment_finish

...

Actually working with segments isn't all that useful except for debugging/checking as they will span disparate ranges..

E.g. a segment might be for one day, the next segment could span a month, all for one 'record' (investment instance) and then the next record's first segment might be for a week, and the second one for 3 days.  When querying you can't do much real work with that information as it won't crosstab / tabulate effectively, it just gives you a look at the raw information in a more human readable manner.

So in that respect working with the pfm_sum_fct() where you can adhoc slice the clobs with dates that can span or slice segments into consistent blocks of time for all records in the results will be more likely what you would build into a query/report.

For the other query regarding the rate returned; I'll have to check into it further and see what I can find out.  Depending on the type of data being stored in the clob there may be further manipulation required (such as multiplying the values by another factor).

nick_darlington you wanna create a document (Tecdoc) or a Tuesday's tip on that please.

Using PFM_PORT_SLICE_FCT()

SELECT p.*,

       clobs.*

  FROM pfm_clob_curves p, TABLE (

  PFM_PORT_SLICES_FCT (p.id, 0, 'TOTAL_COST', p.object_id, 'MONTHLY', 12, to_date('1/1/2015', 'mm/dd/yyyy'), 'DEFAULT',  0 )

  ) clobs

WHERE     p.id = clobs.id

       AND p.table_name = 'PFM_PORTFOLIOS'

       AND p.attribute = 'TOTAL_COST'

       AND p.id = 5004000;

Hi All,

I just tried to run the query on a 14.3.

I get the following error :

ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur: character to number conversion error
ORA-06512: à "NIKU.PFM_SEGMENTS_FCT", ligne 51
ORA-06512: à "NIKU.PFM_SLICES_FCT", ligne 17
ORA-06512: à "NIKU.PFM_PORT_SLICES_FCT", ligne 31
ORA-06512: à ligne 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

Thanks for your help.

Ludovic

Hi Ludovic,

you probably need to check the NLS settings for your db-session:

SELECT USERENV ('language') FROM DUAL;

and whether the number format (used in your query - especially for the invoked function) matches the number format of data saved in your db.

Then altering the NLS-session parameters for your query could be of help, e.g.:

ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,';

HTH,

Sebastian

Thanks for your answer Sebastian.

I managed to fix the issue... but I don't remember how.

Thanks again anyway.