My Oracle Support Banner

Error While Executing Fast Formula From PLSQL (Doc ID 2417716.1)

Last updated on JULY 05, 2018

Applies to:

Oracle Payroll - Version 12.1.3 and later
Information in this document applies to any platform.

Goal

On : 12.1.3 version R12.HR_PF.B.Delta.10, Core Payroll - General

Error While executing Fast Formula from PLSQL when Database Items are used.
Works fine when running from Applications.

ERROR
-----------------------
Error report:
ORA-20001: Data ELEMENT_NAME not found at line 3 of ELEMENTTEST

Cause: A SQL SELECT statement, obtained from the application
dictionary, returned no rows when executed.

Action: Please refer to your local support representative.
ORA-06512: at "APPS.HR_UTILITY", line 939
ORA-06512: at "APPS.FF_EXEC", line 2807
ORA-06512: at line 51

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create a Fast Formula with Database Item
2. Create Formula Results and attach to an element.
3. Add Element Entry to an Employee.
4. Run Quickpay - works fine.
5. Run the Fast Formula from PL/SQL - Errors

TEST CASE
--------------------------
Fast Formula:

alias ELEMENT_NAME as DB_Element_Name
default for DB_ELEMENT_NAME is 'X'
L_Msg = DB_Element_Name
L_Element_Name = DB_Element_Name
Return L_Element_Name


Error starting at line 1 in command:
DECLARE
  l_formula_id pay_status_processing_rules_f.formula_id%TYPE;
  l_f_inputs ff_exec.inputs_t;
  l_f_outputs ff_exec.outputs_t;
  v_eff_date DATE := NULL;
  l_MONTHLY_VALUE NUMBER;
  vactualvalue NUMBER :=0;
  l_effective_date DATE:=SYSDATE;
BEGIN


delete from fnd_sessions where trunc(effective_date)=trunc(sysdate);
commit;
declare
l_session_id number;
begin
select 1 into l_session_id from fnd_sessions where session_id =
userenv('sessionid');
exception
when no_data_found then
insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE)
values(userenv('sessionid'), trunc(SYSDATE));
end;
--
--

insert into fnd_sessions (
session_id,
effective_date)
values (userenv('sessionid'),l_effective_date);

--
-- Initialise the formula.
FF_EXEC.init_formula (p_formula_id => 76269,
p_effective_date => '31-MAY-2016',
p_inputs => l_f_inputs,
p_outputs => l_f_outputs);

IF l_f_inputs.COUNT > 0
  THEN
  FOR i IN l_f_inputs.FIRST .. l_f_inputs.LAST
  LOOP
  dbms_output.put_line('l_f_inputs (i).nam '||l_f_inputs (i).name);
  IF (l_f_inputs (I).NAME = 'DATE_EARNED')
  THEN
  l_f_inputs (I).VALUE := '31-MAY-2016';
  ELSIF (l_f_inputs (I).NAME = 'ELEMENT_TYPE_ID')
  THEN
  l_f_inputs (I).VALUE := 76785;
  END IF;
  END LOOP;
  END IF;
  FF_EXEC.run_formula (p_inputs => l_f_inputs,
  p_outputs => l_f_outputs,
  p_use_dbi_cache => false);
  FOR i IN l_f_outputs.FIRST .. l_f_outputs.LAST
  LOOP vactualvalue := l_f_outputs (i).VALUE;
  DBMS_OUTPUT.PUT_LINE ('L_VALUE ' || vactualvalue);
  END LOOP;
END;
Error report:
ORA-20001: Data ELEMENT_NAME not found at line 3 of ELEMENTTEST

Cause: A SQL SELECT statement, obtained from the application
dictionary, returned no rows when executed.

Action: Please refer to your local support representative.
ORA-06512: at "APPS.HR_UTILITY", line 939
ORA-06512: at "APPS.FF_EXEC", line 2807
ORA-06512: at line 51
 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.