How to Call ff_exec.run_formula in Oracle BI Publisher Report

Question

Summary:

Hello Oracle Experts,

I am working on Oracle Fusion Cloud BI Publisher (BIP) report and need to call the ff_exec.run_formula function to retrieve Fast Formula results within my data model. However, I am unsure about the correct way to invoke this function in BIP and whether there are any prerequisites or limitations when using it in a SQL-based data model.

Here are my specific questions:

* What is the correct syntax to call ff_exec.run_formula in a BIP report data model?
* Are there any parameters or context settings required for this function?
* Is there any restriction on calling it in SQL queries inside the BI Publisher data model?
* Are there alternative ways to fetch Fast Formula results in BI Publisher reports?

I would appreciate any guidance or examples from those who have successfully used ff_exec.run_formula in BI Publisher reports.

Thanks in advance!

Content (please ensure you mask any confidential information):

Version (include the version you are using, if applicable):

Code Snippet (add any code snippets that support your topic, if applicable):

Rahul Bhavikatti · Answer

Hello, any idea on the above query?

Rahul Bhavikatti · Answer

Hi,

Thank you for sharing this solution! We are on Oracle Fusion HCM Cloud, and we would like to implement this in our BIP Data Model. however, our main concern is whether FF_EXEC.INIT_FORMULA requires any HCM session context such as Legislative Data Group (LDG) or Business Group to be set before calling it inside the WITH FUNCTION block, and if yes, could you please share how you handled that in your implementation? Also, could you confirm that this is currently working in a live BIP report on Oracle Fusion Cloud, as this would really help us move forward with confidence.

Moamin Shadid · Answer

Check below sample of calling an FF from BI

WITH

-- Function to initilize and run formula

FUNCTION Get_FF_Values(

P_ASSIGNMENT_ID IN NUMBER

,L_EFF_DATE      IN DATE)

RETURN VARCHAR2

AS

L_FORMULA_ID     NUMBER;

L_ERR_MSG        VARCHAR2(4000);

L_RESULT         VARCHAR2(4000);

L_DATE_EARNED    DATE;

L_FINAL_RESULT   VARCHAR2(4000);

L_INPUTS         FF_EXEC.INPUTS_T;

L_OUTPUTS        FF_EXEC.OUTPUTS_T;

BEGIN

BEGIN

SELECT L_EFF_DATE 

  INTO   L_DATE_EARNED 

  FROM   DUAL;

SELECT FORMULA_ID

  INTO   L_FORMULA_ID

  FROM   FF_FORMULAS_F

  WHERE  FORMULA_NAME = '------------Your Formula Name---------------';

FF_EXEC.INIT_FORMULA(L_FORMULA_ID,L_EFF_DATE,L_INPUTS,L_OUTPUTS);

L_INPUTS(3).NAME  := 'L_EFF_DATE';

  L_INPUTS(3).VALUE := FND_DATE.DATE_TO_CANONICAL(L_DATE_EARNED);

  L_INPUTS(4).NAME  := 'HR_ASSIGNMENT_ID';

  L_INPUTS(4).VALUE := P_ASSIGNMENT_ID;

BEGIN

    FF_EXEC.RUN_FORMULA(L_INPUTS,L_OUTPUTS);

L_FINAL_RESULT :=

         L_OUTPUTS(1).VALUE|| ';' || L_OUTPUTS(2).VALUE|| ';'||L_OUTPUTS(3).VALUE|| ';'|| L_OUTPUTS(4).VALUE|| ';'|| L_OUTPUTS(5).VALUE|| ';'

                  ||L_OUTPUTS(6).VALUE|| ';'||L_OUTPUTS(7).VALUE|| ';' ||L_OUTPUTS(8).VALUE;

  EXCEPTION

    WHEN OTHERS

    THEN

      L_ERR_MSG := 'FF_EXEC.RUN_FORMULA Errors: ' || SQLERRM;

  END;

END;

IF L_FINAL_RESULT IS NOT NULL

THEN

  RETURN L_FINAL_RESULT;

ELSE

  RETURN L_RESULT || CHR(10) || L_ERR_MSG;

END IF;

EXCEPTION

WHEN OTHERS

THEN

L_ERR_MSG := SQLERRM;

RETURN L_ERR_MSG;

END;

-- Table to get Effective Date

WITH_DP AS (SELECT TRUNC(TO_DATE(TO_CHAR(:p_date, 'yyyy-MM-dd'), 'yyyy-mm-dd')) P_E_DATE

--TRUNC(TO_DATE(:p_date, 'yyyy-mm-dd')) P_E_DATE

FROM DUAL

)-- Main SQLSELECT PAPF.PERSON_NUMBER,

PAPF.PERSON_ID,

Get_FF_Values

(

PAAM.ASSIGNMENT_ID,

LEAST(NVL(PPOS.ACTUAL_TERMINATION_DATE,

(LEAST(NVL(PPOS.ACTUAL_TERMINATION_DATE,WITH_DP.P_E_DATE),WITH_DP.P_E_DATE))),

(LEAST(NVL(PPOS.ACTUAL_TERMINATION_DATE,WITH_DP.P_E_DATE),WITH_DP.P_E_DATE)))

) ORIGNAL_OUTPUT -- This logic will retrive values in a single column.. to Split data need to work at rtf level..

FROM PER_ALL_PEOPLE_F       PAPF,

PER_ALL_ASSIGNMENTS_M  PAAM,

PER_PERIODS_OF_SERVICE PPOS,

WITH_DP

WHERE PAPF.PERSON_ID = PAAM.PERSON_ID

AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID

AND PAAM.ASSIGNMENT_TYPE = 'E'

AND PAAM.PRIMARY_FLAG = 'Y'

AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'

AND PPOS.PERIOD_TYPE = 'E'

AND PPOS.PRIMARY_FLAG = 'Y'

AND PAPF.PERSON_NUMBER = :P_PERSON_NUMBER

AND WITH_DP.P_E_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND  PAPF.EFFECTIVE_END_DATE

AND WITH_DP.P_E_DATE BETWEEN PAAM.EFFECTIVE_START_DATE AND  PAAM.EFFECTIVE_END_DATEAND PPOS.DATE_START =

(SELECT MAX(PPOS1.DATE_START)

FROM PER_PERIODS_OF_SERVICE PPOS1

WHERE PPOS1.PERSON_ID = PPOS.PERSON_ID

AND PPOS1.PRIMARY_FLAG = 'Y'

AND TRUNC(PPOS1.DATE_START) <= WITH_DP.P_E_DATE)