1 package body Fnd_Dictionary_Pkg as
5 visited_tables fnd_dictionary_pkg.NameArrayTyp;
2 /* $Header: AFDICTB.pls 120.5 2005/11/22 11:30:03 rsheh ship $ */
3
4 c_log_head CONSTANT varchar2(40) := 'fnd.plsql.fnd_dictionary_pkg.';
6 visited_table_count number := 0;
7 checking_log boolean := FALSE;
8
9 --
10 -- MultipleDeveloperKeys (PRIVATE))
11 -- Check if there are more than one Developer key for this table
12 --
13 function MultipleDeveloperKeys (
14 x_application_id in number,
15 x_table_id in number,
16 x_primary_key_name in varchar2) return boolean
17 is
18 l_tmp number;
19 begin
20 select count(*)
21 into l_tmp
22 from FND_PRIMARY_KEYS
23 where APPLICATION_ID = x_application_id
24 and TABLE_ID = x_table_id
25 and PRIMARY_KEY_NAME <> upper(x_primary_key_name)
26 and PRIMARY_KEY_TYPE = 'D';
27
28 if (l_tmp > 0) then
29 return(TRUE);
30 else
31 return(FALSE);
32 end if;
33 end MultipleDeveloperKeys;
34
35 --
36 -- ValidatePrimaryKey (PRIVATE))
37 -- Check if this primary key is already exist
38 --
39 procedure ValidatePrimaryKey (
40 x_application_short_name in varchar2,
41 x_table_name in varchar2,
42 x_primary_key_name in varchar2,
43 x_pk_application_id in out nocopy number,
44 x_pk_table_id in out nocopy number,
45 x_pk_id in out nocopy number)
46 is
47 begin
48 begin
49 select P.APPLICATION_ID, P.TABLE_ID, P.PRIMARY_KEY_ID
50 into x_pk_application_id, x_pk_table_id, x_pk_id
51 from FND_PRIMARY_KEYS P,
52 FND_TABLES T,
53 FND_APPLICATION A
54 where A.APPLICATION_SHORT_NAME = x_application_short_name
55 and A.APPLICATION_ID = T.APPLICATION_ID
56 and T.TABLE_NAME = x_table_name
57 and T.TABLE_ID = P.TABLE_ID
58 and T.APPLICATION_ID = P.APPLICATION_ID
59 and P.PRIMARY_KEY_NAME = upper(x_primary_key_name);
60 exception
61 when no_data_found then
62 x_pk_id := -1;
63 end;
64
65 end ValidatePrimaryKey;
66
67
68 --
69 -- ResolveConflictColumn (PRIVATE))
70 -- If there is a column has the same USER_COLUMN_NAME or COLUMN_SEQUENCE
71 -- bump it away by prepend '@' for USER_COLUMN_NAME and big number for
72 -- COLUMN_SEQUENCE.
73 --
74 procedure ResolveConflictColumn (
75 x_application_id in number,
76 x_table_id in number,
77 x_column_name in varchar2,
78 x_user_column_name in varchar2,
79 x_column_sequence in varchar2
80 ) is
81 maxseq number;
82 begin
83
84 -- If there is no row updated, no exception will be raise.
85 update FND_COLUMNS
86 set USER_COLUMN_NAME = '@'||USER_COLUMN_NAME
87 where APPLICATION_ID = x_application_id
88 and TABLE_ID = x_table_id
89 and COLUMN_NAME <> x_column_name
90 and USER_COLUMN_NAME = x_user_column_name;
91
92 select max(column_sequence)
93 into maxseq
94 from FND_COLUMNS
95 where APPLICATION_ID = x_application_id
96 and TABLE_ID = x_table_id;
97
98 update FND_COLUMNS
99 set COLUMN_SEQUENCE = to_number('200001') + maxseq
100 where APPLICATION_ID = x_application_id
101 and TABLE_ID = x_table_id
102 and COLUMN_NAME <> x_column_name
103 and COLUMN_SEQUENCE = to_number(x_column_sequence);
104
105 end ResolveConflictColumn;
106
107 --
108 -- InsertTable (PRIVATE))
109 -- Add a new table into FND_TABLES. This is only called after checking
110 -- there is no such table exists in UploadTable().
111 --
112 procedure InsertTable (
113 x_application_id in number,
114 x_table_name in varchar2,
115 x_user_table_name in varchar2,
116 x_table_type in varchar2,
117 x_description in varchar2,
118 x_auto_size in varchar2,
119 x_initial_extent in varchar2,
120 x_next_extent in varchar2,
121 x_min_extents in varchar2,
122 x_max_extents in varchar2,
123 x_ini_trans in varchar2,
124 x_max_trans in varchar2,
125 x_pct_free in varchar2,
126 x_pct_increase in varchar2,
127 x_pct_used in varchar2,
128 x_hosted_support_style in varchar2,
129 x_creation_date in date,
130 x_created_by in number,
131 x_last_update_date in date,
132 x_last_updated_by in number,
133 x_last_update_login in number
134 ) is
135 begin
136 insert into FND_TABLES (
137 APPLICATION_ID,
138 TABLE_ID,
139 TABLE_NAME,
140 USER_TABLE_NAME,
141 TABLE_TYPE,
142 DESCRIPTION,
143 AUTO_SIZE,
144 INITIAL_EXTENT,
145 NEXT_EXTENT,
146 MIN_EXTENTS,
147 MAX_EXTENTS,
148 INI_TRANS,
149 MAX_TRANS,
150 PCT_FREE,
151 PCT_INCREASE,
152 PCT_USED,
153 HOSTED_SUPPORT_STYLE,
154 LAST_UPDATED_BY,
155 LAST_UPDATE_DATE,
156 LAST_UPDATE_LOGIN,
157 CREATION_DATE,
158 CREATED_BY)
159 values (
160 x_application_id,
161 FND_TABLES_S.NEXTVAL,
162 x_table_name,
163 x_user_table_name,
164 x_table_type,
165 x_description,
166 x_auto_size,
167 x_initial_extent,
168 x_next_extent,
169 x_min_extents,
170 x_max_extents,
171 x_ini_trans,
172 x_max_trans,
173 x_pct_free,
174 x_pct_increase,
175 x_pct_used,
176 x_hosted_support_style,
177 x_last_updated_by,
178 x_last_update_date,
179 x_last_update_login,
180 x_last_update_date,
181 x_last_updated_by);
182
183 end InsertTable;
184
185 --
186 -- InsertColumn (PRIVATE))
187 -- Add a new column into FND_COLUMNS. This is only called after checking
188 -- there is no such column exists in UploadColumn().
189 --
190 procedure InsertColumn (
191 x_application_id in number,
192 x_table_id in number,
193 x_column_name in varchar2,
194 x_user_column_name in varchar2,
195 x_column_sequence in varchar2,
196 x_column_type in varchar2,
197 x_width in varchar2,
198 x_null_allowed_flag in varchar2,
199 x_description in varchar2,
200 x_default_value in varchar2,
201 x_translate_flag in varchar2,
202 x_precision in varchar2,
203 x_scale in varchar2,
204 x_flexfield_usage_code in varchar2,
205 x_flexfield_application_id in varchar2,
206 x_flexfield_name in varchar2,
207 x_flex_value_set_app_id in varchar2,
208 x_flex_value_set_id in varchar2,
209 x_creation_date in date,
210 x_created_by in number,
211 x_last_update_date in date,
212 x_last_updated_by in number,
213 x_last_update_login in number
214 ) is
215
216 begin
217
218 insert into FND_COLUMNS (
219 APPLICATION_ID,
220 TABLE_ID,
221 COLUMN_ID,
222 COLUMN_NAME,
223 USER_COLUMN_NAME,
224 COLUMN_SEQUENCE,
225 COLUMN_TYPE,
226 WIDTH,
227 NULL_ALLOWED_FLAG,
228 DESCRIPTION,
229 DEFAULT_VALUE,
230 TRANSLATE_FLAG,
231 PRECISION,
232 SCALE,
233 FLEXFIELD_USAGE_CODE,
234 FLEXFIELD_APPLICATION_ID,
235 FLEXFIELD_NAME,
236 FLEX_VALUE_SET_APPLICATION_ID,
237 FLEX_VALUE_SET_ID,
238 CREATION_DATE,
239 CREATED_BY,
240 LAST_UPDATED_BY,
241 LAST_UPDATE_DATE,
242 LAST_UPDATE_LOGIN)
243 values (
244 x_application_id,
245 x_table_id,
246 FND_COLUMNS_S.NEXTVAL,
247 x_column_name,
248 x_user_column_name,
249 x_column_sequence,
250 x_column_type,
251 x_width,
252 x_null_allowed_flag,
253 x_description,
254 x_default_value,
255 x_translate_flag,
256 x_precision,
257 x_scale,
258 x_flexfield_usage_code,
259 x_flexfield_application_id,
260 x_flexfield_name,
261 x_flex_value_set_app_id,
262 x_flex_value_set_id,
263 x_last_update_date,
264 x_last_updated_by,
265 x_last_updated_by,
266 x_last_update_date,
267 x_last_update_login);
268
269 end InsertColumn;
270
271 --
272 -- InsertIndex (PRIVATE))
273 -- Add a new index into FND_INDEXES. This is only called after checking
274 -- there is no such index exists in UploadIndex().
275 --
276 procedure InsertIndex (
277 x_application_id in number,
278 x_table_id in number,
279 x_index_name in varchar2,
280 x_uniqueness in varchar2,
281 x_auto_size in varchar2,
282 x_description in varchar2,
283 x_initial_extent in varchar2,
284 x_next_extent in varchar2,
285 x_min_extents in varchar2,
286 x_max_extents in varchar2,
287 x_ini_trans in varchar2,
288 x_max_trans in varchar2,
289 x_pct_free in varchar2,
290 x_pct_increase in varchar2,
291 x_creation_date in date,
292 x_created_by in number,
293 x_last_update_date in date,
294 x_last_updated_by in number,
295 x_last_update_login in number
296
297 ) is
298
299 begin
300
301 insert into FND_INDEXES (
302 APPLICATION_ID,
303 TABLE_ID,
304 INDEX_ID,
305 INDEX_NAME,
306 UNIQUENESS,
307 AUTO_SIZE,
308 DESCRIPTION,
309 INITIAL_EXTENT,
310 NEXT_EXTENT,
311 MIN_EXTENTS,
312 MAX_EXTENTS,
313 INI_TRANS,
314 MAX_TRANS,
315 PCT_FREE,
316 PCT_INCREASE,
317 LAST_UPDATED_BY,
318 LAST_UPDATE_DATE,
319 LAST_UPDATE_LOGIN,
320 CREATION_DATE,
321 CREATED_BY)
322 values (
323 x_application_id,
324 x_table_id,
325 FND_INDEXES_S.NEXTVAL,
326 x_index_name,
327 x_uniqueness,
328 x_auto_size,
329 x_description,
330 x_initial_extent,
331 x_next_extent,
332 x_min_extents,
333 x_max_extents,
334 x_ini_trans,
335 x_max_trans,
336 x_pct_free,
337 x_pct_increase,
338 x_last_updated_by,
339 x_last_update_date,
340 x_last_update_login,
341 x_last_update_date,
342 x_last_updated_by);
343 end InsertIndex;
344
345 --
346 -- InsertPrimaryKey (PRIVATE))
347 -- Add a new primary key into FND_PRIMARY_KEYS.
348 -- This is only called after checking
349 -- there is no such primary key exists in UploadPrimaryKey().
350 --
351 procedure InsertPrimaryKey(
352 x_application_id in number,
353 x_table_id in number,
354 x_primary_key_name in varchar2,
355 x_primary_key_type in varchar2,
356 x_audit_key_flag in varchar2,
357 x_enabled_flag in varchar2,
358 x_description in varchar2,
359 x_creation_date in date,
360 x_created_by in number,
361 x_last_update_date in date,
362 x_last_updated_by in number,
363 x_last_update_login in number
364 ) is
365
366 begin
367
368 insert into FND_PRIMARY_KEYS(
369 APPLICATION_ID,
370 TABLE_ID,
371 PRIMARY_KEY_NAME,
372 PRIMARY_KEY_ID,
373 PRIMARY_KEY_TYPE,
374 AUDIT_KEY_FLAG,
375 ENABLED_FLAG,
376 DESCRIPTION,
377 LAST_UPDATED_BY,
378 LAST_UPDATE_DATE,
379 LAST_UPDATE_LOGIN,
380 CREATION_DATE,
381 CREATED_BY)
382 values (
383 x_application_id,
384 x_table_id,
385 x_primary_key_name,
386 FND_PRIMARY_KEYS_S.NEXTVAL,
387 x_primary_key_type,
388 x_audit_key_flag,
389 x_enabled_flag,
390 x_description,
391 x_last_updated_by,
392 x_last_update_date,
393 x_last_update_login,
394 x_last_update_date,
395 x_last_updated_by);
396 end InsertPrimaryKey;
397
398 --
399 -- InsertForeignKey (PRIVATE))
400 -- Add a new foreign key into FND_FOREIGN_KEYS.
401 -- This is only called after checking
402 -- there is no such foreign key exists in UploadForeignKey().
403 --
404 procedure InsertForeignKey(
405 x_application_id in number,
406 x_table_id in number,
407 x_foreign_key_name in varchar2,
408 x_primary_key_application_id in number,
409 x_primary_key_table_id in number,
410 x_primary_key_id in number,
411 x_description in varchar2,
412 x_cascade_behavior in varchar2,
413 x_foreign_key_relation in varchar2,
414 x_condition in varchar2,
415 x_enabled_flag in varchar2,
416 x_creation_date in date,
417 x_created_by in number,
418 x_last_update_date in date,
419 x_last_updated_by in number,
420 x_last_update_login in number
421 ) is
422
423 begin
424
425 insert into FND_FOREIGN_KEYS(
426 APPLICATION_ID,
427 TABLE_ID,
428 FOREIGN_KEY_ID,
429 FOREIGN_KEY_NAME,
430 PRIMARY_KEY_APPLICATION_ID,
431 PRIMARY_KEY_TABLE_ID,
432 PRIMARY_KEY_ID,
433 DESCRIPTION,
434 CASCADE_BEHAVIOR,
435 FOREIGN_KEY_RELATION,
436 CONDITION,
437 ENABLED_FLAG,
438 LAST_UPDATED_BY,
439 LAST_UPDATE_DATE,
440 LAST_UPDATE_LOGIN,
441 CREATION_DATE,
442 CREATED_BY)
443 values (
444 x_application_id,
445 x_table_id,
446 FND_FOREIGN_KEYS_S.NEXTVAL,
447 x_foreign_key_name,
448 x_primary_key_application_id,
449 x_primary_key_table_id,
450 x_primary_key_id,
451 x_description,
452 x_cascade_behavior,
453 x_foreign_key_relation,
454 x_condition,
455 x_enabled_flag,
456 x_last_updated_by,
457 x_last_update_date,
458 x_last_update_login,
459 x_last_update_date,
460 x_last_updated_by);
461 end InsertForeignKey;
462
463 --
464 -- InsertSequence (PRIVATE))
465 -- Add a new sequence into FND_SEQUENCES. This is only called after checking
466 -- there is no such sequence exists in UploadSequence().
467 --
468 procedure InsertSequence (
469 x_application_id in number,
470 x_sequence_name in varchar2,
471 x_start_value in varchar2,
472 x_description in varchar2,
473 x_increment_by in varchar2,
474 x_min_value in varchar2,
475 x_max_value in varchar2,
476 x_cache_size in varchar2,
477 x_cycle_flag in varchar2,
478 x_order_flag in varchar2,
479 x_creation_date in date,
480 x_created_by in number,
481 x_last_update_date in date,
482 x_last_updated_by in number,
483 x_last_update_login in number
484 ) is
485 begin
486 insert into FND_SEQUENCES (
487 APPLICATION_ID,
488 SEQUENCE_ID,
489 SEQUENCE_NAME,
490 START_VALUE,
491 DESCRIPTION,
492 INCREMENT_BY,
493 MIN_VALUE,
494 MAX_VALUE,
495 CACHE_SIZE,
496 CYCLE_FLAG,
497 ORDER_FLAG,
498 LAST_UPDATED_BY,
499 LAST_UPDATE_DATE,
500 LAST_UPDATE_LOGIN,
501 CREATION_DATE,
502 CREATED_BY)
503 values (
504 x_application_id,
505 FND_SEQUENCES_S.NEXTVAL,
506 x_sequence_name,
507 x_start_value,
508 x_description,
509 x_increment_by,
510 x_min_value,
511 x_max_value,
512 x_cache_size,
513 x_cycle_flag,
514 x_order_flag,
515 x_last_updated_by,
516 x_last_update_date,
517 x_last_update_login,
518 x_last_update_date,
519 x_last_updated_by);
520
521 end InsertSequence;
522
523 --
524 -- InsertView (PRIVATE))
525 -- Add a new view into FND_VIEWS. This is only called after checking
526 -- there is no such view key exists in UploadView().
527 --
528 procedure InsertView (
529 x_application_id in number,
530 x_view_name in varchar2,
531 x_text in varchar2,
532 x_description in varchar2,
533 x_creation_date in date,
534 x_created_by in number,
535 x_last_update_date in date,
536 x_last_updated_by in number,
537 x_last_update_login in number
538 ) is
539 begin
540 insert into FND_VIEWS (
541 APPLICATION_ID,
542 VIEW_ID,
543 VIEW_NAME,
544 TEXT,
545 DESCRIPTION,
546 LAST_UPDATED_BY,
547 LAST_UPDATE_DATE,
548 LAST_UPDATE_LOGIN,
549 CREATION_DATE,
550 CREATED_BY)
551 values (
552 x_application_id,
553 FND_VIEWS_S.NEXTVAL,
554 x_view_name,
555 x_text,
556 x_description,
557 x_last_updated_by,
558 x_last_update_date,
559 x_last_update_login,
560 x_last_update_date,
564
561 x_last_updated_by);
562
563 end InsertView;
565 --
566 -- UploadTable (PUBLIC))
567 -- Public procedure for afdict.lct to call when uploading tables using
568 -- using afdict.lct. It calls InsertTable() when needed.
569 --
570 procedure UploadTable (
571 x_application_short_name in varchar2,
572 x_table_name in varchar2,
573 x_user_table_name in varchar2,
574 x_table_type in varchar2,
575 x_description in varchar2,
576 x_auto_size in varchar2,
577 x_initial_extent in varchar2,
578 x_next_extent in varchar2,
579 x_min_extents in varchar2,
580 x_max_extents in varchar2,
581 x_ini_trans in varchar2,
582 x_max_trans in varchar2,
583 x_pct_free in varchar2,
584 x_pct_increase in varchar2,
585 x_pct_used in varchar2,
586 x_hosted_support_style in varchar2,
587 x_user_id in varchar2
588 ) is
589 begin
590
591 UploadTable (
592 x_application_short_name => x_application_short_name,
593 x_table_name => x_table_name,
594 x_user_table_name => x_user_table_name,
595 x_table_type => x_table_type,
596 x_description => x_description,
597 x_auto_size => x_auto_size,
598 x_initial_extent=> x_initial_extent,
599 x_next_extent => x_next_extent,
600 x_min_extents => x_min_extents,
601 x_max_extents => x_max_extents,
602 x_ini_trans => x_ini_trans,
603 x_max_trans => x_max_trans,
604 x_pct_free => x_pct_free,
605 x_pct_increase => x_pct_increase,
606 x_pct_used => x_pct_used,
607 x_hosted_support_style => x_hosted_support_style,
608 x_user_id => x_user_id,
609 x_custom_mode => null,
610 x_last_update_date => null);
611
612 end UploadTable;
613
614 --
615 -- UploadColumn (PUBLIC))
616 -- Public procedure for afdict.lct to call when uploading columns using
617 -- using afdict.lct. It calls InsertColumn() when needed.
618 --
619 procedure UploadColumn (
620 x_application_short_name in varchar2,
621 x_table_name in varchar2,
622 x_column_name in varchar2,
623 x_user_column_name in varchar2,
624 x_column_sequence in varchar2,
625 x_column_type in varchar2,
626 x_width in varchar2,
627 x_null_allowed_flag in varchar2,
628 x_description in varchar2,
629 x_default_value in varchar2,
630 x_translate_flag in varchar2,
631 x_precision in varchar2,
632 x_scale in varchar2,
633 x_flexfield_usage_code in varchar2,
634 x_flexfield_application_id in varchar2,
635 x_flexfield_name in varchar2,
636 x_flex_value_set_app_id in varchar2,
637 x_flex_value_set_id in varchar2,
638 x_user_id in varchar2
639 ) is
640 begin
641
642 UploadColumn (
643 x_application_short_name => x_application_short_name,
644 x_table_name => x_table_name,
645 x_column_name => x_column_name,
646 x_user_column_name => x_user_column_name,
647 x_column_sequence => x_column_sequence,
648 x_column_type => x_column_type,
649 x_width => x_width,
650 x_null_allowed_flag => x_null_allowed_flag,
651 x_description => x_description,
652 x_default_value => x_default_value,
653 x_translate_flag => x_translate_flag,
654 x_precision => x_precision,
655 x_scale => x_scale,
656 x_flexfield_usage_code => x_flexfield_usage_code,
657 x_flexfield_application_id => x_flexfield_application_id,
658 x_flexfield_name => x_flexfield_name,
659 x_flex_value_set_app_id => x_flex_value_set_app_id,
660 x_flex_value_set_id => x_flex_value_set_id,
661 x_user_id => x_user_id,
662 x_custom_mode => null,
663 x_last_update_date => null);
664
665 end UploadColumn;
666
667 --
668 -- UploadHistColumn (PUBLIC))
669 -- Public procedure for afdict.lct to call when uploading columns using
670 -- using afdict.lct. It calls InsertHistColumn() when needed.
671 --
672 procedure UploadHistColumn (
673 x_application_short_name in varchar2,
674 x_table_name in varchar2,
675 x_column_name in varchar2,
676 x_partition in varchar2,
677 x_hsize in varchar2,
678 x_user_id in varchar2
679 ) is
680 begin
681 UploadHistColumn (
682 x_application_short_name => x_application_short_name,
683 x_table_name => x_table_name,
684 x_column_name => x_column_name,
685 x_partition => x_partition,
686 x_hsize => x_hsize,
687 x_user_id => x_user_id,
688 x_custom_mode => null,
689 x_last_update_date => null);
690 end UploadHistColumn;
691
692
693
694 --
695 -- UploadIndex (PUBLIC))
696 -- Public procedure for afdict.lct to call when uploading indexes using
697 -- using afdict.lct. It calls InsertIndex() when needed.
698 --
699 procedure UploadIndex (
700 x_application_short_name in varchar2,
701 x_table_name in varchar2,
702 x_index_name in varchar2,
703 x_uniqueness in varchar2,
704 x_auto_size in varchar2,
705 x_description in varchar2,
706 x_initial_extent in varchar2,
707 x_next_extent in varchar2,
708 x_min_extents in varchar2,
709 x_max_extents in varchar2,
710 x_ini_trans in varchar2,
711 x_max_trans in varchar2,
712 x_pct_free in varchar2,
713 x_pct_increase in varchar2,
714 x_user_id in varchar2
715 ) is
716 begin
717
718 UploadIndex (
719 x_application_short_name => x_application_short_name,
720 x_table_name => x_table_name,
721 x_index_name => x_index_name,
722 x_uniqueness => x_uniqueness,
723 x_auto_size => x_auto_size,
724 x_description => x_description,
725 x_initial_extent => x_initial_extent,
726 x_next_extent => x_next_extent,
727 x_min_extents => x_min_extents,
728 x_max_extents => x_max_extents,
729 x_ini_trans => x_ini_trans,
730 x_max_trans => x_max_trans,
731 x_pct_free => x_pct_free,
732 x_pct_increase => x_pct_increase,
733 x_user_id => x_user_id,
734 x_custom_mode => null,
735 x_last_update_date => null,
736 x_phase_mode => 'BEGIN');
737
738
739 UploadIndex (
740 x_application_short_name => x_application_short_name,
741 x_table_name => x_table_name,
742 x_index_name => x_index_name,
743 x_uniqueness => x_uniqueness,
744 x_auto_size => x_auto_size,
745 x_description => x_description,
746 x_initial_extent => x_initial_extent,
747 x_next_extent => x_next_extent,
748 x_min_extents => x_min_extents,
749 x_max_extents => x_max_extents,
750 x_ini_trans => x_ini_trans,
751 x_max_trans => x_max_trans,
752 x_pct_free => x_pct_free,
753 x_pct_increase => x_pct_increase,
754 x_user_id => x_user_id,
755 x_custom_mode => null,
756 x_last_update_date => null,
757 x_phase_mode => 'END');
758
759 end UploadIndex;
760
761
762 --
763 -- UploadIndexColumn (PUBLIC))
764 -- Public procedure for afdict.lct to call when uploading index columns using
765 -- using afdict.lct.
766 --
767 procedure UploadIndexColumn (
768 x_application_short_name in varchar2,
769 x_table_name in varchar2,
770 x_index_name in varchar2,
771 x_index_column_name in varchar2,
772 x_index_column_sequence in varchar2,
773 x_user_id in varchar2
774 ) is
775 begin
776
777 UploadIndexColumn (
778 x_application_short_name => x_application_short_name,
779 x_table_name => x_table_name,
780 x_index_name => x_index_name,
781 x_index_column_name => x_index_column_name,
782 x_index_column_sequence => x_index_column_sequence,
783 x_user_id => x_user_id,
784 x_custom_mode => null,
785 x_last_update_date => null);
786 end UploadIndexColumn;
787
788 --
789 -- UploadPrimaryKey (PUBLIC))
790 -- Public procedure for afdict.lct to call when uploading primary key using
791 -- using afdict.lct. It calls InsertPrimary() when needed.
792 --
793 procedure UploadPrimaryKey (
794 x_application_short_name in varchar2,
795 x_table_name in varchar2,
796 x_primary_key_name in varchar2,
797 x_primary_key_type in varchar2,
798 x_audit_key_flag in varchar2,
799 x_description in varchar2,
800 x_enabled_flag in varchar2,
801 x_user_id in varchar2
802 ) is
803 begin
804
805 UploadPrimaryKey (
806 x_application_short_name => x_application_short_name,
807 x_table_name => x_table_name,
808 x_primary_key_name => x_primary_key_name,
809 x_primary_key_type => x_primary_key_type,
810 x_audit_key_flag => x_audit_key_flag,
811 x_description=> x_description,
812 x_enabled_flag => x_enabled_flag,
813 x_user_id => x_user_id,
814 x_custom_mode => null,
815 x_last_update_date => null,
816 x_phase_mode => 'BEGIN');
817
818 UploadPrimaryKey (
819 x_application_short_name => x_application_short_name,
820 x_table_name => x_table_name,
821 x_primary_key_name => x_primary_key_name,
822 x_primary_key_type => x_primary_key_type,
823 x_audit_key_flag => x_audit_key_flag,
824 x_description=> x_description,
825 x_enabled_flag => x_enabled_flag,
826 x_user_id => x_user_id,
827 x_custom_mode => null,
828 x_last_update_date => null,
829 x_phase_mode => 'END');
830
831 end UploadPrimaryKey;
832
833 --
834 -- UploadPrimaryKeyColumn (PUBLIC))
835 -- Public procedure for afdict.lct to call when uploading primary key column
836 -- using afdict.lct.
837 --
838 procedure UploadPrimaryKeyColumn (
839 x_application_short_name in varchar2,
840 x_table_name in varchar2,
841 x_primary_key_name in varchar2,
842 x_primary_key_column_name in varchar2,
843 x_primary_key_column_sequence in varchar2,
844 x_user_id in varchar2
845 ) is
846 begin
847
848 UploadPrimaryKeyColumn (
849 x_application_short_name => x_application_short_name,
850 x_table_name => x_table_name,
851 x_primary_key_name => x_primary_key_name,
852 x_primary_key_column_name =>x_primary_key_column_name,
853 x_primary_key_column_sequence =>x_primary_key_column_sequence,
854 x_user_id => x_user_id,
855 x_custom_mode => null,
856 x_last_update_date => null);
857
858 end UploadPrimaryKeyColumn;
859
860 --
861 -- UploadForeignKey (PUBLIC))
862 -- Public procedure for afdict.lct to call when uploading foreign key using
863 -- using afdict.lct. It calls InsertForeign() when needed.
864 --
865 procedure UploadForeignKey (
866 x_application_short_name in varchar2,
867 x_table_name in varchar2,
868 x_foreign_key_name in varchar2,
869 x_primary_key_application_name in varchar2,
870 x_primary_key_table_name in varchar2,
871 x_primary_key_name in varchar2,
872 x_description in varchar2,
873 x_cascade_behavior in varchar2,
874 x_foreign_key_relation in varchar2,
875 x_condition in varchar2,
876 x_enabled_flag in varchar2,
877 x_user_id in varchar2
878 ) is
879 begin
880
881 UploadForeignKey (
882 x_application_short_name => x_application_short_name,
883 x_table_name => x_table_name,
884 x_foreign_key_name => x_foreign_key_name,
885 x_primary_key_application_name => x_primary_key_application_name,
886 x_primary_key_table_name => x_primary_key_table_name,
887 x_primary_key_name => x_primary_key_name,
888 x_description => x_description,
889 x_cascade_behavior => x_cascade_behavior,
890 x_foreign_key_relation => x_foreign_key_relation,
891 x_condition => x_condition,
892 x_enabled_flag => x_enabled_flag,
893 x_user_id => x_user_id,
894 x_custom_mode => null,
895 x_last_update_date => null,
896 x_phase_mode => 'BEGIN');
897
898 UploadForeignKey (
899 x_application_short_name => x_application_short_name,
900 x_table_name => x_table_name,
901 x_foreign_key_name => x_foreign_key_name,
902 x_primary_key_application_name => x_primary_key_application_name,
903 x_primary_key_table_name => x_primary_key_table_name,
904 x_primary_key_name => x_primary_key_name,
905 x_description => x_description,
906 x_cascade_behavior => x_cascade_behavior,
907 x_foreign_key_relation => x_foreign_key_relation,
908 x_condition => x_condition,
909 x_enabled_flag => x_enabled_flag,
910 x_user_id => x_user_id,
911 x_custom_mode => null,
912 x_last_update_date => null,
913 x_phase_mode => 'END');
914
915
916 end UploadForeignKey;
917
918 --
919 -- UploadForeignKeyColumn (PUBLIC))
920 -- Public procedure for afdict.lct to call when uploading foreign key column
921 -- using afdict.lct.
922 --
923 procedure UploadForeignKeyColumn (
924 x_application_short_name in varchar2,
925 x_table_name in varchar2,
926 x_foreign_key_name in varchar2,
927 x_foreign_key_column_name in varchar2,
928 x_foreign_key_column_sequence in varchar2,
929 x_cascade_value in varchar2,
930 x_user_id in varchar2
931 ) is
932 begin
933 UploadForeignKeyColumn (
934 x_application_short_name => x_application_short_name,
935 x_table_name => x_table_name,
936 x_foreign_key_name => x_foreign_key_name,
937 x_foreign_key_column_name => x_foreign_key_column_name,
938 x_foreign_key_column_sequence => x_foreign_key_column_sequence,
939 x_cascade_value => x_cascade_value,
940 x_user_id => x_user_id,
941 x_custom_mode => null,
942 x_last_update_date => null);
943
944 end UploadForeignKeyColumn;
945
946
947 --
948 -- UploadSequence (PUBLIC))
949 -- Public procedure for afdict.lct to call when uploading sequence
950 -- using afdict.lct. It calls InsertSequence when needed.
951 --
952 procedure UploadSequence (
953 x_application_short_name in varchar2,
954 x_sequence_name in varchar2,
955 x_start_value in varchar2,
956 x_description in varchar2,
957 x_increment_by in varchar2,
958 x_min_value in varchar2,
959 x_max_value in varchar2,
960 x_cache_size in varchar2,
961 x_cycle_flag in varchar2,
962 x_order_flag in varchar2,
963 x_user_id in varchar2
964 ) is
965 begin
966 UploadSequence (
967 x_application_short_name => x_application_short_name,
968 x_sequence_name => x_sequence_name,
969 x_start_value => x_start_value,
970 x_description => x_description,
971 x_increment_by => x_increment_by,
972 x_min_value => x_min_value,
973 x_max_value => x_max_value,
974 x_cache_size => x_cache_size,
975 x_cycle_flag => x_cycle_flag,
976 x_order_flag => x_order_flag,
977 x_user_id => x_user_id,
978 x_custom_mode => null,
979 x_last_update_date => null);
980
981 end UploadSequence;
982
983 --
984 -- UploadView (PUBLIC))
985 -- Public procedure for afdict.lct to call when uploading view
986 -- using afdict.lct. It calls InsertView when needed.
987 --
988 procedure UploadView (
989 x_application_short_name in varchar2,
990 x_view_name in varchar2,
991 x_text in varchar2,
992 x_description in varchar2,
993 x_user_id in varchar2
994 ) is
995 begin
996 UploadView (
997 x_application_short_name => x_application_short_name,
998 x_view_name => x_view_name,
999 x_text => x_text,
1000 x_description => x_description,
1001 x_user_id => x_user_id,
1002 x_custom_mode => null,
1003 x_last_update_date => null,
1004 x_phase_mode => 'BEGIN');
1005
1006 UploadView (
1007 x_application_short_name => x_application_short_name,
1008 x_view_name => x_view_name,
1009 x_text => x_text,
1010 x_description => x_description,
1011 x_user_id => x_user_id,
1012 x_custom_mode => null,
1013 x_last_update_date => null,
1014 x_phase_mode => 'END');
1015
1016
1017 end UploadView;
1018
1019 --
1020 -- UploadViewColumn (PUBLIC))
1021 -- Public procedure for afdict.lct to call when uploading view column
1022 -- using afdict.lct.
1023 --
1024 procedure UploadViewColumn (
1025 x_application_short_name in varchar2,
1026 x_view_name in varchar2,
1027 x_view_column_name in varchar2,
1028 x_view_column_sequence in varchar2,
1029 x_user_id in varchar2
1030 ) is
1031 begin
1032 UploadViewColumn (
1033 x_application_short_name => x_application_short_name,
1034 x_view_name => x_view_name,
1035 x_view_column_name => x_view_column_name,
1036 x_view_column_sequence => x_view_column_sequence,
1037 x_user_id => x_user_id,
1038 x_custom_mode => null,
1039 x_last_update_date => null);
1040
1041 end UploadViewColumn;
1042
1043 --
1044 -- ViewTextLength (PUBLIC)
1045 -- Return the view text length.
1046 -- This is a helper function as length() function can not be used
1047 -- directly in sql but is ok to used on a variable in PL/SQL.
1048 --
1049 /*
1050 function ViewTextLength (
1051 x_application_id in number,
1052 x_view_name in varchar2) return number
1053 is
1054 len number;
1055 begin
1056 for r in (select text from fnd_views
1057 where application_id = x_application_id
1058 and view_name = x_view_name) loop
1059 len := length(r.text);
1060
1061 end loop;
1062
1063 return(len);
1064 end ViewTextLength;
1065 */
1066 -- There is no way we can figure out the lenght of a LONG column, so
1067 -- just have to trap that ORA-1406.
1068 function ViewTextLength (
1069 x_application_id in number,
1070 x_view_name in varchar2) return number
1071 is
1072 len number;
1073 vtext varchar2(32000);
1074 begin
1075 begin
1076 select text
1077 into vtext
1078 from fnd_views
1079 where application_id = x_application_id
1080 and view_name = x_view_name;
1081 len := 200;
1082 return(len);
1083 exception
1084 when others then
1085 if (SQLCODE = -1406) then
1086 len := 33000;
1087 end if;
1088 return(len);
1089 end;
1090
1091 end ViewTextLength;
1092
1093 --
1094 -- RemoveColumn (PUBLIC)
1095 -- Remove column from FND_COLUMNS table.
1096 -- Before removing this column, make sure that there is no index,
1097 -- primary key or foreign key is using this column
1098 --
1099 procedure RemoveColumn(
1100 x_application_short_name in varchar2,
1101 x_table_name in varchar2,
1102 x_column_name in varchar2) is
1103 appl_id number;
1104 tab_id number;
1105 col_id number;
1106 cnt number;
1107 begin
1108 begin
1109 select application_id
1110 into appl_id
1111 from fnd_application
1112 where application_short_name = upper(x_application_short_name);
1113 exception
1114 when no_data_found then
1115 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1116 fnd_message.set_token('TABLE', 'FND_APPLICATION');
1117 fnd_message.set_token('COLUMN', 'APPLICATION_SHORT_NAME');
1118 fnd_message.set_token('VALUE', x_application_short_name);
1119 app_exception.raise_exception;
1120 return;
1121 end;
1122
1123 begin
1124 select table_id
1125 into tab_id
1126 from fnd_tables
1127 where application_id = appl_id
1128 and table_name = upper(x_table_name);
1129 exception
1130 when no_data_found then
1131 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1132 fnd_message.set_token('TABLE', 'FND_TABLES');
1133 fnd_message.set_token('COLUMN', 'TABLE_NAME');
1134 fnd_message.set_token('VALUE', x_table_name);
1135 app_exception.raise_exception;
1136 return;
1137 end;
1138
1139 begin
1140 select column_id
1141 into col_id
1142 from fnd_columns
1143 where application_id = appl_id
1144 and table_id = tab_id
1145 and column_name = upper(x_column_name);
1146 exception
1147 when no_data_found then
1148 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1149 fnd_message.set_token('TABLE', 'FND_COLUMNS');
1150 fnd_message.set_token('COLUMN', 'COLUMN_NAME');
1151 fnd_message.set_token('VALUE', x_column_name);
1152 app_exception.raise_exception;
1153 return;
1154 end;
1155
1156 -- Before removing this column, make sure that there is no index,
1157 -- primary key or foreign key is using this column
1158
1159 -- Check index column
1160 cnt := 0;
1161 select count(*) into cnt
1162 from fnd_index_columns
1163 where application_id = appl_id
1164 and table_id = tab_id
1165 and column_id = col_id;
1166 if (cnt > 0) then
1167 fnd_message.set_name('FND', 'FND-CHILD EXISTS');
1168 fnd_message.set_token('APPLICATION', x_application_short_name);
1169 fnd_message.set_token('TABLE', x_table_name);
1170 fnd_message.set_token('COLUMN', x_column_name);
1171 fnd_message.set_token('CHILD', 'INDEX COLUMN');
1172 app_exception.raise_exception;
1173 return;
1174 end if;
1175
1176 -- Check primary key column
1177 cnt := 0;
1178 select count(*) into cnt
1179 from fnd_primary_key_columns
1180 where application_id = appl_id
1181 and table_id = tab_id
1182 and column_id = col_id;
1183 if (cnt > 0) then
1184 fnd_message.set_name('FND', 'FND-CHILD EXISTS');
1185 fnd_message.set_token('APPLICATION', x_application_short_name);
1186 fnd_message.set_token('TABLE', x_table_name);
1187 fnd_message.set_token('COLUMN', x_column_name);
1188 fnd_message.set_token('CHILD', 'PRIMARY KEY COLUMN');
1189 app_exception.raise_exception;
1190 return;
1191 end if;
1192
1193 -- Check foreign key column
1194 cnt := 0;
1195 select count(*) into cnt
1196 from fnd_foreign_key_columns
1197 where application_id = appl_id
1198 and table_id = tab_id
1199 and column_id = col_id;
1200 if (cnt > 0) then
1201 fnd_message.set_name('FND', 'FND-CHILD EXISTS');
1202 fnd_message.set_token('APPLICATION', x_application_short_name);
1203 fnd_message.set_token('TABLE', x_table_name);
1204 fnd_message.set_token('COLUMN', x_column_name);
1205 fnd_message.set_token('CHILD', 'FOREIGN KEY COLUMN');
1206 app_exception.raise_exception;
1207 return;
1208 end if;
1209
1210 delete from fnd_columns
1211 where application_id = appl_id
1212 and table_id = tab_id
1213 and column_id = col_id;
1214
1215 end RemoveColumn;
1216
1217 --
1218 -- RemoveIndex (PUBLIC)
1219 -- Remove index from FND_INDEXES and FND_INDEX_COLUMNS table.
1220 --
1221 procedure RemoveIndex(
1222 x_application_short_name in varchar2,
1223 x_table_name in varchar2,
1224 x_index_name in varchar2) is
1225 appl_id number;
1226 tab_id number;
1227 ind_id number;
1228 begin
1229 begin
1230 select application_id
1231 into appl_id
1232 from fnd_application
1233 where application_short_name = upper(x_application_short_name);
1234 exception
1235 when no_data_found then
1236 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1237 fnd_message.set_token('TABLE', 'FND_APPLICATION');
1238 fnd_message.set_token('COLUMN', 'APPLICATION_SHORT_NAME');
1239 fnd_message.set_token('VALUE', x_application_short_name);
1240 app_exception.raise_exception;
1241 return;
1242 end;
1243
1244 begin
1245 select table_id
1246 into tab_id
1247 from fnd_tables
1248 where application_id = appl_id
1249 and table_name = upper(x_table_name);
1250 exception
1251 when no_data_found then
1252 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1253 fnd_message.set_token('TABLE', 'FND_TABLES');
1254 fnd_message.set_token('COLUMN', 'TABLE_NAME');
1255 fnd_message.set_token('VALUE', x_table_name);
1256 app_exception.raise_exception;
1257 return;
1258 end;
1259
1260 begin
1261 select index_id
1262 into ind_id
1263 from fnd_indexes
1264 where application_id = appl_id
1265 and table_id = tab_id
1266 and index_name = upper(x_index_name);
1267 exception
1268 when no_data_found then
1269 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1270 fnd_message.set_token('TABLE', 'FND_INDEXES');
1271 fnd_message.set_token('COLUMN', 'INDEX_NAME');
1272 fnd_message.set_token('VALUE', x_index_name);
1273 app_exception.raise_exception;
1274 return;
1275 end;
1276
1277 -- Delete index columns
1278 delete from fnd_index_columns
1279 where application_id = appl_id
1280 and table_id = tab_id
1281 and index_id = ind_id;
1282
1283 delete from fnd_indexes
1284 where application_id = appl_id
1285 and table_id = tab_id
1286 and index_id = ind_id;
1287
1288 end RemoveIndex;
1289
1290 --
1291 -- RemovePrimaryKey (PUBLIC)
1292 -- Remove primary key from FND_PRIMARY_KEYS and FND_PRIMARY_KEY_COLUMNS table.
1293 -- Before deleting primary key, make sure that there is no foreign key
1294 -- pointing to this primary key
1295 --
1296 procedure RemovePrimaryKey(
1297 x_application_short_name in varchar2,
1298 x_table_name in varchar2,
1299 x_primary_key_name in varchar2) is
1300 appl_id number;
1301 tab_id number;
1302 pk_id number;
1303 cnt number;
1304 begin
1305 begin
1306 select application_id
1307 into appl_id
1308 from fnd_application
1309 where application_short_name = upper(x_application_short_name);
1310 exception
1311 when no_data_found then
1312 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1313 fnd_message.set_token('TABLE', 'FND_APPLICATION');
1314 fnd_message.set_token('COLUMN', 'APPLICATION_SHORT_NAME');
1315 fnd_message.set_token('VALUE', x_application_short_name);
1316 app_exception.raise_exception;
1317 return;
1318 end;
1319
1320 begin
1321 select table_id
1322 into tab_id
1323 from fnd_tables
1324 where application_id = appl_id
1325 and table_name = upper(x_table_name);
1326 exception
1327 when no_data_found then
1328 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1329 fnd_message.set_token('TABLE', 'FND_TABLES');
1330 fnd_message.set_token('COLUMN', 'TABLE_NAME');
1331 fnd_message.set_token('VALUE', x_table_name);
1332 app_exception.raise_exception;
1333 return;
1334 end;
1335
1336 begin
1337 select primary_key_id
1338 into pk_id
1339 from fnd_primary_keys
1340 where application_id = appl_id
1341 and table_id = tab_id
1342 and primary_key_name = upper(x_primary_key_name);
1343 exception
1344 when no_data_found then
1345 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1346 fnd_message.set_token('TABLE', 'FND_PRIMARY_KEYS');
1350 return;
1347 fnd_message.set_token('COLUMN', 'PRIMARY_KEY_NAME');
1348 fnd_message.set_token('VALUE', x_primary_key_name);
1349 app_exception.raise_exception;
1351 end;
1352
1353 -- Before deleting primary key, make sure that there is no foreign key
1354 -- pointing to this primary key
1355
1356 cnt := 0;
1357 select count(*) into cnt
1358 from fnd_foreign_keys
1359 where primary_key_application_id = appl_id
1360 and primary_key_table_id = tab_id
1361 and primary_key_id = pk_id;
1362
1363 if (cnt = 0) then
1364 delete from fnd_primary_key_columns
1365 where application_id = appl_id
1366 and table_id = tab_id
1367 and primary_key_id = pk_id;
1368
1369 delete from fnd_primary_keys
1370 where application_id = appl_id
1371 and table_id = tab_id
1372 and primary_key_id = pk_id;
1373
1374 else
1375 -- There are foreign keys pointing to this primary key.
1376 -- Removing the foreign key before removing this primary key
1377 fnd_message.set_name('FND', 'FND-FOREIGN KEY EXISTS');
1378 fnd_message.set_token('PRIMARY_KEY', x_primary_key_name);
1379 app_exception.raise_exception;
1380 return;
1381 end if;
1382
1383 end RemovePrimaryKey;
1384
1385 --
1386 -- RemoveForeignKey (PUBLIC)
1387 -- Remove foreign key from FND_FOREIGN_KEYS and FND_FOREIGN_KEY_COLUMNS table.
1388 --
1389 procedure RemoveForeignKey(
1390 x_application_short_name in varchar2,
1391 x_table_name in varchar2,
1392 x_foreign_key_name in varchar2) is
1393 appl_id number;
1394 tab_id number;
1395 fk_id number;
1396 begin
1397 begin
1398 select application_id
1399 into appl_id
1400 from fnd_application
1401 where application_short_name = upper(x_application_short_name);
1402 exception
1403 when no_data_found then
1404 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1405 fnd_message.set_token('TABLE', 'FND_APPLICATION');
1406 fnd_message.set_token('COLUMN', 'APPLICATION_SHORT_NAME');
1407 fnd_message.set_token('VALUE', x_application_short_name);
1408 app_exception.raise_exception;
1409 return;
1410 end;
1411
1412 begin
1413 select table_id
1414 into tab_id
1415 from fnd_tables
1416 where application_id = appl_id
1417 and table_name = upper(x_table_name);
1418 exception
1419 when no_data_found then
1420 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1421 fnd_message.set_token('TABLE', 'FND_TABLES');
1422 fnd_message.set_token('COLUMN', 'TABLE_NAME');
1423 fnd_message.set_token('VALUE', x_table_name);
1424 app_exception.raise_exception;
1425 return;
1426 end;
1427
1428 begin
1429 select foreign_key_id
1430 into fk_id
1431 from fnd_foreign_keys
1432 where application_id = appl_id
1433 and table_id = tab_id
1434 and foreign_key_name = upper(x_foreign_key_name);
1435 exception
1436 when no_data_found then
1437 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1438 fnd_message.set_token('TABLE', 'FND_FOREIGN_KEYS');
1439 fnd_message.set_token('COLUMN', 'FOREIGN_KEY_NAME');
1440 fnd_message.set_token('VALUE', x_foreign_key_name);
1441 app_exception.raise_exception;
1442 return;
1443 end;
1444
1445 -- Nothing pointing to foreign key so is safe to delete
1446 delete from fnd_foreign_key_columns
1447 where application_id = appl_id
1448 and table_id = tab_id
1449 and foreign_key_id = fk_id;
1450
1451 delete from fnd_foreign_keys
1452 where application_id = appl_id
1453 and table_id = tab_id
1454 and foreign_key_id = fk_id;
1455
1456 end RemoveForeignKey;
1457
1458 --
1459 -- RemoveSequence (PUBLIC)
1460 -- Remove sequence from FND_SEQUENCES table.
1461 --
1462 procedure RemoveSequence(
1463 x_application_short_name in varchar2,
1464 x_sequence_name in varchar2) is
1465 appl_id number;
1466 begin
1467 begin
1468 select application_id
1469 into appl_id
1470 from fnd_application
1471 where application_short_name = upper(x_application_short_name);
1472 exception
1473 when no_data_found then
1474 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1475 fnd_message.set_token('TABLE', 'FND_APPLICATION');
1476 fnd_message.set_token('COLUMN', 'APPLICATION_SHORT_NAME');
1477 fnd_message.set_token('VALUE', x_application_short_name);
1478 app_exception.raise_exception;
1479 return;
1480 end;
1481
1482 delete from fnd_sequences
1483 where application_id = appl_id
1484 and sequence_name = upper(x_sequence_name);
1485 if (SQL%ROWCOUNT = 0) then
1486 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1487 fnd_message.set_token('TABLE', 'FND_SEQUENCES');
1488 fnd_message.set_token('COLUMN', 'SEQUENCE_NAME');
1489 fnd_message.set_token('VALUE', x_sequence_name);
1490 app_exception.raise_exception;
1491 return;
1492 end if;
1493
1494 end RemoveSequence;
1495
1496 --
1497 -- RemoveView (PUBLIC)
1498 -- Remove view from FND_VIEWS and FND_VIEW_COLUMNS table.
1499 --
1500 procedure RemoveView(
1501 x_application_short_name in varchar2,
1502 x_view_name in varchar2) is
1503 appl_id number;
1504 vw_id number;
1505 begin
1506 begin
1507 select application_id
1508 into appl_id
1509 from fnd_application
1510 where application_short_name = upper(x_application_short_name);
1511 exception
1512 when no_data_found then
1513 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1514 fnd_message.set_token('TABLE', 'FND_APPLICATION');
1515 fnd_message.set_token('COLUMN', 'APPLICATION_SHORT_NAME');
1516 fnd_message.set_token('VALUE', x_application_short_name);
1517 app_exception.raise_exception;
1518 return;
1519 end;
1520
1521 begin
1522 select view_id into vw_id
1523 from fnd_views
1524 where application_id = appl_id
1525 and view_name = upper(x_view_name);
1526 exception
1527 when no_data_found then
1528 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1529 fnd_message.set_token('TABLE', 'FND_VIEWS');
1530 fnd_message.set_token('COLUMN', 'VIEW_NAME');
1531 fnd_message.set_token('VALUE', x_view_name);
1532 app_exception.raise_exception;
1533 return;
1534 end;
1535
1536 -- Nothing pointing to view, so is safe to delete
1537 delete from fnd_view_columns
1538 where application_id = appl_id
1539 and view_id = vw_id;
1540
1541 delete from fnd_views
1542 where application_id = appl_id
1543 and view_id = vw_id;
1544
1545 end RemoveView;
1546
1547 --
1548 -- RemoveTable (PUBLIC)
1549 -- Remove table from FND_TABLES and all its columns, indexes, primary
1550 -- keys and foreign keys.
1551 --
1552 procedure RemoveTable(
1553 x_application_short_name in varchar2,
1554 x_table_name in varchar2) is
1555 appl_id number;
1556 tab_id number;
1557
1558 cursor ind is
1559 select index_name
1560 from fnd_indexes
1561 where application_id = appl_id
1562 and table_id = tab_id;
1563
1564 cursor fk is
1565 select foreign_key_name
1566 from fnd_foreign_keys
1567 where application_id = appl_id
1568 and table_id = tab_id;
1569
1570 cursor pk is
1571 select primary_key_name
1572 from fnd_primary_keys
1573 where application_id = appl_id
1574 and table_id = tab_id;
1575
1576 begin
1577
1578 begin
1579 select application_id
1580 into appl_id
1581 from fnd_application
1582 where application_short_name = upper(x_application_short_name);
1583 exception
1584 when no_data_found then
1585 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1586 fnd_message.set_token('TABLE', 'FND_APPLICATION');
1587 fnd_message.set_token('COLUMN', 'APPLICATION_SHORT_NAME');
1588 fnd_message.set_token('VALUE', x_application_short_name);
1589 app_exception.raise_exception;
1590 return;
1591 end;
1592
1593 begin
1594 select table_id
1595 into tab_id
1596 from fnd_tables
1597 where application_id = appl_id
1598 and table_name = upper(x_table_name);
1599 exception
1600 when no_data_found then
1601 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1602 fnd_message.set_token('TABLE', 'FND_TABLES');
1603 fnd_message.set_token('COLUMN', 'TABLE_NAME');
1604 fnd_message.set_token('VALUE', x_table_name);
1605 app_exception.raise_exception;
1606 return;
1607 end;
1608
1609 -- Before removing this table, remove all the children.
1610
1611 -- Remove indexes
1612 for c_ind in ind loop
1613 RemoveIndex(x_application_short_name, x_table_name, c_ind.index_name);
1614 end loop;
1615
1616 -- Remove foreign keys
1617 for c_fk in fk loop
1618 RemoveForeignKey(x_application_short_name, x_table_name,
1619 c_fk.foreign_key_name);
1620 end loop;
1621
1622 -- Remove primary keys
1623 for c_pk in pk loop
1624 RemovePrimaryKey(x_application_short_name, x_table_name,
1625 c_pk.primary_key_name);
1626 end loop;
1627
1628 -- Remove columns
1629 delete from fnd_columns
1630 where application_id = appl_id
1631 and table_id = tab_id;
1632
1633 -- Remove table itself
1634 delete from fnd_tables
1635 where application_id = appl_id
1636 and table_id = tab_id;
1637
1638 end RemoveTable;
1639
1640 --
1641 -- UploadTable (PUBLIC)) - Overloaded
1642 -- Public procedure for afdict.lct to call when uploading tables using
1643 -- using afdict.lct. It calls InsertTable() when needed.
1644 --
1645 procedure UploadTable (
1646 x_application_short_name in varchar2,
1647 x_table_name in varchar2,
1648 x_user_table_name in varchar2,
1649 x_table_type in varchar2,
1650 x_description in varchar2,
1651 x_auto_size in varchar2,
1652 x_initial_extent in varchar2,
1653 x_next_extent in varchar2,
1654 x_min_extents in varchar2,
1655 x_max_extents in varchar2,
1656 x_ini_trans in varchar2,
1657 x_max_trans in varchar2,
1658 x_pct_free in varchar2,
1659 x_pct_increase in varchar2,
1660 x_pct_used in varchar2,
1661 x_hosted_support_style in varchar2,
1662 x_user_id in varchar2,
1663 x_custom_mode in varchar2,
1664 x_last_update_date in varchar2
1665 ) is
1666 appl_id number;
1667 dummy varchar2(1);
1668 f_luby number; -- entity owner in file
1669 f_ludate date; -- entity update date in file
1670 db_luby number; -- entity owner in db
1671 db_ludate date; -- entity update date in db
1672 begin
1673
1674 -- Validate application
1675 begin
1676 select A.APPLICATION_ID
1677 into appl_id
1678 from FND_APPLICATION A
1679 where A.APPLICATION_SHORT_NAME = x_application_short_name;
1680 exception
1681 when no_data_found then
1682 fnd_message.set_name('FND', 'FND-APPLICATION NAME');
1683 fnd_message.set_token('APPLICATION_NAME', x_application_short_name);
1684 app_exception.raise_exception;
1685 end;
1686
1687 -- Validate hosted support style
1688 begin
1689 if (x_hosted_support_style <> 'LOCAL') then
1690 select 'X' into dummy
1691 from fnd_lookups
1692 where lookup_type = 'HOSTED_SUPPORT_STYLE'
1693 and lookup_code = x_hosted_support_style;
1694 end if;
1695 exception
1696 when no_data_found then
1697 fnd_message.set_name('FND', 'FND-HOSTED SUPPORT STYLE');
1698 fnd_message.set_token('HOSTED_SUPPORT_STYLE', x_hosted_support_style);
1699 app_exception.raise_exception;
1700 end;
1701
1702 -- Translate owner to file_last_updated_by
1703 f_luby := fnd_load_util.owner_id(x_user_id);
1704
1705 -- Translate char last_update_date to date
1709 select LAST_UPDATED_BY, LAST_UPDATE_DATE
1706 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
1707
1708 begin
1710 into db_luby, db_ludate
1711 from FND_TABLES
1712 where APPLICATION_ID = appl_id
1713 and TABLE_NAME = x_table_name;
1714
1715 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
1716 db_ludate, X_CUSTOM_MODE)) then
1717
1718 -- Resolve USER_TABLE_NAME by pre-pending '@'
1719 update FND_TABLES
1720 set USER_TABLE_NAME = '@'||USER_TABLE_NAME
1721 where APPLICATION_ID = appl_id
1722 and TABLE_NAME <> x_table_name
1723 and USER_TABLE_NAME = x_user_table_name;
1724
1725 update FND_TABLES set
1726 USER_TABLE_NAME = x_user_table_name,
1727 TABLE_TYPE = x_table_type,
1728 DESCRIPTION = x_description,
1729 AUTO_SIZE = x_auto_size,
1730 INITIAL_EXTENT = x_initial_extent,
1731 NEXT_EXTENT = x_next_extent,
1732 MIN_EXTENTS = x_min_extents,
1733 MAX_EXTENTS = x_max_extents,
1734 INI_TRANS = x_ini_trans,
1735 MAX_TRANS = x_max_trans,
1736 PCT_FREE = x_pct_free,
1737 PCT_INCREASE = x_pct_increase,
1738 PCT_USED = x_pct_used,
1739 HOSTED_SUPPORT_STYLE = x_hosted_support_style,
1740 LAST_UPDATED_BY = f_luby,
1741 LAST_UPDATE_DATE = f_ludate,
1742 LAST_UPDATE_LOGIN = f_luby
1746 exception
1743 where APPLICATION_ID = appl_id
1744 and TABLE_NAME = x_table_name;
1745 end if;
1747 when no_data_found then
1748 Fnd_Dictionary_Pkg.InsertTable(
1749 appl_id,
1750 x_table_name,
1751 x_user_table_name,
1752 x_table_type,
1753 x_description,
1754 x_auto_size,
1755 x_initial_extent,
1756 x_next_extent,
1757 x_min_extents,
1758 x_max_extents,
1759 x_ini_trans,
1760 x_max_trans,
1761 x_pct_free,
1762 x_pct_increase,
1763 x_pct_used,
1764 x_hosted_support_style,
1765 f_ludate,
1766 f_luby,
1767 f_ludate,
1768 f_luby,
1769 0);
1770 end;
1771 end UploadTable;
1772
1773 --
1774 -- UploadColumn (PUBLIC)) - Overloaded
1775 -- Public procedure for afdict.lct to call when uploading columns using
1776 -- using afdict.lct. It calls InsertColumn() when needed.
1777 --
1778 procedure UploadColumn (
1779 x_application_short_name in varchar2,
1780 x_table_name in varchar2,
1781 x_column_name in varchar2,
1782 x_user_column_name in varchar2,
1783 x_column_sequence in varchar2,
1784 x_column_type in varchar2,
1785 x_width in varchar2,
1786 x_null_allowed_flag in varchar2,
1787 x_description in varchar2,
1788 x_default_value in varchar2,
1789 x_translate_flag in varchar2,
1790 x_precision in varchar2,
1791 x_scale in varchar2,
1792 x_flexfield_usage_code in varchar2,
1793 x_flexfield_application_id in varchar2,
1794 x_flexfield_name in varchar2,
1795 x_flex_value_set_app_id in varchar2,
1796 x_flex_value_set_id in varchar2,
1797 x_user_id in varchar2,
1798 x_custom_mode in varchar2,
1799 x_last_update_date in varchar2
1800 ) is
1801 tab_id number;
1802 appl_id number;
1803 f_luby number; -- entity owner in file
1804 f_ludate date; -- entity update date in file
1805 db_luby number; -- entity owner in db
1806 db_ludate date; -- entity update date in db
1807 begin
1808
1809 -- Because Column is in the same entity as Table, no need to validate
1810 -- Application and Table again.
1811 select A.APPLICATION_ID
1812 into appl_id
1813 from FND_APPLICATION A
1814 where A.APPLICATION_SHORT_NAME = x_application_short_name;
1815
1816 select T.TABLE_ID into tab_id from FND_TABLES T
1817 where T.APPLICATION_ID = appl_id
1818 and T.TABLE_NAME = x_table_name;
1819
1820 ResolveConflictColumn(appl_id, tab_id, x_column_name, x_user_column_name,
1821 x_column_sequence);
1822
1823 -- Translate owner to file_last_updated_by
1824 f_luby := fnd_load_util.owner_id(x_user_id);
1825
1826 -- Translate char last_update_date to date
1827 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
1828
1829 begin
1830 select LAST_UPDATED_BY, LAST_UPDATE_DATE
1831 into db_luby, db_ludate
1832 from FND_COLUMNS
1833 where APPLICATION_ID = appl_id
1834 and TABLE_ID = tab_id
1835 and COLUMN_NAME = x_column_name;
1836
1837 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
1838 db_ludate, X_CUSTOM_MODE)) then
1839
1840 update FND_COLUMNS set
1841 USER_COLUMN_NAME = x_user_column_name,
1842 COLUMN_SEQUENCE = x_column_sequence,
1843 COLUMN_TYPE = x_column_type,
1844 WIDTH = x_width,
1845 NULL_ALLOWED_FLAG = x_null_allowed_flag,
1846 DESCRIPTION = x_description,
1847 DEFAULT_VALUE = x_default_value,
1848 TRANSLATE_FLAG = x_translate_flag,
1849 PRECISION = x_precision,
1850 SCALE = x_scale,
1851 FLEXFIELD_USAGE_CODE = x_flexfield_usage_code,
1852 FLEXFIELD_APPLICATION_ID = x_flexfield_application_id,
1853 FLEXFIELD_NAME = x_flexfield_name,
1854 FLEX_VALUE_SET_APPLICATION_ID = x_flex_value_set_app_id,
1855 FLEX_VALUE_SET_ID = x_flex_value_set_id,
1856 LAST_UPDATED_BY = f_luby,
1857 LAST_UPDATE_DATE = f_ludate,
1858 LAST_UPDATE_LOGIN = f_luby
1859 where APPLICATION_ID = appl_id
1860 and TABLE_ID = tab_id
1861 and COLUMN_NAME = x_column_name;
1862 end if;
1863 exception
1864 when no_data_found then
1865 Fnd_Dictionary_Pkg.InsertColumn(
1866 appl_id,
1867 tab_id,
1868 x_column_name,
1869 x_user_column_name,
1870 x_column_sequence,
1871 x_column_type,
1872 x_width,
1873 x_null_allowed_flag,
1874 x_description,
1875 x_default_value,
1876 x_translate_flag,
1877 x_precision,
1878 x_scale,
1879 x_flexfield_usage_code,
1880 x_flexfield_application_id,
1881 x_flexfield_name,
1882 x_flex_value_set_app_id,
1883 x_flex_value_set_id,
1884 f_ludate,
1885 f_luby,
1886 f_ludate,
1887 f_luby,
1888 0);
1889 end;
1890 end UploadColumn;
1891
1892 --
1893 -- UploadHistColumn (PUBLIC))
1897 procedure UploadHistColumn (
1894 -- Public procedure for afdict.lct to call when uploading columns using
1895 -- using afdict.lct. It calls InsertHistColumn() when needed.
1896 --
1898 x_application_short_name in varchar2,
1899 x_table_name in varchar2,
1900 x_column_name in varchar2,
1901 x_partition in varchar2,
1902 x_hsize in varchar2,
1903 x_user_id in varchar2,
1904 x_custom_mode in varchar2,
1905 x_last_update_date in varchar2
1906 ) is
1907 appl_id number;
1908 f_luby number; -- entity owner in file
1909 f_ludate date; -- entity update date in file
1910 db_luby number; -- entity owner in db
1911 db_ludate date; -- entity update date in db
1912 begin
1913
1914 -- Because Column is in the same entity as Table, no need to validate
1915 -- Application and Table again.
1916 select A.APPLICATION_ID
1917 into appl_id
1918 from FND_APPLICATION A
1919 where A.APPLICATION_SHORT_NAME = x_application_short_name;
1920
1921 -- Translate owner to file_last_updated_by
1922 f_luby := fnd_load_util.owner_id(x_user_id);
1923
1924 -- Translate char last_update_date to date
1925 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
1926
1927 begin
1928 select LAST_UPDATED_BY, LAST_UPDATE_DATE
1929 into db_luby, db_ludate
1930 from FND_HISTOGRAM_COLS
1931 where APPLICATION_ID = appl_id
1932 and TABLE_NAME = x_table_name
1933 and COLUMN_NAME = x_column_name;
1934
1935 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
1936 db_ludate, X_CUSTOM_MODE)) then
1937
1938 update FND_HISTOGRAM_COLS set
1939 PARTITION = x_partition,
1940 HSIZE = x_hsize,
1941 LAST_UPDATED_BY = f_luby,
1942 LAST_UPDATE_DATE = f_ludate,
1943 LAST_UPDATE_LOGIN = f_luby
1944 where APPLICATION_ID = appl_id
1945 and TABLE_NAME = x_table_name
1946 and COLUMN_NAME = x_column_name;
1947 end if;
1948 exception
1949 when no_data_found then
1950 insert into FND_HISTOGRAM_COLS (
1951 APPLICATION_ID,
1952 TABLE_NAME,
1953 COLUMN_NAME,
1954 PARTITION,
1955 HSIZE,
1956 CREATION_DATE,
1957 CREATED_BY,
1958 LAST_UPDATED_BY,
1959 LAST_UPDATE_DATE,
1960 LAST_UPDATE_LOGIN)
1961 values (
1962 appl_id,
1963 x_table_name,
1964 x_column_name,
1965 x_partition,
1966 x_hsize,
1967 f_ludate,
1968 f_luby,
1969 f_luby,
1970 f_ludate,
1971 f_luby);
1972 end;
1973 end UploadHistColumn;
1974
1975
1976
1977 --
1978 -- UploadIndex (PUBLIC))
1979 -- Public procedure for afdict.lct to call when uploading indexes using
1980 -- using afdict.lct. It calls InsertIndex() when needed.
1981 --
1982 procedure UploadIndex (
1983 x_application_short_name in varchar2,
1984 x_table_name in varchar2,
1985 x_index_name in varchar2,
1986 x_uniqueness in varchar2,
1987 x_auto_size in varchar2,
1988 x_description in varchar2,
1989 x_initial_extent in varchar2,
1990 x_next_extent in varchar2,
1991 x_min_extents in varchar2,
1992 x_max_extents in varchar2,
1993 x_ini_trans in varchar2,
1994 x_max_trans in varchar2,
1995 x_pct_free in varchar2,
1996 x_pct_increase in varchar2,
1997 x_user_id in varchar2,
1998 x_custom_mode in varchar2,
1999 x_last_update_date in varchar2,
2000 x_phase_mode in varchar2
2001 ) is
2002 tab_id number;
2003 appl_id number;
2004 f_luby number; -- entity owner in file
2005 f_ludate date; -- entity update date in file
2006 db_luby number; -- entity owner in db
2007 db_ludate date; -- entity update date in db
2008 -- Bug2631776 new variables to handle update.
2009 child_file_ludate date; -- child entity update date in file
2010 child_file_luby number; -- child owner in file
2011 child_db_ludate date; -- child update date in db
2012 child_db_luby number; -- child owner in db
2013 ind_id number;
2014
2015 begin
2016
2017 -- Because Index is in the same entity as Table, no need to validate
2018 -- Application and Table again.
2019
2020 select A.APPLICATION_ID
2021 into appl_id
2022 from FND_APPLICATION A
2023 where A.APPLICATION_SHORT_NAME = x_application_short_name;
2024
2025 select T.TABLE_ID into tab_id from FND_TABLES T
2026 where T.APPLICATION_ID = appl_id
2027 and T.TABLE_NAME = x_table_name;
2028
2029 -- Bug2631776 In this section handle the parent entity
2030 -- and update the child entity so that constraints do not occur.
2031
2032 if (x_phase_mode = 'BEGIN') then
2033
2034 -- Translate owner to file_last_updated_by
2035 f_luby := fnd_load_util.owner_id(x_user_id);
2036
2037 -- Translate char last_update_date to date
2038 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
2039
2040 begin
2041
2042 select INDEX_ID
2043 into ind_id
2044 from FND_INDEXES
2045 where APPLICATION_ID = appl_id
2046 and TABLE_ID = tab_id
2047 and INDEX_NAME = x_index_name;
2048
2049 select LAST_UPDATED_BY, LAST_UPDATE_DATE
2050 into db_luby, db_ludate
2051 from FND_INDEXES
2052 where APPLICATION_ID = appl_id
2053 and TABLE_ID = tab_id
2054 and INDEX_NAME = x_index_name;
2055
2056 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
2057 db_ludate, X_CUSTOM_MODE)) then
2058
2059 update FND_INDEXES set
2060 UNIQUENESS = x_uniqueness,
2061 AUTO_SIZE = x_auto_size,
2062 DESCRIPTION = x_description,
2063 INITIAL_EXTENT = x_initial_extent,
2064 NEXT_EXTENT = x_next_extent,
2065 MIN_EXTENTS = x_min_extents,
2066 MAX_EXTENTS = x_max_extents,
2067 INI_TRANS = x_ini_trans,
2068 MAX_TRANS = x_max_trans,
2069 PCT_FREE = x_pct_free,
2070 PCT_INCREASE = x_pct_increase,
2071 LAST_UPDATED_BY = f_luby,
2072 LAST_UPDATE_DATE = f_ludate,
2073 LAST_UPDATE_LOGIN = f_luby
2074 where APPLICATION_ID = appl_id
2075 and TABLE_ID = tab_id
2076 and INDEX_NAME = x_index_name;
2077
2078 end if;
2079
2083 delete from FND_INDEX_COLUMNS
2080 -- Bug3230044 Delete any child records with a negative
2081 -- value for COLUMN_ID.
2082
2084 where APPLICATION_ID = appl_id
2085 and TABLE_ID = tab_id
2086 and INDEX_ID = ind_id
2087 and COLUMN_ID < 0;
2088
2089 -- BUG2631776 rename the child record's COLUMN_SEQUENCE
2090 -- and COLUMN_ID values to a negative value in order to
2091 -- prevent unique constraints while processing the
2092 -- PARENT/CHILD entity.
2093
2094 update FND_INDEX_COLUMNS
2095 set COLUMN_SEQUENCE = -1 * COLUMN_SEQUENCE,
2096 COLUMN_ID = -1 * COLUMN_ID
2097 where APPLICATION_ID = appl_id
2098 and TABLE_ID = tab_id
2099 and INDEX_ID = ind_id;
2100
2101 /*Bug2773876 - Handle special case where COLUMN_SEQUENCE = 0 */
2102
2103 update FND_INDEX_COLUMNS
2104 set COLUMN_SEQUENCE = -1000
2105 where APPLICATION_ID = appl_id
2106 and TABLE_ID = tab_id
2107 and INDEX_ID = ind_id
2108 and COLUMN_SEQUENCE = 0;
2109
2110 exception
2111 when no_data_found then
2112 Fnd_Dictionary_Pkg.InsertIndex(
2113 appl_id,
2114 tab_id,
2115 x_index_name,
2116 x_uniqueness,
2117 x_auto_size,
2118 x_description,
2119 x_initial_extent,
2120 x_next_extent,
2121 x_min_extents,
2122 x_max_extents,
2123 x_ini_trans,
2124 x_max_trans,
2125 x_pct_free,
2126 x_pct_increase,
2127 f_ludate,
2128 f_luby,
2129 f_ludate,
2130 f_luby,
2131 0);
2132 end;
2133
2134 else -- phase_mode = 'END'
2135
2136 select INDEX_ID
2137 into ind_id
2138 from FND_INDEXES
2139 where APPLICATION_ID = appl_id
2140 and TABLE_ID = tab_id
2141 and INDEX_NAME = x_index_name;
2142
2143 -- Bug2631776 get the latest value for the last update for the db entity
2144 -- and the file entity.
2145
2146 select max(last_update_date)
2147 into child_db_ludate
2148 from fnd_index_columns
2149 where application_id = appl_id
2150 and table_id = tab_id
2151 and index_id = ind_id
2152 and column_sequence < 0
2153 and column_id < 0;
2154
2155 -- Bug3139883 changed select to also include value if column_sequence =0
2156
2157 select max(last_update_date)
2158 into child_file_ludate
2159 from fnd_index_columns
2160 where application_id = appl_id
2161 and table_id = tab_id
2162 and index_id = ind_id
2163 and column_sequence >= 0
2164 and column_id > 0;
2165
2166 -- If no value which means there were no existing child records
2167 -- in the database therefore skip to the end since the new child
2168 -- records have been updated.
2169
2170 if (child_db_ludate IS NULL) or (child_file_ludate IS NULL) then
2171 GOTO done_label;
2172 end if;
2173
2174 -- Continue on to check the owner value since both have columns.
2175
2176 -- Bug2631776 get the maximum value for the userid that made the
2177 -- last update for the db entity and the file entity.
2178
2179 -- If any non-seed owners, set owner to user
2180 select max(-1)
2181 into child_db_luby
2182 from fnd_index_columns
2183 where application_id = appl_id
2184 and table_id = tab_id
2185 and index_id = ind_id
2186 and column_sequence < 0
2187 and column_id < 0
2188 and last_updated_by not in (0,1,2);
2189
2190 if child_db_luby IS NULL then
2191 child_db_luby := 2; -- All rows are seed data, set seed data owner
2192 end if;
2193
2194 -- If any non-seed owners, set owner to user
2195 select max(-1)
2196 into child_file_luby
2197 from fnd_index_columns
2198 where application_id = appl_id
2202 and column_id > 0
2199 and table_id = tab_id
2200 and index_id = ind_id
2201 and column_sequence > 0
2203 and last_updated_by not in (0,1,2);
2204
2205 if child_file_luby IS NULL then
2206 child_file_luby := 2; -- All rows are seed data, set seed data owner
2207 end if;
2208
2209 -- Bug2631776 perform check to see if update occurs or not.
2210
2211 if (fnd_load_util.upload_test(child_file_luby, child_file_ludate, child_db_luby, child_db_ludate, x_custom_mode)) then
2212
2213 -- The new child entity rows from the data file are kept so
2214 -- delete the existing db child entity rows.
2215
2216 delete from fnd_index_columns
2217 where application_id = appl_id
2218 and table_id = tab_id
2219 and index_id = ind_id
2220 and column_sequence < 0
2221 and column_id < 0;
2222
2223 else
2224
2225 -- The existing db child entity rows are kept so delete the new child
2226 -- entity rows from the data file
2227 -- Bug3139883 - Modified delete to include the value column_sequence = 0
2228
2229 delete from fnd_index_columns
2230 where application_id = appl_id
2231 and table_id = tab_id
2232 and index_id = ind_id
2233 and column_sequence >= 0
2234 and column_id > 0;
2235
2236 -- Rename the existing db entity rows back to normal since
2237 -- it was not replaced by the new child entity rows
2238 -- from the data file.
2239
2240 update FND_INDEX_COLUMNS
2241 set COLUMN_SEQUENCE = -1 * COLUMN_SEQUENCE,
2242 COLUMN_ID = -1 * COLUMN_ID
2243 where APPLICATION_ID = appl_id
2244 and TABLE_ID = tab_id
2245 and INDEX_ID = ind_id;
2246
2247 /*Bug2773876 - Handle special case where COLUMN_SEQUENCE = 0 */
2248
2249 update FND_INDEX_COLUMNS
2250 set COLUMN_SEQUENCE = 0
2251 where APPLICATION_ID = appl_id
2252 and TABLE_ID = tab_id
2253 and INDEX_ID = ind_id
2254 and COLUMN_SEQUENCE = 1000;
2255
2256 end if;
2257 <<done_label>>
2258
2259 -- check if the file has no child entries to clean up database.
2260
2261 if (child_file_ludate IS NULL) then
2262
2263 if (child_db_ludate IS NOT NULL) then
2264
2265 -- Rename the existing db entity rows back to normal since
2266 -- it was not replaced by the new child entity rows
2267 -- from the data file.
2268
2269 update FND_INDEX_COLUMNS
2270 set COLUMN_SEQUENCE = -1 * COLUMN_SEQUENCE,
2271 COLUMN_ID = -1 * COLUMN_ID
2272 where APPLICATION_ID = appl_id
2273 and TABLE_ID = tab_id
2274 and INDEX_ID = ind_id;
2275
2276 /*Bug2773876 - Handle special case where COLUMN_SEQUENCE = 0 */
2277
2278 update FND_INDEX_COLUMNS
2279 set COLUMN_SEQUENCE = 0
2280 where APPLICATION_ID = appl_id
2281 and TABLE_ID = tab_id
2282 and INDEX_ID = ind_id
2283 and COLUMN_SEQUENCE = 1000;
2284
2285 end if;
2286 end if;
2287 end if;
2288 end UploadIndex;
2289
2290 --
2291 -- UploadIndexColumn (PUBLIC))
2292 -- Public procedure for afdict.lct to call when uploading index columns using
2293 -- using afdict.lct.
2294 --
2295 procedure UploadIndexColumn (
2296 x_application_short_name in varchar2,
2297 x_table_name in varchar2,
2298 x_index_name in varchar2,
2299 x_index_column_name in varchar2,
2300 x_index_column_sequence in varchar2,
2301 x_user_id in varchar2,
2302 x_custom_mode in varchar2,
2303 x_last_update_date in varchar2
2304 ) is
2305 tab_id number;
2306 appl_id number;
2307 idx_id number;
2308 col_id number;
2309 f_luby number; -- entity owner in file
2310 f_ludate date; -- entity update date in file
2311 begin
2312
2313 -- Because Index Column is in the same entity as Table and Index,
2314 -- no need to validate them again.
2315 select A.APPLICATION_ID
2316 into appl_id
2317 from FND_APPLICATION A
2318 where A.APPLICATION_SHORT_NAME = x_application_short_name;
2319
2320 select T.TABLE_ID into tab_id from FND_TABLES T
2321 where T.APPLICATION_ID = appl_id
2322 and T.TABLE_NAME = x_table_name;
2323
2324 select I.INDEX_ID into idx_id from FND_INDEXES I
2325 where I.APPLICATION_ID = appl_id
2326 and I.TABLE_ID = tab_id
2327 and I.INDEX_NAME = x_index_name;
2328
2329 begin
2330 select C.COLUMN_ID into col_id from FND_COLUMNS C
2331 where C.APPLICATION_ID = appl_id
2332 and C.TABLE_ID = tab_id
2333 and C.COLUMN_NAME = x_index_column_name;
2334 exception
2335 when no_data_found then
2336 fnd_message.set_name('FND', 'FND-COLUMN NAME');
2337 fnd_message.set_token('COLUMN_NAME', x_index_column_name);
2338 fnd_message.set_token('OBJECT_TYPE', 'Index');
2339 fnd_message.set_token('OBJECT_NAME', x_index_name);
2340 app_exception.raise_exception;
2341 end;
2342
2343 -- Translate owner to file_last_updated_by
2344 f_luby := fnd_load_util.owner_id(x_user_id);
2345
2346 -- Translate char last_update_date to date
2347 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
2348
2349 -- NOTE: no "UPDATE" case as we have renamed all the existing
2353
2350 -- index columns so that they can be compared against the new
2351 -- index columns from the data file to determine which will be
2352 -- updated into the database based on the date and custom factirs.
2354 begin
2355 insert into FND_INDEX_COLUMNS(
2356 APPLICATION_ID,
2357 TABLE_ID,
2358 INDEX_ID,
2359 COLUMN_ID,
2360 COLUMN_SEQUENCE,
2361 LAST_UPDATED_BY,
2362 LAST_UPDATE_DATE,
2363 LAST_UPDATE_LOGIN,
2364 CREATION_DATE,
2365 CREATED_BY)
2366 values (
2367 appl_id,
2368 tab_id,
2369 idx_id,
2370 col_id,
2371 x_index_column_sequence,
2372 f_luby,
2373 f_ludate,
2374 f_luby,
2375 f_ludate,
2376 f_luby);
2377
2378 exception
2379 when dup_val_on_index then
2380 fnd_message.set_name('FND', 'FND-DUPLICATE COLUMN SEQUENCE');
2381 fnd_message.set_token('COLUMN_SEQUENCE', x_index_column_sequence);
2382 fnd_message.set_token('OBJECT_NAME', x_index_name);
2383 app_exception.raise_exception;
2384 end;
2385
2386 end UploadIndexColumn;
2387
2388 --
2389 -- UploadPrimaryKey (PUBLIC))
2390 -- Public procedure for afdict.lct to call when uploading primary key using
2391 -- using afdict.lct. It calls InsertPrimary() when needed.
2392 --
2393 procedure UploadPrimaryKey (
2394 x_application_short_name in varchar2,
2395 x_table_name in varchar2,
2396 x_primary_key_name in varchar2,
2397 x_primary_key_type in varchar2,
2398 x_audit_key_flag in varchar2,
2399 x_description in varchar2,
2400 x_enabled_flag in varchar2,
2401 x_user_id in varchar2,
2402 x_custom_mode in varchar2,
2403 x_last_update_date in varchar2,
2404 x_phase_mode in varchar2
2405 ) is
2406 tab_id number;
2407 appl_id number;
2408 pk_id number;
2409 tmpid number;
2410 pkmode varchar2(10);
2411 f_luby number; -- entity owner in file
2412 f_ludate date; -- entity update date in file
2413 db_luby number; -- entity owner in db
2414 db_ludate date; -- entity update date in db
2415
2416 -- Bug2631776 new variables to handle update.
2417 child_file_ludate date; -- child entity update date in file
2418 child_file_luby number; -- child owner in file
2419 child_db_ludate date; -- child update date in db
2420 child_db_luby number; -- child owner in db
2421
2422 begin
2423
2424 -- Because Primary Key is in the same entity as Table, no need to validate
2425 -- Application and Table again.
2426 select A.APPLICATION_ID
2427 into appl_id
2428 from FND_APPLICATION A
2429 where A.APPLICATION_SHORT_NAME = x_application_short_name;
2430
2431 select T.TABLE_ID into tab_id from FND_TABLES T
2432 where T.APPLICATION_ID = appl_id
2433 and T.TABLE_NAME = x_table_name;
2434
2435 -- Validation on primary_key_type, audit_flag and enabled_flag
2436
2437 pk_id := -1;
2438 ValidatePrimaryKey(x_application_short_name,
2439 x_table_name, x_primary_key_name, tmpid, tmpid, pk_id);
2440 if (pk_id = -1) then
2441 pkmode := 'INSERT';
2442 else
2443 pkmode := 'UPDATE';
2444 end if;
2445
2446 if ((pkmode = 'INSERT' and x_primary_key_type not in ('S', 'D')) or
2447 (pkmode = 'UPDATE' and nvl(x_primary_key_type, 'S') not in ('S', 'D'))) then
2448 fnd_message.set_name('FND', 'FND-INVALID PRIMARY KEY ATTR');
2449 fnd_message.set_token('ATTRIBUTE_NAME', 'Type');
2450 fnd_message.set_token('ATTRIBUTE_VALUE', x_primary_key_type);
2451 fnd_message.set_token('PRIMARY_KEY', x_primary_key_name);
2452 app_exception.raise_exception;
2453 end if;
2454
2455 if ((pkmode = 'INSERT' and x_audit_key_flag not in ('Y', 'N')) or
2456 (pkmode = 'UPDATE' and nvl(x_audit_key_flag, 'Y') not in ('Y', 'N'))) then
2457 fnd_message.set_name('FND', 'FND-INVALID PRIMARY KEY ATTR');
2458 fnd_message.set_token('ATTRIBUTE_NAME', 'Audit Key');
2459 fnd_message.set_token('ATTRIBUTE_VALUE', x_audit_key_flag);
2460 fnd_message.set_token('PRIMARY_KEY', x_primary_key_name);
2461 app_exception.raise_exception;
2462 end if;
2463
2464 if ((pkmode = 'INSERT' and x_enabled_flag not in ('Y', 'N')) or
2468 fnd_message.set_token('ATTRIBUTE_VALUE', x_enabled_flag);
2465 (pkmode = 'UPDATE' and nvl(x_enabled_flag, 'Y') not in ('Y', 'N'))) then
2466 fnd_message.set_name('FND', 'FND-INVALID PRIMARY KEY ATTR');
2467 fnd_message.set_token('ATTRIBUTE_NAME', 'Enabled Flag');
2469 fnd_message.set_token('PRIMARY_KEY', x_primary_key_name);
2470 app_exception.raise_exception;
2471 end if;
2472
2473 if (x_primary_key_type = 'D' and
2474 MultipleDeveloperKeys(appl_id, tab_id, x_primary_key_name)) then
2475 fnd_message.set_name('FND', 'FND-MULTIPLE DEVELOPER PK');
2476 fnd_message.set_token('TABLE_NAME', x_table_name);
2477 app_exception.raise_exception;
2478 end if;
2479
2480 -- Bug2631776 In this section handle the parent entity
2481 -- and update the child entity so that constraints do not occur.
2482
2483 if (x_phase_mode = 'BEGIN') then
2484
2485 -- Translate owner to file_last_updated_by
2486 f_luby := fnd_load_util.owner_id(x_user_id);
2487
2488 -- Translate char last_update_date to date
2489 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
2490
2491
2492 begin
2493 select LAST_UPDATED_BY, LAST_UPDATE_DATE
2494 into db_luby, db_ludate
2495 from FND_PRIMARY_KEYS
2496 where APPLICATION_ID = appl_id
2497 and TABLE_ID = tab_id
2498 and PRIMARY_KEY_NAME = x_primary_key_name;
2499
2500 if (pkmode = 'UPDATE') then
2501
2502 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
2503 db_ludate, X_CUSTOM_MODE)) then
2504 update FND_PRIMARY_KEYS set
2505 PRIMARY_KEY_TYPE = x_primary_key_type,
2506 AUDIT_KEY_FLAG = x_audit_key_flag,
2507 ENABLED_FLAG = x_enabled_flag,
2508 DESCRIPTION = x_description,
2509 LAST_UPDATED_BY = f_luby,
2510 LAST_UPDATE_DATE = f_ludate,
2511 LAST_UPDATE_LOGIN = f_luby
2512 where APPLICATION_ID = appl_id
2513 and TABLE_ID = tab_id
2514 and PRIMARY_KEY_NAME = x_primary_key_name;
2515 end if;
2516
2517 -- Bug3230044 Delete any child records with a negative
2518 -- value for COLUMN_ID.
2519
2520 delete from FND_PRIMARY_KEY_COLUMNS
2521 where APPLICATION_ID = appl_id
2522 and TABLE_ID = tab_id
2523 and PRIMARY_KEY_ID = pk_id
2524 and COLUMN_ID < 0;
2525
2526
2527 -- BUG2631776 rename the child record's PRIMARY_KEY_SEQUENCE
2528 -- and COLUMN_ID values to a negative value in order to
2529 -- prevent unique constraints while processing the
2530 -- PARENT/CHILD entity.
2531
2532 update FND_PRIMARY_KEY_COLUMNS
2533 set PRIMARY_KEY_SEQUENCE = -1 * PRIMARY_KEY_SEQUENCE,
2534 COLUMN_ID = -1 * COLUMN_ID
2535 where APPLICATION_ID = appl_id
2536 and TABLE_ID = tab_id
2537 and PRIMARY_KEY_ID = pk_id;
2538
2539 /*Bug3139883 - Handle special case where PRIMARY_KEY_SEQUENCE = 0 */
2540
2541 update FND_PRIMARY_KEY_COLUMNS
2542 set PRIMARY_KEY_SEQUENCE = -1000
2543 where APPLICATION_ID = appl_id
2544 and TABLE_ID = tab_id
2545 and PRIMARY_KEY_ID = pk_id
2546 and PRIMARY_KEY_SEQUENCE = 0;
2547
2548 else
2549 Fnd_Dictionary_Pkg.InsertPrimaryKey(
2550 appl_id,
2551 tab_id,
2552 x_primary_key_name,
2553 x_primary_key_type,
2554 x_audit_key_flag,
2555 x_enabled_flag,
2556 x_description,
2557 f_ludate,
2558 f_luby,
2559 f_ludate,
2560 f_luby,
2561 0);
2562 end if;
2563
2564 exception
2565 when no_data_found then
2566 Fnd_Dictionary_Pkg.InsertPrimaryKey(
2567 appl_id,
2568 tab_id,
2569 x_primary_key_name,
2570 x_primary_key_type,
2571 x_audit_key_flag,
2572 x_enabled_flag,
2573 x_description,
2574 f_ludate,
2575 f_luby,
2576 f_ludate,
2577 f_luby,
2578 0);
2579
2580 end;
2581
2582 else -- phase_mode = 'END'
2583
2584 -- Bug2631776 get the latest value for the last update for the db entity
2585 -- and the file entity.
2586
2587 select max(last_update_date)
2588 into child_db_ludate
2589 from fnd_primary_key_columns
2590 where application_id = appl_id
2591 and table_id = tab_id
2592 and primary_key_id = pk_id
2593 and primary_key_sequence < 0
2594 and column_id < 0;
2595
2596 -- Bug3139883 changed select to also include value if primary_key_sequence =0
2597
2598 select max(last_update_date)
2599 into child_file_ludate
2600 from fnd_primary_key_columns
2601 where application_id = appl_id
2602 and table_id = tab_id
2603 and primary_key_id = pk_id
2604 and PRIMARY_KEY_SEQUENCE >= 0
2605 and column_id > 0;
2606
2607 -- If no value which means there were no existing child records
2608 -- in the database therefore skip to the end since the new child
2609 -- records have been updated.
2610
2611 if (child_db_ludate IS NULL) or (child_file_ludate IS NULL) then
2612 GOTO done_label;
2613 end if;
2614
2615 -- Continue on to check the owner value since both have columns.
2616
2617 -- Bug2631776 get the maximum value for the userid that made the
2618 -- last update for the db entity and the file entity.
2619
2620 -- If any non-seed owners, set owner to user
2621 select max(-1)
2622 into child_db_luby
2623 from fnd_primary_key_columns
2624 where application_id = appl_id
2628 and column_id < 0
2625 and table_id = tab_id
2626 and primary_key_id = pk_id
2627 and PRIMARY_KEY_SEQUENCE < 0
2629 and last_updated_by not in (0,1,2);
2630
2631 if child_db_luby IS NULL then
2632 child_db_luby := 2; -- All rows are seed data, set seed data owner
2633 end if;
2634
2635 -- If any non-seed owners, set owner to user
2636 select max(-1)
2637 into child_file_luby
2638 from fnd_primary_key_columns
2639 where application_id = appl_id
2640 and table_id = tab_id
2641 and primary_key_id = pk_id
2642 and PRIMARY_KEY_SEQUENCE > 0
2643 and column_id > 0
2644 and last_updated_by not in (0,1,2);
2645
2646 if child_file_luby IS NULL then
2647 child_file_luby := 2; -- All rows are seed data, set seed data owner
2648 end if;
2649
2650 -- Bug2631776 perform check to see if update occurs or not.
2651
2652 if (fnd_load_util.upload_test(child_file_luby, child_file_ludate, child_db_luby, child_db_ludate, x_custom_mode)) then
2653
2654 -- The new child entity rows from the data file are kept so
2655 -- delete the existing db child entity rows.
2656
2657 delete from fnd_primary_key_columns
2658 where application_id = appl_id
2659 and table_id = tab_id
2660 and primary_key_id = pk_id
2661 and PRIMARY_KEY_SEQUENCE < 0
2662 and column_id < 0;
2663
2664 else
2665
2666 -- The existing db child entity rows are kept so delete the new child
2667 -- entity rows from the data file
2668 -- Bug3139883 - Modified delete to include the value column_sequence = 0
2669
2670 delete from fnd_primary_key_columns
2671 where application_id = appl_id
2672 and table_id = tab_id
2673 and primary_key_id = pk_id
2674 and PRIMARY_KEY_SEQUENCE >= 0
2675 and column_id > 0;
2676
2677 -- Rename the existing db entity rows back to normal since
2678 -- it was not replaced by the new child entity rows
2679 -- from the data file.
2680
2681 update FND_PRIMARY_KEY_COLUMNS
2682 set PRIMARY_KEY_SEQUENCE = -1 * PRIMARY_KEY_SEQUENCE,
2683 COLUMN_ID = -1 * COLUMN_ID
2684 where APPLICATION_ID = appl_id
2685 and TABLE_ID = tab_id
2686 and PRIMARY_KEY_ID = pk_id;
2687
2688 /*Bug3139883 - Handle special case where PRIMARY_KEY_SEQUENCE = 0 */
2689
2690 update FND_PRIMARY_KEY_COLUMNS
2691 set PRIMARY_KEY_SEQUENCE = 0
2692 where APPLICATION_ID = appl_id
2693 and TABLE_ID = tab_id
2694 and PRIMARY_KEY_ID = pk_id
2695 and PRIMARY_KEY_SEQUENCE = 1000;
2696
2697 end if;
2698 <<done_label>>
2699
2700 -- check if the file has no child entries to clean up database.
2701
2702 if (child_file_ludate IS NULL) then
2703
2704 if (child_db_ludate IS NOT NULL) then
2705
2706 -- Rename the existing db entity rows back to normal since
2707 -- it was not replaced by the new child entity rows
2708 -- from the data file.
2709
2710 update FND_PRIMARY_KEY_COLUMNS
2711 set PRIMARY_KEY_SEQUENCE = -1 * PRIMARY_KEY_SEQUENCE,
2712 COLUMN_ID = -1 * COLUMN_ID
2713 where APPLICATION_ID = appl_id
2714 and TABLE_ID = tab_id
2715 and PRIMARY_KEY_ID = pk_id;
2716
2717 /*Bug3139883 - Handle special case where PRIMARY_KEY_SEQUENCE = 0 */
2718
2719 update FND_PRIMARY_KEY_COLUMNS
2720 set PRIMARY_KEY_SEQUENCE = 0
2721 where APPLICATION_ID = appl_id
2722 and TABLE_ID = tab_id
2723 and PRIMARY_KEY_ID = pk_id
2724 and PRIMARY_KEY_SEQUENCE = 1000;
2725
2726 end if;
2727 end if;
2728 end if;
2729
2730 end UploadPrimaryKey;
2731
2732 --
2733 -- UploadPrimaryKeyColumn (PUBLIC))
2734 -- Public procedure for afdict.lct to call when uploading primary key column
2735 -- using afdict.lct.
2736 --
2737 procedure UploadPrimaryKeyColumn (
2738 x_application_short_name in varchar2,
2739 x_table_name in varchar2,
2740 x_primary_key_name in varchar2,
2741 x_primary_key_column_name in varchar2,
2742 x_primary_key_column_sequence in varchar2,
2743 x_user_id in varchar2,
2744 x_custom_mode in varchar2,
2745 x_last_update_date in varchar2
2746 ) is
2747 tab_id number;
2748 appl_id number;
2749 pk_id number;
2750 col_id number;
2751 f_luby number; -- entity owner in file
2752 f_ludate date; -- entity update date in file
2753
2754 begin
2755
2756 -- No need to validate/check Application, Table or Primary Key.
2757 -- Within the same entity.
2758 select A.APPLICATION_ID
2759 into appl_id
2760 from FND_APPLICATION A
2761 where A.APPLICATION_SHORT_NAME = x_application_short_name;
2762
2763 select T.TABLE_ID into tab_id from FND_TABLES T
2764 where T.APPLICATION_ID = appl_id
2765 and T.TABLE_NAME = x_table_name;
2766
2767 select P.PRIMARY_KEY_ID into pk_id from FND_PRIMARY_KEYS P
2768 where P.APPLICATION_ID = appl_id
2769 and P.TABLE_ID = tab_id
2770 and P.PRIMARY_KEY_NAME = x_primary_key_name;
2771
2772 begin
2773 select C.COLUMN_ID into col_id from FND_COLUMNS C
2774 where C.APPLICATION_ID = appl_id
2775 and C.TABLE_ID = tab_id
2776 and C.COLUMN_NAME = x_primary_key_column_name;
2777 exception
2778 when no_data_found then
2782 fnd_message.set_token('OBJECT_NAME', x_primary_key_name);
2779 fnd_message.set_name('FND', 'FND-COLUMN NAME');
2780 fnd_message.set_token('COLUMN_NAME', x_primary_key_column_name);
2781 fnd_message.set_token('OBJECT_TYPE', 'Primary Key');
2783 app_exception.raise_exception;
2784 end;
2785
2786 -- Translate owner to file_last_updated_by
2787 f_luby := fnd_load_util.owner_id(x_user_id);
2788
2789 -- Translate char last_update_date to date
2790 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
2791
2792 -- NOTE: no "UPDATE" case as we have renamed all the existing
2793 -- primary key columns so that they can be compared against the new
2794 -- primary key columns from the data file to determine which will be
2795 -- updated into the database based on the date and custom factirs.
2796
2797 begin
2798 insert into FND_PRIMARY_KEY_COLUMNS(
2799 APPLICATION_ID,
2800 TABLE_ID,
2801 PRIMARY_KEY_ID,
2802 COLUMN_ID,
2803 PRIMARY_KEY_SEQUENCE,
2804 LAST_UPDATED_BY,
2805 LAST_UPDATE_DATE,
2806 LAST_UPDATE_LOGIN,
2807 CREATION_DATE,
2808 CREATED_BY)
2809 values (
2810 appl_id,
2811 tab_id,
2812 pk_id,
2813 col_id,
2814 x_primary_key_column_sequence,
2815 f_luby,
2816 f_ludate,
2817 f_luby,
2818 f_ludate,
2819 f_luby);
2820 exception
2821 when dup_val_on_index then
2822 fnd_message.set_name('FND', 'FND-DUPLICATE COLUMN SEQUENCE');
2823 fnd_message.set_token('COLUMN_SEQUENCE', x_primary_key_column_sequence);
2824 fnd_message.set_token('OBJECT_NAME', x_primary_key_name);
2825 app_exception.raise_exception;
2826 end;
2827
2828 end UploadPrimaryKeyColumn;
2829
2830 --
2831 -- UploadForeignKey (PUBLIC))
2832 -- Public procedure for afdict.lct to call when uploading foreign key using
2833 -- using afdict.lct. It calls InsertForeign() when needed.
2834 --
2835 procedure UploadForeignKey (
2836 x_application_short_name in varchar2,
2837 x_table_name in varchar2,
2838 x_foreign_key_name in varchar2,
2839 x_primary_key_application_name in varchar2,
2840 x_primary_key_table_name in varchar2,
2841 x_primary_key_name in varchar2,
2842 x_description in varchar2,
2843 x_cascade_behavior in varchar2,
2844 x_foreign_key_relation in varchar2,
2845 x_condition in varchar2,
2846 x_enabled_flag in varchar2,
2847 x_user_id in varchar2,
2848 x_custom_mode in varchar2,
2849 x_last_update_date in varchar2,
2850 x_phase_mode in varchar2
2851 ) is
2852 tab_id number;
2853 appl_id number;
2854 pk_appl_id number;
2855 pk_tab_id number;
2856 pk_id number;
2857 f_luby number; -- entity owner in file
2858 f_ludate date; -- entity update date in file
2859 db_luby number; -- entity owner in db
2860 db_ludate date; -- entity update date in db
2861
2862 -- Bug2631776 new variables to handle update.
2863 child_file_ludate date; -- child entity update date in file
2864 child_file_luby number; -- child owner in file
2865 child_db_ludate date; -- child update date in db
2866 child_db_luby number; -- child owner in db
2867 fk_id number;
2868
2869 begin
2870
2871 select A.APPLICATION_ID
2872 into appl_id
2873 from FND_APPLICATION A
2874 where A.APPLICATION_SHORT_NAME = x_application_short_name;
2875
2876 select T.TABLE_ID into tab_id from FND_TABLES T
2877 where T.APPLICATION_ID = appl_id
2878 and T.TABLE_NAME = x_table_name;
2879
2880 -- Validate if primary key exists
2881
2882 pk_id := -1;
2883
2884 ValidatePrimaryKey(x_primary_key_application_name,
2885 x_primary_key_table_name,
2886 x_primary_key_name, pk_appl_id, pk_tab_id, pk_id);
2887
2888 if (pk_id = -1) then
2889 /*
2890 fnd_message.set_name('FND', 'INVALID_PRIMARY_KEY');
2891 fnd_message.set_token('PRIMARY_KEY', x_primary_key_name);
2892 fnd_message.set_token('TABLE_NAME', x_primary_key_table_name);
2893 fnd_message.set_token('APPLICATION_NAME', x_primary_key_application_name);
2894 fnd_message.set_token('FOREIGN_KEY', x_foreign_key_name);
2895 app_exception.raise_exception;
2896 */
2897 pk_appl_id := -1;
2898 pk_tab_id := -1;
2899
2900 end if;
2901
2902 if (x_phase_mode = 'BEGIN') then
2903
2904
2905 -- Translate owner to file_last_updated_by
2906 f_luby := fnd_load_util.owner_id(x_user_id);
2907
2908 -- Translate char last_update_date to date
2909 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
2910
2911 begin
2912
2913 select FOREIGN_KEY_ID
2914 into fk_id
2915 from FND_FOREIGN_KEYS
2916 where APPLICATION_ID = appl_id
2917 and TABLE_ID = tab_id
2918 and FOREIGN_KEY_NAME = x_foreign_key_name;
2919
2920 select LAST_UPDATED_BY, LAST_UPDATE_DATE
2921 into db_luby, db_ludate
2922 from FND_FOREIGN_KEYS
2923 where APPLICATION_ID = appl_id
2924 and TABLE_ID = tab_id
2925 and FOREIGN_KEY_NAME = x_foreign_key_name;
2926
2927 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
2928 db_ludate, X_CUSTOM_MODE)) then
2929
2930 update FND_FOREIGN_KEYS set
2931 PRIMARY_KEY_APPLICATION_ID = pk_appl_id,
2932 PRIMARY_KEY_TABLE_ID = pk_tab_id,
2933 PRIMARY_KEY_ID = pk_id,
2934 CASCADE_BEHAVIOR = x_cascade_behavior,
2938 ENABLED_FLAG = x_enabled_flag,
2935 FOREIGN_KEY_RELATION = x_foreign_key_relation,
2936 DESCRIPTION = x_description,
2937 CONDITION = x_condition,
2939 LAST_UPDATED_BY = f_luby,
2940 LAST_UPDATE_DATE = f_ludate,
2941 LAST_UPDATE_LOGIN = f_luby
2942 where APPLICATION_ID = appl_id
2943 and TABLE_ID = tab_id
2944 and FOREIGN_KEY_NAME = x_foreign_key_name;
2945
2946 end if;
2947
2948 -- Bug3230044 Delete any child records with a negative
2949 -- value for COLUMN_ID.
2950
2951 delete from FND_FOREIGN_KEY_COLUMNS
2952 where APPLICATION_ID = appl_id
2953 and TABLE_ID = tab_id
2954 and FOREIGN_KEY_ID = fk_id
2955 and COLUMN_ID < 0;
2956
2957
2958 -- BUG2631776 rename the child record's FOREIGN_KEY_SEQUENCE
2959 -- and COLUMN_ID values to a negative value in order to
2960 -- prevent unique constraints while processing the
2961 -- PARENT/CHILD entity.
2962
2963 update FND_FOREIGN_KEY_COLUMNS
2964 set FOREIGN_KEY_SEQUENCE = -1 * FOREIGN_KEY_SEQUENCE,
2965 COLUMN_ID = -1 * COLUMN_ID
2966 where APPLICATION_ID = appl_id
2967 and TABLE_ID = tab_id
2968 and FOREIGN_KEY_ID = fk_id;
2969
2970 /*Bug3139883 - Handle special case where FOREIGN_KEY_SEQUENCE = 0 */
2971
2972 update FND_FOREIGN_KEY_COLUMNS
2973 set FOREIGN_KEY_SEQUENCE = -1000
2974 where APPLICATION_ID = appl_id
2975 and TABLE_ID = tab_id
2976 and FOREIGN_KEY_ID = fk_id
2977 and FOREIGN_KEY_SEQUENCE = 0;
2978
2979 exception
2980 when no_data_found then
2981 Fnd_Dictionary_Pkg.InsertForeignKey(
2982 appl_id,
2983 tab_id,
2984 x_foreign_key_name,
2985 pk_appl_id,
2986 pk_tab_id,
2987 pk_id,
2988 x_description,
2989 x_cascade_behavior,
2990 x_foreign_key_relation,
2991 x_condition,
2992 x_enabled_flag,
2993 f_ludate,
2994 f_luby,
2995 f_ludate,
2996 f_luby,
2997 0);
2998 end;
2999
3000 else -- phase_mode = 'END'
3001
3002 select FOREIGN_KEY_ID
3003 into fk_id
3004 from FND_FOREIGN_KEYS
3005 where APPLICATION_ID = appl_id
3006 and TABLE_ID = tab_id
3007 and FOREIGN_KEY_NAME = x_foreign_key_name;
3008
3009
3010 -- Bug2631776 get the latest value for the last update for the db entity
3011 -- and the file entity.
3012
3013 select max(last_update_date)
3014 into child_db_ludate
3015 from fnd_foreign_key_columns
3016 where application_id = appl_id
3017 and table_id = tab_id
3018 and foreign_key_id = fk_id
3019 and foreign_key_sequence < 0
3020 and column_id < 0;
3021
3022 -- Bug3139883 changed select to also include value if foreign_key_sequence =0
3023
3024 select max(last_update_date)
3025 into child_file_ludate
3026 from fnd_foreign_key_columns
3027 where application_id = appl_id
3028 and table_id = tab_id
3029 and foreign_key_id = fk_id
3030 and foreign_key_sequence >= 0
3031 and column_id > 0;
3032
3033 -- If no value which means there were no existing child records
3034 -- in the database therefore skip to the end since the new child
3035 -- records have been updated.
3036
3037 if (child_db_ludate IS NULL) or (child_file_ludate IS NULL) then
3038 GOTO done_label;
3039 end if;
3040
3041 -- Continue on to check the owner value since both have columns.
3042
3043 -- Bug2631776 get the maximum value for the userid that made the
3044 -- last update for the db entity and the file entity.
3045
3046 -- If any non-seed owners, set owner to user
3047 select max(-1)
3048 into child_db_luby
3049 from fnd_foreign_key_columns
3050 where application_id = appl_id
3051 and table_id = tab_id
3052 and foreign_key_id = fk_id
3053 and foreign_key_sequence < 0
3054 and column_id < 0
3055 and last_updated_by not in (0,1,2);
3056
3057 if child_db_luby IS NULL then
3058 child_db_luby := 2; -- All rows are seed data, set seed data owner
3059 end if;
3060
3061 -- If any non-seed owners, set owner to user
3062 select max(-1)
3063 into child_file_luby
3064 from fnd_foreign_key_columns
3065 where application_id = appl_id
3066 and table_id = tab_id
3067 and foreign_key_id = fk_id
3068 and foreign_key_sequence > 0
3069 and column_id > 0
3070 and last_updated_by not in (0,1,2);
3071
3072 if child_file_luby IS NULL then
3073 child_file_luby := 2; -- All rows are seed data, set seed data owner
3074 end if;
3075
3076 -- Bug2631776 perform check to see if update occurs or not.
3077
3078 if (fnd_load_util.upload_test(child_file_luby, child_file_ludate, child_db_luby, child_db_ludate, x_custom_mode)) then
3079
3080 -- The new child entity rows from the data file are kept so
3081 -- delete the existing db child entity rows.
3082
3083 delete from fnd_foreign_key_columns
3084 where application_id = appl_id
3085 and table_id = tab_id
3086 and foreign_key_id = fk_id
3087 and foreign_key_sequence < 0
3088 and column_id < 0;
3089
3090 else
3091
3092 -- The existing db child entity rows are kept so delete the new child
3093 -- entity rows from the data file
3094 -- Bug3139883 - Modified delete to include the value column_sequence = 0
3095
3096 delete from fnd_foreign_key_columns
3100 and foreign_key_sequence >= 0
3097 where application_id = appl_id
3098 and table_id = tab_id
3099 and foreign_key_id = fk_id
3101 and column_id > 0;
3102
3103 -- Rename the existing db entity rows back to normal since
3104 -- it was not replaced by the new child entity rows
3105 -- from the data file.
3106
3107 update FND_FOREIGN_KEY_COLUMNS
3108 set FOREIGN_KEY_SEQUENCE = -1 * FOREIGN_KEY_SEQUENCE,
3109 COLUMN_ID = -1 * COLUMN_ID
3110 where APPLICATION_ID = appl_id
3111 and TABLE_ID = tab_id
3112 and FOREIGN_KEY_ID = fk_id;
3113
3114 /*Bug3139883 - Handle special case where FOREIGN_KEY_SEQUENCE = 0 */
3115
3116 update FND_FOREIGN_KEY_COLUMNS
3117 set FOREIGN_KEY_SEQUENCE = 0
3118 where APPLICATION_ID = appl_id
3119 and TABLE_ID = tab_id
3120 and FOREIGN_KEY_ID = fk_id
3121 and FOREIGN_KEY_SEQUENCE = 1000;
3122
3123 end if;
3124 <<done_label>>
3125
3126 -- check if the file has no child entries to clean up database.
3127
3128 if (child_file_ludate IS NULL) then
3129
3130 if (child_db_ludate IS NOT NULL) then
3131
3132 -- Rename the existing db entity rows back to normal since
3133 -- it was not replaced by the new child entity rows
3134 -- from the data file.
3135
3136 update FND_FOREIGN_KEY_COLUMNS
3137 set FOREIGN_KEY_SEQUENCE = -1 * FOREIGN_KEY_SEQUENCE,
3138 COLUMN_ID = -1 * COLUMN_ID
3139 where APPLICATION_ID = appl_id
3140 and TABLE_ID = tab_id
3141 and FOREIGN_KEY_ID = fk_id;
3142
3143 /*Bug3139883 - Handle special case where FOREIGN_KEY_SEQUENCE = 0 */
3144
3145 update FND_FOREIGN_KEY_COLUMNS
3146 set FOREIGN_KEY_SEQUENCE = 0
3147 where APPLICATION_ID = appl_id
3148 and TABLE_ID = tab_id
3149 and FOREIGN_KEY_ID = fk_id
3150 and FOREIGN_KEY_SEQUENCE = 1000;
3151
3152 end if;
3153 end if;
3154 end if;
3155 end UploadForeignKey;
3156
3157 --
3158 -- UploadForeignKeyColumn (PUBLIC))
3159 -- Public procedure for afdict.lct to call when uploading foreign key column
3160 -- using afdict.lct.
3161 --
3162 procedure UploadForeignKeyColumn (
3163 x_application_short_name in varchar2,
3164 x_table_name in varchar2,
3165 x_foreign_key_name in varchar2,
3166 x_foreign_key_column_name in varchar2,
3167 x_foreign_key_column_sequence in varchar2,
3168 x_cascade_value in varchar2,
3169 x_user_id in varchar2,
3170 x_custom_mode in varchar2,
3171 x_last_update_date in varchar2
3172 ) is
3173 tab_id number;
3174 appl_id number;
3175 fk_id number;
3176 col_id number;
3177 f_luby number; -- entity owner in file
3178 f_ludate date; -- entity update date in file
3179
3180 begin
3181
3182 -- No need to validate/check Application, Table or Foreign Key.
3183 -- Within the same entity.
3184 select A.APPLICATION_ID
3185 into appl_id
3186 from FND_APPLICATION A
3187 where A.APPLICATION_SHORT_NAME = x_application_short_name;
3188
3189 select T.TABLE_ID into tab_id from FND_TABLES T
3190 where T.APPLICATION_ID = appl_id
3191 and T.TABLE_NAME = x_table_name;
3192
3193 select F.FOREIGN_KEY_ID into fk_id from FND_FOREIGN_KEYS F
3194 where F.APPLICATION_ID = appl_id
3195 and F.TABLE_ID = tab_id
3196 and F.FOREIGN_KEY_NAME = x_foreign_key_name;
3197
3198 begin
3199 select C.COLUMN_ID into col_id from FND_COLUMNS C
3200 where C.APPLICATION_ID = appl_id
3201 and C.TABLE_ID = tab_id
3202 and C.COLUMN_NAME = x_foreign_key_column_name;
3203 exception
3204 when no_data_found then
3205 fnd_message.set_name('FND', 'FND-COLUMN NAME');
3206 fnd_message.set_token('COLUMN_NAME', x_foreign_key_column_name);
3207 fnd_message.set_token('OBJECT_TYPE', 'Foreign Key');
3208 fnd_message.set_token('OBJECT_NAME', x_foreign_key_name);
3209 app_exception.raise_exception;
3210 end;
3211
3212 -- Translate owner to file_last_updated_by
3213 f_luby := fnd_load_util.owner_id(x_user_id);
3214
3215 -- Translate char last_update_date to date
3216 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
3217
3218 -- NOTE: no "UPDATE" case as we have renamed all the existing
3219 -- foreign key columns so that they can be compared against the new
3220 -- foreign key columns from the data file to determine which will be
3221 -- updated into the database based on the date and custom factirs.
3222
3223
3224 begin
3225 insert into FND_FOREIGN_KEY_COLUMNS(
3226 APPLICATION_ID,
3227 TABLE_ID,
3228 FOREIGN_KEY_ID,
3229 COLUMN_ID,
3230 FOREIGN_KEY_SEQUENCE,
3231 CASCADE_VALUE,
3232 LAST_UPDATED_BY,
3233 LAST_UPDATE_DATE,
3234 LAST_UPDATE_LOGIN,
3235 CREATION_DATE,
3236 CREATED_BY)
3237 values (
3238 appl_id,
3239 tab_id,
3240 fk_id,
3241 col_id,
3242 x_foreign_key_column_sequence,
3243 x_cascade_value,
3244 f_luby,
3245 f_ludate,
3246 f_luby,
3247 f_ludate,
3248 f_luby);
3249 exception
3250 when dup_val_on_index then
3251 fnd_message.set_name('FND', 'FND-DUPLICATE COLUMN SEQUENCE');
3252 fnd_message.set_token('COLUMN_SEQUENCE', x_foreign_key_column_sequence);
3253 fnd_message.set_token('OBJECT_NAME', x_foreign_key_name);
3257
3254 app_exception.raise_exception;
3255 end;
3256 end UploadForeignKeyColumn;
3258
3259 --
3260 -- UploadSequence (PUBLIC))
3261 -- Public procedure for afdict.lct to call when uploading sequence
3262 -- using afdict.lct. It calls InsertSequence when needed.
3263 --
3264 procedure UploadSequence (
3265 x_application_short_name in varchar2,
3266 x_sequence_name in varchar2,
3267 x_start_value in varchar2,
3268 x_description in varchar2,
3269 x_increment_by in varchar2,
3270 x_min_value in varchar2,
3271 x_max_value in varchar2,
3272 x_cache_size in varchar2,
3273 x_cycle_flag in varchar2,
3274 x_order_flag in varchar2,
3275 x_user_id in varchar2,
3276 x_custom_mode in varchar2,
3277 x_last_update_date in varchar2
3278 ) is
3279 appl_id number;
3280 f_luby number; -- entity owner in file
3281 f_ludate date; -- entity update date in file
3282 db_luby number; -- entity owner in db
3283 db_ludate date; -- entity update date in db
3284 begin
3285 -- Validate Application.
3286 begin
3287 select A.APPLICATION_ID
3288 into appl_id
3289 from FND_APPLICATION A
3290 where A.APPLICATION_SHORT_NAME = x_application_short_name;
3291
3292 exception
3293 when no_data_found then
3294 fnd_message.set_name('FND', 'FND-APPLICATION NAME');
3295 fnd_message.set_token('APPLICATION_NAME', x_application_short_name);
3296 app_exception.raise_exception;
3297 end;
3298
3299 -- Translate owner to file_last_updated_by
3300 f_luby := fnd_load_util.owner_id(x_user_id);
3301
3302 -- Translate char last_update_date to date
3303 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
3304
3305 begin
3306 select LAST_UPDATED_BY, LAST_UPDATE_DATE
3307 into db_luby, db_ludate
3308 from FND_SEQUENCES
3309 where APPLICATION_ID = appl_id
3310 and SEQUENCE_NAME = x_sequence_name;
3311
3312 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
3313 db_ludate, X_CUSTOM_MODE)) then
3314
3315
3316 -- Check if this is a new table or old table
3317 update FND_SEQUENCES set
3318 START_VALUE = x_start_value,
3319 DESCRIPTION = x_description,
3320 INCREMENT_BY = x_increment_by,
3321 MIN_VALUE = x_min_value,
3322 MAX_VALUE = x_max_value,
3323 CACHE_SIZE = x_cache_size,
3324 CYCLE_FLAG = x_cycle_flag,
3325 ORDER_FLAG = x_order_flag,
3326 LAST_UPDATED_BY = f_luby,
3327 LAST_UPDATE_DATE = f_ludate,
3328 LAST_UPDATE_LOGIN = f_luby
3329 where APPLICATION_ID = appl_id
3330 and SEQUENCE_NAME = x_sequence_name;
3331 end if;
3332 exception
3333 when no_data_found then
3334 Fnd_Dictionary_Pkg.InsertSequence(
3335 appl_id,
3336 x_sequence_name,
3337 x_start_value,
3338 x_description,
3339 x_increment_by,
3340 x_min_value,
3341 x_max_value,
3342 x_cache_size,
3343 x_cycle_flag,
3344 x_order_flag,
3345 f_ludate,
3346 f_luby,
3347 f_ludate,
3348 f_luby,
3349 0);
3350 end;
3351 end UploadSequence;
3352
3353 --
3354 -- UploadView (PUBLIC))
3355 -- Public procedure for afdict.lct to call when uploading view
3356 -- using afdict.lct. It calls InsertView when needed.
3357 --
3358 procedure UploadView (
3359 x_application_short_name in varchar2,
3360 x_view_name in varchar2,
3361 x_text in varchar2,
3362 x_description in varchar2,
3363 x_user_id in varchar2,
3364 x_custom_mode in varchar2,
3365 x_last_update_date in varchar2,
3366 x_phase_mode in varchar2
3367 ) is
3368 appl_id number;
3369 f_luby number; -- entity owner in file
3370 f_ludate date; -- entity update date in file
3371 db_luby number; -- entity owner in db
3372 db_ludate date; -- entity update date in db
3373 -- Bug2631776 new variables to handle update.
3374 child_file_ludate date; -- child entity update date in file
3375 child_file_luby number; -- child owner in file
3376 child_db_ludate date; -- child update date in db
3377 child_db_luby number; -- child owner in db
3378 vw_id number;
3379 first_char varchar2(1); -- first character in column_name
3380
3381 begin
3382 -- Validate Application
3383 begin
3384 select A.APPLICATION_ID
3385 into appl_id
3386 from FND_APPLICATION A
3387 where A.APPLICATION_SHORT_NAME = x_application_short_name;
3388 exception
3389 when no_data_found then
3390 fnd_message.set_name('FND', 'FND-APPLICATION NAME');
3391 fnd_message.set_token('APPLICATION_NAME', x_application_short_name);
3392 app_exception.raise_exception;
3393 end;
3394
3395 if (x_phase_mode = 'BEGIN') then
3396
3397 -- Translate owner to file_last_updated_by
3398 f_luby := fnd_load_util.owner_id(x_user_id);
3399
3400 -- Translate char last_update_date to date
3401 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
3402
3403 begin
3404
3405 select VIEW_ID
3406 into vw_id
3407 from fnd_views
3408 where application_id = appl_id
3409 and VIEW_NAME = x_view_name;
3410
3414 where APPLICATION_ID = appl_id
3411 select LAST_UPDATED_BY, LAST_UPDATE_DATE
3412 into db_luby, db_ludate
3413 from FND_VIEWS
3415 and VIEW_NAME = x_view_name;
3416
3417 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
3418 db_ludate, X_CUSTOM_MODE)) then
3419
3420 update FND_VIEWS set
3421 TEXT = x_text,
3422 DESCRIPTION = x_description,
3423 LAST_UPDATED_BY = f_luby,
3424 LAST_UPDATE_DATE = f_ludate,
3425 LAST_UPDATE_LOGIN = f_luby
3426 where APPLICATION_ID = appl_id
3427 and VIEW_NAME = x_view_name;
3428 end if;
3429
3430 -- BUG2631776 rename the child record's COLUMN_SEQUENCE
3431 -- and COLUMN_NAME to in order to prevent unique
3432 -- constraints while processing the PARENT/CHILD entity.
3433
3434 update FND_VIEW_COLUMNS
3435 set COLUMN_SEQUENCE = -1 * COLUMN_SEQUENCE,
3436 COLUMN_NAME = decode(instr(COLUMN_NAME,'_'),0,concat('#',COLUMN_NAME),
3437 replace(COLUMN_NAME, '_','#'))
3438 where APPLICATION_ID = appl_id
3439 and VIEW_ID = vw_id;
3440
3441 exception
3442 when no_data_found then
3443 Fnd_Dictionary_Pkg.InsertView(
3444 appl_id,
3445 x_view_name,
3446 x_text,
3447 x_description,
3448 f_ludate,
3449 f_luby,
3450 f_ludate,
3451 f_luby,
3452 0);
3453 end;
3454
3455 else -- phase_mode = 'END'
3456
3457 select VIEW_ID
3458 into vw_id
3459 from fnd_views
3460 where application_id = appl_id
3461 and VIEW_NAME = x_view_name;
3462
3463 -- Bug2631776 get the latest value for the last update for the db entity
3464 -- and the file entity.
3465
3466 select max(last_update_date)
3467 into child_db_ludate
3468 from fnd_view_columns
3469 where application_id = appl_id
3470 and VIEW_ID = vw_id
3471 and column_sequence < 0;
3472
3473 select max(last_update_date)
3474 into child_file_ludate
3475 from fnd_view_columns
3476 where application_id = appl_id
3477 and VIEW_ID = vw_id
3478 and column_sequence > 0;
3479
3480 -- If no value which means there were no existing child records
3481 -- in the database therefore skip to the end since the new child
3482 -- records have been updated.
3483
3484 if (child_db_ludate IS NULL) or (child_file_ludate IS NULL) then
3485 GOTO done_label;
3486 end if;
3487
3488 -- Continue on to check the owner value since both have columns.
3489
3490 -- Bug2631776 get the maximum value for the userid that made the
3491 -- last update for the db entity and the file entity.
3492
3493 -- If any non-seed owners, set owner to user
3494 select max(-1)
3495 into child_db_luby
3496 from fnd_view_columns
3497 where application_id = appl_id
3498 and VIEW_ID = vw_id
3499 and column_sequence < 0
3500 and last_updated_by not in (0,1,2);
3501
3502 if child_db_luby IS NULL then
3503 child_db_luby := 2; -- All rows are seed data, set seed data owner
3504 end if;
3505
3506 -- If any non-seed owners, set owner to user
3507 select max(-1)
3508 into child_file_luby
3509 from fnd_view_columns
3510 where application_id = appl_id
3511 and VIEW_ID = vw_id
3512 and column_sequence > 0
3513 and last_updated_by not in (0,1,2);
3514
3515 if child_file_luby IS NULL then
3516 child_file_luby := 2; -- All rows are seed data, set seed data owner
3517 end if;
3518
3519 -- Bug2631776 perform check to see if update occurs or not.
3520
3521 if (fnd_load_util.upload_test(child_file_luby, child_file_ludate, child_db_luby, child_db_ludate, x_custom_mode)) then
3522
3523 -- The new child entity rows from the data file are kept so
3524 -- delete the existing db child entity rows.
3525
3526 delete from fnd_view_columns
3527 where application_id = appl_id
3528 and VIEW_ID = vw_id
3529 and column_sequence < 0;
3530
3531 else
3532
3533 -- The existing db child entity rows are kept so delete the new child
3534 -- entity rows from the data file
3535
3536 delete from fnd_view_columns
3537 where application_id = appl_id
3538 and VIEW_ID = vw_id
3539 and column_sequence > 0;
3540
3541 -- Rename the existing db entity rows back to normal since
3542 -- it was not replaced by the new child entity rows
3543 -- from the data file.
3544
3545 update FND_VIEW_COLUMNS
3546 set COLUMN_SEQUENCE = -1 * COLUMN_SEQUENCE,
3547 COLUMN_NAME = decode(instr(COLUMN_NAME, '#'),1,ltrim(COLUMN_NAME, '#'),
3548 replace(COLUMN_NAME, '#','_'))
3549 where APPLICATION_ID = appl_id
3550 and VIEW_ID = vw_id;
3551
3552 end if;
3553 <<done_label>>
3554
3555 -- check if the file has no child entries to clean up database.
3556
3557 if (child_file_ludate IS NULL) then
3558
3559 if (child_db_ludate IS NOT NULL) then
3560
3561 -- Rename the existing db entity rows back to normal since
3562 -- it was not replaced by the new child entity rows
3563 -- from the data file.
3564
3565 update FND_VIEW_COLUMNS
3566 set COLUMN_SEQUENCE = -1 * COLUMN_SEQUENCE,
3570 and VIEW_ID = vw_id;
3567 COLUMN_NAME = decode(instr(COLUMN_NAME, '#'),1,ltrim(COLUMN_NAME, '#'),
3568 replace(COLUMN_NAME, '#','_'))
3569 where APPLICATION_ID = appl_id
3571 end if;
3572 end if;
3573 end if;
3574 end UploadView;
3575
3576 --
3577 -- UploadViewColumn (PUBLIC))
3578 -- Public procedure for afdict.lct to call when uploading view column
3579 -- using afdict.lct.
3580 --
3581 procedure UploadViewColumn (
3582 x_application_short_name in varchar2,
3583 x_view_name in varchar2,
3584 x_view_column_name in varchar2,
3585 x_view_column_sequence in varchar2,
3586 x_user_id in varchar2,
3587 x_custom_mode in varchar2,
3588 x_last_update_date in varchar2
3589 ) is
3590 appl_id number;
3591 vw_id number;
3592 f_luby number; -- entity owner in file
3593 f_ludate date; -- entity update date in file
3594 begin
3595
3596 -- No need to validate/check Application and View.
3597 -- Within the same entity.
3598 select A.APPLICATION_ID
3599 into appl_id
3600 from FND_APPLICATION A
3601 where A.APPLICATION_SHORT_NAME = x_application_short_name;
3602
3603 select V.VIEW_ID into vw_id from FND_VIEWS V
3604 where V.APPLICATION_ID = appl_id
3605 and V.VIEW_NAME = x_view_name;
3606
3607 -- Translate owner to file_last_updated_by
3608 f_luby := fnd_load_util.owner_id(x_user_id);
3609
3610 -- Translate char last_update_date to date
3611 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
3612
3613 -- NOTE: no "UPDATE" case as we have renamed all the existing
3614 -- view columns so that they can be compared against the new
3615 -- view columns from the data file to determine which will be
3616 -- updated into the database based on the date and custom factirs.
3617
3618 begin
3619 insert into FND_VIEW_COLUMNS(
3620 APPLICATION_ID,
3621 VIEW_ID,
3622 COLUMN_SEQUENCE,
3623 COLUMN_NAME,
3624 LAST_UPDATED_BY,
3625 LAST_UPDATE_DATE,
3626 LAST_UPDATE_LOGIN,
3627 CREATION_DATE,
3628 CREATED_BY)
3629 values (
3630 appl_id,
3631 vw_id,
3632 x_view_column_sequence,
3633 x_view_column_name,
3634 f_luby,
3635 f_ludate,
3636 f_luby,
3637 f_ludate,
3638 f_luby);
3639 exception
3640 when dup_val_on_index then
3641 fnd_message.set_name('FND', 'FND-DUPLICATE COLUMN SEQUENCE');
3642 fnd_message.set_token('COLUMN_SEQUENCE', x_view_column_sequence);
3643 fnd_message.set_token('OBJECT_NAME', x_view_name);
3644 app_exception.raise_exception;
3645 end;
3646 end UploadViewColumn;
3647
3648
3649 --PRIVATE
3650 --Checking table who had been visited by UpdateChildren().
3651 function visited(x_table_name in varchar2) return boolean is
3652 i number;
3653 begin
3654
3655 i := 0;
3656 while ((i < visited_table_count) and (visited_tables(i) is not null)) loop
3657 if (visited_tables(i) = x_table_name) then
3658 return TRUE;
3659 else
3660 i := i+1;
3661 end if;
3662 end loop;
3663
3664 return FALSE;
3665 end visited;
3666
3667
3668 -- UpdateOrCheckChildren (PRIVATE) is called from UpdatePKColumns
3669 --
3670 -- Description
3671 -- For a given primary key, find all the foreign key children
3672 -- and update their column(s) to the new value.
3673 -- For each foreign key child and the associate foreign key table info,
3674 -- recursive call UpdatePKColumns() to update its foreign key children.
3675 -- The recursive call is to take care of cascade foreign keys.
3676 -- Description(2) : Added for bug 1496010
3677 -- Added support for checking whether any foreign key child contain the
3678 -- the same value as its pk value.
3679 -- Input
3680 -- x_application_id:
3681 -- primary key application_id
3682 -- x_primary_table_id:
3683 -- primary key table_id
3684 -- x_primary_key_id:
3685 -- primary key id
3686 -- x_col_sequences:
3687 -- this array store number like (0, 1) or (1, 2) and so on.
3688 -- (0, 1) means the first and second columns inside the primary key.
3689 -- (0, 2) means the first and third columns inside the primary key.
3690 -- This is for handling partial columns update inside a primary key.
3691 -- x_primary_key_col_value_old:
3692 -- columns's existing value
3693 -- x_primary_key_col_value_new:
3694 -- columns's new value
3695 -- If x_primary_key_col_value_new(0) = '', then this is the case for
3696 -- checking not performing any update. Described in Description(2).
3697 function UpdateOrCheckChildren(x_application_id in number,
3698 x_primary_table_id in number,
3699 x_primary_key_id in number,
3700 x_col_sequences in NumberArrayTyp,
3701 x_primary_key_col_value_old in NameArrayTyp,
3702 x_primary_key_col_value_new in NameArrayTyp)
3703 return boolean is
3704 cursor fks is
3705 select a.application_id, a.application_short_name, ft.table_name,
3706 ft.table_id, f.foreign_key_id,
3707 f.foreign_key_name, f.condition
3708 from fnd_tables ft, fnd_foreign_keys f, fnd_application a
3709 where f.primary_key_application_id = x_application_id
3710 and f.primary_key_table_id = x_primary_table_id
3711 and f.primary_key_id = x_primary_key_id
3712 and ft.table_id = f.table_id
3713 and ft.application_id = f.application_id
3717 select fc.column_name
3714 and ft.application_id = a.application_id;
3715
3716 cursor fkcols(fkapplid number, fktabid number, fkid number) is
3718 from fnd_columns fc, fnd_foreign_key_columns fcc
3719 where fcc.foreign_key_id = fkid
3720 and fcc.table_id = fktabid
3721 and fcc.application_id = fkapplid
3722 and fcc.column_id = fc.column_id
3723 and fcc.table_id = fc.table_id
3724 and fcc.application_id = fc.application_id
3725 order by fcc.foreign_key_sequence;
3726
3727 updbuf varchar2(2000);
3728 selbuf varchar2(2000);
3729 colnames NameArrayTyp;
3730 pknames NameArrayTyp;
3731 i number;
3732 j number;
3733 tmpbuf varchar2(2000); -- for error message logging purpose
3734 ret boolean;
3735
3736 table_doesnot_exist exception;
3737 pragma exception_init (table_doesnot_exist, -942);
3738
3739 column_doesnot_exist exception;
3740 pragma exception_init (column_doesnot_exist, -904);
3741
3742 duplicate_column_name exception;
3743 pragma exception_init (duplicate_column_name, -957);
3744
3745 unique_constraint exception;
3746 pragma exception_init (unique_constraint, -00001);
3747
3748 l_api_name CONSTANT varchar2(30) := 'UpdateChildren';
3749
3750 curs integer;
3751 row_processed number;
3752
3753 l_exists_flag varchar2(1) := 'N';
3754
3755 begin
3756
3757 -- for every foreign key child, we need to update the
3758 -- foreign key child columns. (only the one in the x_col_sequences)
3759 -- this is because we support partial primary key columns update.
3760 for fk in fks loop
3761 if (not(visited(fk.table_name))) then
3762 -- We should make sure that this foreign key table is not a view.
3763 -- Sometimes, people might register the wrong info inside our
3764 -- dictionary.
3765 -- We should probably select from user_tables and make sure that
3766 -- it is a real table.
3767
3768 -- find which column need to be updated by matching the
3769 -- sequence in x_col_sequences
3770 i := 0;
3771 j := 0; -- controls x_col_sequences
3772 for fkcol in fkcols(fk.application_id, fk.table_id, fk.foreign_key_id) loop
3773 if (i = x_col_sequences(j)) then
3774 colnames(j) := fkcol.column_name;
3775 j := j + 1;
3776 end if;
3777 i := i+1;
3778 end loop;
3779 colnames(j) := '';
3780
3781 -- After the above loop, colnames() contains foreign key columns name
3782 -- that needed to be updated or checked.
3783 -- and j is the number of how many columns in colnames().
3784
3785
3786 -- Bug 1496010: This is the case for checking only.
3787 -- Therefore, construct select stmt.
3788 if (x_primary_key_col_value_new(0) is null) then
3789 selbuf := 'select ''Y'' from dual where exists (select 1 from '||
3790 fk.table_name||' where ';
3791 i := 0;
3792 while (i < j) loop
3793 if (i > 0) then
3794 selbuf := selbuf||' and ';
3795 end if;
3796 selbuf := selbuf||colnames(i)||' = :C'||i;
3797 i := i + 1;
3798 end loop;
3799 -- End checking case. Bug 1496010.
3800 else
3801
3802 updbuf := 'update '||fk.table_name||' set ';
3803 i := 0;
3804 while (i < j) loop
3805 if (i = j-1) then
3806 updbuf := updbuf||colnames(i)||'= :B'||i;
3807 else
3808 updbuf := updbuf||colnames(i)||'= :B'||i||',';
3809 end if;
3810 i := i + 1;
3811 end loop;
3812
3813 -- Comment out who columns because there is no right value to pick
3814 -- Plus, some tables have no who columns and it causes trouble.
3815 -- add the who columns;
3816 -- updbuf := updbuf || 'last_update_date = sysdate, last_updated_by = 1 ';
3817
3818 i := 0;
3819 updbuf := updbuf ||' where ';
3820 while (i < j) loop
3821 updbuf := updbuf||colnames(i)||'= :S'||i ;
3822 if (j > (i+1)) then
3823 updbuf := updbuf||' and ';
3824 else
3825 updbuf := updbuf||' ';
3826 end if;
3827 i := i + 1;
3828 end loop;
3829
3830 if (fk.condition is not null) then
3831 updbuf := updbuf || ' and '||fk.condition;
3832 end if;
3833
3834 end if;
3835
3836 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
3837 if (x_primary_key_col_value_new(0) is null) then
3838 -- Bug 1496010: This is the case for checking so print out selbuf.
3839 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name || '.',
3840 selbuf);
3841 else
3842 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name || '.',
3843 updbuf);
3844 end if;
3845 end if;
3846
3847 begin
3848 -- Bug 1496010: This is the case for checking only.
3849 if (x_primary_key_col_value_new(0) is null) then
3850 curs := dbms_sql.open_cursor;
3851 dbms_sql.parse(curs, selbuf||')', dbms_sql.v7);
3852
3853 dbms_sql.define_column(curs, 1, l_exists_flag, 1);
3854
3855 i := 0;
3856 while (i < j) loop
3857 dbms_sql.bind_variable(curs, ':C'||i, x_primary_key_col_value_old(i));
3858 i := i+1;
3859 end loop;
3860 row_processed := dbms_sql.execute_and_fetch(curs);
3861 dbms_sql.column_value(curs,1,l_exists_flag);
3862
3863 -- End Bug 1496010
3864 else
3865 curs := dbms_sql.open_cursor;
3866 dbms_sql.parse(curs, updbuf, dbms_sql.v7);
3867
3868 i := 0;
3869 while (i < j) loop
3873 i := 0;
3870 dbms_sql.bind_variable(curs, ':B'||i, x_primary_key_col_value_new(i));
3871 i := i+1;
3872 end loop;
3874 while (i < j) loop
3875 dbms_sql.bind_variable(curs, ':S'||i, x_primary_key_col_value_old(i));
3876 i := i+1;
3877 end loop;
3878 row_processed := dbms_sql.execute(curs);
3879 end if;
3880
3881
3882 -- Add table name into our global list to prevent infinite loop
3883 visited_tables(visited_table_count) := fk.table_name;
3884 visited_table_count := visited_table_count + 1;
3885
3886 exception
3887 when unique_constraint then
3888 fnd_message.set_name('FND', 'FK_COLUMN_VALUE_DUPLICATE');
3889 fnd_message.set_token('FK_TABLE', fk.table_name);
3890 fnd_message.set_token('UPDATE_STATEMENT', updbuf);
3891 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
3892 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
3893 c_log_head || l_api_name || '.update_failed', FALSE);
3894 end if;
3895 checking_log := TRUE;
3896 -- Probably no need to raise error
3897 -- Just skip this table and continue.
3898 -- continue;
3899
3900 when table_doesnot_exist then
3901 -- ORA 942
3902 fnd_message.set_name('FND', 'FK_TABLE_NOT_FOUND');
3903 fnd_message.set_token('FK_TABLE', fk.table_name);
3904 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
3905 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
3906 c_log_head || l_api_name || '.update_failed', FALSE);
3907 end if;
3908 checking_log := TRUE;
3909 -- Probably no need to raise error
3910 -- Just skip this table and continue.
3911 -- continue;
3912
3913 when column_doesnot_exist then
3914 -- ORA 904
3915 fnd_message.set_name('FND', 'FK_COLUMN_NOT_FOUND');
3916 j := 0;
3917 tmpbuf := '';
3918 while (j < i or j = i) loop
3919 tmpbuf := tmpbuf || colnames(j)||',';
3920 j := j + 1;
3921 end loop;
3922 fnd_message.set_token('FK_COLUMN',
3923 tmpbuf||' or LAST_UPDATED_BY or LAST_UPDATE_DATE' );
3924 fnd_message.set_token('FK_TABLE', fk.table_name);
3925 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
3926 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
3927 c_log_head || l_api_name || '.update_failed', FALSE);
3928 end if;
3929 checking_log := TRUE;
3930 -- Probably no need to raise error
3931 -- Try again. Update it WITHOUT the who column
3932
3933 when duplicate_column_name then
3934 -- ORA 957
3935 fnd_message.set_name('FND', 'FK_DUPLICATE_COLUMN_UPDATE');
3936 fnd_message.set_token('SQL_STATEMENT', updbuf);
3937 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
3938 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
3939 c_log_head || l_api_name || '.update_failed', FALSE);
3940 end if;
3941 checking_log := TRUE;
3942 -- Probably no need to raise error
3943 when others then
3944 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
3945 fnd_message.set_token('ROUTINE', 'UpdateChildren');
3946 fnd_message.set_token('ERRNO', to_char(sqlcode));
3947 fnd_message.set_token('REASON', sqlerrm);
3948 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
3949 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
3950 c_log_head || l_api_name || '.update_failed', FALSE);
3951 end if;
3952 checking_log := TRUE;
3953 end; -- end execute updbuf and selbuf;
3954
3955 -- Close cursor
3956 dbms_sql.close_cursor(curs);
3957
3958 -- Bug 1496010. If any child contain same value, we can return TRUE
3959 -- and forget about the rest of the loop.
3960 if (x_primary_key_col_value_new(0) is null) then
3961 if (l_exists_flag = 'Y') then
3962 -- put a message on the stack regarding which foreign key child
3963 fnd_message.set_name('FND', 'FK_FOUND_NO_DELETE_PK');
3964 fnd_message.set_token('FK_TABLE', fk.table_name);
3965 fnd_message.set_token('FK_KEY', fk.foreign_key_name);
3966 return(TRUE);
3967 end if;
3968 -- End 1496010
3969 else
3970
3971 -- This foreign key child itself might be a primary key, so we
3972 -- need to recursively do the same thing.
3973
3974 -- Right now, colnames array only contains the columns needed to
3975 -- be updated.
3976 -- Using our example will be (APPLICATION_ID, PROFILE_OPTION_ID)
3977
3978 ret := UpdatePKColumns(fk.application_short_name,
3979 fk.table_name,
3980 colnames,
3981 x_primary_key_col_value_old,
3982 x_primary_key_col_value_new);
3983
3984 end if;
3985 end if; -- there is no "continue" command to use so I have to use if.
3986 end loop; -- end for each foreign key child
3987
3988 -- Bug 1496160
3989 -- If we got here, and is the checking case, that means no foreign key
3990 -- value found
3991 if (x_primary_key_col_value_new(0) is null) then
3992 -- just simply return FALSE because nothing found
3993 return (FALSE);
3994 -- End Bug 1496160
3995 else
3996 return(checking_log);
3997 end if;
3998
3999
4000 end UpdateOrCheckChildren;
4001
4002
4003 -- UpdatePKCols (Private)
4004 --
4005 -- Description
4009 -- UpdateChildren() to find their children table and do a recursive update.
4006 -- For a given table(x_application_short_name and x_primary_table_name)
4007 -- and column names array, find all pk/uk that contains all the columns
4008 -- in the x_primary_key_col_names. For each of these pk/uk, call
4010 --
4011 -- Input
4012 -- x_application_short_name:
4013 -- application_short_name
4014 -- x_primary_table_name:
4015 -- table that contains the column name array
4016 -- x_primary_key_col_names:
4017 -- column name array
4018 -- x_primary_key_col_value_old:
4019 -- columns's existing value in array
4020 -- x_primary_key_col_value_new:
4021 -- columns's new value in array
4022 --
4023 function UpdatePKCols(x_application_short_name in varchar2,
4024 x_primary_table_name in varchar2,
4025 x_primary_key_col_names in NameArrayTyp,
4026 x_primary_key_col_value_old in NameArrayTyp,
4027 x_primary_key_col_value_new in NameArrayTyp)
4028 return boolean is
4029 appl_id number;
4030 tab_id number;
4031 colnames NameArrayTyp;
4032 i number;
4033 j number;
4034 colseqs NumberArrayTyp;
4035 numofcols number;
4036 l_api_name CONSTANT varchar2(30) := 'UpdatePKColumns';
4037 tmpbuf varchar2(2000); -- For logging statement buffer
4038
4039 -- Validate primary key column name
4040 cursor pkcols(aid number, tid number, pid number) is
4041 select c.column_name
4042 from fnd_columns c, fnd_primary_key_columns pc
4043 where pc.application_id = aid
4044 and pc.table_id = tid
4045 and pc.primary_key_id = pid
4046 and pc.column_id = c.column_id
4047 and c.application_id = aid
4048 and c.table_id = tid
4049 order by pc.primary_key_sequence;
4050
4051 -- Fetch all primary keys in this table
4052 cursor pks(aid number, tid number) is
4053 select p.primary_key_id, p.primary_key_name
4054 from fnd_primary_keys p
4055 where p.application_id = aid
4056 and p.table_id = tid;
4057
4058 hasChild boolean := FALSE;
4059 ret boolean;
4060
4061 begin
4062
4063 -- Fetch general info
4064 begin
4065 select a.application_id, t.table_id
4066 into appl_id, tab_id
4067 from fnd_application a, fnd_tables t
4068 where a.application_short_name = x_application_short_name
4069 and a.application_id = t.application_id
4070 and t.table_name = x_primary_table_name;
4071 exception
4072 when no_data_found then
4073 fnd_message.set_name('FND', 'FK_INVALID_TABLE');
4074 fnd_message.set_token('APPS', x_application_short_name);
4075 fnd_message.set_token('TABLE', x_primary_table_name);
4076 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
4077 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
4078 c_log_head || l_api_name || '.fetch_info', FALSE);
4079 end if;
4080 return(FALSE);
4081 when others then
4082 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
4083 fnd_message.set_token('ROUTINE', 'UpdatePKColumns');
4084 fnd_message.set_token('ERRNO', to_char(sqlcode));
4085 fnd_message.set_token('REASON', sqlerrm);
4086 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
4087 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
4088 c_log_head || l_api_name || '.fetch_info', FALSE);
4089 end if;
4090 return(FALSE);
4091 end;
4092
4093 -- Find out how many columns we are talking about.
4094 -- Because we need to use this info later.
4095 numofcols := 0;
4096 i := 0;
4097 while (x_primary_key_col_names(i) is not null) loop
4098 numofcols := numofcols + 1;
4099 i := i + 1;
4100 end loop;
4101
4102 tmpbuf := 'Checking table '||x_primary_table_name;
4103 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
4104 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head||l_api_name, tmpbuf);
4105 end if;
4106
4107
4108 for pk in pks(appl_id, tab_id) loop
4109 tmpbuf := 'Checking '||pk.primary_key_name;
4110 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
4111 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head||l_api_name, tmpbuf);
4112 end if;
4113
4114 -- Figure out which columns are the one user wants to update.
4115 -- UpdatePKColumns allows you to pass all pk columns or partial columns.
4116 -- Therefore, when this following loop finishes, the colseqs() array
4117 -- stores the column sequence number. The number has been resequence to
4118 -- the format of 0, 1, 2, 3.. instead of 10, 20, 30 or 2,4,6 in case
4119 -- some weird sequence number in the database.
4120 -- And inside UpdateChildren(), it will use the format of 0, 1, 2, 3 to
4121 -- do comparison.
4122 i := 0;
4123 j := 0;
4124 for pkcol in pkcols(appl_id, tab_id, pk.primary_key_id) loop
4125 if (pkcol.column_name = x_primary_key_col_names(j)) then
4126 colseqs(j) := i;
4127 j := j + 1;
4128 end if;
4129 i := i+1;
4130 end loop;
4131 colseqs(j) := '';
4132
4133 -- After the above for loop, colseqs array contains the sequences of the
4134 -- used pk columns
4135
4136 -- The pk/uk has to contains the actual columns that user passes in.
4137 -- The pk/uk could be a three columns key and the user wants to update
4138 -- two columns.
4139
4140 if (j = numofcols) then
4141 -- Bug 1496010
4142 -- Sneak in support for checking whether a pk/uk has foreign key
4143 -- children contain the same value.
4144 -- This case will come from function CheckPKColumn()
4145 -- which pass null value inside x_primary_key_col_value_new
4146 if( x_primary_key_col_value_new(0) is null) then
4150 -- Don't have to finish the whole loop as long as there is one
4147 hasChild := UpdateOrCheckChildren(appl_id, tab_id, pk.primary_key_id,
4148 colseqs, x_primary_key_col_value_old, x_primary_key_col_value_new);
4149 if (hasChild) then
4151 -- child has the same value as one pk/uk, return TRUE.
4152 return(TRUE);
4153 end if;
4154 -- End Bug 1496010
4155 else
4156 -- Call the actual update routine which takes care of cascading update
4157 ret := UpdateOrCheckChildren(appl_id, tab_id, pk.primary_key_id, colseqs,
4158 x_primary_key_col_value_old,
4159 x_primary_key_col_value_new);
4160 -- The above UpdateChildren means for a given primary key, I would like to
4161 -- update its first and third columns.(colseqs = (0, 2)). And the first
4162 -- and third columns old value are inside the value_old array and their
4163 -- new values are inside the value_new array.
4164 end if;
4165 end if;
4166
4167 end loop; -- end loop for each possible pk/uk in the same table.
4168
4169
4170 -- Bug 1496010
4171 -- this is the checking pk children case and none of the pk/uk
4172 -- has any children with the same value, that is why we finished
4173 -- the loop and came here. (Therefore, return FALSE)
4174 if (x_primary_key_col_value_new(0) is null) then
4175 return(FALSE);
4176 -- End Bug 1496010
4177 else
4178 -- this is the update pk column case. (Logic remains the same)
4179 return(not(checking_log));
4180 end if;
4181
4182 end UpdatePKCols;
4183
4184 -- UpdatePKColumns (Public)
4185 --
4186 -- Description
4187 -- The real job is done inside UpdatePKCols(). I have to create this one
4188 -- because I need a place to reset all global variables. I cannot do it
4189 -- inside UpdatePKCols() because of the natural of the recursive calls.
4190 -- Input
4191 -- x_application_short_name:
4192 -- application_short_name
4193 -- x_primary_table_name:
4194 -- table that contains the column name array
4195 -- x_primary_key_col_names:
4196 -- column name array
4197 -- x_primary_key_col_value_old:
4198 -- columns's existing value in array
4199 -- x_primary_key_col_value_new:
4200 -- columns's new value in array
4201 --
4202 function UpdatePKColumns(x_application_short_name in varchar2,
4203 x_primary_table_name in varchar2,
4204 x_primary_key_col_names in NameArrayTyp,
4205 x_primary_key_col_value_old in NameArrayTyp,
4206 x_primary_key_col_value_new in NameArrayTyp)
4207 return boolean is
4208 ret boolean;
4209 begin
4210 ret := UpdatePKCols(x_application_short_name,
4211 x_primary_table_name,
4212 x_primary_key_col_names,
4213 x_primary_key_col_value_old,
4214 x_primary_key_col_value_new);
4215 -- Clear the global variables
4216 visited_tables(0) := '';
4217 visited_table_count := 0;
4218 checking_log := FALSE;
4219
4220 return(ret);
4221
4222 end UpdatePKColumns;
4223
4224
4225 -- CheckPKColumns (Public)
4226 --
4227 -- Description
4228 -- For a given table(x_application_short_name and x_primary_table_name)
4229 -- and column names array, find all pk/uk that contains all the columns
4230 -- in the x_primary_key_col_names. For each of these pk/uk, call
4231 -- UpdateOrCheckChildren() to see if there is any foreign key children has
4232 -- the same value as the value inside x_primary_key_col_value_old.
4233 -- If there is, return TRUE otherwise return FALSE.
4234 --
4235 -- Input
4236 -- x_application_short_name:
4237 -- application_short_name
4238 -- x_primary_table_name:
4239 -- table that contains the column name array
4240 -- x_primary_key_col_names:
4241 -- column name array
4242 -- x_primary_key_col_value_old:
4243 -- columns's existing value in array
4244 --
4245 function CheckPKColumns(x_application_short_name in varchar2,
4246 x_primary_table_name in varchar2,
4247 x_primary_key_col_names in NameArrayTyp,
4248 x_primary_key_col_value_old in NameArrayTyp)
4249 return boolean is
4250 nullarray NameArrayTyp;
4251 ret boolean;
4252 begin
4253
4254 nullarray(0) := '';
4255 -- Passing null array in the last argument to UpdatePKColumns() means
4256 -- we want to perform checking not updating.
4257 ret := UpdatePKCols(x_application_short_name, x_primary_table_name,
4258 x_primary_key_col_names, x_primary_key_col_value_old,
4259 nullarray);
4260 -- Clear the global variables
4261 visited_tables(0) := '';
4262 visited_table_count := 0;
4263 checking_log := FALSE;
4264
4265 return(ret);
4266
4267 end CheckPKColumns;
4268
4269
4270
4271 end Fnd_Dictionary_Pkg;
[Home] [Help]