[Home] [Help]
PACKAGE BODY: APPS.AD_DD
Source
1 package body ad_dd as
2 /* $Header: adddb.pls 115.9 2004/06/02 08:09:51 sallamse ship $ */
3 --
4 -- PRIVATE FUNCTION
5 --
6 procedure is_valid_appl_short_name
7 (p_apps_short_name varchar2,
8 p_apps_id in out nocopy number) is
9 cnt integer;
10 begin
11 select count(*), application_id into cnt, p_apps_id
12 from fnd_application
13 where application_short_name = upper(p_apps_short_name)
14 group by application_id;
15
16 if cnt <> 1 then
17 raise_application_error(-20000, 'Invalid application_short_name: "'||
18 p_apps_short_name||'"');
19 end if;
20
21 exception
22 when no_data_found then
23 raise_application_error(-20000, 'Invalid application_short_name: "'||
24 p_apps_short_name||'"');
25
26 end is_valid_appl_short_name;
27
28 function get_table_id(p_apps_id in number,
29 p_tab_name in varchar2) return number is
30 p_table_id number;
31 begin
32
33 select table_id into p_table_id
34 from fnd_tables
35 where application_id = p_apps_id
36 and table_name = p_tab_name;
37
38 return p_table_id;
39
40 exception
41 when no_data_found then
42 return null;
43
44 end get_table_id;
45
46 function get_column_id(p_appl_id in number,
47 p_table_id in number,
48 p_col_name in varchar2) return number is
49 l_col_id number;
50 begin
51
52 select column_id into l_col_id
53 from fnd_columns
54 where application_id = p_appl_id
55 and table_id = p_table_id
56 and column_name = p_col_name;
57
58 return l_col_id;
59
60 exception
61 when no_data_found then
62 return null;
63
64 end get_column_id;
65
66 function check_multiple_developer_keys(
67 p_appl_id in number,
68 p_table_id in number,
69 p_key_name in varchar2) return boolean
70 is
71 l_tmp number;
72 begin
73
74 select count(*)
75 into l_tmp
76 from fnd_primary_keys
77 where application_id = p_appl_id
78 and table_id = p_table_id
79 and primary_key_name <> upper(p_key_name)
80 and primary_key_type = 'D';
81
82 if (l_tmp > 0) then
83 return(TRUE);
84 else
85 return(FALSE);
86 end if;
87 end;
88
89 function get_primary_key_id(p_appl_id in number,
90 p_table_id in number,
91 p_key_name in varchar2) return number
92 is
93 l_key_id number;
94 begin
95
96 select primary_key_id
97 into l_key_id
98 from fnd_primary_keys
99 where application_id = p_appl_id
100 and table_id = p_table_id
101 and primary_key_name = upper(p_key_name);
102
103 return(l_key_id);
104
105 exception
106 when no_data_found then
107 return(null);
108 end;
109
110 procedure insert_update_primary_key
111 (p_mode in varchar2,
112 p_appl_short_name in varchar2,
113 p_key_name in varchar2,
114 p_tab_name in varchar2,
115 p_description in varchar2,
116 p_enabled_flag in varchar2,
117 p_key_type in varchar2,
118 p_audit_flag in varchar2)
119 is
120 l_table_id number := null;
121 l_appl_id number := null;
122 l_key_id number;
123 begin
124
125 --
126 -- check to see if the application_id is valid
127 --
128 is_valid_appl_short_name(p_appl_short_name, l_appl_id);
129
130 --
131 -- check to see if the table exists
132 --
133 l_table_id := get_table_id(l_appl_id, upper(p_tab_name));
134
135 if (l_table_id is null) then
136 raise_application_error(-20000, 'Cannot find the table_id for table: "'||
137 p_tab_name||'" with application_short_name "'||
138 p_appl_short_name||'"');
139 end if;
140
141 --
142 -- check if the key exists
143 --
144 l_key_id := get_primary_key_id(l_appl_id, l_table_id, p_key_name);
145
146 if (p_mode = 'INSERT' and l_key_id is not null) then
147 return;
148 elsif (p_mode = 'UPDATE' and l_key_id is null) then
149 raise_application_error(-20000,
150 'Cannot find the key_id for key: "'||p_key_name||'" for table "'||
151 p_tab_name||'" with application_short_name "'||
152 p_appl_short_name||'"');
153 end if;
154
155 --
156 -- check for multiple developer primary keys
157 --
158 if (p_key_type = 'D') then
159 if (check_multiple_developer_keys(l_appl_id, l_table_id, p_key_name)) then
160 raise_application_error(-20000, 'You cannot define more than one '||
161 'developer primary key on a table ('||p_tab_name||')');
162 end if;
163 end if;
164
165 --
166 -- perform other checks
167 --
168 if ((p_mode = 'INSERT' and p_key_type not in ('S', 'D')) or
169 (p_mode = 'UPDATE' and nvl(p_key_type, 'S') not in ('S', 'D'))) then
170 raise_application_error(-20000, 'Invalid value for primary key type : '||
171 p_key_type);
172 end if;
173
174 if ((p_mode = 'INSERT' and p_audit_flag not in ('Y', 'N')) or
175 (p_mode = 'UPDATE' and nvl(p_audit_flag, 'Y') not in ('Y', 'N'))) then
176 raise_application_error(-20000, 'Invalid value for audit flag : '||
177 p_audit_flag);
178 end if;
179
180 if ((p_enabled_flag = 'INSERT' and p_enabled_flag not in ('Y', 'N'))
181 or
182 (p_enabled_flag = 'UPDATE' and nvl(p_enabled_flag,'Y') not in ('Y','N')))
183 then
184 raise_application_error(-20000, 'Invalid value for enabled flag : '||
185 p_enabled_flag);
186 end if;
187
188
189 if (p_mode = 'INSERT' and l_key_id is null) then
190
191 insert into fnd_primary_keys (
192 APPLICATION_ID,
193 TABLE_ID,
194 PRIMARY_KEY_ID,
195 PRIMARY_KEY_NAME,
196 LAST_UPDATE_DATE, LAST_UPDATED_BY,
197 CREATION_DATE, CREATED_BY,
198 LAST_UPDATE_LOGIN,
199 PRIMARY_KEY_TYPE,
200 AUDIT_KEY_FLAG,
201 DESCRIPTION,
202 ENABLED_FLAG)
203 select l_appl_id,
204 l_table_id,
205 fnd_primary_keys_s.nextval,
206 p_key_name,
207 to_date('01/01/1990', 'DD/MM/YYYY'), 1,
208 to_date('01/01/1990', 'DD/MM/YYYY'), 1,
209 0,
210 p_key_type,
211 p_audit_flag,
212 p_description,
213 p_enabled_flag
214 from dual
215 where not exists (
216 select 'x'
217 from fnd_primary_keys
218 where application_id = l_appl_id
219 and table_id = l_table_id
220 and primary_key_name = upper(p_key_name));
221
222 elsif (p_mode = 'UPDATE' and l_key_id is not null) then
223
224 update fnd_primary_keys
225 set primary_key_type = nvl(p_key_type, primary_key_type),
226 audit_key_flag = nvl(p_audit_flag, audit_key_flag),
227 description = nvl(p_description, description),
228 enabled_flag = nvl(p_enabled_flag, enabled_flag)
229 where application_id = l_appl_id
230 and table_id = l_table_id
231 and primary_key_id = l_key_id;
232
233 end if;
234
235 end;
236
237
238 --
239 -- PUBLIC PROCEDURES/FUNCTIONS
240 --
241 procedure register_table
242 (p_appl_short_name in varchar2,
243 p_tab_name in varchar2,
244 p_tab_type in varchar2,
245 p_next_extent in number,
246 p_pct_free in number,
247 p_pct_used in number)
248 is
249 up_tab_name varchar2(40);
250 p_table_id number := null;
251 p_appl_id number := null;
252 begin
253 up_tab_name := upper(p_tab_name);
254 --
255 -- check to see if the application_id is valid
256 --
257 is_valid_appl_short_name(p_appl_short_name, p_appl_id);
258 --
259 -- check to see if the table already exists
260 --
261 p_table_id := get_table_id(p_appl_id, up_tab_name);
262
263 if p_table_id is null then
264 --
265 -- table does not exist yet; insert it
266 --
267 declare
268 new_auto_size char(1);
269 new_next_extent number;
270 new_tab_type char(1);
271 factor number;
272 begin
273 --
274 -- need to auto size?
275 --
276 new_tab_type := upper(p_tab_type);
277
278 if new_tab_type = 'S' then
279 new_auto_size := 'N';
280 elsif new_tab_type = 'T' then
281 new_auto_size := 'Y';
282 else
283 raise_application_error(-20000, 'Unknown table type: "'||p_tab_type||
284 '" for table "'||up_tab_name||'".');
285 end if;
286
287 --
288 -- calculate the next_extent size
289 --
290 new_next_extent := round(p_next_extent, 0);
291
292 insert into fnd_tables (
293 APPLICATION_ID ,
294 TABLE_ID ,
295 TABLE_NAME ,
296 USER_TABLE_NAME ,
297 LAST_UPDATE_DATE ,
298 LAST_UPDATED_BY ,
299 CREATION_DATE ,
300 CREATED_BY ,
301 LAST_UPDATE_LOGIN ,
302 AUTO_SIZE ,
303 TABLE_TYPE ,
304 INITIAL_EXTENT ,
305 NEXT_EXTENT ,
306 MIN_EXTENTS ,
307 MAX_EXTENTS ,
308 PCT_INCREASE ,
309 INI_TRANS ,
310 MAX_TRANS ,
311 PCT_FREE ,
312 PCT_USED )
313 select p_appl_id,
314 fnd_tables_s.nextval,
315 up_tab_name,
316 up_tab_name,
317 to_date('01-01-1990', 'DD-MM-YYYY') ,
318 1,
319 to_date('01-01-1990', 'DD-MM-YYYY') ,
320 1,
321 0,
322 new_auto_size,
323 new_tab_type,
324 4,
325 new_next_extent,
326 1,
327 50,
328 0,
329 1,
330 255,
331 p_pct_free,
332 p_pct_used
333 from sys.dual
334 where not exists ( select 'x'
335 from fnd_tables
336 where application_id = p_appl_id
337 and table_name = up_tab_name);
338
339 end;
340 end if;
341 end register_table;
342
343
344 procedure register_column
345 (p_appl_short_name in varchar2,
346 p_tab_name in varchar2,
347 p_col_name in varchar2,
348 p_col_seq in number,
349 p_col_type in varchar2,
350 p_col_width in number,
351 p_nullable in varchar2,
352 p_translate in varchar2,
353 p_precision in number default null,
354 p_scale in number default null)
355 is
356 new_col_type char(1);
357 up_col_type varchar2(40);
358 up_tab_name varchar2(40);
359 p_table_id number := null;
360 p_appl_id number := null;
361 begin
362 up_col_type := upper(p_col_type);
363 up_tab_name := upper(p_tab_name);
364 --
365 -- check to see if the application_id is valid
366 --
367 is_valid_appl_short_name(p_appl_short_name, p_appl_id);
368 --
369 -- get table_id
370 --
371 p_table_id := get_table_id(p_appl_id, up_tab_name);
372
373 if p_table_id is null then
374
375 raise_application_error(-20000, 'Table '||up_tab_name||
376 ' does not exist in FND_TABLES for application_short_name "'||
377 p_appl_short_name||'" application_id "'||p_appl_id||'".');
378
379 end if;
380 --
381 -- check input column type value
382 --
383 if up_col_type = 'NCLOB' then
384 new_col_type := 'A';
385 elsif up_col_type = 'BLOB' then
386 new_col_type := 'B';
387 elsif up_col_type = 'DATE' then
388 new_col_type := 'D';
389 elsif up_col_type = 'CLOB' then
390 new_col_type := 'E';
391 elsif up_col_type = 'BFILE' then
392 new_col_type := 'F';
393 elsif up_col_type = 'ROWID' then
394 new_col_type := 'I';
395 elsif up_col_type = 'LONG' then
396 new_col_type := 'L';
397 elsif up_col_type = 'MLSLABEL' then
398 new_col_type := 'M';
399 elsif up_col_type = 'NUMBER' then
400 new_col_type := 'N';
401 elsif up_col_type = 'RAW' then
402 new_col_type := 'R';
403 elsif up_col_type = 'CHAR' then
404 new_col_type := 'V';
405 elsif up_col_type = 'VARCHAR2' then
406 new_col_type := 'V';
407 elsif up_col_type = 'NCHAR' then
408 new_col_type := 'W';
409 elsif up_col_type = 'NVARCHAR2' then
410 new_col_type := 'W';
411 elsif up_col_type = 'LONG RAW' then
412 new_col_type := 'X';
413 elsif up_col_type = 'VARCHAR' then
414 new_col_type := 'Y';
415 elsif up_col_type = 'RAW MLSLABEL' then
416 new_col_type := 'Z';
417 else
418
419 raise_application_error(-20000, 'Unknown column type: "'||up_col_type||
420 '" provided for Application_short_name "'||p_appl_short_name||
421 '" application_id "'||p_appl_id||'" Table_name='||up_tab_name||
422 ' Column_name='||p_col_name);
423
424 end if;
425 --
426 -- insert into FND_COLUMNS
427 --
428 insert into fnd_columns
429 (APPLICATION_ID,
430 TABLE_ID,
431 COLUMN_ID,
432 COLUMN_NAME,
433 USER_COLUMN_NAME,
434 COLUMN_SEQUENCE,
435 LAST_UPDATE_DATE,
436 LAST_UPDATED_BY,
437 CREATION_DATE,
438 CREATED_BY,
439 LAST_UPDATE_LOGIN,
440 COLUMN_TYPE,
441 WIDTH,
442 NULL_ALLOWED_FLAG,
443 TRANSLATE_FLAG,
444 FLEXFIELD_USAGE_CODE,
445 PRECISION,
446 SCALE)
447 select p_appl_id,
448 p_table_id,
449 fnd_columns_s.nextval,
450 upper(p_col_name),
451 upper(p_col_name),
452 p_col_seq,
453 to_date('01-01-1990', 'DD-MM-YYYY'),
454 1,
455 to_date('01-01-1990', 'DD-MM-YYYY'),
456 1,
457 0,
458 new_col_type,
459 p_col_width,
460 upper(p_nullable),
461 upper(p_translate),
462 'N',
463 p_precision,
464 p_scale
465 from sys.dual
466 where not exists (select 'x' from fnd_columns
467 where application_id = p_appl_id
468 and table_id = p_table_id
469 and column_name = upper(p_col_name));
470
471 end register_column;
472
473 procedure delete_table
474 (p_appl_short_name in varchar2,
475 p_tab_name in varchar2)
476 is
477 up_tab_name varchar2(40);
478 p_table_id number := null;
479 p_appl_id number := null;
480 begin
481 up_tab_name := upper(p_tab_name);
482 --
483 -- check to see if the application_id is valid
484 --
485 is_valid_appl_short_name(p_appl_short_name, p_appl_id);
486
487 --
488 -- check to see if the table exists
489 --
490 p_table_id := get_table_id(p_appl_id, up_tab_name);
491
492 if p_table_id is null then
493 --
494 -- either the table has been deleted or does not exist
495 --
496 return;
497 end if;
498
499 --
500 -- delete all columns
501 --
502 delete from fnd_columns
503 where application_id = p_appl_id and table_id = p_table_id;
504
505 delete from fnd_tables
506 where application_id = p_appl_id and table_id = p_table_id;
507
508 end delete_table;
509
510 procedure delete_column
511 (p_appl_short_name in varchar2,
512 p_tab_name in varchar2,
513 p_col_name in varchar2)
514 is
515 p_table_id number := null;
516 p_appl_id number := null;
517 begin
518 --
519 -- check to see if the application_id is valid
520 --
521 is_valid_appl_short_name(p_appl_short_name, p_appl_id);
522
523 --
524 -- check to see if the table exists
525 --
526 p_table_id := get_table_id(p_appl_id, upper(p_tab_name));
527
528 if p_table_id is null then
529 --
530 -- either the table has been deleted or does not exist
531 --
532 raise_application_error(-20000, 'Cannot find the table_id for table: "'||
533 p_tab_name||'" with application_short_name "'||p_appl_short_name||
534 '", application_id "'||p_appl_id||'" for column "'||
535 p_col_name||'".');
536
537 end if;
538
539 --
540 -- delete the given column
541 --
542 delete from fnd_columns
543 where application_id = p_appl_id
544 and table_id = p_table_id
545 and column_name = upper(p_col_name);
546
547 end delete_column;
548
549
550 procedure register_primary_key
551 (p_appl_short_name in varchar2,
552 p_key_name in varchar2,
553 p_tab_name in varchar2,
554 p_description in varchar2,
555 p_key_type in varchar2,
556 p_audit_flag in varchar2,
557 p_enabled_flag in varchar2)
558 is
559 begin
560 insert_update_primary_key('INSERT',
561 p_appl_short_name,
562 upper(p_key_name),
563 upper(p_tab_name),
564 p_description,
565 upper(p_enabled_flag),
566 upper(p_key_type),
567 upper(p_audit_flag));
568 end;
569
570 procedure update_primary_key
571 (p_appl_short_name in varchar2,
572 p_key_name in varchar2,
573 p_tab_name in varchar2,
574 p_description in varchar2 default null,
575 p_key_type in varchar2 default null,
576 p_audit_flag in varchar2 default null,
577 p_enabled_flag in varchar2 default null)
578 is
579 begin
580 insert_update_primary_key('UPDATE',
581 p_appl_short_name,
582 upper(p_key_name),
583 upper(p_tab_name),
584 p_description,
585 upper(p_enabled_flag),
586 upper(p_key_type),
587 upper(p_audit_flag));
588 end;
589
590 procedure register_primary_key_column
591 (p_appl_short_name in varchar2,
592 p_key_name in varchar2,
593 p_tab_name in varchar2,
594 p_col_name in varchar2,
595 p_col_sequence in number)
596 is
597 l_table_id number := null;
598 l_appl_id number := null;
599 l_col_id number := null;
600 l_key_id number;
601 begin
602
603 --
604 -- check to see if the application_id is valid
605 --
606 is_valid_appl_short_name(p_appl_short_name, l_appl_id);
607
608 --
609 -- check to see if the table exists
610 --
611 l_table_id := get_table_id(l_appl_id, upper(p_tab_name));
612 l_col_id := get_column_id(l_appl_id, l_table_id, upper(p_col_name));
613
614 l_key_id := get_primary_key_id(l_appl_id, l_table_id, p_key_name);
615
616 if (l_table_id is null) then
617 raise_application_error(-20000,
618 'Cannot find the table_id for table: "'||p_tab_name||
619 '" with application_short_name "'||p_appl_short_name||'"');
623 raise_application_error(-20000,
620 end if;
621
622 if (l_col_id is null) then
624 'Cannot find the column_id for column: "'||
625 p_tab_name||'.'||p_col_name||'" with application_short_name "'||
626 p_appl_short_name||'"');
627 end if;
628
629 if (l_key_id is null) then
630 raise_application_error(-20000,
631 'Cannot find the primary_key_id for the key : "'||
632 p_key_name||'" on table "'||p_tab_name||
633 '" with application_short_name "'|| p_appl_short_name||'"');
634 end if;
635
636
637 insert into fnd_primary_key_columns(
638 APPLICATION_ID,
639 TABLE_ID,
640 PRIMARY_KEY_ID,
641 PRIMARY_KEY_SEQUENCE,
642 COLUMN_ID,
643 LAST_UPDATE_DATE, LAST_UPDATED_BY,
644 CREATION_DATE, CREATED_BY,
645 LAST_UPDATE_LOGIN)
646 select l_appl_id,
647 l_table_id,
648 l_key_id,
649 p_col_sequence,
650 l_col_id,
651 to_date('01/01/1990', 'DD/MM/YYYY'), 1,
652 to_date('01/01/1990', 'DD/MM/YYYY'), 1,
653 0
654 from dual
655 where not exists (
656 select 'x'
657 from fnd_primary_key_columns
658 where application_id = l_appl_id
659 and table_id = l_table_id
660 and primary_key_id = l_key_id
661 and column_id = l_col_id);
662 end;
663
664
665 procedure delete_primary_key_column
666 (p_appl_short_name in varchar2,
667 p_key_name in varchar2,
668 p_tab_name in varchar2,
669 p_col_name in varchar2 default null)
670 is
671 l_table_id number := null;
672 l_appl_id number := null;
673 l_col_id number := null;
674 l_key_id number;
675 begin
676 --
677 -- check to see if the application_id is valid
678 --
679 is_valid_appl_short_name(p_appl_short_name, l_appl_id);
680
681 --
682 -- check to see if the table exists
683 --
684 l_table_id := get_table_id(l_appl_id, upper(p_tab_name));
685
686 if (p_col_name is not null) then
687 l_col_id := get_column_id(l_appl_id, l_table_id, upper(p_col_name));
688 end if;
689
690 l_key_id := get_primary_key_id(l_appl_id, l_table_id, p_key_name);
691
692 if (l_table_id is null) then
693 raise_application_error(-20000,
694 'Cannot find the table_id for table: "'||p_tab_name||
695 '" with application_short_name "'||p_appl_short_name||'"');
696 end if;
697
698 if (l_key_id is null) then
699 raise_application_error(-20000,
700 'Cannot find the key_id for key: "'||p_key_name||'" for table "'||
701 p_tab_name||'" with application_short_name "'||
702 p_appl_short_name||'"');
703 end if;
704
705 delete from fnd_primary_key_columns
706 where application_id = l_appl_id
707 and table_id = l_table_id
708 and primary_key_id = l_key_id
709 and column_id = decode(p_col_name, null, column_id, l_col_id);
710
711 end;
712
713
714 end ad_dd;