[Home] [Help]
PACKAGE BODY: APPS.PQH_RBC_ELPRO
Source
1 PACKAGE BODY PQH_RBC_ELPRO as
2 /* $Header: pqrbcelp.pkb 120.0 2005/10/06 14:52 srajakum noship $ */
3
4 g_package Varchar2(30) := 'pqh_rbc_stage_to_rbc';
5
6 procedure get_criteria_details (p_short_code in varchar2,
7 p_business_group_id in number,
8 p_criteria_id out nocopy number,
9 P_CRIT_COL1_DATATYPE out nocopy varchar2,
10 p_CRITERIA_TYPE OUT nocopy VARCHAR2,
11 p_ALLOW_RANGE_VALIDATION_FLAG out nocopy varchar2,
12 p_CRIT_COL1_VAL_TYPE_CD out nocopy varchar2,
13 P_CRIT_COL2_DATATYPE out nocopy varchar2,
14 p_ALLOW_RANGE_VALIDATION_FLAG2 out nocopy varchar2,
15 p_CRIT_COL2_VAL_TYPE_CD out nocopy varchar2
16 ) is
17 Begin
18
19 Select ELIGY_CRITERIA_ID, CRIT_COL1_DATATYPE,
20 CRITERIA_TYPE, ALLOW_RANGE_VALIDATION_FLAG,
21 CRIT_COL1_VAL_TYPE_CD,CRIT_COL2_DATATYPE,
22 ALLOW_RANGE_VALIDATION_FLAG2,CRIT_COL2_VAL_TYPE_CD
23 into p_criteria_id, P_CRIT_COL1_DATATYPE,
24 p_CRITERIA_TYPE, p_ALLOW_RANGE_VALIDATION_FLAG,
25 p_CRIT_COL1_VAL_TYPE_CD, P_CRIT_COL2_DATATYPE,
26 p_ALLOW_RANGE_VALIDATION_FLAG2,p_CRIT_COL2_VAL_TYPE_CD
27 from ben_eligy_criteria
28 where short_code = p_short_code
29 and business_group_id = p_business_group_id;
30
31
32 exception
33 when no_data_found then
34 begin
35 hr_utility.set_location('Criteria details not found for the short_code '||p_short_code||' For business group '||p_business_group_id, 20);
36 Select ELIGY_CRITERIA_ID, CRIT_COL1_DATATYPE,
37 CRITERIA_TYPE, ALLOW_RANGE_VALIDATION_FLAG,
38 CRIT_COL1_VAL_TYPE_CD,CRIT_COL2_DATATYPE,
39 ALLOW_RANGE_VALIDATION_FLAG2,CRIT_COL2_VAL_TYPE_CD
40 into p_criteria_id, P_CRIT_COL1_DATATYPE,
41 p_CRITERIA_TYPE, p_ALLOW_RANGE_VALIDATION_FLAG,
42 p_CRIT_COL1_VAL_TYPE_CD, P_CRIT_COL2_DATATYPE,
43 p_ALLOW_RANGE_VALIDATION_FLAG2,p_CRIT_COL2_VAL_TYPE_CD
44 from ben_eligy_criteria
45 where short_code = p_short_code
46 and business_group_id is null;
47 end;
48 when too_many_rows then
49 hr_utility.set_location('More than one row found for the short_code '||p_short_code, 20);
50 raise;
51 when others then
52 hr_utility.set_location('Issue in getting details for the short_code '||p_short_code, 20);
53 raise;
54 End get_criteria_details;
55
56
57 procedure create_elpro(p_name in varchar2,
58 p_description in varchar2,
59 p_business_group_id in number,
60 p_effective_date in date,
61 p_elig_prfl_id out nocopy number) is
62
63 l_proc varchar2(61) := g_package||'create_elpro';
64 l_elp_id number;
65 l_effective_start_date date;
66 l_effective_end_date date;
67 l_elp_ovn number;
68
69 Begin
70
71 hr_utility.set_location('inside '||l_proc,10);
72
73 BEN_ELIGY_PROFILE_API.CREATE_ELIGY_PROFILE(
74 P_EFFECTIVE_DATE => p_effective_date
75 ,P_BUSINESS_GROUP_ID => p_business_group_id
76 ,P_ASMT_TO_USE_CD => 'ANY'
77 ,P_BNFT_CAGR_PRTN_CD => 'BNFT'
78 ,P_DESCRIPTION => p_description
79 ,P_ELIGY_PRFL_ID => l_elp_id
80 ,P_NAME => p_name
81 ,P_STAT_CD => 'A'
82 ,P_EFFECTIVE_START_DATE => l_effective_start_date
83 ,P_EFFECTIVE_END_DATE => l_effective_end_date
84 ,P_OBJECT_VERSION_NUMBER => l_elp_ovn
85 );
86
87 hr_utility.set_location('Leaving '||l_proc,10);
88
89 p_elig_prfl_id := l_elp_id;
90
91 End create_elpro;
92
93
94 procedure create_criteria (p_criteria_code in varchar2,
95 p_char_value1 in varchar2 default null,
96 p_char_value2 in varchar2 default null,
97 p_char_value3 in varchar2 default null,
98 p_char_value4 in varchar2 default null,
99 p_number_value1 in number default null,
100 p_number_value2 in number default null,
101 p_number_value3 in number default null,
102 p_number_value4 in number default null,
103 p_date_value1 in date default null,
104 p_date_value2 in date default null,
105 p_date_value3 in date default null,
106 p_date_value4 in date default null,
107 p_business_group_id in number default null,
108 p_effective_date in date,
109 p_elig_prfl_id in number
110 ) is
111
112 l_proc varchar2(61) := g_package||'create_criteria';
113 l_elp_id number;
114 l_effective_start_date date;
115 l_effective_end_date date;
116 l_crit_ovn number;
117 l_criteria_id number;
118 l_CRIT_COL1_DATATYPE varchar2(80);
119 l_CRITERIA_TYPE VARCHAR2(80);
120 l_ALLOW_RANGE_VALIDATION_FLAG varchar2(1);
121 l_CRIT_COL1_VAL_TYPE_CD varchar2(80);
122 l_crit_values_id number;
123 l_criteria_value1 varchar2(80);
124 l_criteria_value2 varchar2(80);
125 l_criteria_value3 varchar2(80);
126 l_criteria_value4 varchar2(80);
127 l_ALLOW_RANGE_VALIDATION_FLAG2 varchar2(1);
128 l_CRIT_COL2_VAL_TYPE_CD varchar2(80);
129 l_CRIT_COL2_DATATYPE varchar2(80);
130 l_criteria_typ_id number;
131 Begin
132
133 hr_utility.set_location('inside '||l_proc,10);
134
135 -- get_criteria_details
136 get_criteria_details (p_short_code => p_criteria_code,
137 p_business_group_id => p_business_group_id,
138 p_criteria_id => l_criteria_typ_id,
139 P_CRIT_COL1_DATATYPE => l_CRIT_COL1_DATATYPE,
140 p_CRITERIA_TYPE => l_CRITERIA_TYPE,
141 p_ALLOW_RANGE_VALIDATION_FLAG => l_ALLOW_RANGE_VALIDATION_FLAG,
142 p_CRIT_COL1_VAL_TYPE_CD => l_CRIT_COL1_VAL_TYPE_CD,
143 P_CRIT_COL2_DATATYPE => l_CRIT_COL2_DATATYPE,
144 p_ALLOW_RANGE_VALIDATION_FLAG2 => l_ALLOW_RANGE_VALIDATION_FLAG2,
145 p_CRIT_COL2_VAL_TYPE_CD => l_CRIT_COL2_VAL_TYPE_CD
146 );
147 if l_CRITERIA_TYPE = 'USER' then
148 -- User defined criteria
149 hr_utility.set_location('User defined criteria ',10);
150
151 ben_eligy_crit_values_api.create_eligy_crit_values
152 (
153 p_eligy_crit_values_id => l_crit_values_id
154 ,p_eligy_prfl_id => p_elig_prfl_id
155 ,p_eligy_criteria_id => l_criteria_typ_id
156 ,p_effective_start_date => l_effective_start_date
157 ,p_effective_end_date => l_effective_end_date
158 ,p_number_value1 => p_number_value1
159 ,p_number_value2 => p_number_value2
160 ,p_char_value1 => p_char_value1
161 ,p_char_value2 => p_char_value2
162 ,p_date_value1 => p_date_value1
163 ,p_date_value2 => p_date_value2
164 ,p_business_group_id => p_business_group_id
165 ,p_object_version_number => l_crit_ovn
166 ,p_effective_date => p_effective_date
167 ,p_char_value3 => p_char_value3
168 ,p_char_value4 => p_char_value4
169 ,p_number_value3 => p_number_value3
170 ,p_number_value4 => p_number_value4
171 ,p_date_value3 => p_date_value3
172 ,p_date_value4 => p_date_value4
173 );
174
175 elsif l_CRITERIA_TYPE = 'STD' then
176 hr_utility.set_location('Standard criteria ',10);
177
178
179 if l_CRIT_COL1_VAL_TYPE_CD is not null then
180 if l_CRIT_COL1_DATATYPE = 'N' then
181 l_criteria_value1 := to_char(p_number_value1);
182 l_criteria_value2 := to_char(p_number_value2);
183 elsif l_CRIT_COL1_DATATYPE = 'C' then
184 l_criteria_value1 := p_char_value1;
185 l_criteria_value2 := p_char_value2;
186 elsif l_CRIT_COL1_DATATYPE = 'D' then
187 l_criteria_value1 := to_char(p_date_value1,'dd-mm-yyyy');
188 l_criteria_value2 := to_char(p_date_value2,'dd-mm-yyyy');
189 end if;
190 end if;
191
192 if l_CRIT_COL2_VAL_TYPE_CD is not null then
193 if l_CRIT_COL2_DATATYPE = 'N' then
194 l_criteria_value3 := to_char(p_number_value3);
195 l_criteria_value4 := to_char(p_number_value4);
196 elsif l_CRIT_COL2_DATATYPE = 'C' then
197 l_criteria_value3 := p_char_value3;
198 l_criteria_value4 := p_char_value4;
199 elsif l_CRIT_COL2_DATATYPE = 'D' then
200 l_criteria_value3 := to_char(p_date_value3,'dd-mm-yyyy');
201 l_criteria_value4 := to_char(p_date_value4,'dd-mm-yyyy');
202 end if;
203 end if;
204
205
206 if p_criteria_code = 'EAN' then
207 ben_ELIG_ASNT_SET_PRTE_api.create_ELIG_ASNT_SET_PRTE
208 (p_elig_asnt_set_prte_id => l_criteria_id
209 ,p_effective_start_date => l_effective_start_date
210 ,p_effective_end_date => l_effective_end_date
211 ,p_assignment_set_id => l_criteria_value1
212 ,p_eligy_prfl_id => p_elig_prfl_id
213 ,p_excld_flag => 'N'
214 ,p_business_group_id => p_business_group_id
215 ,p_object_version_number => l_crit_ovn
216 ,p_effective_date => p_effective_date
217 );
218 elsif p_criteria_code = 'EAP' then
219 ben_ELIG_AGE_PRTE_api.create_ELIG_AGE_PRTE
220 (p_elig_age_prte_id => l_criteria_id
221 ,p_effective_start_date => l_effective_start_date
222 ,p_effective_end_date => l_effective_end_date
223 ,p_business_group_id => p_business_group_id
224 ,p_age_fctr_id => to_number(l_criteria_value1)
225 ,p_eligy_prfl_id => p_elig_prfl_id
226 ,p_excld_flag => 'N'
227 ,p_object_version_number => l_crit_ovn
228 ,p_effective_date => p_effective_date
229 );
230 elsif p_criteria_code = 'EBN' then
231 ben_ELIG_BENFTS_GRP_PRTE_api.create_ELIG_BENFTS_GRP_PRTE
232 (p_elig_benfts_grp_prte_id => l_criteria_id
233 ,p_effective_start_date => l_effective_start_date
234 ,p_effective_end_date => l_effective_end_date
235 ,p_benfts_grp_id => to_number(l_criteria_value1)
236 ,p_eligy_prfl_id => p_elig_prfl_id
237 ,p_excld_flag => 'N'
238 ,p_business_group_id => p_business_group_id
239 ,p_object_version_number => l_crit_ovn
240 ,p_effective_date => p_effective_date
241 );
242 elsif p_criteria_code = 'EBU' then
243 ben_ELIG_BRGNG_UNIT_PRTE_api.create_ELIG_BRGNG_UNIT_PRTE
244 (p_elig_brgng_unit_prte_id => l_criteria_id
245 ,p_effective_start_date => l_effective_start_date
246 ,p_effective_end_date => l_effective_end_date
247 ,p_brgng_unit_cd => l_criteria_value1
248 ,p_eligy_prfl_id => p_elig_prfl_id
249 ,p_excld_flag => 'N'
250 ,p_business_group_id => p_business_group_id
251 ,p_object_version_number => l_crit_ovn
252 ,p_effective_date => p_effective_date);
253 elsif p_criteria_code = 'ECL' then
254 ben_ELIG_COMP_LVL_PRTE_api.create_ELIG_COMP_LVL_PRTE
255 (p_elig_comp_lvl_prte_id => l_criteria_id
256 ,p_effective_start_date => l_effective_start_date
257 ,p_effective_end_date => l_effective_end_date
258 ,p_comp_lvl_fctr_id => to_number(l_criteria_value1)
259 ,p_eligy_prfl_id => p_elig_prfl_id
260 ,p_excld_flag => 'N'
261 ,p_business_group_id => p_business_group_id
262 ,p_object_version_number => l_crit_ovn
263 ,p_effective_date => p_effective_date);
264 elsif p_criteria_code = 'ECP' then
265 ben_ELIG_CMBN_AGE_LOS_api.create_ELIG_CMBN_AGE_LOS
266 (p_elig_cmbn_age_los_prte_id => l_criteria_id
267 ,p_effective_start_date => l_effective_start_date
268 ,p_effective_end_date => l_effective_end_date
269 ,p_cmbn_age_los_fctr_id => to_number(l_criteria_value1)
270 ,p_eligy_prfl_id => p_elig_prfl_id
271 ,p_excld_flag => 'N'
272 ,p_mndtry_flag => 'N'
273 ,p_business_group_id => p_business_group_id
274 ,p_object_version_number => l_crit_ovn
275 ,p_effective_date => p_effective_date);
276 elsif p_criteria_code = 'ECY' then
277 ben_ELIG_COMPTNCY_PRTE_api.create_ELIG_COMPTNCY_PRTE
278 (p_ELIG_COMPTNCY_PRTE_id => l_criteria_id
279 ,p_effective_start_date => l_effective_start_date
280 ,p_effective_end_date => l_effective_end_date
281 ,p_competence_id => to_number(l_criteria_value1)
282 ,p_rating_level_id => to_number(l_criteria_value3)
283 ,p_eligy_prfl_id => p_elig_prfl_id
284 ,p_excld_flag => 'N'
285 ,p_business_group_id => p_business_group_id
286 ,p_object_version_number => l_crit_ovn
287 ,p_effective_date => p_effective_date);
288 elsif p_criteria_code = 'EDB' then
289 ben_ELIG_DSBLD_PRTE_api.create_ELIG_DSBLD_PRTE
290 (p_elig_dsbld_prte_id => l_criteria_id
291 ,p_effective_start_date => l_effective_start_date
292 ,p_effective_end_date => l_effective_end_date
293 ,p_dsbld_cd => l_criteria_value1
294 ,p_eligy_prfl_id => p_elig_prfl_id
295 ,p_excld_flag => 'N'
296 ,p_business_group_id => p_business_group_id
297 ,p_object_version_number => l_crit_ovn
298 ,p_effective_date => p_effective_date);
299 elsif p_criteria_code = 'EES' then
300 ben_ELIG_EE_STAT_PRTE_api.create_ELIG_EE_STAT_PRTE
301 (p_elig_ee_stat_prte_id => l_criteria_id
302 ,p_effective_start_date => l_effective_start_date
303 ,p_effective_end_date => l_effective_end_date
304 ,p_assignment_status_type_id => to_number(l_criteria_value1)
305 ,p_eligy_prfl_id => p_elig_prfl_id
306 ,p_excld_flag => 'N'
307 ,p_business_group_id => p_business_group_id
308 ,p_object_version_number => l_crit_ovn
309 ,p_effective_date => p_effective_date);
310 elsif p_criteria_code = 'EFP' then
311 ben_ELIG_FL_TM_PT_TM_PRTE_api.create_ELIG_FL_TM_PT_TM_PRTE
315 ,p_fl_tm_pt_tm_cd => l_criteria_value1
312 (p_elig_fl_tm_pt_tm_prte_id => l_criteria_id
313 ,p_effective_start_date => l_effective_start_date
314 ,p_effective_end_date => l_effective_end_date
316 ,p_eligy_prfl_id => p_elig_prfl_id
317 ,p_excld_flag => 'N'
318 ,p_business_group_id => p_business_group_id
319 ,p_object_version_number => l_crit_ovn
320 ,p_effective_date => p_effective_date);
321 elsif p_criteria_code = 'EGN' then
322 ben_elig_gndr_prte_api.create_elig_gndr_prte
323 (p_elig_gndr_prte_id => l_criteria_id
324 ,p_effective_start_date => l_effective_start_date
325 ,p_effective_end_date => l_effective_end_date
326 ,p_sex => l_criteria_value1
327 ,p_eligy_prfl_id => p_elig_prfl_id
328 ,p_excld_flag => 'N'
329 ,p_business_group_id => p_business_group_id
330 ,p_object_version_number => l_crit_ovn
331 ,p_effective_date => p_effective_date);
332 elsif p_criteria_code = 'EHS' then
333 ben_ELIG_HRLY_SLRD_PRTE_api.create_ELIG_HRLY_SLRD_PRTE
334 (p_elig_hrly_slrd_prte_id => l_criteria_id
335 ,p_effective_start_date => l_effective_start_date
336 ,p_effective_end_date => l_effective_end_date
337 ,p_hrly_slrd_cd => l_criteria_value1
338 ,p_eligy_prfl_id => p_elig_prfl_id
339 ,p_excld_flag => 'N'
340 ,p_business_group_id => p_business_group_id
341 ,p_object_version_number => l_crit_ovn
342 ,p_effective_date => p_effective_date);
343 elsif p_criteria_code = 'EHW' then
344 ben_ELIG_HRS_WKD_PRTE_api.create_ELIG_HRS_WKD_PRTE
345 (p_elig_hrs_wkd_prte_id => l_criteria_id
346 ,p_effective_start_date => l_effective_start_date
347 ,p_effective_end_date => l_effective_end_date
348 ,p_hrs_wkd_in_perd_fctr_id => to_number(l_criteria_value1)
349 ,p_eligy_prfl_id => p_elig_prfl_id
350 ,p_excld_flag => 'N'
351 ,p_business_group_id => p_business_group_id
352 ,p_object_version_number => l_crit_ovn
353 ,p_effective_date => p_effective_date);
354 elsif p_criteria_code = 'EJP' then
355 ben_ELIGY_JOB_PRTE_api.create_ELIGY_JOB_PRTE
356 (p_elig_job_prte_id => l_criteria_id
357 ,p_effective_start_date => l_effective_start_date
358 ,p_effective_end_date => l_effective_end_date
359 ,p_job_id => to_number(l_criteria_value1)
360 ,p_eligy_prfl_id => p_elig_prfl_id
361 ,p_excld_flag => 'N'
362 ,p_business_group_id => p_business_group_id
363 ,p_object_version_number => l_crit_ovn
364 ,p_effective_date => p_effective_date);
365 elsif p_criteria_code = 'ELN' then
366 ben_ELIG_LGL_ENTY_PRTE_api.create_ELIG_LGL_ENTY_PRTE
367 (p_elig_lgl_enty_prte_id => l_criteria_id
368 ,p_effective_start_date => l_effective_start_date
369 ,p_effective_end_date => l_effective_end_date
370 ,p_organization_id => to_number(l_criteria_value1)
371 ,p_eligy_prfl_id => p_elig_prfl_id
372 ,p_excld_flag => 'N'
373 ,p_business_group_id => p_business_group_id
374 ,p_object_version_number => l_crit_ovn
375 ,p_effective_date => p_effective_date);
376 elsif p_criteria_code = 'ELR' then
377 ben_ELIG_LOA_RSN_PRTE_api.create_ELIG_LOA_RSN_PRTE
378 (p_elig_loa_rsn_prte_id => l_criteria_id
379 ,p_effective_start_date => l_effective_start_date
380 ,p_effective_end_date => l_effective_end_date
381 ,p_absence_attendance_type_id => to_number(l_criteria_value1)
382 ,p_abs_attendance_reason_id => to_number(l_criteria_value3)
383 ,p_eligy_prfl_id => p_elig_prfl_id
384 ,p_excld_flag => 'N'
385 ,p_business_group_id => p_business_group_id
386 ,p_object_version_number => l_crit_ovn
387 ,p_effective_date => p_effective_date);
388 elsif p_criteria_code = 'ELS' then
389 ben_ELIG_LOS_PRTE_api.create_ELIG_LOS_PRTE
390 (p_elig_los_prte_id => l_criteria_id
394 ,p_eligy_prfl_id => p_elig_prfl_id
391 ,p_effective_start_date => l_effective_start_date
392 ,p_effective_end_date => l_effective_end_date
393 ,p_los_fctr_id => to_number(l_criteria_value1)
395 ,p_excld_flag => 'N'
396 ,p_business_group_id => p_business_group_id
397 ,p_object_version_number => l_crit_ovn
398 ,p_effective_date => p_effective_date);
399 elsif p_criteria_code = 'ELV' then
400 ben_ELIG_LVG_RSN_PRTE_api.create_ELIG_LVG_RSN_PRTE
401 (p_elig_lvg_rsn_prte_id => l_criteria_id
402 ,p_effective_start_date => l_effective_start_date
403 ,p_effective_end_date => l_effective_end_date
404 ,p_lvg_rsn_cd => l_criteria_value1
405 ,p_eligy_prfl_id => p_elig_prfl_id
406 ,p_excld_flag => 'N'
407 ,p_business_group_id => p_business_group_id
408 ,p_object_version_number => l_crit_ovn
409 ,p_effective_date => p_effective_date);
410 elsif p_criteria_code = 'EOM' then
411 ben_ELIG_OPTD_MDCR_PRTE_api.create_ELIG_OPTD_MDCR_PRTE
412 (p_elig_optd_mdcr_prte_id => l_criteria_id
413 ,p_effective_start_date => l_effective_start_date
414 ,p_effective_end_date => l_effective_end_date
415 ,p_optd_mdcr_flag => l_criteria_value1
416 ,p_eligy_prfl_id => p_elig_prfl_id
417 ,p_exlcd_flag => 'N'
418 ,p_business_group_id => p_business_group_id
419 ,p_object_version_number => l_crit_ovn
420 ,p_effective_date => p_effective_date);
421 elsif p_criteria_code = 'EOU' then
422 ben_ELIG_ORG_UNIT_PRTE_api.create_ELIG_ORG_UNIT_PRTE
423 (p_elig_org_unit_prte_id => l_criteria_id
424 ,p_effective_start_date => l_effective_start_date
425 ,p_effective_end_date => l_effective_end_date
426 ,p_organization_id => to_number(l_criteria_value1)
427 ,p_eligy_prfl_id => p_elig_prfl_id
428 ,p_excld_flag => 'N'
429 ,p_business_group_id => p_business_group_id
430 ,p_object_version_number => l_crit_ovn
431 ,p_effective_date => p_effective_date);
432 elsif p_criteria_code = 'EPB' then
433 ben_ELIG_PY_BSS_PRTE_api.create_ELIG_PY_BSS_PRTE
434 (p_elig_py_bss_prte_id => l_criteria_id
435 ,p_effective_start_date => l_effective_start_date
436 ,p_effective_end_date => l_effective_end_date
437 ,p_pay_basis_id => to_number(l_criteria_value1)
438 ,p_eligy_prfl_id => p_elig_prfl_id
439 ,p_excld_flag => 'N'
440 ,p_business_group_id => p_business_group_id
441 ,p_object_version_number => l_crit_ovn
442 ,p_effective_date => p_effective_date);
443 elsif p_criteria_code = 'EPF' then
444 ben_ELIG_PCT_FL_TM_PRTE_api.create_ELIG_PCT_FL_TM_PRTE
445 (p_elig_pct_fl_tm_prte_id => l_criteria_id
446 ,p_effective_start_date => l_effective_start_date
447 ,p_effective_end_date => l_effective_end_date
448 ,p_pct_fl_tm_fctr_id => to_number(l_criteria_value1)
449 ,p_eligy_prfl_id => p_elig_prfl_id
450 ,p_excld_flag => 'N'
451 ,p_business_group_id => p_business_group_id
452 ,p_object_version_number => l_crit_ovn
453 ,p_effective_date => p_effective_date);
454 elsif p_criteria_code = 'EPS' then
455 ben_ELIG_PSTN_PRTE_api.create_ELIG_PSTN_PRTE
456 (p_ELIG_PSTN_PRTE_id => l_criteria_id
457 ,p_effective_start_date => l_effective_start_date
458 ,p_effective_end_date => l_effective_end_date
459 ,p_position_id => to_number(l_criteria_value1)
460 ,p_eligy_prfl_id => p_elig_prfl_id
461 ,p_excld_flag => 'N'
462 ,p_business_group_id => p_business_group_id
463 ,p_object_version_number => l_crit_ovn
464 ,p_effective_date => p_effective_date);
465 elsif p_criteria_code = 'EPT' then
466 ben_ELIG_PER_TYP_PRTE_api.create_ELIG_PER_TYP_PRTE
467 (p_elig_per_typ_prte_id => l_criteria_id
468 ,p_effective_start_date => l_effective_start_date
469 ,p_effective_end_date => l_effective_end_date
473 ,p_business_group_id => p_business_group_id
470 ,p_person_type_id => to_number(l_criteria_value1)
471 ,p_eligy_prfl_id => p_elig_prfl_id
472 ,p_excld_flag => 'N'
474 ,p_object_version_number => l_crit_ovn
475 ,p_effective_date => p_effective_date);
476 elsif p_criteria_code = 'EPY' then
477 ben_ELIG_PYRL_PRTE_api.create_ELIG_PYRL_PRTE
478 (p_elig_pyrl_prte_id => l_criteria_id
479 ,p_effective_start_date => l_effective_start_date
480 ,p_effective_end_date => l_effective_end_date
481 ,p_payroll_id => to_number(l_criteria_value1)
482 ,p_eligy_prfl_id => p_elig_prfl_id
483 ,p_excld_flag => 'N'
484 ,p_business_group_id => p_business_group_id
485 ,p_object_version_number => l_crit_ovn
486 ,p_effective_date => p_effective_date);
487 elsif p_criteria_code = 'EPZ' then
488 ben_ELIG_PSTL_CD_RNG_PRTE_api.create_ELIG_PSTL_CD_RNG_PRTE
489 (p_elig_pstl_cd_r_rng_prte_id => l_criteria_id
490 ,p_effective_start_date => l_effective_start_date
491 ,p_effective_end_date => l_effective_end_date
492 ,p_pstl_zip_rng_id => to_number(l_criteria_value1)
493 ,p_eligy_prfl_id => p_elig_prfl_id
494 ,p_excld_flag => 'N'
495 ,p_business_group_id => p_business_group_id
496 ,p_object_version_number => l_crit_ovn
497 ,p_effective_date => p_effective_date);
498 elsif p_criteria_code = 'EQG' then
499 ben_ELIG_QUA_IN_GR_PRTE_api.create_ELIG_QUA_IN_GR_PRTE
500 (p_ELIG_QUA_IN_GR_PRTE_id => l_criteria_id
501 ,p_effective_start_date => l_effective_start_date
502 ,p_effective_end_date => l_effective_end_date
503 ,p_quar_in_grade_cd => l_criteria_value1
504 ,p_eligy_prfl_id => p_elig_prfl_id
505 ,p_excld_flag => 'N'
506 ,p_business_group_id => p_business_group_id
507 ,p_object_version_number => l_crit_ovn
508 ,p_effective_date => p_effective_date);
509 elsif p_criteria_code = 'EQT' then
510 ben_elig_qual_titl_prte_api.create_elig_qual_titl_prte
511 (p_elig_qual_titl_prte_id => l_criteria_id
512 ,p_effective_start_date => l_effective_start_date
513 ,p_effective_end_date => l_effective_end_date
514 ,p_title => l_criteria_value3
515 ,p_qualification_type_id => to_number(l_criteria_value1)
516 ,p_eligy_prfl_id => p_elig_prfl_id
517 ,p_excld_flag => 'N'
518 ,p_business_group_id => p_business_group_id
519 ,p_object_version_number => l_crit_ovn
520 ,p_effective_date => p_effective_date);
521 elsif p_criteria_code = 'ERG' then
522 ben_ELIG_PERF_RTNG_PRTE_api.create_ELIG_PERF_RTNG_PRTE
523 (p_ELIG_PERF_RTNG_PRTE_id => l_criteria_id
524 ,p_effective_start_date => l_effective_start_date
525 ,p_effective_end_date => l_effective_end_date
526 ,p_event_type => l_criteria_value1
527 ,p_perf_rtng_cd => l_criteria_value3
528 ,p_eligy_prfl_id => p_elig_prfl_id
529 ,p_excld_flag => 'N'
530 ,p_business_group_id => p_business_group_id
531 ,p_object_version_number => l_crit_ovn
532 ,p_effective_date => p_effective_date);
533 elsif p_criteria_code = 'ESA' then
534 ben_elig_svc_area_prte_api.create_elig_svc_area_prte
535 (p_elig_svc_area_prte_id => l_criteria_id
536 ,p_effective_start_date => l_effective_start_date
537 ,p_effective_end_date => l_effective_end_date
538 ,p_svc_area_id => to_number(l_criteria_value1)
539 ,p_eligy_prfl_id => p_elig_prfl_id
540 ,p_excld_flag => 'N'
541 ,p_business_group_id => p_business_group_id
542 ,p_object_version_number => l_crit_ovn
543 ,p_effective_date => p_effective_date);
544 elsif p_criteria_code = 'ETU' then
545 ben_ELIG_TBCO_USE_PRTE_api.create_ELIG_TBCO_USE_PRTE
546 (p_elig_tbco_use_prte_id => l_criteria_id
547 ,p_effective_start_date => l_effective_start_date
548 ,p_effective_end_date => l_effective_end_date
552 ,p_business_group_id => p_business_group_id
549 ,p_uses_tbco_flag => l_criteria_value1
550 ,p_eligy_prfl_id => p_elig_prfl_id
551 ,p_excld_flag => 'N'
553 ,p_object_version_number => l_crit_ovn
554 ,p_effective_date => p_effective_date);
555 elsif p_criteria_code = 'EWL' then
556 ben_ELIG_WK_LOC_PRTE_api.create_ELIG_WK_LOC_PRTE
557 (p_elig_wk_loc_prte_id => l_criteria_id
558 ,p_effective_start_date => l_effective_start_date
559 ,p_effective_end_date => l_effective_end_date
560 ,p_location_id => to_number(l_criteria_value1)
561 ,p_eligy_prfl_id => p_elig_prfl_id
562 ,p_excld_flag => 'N'
563 ,p_business_group_id => p_business_group_id
564 ,p_object_version_number => l_crit_ovn
565 ,p_effective_date => p_effective_date);
566 elsif p_criteria_code = 'EPF' then
567 ben_ELIG_PCT_FL_TM_PRTE_api.create_ELIG_PCT_FL_TM_PRTE
568 (p_elig_pct_fl_tm_prte_id => l_criteria_id
569 ,p_effective_start_date => l_effective_start_date
570 ,p_effective_end_date => l_effective_end_date
571 ,p_pct_fl_tm_fctr_id => to_number(l_criteria_value1)
572 ,p_eligy_prfl_id => p_elig_prfl_id
573 ,p_excld_flag => 'N'
574 ,p_business_group_id => p_business_group_id
575 ,p_object_version_number => l_crit_ovn
576 ,p_effective_date => p_effective_date);
577 elsif p_criteria_code = 'EGR' then
578 ben_ELIG_GRD_PRTE_api.create_ELIG_GRD_PRTE
579 (p_elig_grd_prte_id => l_criteria_id
580 ,p_effective_start_date => l_effective_start_date
581 ,p_effective_end_date => l_effective_end_date
582 ,p_grade_id => to_number(l_criteria_value1)
583 ,p_eligy_prfl_id => p_elig_prfl_id
584 ,p_excld_flag => 'N'
585 ,p_business_group_id => p_business_group_id
586 ,p_object_version_number => l_crit_ovn
587 ,p_effective_date => p_effective_date);
588 elsif p_criteria_code = 'ELU' then
589 ben_ELIG_LBR_MMBR_PRTE_api.create_ELIG_LBR_MMBR_PRTE
590 (p_elig_lbr_mmbr_prte_id => l_criteria_id
591 ,p_effective_start_date => l_effective_start_date
592 ,p_effective_end_date => l_effective_end_date
593 ,p_lbr_mmbr_flag => l_criteria_value1
594 ,p_eligy_prfl_id => p_elig_prfl_id
595 ,p_excld_flag => 'N'
596 ,p_business_group_id => p_business_group_id
597 ,p_object_version_number => l_crit_ovn
598 ,p_effective_date => p_effective_date);
599 elsif p_criteria_code = 'EPG' then
600 ben_ELIG_PPL_GRP_PRTE_api.create_ELIG_PPL_GRP_PRTE
601 (p_elig_ppl_grp_prte_id => l_criteria_id
602 ,p_effective_start_date => l_effective_start_date
603 ,p_effective_end_date => l_effective_end_date
604 ,p_people_group_id => to_number(l_criteria_value1)
605 ,p_eligy_prfl_id => p_elig_prfl_id
606 ,p_excld_flag => 'N'
607 ,p_business_group_id => p_business_group_id
608 ,p_object_version_number => l_crit_ovn
609 ,p_effective_date => p_effective_date);
610 end if;
611 else
612 hr_utility.set_location('Not a valid criteria type ',10);
613
614
615 End if;
616
617
618
619 hr_utility.set_location('Leaving '||l_proc,10);
620
621 -- p_elig_prfl_id := l_elp_id;
622
623 End create_criteria;
624
625
626
627 end PQH_RBC_ELPRO;