[Home] [Help]
PACKAGE BODY: APPS.PQH_FR_CR_PATH_ENGINE_PKG
Source
1 PACKAGE BODY PQH_FR_CR_PATH_ENGINE_PKG AS
2 /* $Header: pqcrpeng.pkb 120.1 2005/06/03 04:57 sankjain noship $ */
3
4 g_end_of_time DATE := TO_DATE('31/12/4712','DD/MM/RRRR');
5 g_package varchar2(30) := 'PQH_FR_CR_PATH_ENGINE_PKG.';
6 g_business_group_id number;
7
8 function check_length_of_service (p_person_id in number,
9 p_business_group_id in number,
10 p_assignment_id in number,
11 p_length_of_service_type in varchar2,
12 p_effective_date in date,
13 p_req_years in number,
14 p_req_Months in number,
15 p_req_days in number) return varchar2 is
16
17 l_proc varchar2(60) := g_package||'check_length_of_service';
18
19 l_length_of_service number;
20
21 l_req_length_of_service number;
22
23 l_return_status varchar2(1);
24
25 begin
26 hr_utility.set_location('Entering into '||l_proc,5);
27
28 l_length_of_service := PQH_LENGTH_OF_SERVICE_PKG.get_length_of_service(
29 p_bg_id => p_business_group_id,
30 p_person_id => p_person_id,
31 p_assignment_id => p_assignment_id,
32 p_los_type => p_length_of_service_type,
33 p_return_units => 'M',
34 p_determination_date => p_effective_date );
35
36 l_req_length_of_service := pqh_corps_utility.los_in_months(p_los_years => p_req_years,
37 p_los_months => p_req_Months,
38 p_los_days => p_req_days);
39
40 hr_utility.set_location('Required length of service in Months '||to_char(l_req_length_of_service),5);
41 hr_utility.set_location('Current length of service in Months '||to_char(l_length_of_service),5);
42
43 if l_length_of_service >= l_req_length_of_service then
44 l_return_status := 'Y';
45 else
46 l_return_status := 'N';
47 end if;
48 hr_utility.set_location('Leaving from '||l_proc,5);
49 return l_return_status;
50 end check_length_of_service;
51
52 procedure check_eligibility ( p_per_in_ler_id in number,
53 p_person_id in number,
54 p_effective_date in date,
55 p_business_group_id in number,
56 p_pgm_id in number default null,
57 p_pl_id in number default null,
58 p_oipl_id in number default null,
59 p_opt_id in number default null,
60 p_plip_id in number default null,
61 p_ptip_id in number default null,
62 p_pl_type_id in number default null,
63 p_par_pgm_id in number default null,
64 p_par_pl_id in number default null,
65 p_par_plip_id in number default null,
66 p_par_opt_id in number default null,
67 p_return_status out nocopy varchar2,
68 p_score_tab out nocopy ben_evaluate_elig_profiles.scoreTab
69 ) is
70
71 l_proc varchar2(60) := g_package||'check_eligibility';
72
73 l_return_status varchar2(1);
74 l_elig_return_status boolean;
75 l_comp_rec ben_derive_part_and_rate_facts.g_cache_structure;
76 l_comp_obj_tree_row ben_manage_life_events.g_cache_proc_objects_rec;
77 l_empasg_row per_all_assignments_f%ROWTYPE;
78 l_benasg_row per_all_assignments_f%ROWTYPE;
79 l_pil_row ben_per_in_ler%ROWTYPE;
80 l_oiplip_rec ben_derive_part_and_rate_facts.g_cache_structure;
81 l_score_tab ben_evaluate_elig_profiles.scoreTab;
82
83 begin
84 hr_utility.set_location('Entering into '||l_proc,5);
85 hr_utility.set_location('Clearing Cache '||l_proc,5);
86 -- ben_cep_cache.clear_down_cache;
87
88 /* ben_manage_life_events.clear_init_benmngle_caches
89 (p_business_group_id => p_business_group_id
90 ,p_effective_date => p_effective_date
91 ,p_threads => 1
92 ,p_chunk_size => 1
93 ,p_max_errors => 1
94 ,p_benefit_action_id => Null
95 ,p_thread_id => 1
96 ); */
97
98 hr_utility.set_location('Creating ben person objects '||l_proc,5);
99
100 hr_utility.set_location('Pgm Id '||p_pgm_id,5);
101 hr_utility.set_location('p_oipl_id '||p_oipl_id,5);
102 hr_utility.set_location('p_opt_id '||p_opt_id,5);
103 hr_utility.set_location('p_plip_id '||p_plip_id,5);
104 hr_utility.set_location('p_ptip_id'||p_ptip_id,5);
105 hr_utility.set_location('p_pl_type_id '||p_pl_type_id,5);
106 hr_utility.set_location('p_par_pgm_id '||p_par_pgm_id,5);
107 hr_utility.set_location('p_par_pl_id '||p_par_pl_id,5);
108 hr_utility.set_location('p_par_plip_id '||p_par_plip_id,5);
109 hr_utility.set_location('p_par_opt_id '||p_par_opt_id,5);
110
111 ben_person_object.get_object
112 (p_person_id => p_person_id
113 ,p_rec => l_empasg_row
114 );
115
116 ben_person_object.get_benass_object
117 (p_person_id => p_person_id
118 ,p_rec => l_benasg_row
119 );
120
121 ben_person_object.get_object
122 (p_person_id => p_person_id
123 ,p_per_in_ler_id =>p_per_in_ler_id
124 ,p_rec => l_pil_row
125 );
126
127 hr_utility.set_location('Creating comp_obj_tree_row ',5);
128
129 l_comp_obj_tree_row.pl_id := p_pl_id;
130 l_comp_obj_tree_row.pgm_id := p_pgm_id;
131 l_comp_obj_tree_row.oipl_id := p_oipl_id;
132 l_comp_obj_tree_row.ptip_id := p_ptip_id;
133 l_comp_obj_tree_row.plip_id := p_plip_id;
134 l_comp_obj_tree_row.pl_nip := 'N';
135 l_comp_obj_tree_row.par_pgm_id := p_par_pgm_id;
136 l_comp_obj_tree_row.par_plip_id := p_plip_id;
137 l_comp_obj_tree_row.par_pl_id := p_par_pl_id;
138 l_comp_obj_tree_row.par_plip_id := p_par_plip_id;
139 l_comp_obj_tree_row.par_opt_id := p_par_opt_id;
140 l_comp_obj_tree_row.prtn_strt_dt := p_effective_date;
141
142
143 hr_utility.set_location('Creating derive rate factors data structure ',5);
144
145 hr_utility.set_location('Creating derive rate factors data structure ',5);
146 hr_utility.set_location('Values in Comp obj tree ',5);
147 hr_utility.set_location('Pgm Id '||l_comp_obj_tree_row.pgm_id,5);
148 hr_utility.set_location('p_oipl_id '|| l_comp_obj_tree_row.oipl_id,5);
149 --hr_utility.set_location('p_opt_id '||l_comp_obj_tree_row.oipl_id,5);
150 hr_utility.set_location('p_plip_id '||l_comp_obj_tree_row.plip_id,5);
151 hr_utility.set_location('p_ptip_id'|| l_comp_obj_tree_row.ptip_id,5);
152 --hr_utility.set_location('p_pl_type_id '||p_pl_type_id,5);
153 hr_utility.set_location('p_par_pgm_id '||l_comp_obj_tree_row.par_pgm_id ,5);
154 hr_utility.set_location('p_par_pl_id '||l_comp_obj_tree_row.par_pl_id,5);
155 hr_utility.set_location('p_par_plip_id '||l_comp_obj_tree_row.par_plip_id ,5);
156 hr_utility.set_location('p_par_opt_id '|| l_comp_obj_tree_row.par_opt_id,5);
157
158
159 ben_derive_part_and_rate_facts.cache_data_structures(
160 p_comp_obj_tree_row => l_comp_obj_tree_row
161 ,p_empasg_row => l_empasg_row
162 ,p_benasg_row => l_benasg_row
163 ,p_pil_row => l_pil_row
164 ,p_business_group_id => p_business_group_id
165 ,p_effective_date => p_effective_date
166 ,p_person_id => p_person_id
167 ,p_pgm_id => p_pgm_id
168 ,p_pl_id => p_pl_id
169 ,p_plip_id => p_plip_id
170 ,p_ptip_id => p_ptip_id
171 ,p_oipl_id => p_oipl_id
172 ,p_comp_rec => l_comp_rec
173 ,p_oiplip_rec => l_oiplip_rec);
174
175 hr_utility.set_location('Checking Eligibiltiy ',5);
176
177 l_elig_return_status := ben_evaluate_elig_profiles.eligible
178 (
179 p_person_id => p_person_id
180 ,p_business_group_id => p_business_group_id
181 ,p_effective_date => p_effective_date
182 ,p_dpr_rec => l_comp_rec
183 ,p_pgm_id => p_pgm_id
184 ,p_ptip_id => p_ptip_id
185 ,p_plip_id => p_plip_id
186 ,p_pl_id => p_pl_id
187 ,p_oipl_id => p_oipl_id
188 ,p_pl_typ_id => p_pl_type_id
189 ,p_opt_id => p_opt_id
190 ,p_par_pgm_id => p_par_pgm_id
191 ,p_par_plip_id => p_par_plip_id
192 ,p_par_pl_id => p_par_pl_id
193 ,p_par_opt_id => p_par_opt_id
194 ,p_comp_obj_mode => false
195 ,p_lf_evt_ocrd_dt => p_effective_date
196 ,p_score_tab => l_score_tab
197 ) ;
198
199 if l_elig_return_status then
200
201 p_return_status := 'Y';
202 p_score_tab := l_score_tab;
203 else
204 p_return_status := 'N';
205 end if;
206 hr_utility.set_location('Leaving from '||l_proc,5);
207 end check_eligibility;
208
209 procedure Create_Elictable_chc(
210 p_person_id in number,
211 p_per_in_ler_id in number,
212 p_business_group_id in number,
213 p_effective_date in date,
214 p_pgm_id in number default null,
215 p_pl_id in number default null,
216 p_oipl_id in number default null,
217 p_pl_type_id in number default null,
218 p_plip_id in number default null,
219 P_COMP_LVL_CD in varchar2,
220 P_Elig_Per_Elctbl_Chc_Id out nocopy number,
221 p_return_code out nocopy varchar2
222 ) is
223 l_proc varchar2(60) := g_package||'Create_Elictable_chc';
224
225
226 l_pgm_row ben_cobj_cache.g_pgm_inst_row;
227
228 Cursor pgm_info is
229 select * from ben_pgm_f pgm
230 where pgm_id = p_pgm_id
231 and p_effective_date between pgm.effective_start_date and pgm.effective_end_date;
232
233 pgm_info_rec pgm_info%rowtype;
234
235 l_enrt_cvg_strt_dt date;
236 l_enrt_cvg_strt_dt_cd varchar2(10);
237 l_enrt_cvg_strt_dt_rl number;
238 l_rt_strt_dt date;
239 l_rt_strt_dt_cd varchar2(10);
240 l_rt_strt_dt_rl number;
241 l_enrt_cvg_end_dt date;
242 l_enrt_cvg_end_dt_cd varchar2(10);
243 l_enrt_cvg_end_dt_rl number;
244 l_rt_end_dt date;
245 l_rt_end_dt_cd varchar2(10);
246 l_rt_end_dt_rl number;
247 L_Elig_Per_Elctbl_Chc_Id number;
248 l_LEE_RSN_ID number;
249 l_posting_style varchar2(1);
250 l_yr_perd_id number;
251 L_OIPL_ELIG_PER_ELCTBL_CHC_ID number;
252 l_Elctbl_Ovn number;
253 l_Oipl_Elctbl_Ovn number;
254 l_request_id number;
255
256
257 Begin
258 hr_utility.set_location('Entering into '||l_proc,5);
259
260 hr_utility.set_location('Creating pgm Row '||l_proc,5);
261 open pgm_info;
262 fetch pgm_info into pgm_info_rec;
263
264 l_pgm_row.pgm_id := pgm_info_rec.pgm_id;
265 l_pgm_row.effective_start_date := pgm_info_rec.effective_start_date;
266 l_pgm_row.effective_end_date := pgm_info_rec.effective_end_date;
267 l_pgm_row.enrt_cvg_strt_dt_cd := pgm_info_rec.enrt_cvg_strt_dt_cd;
268 l_pgm_row.enrt_cvg_strt_dt_rl := pgm_info_rec.enrt_cvg_strt_dt_rl;
269 l_pgm_row.enrt_cvg_end_dt_cd := pgm_info_rec.enrt_cvg_end_dt_cd;
270 l_pgm_row.enrt_cvg_end_dt_rl := pgm_info_rec.enrt_cvg_end_dt_rl;
271 l_pgm_row.rt_strt_dt_cd := pgm_info_rec.rt_strt_dt_cd;
272 l_pgm_row.rt_strt_dt_rl := pgm_info_rec.rt_strt_dt_rl;
273 l_pgm_row.rt_end_dt_cd := pgm_info_rec.rt_end_dt_cd;
274 l_pgm_row.rt_end_dt_rl := pgm_info_rec.rt_end_dt_rl;
275 l_pgm_row.elig_apls_flag := pgm_info_rec.elig_apls_flag;
276 l_pgm_row.prtn_elig_ovrid_alwd_flag := pgm_info_rec.prtn_elig_ovrid_alwd_flag;
277 l_pgm_row.trk_inelig_per_flag := pgm_info_rec.trk_inelig_per_flag;
278 l_pgm_row.vrfy_fmly_mmbr_cd := pgm_info_rec.vrfy_fmly_mmbr_cd;
279 l_pgm_row.vrfy_fmly_mmbr_rl := pgm_info_rec.vrfy_fmly_mmbr_rl;
280 l_pgm_row.dpnt_dsgn_lvl_cd := pgm_info_rec.dpnt_dsgn_lvl_cd;
281 l_pgm_row.dpnt_dsgn_cd := pgm_info_rec.dpnt_dsgn_cd;
282 l_pgm_row.dpnt_cvg_strt_dt_cd := pgm_info_rec.dpnt_cvg_strt_dt_cd;
283 l_pgm_row.dpnt_cvg_strt_dt_rl := pgm_info_rec.dpnt_cvg_strt_dt_rl;
284 l_pgm_row.dpnt_cvg_end_dt_cd := pgm_info_rec.dpnt_cvg_end_dt_cd;
285 l_pgm_row.dpnt_cvg_end_dt_rl := pgm_info_rec.dpnt_cvg_end_dt_rl;
286 l_pgm_row.pgm_typ_cd := pgm_info_rec.pgm_typ_cd;
287
288 close pgm_info;
289
290
291 hr_utility.set_location('Geting P_Enrt_Cvg_Strt_Dt ',5);
292
293 select blr.LEE_RSN_ID,pil.request_id
294 into l_LEE_RSN_ID, l_request_id
295 from ben_lee_rsn_f blr,
296 ben_popl_enrt_typ_cycl_f popl,
297 ben_per_in_ler pil
298 where blr.business_group_id = p_business_group_id
299 and blr.popl_enrt_typ_cycl_id = popl.POPL_ENRT_TYP_CYCL_ID
300 and blr.ler_id = pil.ler_id
301 and pil.per_in_ler_id = p_per_in_ler_id
302 and popl.business_group_id = p_business_group_id
303 and popl.pgm_id = p_pgm_id
304 and p_effective_date between popl.effective_start_date and popl.effective_end_date
305 and p_effective_date between blr.effective_start_date and blr.effective_end_date;
306
307 ben_determine_date.rate_and_coverage_dates
308 ( p_pgm_row => l_pgm_row
309 ,p_per_in_ler_id => p_per_in_ler_id
310 ,p_person_id => p_person_id
311 ,p_pgm_id => p_pgm_id
312 ,p_lee_rsn_id => l_LEE_RSN_ID /* */
313 ,p_which_dates_cd => 'C'
314 ,p_business_group_id => p_business_group_id
315 ,p_effective_date => p_effective_date
316 ,p_lf_evt_ocrd_dt => p_effective_date
317 --
318 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt
319 ,p_enrt_cvg_strt_dt_cd => l_enrt_cvg_strt_dt_cd
320 ,p_enrt_cvg_strt_dt_rl => l_enrt_cvg_strt_dt_rl
321 ,p_rt_strt_dt => l_rt_strt_dt
322 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd
323 ,p_rt_strt_dt_rl => l_rt_strt_dt_rl
324 ,p_enrt_cvg_end_dt => l_enrt_cvg_end_dt
325 ,p_enrt_cvg_end_dt_cd => l_enrt_cvg_end_dt_cd
326 ,p_enrt_cvg_end_dt_rl => l_enrt_cvg_end_dt_rl
327 ,p_rt_end_dt => l_rt_end_dt
328 ,p_rt_end_dt_cd => l_rt_end_dt_cd
329 ,p_rt_end_dt_rl => l_rt_end_dt_rl
330 );
331
332 hr_utility.set_location('Creating electable choice ',5);
333
334 select enrt_mthd_cd
335 into l_posting_style
336 from ben_pgm_f
337 where pgm_id = p_pgm_id
338 and p_effective_date between effective_start_date and effective_end_date;
339
340
341
342 if l_posting_style = 'A' then
343 l_posting_style := 'Y';
344 p_return_code := l_posting_style;
345 else
346 l_posting_style := 'N';
347 p_return_code := 'D';
348 end if;
349
350
351 Select Yr_Perd_id
352 into l_yr_perd_id
353 From Ben_Yr_Perd
354 Where P_Effective_Date Between Start_Date and End_Date
355 and rownum = 1;
356
357 -- and business_group_id = p_business_group_id;
358
359 if P_COMP_LVL_CD = 'PLAN' then
360
361 Ben_Elig_Per_Elc_Chc_Api.CREATE_PERF_ELIG_PER_ELC_CHC
362 (P_ELIG_PER_ELCTBL_CHC_ID => L_Elig_Per_Elctbl_Chc_Id /* OUT Parameter */
363 ,P_ENRT_CVG_STRT_DT_CD => l_enrt_cvg_strt_dt_cd
364 ,P_DFLT_FLAG => 'Y' /* Mark it as 'Y' if default */
365 ,P_ELCTBL_FLAG => 'Y' /* */
366 ,P_PL_ID => p_pl_id /* Plan Id corresponding to Grade */
367 ,P_PGM_ID => p_pgm_id /* Pgm Id of the Grade Ladder*/
368 ,P_PLIP_ID => p_plip_id /* Plip Id of the Grade */
369 ,P_PGM_TYP_CD => 'GSP' /* pass GSP */
370 ,P_PL_TYP_ID => p_pl_type_id /* Pass Plan Type Id of type GSP */
371 ,P_PER_IN_LER_ID => p_per_in_ler_id /* Person Life Event Reason Id */
372 ,P_YR_PERD_ID => l_yr_perd_id /* Pass the result of the query */
373 ,P_Enrt_Cvg_Strt_Dt => l_enrt_cvg_strt_dt
374 ,P_COMP_LVL_CD => 'PLAN'
375 ,P_LEE_RSN_ID => l_LEE_RSN_ID /* select lee_rsn_f
376 where pgm_id= <pgm_id> */
377 ,P_AUTO_ENRT_FLAG => l_posting_style /* 'Y' if Automatic Progression
378 'N' if Manual Progression */
379 ,P_BUSINESS_GROUP_ID => p_business_group_id /* Business Group Id */
380 ,P_ELIG_FLAG => 'Y' /* Y */
381 ,P_OBJECT_VERSION_NUMBER => l_Elctbl_Ovn /* out parameter */
382 ,P_EFFECTIVE_DATE => p_effective_date
383 ,p_request_id => l_request_id);
384
385 P_Elig_Per_Elctbl_Chc_Id := L_Elig_Per_Elctbl_Chc_Id;
386 elsif P_COMP_LVL_CD = 'OIPL' then
387
388 Ben_Elig_Per_Elc_Chc_Api.CREATE_PERF_ELIG_PER_ELC_CHC
389 (P_ELIG_PER_ELCTBL_CHC_ID => L_Elig_Per_Elctbl_Chc_Id /* OUT Parameter */
390 ,P_ENRT_CVG_STRT_DT_CD => l_enrt_cvg_strt_dt_cd
391 ,P_DFLT_FLAG => 'N' /* Mark it as 'Y' if default */
392 ,P_ELCTBL_FLAG => 'N' /* */
393 ,P_PL_ID => p_pl_id /* Plan Id corresponding to Grade */
394 ,P_PGM_ID => p_pgm_id /* Pgm Id of the Grade Ladder*/
395 ,P_PLIP_ID => p_plip_id /* Plip Id of the Grade */
396 ,P_PGM_TYP_CD => 'GSP' /* pass GSP */
397 ,P_PL_TYP_ID => p_pl_type_id /* Pass Plan Type Id of type GSP */
398 ,P_PER_IN_LER_ID => p_per_in_ler_id /* Person Life Event Reason Id */
399 ,P_YR_PERD_ID => l_yr_perd_id /* Pass the result of the query */
400 ,P_Enrt_Cvg_Strt_Dt => l_enrt_cvg_strt_dt
401 ,P_COMP_LVL_CD => 'PLAN'
402 ,P_LEE_RSN_ID => l_LEE_RSN_ID /* select lee_rsn_f
403 where pgm_id= <pgm_id> */
404 ,P_AUTO_ENRT_FLAG => l_posting_style /* 'Y' if Automatic Progression
405 'N' if Manual Progression */
406 ,P_BUSINESS_GROUP_ID => p_business_group_id /* Business Group Id */
407 ,P_ELIG_FLAG => 'N' /* Y */
408 ,P_OBJECT_VERSION_NUMBER => l_Elctbl_Ovn /* out parameter */
409 ,P_EFFECTIVE_DATE => p_effective_date
410 ,p_request_id => l_request_id);
411
412 Ben_Elig_Per_Elc_Chc_Api.CREATE_PERF_ELIG_PER_ELC_CHC
413 (
414 P_ELIG_PER_ELCTBL_CHC_ID => L_Oipl_Elig_Per_Elctbl_Chc_Id
415 ,P_ENRT_CVG_STRT_DT_CD => l_enrt_cvg_strt_dt_cd
416 ,P_DFLT_FLAG => 'Y'
417 ,P_ELCTBL_FLAG => 'Y'
418 ,P_PL_ID => p_pl_id /* Plan Id corresponding to Grade */
419 ,P_PGM_ID => p_pgm_id /* Pgm Id of the Grade Ladder*/
420 ,P_PLIP_ID => p_plip_id /* Plip Id of the Grade */
421 ,P_OIPL_ID => p_oipl_id
422 ,P_PGM_TYP_CD => 'GSP'
423 ,P_PL_TYP_ID => p_pl_type_id
424 ,P_Enrt_Cvg_Strt_Dt => l_enrt_cvg_strt_dt
425 ,P_YR_PERD_ID => l_yr_perd_id
426 ,P_PER_IN_LER_ID => p_per_in_ler_id
427 ,P_COMP_LVL_CD => 'OIPL'
428 ,P_LEE_RSN_ID => l_LEE_RSN_ID
429 ,P_AUTO_ENRT_FLAG => l_posting_style
430 ,P_BUSINESS_GROUP_ID => p_business_group_id
431 ,P_ELIG_FLAG => 'Y'
432 ,P_OBJECT_VERSION_NUMBER => l_Oipl_Elctbl_Ovn
433 ,P_EFFECTIVE_DATE => p_effective_date
434 ,p_request_id => l_request_id);
435
436 P_Elig_Per_Elctbl_Chc_Id := L_Oipl_Elig_Per_Elctbl_Chc_Id;
437
438 end if;
439 hr_utility.set_location('Leaving from '||l_proc,5);
440
441
442 end Create_Elictable_chc;
443
444 /* procedure Create_Enrolment_rates(p_assignment_id in number,
445 p_effective_date in date,
446 p_electble_chc_id in number,
447 p_business_group_id in number,
448 p_pl_id in number default null,
449 p_opt_id in number default null,
450 p_comb_lvl_cd in varchar2
451 ) is
452
453 l_proc varchar2(60) := g_package||'Create_Enrolment_rates';
454 l_Cur_Sal Ben_Enrt_Rt.Val%TYPE;
455 l_Rt_Typ_Cd Ben_Enrt_Rt.Rt_Typ_Cd%TYPE;
456 L_Acty_Base_rt_Id Ben_Acty_Base_Rt.Acty_Base_rt_Id%TYPE;
457 l_Enrt_Rt_Ovn Ben_Elig_Per_Elctbl_Chc.Object_version_Number%TYPE;
458 l_Entr_Ann_Val_Flag Ben_Acty_Base_rt_F.Entr_Ann_Val_Flag%TYPE;
459 L_Enrt_Rt_Id Ben_Enrt_Rt.Enrt_Rt_Id%TYPE;
460
461 Cursor Pl_Bas_rt(l_Pl_Id IN Number) Is
462 Select ACTY_BASE_RT_ID, Rt_Typ_cd, Entr_Ann_Val_Flag
463 From Ben_Acty_base_Rt_f
464 where Pl_id = l_Pl_Id
465 and P_effective_Date
466 Between Effective_Start_Date
467 and Effective_End_Date;
468
469 Cursor Opt_Bas_Rt(l_Opt_Id IN Number) Is
470 Select ACTY_BASE_RT_ID, Rt_Typ_cd, Entr_Ann_Val_Flag
471 From Ben_Acty_Base_rt_f
472 where Opt_Id = L_Opt_id
473 and P_effective_Date
474 Between Effective_Start_Date
475 and Effective_End_Date;
476
477 begin
478 hr_utility.set_location('Entering into '||l_proc,5);
479
480 if p_comb_lvl_cd = 'PLAN' then
481
482 Open Pl_Bas_rt(p_Pl_Id);
483 Fetch Pl_Bas_rt into l_ACTY_BASE_RT_ID, l_Rt_Typ_cd, l_Entr_Ann_Val_Flag;
484 Close Pl_Bas_rt;
485
486 elsif p_comb_lvl_cd = 'OIPL' then
487
488 Open Opt_Bas_rt(p_Opt_Id);
489 Fetch Opt_Bas_Rt into l_ACTY_BASE_RT_ID, l_Rt_Typ_cd, l_Entr_Ann_Val_Flag;
490 Close Opt_Bas_Rt;
491
492 end if;
493
494 l_Cur_Sal := Pqh_gsp_utility.Get_Cur_Sal
495 (P_Assignment_id => P_Assignment_id
496 ,P_Effective_Date => P_Effective_date);
497
498 ben_Enrollment_Rate_api.CREATE_PERF_ENROLLMENT_RATE
499 (P_ENRT_RT_ID => L_Enrt_Rt_Id
500 ,P_ACTY_TYP_CD => 'GSPSA'
501 ,P_TX_TYP_CD => 'NOTAPPLICABLE'
502 ,P_DFLT_FLAG => 'Y'
503 ,P_VAL => l_Cur_Sal
504 ,P_RT_TYP_CD => l_Rt_Typ_Cd
505 ,P_ELIG_PER_ELCTBL_CHC_ID => p_electble_chc_id
506 ,P_Entr_Ann_Val_Flag => l_Entr_Ann_Val_Flag
507 ,P_Business_Group_Id => p_business_group_id
508 ,P_ACTY_BASE_RT_ID => L_Acty_Base_rt_Id
509 ,P_OBJECT_VERSION_NUMBER => l_Enrt_Rt_Ovn
510 ,P_Effective_Date => P_Effective_Date);
511
512 hr_utility.set_location('Leaving from '||l_proc,5);
513
514 end Create_Enrolment_rates; */
515
516 procedure Create_Enrolment_rates(p_effective_date in date,
517 p_electble_chc_id in number,
518 p_business_group_id in number,
519 p_per_in_ler_id in number ,
520 p_person_id in number
521 ) is
522 l_proc varchar2(60) := g_package||'Create_Enrolment_rates';
523 begin
524 hr_utility.set_location('Entering into '||l_proc,5);
525 ben_env_object.init(p_business_group_id => p_business_group_id,
526 p_effective_date => p_effective_date,
527 p_thread_id => 1,
528 p_chunk_size => 1,
529 p_threads => 1,
530 p_max_errors => 1,
531 p_benefit_action_id => null);
532
533 ben_env_object.setenv(P_LF_EVT_OCRD_DT => p_effective_date);
534
535 ben_env_object.g_global_env_rec.mode_cd := 'G';
536
537 Ben_determine_rates.Main
538 (P_EFFECTIVE_DATE => p_effective_date
539 ,P_LF_EVT_OCRD_DT => p_effective_date
540 ,P_PERSON_ID => p_person_id
541 ,P_PER_IN_LER_ID => p_per_in_ler_id
542 ,p_elig_per_elctbl_chc_id => p_electble_chc_id);
543
544 hr_utility.set_location('Leaving from '||l_proc,5);
545 end Create_Enrolment_rates;
546
547 procedure Create_elig_per_scre( p_business_group_id in number,
548 p_per_in_ler_id in number,
549 p_person_id in number,
550 p_effective_date in date,
551 p_pgm_id in number default null,
552 p_pl_id in number default null,
553 p_plip_id in number default null,
554 p_ptip_id in number default null,
555 p_opt_id in number default null,
556 p_score_tab in ben_evaluate_elig_profiles.scoreTab
557 ) is
558 l_number_of_rows number;
559 l_elig_per_id number;
560 l_effective_start_date date;
561 l_effective_end_date date;
562 l_elig_per_ovn_no number;
563 l_request_id number;
564 l_elig_per_opt_id number;
565 l_opt_effective_start_date date;
566 l_opt_effective_end_date date;
567 l_elig_per_opt_ovn_no number;
568 l_proc varchar2(60) := g_package||'Create_elig_per_scre';
569
570 begin
571 hr_utility.set_location('Entering into '||l_proc,5);
572 l_number_of_rows := p_score_tab.COUNT;
573 if l_number_of_rows > 0 then
574
575 select pil.request_id
576 into l_request_id
577 from ben_lee_rsn_f blr,
578 ben_popl_enrt_typ_cycl_f popl,
579 ben_per_in_ler pil
580 where blr.business_group_id = p_business_group_id
581 and blr.popl_enrt_typ_cycl_id = popl.POPL_ENRT_TYP_CYCL_ID
582 and blr.ler_id = pil.ler_id
583 and pil.per_in_ler_id = p_per_in_ler_id
584 and popl.business_group_id = p_business_group_id
585 and popl.pgm_id = p_pgm_id
586 and p_effective_date between popl.effective_start_date and popl.effective_end_date
587 and p_effective_date between blr.effective_start_date and blr.effective_end_date;
588
589 ben_Eligible_Person_api.create_perf_Eligible_Person
590 ( p_elig_per_id => l_elig_per_id
591 ,p_effective_start_date => l_effective_start_date
592 ,p_effective_end_date => l_effective_end_date
593 ,p_business_group_id => p_business_group_id
594 ,p_pl_id => p_pl_id
595 ,p_pgm_id => p_pgm_id
596 ,p_plip_id => p_plip_id
597 ,p_ptip_id => p_ptip_id
598 ,p_person_id => p_person_id
599 ,p_per_in_ler_id => p_per_in_ler_id
600 ,p_object_version_number => l_elig_per_ovn_no
601 ,p_effective_date => p_effective_date
602 ,p_prtn_ovridn_flag => 'N'
603 ,p_request_id => l_request_id
604 );
605 hr_utility.set_location('elig_per_id'||l_elig_per_id,5);
606 hr_utility.set_location('p_opt_id'||p_opt_id,5);
607
608 -- if p_opt_id is not null then
609 hr_utility.set_location('going to create elig_per_opt record'||p_opt_id,5);
610 ben_Elig_Person_option_api.create_perf_Elig_Person_option
611 ( p_elig_per_opt_id => l_elig_per_opt_id
612 ,p_elig_per_id => l_elig_per_id
613 ,p_effective_start_date => l_opt_effective_start_date
614 ,p_effective_end_date => l_opt_effective_end_date
615 ,p_prtn_ovridn_flag => 'N'
616 ,p_no_mx_prtn_ovrid_thru_flag => 'N'
617 ,p_elig_flag => 'Y'
618 ,p_opt_id => p_opt_id
619 ,p_per_in_ler_id => p_per_in_ler_id
620 ,p_business_group_id => p_business_group_id
621 ,p_request_id => l_request_id
622 ,p_object_version_number => l_elig_per_opt_ovn_no
623 ,p_effective_date => p_effective_date
624 );
625 -- end if;
626
627 hr_utility.set_location('l_elig_per_opt_id'||l_elig_per_opt_id,5);
628 end if;
629 FOR table_row IN 1 .. l_number_of_rows
630 LOOP
631 hr_utility.set_location('eligy_prfl_id'||p_score_tab(table_row).eligy_prfl_id,5);
632 hr_utility.set_location('crit_tab_short_name'||p_score_tab(table_row).crit_tab_short_name,5);
633 hr_utility.set_location('crit_tab_pk_id'||p_score_tab(table_row).crit_tab_pk_id,5);
634 hr_utility.set_location('computed_score'||p_score_tab(table_row).computed_score,5);
635 hr_utility.set_location('benefit_action_id'||p_score_tab(table_row).benefit_action_id,5);
636 BEN_ELIG_SCRE_WTG_API.load_score_weight( p_score_tab => p_score_tab
637 ,p_elig_per_id => l_elig_per_id
638 ,p_elig_per_opt_id => l_elig_per_opt_id
639 ,p_effective_date => p_effective_date );
640 END LOOP;
641 hr_utility.set_location('Leaving '||l_proc,5);
642 end Create_elig_per_scre ;
643
644 procedure check_career_paths(p_per_in_ler_id in number,
645 p_person_id in number,
646 p_business_group_id in number,
647 p_cur_corp_id in number,
648 p_cur_grade_id in number,
649 p_cur_step_id in number,
650 p_effective_date in date,
651 p_assignment_id in number,
652 P_Elig_Per_Elctbl_Chc_Id out nocopy number,
653 p_return_code out nocopy varchar2,
654 p_return_status out nocopy varchar2
655 ) is
656
657
658 l_los_return_status varchar2(1);
659 l_elig_return_status varchar2(1);
660 l_person_id number;
661 l_assignment_id number;
662 l_effective_date date;
663 l_proc varchar2(60) := g_package||'get_career_paths';
664 l_cur_corp_id number;
665 l_cur_grade_id number;
666 l_cur_step_id number;
667 l_score_tab ben_evaluate_elig_profiles.scoreTab;
668
669
670 cursor career_paths is
671 select ghn.Information9 to_corp_Id,
672 ghn.Information23 to_grade_id,
673 ghn.Information3 to_step_id,
674 pl.pl_id to_pl_id,
675 pgm.pgm_id to_pgm_id,
676 plip.plip_id to_plip_id,
677 pl.pl_typ_id to_pl_type_id,
678 ptip.ptip_id to_ptip_id,
679 ghn.Information10 length_of_service_type,
680 ghn.Information11 req_years,
681 ghn.Information12 req_Months,
682 ghn.Information13 req_days
683 from PER_GEN_HIERARCHY_NODES ghn,
684 ben_pl_f pl,
685 pqh_corps_definitions corp,
686 ben_pgm_f pgm,
687 ben_plip_f plip,
688 ben_ptip_f ptip
689 where ghn.Information4 = l_cur_corp_id --cur_corp_id
690 --and ghn.Information30 = l_cur_grade_id -- cur_pgm_id
691 and ghn.Entity_id = l_cur_step_id -- cur_step_id
692 and ghn.business_group_id = g_business_group_id
693 and ghn.node_type = 'CAREER_NODE'
694 and pl.mapping_table_name = 'PER_GRADES'
695 and pl.mapping_table_pk_id = ghn.Information23
696 and l_effective_date between pl.effective_start_date and pl.effective_end_date
697 and corp.corps_definition_id = ghn.Information9
698 and pgm.pgm_id = corp.ben_pgm_id
699 and l_effective_date between pgm.effective_start_date and pgm.effective_end_date
700 and plip.pl_id = pl.pl_id
701 and plip.pgm_id = pgm.pgm_id
702 and l_effective_date between plip.effective_start_date and plip.effective_end_date
703 and ptip.pgm_id = pgm.pgm_id
704 and ptip.pl_typ_id = pl.pl_typ_id
705 and l_effective_date between ptip.effective_start_date and ptip.effective_end_date
706 ;
707
708 career_path_rec career_paths%rowtype;
709
710 cursor step_info is
711 select
712 opt.opt_id to_opt_id,
713 oipl.oipl_id to_oipl_id
714 from
715 per_spinal_point_steps_f sps
716 , ben_opt_f opt
717 , ben_oipl_f oipl
718 where sps.step_id = career_path_rec.to_step_id
719 and opt.mapping_table_name = 'PER_SPINAL_POINTS'
720 and opt.mapping_table_pk_id = sps.spinal_point_id
721 and oipl.opt_id = opt.opt_id
722 and oipl.pl_id = career_path_rec.to_pl_id
723 and l_effective_date between sps.effective_start_date and sps.effective_end_date
724 and l_effective_date between opt.effective_start_date and opt.effective_end_date
725 and l_effective_date between oipl.effective_start_date and oipl.effective_end_date
726 ;
727
728 step_info_rec step_info%rowtype;
729
730 begin
731 hr_utility.set_location('Entering into '||l_proc,5);
732
733 l_person_id := p_person_id;
734 l_assignment_id := p_assignment_id;
735 l_effective_date := p_effective_date;
736 l_cur_corp_id := p_cur_corp_id;
737 l_cur_grade_id := p_cur_grade_id;
738 l_cur_step_id := p_cur_step_id;
739
740
741 hr_utility.set_location('l_person_id '||to_char(l_person_id),5);
742 hr_utility.set_location('l_assignment_id '||to_char(l_assignment_id),5);
743 hr_utility.set_location('l_effective_date '||to_char(l_effective_date),5);
744 hr_utility.set_location('l_cur_corp_id '||to_char(l_cur_corp_id),5);
745 hr_utility.set_location('l_cur_grade_id '||to_char(l_cur_grade_id),5);
746 hr_utility.set_location('l_cur_step_id '||to_char(l_cur_step_id),5);
747 open career_paths;
748 loop
749 fetch career_paths into career_path_rec;
750 exit when career_paths%notfound;
751
752 l_los_return_status := check_length_of_service (
753 p_person_id => l_person_id,
754 p_business_group_id => p_business_group_id,
755 p_assignment_id => l_assignment_id,
756 p_length_of_service_type => career_path_rec.length_of_service_type,
757 p_effective_date => l_effective_date,
758 p_req_years => career_path_rec.req_years,
759 p_req_Months => career_path_rec.req_Months,
760 p_req_days => career_path_rec.req_days);
761
762 if l_los_return_status = 'Y' then
763
764 hr_utility.set_location('Checking eligibility for Corp ',5);
765 check_eligibility (
766 p_per_in_ler_id => p_per_in_ler_id,
767 p_person_id => p_person_id,
768 p_effective_date => p_effective_date,
769 p_business_group_id => p_business_group_id,
770 p_pgm_id => career_path_rec.to_pgm_id,
771 p_par_pgm_id => career_path_rec.to_pgm_id,
772 p_return_status => l_elig_return_status,
773 p_score_tab => l_score_tab
774 );
775 if l_elig_return_status = 'Y' then
776 hr_utility.set_location('Eligibility defined at Corp is satisfied ',5);
777 Create_elig_per_scre( p_business_group_id => p_business_group_id,
778 p_per_in_ler_id => p_per_in_ler_id,
779 p_person_id => p_person_id,
780 p_effective_date => p_effective_date,
781 p_pgm_id => career_path_rec.to_pgm_id,
782 p_pl_id => career_path_rec.to_pl_id,
783 p_plip_id => career_path_rec.to_plip_id,
784 p_ptip_id => career_path_rec.to_ptip_id,
785 p_score_tab => l_score_tab
786 );
787 hr_utility.set_location('Checking eligibility defined at grade in corp (plip) ',5);
788 check_eligibility (
789 p_per_in_ler_id => p_per_in_ler_id,
790 p_person_id => p_person_id,
791 p_effective_date => p_effective_date,
792 p_business_group_id => p_business_group_id,
793 p_plip_id => career_path_rec.to_plip_id,
794 p_par_pgm_id => career_path_rec.to_pgm_id,
795 p_par_pl_id => career_path_rec.to_pl_id,
796 p_par_plip_id => career_path_rec.to_plip_id,
797 p_return_status => l_elig_return_status,
798 p_score_tab => l_score_tab
799 );
800 if l_elig_return_status = 'Y' then
801 hr_utility.set_location('Eligibility defined at Grade in Corp is satisfied ',5);
802 Create_elig_per_scre( p_business_group_id => p_business_group_id,
803 p_per_in_ler_id => p_per_in_ler_id,
804 p_person_id => p_person_id,
805 p_effective_date => p_effective_date,
806 p_pgm_id => career_path_rec.to_pgm_id,
807 p_pl_id => career_path_rec.to_pl_id,
808 p_plip_id => career_path_rec.to_plip_id,
809 p_ptip_id => career_path_rec.to_ptip_id,
810 p_score_tab => l_score_tab
811 );
812 hr_utility.set_location('Checking eligibility defined at grade ',5);
813 check_eligibility (
814 p_per_in_ler_id => p_per_in_ler_id,
815 p_person_id => p_person_id,
816 p_effective_date => p_effective_date,
817 p_business_group_id => p_business_group_id,
818 p_pl_id => career_path_rec.to_pl_id,
819 p_pl_type_id => career_path_rec.to_pl_type_id,
820 p_par_pl_id => career_path_rec.to_pl_id,
821 p_return_status => l_elig_return_status,
822 p_score_tab => l_score_tab
823 );
824 if l_elig_return_status = 'Y' then
825 hr_utility.set_location('Eligibility defined at Grade is satisfied ',5);
826 Create_elig_per_scre( p_business_group_id => p_business_group_id,
827 p_per_in_ler_id => p_per_in_ler_id,
828 p_person_id => p_person_id,
829 p_effective_date => p_effective_date,
830 p_pgm_id => career_path_rec.to_pgm_id,
831 p_pl_id => career_path_rec.to_pl_id,
832 p_plip_id => career_path_rec.to_plip_id,
833 p_ptip_id => career_path_rec.to_ptip_id,
834 p_score_tab => l_score_tab
835 );
836 if career_path_rec.to_step_id is not null then
837 open step_info;
838 fetch step_info into step_info_rec;
839 hr_utility.set_location('Checking eligibility defined at step ',5);
840 check_eligibility(
841 p_per_in_ler_id => p_per_in_ler_id,
842 p_person_id => p_person_id,
843 p_effective_date => p_effective_date,
844 p_business_group_id => p_business_group_id,
845 p_oipl_id => step_info_rec.to_oipl_id,
846 p_par_opt_id => step_info_rec.to_opt_id,
847 p_pl_type_id => career_path_rec.to_pl_type_id,
848 p_par_pl_id => career_path_rec.to_pl_id,
849 p_return_status => l_elig_return_status,
850 p_score_tab => l_score_tab
851 );
852 close step_info;
853 if l_elig_return_status = 'Y' then
854 Create_elig_per_scre( p_business_group_id => p_business_group_id,
855 p_per_in_ler_id => p_per_in_ler_id,
856 p_person_id => p_person_id,
857 p_effective_date => p_effective_date,
858 p_pgm_id => career_path_rec.to_pgm_id,
859 p_pl_id => career_path_rec.to_pl_id,
860 p_plip_id => career_path_rec.to_plip_id,
861 p_ptip_id => career_path_rec.to_ptip_id,
862 p_opt_id => step_info_rec.to_opt_id,
863 p_score_tab => l_score_tab
864 );
865 Create_Elictable_chc(
866 p_person_id => p_person_id,
867 p_per_in_ler_id => p_per_in_ler_id,
868 p_business_group_id => p_business_group_id,
869 p_effective_date => p_effective_date,
870 p_pgm_id => career_path_rec.to_pgm_id,
871 p_pl_id => career_path_rec.to_pl_id,
872 p_oipl_id => step_info_rec.to_oipl_id,
873 p_pl_type_id => career_path_rec.to_pl_type_id,
874 p_plip_id => career_path_rec.to_plip_id,
875 P_COMP_LVL_CD => 'OIPL',
876 P_Elig_Per_Elctbl_Chc_Id => P_Elig_Per_Elctbl_Chc_Id,
877 p_return_code => p_return_code
878 );
879
880
881 exit;
882 else
883 hr_utility.set_location('Eligibility defined at step is NOT satisfied ',5);
884 l_elig_return_status := 'N';
885 end if;
886 else
887 Create_Elictable_chc(
888 p_person_id => p_person_id,
889 p_per_in_ler_id => p_per_in_ler_id,
890 p_business_group_id => p_business_group_id,
891 p_effective_date => p_effective_date,
892 p_pgm_id => career_path_rec.to_pgm_id,
893 p_pl_id => career_path_rec.to_pl_id,
894 p_pl_type_id => career_path_rec.to_pl_type_id,
895 p_plip_id => career_path_rec.to_plip_id,
896 P_COMP_LVL_CD => 'PLAN',
897 P_Elig_Per_Elctbl_Chc_Id => P_Elig_Per_Elctbl_Chc_Id,
898 p_return_code => p_return_code
899 );
900
901 exit;
902 end if;
903 else
904 hr_utility.set_location('Eligibility defined at grade is NOT satisfied ',5);
905 l_elig_return_status := 'N';
906 end if;
907 else
908 hr_utility.set_location('Eligibility defined at PLIP is NOT satisfied ',5);
909 l_elig_return_status := 'N';
910 end if;
911 else
912 hr_utility.set_location('Eligibility defined at Corp is NOT satisfied ',5);
913 l_elig_return_status := 'N';
914 end if;
915 else
916 l_elig_return_status := 'N';
917 end if;
918
919 end loop;
920
921 if l_elig_return_status <> 'N' then
922
923 hr_utility.set_location('Creating Enrolment rates for the new electable choice ',5);
924
925 Create_Enrolment_rates(p_effective_date => p_effective_date,
926 p_electble_chc_id => P_Elig_Per_Elctbl_Chc_Id,
927 p_business_group_id => p_business_group_id,
928 p_per_in_ler_id => p_per_in_ler_id ,
929 p_person_id => p_person_id
930 );
931
932 end if;
933
934 p_return_status := l_elig_return_status;
935 hr_utility.set_location('Leaving from '||l_proc,5);
936
937 end check_career_paths ;
938
939 Procedure get_elctbl_chc_career_path (p_per_in_ler_id in number,
940 p_effective_date in date,
941 P_Elig_Per_Elctbl_Chc_Id out nocopy number,
942 p_return_code out nocopy varchar2,
943 p_return_status out nocopy varchar2)
944 is
945
946 cursor per_in_ler_info is
947 Select *
948 from ben_per_in_ler
949 where per_in_ler_id = p_per_in_ler_id;
950
951 l_pil_info_rec per_in_ler_info%rowtype;
952
953 cursor person_cur_asg_info is
954 select corp.corps_definition_id cur_corp_id,
955 asg.grade_id cur_grade_id,
956 asg.assignment_id assignment_id,
957 spp.step_id cur_step_id,
958 asg.grade_ladder_pgm_id cur_pgm_id,
959 pl.pl_id cur_plan_id,
960 oipl.oipl_id cur_oipl_id
961 from per_all_assignments_f asg,
962 per_spinal_point_placements_f spp,
963 per_spinal_point_steps_f sps,
964 ben_opt_f opt,
965 ben_oipl_f oipl,
966 ben_pl_f pl,
967 pqh_corps_definitions corp
968 where asg.person_id = l_pil_info_rec.person_id
969 and asg.primary_flag = 'Y'
970 and asg.business_group_id = l_pil_info_rec.business_group_id
971 and asg.assignment_status_type_id = 1
972 and p_effective_date between asg.effective_start_date and asg.effective_end_date
973 and spp.assignment_id = asg.assignment_id
974 and p_effective_date between spp.effective_start_date and spp.effective_end_date
975 and sps.step_id = spp.step_id
976 and p_effective_date between sps.effective_start_date and sps.effective_end_date
977 and opt.mapping_table_name = 'PER_SPINAL_POINTS'
978 and opt.mapping_table_pk_id = sps.spinal_point_id
979 and p_effective_date between opt.effective_start_date and opt.effective_end_date
980 and pl.mapping_table_name = 'PER_GRADES'
981 and pl.mapping_table_pk_id = asg.grade_id
982 and p_effective_date between pl.effective_start_date and pl.effective_end_date
983 and oipl.pl_id = pl.pl_id
984 and oipl.opt_id = opt.opt_id
985 and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
986 and corp.ben_pgm_id = asg.grade_ladder_pgm_id;
987
988
989 l_person_info_rec person_cur_asg_info%rowtype;
990
991 l_proc varchar2(60) := g_package||'get_elctbl_chc_career_path';
992 l_person_id number;
993 l_lf_evt_ocrd_dt date;
994 l_return_status varchar2(1);
995
996
997 begin
998 -- hr_utility.trace_on(NULL,'SJSCR');
999
1000 hr_utility.set_location('Entering into '||l_proc,5);
1001
1002 open per_in_ler_info;
1003 fetch per_in_ler_info into l_pil_info_rec;
1004 close per_in_ler_info;
1005
1006 g_business_group_id := l_pil_info_rec.business_group_id;
1007
1008 open person_cur_asg_info;
1009 loop
1010 fetch person_cur_asg_info into l_person_info_rec;
1011 exit when person_cur_asg_info%notfound;
1012
1013 hr_utility.set_location('In the person_cur_asg_info cur asg info loop '||l_proc,5);
1014
1015 check_career_paths(p_per_in_ler_id => p_per_in_ler_id,
1016 p_person_id => l_pil_info_rec.person_id,
1017 p_business_group_id => l_pil_info_rec.business_group_id,
1018 p_cur_corp_id => l_person_info_rec.cur_corp_id,
1019 p_cur_grade_id => l_person_info_rec.cur_grade_id,
1020 p_cur_step_id => l_person_info_rec.cur_step_id,
1021 p_assignment_id => l_person_info_rec.assignment_id,
1022 p_effective_date => p_effective_date,
1023 P_Elig_Per_Elctbl_Chc_Id => P_Elig_Per_Elctbl_Chc_Id,
1024 p_return_code => p_return_code,
1025 p_return_status => p_return_status
1026 );
1027
1028 end loop;
1029
1030 g_business_group_id := null;
1031
1032 hr_utility.set_location('Leaving from '||l_proc,5);
1033
1034
1035 end get_elctbl_chc_career_path;
1036
1037 END PQH_FR_CR_PATH_ENGINE_PKG;