Database Items are limited for any kind of Fast Formula.Apart from Oracle offered database items, We are not able to get the values.
For this kind of values which are not able to get by using database items, We can get it by executing query using table type value set.
Requirement:
Check Element Entry Value on
Particular Effective Date.
Solution:
Step 1:
Create Table Type value set
with query which needed to be executed from Fast Formula.
Where Clause:
Parameter which needed to pass from fast formula should be in
“:{PARAMETER.PARAMETER_NAME}”
i.e.,
PEEF.person_id=:{PARAMETER.PERSON_ID}
AND PEEF.ELEMENT_TYPE_ID in
(300000001208613,300000001344773)
AND to_date(:{PARAMETER.EFF_DATE},'YYYY/MM/DD')
BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
Step 2: Fast Formula Code to get the value from the query
l_element_entry_exists =
GET_VALUE_SET('ADIB_HOUSING_ELEMENT_EXIST','|=PERSON_ID='''||L_Person_Id_char||''''||'|EFF_DATE='''||l_effective_date1||'''')
Limitation:
Value set is
not bringing proper value in Fast Formula, if query
is exceeding certain amount of characters.
How can we use distinct in this kind of value set if my query return multiple rows?
ReplyDeleteHi,
DeleteYou can use disctinct in "Value Column Name"
Can we get a sumarize result?
ReplyDeleteHi , Can you please give me your detailed requirement ??
DeleteThank you Mounika
ReplyDeleteThanks,I got solution
ReplyDeleteGood Blog thanks for sharing this informative article.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
This comment has been removed by the author.
ReplyDeleteHi
ReplyDeleteI have a requirement to pull the Requisition Approved Date by using hcm extract.But Requisition Approved date is not in DBI(Database Item group).so,i have called value set and fast formulae to pull the value set value.but i am unable to get the value for Req Approved date.the given below is fast formulae will i have write
Fast Formulae :
DEFAULT FOR DATA_ELEMENTS IS EMPTY_TEXT_TEXT
INPUTS ARE DATA_ELEMENT_CODE (TEXT), DATA_ELEMENTS (TEXT_TEXT) , l (TEXT_TEXT)
RULE_VALUE = ' '
l_num = add_rlog(100, 45,'entry the approved date')
if (DATA_ELEMENTS.EXISTS('Extracts_Requisition_Id')) then
(
l_REQUISITION_ID = DATA_ELEMENTS['Extracts_Requisition_Id']
RULE_VALUE = GET_VALUE_SET_VALUE('SM_GET_APPROVED_DATE','|=P_REQUISITION_ID='''||l_REQUISITION_ID||'''')
)
l_num = add_rlog(101, 45 ,'failed approved date ')
RETURN RULE_VALUE
please help on to this..