[Home] [Help]
PACKAGE BODY: APPS.HR_LONG2LOB
Source
1 PACKAGE BODY HR_LONG2LOB AS
2 /*$Header: hrl2lmig.pkb 120.4 2005/11/17 11:46 smallina noship $ */
3
4 FUNCTION getTblOwner(p_appl_short_name VARCHAR2) RETURN VARCHAR2 IS
5 l_status VARCHAR2(100) := NULL;
6 l_industry VARCHAR2(100) := NULL;
7 l_result BOOLEAN;
8 l_schema_owner VARCHAR2(10) := NULL;
9
10 BEGIN
11 l_result := FND_INSTALLATION.GET_APP_INFO(
12 p_appl_short_name,
13 l_status,
14 l_industry,
15 l_schema_owner);
16 RETURN l_schema_owner;
17 END getTblOwner;
18
19 FUNCTION columnExists(
20 p_appl_short_name VARCHAR2,
21 p_table_name VARCHAR2,
22 p_col_name VARCHAR2,
23 p_col_data_type VARCHAR2) RETURN BOOLEAN AS
24
25 dummy VARCHAR2(1);
26 retValue BOOLEAN:=FALSE;
27 l_schema_owner VARCHAR2(10):= NULL;
28
29 BEGIN
30
31 l_schema_owner := getTblOwner(p_appl_short_name);
32
33 SELECT 'X'
34 INTO dummy
35 FROM
36 all_tab_columns
37 WHERE owner = l_schema_owner
38 AND table_name = p_table_name
39 AND column_name = p_col_name
40 AND data_type = p_col_data_type;
41
42 IF (SQL%FOUND) THEN
43 retValue:=TRUE;
44 RETURN retValue;
45 END IF;
46
47 EXCEPTION
48 WHEN OTHERS THEN
49 RETURN retValue;
50 END columnExists;
51
52
53 --wrapper to all actions in the process of migration.
54 PROCEDURE main(
55 p_appl_short_name IN VARCHAR2,
56 p_table_name IN VARCHAR2,
57 p_old_column_name IN VARCHAR2,
58 p_new_column_data_type IN VARCHAR2,
59 p_mode IN VARCHAR2
60 )AS
61
62 TYPE cur_type IS REF CURSOR;
63
64 l_cur cur_type;
65 l_str VARCHAR2 (500);
66 l_schema VARCHAR2 (30);
67 l_table_name VARCHAR2 (30);
68 l_old_column_name VARCHAR2 (30);
69 l_old_data_type VARCHAR2 (30);
70 l_new_column_name VARCHAR2 (30);
71 l_new_data_type VARCHAR2 (30);
72 l_curr_status VARCHAR2 (20);
73 l_action VARCHAR2 (30);
74 l_rowid VARCHAR2 (30);
75 l_init_process BOOLEAN := FALSE;
76 l_add_columns BOOLEAN := FALSE;
77 l_add_triggers BOOLEAN := FALSE;
78 l_conv_data BOOLEAN := FALSE;
79 l_drop_triggers BOOLEAN := FALSE;
80 l_drop_columns BOOLEAN := FALSE;
81 l_rename_columns BOOLEAN := FALSE;
82 l_mark_columns_as_unused BOOLEAN := FALSE;
83 lp_table_name VARCHAR2(30);
84 lp_old_column_name VARCHAR2(30);
85 lp_new_column_data_type VARCHAR2(30);
86 lp_mode VARCHAR2(30);
87
88
89 BEGIN
90
91 lp_table_name:=Upper(p_table_name);
92 lp_old_column_name:=Upper(p_old_column_name);
93 lp_new_column_data_type:=Upper(p_new_column_data_type);
94 lp_mode:=Upper(p_mode);
95
96 IF p_mode IN ( 'ALL_DROP','ALL_UNUSED','INIT') AND columnExists(p_appl_short_name,
97 lp_table_name, lp_old_column_name, lp_new_column_data_type) = TRUE THEN
98 RETURN;
99 END IF;
100
101 IF (lp_mode = 'ALL_DROP')
102 THEN
103 l_init_process := TRUE;
104 l_add_columns := TRUE;
105 l_conv_data := TRUE;
106 l_rename_columns := TRUE;
107 l_drop_columns := TRUE;
108 END IF;
109
110 IF (lp_mode = 'ALL_UNUSED')
111 THEN
112 l_init_process := TRUE;
113 l_add_columns := TRUE;
114 l_conv_data := TRUE;
115 l_rename_columns := TRUE;
116 l_mark_columns_as_unused := TRUE;
117 END IF;
118
119 IF (lp_mode = 'INIT')
120 THEN
121 l_init_process := TRUE;
122 l_add_columns := TRUE;
123 l_add_triggers := TRUE;
124 END IF;
125
126 IF (lp_mode = 'MIGRATE')
127 THEN
128 l_conv_data := TRUE;
129 l_rename_columns := TRUE;
130 l_drop_triggers := TRUE;
131 END IF;
132
133 IF (lp_mode = 'DROP')
134 THEN
135 l_drop_columns := TRUE;
136 END IF;
137
138 IF (lp_mode = 'UNUSED')
139 THEN
140 l_mark_columns_as_unused := TRUE;
141 END IF;
142
143 -- Steps-1 Intiliazation of AD tables.
144 IF (l_init_process = TRUE) THEN
145 BEGIN
146 ad_longtolob_pkg.initialize_process
147 (p_specific_table => lp_table_name);
148 END;
149 END IF;
150
151 -- Step-2 Addding of New Columns
152 IF (l_add_columns = TRUE)
153 THEN
154 BEGIN
155 l_str :=
156 ' SELECT schema_name, table_name, old_column_name,'
157 || ' new_column_name, new_data_type, status, '
158 || ' action, ROWID '
159 || ' FROM ad_long_column_conversions '
160 || ' WHERE status = '''
161 || ad_longtolob_pkg.g_initialized_status
162 || ''''
163 || ' AND upper(table_name) = upper('''
164 || lp_table_name
165 || ''')'
166 || ' AND upper(old_column_name) = upper('''
167 || lp_old_column_name
168 || ''')';
169
170 OPEN l_cur
171 FOR l_str;
172
173 LOOP
174 FETCH l_cur
175 INTO l_schema, l_table_name, l_old_column_name,
176 l_new_column_name, l_new_data_type, l_curr_status,
177 l_action, l_rowid;
178
179 EXIT WHEN l_cur%NOTFOUND;
180
181 BEGIN
182 IF (l_curr_status = ad_longtolob_pkg.g_initialized_status)
183 THEN
184 -- overrding the new column datatype
185 IF ( lp_new_column_data_type IS NOT NULL
186 AND lp_new_column_data_type IN
187 ('VARCHAR2', 'CLOB', 'BLOB')
188 )
189 THEN
190 l_new_data_type := lp_new_column_data_type;
191 END IF;
192
193 ad_longtolob_pkg.add_new_column (l_schema,
194 l_table_name,
195 l_old_column_name,
196 l_new_column_name,
197 l_new_data_type,
198 l_curr_status,
199 l_action
200 );
201 END IF;
202 EXCEPTION
203 WHEN OTHERS
204 THEN
205 RAISE_APPLICATION_ERROR (-20001,'Exception in adding the new column.');
206 END;
207 END LOOP;
208
209 IF l_cur%ISOPEN
210 THEN
211 CLOSE l_cur;
212 END IF;
213 END;
214 END IF;
215
216 -- Step-3 Addding of Triggers Columns
217 IF (l_add_triggers = TRUE)
218 THEN
219 BEGIN
220 l_str :=
221 ' SELECT schema_name, table_name, old_column_name,'
222 || ' new_column_name, new_data_type, status, '
223 || ' action, ROWID '
224 || ' FROM ad_long_column_conversions '
225 || ' WHERE status = '''
226 || ad_longtolob_pkg.g_add_new_column_status
227 || ''''
228 || ' and upper(table_name) = upper('''
229 || lp_table_name
230 || ''')'
231 || ' AND upper(old_column_name) = upper('''
232 || lp_old_column_name
233 || ''')';
234
235 OPEN l_cur
236 FOR l_str;
237
238 LOOP
239 FETCH l_cur
240 INTO l_schema, l_table_name, l_old_column_name,
241 l_new_column_name, l_new_data_type, l_curr_status,
242 l_action, l_rowid;
243
244 EXIT WHEN l_cur%NOTFOUND;
245
246 BEGIN
247 IF (l_curr_status = ad_longtolob_pkg.g_add_new_column_status
248 )
249 THEN
250 --overrding the new column datatype
251 IF ( lp_new_column_data_type IS NOT NULL
252 AND lp_new_column_data_type IN
253 ('VARCHAR2', 'CLOB', 'BLOB')
254 )
255 THEN
256 l_new_data_type := lp_new_column_data_type;
257 END IF;
258
259 ad_longtolob_pkg.create_transform_triggers
260 (l_schema,
261 l_table_name,
262 l_old_column_name,
263 l_new_column_name,
264 l_new_data_type
265 );
266 END IF;
267 EXCEPTION
268 WHEN OTHERS
269 THEN
270 RAISE_APPLICATION_ERROR (-20001,'Exception in adding the triggers.');
271 END;
272 END LOOP;
273
274 IF l_cur%ISOPEN
275 THEN
276 CLOSE l_cur;
277 END IF;
278 END;
279 END IF;
280
281 --Step-4 Data Migration.
282 IF (l_conv_data = TRUE)
283 THEN
284 BEGIN
285 l_str :=
286 ' SELECT schema_name, table_name, old_column_name,'
287 || ' old_data_type, new_column_name, new_data_type, status, '
288 || ' action, ROWID '
289 || ' FROM ad_long_column_conversions '
290 || ' WHERE status IN ('''
291 || ad_longtolob_pkg.g_add_trigger_status
292 || ''','''|| ad_longtolob_pkg.g_add_new_column_status || ''''
293 || ') AND upper(table_name) = upper('''
294 || lp_table_name
295 || ''')'
296 || ' AND upper(old_column_name) = upper('''
297 || lp_old_column_name
298 || ''')';
299 OPEN l_cur
300 FOR l_str;
301
302 LOOP
303 FETCH l_cur
304 INTO l_schema, l_table_name, l_old_column_name, l_old_data_type,
305 l_new_column_name, l_new_data_type, l_curr_status,
306 l_action, l_rowid;
307
308 EXIT WHEN l_cur%NOTFOUND;
309
310 BEGIN
311 IF (l_curr_status IN (ad_longtolob_pkg.g_add_trigger_status,ad_longtolob_pkg.g_add_new_column_status))
312 THEN
313 ad_longtolob_pkg.update_new_data (l_schema,
314 l_table_name,
315 l_old_column_name,
316 l_old_data_type,
317 l_new_column_name
318 );
319 END IF;
320 EXCEPTION
321 WHEN OTHERS
322 THEN
323 RAISE_APPLICATION_ERROR (-20001,'Exception in migration of long data to clob data.');
324 END;
325 END LOOP;
326
327 IF l_cur%ISOPEN
328 THEN
329 CLOSE l_cur;
330 END IF;
331 END;
332 END IF;
333
334 -- Step-5 Renaming of Columns
335 IF (l_rename_columns = TRUE)
336 THEN
337 DECLARE
338 CURSOR c1 (cur_p_table_name VARCHAR2, cur_old_column_name VARCHAR2)
339 IS
340 SELECT schema_name, table_name, old_column_name, new_column_name
341 FROM ad_long_column_conversions
342 WHERE status IN
343 (ad_longtolob_pkg.g_update_rows_status,
344 ad_longtolob_pkg.g_drop_old_column_status
345 )
346 AND table_name = cur_p_table_name
347 AND old_column_name = cur_old_column_name;
348
349 l_column_name VARCHAR2 (30);
350 l_command VARCHAR2 (300);
351 l_flag NUMBER;
352 BEGIN
353 l_flag := 0;
354
355 FOR rec IN c1 (lp_table_name, lp_old_column_name)
356 LOOP
357 l_command :=
358 'alter table '
359 || rec.schema_name
360 || '.'
361 || rec.table_name
362 || ' rename column '
363 || rec.old_column_name
364 || ' to '
365 || SUBSTR (rec.old_column_name, 1, 26)
366 || '_old';
367
368 EXECUTE IMMEDIATE l_command;
369
370 IF rec.new_column_name LIKE 'R118_%'
371 THEN
372 SELECT LTRIM (rec.new_column_name, 'R118_')
373 INTO l_column_name
374 FROM DUAL;
375
376 l_command :=
377 'alter table '
378 || rec.schema_name
379 || '.'
380 || rec.table_name
381 || ' rename column '
382 || rec.new_column_name
383 || ' to '
384 || l_column_name;
385
386 BEGIN
387 EXECUTE IMMEDIATE l_command;
388 EXCEPTION
389 WHEN OTHERS
390 THEN
391 RAISE_APPLICATION_ERROR (-20001,'Exception in renaming the column.');
392 END;
393 END IF;
394
398 AND table_name = rec.table_name
395 UPDATE ad_long_column_conversions
396 SET status = ad_longtolob_pkg.g_col_renamed_status
397 WHERE schema_name = rec.schema_name
399 AND old_column_name = rec.old_column_name;
400 END LOOP;
401 END;
402 END IF;
403
404 --Step-6 Dropping Triggers
405 IF (l_drop_triggers = TRUE)
406 THEN
407 DECLARE
408 CURSOR c1 (cur_table_name VARCHAR2, cur_old_column_name VARCHAR2)
409 IS
410 SELECT schema_name, table_name, old_column_name
411 FROM ad_long_column_conversions
412 WHERE status IN (ad_longtolob_pkg.g_col_renamed_status)
413 AND table_name = cur_table_name
414 AND old_column_name = cur_old_column_name;
415
416 l_command VARCHAR2 (300);
417 BEGIN
418 FOR rec IN c1 (lp_table_name, lp_old_column_name)
419 LOOP
420 l_command :=
421 'drop trigger ' || SUBSTR (rec.table_name, 1, 24)
422 || '_$R2U1';
423
424 EXECUTE IMMEDIATE l_command;
425
426 l_command :=
427 'drop trigger ' || SUBSTR (rec.table_name, 1, 24)
428 || '_$R2U2';
429
430 EXECUTE IMMEDIATE l_command;
431
432 UPDATE ad_long_column_conversions
433 SET status = ad_longtolob_pkg.g_drop_trigger_status
434 WHERE schema_name = rec.schema_name
435 AND table_name = rec.table_name
436 AND old_column_name = rec.old_column_name;
437 END LOOP;
438 END;
439 END IF;
440
441 --Step-7 Marking Columns as Unused
442 IF (l_mark_columns_as_unused = TRUE)
443 THEN
444 DECLARE
445 CURSOR c1 (cur_table_name VARCHAR2, cur_old_column_name VARCHAR2)
446 IS
447 SELECT schema_name, table_name, old_column_name
448 FROM ad_long_column_conversions
449 WHERE status IN (ad_longtolob_pkg.g_drop_trigger_status,ad_longtolob_pkg.g_col_renamed_status)
450 AND table_name = cur_table_name
451 AND old_column_name = cur_old_column_name;
452
453 l_column_name VARCHAR2 (30);
454 l_command VARCHAR2 (300);
455 BEGIN
456 FOR rec IN c1 (lp_table_name, lp_old_column_name)
457 LOOP
458 l_command :=
459 'alter table '
460 || rec.schema_name
461 || '.'
462 || rec.table_name
463 || ' set unused column '
464 || SUBSTR (rec.old_column_name, 1, 26)
465 || '_old';
466
467 EXECUTE IMMEDIATE l_command;
468
469 UPDATE ad_long_column_conversions
470 SET status = ad_longtolob_pkg.g_complete_status
471 WHERE schema_name = rec.schema_name
472 AND table_name = rec.table_name
473 AND old_column_name = rec.old_column_name;
474 END LOOP;
475 END;
476 END IF;
477
478 --Step-8 Dropping of Columns
479 IF (l_drop_columns = TRUE)
480 THEN
481 DECLARE
482 CURSOR c1 (cur_table_name VARCHAR2, cur_old_column_name VARCHAR2)
483 IS
484 SELECT schema_name, table_name, old_column_name
485 FROM ad_long_column_conversions
486 WHERE status IN (
487 ad_longtolob_pkg.g_drop_trigger_status,ad_longtolob_pkg.g_col_renamed_status)
488 AND table_name = cur_table_name
489 AND old_column_name = cur_old_column_name;
490
491 l_column_name VARCHAR2 (30);
492 l_command VARCHAR2 (300);
493 BEGIN
494 FOR rec IN c1 (lp_table_name, lp_old_column_name)
495 LOOP
496 l_command :=
497 'alter table '
498 || rec.schema_name
499 || '.'
500 || rec.table_name
501 || ' drop column '
502 || SUBSTR (rec.old_column_name, 1, 26)
503 || '_old';
504
505 EXECUTE IMMEDIATE l_command;
506
507 UPDATE ad_long_column_conversions
508 SET status = ad_longtolob_pkg.g_complete_status
509 WHERE schema_name = rec.schema_name
510 AND table_name = rec.table_name
511 AND old_column_name = rec.old_column_name;
512 END LOOP;
513 END;
514 END IF;
515 END MAIN;
516
517 -- init procedure will do the following steps.
518 -- 1.initialize the AD tables with required data.
519 -- 2.Adding the columns
520 -- 3.Adding the Triggers
521
522 PROCEDURE DO_INIT(
523 p_appl_short_name IN VARCHAR2,
524 p_table_name IN VARCHAR2,
525 p_old_column_name IN VARCHAR2,
526 p_new_column_data_type IN VARCHAR2) AS
527
528 BEGIN
529
530 main(p_appl_short_name,p_table_name,p_old_column_name,p_new_column_data_type,'INIT');
531
532 END DO_INIT;
533
534 -- migrate procedure will do the following steps.
535 -- 1.migration of data.
536 -- 2.Renaming the columns
537 -- 3.Dropping the triggers.
538
539 PROCEDURE DO_MIGRATE(
540 p_appl_short_name IN VARCHAR2,
541 p_table_name IN VARCHAR2,
542 p_old_column_name IN VARCHAR2) AS
543
544 BEGIN
545
546 main(p_appl_short_name,p_table_name,p_old_column_name,Null,'MIGRATE');
547
548 END DO_MIGRATE;
549
550 -- drop procedure will do the following step.
551 -- 1.dropping the column
552
553 PROCEDURE DO_DROP(
554 p_appl_short_name IN VARCHAR2,
555 p_table_name IN VARCHAR2,
556 p_old_column_name IN VARCHAR2) AS
557
558 BEGIN
559
560 main(p_appl_short_name,p_table_name,p_old_column_name,NULL,'DROP');
561
562 END DO_DROP;
563
564 -- unused procedure will do the following step.
565 -- 1.marks the column as unused.
566
567 PROCEDURE DO_UNUSED(
568 p_appl_short_name IN VARCHAR2,
569 p_table_name IN VARCHAR2,
570 p_old_column_name IN VARCHAR2) AS
571
572 BEGIN
573
574 main(p_appl_short_name,p_table_name,p_old_column_name,NULL,'UNUSED');
575
576 END DO_UNUSED;
577
578 -- ALL_DROP procedure will do the following steps.
579 -- 1.initialize the AD tables with required data.
580 -- 2.Adding the column
581 -- 3.migration of data.
582 -- 4.Renaming the column
583 -- 5.Dropping the column
584
585 PROCEDURE DO_ALL_DROP(
586 p_appl_short_name IN VARCHAR2,
587 p_table_name IN VARCHAR2,
588 p_old_column_name IN VARCHAR2,
589 p_new_column_data_type IN VARCHAR2) AS
590
591 BEGIN
592
593 main(p_appl_short_name,p_table_name,p_old_column_name,p_new_column_data_type,'ALL_DROP');
594
595 END DO_ALL_DROP;
596
597 -- ALL_DROP procedure will do the following steps.
598 -- 1.initialize the AD tables with required data.
599 -- 2.Adding the column
600 -- 3.migration of data.
601 -- 4.Renaming the column
602 -- 5.Marking the column as unused.
603
604 PROCEDURE DO_ALL_UNUSED(
605 p_appl_short_name IN VARCHAR2,
606 p_table_name IN VARCHAR2,
607 p_old_column_name IN VARCHAR2,
608 p_new_column_data_type IN VARCHAR2) AS
609
610 BEGIN
611
612 main(p_appl_short_name, p_table_name,p_old_column_name,p_new_column_data_type,'ALL_UNUSED');
613
614 END DO_ALL_UNUSED;
615
616
617 END HR_LONG2LOB;