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