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
|
