Wednesday, February 8, 2017

Benefit Function - BEN_FN_GET_CHAR_VALUE

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