1 package body Fnd_XdfDictionary_Pkg as
2 /* $Header: fndpxdtb.pls 120.10 2012/02/02 11:58:39 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,
433 ENABLED_FLAG,
430 CASCADE_BEHAVIOR,
431 FOREIGN_KEY_RELATION,
432 CONDITION,
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,
447 x_description,
448 x_cascade_behavior,
449 x_foreign_key_relation,
450 x_condition,
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,
728 x_pct_increase => x_pct_increase,
729 x_user_id => x_user_id,
730 x_custom_mode => null,
731 x_last_update_date => 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,
744 x_min_extents => x_min_extents,
741 x_description => x_description,
742 x_initial_extent => x_initial_extent,
743 x_next_extent => x_next_extent,
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,
865 x_table_name in varchar2,
866 x_foreign_key_name in varchar2,
867 x_primary_key_application_name in varchar2,
868 x_primary_key_table_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,
890 x_enabled_flag => x_enabled_flag,
887 x_cascade_behavior => x_cascade_behavior,
888 x_foreign_key_relation => x_foreign_key_relation,
889 x_condition => x_condition,
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 (
995 x_application_short_name => x_application_short_name,
996 x_view_name => x_view_name,
997 x_text => x_text,
998 x_description => x_description,
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,
1040
1037 x_last_update_date => null);
1038
1039 end UploadViewColumn;
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;
1156 select count(*) into cnt
1157 from fnd_index_columns
1158 where application_id = appl_id
1159 and table_id = tab_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 --
1219 x_index_name in varchar2) is
1216 procedure RemoveIndex(
1217 x_application_short_name in varchar2,
1218 x_table_name in varchar2,
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 '
1317 || x_table_name , TRUE);
1318 return;
1319 end;
1320
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;
1343 and primary_key_table_id = tab_id
1340 select count(*) into cnt
1341 from fnd_foreign_keys
1342 where primary_key_application_id = appl_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(
1364 end if;
1361 -20001, 'Fnd Foreign key exists for primary key name '
1362 || x_primary_key_name , TRUE);
1363 return;
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
1476 appl_id number;
1477 vw_id number;
1478 begin
1479 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
1543 and table_id = tab_id;
1540 select primary_key_name
1541 from fnd_primary_keys
1542 where application_id = appl_id
1544
1545 begin
1546
1547 begin
1548 select application_id
1549 into appl_id
1550 from fnd_application
1554 RAISE_APPLICATION_ERROR(
1551 where application_short_name = upper(x_application_short_name);
1552 exception
1553 when no_data_found then
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 P_NZDT_MODE in VARCHAR2 default 'N'
1631 ) is
1632 appl_id number;
1633 dummy varchar2(1);
1634 f_luby number; -- entity owner in file
1635 f_ludate date; -- entity update date in file
1636 db_luby number; -- entity owner in db
1637 db_ludate date; -- entity update date in db
1638 begin
1639 if (P_NZDT_MODE ='Y')
1640 then
1641 begin
1642 AD_ZD_SEED.PREPARE ('FND_TABLES');
1643 end;
1644 end if;
1645
1646 -- Validate application
1647 begin
1648 select A.APPLICATION_ID
1649 into appl_id
1650 from FND_APPLICATION A
1651 where A.APPLICATION_SHORT_NAME = x_application_short_name;
1652 exception
1653 when no_data_found then
1654 RAISE_APPLICATION_ERROR(
1658
1655 -20001, 'Sql no data found in fnd_application for application short name '
1656 || x_application_short_name , TRUE);
1657 end;
1659 -- Validate hosted support style
1660 /*
1661 begin
1662 if (x_hosted_support_style <> 'LOCAL') then
1663 select 'X' into dummy
1664 from fnd_lookups
1665 where lookup_type = 'HOSTED_SUPPORT_STYLE'
1666 and lookup_code = x_hosted_support_style;
1667 end if;
1668 exception
1669 when no_data_found then
1670 RAISE_APPLICATION_ERROR(
1671 -20001, 'Sql no data found for hosted support style '
1672 || x_hosted_support_style , TRUE);
1673 end;
1674 */
1675
1676 -- Translate owner to file_last_updated_by
1677 f_luby := owner_id(x_user_id);
1678
1679 -- Translate char last_update_date to date
1680 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
1681
1682 begin
1683 select LAST_UPDATED_BY, LAST_UPDATE_DATE
1684 into db_luby, db_ludate
1685 from FND_TABLES
1686 where APPLICATION_ID = appl_id
1687 and TABLE_NAME = x_table_name;
1688
1689 if (upload_test(f_luby, f_ludate, db_luby,
1690 db_ludate, X_CUSTOM_MODE)) then
1691
1692 -- Resolve USER_TABLE_NAME by pre-pending '@'
1693 update FND_TABLES
1694 set USER_TABLE_NAME = '@'||USER_TABLE_NAME
1695 where APPLICATION_ID = appl_id
1696 and TABLE_NAME <> x_table_name
1697 and USER_TABLE_NAME = x_user_table_name;
1698
1699 update FND_TABLES set
1700 USER_TABLE_NAME = x_user_table_name,
1701 TABLE_TYPE = x_table_type,
1702 DESCRIPTION = x_description,
1703 AUTO_SIZE = x_auto_size,
1704 INITIAL_EXTENT = x_initial_extent,
1705 NEXT_EXTENT = x_next_extent,
1706 MIN_EXTENTS = x_min_extents,
1707 MAX_EXTENTS = x_max_extents,
1708 INI_TRANS = x_ini_trans,
1709 MAX_TRANS = x_max_trans,
1710 PCT_FREE = x_pct_free,
1711 PCT_INCREASE = x_pct_increase,
1712 PCT_USED = x_pct_used,
1713 HOSTED_SUPPORT_STYLE = x_hosted_support_style,
1714 LAST_UPDATED_BY = f_luby,
1715 LAST_UPDATE_DATE = f_ludate,
1716 LAST_UPDATE_LOGIN = f_luby
1717 where APPLICATION_ID = appl_id
1718 and TABLE_NAME = x_table_name;
1719 end if;
1720 exception
1721 when no_data_found then
1722 Fnd_XdfDictionary_Pkg.InsertTable(
1723 appl_id,
1724 x_table_name,
1725 x_user_table_name,
1726 x_table_type,
1727 x_description,
1728 x_auto_size,
1729 x_initial_extent,
1730 x_next_extent,
1731 x_min_extents,
1732 x_max_extents,
1733 x_ini_trans,
1734 x_max_trans,
1735 x_pct_free,
1736 x_pct_increase,
1737 x_pct_used,
1738 x_hosted_support_style,
1739 f_ludate,
1740 f_luby,
1741 f_ludate,
1742 f_luby,
1743 0);
1744 end;
1745 end UploadTable;
1746
1747 --
1748 -- UploadColumn (PUBLIC)) - Overloaded
1752 procedure UploadColumn (
1749 -- Public procedure for afdict.lct to call when uploading columns using
1750 -- using afdict.lct. It calls InsertColumn() when needed.
1751 --
1753 x_application_short_name in varchar2,
1754 x_table_name in varchar2,
1755 x_column_name in varchar2,
1756 x_user_column_name in varchar2,
1757 x_column_sequence in varchar2,
1758 x_column_type in varchar2,
1759 x_width in varchar2,
1760 x_null_allowed_flag in varchar2,
1761 x_description in varchar2,
1762 x_default_value in varchar2,
1763 x_translate_flag in varchar2,
1764 x_precision in varchar2,
1765 x_scale in varchar2,
1766 x_flexfield_usage_code in varchar2,
1767 x_flexfield_application_id in varchar2,
1768 x_flexfield_name in varchar2,
1769 x_flex_value_set_app_id in varchar2,
1770 x_flex_value_set_id in varchar2,
1771 x_user_id in varchar2,
1772 x_custom_mode in varchar2,
1773 x_last_update_date in varchar2,
1774 P_NZDT_MODE in VARCHAR2 default 'N'
1775 ) is
1776 tab_id number;
1777 appl_id number;
1778 f_luby number; -- entity owner in file
1779 f_ludate date; -- entity update date in file
1780 db_luby number; -- entity owner in db
1781 db_ludate date; -- entity update date in db
1782 begin
1783 if (P_NZDT_MODE ='Y')
1784 then
1785 begin
1786 AD_ZD_SEED.PREPARE ('FND_COLUMNS');
1790 -- Application and Table again.
1787 end;
1788 end if;
1789 -- Because Column is in the same entity as Table, no need to validate
1791 select A.APPLICATION_ID
1792 into appl_id
1793 from FND_APPLICATION A
1794 where A.APPLICATION_SHORT_NAME = x_application_short_name;
1795
1796 select T.TABLE_ID into tab_id from FND_TABLES T
1797 where T.APPLICATION_ID = appl_id
1798 and T.TABLE_NAME = x_table_name;
1799
1800 ResolveConflictColumn(appl_id, tab_id, x_column_name, x_user_column_name,
1801 x_column_sequence);
1802
1803 -- Translate owner to file_last_updated_by
1804 f_luby := owner_id(x_user_id);
1805
1806 -- Translate char last_update_date to date
1807 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
1808
1809 begin
1810 select LAST_UPDATED_BY, LAST_UPDATE_DATE
1811 into db_luby, db_ludate
1812 from FND_COLUMNS
1813 where APPLICATION_ID = appl_id
1814 and TABLE_ID = tab_id
1815 and COLUMN_NAME = x_column_name;
1816
1817 if (upload_test(f_luby, f_ludate, db_luby,
1818 db_ludate, X_CUSTOM_MODE)) then
1819
1820 update FND_COLUMNS set
1821 USER_COLUMN_NAME = x_user_column_name,
1822 COLUMN_SEQUENCE = x_column_sequence,
1823 COLUMN_TYPE = x_column_type,
1824 WIDTH = x_width,
1825 NULL_ALLOWED_FLAG = x_null_allowed_flag,
1826 DESCRIPTION = x_description,
1827 DEFAULT_VALUE = x_default_value,
1828 TRANSLATE_FLAG = x_translate_flag,
1829 PRECISION = x_precision,
1830 SCALE = x_scale,
1831 FLEXFIELD_USAGE_CODE = x_flexfield_usage_code,
1832 FLEXFIELD_APPLICATION_ID = x_flexfield_application_id,
1833 FLEXFIELD_NAME = x_flexfield_name,
1834 FLEX_VALUE_SET_APPLICATION_ID = x_flex_value_set_app_id,
1835 FLEX_VALUE_SET_ID = x_flex_value_set_id,
1836 LAST_UPDATED_BY = f_luby,
1837 LAST_UPDATE_DATE = f_ludate,
1838 LAST_UPDATE_LOGIN = f_luby
1839 where APPLICATION_ID = appl_id
1840 and TABLE_ID = tab_id
1841 and COLUMN_NAME = x_column_name;
1842 end if;
1843 exception
1844 when no_data_found then
1845 Fnd_XdfDictionary_Pkg.InsertColumn(
1846 appl_id,
1847 tab_id,
1848 x_column_name,
1849 x_user_column_name,
1850 x_column_sequence,
1851 x_column_type,
1852 x_width,
1853 x_null_allowed_flag,
1854 x_description,
1855 x_default_value,
1856 x_translate_flag,
1857 x_precision,
1858 x_scale,
1859 x_flexfield_usage_code,
1860 x_flexfield_application_id,
1861 x_flexfield_name,
1862 x_flex_value_set_app_id,
1863 x_flex_value_set_id,
1864 f_ludate,
1865 f_luby,
1866 f_ludate,
1867 f_luby,
1868 0);
1869 end;
1870 end UploadColumn;
1871
1872 --
1873 -- UploadHistColumn (PUBLIC))
1874 -- Public procedure for afdict.lct to call when uploading columns using
1875 -- using afdict.lct. It calls InsertHistColumn() when needed.
1876 --
1877 procedure UploadHistColumn (
1878 x_application_short_name in varchar2,
1879 x_table_name in varchar2,
1880 x_column_name in varchar2,
1881 x_partition in varchar2,
1882 x_hsize in varchar2,
1883 x_user_id in varchar2,
1884 x_custom_mode in varchar2,
1885 x_last_update_date in varchar2,
1886 P_NZDT_MODE in VARCHAR2 default 'N'
1887 ) is
1888 appl_id number;
1889 f_luby number; -- entity owner in file
1890 f_ludate date; -- entity update date in file
1891 db_luby number; -- entity owner in db
1892 db_ludate date; -- entity update date in db
1893 begin
1894 if (P_NZDT_MODE ='Y')
1895 then
1896 begin
1897 AD_ZD_SEED.PREPARE ('FND_HISTOGRAM_COLS');
1898 end;
1899 end if;
1900 -- Because Column is in the same entity as Table, no need to validate
1901 -- Application and Table again.
1902 select A.APPLICATION_ID
1903 into appl_id
1904 from FND_APPLICATION A
1905 where A.APPLICATION_SHORT_NAME = x_application_short_name;
1906
1907 -- Translate owner to file_last_updated_by
1908 f_luby := owner_id(x_user_id);
1909
1910 -- Translate char last_update_date to date
1911 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
1912
1913 begin
1914 select LAST_UPDATED_BY, LAST_UPDATE_DATE
1915 into db_luby, db_ludate
1916 from FND_HISTOGRAM_COLS
1917 where APPLICATION_ID = appl_id
1918 and TABLE_NAME = x_table_name
1919 and COLUMN_NAME = x_column_name;
1920
1921 if (upload_test(f_luby, f_ludate, db_luby,
1922 db_ludate, X_CUSTOM_MODE)) then
1923
1924 update FND_HISTOGRAM_COLS set
1925 PARTITION = x_partition,
1926 HSIZE = x_hsize,
1927 LAST_UPDATED_BY = f_luby,
1928 LAST_UPDATE_DATE = f_ludate,
1932 and COLUMN_NAME = x_column_name;
1929 LAST_UPDATE_LOGIN = f_luby
1930 where APPLICATION_ID = appl_id
1931 and TABLE_NAME = x_table_name
1933 end if;
1934 exception
1935 when no_data_found then
1936 insert into FND_HISTOGRAM_COLS (
1937 APPLICATION_ID,
1938 TABLE_NAME,
1939 COLUMN_NAME,
1940 PARTITION,
1941 HSIZE,
1942 CREATION_DATE,
1943 CREATED_BY,
1944 LAST_UPDATED_BY,
1945 LAST_UPDATE_DATE,
1946 LAST_UPDATE_LOGIN)
1947 values (
1948 appl_id,
1949 x_table_name,
1950 x_column_name,
1951 x_partition,
1952 x_hsize,
1953 f_ludate,
1954 f_luby,
1955 f_luby,
1956 f_ludate,
1957 f_luby);
1958 end;
1959 end UploadHistColumn;
1960
1961
1962 --
1963 -- UploadHistColumn_MV (PUBLIC))
1964 -- Added for MV histogram DATA
1965
1966 procedure UploadHistColumn_MV (
1967 x_application_short_name in varchar2,
1968 x_table_name in varchar2,
1969 x_column_name in varchar2,
1970 x_partition in varchar2,
1971 x_hsize in varchar2,
1972 x_user_id in varchar2,
1973 x_custom_mode in varchar2,
1974 x_last_update_date in varchar2,
1975 x_mview_owner in varchar2,
1976 P_NZDT_MODE in VARCHAR2 default 'N'
1977 ) is
1978 appl_id number;
1979 f_luby number; -- entity owner in file
1980 f_ludate date; -- entity update date in file
1981 db_luby number; -- entity owner in db
1982 db_ludate date; -- entity update date in db
1983 begin
1984 if (P_NZDT_MODE ='Y')
1985 then
1986 begin
1987 AD_ZD_SEED.PREPARE ('FND_HISTOGRAM_COLS');
1988 end;
1989 end if;
1990 -- Because Column is in the same entity as Table, no need to validate
1991 -- Application and Table again.
1992 select A.APPLICATION_ID
1993 into appl_id
1994 from FND_APPLICATION A
1995 where A.APPLICATION_SHORT_NAME = x_application_short_name;
1996
1997
1998 -- Translate owner to file_last_updated_by
1999 f_luby := owner_id(x_user_id);
2000
2001 -- Translate char last_update_date to date
2002 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
2003
2004 -- application_id is set to -1 if the owner of MV is apps.
2005
2006 if(UPPER(x_mview_owner)='APPS')then
2007 appl_id:=-1;
2008 end if;
2009
2010 begin
2011 select LAST_UPDATED_BY, LAST_UPDATE_DATE
2012 into db_luby, db_ludate
2013 from FND_HISTOGRAM_COLS
2014 where APPLICATION_ID = appl_id
2015 and TABLE_NAME = x_table_name
2016 and COLUMN_NAME = x_column_name
2017 and OWNER =x_mview_owner;
2018
2019
2020 if (upload_test(f_luby, f_ludate, db_luby,
2021 db_ludate, X_CUSTOM_MODE)) then
2022
2023 update FND_HISTOGRAM_COLS set
2024 PARTITION = x_partition,
2025 HSIZE = x_hsize,
2026 LAST_UPDATED_BY = f_luby,
2027 LAST_UPDATE_DATE = f_ludate,
2028 LAST_UPDATE_LOGIN = f_luby
2029 where APPLICATION_ID = appl_id
2030 and TABLE_NAME = x_table_name
2031 and COLUMN_NAME = x_column_name
2032 and OWNER =x_mview_owner;
2033 end if;
2034 exception
2035 when no_data_found then
2036 insert into FND_HISTOGRAM_COLS (
2037 APPLICATION_ID,
2038 TABLE_NAME,
2039 COLUMN_NAME,
2040 PARTITION,
2041 HSIZE,
2042 CREATION_DATE,
2043 CREATED_BY,
2044 LAST_UPDATED_BY,
2045 LAST_UPDATE_DATE,
2046 LAST_UPDATE_LOGIN,
2047 OWNER
2048 )
2049 values (
2050 appl_id,
2051 x_table_name,
2052 x_column_name,
2053 x_partition,
2054 x_hsize,
2055 f_ludate,
2056 f_luby,
2057 f_luby,
2058 f_ludate,
2059 f_luby,
2060 x_mview_owner
2061 );
2062 end;
2063 end UploadHistColumn_MV;
2064
2065
2066 --
2067 -- UploadIndex (PUBLIC))
2068 -- Public procedure for afdict.lct to call when uploading indexes using
2069 -- using afdict.lct. It calls InsertIndex() when needed.
2070 --
2071 procedure UploadIndex (
2072 x_application_short_name in varchar2,
2073 x_table_name in varchar2,
2074 x_index_name in varchar2,
2075 x_uniqueness in varchar2,
2076 x_auto_size in varchar2,
2077 x_description in varchar2,
2078 x_initial_extent in varchar2,
2079 x_next_extent in varchar2,
2080 x_min_extents in varchar2,
2081 x_max_extents in varchar2,
2082 x_ini_trans in varchar2,
2083 x_max_trans in varchar2,
2084 x_pct_free in varchar2,
2085 x_pct_increase in varchar2,
2086 x_user_id in varchar2,
2087 x_custom_mode in varchar2,
2088 x_last_update_date in varchar2,
2089 x_phase_mode in varchar2
2090 ) is
2091 tab_id number;
2092 appl_id number;
2093 f_luby number; -- entity owner in file
2097 -- Bug2631776 new variables to handle update.
2094 f_ludate date; -- entity update date in file
2095 db_luby number; -- entity owner in db
2096 db_ludate date; -- entity update date in db
2098 child_file_ludate date; -- child entity update date in file
2099 child_file_luby number; -- child owner in file
2100 child_db_ludate date; -- child update date in db
2101 child_db_luby number; -- child owner in db
2102 ind_id number;
2103
2104 begin
2105
2106 -- Because Index is in the same entity as Table, no need to validate
2107 -- Application and Table again.
2108
2109 select A.APPLICATION_ID
2110 into appl_id
2111 from FND_APPLICATION A
2112 where A.APPLICATION_SHORT_NAME = x_application_short_name;
2113
2114 select T.TABLE_ID into tab_id from FND_TABLES T
2115 where T.APPLICATION_ID = appl_id
2116 and T.TABLE_NAME = x_table_name;
2117
2118 -- Bug2631776 In this section handle the parent entity
2119 -- and update the child entity so that constraints do not occur.
2120
2121 if (x_phase_mode = 'BEGIN') then
2122
2123 -- Translate owner to file_last_updated_by
2124 f_luby := owner_id(x_user_id);
2125
2126 -- Translate char last_update_date to date
2127 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
2128
2129 begin
2130
2134 where APPLICATION_ID = appl_id
2131 select INDEX_ID
2132 into ind_id
2133 from FND_INDEXES
2135 and TABLE_ID = tab_id
2136 and INDEX_NAME = x_index_name;
2137
2138 select LAST_UPDATED_BY, LAST_UPDATE_DATE
2139 into db_luby, db_ludate
2140 from FND_INDEXES
2141 where APPLICATION_ID = appl_id
2142 and TABLE_ID = tab_id
2143 and INDEX_NAME = x_index_name;
2144
2145 if (upload_test(f_luby, f_ludate, db_luby,
2146 db_ludate, X_CUSTOM_MODE)) then
2147
2148 update FND_INDEXES set
2149 UNIQUENESS = x_uniqueness,
2150 AUTO_SIZE = x_auto_size,
2151 DESCRIPTION = x_description,
2152 INITIAL_EXTENT = x_initial_extent,
2153 NEXT_EXTENT = x_next_extent,
2154 MIN_EXTENTS = x_min_extents,
2155 MAX_EXTENTS = x_max_extents,
2156 INI_TRANS = x_ini_trans,
2157 MAX_TRANS = x_max_trans,
2158 PCT_FREE = x_pct_free,
2159 PCT_INCREASE = x_pct_increase,
2160 LAST_UPDATED_BY = f_luby,
2161 LAST_UPDATE_DATE = f_ludate,
2162 LAST_UPDATE_LOGIN = f_luby
2163 where APPLICATION_ID = appl_id
2164 and TABLE_ID = tab_id
2165 and INDEX_NAME = x_index_name;
2166
2167 end if;
2168
2169 -- Bug3230044 Delete any child records with a negative
2170 -- value for COLUMN_ID.
2171
2172 delete from FND_INDEX_COLUMNS
2173 where APPLICATION_ID = appl_id
2174 and TABLE_ID = tab_id
2175 and INDEX_ID = ind_id
2176 and COLUMN_ID < 0;
2177
2178 -- BUG2631776 rename the child record's COLUMN_SEQUENCE
2179 -- and COLUMN_ID values to a negative value in order to
2180 -- prevent unique constraints while processing the
2181 -- PARENT/CHILD entity.
2182
2183 update FND_INDEX_COLUMNS
2184 set COLUMN_SEQUENCE = -1 * COLUMN_SEQUENCE,
2185 COLUMN_ID = -1 * COLUMN_ID
2186 where APPLICATION_ID = appl_id
2187 and TABLE_ID = tab_id
2188 and INDEX_ID = ind_id;
2189
2190 /*Bug2773876 - Handle special case where COLUMN_SEQUENCE = 0 */
2191
2192 update FND_INDEX_COLUMNS
2193 set COLUMN_SEQUENCE = -1000
2194 where APPLICATION_ID = appl_id
2195 and TABLE_ID = tab_id
2196 and INDEX_ID = ind_id
2197 and COLUMN_SEQUENCE = 0;
2198
2199 exception
2200 when no_data_found then
2201 Fnd_XdfDictionary_Pkg.InsertIndex(
2202 appl_id,
2203 tab_id,
2204 x_index_name,
2205 x_uniqueness,
2206 x_auto_size,
2207 x_description,
2208 x_initial_extent,
2209 x_next_extent,
2210 x_min_extents,
2211 x_max_extents,
2212 x_ini_trans,
2213 x_max_trans,
2214 x_pct_free,
2215 x_pct_increase,
2216 f_ludate,
2217 f_luby,
2218 f_ludate,
2219 f_luby,
2220 0);
2221 end;
2222
2223 else -- phase_mode = 'END'
2224
2225 select INDEX_ID
2226 into ind_id
2227 from FND_INDEXES
2228 where APPLICATION_ID = appl_id
2229 and TABLE_ID = tab_id
2230 and INDEX_NAME = x_index_name;
2231
2232 -- Bug2631776 get the latest value for the last update for the db entity
2236 into child_db_ludate
2233 -- and the file entity.
2234
2235 select max(last_update_date)
2237 from fnd_index_columns
2238 where application_id = appl_id
2239 and table_id = tab_id
2240 and index_id = ind_id
2241 and column_sequence < 0
2242 and column_id < 0;
2243
2244 -- Bug3139883 changed select to also include value if column_sequence =0
2245
2246 select max(last_update_date)
2247 into child_file_ludate
2248 from fnd_index_columns
2249 where application_id = appl_id
2250 and table_id = tab_id
2251 and index_id = ind_id
2252 and column_sequence >= 0
2253 and column_id > 0;
2254
2255 -- If no value which means there were no existing child records
2256 -- in the database therefore skip to the end since the new child
2257 -- records have been updated.
2258
2259 if (child_db_ludate IS NULL) or (child_file_ludate IS NULL) then
2260 GOTO done_label;
2261 end if;
2262
2263 -- Continue on to check the owner value since both have columns.
2264
2265 -- Bug2631776 get the maximum value for the userid that made the
2266 -- last update for the db entity and the file entity.
2267
2268 -- If any non-seed owners, set owner to user
2269 select max(-1)
2270 into child_db_luby
2271 from fnd_index_columns
2272 where application_id = appl_id
2273 and table_id = tab_id
2274 and index_id = ind_id
2275 and column_sequence < 0
2276 and column_id < 0
2277 and last_updated_by not in (0,1,2);
2278
2279 if child_db_luby IS NULL then
2280 child_db_luby := 2; -- All rows are seed data, set seed data owner
2281 end if;
2282
2283 -- If any non-seed owners, set owner to user
2284 select max(-1)
2285 into child_file_luby
2286 from fnd_index_columns
2287 where application_id = appl_id
2288 and table_id = tab_id
2289 and index_id = ind_id
2290 and column_sequence > 0
2291 and column_id > 0
2292 and last_updated_by not in (0,1,2);
2293
2294 if child_file_luby IS NULL then
2295 child_file_luby := 2; -- All rows are seed data, set seed data owner
2296 end if;
2297
2298 -- Bug2631776 perform check to see if update occurs or not.
2299
2300 if (upload_test(child_file_luby, child_file_ludate, child_db_luby, child_db_ludate, x_custom_mode)) then
2301
2302 -- The new child entity rows from the data file are kept so
2303 -- delete the existing db child entity rows.
2304
2305 delete from fnd_index_columns
2306 where application_id = appl_id
2307 and table_id = tab_id
2308 and index_id = ind_id
2309 and column_sequence < 0
2310 and column_id < 0;
2311
2312 else
2313
2314 -- The existing db child entity rows are kept so delete the new child
2315 -- entity rows from the data file
2316 -- Bug3139883 - Modified delete to include the value column_sequence = 0
2317
2318 delete from fnd_index_columns
2319 where application_id = appl_id
2320 and table_id = tab_id
2321 and index_id = ind_id
2322 and column_sequence >= 0
2326 -- it was not replaced by the new child entity rows
2323 and column_id > 0;
2324
2325 -- Rename the existing db entity rows back to normal since
2327 -- from the data file.
2328
2329 update FND_INDEX_COLUMNS
2330 set COLUMN_SEQUENCE = -1 * COLUMN_SEQUENCE,
2331 COLUMN_ID = -1 * COLUMN_ID
2332 where APPLICATION_ID = appl_id
2333 and TABLE_ID = tab_id
2334 and INDEX_ID = ind_id;
2335
2336 /*Bug2773876 - Handle special case where COLUMN_SEQUENCE = 0 */
2337
2338 update FND_INDEX_COLUMNS
2339 set COLUMN_SEQUENCE = 0
2340 where APPLICATION_ID = appl_id
2341 and TABLE_ID = tab_id
2342 and INDEX_ID = ind_id
2343 and COLUMN_SEQUENCE = 1000;
2344
2345 end if;
2346 <<done_label>>
2347
2348 -- check if the file has no child entries to clean up database.
2349
2350 if (child_file_ludate IS NULL) then
2351
2352 if (child_db_ludate IS NOT NULL) then
2353
2354 -- Rename the existing db entity rows back to normal since
2355 -- it was not replaced by the new child entity rows
2356 -- from the data file.
2357
2358 update FND_INDEX_COLUMNS
2359 set COLUMN_SEQUENCE = -1 * COLUMN_SEQUENCE,
2360 COLUMN_ID = -1 * COLUMN_ID
2361 where APPLICATION_ID = appl_id
2365 /*Bug2773876 - Handle special case where COLUMN_SEQUENCE = 0 */
2362 and TABLE_ID = tab_id
2363 and INDEX_ID = ind_id;
2364
2366
2367 update FND_INDEX_COLUMNS
2368 set COLUMN_SEQUENCE = 0
2369 where APPLICATION_ID = appl_id
2370 and TABLE_ID = tab_id
2371 and INDEX_ID = ind_id
2372 and COLUMN_SEQUENCE = 1000;
2373
2374 end if;
2375 end if;
2376 end if;
2377 end UploadIndex;
2378
2379 --
2380 -- UploadIndexColumn (PUBLIC))
2381 -- Public procedure for afdict.lct to call when uploading index columns using
2382 -- using afdict.lct.
2383 --
2384 procedure UploadIndexColumn (
2385 x_application_short_name in varchar2,
2386 x_table_name in varchar2,
2387 x_index_name in varchar2,
2388 x_index_column_name in varchar2,
2389 x_index_column_sequence in varchar2,
2390 x_user_id in varchar2,
2391 x_custom_mode in varchar2,
2392 x_last_update_date in varchar2
2393 ) is
2394 tab_id number;
2395 appl_id number;
2396 idx_id number;
2397 col_id number;
2398 f_luby number; -- entity owner in file
2399 f_ludate date; -- entity update date in file
2400 begin
2401
2402 -- Because Index Column is in the same entity as Table and Index,
2403 -- no need to validate them again.
2404 select A.APPLICATION_ID
2405 into appl_id
2406 from FND_APPLICATION A
2407 where A.APPLICATION_SHORT_NAME = x_application_short_name;
2408
2409 select T.TABLE_ID into tab_id from FND_TABLES T
2410 where T.APPLICATION_ID = appl_id
2411 and T.TABLE_NAME = x_table_name;
2412
2413 select I.INDEX_ID into idx_id from FND_INDEXES I
2414 where I.APPLICATION_ID = appl_id
2415 and I.TABLE_ID = tab_id
2416 and I.INDEX_NAME = x_index_name;
2417
2418 begin
2419 select C.COLUMN_ID into col_id from FND_COLUMNS C
2420 where C.APPLICATION_ID = appl_id
2421 and C.TABLE_ID = tab_id
2422 and C.COLUMN_NAME = x_index_column_name;
2423 exception
2424 when no_data_found then
2425 RAISE_APPLICATION_ERROR(
2426 -20001, 'No data found for fnd column name '
2427 || x_index_column_name || ' object type Index '
2428 || ' Index Name ' ||
2429 x_index_name, TRUE);
2430 end;
2431
2432 -- Translate owner to file_last_updated_by
2433 f_luby := owner_id(x_user_id);
2434
2435 -- Translate char last_update_date to date
2436 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
2437
2438 -- NOTE: no "UPDATE" case as we have renamed all the existing
2439 -- index columns so that they can be compared against the new
2440 -- index columns from the data file to determine which will be
2441 -- updated into the database based on the date and custom factirs.
2442
2443 begin
2444 insert into FND_INDEX_COLUMNS(
2445 APPLICATION_ID,
2446 TABLE_ID,
2447 INDEX_ID,
2448 COLUMN_ID,
2449 COLUMN_SEQUENCE,
2450 LAST_UPDATED_BY,
2451 LAST_UPDATE_DATE,
2452 LAST_UPDATE_LOGIN,
2453 CREATION_DATE,
2454 CREATED_BY)
2455 values (
2456 appl_id,
2457 tab_id,
2458 idx_id,
2459 col_id,
2460 x_index_column_sequence,
2461 f_luby,
2462 f_ludate,
2463 f_luby,
2464 f_ludate,
2465 f_luby);
2466
2467 exception
2468 when dup_val_on_index then
2469 RAISE_APPLICATION_ERROR(
2470 -20001, 'Duplicate value on index for column sequence'
2471 || x_index_column_sequence || ' index name '
2472 || ' Index Name ' ||
2473 x_index_name, TRUE);
2474 end;
2475
2476 end UploadIndexColumn;
2477
2478 --
2479 -- UploadPrimaryKey (PUBLIC))
2480 -- Public procedure for afdict.lct to call when uploading primary key using
2481 -- using afdict.lct. It calls InsertPrimary() when needed.
2482 --
2483 procedure UploadPrimaryKey (
2484 x_application_short_name in varchar2,
2485 x_table_name in varchar2,
2486 x_primary_key_name in varchar2,
2487 x_primary_key_type in varchar2,
2488 x_audit_key_flag in varchar2,
2489 x_description in varchar2,
2490 x_enabled_flag in varchar2,
2491 x_user_id in varchar2,
2492 x_custom_mode in varchar2,
2493 x_last_update_date in varchar2,
2494 x_phase_mode in varchar2,
2495 x_overwrite_PK in varchar2 DEFAULT 'N',
2496 P_NZDT_MODE in VARCHAR2 default 'N'
2497 ) is
2498 tab_id number;
2499 appl_id number;
2500 pk_id number;
2501 tmpid number;
2502 pkmode varchar2(10);
2503 f_luby number; -- entity owner in file
2504 f_ludate date; -- entity update date in file
2505 db_luby number; -- entity owner in db
2506 db_ludate date; -- entity update date in db
2507
2508 -- Bug2631776 new variables to handle update.
2509 child_file_ludate date; -- child entity update date in file
2513
2510 child_file_luby number; -- child owner in file
2511 child_db_ludate date; -- child update date in db
2512 child_db_luby number; -- child owner in db
2514 begin
2515 if (P_NZDT_MODE ='Y')
2516 then
2517 begin
2518 AD_ZD_SEED.PREPARE ('FND_PRIMARY_KEYS');
2519 AD_ZD_SEED.PREPARE ('FND_PRIMARY_KEY_COLUMNS');
2520 end;
2521 end if;
2522
2523 -- Because Primary Key is in the same entity as Table, no need to validate
2524 -- Application and Table again.
2525 select A.APPLICATION_ID
2526 into appl_id
2527 from FND_APPLICATION A
2528 where A.APPLICATION_SHORT_NAME = x_application_short_name;
2529
2530 select T.TABLE_ID into tab_id from FND_TABLES T
2531 where T.APPLICATION_ID = appl_id
2532 and T.TABLE_NAME = x_table_name;
2533
2534 -- Validation on primary_key_type, audit_flag and enabled_flag
2535
2536 pk_id := -1;
2537 ValidatePrimaryKey(x_application_short_name,
2538 x_table_name, x_primary_key_name, tmpid, tmpid, pk_id);
2539 if (pk_id = -1) then
2540 pkmode := 'INSERT';
2541 else
2542 pkmode := 'UPDATE';
2543 end if;
2544
2545 if ((pkmode = 'INSERT' and x_primary_key_type not in ('S', 'D')) or
2546 (pkmode = 'UPDATE' and nvl(x_primary_key_type, 'S') not in ('S', 'D'))) then
2547 RAISE_APPLICATION_ERROR(
2548 -20001, 'Invalid primary key attribute - type, attribute value '
2549 || x_primary_key_type || ' primary key name '
2550 || x_primary_key_name, TRUE);
2551 end if;
2552
2553 if ((pkmode = 'INSERT' and x_audit_key_flag not in ('Y', 'N')) or
2554 (pkmode = 'UPDATE' and nvl(x_audit_key_flag, 'Y') not in ('Y', 'N'))) then
2555 RAISE_APPLICATION_ERROR(
2556 -20001, 'Invalid primary key attribute - audit key, attribute value '
2557 || x_audit_key_flag || ' primary key name '
2558 || x_primary_key_name, TRUE);
2559 end if;
2560
2561 if ((pkmode = 'INSERT' and x_enabled_flag not in ('Y', 'N')) or
2562 (pkmode = 'UPDATE' and nvl(x_enabled_flag, 'Y') not in ('Y', 'N'))) then
2563 RAISE_APPLICATION_ERROR(
2564 -20001, 'Invalid primary key attribute - Enabled flag , attribute value '
2565 || x_enabled_flag|| ' primary key name '
2566 || x_primary_key_name, TRUE);
2567 end if;
2568
2569 if (x_primary_key_type = 'D' and
2570 MultipleDeveloperKeys(appl_id, tab_id, x_primary_key_name)) then
2571 if(x_overwrite_PK = 'Y') then
2572 delete from FND_PRIMARY_KEY_COLUMNS
2573 where APPLICATION_ID = appl_id
2574 and TABLE_ID = tab_id
2575 and PRIMARY_KEY_ID IN (select PRIMARY_KEY_ID from FND_PRIMARY_KEYS
2576 where APPLICATION_ID = appl_id
2577 and TABLE_ID = tab_id
2578 and PRIMARY_KEY_NAME <> upper(x_primary_key_name)
2579 and PRIMARY_KEY_TYPE = 'D'
2580 );
2581 delete from FND_PRIMARY_KEYS
2582 where APPLICATION_ID = appl_id
2583 and TABLE_ID = tab_id
2584 and PRIMARY_KEY_NAME <> upper(x_primary_key_name)
2585 and PRIMARY_KEY_TYPE = 'D';
2586 else
2587 RAISE_APPLICATION_ERROR(
2588 -20001, 'Multiple developer PK table name '
2589 || x_table_name, TRUE);
2590 end if;
2591 end if;
2592
2593 -- Bug2631776 In this section handle the parent entity
2594 -- and update the child entity so that constraints do not occur.
2595
2596 if (x_phase_mode = 'BEGIN') then
2597
2598 -- Translate owner to file_last_updated_by
2599 f_luby := owner_id(x_user_id);
2600
2601 -- Translate char last_update_date to date
2602 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
2603
2604
2605 begin
2606 select LAST_UPDATED_BY, LAST_UPDATE_DATE
2607 into db_luby, db_ludate
2608 from FND_PRIMARY_KEYS
2609 where APPLICATION_ID = appl_id
2610 and TABLE_ID = tab_id
2611 and PRIMARY_KEY_NAME = x_primary_key_name;
2612
2613 if (pkmode = 'UPDATE') then
2614
2615 if (upload_test(f_luby, f_ludate, db_luby,
2616 db_ludate, X_CUSTOM_MODE)) then
2617 update FND_PRIMARY_KEYS set
2618 PRIMARY_KEY_TYPE = x_primary_key_type,
2619 AUDIT_KEY_FLAG = x_audit_key_flag,
2620 ENABLED_FLAG = x_enabled_flag,
2624 LAST_UPDATE_LOGIN = f_luby
2621 DESCRIPTION = x_description,
2622 LAST_UPDATED_BY = f_luby,
2623 LAST_UPDATE_DATE = f_ludate,
2625 where APPLICATION_ID = appl_id
2626 and TABLE_ID = tab_id
2627 and PRIMARY_KEY_NAME = x_primary_key_name;
2628 end if;
2629
2630 -- Bug3230044 Delete any child records with a negative
2631 -- value for COLUMN_ID.
2632
2633 delete from FND_PRIMARY_KEY_COLUMNS
2634 where APPLICATION_ID = appl_id
2635 and TABLE_ID = tab_id
2636 and PRIMARY_KEY_ID = pk_id
2637 and COLUMN_ID < 0;
2638
2639
2640 -- BUG2631776 rename the child record's PRIMARY_KEY_SEQUENCE
2641 -- and COLUMN_ID values to a negative value in order to
2642 -- prevent unique constraints while processing the
2643 -- PARENT/CHILD entity.
2644
2645 update FND_PRIMARY_KEY_COLUMNS
2646 set PRIMARY_KEY_SEQUENCE = -1 * PRIMARY_KEY_SEQUENCE,
2647 COLUMN_ID = -1 * COLUMN_ID
2648 where APPLICATION_ID = appl_id
2649 and TABLE_ID = tab_id
2650 and PRIMARY_KEY_ID = pk_id;
2651
2652 /*Bug3139883 - Handle special case where PRIMARY_KEY_SEQUENCE = 0 */
2653
2654 update FND_PRIMARY_KEY_COLUMNS
2655 set PRIMARY_KEY_SEQUENCE = -1000
2656 where APPLICATION_ID = appl_id
2657 and TABLE_ID = tab_id
2658 and PRIMARY_KEY_ID = pk_id
2659 and PRIMARY_KEY_SEQUENCE = 0;
2660
2661 else
2662 Fnd_XdfDictionary_Pkg.InsertPrimaryKey(
2663 appl_id,
2664 tab_id,
2665 x_primary_key_name,
2666 x_primary_key_type,
2667 x_audit_key_flag,
2668 x_enabled_flag,
2669 x_description,
2670 f_ludate,
2671 f_luby,
2672 f_ludate,
2673 f_luby,
2674 0);
2675 end if;
2676
2677 exception
2678 when no_data_found then
2679 Fnd_XdfDictionary_Pkg.InsertPrimaryKey(
2680 appl_id,
2681 tab_id,
2682 x_primary_key_name,
2683 x_primary_key_type,
2684 x_audit_key_flag,
2685 x_enabled_flag,
2686 x_description,
2687 f_ludate,
2688 f_luby,
2689 f_ludate,
2690 f_luby,
2691 0);
2692
2693 end;
2694
2695 else -- phase_mode = 'END'
2696
2697 -- Bug2631776 get the latest value for the last update for the db entity
2698 -- and the file entity.
2699
2700 select max(last_update_date)
2701 into child_db_ludate
2702 from fnd_primary_key_columns
2703 where application_id = appl_id
2704 and table_id = tab_id
2705 and primary_key_id = pk_id
2706 and primary_key_sequence < 0
2707 and column_id < 0;
2708
2709 -- Bug3139883 changed select to also include value if primary_key_sequence =0
2710
2711 select max(last_update_date)
2712 into child_file_ludate
2713 from fnd_primary_key_columns
2714 where application_id = appl_id
2715 and table_id = tab_id
2716 and primary_key_id = pk_id
2717 and PRIMARY_KEY_SEQUENCE >= 0
2718 and column_id > 0;
2719
2720 -- If no value which means there were no existing child records
2721 -- in the database therefore skip to the end since the new child
2722 -- records have been updated.
2723
2724 if (child_db_ludate IS NULL) or (child_file_ludate IS NULL) then
2725 GOTO done_label;
2726 end if;
2727
2728 -- Continue on to check the owner value since both have columns.
2729
2730 -- Bug2631776 get the maximum value for the userid that made the
2731 -- last update for the db entity and the file entity.
2732
2733 -- If any non-seed owners, set owner to user
2734 select max(-1)
2735 into child_db_luby
2736 from fnd_primary_key_columns
2737 where application_id = appl_id
2738 and table_id = tab_id
2739 and primary_key_id = pk_id
2740 and PRIMARY_KEY_SEQUENCE < 0
2741 and column_id < 0
2742 and last_updated_by not in (0,1,2);
2743
2744 if child_db_luby IS NULL then
2745 child_db_luby := 2; -- All rows are seed data, set seed data owner
2746 end if;
2747
2748 -- If any non-seed owners, set owner to user
2749 select max(-1)
2750 into child_file_luby
2751 from fnd_primary_key_columns
2752 where application_id = appl_id
2753 and table_id = tab_id
2754 and primary_key_id = pk_id
2755 and PRIMARY_KEY_SEQUENCE > 0
2756 and column_id > 0
2757 and last_updated_by not in (0,1,2);
2758
2759 if child_file_luby IS NULL then
2760 child_file_luby := 2; -- All rows are seed data, set seed data owner
2761 end if;
2762
2763 -- Bug2631776 perform check to see if update occurs or not.
2764
2765 if (upload_test(child_file_luby, child_file_ludate, child_db_luby, child_db_ludate, x_custom_mode)) then
2766
2767 -- The new child entity rows from the data file are kept so
2768 -- delete the existing db child entity rows.
2769
2770 delete from fnd_primary_key_columns
2771 where application_id = appl_id
2772 and table_id = tab_id
2773 and primary_key_id = pk_id
2774 and PRIMARY_KEY_SEQUENCE < 0
2775 and column_id < 0;
2776
2777 else
2778
2779 -- The existing db child entity rows are kept so delete the new child
2780 -- entity rows from the data file
2781 -- Bug3139883 - Modified delete to include the value column_sequence = 0
2782
2783 delete from fnd_primary_key_columns
2784 where application_id = appl_id
2788 and column_id > 0;
2785 and table_id = tab_id
2786 and primary_key_id = pk_id
2787 and PRIMARY_KEY_SEQUENCE >= 0
2789
2790 -- Rename the existing db entity rows back to normal since
2791 -- it was not replaced by the new child entity rows
2792 -- from the data file.
2793
2794 update FND_PRIMARY_KEY_COLUMNS
2795 set PRIMARY_KEY_SEQUENCE = -1 * PRIMARY_KEY_SEQUENCE,
2796 COLUMN_ID = -1 * COLUMN_ID
2797 where APPLICATION_ID = appl_id
2798 and TABLE_ID = tab_id
2799 and PRIMARY_KEY_ID = pk_id;
2800
2801 /*Bug3139883 - Handle special case where PRIMARY_KEY_SEQUENCE = 0 */
2802
2803 update FND_PRIMARY_KEY_COLUMNS
2804 set PRIMARY_KEY_SEQUENCE = 0
2805 where APPLICATION_ID = appl_id
2806 and TABLE_ID = tab_id
2807 and PRIMARY_KEY_ID = pk_id
2808 and PRIMARY_KEY_SEQUENCE = 1000;
2809
2810 end if;
2811 <<done_label>>
2812
2813 -- check if the file has no child entries to clean up database.
2814
2815 if (child_file_ludate IS NULL) then
2816
2817 if (child_db_ludate IS NOT NULL) then
2818
2819 -- Rename the existing db entity rows back to normal since
2820 -- it was not replaced by the new child entity rows
2821 -- from the data file.
2822
2823 update FND_PRIMARY_KEY_COLUMNS
2824 set PRIMARY_KEY_SEQUENCE = -1 * PRIMARY_KEY_SEQUENCE,
2825 COLUMN_ID = -1 * COLUMN_ID
2826 where APPLICATION_ID = appl_id
2827 and TABLE_ID = tab_id
2828 and PRIMARY_KEY_ID = pk_id;
2829
2830 /*Bug3139883 - Handle special case where PRIMARY_KEY_SEQUENCE = 0 */
2831
2832 update FND_PRIMARY_KEY_COLUMNS
2833 set PRIMARY_KEY_SEQUENCE = 0
2834 where APPLICATION_ID = appl_id
2835 and TABLE_ID = tab_id
2836 and PRIMARY_KEY_ID = pk_id
2837 and PRIMARY_KEY_SEQUENCE = 1000;
2838
2839 end if;
2840 end if;
2841 end if;
2842
2843 end UploadPrimaryKey;
2844
2845 --
2846 -- UploadPrimaryKeyColumn (PUBLIC))
2847 -- Public procedure for afdict.lct to call when uploading primary key column
2848 -- using afdict.lct.
2849 --
2850 procedure UploadPrimaryKeyColumn (
2851 x_application_short_name in varchar2,
2852 x_table_name in varchar2,
2853 x_primary_key_name in varchar2,
2854 x_primary_key_column_name in varchar2,
2855 x_primary_key_column_sequence in varchar2,
2856 x_user_id in varchar2,
2857 x_custom_mode in varchar2,
2858 x_last_update_date in varchar2,
2859 P_NZDT_MODE in VARCHAR2 default 'N'
2860 ) is
2861 tab_id number;
2862 appl_id number;
2863 pk_id number;
2864 col_id number;
2865 f_luby number; -- entity owner in file
2866 f_ludate date; -- entity update date in file
2867
2868 begin
2869 if (P_NZDT_MODE ='Y')
2870 then
2871 begin
2872 AD_ZD_SEED.PREPARE ('FND_PRIMARY_KEY_COLUMNS');
2873 end;
2874 end if;
2875 -- No need to validate/check Application, Table or Primary Key.
2876 -- Within the same entity.
2877 select A.APPLICATION_ID
2878 into appl_id
2882 select T.TABLE_ID into tab_id from FND_TABLES T
2879 from FND_APPLICATION A
2880 where A.APPLICATION_SHORT_NAME = x_application_short_name;
2881
2883 where T.APPLICATION_ID = appl_id
2884 and T.TABLE_NAME = x_table_name;
2885
2886 select P.PRIMARY_KEY_ID into pk_id from FND_PRIMARY_KEYS P
2887 where P.APPLICATION_ID = appl_id
2888 and P.TABLE_ID = tab_id
2889 and P.PRIMARY_KEY_NAME = x_primary_key_name;
2890
2891 begin
2892 select C.COLUMN_ID into col_id from FND_COLUMNS C
2893 where C.APPLICATION_ID = appl_id
2894 and C.TABLE_ID = tab_id
2895 and C.COLUMN_NAME = x_primary_key_column_name;
2896 exception
2897 when no_data_found then
2898 RAISE_APPLICATION_ERROR(
2899 -20001, 'No data found Fnd columns column name '
2900 || x_primary_key_column_name || ' object type - primary key '
2901 || x_primary_key_name, TRUE);
2902 end;
2903
2904 -- Translate owner to file_last_updated_by
2905 f_luby := owner_id(x_user_id);
2906
2907 -- Translate char last_update_date to date
2908 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
2909
2910 -- NOTE: no "UPDATE" case as we have renamed all the existing
2911 -- primary key columns so that they can be compared against the new
2912 -- primary key columns from the data file to determine which will be
2916 insert into FND_PRIMARY_KEY_COLUMNS(
2913 -- updated into the database based on the date and custom factirs.
2914
2915 begin
2917 APPLICATION_ID,
2918 TABLE_ID,
2919 PRIMARY_KEY_ID,
2920 COLUMN_ID,
2921 PRIMARY_KEY_SEQUENCE,
2922 LAST_UPDATED_BY,
2923 LAST_UPDATE_DATE,
2924 LAST_UPDATE_LOGIN,
2925 CREATION_DATE,
2926 CREATED_BY)
2927 values (
2928 appl_id,
2929 tab_id,
2930 pk_id,
2931 col_id,
2932 x_primary_key_column_sequence,
2933 f_luby,
2934 f_ludate,
2935 f_luby,
2936 f_ludate,
2937 f_luby);
2938 exception
2939 when dup_val_on_index then
2940 RAISE_APPLICATION_ERROR(
2941 -20001, 'Duplicate value on index - column sequence '
2942 || x_primary_key_column_name || ' object name - primary key name'
2943 || x_primary_key_name, TRUE);
2944 end;
2945
2946 end UploadPrimaryKeyColumn;
2947
2948 --
2949 -- UploadForeignKey (PUBLIC))
2950 -- Public procedure for afdict.lct to call when uploading foreign key using
2951 -- using afdict.lct. It calls InsertForeign() when needed.
2952 --
2953 procedure UploadForeignKey (
2954 x_application_short_name in varchar2,
2955 x_table_name in varchar2,
2956 x_foreign_key_name in varchar2,
2957 x_primary_key_application_name in varchar2,
2958 x_primary_key_table_name in varchar2,
2959 x_primary_key_name in varchar2,
2960 x_description in varchar2,
2961 x_cascade_behavior in varchar2,
2962 x_foreign_key_relation in varchar2,
2963 x_condition in varchar2,
2964 x_enabled_flag in varchar2,
2965 x_user_id in varchar2,
2966 x_custom_mode in varchar2,
2967 x_last_update_date in varchar2,
2968 x_phase_mode in varchar2,
2969 P_NZDT_MODE in VARCHAR2 default 'N'
2970 ) is
2971 tab_id number;
2972 appl_id number;
2973 pk_appl_id number;
2974 pk_tab_id number;
2975 pk_id number;
2976 f_luby number; -- entity owner in file
2977 f_ludate date; -- entity update date in file
2978 db_luby number; -- entity owner in db
2979 db_ludate date; -- entity update date in db
2980
2981 -- Bug2631776 new variables to handle update.
2982 child_file_ludate date; -- child entity update date in file
2983 child_file_luby number; -- child owner in file
2984 child_db_ludate date; -- child update date in db
2985 child_db_luby number; -- child owner in db
2986 fk_id number;
2987
2988 begin
2989 if (P_NZDT_MODE ='Y')
2990 then
2991 begin
2992 AD_ZD_SEED.PREPARE ('FND_FOREIGN_KEYS');
2993 AD_ZD_SEED.PREPARE ('FND_FOREIGN_KEY_COLUMNS');
2994 end;
2995 end if;
2996 select A.APPLICATION_ID
2997 into appl_id
2998 from FND_APPLICATION A
2999 where A.APPLICATION_SHORT_NAME = x_application_short_name;
3000
3001 select T.TABLE_ID into tab_id from FND_TABLES T
3002 where T.APPLICATION_ID = appl_id
3003 and T.TABLE_NAME = x_table_name;
3004
3005 -- Validate if primary key exists
3006
3007 pk_id := -1;
3008
3009 ValidatePrimaryKey(x_primary_key_application_name,
3010 x_primary_key_table_name,
3011 x_primary_key_name, pk_appl_id, pk_tab_id, pk_id);
3012
3013 if (pk_id = -1) then
3014 pk_appl_id := -1;
3015 pk_tab_id := -1;
3016
3017 end if;
3018
3019 if (x_phase_mode = 'BEGIN') then
3020
3021
3022 -- Translate owner to file_last_updated_by
3023 f_luby := owner_id(x_user_id);
3024
3025 -- Translate char last_update_date to date
3026 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
3027
3028 begin
3029
3030 select FOREIGN_KEY_ID
3031 into fk_id
3032 from FND_FOREIGN_KEYS
3033 where APPLICATION_ID = appl_id
3034 and TABLE_ID = tab_id
3035 and FOREIGN_KEY_NAME = x_foreign_key_name;
3036
3037 select LAST_UPDATED_BY, LAST_UPDATE_DATE
3038 into db_luby, db_ludate
3039 from FND_FOREIGN_KEYS
3040 where APPLICATION_ID = appl_id
3041 and TABLE_ID = tab_id
3042 and FOREIGN_KEY_NAME = x_foreign_key_name;
3043
3044 if (upload_test(f_luby, f_ludate, db_luby,
3045 db_ludate, X_CUSTOM_MODE)) then
3046
3047 update FND_FOREIGN_KEYS set
3048 PRIMARY_KEY_APPLICATION_ID = pk_appl_id,
3049 PRIMARY_KEY_TABLE_ID = pk_tab_id,
3050 PRIMARY_KEY_ID = pk_id,
3051 CASCADE_BEHAVIOR = x_cascade_behavior,
3052 FOREIGN_KEY_RELATION = x_foreign_key_relation,
3053 DESCRIPTION = x_description,
3054 CONDITION = x_condition,
3055 ENABLED_FLAG = x_enabled_flag,
3056 LAST_UPDATED_BY = f_luby,
3057 LAST_UPDATE_DATE = f_ludate,
3058 LAST_UPDATE_LOGIN = f_luby
3059 where APPLICATION_ID = appl_id
3060 and TABLE_ID = tab_id
3061 and FOREIGN_KEY_NAME = x_foreign_key_name;
3062
3063 end if;
3064
3065 -- Bug3230044 Delete any child records with a negative
3066 -- value for COLUMN_ID.
3067
3068 delete from FND_FOREIGN_KEY_COLUMNS
3069 where APPLICATION_ID = appl_id
3070 and TABLE_ID = tab_id
3071 and FOREIGN_KEY_ID = fk_id
3072 and COLUMN_ID < 0;
3073
3074
3075 -- BUG2631776 rename the child record's FOREIGN_KEY_SEQUENCE
3076 -- and COLUMN_ID values to a negative value in order to
3077 -- prevent unique constraints while processing the
3081 set FOREIGN_KEY_SEQUENCE = -1 * FOREIGN_KEY_SEQUENCE,
3078 -- PARENT/CHILD entity.
3079
3080 update FND_FOREIGN_KEY_COLUMNS
3082 COLUMN_ID = -1 * COLUMN_ID
3083 where APPLICATION_ID = appl_id
3084 and TABLE_ID = tab_id
3085 and FOREIGN_KEY_ID = fk_id;
3086
3087 /*Bug3139883 - Handle special case where FOREIGN_KEY_SEQUENCE = 0 */
3088
3089 update FND_FOREIGN_KEY_COLUMNS
3090 set FOREIGN_KEY_SEQUENCE = -1000
3091 where APPLICATION_ID = appl_id
3092 and TABLE_ID = tab_id
3093 and FOREIGN_KEY_ID = fk_id
3094 and FOREIGN_KEY_SEQUENCE = 0;
3095
3096 exception
3097 when no_data_found then
3098 Fnd_XdfDictionary_Pkg.InsertForeignKey(
3099 appl_id,
3100 tab_id,
3101 x_foreign_key_name,
3102 pk_appl_id,
3103 pk_tab_id,
3104 pk_id,
3105 x_description,
3106 x_cascade_behavior,
3107 x_foreign_key_relation,
3108 x_condition,
3109 x_enabled_flag,
3110 f_ludate,
3111 f_luby,
3112 f_ludate,
3113 f_luby,
3114 0);
3115 end;
3116
3117 else -- phase_mode = 'END'
3118
3119 select FOREIGN_KEY_ID
3120 into fk_id
3121 from FND_FOREIGN_KEYS
3122 where APPLICATION_ID = appl_id
3123 and TABLE_ID = tab_id
3124 and FOREIGN_KEY_NAME = x_foreign_key_name;
3125
3126
3127 -- Bug2631776 get the latest value for the last update for the db entity
3128 -- and the file entity.
3129
3130 select max(last_update_date)
3131 into child_db_ludate
3132 from fnd_foreign_key_columns
3133 where application_id = appl_id
3134 and table_id = tab_id
3135 and foreign_key_id = fk_id
3136 and foreign_key_sequence < 0
3137 and column_id < 0;
3138
3139 -- Bug3139883 changed select to also include value if foreign_key_sequence =0
3140
3141 select max(last_update_date)
3142 into child_file_ludate
3143 from fnd_foreign_key_columns
3144 where application_id = appl_id
3145 and table_id = tab_id
3146 and foreign_key_id = fk_id
3147 and foreign_key_sequence >= 0
3148 and column_id > 0;
3149
3150 -- If no value which means there were no existing child records
3151 -- in the database therefore skip to the end since the new child
3152 -- records have been updated.
3153
3154 if (child_db_ludate IS NULL) or (child_file_ludate IS NULL) then
3155 GOTO done_label;
3156 end if;
3157
3158 -- Continue on to check the owner value since both have columns.
3159
3160 -- Bug2631776 get the maximum value for the userid that made the
3161 -- last update for the db entity and the file entity.
3162
3163 -- If any non-seed owners, set owner to user
3164 select max(-1)
3165 into child_db_luby
3166 from fnd_foreign_key_columns
3167 where application_id = appl_id
3168 and table_id = tab_id
3169 and foreign_key_id = fk_id
3170 and foreign_key_sequence < 0
3171 and column_id < 0
3172 and last_updated_by not in (0,1,2);
3173
3174 if child_db_luby IS NULL then
3175 child_db_luby := 2; -- All rows are seed data, set seed data owner
3176 end if;
3177
3178 -- If any non-seed owners, set owner to user
3179 select max(-1)
3180 into child_file_luby
3181 from fnd_foreign_key_columns
3182 where application_id = appl_id
3183 and table_id = tab_id
3184 and foreign_key_id = fk_id
3185 and foreign_key_sequence > 0
3186 and column_id > 0
3187 and last_updated_by not in (0,1,2);
3188
3189 if child_file_luby IS NULL then
3190 child_file_luby := 2; -- All rows are seed data, set seed data owner
3191 end if;
3192
3193 -- Bug2631776 perform check to see if update occurs or not.
3194
3195 if (upload_test(child_file_luby, child_file_ludate, child_db_luby, child_db_ludate, x_custom_mode)) then
3196
3197 -- The new child entity rows from the data file are kept so
3198 -- delete the existing db child entity rows.
3199
3200 delete from fnd_foreign_key_columns
3201 where application_id = appl_id
3202 and table_id = tab_id
3203 and foreign_key_id = fk_id
3204 and foreign_key_sequence < 0
3205 and column_id < 0;
3206
3207 else
3208
3209 -- The existing db child entity rows are kept so delete the new child
3210 -- entity rows from the data file
3211 -- Bug3139883 - Modified delete to include the value column_sequence = 0
3212
3213 delete from fnd_foreign_key_columns
3214 where application_id = appl_id
3215 and table_id = tab_id
3216 and foreign_key_id = fk_id
3217 and foreign_key_sequence >= 0
3218 and column_id > 0;
3219
3220 -- Rename the existing db entity rows back to normal since
3221 -- it was not replaced by the new child entity rows
3222 -- from the data file.
3223
3224 update FND_FOREIGN_KEY_COLUMNS
3225 set FOREIGN_KEY_SEQUENCE = -1 * FOREIGN_KEY_SEQUENCE,
3226 COLUMN_ID = -1 * COLUMN_ID
3227 where APPLICATION_ID = appl_id
3228 and TABLE_ID = tab_id
3229 and FOREIGN_KEY_ID = fk_id;
3230
3231 /*Bug3139883 - Handle special case where FOREIGN_KEY_SEQUENCE = 0 */
3232
3233 update FND_FOREIGN_KEY_COLUMNS
3234 set FOREIGN_KEY_SEQUENCE = 0
3235 where APPLICATION_ID = appl_id
3236 and TABLE_ID = tab_id
3237 and FOREIGN_KEY_ID = fk_id
3241 <<done_label>>
3238 and FOREIGN_KEY_SEQUENCE = 1000;
3239
3240 end if;
3242
3243 -- check if the file has no child entries to clean up database.
3244
3245 if (child_file_ludate IS NULL) then
3246
3247 if (child_db_ludate IS NOT NULL) then
3248
3249 -- Rename the existing db entity rows back to normal since
3250 -- it was not replaced by the new child entity rows
3251 -- from the data file.
3252
3253 update FND_FOREIGN_KEY_COLUMNS
3254 set FOREIGN_KEY_SEQUENCE = -1 * FOREIGN_KEY_SEQUENCE,
3258 and FOREIGN_KEY_ID = fk_id;
3255 COLUMN_ID = -1 * COLUMN_ID
3256 where APPLICATION_ID = appl_id
3257 and TABLE_ID = tab_id
3259
3260 /*Bug3139883 - Handle special case where FOREIGN_KEY_SEQUENCE = 0 */
3261
3262 update FND_FOREIGN_KEY_COLUMNS
3263 set FOREIGN_KEY_SEQUENCE = 0
3264 where APPLICATION_ID = appl_id
3265 and TABLE_ID = tab_id
3266 and FOREIGN_KEY_ID = fk_id
3267 and FOREIGN_KEY_SEQUENCE = 1000;
3268
3269 end if;
3270 end if;
3271 end if;
3272 end UploadForeignKey;
3273
3274 --
3275 -- UploadForeignKeyColumn (PUBLIC))
3276 -- Public procedure for afdict.lct to call when uploading foreign key column
3277 -- using afdict.lct.
3278 --
3279 procedure UploadForeignKeyColumn (
3280 x_application_short_name in varchar2,
3281 x_table_name in varchar2,
3282 x_foreign_key_name in varchar2,
3283 x_foreign_key_column_name in varchar2,
3284 x_foreign_key_column_sequence in varchar2,
3285 x_cascade_value in varchar2,
3286 x_user_id in varchar2,
3287 x_custom_mode in varchar2,
3288 x_last_update_date in varchar2,
3289 P_NZDT_MODE in VARCHAR2 default 'N'
3290 ) is
3291 tab_id number;
3292 appl_id number;
3293 fk_id number;
3294 col_id number;
3295 f_luby number; -- entity owner in file
3296 f_ludate date; -- entity update date in file
3297
3298 begin
3299 if (P_NZDT_MODE ='Y')
3300 then
3301 begin
3302 AD_ZD_SEED.PREPARE ('FND_FOREIGN_KEY_COLUMNS');
3303 end;
3304 end if;
3305 -- No need to validate/check Application, Table or Foreign Key.
3306 -- Within the same entity.
3307 select A.APPLICATION_ID
3308 into appl_id
3309 from FND_APPLICATION A
3310 where A.APPLICATION_SHORT_NAME = x_application_short_name;
3311
3312 select T.TABLE_ID into tab_id from FND_TABLES T
3313 where T.APPLICATION_ID = appl_id
3314 and T.TABLE_NAME = x_table_name;
3315
3316 select F.FOREIGN_KEY_ID into fk_id from FND_FOREIGN_KEYS F
3317 where F.APPLICATION_ID = appl_id
3318 and F.TABLE_ID = tab_id
3319 and F.FOREIGN_KEY_NAME = x_foreign_key_name;
3320
3321 begin
3322 select C.COLUMN_ID into col_id from FND_COLUMNS C
3323 where C.APPLICATION_ID = appl_id
3324 and C.TABLE_ID = tab_id
3325 and C.COLUMN_NAME = x_foreign_key_column_name;
3326 exception
3327 when no_data_found then
3328 RAISE_APPLICATION_ERROR(
3329 -20001, 'No data found Fnd columns column name '
3330 || x_foreign_key_column_name || ' object type - foreign key '
3331 || x_foreign_key_name, TRUE);
3332 end;
3333
3334 -- Translate owner to file_last_updated_by
3335 f_luby := owner_id(x_user_id);
3336
3337 -- Translate char last_update_date to date
3338 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
3339
3340 -- NOTE: no "UPDATE" case as we have renamed all the existing
3341 -- foreign key columns so that they can be compared against the new
3345
3342 -- foreign key columns from the data file to determine which will be
3343 -- updated into the database based on the date and custom factirs.
3344
3346 begin
3347 insert into FND_FOREIGN_KEY_COLUMNS(
3348 APPLICATION_ID,
3349 TABLE_ID,
3350 FOREIGN_KEY_ID,
3351 COLUMN_ID,
3352 FOREIGN_KEY_SEQUENCE,
3353 CASCADE_VALUE,
3354 LAST_UPDATED_BY,
3355 LAST_UPDATE_DATE,
3356 LAST_UPDATE_LOGIN,
3357 CREATION_DATE,
3358 CREATED_BY)
3359 values (
3360 appl_id,
3361 tab_id,
3362 fk_id,
3363 col_id,
3364 x_foreign_key_column_sequence,
3365 x_cascade_value,
3366 f_luby,
3367 f_ludate,
3368 f_luby,
3369 f_ludate,
3370 f_luby);
3371 exception
3372 when dup_val_on_index then
3373 RAISE_APPLICATION_ERROR(
3374 -20001, 'Duplicate value on index column sequence '
3375 || x_foreign_key_column_sequence || ' object type - foreign key '
3376 || x_foreign_key_name, TRUE);
3377 end;
3378 end UploadForeignKeyColumn;
3379
3380
3381 --
3382 -- UploadSequence (PUBLIC))
3383 -- Public procedure for afdict.lct to call when uploading sequence
3384 -- using afdict.lct. It calls InsertSequence when needed.
3385 --
3386 procedure UploadSequence (
3387 x_application_short_name in varchar2,
3388 x_sequence_name in varchar2,
3389 x_start_value in varchar2,
3390 x_description in varchar2,
3391 x_increment_by in varchar2,
3392 x_min_value in varchar2,
3393 x_max_value in varchar2,
3394 x_cache_size in varchar2,
3395 x_cycle_flag in varchar2,
3396 x_order_flag in varchar2,
3397 x_user_id in varchar2,
3398 x_custom_mode in varchar2,
3399 x_last_update_date in varchar2
3400 ) is
3401 appl_id number;
3402 f_luby number; -- entity owner in file
3403 f_ludate date; -- entity update date in file
3404 db_luby number; -- entity owner in db
3405 db_ludate date; -- entity update date in db
3406 begin
3407 -- Validate Application.
3408 begin
3409 select A.APPLICATION_ID
3410 into appl_id
3411 from FND_APPLICATION A
3412 where A.APPLICATION_SHORT_NAME = x_application_short_name;
3413
3414 exception
3415 when no_data_found then
3416 RAISE_APPLICATION_ERROR(
3417 -20001, 'No data found Fnd application - application name '
3418 || x_application_short_name, TRUE);
3419 end;
3420
3421 -- Translate owner to file_last_updated_by
3422 f_luby := owner_id(x_user_id);
3423
3424 -- Translate char last_update_date to date
3425 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
3426
3427 begin
3428 select LAST_UPDATED_BY, LAST_UPDATE_DATE
3429 into db_luby, db_ludate
3430 from FND_SEQUENCES
3431 where APPLICATION_ID = appl_id
3432 and SEQUENCE_NAME = x_sequence_name;
3433
3434 if (upload_test(f_luby, f_ludate, db_luby,
3435 db_ludate, X_CUSTOM_MODE)) then
3436
3437
3438 -- Check if this is a new table or old table
3439 update FND_SEQUENCES set
3440 START_VALUE = x_start_value,
3441 DESCRIPTION = x_description,
3442 INCREMENT_BY = x_increment_by,
3443 MIN_VALUE = x_min_value,
3447 ORDER_FLAG = x_order_flag,
3444 MAX_VALUE = x_max_value,
3445 CACHE_SIZE = x_cache_size,
3446 CYCLE_FLAG = x_cycle_flag,
3448 LAST_UPDATED_BY = f_luby,
3449 LAST_UPDATE_DATE = f_ludate,
3450 LAST_UPDATE_LOGIN = f_luby
3451 where APPLICATION_ID = appl_id
3452 and SEQUENCE_NAME = x_sequence_name;
3453 end if;
3454 exception
3455 when no_data_found then
3456 Fnd_XdfDictionary_Pkg.InsertSequence(
3457 appl_id,
3458 x_sequence_name,
3459 x_start_value,
3460 x_description,
3461 x_increment_by,
3462 x_min_value,
3463 x_max_value,
3464 x_cache_size,
3465 x_cycle_flag,
3466 x_order_flag,
3467 f_ludate,
3468 f_luby,
3469 f_ludate,
3470 f_luby,
3471 0);
3472 end;
3473 end UploadSequence;
3474
3475 --
3476 -- UploadView (PUBLIC))
3477 -- Public procedure for afdict.lct to call when uploading view
3478 -- using afdict.lct. It calls InsertView when needed.
3479 --
3480 procedure UploadView (
3481 x_application_short_name in varchar2,
3482 x_view_name in varchar2,
3483 x_text in varchar2,
3487 x_last_update_date in varchar2,
3484 x_description in varchar2,
3485 x_user_id in varchar2,
3486 x_custom_mode in varchar2,
3488 x_phase_mode in varchar2,
3489 P_NZDT_MODE in VARCHAR2 default 'N'
3490 ) is
3491 appl_id number;
3492 f_luby number; -- entity owner in file
3493 f_ludate date; -- entity update date in file
3494 db_luby number; -- entity owner in db
3495 db_ludate date; -- entity update date in db
3496 -- Bug2631776 new variables to handle update.
3497 child_file_ludate date; -- child entity update date in file
3498 child_file_luby number; -- child owner in file
3499 child_db_ludate date; -- child update date in db
3500 child_db_luby number; -- child owner in db
3501 vw_id number;
3502 first_char varchar2(1); -- first character in column_name
3503
3504 begin
3505 -- Validate Application
3506 if (P_NZDT_MODE ='Y')
3507 then
3508 begin
3509 AD_ZD_SEED.PREPARE ('FND_VIEWS');
3510 AD_ZD_SEED.PREPARE ('FND_VIEW_COLUMNS');
3511 end;
3512 end if;
3513 begin
3514 select A.APPLICATION_ID
3515 into appl_id
3516 from FND_APPLICATION A
3517 where A.APPLICATION_SHORT_NAME = x_application_short_name;
3518 exception
3519 when no_data_found then
3520 RAISE_APPLICATION_ERROR(
3521 -20001, 'No data found Fnd application - application name '
3522 || x_application_short_name, TRUE);
3523 end;
3524
3525 if (x_phase_mode = 'BEGIN') then
3526
3527 -- Translate owner to file_last_updated_by
3528 f_luby := owner_id(x_user_id);
3529
3530 -- Translate char last_update_date to date
3531 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
3532
3533 begin
3534
3535 select VIEW_ID
3536 into vw_id
3537 from fnd_views
3538 where application_id = appl_id
3539 and VIEW_NAME = x_view_name;
3540
3541 select LAST_UPDATED_BY, LAST_UPDATE_DATE
3542 into db_luby, db_ludate
3543 from FND_VIEWS
3544 where APPLICATION_ID = appl_id
3545 and VIEW_NAME = x_view_name;
3546
3547 if (upload_test(f_luby, f_ludate, db_luby,
3548 db_ludate, X_CUSTOM_MODE)) then
3549
3550 update FND_VIEWS set
3551 TEXT = x_text,
3552 DESCRIPTION = x_description,
3553 LAST_UPDATED_BY = f_luby,
3554 LAST_UPDATE_DATE = f_ludate,
3555 LAST_UPDATE_LOGIN = f_luby
3556 where APPLICATION_ID = appl_id
3557 and VIEW_NAME = x_view_name;
3558 end if;
3559
3560 -- BUG2631776 rename the child record's COLUMN_SEQUENCE
3561 -- and COLUMN_NAME to in order to prevent unique
3562 -- constraints while processing the PARENT/CHILD entity.
3563
3564 update FND_VIEW_COLUMNS
3565 set COLUMN_SEQUENCE = -1 * COLUMN_SEQUENCE,
3566 COLUMN_NAME = decode(instr(COLUMN_NAME,'_'),0,concat('#',COLUMN_NAME),
3567 replace(COLUMN_NAME, '_','#'))
3568 where APPLICATION_ID = appl_id
3569 and VIEW_ID = vw_id;
3570
3571 exception
3572 when no_data_found then
3573 Fnd_XdfDictionary_Pkg.InsertView(
3574 appl_id,
3575 x_view_name,
3576 x_text,
3577 x_description,
3578 f_ludate,
3579 f_luby,
3580 f_ludate,
3581 f_luby,
3582 0);
3583 end;
3584
3585 else -- phase_mode = 'END'
3586
3587 select VIEW_ID
3588 into vw_id
3589 from fnd_views
3590 where application_id = appl_id
3591 and VIEW_NAME = x_view_name;
3592
3593 -- Bug2631776 get the latest value for the last update for the db entity
3594 -- and the file entity.
3595
3596 select max(last_update_date)
3597 into child_db_ludate
3598 from fnd_view_columns
3599 where application_id = appl_id
3600 and VIEW_ID = vw_id
3601 and column_sequence < 0;
3602
3603 select max(last_update_date)
3604 into child_file_ludate
3605 from fnd_view_columns
3606 where application_id = appl_id
3607 and VIEW_ID = vw_id
3608 and column_sequence > 0;
3609
3610 -- If no value which means there were no existing child records
3611 -- in the database therefore skip to the end since the new child
3612 -- records have been updated.
3613
3614 if (child_db_ludate IS NULL) or (child_file_ludate IS NULL) then
3615 GOTO done_label;
3616 end if;
3617
3618 -- Continue on to check the owner value since both have columns.
3619
3620 -- Bug2631776 get the maximum value for the userid that made the
3621 -- last update for the db entity and the file entity.
3622
3623 -- If any non-seed owners, set owner to user
3624 select max(-1)
3625 into child_db_luby
3626 from fnd_view_columns
3627 where application_id = appl_id
3628 and VIEW_ID = vw_id
3629 and column_sequence < 0
3630 and last_updated_by not in (0,1,2);
3631
3632 if child_db_luby IS NULL then
3633 child_db_luby := 2; -- All rows are seed data, set seed data owner
3634 end if;
3635
3636 -- If any non-seed owners, set owner to user
3637 select max(-1)
3638 into child_file_luby
3639 from fnd_view_columns
3640 where application_id = appl_id
3641 and VIEW_ID = vw_id
3642 and column_sequence > 0
3643 and last_updated_by not in (0,1,2);
3644
3645 if child_file_luby IS NULL then
3649 -- Bug2631776 perform check to see if update occurs or not.
3646 child_file_luby := 2; -- All rows are seed data, set seed data owner
3647 end if;
3648
3650
3651 if (upload_test(child_file_luby, child_file_ludate, child_db_luby, child_db_ludate, x_custom_mode)) then
3652
3653 -- The new child entity rows from the data file are kept so
3654 -- delete the existing db child entity rows.
3655
3656 delete from fnd_view_columns
3657 where application_id = appl_id
3658 and VIEW_ID = vw_id
3659 and column_sequence < 0;
3660
3661 else
3662
3663 -- The existing db child entity rows are kept so delete the new child
3664 -- entity rows from the data file
3665
3666 delete from fnd_view_columns
3667 where application_id = appl_id
3668 and VIEW_ID = vw_id
3669 and column_sequence > 0;
3670
3671 -- Rename the existing db entity rows back to normal since
3672 -- it was not replaced by the new child entity rows
3673 -- from the data file.
3674
3675 update FND_VIEW_COLUMNS
3676 set COLUMN_SEQUENCE = -1 * COLUMN_SEQUENCE,
3677 COLUMN_NAME = decode(instr(COLUMN_NAME, '#'),1,ltrim(COLUMN_NAME, '#'),
3678 replace(COLUMN_NAME, '#','_'))
3679 where APPLICATION_ID = appl_id
3680 and VIEW_ID = vw_id;
3681
3682 end if;
3683 <<done_label>>
3684
3685 -- check if the file has no child entries to clean up database.
3686
3687 if (child_file_ludate IS NULL) then
3688
3689 if (child_db_ludate IS NOT NULL) then
3690
3691 -- Rename the existing db entity rows back to normal since
3692 -- it was not replaced by the new child entity rows
3693 -- from the data file.
3694
3695 update FND_VIEW_COLUMNS
3696 set COLUMN_SEQUENCE = -1 * COLUMN_SEQUENCE,
3697 COLUMN_NAME = decode(instr(COLUMN_NAME, '#'),1,ltrim(COLUMN_NAME, '#'),
3698 replace(COLUMN_NAME, '#','_'))
3699 where APPLICATION_ID = appl_id
3700 and VIEW_ID = vw_id;
3701 end if;
3702 end if;
3703 end if;
3704 end UploadView;
3705
3706 --
3707 -- UploadViewColumn (PUBLIC))
3708 -- Public procedure for afdict.lct to call when uploading view column
3709 -- using afdict.lct.
3710 --
3711 procedure UploadViewColumn (
3712 x_application_short_name in varchar2,
3713 x_view_name in varchar2,
3714 x_view_column_name in varchar2,
3715 x_view_column_sequence in varchar2,
3716 x_user_id in varchar2,
3717 x_custom_mode in varchar2,
3718 x_last_update_date in varchar2
3719 ) is
3720 appl_id number;
3721 vw_id number;
3722 f_luby number; -- entity owner in file
3723 f_ludate date; -- entity update date in file
3724 begin
3725
3726 -- No need to validate/check Application and View.
3727 -- Within the same entity.
3728 select A.APPLICATION_ID
3729 into appl_id
3730 from FND_APPLICATION A
3731 where A.APPLICATION_SHORT_NAME = x_application_short_name;
3732
3733 select V.VIEW_ID into vw_id from FND_VIEWS V
3734 where V.APPLICATION_ID = appl_id
3735 and V.VIEW_NAME = x_view_name;
3736
3737 -- Translate owner to file_last_updated_by
3738 f_luby := owner_id(x_user_id);
3739
3740 -- Translate char last_update_date to date
3741 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
3742
3743 -- NOTE: no "UPDATE" case as we have renamed all the existing
3744 -- view columns so that they can be compared against the new
3745 -- view columns from the data file to determine which will be
3746 -- updated into the database based on the date and custom factirs.
3747
3748 begin
3749 insert into FND_VIEW_COLUMNS(
3750 APPLICATION_ID,
3751 VIEW_ID,
3752 COLUMN_SEQUENCE,
3753 COLUMN_NAME,
3754 LAST_UPDATED_BY,
3755 LAST_UPDATE_DATE,
3756 LAST_UPDATE_LOGIN,
3757 CREATION_DATE,
3758 CREATED_BY)
3759 values (
3760 appl_id,
3761 vw_id,
3762 x_view_column_sequence,
3763 x_view_column_name,
3764 f_luby,
3765 f_ludate,
3766 f_luby,
3767 f_ludate,
3768 f_luby);
3769 exception
3770 when dup_val_on_index then
3771 RAISE_APPLICATION_ERROR(
3772 -20001, ' Duplicate value on index column sequence - '
3773 || x_view_column_sequence || ' Object Name '
3774 || x_view_name, TRUE);
3775 end;
3776 end UploadViewColumn;
3777
3778 --
3779 -- OWNER_ID
3780 -- Return the user_id of the OWNER attribute
3781 -- IN
3782 -- p_name - OWNER attribute value from FNDLOAD data file
3783 -- RETURNS
3784 -- user_id of owner to use in who columns
3785 --
3786
3787 function OWNER_ID(
3788 p_name in varchar2)
3789 return number is
3790 l_user_id number;
3791 begin
3792 if (p_name in ('SEED','CUSTOM')) then
3793 -- Old loader seed data
3794 return 1;
3795 elsif (p_name = 'ORACLE') then
3796 -- New loader seed data
3797 return 2;
3798 elsif (p_name = 'ORACLE12.0.0') then
3799 -- R12 seed data
3800 return 120;
3801 else
3802 begin
3803 -- User customized data
3804 select user_id
3805 into l_user_id
3806 from fnd_user
3807 where p_name = user_name;
3808 return l_user_id;
3809 exception
3810 when no_data_found then
3811 return -1;
3812 end;
3813 end if;
3817 function UPLOAD_TEST(
3814 end OWNER_ID;
3815
3816
3818 p_file_id in number,
3819 p_file_lud in date,
3820 p_db_id in number,
3821 p_db_lud in date,
3822 p_custom_mode in varchar2)
3823 return boolean is
3824 l_db_id number;
3825 l_file_id number;
3826 l_original_seed_data_window date;
3827 retcode boolean;
3828 begin
3829 -- CUSTOM_MODE=FORCE trumps all.
3830 if (p_custom_mode = 'FORCE') then
3831 retcode := TRUE;
3832 return retcode;
3833 end if;
3834
3835 -- Handle cases where data was previously up/downloaded with
3836 -- 'SEED'/1 owner instead of 'ORACLE'/2, but DOES have a version
3840 -- the file.
3837 -- date. These rows can be distinguished by the lud timestamp;
3838 -- Rows without versions were uploaded with sysdate, rows with
3839 -- versions were uploaded with a date (with time truncated) from
3841
3842 -- Check file row for SEED/version
3843 l_file_id := p_file_id;
3844 if ((l_file_id in (0,1)) and (p_file_lud = trunc(p_file_lud)) and
3845 (p_file_lud < sysdate - .1)) then
3846 l_file_id := 2;
3847 end if;
3848
3849 -- Check db row for SEED/version.
3850 -- NOTE: if db ludate < seed_data_window, then consider this to be
3851 -- original seed data, never touched by FNDLOAD, even if it doesn't
3852 -- have a timestamp.
3853 l_db_id := p_db_id;
3854 l_original_seed_data_window := to_date('01/01/1990','MM/DD/YYYY');
3855 if ((l_db_id in (0,1)) and (p_db_lud = trunc(p_db_lud)) and
3856 (p_db_lud > l_original_seed_data_window)) then
3857 l_db_id := 2;
3858 end if;
3859
3860 if (l_file_id in (0,1)) then
3861 -- File owner is old FNDLOAD.
3862 if (l_db_id in (0,1)) then
3863 -- DB owner is also old FNDLOAD.
3864 -- Over-write, but only if file ludate >= db ludate.
3865 if (p_file_lud >= p_db_lud) then
3866 retcode := TRUE;
3867 else
3868 retcode := FALSE;
3869 end if;
3870 else
3871 retcode := FALSE;
3872 end if;
3873 elsif (l_file_id = 2) then
3874 -- File owner is new FNDLOAD. Over-write if:
3875 -- 1. Db owner is old FNDLOAD, or
3876 -- 2. Db owner is new FNDLOAD, and file date >= db date
3877 if ((l_db_id in (0,1)) or
3878 ((l_db_id = 2) and (p_file_lud >= p_db_lud))) then
3879 retcode := TRUE;
3880 else
3881 retcode := FALSE;
3882 end if;
3883 elsif (l_file_id = 120) then
3884 -- File owner is R12 seed data, Over-write if:
3885 -- 1. Db owner is (0, 1, 2), or
3886 -- 2. Db owner is 120, and file date >= db date
3887 if ((l_db_id in (0,1,2)) or
3888 ((l_db_id = 120) and (p_file_lud >= p_db_lud))) then
3889 retcode := TRUE;
3890 else
3891 retcode := FALSE;
3892 end if;
3893 else
3894 -- File owner is USER. Over-write if:
3895 -- 1. Db owner is old or new FNDLOAD, or
3896 -- 2. File date >= db date
3897 if ((l_db_id in (0,1,2,120)) or
3898 (trunc(p_file_lud) >= trunc(p_db_lud))) then
3899 retcode := TRUE;
3900 else
3901 retcode := FALSE;
3902 end if;
3903 end if;
3904
3905 /*
3906 if (retcode = FALSE) then
3907 fnd_message.set_name('FND', 'FNDLOAD_CUSTOMIZED');
3908 end if;
3909 */
3910 return retcode;
3911 end UPLOAD_TEST;
3912
3913 /* To load fnd_object_Tablespace */
3914 Procedure LOAD_ROW (
3915 P_APPLICATION_ID in NUMBER,
3916 P_OBJECT_NAME in VARCHAR2,
3917 P_OBJECT_TYPE in VARCHAR2,
3918 P_TABLESPACE_TYPE in VARCHAR2,
3919 P_CUSTOM_TABLESPACE_TYPE in VARCHAR2,
3920 P_OBJECT_SOURCE in VARCHAR2,
3921 P_ORACLE_USERNAME in VARCHAR2,
3922 P_CUSTOM_FLAG in VARCHAR2,
3923 P_LAST_UPDATED_BY in VARCHAR2,
3924 P_CUSTOM_MODE in VARCHAR2,
3925 P_LAST_UPDATE_DATE in VARCHAR2,
3926 P_NZDT_MODE in VARCHAR2 default 'N'
3927 ) is
3928 man_id number;
3929 row_id varchar2(64);
3930 f_luby number; -- entity owner in file
3931 f_ludate date; -- entity update date in file
3932 db_luby number; -- entity owner in db
3933 db_ludate date; -- entity update date in db
3934 begin
3935 if (P_NZDT_MODE ='Y')
3936 then
3937 begin
3938 AD_ZD_SEED.PREPARE ('FND_OBJECT_TABLESPACES');
3939 end;
3940 end if;
3941 -- Translate owner to file_last_updated_by
3942 f_luby := owner_id(P_LAST_UPDATED_BY);
3943
3944 -- Translate char last_update_date to date
3945 f_ludate := nvl(to_date(P_last_update_date, 'YYYY/MM/DD'), sysdate);
3946
3947 select last_updated_by, last_update_date
3948 into db_luby, db_ludate
3949 from FND_OBJECT_TABLESPACES
3950 where object_name = P_OBJECT_NAME
3951 and application_id =P_application_id
3955
3952 and object_type =P_object_type;
3953
3954 if (upload_test(f_luby, f_ludate, db_luby,db_ludate, P_CUSTOM_MODE)) then
3956 Fnd_XdfDictionary_pkg.UPDATE_ROW (
3957 P_APPLICATION_ID => P_application_id,
3958 P_OBJECT_NAME => P_OBJECT_NAME,
3959 P_OBJECT_TYPE => P_OBJECT_TYPE,
3960 P_TABLESPACE_TYPE => P_TABLESPACE_TYPE,
3961 P_CUSTOM_TABLESPACE_TYPE => P_CUSTOM_TABLESPACE_TYPE,
3962 P_OBJECT_SOURCE => P_OBJECT_SOURCE ,
3963 P_ORACLE_USERNAME => P_ORACLE_USERNAME ,
3964 P_CUSTOM_FLAG => P_CUSTOM_FLAG,
3965 P_LAST_UPDATE_DATE => f_ludate,
3966 P_LAST_UPDATED_BY => f_luby,
3967 P_LAST_UPDATE_LOGIN => 0 );
3968 end if;
3969
3970 exception
3971 when NO_DATA_FOUND then
3972
3973 -- select fnd_menus_s.nextval into man_id from dual;
3974 --select FND_OBJECT_TABLESPACES_S.nextval into man_id from duaL;
3975
3976 Fnd_XdfDictionary_pkg.INSERT_ROW(
3977 X_ROWID => ROW_ID,
3978 P_APPLICATION_ID => P_application_id,
3979 P_OBJECT_NAME => P_OBJECT_NAME,
3980 P_OBJECT_TYPE => P_OBJECT_TYPE,
3981 P_TABLESPACE_TYPE => P_TABLESPACE_TYPE,
3982 P_CUSTOM_TABLESPACE_TYPE => P_CUSTOM_TABLESPACE_TYPE,
3983 P_OBJECT_SOURCE => P_OBJECT_SOURCE ,
3984 P_ORACLE_USERNAME => P_ORACLE_USERNAME ,
3985 P_CUSTOM_FLAG => P_CUSTOM_FLAG,
3986 P_CREATION_DATE => f_ludate,
3987 P_CREATED_BY => f_luby,
3988 P_LAST_UPDATE_DATE => f_ludate,
3989 P_LAST_UPDATED_BY => f_luby,
3990 P_LAST_UPDATE_LOGIN => 0 );
3991
3992 end LOAD_ROW;
3993
3994 procedure INSERT_ROW (
3995 X_ROWID IN OUT NOCOPY VARCHAR2 ,
3996 P_APPLICATION_ID IN NUMBER,
3997 P_OBJECT_NAME IN VARCHAR2,
3998 P_OBJECT_TYPE IN VARCHAR2,
3999 P_TABLESPACE_TYPE IN VARCHAR2,
4000 P_CUSTOM_TABLESPACE_TYPE IN VARCHAR2,
4001 P_OBJECT_SOURCE IN VARCHAR2,
4002 P_ORACLE_USERNAME IN VARCHAR2,
4003 P_CUSTOM_FLAG IN VARCHAR2,
4004 P_CREATION_DATE IN DATE,
4005 P_CREATED_BY IN NUMBER,
4006 P_LAST_UPDATE_DATE IN DATE,
4007 P_LAST_UPDATED_BY IN NUMBER,
4008 P_LAST_UPDATE_LOGIN IN NUMBER
4009 ) is
4010 cursor C is select ROWID from FND_OBJECT_TABLESPACES
4011 where APPLICATION_ID = P_APPLICATION_ID
4012 and object_name =P_object_name
4013 and object_type=P_object_type ;
4014
4015 begin
4016 insert into FND_OBJECT_TABLESPACES (
4017 APPLICATION_ID,
4018 OBJECT_NAME,
4019 OBJECT_TYPE,
4020 TABLESPACE_TYPE,
4021 CUSTOM_TABLESPACE_TYPE,
4022 OBJECT_SOURCE ,
4023 ORACLE_USERNAME,
4027 LAST_UPDATE_DATE,
4024 CUSTOM_FLAG ,
4025 CREATION_DATE,
4026 CREATED_BY,
4028 LAST_UPDATED_BY,
4029 LAST_UPDATE_LOGIN
4030 ) values (
4031 P_APPLICATION_ID,
4032 P_OBJECT_NAME,
4033 P_OBJECT_TYPE,
4034 P_TABLESPACE_TYPE,
4035 P_CUSTOM_TABLESPACE_TYPE,
4036 P_OBJECT_SOURCE ,
4037 P_ORACLE_USERNAME,
4038 P_CUSTOM_FLAG ,
4039 P_CREATION_DATE,
4040 P_CREATED_BY,
4041 P_LAST_UPDATE_DATE,
4042 P_LAST_UPDATED_BY,
4043 P_LAST_UPDATE_LOGIN
4044 );
4045
4046
4047 end INSERT_ROW;
4048
4049 procedure LOCK_ROW (
4050 P_APPLICATION_ID in NUMBER,
4051 P_OBJECT_NAME in VARCHAR2,
4052 P_OBJECT_TYPE in VARCHAR2,
4053 P_TABLESPACE_TYPE in VARCHAR2) is
4054 cursor c is select OBJECT_NAME, OBJECT_TYPE
4055 from FND_OBJECT_TABLESPACES
4056 where APPLICATION_ID = P_APPLICATION_ID
4057 for update of APPLICATION_ID nowait;
4058 recinfo c%rowtype;
4059 begin
4060 open c;
4061 fetch c into recinfo;
4062 if (c%notfound) then
4063 close c;
4067 close c;
4064 RAISE_APPLICATION_ERROR(
4065 -20001, 'Fnd Form record deleted ' , TRUE);
4066 end if;
4068 if ( (recinfo.OBJECT_NAME = P_OBJECT_NAME)
4069 AND ((recinfo.OBJECT_TYPE = P_OBJECT_TYPE)
4070 OR ((recinfo.OBJECT_TYPE is null) AND (P_OBJECT_TYPE is null)))) then
4071 null;
4072 else
4073 RAISE_APPLICATION_ERROR(
4074 -20001, 'Form record change ' , TRUE);
4075 end if;
4076
4077 return;
4078 end LOCK_ROW;
4079
4080 procedure UPDATE_ROW (
4081 P_APPLICATION_ID in NUMBER,
4082 P_OBJECT_NAME in VARCHAR2,
4083 P_OBJECT_TYPE in VARCHAR2,
4084 P_TABLESPACE_TYPE in VARCHAR2,
4085 P_CUSTOM_TABLESPACE_TYPE in VARCHAR2,
4086 P_OBJECT_SOURCE in VARCHAR2,
4087 P_ORACLE_USERNAME in VARCHAR2,
4088 P_CUSTOM_FLAG in VARCHAR2,
4089 P_LAST_UPDATE_DATE in DATE,
4090 P_LAST_UPDATED_BY in NUMBER,
4091 P_LAST_UPDATE_LOGIN in NUMBER
4092 ) is
4093 begin
4094 update FND_OBJECT_TABLESPACES set
4095 OBJECT_NAME = P_OBJECT_NAME,
4096 OBJECT_TYPE = P_OBJECT_TYPE,
4097 TABLESPACE_TYPE = P_TABLESPACE_TYPE,
4098 CUSTOM_TABLESPACE_TYPE = P_CUSTOM_TABLESPACE_TYPE,
4099 OBJECT_SOURCE= P_OBJECT_SOURCE,
4100 ORACLE_USERNAME=P_ORACLE_USERNAME,
4101 CUSTOM_FLAG = P_CUSTOM_FLAG,
4102 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
4103 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
4104 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
4105 where APPLICATION_ID = P_APPLICATION_ID
4106 and object_name =P_object_name;
4107
4108 if (sql%notfound) then
4109 raise no_data_found;
4110 end if;
4111
4112
4113 end UPDATE_ROW;
4114
4115 end Fnd_XdfDictionary_Pkg;
[Home] [Help]