[Home] [Help]
PACKAGE BODY: APPS.OTA_REPROCESS_CLASSIC_UPGRADE
Source
1 PACKAGE BODY OTA_REPROCESS_CLASSIC_UPGRADE as
2 /* $Header: otreprocclsupg.pkb 120.0.12000000.2 2007/02/13 14:03:30 vkkolla noship $ */
3 -- List processes
4
5 MIGRATE_LOOKUP constant varchar2(50) := 'MIGRATE_LOOKUP';
6 UPGRADE_CATEGORY constant varchar2(50) := 'UPGRADE_CATEGORY';
7 CATEGORY_TO_ACTIVITY constant varchar2(50) := 'CATEGORY_TO_ACTIVITY';
8 ACTIVITY_TO_CATEGORY constant varchar2(50) := 'ACTIVITY_TO_CATEGORY';
9 CREATE_OFFERINGS constant varchar2(50) := 'CREATE_OFFERINGS';
10 UPG_EVENT_ASSOCIATIONS constant varchar2(50) := 'UPG_EVENT_ASSOCIATIONS';
11 UPG_EVENTS constant varchar2(50) := 'UPG_EVENTS';
12
13 CONC_UPGRADE_ID constant number := get_next_upgrade_id ;
14 l_request_id number;
15
16 ORACLE_USER_NAME constant fnd_oracle_userid.oracle_username%type
17 := get_ota_schema;
18 -- ----------------------------------------------------------------------------
19 -- |-------------------------< get_next_upgrade_id >--------------------------|
20 -- ----------------------------------------------------------------------------
21 function get_next_upgrade_id
22 return number is
23 l_upgrade_id number;
24 begin
25 select nvl(max(upgrade_id),1)
26 into l_upgrade_id
27 from ota_upgrade_log ;
28
29 return l_upgrade_id +1 ;
30
31 end get_next_upgrade_id;
32 -- ----------------------------------------------------------------------------
33 -- |--------------------------< get_ota_schema >------------------------------|
34 -- ----------------------------------------------------------------------------
35 function get_ota_schema return varchar2 is
36 OTA_APP_SHORT_NAME constant varchar2(10) := 'OTA';
37 l_status fnd_product_installations.status%type;
38 l_industry fnd_product_installations.industry%type;
39 l_schema FND_ORACLE_USERID.oracle_username%type := null;
40
41 l_found boolean;
42 begin
43 l_found := fnd_installation.get_app_info( OTA_APP_SHORT_NAME
44 ,l_status
45 ,l_industry
46 ,l_schema );
47 if l_found then
48 return l_schema;
49 else
50 return null;
51 end if;
52 end get_ota_schema;
53 -- ----------------------------------------------------------------------------
54 -- |-------------------------< get_upgrade_name >-----------------------------|
55 -- ----------------------------------------------------------------------------
56 function get_upgrade_name(proc_name in varchar2,upg_id in number)
57 return varchar2 is
58 begin
59 return proc_name||upg_id;
60 end get_upgrade_name;
61 -- ----------------------------------------------------------------------------
62 -- |---------------------< get_conc_date_param >------------------------------|
63 -- ----------------------------------------------------------------------------
64 function get_conc_date_param(upg_id in number) return date is
65 cursor c_upg_date is
66 select process_date
67 from ota_upgrade_log
68 where upgrade_id = upg_id;
69
70 l_ret date;
71 begin
72 open c_upg_date;
73 fetch c_upg_date into l_ret;
74 close c_upg_date;
75
76 return l_ret;
77 end get_conc_date_param;
78 -- ----------------------------------------------------------------------------
79 -- |-------------------------< check_errors >---------------------------------|
80 -- ----------------------------------------------------------------------------
81 function check_errors(upg_id in number) return boolean is
82 cursor c_any_errors is
83 select 1
84 from ota_upgrade_log
85 where upgrade_id = upg_id
86 and log_type = 'E';
87
88 l_ret boolean := false;
89 l_local number;
90 begin
91 open c_any_errors;
92 fetch c_any_errors into l_local;
93 if c_any_errors%found then
94 l_ret := true;
95 end if;
96 close c_any_errors;
97
98 return l_ret;
99 end check_errors;
100 -- ----------------------------------------------------------------------------
101 -- |-------------------------< submit_upgrade >--------------------------------|
102 -- ----------------------------------------------------------------------------
103 procedure submit_upgrade (p_procedure in varchar2, p_upg_name in varchar2,
104 p_table_name in varchar2,p_primary_col in varchar2) is
105
106 l_errbuf varchar2(1000);
107 l_retcode number;
108 begin
109 ota_data_upgrader_util.submitUpgradeProcessSingle(
110 errbuf => l_errbuf,
111 retcode => l_retcode,
112 p_process_number => '1', -- This worker
113 p_max_number_proc => '1', -- Total workers
114 p_process_to_call => p_procedure,
115 p_upgrade_type => 'AD_LGE_TBL_UPG',
116 p_process_ctrl => null,
117 p_param1 => ORACLE_USER_NAME, -- table owner
118 p_param2 => p_table_name, -- table name
119 p_param3 => p_primary_col, -- PK id column
120 p_param4 => p_upg_name, -- Update name
121 p_param5 => '200', -- batchsize
122 p_param6 => CONC_UPGRADE_ID, -- Concurrent upgrade id;
123 p_param7 => null,
124 p_param8 => null,
125 p_param9 => null,
126 p_param10 => null);
127 end submit_upgrade;
128
129 -- ----------------------------------------------------------------------------
130 -- |-------------------------< write_log >------------------------------------|
131 -- ----------------------------------------------------------------------------
132 procedure write_log (msg in varchar2) is
133 begin
134 fnd_file.put_line(fnd_file.OUTPUT,msg);
135 end write_log;
136
137 /*
138 Functions determining whether upgrade on a particular entity is required.
139 */
140 -- ----------------------------------------------------------------------------
141 -- |-------------------------< do_migrate_lookup >-----------------------------|
142 -- ----------------------------------------------------------------------------
143 function do_migrate_lookup return boolean is
144 cursor c_exist is
145 select 1
146 From Fnd_Lookup_values
147 Where Lookup_type = 'FREQUENCY'
148 and created_by not in (1,2)
149 and (Lookup_code,language)
150 not in (Select Lookup_code,language from Fnd_lookup_values
151 Where Lookup_type = 'OTA_DURATION_UNITS')
152 and rownum = 1 ;
153
154 l_exists boolean := false;
155 l_ret number ;
156 begin
157 open c_exist;
158 fetch c_exist into l_ret;
159 if c_exist%FOUND then
160 l_exists := true;
161 end if;
162 close c_exist;
163
164 return l_exists;
165 end do_migrate_lookup;
166 -- ----------------------------------------------------------------------------
167 -- |-------------------------< do_upgrade_category >--------------------------|
168 -- ----------------------------------------------------------------------------
169 function do_upgrade_category return boolean is
170 cursor c_exist is
171 select 1
172 from ota_category_usages ocu
173 where ocu.category = (SELECT lkp.meaning
174 FROM hr_lookups lkp
175 WHERE lkp.lookup_code = ocu.category
176 AND lkp.lookup_type = 'ACTIVITY_CATEGORY')
177 or not exists (select 1
178 from ota_category_usages_tl oct
179 where oct.category_usage_id = ocu.category_usage_id)
180 and rownum = 1 ;
181
182 l_exists boolean := false;
183 l_ret number ;
184 begin
185 open c_exist;
186 fetch c_exist into l_ret;
187 if c_exist%FOUND then
188 l_exists := true;
189 end if;
190 close c_exist;
191
192 return l_exists;
193 end do_upgrade_category;
194 -- ----------------------------------------------------------------------------
195 -- |-------------------------< do_create_ctg_for_tad >------------------------|
196 -- ----------------------------------------------------------------------------
197 function do_create_ctg_for_tad return boolean is
198 cursor c_exist is
199 select 1
200 FROM ota_activity_definitions tad
201 WHERE tad.category_usage_id is null
202 AND rownum = 1 ;
203
204 l_exists boolean := false;
205 l_ret number ;
206 begin
207 open c_exist;
208 fetch c_exist into l_ret;
209 if c_exist%FOUND then
210 l_exists := true;
211 end if;
212 close c_exist;
213
214 return l_exists;
215 end do_create_ctg_for_tad;
216 -- ----------------------------------------------------------------------------
217 -- |-------------------------< do_create_tad_for_ctg >------------------------|
218 -- ----------------------------------------------------------------------------
219 function do_create_tad_for_ctg return boolean is
220 cursor c_exist is
221 select 1
222 from ota_category_usages ocu
223 where ocu.type = 'C'
224 and not exists (select category_usage_id
225 from ota_activity_definitions tad
226 where tad.category_usage_id is not null
227 and tad.category_usage_id = ocu.category_usage_id)
228 and rownum = 1 ;
229
230 l_exists boolean := false;
231 l_ret number ;
232 begin
233 open c_exist;
234 fetch c_exist into l_ret;
235 if c_exist%FOUND then
236 l_exists := true;
237 end if;
238 close c_exist;
239
240 return l_exists;
241 end do_create_tad_for_ctg;
242 -- ----------------------------------------------------------------------------
243 -- |-------------------------< do_create_offerings >--------------------------|
244 -- ----------------------------------------------------------------------------
245 -- Select Query is wrong.
246 function do_create_offerings return boolean is
247 cursor c_exist is
248 select 1
249 FROM ota_activity_versions tav
250 WHERE
251 not exists (select 1 from ota_offerings off where off.activity_version_id = tav.activity_version_id )
252 and ( exists (select 1 from ota_events evt where evt.activity_version_id = tav.activity_version_id )
253 or ( exists (select 1 from ota_resource_usages rud where rud.activity_version_id = tav.activity_version_id)
254 and not exists (select 1 from ota_resource_usages rud1 where rud1.activity_version_id= tav.activity_version_id
255 and rud1.offering_id is null))
256 or ( exists ( select 1 from per_competence_elements where object_id = tav.activity_version_id and type = 'TRAINER')
257 and not exists (select 1 from per_competence_elements where object_id = tav.activity_version_id and type = 'OTA_OFFERING')))
258 and rownum =1;
259
260 cursor c_exist_0 is
261 select 1
262 from ota_events
263 where parent_offering_id is null
264 and rownum = 1 ;
265
266 l_exists boolean := false;
267 l_ret number ;
268 begin
269 open c_exist_0;
270 fetch c_exist_0 into l_ret;
271 if c_exist_0%FOUND then
272 l_exists := true;
273 end if;
274 close c_exist_0;
275
276 if l_exists <> true then
277 open c_exist;
278 fetch c_exist into l_ret;
279 if c_exist%FOUND then
280 l_exists := true;
281 end if;
282 close c_exist;
283 end if;
284
285 return l_exists;
286 end do_create_offerings;
287 -- ----------------------------------------------------------------------------
288 -- |-------------------------< do_upgrade_evt_assoc >-------------------------|
289 -- ----------------------------------------------------------------------------
290 function do_upgrade_evt_assoc return boolean is
291 cursor c_exist is
292 select 1
293 from ota_event_associations
294 where nvl(self_enrollment_flag,'Y') <> 'N'
295 and ( customer_id is not null
296 or job_id is not null
297 or organization_id is not null
298 or position_id is not null)
299 and rownum = 1;
300
301 l_exists boolean := false;
302 l_ret number ;
303 begin
304 open c_exist;
305 fetch c_exist into l_ret;
306 if c_exist%FOUND then
307 l_exists := true;
308 end if;
309 close c_exist;
310
311 return l_exists;
312 end do_upgrade_evt_assoc;
313 -- ----------------------------------------------------------------------------
314 -- |-------------------------< do_upgrade_evt >------------------------------|
315 -- ----------------------------------------------------------------------------
316 function do_upgrade_evt return boolean is
317 cursor c_exist is
318 select 1
319 from ota_events
320 where line_id is not null
321 and ( nvl(book_independent_flag,'Y') <> 'N'
322 or nvl(Maximum_internal_attendees,0) <> 0)
323 and rownum = 1 ;
324
325 l_exists boolean := false;
326 l_ret number ;
327 begin
328 open c_exist;
329 fetch c_exist into l_ret;
330 if c_exist%FOUND then
331 l_exists := true;
332 end if;
333 close c_exist;
334
335 return l_exists;
336 end do_upgrade_evt;
337
338
339
340 -- ----------------------------------------------------------------------------
341 -- |-------------------< check_offering_event_link >--------------------------|
342 -- ----------------------------------------------------------------------------
343 function check_offering_event_link return boolean is
344 cursor csr_off_evt_link is
345 select evt.event_id,evt.activity_version_id evt_act_Ver_id, off.activity_version_id,evt.parent_offering_id
346 from ota_events evt, ota_offerings off
347 where evt.parent_offering_id = off.offering_id
348 and evt.activity_version_id <> off.activity_version_id ;
349
350 cursor csr_off_evt_lang is
351 select evt.event_id,evt.activity_version_id evt_act_Ver_id, off.activity_version_id,evt.parent_offering_id,evt.language_id evt_lang, off.language_id off_lang
352 from ota_events evt, ota_offerings off
353 where evt.parent_offering_id = off.offering_id
354 and evt.language_id <> off.language_id ;
355
356
357 l_exists boolean := false;
358 l_ret number ;
359 begin
360 -- Check if any event attached to a offering belongs to a different course. If any found remove the link.
361 for l_off_evt_link in csr_off_evt_link loop
362 l_exists := TRUE;
363 Update ota_events
364 set parent_offering_id = Null
365 Where event_id = l_off_evt_link.event_id
366 and parent_offering_id = l_off_evt_link.parent_offering_id ;
367 end loop;
368
369 -- Check if any event attached to a offering of differnt language. If any found remove the link.
370 for l_off_evt_lang in csr_off_evt_lang loop
371 l_exists := TRUE;
372 Update ota_events
373 set parent_offering_id = Null
374 Where event_id = l_off_evt_lang.event_id
375 and parent_offering_id = l_off_evt_lang.parent_offering_id ;
376 end loop;
377
378
379 return l_exists;
380 end check_offering_event_link;
381
382 -- ----------------------------------------------------------------------------
383 -- |-------------------------< upgrade_request >------------------------------|
384 -- ----------------------------------------------------------------------------
385 -- Procedure called on concurrent request submission.
386 -- Two parameters are required by the Concurrent Manager.
387 -- This procedure checks if upgrade is required for an entity, and if so
388 -- submits a large table update for the entity.
389 procedure upgrade_request(aSqlerrm IN OUT NOCOPY VARCHAR2,
390 aSqlcode IN OUT NOCOPY number) is
391
392 l_upgrade_done boolean := false;
393 begin
394
395 write_log('Starting Reprocess OTA Upgrade Concurrent Process ');
396
397 INSERT INTO OTA_UPGRADE_LOG (
398 UPGRADE_ID,
399 TABLE_NAME,
400 SOURCE_PRIMARY_KEY,
401 OBJECT_VALUE,
402 BUSINESS_GROUP_ID,
403 PROCESS_DATE,
404 MESSAGE_TEXT,
405 TARGET_PRIMARY_KEY,
406 LOG_TYPE,
407 UPGRADE_NAME)
408 VALUES
409 (CONC_UPGRADE_ID,
410 'DUMMY',
411 '-1',
412 null,
413 null,
414 sysdate,
415 'Starting Reprocess OTA Classic Data Upgrade',
416 null,
417 'N',
418 'OTCLSUPG');
419
420 write_log('Checking for mismatch between offering and events table');
421 if check_offering_Event_link then
422 write_log('There is a mismatch between offering and events table. This proces will correct them');
423 end if;
424
425
426 -- 1) Migrate Lookups
427 if do_migrate_lookup then
428 write_log('Migrating Lookup:FREQUENCY');
429 l_upgrade_done := true;
430 ota_classic_upgrade.migrate_lookup;
431 end if;
432 -- 2) Upgrade Category
433 if do_upgrade_category then
434 write_log('Upgrading Category definitions');
435 l_upgrade_done := true;
436 submit_upgrade( 'ota_classic_upgrade.upgrade_category'
437 ,get_upgrade_name(UPGRADE_CATEGORY,CONC_UPGRADE_ID)
438 ,'OTA_CATEGORY_USAGES'
439 ,'CATEGORY_USAGE_ID');
440 end if;
441
442 -- Upgrade Act cat Inclusions
443 ota_classic_upgrade.upgrade_act_cat_inclusions;
444
445 -- Upgrade Online delivery modes
446 ota_classic_upgrade.upgrade_online_delivery_modes(CONC_UPGRADE_ID);
447
448 -- Create root dms and categories for BGS
449 ota_classic_upgrade.create_root_ctg_and_dms;
450
451 ota_classic_upgrade.create_ctg_dm_for_act_bg(CONC_UPGRADE_ID);
452
453
454 --3) Create Category for Activity
455 if do_create_ctg_for_tad then
456 write_log('Creating category for activity');
457 l_upgrade_done := true;
458 submit_upgrade( 'ota_classic_upgrade.create_category_for_activity'
459 ,get_upgrade_name(ACTIVITY_TO_CATEGORY,CONC_UPGRADE_ID)
460 ,'OTA_ACTIVITY_DEFINITIONS'
461 ,'ACTIVITY_ID');
462 end if;
463
464
465
466 -- 4) Create Activity for Category
467 if do_create_tad_for_ctg then
468 write_log('Creating activity for category');
469 l_upgrade_done := true;
470 submit_upgrade( 'ota_classic_upgrade.create_activity_for_category'
471 ,get_upgrade_name(CATEGORY_TO_ACTIVITY,CONC_UPGRADE_ID)
472 ,'OTA_CATEGORY_USAGES'
473 ,'CATEGORY_USAGE_ID');
474 end if;
475
476 ota_classic_upgrade.upgrade_root_category_dates;
477
478 --5) Create Offerings
479 if do_create_offerings then
480 write_log('Creating Offerings');
481 l_upgrade_done := true;
482 submit_upgrade( 'ota_classic_upgrade.create_offering'
483 ,get_upgrade_name(CREATE_OFFERINGS,CONC_UPGRADE_ID)
484 ,'OTA_ACTIVITY_VERSIONS'
485 ,'ACTIVITY_VERSION_ID');
486
487 update ota_offerings
488 set learning_object_id = null
489 where learning_object_id = -1;
490 end if;
491
492 -- 6) Upgrade Event Associations
493 if do_upgrade_evt_assoc then
494 write_log (' Upgrading event Associations');
495 l_upgrade_done := true;
496 submit_upgrade( 'ota_classic_upgrade.upgrade_event_associations'
497 ,get_upgrade_name(UPG_EVENT_ASSOCIATIONS,CONC_UPGRADE_ID)
498 ,'OTA_EVENT_ASSOCIATIONS'
499 ,'EVENT_ASSOCIATION_ID');
500
501 end if;
502
503 -- 7) Upgrade Events
504 if do_upgrade_evt then
505 write_log (' Upgrading Events');
506 l_upgrade_done := true;
507 submit_upgrade( 'ota_classic_upgrade.upgrade_events'
508 ,get_upgrade_name(UPG_EVENTS,CONC_UPGRADE_ID)
509 ,'OTA_EVENTS'
510 ,'EVENT_ID');
511 end if;
512
513 -- 2733966
514 -- 8) Populate Language_code column in OTA_Offerings, OTA_Learning_Objects and
515 -- OTA_Competence_languages if it is null.
516 ota_classic_upgrade.upgrade_language_code;
517
518
519 if l_upgrade_done then
520 if check_errors(CONC_UPGRADE_ID) then
521 write_log('Errors have been encountered during this Upgrade process' );
522 write_log('Correct the errors and Re-run this concurrent request' );
523 write_log('To check for errors run the Upgrade Log Report with the following parameters:');
524 write_log('Upgrade Id: '||CONC_UPGRADE_ID);
525 write_log('Date '||get_conc_date_param(CONC_UPGRADE_ID));
526
527 /*Submit the Upgrade Log Request with initial upgrade arguments */
528 l_request_id := fnd_request.submit_request(
529 application => 'OTA',
530 program => 'OTARPUPG',
531 argument1 => CONC_UPGRADE_ID,
532 argument2 => fnd_date.date_to_canonical(sysdate));
533 else
534 write_log('Upgrade Completed Successfully');
535 write_log('There is no further need to run this concurrent request');
536
537 end if;
538 else
539 write_log('No Upgrade is required');
540 end if;
541
542
543 end upgrade_request;
544
545 end ota_reprocess_classic_upgrade;