1 package body ota_elig_profile_util as
2 /* $Header: oteligprfl.pkb 120.4.12020000.5 2013/02/08 04:17:48 jaysridh ship $ */
3
4 g_package varchar2(33) := 'ota_elig_profile_util'; -- Global package name
5
6 CURSOR csr_get_learner(
7 p_per_bg_grp_prof_val number,
8 p_ota_global_bg_prof_val number
9 ) IS
10 SELECT paf.person_id
11 , paf.assignment_id
12 , paf.assignment_type
13 , paf.business_group_id
14 FROM per_people_f ppf
15 ,per_assignments_f paf
16 ,per_person_type_usages_f ptu
17 ,per_person_types pts
18 ,per_business_groups pbg
19 WHERE
20 ppf.person_id = paf.person_id
21 AND ((pts.system_person_type IN ('EMP','CWK') AND paf.primary_flag = 'Y') OR (paf.assignment_type = 'A' AND pts.system_person_type ='APL'))
22 AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
23 AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date --added
24 AND trunc(sysdate) BETWEEN trunc(ptu.effective_start_date) AND trunc(ptu.effective_end_date) --added
25 AND pts.person_type_id = ptu.person_type_id
26 AND ptu.person_id = ppf.person_id
27 AND pts.system_person_type IN ('EMP', 'CWK', 'APL')
28 AND paf.assignment_type IN ('A','E','C')
29 AND (p_ota_global_bg_prof_val IS NOT NULL or pbg.business_group_id = p_per_bg_grp_prof_val)
30 -- AND pbg.business_group_id = p_per_bg_grp_prof_val
31 AND paf.business_group_id = pbg.business_group_id
32 AND
33 ((pts.system_person_type = 'APL'
34 AND NOT EXISTS (SELECT person_id
35 FROM per_person_type_usages_f ptf,
36 per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
37 AND ptf.person_type_id = ptp.person_type_id
38 AND ptp.system_person_type IN ('EMP', 'CWK')
39 AND ptf.person_id = ppf.PERSON_ID)
40 )
41 OR pts.system_person_type IN ('EMP', 'CWK'))
42 order by paf.person_id;
43
44 function check_processing_status(p_user_group_id in number,p_eligibility_profile_id in number default null )
45 return varchar2 is
46
47 cursor check_status_for_elig_prf is
48 select
49 nvl(processing_status,'A') processing_status_flag
50 from
51 ota_user_group_elements
52 where
53 user_group_id = p_user_group_id
54 --and trunc(sysdate) between start_date_active and nvl(end_date_active,trunc(sysdate+1))
55 --Adding above clause will not display members for end dated user groups
56 and elig_prfl_id = p_eligibility_profile_id
57 and person_id is null;
58
59 cursor check_status_for_ug is
60 select
61 processing_status
62 from
63 ota_user_group_elements
64 where
65 user_group_id = p_user_group_id
66 --and trunc(sysdate) between start_date_active and nvl(end_date_active,trunc(sysdate+1))
67 --Adding above clause will not display members for end dated user groups
68 and elig_prfl_id is not null
69 and person_id is null
70 and nvl(processing_status,'A') <> 'A';
71
72
73 l_processing_status_flag varchar2(1);
74
75 begin
76 if p_eligibility_profile_id is not null then
77 open check_status_for_elig_prf;
78 fetch check_status_for_elig_prf into l_processing_status_flag;
79
80 if check_status_for_elig_prf%NOTFOUND then
81 l_processing_status_flag:='E';--some error,so skip processing
82 end if;
83 close check_status_for_elig_prf;
84
85 else
86 open check_status_for_ug;
87 fetch check_status_for_ug into l_processing_status_flag;
88 if check_status_for_ug%NOTFOUND then
89 l_processing_status_flag:='A';
90 end if;
91 close check_status_for_ug;
92
93
94 end if;
95
96 return l_processing_status_flag;
97
98 end check_processing_status;
99
100 function get_learner_group_name(p_ug_id in number)
101 return varchar2 is
102
103 cursor get_learner_group_name is
104 select
105 user_group_name
106 from ota_user_groups_tl
107 where
108 user_group_id = p_ug_id
109 and language = userenv('LANG');
110
111 l_lrnr_group_name ota_user_groups_tl.user_group_name%type;
112
113 begin
114
115 open get_learner_group_name;
116 fetch get_learner_group_name into l_lrnr_group_name;
117 close get_learner_group_name;
118
119 return l_lrnr_group_name;
120
121 end get_learner_group_name;
122
123 function is_learner_in_resolved_ug_data(p_user_group_id in number,
124 p_person_id in number,
125 p_processing_status in varchar2 default null)
126 return varchar2 is
127
128 cursor check_resolved_data is
129 select person_id
130 from
131 ota_user_group_elements
132 where
133 user_group_id = p_user_group_id and person_id= p_person_id
134 and processing_status = nvl(p_processing_status,processing_status)
135 and elig_prfl_id is not null
136 and person_id is not null;
137
138 l_data_exists varchar2(1) := 'N';
139 l_person_id ota_user_group_elements.person_id%type;
140
141 begin
142 open check_resolved_data ;
143 fetch check_resolved_data into l_person_id;
144 if check_resolved_data %NOTFOUND then
145 close check_resolved_data;
146 else
147 l_data_exists := 'Y';
148 close check_resolved_data;
149 end if;
150
151 return l_data_exists;
152 end is_learner_in_resolved_ug_data;
153
154
155 function get_elig_object_id(p_user_group_id in number)
156 return number is
157
158 cursor get_elig_obj_id is
159 select elig_obj_id
160 from ben_elig_obj_f
161 where
162 TABLE_NAME = 'OTA_USER_GROUPS_B'
163 and COLUMN_NAME='USER_GROUP_ID'
164 and COLUMN_VALUE= p_user_group_id
165 and trunc(sysdate) between effective_start_date and effective_end_date;
166
167 l_elig_obj_id number :=null;
168 begin
169
170 open get_elig_obj_id;
171 fetch get_elig_obj_id into l_elig_obj_id;
172 close get_elig_obj_id;
173
174 return l_elig_obj_id;
175
176 end get_elig_object_id;
177
178
179 Procedure process_eligible_lrnr_record(p_person_id per_all_assignments_f.person_id%type,
180 p_user_group_id ota_user_group_elements.user_group_id%type,
181 p_eligibility_profile_id ota_user_group_elements.elig_prfl_id%type,
182 p_business_group_id ota_user_group_elements.business_group_id%type,
183 p_enable_logging in varchar2 default 'N') IS
184
185 l_object_version_number number :=1;
186 begin
187
188 if is_learner_in_resolved_ug_data(p_user_group_id, p_person_id)= 'Y' then
189 if p_enable_logging = 'Y' then
190 FND_FILE.PUT_LINE(FND_FILE.LOG,'Record exist for person_id : ' ||TO_CHAR(p_person_id) ||' user_group_id : ' ||TO_CHAR(p_user_group_id) ||' so update.Eligibility profile id is : '||TO_CHAR(p_eligibility_profile_id));
191 end if;
192
193 update ota_user_group_elements set processing_status= 'A' ,elig_prfl_id = p_eligibility_profile_id
194 where
195 user_group_id= p_user_group_id and
196 person_id = p_person_id and
197 elig_prfl_id is not null;
198
199
200
201 else
202 if p_enable_logging = 'Y' then
203 FND_FILE.PUT_LINE(FND_FILE.LOG,'Record does not exist for person_id : ' ||TO_CHAR(p_person_id) ||' user_group_id : ' ||TO_CHAR(p_user_group_id) ||' so create.Eligibility profile id is : '||TO_CHAR(p_eligibility_profile_id));
204 end if;
205
206 insert into ota_user_group_elements(
207 USER_GROUP_ELEMENT_ID,
208 USER_GROUP_ID,
209 BUSINESS_GROUP_ID,
210 PERSON_ID,
211 OBJECT_VERSION_NUMBER,
212 CREATION_DATE,
213 ELIG_PRFL_ID,
214 processing_status)
215 VALUES(
216 ota_user_group_elements_s.nextval,
217 p_user_group_id,
218 p_business_group_id,
219 p_person_id,
220 l_object_version_number,
221 sysdate,
222 p_eligibility_profile_id,
223 'A');
224
225 end if;
226 end process_eligible_lrnr_record;
227
228
229
230
231 procedure resolve_eligibility_profile(errbuf out nocopy varchar2,
232 retcode out nocopy varchar2,
233 p_learner_group_id in number,
234 p_elig_prof_id in number,
235 p_enable_logging in varchar2 default 'N') is
236
237 cursor get_elig_prof_details(p_elig_prof_id in number) is
238 select
239 eligy_prfl_id,
240 name,
241 asmt_to_use_cd,
242 business_group_id
243 from ben_eligy_prfl_f
244 where
245 eligy_prfl_id = p_elig_prof_id
246 and trunc(sysdate) between effective_start_date and effective_end_date
247 and stat_cd in ('A','P');
248
249
250
251 l_elig_prof_id ben_eligy_prfl_f.eligy_prfl_id%type;
252 l_elig_prof_name ben_eligy_prfl_f.name%type;
253 l_asmt_to_use_cd ben_eligy_prfl_f.asmt_to_use_cd%type;
254 l_elig_pro_bg_id ben_eligy_prfl_f.business_group_id%type;
255 l_sql_stmnt varchar2(4000);
256 l_allow_contingent_wrk varchar2(1) := 'Y';
257 l_learner_group_name ota_user_groups_tl.user_group_name%type;
258 l_numberof_records_processed number:=0;
259 assignment_whereclause varchar2(100);
260 l_elig_obj_id number;
261 l_counter number:=0;
262 l_learner_group_element_id ota_user_group_elements.user_group_element_id%type;
263
264 TYPE learner_rec IS RECORD(
265 person_id per_assignments_f.person_id%type,
266 assignment_id per_assignments_f.assignment_id%type,
267 assignment_type per_assignments_f.assignment_type%type,
268 business_group_id per_assignments_f.business_group_id%type
269 );
270
271 lrnr_rec learner_rec;
272
273 TYPE ug_rec IS RECORD(
274 user_group_id ota_user_group_elements.user_group_id%type,
275 elig_prfl_id ota_user_group_elements.elig_prfl_id%type,
276 user_group_element_id ota_user_group_elements.user_group_element_id%type,
277 business_group_id ota_user_group_elements.business_group_id%type
278 );
279
280 user_group_rec ug_rec;
281
282 TYPE learner_assign_details IS REF CURSOR;
283 csr_get_lrnr_in_assign learner_assign_details;
284 csr_get_ug_to_be_processed learner_assign_details;
285
286 ug_ep_whereclause varchar2(200);
287 get_ug_to_be_processed varchar2(4000);
288 l_eligprof_tab ben_evaluate_elig_profiles.proftab;
289 l_score_tab ben_evaluate_elig_profiles.scoreTab;
290 begin
291
292 if p_learner_group_id is not null and p_elig_prof_id is not null then
293 ug_ep_whereclause := 'user_group_id = ' ||TO_CHAR(p_learner_group_id)
294 || 'and elig_prfl_id = '||TO_CHAR(p_elig_prof_id);
295 elsif p_elig_prof_id is null and p_learner_group_id is not null then
296 ug_ep_whereclause := 'user_group_id = ' ||TO_CHAR(p_learner_group_id) ;
297 elsif p_elig_prof_id is not null and p_learner_group_id is null then
298 ug_ep_whereclause := 'user_group_id in
299 (select user_group_id from ota_user_group_elements
300 where elig_prfl_id ='|| TO_CHAR(p_elig_prof_id)|| ')';
301 else
302 ug_ep_whereclause := '1=1';
303
304 end if;
305
306 get_ug_to_be_processed := 'select * from
307 (select
308 ug.user_group_id,
309 uge.elig_prfl_id,
310 uge.user_group_element_id,
311 uge.business_group_id
312 from
313 ota_user_groups_b ug,
314 ota_user_group_elements uge
315 where
316 ug.user_group_id = uge.user_group_id
317 and trunc(sysdate) between trunc(ug.start_date_active) and nvl(ug.end_date_active,trunc(sysdate+1))
318 and uge.elig_prfl_id is not null
319 and uge.person_id is null
320 and nvl(uge.processing_status,''A'') <> ''P''
321 and ug.business_group_id = ota_general.get_business_group_id) QRSLT WHERE '|| ug_ep_whereclause ;
322
323
324 if p_enable_logging = 'Y' then
325 FND_FILE.PUT_LINE(FND_FILE.LOG,'OTA_HR_GLOBAL_BUSINESS_GROUP_ID :' ||fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID'));
326 FND_FILE.PUT_LINE(FND_FILE.LOG,'PER_BUSINESS_GROUP_ID :' ||fnd_profile.value('PER_BUSINESS_GROUP_ID'));
327 FND_FILE.PUT_LINE(FND_FILE.LOG,'get_ug_to_be_processed is:'||get_ug_to_be_processed);
328
329 end if;
330
331 OPEN csr_get_ug_to_be_processed for get_ug_to_be_processed;
332 LOOP
333 FETCH csr_get_ug_to_be_processed into user_group_rec;
334 EXIT WHEN csr_get_ug_to_be_processed %NOTFOUND;
335
336 l_eligprof_tab(1).eligy_prfl_id := null;
337 l_eligprof_tab(1).mndtry_flag := 'N';
338 l_eligprof_tab(1).compute_score_flag := 'Y';
339 l_eligprof_tab(1).trk_scr_for_inelg_flag := 'N';
340 l_counter :=l_counter+1;
341
342 l_learner_group_name := get_learner_group_name(user_group_rec.user_group_id);
343
344
345 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner group being processed : '||l_learner_group_name);
346
347 if check_processing_status(user_group_rec.user_group_id, user_group_rec.elig_prfl_id) <> 'P' then
348 --This is done to avoid the learner group if its been picked/processed by some other run of this conc program
349 --i.e the user group is under processing in the mean time.
350 l_learner_group_element_id := user_group_rec.user_group_element_id;
351 update ota_user_group_elements set processing_status = 'P' --Parent record to Processing
352 where user_group_element_id = user_group_rec.user_group_element_id;
353 update ota_user_group_elements set processing_status = 'H' --Child records to history
354 where user_group_id = user_group_rec.user_group_id and elig_prfl_id =user_group_rec.elig_prfl_id and person_id is not null;
355 commit;--commit early so that other re-runs of conc programs donot pick it up
356
357 open get_elig_prof_details(user_group_rec.elig_prfl_id);
358 fetch get_elig_prof_details into l_elig_prof_id,l_elig_prof_name,l_asmt_to_use_cd,l_elig_pro_bg_id;
359 if get_elig_prof_details%NOTFOUND then
360 close get_elig_prof_details;
361 FND_FILE.PUT_LINE(FND_FILE.LOG,'Eligibility profile details not found or it is in inactive/closed status');
362 --write messages in conc program log;
363 else
364 -- row exists, so resolve ppl based on column asmt_to_use_cd and then function is_eligible.
365 --lookup name 'BEN_ASMT_TO_USE';
366 close get_elig_prof_details;
367 l_eligprof_tab(1).eligy_prfl_id := l_elig_prof_id;
368
369 l_elig_obj_id := get_elig_object_id(user_group_rec.user_group_id);
370 FND_FILE.PUT_LINE(FND_FILE.LOG,'Eligibility profile being processed :'||l_elig_prof_name);
371
372 if l_asmt_to_use_cd = 'EAO' then --(Employee Assignment only)
373 assignment_whereclause := 'assignment_type = ''E'' ';
374 elsif l_asmt_to_use_cd = 'AAO' then --(Applicant Assignment only)
375 assignment_whereclause := 'assignment_type = ''A'' ' ;
376 elsif l_asmt_to_use_cd = 'CWAO' then --(Contingent Worker Assignment only)
377 assignment_whereclause := 'assignment_type = ''C'' ';
378 elsif l_asmt_to_use_cd = 'EAAO' then --(Employee and Applicant Assignments only)
379 assignment_whereclause := 'assignment_type in (''A'',''E'')' ;
380 elsif l_asmt_to_use_cd = 'ECWA' then --(Employee and Contingent Worker only)
381 assignment_whereclause := 'assignment_type in (''A'',''C'')' ;
382 elsif l_asmt_to_use_cd = 'ANY' then --(Any Assignment Employee, Applicant and Contingent Worker)
383 assignment_whereclause := 'assignment_type in (''A'',''E'',''C'')';
384 end if;
385
386 l_sql_stmnt := 'select
387 * from(
388 select
389 person_id,
390 assignment_id,
391 assignment_type,
392 business_group_id
393 from
394 per_assignments_f
395 where
396 trunc(sysdate) between effective_start_date and effective_end_date
397 and((assignment_type in (''E'',''C'') and PRIMARY_FLAG = ''Y'') OR (assignment_type = ''A''))
398 and ((fnd_profile.value(''OTA_HR_GLOBAL_BUSINESS_GROUP_ID'') is not null and business_group_id = '||l_elig_pro_bg_id||') or (business_group_id = fnd_profile.value(''PER_BUSINESS_GROUP_ID''))))
399 QRSLT WHERE ' || assignment_whereclause;
400
401 if p_enable_logging = 'Y' then
402 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_sql_stmnt is:'||l_sql_stmnt);
403 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_elig_obj_id is:'|| TO_CHAR(l_elig_obj_id));
404 end if;
405
406 OPEN csr_get_lrnr_in_assign FOR l_sql_stmnt;
407
408 LOOP
409 FETCH csr_get_lrnr_in_assign into lrnr_rec;
410 EXIT WHEN csr_get_lrnr_in_assign %NOTFOUND;
411
412
413 --- Added this code to set the context .
414
415 ben_env_object.init
416 (p_business_group_id =>lrnr_rec.business_group_id,
417 p_thread_id => null,
418 p_chunk_size => null,
419 p_threads => null,
420 p_max_errors => null,
421 p_benefit_action_id => null,
422 p_effective_date=> trunc(sysdate));
423
424
425 if lrnr_rec.business_group_id = l_elig_pro_bg_id then
426
427 IF ben_evaluate_elig_profiles.eligible
428 (p_person_id => lrnr_rec.person_id,
429 p_assignment_id => lrnr_rec.assignment_id,
430 p_business_group_id => lrnr_rec.business_group_id,
431 p_eval_typ => 'E',
432 p_comp_obj_mode => false,
433 p_eligprof_tab => l_eligprof_tab,
434 p_score_tab => l_score_tab,
435 p_effective_date => trunc (sysdate)) THEN
436
437 --if ben_per_asg_elig.eligible(lrnr_rec.person_id,lrnr_rec.assignment_id,l_elig_obj_id,trunc(sysdate), lrnr_rec.business_group_id,l_allow_contingent_wrk) = 'Y' then
438
439 l_numberof_records_processed := l_numberof_records_processed +1;
440 process_eligible_lrnr_record(lrnr_rec.person_id, user_group_rec.user_group_id,
441 user_group_rec.elig_prfl_id, user_group_rec.business_group_id, p_enable_logging);
442
443 if l_numberof_records_processed = 1000 then
444 commit;--commit for every 1000 child records
445 l_numberof_records_processed :=0;
446 end if;
447 end if;
448 end if;
449
450 END LOOP;
451 CLOSE csr_get_lrnr_in_assign;
452 end if;--end if for get_elig_prof_details%NOTFOUND
453
454 update ota_user_group_elements set processing_status= 'A'
455 where user_group_element_id = user_group_rec.user_group_element_id;--set status of master record to A
456 end if ; --end if for check_processing_status
457 end loop;
458 close csr_get_ug_to_be_processed;
459
460 if l_counter = 0 then
461 FND_FILE.PUT_LINE(FND_FILE.LOG,'The given combination of learner group and eligibility profile donot exist');
462 end if;
463
464 commit;
465 exception
466 when others then
467
468 update ota_user_group_elements set processing_status= 'E' --Parent record to Error
469 where user_group_element_id =l_learner_group_element_id;
470
471 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in processing learner group '||l_learner_group_name);
472 commit;
473
474 end resolve_eligibility_profile;
475
476 procedure resolve_elig_profile_multi(errbuf out nocopy varchar2,
477 retcode out nocopy varchar2,
478 p_action_id in number default null,
479 p_chunk_size in number default 1000,
480 p_thread_count in number default 3,
481 p_learner_group_id in number,
482 p_elig_prof_id in number,
483 p_enable_logging in varchar2 default 'N') is
484
485
486 TYPE learner_rec IS RECORD(
487 person_id per_assignments_f.person_id%type,
488 assignment_id per_assignments_f.assignment_id%type,
489 assignment_type per_assignments_f.assignment_type%type,
490 business_group_id per_assignments_f.business_group_id%type
491 );
492
493 lrnr_rec learner_rec;
494
495 l_sql_stmnt varchar2(4000);
496 l_counter number:=0;
497 l_per_bg_grp_prof_val number:= fnd_profile.value('PER_BUSINESS_GROUP_ID');
498 l_ota_global_bg_prof_val number:= fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID');
499
500 l_object_version_number Number(15);
501 l_request_id number;
502 l_action_id number(15);
503 l_person_action_id number(15);
504 l_range_id number(15);
505 l_num_range number := 0;
506 l_threads number := p_thread_count;
507 l_chunk_num number := 1;
508 l_person_ok varchar2(1) := 'Y';
509 l_person_actn_cnt number := 0;
510 l_start_person_action_id number(15);
511 l_end_person_action_id number(15);
512 l_commit number;
513 l_num_row number := 0;
514 l_validate varchar2(1) := 'N';
515 l_pre_person_id per_assignments_f.person_id%type := -1;
516
517 ug_whereclause varchar2(200);
518
519 TYPE ug_rec IS RECORD(
520 user_group_id ota_user_group_elements.user_group_id%type,
521 elig_prfl_id ota_user_group_elements.elig_prfl_id%type,
522 user_group_element_id ota_user_group_elements.user_group_element_id%type,
523 business_group_id ota_user_group_elements.business_group_id%type
524 );
525
526 TYPE t_ug_rec IS TABLE OF ug_rec
527 INDEX BY BINARY_INTEGER;
528
529 user_elig_pro_rec t_ug_rec;
530
531 TYPE learner_assign_details IS REF CURSOR;
532 csr_get_ug_to_be_processed learner_assign_details;
533
534 get_ug_to_be_processed varchar2(4000);
535 l_pre_ug_id ota_user_group_elements.user_group_id%TYPE := -1;
536 l_general_bg number := ota_general.get_business_group_id;
537 l_proc varchar2(200) := 'resolve_eligibility_profile';
538 OTA_NO_PERSON_LG_TO_PROCESS exception;
539
540 begin
541
542 if p_enable_logging = 'Y' then
543 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_per_bg_grp_prof_val:'||l_per_bg_grp_prof_val);
544 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_ota_global_bg_prof_val:'||l_ota_global_bg_prof_val);
545 FND_FILE.PUT_LINE(FND_FILE.LOG,'Entering :resolve_eligibility_profile');
546 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_action_id:'||p_action_id);
547 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_learner_group_id:'||p_learner_group_id);
548 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_elig_prof_id:'||p_elig_prof_id);
549 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_enable_logging:'||p_enable_logging);
550 end if;
551
552 If p_action_id is null then
553 --
554 if p_enable_logging = 'Y' then
555 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_action_id is null');
556 end if;
557
558 ota_batch_action_api.create_bulk_enr_request
559 (p_bulk_enr_request_id => l_action_id
560 ,p_requestor_id => fnd_global.conc_login_id
561 ,p_object_type => 'EP'
562 ,p_business_group_id => ota_general.get_business_group_id
563 ,p_conc_program_request_id => fnd_global.conc_request_id);
564
565 if p_enable_logging = 'Y' then
566 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_action_id:'||l_action_id);
567 end if;
568
569 if p_learner_group_id is not null and p_elig_prof_id is not null then
570
571 ug_whereclause := 'user_group_id = ' ||TO_CHAR(p_learner_group_id)
572 || ' and elig_prfl_id = '||TO_CHAR(p_elig_prof_id);
573
574 elsif p_elig_prof_id is null and p_learner_group_id is not null then
575
576 ug_whereclause := 'user_group_id = ' ||TO_CHAR(p_learner_group_id) ;
577
578 elsif p_elig_prof_id is not null and p_learner_group_id is null then
579
580 ug_whereclause := 'user_group_id in
581 (select user_group_id from ota_user_group_elements
582 where elig_prfl_id ='|| TO_CHAR(p_elig_prof_id)|| ')';
583 else
584
585 ug_whereclause := '1=1';
586 end if;
587
588 get_ug_to_be_processed := 'select * from
589 (select
590 ug.user_group_id,
591 uge.elig_prfl_id,
592 uge.user_group_element_id,
593 uge.business_group_id
594 from
595 ota_user_groups_b ug,
596 ota_user_group_elements uge
597 where
598 ug.user_group_id = uge.user_group_id
599 and trunc(sysdate) between trunc(ug.start_date_active) and nvl(ug.end_date_active,trunc(sysdate+1))
600 and uge.elig_prfl_id is not null
601 and uge.person_id is null
602 and nvl(uge.processing_status,''A'') in (''A'',''E'')
603 and ug.business_group_id = ota_general.get_business_group_id) QRSLT WHERE '|| ug_whereclause ||
604 ' order by user_group_id';
605
606 OPEN csr_get_ug_to_be_processed for get_ug_to_be_processed;
607 FETCH csr_get_ug_to_be_processed BULK COLLECT into user_elig_pro_rec;
608 CLOSE csr_get_ug_to_be_processed;
609
610 if p_enable_logging = 'Y' then
611 FND_FILE.PUT_LINE(FND_FILE.LOG,l_proc || ' get_ug_to_be_processed:'||get_ug_to_be_processed);
612 FND_FILE.PUT_LINE(FND_FILE.LOG,l_proc || ' ota_general.get_business_group_id:'||ota_general.get_business_group_id);
613 FND_FILE.PUT_LINE(FND_FILE.LOG,l_proc || ' user_elig_pro_rec.count:'||user_elig_pro_rec.count);
614 end if;
615 if user_elig_pro_rec.count > 0 then -- check whether there is any ug to process
616
617 for ug_print_count in user_elig_pro_rec.first..user_elig_pro_rec.last --{ ug_ep loop
618 loop
619 if l_pre_ug_id <> user_elig_pro_rec(ug_print_count).user_group_id then
620 update ota_user_group_elements set processing_status = fnd_global.conc_request_id --Parent record to Processing
621 where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
622 and elig_prfl_id is not null
623 and person_id is null
624 and BUSINESS_GROUP_ID = l_general_bg;
625
626 update ota_user_group_elements set processing_status = 'H' --Child records to history
627 where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
628 and elig_prfl_id is not null
629 and person_id is not null
630 and BUSINESS_GROUP_ID = l_general_bg;
631
632 l_pre_ug_id := user_elig_pro_rec(ug_print_count).user_group_id;
633 end if;
634
635 end loop;
636 commit;--commit early so that other re-runs of conc programs donot pick it up
637
638
639 FOR lrnr_rec IN csr_get_learner (l_per_bg_grp_prof_val,l_ota_global_bg_prof_val) LOOP
640
641 if l_pre_person_id <> lrnr_rec.person_id then
642 l_pre_person_id := lrnr_rec.person_id;
643
644 ota_batch_action_api.create_bulk_enr_request_member
645 (p_person_action_id => l_person_action_id
646 ,p_person_id => lrnr_rec.person_id
647 ,p_assignment_id => lrnr_rec.assignment_id
648 ,p_bulk_enr_request_id => l_action_id
649 ,p_action_status_cd => 'U'
650 ,p_business_group_id => lrnr_rec.business_group_id);
651
652 l_num_row := l_num_row + 1;
653 l_end_person_action_id := l_person_action_id;
654 --
655 If l_num_row = 1 then
656 --
657 l_start_person_action_id := l_person_action_id;
658 --
659 End if;
660 --
661 If l_num_row = p_chunk_size then
662 --
663 -- Create a range of data to be multithreaded.
664 --
665
666 ota_batch_action_api.create_batch_ranges
667 (p_validate => false
668 ,p_range_id => l_range_id
669 ,p_batch_source_cd => 'EP'
670 ,p_batch_action_id => l_action_id
671 ,p_range_status_cd => 'U'
672 ,p_starting_person_action_id => l_start_person_action_id
673 ,p_ending_person_action_id => l_end_person_action_id
674 ,p_object_version_number => l_object_version_number);
675
676 l_start_person_action_id := 0;
677 l_end_person_action_id := 0;
678 l_num_row := 0;
679 l_num_range := l_num_range + 1;
680 --
681 End if;
682 end if;
683 end loop;
684
685 If l_num_row <> 0 then
686 --
687 --
688 ota_batch_action_api.create_batch_ranges
689 (p_validate => false
690 ,p_range_id => l_range_id
691 ,p_batch_source_cd => 'EP'
692 ,p_batch_action_id => l_action_id
693 ,p_range_status_cd => 'U'
694 ,p_starting_person_action_id => l_start_person_action_id
695 ,p_ending_person_action_id => l_end_person_action_id
696 ,p_object_version_number => l_object_version_number);
697
698 l_num_range := l_num_range + 1;
699 --
700 End if;
701
702 end if; --end of ug check if
703 else --Restaring part which will have p_action_id
704 --
705 l_action_id := p_action_id;
706 --
707 l_num_range := 1;
708 --
709 end if; --If p_action_id is null
710
711 if l_num_range > 1 --2
712 then
713
714 l_threads := least(l_threads, l_num_range);
715 --
716 for l_count in 1..(l_threads - 1)
717 loop
718 --
719 --l_request_id := fnd_request.submit_request();
720
721 l_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'OTA'
722 ,program => 'OTA_RESOLVE_ELIG_PRF_MUL'
723 ,argument1 => l_action_id
724 ,argument2 => p_chunk_size
725 ,argument3 => p_thread_count
726 ,argument4 => p_learner_group_id
727 ,argument5 => p_elig_prof_id
728 ,argument6 => p_enable_logging);
729
730
731 --
732 -- Store the request id of the concurrent request
733 --
734 ota_elig_profile_util.g_num_processes := ota_elig_profile_util.g_num_processes + 1;
735 ota_elig_profile_util.g_processes_tbl(ota_elig_profile_util.g_num_processes) := l_request_id;
736
737 --
738 end loop;
739 --
740 commit;
741 --
742 elsif l_num_range = 0
743 then
744 -- Means no person is selected or no ug to process
745 --
746 --
747 raise OTA_NO_PERSON_LG_TO_PROCESS;
748
749 --
750 end if; --2
751
752 --<< better to keep this code in separate procedure >>
753 begin --1
754 --<<available batch range and available person in that range will be selected>>
755 --<<for each person get the learner group/groups details that should be processed>>
756 --<<If more than one group is present loop through all the groups>>
757 --<<as we have the person id select appropriate assignment record to check the eligibility>>
758 --<<get the elig_obj_id for the learner group>>
759 --<<check the eligibility of the person assignment against the learner group>>
760 --<<If multiple assignment records are present should loop through all the assigments
761 -- but if any one of the assignment is eligible to LG then person is eligible to LG
762 -- then no need to loop all the records>>
763 -- Call resolve_eligibility_profile_m;
764
765
766 process_elig_profile_multi( p_validate =>l_validate ,
767 p_action_id =>l_action_id,
768 p_business_group_id => ota_general.get_business_group_id,
769 p_learner_group_id => p_learner_group_id,
770 p_elig_prof_id => p_elig_prof_id,
771 p_enable_logging => p_enable_logging,
772 p_chunk_size => p_chunk_size,
773 p_thread_count => p_thread_count);
774
775 If p_action_id is null then
776
777 l_pre_ug_id := -1;
778 for ug_print_count in user_elig_pro_rec.first..user_elig_pro_rec.last --{ ug_ep loop
779 loop
780 if l_pre_ug_id <> user_elig_pro_rec(ug_print_count).user_group_id then
781 update ota_user_group_elements set processing_status = 'A' --Parent record to completed
782 where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
783 and nvl(processing_status,'A') <> 'E'
784 and elig_prfl_id is not null
785 and person_id is null
786 and BUSINESS_GROUP_ID = l_general_bg;
787
788 l_pre_ug_id := user_elig_pro_rec(ug_print_count).user_group_id;
789 end if;
790 end loop;
791 commit;
792 end if;
793 hr_utility.set_location('in multithread',10);
794 end; --1
795 FND_FILE.PUT_LINE(FND_FILE.LOG,l_proc || ' leaving');
796 exception
797 when OTA_NO_PERSON_LG_TO_PROCESS then
798 if p_enable_logging = 'Y' then
799 FND_FILE.PUT_LINE(FND_FILE.LOG,l_proc || ' No person or no learner group to process');
800 end if;
801 IF user_elig_pro_rec.count > 0 THEN
802 for ug_print_count in user_elig_pro_rec.first..user_elig_pro_rec.last --{ ug_ep loop
803 loop
804 if l_pre_ug_id <> user_elig_pro_rec(ug_print_count).user_group_id then
805 update ota_user_group_elements set processing_status = 'P' --Parent record to Processed
806 where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
807 and elig_prfl_id is not null
808 and person_id is null
809 and BUSINESS_GROUP_ID = l_general_bg;
810
811 l_pre_ug_id := user_elig_pro_rec(ug_print_count).user_group_id;
812 end if;
813 end loop;
814 end if;
815 commit;
816 when others then
817 if p_enable_logging = 'Y' then
818 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error:'||substr(SQLERRM,1,2000));
819 end if;
820 IF user_elig_pro_rec.count > 0 THEN
821 for ug_print_count in user_elig_pro_rec.first..user_elig_pro_rec.last --{ ug_ep loop
822 loop
823 if l_pre_ug_id <> user_elig_pro_rec(ug_print_count).user_group_id then
824 update ota_user_group_elements set processing_status = 'E' --Parent record to Error
825 where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
826 and elig_prfl_id is not null
827 and person_id is null
828 and BUSINESS_GROUP_ID = l_general_bg;
829
830 l_pre_ug_id := user_elig_pro_rec(ug_print_count).user_group_id;
831 end if;
832 end loop;
833 end if;
834 commit;
835 raise;
836 end resolve_elig_profile_multi;
837
838 /**
839 This function is used to get the parent request id for a concurrent program.
840 returns - Parent request id or Current request id (if no parent)
841 **/
842 function conc_parent_request_id(p_conc_request_id number) return number is
843
844 cursor get_parent_request_id is
845 select decode(PARENT_REQUEST_ID,-1,REQUEST_ID,PARENT_REQUEST_ID)
846 from fnd_concurrent_requests
847 where REQUEST_ID = p_conc_request_id;
848
849 l_parent_request_id number := -1;
850 begin
851
852 open get_parent_request_id;
853
854 fetch get_parent_request_id into l_parent_request_id;
855
856 if get_parent_request_id%notfound then
857 close get_parent_request_id;
858 return -1;
859 end if;
860
861 close get_parent_request_id;
862 return l_parent_request_id;
863
864 end conc_parent_request_id;
865
866 /**
867 This procedure is used to process the individual threads of the resolve eligiblity profile concurrent program
868 **/
869 PROCEDURE process_elig_profile_multi
870 (p_validate IN varchar2 DEFAULT 'N'
871 ,p_action_id IN number
872 ,p_business_group_id IN number
873 ,p_learner_group_id IN number
874 ,p_elig_prof_id IN number
875 ,p_enable_logging IN varchar2 DEFAULT 'N'
876 ,p_chunk_size IN number
877 ,p_thread_count IN number) IS
878 l_effective_date date;
879 l_range_id OTA_BATCH_RANGES.range_id%TYPE;
880 l_record_number number DEFAULT 0;
881 l_start_person_action_id number DEFAULT 0;
882 l_end_person_action_id number DEFAULT 0;
883 l_actn varchar2(80);
884 l_cnt number(5) DEFAULT 0;
885 l_per_bg_prof_val number := fnd_profile.value ('PER_BUSINESS_GROUP_ID');
886 l_ota_global_bg_prof_val number:= fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID');
887
888 l_eligprof_tab ben_evaluate_elig_profiles.proftab;
889 l_score_tab ben_evaluate_elig_profiles.scoreTab;
890
891 CURSOR c_range_thread IS
892 SELECT ran.range_id
893 ,ran.starting_person_action_id
894 ,ran.ending_person_action_id
895 FROM OTA_BATCH_RANGES ran
896 WHERE ran.range_status_cd = 'U'
897 AND ran.batch_source_cd = 'EP'
898 AND ran.BATCH_ACTION_ID = p_action_id
899 AND rownum < 2
900 FOR UPDATE OF ran.range_status_cd;
901
902 cursor c_person_assign_thread is
903 SELECT perf.person_id
904 ,perf.assignment_id
905 ,perf.mbr_bg_id
906 FROM ota_bulk_enr_req_members perf
907 WHERE perf.BULK_ENR_REQUEST_ID = p_action_id
908 AND perf.person_action_id BETWEEN l_start_person_action_id AND l_end_person_action_id
909 ORDER BY perf.person_action_id;
910
911 l_commit number;
912
913 CURSOR get_elig_prof_details
914 (p_elig_prof_id IN number) IS
915 SELECT DISTINCT
916 asmt_to_use_cd
917 FROM ben_eligy_prfl_f
918 WHERE eligy_prfl_id = p_elig_prof_id
919 AND trunc (sysdate) BETWEEN effective_start_date
920 AND effective_end_date
921 AND stat_cd IN ('A','P');
922
923 l_elig_prof_id ben_eligy_prfl_f.eligy_prfl_id%TYPE;
924 l_elig_prof_name ben_eligy_prfl_f.name%TYPE;
925 l_asmt_to_use_cd ben_eligy_prfl_f.asmt_to_use_cd%TYPE;
926 l_allow_contingent_wrk varchar2(1) DEFAULT 'Y';
927 l_learner_group_name ota_user_groups_tl.user_group_name%TYPE;
928 l_numberof_records_processed number DEFAULT 0;
929 assignment_whereclause varchar2(100);
930 l_elig_obj_id number;
931 l_counter number DEFAULT 0;
932 l_learner_group_element_id ota_user_group_elements.user_group_element_id%TYPE;
933 TYPE learner_rec IS RECORD (person_id per_assignments_f.person_id%TYPE
934 ,assignment_id per_assignments_f.assignment_id%TYPE
935 ,business_group_id per_assignments_f.business_group_id%TYPE);
936 TYPE t_learner_rec IS TABLE OF learner_rec INDEX BY binary_integer;
937 lrnr_rec t_learner_rec;
938 TYPE ug_rec IS RECORD (user_group_id ota_user_group_elements.user_group_id%TYPE
939 ,elig_prfl_id ota_user_group_elements.elig_prfl_id%TYPE
940 ,user_group_element_id ota_user_group_elements.user_group_element_id%TYPE
941 ,business_group_id ota_user_group_elements.business_group_id%TYPE
942 ,elig_pro_bg_id ben_eligy_prfl_f.business_group_id%TYPE);
943
944 TYPE t_ug_rec IS TABLE OF ug_rec INDEX BY binary_integer;
945 user_elig_pro_rec t_ug_rec;
946 TYPE learner_assign_details IS REF CURSOR;
947 csr_get_lrnr_in_assign learner_assign_details;
948 csr_get_ug_to_be_processed learner_assign_details;
949 csr_get_ug_ep_det learner_assign_details;
950 ug_whereclause varchar2(200);
951 get_ug_to_be_processed varchar2(4000);
952 l_pre_ug_id ota_user_group_elements.user_group_id%TYPE := -1;
953 l_proc varchar2(200) := 'resolve_eligibility_profile_m';
954 l_ota_general_bg number := ota_general.get_business_group_id;
955 l_is_eligible boolean := false;
956
957 BEGIN
958
959 IF p_learner_group_id IS NOT NULL
960 AND p_elig_prof_id IS NOT NULL THEN
961 ug_whereclause := 'user_group_id = '
962 || to_char (p_learner_group_id)
963 || ' and elig_prfl_id = '
964 || to_char (p_elig_prof_id);
965 ELSIF p_elig_prof_id IS NULL
966 AND p_learner_group_id IS NOT NULL THEN
967 ug_whereclause := 'user_group_id = '
968 || to_char (p_learner_group_id);
969 ELSIF p_elig_prof_id IS NOT NULL
970 AND p_learner_group_id IS NULL THEN
971 ug_whereclause := 'user_group_id in (select user_group_id from ota_user_group_elements
972 where elig_prfl_id = '
973 || to_char (p_elig_prof_id)
974 || ' )';
975 ELSE
976 ug_whereclause := '1=1';
977 END IF;
978
979 get_ug_to_be_processed := 'select * from
980 (select
981 ug.user_group_id user_group_id,
982 uge.elig_prfl_id elig_prfl_id,
983 uge.user_group_element_id user_group_element_id,
984 uge.business_group_id uge_business_group_id,
985 bep.business_group_id ep_business_group_id
986 from
987 ota_user_groups_b ug,
988 ota_user_group_elements uge,
989 ben_eligy_prfl_f bep
990 where
991 ug.user_group_id = uge.user_group_id
992 and trunc(sysdate) between trunc(ug.start_date_active) and nvl(ug.end_date_active,trunc(sysdate+1))
993 and uge.elig_prfl_id is not null
994 and uge.person_id is null
995 and bep.eligy_prfl_id = uge.elig_prfl_id
996 and trunc(sysdate) between bep.effective_start_date AND bep.effective_end_date
997 and bep.stat_cd IN (''A'',''P'')
998 and nvl(uge.processing_status,''A'') = '''
999 || to_char (conc_parent_request_id(fnd_global.conc_request_id))
1000 || ''' and ug.business_group_id = ota_general.get_business_group_id) QRSLT WHERE '
1001 || ug_whereclause
1002 || ' order by user_group_id';
1003 if p_enable_logging = 'Y' then
1004 FND_FILE.PUT_LINE(FND_FILE.LOG,l_proc || ' get_ug_to_be_processed:'||get_ug_to_be_processed);
1005 end if;
1006
1007 FND_FILE.PUT_LINE(FND_FILE.LOG,l_proc || ' conc_parent_request_id:'||conc_parent_request_id(fnd_global.conc_request_id));
1008
1009 OPEN csr_get_ug_to_be_processed
1010 FOR get_ug_to_be_processed;
1011
1012 FETCH csr_get_ug_to_be_processed
1013 BULK COLLECT INTO user_elig_pro_rec;
1014
1015 CLOSE csr_get_ug_to_be_processed;
1016
1017 LOOP
1018 OPEN c_range_thread;
1019
1020 FETCH c_range_thread
1021 INTO l_range_id
1022 ,l_start_person_action_id
1023 ,l_end_person_action_id;
1024
1025 IF c_range_thread%NOTFOUND THEN
1026
1027 CLOSE c_range_thread;
1028
1029 EXIT;
1030 END IF;
1031
1032 CLOSE c_range_thread;
1033
1034 UPDATE OTA_BATCH_RANGES ran
1035 SET ran.range_status_cd = 'P'
1036 WHERE ran.range_id = l_range_id;
1037
1038 COMMIT;
1039
1040 IF user_elig_pro_rec.count > 0 THEN -- if no elig pro record to process skip processing
1041
1042 l_eligprof_tab(1).eligy_prfl_id := null;
1043 l_eligprof_tab(1).mndtry_flag := 'N';
1044 l_eligprof_tab(1).compute_score_flag := 'Y';
1045 l_eligprof_tab(1).trk_scr_for_inelg_flag := 'N';
1046
1047 l_record_number := 0;
1048 l_counter := l_counter + 1;
1049 FOR l_rec IN c_person_assign_thread LOOP
1050
1051 IF p_enable_logging = 'Y' THEN
1052 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------------------------------');
1053 FND_FILE.PUT_LINE(FND_FILE.LOG,l_proc || ' l_rec.person_id:'||l_rec.person_id);
1054 FND_FILE.PUT_LINE(FND_FILE.LOG,l_proc || ' l_rec.mbr_bg_id:'||l_rec.mbr_bg_id);
1055 End if;
1056
1057 l_counter := l_counter + 1;
1058 l_pre_ug_id := - 1;
1059
1060 FOR l_ug_count IN user_elig_pro_rec.first .. user_elig_pro_rec.last LOOP
1061 IF l_rec.mbr_bg_id = user_elig_pro_rec (l_ug_count).elig_pro_bg_id THEN
1062 IF not l_is_eligible or l_pre_ug_id <> user_elig_pro_rec (l_ug_count).user_group_id THEN
1063 l_is_eligible := false;
1064
1065 IF l_pre_ug_id <> user_elig_pro_rec (l_ug_count).user_group_id THEN
1066 l_pre_ug_id := user_elig_pro_rec (l_ug_count).user_group_id;
1067
1068 l_learner_group_name := get_learner_group_name (user_elig_pro_rec (l_ug_count).user_group_id);
1069
1070 l_elig_obj_id := get_elig_object_id (user_elig_pro_rec (l_ug_count).user_group_id);
1071 END IF;
1072
1073 l_learner_group_element_id := user_elig_pro_rec (l_ug_count).user_group_element_id;
1074
1075 l_eligprof_tab(1).eligy_prfl_id := user_elig_pro_rec (l_ug_count).elig_prfl_id;
1076
1077 ben_env_object.init
1078 (p_business_group_id => l_rec.mbr_bg_id
1079 ,p_thread_id => NULL
1080 ,p_chunk_size => NULL
1081 ,p_threads => NULL
1082 ,p_max_errors => NULL
1083 ,p_benefit_action_id => NULL
1084 ,p_effective_date => trunc (sysdate));
1085
1086 IF ben_evaluate_elig_profiles.eligible
1087 (p_person_id => l_rec.person_id,
1088 p_assignment_id => l_rec.assignment_id,
1089 p_business_group_id => l_rec.mbr_bg_id,
1090 p_eval_typ => 'E',
1091 p_comp_obj_mode => false,
1092 p_eligprof_tab => l_eligprof_tab,
1093 p_score_tab => l_score_tab,
1094 p_effective_date => trunc (sysdate)) THEN
1095 l_numberof_records_processed := l_numberof_records_processed + 1;
1096
1097 process_eligible_lrnr_record (l_rec.person_id
1098 ,user_elig_pro_rec (l_ug_count).user_group_id
1099 ,user_elig_pro_rec (l_ug_count).elig_prfl_id
1100 ,user_elig_pro_rec (l_ug_count).business_group_id
1101 ,p_enable_logging);
1102 l_is_eligible := true;
1103 IF l_numberof_records_processed = 1000 THEN
1104 COMMIT;
1105
1106 l_numberof_records_processed := 0;
1107 END IF;
1108
1109 IF p_enable_logging = 'Y' THEN
1110 fnd_file.put_line (fnd_file.log
1111 ,l_rec.person_id
1112 || ' is eligible for '
1113 || l_learner_group_name);
1114 END IF;
1115 l_score_tab.delete;
1116 END IF;
1117 END IF;
1118 END IF;
1119 END LOOP;
1120
1121 UPDATE ota_bulk_enr_req_members
1122 SET action_status_cd = 'P'
1123 WHERE person_id = l_rec.person_id
1124 AND BULK_ENR_REQUEST_ID = p_action_id
1125 AND action_status_cd <> 'P';
1126
1127 lrnr_rec.delete;
1128 END LOOP;
1129 update ota_bulk_enr_req_members set action_status_cd = 'P'
1130 WHERE BULK_ENR_REQUEST_ID = p_action_id
1131 AND action_status_cd <> 'P'
1132 AND person_action_id BETWEEN l_start_person_action_id
1133 AND l_end_person_action_id;
1134 END IF;
1135 END LOOP;
1136
1137 user_elig_pro_rec.delete;
1138 l_eligprof_tab.delete;
1139
1140
1141 ota_elig_profile_util.check_all_slaves_finished
1142 (p_rpt_flag => TRUE);
1143
1144 IF l_counter = 0 THEN
1145 fnd_file.put_line (fnd_file.log
1146 ,'The given combination of learner group and eligibility profile donot exist');
1147 END IF;
1148
1149 IF p_enable_logging = 'Y' THEN
1150 fnd_file.put_line (fnd_file.log
1151 ,'Commit Done');
1152 END IF;
1153
1154 COMMIT;
1155 EXCEPTION
1156 WHEN others THEN
1157
1158 l_pre_ug_id := -1;
1159
1160 IF user_elig_pro_rec.count > 0 THEN
1161 for ug_print_count in user_elig_pro_rec.first..user_elig_pro_rec.last --{ ug_ep loop
1162 loop
1163 if l_pre_ug_id <> user_elig_pro_rec(ug_print_count).user_group_id then
1164 update ota_user_group_elements set processing_status = 'E' --Parent record to Error
1165 where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
1166 and elig_prfl_id is not null
1167 and person_id is null
1168 and BUSINESS_GROUP_ID = l_ota_general_bg;
1169
1170 l_pre_ug_id := user_elig_pro_rec(ug_print_count).user_group_id;
1171 end if;
1172 end loop;
1173 end if;
1174 fnd_file.put_line (fnd_file.log
1175 ,'Error in processing learner group '
1176 || l_learner_group_name);
1177
1178 fnd_file.put_line (fnd_file.log
1179 ,substr (sqlerrm
1180 ,1
1181 ,2000)
1182 || l_learner_group_name);
1183
1184 COMMIT;
1185 END process_elig_profile_multi;
1186
1187 procedure delete_ineligible_records(errbuf out nocopy varchar2,
1188 retcode out nocopy varchar2,
1189 p_learner_group_id in number,
1190 p_elig_prof_id in number) is
1191
1192 --user_group_rec get_ug_to_be_processed%rowtype;
1193 l_counter number:=0;
1194 l_learner_group_name ota_user_groups_tl.user_group_name%type;
1195 l_learner_group_element_id ota_user_group_elements.user_group_element_id%type;
1196 ug_ep_whereclause varchar2(200);
1197 get_ug_to_be_processed varchar2(4000);
1198 l_business_group_id ben_eligy_prfl_f.business_group_id%type;
1199
1200 TYPE ug_rec IS RECORD(
1201 user_group_id ota_user_group_elements.user_group_id%type,
1202 elig_prfl_id ota_user_group_elements.elig_prfl_id%type,
1203 user_group_element_id ota_user_group_elements.user_group_element_id%type,
1204 business_group_id ota_user_group_elements.business_group_id%type
1205 );
1206
1207 user_group_rec ug_rec;
1208
1209 TYPE learner_assign_details IS REF CURSOR;
1210 csr_get_ug_to_be_processed learner_assign_details;
1211
1212 l_elig_prof_id ben_eligy_prfl_f.eligy_prfl_id%type;
1213 l_elig_prof_name ben_eligy_prfl_f.name%type;
1214 l_asmt_to_use_cd ben_eligy_prfl_f.asmt_to_use_cd%type;
1215 begin
1216
1217
1218 if p_learner_group_id is not null and p_elig_prof_id is not null then
1219 ug_ep_whereclause := 'user_group_id = ' ||TO_CHAR(p_learner_group_id)
1220 || 'and elig_prfl_id = '||TO_CHAR(p_elig_prof_id);
1221 elsif p_elig_prof_id is null and p_learner_group_id is not null then
1222 ug_ep_whereclause := 'user_group_id = ' ||TO_CHAR(p_learner_group_id) ;
1223 elsif p_elig_prof_id is not null and p_learner_group_id is null then
1224 ug_ep_whereclause := 'user_group_id in
1225 (select user_group_id from ota_user_group_elements
1226 where elig_prfl_id ='|| TO_CHAR(p_elig_prof_id)|| ')';
1227 else
1228 ug_ep_whereclause := '1=1';
1229
1230 end if;
1231
1232 get_ug_to_be_processed := 'select * from
1233 (select
1234 ug.user_group_id,
1235 uge.elig_prfl_id,
1236 uge.user_group_element_id,
1237 uge.business_group_id
1238 from
1239 ota_user_groups_b ug,
1240 ota_user_group_elements uge
1241 where
1242 ug.user_group_id = uge.user_group_id
1243 and trunc(sysdate) between trunc(ug.start_date_active) and nvl(ug.end_date_active,trunc(sysdate+1))
1244 and uge.elig_prfl_id is not null
1245 and uge.person_id is null
1246 and nvl(uge.processing_status,''A'') <> ''P''
1247 and ug.business_group_id = ota_general.get_business_group_id) QRSLT WHERE '|| ug_ep_whereclause ;
1248
1249 OPEN csr_get_ug_to_be_processed for get_ug_to_be_processed;
1250 LOOP
1251 FETCH csr_get_ug_to_be_processed into user_group_rec;
1252 EXIT WHEN csr_get_ug_to_be_processed %NOTFOUND;
1253
1254 l_learner_group_name := get_learner_group_name(user_group_rec.user_group_id);
1255 l_learner_group_element_id := user_group_rec.user_group_element_id;
1256
1257 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner group being processed : '||l_learner_group_name);
1258
1259 open get_elig_prof_details(user_group_rec.elig_prfl_id);
1260 fetch get_elig_prof_details into l_elig_prof_id,l_elig_prof_name,l_asmt_to_use_cd,l_business_group_id;
1261 if get_elig_prof_details%NOTFOUND then
1262 close get_elig_prof_details;
1263 FND_FILE.PUT_LINE(FND_FILE.LOG,'Eligibility profile details not found or it is in inactive/closed status');
1264 --write messages in conc program log;
1265 else
1266
1267 l_counter := l_counter+1;
1268 delete from ota_user_group_elements uge where
1269 uge.person_id is not null and
1270 uge.elig_prfl_id is not null and
1271 uge.processing_status = 'H' and
1272 uge.user_group_id = user_group_rec.user_group_id and
1273 uge.elig_prfl_id = user_group_rec.elig_prfl_id
1274 and exists
1275 (
1276 select user_group_element_id from
1277 ota_user_group_elements parent
1278 where
1279 parent.elig_prfl_id = uge.elig_prfl_id
1280 and parent.user_group_id = uge.user_group_id
1281 and nvl(parent.processing_status,'A') = 'A'
1282 and parent.person_id is null
1283 );
1284
1285 commit;
1286 end if;
1287
1288 end loop;
1289
1290 if l_counter = 0 then
1291 FND_FILE.PUT_LINE(FND_FILE.LOG,'The given combination of learner group and eligibility profile donot exist');
1292 end if;
1293
1294 exception
1295 when others then
1296
1297 update ota_user_group_elements set processing_status= 'E' --Parent record to Error
1298 where user_group_element_id =l_learner_group_element_id;
1299
1300 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in deleting ineligible records for learner group '||l_learner_group_name);
1301 commit;
1302
1303 end delete_ineligible_records;
1304
1305
1306
1307 function is_profile_attached_to_ug(p_elig_prof_id in number)
1308 return varchar2 is
1309
1310 cursor user_group_exists is
1311 select user_group_id
1312 from
1313 ota_user_group_elements
1314 where
1315 elig_prfl_id=p_elig_prof_id;
1316
1317 l_user_group_id ota_user_group_elements.user_group_id%type;
1318
1319 begin
1320
1321 open user_group_exists;
1322 fetch user_group_exists into l_user_group_id;
1323 if user_group_exists%NOTFOUND then
1324 close user_group_exists;
1325 return 'N';
1326 else
1327 close user_group_exists;
1328 return 'Y';
1329 end if;
1330
1331
1332 end is_profile_attached_to_ug;
1333
1334
1335
1336 procedure create_eligibility_object
1337 (p_eligibility_profile_id in number,
1338 p_user_group_id in number,
1339 p_business_group_id in number,
1340 p_result out nocopy varchar2) is
1341
1342 cursor get_elig_object_id (p_user_group_id in number) is
1343 select
1344 elig_obj_id
1345 from
1346 ben_elig_obj_f EO
1347 where
1348 trunc(sysdate) between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
1349 and TABLE_NAME = 'OTA_USER_GROUPS_B'
1350 and COLUMN_NAME = 'USER_GROUP_ID'
1351 and COLUMN_VALUE = p_user_group_id;
1352
1353
1354 l_elig_obj_id ben_elig_obj_f.elig_obj_id%type;
1355 l_start_date date;
1356 l_end_date date;
1357 l_object_version_number1 number;
1358 l_effective_start_date date;
1359 l_effective_end_date date;
1360 l_object_version_number2 number;
1361 l_business_group_id number:= p_business_group_id;
1362 l_elig_obj_obj_version ben_elig_obj_elig_profl_f.object_version_number%type;
1363 l_elig_prof_id ben_elig_obj_elig_profl_f.elig_prfl_id%type;
1364 l_elig_obj_elig_prf_id ben_elig_obj_elig_profl_f.elig_obj_elig_prfl_id%type;
1365
1366 begin
1367
1368
1369 savepoint create_eligibility_object;
1370
1371 open get_elig_object_id(p_user_group_id);
1372 fetch get_elig_object_id into l_elig_obj_id;
1373 close get_elig_object_id;
1374
1375 if l_elig_obj_id is null then
1376 ben_elig_obj_api.create_ELIG_OBJ
1377 (
1378 p_validate => false
1379 ,p_elig_obj_id => l_elig_obj_id
1380 ,p_effective_start_date => l_start_date
1381 ,p_effective_end_date => l_end_date
1382 ,p_business_group_id => l_business_group_id
1383 ,p_table_name => 'OTA_USER_GROUPS_B'
1384 ,p_column_name => 'USER_GROUP_ID'
1385 ,p_column_value => p_user_group_id
1386 ,p_object_version_number => l_object_version_number1
1387 ,p_effective_date => trunc(sysdate)
1388 );
1389 end if;
1390
1391 if(l_elig_obj_id is null) then
1392 p_result := 'F';
1393 ROLLBACK TO create_ELIG_OBJ_ELIG_PROFL;
1394 return;
1395 -- raise;
1396 end if;
1397
1398 open get_elig_obj_elig_prf_id(l_elig_obj_id,p_eligibility_profile_id);
1399 fetch get_elig_obj_elig_prf_id into l_elig_obj_elig_prf_id,l_elig_prof_id,l_elig_obj_obj_version;
1400 close get_elig_obj_elig_prf_id;
1401
1402 if l_elig_obj_elig_prf_id is null then
1403 ben_ELIG_OBJ_ELIG_PROFL_api.create_elig_obj_elig_profl
1404 (
1405 p_validate => false
1406 ,p_elig_obj_id => l_elig_obj_id
1407 ,p_elig_prfl_id => p_eligibility_profile_id
1408 ,p_mndtry_flag => 'N'
1409 ,p_business_group_id => l_business_group_id
1410 ,p_effective_start_date => l_effective_start_date
1411 ,p_effective_end_date => l_effective_end_date
1412 ,p_elig_obj_elig_prfl_id => l_elig_obj_elig_prf_id
1413 ,p_object_version_number => l_object_version_number2
1414 ,p_effective_date => trunc(sysdate)
1415 );
1416 end if;
1417
1418 if(l_elig_obj_elig_prf_id is not null) then
1419 p_result := 'S';
1420 else
1421 p_result := 'F';
1422 end if;
1423
1424 Exception
1425 when others then
1426 ROLLBACK TO create_elig_obj_elig_profl;
1427 p_result := 'F';
1428 end create_eligibility_object;
1429
1430
1431 procedure delete_eligibility_object(
1432 p_user_group_id in varchar2,
1433 p_eligibility_profile_id in varchar2,
1434 p_result out nocopy varchar) is
1435
1436 cursor get_elig_object_id (p_user_group_id in number) is
1437 select
1438 elig_obj_id ,
1439 object_version_number
1440 from
1441 ben_elig_obj_f EO
1442 where
1443 trunc(sysdate) between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
1444 and TABLE_NAME = 'OTA_USER_GROUPS_B'
1445 and COLUMN_NAME = 'USER_GROUP_ID'
1446 and COLUMN_VALUE = p_user_group_id;
1447
1448
1449
1450 cursor check_elig_obj_asso_id_exist(p_elig_obj_id in number) is
1451 select
1452 elig_obj_elig_prfl_id
1453 from
1454 ben_elig_obj_elig_profl_f
1455 where
1456 trunc(sysdate) between effective_start_date and effective_end_date
1457 and elig_obj_id = p_elig_obj_id
1458 and rownum < 2;
1459
1460 l_elig_object_id ben_elig_obj_f.elig_obj_id%type;
1461 l_obj_version ben_elig_obj_f.object_version_number%type;
1462 l_elig_obj_elig_prf_id ben_elig_obj_elig_profl_f.elig_obj_elig_prfl_id%type;
1463 l_elig_obj_obj_version ben_elig_obj_elig_profl_f.object_version_number%type;
1464 l_elig_prof_id ben_elig_obj_elig_profl_f.elig_prfl_id%type;
1465 l_effective_start_date date;
1466 l_effective_end_date date;
1467 l_check_elig_obj_elig_prf_id ben_elig_obj_elig_profl_f.elig_obj_elig_prfl_id%type;
1468 begin
1469
1470 savepoint delete_eligibility_object;
1471
1472 open get_elig_object_id(p_user_group_id);
1473 fetch get_elig_object_id into l_elig_object_id,l_obj_version;
1474 close get_elig_object_id;
1475
1476 if l_elig_object_id is null then--add logic for cursor not found
1477 p_result := 'F';
1478 ROLLBACK TO delete_eligibility_object;
1479 return;
1480 end if;
1481
1482 open get_elig_obj_elig_prf_id(l_elig_object_id,p_eligibility_profile_id);
1483 fetch get_elig_obj_elig_prf_id into l_elig_obj_elig_prf_id,l_elig_prof_id,l_elig_obj_obj_version;
1484 close get_elig_obj_elig_prf_id;
1485
1486 ben_elig_obj_elig_profl_api.delete_elig_obj_elig_profl
1487 (p_validate => false
1488 ,p_elig_obj_elig_prfl_id => l_elig_obj_elig_prf_id
1489 ,p_effective_start_date => l_effective_start_date
1490 ,p_effective_end_date => l_effective_end_date
1491 ,p_object_version_number => l_elig_obj_obj_version
1492 ,p_effective_date => trunc(sysdate)
1493 ,p_datetrack_mode => hr_api.g_zap
1494 );
1495
1496 open check_elig_obj_asso_id_exist(l_elig_object_id);
1497 fetch check_elig_obj_asso_id_exist into l_check_elig_obj_elig_prf_id;
1498 close check_elig_obj_asso_id_exist;
1499
1500 if l_check_elig_obj_elig_prf_id is null then
1501 ben_elig_obj_api.delete_ELIG_OBJ
1502 (p_validate => false
1503 ,p_elig_obj_id => l_elig_object_id
1504 ,p_effective_start_date => l_effective_start_date
1505 ,p_effective_end_date => l_effective_end_date
1506 ,p_object_version_number => l_obj_version
1507 ,p_effective_date => trunc(sysdate)
1508 ,p_datetrack_mode => hr_api.g_zap
1509 );
1510 else
1511 l_obj_version := 1;
1512 end if;
1513 if l_elig_obj_obj_version is not null and l_obj_version is not null then
1514 delete from ota_user_group_elements
1515 where user_group_id=p_user_group_id and
1516 elig_prfl_id=l_elig_prof_id and
1517 person_id is not null;
1518 p_result := 'S';
1519 else
1520 p_result := 'F';
1521 end if;
1522
1523 exception
1524 when others then
1525 ROLLBACK TO delete_eligibility_object;
1526 p_result := 'F';
1527 end delete_eligibility_object;
1528
1529 function submit_conc_request(
1530 p_user_group_id in varchar2,
1531 p_elig_prfl_id in varchar2 default NULL,
1532 p_enable_debug in varchar2 default 'N')
1533 return number is
1534
1535 l_conc_proc_id number;
1536 begin
1537
1538 l_conc_proc_id := FND_REQUEST.SUBMIT_REQUEST(application => 'OTA'
1539 ,program => 'OTA_RESOLVE_ELIG_PRF_MUL'
1540 ,argument1 => NULL
1541 ,argument2 => 1000
1542 ,argument3 => 3
1543 ,argument4 => p_user_group_id
1544 ,argument5 => p_elig_prfl_id
1545 ,argument6 => p_enable_debug);
1546
1547 return l_conc_proc_id;
1548
1549 end submit_conc_request;
1550
1551 -- ============================================================================
1552 -- << Rpt_error >>
1553 -- ============================================================================
1554 --
1555 procedure rpt_error (p_proc in varchar2
1556 ,p_last_actn in varchar2
1557 ,p_rpt_flag in boolean default FALSE
1558 ) is
1559 L_proc varchar2(80) := g_package||'.rpt_error';
1560 Begin
1561 If (p_rpt_flag ) then
1562 fnd_file.put_line(fnd_file.log,'<<<Fail in '||p_proc||' while '|| p_last_actn||'>>>');
1563 End if;
1564 hr_utility.set_location('> Fail in ' || p_proc, 999 );
1565 hr_utility.set_location('>> While ' || p_last_actn, 999);
1566 End rpt_error;
1567
1568 --
1569 -- ============================================================================
1570 -- <<Check_all_slaves_finished>>
1571 -- ============================================================================
1572 --
1573 Procedure check_all_slaves_finished(p_rpt_flag Boolean default FALSE) is
1574 --
1575 l_proc varchar2(80) := g_package||'.check_all_slaves_finished';
1576 l_no_slaves boolean := true;
1577 l_dummy varchar2(1);
1578 l_actn varchar2(80);
1579 --
1580 Cursor c_slaves(p_request_id number) is
1581 Select null
1582 From fnd_concurrent_requests fnd
1583 Where fnd.phase_code <> 'C'
1584 And fnd.request_id = p_request_id;
1585 Begin
1586 hr_utility.set_location ('Entering '||l_proc,5);
1587 If g_num_processes <> 0 then
1588 l_actn := 'Checking Slaves.....';
1589 While l_no_slaves loop
1590 l_no_slaves := false;
1591 For l_count in 1..g_num_processes loop
1592 open c_slaves(g_processes_tbl(l_count));
1593 fetch c_slaves into l_dummy;
1594 If c_slaves%found then
1595 l_no_slaves := true;
1596 close c_slaves;
1597 exit;
1598 End if;
1599 Close c_slaves;
1600 End loop;
1601 If (l_no_slaves) then
1602 dbms_lock.sleep(5);
1603 End if;
1604 End loop;
1605 End if;
1606 hr_utility.set_location ('Leaving '||l_proc,5);
1607 Exception
1608 when others then
1609 rpt_error(p_proc =>l_proc,p_last_actn=>l_actn,p_rpt_flag=>p_rpt_flag);
1610 raise;
1611 End check_all_slaves_finished;
1612
1613 end ota_elig_profile_util;
1614
[Home] [Help]