BEN_FN_GET_CHAR_VALUE
In this session, we will cover the usages of Benefit Function, BEN_FN_GET_CHAR_VALUE.
Since we have already discussed about the basic functionality of the Fast Formula Functions, Contexts and Parameters in one of our previous session, we are not going to discuss in detail about that. If you do not understand the basic functionality of Fast Formula Function, we recommend you to read our previous session before starting here.
As of today, the following information is collected from different sources. We always recommend you to contact the Oracle Support to get the accurate and updated information. If you think any of the information is not up to date or accurate, please let us know, we will correct the mistakes.
Since this function, BEN_FN_GET_CHAR_VALUE is one of the important and complicated functions of Benefit module; we would like to explain the functionality here in detail.
This function, BEN_FN_GET_CHAR_VALUE helps to extract a data from a column and it supports 3 Context and 6 Parameters to extract the value.
The contexts are:
1) HR Assignment
2) Effective Date
3) Business Group ID.
Since these Contexts are passed internally and customer has no control over them, we don’t want to discuss here the details. The only thing that the user needs to understand is, this function cannot be used unless the Fast Formula Type supports all these Contexts.
For example, none of the Compensation Formula Types supports Business Group Id as context and therefore, this Function cannot be used in Compensation Fast Formula types.
In case, if you want to use this function in one of the Compensation Fast Formula, you need to define a (child) Fast Formula in BEN type and call the (child) Fast Formula from Compensation Fast Formula by passing Business Group Id as context.
The parameters of BEN_FN_GET_CHAR_VALUE:
1) TABLE NAME
2) COLUMN NAME
3) PLAN NAME
4) OPTION NAME
5) KEY NAME
6) KEY VALUE
these Parameters names are provided here just for reference. Since the user does not use this Parameter name anywhere in the formula, please remember them as Parameter 1 to Parameter 6.
1) Parameter 1: The first parameter is a required parameter and is reserved for the Table Name. Please use the Table name in upper case.
2) Parameter 2: This parameter is a required parameter. This is reserved for Column Name. In case you do not need to pass the Column Name (For example, the Table, ‘BEN_CVRD_DPNT_CTFN_PRVDD’ does not need a Column Name as it is returning ‘Y’ or ‘N’) you need to pass either a ‘NULL’ value or any dummy value.
3) Parameter 3: This is an optional parameter. This parameter is mostly used for Plan Name but in some cases, the parameter can be used for other values.
In some cases, you do not need this value.
In some cases, you need to pass a hard coded value. For example, BEN_FN_GET_CHAR_VALUE ('BEN_PRTT_RT_VAL', 'AGG_RT_VAL' , 'ACTIVITY_BASE_RATE' ).
In some other cases, you do not need a value for this parameter but need to pass value for supporting subsequent parameters. For example you need to pass values for parameter 1, 2, 5 and 6. In this case you can pass ‘NULL’ as a value to this and 4th parameter. BEN_FN_GET_CHAR_VALUE ('PAY_TIME_PERIODS', 'PAYROLL_TYPE' , 'NULL', 'NULL', 'START_DATE' , '2017/01/01' ).
4) Parameter 4: This is an optional parameter. This parameter is used for Option Name. It is not necessary this parameter is always used for Option Name. In some cases, you do not need this value, in some cases, you need to pass a hard coded value. In some other cases, you need to pass ‘NA’ to support any option name. In some other cases, you do not need a value for this parameter but need to pass value for subsequent parameters. For example you need to pass values for parameter 1, 2, 5 and 6. In this case you can pass ‘NULL’ as a value to this and 3rd parameter.
5) Parameter 5: This is an optional parameter. This is an extra parameter. This parameter is generally passed as name parameter along with 6th parameter as name and value pair.
6) Parameter 6: This is an optional parameter. This is usually passed along with 5th parameter. This parameter generally passed as value parameter of name and value pair.
When there is no value found, the function returns ‘NO_DATA_FOUND’. There are some exceptions to that where the function is able to location the Plan Name and Option Name but there is no data for the Assignment or for the 5th and 6th parameter, then the function returns ‘N’ as return value.
Table Name
|
Column Name
|
Description
|
PER_JOB_EXTRA_INFO_F
|
JEI_ATTRIBUTE1 .. 30
|
Data from Job Extra information table is extracted for information Type 'JOB_KFF' for the current assignment and the effective date.
Only required parameters are Table Name as the first parameter and Column Name as the second parameter
|
JEI_INFORMATION1 .. 30
| ||
PER_ALL_ASSIGNMENTS_F
|
All ATTRIBUTE Columns
|
To extract value from this table, all you need to pass Table Name as the first parameter and Column Name as the second parameter
|
ACTION_CODE
| ||
EXPENSE_CHECK_ADDRESS
| ||
INTERNAL_BUILDING
| ||
INTERNAL_FLOOR
| ||
INTERNAL_LOCATION
| ||
INTERNAL_MAILSTOP
| ||
INTERNAL_OFFICE_NUMBER
| ||
PRIMARY_ASSIGNMENT_FLAG
| ||
PRIMARY_FLAG
| ||
PRIMARY_WORK_RELATION_FLAG
| ||
PRIMARY_WORK_TERMS_FLAG
| ||
REASON_CODE
| ||
RETIREMENT_AGE
| ||
RETIREMENT_DATE
| ||
SYSTEM_PERSON_TYPE
| ||
BEN_PRTT_ENRT_RSLT
|
ADMIN_CATEGORY_CD
|
The data is extracted for an assignment, effective date, Plan Name and Option Name.
Plan Name and Option Name are required columns.
In case, if you do not want to pass the Option Name you can use 'NA' to avoid the Option Name validation.
If you are passing wrong Plan Name or Option Name then the function will return 'NO_DATA_FOUND'
If there is no data found in Enrollment Result for the Assignment, Effective Date, Plan Name and Option Name this function will return 'N'
The parameters are:
1) Table Name
2) Column Name
3) Plan Name
4) Option Name
|
BNFT_AMT
| ||
BNFT_NNMNTRY_UOM
| ||
BNFT_TYP_CD
| ||
COMP_LVL_CD
| ||
ENROLLED
| ||
ENRT_CVG_STRT_DT
| ||
ENRT_CVG_THRU_DT
| ||
IMPTD_INCM_CALC_CD
| ||
PRTT_ENRT_RSLT_ID
| ||
RPLCS_SSPNDD_RSLT_ID
| ||
SS_CATEGORY_CD
| ||
SSPNDD_FLAG
| ||
SVNGS_PLN_FLAG
| ||
UOM
| ||
ORGNL_ENRT_DT
| ||
ERLST_DEENRT_DT
| ||
ENRT_OVRID_THRU_DT
| ||
INTERIM_FLAG
| ||
ENRT_OVRIDN_FLAG
| ||
BEN_ELIG_CVRD_DPNT
|
CVG_STRT_DT
|
All the conditions for table 'BEN_PRTT_ENRT_RSLT' also applied to this table.
Along with the parameter Table Name, Column Name, Plan Name, optional (NA) Option Name, you need to pass the dependent full name as the 5th parameter.
The dependent name is validated in LIKE condition; even partial name can be passed. Please note the partial name may not guarantee information of correct dependent.
|
CVG_THRU_DT
| ||
DPNT_PERSON_ID
| ||
ENROLLED
| ||
RLNSHP_CD
| ||
BEN_CVRD_DPNT_CTFN_PRVDD
|
Along with Table Name, Plan Name, Option Name and dependent name, you need to pass certification name as 6th parameter. The function either return 'Y' or 'N'.
If there is any issue with your parameters like Plan Name, you get 'NO_DATA_FOUND' as result.
Since there is no Column Name required and it is a 2nd parameter, you can pass a dummy value or a ‘NULL’ value to the Column Came.
| |
CMP_SALARY_COMPONENTS
|
COMPONENT_APPROVED
|
For This table, along with Table and Column Name, you need to pass 5th parameter as 'COMPONENT_REASON_CODE' and 6th parameter as Component Reason code. Please note it is a code not the name.
So you need to pass Table Name, Column Name (3rd and 4th parameter), any dummy value or ‘NULL’ value .
|
COMPONENT_REASON_CODE
| ||
CHANGE_AMOUNT
| ||
CHANGE_PERCENTAGE
| ||
PER_CONTACT_RELSHIPS_F
|
DAUGHTER_COUNT
|
This is very similar to table 'CMP_SALARY_COMPONENTS'. For this table you need to pass 5th parameter as 'LEGISLATION_CODE' and a Legislation Code for 6th parameter.
In summary, Table Name, Column Name, any dummy value or ‘NULL’ for 3rd and 4th parameter,
'LEGISLATION_CODE' and a Legislation Code for 5th and 6th parameter.
|
OTHERS_COUNT
| ||
SON_COUNT
| ||
SPOUSE_COUNT
| ||
DP_COUNT
| ||
DP_MALE_COUNT
| ||
DP_FEMALE_COUNT
| ||
ADOPTED_CHILD_COUNT
| ||
STEP_CHILD_COUNT
| ||
FOSTER_CHILD_COUNT
| ||
BEN_PER_LE_HABITS_COV_F
|
COORD_MED_CVG_END_DT
|
All you need to pass is Table Name and Column Name to get a value from this table.
|
COORD_MED_CVG_STRT_DT
| ||
COORD_MED_EXT_ER
| ||
COORD_MED_INSR_CRR_IDENT
| ||
COORD_MED_INSR_CRR_NAM
| ||
COORD_MED_PL_NAME
| ||
COORD_MED_PLN_NO
| ||
COORD_NO_CVG_FLAG
| ||
CVRD_IN_ANTHR_PL
| ||
DISABILITY_STATUS
| ||
DPDNT_ADOPTION_DATE
| ||
DPDNT_VLNTRY_SVCE_FLAG
| ||
ON_MILITARY_SERVICE
| ||
RECEIPT_OF_DEATH_CERT_DATE
| ||
REGISTERED_DISABLED_FLAG
| ||
STUDENT_STATUS
| ||
TOBACCO_TYPE_USAGE
| ||
PER_JOB_LEG_F
|
LEGISLATION_CODE
|
This table needs all the parameters along with the Table and Column Name. The 3rd parameter must be 'JOB_ID’ and 4th parameter can be any value or a ‘NULL’ value, the 5th parameter must be 'INFORMATION_CATEGORY' and the 6th parameter must be Information Category.
|
INFORMATION_CATEGORY
| ||
PER_JOB_LEG_F .. 30
| ||
ATTRIBUTE_CATEGORY
| ||
ATTRIBUTE1 .. 30
| ||
INFORMATION_NUMBER1 .. 20
| ||
INFORMATION_DATE1 .. 15
| ||
ATTRIBUTE_NUMBER1 .. 20
| ||
ATTRIBUTE_DATE1 .. 15
| ||
CMP_SALARY
|
SALARY_BASIS_CODE
|
All you need to pass is Table name and Column Name to get a value from this table.
|
SALARY_BASIS_ID
| ||
SALARY_ANNUALIZATION_FACTOR
| ||
GRADE_RATE_ID
| ||
COMPONENT_USAGE
| ||
NAME
| ||
DATE_FROM
| ||
DATE_TO
| ||
SALARY_AMOUNT
| ||
ACTION_ID
| ||
ACTION_REASON_ID
| ||
PER_JOB_LEG_F
|
LEGISLATION_CODE
|
This table expects all the parameters along with Table name and Column Name.
The 3rd parameter must be 'JOB_ID', 4th parameter is the job id, please note it is id not the name. 5th parameter is 'INFORMATION_CATEGORY' and the sixth parameter is Information Category.
|
INFORMATION_CATEGORY
| ||
INFORMATION1 .. 30
| ||
ATTRIBUTE1 .. 30
| ||
INFORMATION_NUMBER1 .. 20
| ||
INFORMATION_DATE1 .. 15
| ||
ATTRIBUTE_NUMBER1 .. 20
| ||
ATTRIBUTE_DATE1 .. 15
| ||
BEN_BENEFIT_RELATIONS_F
|
BENEFIT_RELATION_ID
|
This table needs all the parameter along with Table and Column Name.
The 3rd parameter and 4th parameter can be any dummy value or ‘NULL’ Value. It is not significant.
The 5th parameter must be either ‘BENEFIT_RELATION_NAME' or 'BENEFIT_REL_SYSTEM_CD'.
If the 5th parameter is 'BENEFIT_RELATION_NAME' then the 6th parameter must be Benefit Relationship name. If the 5th parameter is 'BENEFIT_REL_SYSTEM_CD' then the 6th parameter must be Benefit Relation System code.
|
EFFECTIVE_START_DATE
| ||
EFFECTIVE_END_DATE
| ||
BENEFIT_RELATION_NAME
| ||
BENEFIT_REL_SYSTEM_CD
| ||
PRIMARY_REL
| ||
STATUS
| ||
ORIGINAL_SOURCE
| ||
UPDATED_SOURCE
| ||
LEGAL_ENTITY_ID
| ||
PERSON_ID
| ||
REL_PRMRY_ASG_ID
| ||
BRN_ATTRIBUTE_CATEGORY
| ||
BRN_ATTRIBUTE1 .. 30
| ||
BRN_ATTRIBUTE_NUMBER1 .. 20
| ||
BRN_ATTRIBUTE_DATE1 .. 15
| ||
BEN_PER_IN_LER
|
PER_IN_LER_ID
|
This table needs all the parameter along with Table and Column Name.
The 3rd parameter and 4th parameter can be any dummy value or ‘NULL’ value. It is not significant.
The 5th parameter must be 'BENEFIT_RELATION_NAME'.
The 6th parameter must be Benefit Relationship name.
|
BENEFIT_RELATION_ID
| ||
LF_EVT_OCRD_DT
| ||
PROCD_DT
| ||
STRTD_DT
| ||
VOIDD_DT
| ||
PERSON_ID
| ||
BCKT_DT
| ||
CLSD_DT
| ||
NTFN_DT
| ||
LEGAL_ENTITY_ID
| ||
BENEFIT_REL_SYSTEM_CD
| ||
PRIMARY_REL
| ||
REL_PRMRY_ASG_ID
| ||
NAME
| ||
LER_ID
| ||
PAY_REL_GROUPS_DN
|
PAYROLL_ASSIGNMENT_ID
|
This table needs all the parameter along with Table and Column Name.
The 3rd parameter and 4th parameter is not significant.
The 5th parameter must be either 'GROUP_TYPE' or no value.
The 6th parameter can be a group type code.
If the 5th parameter is not passed, the function uses 'A' as default group type, there is no need for 6th parameter.
|
PAYROLL_TERM_ID
| ||
PAYROLL_RELATIONSHIP_ID
| ||
TERM_ID
| ||
LEGAL_EMPLOYER_ID
| ||
ASSIGNMENT_ID
| ||
PAY_ELEMENT_ENTRIES_F
|
AGGREGATE
|
This Table and Column fetches the sum of element entry values.
The 3rd and fou4th parameters are required and the 5th and 6th parameters are optional.
If the 3rd parameter is Element Name then the 4th parameter is input value name.
5th parameter, From date and 6th parameter, End Date are optional. The dates must be in YYYY/MM/DD format. If the dates are not passed, effective date will be used as the default date.
|
BEN_PRTT_RT_VAL
|
AGG_RT_VAL
|
Beside the Table and column name you need to pass the third parameter as either a plan name or 'ACTIVITY_BASE_RATE'
If the third parameter is a plan name, you can pass the fourth parameter as option name or 'NA’ for any Option. You can pass optional From Date and To Date as 5th and 6th parameters. The dates must be in YYYY/MM/DD format. If you are not passing any dates, the effective date will be used as the default date.
If the 3rd parameter is 'ACTIVITY_BASE_RATE', then you need to pass the option name as 4th parameter and optional From Date and To Date as the last 2 parameters
|
AGG_CMCD_RT_VAL
| ||
PAY_DIR_CARDS
|
TRU
|
Table name and column names are the only parameters need for this table.
|
CARD_ID
| ||
PAY_TIME_PERIODS
|
PAYROLL_ID
|
Along with Table and Column Name you need to pass the 5th parameter as 'START_DATE' and the 6th parameter is a date value in YYYY/MM/DD format for the Start Date.
Since you may not pass the 5th parameter without 3rd and 4th, you can pass any dummy value.
|
PAYROLL_TYPE
| ||
PERIOD_NUM
| ||
PERIOD_NAME
| ||
START_DATE
| ||
END_DATE
| ||
CUT_OFF_DATE
| ||
DEFAULT_PAYDATE
| ||
TOTAL_TP_IN_CALENDAR_YEAR
|
Hello Tilak and Lakshmi,
ReplyDeleteWe urgently need your help to write fast formula to fetch stocks and grants information from Manage Stock Grants work area to Compensation Worksheet in Comp module - with value set defined in fast formula, where stocks DBI are array elements.
Can you please help clarify how to collect shares granted per employee per grant type?
Warm Regards
Shyam
I bit confused with your question. Are you asking me the DBI names , if so, you can use CMP_STOCK_DATA_RGE_ASG_XXXXXXXXXXXX DBIS, it is an array (range) DBIs and uses the context of , START_DATE, END_DATE, PERSON_ID and HR_ASSIGNMENT_ID.
DeleteYou can use the CMP_STOCK_DATA_RGE_ASG_GRANT_TYPE to filter it.
If you are ask me, how to use the array DBI, i already have a blog on it.
Please provide me more information to address your issue.
We have a requirement to fetch Stock Granted information into a compensation worksheet based on the Date and the Grant Type. Where, a person can have many Grant types (which is array element). We are not able to pass Grant type dynamically to the fast formula. And hence need your inputs on how to pass Grant Type dynamically?
ReplyDeleteThanks a lot for helping us.
Warm Regards
Shyam
DeleteYou cannot pass the any parameter to Fast formula but
1)you can define the grant type into user table and get the value by using the function ‘GET_TABLE_VALUE’
2) If you already have the grant type in any other table, you can get the value by using the value set (please see my Sep, 2016 blog for value set details).
3) If you have the grant type in any of the table that supported by BEN function BEN_FN_GET_CHAR_VALUE then you can define a child formula using BEN ff type and get the value using the function BEN_FN_GET_CHAR_VALUE
Let me know if my reply does not help you issue.
Thank you Tilak and Lakshmi. We kind of hard coded the Grant type and used Current Date - 1 function to gather "Shares Granted per employee per year" into a column in Compensation Worksheet. We will continue to pursue to fine-tune this FF and update you once done.Look forward to interacting with you more in the coming days!
ReplyDeleteWarm Regards
Shyam
Hi Tilak and Lakshmi, In Compensation module, Our client wants to remove worksheet access from planning managers immediately after submission. We want to use Compensation Default Access level Formula type to build this requirement. Can you please help with some suggestions on how to proceed? Thanks! Basically, change worksheet access to "NO Access" Based on Manager "Submit" submission status
ReplyDeletePlease consider relevant blog to post questions. It could be useful to other readers.
DeleteI can help assisting specific technical questions related to Fast formula.
Help writing Fast Formula or functional solutions are beyond the scope of my work here.
sure Tilak, sorry about that. I understand. You are doing a great service to the Tech community. Appreciate it! Hope Oracle recognizes your service in the Open World kind of big stage. My best wishes !!
ReplyDeletethanks
DeleteHi Tilak and lakshmi,
ReplyDeleteI wrote a formula using this function.
But it returns 'N'
U said missing assignment might be a reason.
Anything to not include assignment.
I can not advise much with your information.
DeletePlease check your data then check your context values in your formula.
Hi Tilak & Lakshmi,
ReplyDeleteI'm using ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT','ORGNL_ENRT_DT','PlanName,'NA',' ',' ') to get the Original Enrollment Date for a Life Event Plan in my fast formula. But when I run the event I'm encountering below error
Formula , line 32, an arithmetic, string, or conversion error occurred. The value exceeded the allowable range. For example, a string value may be too long.
Line 32 is where that function is being used. Should I pass additional parameters to get the value?
Thanks for your help,
Kal
please try ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT','ORGNL_ENRT_DT','PlanName','NA' )
DeleteLet me know if it is not working and provide me what is in line 32
I found the issue by placing the function directly in Log statement. It was returning date as N for not yet enrolled plans. I was trying to convert it to Date.
Deleteorig_enrl_dt : 2014-07-01
enrolled: Y
orig_enrl_dt : N
enrolled: N
I will correct my formula accordingly.
Again, thanks for your help and your blogspot. It's really helpful.
Hai Lakshmi,
ReplyDeleteI am using the function in benefits for rate periodization FF with below parameters.Could you please help me to know how function behaves for last 2 parameters.
BEN_FN_GET_CHAR_VALUE('PAY_TIME_PERIODS' --2017-NOV-20
,'START_DATE'
,' '
,' '
,'START_DATE'
,to_char(l_enrt_cvg_strt_dt)
Thanks
The value from PAY_TIME_PERIODS cannot be extracted without the last two parameters. The parameter START_DATE works as effective date to get the values from date tracked tables. In other word the value from payroll tables are extracted as of this date.
DeleteYou have not mentioned that you have any issues, so I believe your code works as expected.
In case if you have any issue, please try to pass the date in YYYY/MM/DD format.
I need help getting enrollment coverage start date in the fast formula and want to calculate coverage amount based on age. Thanks in advance.Raja
ReplyDeleteplease be more specific. The blog has detail information about how to get the values from BEN_PRTT_ENRT_RSLT->ENRT_CVG_STRT_DT.
DeleteIf you are not able to use that, please let me know in detail.
I tried to get enrollment coverage date using two methods but both did not provide the actual value.
ReplyDelete1) Database Item directly : BEN_PEN_ENRT_CVG_STRT_DT
2)ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT','ENRT_CVG_STRT_DT',l_pl_name, 'NA' ) .Thanks Raja
lease try to understand that you need to provide as much possible information.
DeleteIf you start providing limited information 'need to know' basis then I may not able to help you.
What you mean by actual value.
What are you expecting and what are you getting.
What makes you think it is not correct value.
what is the formula type ?
Your DBI, BEN_PEN_ENRT_CVG_STRT_DT uses the following contexts.
EFFECTIVE_DATE, PL_ID, OPT_ID, LER_ID, HR_ASSIGNMENT_ID, EFFECTIVE_DATE and BUSINESS_GROUP_ID.
Do they set with correct values ?
The life event date is 12-17-2017 and enrollment coverage start date is 01-01-2018. I need to get the calculate coverage amount based on enrollment coverage amount. Below is the fast formula
Delete/*************************************************************************************************
FORMULA NAME: VAR_CVG_2X
FORMULA TYPE: Coverage Amount Calculation
DESCRIPTION: Formula derives coverage as factor of person’s age.
Change History:
Name Date Comments
--------------------------------------------------------------------------------------------------------------------------
********************************************************************************************************/
default for CMP_ASSIGNMENT_SALARY_AMOUNT is 0
default for CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR is 1
Default for PER_PER_DATE_OF_BIRTH is '01-JAN-1951' (date)
default for l_hold is 0
default for l_output is 0
default FOR BEN_PEN_ENRT_CVG_STRT_DT_DN is '1951/01/01 0:00:00'(date)
default FOR BEN_PEN_ENRT_CVG_STRT_DT is '1951/01/01 0:00:00'(date)
DEFAULT for BEN_LER_NAME is 'ABC'
DEFAULT for BEN_PLN_NAME is 'XYZ'
DEFAULT for BEN_OPT_NAME is 'XYZ'
l_effective_date = '1951/01/01 00:00:00' (date)
l_age = 0
l_sal = 0
l_ann_fctr = 0
l_dob = '4712/12/31 00:00:00' (date)
l_hold = 0
l_cvg = 0
l_output = 0
l_enrt_cvg_strt_dt='1951/01/01 0:00:00' (date)
l_ler_name = 'ABC'
l_pl_name = 'XYZ'
l_opt_name = 'XYZ'
l_ler_name = BEN_LER_NAME
l_pl_name = BEN_PLN_NAME
l_opt_name = BEN_OPT_NAME
l_enrt_cvg_strt_dt = BEN_PEN_ENRT_CVG_STRT_DT
l_comp_factor = 2
l_sal = CMP_ASSIGNMENT_SALARY_AMOUNT
l_ann_fctr = CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR
l_le_date_char = ben_fn_get_char_value('BEN_PER_IN_LER','LF_EVT_OCRD_DT'
,' '
,' '
,'BENEFIT_RELATION_NAME'
,'DFLT')
l_hold = l_comp_factor * l_sal * l_ann_fctr
IF (l_hold >= 500000 ) THEN
(l_output = 500000 )
ELSE
(l_output = l_hold )
l_dob = PER_PER_DATE_OF_BIRTH
l_effective_date = GET_CONTEXT(EFFECTIVE_DATE, to_date(l_le_date_char))
/*l_age = (months_between(l_effective_date,l_dob)/12) */
l_age = (months_between(l_enrt_cvg_strt_dt,l_dob)/12)
IF l_age >= 65 and l_age <70 THEN
(
l_cvg = l_output * 0.67
)
ELSE IF l_age >= 70 and l_age < 75 THEN
(
l_cvg = l_output * 0.45
)
ELSE IF l_age >= 75 and l_age < 121 THEN
(
l_cvg = l_output * 0.35
)
l_cvg = roundup(l_cvg,-3)
l_log_data = ess_log_write('l_age : '||to_char(l_age))
l_log_data = ess_log_write('l_hold : '||to_char(l_hold))
l_log_data = ess_log_write('l_cvg : '||to_char(l_cvg))
l_log_data = ess_log_write('l_le_date_char : '||l_le_date_char)
l_log_data = ess_log_write('l_enrt_cvg_strt_dt : '||to_char(l_enrt_cvg_strt_dt,'MM-DD-YYYY'))
l_log_data = ess_log_write('l_enrt_cvg_strt_char : '||l_enrt_cvg_strt_char)
l_log_data = ess_log_write('BEN_PEN_ENRT_CVG_STRT_DT : '||to_char(BEN_PEN_ENRT_CVG_STRT_DT))
return l_cvg
hr Raja,
DeleteI am sorry, I can not help you as I am not getting any information from you.
Still I am not sure what you got out of your formula as coverage date.
The formula, you have provided does not use ben_fn_get_char_value for BEN_PRTT_ENRT_RSLT.
You need to print all the context and check why the db is not getting the coverage date.
Thanks for using the blog.
Thanks Tilak for taking time but I am trying to get Enrollment Coverage start date by using dbi BEN_PEN_ENRT_CVG_STRT_DT. Do i need to use get_context to get the values for BEN_PEN_ENRT_CVG_STRT_DT
DeleteThe detail information about the context and DBI are already available in the Blogs.
DeleteHi Tilak and Lakshmi ,
ReplyDeleteCan some one help me out on 'BEN_PEN_ENRT_CVG_STRT_DT','BEN_PEN_ORGNL_ENRT_DT ' DBI items are getting default( '1951/01/01 00:00:00') value not actual value.
Is there any problem with DBI items or below code.
FF type: Enrollment Coverage start date / Rate start date
FF code :
default for BEN_LER_NAME is 'XXX'
DEFAULT FOR BEN_PEN_ORGNL_ENRT_DT is '1951/01/01 00:00:00' (DATE)
DEFAULT FOR BEN_PPL_LF_EVT_OCRD_DT is '1951/01/01 00:00:00' (DATE)
DEFAULT FOR BEN_PEN_ENRT_CVG_STRT_DT is '1951/01/01 00:00:00' (DATE)
DEFAULT FOR BEN_PIL_CLSD_DT is '1951/01/01 00:00:00' (DATE)
v_eff_cove_date=GET_CONTEXT(EFFECTIVE_DATE, to_date('1951/01/01 00:00:00'))
v_eff_cove_date = add_months(v_eff_cove_date,1)
CHANGE_CONTEXTS(EFFECTIVE_DATE = v_eff_cove_date)
v_coverage_begin_date = BEN_PEN_ENRT_CVG_STRT_DT
v_original_start_date = BEN_PEN_ORGNL_ENRT_DT
v_lf_evt_ocrd_date = BEN_PPL_LF_EVT_OCRD_DT
v_ben_ler_name = BEN_LER_NAME
V_DATA = ESS_LOG_WRITE( '---- v_coverage_begin_date: ' || to_char(v_coverage_begin_date))
V_DATA = ESS_LOG_WRITE( '---- v_original_start_date: ' || to_char(v_original_start_date))
if v_ben_ler_name = 'Salary Change' then
(
V_DATA = ESS_LOG_WRITE( '---- v_ben_ler_name: ' || v_ben_ler_name)
v_coverage_begin_date = v_original_start_date
V_DATA = ESS_LOG_WRITE( '---- v_coverage_begin_date_inside loop : ' || to_char(v_coverage_begin_date))
)
return v_coverage_begin_date
I am running the process through 'Evaluate Life Event Participation' and following screen shot is from log file.
The person is eligible to participate and was previously eligible.
---- v_coverage_begin_date: 1951-01-01T00:00:00.000Z
---- v_original_start_date: 1951-01-01T00:00:00.000Z
---- v_ben_ler_name: Salary Change
---- v_coverage_begin_date_inside loop : 1951-01-01T00:00:00.000Z
The resulting date Enrollment Coverage Start Date 1951-01-01 for the compensation object Program: Health and Welfare - Plan: Waive Medical Coverage - Option: is earlier than the life event occurred date 2018-07-01.
A valid program or plan year period is missing for this compensation object. This error occurred in the package ben_enrolment_requirements.enrolment_requirements.
----------------------------------------------------------------------
Benefits Statistical Information
----------------------------------------------------------------------
Processed persons 0
Errored persons 1
----------------------------------------------------------------------
Thanks in advance for your suggestions on this.
Hi Lakshmi,
ReplyDeleteI need suggestion on Post election edit formula. I have a requirement to check the enrollments for dependent(Spouse,Children) along with the employee. If Employee select child A in the medical plan and child B in the Vision plan, It should error out after hitting save in the Enrollments page(Benefits->Enrollments->search for a person->Enrollments in task bar). In my Fast Formula I have used this ben_fn_get_char_value function to check the enrollments for employee and after changing the contexts to Contact dependent person ID, checking the enrollments for dependents. I am getting wrong values means getting Y even though the person is not enrolled. Please suggest me for the solution.
Hi Laskshmi,
ReplyDeleteI need a suggestion on Rate peridization formula. I have a requirement to to reset the coverage amounts to zero for Employees during Open enrollment. Employees will carry over elections from last year but the amounts has to be reset to zero. I was able to reset it to zero but if employee selects another option and selects the original election one more time, the coverage amounts were overwritten from last year. Any inputs will help.
Thanks,
This was an amazing blog. It had all the relevant information. Thankyou for sharing it. Here is a referred blog that I have found same as yours oracle fusion hcm training. Actually, I was looking for the same information on internet and i found your blog quite interesting and relevant.
ReplyDeletehow to get lastupdatedby ,person number and person name in different column
ReplyDeletelast updated comes from per_users that has person id too.
DeleteThere is no direct way to get the information.
Can we use the function BEN_FN_GET_CHAR_VALUE in payroll type fast formulas?
ReplyDeleteIt is a tricky one. The function uses 6 parameters, beside the parameter it also uses 3 context, that are effective date, assignment id, business group id. Business Group id is not a common context in other formula types. you can set the context, BUSINESS_GROUP_ID manually and try calling the function.
Deleteplease let me know the result.
Hi can some one help me .am getting string value exceed on fast formula.while evaluating the life event.the formula return value from.Udt till dec 2023 it did jot error but from jan 2024 it errors
ReplyDelete