Oracle Advanced Benefit (OAB) Queries

I will try to give the basic queries without joining with plans or options or programs. So that everyone can understand the basic underlying conditions. Later on you can add all plan and program joins to add to the reports.

1. Enrollment Results


SELECT *
FROM ben_prtt_enrt_rslt_f pen
WHERE business_group_id = p_business_group_id
AND p_effective_date BETWEEN pen.enrt_cvg_strt_dt AND pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_thru_dt <= pen.effective_end_date
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.sspndd_flag = 'N'
AND pen.person_id = p_person_id


Same query will give you coverage amount stored as bnft_amt in the table.

2. Enrollment Rates
For each enrollment you can have different rates. Rates are store in table ben_prtt_rt_val
you can use the following query for rates

SELECT *
FROM ben_prtt_rt_val
WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;


But here main things to take care is rt_strt_dt and rt_end_dt. rt_strt_dt will always be less than equal to rt_end_date for a recurring rate but rt_strt_dt and rt_end_dt are equal when rt is non-recurring.

3. Eligibility Results
These results are store for each life event in person record. Also in this table you will find if a particular enrollments is set for default or auto enrollment with dflt_flag and auto_enrt_flag.

SELECT epe.*
FROM ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
WHERE pil.person_id = p_person_id
AND epe.per_in_ler_id = pil.per_in_ler_id;


4. Enrolled Dependents

SELECT *
FROM ben_elig_cvrd_dpnt_f dpnt,
ben_prtt_enrt_rslt_f pen
WHERE dpnt.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
AND EXISTS (SELECT pil.per_in_ler_id
FROM ben_per_in_ler pil
WHERE pil.per_in_ler_id = pen.per_in_ler_id
AND pil.business_group_id = p_business_group_id
AND pil.per_in_ler_stat_cd NOT IN ('VOIDD','BCKDT'))
AND p_effective_date BETWEEN dpnt.cvg_strt_dt AND Nvl(dpnt.cvg_thru_dt,p_effective_date)
AND (Nvl(dpnt.cvg_thru_dt,p_effective_date) <= dpnt.effective_end_date
OR p_effective_date BETWEEN dpnt.effective_start_date AND Nvl(dpnt.effective_end_date,p_effective_date))
AND dpnt.dpnt_person_id = p_person_id



In above query you can dpnt_person_id is the dependent. You can see the enrolled person in ben_prtt_enrt_rslt_f table.

5. Pending Action Items
SELECT *
FROM ben_prtt_enrt_rslt_f pen,
ben_prtt_enrt_actn_f pea,
ben_per_in_ler pil
WHERE pen.prtt_enrt_rslt_id = pea.prtt_enrt_rslt_id
AND pil.per_in_ler_id (+) = pea.per_in_ler_id
AND (pil.per_in_ler_stat_cd NOT IN ('VOIDD','BCKDT')
OR pil.per_in_ler_stat_cd IS NULL)
AND pen.sspndd_flag = 'Y'
AND p_effective_date BETWEEN pen.enrt_cvg_strt_dt AND pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_thru_dt <= pen.effective_end_date
AND pen.prtt_enrt_rslt_stat_cd IS NULL



I will come up with some queries. Please let me know what kind of queries I can add here.

Basic Definition of Oracle Advanced Benefits (OAB)Terminology

Hi Guys
I have received lot of questions regarding Benefits module. So I thought I will go through some basics terminology used in Benefits following with some details and queries.

Life Event:: Life event is any change in the life of an employee. that may be new hire, marriage, child, or even termination. All these events, in one or other organization, effect benefits given to employee. Any life event that can be processed is a Potential Life Event. Once we Process a potential Life event it is called Person in Life Event. Person in Life event is stored in BEN_PER_IN_LER. All Benefits data is stored with Primary key of this table (i.e. PER_IN_LER_ID), like eligibility records for each processed life events, enrollments, rate values, etc.


Plan: Any Benefit given to employee is defined as a Plan. It may be Medical Insurance, Vision/ Dental Insurance, 401 K Saving Plan, Car Insurance etc. A Program is a group of plans that have similar properties, like when to enroll, how to enroll etc.

Enrollment Requirements: For each Program or Plan we define Enrollment Requirements. These definitions tells the processes, when to start the coverage or rates (deductions), what all plans will be automatically enrolled, what all will be defaulted. Setting up these are mandatory. We can defined these properties at different levels, Program, Plan, Life event for Program, Life event for Plan, Option. Lowest level always takes precedence.

Standard Rate: We can attach a monetary value or any numeric value to a plan, this may be deduction or a bonus. We define this using a standard rate. Elements are also attached to a standard rate. This is the link where payroll and Benefits are integrated.

Coverage: Coverage is basically a kind of benefit that is given to an employee, like medical insurance, dental insurance etc.

.
.

Now I will try to use all above terms to be used in a benefit, say XX_Medical_Insurance. Say customer requirement is to give medical insurance once a person joins.

1. As setup a new life event will be setup, which will be triggered when a new employee is cretaed.
2. XX_Medical_Insurance Plan should be created. Then we will define enrollment requirements such that when a life event is processed person is enrolled as first date of next pay-period and deductions are also starting on first date of next payperiod.
3. Coverage is enterable such that employee will tell, I need $50,000 coverage on my medical insurance
4. Standard Rate is dependent on coverage, so whatever coverage employee chooses accordingly amount will be deducted. Deduction amount is attached with a element.
5. Now when person is hired, life event will trigger. This will be potential life event.
6. Now once this life event is processed, Person in Life event record will be created. At this time eligibility will be calculated.
7. Now once the person enrolls into benefit, benefits will stored again Person in Life event record.


I will be coming up with various queries to find enrollments, enrollment rates, eligibility etc.

Miscellaneous SQL/ PLSQLs

Finding Patch Set Level

SELECT * FROM (
SELECT '(Patch: ' || bug_number || ') applied ' || LAST_UPDATE_DATE PFbugDate
FROM ad_bugs
WHERE BUG_NUMBER IN (
'2115771',
'2268451',
'2502761',
'2632500',
'2803988',
'2968701', -- F
'3116666', -- G
'3233333', -- H
'3127777', -- I = 11.5.10 base
'3333633', -- J
'3500000', -- K
'3800000', -- L
'5055050') -- Rup1
ORDER BY LAST_UPDATE_DATE desc
) WHERE ROWNUM = 1;






Find the key flex field structure
SELECT id_flex_structure_code structure_code,
DECODE (dynamic_inserts_allowed_flag,
'Y', 'OK: Dynamic',
'ERROR: No Dynamic'
) dynamic,
DECODE (freeze_flex_definition_flag,
'Y', 'OK: Freezed',
'ERROR: No Freezed'
) freeze,
segment_num seg_number, segment_name seg_name,
DECODE (required_flag,
'Y', 'OK: Required',
'ERROR: No required'
) required,
DECODE (fvs.flex_value_set_name,
NULL, 'NULL',
fvs.flex_value_set_name
) value_set,
DECODE (fvs.validation_type,
'N', 'OK: No validation',
'ERROR: ' || fvs.validation_type
) VALIDATION,
DECODE (fvs.uppercase_only_flag,
'N', 'OK: No',
'ERROR: Uppercase Only'
) uppercase_only,
DECODE (alphanumeric_allowed_flag,
'Y', 'OK: Allowed',
'ERROR: Not Allowed'
) alphanumeric,
DECODE (numeric_mode_enabled_flag,
'N', 'OK:Not justified',
'ERROR: Justified'
) right_justify,
DECODE (format_type,
'C', 'OK: Char',
'ERROR: ' || format_type
) format_type
FROM fnd_id_flex_structures_vl ffst,
fnd_id_flex_segments_vl ffsg,
fnd_flex_value_sets fvs
WHERE ffst.application_id = 800
AND ffst.id_flex_code = 'GRD'
AND ffst.enabled_flag = 'Y'
AND ffst.application_id = ffsg.application_id
AND ffst.id_flex_code = ffsg.id_flex_code
AND ffst.id_flex_num = ffsg.id_flex_num
AND ffsg.enabled_flag = 'Y'
AND fvs.flex_value_set_id(+) = ffsg.flex_value_set_id;

Find the Descriptive flex field structure
SELECT apps.application_name "App",
d_flex.title "Title",
d_flex.application_table_name "Table",
d_flex.context_user_override_flag "DFF Disp",
d_flex.freeze_flex_definition_flag "Frozen",
d_flex.default_context_value "Default Ctx",
d_flex.context_required_flag "DFF Required",
ctx_val_s.flex_value_set_name "Ctx ValueSet",
ctx.descriptive_flex_context_name "Ctx",
ctx.enabled_flag "Ctx Enabled",
col.form_left_prompt "Prompt",
col.end_user_column_name "Column",
val_s.flex_value_set_name "Value Set",
col.enabled_flag " Col Enabled",
col.required_flag "Required",
col.application_column_name "App Column",
col.display_flag " Col Display",
col.default_value "Default Val",
col.security_enabled_flag "Security",
col.display_size,
col.maximum_description_len,
col.concatenation_description_len,
col.application_id,
d_flex.concatenated_segs_view_name,
col.descriptive_flexfield_name,
col.form_above_prompt,
col.descriptive_flex_context_code,
col.range_code,
col.flex_value_set_id,
col.default_type,
col.srw_param
FROM fnd_descr_flex_col_usage_vl col,
fnd_descr_flex_contexts_vl ctx,
fnd_descriptive_flexs_vl d_flex,
fnd_flex_value_sets val_s,
fnd_flex_value_sets ctx_val_s,
fnd_application_tl apps
WHERE d_flex.application_id IN (809,
808)
--AND Upper(d_flex.title) LIKE Upper('%Addtional Ben Pl F Details%')
AND d_flex.descriptive_flexfield_name = ctx.descriptive_flexfield_name
AND ctx.descriptive_flexfield_name = col.descriptive_flexfield_name (+)
AND col.descriptive_flex_context_code (+) = ctx.descriptive_flex_context_code
AND val_s.flex_value_set_id (+) = col.flex_value_set_id
AND ctx_val_s.flex_value_set_id (+) = d_flex.context_override_value_set_id
AND apps.application_id = d_flex.application_id
AND d_flex.application_table_name <> 'FND_SRS_MASTER'
ORDER BY apps.application_name,
d_flex.title,
ctx.descriptive_flex_context_name,
col.column_seq_num;



Compile a Menu
declare
l_r number;
l_e varchar2(1000);
begin
fnd_function.compile(l_e,l_r,'N');
end;



Retrieve Supervisory Hierarchy (CONNECT BY PRIOR)

SELECT lpad(' ',level*3)
||
(SELECT full_name
FROM per_all_people_f ppl
WHERE person_id = paf.person_id
AND rownum =1
) full_name,
(SELECT full_name
FROM per_all_people_f ppl
WHERE person_id = paf.supervisor_id
AND rownum =1
) sup ,
assignment_number,
position_id
FROM per_all_assignments_f paf
WHERE business_group_id = 81
AND assignment_type = 'E'
AND assignment_status_type_id = 1
AND sysdate BETWEEN effective_start_date AND effective_end_date START
WITH assignment_number = '2748' CONNECT BY prior person_id = supervisor_id;



Initialize Apps (FND_GLOBAL.APPS_INTIALIZE)


declare
l_user_id number;
l_resp_id number;
l_resp_app_id number;

cursor get_user is
select user_id
from fnd_user
where user_name = ‘GSEHGAL’;

cursor get_resp is
select responsibility_id, application_id
from fnd_responsibility_vl
where responsibility_key = 'HIRING_MANAGER';


begin
open get_user;
fetch get_user into l_user_id;
close get_user;

open get_resp;
fetch get_resp into l_resp_id, l_resp_app_id;
close get_resp;

fnd_global.apps_initialize(l_user_id, l_resp_id,l_resp_app_id);
end;