[Home] [Help]
PACKAGE BODY: APPS.OTA_CLASSIC_UPGRADE
Source
1 PACKAGE BODY OTA_CLASSIC_UPGRADE AS
2 /* $Header: otclassicupg.pkb 120.5.12010000.3 2008/08/05 11:41:56 ubhat ship $ */
3
4 DM_COPY_SUFFIX constant varchar2(50) := '***UPG***';
5 UPGRADE_NAME constant varchar2(30) := 'OTCLSUPG';
6 LOG_TYPE_I constant varchar2(30) := 'I'; -- log type is Information
7 LOG_TYPE_N constant varchar2(30) := 'N'; -- log type is Internal
8 LOG_TYPE_E constant varchar2(30) := 'E'; -- log type is Error
9 G_PROCESS_DATE constant date := sysdate;
10
11 -- ----------------------------------------------------------------------------
12 -- |-------------------------< get_process_date >-----------------------------|
13 -- ----------------------------------------------------------------------------
14 function get_process_date (p_upgrade_id in number
15 ,p_upgrade_name in varchar2)
16 return date is
17 l_process_date date;
18 cursor process_date is
19 select max(process_date)
20 from ota_upgrade_log
21 where upgrade_id = p_upgrade_id
22 and upgrade_name = p_upgrade_name
23 and source_primary_key = '-1';
24 begin
25 l_process_date:= null;
26 open process_date;
27 fetch process_date into l_process_date;
28 close process_date;
29 if l_process_date is null then
30 l_process_date := g_process_date;
31 end if;
32 return l_process_date;
33 end;
34
35
36 -- ----------------------------------------------------------------------------
37 -- |-------------------------< get_next_upgrade_id >--------------------------|
38 -- ----------------------------------------------------------------------------
39 function get_next_upgrade_id
40 return number is
41 l_upgrade_id number;
42 begin
43 select nvl(max(upgrade_id),1)
44 into l_upgrade_id
45 from ota_upgrade_log ;
46
47 return l_upgrade_id +1 ;
48
49 end get_next_upgrade_id;
50
51
52 -- ----------------------------------------------------------------------------
53 -- |-------------------------< get_lang_code >-----------------------------|
54 -- ----------------------------------------------------------------------------
55 function get_lang_code(p_evt_language_id in number,p_tav_language_id in number) return varchar2 is
56 l_lang_code fnd_languages.language_code%type;
57 cursor c_lang_code is
58 select fl.language_code
59 from fnd_languages fl
60 where fl.language_id = Nvl(p_evt_language_id,p_tav_language_id);
61
62 begin
63 open c_lang_code;
64 fetch c_lang_code into l_lang_code;
65 close c_lang_code;
66
67 return l_lang_code;
68 end;
69 -- ----------------------------------------------------------------------------
70 -- |-------------------------< Create_Default_DM >----------------------------|
71 -- ----------------------------------------------------------------------------
72 Procedure Create_Default_Dm(p_business_group_id in number,
73 p_dm_id IN OUT NOCOPY Number,
74 p_update_id in number default 1 ) is
75 l_category_usage_id ota_category_usages.category_usage_id%Type ;
76 l_object_version_number ota_category_usages.object_version_number%Type ;
77 l_dm_name ota_category_usages_tl.category%Type;
78 l_err_code varchar2(72);
79 l_err_msg varchar2(2000);
80 l_course_min_st_dt date;
81 Cursor csr_check_dm(p_dm in varchar2) is
82 Select Ocu.Category_usage_id
83 From Ota_category_usages_vl ocu
84 Where ocu.Business_group_id = p_business_group_id
85 and ocu.Category = p_dm
86 and ocu.Type = 'DM' ;
87 l_delivery_mode_id ota_category_usages.category_usage_id%Type;
88
89 Begin
90
91 Select lkp.Meaning into l_dm_name
92 From Hr_lookups lkp
93 Where lkp.lookup_type = 'ACTIVITY_CATEGORY'
94 And lkp.lookup_code = 'INCLASS' ;
95
96 Open csr_check_dm(l_dm_name) ;
97 Fetch csr_check_dm into l_delivery_mode_id ;
98 If csr_check_dm%NOTfound then
99 begin
100 SELECT MIN(TAV.START_DATE) INTO l_course_min_st_dt
101 FROM OTA_ACTIVITY_VERSIONS TAV
102 WHERE NOT EXISTS
103 (SELECT ACI.ACTIVITY_VERSION_ID FROM OTA_ACT_CAT_INCLUSIONS ACI,OTA_CATEGORY_USAGES CTU
104 WHERE ACI.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
105 AND ACI.CATEGORY_USAGE_ID = CTU.CATEGORY_USAGE_ID
106 AND CTU.TYPE = 'DM') ;
107 If l_course_min_st_dt is NULL then
108 l_course_min_st_dt := trunc(sysdate);
109 End If;
110
111 Ota_ctu_ins.ins(
112 p_effective_date => trunc(sysdate)
113 ,p_business_group_id => p_business_group_id --nvl(c_get_act.business_group_id,0)
114 ,p_category => l_dm_name
115 ,p_type => 'DM'
116 ,p_parent_cat_usage_id => NULL
117 ,p_synchronous_flag => 'Y'
118 ,p_online_flag => 'N'
119 ,p_start_date_active => l_course_min_st_dt -- trunc(sysdate) -- trunc(c_get_act.Creation_Date)
120 ,p_category_usage_id => l_category_usage_id
121 ,p_object_version_number => l_object_version_number
122 );
123 p_dm_id := l_category_usage_id;
124 insert into ota_category_usages_tl
125 ( CATEGORY_USAGE_ID,
126 LANGUAGE ,
127 CATEGORY ,
128 DESCRIPTION ,
129 SOURCE_LANG )
130 select
131 l_category_usage_id,
132 lkp.language,
133 lkp.meaning,
134 lkp.meaning,
135 lkp.source_lang
136 from fnd_lookup_values lkp
137 where lkp.lookup_type = 'ACTIVITY_CATEGORY'
138 and lkp.lookup_code = 'INCLASS'
139 and lkp.security_group_id = 0 -- added for bug#4116886
140 and lkp.view_application_id = 3; -- added for bug#4116886
141
142 exception
143 when others then
144 p_dm_id:= null;
145 l_err_code := SQLCODE;
146 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When creating Default Delivery Mode for Business group name');
147
148 add_log_entry( p_table_name=>'CREATE_DM'
149 ,p_business_group_id => p_business_group_id
150 ,p_source_primary_key => p_business_group_id
151 ,p_object_value => l_dm_name
152 ,p_message_text => l_err_msg
153 ,p_upgrade_id => p_update_id
154 ,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
155 ,p_log_type => LOG_TYPE_E
156 ,p_upgrade_name => UPGRADE_NAME );
157
158
159 end;
160 End If;
161 Close csr_check_dm;
162 End Create_Default_Dm;
163
164 -- ----------------------------------------------------------------------------
165 -- |---------------------------< Create_Root_Category >-----------------------|
166 -- ----------------------------------------------------------------------------
167 PROCEDURE Create_Root_Category(
168 p_business_group_id in Number,
169 p_business_group_name in varchar2,
170 p_root_cat_usage_id in out NOCOPY Number,
171 p_update_id in number default 1 ) IS
172
173 Cursor Csr_chk_root_ctg_exists Is
174 Select ctu.Category_usage_id from ota_Category_usages ctu, ota_Category_usages ct
175 where ctu.parent_cat_usage_id is NULL
176 and ctu.category_usage_id = ct.parent_cat_usage_id
177 and ctu.business_group_id = p_business_group_id
178 and ctu.business_group_id = ct.business_group_id
179 and ctu.type = 'C'
180 and ct.type = 'C' ;
181
182 CURSOR csr_Category_present is
183 SELECT ocu.category_usage_id,ocu.start_date_active
184 FROM ota_category_usages ocu
185 WHERE ocu.category = p_business_group_name -- business_group_id AND name is unique
186 AND ocu.business_group_id = p_business_group_id
187 AND type = 'C' ;
188 l_category_usage_id ota_category_usages.category_usage_id%Type;
189 l_parent_cat_usage_id ota_category_usages.parent_cat_usage_id%Type;
190 l_start_date ota_category_usages.start_date_active%Type;
191 l_object_version_number ota_category_usages.object_version_number%Type;
192 l_type Varchar2(1) := 'C' ;
193 l_err_code varchar2(72);
194 l_err_msg varchar2(2000);
195
196 Begin
197 Open Csr_Chk_root_ctg_exists;
198 Fetch Csr_Chk_root_ctg_exists into l_parent_cat_usage_id;
199
200 If Csr_chk_root_ctg_exists%NotFound then
201 open csr_category_present ;
202 fetch csr_category_present into l_parent_cat_usage_id,l_start_date;
203
204 if csr_category_present%notfound then
205 begin
206
207 Ota_ctu_ins.ins
208 (
209 p_effective_date => trunc(sysdate)
210 ,p_business_group_id => p_business_group_id
211 ,p_category => p_business_group_name
212 ,p_type => l_type
213 ,p_parent_cat_usage_id => -1 -- To bypass the ota_ctu_bus.chk_root_cat validation, created the root category with parent_cat_usage_id as '-1'
214 ,p_synchronous_flag => null
215 ,p_online_flag => null
216 ,p_start_date_active => l_start_date
217 ,p_category_usage_id => l_category_usage_id
218 ,p_object_version_number => l_object_version_number
219 );
220
221 -- To bypass the ota_ctu_bus.chk_root_cat validation, created the root category with parent_cat_usage_id as '-1'
222 -- Updating the dummy value to NULL
223 Update Ota_category_usages
224 Set Parent_cat_usage_id = Null
225 Where Category_usage_id = l_category_usage_id
226 and Business_group_id = p_business_group_id
227 and Parent_cat_usage_id = -1 ;
228
229 Insert into ota_category_usages_tl
230 (Category_usage_Id,
231 Language,
232 Category,
233 Description,
234 Source_Lang,
235 Created_By,
236 Creation_Date,
237 Last_Updated_By,
238 Last_Update_Date,
239 Last_Update_Login )
240 Select l_category_usage_id,
241 orgtl.language,
242 orgtl.Name,
243 orgtl.name,
244 orgtl.source_lang,
245 orgtl.Created_By,
246 orgtl.Creation_date,
247 orgtl.Last_Updated_By,
248 orgtl.Last_Update_Date,
249 orgtl.Last_Update_Login
250 From hr_organization_units org, hr_all_organization_units_tl orgtl
251 Where orgtl.organization_id = p_business_group_id
252 and org.organization_id = org.business_group_id
253 and org.organization_id = orgtl.organization_id
254 And Not Exists (Select '1'
255 From OTA_CATEGORY_USAGES_TL T
256 Where T.Category_usage_Id = l_Category_usage_Id
257 And T.Language = Orgtl.Language ) ;
258
259
260 l_parent_cat_usage_id := l_category_usage_id;
261 Exception
262 when others then
263 l_err_code := SQLCODE;
264 l_err_msg := nvl(substr(SQLERRM,1,2000),'When creating root category');
265 l_category_usage_id:= null;
266
267 add_log_entry( p_table_name=>'CREATE_CTU'
268 ,p_business_group_id => p_business_group_id
269 ,p_source_primary_key => p_business_group_id
270 ,p_object_value => p_business_group_name
271 ,p_message_text => l_err_msg
272 ,p_upgrade_id => p_update_id
273 ,p_process_date => get_process_date(UPGRADE_NAME,UPGRADE_NAME)
274 ,p_log_type => LOG_TYPE_E
275 ,p_upgrade_name => UPGRADE_NAME );
276
277 end;
278 End If;
279 Close csr_category_present;
280 End If;
281
282 Close Csr_Chk_root_ctg_exists;
283
284 -- There can be only one category exist wih parent_cat_usage_id as NULL
285 -- It will be the root category for that BG. And hence updating the
286 -- other categories belongs to that BG to have <Business Group Name> Category as Parent.
287
288 Update Ota_category_usages
289 Set parent_cat_usage_id = l_parent_cat_usage_id
290 Where parent_cat_usage_id is null
291 and Type = 'C'
292 and Business_group_id = p_business_group_id
293 and Category_usage_id <> l_parent_cat_usage_id ;
294
295 p_root_cat_usage_id := l_parent_cat_usage_id;
296
297
298 End ;
299 -- ----------------------------------------------------------------------------
300 -- |---------------------------< upgrade_act_cat_inclusions >-----------------|
301 -- ----------------------------------------------------------------------------
302 procedure upgrade_act_cat_inclusions is
303 begin
304 update ota_act_cat_inclusions
305 set activity_category = null
306 where activity_category is not null
307 and category_usage_id is not null;
308 end upgrade_act_cat_inclusions;
309 -- ----------------------------------------------------------------------------
310 -- |---------------------------< create_delivery_mode >-----------------------|
311 -- ----------------------------------------------------------------------------
312 function create_delivery_mode ( p_name in varchar2
313 ,p_online_flag in varchar2
314 ,p_sync_flag in varchar2
315 ,p_business_group_id in number
316 ,p_start_date in date default null)
317 return number
318 is
319 cursor c_duplicate_dm(p_cat_name varchar2,p_bg_id number) is
320 select category_usage_id
321 from ota_category_usages_vl
322 where category = p_cat_name
323 and type = 'DM'
324 and business_group_id = p_bg_id;
325
326 l_ovn ota_category_usages.object_version_number%type;
327 l_category_usage_id ota_category_usages.category_usage_id%type;
328
329 begin
330 open c_duplicate_dm(p_name,p_business_group_id);
331 fetch c_duplicate_dm into l_category_usage_id;
332 if c_duplicate_dm %notfound then
333 Ota_category_usage_api.Create_Category(
334 p_effective_date => trunc(sysdate)
335 ,p_business_group_id => p_business_group_id --nvl(c_get_act.business_group_id,0)
336 ,p_category => p_name
337 ,p_description => substrb(p_name,1,240)
338 ,p_type => 'DM'
339 ,p_parent_cat_usage_id => NULL
340 ,p_synchronous_flag => p_sync_flag
341 ,p_online_flag => p_online_flag
342 ,p_start_date_active => p_start_date -- trunc(sysdate) -- trunc(c_get_act.Creation_Date)
343 ,p_category_usage_id => l_category_usage_id
344 ,p_object_version_number => l_ovn
345 );
346 end if;
347 close c_duplicate_dm;
351 -- ----------------------------------------------------------------------------
348 return l_category_usage_id;
349 end create_delivery_mode;
350
352 -- |---------------------------< upgrade_online_del_modes >-------------------|
353 -- ----------------------------------------------------------------------------
354 procedure upgrade_non_online_attach_dms (p_upgrade_id in number default 1) is
355
356 cursor c_non_online_dms is
357 select ctu.category_usage_id
358 ,ctu.category
359 ,ctu.online_flag
360 ,ctu.synchronous_flag
361 ,ctu.business_group_id
362 from ota_category_usages_vl ctu
363 where ctu.type ='DM'
364 and nvl(ctu.online_flag,'N') = 'N'
365 and nvl(ctu.synchronous_flag,'Y')= 'Y'
366 and ctu.category in (select lkp.meaning
367 from hr_lookups lkp
368 where lkp.lookup_type = 'ACTIVITY_CATEGORY'
369 and lkp.lookup_code = 'INCLASS');
370
371 cursor c_online_courses(p_dm_id number) is
372 select tav.activity_version_id
373 from ota_category_usages ctu
374 ,ota_activity_versions tav
375 ,ota_act_cat_inclusions aci
376 where aci.category_usage_id = ctu.category_usage_id
377 and tav.activity_version_id = aci.activity_version_id
378 and ctu.type = 'DM'
379 and tav.rco_id is not null
380 and ctu.category_usage_id = p_dm_id
381 and ( aci.primary_flag = 'Y'
382 or not exists(select 1 from ota_act_cat_inclusions aci1
383 where aci1.activity_version_id = tav.activity_version_id
384 and aci1.category_usage_id <> p_dm_id)) ;
385
386 cursor c_online_events(p_act_ver_id number) is
387 select count(decode(evt.event_type,'SELFPACED',1,null)) selfpaced_c
388 ,count(decode(evt.event_type,'SCHEDULED',1,null)) scheduled_c
389 from ota_events evt
390 where evt.activity_version_id = p_act_ver_id;
391
392 l_found number;
393 l_online_flag ota_category_usages.online_flag%type;
394 l_sync_flag ota_category_usages.synchronous_flag%type;
395
396 l_sp_count number;
397 l_sched_count number;
398
399 l_category_usage_id number;
400
401 l_err_code varchar2(72);
402 l_err_msg varchar2(2000);
403 begin
404
405 for c_ol_dms in c_non_online_dms loop
406 l_sync_flag := 'Y';
407 l_online_flag := 'N';
408 for c_ol_tav in c_online_courses(c_ol_dms.category_usage_id) loop
409 l_sp_count := -1;
410 l_sched_count := -1;
411 l_online_flag := 'Y';
412
413 open c_online_events(c_ol_tav.activity_version_id);
414 fetch c_online_events into l_sp_count,l_sched_count;
415
416 if c_online_events%found then
417 if Nvl(l_sync_flag,'*') <> 'N' then
418 if nvl(l_sp_count,-1) > 0 then
419 l_sync_flag := 'N';
420 elsif l_sched_count > 0 then
421 l_sync_flag := 'Y';
422 end if;
423 end if;
424 end if;
425 close c_online_events;
426
427 l_category_usage_id := create_delivery_mode(c_ol_dms.category||DM_COPY_SUFFIX,
428 l_online_flag,
429 l_sync_flag,c_ol_dms.business_group_id);
430
431 delete from ota_act_cat_inclusions
432 where activity_version_id = c_ol_tav.activity_version_id
433 and category_usage_id = c_ol_dms.category_usage_id;
434
435 Insert into ota_act_cat_inclusions
436 ( activity_category
437 ,activity_version_id
438 ,category_usage_id
439 ,object_version_number
440 ,primary_flag
441 ) values
442 (null
443 ,c_ol_tav.activity_version_id
444 ,l_category_usage_id
445 ,1
446 ,'Y');
447
448 end loop;
449
450 end loop;
451 end;
452 -- ----------------------------------------------------------------------------
453 -- |---------------------------< upgrade_online_del_modes >-------------------|
454 -- ----------------------------------------------------------------------------
455 procedure upgrade_online_del_modes (p_upgrade_id in number default 1) is
456 cursor c_online_dms is
457 select ctu.category_usage_id
458 ,ctu.category
459 ,ctu.online_flag
460 ,ctu.synchronous_flag
461 ,ctu.business_group_id
462 from ota_category_usages_vl ctu
463 where ctu.type = 'DM'
464 and nvl(ctu.online_flag,'N') = 'N'
465 and nvl(ctu.synchronous_flag,'Y')= 'Y'
466 and ctu.category not in (select lkp.meaning
467 from hr_lookups lkp
468 where lkp.lookup_type = 'ACTIVITY_CATEGORY'
469 and lkp.lookup_code = 'INCLASS');
470
471 cursor c_online_courses(p_dm_id number) is
472 select tav.activity_version_id
473 from ota_category_usages ctu
474 ,ota_activity_versions tav
475 ,ota_act_cat_inclusions aci
476 where aci.category_usage_id = ctu.category_usage_id
477 and tav.activity_version_id = aci.activity_version_id
478 and ctu.type = 'DM'
479 and tav.rco_id is not null
480 and ctu.category_usage_id = p_dm_id ;
481
482
483
484 cursor c_online_events(p_act_ver_id number) is
485 select count(decode(evt.event_type,'SELFPACED',1,null)) selfpaced_c
486 ,count(decode(evt.event_type,'SCHEDULED',1,null)) scheduled_c
487 from ota_events evt
488 where evt.activity_version_id = p_act_ver_id;
489
490 l_found number;
491 l_online_flag ota_category_usages.online_flag%type;
492 l_sync_flag ota_category_usages.synchronous_flag%type;
493
494 l_sp_count number;
495 l_sched_count number;
496
497 l_err_code varchar2(72);
498 l_err_msg varchar2(2000);
499
500
501 begin
502
503 for c_ol_dms in c_online_dms loop
504 l_sync_flag := 'Y';
505 l_online_flag := 'N';
506 for c_ol_tav in c_online_courses(c_ol_dms.category_usage_id) loop
507
508 l_sp_count := -1;
509 l_sched_count := -1;
510 l_online_flag := 'Y';
511
512 open c_online_events(c_ol_tav.activity_version_id);
513 fetch c_online_events into l_sp_count,l_sched_count;
514
515 if c_online_events%found then
516 if Nvl(l_sync_flag,'*') <> 'N' then
517 if nvl(l_sp_count,-1) > 0 then
518 l_sync_flag := 'N';
519 elsif l_sched_count > 0 then
520 l_sync_flag := 'Y';
521 end if;
522 end if;
523 end if;
524 close c_online_events;
525
526
527 begin
528
529 update ota_act_cat_inclusions aci
530 set primary_flag = decode(category_usage_id, c_ol_dms.category_usage_id,'Y','N') --'N'
531 where aci.activity_version_id = c_ol_tav.activity_version_id
532 and aci.category_usage_id in (select ctu.category_usage_id
533 from ota_Category_usages ctu
534 where ctu.type = 'DM' ) ;
535
536 exception
537 when others then
538 l_err_code := SQLCODE;
539 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When creating Default Delivery Mode for Business group name');
540
541 add_log_entry( p_table_name=>'upgrade_attached_online_dms'
542 ,p_business_group_id => null
543 ,p_source_primary_key => c_ol_dms.category_usage_id
544 ,p_object_value => c_ol_dms.category_usage_id
545 ,p_message_text => l_err_msg
546 ,p_upgrade_id => p_upgrade_id
547 ,p_process_date => get_process_date(UPGRADE_NAME,UPGRADE_NAME)
548 ,p_log_type => LOG_TYPE_E
549 ,p_upgrade_name => UPGRADE_NAME );
550 end;
551
552
553 end loop;
554
555 update ota_category_usages
556 set online_flag = l_online_flag
557 ,synchronous_flag =nvl(l_sync_flag,'N')
558 where category_usage_id = c_ol_dms.category_usage_id;
559
560
561
562 end loop;
563
564
565 end upgrade_online_del_modes;
566
567
568 -- ----------------------------------------------------------------------------
569 -- |---------------------------< upgrade_attached_online_dms >----------------|
570 -- ----------------------------------------------------------------------------
571 procedure upgrade_attached_online_dms(p_upgrade_id in number default 1) is
572
573 cursor c_online_dms is
574 select ctu.category_usage_id
575 ,ctu.category
576 ,ctu.business_group_id
577 from ota_category_usages_vl ctu
578 where ctu.online_flag = 'Y'
579 and ctu.type = 'DM';
580
581 cursor c_attach_online_dms(p_dm_id in number)is
582 select tav.activity_version_id
583 ,aci.primary_flag
584 from ota_activity_versions tav
585 ,ota_act_cat_inclusions aci
586 where aci.category_usage_id = p_dm_id
587 and tav.activity_version_id = aci.activity_version_id
588 and tav.rco_id is null
589 and not exists (select 1 from ota_offerings off
590 where off.activity_version_id = tav.activity_version_id
591 and rownum =1);
592
593
594 l_activity_version_id ota_activity_versions.activity_version_id%type;
595 l_dm_start_date date;
596 l_category_usage_id ota_category_usages.category_usage_id%type;
597 l_primary_flag ota_act_cat_inclusions.primary_flag%type;
598 l_found number;
599
600 l_err_code varchar2(72);
601 l_err_msg varchar2(2000);
602
603
604 begin
605
606 for c_ol_dms in c_online_dms loop
607
608 for c_attach_ol_dms in c_attach_online_dms(c_ol_dms.category_usage_id) loop
609 l_activity_version_id := c_attach_ol_dms.activity_version_id;
610 l_primary_flag := c_attach_ol_dms.primary_flag;
611 l_category_usage_id := null;
612 begin
613 l_category_usage_id := create_delivery_mode(c_ol_dms.category||DM_COPY_SUFFIX
614 ,'N','Y',c_ol_dms.business_group_id);
615 delete from ota_act_cat_inclusions
616 where category_usage_id = c_ol_dms.category_usage_id
617 and activity_version_id = l_activity_version_id;
618
619 Insert into ota_act_cat_inclusions
620 ( activity_category
621 ,activity_version_id
622 ,category_usage_id
623 ,object_version_number
624 ,primary_flag
625 ) values
626 (null
627 ,l_activity_version_id
628 ,l_category_usage_id
629 ,1
630 ,l_primary_flag);
631 exception
632 when others then
633 l_err_code := SQLCODE;
634 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When creating Default Delivery Mode for Business group name');
635
636 add_log_entry( p_table_name=>'upgrade_attached_online_dms'
637 ,p_business_group_id => null
638 ,p_source_primary_key =>l_category_usage_id
639 ,p_object_value => l_category_usage_id
640 ,p_message_text => l_err_msg
641 ,p_upgrade_id => p_upgrade_id
642 ,p_process_date => get_process_date(UPGRADE_NAME,UPGRADE_NAME)
643 ,p_log_type => LOG_TYPE_E
644 ,p_upgrade_name => UPGRADE_NAME );
645 end;
646
647
648 end loop;
649 end loop;
650 end upgrade_attached_online_dms;
651 -- ----------------------------------------------------------------------------
652 -- |----------------------< upgrade_online_delivery_modes >-------------------|
653 -- ----------------------------------------------------------------------------
654 procedure upgrade_online_delivery_modes (p_upgrade_id in number default 1) is
655 begin
656 upgrade_online_del_modes( p_upgrade_id);
657 upgrade_non_online_attach_dms( p_upgrade_id);
658 upgrade_attached_online_dms( p_upgrade_id);
659 end upgrade_online_delivery_modes;
660
661
662 procedure create_root_ctg_and_dms is
663 Cursor Csr_Category_BG is
664 Select ctu.business_group_id
665 From Ota_Category_Usages ctu
666 Group by ctu.business_group_id ;
667
668 Cursor Csr_Bg_name(p_bg_id number) is
669 Select hou.name Bg_name
670 From hr_organization_units hou
671 Where hou.business_group_id = p_bg_id
672 and hou.business_group_id = hou.organization_id ;
673
674 l_default_dm Ota_Category_usages_tl.Category_usage_id%Type;
675 l_root_cat_usage_id ota_category_usages.category_usage_id%Type;
676 l_bg_name hr_organization_units.name%Type ;
677
678 begin
679
680 For Cat_Bg in csr_Category_bg Loop
681 Open Csr_bg_name(cat_bg.business_group_id);
682 Fetch csr_bg_name into l_bg_name;
683 If csr_bg_name%found then
684 Create_Root_Category(Cat_bg.Business_group_id,l_Bg_name,l_root_cat_usage_id);
685 Create_Default_DM(Cat_bg.business_group_id,l_default_dm);
686 End If;
687 Close csr_bg_name;
688 End Loop;
689 end create_root_ctg_and_dms;
690
691 -- ----------------------------------------------------------------------------
692 -- |---------------------------< Upgrade_Category >----------------------------|
693 -- ----------------------------------------------------------------------------
694 -- This procedure does the following :
695 -- 1. Updates ota_booking_deals.category with category_usage_id
696 -- 2. Updates ota_category_usages.Category with Meaning from
697 -- lookup table (earlier it stores lookup code)
698 -- 3. Populates the category translation table ota_category_usages_tl
699 -- 4. Migrates the customer defined lookup codes(not seeded) from FREQUNCY to
700 -- OTA_DURATION_UNITS
701
702 PROCEDURE Upgrade_Category(
703 p_process_control IN varchar2,
704 p_start_pkid IN number,
705 p_end_pkid IN number,
706 p_rows_processed OUT nocopy number,
707 p_update_id in number default 1 ) is
708
709 CURSOR csr_installed_languages is
710 SELECT lng.language_code,
711 lng.nls_Language
712 FROM fnd_languages lng
713 WHERE lng.installed_flag in ('I', 'B');
714
715
716
717
718 l_userenv_language_code FND_LANGUAGES.LANGUAGE_CODE%TYPE := userenv('LANG');
719 l_current_nls_language VARCHAR2(30);
720 l_current_language FND_LANGUAGES.LANGUAGE_CODE%TYPE ;
721 l_rows_processed number := 0;
722
723
724
725 l_err_code varchar2(72);
726 l_err_msg varchar2(2000);
727 l_upgrade_id ota_upgrade_log.upgrade_id%Type;
728 l_process_date ota_upgrade_log.process_date%Type;
729
730 begin
731
732 /* In Ota_booking_deals table Category field was the equivalent lookup_code
733 for the lookup_type 'ACTIVITY_CATEGORY'. Now Category_usage_id is being stored in this field
734 */
735 --Should this check be included here?
736 begin
737 UPDATE ota_booking_deals tbd
738 SET tbd.category = (select ocu.category_usage_id
739 from ota_category_usages ocu
740 where tbd.category = ocu.category
741 and tbd.business_group_id = ocu.business_group_id
742 and ocu.type = 'D')
743 Where exists (select ocu.category_usage_id
744 from ota_category_usages ocu
745 where tbd.category = ocu.category
746 and tbd.business_group_id = ocu.business_group_id
747 and ocu.type = 'D');
748 Exception
749 when others then
750 l_err_code := SQLCODE;
751 l_err_msg := nvl(substr(SQLERRM,1,2000),'When Updating Booking deals');
752
753 add_log_entry( p_table_name=>'UPDATE_BOOKING_DEALS'
754 ,p_source_primary_key => 1
755 ,p_object_value => 'No record in Booking Deals record updated with Category Usage id'
756 ,p_message_text => l_err_msg
757 ,p_upgrade_id => p_update_id
758 ,p_process_date => get_process_date(UPGRADE_NAME,UPGRADE_NAME)
759 ,p_log_type => LOG_TYPE_E
760 ,p_upgrade_name => UPGRADE_NAME );
761
762 end;
763
764 ota_mls_utility.set_session_language_code( l_userenv_language_code );
765
766 update ota_category_usages ocu
767 set ocu.category = (SELECT lkp.meaning
768 FROM hr_lookups lkp
769 WHERE lkp.lookup_code = ocu.category
770 AND lkp.lookup_type = 'ACTIVITY_CATEGORY')
771 ,Synchronous_flag = Decode(Type,'C',NULL,'DM','Y')
772 ,Online_Flag = Decode(Type,'C',NULL,'DM','N')
773 WHERE category_usage_id between p_start_pkid and p_end_pkid
774 AND exists (SELECT lkp.meaning
775 FROM hr_lookups lkp
776 WHERE lkp.lookup_code = ocu.category
777 AND lkp.lookup_type = 'ACTIVITY_CATEGORY') ;
778
779
780
781 /*
782 **
783 ** For each installed language insert a new record into the TL table for
784 ** each record in the range provided that is present in the base table.
785 */
786
787 for c_language in csr_installed_languages loop
788 begin
789 /*
790 ** Set language for iteration....
791 */
792 ota_mls_utility.set_session_nls_language(c_language.nls_language);
793 l_current_language := c_language.language_code;
794
795 /*
796 ** Insert the TL rows.
797 */
798
799 Insert into OTA_CATEGORY_USAGES_TL
800 (Category_usage_Id,
801 Language,
802 Category,
803 Description,
804 Source_Lang,
805 Created_By,
806 Creation_Date,
807 Last_Updated_By,
808 Last_Update_Date,
809 Last_Update_Login )
810 Select
811 M.Category_usage_Id,
812 L_Current_Language,
813 M.Category,
814 M.Category,
815 L_Userenv_language_code,
816 M.Created_By,
817 M.Creation_date,
818 M.Last_Updated_By,
819 M.Last_Update_Date,
820 M.Last_Update_Login
821 From OTA_CATEGORY_USAGES M
822 Where M.Category_usage_id Between P_start_pkid AND P_end_pkid
823 And Not Exists (Select '1'
824 From OTA_CATEGORY_USAGES_TL T
825 Where T.Category_usage_Id = M.Category_usage_Id
826 And T.Language = L_Current_Language ) ;
827
828
829 -- l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
830 Exception
831 when others then
832 l_err_code := SQLCODE;
833 l_err_msg := nvl(substr(SQLERRM,1,2000),'When creating category TL records');
834 add_log_entry( p_table_name =>'POPULATE_CTU_TL'
835 ,p_source_primary_key => L_current_language
836 ,p_object_value => 'No Category translated for language'||l_current_language
837 ,p_message_text => l_err_msg
838 ,p_upgrade_id => p_update_id
839 ,p_process_date => get_process_date(UPGRADE_NAME,UPGRADE_NAME)
840 ,p_log_type => LOG_TYPE_E
841 ,p_upgrade_name => UPGRADE_NAME) ;
842
843 end;
844 end loop;
845
846 ota_mls_utility.set_session_language_code( l_userenv_language_code );
847
848 Select Nvl(Count(Category_usage_id),0) into l_rows_processed from ota_category_usages
849 Where Category_usage_id between p_start_pkid and p_end_pkid ;
850
851 p_rows_processed := l_rows_processed;
852
853
854 Exception
855 --
856 When Others Then
857 --
858 ota_mls_utility.set_session_language_code( l_userenv_language_code );
859 --
860 -- Should be commented?
861 raise;
862
863 end Upgrade_Category;
864
865 -- ----------------------------------------------------------------------------
866 -- |---------------------< Upgrade_delivery_mode >----------------------------|
867 -- ----------------------------------------------------------------------------
868 -- This procedure creates the Activity typ for each category exists in
869 -- Ota_category_usages table.
870 PROCEDURE Upgrade_Delivery_Mode(p_update_id in number default 1 ) is
871
872 CURSOR csr_del_mode is
873 select ocu.category_usage_id, ocu.type,ocu.business_group_id,ocu.start_date_active,ocu.end_date_active
874 from ota_category_usages ocu
875 Where type = 'DM' or parent_cat_usage_id is not null ;
876 l_course_min_st_dt date;
877 l_course_max_end_dt date;
878 l_course_end_dt_has_null Varchar2(1) := 'N' ;
879 Begin
880
881 For dm in csr_del_mode Loop
882 l_course_min_st_dt := dm.start_date_active;
883 l_course_max_end_dt := dm.end_date_active;
884
885 Select Min(tav.Start_Date),Max(tav.End_date),Max(Decode(tav.End_date,NULL,'Y','N'))
886 into l_course_min_st_dt, l_course_max_end_dt,l_course_end_dt_has_null
887 From ota_activity_versions tav,ota_act_cat_inclusions aci
888 Where tav.activity_version_id = aci.activity_version_id
889 and aci.category_usage_id = dm.category_usage_id;
890
891 If l_course_end_dt_has_null = 'Y' then
892 l_course_max_end_dt := NULL ;
893 End if;
894
895 if l_course_min_st_dt is null then
896 l_course_min_st_dt := least (trunc(sysdate),nvl(dm.end_date_active,trunc(sysdate)));
897 end if;
898
899 If l_course_min_st_dt <= nvl(dm.start_date_active,l_course_min_st_dt) then
900 Update Ota_Category_Usages
901 Set Start_date_active = l_course_min_st_dt
902 Where Category_usage_id = dm.category_usage_id
903 and Business_group_id = dm.business_group_id ;
904 End If;
905
906 If (l_course_max_end_dt is NULL and dm.end_date_active is NOT NULL) or (l_course_max_end_dt > dm.end_date_active) then
907 Update Ota_Category_Usages
908 Set End_date_active = l_course_max_end_dt
909 Where Category_usage_id = dm.category_usage_id
910 and Business_group_id = dm.business_group_id ;
911 End If;
912
913
914 End Loop;
915
916 End Upgrade_Delivery_Mode ;
917 -- ----------------------------------------------------------------------------
918 -- |---------------------< set_primary_category >-----------------------------|
919 -- ----------------------------------------------------------------------------
920 -- For courses under p_act_id,
921 --1) create an act cat inlcusion record with p_category_usage_id,and primary flag
922 -- = 'Y' if no primary category exist. 'N', Otherwise.
923 --2) Update the activity_id for activity versions with the activity_id of the
924 -- corresponding to the primary category.
925 -- Called from Create_Category_for_Activity and Create_Activity_For_Category.
926 procedure set_primary_category ( p_act_id in number,p_business_group_id in number
927 ,p_category_usage_id in number
928 ,p_update_id in number default 1) is
929
930 CURSOR csr_activity_versions (p_act_id number)is
931 SELECT tav.activity_version_id
932 FROM OTA_ACTIVITY_VERSIONS TAV
933 WHERE tav.activity_id = p_act_id;
934
935 CURSOR csr_primary_present (p_act_ver_id number) is
936 SELECT ctu.category_usage_id
937 FROM ota_act_cat_inclusions cat,
938 ota_category_usages ctu
939 WHERE ctu.category_usage_id = cat.category_usage_id
940 AND ctu.type = 'C'
941 AND cat.primary_flag='Y'
942 AND cat.activity_version_id = p_act_ver_id;
943
944 cursor csr_equivalent_tad(p_ctu_id number) is
945 select activity_id
946 from ota_activity_definitions
947 where category_usage_id = p_ctu_id;
948
949 CURSOR csr_dup_act_cat (p_act_ver_id number, p_category_usage_id number) is
950 SELECT 1
951 FROM ota_act_cat_inclusions cat
952 WHERE cat.category_usage_id =p_category_usage_id
953 AND cat.activity_version_id = p_act_ver_id;
954
955
956 l_equivalent_tad ota_activity_definitions.activity_id%type;
957 l_equivalent_ctu ota_category_usages.category_usage_id%type;
958 l_act_ver_id ota_activity_versions.activity_version_id%type;
959 l_count number;
960 l_err_code varchar2(72);
961 l_err_msg varchar2(2000);
962
963 l_primary_flag varchar2(1);
964 begin
965 for c_get_act_ver in csr_activity_versions(p_act_id) loop
966
967 l_act_ver_id := c_get_act_ver.activity_version_id;
968 open csr_primary_present(l_act_ver_id);
969 fetch csr_primary_present into l_equivalent_ctu;
970 if csr_primary_present%notfound then
971 l_primary_flag := 'Y';
972 else
973 l_primary_flag := 'N';
974
975 -- If Primary category already exists, fetch the equivalent activity_id
976 -- from activity type and set it as activity_id for that course.
977 l_equivalent_tad := -1;
978 open csr_equivalent_tad(l_equivalent_ctu);
979 fetch csr_equivalent_tad into l_equivalent_tad;
980 if csr_equivalent_tad%found then
981 update Ota_Activity_Versions
982 set activity_id = l_equivalent_tad
983 where activity_version_id = l_act_ver_id;
984 end if;
985 close csr_equivalent_tad;
986
987 end if;
988 close csr_primary_present;
989
990 open csr_dup_act_cat(l_act_ver_id,p_category_usage_id);
991 fetch csr_dup_act_cat into l_count;
992 if csr_dup_act_cat%notfound then
993 begin
994 Insert into ota_act_cat_inclusions
995 ( activity_category
996 ,activity_version_id
997 ,category_usage_id
998 ,object_version_number
999 ,primary_flag
1000 ) values
1001 (null
1002 ,l_act_ver_id
1003 ,p_category_usage_id
1004 ,1
1005 ,l_primary_flag);
1006 Exception
1007 When others then
1008 l_err_code := SQLCODE;
1009 l_err_msg := nvl(substr(SQLERRM,1,2000),'When creating Activity Category Associations');
1010
1011 add_log_entry( p_table_name => 'CREATE_ACT_CAT_INCLUSIONS'
1012 ,p_source_primary_key => l_act_ver_id
1013 ,p_business_group_id => p_business_group_id
1014 ,p_object_value => 'Course Id : ' || l_act_ver_id
1015 ,p_message_text => l_err_msg
1016 ,p_upgrade_id => p_update_id
1017 ,p_process_date => get_process_date(UPGRADE_NAME,UPGRADE_NAME)
1018 ,p_log_type => LOG_TYPE_E
1019 ,p_upgrade_name => UPGRADE_NAME );
1020
1021 end;
1022
1023 elsif csr_dup_act_cat%found and l_primary_flag = 'Y' then
1024
1025 update ota_act_cat_inclusions
1026 set primary_flag = 'Y'
1027 where category_usage_id = p_category_usage_id
1028 and activity_version_id = l_act_ver_id;
1029
1030
1031 end if ;
1032 close csr_dup_act_cat;
1033
1034 end loop;
1035 End set_primary_category;
1036 -- ----------------------------------------------------------------------------
1037 -- |---------------------< update_act_ver_bg >-----------------------------|
1038 -- ----------------------------------------------------------------------------
1039
1040 procedure update_act_ver_bg( p_business_group_id in number
1041 ,p_activity_id in number)
1042 is
1043 begin
1044
1045 Update Ota_Activity_Versions
1046 Set Business_group_id = p_business_group_id
1047 Where Activity_id = p_activity_id
1048 and Business_group_id is NULL ;
1049
1050 end update_act_ver_bg;
1051 -- ----------------------------------------------------------------------------
1052 -- |---------------------< update_tad_cat_usg_id >--------------------------|
1053 -- ----------------------------------------------------------------------------
1054
1055 procedure update_tad_cat_usg_id( p_business_group_id in number
1056 ,p_activity_id in number
1057 ,p_category_usage_id in number)
1058 is
1059 begin
1060 Update Ota_activity_Definitions
1061 Set Category_usage_id = p_category_usage_id
1062 Where Business_group_id = p_business_group_id
1063 and Activity_id = p_activity_id ;
1064 end update_tad_cat_usg_id;
1065 -- ----------------------------------------------------------------------------
1066 -- |---------------------< Create_Activity_For_Category >---------------------|
1067 -- ----------------------------------------------------------------------------
1068 -- This procedure creates the Activity typ for each category exists in
1069 -- Ota_category_usages table.
1070 PROCEDURE Create_Activity_For_Category(
1071 p_process_control IN varchar2,
1072 p_start_pkid IN number,
1073 p_end_pkid IN number,
1074 p_rows_processed OUT nocopy number,
1075 p_update_id in number default 1 ) is
1076
1077
1078
1079 CURSOR c_activity_def is
1080 select ocu.category_usage_id, oct.category, ocu.business_group_id,oct.description
1081 from ota_category_usages ocu, ota_category_usages_tl oct
1082 where ocu.category_usage_id = oct.category_usage_id
1083 and oct.language = Userenv('LANG')
1084 and ocu.type = 'C'
1085 and ocu.category_usage_id between p_start_pkid and p_end_pkid
1086 and ocu. category_usage_id not in (select category_usage_id
1087 from ota_activity_definitions
1088 where category_usage_id is not null) ;
1089
1090 l_object_version_number number;
1091 l_category_usage_id number;
1092 l_activity_id number;
1093 l_category ota_category_usages_tl.category%type;
1094 l_business_group_id number;
1095 l_description ota_category_usages_tl.description%Type;
1096 l_rows_processed number := 0;
1097 l_err_code varchar2(72);
1098 l_err_msg varchar2(2000);
1099
1100 begin
1101 For c_activity IN c_activity_def loop
1102
1103 l_category_usage_id := c_activity.category_usage_id;
1104 l_category := c_activity.category;
1105 l_business_group_id := c_activity.business_group_id;
1106 l_description := c_activity.description;
1107
1108 Begin
1109 Select tad.Activity_id into l_activity_id
1110 from ota_activity_definitions tad, ota_activity_definitions_tl adt
1111 Where tad.Business_group_id = l_business_group_id
1112 and tad.activity_id = adt.activity_id
1113 and adt.Name = l_category
1114 -- and Category_usage_id is NULL
1115 Group by Tad.Activity_id ;
1116
1117 update_tad_cat_usg_id(l_business_group_id, l_activity_id,l_category_usage_id);
1118
1119 update_act_ver_bg(l_business_group_id, l_activity_id);
1120
1121 set_primary_category(l_activity_id,l_business_group_id,l_category_usage_id,p_update_id);
1122
1123 Exception
1124 When No_data_found then
1125 begin
1126 ota_tad_api.Ins
1127 (
1128 P_activity_id => l_activity_id
1129 ,P_business_group_id => l_business_group_id
1130 ,P_name => l_category
1131 ,p_description => l_description
1132 ,p_multiple_con_versions_flag => 'Y'
1133 ,P_object_version_number => l_object_version_number
1134 ,p_category_usage_id => l_category_usage_id
1135 ,P_validate => false
1136 );
1137 --
1138 ota_adt_ins.ins_tl
1139 (p_effective_date => sysdate
1140 ,p_language_code => USERENV('LANG')
1141 ,p_activity_id => l_activity_id
1142 ,p_name => l_category
1143 ,p_description => l_description);
1144
1145 l_rows_processed := l_rows_processed + 1;
1146 Exception
1147 when others then
1148 l_err_code := SQLCODE;
1149 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When creating Activity for category');
1150
1151 add_log_entry( p_table_name =>'CREATE_ACT_FOR_CTU'
1152 ,p_source_primary_key => l_category_usage_id
1153 ,p_business_group_id => l_business_group_id
1154 ,p_object_value => l_category
1155 ,p_message_text => l_err_msg
1156 ,p_upgrade_id => p_update_id
1157 ,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
1158 ,p_log_type => LOG_TYPE_E
1159 ,p_upgrade_name => UPGRADE_NAME);
1160 End;
1161 End ;
1162
1163 end loop;
1164
1165
1166
1167 end Create_Activity_for_Category;
1168
1169
1170
1171 -- ----------------------------------------------------------------------------
1172 -- |--------------------< Create_Category_for_Activity >----------------------|
1173 -- ----------------------------------------------------------------------------
1174 -- This procedure does the following
1175 -- 1. Creates a Category for each BG in ota_activity_definitions
1176 -- and ota_category_usages. And this new category will be the
1177 -- parent category for other categories(belongs to that BG).
1178 -- 2. Creates Category for each Activity types, which are not
1179 -- as part of step 3. Attaches the newly created category to
1180 -- Activity versions exist under the equivalent Activity type.
1181 -- If NO primary category specified for that Activity version,
1182 -- then newly created category will be the primary.
1183 PROCEDURE Create_Category_for_Activity(
1184 p_process_control IN varchar2,
1185 p_start_pkid IN number,
1186 p_end_pkid IN number,
1187 p_rows_processed OUT nocopy number,
1188 p_update_id in number default 1 )
1189 is
1190
1191 CURSOR csr_activity_type is
1192 SELECT tad.activity_id,tad.business_group_id,
1193 tad.name , tad.description,tad.Created_By,
1194 tad.Creation_Date,tad.Last_Updated_By,
1195 tad.Last_Update_Date,tad.Last_Update_Login,
1196 Comments
1197 ,TAD_INFORMATION_CATEGORY
1198 ,TAD_INFORMATION1
1199 ,TAD_INFORMATION2
1200 ,TAD_INFORMATION3
1201 ,TAD_INFORMATION4
1202 ,TAD_INFORMATION5
1203 ,TAD_INFORMATION6
1204 ,TAD_INFORMATION7
1205 ,TAD_INFORMATION8
1206 ,TAD_INFORMATION9
1207 ,TAD_INFORMATION10
1208 ,TAD_INFORMATION11
1209 ,TAD_INFORMATION12
1210 ,TAD_INFORMATION13
1211 ,TAD_INFORMATION14
1212 ,TAD_INFORMATION15
1213 ,TAD_INFORMATION16
1214 ,TAD_INFORMATION17
1215 ,TAD_INFORMATION18
1216 ,TAD_INFORMATION19
1217 ,TAD_INFORMATION20
1218 FROM ota_activity_definitions tad, ota_activity_definitions_tl adt
1219 WHERE tad.activity_id between p_start_pkid AND p_end_pkid
1220 and tad.activity_id = adt.activity_id
1221 and adt.language = Userenv('LANG')
1222 AND tad.category_usage_id is null ;
1223
1224
1225 CURSOR csr_installed_languages is
1226 SELECT lng.language_code, lng.nls_Language
1227 FROM fnd_languages lng
1228 WHERE lng.installed_flag in ('I', 'B');
1229
1230 CURSOR csr_duplicate_category (p_act_name varchar2, p_business_group_id number) is
1231 SELECT ocu.category_usage_id
1232 FROM ota_category_usages ocu, ota_category_usages_tl oct
1233 WHERE oct.category = p_act_name
1234 AND ocu.type ='C'
1235 AND ocu.category_usage_id = oct.category_usage_id
1236 AND ocu.business_group_id = p_business_group_id;
1237
1238
1239 cursor csr_min_start_date(p_act_id number) is
1240 select min(start_date)
1241 from ota_activity_versions
1242 where activity_id = p_act_id;
1243
1244
1245 l_object_version_number number;
1246 l_category_usage_id number;
1247 l_type varchar2(30) :='C';
1248 l_act_id number;
1249 l_business_group_id number;
1250 l_business_group_name varchar2(240);
1251 l_userenv_language_code FND_LANGUAGES.LANGUAGE_CODE%TYPE := userenv('LANG');
1252 l_current_nls_language VARCHAR2(30);
1253 l_current_language FND_LANGUAGES.LANGUAGE_CODE%TYPE ;
1254 l_parent_category_usage_id number;
1255 l_count number ;
1256 l_act_name ota_activity_definitions_tl.name%type;
1257 l_act_ver_id number;
1258
1259 l_rows_processed number := 0;
1260 l_err_code varchar2(72);
1261 l_err_msg varchar2(2000);
1262 l_min_course_start_date date;
1263 l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
1264 hr_dflex_utility.l_ignore_dfcode_varray();
1265 --l_default_dm Ota_Category_usages_tl.Category_usage_id%Type;
1266 --l_default_dm_name Hr_lookups.Meaning%Type ;
1267 --l_root_cat_usage_id ota_category_usages.category_usage_id%Type;
1268 --l_bg_name hr_organization_units.name%Type ;
1269 Begin
1270
1271 -- Ignore Category Dff Validation while creating Category from Activity.
1272 l_add_struct_d.extend(1);
1273 l_add_struct_d(l_add_struct_d.count) := 'OTA_CATEGORY_USAGES';
1274 hr_dflex_utility.create_ignore_df_validation(p_rec => l_add_struct_d);
1275
1276 for c_get_act in csr_activity_type loop
1277
1278 l_act_id := c_get_act.activity_id;
1279 l_business_group_id := c_get_act.business_group_id;
1280 l_act_name := c_get_act.name;
1281
1282 Begin
1283 Select Category_usage_id into l_parent_category_usage_id
1284 From Ota_category_usages
1285 Where business_group_id = l_business_group_id
1286 and parent_cat_usage_id is NULL
1287 and type = 'C';
1288 Exception
1289 When Others then
1290 -- when no_data_found then ?
1291 l_parent_category_usage_id := NULL;
1292 End ;
1293
1294
1295 L_CATEGORY_USAGE_ID := NULL;
1296 l_object_version_number := NULL;
1297 open csr_duplicate_category(l_act_name, l_business_group_id);
1298 fetch csr_duplicate_category into l_category_usage_id;
1299 if csr_duplicate_category%notfound then
1300
1301 ota_mls_utility.set_session_language_code( l_userenv_language_code );
1302 --
1303 begin
1304
1305 l_min_course_start_date := null;
1306 open csr_min_start_date(l_act_id);
1307 fetch csr_min_start_date into l_min_course_start_date;
1308 close csr_min_start_date;
1309
1310
1311
1312 ota_ctu_ins.ins( p_effective_date =>trunc(sysdate)
1313 ,p_business_group_id => l_business_group_id
1314 ,p_category => c_get_act.name
1315 ,p_type => l_type
1316 ,p_parent_cat_usage_id => l_parent_category_usage_id
1317 ,p_synchronous_flag => null
1318 ,p_online_flag => null
1319 ,p_start_date_active => nvl(l_min_course_start_date,trunc(c_get_act.Creation_Date))
1320 ,p_category_usage_id => l_category_usage_id
1321 ,p_object_version_number => l_object_version_number
1322 ,p_comments => c_get_act.comments
1323 ,P_ATTRIBUTE_CATEGORY => c_get_act.tad_information_Category
1324 ,P_ATTRIBUTE1 => c_get_act.tad_information1
1325 ,P_ATTRIBUTE2 => c_get_act.tad_information2
1326 ,P_ATTRIBUTE3 => c_get_act.tad_information3
1327 ,P_ATTRIBUTE4 => c_get_act.tad_information4
1328 ,P_ATTRIBUTE5 => c_get_act.tad_information5
1329 ,P_ATTRIBUTE6 => c_get_act.tad_information6
1330 ,P_ATTRIBUTE7 => c_get_act.tad_information7
1331 ,P_ATTRIBUTE8 => c_get_act.tad_information8
1332 ,P_ATTRIBUTE9 => c_get_act.tad_information9
1333 ,P_ATTRIBUTE10 => c_get_act.tad_information10
1334 ,P_ATTRIBUTE11 => c_get_act.tad_information11
1335 ,P_ATTRIBUTE12 => c_get_act.tad_information12
1336 ,P_ATTRIBUTE13 => c_get_act.tad_information13
1337 ,P_ATTRIBUTE14 => c_get_act.tad_information14
1338 ,P_ATTRIBUTE15 => c_get_act.tad_information15
1339 ,P_ATTRIBUTE16 => c_get_act.tad_information16
1340 ,P_ATTRIBUTE17 => c_get_act.tad_information17
1341 ,P_ATTRIBUTE18 => c_get_act.tad_information18
1342 ,P_ATTRIBUTE19 => c_get_act.tad_information19
1343 ,P_ATTRIBUTE20 => c_get_act.tad_information20
1344
1345 );
1346
1347 Insert into ota_category_usages_tl
1348 (Category_usage_Id,
1349 Language,
1350 Category,
1351 Description,
1352 Source_Lang,
1353 Created_By,
1354 Creation_Date,
1355 Last_Updated_By,
1356 Last_Update_Date,
1357 Last_Update_Login )
1358 Select l_category_usage_id,
1359 M.language,
1360 M.name,
1361 M.description,
1362 M.source_lang,
1363 M.Created_By,
1364 M.Creation_date,
1365 M.Last_Updated_By,
1366 M.Last_Update_Date,
1367 M.Last_Update_Login
1368 From Ota_activity_definitions_tl M
1369 Where M.activity_id = l_act_id ;
1370 Exception
1371 when others then
1372 l_err_code := SQLCODE;
1373 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When creating Category for Activity');
1374
1375 add_log_entry( p_table_name=>'CREATE_CTU_FOR_ACT'
1376 ,p_source_primary_key =>l_act_id
1377 ,p_business_group_id => l_business_group_id
1378 ,p_object_value => c_get_act.name
1379 ,p_message_text =>l_err_msg
1380 ,p_upgrade_id => p_update_id
1381 ,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
1382 ,p_log_type => LOG_TYPE_E
1383 ,p_upgrade_name => UPGRADE_NAME);
1384
1385 End;
1386 end if;
1387 close csr_duplicate_category;
1388
1389 update_act_ver_bg(l_business_group_id, l_act_id);
1390
1391 if l_category_usage_id is not null then
1392
1393 update_tad_cat_usg_id(l_business_group_id, l_act_id,l_category_usage_id);
1394
1395 set_primary_category(l_act_id,l_business_group_id,l_category_usage_id,p_update_id);
1396
1397 end if;
1398
1399 end loop;
1400 hr_dflex_utility.remove_ignore_df_validation;
1401 select nvl(count(1),0)
1402 into l_rows_processed
1403 from ota_activity_definitions
1404 where activity_id between p_start_pkid and p_end_pkid;
1405
1406 p_rows_processed := l_rows_processed;
1407
1408
1409 End Create_Category_for_Activity;
1410
1411
1412 -- ----------------------------------------------------------------------------
1413 -- |--------------------------< Create_Offering >-----------------------------|
1414 -- ----------------------------------------------------------------------------
1415 -- This procedure creates the Offering based on the records exists in ota_events
1416 -- and ota_activity_versions table.
1417 PROCEDURE Create_Offering(
1418 p_process_control IN varchar2,
1419 p_start_pkid IN number,
1420 p_end_pkid IN number,
1421 p_rows_processed OUT nocopy number,
1422 p_update_id in number default 1 ) is
1423
1424 l_dm_id number;
1425 l_act_ver_id number;
1426 l_offering_id number;
1427 l_object_version_number number;
1428 l_lang_id number := 0;
1429 l_base_lang_id fnd_languages.language_id%Type ;
1430 l_language_id number ;
1431 l_competence_element_id number;
1432 l_parent_offering_id ota_offerings.Offering_id%type;
1433 l_vendor_id number;
1434 l_supplier_id number;
1435 l_rows_processed number := 0;
1436 l_default_dm ota_category_usages.category_usage_id%Type ;
1437 l_resource_usage_id number;
1438 l_duration_units ota_offerings.duration_units%Type;
1439 l_duration ota_offerings.duration%Type;
1440 l_default_language Fnd_languages.Language_id%Type;
1441
1442 l_language_code fnd_natural_languages.language_code%type;
1443
1444 l_off_dup_lang number;
1445
1446 CURSOR c_get_ids is
1447 SELECT tav.activity_version_id
1448 ,tad.BUSINESS_GROUP_ID
1449 ,tav.version_name
1450 ,tav.start_date
1451 ,tav.end_date
1452 ,tav.duration
1453 ,tav.description
1454 ,tav.language_id
1455 ,tav.duration_units
1456 ,tav.maximum_attendees
1457 ,tav.maximum_internal_attendees
1458 ,tav.minimum_attendees
1459 ,tav.actual_cost
1460 ,tav.budget_cost
1461 ,tav.budget_currency_code
1462 ,tav.vendor_id
1463 ,tav.rco_id
1464 FROM ota_activity_versions_vl tav, ota_activity_definitions tad
1465 WHERE tav.activity_id =tad.activity_id
1466 AND tav.activity_version_id between p_start_pkid and p_end_pkid ;
1467
1468 CURSOR c_get_dm(p_act_ver_id number)is
1469 SELECT aci.CATEGORY_USAGE_ID
1470 FROM ota_act_cat_inclusions aci ,
1471 OTA_CATEGORY_USAGES TCU
1472 WHERE ACI.CATEGORY_USAGE_ID = TCU.CATEGORY_USAGE_ID
1473 AND TCU.TYPE = 'DM'
1474 AND primary_flag = 'Y'
1475 AND activity_version_id = p_act_ver_id;
1476
1477 CURSOR c_get_dm1(p_act_ver_id number)is
1478 SELECT aci.CATEGORY_USAGE_ID
1479 FROM ota_act_cat_inclusions aci ,
1480 OTA_CATEGORY_USAGES TCU
1481 WHERE ACI.CATEGORY_USAGE_ID = TCU.CATEGORY_USAGE_ID
1482 AND rownum = 1
1483 AND TCU.TYPE = 'DM'
1484 And Aci.activity_Version_id = p_act_ver_id;
1485
1486
1487 CURSOR c_get_lang(p_act_ver_id number) is
1488 SELECT language_id, decode(event_type,'SELFPACED','SELFPACED','SCHEDULED') event_type
1489 FROM ota_events
1490 WHERE activity_version_id = p_act_ver_id
1491 AND parent_offering_id is NULL
1492 Group by Language_id , decode(event_type,'SELFPACED','SELFPACED','SCHEDULED');
1493
1494
1495 CURSOR c_dup_off(p_name varchar, p_act_ver_id number) is
1496 SELECT Oft.Offering_id FROM ota_offerings off, ota_offerings_tl oft
1497 WHERE off.offering_id = oft.offering_id
1498 AND oft.name = p_name
1499 AND oft.language = USERENV('LANG')
1500 AND off.activity_version_id = p_act_ver_id;
1501
1502 cursor c_def_dm_for_bg(l_bg_id number) is
1503 Select ocu.category_usage_id from
1504 Ota_category_usages_vl ocu, hr_lookups lkp
1505 Where Ocu.Category = Meaning
1506 and lkp.Lookup_type = 'ACTIVITY_CATEGORY'
1507 and lkp.lookup_code = 'INCLASS'
1508 and ocu.type = 'DM'
1509 and ocu.business_group_id = l_bg_id;
1510 cursor c_dm_name (l_dm_id number) is
1511 select category
1512 from ota_category_usages
1513 where type='DM'
1514 and category_usage_id = l_dm_id;
1515
1516 cursor c_get_tav_dms(p_tav_id number) is
1517 select ctu.category_usage_id,
1518 ctu.category
1519 from ota_act_cat_inclusions aci ,
1520 ota_category_usages_vl ctu
1521 where aci.activity_version_id = p_tav_id
1522 and ctu.category_usage_id = aci.category_usage_id
1523 and ctu.type = 'DM';
1524
1525 cursor c_lang_code_for_langid(p_lang_id number) is
1526 select b.language_code LanguageCode
1527 from (
1528 SELECT fl.language_id, fnl.language_code FROM fnd_languages fl, fnd_natural_languages fnl
1529 WHERE fl.iso_language_3 = UPPER(fnl.iso_language_3)
1530 AND fl.iso_territory = fnl.iso_territory ) a,
1531 ota_natural_languages_v b
1532 where a.language_code(+) = b.language_code
1533 and a.language_id = p_lang_id;
1534
1535 l_fnd_lang_code fnd_natural_languages.language_code%type;
1536
1537 l_new_dm_name ota_category_usages.category%type;
1538
1539
1540 -- Changed Table type declaration for 3389869 as multidimesion table declaration is not supported in 8.1.7
1541 -- Type Event_tab is Table of c_get_lang%RowType INDEX BY BINARY_INTEGER;
1542 Type Event_tab is Table of Ota_Events.Language_id%Type INDEX BY BINARY_INTEGER;
1543 Offering_RecSet Event_tab;
1544 Type Event_type_tab is Table of Ota_Events.event_type%Type INDEX BY BINARY_INTEGER;
1545 Offering_evt_set Event_type_tab;
1546
1547 l_event_exists Varchar2(1) ;
1548 l_res_usg_cnt Number(5) ;
1549 l_cmp_cnt Number(5) ;
1550 l_evt_language_id Ota_Events.Language_id%Type ;
1551 l_learning_object_id ota_offerings.learning_object_id%type;
1552 l_iteration Number(5) ;
1553
1554 l_err_code varchar2(72);
1555 l_err_msg varchar2(2000);
1556 b_act_ver_migrated boolean;
1557
1558 Begin
1559
1560 Select lng.Language_id into l_default_language
1561 From Fnd_Languages lng
1562 Where lng.Installed_Flag = 'B' ;
1563
1564 For ids in c_get_ids loop
1565
1566 l_act_ver_id:= Ids.activity_version_id;
1567
1568
1569 -- If NO language specified at course, then initialize it with 'B'ase language
1570 -- This will be overwritten, if any language specified at Event(Class).
1571 l_language_id := Nvl(Ids.language_id,l_default_language);
1572 l_dm_id := null;
1573 l_iteration := 0;
1574 l_event_exists := 'N';
1575 l_res_usg_cnt := 0;
1576 l_cmp_cnt := 0;
1577 l_evt_language_id := NULL ;
1578 b_act_ver_migrated := true;
1579
1580 l_learning_object_id := Ids.rco_id;
1581
1582 if l_learning_object_id is not null then
1583 l_learning_object_id := -1;
1584 end if;
1585
1586 If (ids.duration is Null or ids.duration_units is NULL) then
1587 l_duration_units := NULL;
1588 l_duration := NULL;
1589 Else
1590 l_duration_units := Ids.Duration_units ;
1591 l_duration := Ids.Duration ;
1592 End if;
1593
1594 -- Setting the dm for the offering to be created.
1595 --1) if there's a dm with primary_flag=Y
1596 --2) else, any DM attached to activity(rowmnum=1)
1597 --3) else, Default DM of the BG
1598 open c_get_dm(l_act_ver_id);
1599 fetch c_get_dm into l_dm_id;
1600 If l_dm_id is null then
1601 Open c_get_dm1(l_act_ver_id);
1602 Fetch c_get_dm1 into l_dm_id;
1603 Close c_get_dm1;
1604 End if;
1605
1606 if l_dm_id is null then
1607 open c_def_dm_for_bg(ids.business_group_id);
1608 fetch c_def_dm_for_bg into l_dm_id;
1609 close c_def_dm_for_bg;
1610 end if;
1611
1612 Close c_get_dm;
1613
1614
1615 Open c_get_lang(l_act_ver_id);
1616 Fetch c_get_lang Bulk COllect into Offering_RecSet,Offering_evt_set ;
1617 -- If c_get_lang%Found then
1618 l_iteration := Offering_RecSet.Count() ;
1619 If Nvl(l_iteration,0) > 0 then
1620 l_event_exists := 'Y' ;
1621 Else
1622 l_event_exists := 'N' ;
1623
1624 Select count(Resource_Usage_id) into l_res_usg_cnt
1625 From Ota_Resource_Usages
1626 Where Activity_Version_id = l_act_ver_id ;
1627 If Nvl(l_res_usg_cnt,0) = 0 then
1628 Select count(Competence_element_id) into l_cmp_cnt
1629 From Per_Competence_Elements
1630 Where Type = 'TRAINER'
1631 and Object_id = l_act_ver_id ;
1632 End If;
1633 If Nvl(l_res_usg_cnt,0) > 0 OR Nvl(l_cmp_cnt,0) > 0 then
1634 l_iteration := 1 ;
1635 Else
1636 l_iteration := 0;
1637 End If;
1638 End If;
1639 Close c_get_lang;
1640 -- For Evt_lang in 1 .. Offering_RecSet.Count() Loop
1641 For Evt_lang in 1 .. l_iteration Loop
1642
1643 l_parent_offering_id := NUll ; --4116886
1644 l_language_id := Nvl(Ids.language_id,l_default_language);
1645 If l_event_exists = 'Y' then
1646 -- If language specified at Event level,then use it to create offering
1647 If Offering_RecSet(Evt_lang) is Not NULL then -- commented for bug 3389869 .language_id is Not null then
1648 l_language_id := Offering_RecSet(Evt_lang) ; -- commented for bug 3389869 .language_id ;
1649 l_evt_language_id := Offering_RecSet(Evt_lang) ; -- commented for bug 3389869 .language_id ;
1650 Else
1651 l_evt_language_id := NULL ;
1652 ENd if;
1653
1654
1655 if ( Offering_evt_set(evt_lang) = 'SCHEDULED' and ids.rco_id is not null) then
1656 open c_dm_name(l_dm_id);
1657 fetch c_dm_name into l_new_dm_name;
1658 close c_dm_name;
1659 l_dm_id := create_delivery_mode(l_new_dm_name||DM_COPY_SUFFIX,'Y','N'
1660 ,ids.business_group_id,ids.start_date);
1661 end if;
1662
1663 Else
1664 l_evt_language_id := Null;
1665 End If;
1666
1667
1668 -- bug#4116886
1669 -- select nvl(count(1),0)
1670 select Max(offering_id)
1671 -- into l_off_dup_lang
1672 into l_parent_offering_id
1673 from ota_offerings
1674 where language_id = l_language_id
1675 and activity_version_id =l_act_ver_id;
1676
1677 -- enh2733966 fnd_natural_lang support --
1678 open c_lang_code_for_langid(l_language_id);
1679 fetch c_lang_code_for_langid into l_fnd_lang_code;
1680 close c_lang_code_for_langid;
1681 l_language_code := l_fnd_lang_code;
1682 -- enh2733966 fnd_natural_lang support --
1683
1684 --Open c_dup_off(Ids.version_name,l_act_ver_id);
1685 -- Fetch c_dup_off into l_parent_offering_id ;
1686 -- 4116886
1687
1688 --if c_dup_off%notfound and l_off_dup_lang <= 0 then
1689 if l_parent_offering_id is null then
1690
1691 begin
1692
1693
1694 Ota_off_ins.ins(
1695 p_effective_date =>trunc(sysdate),
1696 p_business_group_id =>nvl(Ids.business_group_id,0),
1697 p_name =>Ids.version_name,
1698 p_learning_object_id =>l_learning_object_id,
1699 p_start_date => Ids.Start_date,
1700 p_activity_version_id =>l_act_ver_id,
1701 p_end_date =>Ids.end_date,
1702 p_delivery_mode_id => l_dm_id,
1703 p_language_id => l_language_id,
1704 p_duration => l_duration,
1705 p_duration_units => l_duration_units,
1706 p_maximum_attendees =>Ids.maximum_attendees,
1707 p_maximum_internal_attendees =>Ids.maximum_internal_attendees,
1708 p_minimum_attendees =>Ids.minimum_attendees,
1709 p_actual_cost =>Ids.actual_cost,
1710 p_budget_cost =>Ids.budget_cost,
1711 p_budget_currency_code =>Ids.budget_currency_code,
1712 p_offering_id => l_offering_id,
1713 p_object_version_number => l_object_version_number,
1714 p_vendor_id => Ids.vendor_id,
1715 p_language_code => l_language_code -- enh2733966 fnd_natural_lang support --
1716 );
1717
1718 Insert into ota_offerings_tl
1719 (offering_Id,
1720 Language,
1721 name,
1722 Description,
1723 Source_Lang,
1724 Created_By,
1725 Creation_Date,
1726 Last_Updated_By,
1727 Last_Update_Date,
1728 Last_Update_Login )
1729 Select l_offering_id,
1730 M.language,
1731 decode(greatest(l_iteration,1) ,1,M.version_name,
1732 get_offering_name_with_lang(M.version_name,l_language_id,M.language)),
1733 M.description,
1734 M.source_lang,
1735 M.Created_By,
1736 M.Creation_date,
1737 M.Last_Updated_By,
1738 M.Last_Update_Date,
1739 M.Last_Update_Login
1740 From Ota_activity_versions_tl M
1741 Where M.activity_version_id = l_act_ver_id;
1742
1743
1744 begin
1745 Insert into ota_resource_usages
1746 (resource_usage_id
1747 ,supplied_resource_id
1748 ,activity_version_id
1749 ,object_version_number
1750 ,required_flag
1751 ,start_date
1752 ,comments
1753 ,end_date
1754 ,quantity
1755 ,resource_type
1756 ,role_to_play
1757 ,usage_reason
1758 ,rud_information_category
1759 ,rud_information1
1760 ,rud_information2
1761 ,rud_information3
1762 ,rud_information4
1763 ,rud_information5
1764 ,rud_information6
1765 ,rud_information7
1766 ,rud_information8
1767 ,rud_information9
1768 ,rud_information10
1769 ,rud_information11
1770 ,rud_information12
1771 ,rud_information13
1772 ,rud_information14
1773 ,rud_information15
1774 ,rud_information16
1775 ,rud_information17
1776 ,rud_information18
1777 ,rud_information19
1778 ,rud_information20
1779 ,offering_id )
1780 Select ota_resource_usages_s.nextval
1781 ,res.supplied_resource_id
1782 ,l_act_ver_id
1783 ,1
1784 ,res.required_flag
1785 ,res.start_date
1786 ,res.comments
1787 ,res.end_date
1788 ,res.quantity
1789 ,res.resource_type
1790 ,res.role_to_play
1791 ,res.usage_reason
1792 ,res.rud_information_category
1793 ,res.rud_information1
1794 ,res.rud_information2
1795 ,res.rud_information3
1796 ,res.rud_information4
1797 ,res.rud_information5
1798 ,res.rud_information6
1799 ,res.rud_information7
1800 ,res.rud_information8
1801 ,res.rud_information9
1802 ,res.rud_information10
1803 ,res.rud_information11
1804 ,res.rud_information12
1805 ,res.rud_information13
1806 ,res.rud_information14
1807 ,res.rud_information15
1808 ,res.rud_information16
1809 ,res.rud_information17
1810 ,res.rud_information18
1811 ,res.rud_information19
1812 ,res.rud_information20
1813 ,l_offering_id
1814 From ota_resource_usages res
1815 where res.activity_version_id = l_act_ver_id
1816 and Offering_id is NULL ;
1817 Exception
1818
1819 when others then
1820 b_act_ver_migrated := false;
1821 l_err_code := SQLCODE;
1822 l_err_msg := nvl(substr(SQLERRM,1,2000),'When Moving resources associated with this Activity');
1823 add_log_entry( p_table_name=>'MIGRATE_RES_USG'
1824 ,p_source_primary_key => l_act_ver_id ||'|'|| get_lang_code(l_evt_language_id,l_language_id)
1825 ,p_business_group_id => Ids.Business_group_id
1826 ,p_object_value => Ids.Version_name
1827 ,p_message_text => l_err_msg
1828 ,p_upgrade_id => p_update_id
1829 ,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
1830 ,p_log_type => LOG_TYPE_E
1831 ,p_upgrade_name => UPGRADE_NAME);
1832 end;
1833
1834
1835 begin
1836 Insert into PER_COMPETENCE_ELEMENTS
1837 (competence_element_id
1838 ,business_group_id
1839 ,object_version_number
1840 ,type
1841 ,competence_id
1842 ,member_competence_set_id
1843 ,proficiency_level_id
1844 ,effective_date_from
1845 ,effective_date_to
1846 ,object_id
1847 ,object_name)
1848 SELECT per_competence_elements_s.nextval
1849 ,nvl(Ids.business_group_id,0)
1850 ,1
1851 ,'OTA_OFFERING'
1852 ,cmp.competence_id
1853 ,cmp.member_competence_set_id
1854 ,cmp.proficiency_level_id
1855 ,cmp.effective_date_from
1856 ,cmp.effective_date_to
1857 ,l_offering_id
1858 ,'OTA'
1859 FROM PER_COMPETENCE_ELEMENTS cmp
1860 WHERE cmp.type = 'TRAINER'
1861 AND cmp.object_id = l_act_ver_id
1862 AND not exists (select 1 from per_competence_elements pce
1863 where pce.object_id = l_act_ver_id and type = 'OTA_OFFERING');
1864 Exception
1865
1866 when others then
1867 b_act_ver_migrated := false;
1868 l_err_code := SQLCODE;
1869 l_err_msg := nvl(substr(SQLERRM,1,2000),'When Moving Competencies associated with this Course');
1870 add_log_entry( p_table_name => 'MIGRATE_TRN_COMP'
1871 ,p_source_primary_key => l_act_ver_id ||'|'|| get_lang_code(l_evt_language_id,l_language_id)
1872 --get_lang_code(case when l_evt_language_id = NULL then l_language_id else l_evt_language_id end)
1873 ,p_business_group_id => Ids.Business_group_id
1874 ,p_object_value => Ids.version_name
1875 ,p_message_text => l_err_msg
1876 ,p_upgrade_id => p_update_id
1877 ,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
1878 ,p_log_type => LOG_TYPE_E
1879 ,p_upgrade_name => UPGRADE_NAME);
1880 end;
1881 -- Sets the Parent offering id .
1882
1883 Update Ota_events Evt
1884 Set Evt.Parent_Offering_id = l_offering_id
1885 Where Evt.activity_Version_id = Ids.Activity_Version_id
1886 -- and Nvl(Evt.language_id,0) = Decode(Offering_RecSet(Evt_lang).language_id,NULL,0,l_Language_id)
1887 and (
1888 (l_evt_language_id is NULL and Evt.language_id is NULL)
1889 OR
1890 Evt.language_id = l_evt_language_id
1891 )
1892
1893 -- and Nvl(Evt.language_id,0) = Decode(l_evt_language_id,NULL,Nvl(Evt.language_id,0),l_evt_language_id) commented for bug# 4139874
1894 and Evt.business_group_id = Ids.Business_group_id
1895 and Parent_Offering_id is NULL ;
1896
1897 Exception
1898 when others then
1899 b_act_ver_migrated := false;
1900 l_err_code := SQLCODE;
1901 l_err_msg := nvl(substr(SQLERRM,1,2000),'When creating Offerings from Activity Versions');
1902 add_log_entry( p_table_name => 'CREATE_OFFERING'
1903 ,p_source_primary_key => l_act_ver_id ||'|'|| get_lang_code(l_evt_language_id,l_language_id)
1904 ,p_business_group_id => Ids.Business_group_id
1905 ,p_object_value => Ids.version_name
1906 ,p_message_text => l_err_msg
1907 ,p_upgrade_id => p_update_id
1908 ,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
1909 ,p_log_type => LOG_TYPE_E
1910 ,p_upgrade_name => UPGRADE_NAME);
1911
1912 end;
1913 else
1914
1915 Update Ota_events Evt
1916 Set Evt.Parent_Offering_id = l_parent_offering_id
1917 Where Evt.activity_Version_id = Ids.Activity_Version_id
1918 -- and Nvl(Evt.language_id,0) = Decode(l_evt_language_id,NULL,Nvl(Evt.language_id,0),l_evt_language_id) Commented for bug# 4139874
1919 and (
1920 (l_evt_language_id is NULL and Evt.language_id is NULL)
1921 OR
1922 Evt.language_id = l_evt_language_id
1923 )
1924
1925 and Evt.business_group_id = Ids.Business_group_id
1926 and Parent_offering_id is NULL ;
1927 end if;
1928 --Close c_dup_off;
1929
1930
1931
1932 -- End loop;
1933
1934 End Loop; -- Events loop end here
1935
1936 -- Deleting all the unreferenced child entities for a particular
1937 -- activity version id.
1938 -- Only delete records if migration for activity version is successful.
1939 -- i.e. No errors encountered during migrating any of the entities.
1940 if b_act_ver_migrated then
1941 -- 1) Resource Booking
1942 delete from ota_resource_usages
1943 where activity_version_id = l_act_ver_id
1944 and offering_id is null;
1945
1946 --2) Delivery Modes
1947 -- Add Log entry to record delivery modes
1948 for csr_dm_list in c_get_tav_dms(l_act_ver_id) loop
1949 add_log_entry(p_upgrade_id=>p_update_id
1950 ,p_table_name =>substr('DM_DEL_INFO'||'|'||ids.activity_version_id,1,30)
1951 ,p_source_primary_key => csr_dm_list.category_usage_id
1952 ,p_object_value => ids.version_name
1953 ,p_message_text =>csr_dm_list.category
1954 ,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
1955 ,p_log_type => LOG_TYPE_I
1956 ,p_upgrade_name => UPGRADE_NAME);
1957 end loop;
1958
1959 -- Delete Delivery Mode
1960 delete from ota_act_cat_inclusions
1961 where activity_version_id = l_act_ver_id
1962 and category_usage_id in (select category_usage_id
1963 from ota_category_usages
1964 where type = 'DM');
1965
1966 --3) Trainer Competences
1967 delete from per_competence_elements
1968 where object_id = l_act_ver_id
1969 and type = 'TRAINER';
1970 end if;
1971
1972
1973 End loop; -- Activity Version loop ends here
1974
1975
1976 Select count(Activity_version_id) into l_rows_processed
1977 from ota_activity_versions
1978 Where activity_Version_id between p_start_pkid and p_end_pkid ;
1979
1980 p_rows_processed := l_rows_processed;
1981
1982 End Create_Offering;
1983 -- ----------------------------------------------------------------------------
1984 -- |--------------------------< get_apps_timezone >---------------------------|
1985 -- ----------------------------------------------------------------------------
1986 function get_apps_timezone(ila_tzone in varchar2) return varchar2 is
1987
1988 NOT_MAPPED constant varchar2(20) := 'NOT_MAPPED';
1989 TZ_DEFAULT constant varchar2(20) := null;
1990 type ila_timezone_type is table of ota_events.timezone%TYPE ;
1991 type fnd_timezone_type is table of ota_events.timezone%TYPE ;
1992 ila_timezones ila_timezone_type
1993 := ila_timezone_type( 'GMT' -- 'Pacific/Kwajalein'
1994 ,'SST' -- 'Pacific/Midway'
1995 ,'HST' -- NOT_MAPPED
1996 ,'AKDT' -- NOT_MAPPED
1997 ,'PDT' -- 'America/Los_Angeles'
1998 ,'MST' -- 'America/Denver'
1999 ,'CST' -- 'America/Chicago'
2000 ,'EST' -- 'America/New_York'
2001 ,'AST' -- 'Atlantic/Bermuda'
2002 ,'NDT' -- NOT_MAPPED
2003 ,'BRST' -- 'America/Buenos_Aires'
2004 ,'GST' -- NOT_MAPPED
2005 ,'AZOST' -- 'Atlantic/Azores'
2006 ,'WEST' -- 'GMT'
2007 ,'CEST' -- 'Europe/Amsterdam'
2008 ,'EET' -- 'Europe/Athens'
2009 ,'EET' -- 'Africa/Cairo'
2010 ,'EET' -- 'Europe/Riga'
2011 ,'EAT' -- 'Asia/Baghdad'
2012 ,'MSD' -- 'Europe/Moscow'
2013 ,'IRST' -- 'Asia/Tehran'
2014 ,'AMST' -- 'Asia/Muscat'
2015 ,'AFT' -- 'Asia/Kabul'
2016 ,'PKT' -- 'Asia/Karachi'
2017 ,'IST' -- 'Asia/Calcutta'
2018 ,'BDT' -- 'Asia/Almaty'
2019 ,'ICT' -- 'Asia/Bangkok'
2020 ,'CST' -- 'Asia/Hong_Kong'
2021 ,'CST' -- 'Asia/Singapore'
2022 ,'PWT' -- 'Asia/Tokyo'
2023 ,'PWT' -- 'Asia/Seoul'
2024 ,'CST' -- 'Australia/Adelaide'
2025 ,'TRUT' -- 'Australia/Brisbane'
2026 ,'TRUT' -- 'Australia/Melbourne'
2027 ,'TRUT' -- 'Australia/Hobart'
2028 ,'MAGST' -- 'Asia/Magadan'
2029 ,'WFT' -- 'Pacific/Auckland'
2030 ,'WFT'); --'Asia/Kamchatka'
2031
2032
2033 -- Mapping between iLearning and Java Timezone codes are as follows :
2034 --ILA JAVA
2035 --TZ TZ
2036 --=== ====
2037 --EK GMT
2038 --MIS SST
2039 --HAW HST
2040 --ALA AKDT
2041 --PST PDT
2042 --MST MST
2043 --CST CST
2044 --EST EST
2045 --AST AST
2046 --NWF NDT
2047 --BBA BRST
2048 --MAT GST
2049 --AZO AZOST
2050 --GMT WEST
2051 --AMS CEST
2052 --AIM EET
2053 --BCP EET
2054 --HRI EET
2055 --BKR EAT
2056 --MSV MSD
2057 --THE IRST
2058 --ABT AMST
2059 --KAB AFT
2060 --EIK PKT
2061 --BCD IST
2062 --ADC BDT
2063 --BHJ ICT
2064 --BHU CST
2065 --SST CST
2066 --OST PWT
2067 --SYA PWT
2068 --ADA CST
2069 --BGP TRUT
2070 --CMS TRUT
2071 --HVL TRUT
2072 --MSN MAGST
2073 --AWE WFT
2074 --FKM WFT
2075 --
2076
2077 fnd_timezones fnd_timezone_type
2078 := fnd_timezone_type
2079 ( 'Pacific/Kwajalein'
2080 ,'Pacific/Midway'
2081 ,'HST'
2082 ,'AKDT'
2083 ,'America/Los_Angeles'
2084 ,'America/Denver'
2085 ,'America/Chicago'
2086 ,'America/New_York'
2087 ,'Atlantic/Bermuda'
2088 ,'NDT'
2089 ,'America/Buenos_Aires'
2090 ,'GST'
2091 ,'Atlantic/Azores'
2092 ,'GMT'
2093 ,'Europe/Amsterdam'
2094 ,'Europe/Athens'
2095 ,'Africa/Cairo'
2096 ,'Europe/Riga'
2097 ,'Asia/Baghdad'
2098 ,'Europe/Moscow'
2099 ,'Asia/Tehran'
2100 ,'Asia/Muscat'
2101 ,'Asia/Kabul'
2102 ,'Asia/Karachi'
2103 ,'Asia/Calcutta'
2104 ,'Asia/Almaty'
2105 ,'Asia/Bangkok'
2106 ,'Asia/Hong_Kong'
2107 ,'Asia/Singapore'
2108 ,'Asia/Tokyo'
2109 ,'Asia/Seoul'
2110 ,'Australia/Adelaide'
2111 ,'Australia/Brisbane'
2112 ,'Australia/Melbourne'
2113 ,'Australia/Hobart'
2114 ,'Asia/Magadan'
2115 ,'Pacific/Auckland'
2116 ,'Asia/Kamchatka');
2117
2118 ila_i BINARY_INTEGER;
2119 fnd_i BINARY_INTEGER;
2120
2121 retval ota_events.timezone%TYPE := TZ_DEFAULT;
2122
2123 begin
2124
2125 if ( ila_tzone is null ) then
2126 return NULL;
2127 end if;
2128 -- Check for iLearning timezone, if match found return
2129 -- the corresponding FND_TIMEZONE.
2130 for ila_i in ila_timezones.FIRST..ila_timezones.LAST
2131 loop
2132 if (ila_timezones(ila_i) = ila_tzone ) then
2133 retval := fnd_timezones(ila_i);
2134 exit;
2135 end if;
2136 end loop;
2137
2138 -- If no match is found check for existance in FND_TIMEZONES
2139 -- if found, return the the same.
2140 if ( retval is null ) then
2141 for fnd_i in fnd_timezones.FIRST..fnd_timezones.LAST
2142 loop
2143 if ( fnd_timezones(fnd_i) = ila_tzone ) then
2144 retval := fnd_timezones(fnd_i);
2145 exit;
2146 end if;
2147 end loop;
2148 end if;
2149
2150 -- if no match is found in either iLearning timezone and
2151 -- FND_TIMEZONE or the iLearning Timezone is not mapped
2152 -- return NULL
2153 if ( retval = NOT_MAPPED ) then
2154 retval := TZ_DEFAULT;
2155 end if;
2156
2157 return retval;
2158
2159 end get_apps_timezone;
2160
2161
2162 -- ----------------------------------------------------------------------------
2163 -- |--------------------------< Upgrade_Event_Associations >------------------|
2164 -- ----------------------------------------------------------------------------
2165 Procedure Upgrade_Event_Associations(
2166 p_process_control IN varchar2,
2167 p_start_pkid IN number,
2168 p_end_pkid IN number,
2169 p_rows_processed OUT nocopy number,
2170 p_update_id in number default 1 ) is
2171
2172 l_rows_processed number;
2173 begin
2174 update OTA_EVENT_ASSOCIATIONS
2175 set self_enrollment_flag = 'N'
2176 where event_association_id between p_start_pkid and p_end_pkid
2177 and self_enrollment_flag is null -- Bug#6804783
2178 and ( customer_id is not null
2179 or job_id is not null
2180 or organization_id is not null
2181 or position_id is not null);
2182
2183
2184 select nvl(count(event_association_id),0)
2185 into l_rows_processed
2186 from OTA_EVENT_ASSOCIATIONS
2187 where event_association_id between p_start_pkid and p_end_pkid ;
2188
2189 p_rows_processed := l_rows_processed;
2190
2191
2192 end Upgrade_Event_Associations;
2193 -- ----------------------------------------------------------------------------
2194 -- |--------------------------< Upgrade_Events >------------------------------|
2195 -- ----------------------------------------------------------------------------
2196 Procedure Upgrade_Events( p_process_control IN varchar2,
2197 p_start_pkid IN number,
2198 p_end_pkid IN number,
2199 p_rows_processed OUT nocopy number,
2200 p_update_id in number default 1 ) is
2201
2202 l_rows_processed number := 0;
2203 begin
2204 -- Upgrade OM Events
2205 -- 1) set book_independent_flag to N iff null
2206 -- 2) Maximum_internal_attendees to 0 for price basis in 'C' or 'O'
2207 update OTA_EVENTS
2208 set book_independent_flag = nvl(book_independent_flag,'N'),
2209 Maximum_internal_attendees = Decode(Price_basis,'C',0,'O',0,Maximum_internal_attendees)
2210 where event_id between p_start_pkid and p_end_pkid;
2211
2212
2213 -- Update TIMEZONE for iLearning imported events to
2214 -- the corresponding APPS (FND_TIMEZONES_VL) timezone code.
2215 update OTA_EVENTS
2216 set TIMEZONE = get_apps_timezone(timezone)
2217 where offering_id is not null --iLearning imported events.
2218 and event_id between p_start_pkid and p_end_pkid
2219 and TIMEZONE is NOT NULL;
2220
2221 select nvl(count(event_id),0)
2222 into l_rows_processed
2223 from ota_events
2224 where event_id between p_start_pkid and p_end_pkid ;
2225
2226 p_rows_processed := l_rows_processed;
2227 end Upgrade_Events;
2228
2229 --enh 2733966 --
2230 -- ----------------------------------------------------------------------------
2231 -- |--------------------------< Upgrade_Off_Lang_Code >-----------------------|
2232 -- ----------------------------------------------------------------------------
2233 -- This procedure populates Language_code in OTA_OFFERINGS if it is null.
2234
2235 Procedure Upgrade_Off_Lang_Code is
2236
2237 begin
2238 update ota_offerings a
2239 set a.language_code=decode(a.language_code, null, (select fnl.language_code
2240 FROM fnd_languages fl,
2241 fnd_natural_languages fnl WHERE
2242 fl.iso_language_3 = UPPER(fnl.iso_language_3) AND fl.iso_territory =
2243 fnl.iso_territory and fl.language_id=a.language_id),a.language_code);
2244
2245
2246 end Upgrade_Off_Lang_Code;
2247
2248 --enh 2733966 --
2249 -- ----------------------------------------------------------------------------
2250 -- |--------------------------< Upgrade_LO_Lang_Code >-----------------------|
2251 -- ----------------------------------------------------------------------------
2252 -- This procedure populates Language_code in OTA_LEARNING_OBJECTS if it is null.
2253
2254 Procedure Upgrade_LO_Lang_Code is
2255
2256 begin
2257 update ota_learning_objects a
2258 set a.language_code=decode(a.language_code, null, (select fnl.language_code
2259 FROM fnd_languages fl,
2260 fnd_natural_languages fnl WHERE
2261 fl.iso_language_3 = UPPER(fnl.iso_language_3) AND fl.iso_territory =
2262 fnl.iso_territory and fl.language_id=a.language_id),a.language_code);
2263
2264 end Upgrade_LO_Lang_Code;
2265
2266 --enh 2733966 --
2267 -- ----------------------------------------------------------------------------
2268 -- |--------------------------< Upgrade_Comp_Lang_Code >-----------------------|
2269 -- ----------------------------------------------------------------------------
2270 -- This procedure populates Language_code in OTA_COMPETENCE_LANGUAGES if it is null.
2271
2272 Procedure Upgrade_Comp_Lang_Code is
2273
2274 begin
2275 update ota_competence_languages a
2276 set a.language_code=decode(a.language_code, null, (select fnl.language_code
2277 FROM fnd_languages fl,
2278 fnd_natural_languages fnl WHERE
2279 fl.iso_language_3 = UPPER(fnl.iso_language_3) AND fl.iso_territory =
2280 fnl.iso_territory and fl.language_id=a.language_id),a.language_code);
2281
2282 end Upgrade_Comp_Lang_Code;
2283
2284 --enh 2733966 --
2285 -- ----------------------------------------------------------------------------
2286 -- |--------------------------< Upgrade_Language_Code >----------------------------|
2287 -- ----------------------------------------------------------------------------
2288 -- This procedure checks if Language_Code is null in OTA_OFFERINGS, OTA_LEARNING_OBJECTS
2289 -- and OTA_COMPETENCE_LANGUAGES and populate it based on Langauge_Id
2290 Procedure Upgrade_Language_Code is
2291
2292 cursor c_exist_off is
2293 select 1
2294 from ota_offerings
2295 where language_code is null;
2296
2297 cursor c_exist_lo is
2298 select 1
2299 from ota_learning_objects
2300 where language_code is null;
2301
2302 cursor c_exist_comp is
2303 select 1
2304 from ota_competence_languages
2305 where language_code is null;
2306
2307
2308 l_exists_off boolean := false;
2309 l_exists_lo boolean := false;
2310 l_exists_comp boolean := false;
2311 l_upg_id number;
2312 l_ret number ;
2313 l_err_code varchar2(72);
2314 l_err_msg varchar2(2000);
2315
2316 begin
2317 l_upg_id := get_next_upgrade_id;
2318
2319 add_log_entry( p_table_name => 'UPG_LANGUAGE_CODE'
2320 ,p_source_primary_key => '-1'
2321 ,p_object_value => ''
2322 ,p_message_text => 'Starting LanguageCode upgrade'
2323 ,p_upgrade_id => l_upg_id
2324 ,p_process_date => get_process_date(l_upg_id,'UPG_LANGUAGE_CODE')
2325 ,p_log_type => LOG_TYPE_N
2326 ,p_upgrade_name => 'UPG_LANGUAGE_CODE');
2327
2328 open c_exist_off;
2329 fetch c_exist_off into l_ret;
2330 if c_exist_off%FOUND then
2331 l_exists_off := true;
2332 end if;
2333 close c_exist_off;
2334
2335 if l_exists_off then
2336
2337 begin
2338 upgrade_off_lang_code;
2339 exception
2340 when others then
2341
2342 l_err_code := SQLCODE;
2343 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When upgrading Language code for OTA_OFFERINGS');
2344
2345 add_log_entry( p_table_name => 'UPG_LANGUAGE_CODE'
2346 ,p_source_primary_key => '999'
2347 ,p_object_value => 'No Language code updated for OTA_OFFERINGS'
2348 ,p_message_text => l_err_msg
2349 ,p_upgrade_id => l_upg_id
2350 ,p_process_date => get_process_date(l_upg_id,'UPG_LANGUAGE_CODE')
2351 ,p_log_type => LOG_TYPE_E
2352 ,p_upgrade_name => 'UPG_LANGUAGE_CODE');
2353 end;
2354
2355
2356 end if; -- end offering upgrade
2357
2358
2359
2360
2361 open c_exist_lo;
2362 fetch c_exist_lo into l_ret;
2363 if c_exist_lo%FOUND then
2364 l_exists_lo := true;
2365 end if;
2366 close c_exist_lo;
2367
2368 if l_exists_lo then
2369
2370 begin
2371 upgrade_lo_lang_code;
2372 exception
2373 when others then
2374
2375 l_err_code := SQLCODE;
2376 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When upgrading Language code for OTA_LEARNING_OBJECTS');
2377
2378 add_log_entry( p_table_name => 'UPG_LANGUAGE_CODE'
2379 ,p_source_primary_key => '9999'
2380 ,p_object_value => 'No Language code updated for OTA_LEARNING_OBJETCS'
2381 ,p_message_text => l_err_msg
2382 ,p_upgrade_id => l_upg_id
2383 ,p_process_date => get_process_date(l_upg_id,'UPG_LANGUAGE_CODE')
2384 ,p_log_type => LOG_TYPE_E
2385 ,p_upgrade_name => 'UPG_LANGUAGE_CODE');
2386 end;
2387
2388
2389 end if; -- end learning_objects upgrade
2390
2391
2392 open c_exist_comp;
2393 fetch c_exist_comp into l_ret;
2394 if c_exist_comp%FOUND then
2395 l_exists_comp := true;
2396 end if;
2397 close c_exist_comp;
2398
2399 if l_exists_comp then
2400 begin
2401 upgrade_comp_lang_code;
2402 exception
2403 when others then
2404
2405 l_err_code := SQLCODE;
2406 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When upgrading Language code for OTA_COMPETENCE_LANGUAGES');
2407
2408 add_log_entry( p_table_name => 'UPG_LANGUAGE_CODE'
2409 ,p_source_primary_key => '99999'
2410 ,p_object_value => 'No Language code updated for OTA_COMPETENCE_LANGUAGES'
2411 ,p_message_text => l_err_msg
2412 ,p_upgrade_id => l_upg_id
2413 ,p_process_date => get_process_date(l_upg_id,'UPG_LANGUAGE_CODE')
2414 ,p_log_type => LOG_TYPE_E
2415 ,p_upgrade_name => 'UPG_LANGUAGE_CODE');
2416 end;
2417
2418 end if;
2419
2420 end Upgrade_Language_Code;
2421
2422
2423 -- ----------------------------------------------------------------------------
2424 -- |--------------------------< Add_Log_Entry >-------------------------------|
2425 -- ----------------------------------------------------------------------------
2426 Procedure add_log_entry(p_upgrade_id in number
2427 ,p_table_name in varchar2
2428 ,p_business_group_id in number default null
2429 ,p_source_primary_key in varchar2
2430 ,p_object_value in varchar2 default null
2431 ,p_message_text in varchar2 default null
2432 ,p_process_date in date
2433 ,p_log_type in varchar2 default null
2434 ,p_upgrade_name in varchar2 default null) is
2435
2436 l_upgrade_id number;
2437 l_err_code varchar2(72);
2438 l_err_msg varchar2(2000);
2439
2440 begin
2441
2442 Insert into ota_upgrade_log(upgrade_id,table_name,business_group_id,source_primary_key,object_value,message_text,process_date,log_type,upgrade_name)
2443 Values(p_upgrade_id,p_table_name,p_business_group_id,p_source_primary_key,p_object_value,p_message_text,p_process_date,p_log_type,p_upgrade_name) ;
2444
2445 Exception
2446 When Others then
2447 Select Nvl(Max(source_primary_key),0)+1 into l_upgrade_id
2448 From Ota_Upgrade_Log
2449 Where Table_name = 'OTA_UPGRADE_LOG' ;
2450
2451 l_err_code := SQLCODE;
2452 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When trapping Logging errors');
2453 Begin
2454 Insert into ota_upgrade_log(upgrade_id,table_name,business_group_id,source_primary_key,object_value,message_text,process_date,Target_primary_key,log_type,upgrade_name)
2455 Values(p_upgrade_id,'OTA_UPGRADE_LOG',p_business_group_id,l_upgrade_id,p_table_name,p_message_text,p_process_date,p_source_primary_key,p_log_type,p_upgrade_name) ;
2456 Exception
2457 When Dup_val_on_index then
2458 Null;
2459 End ;
2460 End;
2461 -- ----------------------------------------------------------------------------
2462 -- |--------------------------< Migrate_Lookup >-----------------------------------|
2463 -- ----------------------------------------------------------------------------
2464 Procedure Migrate_Lookup(p_update_id in number default 1 ) is
2465 l_err_code varchar2(72);
2466 l_err_msg varchar2(2000);
2467 begin
2468 -- Migrates User defined lookup codes defined under 'FREQUENCY' to 'OTA_DURATION_UNITS'
2469 --
2470 begin
2471 Insert into FND_LOOKUP_VALUES
2472 (LOOKUP_TYPE,LANGUAGE,LOOKUP_CODE,MEANING,DESCRIPTION,ENABLED_FLAG,START_DATE_ACTIVE,
2473 END_DATE_ACTIVE,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,SOURCE_LANG,
2474 SECURITY_GROUP_ID,VIEW_APPLICATION_ID,TERRITORY_CODE,ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,
2475 ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,
2476 ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,TAG)
2477 Select 'OTA_DURATION_UNITS',lkp.LANGUAGE,lkp.LOOKUP_CODE,lkp.MEANING,lkp.DESCRIPTION,lkp.ENABLED_FLAG,lkp.START_DATE_ACTIVE,
2478 lkp.END_DATE_ACTIVE,lkp.CREATED_BY,lkp.CREATION_DATE,lkp.LAST_UPDATED_BY,lkp.LAST_UPDATE_LOGIN,lkp.LAST_UPDATE_DATE,lkp.SOURCE_LANG,
2479 lkp.SECURITY_GROUP_ID,lkp.VIEW_APPLICATION_ID,lkp.TERRITORY_CODE,lkp.ATTRIBUTE_CATEGORY,lkp.ATTRIBUTE1,lkp.ATTRIBUTE2,lkp.ATTRIBUTE3,
2480 lkp.ATTRIBUTE4,lkp.ATTRIBUTE5,lkp.ATTRIBUTE6,lkp.ATTRIBUTE7,lkp.ATTRIBUTE8,lkp.ATTRIBUTE9,lkp.ATTRIBUTE10,lkp.ATTRIBUTE11,lkp.ATTRIBUTE12,
2481 lkp.ATTRIBUTE13,lkp.ATTRIBUTE14,lkp.ATTRIBUTE15,TAG
2482 From Fnd_Lookup_values lkp
2483 Where lkp.Lookup_type = 'FREQUENCY'
2484 and lkp.created_by not in (1,2)
2485 and (lkp.Lookup_code,lkp.language)
2486 not in (Select flk.Lookup_code,flk.language from Fnd_lookup_values flk
2487 Where flk.Lookup_type = 'OTA_DURATION_UNITS') ;
2488 Exception
2489 when others then
2490 l_err_code := SQLCODE;
2491 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When Migrating Frequency Lookups');
2492 add_log_entry( p_table_name => 'MIGRATE_FREQUENCY'
2493 ,p_source_primary_key => ' '
2494 ,p_object_value => 'No lookup values migrated for Frequncy'
2495 ,p_message_text => l_err_msg
2496 ,p_upgrade_id => p_update_id
2497 ,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
2498 ,p_log_type => LOG_TYPE_E
2499 ,p_upgrade_name => UPGRADE_NAME);
2500 End;
2501 end Migrate_lookup;
2502 -- ----------------------------------------------------------------------------
2503 -- |--------------------------< create_ctg_dm_for_act_bg >---------------------------|
2504 -- ----------------------------------------------------------------------------
2505 Procedure create_ctg_dm_for_act_bg(p_update_id in number default 1 ) is
2506 Cursor Csr_Activity_Bg is
2507 Select tad.business_group_id
2508 From Ota_Activity_definitions tad
2509 Group by tad.business_group_id ;
2510
2511 Cursor Csr_Bg_name(p_bg_id number) is
2512 Select hou.name Bg_name
2513 From hr_organization_units hou
2514 Where hou.business_group_id = p_bg_id
2515 and hou.business_group_id = hou.organization_id ;
2516
2517 l_default_dm Ota_Category_usages_tl.Category_usage_id%Type;
2518 l_root_cat_usage_id ota_category_usages.category_usage_id%Type;
2519 l_bg_name hr_organization_units.name%Type ;
2520
2521 begin
2522
2523 For Act_Bg in csr_Activity_bg Loop
2524 Open Csr_bg_name(Act_bg.business_group_id);
2525 Fetch Csr_bg_name into l_bg_name;
2526 If Csr_bg_name%found then
2527 --Fetch csr_bg_name into l_bg_name;
2528 Create_Root_Category(Act_bg.Business_group_id,l_Bg_name,l_root_cat_usage_id);
2529 Create_Default_DM(Act_bg.business_group_id,l_default_dm);
2530 End if;
2531 Close csr_bg_name;
2532 End Loop;
2533
2534 Upgrade_delivery_mode;
2535
2536 end create_ctg_dm_for_act_bg;
2537
2538 -- ----------------------------------------------------------------------------
2539 -- |--------------------------< get_offering_name_with_lang >-----------------|
2540 -- ----------------------------------------------------------------------------
2541 function get_offering_name_with_lang(p_off_name in varchar2,
2542 p_language_id in number,
2543 p_language in varchar2)
2544 return varchar2 is
2545 MAX_OFFERING_NAME_LEN constant number := 80;
2546 MAX_DATA_TRUNC_LEN constant number := 10;
2547 cursor c_get_lang_description is
2548 select flt.description
2549 from fnd_languages fl,
2550 fnd_languages_tl flt
2551 where flt.language_code = fl.language_code
2552 and flt.language = p_language
2553 and fl.language_id = p_language_id ;
2554 l_lang_len number;
2555 l_off_name ota_offerings_tl.name%type := p_off_name;
2556 description fnd_languages_tl.description%type;
2557 begin
2558
2559
2560 open c_get_lang_description;
2561 fetch c_get_lang_description into description;
2562 close c_get_lang_description;
2563
2564 l_lang_len := length(l_off_name||'-'||description) ;
2565 If l_lang_len > MAX_OFFERING_NAME_LEN then
2566 l_lang_len := l_lang_len - MAX_OFFERING_NAME_LEN ;
2567 If l_lang_len > MAX_DATA_TRUNC_LEN then
2568 l_lang_len := MAX_DATA_TRUNC_LEN;
2569 End if;
2570 End if;
2571
2572 if( length(l_off_name) = 80 OR length(l_off_name||'-'||description) > 80 ) then
2573 l_off_name := substrb(l_off_name,1,80-l_lang_len);
2574 end if;
2575
2576 l_off_name := substrb(l_off_name||'-'||description,1,80);
2577
2578 return l_off_name;
2579
2580 end get_offering_name_with_lang;
2581 -- ----------------------------------------------------------------------------
2582 -- |--------------------------< upgrade_root_category_dates >------------------|
2583 -- ----------------------------------------------------------------------------
2584 procedure upgrade_root_category_dates is
2585
2586 cursor min_ctg_start_date(parent_ctg number) is
2587 select nvl(min(start_date_active),trunc(sysdate))
2588 from ota_category_usages
2589 where parent_cat_usage_id = parent_ctg
2590 and type = 'C';
2591
2592 cursor c_root_ctgs is
2593 select category_usage_id,start_date_active
2594 from ota_category_usages
2595 where type = 'C'
2596 and parent_cat_usage_id is null;
2597
2598 l_start_date date;
2599 l_min_start_date date;
2600 begin
2601
2602 for c_roots in c_root_ctgs
2603 loop
2604 open min_ctg_start_date(c_roots.category_usage_id);
2605 fetch min_ctg_start_date into l_start_date;
2606 close min_ctg_start_date;
2607
2608 l_min_start_date := least(nvl(c_roots.start_date_active,l_start_date),l_start_date);
2609
2610 update ota_category_usages
2611 set start_date_active = l_min_start_date
2612 where category_usage_id = c_roots.category_usage_id;
2613
2614
2615 end loop;
2616 end upgrade_root_category_dates;
2617
2618 -- ----------------------------------------------------------------------------
2619 -- |--------------------------< submit_upgrade_report >------------------------|
2620 -- ----------------------------------------------------------------------------
2621 -- Submit an upgrade process
2622 procedure submit_upgrade_report is
2623
2624 OTA_APPLICATION_ID constant number := 810;
2625 OTA_STATUS_INSTALLED constant varchar2(2) := 'I';
2626
2627 l_usr_id number;
2628 l_resp_id number;
2629 l_resp_appl_id number;
2630 l_request_id number := null;
2631 l_installed fnd_product_installations.status%type;
2632 cursor csr_ota_installed is
2633 select fpi.status
2634 from fnd_product_installations fpi
2635 where fpi.application_id = OTA_APPLICATION_ID;
2636
2637 cursor csr_get_resp_details is
2638 select frp.application_id, frp.responsibility_id
2639 from fnd_responsibility frp
2640 where frp.responsibility_key='SYSTEM_ADMINISTRATOR';
2641
2642 cursor csr_get_user_details is
2643 select fu.user_id
2644 from fnd_user fu
2645 where fu.user_name = 'SYSADMIN';
2646 begin
2647
2648 /*
2649 ** The update is required so submit a request for the SYSADMIN user using
2650 ** the System Administrator responsibility.
2651 */
2652
2653 /* Get the required IDs...
2654 */
2655 open csr_ota_installed;
2656 fetch csr_ota_installed into l_installed;
2657 close csr_ota_installed;
2658
2659 if ( l_installed = OTA_STATUS_INSTALLED ) then
2660 open csr_get_user_details;
2661 fetch csr_get_user_details into l_usr_id;
2662 close csr_get_user_details;
2663
2664 open csr_get_resp_details;
2665 fetch csr_get_resp_details into l_resp_appl_id, l_resp_id;
2666 close csr_get_resp_details;
2667
2668 /* Initiate an APPS session as SYSADMIN*/
2669 fnd_global.apps_initialize(user_id => l_usr_id,
2670 resp_id => l_resp_id,
2671 resp_appl_id => l_resp_appl_id);
2672
2673 /*Submit the Upgrade Log Request with initial upgrade arguments */
2674 l_request_id := fnd_request.submit_request(
2675 application => 'OTA',
2676 program => 'OTARPUPG',
2677 argument1 => '1',
2678 argument2 => fnd_date.date_to_canonical(sysdate));
2679 end if;
2680
2681
2682
2683 end submit_upgrade_report;
2684 -- ----------------------------------------------------------------------------
2685 -- |--------------------------<validate_proc_for_hr_upg >------------------------|
2686 -- ----------------------------------------------------------------------------
2687 -- Validation procedure for submitting the upgrade report during patch.
2688 procedure validate_proc_for_hr_upg(do_upg out nocopy varchar2) is
2689 OTA_APPLICATION_ID constant number := 810;
2690 OTA_STATUS_INSTALLED constant varchar2(2) := 'I';
2691
2692 l_installed fnd_product_installations.status%type;
2693 cursor csr_ota_installed is
2694 select fpi.status
2695 from fnd_product_installations fpi
2696 where fpi.application_id = OTA_APPLICATION_ID;
2697
2698 l_do_submit varchar2(10) := 'FALSE';
2699
2700 begin
2701 open csr_ota_installed;
2702 fetch csr_ota_installed into l_installed;
2703 if ( l_installed =OTA_STATUS_INSTALLED ) then
2704 l_do_submit := 'TRUE';
2705 end if;
2706 close csr_ota_installed;
2707
2708 do_upg := l_do_submit;
2709 end validate_proc_for_hr_upg;
2710
2711 -- ----------------------------------------------------------------------------
2712 -- |--------------------------<migrate_tad_dff_contexts >--------------------|
2713 -- ----------------------------------------------------------------------------
2714
2715 procedure migrate_tad_dff_contexts (p_upgrade_id in number default 1) is
2716 l_date date;
2717 Cursor C1 (p_context_code in varchar2)
2718 is Select fcu.*,fvs.flex_value_set_name
2719 From Fnd_Descr_Flex_Col_Usage_Vl fcu, fnd_flex_value_sets fvs
2720 Where fcu.Application_id = 810
2721 and fcu.Descriptive_FlexField_Name = 'OTA_ACTIVITY_DEFINITIONS'
2722 and fcu.Descriptive_Flex_Context_code = p_context_code
2723 and fcu.flex_value_set_id = fvs.flex_value_set_id(+)
2724 and Not exists (SELECT 'Y'
2725 From Fnd_Descr_Flex_Col_Usage_Vl cat_fcu
2726 Where cat_fcu.Application_id = fcu.application_id
2727 and cat_fcu.Descriptive_FlexField_Name = 'OTA_CATEGORY_USAGES'
2728 and cat_fcu.Descriptive_Flex_Context_code = fcu.Descriptive_Flex_Context_code
2729 and cat_fcu.end_user_column_name = fcu.end_user_column_name );
2730
2731 l_segrec FND_DESCR_FLEX_COLUMN_USAGES%RowType ;
2732
2733 l_err_code varchar2(72);
2734 l_err_msg varchar2(2000);
2735
2736 l_context_exists Varchar2(1) := 'N' ;
2737
2738 l_context_code FND_DESCR_FLEX_CONTEXTS.descriptive_flex_context_code%Type;
2739 l_segment_exists Varchar2(1) := 'N' ;
2740
2741 Cursor Csr_DFF_contexts is Select * from FND_DESCR_FLEX_CONTEXTS_vl
2742 Where Application_id = 810
2743 and Descriptive_FLexfield_Name = 'OTA_ACTIVITY_DEFINITIONS'
2744 and Enabled_Flag = 'Y';
2745
2746 Cursor Csr_Segment_exists(p_context_name in varchar2) is SELECT 'Y'
2747 FROM fnd_descriptive_flexs
2748 WHERE application_id = 810
2749 AND descriptive_flexfield_name = p_context_name ;
2750
2751 Begin
2752 fnd_flex_dsc_api.set_session_mode('seed_data');
2753
2754 Select Trunc(sysdate) into l_date from dual ;
2755
2756 For dff_context in Csr_Dff_contexts
2757 Loop
2758 begin
2759 l_context_exists := NULL ;
2760 Select Max('Y') into l_context_exists
2761 From FND_DESCR_FLEX_CONTEXTS_vl
2762 Where Descriptive_Flexfield_name = 'OTA_CATEGORY_USAGES'
2763 and Descriptive_Flex_COntext_Code = dff_context.Descriptive_Flex_Context_code ;
2764 -- If there is a definition already existis and it is a Global context
2765 -- then NO context will be created but the strucre will be added to the existing one.
2766
2767 If (NOT (dff_context.Global_Flag = 'Y' OR l_context_exists is NOT NULL)) then
2768
2769 fnd_flex_dsc_api.create_context(
2770 appl_short_name => 'OTA' ,
2771 flexfield_name => 'OTA_CATEGORY_USAGES',
2772 context_code => Dff_context.Descriptive_flex_context_code,
2773 context_name => Nvl(dff_context.DESCRiptive_FLEX_CONTEXT_NAME,'*-*'),
2774 description => dff_context.DESCRIPTION,
2775 enabled => dff_context.ENABLED_FLAG );
2776
2777 End If;
2778 For I in C1 (Dff_context.Descriptive_flex_context_code)
2779 Loop
2780 begin
2781 fnd_flex_dsc_api.create_segment(
2782 appl_short_name => 'OTA' ,
2783 flexfield_name => 'OTA_CATEGORY_USAGES', --'Add''l Category Information',
2784 context_name => Nvl(dff_context.DESCRiptive_FLEX_CONTEXT_NAME,'*-*'),
2785 name => I.END_USER_COLUMN_NAME,
2786 column => Replace(I.APPLICATION_COLUMN_NAME,'TAD_INFORMATION','ATTRIBUTE'),
2787 description => I.DESCRIPTION,
2788 sequence_number => I.COLUMN_SEQ_NUM,
2789 enabled => I.ENABLED_FLAG,
2790 displayed => I.DISPLAY_FLAG,
2791 value_set => I.flex_value_set_name, --'7 Characters',
2792 default_type => I.DEFAULT_TYPE,
2793 default_value => I.DEFAULT_VALUE,
2794 required => I.REQUIRED_FLAG,
2795 security_enabled => I.SECURITY_ENABLED_FLAG,
2796 display_size => I.DISPLAY_SIZE,
2797 description_size => I.MAXIMUM_DESCRIPTION_LEN,
2798 concatenated_description_size => I.CONCATENATION_DESCRIPTION_LEN,
2799 list_of_values_prompt => I.FORM_ABOVE_PROMPT,
2800 window_prompt => I.FORM_LEFT_PROMPT,
2801 range => NULL);
2802 Exception
2803 when others then
2804 l_err_code := SQLCODE;
2805 l_err_msg := nvl(substr(SQLERRM,1,2000),'migrate_tad_dff_contexts - segments');
2806 add_log_entry( p_table_name => 'MIGRATE_TAD_DFF_SEGS'
2807 ,p_source_primary_key => substr(dff_context.application_id||'|'||
2808 dff_context.descriptive_flexfield_name||'|'||
2809 I.application_column_name,1,80)
2810 ,p_object_value => 'migrate_tad_dff_contexts'
2811 ,p_message_text => l_err_msg
2812 ,p_upgrade_id => p_upgrade_id
2813 ,p_process_date => get_process_date(p_upgrade_id,UPGRADE_NAME)
2814 ,p_log_type => LOG_TYPE_E
2815 ,p_upgrade_name => UPGRADE_NAME);
2816 End;
2817
2818
2819 End Loop;
2820 Exception
2821 when others then
2822 l_err_code := SQLCODE;
2823 l_err_msg := nvl(substr(SQLERRM,1,2000),'migrate_tad_dff_contexts');
2824 add_log_entry( p_table_name => 'MIGRATE_TAD_DFF'
2825 ,p_source_primary_key => substr( dff_context.application_id||
2826 dff_context.descriptive_flexfield_name,
2827 1,80)
2828 ,p_object_value => 'migrate_tad_dff_contexts'
2829 ,p_message_text => l_err_msg
2830 ,p_upgrade_id => p_upgrade_id
2831 ,p_process_date => get_process_date(p_upgrade_id,UPGRADE_NAME)
2832 ,p_log_type => LOG_TYPE_E
2833 ,p_upgrade_name => UPGRADE_NAME);
2834 End;
2835 End Loop;
2836
2837 End migrate_tad_dff_contexts;
2838 --
2839 -- ----------------------------------------------------------------------------
2840 -- |---------------------< upg_tdb_history_att_flags >-------------------------|
2841 -- ----------------------------------------------------------------------------
2842 -- This procedure will update ota_delegate_bookings table records
2843 -- a. successful_attendance_flag to 'Y' where it is NULL and enrollment status
2844 -- is 'Attended'
2845 -- b. is_history_flag to 'Y' where enrollment status is 'Attended'
2846 -- c. enrollment status to 'Attended' for online classes, if lo has 'Completed' / 'Passed' status.
2847
2848 PROCEDURE upg_tdb_history_att_flags(
2849 p_process_control IN varchar2,
2850 p_start_rowid IN rowid,
2851 p_end_rowid IN rowid,
2852 p_rows_processed OUT nocopy number,
2853 p_update_id in number default 1 ) is
2854
2855 l_rows_processed number := 0;
2856
2857 l_err_code varchar2(72);
2858 l_err_msg varchar2(2000);
2859 l_upgrade_id ota_upgrade_log.upgrade_id%Type;
2860 l_process_date ota_upgrade_log.process_date%Type;
2861
2862 l_event_id number;
2863 l_business_group_id number;
2864 l_learning_object_id ota_learning_objects.learning_object_id%Type ;
2865
2866 CURSOR c_booking_id IS
2867 SELECT Booking_id, event_id,content_player_status,business_group_id,object_version_number,
2868 delegate_person_id,
2869 contact_id,
2870 customer_id ,
2871 delegate_contact_id ,
2872 organization_id,
2873 sponsor_person_id,
2874 sponsor_assignment_id,
2875 delegate_assignment_id ,
2876 is_history_flag,
2877 booking_status_type_id
2878 FROM ota_delegate_bookings
2879 WHERE BOOKING_STATUS_TYPE_ID in
2880 (select booking_status_type_id from ota_booking_status_types
2881 where type ='P')
2882 AND RowID Between p_start_rowid and p_end_rowid ;
2883
2884 CURSOR c_learning_object_id IS
2885 SELECT off.learning_object_id
2886 FROM ota_events evt, ota_offerings off
2887 WHERE evt.event_id = l_event_id
2888 and evt.parent_offering_id = off.offering_id;
2889
2890 CURSOR c_less_status(p_user_id in number,p_lo_id in number) IS
2891 SELECT lesson_status
2892 FROM ota_performances
2893 WHERE learning_object_id = p_lo_id
2894 AND user_id = p_user_id
2895 AND lesson_status IN ('P', 'C');
2896
2897
2898 CURSOR c_booking_status_type_id IS
2899 SELECT booking_status_type_id
2900 FROM ota_booking_status_types
2901 WHERE type ='A'
2902 AND business_group_id = l_business_group_id
2903 ORDER BY Nvl(Default_flag,'N') Desc;
2904
2905
2906 l_lo_completed Varchar2(1) := 'N';
2907 l_lesson_status Varchar2(1);
2908 l_booking_status_type_id number ;
2909 l_user_id number;
2910 l_tfl_ovn number;
2911 l_fin_line number;
2912 Begin
2913
2914 UPDATE /*+ ROWID (TDB) */ OTA_DELEGATE_BOOKINGS TDB
2915 SET IS_HISTORY_FLAG = Decode(Is_History_flag,NULL,'Y',Is_History_Flag),
2916 successful_attendance_flag = Decode(successful_attendance_flag,NULL,'Y',successful_attendance_flag)
2917 WHERE BOOKING_STATUS_TYPE_ID in
2918 (select booking_status_type_id from ota_booking_status_types
2919 where type ='A')
2920 AND RowID Between p_start_rowid and p_end_rowid ;
2921
2922 p_rows_processed := SQL%ROWCOUNT;
2923
2924
2925 For l_c_booking_id in c_booking_id Loop
2926 Begin
2927 l_event_id := l_c_booking_id.event_id ;
2928 l_lo_completed := 'N' ;
2929 l_learning_object_id := NULL ;
2930 l_lesson_status := NULL ;
2931 l_business_group_id := l_c_booking_id.business_group_id;
2932 l_user_id := l_c_booking_id.delegate_person_id;
2933
2934 -- Check whether the content is imported from iLearning
2935 If l_c_booking_id.content_player_status in ('P','C') then
2936 l_lo_completed := 'Y' ;
2937 Else
2938 -- If not a imported course, check whether its a online/async class
2939 Open c_learning_object_id;
2940 Fetch c_learning_object_id into l_learning_object_id;
2941 Close c_learning_object_id;
2942
2943 If l_learning_object_id is not null then
2944 -- pass party id for external learner
2945 If l_c_booking_id.delegate_person_id is null then
2946 l_user_id := ota_utility.get_ext_lrnr_party_id(l_c_booking_id.delegate_contact_id);
2947 End if;
2948
2949 -- Check LO attached the offering has record in performance with 'C' or 'P' status.
2950 Open c_less_status(l_user_id,l_learning_object_id) ;
2951 Fetch c_less_status into l_lesson_status ;
2952 Close c_less_status ;
2953 If l_lesson_status is not null then
2954 l_lo_completed := 'Y' ;
2955 End If;
2956 End If;
2957 End if ;
2958
2959 If l_lo_completed = 'Y' then
2960 -- Fetch booking_status_type_id for current record BG of type 'Attended'
2961 Open C_booking_status_type_id ;
2962 Fetch C_booking_status_type_id into l_booking_status_type_id;
2963 Close C_booking_status_type_id ;
2964
2965 ota_tdb_api_upd2.Update_Enrollment
2966 (
2967 p_booking_id => l_c_booking_id.booking_id,
2968 p_delegate_person_id => l_c_booking_id.delegate_person_id,
2969 p_contact_id => l_c_booking_id.contact_id,
2970 p_customer_id => l_c_booking_id.customer_id,
2971 p_booking_status_type_id => l_booking_status_type_id,
2972 p_business_group_id => l_c_booking_id.business_group_id,
2973 p_event_id => l_event_id,
2974 p_object_version_number => l_c_booking_id.object_version_number,
2975 p_date_status_changed => sysdate,
2976 p_successful_attendance_flag => 'Y',
2977 p_tfl_object_version_number => l_tfl_ovn,
2978 p_finance_line_id => l_fin_line,
2979 p_source_cancel => NULL,
2980 p_override_learner_access => 'Y',
2981 p_organization_id => l_c_booking_id.organization_id ,
2982 p_sponsor_person_id => l_c_booking_id.sponsor_person_id,
2983 p_sponsor_assignment_id => l_c_booking_id.sponsor_assignment_id
2984 ) ;
2985
2986 add_log_entry( p_table_name=>'OTA_DELEGATE_BOOKINGS'
2987 ,p_source_primary_key => l_c_booking_id.booking_id
2988 ,p_object_value => l_c_booking_id.booking_status_type_id
2989 ,p_message_text => 'Enrollment status updated successfully from '|| l_c_booking_id.booking_status_type_id || ' to ' || l_booking_status_type_id
2990 ,p_upgrade_id => p_update_id
2991 ,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
2992 ,p_log_type => LOG_TYPE_I
2993 ,p_upgrade_name => UPGRADE_NAME );
2994
2995
2996
2997 End if;
2998
2999 Exception
3000 when others then
3001 l_err_code := SQLCODE;
3002 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When Updating enrollment status to Attended');
3003
3004 add_log_entry( p_table_name=>'OTA_DELEGATE_BOOKINGS'
3005 ,p_source_primary_key => l_c_booking_id.booking_id
3006 ,p_object_value => 'Error When Updating enrollment status to Attended for ID : ' || l_c_booking_id.booking_id
3007 ,p_message_text => l_err_msg
3008 ,p_upgrade_id => p_update_id
3009 ,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
3010 ,p_log_type => LOG_TYPE_E
3011 ,p_upgrade_name => UPGRADE_NAME );
3012
3013 End ;
3014
3015 End Loop ;
3016
3017 Exception
3018 when others then
3019 l_err_code := SQLCODE;
3020 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When Updating Successful attendance and history flag for enrollments');
3021
3022 add_log_entry( p_table_name=>'OTA_DELEGATE_BOOKINGS'
3023 ,p_source_primary_key => 1
3024 ,p_object_value => 'Error When Updating Successful attendance and history flag for class enrollments for ID range : ' || p_start_rowid || ' - ' || p_end_rowid
3025 ,p_message_text => l_err_msg
3026 ,p_upgrade_id => p_update_id
3027 ,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
3028 ,p_log_type => LOG_TYPE_E
3029 ,p_upgrade_name => UPGRADE_NAME );
3030
3031 end upg_tdb_history_att_flags;
3032 --
3033 --
3034 -- ----------------------------------------------------------------------------
3035 -- |---------------------< upgrade_lp_history_flag >--------------------------|
3036 -- ----------------------------------------------------------------------------
3037 -- This procedure will update ota_lp_enrollments table record is_history_flag to 'Y'
3038 -- where learning path enrollment status is 'Completed'
3039
3040 PROCEDURE upgrade_lp_history_flag(
3041 p_process_control IN varchar2,
3042 p_start_rowid IN rowid,
3043 p_end_rowid IN rowid,
3044 p_rows_processed OUT nocopy number,
3045 p_update_id in number default 1 ) is
3046
3047 l_rows_processed number := 0;
3048
3049 l_err_code varchar2(72);
3050 l_err_msg varchar2(2000);
3051 l_upgrade_id ota_upgrade_log.upgrade_id%Type;
3052 l_process_date ota_upgrade_log.process_date%Type;
3053
3054 begin
3055
3056 UPDATE /*+ ROWID (LPE) */ OTA_LP_ENROLLMENTS LPE
3057 SET IS_HISTORY_FLAG = 'Y'
3058 WHERE ROWID between p_start_rowid and p_end_rowid
3059 AND PATH_STATUS_CODE = 'COMPLETED'
3060 AND COMPLETION_DATE IS NOT NULL
3061 AND IS_HISTORY_FLAG IS NULL ;
3062
3063 p_rows_processed := SQL%ROWCOUNT;
3064
3065 Exception
3066 when others then
3067 l_err_code := SQLCODE;
3068 l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When Updating history flag for LP enrollments');
3069
3070 add_log_entry( p_table_name=>'OTA_LP_ENROLLMENTS'
3071 ,p_source_primary_key => 1
3072 ,p_object_value => 'Error When Updating history flag for LP enrollments for ID range : ' || p_start_rowid || ' - ' || p_end_rowid
3073 ,p_message_text => l_err_msg
3074 ,p_upgrade_id => p_update_id
3075 ,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
3076 ,p_log_type => LOG_TYPE_E
3077 ,p_upgrade_name => UPGRADE_NAME );
3078
3079 end upgrade_lp_history_flag;
3080
3081 end ota_classic_upgrade;
3082