Introduction:
In Oracle Advanced Benefits (OAB), ensuring compliance through document verification is a standard operational requirement. During open enrollment or specific life events, organizations frequently mandate that employees upload supporting documentation, such as a birth or marriage certificate, to validate dependent eligibility.
However, standard out-of-the-box system behavior often creates a frustrating user experience, every time a fresh enrollment lifecycle event triggers, the system creates a new Action Item demanding a fresh document upload. This happens even if the employee already provided a verified, approved certificate during a previous enrollment cycle. Also if the user submits the document from document of Records prior enrolling, it also frustrates to again submit into the system.
To eliminate this redundant administrative burden, technical consultants can deploy an upgrade safe automation framework. By combining a Dependent Certification Required Fast Formula with table-validated Value Sets, you can programmatically inspect historical document records. If a verified document already exists for the dependent, the system automatically clears the action item, enabling seamless document reuse.
Solution:
Rather than forcing benefits specialists to manually clear thousands of repetitive action items, the formula acts as an active gatekeeper during the life event evaluation process.
[Life Event / Enrollment Executes]
│
▼
┌──────────────────────────────────────────────┐
│ OAB Engine Invokes Certification Rule FF │
└──────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────┐
│ Formula Calls Table-Validated Value Set │
└──────────────────────────────────────────────┘
│
├──► Queries HR_DOCUMENT_RECORDS for Dependent
│
▼
┌──────────────────────────────────────────────┐
│ Document Found & Status == 'APPROVED'? │
└──────────────────────────────────────────────┘
│ │
[YES] [NO]
│ │
▼ ▼
┌──────────────────┐ ┌──────────────────┐
│ Return REQUIRED │ │ Return REQUIRED │
│ = 'N' │ │ = 'Y' │
└──────────────────┘ └──────────────────┘
│ │
▼ ▼
(Action Item Cleared) (Action Item Created)
Technical Components:
This requirement can be fulfilled by creating two value sets along with Fast Formulas for the corresponding certifications.
Value Sets:
- ORA_BEN_DEP_CNT_CHK_VS: This value set is responsible to return the number of eligible dependents for an employee.
select
to_char(to_number(counter))
from
(select level counter from dual connect by level <= 50)
where
1=1
and counter =
(
select
count(distinct bed.dpnt_person_id)
from
per_contact_relships_f pcrf
inner join ben_elig_dpnt bed on pcrf.contact_person_id = bed.dpnt_person_id
inner join per_all_people_f papf on papf.person_id = pcrf.person_id
left outer join ben_per_le_habits_cov_f bplhcf on pcrf.contact_person_id = bplhcf.person_id and :{PARAMETER.P_EFFECTIVE_DATE} between bplhcf.effective_start_date and bplhcf.effective_end_date
where
1=1
and pcrf.contact_type = :{PARAMETER.P_RELATION_TYPE}
and pcrf.person_id = :{PARAMETER.P_PERSON_ID}
and :{PARAMETER.P_EFFECTIVE_DATE} between pcrf.effective_start_date and pcrf.effective_end_date
and :{PARAMETER.P_EFFECTIVE_DATE} between papf.effective_start_date and papf.effective_end_date
)
- ORA_BEN_DOC_REC_CHK_VS: This value set is responsible to return the number of certain documents available in the Documents of Records.
select
to_char(to_number(counter))
from
(select level counter from dual connect by level <= 50)
where
1=1
and counter =
(
select
count(distinct documents_of_record_id)
from
hr_documents_of_record hdor
inner join hr_document_types_tl hdtt on hdor.document_type_id = hdtt.document_type_id
inner join per_all_people_f papf on papf.person_id = hdor.person_id
where
1=1
and hdor.person_id = :{PARAMETER.P_PERSON_ID}
and upper(hdtt.document_type) = :{PARAMETER.P_DOCUMENT_TYPE}
and :{PARAMETER.P_EFFECTIVE_DATE} between papf.effective_start_date and papf.effective_end_date
)
Fast Formulas:
- Adoption Certificate Formula: This formula will clears the action item to submit the document if it is already submitted either from previous enrollment or from documents of records.
/*
---------------------------------------------------------------------------------------------------------
-- Copyright(C) Oracle
-- All Rights Reserved
---------------------------------------------------------------------------------------------------------
-- Application : HCM Benefits
-- Formula Name : ORA_BEN_DEP_ADOP_CERT_REQ_FF
-- Formula Type : Dependent Certification Required
-- Purpose : This formula is used to determine if an individual needs to provide Adoption certification for their dependents when enrolling in benefits
Programs
-- CHANGE LOG
-- Date Author Version Description
---------------------------------------------------------------------------------------------------------
dd-mm-yyyy Satyanarayana Swamy Chinnamsetti 1.0 Initial Version
---------------------------------------------------------------------------------------------------------
*/
/*@section - Default values for input variables (DBIs)*/
default for CON_PERSON_ID is 0
/*@section - Input variables */
INPUTS are CON_PERSON_ID(number)
/*@section - Local variable declarations and initializations*/
l_dpnt_id = CON_PERSON_ID
l_person_id = get_context(PERSON_ID,0)
l_effective_date = get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_chk_dpnt_ctfn = 'N'
l_adopted_child_cnt = 0
l_document_cnt = 0
l_relation_type = 'A'
l_document_type = 'ADOPTION CERTIFICATE'
/*
log = ess_log_write('--------------ORA_BEN_DEP_ADOP_CERT_REQ_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))
*/
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_ADOP_CERT_REQ_FF Formula Begins--------------')
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))
/*@section - Return Variable initialization*/
L_OUTPUT = 'Y'
/*@section - Business Logic and Interface*/
l_chk_dpnt_ctfn = BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD' ,'PROVIDED' ,'NA_CHECK_ONCE' ,'NA' ,to_char(l_dpnt_id) ,'Adoption certificate')
/*
log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
if (l_chk_dpnt_ctfn = 'Y') then
(
L_OUTPUT = 'N'
/*
log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
)
else
(
l_adopted_child_cnt = to_number(get_value_set('ORA_BEN_DEP_CNT_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_RELATION_TYPE='''||l_relation_type||''''))
if isnull(l_adopted_child_cnt) = 'N' then
(
l_adopted_child_cnt = 0
)
/*
log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_adopted_child_cnt = '+to_char(l_adopted_child_cnt))
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_adopted_child_cnt = '+to_char(l_adopted_child_cnt))
l_document_cnt = to_number(get_value_set('ORA_BEN_DOC_REC_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_DOCUMENT_TYPE='''||l_document_type||''''))
if isnull(l_document_cnt) = 'N' then
(
l_document_cnt = 0
)
/*
log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
if (l_document_cnt >= l_adopted_child_cnt and l_adopted_child_cnt != 0) then
(
L_OUTPUT = 'N'
/*
log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
)
)
/*
log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_output final step = '+L_OUTPUT)
log = ess_log_write('--------------ORA_BEN_DEP_ADOP_CERT_REQ_FF Formula Ends--------------')
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_output final step = '+L_OUTPUT)
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_ADOP_CERT_REQ_FF Formula Ends--------------')
/*@section - Return the calculated value*/
return L_OUTPUT
Similarly we have formulas for other certificates mentioned below.
- Birth Certificate Formula:
/*
---------------------------------------------------------------------------------------------------------
-- Copyright(C) Oracle
-- All Rights Reserved
---------------------------------------------------------------------------------------------------------
-- Application : HCM Benefits
-- Formula Name : ORA_BEN_DEP_BIR_CERT_REQ_FF
-- Formula Type : Dependent Certification Required
-- Purpose : This formula is used to determine if an individual needs to provide birth certificate for their dependents when enrolling in benefits programs
-- CHANGE LOG
-- Date Author Version Description
---------------------------------------------------------------------------------------------------------
dd-mm-yyyy Satyanarayana Swamy Chinnamsetti 1.0 Initial Version
---------------------------------------------------------------------------------------------------------
*/
/*@section - Default values for input variables (DBIs)*/
default for CON_PERSON_ID is 0
/*@section - Input variables */
INPUTS are CON_PERSON_ID(number)
/*@section - Local variable declarations and initializations*/
l_dpnt_id = CON_PERSON_ID
l_person_id = get_context(PERSON_ID,0)
l_effective_date = get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_chk_dpnt_ctfn = 'N'
l_total_cnt = 0
l_child_cnt = 0
l_step_child_cnt = 0
l_document_cnt = 0
l_relation_type = 'C'
l_document_type = 'BIRTH CERTIFICATE'
/*
log = ess_log_write('--------------ORA_BEN_DEP_BIR_CERT_REQ_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))
*/
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_BIR_CERT_REQ_FF Formula Begins--------------')
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))
/*@section - Return Variable initialization*/
L_OUTPUT = 'Y'
/*@section - Business Logic and Interface*/
l_chk_dpnt_ctfn = BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD' ,'PROVIDED' ,'NA_CHECK_ONCE' ,'NA' ,to_char(l_dpnt_id) ,'Birth certificate')
/*
log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
if (l_chk_dpnt_ctfn = 'Y') then
(
L_OUTPUT = 'N'
/*
log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
)
else
(
l_child_cnt = to_number(get_value_set('ORA_BEN_DEP_CNT_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_RELATION_TYPE='''||l_relation_type||''''))
if isnull(l_child_cnt) = 'N' then
(
l_child_cnt = 0
)
/*
log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_child_cnt = '+to_char(l_child_cnt))
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_child_cnt = '+to_char(l_child_cnt))
l_document_cnt = to_number(get_value_set('ORA_BEN_DOC_REC_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_DOCUMENT_TYPE='''||l_document_type||''''))
if isnull(l_document_cnt) = 'N' then
(
l_document_cnt = 0
)
/*
log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
if (l_document_cnt >= l_child_cnt and l_child_cnt != 0) then
(
L_OUTPUT = 'N'
/*
log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
)
)
/*
log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_output final step = '+L_OUTPUT)
log = ess_log_write('--------------ORA_BEN_DEP_BIR_CERT_REQ_FF Formula Ends--------------')
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_output final step = '+L_OUTPUT)
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_BIR_CERT_REQ_FF Formula Ends--------------')
/*@section - Return the calculated value*/
return L_OUTPUT
- Legal Custody Certificate Formula:
/*
---------------------------------------------------------------------------------------------------------
-- Copyright(C) Oracle
-- All Rights Reserved
---------------------------------------------------------------------------------------------------------
-- Application : HCM Benefits
-- Formula Name : ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF
-- Formula Type : Dependent Certification Required
-- Purpose : This formula is used to determine if an individual needs to provide legal custody certification for their dependents when enrolling in
benefits programs
-- CHANGE LOG
-- Date Author Version Description
---------------------------------------------------------------------------------------------------------
dd-mm-yyyy Satyanarayana Swamy Chinnamsetti 1.0 Initial Version
---------------------------------------------------------------------------------------------------------
*/
/*@section - Default values for input variables (DBIs)*/
default for CON_PERSON_ID is 0
/*@section - Input variables */
INPUTS are CON_PERSON_ID(number)
/*@section - Local variable declarations and initializations*/
l_dpnt_id = CON_PERSON_ID
l_person_id = get_context(PERSON_ID,0)
l_effective_date = get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_chk_dpnt_ctfn = 'N'
l_foster_child_cnt = 0
l_document_cnt = 0
l_relation_type = 'O'
l_document_type = 'LEGAL CUSTODY CERTIFICATE'
/*
log = ess_log_write('--------------ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))
*/
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF Formula Begins--------------')
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))
/*@section - Return Variable initialization*/
L_OUTPUT = 'Y'
/*@section - Business Logic and Interface*/
l_chk_dpnt_ctfn = BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD' ,'PROVIDED' ,'NA_CHECK_ONCE' ,'NA' ,to_char(l_dpnt_id) ,'Legal custody certificate')
/*
log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
if (l_chk_dpnt_ctfn = 'Y') then
(
L_OUTPUT = 'N'
/*
log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
)
else
(
l_foster_child_cnt = to_number(get_value_set('ORA_BEN_DEP_CNT_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_RELATION_TYPE='''||l_relation_type||''''))
if isnull(l_foster_child_cnt) = 'N' then
(
l_foster_child_cnt = 0
)
/*
log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_foster_child_cnt = '+to_char(l_foster_child_cnt))
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_foster_child_cnt = '+to_char(l_foster_child_cnt))
l_document_cnt = to_number(get_value_set('ORA_BEN_DOC_REC_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_DOCUMENT_TYPE='''||l_document_type||''''))
if isnull(l_document_cnt) = 'N' then
(
l_document_cnt = 0
)
/*
log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
if (l_document_cnt >= l_foster_child_cnt and l_foster_child_cnt != 0) then
(
L_OUTPUT = 'N'
/*
log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
)
)
/*
log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_output final step = '+L_OUTPUT)
log = ess_log_write('--------------ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF Formula Ends--------------')
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_output final step = '+L_OUTPUT)
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF Formula Ends--------------')
/*@section - Return the calculated value*/
return L_OUTPUT
- Marriage Certificate Formula:
/*
---------------------------------------------------------------------------------------------------------
-- Copyright(C) Oracle
-- All Rights Reserved
---------------------------------------------------------------------------------------------------------
-- Application : HCM Benefits
-- Formula Name : ORA_BEN_DEP_MAR_CERT_REQ_FF
-- Formula Type : Dependent Certification Required
-- Purpose : This formula is used to determine if an individual needs to provide Marriage certification for their dependents when enrolling in benefits
programs
-- CHANGE LOG
-- Date Author Version Description
---------------------------------------------------------------------------------------------------------
dd-mm-yyyy Satyanarayana Swamy Chinnamsetti 1.0 Initial Version
---------------------------------------------------------------------------------------------------------
*/
/*@section - Default values for input variables (DBIs)*/
default for CON_PERSON_ID is 0
/*@section - Input variables */
INPUTS are CON_PERSON_ID(number)
/*@section - Local variable declarations and initializations*/
l_dpnt_id = CON_PERSON_ID
l_person_id = get_context(PERSON_ID,0)
l_effective_date = get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_chk_dpnt_ctfn = 'N'
l_total_cnt = 0
l_spouse_cnt = 0
l_step_child_cnt = 0
l_document_cnt = 0
l_relation_type1 = 'S'
l_relation_type2 = 'T'
l_document_type = 'MARRIAGE CERTIFICATE'
/*
log = ess_log_write('--------------ORA_BEN_DEP_MAR_CERT_REQ_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))
*/
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_MAR_CERT_REQ_FF Formula Begins--------------')
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))
/*@section - Return Variable initialization*/
L_OUTPUT = 'Y'
/*@section - Business Logic and Interface*/
l_chk_dpnt_ctfn = BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD' ,'PROVIDED' ,'NA_CHECK_ONCE' ,'NA' ,to_char(l_dpnt_id) ,'Marriage certificate')
/*
log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
if (l_chk_dpnt_ctfn = 'Y') then
(
L_OUTPUT = 'N'
/*
log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
)
else
(
l_spouse_cnt = to_number(get_value_set('ORA_BEN_DEP_CNT_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_RELATION_TYPE='''||l_relation_type1||''''))
if isnull(l_spouse_cnt) = 'N' then
(
l_spouse_cnt = 0
)
/*
log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_spouse_cnt = '+to_char(l_spouse_cnt))
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_spouse_cnt = '+to_char(l_spouse_cnt))
l_step_child_cnt = to_number(get_value_set('ORA_BEN_DEP_CNT_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_RELATION_TYPE='''||l_relation_type2||''''))
if isnull(l_step_child_cnt) = 'N' then
(
l_step_child_cnt = 0
)
/*
log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_step_child_cnt = '+to_char(l_child_cnt))
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_step_child_cnt = '+to_char(l_step_child_cnt))
l_total_cnt = l_spouse_cnt + l_step_child_cnt
/*
log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_total_cnt_cnt = '+to_char(l_total_cnt))
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_total_cnt_cnt = '+to_char(l_total_cnt))
l_document_cnt = to_number(get_value_set('ORA_BEN_DOC_REC_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_DOCUMENT_TYPE='''||l_document_type||''''))
if isnull(l_document_cnt) = 'N' then
(
l_document_cnt = 0
)
/*
log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
if (l_document_cnt >= l_total_cnt and l_total_cnt != 0) then
(
L_OUTPUT = 'N'
/*
log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
)
)
/*
log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_output final step = '+L_OUTPUT)
log = ess_log_write('--------------ORA_BEN_DEP_MAR_CERT_REQ_FF Formula Ends--------------')
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_output final step = '+L_OUTPUT)
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_MAR_CERT_REQ_FF Formula Ends--------------')
/*@section - Return the calculated value*/
return L_OUTPUT
- ** Proof of External Coverage Formula:**
/*
---------------------------------------------------------------------------------------------------------
-- Copyright(C) Oracle
-- All Rights Reserved
---------------------------------------------------------------------------------------------------------
-- Application : HCM Benefits
-- Formula Name : ORA_BEN_DEP_PEC_CERT_REQ_FF
-- Formula Type : Dependent Certification Required
-- Purpose : This formula is used to determine if an individual needs to provide Proof of External Coverage certification when enrolling in benefits
programs
-- CHANGE LOG
-- Date Author Version Description
---------------------------------------------------------------------------------------------------------
dd-mm-yyyy Satyanarayana Swamy Chinnamsetti 1.0 Initial Version
---------------------------------------------------------------------------------------------------------
*/
/*@section - Default values for input variables (DBIs)*/
default for CON_PERSON_ID is 0
/*@section - Input variables */
INPUTS are CON_PERSON_ID(number)
/*@section - Local variable declarations and initializations*/
l_dpnt_id = CON_PERSON_ID
l_person_id = get_context(PERSON_ID,0)
l_effective_date = get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_chk_dpnt_ctfn = 'N'
l_document_cnt = 0
l_document_type = 'PROOF OF EXTERNAL COVERAGE'
/*
log = ess_log_write('--------------ORA_BEN_DEP_PEC_CERT_REQ_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_DEP_PEC_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
log = ess_log_write('ORA_BEN_DEP_PEC_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
log = ess_log_write('ORA_BEN_DEP_PEC_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))
*/
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_PEC_CERT_REQ_FF Formula Begins--------------')
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PEC_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PEC_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PEC_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))
/*@section - Return Variable initialization*/
L_OUTPUT = 'Y'
/*@section - Business Logic and Interface*/
l_chk_dpnt_ctfn = BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD' ,'PROVIDED' ,'NA_CHECK_ONCE' ,'NA' ,to_char(l_dpnt_id) ,'Proof of external coverage')
/*
log = ess_log_write('ORA_BEN_DEP_PEC_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PEC_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
if (l_chk_dpnt_ctfn = 'Y') then
(
L_OUTPUT = 'N'
/*
log = ess_log_write('ORA_BEN_DEP_PEC_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PEC_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
)
else
(
l_document_cnt = to_number(get_value_set('ORA_BEN_DOC_REC_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_DOCUMENT_TYPE='''||l_document_type||''''))
if isnull(l_document_cnt) = 'N' then
(
l_document_cnt = 0
)
/*
log = ess_log_write('ORA_BEN_DEP_PEC_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PEC_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
if (l_document_cnt > 0) then
(
L_OUTPUT = 'N'
/*
log = ess_log_write('ORA_BEN_DEP_PEC_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PEC_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
)
)
/*
log = ess_log_write('ORA_BEN_DEP_PEC_CERT_REQ_FF l_output final step = '+L_OUTPUT)
log = ess_log_write('--------------ORA_BEN_DEP_PEC_CERT_REQ_FF Formula Ends--------------')
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PEC_CERT_REQ_FF l_output final step = '+L_OUTPUT)
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_PEC_CERT_REQ_FF Formula Ends--------------')
/*@section - Return the calculated value*/
return L_OUTPUT
- Proof of Good Health Formula:
/*
---------------------------------------------------------------------------------------------------------
-- Copyright(C) Oracle
-- All Rights Reserved
---------------------------------------------------------------------------------------------------------
-- Application : HCM Benefits
-- Formula Name : ORA_BEN_DEP_PGH_CERT_REQ_FF
-- Formula Type : Dependent Certification Required
-- Purpose : This formula is used to determine if an individual needs to provide Proof of Good Health certification when enrolling in benefits
-- CHANGE LOG
-- Date Author Version Description
---------------------------------------------------------------------------------------------------------
dd-mm-yyyy Satyanarayana Swamy Chinnamsetti 1.0 Initial Version
---------------------------------------------------------------------------------------------------------
*/
/*@section - Default values for input variables (DBIs)*/
default for CON_PERSON_ID is 0
/*@section - Input variables */
INPUTS are CON_PERSON_ID(number)
/*@section - Local variable declarations and initializations*/
l_dpnt_id = CON_PERSON_ID
l_person_id = get_context(PERSON_ID,0)
l_effective_date = get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_chk_dpnt_ctfn = 'N'
l_document_cnt = 0
l_document_type = 'PROOF OF GOOD HEALTH'
/*
log = ess_log_write('--------------ORA_BEN_DEP_PGH_CERT_REQ_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_DEP_PGH_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
log = ess_log_write('ORA_BEN_DEP_PGH_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
log = ess_log_write('ORA_BEN_DEP_PGH_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))
*/
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_PGH_CERT_REQ_FF Formula Begins--------------')
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PGH_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PGH_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PGH_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))
/*@section - Return Variable initialization*/
L_OUTPUT = 'Y'
/*@section - Business Logic and Interface*/
l_chk_dpnt_ctfn = BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD' ,'PROVIDED' ,'NA_CHECK_ONCE' ,'NA' ,to_char(l_dpnt_id) ,'Proof of good health')
/*
log = ess_log_write('ORA_BEN_DEP_PGH_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PGH_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
if (l_chk_dpnt_ctfn = 'Y') then
(
L_OUTPUT = 'N'
/*
log = ess_log_write('ORA_BEN_DEP_PGH_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PGH_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
)
else
(
l_document_cnt = to_number(get_value_set('ORA_BEN_DOC_REC_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_DOCUMENT_TYPE='''||l_document_type||''''))
if isnull(l_document_cnt) = 'N' then
(
l_document_cnt = 0
)
/*
log = ess_log_write('ORA_BEN_DEP_PGH_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PGH_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
if (l_document_cnt > 0) then
(
L_OUTPUT = 'N'
/*
log = ess_log_write('ORA_BEN_DEP_PGH_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PGH_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
)
)
/*
log = ess_log_write('ORA_BEN_DEP_PGH_CERT_REQ_FF l_output final step = '+L_OUTPUT)
log = ess_log_write('--------------ORA_BEN_DEP_PGH_CERT_REQ_FF Formula Ends--------------')
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PGH_CERT_REQ_FF l_output final step = '+L_OUTPUT)
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_PGH_CERT_REQ_FF Formula Ends--------------')
/*@section - Return the calculated value*/
return L_OUTPUT
- Proof of Other Coverage Formula:
/*
---------------------------------------------------------------------------------------------------------
-- Copyright(C) Oracle
-- All Rights Reserved
---------------------------------------------------------------------------------------------------------
-- Application : HCM Benefits
-- Formula Name : ORA_BEN_DEP_POC_CERT_REQ_FF
-- Formula Type : Dependent Certification Required
-- Purpose : This formula is used to determine if an individual needs to provide Proof of Other Coverage certification when enrolling in benefits or other programs
-- CHANGE LOG
-- Date Author Version Description
---------------------------------------------------------------------------------------------------------
dd-mm-yyyy Satyanarayana Swamy Chinnamsetti 1.0 Initial Version
---------------------------------------------------------------------------------------------------------
*/
/*@section - Default values for input variables (DBIs)*/
default for CON_PERSON_ID is 0
/*@section - Input variables */
INPUTS are CON_PERSON_ID(number)
/*@section - Local variable declarations and initializations*/
l_dpnt_id = CON_PERSON_ID
l_person_id = get_context(PERSON_ID,0)
l_effective_date = get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_chk_dpnt_ctfn = 'N'
l_document_cnt = 0
l_document_type = 'PROOF OF OTHER COVERAGE'
/*
log = ess_log_write('--------------ORA_BEN_DEP_POC_CERT_REQ_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_DEP_POC_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
log = ess_log_write('ORA_BEN_DEP_POC_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
log = ess_log_write('ORA_BEN_DEP_POC_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))
*/
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_POC_CERT_REQ_FF Formula Begins--------------')
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_POC_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_POC_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_POC_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))
/*@section - Return Variable initialization*/
L_OUTPUT = 'Y'
/*@section - Business Logic and Interface*/
l_chk_dpnt_ctfn = BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD' ,'PROVIDED' ,'NA_CHECK_ONCE' ,'NA' ,to_char(l_dpnt_id) ,'Proof of other coverage')
/*
log = ess_log_write('ORA_BEN_DEP_POC_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_POC_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
if (l_chk_dpnt_ctfn = 'Y') then
(
L_OUTPUT = 'N'
/*
log = ess_log_write('ORA_BEN_DEP_POC_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_POC_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
)
else
(
l_document_cnt = to_number(get_value_set('ORA_BEN_DOC_REC_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_DOCUMENT_TYPE='''||l_document_type||''''))
if isnull(l_document_cnt) = 'N' then
(
l_document_cnt = 0
)
/*
log = ess_log_write('ORA_BEN_DEP_POC_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_POC_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
if (l_document_cnt > 0) then
(
L_OUTPUT = 'N'
/*
log = ess_log_write('ORA_BEN_DEP_POC_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_POC_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
)
)
/*
log = ess_log_write('ORA_BEN_DEP_POC_CERT_REQ_FF l_output final step = '+L_OUTPUT)
log = ess_log_write('--------------ORA_BEN_DEP_POC_CERT_REQ_FF Formula Ends--------------')
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_POC_CERT_REQ_FF l_output final step = '+L_OUTPUT)
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_POC_CERT_REQ_FF Formula Ends--------------')
/*@section - Return the calculated value*/
return L_OUTPUT
Configuration:
Navigate to Benefits Administration -> Plan Configuration -> Select Plan -> Designation Requirements -> select Plan Type -> click on Life event -> Choose Life event -> Edit the certification type and select the Determination Formula.
Conclusion:
By shifting document verification requirements away from rigid out-of-the-box triggers and utilizing targeted table-validated calculations, organizations can significantly improve the employee enrollment experience. This architecture ensures that historical compliance validations remain intact, data silos between Core HR and Advanced Benefits are removed, and system resources are preserved during high-volume batch runs.
Top comments (0)