1 package Fnd_Dictionary_Pkg as
2 /* $Header: AFDICTS.pls 120.2 2005/07/11 08:37:07 stadepal ship $ */
3
4
5 type NameArrayTyp is table of varchar2(100) index by binary_integer;
6 type NumberArrayTyp is table of number index by binary_integer;
7 type Text2000ArrayTyp is table of varchar2(2000) index by binary_integer;
8
9 --
10 -- UploadTable (PUBLIC))
11 -- Public procedure for afdict.lct to call when uploading tables using
12 -- using afdict.lct. It calls InsertTable() when needed.
13 --
14 procedure UploadTable (
15 x_application_short_name in varchar2,
16 x_table_name in varchar2,
17 x_user_table_name in varchar2,
18 x_table_type in varchar2,
19 x_description in varchar2,
20 x_auto_size in varchar2,
21 x_initial_extent in varchar2,
22 x_next_extent in varchar2,
23 x_min_extents in varchar2,
24 x_max_extents in varchar2,
25 x_ini_trans in varchar2,
26 x_max_trans in varchar2,
27 x_pct_free in varchar2,
28 x_pct_increase in varchar2,
29 x_pct_used in varchar2,
30 x_hosted_support_style in varchar2,
31 x_user_id in varchar2);
32
33
34 --
35 -- UploadColumn (PUBLIC))
36 -- Public procedure for afdict.lct to call when uploading columns using
37 -- using afdict.lct. It calls InsertColumn() when needed.
38 --
39
40 procedure UploadColumn (
41 x_application_short_name in varchar2,
42 x_table_name in varchar2,
43 x_column_name in varchar2,
44 x_user_column_name in varchar2,
45 x_column_sequence in varchar2,
46 x_column_type in varchar2,
47 x_width in varchar2,
48 x_null_allowed_flag in varchar2,
49 x_description in varchar2,
50 x_default_value in varchar2,
51 x_translate_flag in varchar2,
52 x_precision in varchar2,
53 x_scale in varchar2,
54 x_flexfield_usage_code in varchar2,
55 x_flexfield_application_id in varchar2,
56 x_flexfield_name in varchar2,
57 x_flex_value_set_app_id in varchar2,
58 x_flex_value_set_id in varchar2,
59 x_user_id in varchar2);
60
61
62 --
63 -- UploadHistColumn (PUBLIC))
64 -- Public procedure for afdict.lct to call when uploading hist columns using
65 -- using afdict.lct. It calls InsertHistColumn() when needed.
66 --
67
68 procedure UploadHistColumn (
69 x_application_short_name in varchar2,
70 x_table_name in varchar2,
71 x_column_name in varchar2,
72 x_partition in varchar2,
73 x_hsize in varchar2,
74 x_user_id in varchar2);
75
76 --
77 -- UploadIndex (PUBLIC))
78 -- Public procedure for afdict.lct to call when uploading indexes using
79 -- using afdict.lct. It calls InsertIndex() when needed.
80 --
81 procedure UploadIndex (
82 x_application_short_name in varchar2,
83 x_table_name in varchar2,
84 x_index_name in varchar2,
85 x_uniqueness in varchar2,
86 x_auto_size in varchar2,
87 x_description in varchar2,
88 x_initial_extent in varchar2,
89 x_next_extent in varchar2,
90 x_min_extents in varchar2,
91 x_max_extents in varchar2,
92 x_ini_trans in varchar2,
93 x_max_trans in varchar2,
94 x_pct_free in varchar2,
95 x_pct_increase in varchar2,
96 x_user_id in varchar2);
97
98 --
99 -- UploadIndexColumn (PUBLIC))
100 -- Public procedure for afdict.lct to call when uploading index columns using
101 -- using afdict.lct.
102 --
103 procedure UploadIndexColumn (
104 x_application_short_name in varchar2,
105 x_table_name in varchar2,
106 x_index_name in varchar2,
107 x_index_column_name in varchar2,
108 x_index_column_sequence in varchar2,
109 x_user_id in varchar2);
110
111 --
112 -- UploadPrimaryKey (PUBLIC))
113 -- Public procedure for afdict.lct to call when uploading primary key using
114 -- using afdict.lct. It calls InsertPrimary() when needed.
115 --
116 procedure UploadPrimaryKey (
117 x_application_short_name in varchar2,
118 x_table_name in varchar2,
119 x_primary_key_name in varchar2,
120 x_primary_key_type in varchar2,
121 x_audit_key_flag in varchar2,
122 x_description in varchar2,
123 x_enabled_flag in varchar2,
124 x_user_id in varchar2);
125
126 --
127 -- UploadPrimaryKeyColumn (PUBLIC))
128 -- Public procedure for afdict.lct to call when uploading primary key column
129 -- using afdict.lct.
130 --
131 procedure UploadPrimaryKeyColumn (
132 x_application_short_name in varchar2,
133 x_table_name in varchar2,
134 x_primary_key_name in varchar2,
135 x_primary_key_column_name in varchar2,
136 x_primary_key_column_sequence in varchar2,
137 x_user_id in varchar2);
138
139 --
140 -- UploadForeignKey (PUBLIC))
141 -- Public procedure for afdict.lct to call when uploading foreign key using
142 -- using afdict.lct. It calls InsertForeign() when needed.
143 --
144 procedure UploadForeignKey (
145 x_application_short_name in varchar2,
146 x_table_name in varchar2,
147 x_foreign_key_name in varchar2,
148 x_primary_key_application_name in varchar2,
149 x_primary_key_table_name in varchar2,
150 x_primary_key_name in varchar2,
151 x_description in varchar2,
152 x_cascade_behavior in varchar2,
153 x_foreign_key_relation in varchar2,
154 x_condition in varchar2,
155 x_enabled_flag in varchar2,
156 x_user_id in varchar2);
157
158 --
159 -- UploadForeignKeyColumn (PUBLIC))
160 -- Public procedure for afdict.lct to call when uploading foreign key column
161 -- using afdict.lct.
162 --
163 procedure UploadForeignKeyColumn (
164 x_application_short_name in varchar2,
165 x_table_name in varchar2,
166 x_foreign_key_name in varchar2,
167 x_foreign_key_column_name in varchar2,
168 x_foreign_key_column_sequence in varchar2,
169 x_cascade_value in varchar2,
170 x_user_id in varchar2);
171
172 --
173 -- UploadSequence (PUBLIC))
174 -- Public procedure for afdict.lct to call when uploading sequence
175 -- using afdict.lct. It calls InsertSequence when needed.
176 --
177 procedure UploadSequence (
178 x_application_short_name in varchar2,
179 x_sequence_name in varchar2,
180 x_start_value in varchar2,
181 x_description in varchar2,
182 x_increment_by in varchar2,
183 x_min_value in varchar2,
184 x_max_value in varchar2,
185 x_cache_size in varchar2,
186 x_cycle_flag in varchar2,
187 x_order_flag in varchar2,
188 x_user_id in varchar2);
189
190 --
191 -- UploadView (PUBLIC))
192 -- Public procedure for afdict.lct to call when uploading view
193 -- using afdict.lct. It calls InsertView when needed.
194 --
195 procedure UploadView (
196 x_application_short_name in varchar2,
197 x_view_name in varchar2,
198 x_text in varchar2,
199 x_description in varchar2,
200 x_user_id in varchar2);
201
202 --
203 -- UploadViewColumn (PUBLIC))
204 -- Public procedure for afdict.lct to call when uploading view column
205 -- using afdict.lct.
206 --
207 procedure UploadViewColumn (
208 x_application_short_name in varchar2,
209 x_view_name in varchar2,
210 x_view_column_name in varchar2,
211 x_view_column_sequence in varchar2,
212 x_user_id in varchar2);
213
214 --
215 -- ViewTextLength (PUBLIC)
216 -- Return the view text length.
217 -- This is a helper function as length() function can not be used
218 -- directly in sql but is ok to used on a variable in PL/SQL.
219 --
220 function ViewTextLength (
221 x_application_id in number,
222 x_view_name in varchar2) return number;
223 pragma restrict_references(ViewTextLength, WNDS);
224
225 --
226 -- RemoveColumn (PUBLIC)
227 -- Remove column from FND_COLUMNS table.
228 -- Before removing this column, make sure that there is no index,
229 -- primary key or foreign key is using this column
230 --
231 procedure RemoveColumn(
232 x_application_short_name in varchar2,
233 x_table_name in varchar2,
234 x_column_name in varchar2);
235
236 --
237 -- RemoveIndex (PUBLIC)
238 -- Remove index from FND_INDEXES and FND_INDEX_COLUMNS table.
239 --
240 procedure RemoveIndex(
241 x_application_short_name in varchar2,
242 x_table_name in varchar2,
243 x_index_name in varchar2);
244
245 --
246 -- RemovePrimaryKey (PUBLIC)
247 -- Remove primary key from FND_PRIMARY_KEYS and FND_PRIMARY_KEY_COLUMNS table.
248 -- Before deleting primary key, make sure that there is no foreign key
249 -- pointing to this primary key
250 --
251 procedure RemovePrimaryKey(
252 x_application_short_name in varchar2,
253 x_table_name in varchar2,
254 x_primary_key_name in varchar2);
255
256 --
257 -- RemoveForeignKey (PUBLIC)
258 -- Remove foreign key from FND_FOREIGN_KEYS and FND_FOREIGN_KEY_COLUMNS table.
259 --
260 procedure RemoveForeignKey(
261 x_application_short_name in varchar2,
262 x_table_name in varchar2,
263 x_foreign_key_name in varchar2);
264
265 --
266 -- RemoveSequence (PUBLIC)
267 -- Remove sequence from FND_SEQUENCES table.
268 --
269 procedure RemoveSequence(
270 x_application_short_name in varchar2,
271 x_sequence_name in varchar2);
272
273 --
274 -- RemoveView (PUBLIC)
275 -- Remove view from FND_VIEWS and FND_VIEW_COLUMNS table.
276 --
277 procedure RemoveView(
278 x_application_short_name in varchar2,
279 x_view_name in varchar2);
280
281 --
282 -- RemoveTable (PUBLIC)
283 -- Remove table from FND_TABLES and all its columns, indexes, primary
284 -- keys and foreign keys.
285 --
286 procedure RemoveTable(
287 x_application_short_name in varchar2,
288 x_table_name in varchar2);
289
290 --
291 -- UploadTable (PUBLIC)) - Overloaded
292 -- Public procedure for afdict.lct to call when uploading tables using
293 -- using afdict.lct. It calls InsertTable() when needed.
294 --
295 procedure UploadTable (
296 x_application_short_name in varchar2,
297 x_table_name in varchar2,
298 x_user_table_name in varchar2,
299 x_table_type in varchar2,
300 x_description in varchar2,
301 x_auto_size in varchar2,
302 x_initial_extent in varchar2,
303 x_next_extent in varchar2,
304 x_min_extents in varchar2,
305 x_max_extents in varchar2,
306 x_ini_trans in varchar2,
307 x_max_trans in varchar2,
308 x_pct_free in varchar2,
309 x_pct_increase in varchar2,
310 x_pct_used in varchar2,
311 x_hosted_support_style in varchar2,
312 x_user_id in varchar2,
313 x_custom_mode in varchar2,
314 x_last_update_date in varchar2);
315
316 --
317 -- UploadColumn (PUBLIC))- Overloaded
318 -- Public procedure for afdict.lct to call when uploading columns using
319 -- using afdict.lct. It calls InsertColumn() when needed.
320 --
321
322 procedure UploadColumn (
323 x_application_short_name in varchar2,
324 x_table_name in varchar2,
325 x_column_name in varchar2,
326 x_user_column_name in varchar2,
327 x_column_sequence in varchar2,
328 x_column_type in varchar2,
329 x_width in varchar2,
330 x_null_allowed_flag in varchar2,
331 x_description in varchar2,
332 x_default_value in varchar2,
333 x_translate_flag in varchar2,
334 x_precision in varchar2,
335 x_scale in varchar2,
336 x_flexfield_usage_code in varchar2,
337 x_flexfield_application_id in varchar2,
338 x_flexfield_name in varchar2,
339 x_flex_value_set_app_id in varchar2,
340 x_flex_value_set_id in varchar2,
341 x_user_id in varchar2,
342 x_custom_mode in varchar2,
343 x_last_update_date in varchar2);
344
345 --
346 -- UploadHistColumn (PUBLIC)) - Overloaded
347 -- Public procedure for afdict.lct to call when uploading hist columns using
348 -- using afdict.lct. It calls InsertHistColumn() when needed.
349 --
350
351 procedure UploadHistColumn (
352 x_application_short_name in varchar2,
353 x_table_name in varchar2,
354 x_column_name in varchar2,
355 x_partition in varchar2,
356 x_hsize in varchar2,
357 x_user_id in varchar2,
358 x_custom_mode in varchar2,
359 x_last_update_date in varchar2);
360
361 --
362 -- UploadIndex (PUBLIC)) - Overloaded
363 -- Public procedure for afdict.lct to call when uploading indexes using
364 -- using afdict.lct. It calls InsertIndex() when needed.
365 --
366 procedure UploadIndex (
367 x_application_short_name in varchar2,
368 x_table_name in varchar2,
369 x_index_name in varchar2,
370 x_uniqueness in varchar2,
371 x_auto_size in varchar2,
372 x_description in varchar2,
373 x_initial_extent in varchar2,
374 x_next_extent in varchar2,
375 x_min_extents in varchar2,
376 x_max_extents in varchar2,
377 x_ini_trans in varchar2,
378 x_max_trans in varchar2,
379 x_pct_free in varchar2,
380 x_pct_increase in varchar2,
381 x_user_id in varchar2,
382 x_custom_mode in varchar2,
383 x_last_update_date in varchar2,
384 x_phase_mode in varchar2);
385
386 --
387 -- UploadIndexColumn (PUBLIC)) - Overloaded
388 -- Public procedure for afdict.lct to call when uploading index columns using
389 -- using afdict.lct.
390 --
391 procedure UploadIndexColumn (
392 x_application_short_name in varchar2,
393 x_table_name in varchar2,
394 x_index_name in varchar2,
395 x_index_column_name in varchar2,
396 x_index_column_sequence in varchar2,
397 x_user_id in varchar2,
398 x_custom_mode in varchar2,
399 x_last_update_date in varchar2);
400
401 --
402 -- UploadPrimaryKey (PUBLIC)) - Overloaded
403 -- Public procedure for afdict.lct to call when uploading primary key using
404 -- using afdict.lct. It calls InsertPrimary() when needed.
405 --
406 procedure UploadPrimaryKey (
407 x_application_short_name in varchar2,
408 x_table_name in varchar2,
409 x_primary_key_name in varchar2,
410 x_primary_key_type in varchar2,
411 x_audit_key_flag in varchar2,
412 x_description in varchar2,
413 x_enabled_flag in varchar2,
414 x_user_id in varchar2,
415 x_custom_mode in varchar2,
416 x_last_update_date in varchar2,
417 x_phase_mode in varchar2);
418
419 --
420 -- UploadPrimaryKeyColumn (PUBLIC)) - Overloaded
421 -- Public procedure for afdict.lct to call when uploading primary key column
422 -- using afdict.lct.
423 --
424 procedure UploadPrimaryKeyColumn (
425 x_application_short_name in varchar2,
426 x_table_name in varchar2,
427 x_primary_key_name in varchar2,
428 x_primary_key_column_name in varchar2,
429 x_primary_key_column_sequence in varchar2,
430 x_user_id in varchar2,
431 x_custom_mode in varchar2,
432 x_last_update_date in varchar2);
433
434 --
435 -- UploadForeignKey (PUBLIC)) - Overloaded
436 -- Public procedure for afdict.lct to call when uploading foreign key using
437 -- using afdict.lct. It calls InsertForeign() when needed.
438 --
439 procedure UploadForeignKey (
440 x_application_short_name in varchar2,
441 x_table_name in varchar2,
442 x_foreign_key_name in varchar2,
443 x_primary_key_application_name in varchar2,
444 x_primary_key_table_name in varchar2,
445 x_primary_key_name in varchar2,
446 x_description in varchar2,
447 x_cascade_behavior in varchar2,
448 x_foreign_key_relation in varchar2,
449 x_condition in varchar2,
450 x_enabled_flag in varchar2,
451 x_user_id in varchar2,
452 x_custom_mode in varchar2,
453 x_last_update_date in varchar2,
454 x_phase_mode in varchar2);
455
456 --
457 -- UploadForeignKeyColumn (PUBLIC)) - Overloaded
458 -- Public procedure for afdict.lct to call when uploading foreign key column
459 -- using afdict.lct.
460 --
461 procedure UploadForeignKeyColumn (
462 x_application_short_name in varchar2,
463 x_table_name in varchar2,
464 x_foreign_key_name in varchar2,
465 x_foreign_key_column_name in varchar2,
466 x_foreign_key_column_sequence in varchar2,
467 x_cascade_value in varchar2,
468 x_user_id in varchar2,
469 x_custom_mode in varchar2,
470 x_last_update_date in varchar2);
471
472 --
473 -- UploadSequence (PUBLIC)) - Overloaded
474 -- Public procedure for afdict.lct to call when uploading sequence
475 -- using afdict.lct. It calls InsertSequence when needed.
476 --
477 procedure UploadSequence (
478 x_application_short_name in varchar2,
479 x_sequence_name in varchar2,
480 x_start_value in varchar2,
481 x_description in varchar2,
482 x_increment_by in varchar2,
483 x_min_value in varchar2,
484 x_max_value in varchar2,
485 x_cache_size in varchar2,
486 x_cycle_flag in varchar2,
487 x_order_flag in varchar2,
488 x_user_id in varchar2,
489 x_custom_mode in varchar2,
490 x_last_update_date in varchar2);
491
492 --
493 -- UploadView (PUBLIC))- Overloaded
494 -- Public procedure for afdict.lct to call when uploading view
495 -- using afdict.lct. It calls InsertView when needed.
496 --
497 procedure UploadView (
498 x_application_short_name in varchar2,
499 x_view_name in varchar2,
500 x_text in varchar2,
501 x_description in varchar2,
502 x_user_id in varchar2,
503 x_custom_mode in varchar2,
504 x_last_update_date in varchar2,
505 x_phase_mode in varchar2);
506
507 --
508 -- UploadViewColumn (PUBLIC))- Overloaded
509 -- Public procedure for afdict.lct to call when uploading view column
510 -- using afdict.lct.
511 --
512 procedure UploadViewColumn (
513 x_application_short_name in varchar2,
514 x_view_name in varchar2,
515 x_view_column_name in varchar2,
516 x_view_column_sequence in varchar2,
517 x_user_id in varchar2,
521 --
518 x_custom_mode in varchar2,
519 x_last_update_date in varchar2);
520
522 -- UpdatePKColumns (Public)
523 --
524 -- Description
525 -- For a given table(x_application_short_name and x_primary_table_name)
526 -- and column names array, find all pk/uk that contains all the columns
527 -- in the x_primary_key_col_names. For each of these pk/uk, call
528 -- UpdateChildren() to find their children table and do a recursive update.
529 --
530 -- Input
531 -- x_application_short_name:
532 -- application_short_name
533 -- x_primary_table_name:
534 -- table that contains the column name array
535 -- x_primary_key_col_names:
536 -- column name array
537 -- x_primary_key_col_value_old:
538 -- columns's existing value in array
539 -- x_primary_key_col_value_new:
540 -- columns's new value in array
541 --
542 -- Output
543 -- True: Succeed
544 -- False: Something went wrong, please check the logging info
545 function UpdatePKColumns(x_application_short_name in varchar2,
546 x_primary_table_name in varchar2,
547 x_primary_key_col_names in NameArrayTyp,
548 x_primary_key_col_value_old in NameArrayTyp,
549 x_primary_key_col_value_new in NameArrayTyp)
550 return boolean;
551
552 -- CheckPKColumns (Public)
553 --
554 -- Description
555 -- For a given table(x_application_short_name and x_primary_table_name)
556 -- and column names array, find all pk/uk that contains all the columns
557 -- in the x_primary_key_col_names. For each of these pk/uk, call
558 -- UpdateOrCheckChildren() to see if there is any foreign key children has
559 -- the same value as the value inside x_primary_key_col_old.
560 -- If there is, return TRUE otherwise return FALSE.
561 --
562 -- Input
563 -- x_application_short_name:
564 -- application_short_name
565 -- x_primary_table_name:
566 -- table that contains the column name array
567 -- x_primary_key_col_names:
568 -- column name array
569 -- x_primary_key_col_value_old:
570 -- columns's existing value in array
571 --
572 -- Output
573 -- True: There is foreign key child with the same value.
574 -- False: No foreign key child has the same value.
575 function CheckPKColumns(x_application_short_name in varchar2,
576 x_primary_table_name in varchar2,
577 x_primary_key_col_names in NameArrayTyp,
578 x_primary_key_col_value_old in NameArrayTyp)
579 return boolean;
580 end Fnd_Dictionary_Pkg;
[Home] [Help]