[Home] [Help]
28: l_proc varchar2(72) := g_package||'chk_uniq_elig_prfl';
29: l_dummy number ;
30: --
31: cursor c_uniq_cm_typ_elig_prfl is
32: select obj.ELIG_OBJ_ID from ben_elig_obj_f obj ,
33: ben_elig_obj_elig_profl_f obe
34: where ((obe.effective_start_date > p_effective_date)
35: OR (p_effective_date BETWEEN obe.effective_start_date AND obe.effective_END_date))
36: and obj.table_name = p_table_name
81: cursor get_elig_object_id (p_user_group_id in number) is
82: select
83: elig_obj_id
84: from
85: ben_elig_obj_f EO
86: where
87: p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
88: and TABLE_NAME = p_TABLE_NAME
89: and COLUMN_NAME = p_COLUMN_NAME
89: and COLUMN_NAME = p_COLUMN_NAME
90: and COLUMN_VALUE = p_col_value;
91:
92:
93: l_elig_obj_id ben_elig_obj_f.elig_obj_id%type;
94: l_start_date date;
95: l_end_date date;
96: l_object_version_number1 number;
97: l_effective_start_date date;
199: select
200: EO.elig_obj_id ,
201: EO.object_version_number
202: from
203: ben_elig_obj_f EO,
204: ben_elig_obj_elig_profl_f eop
205: where
206: p_effective_date between eo.EFFECTIVE_START_DATE and eo.EFFECTIVE_END_DATE
207: and TABLE_NAME = p_TABLE_NAME
221: p_effective_date between effective_start_date and effective_end_date
222: and elig_obj_id = p_elig_obj_id
223: and rownum < 2;
224:
225: l_elig_object_id ben_elig_obj_f.elig_obj_id%type;
226: l_obj_version ben_elig_obj_f.object_version_number%type;
227: l_elig_obj_elig_prf_id ben_elig_obj_elig_profl_f.elig_obj_elig_prfl_id%type;
228: l_elig_obj_obj_version ben_elig_obj_elig_profl_f.object_version_number%type;
229: l_elig_prof_id ben_elig_obj_elig_profl_f.elig_prfl_id%type;
222: and elig_obj_id = p_elig_obj_id
223: and rownum < 2;
224:
225: l_elig_object_id ben_elig_obj_f.elig_obj_id%type;
226: l_obj_version ben_elig_obj_f.object_version_number%type;
227: l_elig_obj_elig_prf_id ben_elig_obj_elig_profl_f.elig_obj_elig_prfl_id%type;
228: l_elig_obj_obj_version ben_elig_obj_elig_profl_f.object_version_number%type;
229: l_elig_prof_id ben_elig_obj_elig_profl_f.elig_prfl_id%type;
230: l_effective_start_date date;
312: select TABLE_NAME,
313: COLUMN_NAME,
314: column_value ,
315: business_group_id
316: from ben_elig_obj_f
317: where elig_obj_id = p_elig_obj_id
318: and p_effective_date between effective_start_date and effective_end_date;
319:
320: l_obj_det c_obj_det%rowtype;
422: p_argument => 'effective_date',
423: p_argument_value => p_effective_date);
424:
425: if(p_source = 'C') then
426: l_chk_obj_id := 'select elig_obj_id from ben_elig_obj_f '||
427: ' where table_name = ''BEN_CM_TYP_F'' '||
428: ' and column_name = ''CM_TYP_ID'' '||
429: ' and business_group_id = '||p_business_group_id||
430: ' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') || ''',''DD-MM-RRRR'') between effective_start_date and effective_end_date ' ||
429: ' and business_group_id = '||p_business_group_id||
430: ' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') || ''',''DD-MM-RRRR'') between effective_start_date and effective_end_date ' ||
431: ' and column_value = '|| p_col_value;
432: else
433: l_chk_obj_id := 'select elig_obj_id from ben_elig_obj_f '||
434: ' where table_name = ''BEN_LER_F'' '||
435: ' and column_name = ''LER_ID'' '||
436: ' and business_group_id = '||p_business_group_id||
437: ' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') || ''',''DD-MM-RRRR'') between effective_start_date and effective_end_date ' ||
455: fetch c_chk_ebo into l_elig_obj_id;
456: if c_chk_ebo%notfound then
457: close c_chk_ebo;
458: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
459: hr_utility.set_message_token('TABLE_NAME', 'ben_elig_obj_f');
460: hr_utility.set_message_token('SESSION_DATE', to_char(p_effective_date));
461: hr_utility.raise_error;
462: end if;
463: close c_chk_ebo;