Error While Executing Fast Formula From PLSQL
(Doc ID 2417716.1)
Last updated on JULY 17, 2024
Applies to:
Oracle Payroll - Version 12.1.3 and laterInformation 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 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
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-20XX',
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
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-20XX',
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! |
In this Document
| Goal |
| Solution |
| References |