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.

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;

Useful SQLs/ PLSQLs

I am listing here some of the SQL Queries and PLSQL blocks which we use very often in Oracle Applications. It is cumbersome to see this data in application through delivered screens.

1. Finding the value for a profile


SELECT fpot.user_profile_option_name profile,
fpov.profile_option_value VALUE,
decode(fpov.level_id,
10001,
'SITE',
10002,
'APPLICATION',
10003,
'RESPONSIBILITY',
10004,
'USER') "Apply On",
fa.application_name application,
fr.responsibility_name responsibility,
fu.user_name "USER"
FROM fnd_profile_option_values fpov,
fnd_profile_options_tl fpot,
fnd_profile_options fpo,
fnd_application_tl fa,
fnd_responsibility_vl fr,
fnd_user fu,
fnd_logins fl
WHERE fpo.profile_option_id = fpov.profile_option_id
AND TRIM(fpot.profile_option_name) = TRIM(fpo.profile_option_name)
AND fa.application_id(+) = fpov.level_value
AND fr.application_id(+) = fpov.level_value_application_id
AND fr.responsibility_id(+) = fpov.level_value
AND fu.user_id(+) = fpov.level_value
AND fl.login_id(+) = fpov.last_update_login
-- AND fpo.profile_option_name = 'FND_CUSTOM_OA_DEFINTION'
-- AND fpov.profile_option_value = 1067
-- AND fr.responsibility_name like '%Manager%'
AND fpot.user_profile_option_name = 'MO: Operating Unit'
ORDER BY 1;


2. Element Entries


SELECT ee.element_type_id '-' elt.element_name el_name,
to_char(ee.assignment_id) asId,
to_char(ee.element_entry_id) eeId,
ee.effective_start_date esd,
ee.effective_end_date eed,
to_char(ee.creator_id) Creator,
to_char(ee.element_link_id) el_id,
to_char(ee.object_version_number) ovn
FROM apps.pay_element_entries_f ee, pay_element_types_f elt
WHERE ee.assignment_id IN (SELECT assignment_id
FROM per_all_assignments_f
WHERE person_id = &v_personId AND primary_flag = 'Y')
AND elt.element_type_id(+) = ee.element_type_id
AND ee.effective_start_date BETWEEN elt.effective_start_date AND elt.effective_end_date;


3. Element Values


SELECT to_char(elt.element_type_id) '-'elt.element_name el_name,
to_char(eev.input_value_id) '-' piv.NAME iv_name,
to_char(eev.element_entry_id) eeId,
to_char(eev.element_entry_value_id) eevId,
eev.screen_entry_value,
eev.effective_start_date esd,
eev.effective_end_date eed
FROM pay_element_entry_values_f eev,
pay_input_values_f piv,
pay_element_types_f elt
WHERE eev.element_entry_id IN (
SELECT element_entry_id
FROM apps.pay_element_entries_f ee
WHERE assignment_id IN
(SELECT assignment_id
FROM per_all_assignments_f
WHERE person_id = &v_personId AND primary_flag = 'Y'))
AND elt.element_type_id(+) = piv.element_type_id
AND eev.effective_start_date BETWEEN elt.effective_start_date AND elt.effective_end_date
AND eev.input_value_id = piv.input_value_id
AND eev.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
ORDER BY 1, 2;


4. Finding User name against a responsibility


SELECT usr.user_name,
resp.responsibility_name
FROM fnd_user usr,
fnd_responsibility_vl resp,
fnd_user_resp_groups_direct usr_resp
WHERE usr.user_id = usr_resp.user_id
AND usr_resp.responsibility_id = resp.responsibility_id
AND Upper(resp.responsibility_name) LIKE Upper('%HRMS%Workbench%');


5. Finding menu for a given function


SELECT fn.PROMPT ,
fnf.user_function_name,
men.user_menu_name ,
fn.DESCRIPTION ,
fn.GRANT_FLAG
FROM FND_MENU_ENTRIES_VL fn ,
fnd_form_functions_vl fnf,
fnd_menus_vl men
WHERE fn.function_id = fnf.function_id
AND fn.menu_id = men.menu_id
AND fn.prompt = 'Change Job'
ORDER BY ENTRY_SEQUENCE;


6. Compile Menus through PLSQL (rather than concurrent program)


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


Lot of more stuff following this post. Any doubts regarding any query drop a comment. I will try to see if I have the right answer.