1 package Fnd_XdfDictionary_Pkg AUTHID CURRENT_USER as
2 /* $Header: fndpxdts.pls 120.3 2011/09/27 12:40:26 vkhatri ship $ */
3
4
5 --
6 -- UploadTable (PUBLIC))
7 -- Public procedure for afdict.lct to call when uploading tables using
8 -- using afdict.lct. It calls InsertTable() when needed.
9 --
10 procedure UploadTable (
11 x_application_short_name in varchar2,
12 x_table_name in varchar2,
13 x_user_table_name in varchar2,
14 x_table_type in varchar2,
15 x_description in varchar2,
16 x_auto_size in varchar2,
17 x_initial_extent in varchar2,
18 x_next_extent in varchar2,
19 x_min_extents in varchar2,
20 x_max_extents in varchar2,
21 x_ini_trans in varchar2,
22 x_max_trans in varchar2,
23 x_pct_free in varchar2,
24 x_pct_increase in varchar2,
25 x_pct_used in varchar2,
26 x_hosted_support_style in varchar2,
27 x_user_id in varchar2);
28
29
30 --
31 -- UploadColumn (PUBLIC))
32 -- Public procedure for afdict.lct to call when uploading columns using
33 -- using afdict.lct. It calls InsertColumn() when needed.
34 --
35
36 procedure UploadColumn (
37 x_application_short_name in varchar2,
38 x_table_name in varchar2,
39 x_column_name in varchar2,
40 x_user_column_name in varchar2,
41 x_column_sequence in varchar2,
42 x_column_type in varchar2,
43 x_width in varchar2,
44 x_null_allowed_flag in varchar2,
45 x_description in varchar2,
46 x_default_value in varchar2,
47 x_translate_flag in varchar2,
48 x_precision in varchar2,
49 x_scale in varchar2,
50 x_flexfield_usage_code in varchar2,
51 x_flexfield_application_id in varchar2,
52 x_flexfield_name in varchar2,
53 x_flex_value_set_app_id in varchar2,
54 x_flex_value_set_id in varchar2,
55 x_user_id in varchar2);
56
57
58 --
59 -- UploadHistColumn (PUBLIC))
60 -- Public procedure for afdict.lct to call when uploading hist columns using
61 -- using afdict.lct. It calls InsertHistColumn() when needed.
62 --
63
64 procedure UploadHistColumn (
65 x_application_short_name in varchar2,
66 x_table_name in varchar2,
67 x_column_name in varchar2,
68 x_partition in varchar2,
69 x_hsize in varchar2,
70 x_user_id in varchar2);
71
72 --
73 -- UploadIndex (PUBLIC))
74 -- Public procedure for afdict.lct to call when uploading indexes using
75 -- using afdict.lct. It calls InsertIndex() when needed.
76 --
77 procedure UploadIndex (
78 x_application_short_name in varchar2,
79 x_table_name in varchar2,
80 x_index_name in varchar2,
81 x_uniqueness in varchar2,
82 x_auto_size in varchar2,
83 x_description in varchar2,
84 x_initial_extent in varchar2,
85 x_next_extent in varchar2,
86 x_min_extents in varchar2,
87 x_max_extents in varchar2,
88 x_ini_trans in varchar2,
89 x_max_trans in varchar2,
90 x_pct_free in varchar2,
91 x_pct_increase in varchar2,
92 x_user_id in varchar2);
93
94 --
95 -- UploadIndexColumn (PUBLIC))
96 -- Public procedure for afdict.lct to call when uploading index columns using
97 -- using afdict.lct.
98 --
99 procedure UploadIndexColumn (
100 x_application_short_name in varchar2,
101 x_table_name in varchar2,
102 x_index_name in varchar2,
103 x_index_column_name in varchar2,
104 x_index_column_sequence in varchar2,
105 x_user_id in varchar2);
106
107 --
108 -- UploadPrimaryKey (PUBLIC))
109 -- Public procedure for afdict.lct to call when uploading primary key using
110 -- using afdict.lct. It calls InsertPrimary() when needed.
111 --
112 procedure UploadPrimaryKey (
113 x_application_short_name in varchar2,
114 x_table_name in varchar2,
115 x_primary_key_name in varchar2,
116 x_primary_key_type in varchar2,
117 x_audit_key_flag in varchar2,
118 x_description in varchar2,
119 x_enabled_flag in varchar2,
120 x_user_id in varchar2);
121
122 --
123 -- UploadPrimaryKeyColumn (PUBLIC))
124 -- Public procedure for afdict.lct to call when uploading primary key column
125 -- using afdict.lct.
126 --
127 procedure UploadPrimaryKeyColumn (
128 x_application_short_name in varchar2,
129 x_table_name in varchar2,
130 x_primary_key_name in varchar2,
131 x_primary_key_column_name in varchar2,
132 x_primary_key_column_sequence in varchar2,
133 x_user_id in varchar2);
134
135 --
136 -- UploadForeignKey (PUBLIC))
137 -- Public procedure for afdict.lct to call when uploading foreign key using
138 -- using afdict.lct. It calls InsertForeign() when needed.
139 --
140 procedure UploadForeignKey (
141 x_application_short_name in varchar2,
142 x_table_name in varchar2,
143 x_foreign_key_name in varchar2,
144 x_primary_key_application_name in varchar2,
145 x_primary_key_table_name in varchar2,
146 x_primary_key_name in varchar2,
147 x_description in varchar2,
148 x_cascade_behavior in varchar2,
149 x_foreign_key_relation in varchar2,
150 x_condition in varchar2,
151 x_enabled_flag in varchar2,
152 x_user_id in varchar2);
153
154 --
155 -- UploadForeignKeyColumn (PUBLIC))
156 -- Public procedure for afdict.lct to call when uploading foreign key column
157 -- using afdict.lct.
158 --
159 procedure UploadForeignKeyColumn (
160 x_application_short_name in varchar2,
161 x_table_name in varchar2,
162 x_foreign_key_name in varchar2,
163 x_foreign_key_column_name in varchar2,
164 x_foreign_key_column_sequence in varchar2,
165 x_cascade_value in varchar2,
166 x_user_id in varchar2);
167
168 --
169 -- UploadSequence (PUBLIC))
170 -- Public procedure for afdict.lct to call when uploading sequence
171 -- using afdict.lct. It calls InsertSequence when needed.
172 --
173 procedure UploadSequence (
174 x_application_short_name in varchar2,
175 x_sequence_name in varchar2,
176 x_start_value in varchar2,
177 x_description in varchar2,
178 x_increment_by in varchar2,
179 x_min_value in varchar2,
180 x_max_value in varchar2,
181 x_cache_size in varchar2,
182 x_cycle_flag in varchar2,
183 x_order_flag in varchar2,
184 x_user_id in varchar2);
185
186 --
187 -- UploadView (PUBLIC))
188 -- Public procedure for afdict.lct to call when uploading view
189 -- using afdict.lct. It calls InsertView when needed.
190 --
191 procedure UploadView (
192 x_application_short_name in varchar2,
193 x_view_name in varchar2,
194 x_text in varchar2,
195 x_description in varchar2,
196 x_user_id in varchar2);
197
198 --
199 -- UploadViewColumn (PUBLIC))
200 -- Public procedure for afdict.lct to call when uploading view column
201 -- using afdict.lct.
202 --
203 procedure UploadViewColumn (
204 x_application_short_name in varchar2,
205 x_view_name in varchar2,
206 x_view_column_name in varchar2,
207 x_view_column_sequence in varchar2,
208 x_user_id in varchar2);
209
210 --
211 -- ViewTextLength (PUBLIC)
212 -- Return the view text length.
213 -- This is a helper function as length() function can not be used
214 -- directly in sql but is ok to used on a variable in PL/SQL.
215 --
216 function ViewTextLength (
217 x_application_id in number,
218 x_view_name in varchar2) return number;
219 pragma restrict_references(ViewTextLength, WNDS);
220
221 --
222 -- RemoveColumn (PUBLIC)
223 -- Remove column from FND_COLUMNS table.
224 -- Before removing this column, make sure that there is no index,
225 -- primary key or foreign key is using this column
226 --
227 procedure RemoveColumn(
228 x_application_short_name in varchar2,
229 x_table_name in varchar2,
230 x_column_name in varchar2);
231
232 --
233 -- RemoveIndex (PUBLIC)
234 -- Remove index from FND_INDEXES and FND_INDEX_COLUMNS table.
235 --
236 procedure RemoveIndex(
237 x_application_short_name in varchar2,
238 x_table_name in varchar2,
239 x_index_name in varchar2);
240
241 --
242 -- RemovePrimaryKey (PUBLIC)
243 -- Remove primary key from FND_PRIMARY_KEYS and FND_PRIMARY_KEY_COLUMNS table.
244 -- Before deleting primary key, make sure that there is no foreign key
245 -- pointing to this primary key
246 --
247 procedure RemovePrimaryKey(
248 x_application_short_name in varchar2,
249 x_table_name in varchar2,
250 x_primary_key_name in varchar2);
251
252 --
253 -- RemoveForeignKey (PUBLIC)
254 -- Remove foreign key from FND_FOREIGN_KEYS and FND_FOREIGN_KEY_COLUMNS table.
255 --
256 procedure RemoveForeignKey(
257 x_application_short_name in varchar2,
258 x_table_name in varchar2,
259 x_foreign_key_name in varchar2);
260
261 --
262 -- RemoveSequence (PUBLIC)
263 -- Remove sequence from FND_SEQUENCES table.
264 --
265 procedure RemoveSequence(
266 x_application_short_name in varchar2,
267 x_sequence_name in varchar2);
268
269 --
270 -- RemoveView (PUBLIC)
271 -- Remove view from FND_VIEWS and FND_VIEW_COLUMNS table.
272 --
273 procedure RemoveView(
274 x_application_short_name in varchar2,
275 x_view_name in varchar2);
276
277 --
278 -- RemoveTable (PUBLIC)
279 -- Remove table from FND_TABLES and all its columns, indexes, primary
280 -- keys and foreign keys.
281 --
282 procedure RemoveTable(
283 x_application_short_name in varchar2,
284 x_table_name in varchar2);
285
286 --
287 -- UploadTable (PUBLIC)) - Overloaded
288 -- Public procedure for afdict.lct to call when uploading tables using
289 -- using afdict.lct. It calls InsertTable() when needed.
290 --
291 procedure UploadTable (
292 x_application_short_name in varchar2,
293 x_table_name in varchar2,
294 x_user_table_name in varchar2,
295 x_table_type in varchar2,
296 x_description in varchar2,
297 x_auto_size in varchar2,
298 x_initial_extent in varchar2,
299 x_next_extent in varchar2,
300 x_min_extents in varchar2,
301 x_max_extents in varchar2,
302 x_ini_trans in varchar2,
303 x_max_trans in varchar2,
304 x_pct_free in varchar2,
305 x_pct_increase in varchar2,
306 x_pct_used in varchar2,
307 x_hosted_support_style in varchar2,
308 x_user_id in varchar2,
309 x_custom_mode in varchar2,
310 x_last_update_date in varchar2,
311 P_NZDT_MODE in VARCHAR2 default 'N');
312
313 --
314 -- UploadColumn (PUBLIC))- Overloaded
315 -- Public procedure for afdict.lct to call when uploading columns using
316 -- using afdict.lct. It calls InsertColumn() when needed.
317 --
318
319 procedure UploadColumn (
320 x_application_short_name in varchar2,
321 x_table_name in varchar2,
322 x_column_name in varchar2,
323 x_user_column_name in varchar2,
324 x_column_sequence in varchar2,
325 x_column_type in varchar2,
326 x_width in varchar2,
327 x_null_allowed_flag in varchar2,
328 x_description in varchar2,
329 x_default_value in varchar2,
330 x_translate_flag in varchar2,
331 x_precision in varchar2,
332 x_scale in varchar2,
333 x_flexfield_usage_code in varchar2,
334 x_flexfield_application_id in varchar2,
335 x_flexfield_name in varchar2,
336 x_flex_value_set_app_id in varchar2,
337 x_flex_value_set_id in varchar2,
338 x_user_id in varchar2,
339 x_custom_mode in varchar2,
340 x_last_update_date in varchar2,
341 P_NZDT_MODE in VARCHAR2 default 'N');
342
343 --
344 -- UploadHistColumn (PUBLIC)) - Overloaded
345 -- Public procedure for afdict.lct to call when uploading hist columns using
346 -- using afdict.lct. It calls InsertHistColumn() when needed.
347 --
348
349 procedure UploadHistColumn (
350 x_application_short_name in varchar2,
351 x_table_name in varchar2,
352 x_column_name in varchar2,
353 x_partition in varchar2,
354 x_hsize in varchar2,
355 x_user_id in varchar2,
356 x_custom_mode in varchar2,
360 --
357 x_last_update_date in varchar2,
358 P_NZDT_MODE in VARCHAR2 default 'N');
359
361 -- Public procedure UploadHistColumn_MV () -
362 -- upload MVIEW metadata
363
364 procedure UploadHistColumn_MV (
365 x_application_short_name in varchar2,
366 x_table_name in varchar2,
367 x_column_name in varchar2,
368 x_partition in varchar2,
369 x_hsize in varchar2,
370 x_user_id in varchar2,
371 x_custom_mode in varchar2,
372 x_last_update_date in varchar2,
373 x_mview_owner in varchar2,
374 P_NZDT_MODE in VARCHAR2 default 'N'
375 ) ;
376
377 --
378 -- UploadIndex (PUBLIC)) - Overloaded
379 -- Public procedure for afdict.lct to call when uploading indexes using
380 -- using afdict.lct. It calls InsertIndex() when needed.
381 --
382 procedure UploadIndex (
383 x_application_short_name in varchar2,
384 x_table_name in varchar2,
385 x_index_name in varchar2,
386 x_uniqueness in varchar2,
387 x_auto_size in varchar2,
388 x_description in varchar2,
389 x_initial_extent in varchar2,
390 x_next_extent in varchar2,
391 x_min_extents in varchar2,
392 x_max_extents in varchar2,
393 x_ini_trans in varchar2,
394 x_max_trans in varchar2,
395 x_pct_free in varchar2,
396 x_pct_increase in varchar2,
397 x_user_id in varchar2,
398 x_custom_mode in varchar2,
399 x_last_update_date in varchar2,
400 x_phase_mode in varchar2);
401
402 --
403 -- UploadIndexColumn (PUBLIC)) - Overloaded
404 -- Public procedure for afdict.lct to call when uploading index columns using
405 -- using afdict.lct.
406 --
407 procedure UploadIndexColumn (
408 x_application_short_name in varchar2,
409 x_table_name in varchar2,
410 x_index_name in varchar2,
411 x_index_column_name in varchar2,
412 x_index_column_sequence in varchar2,
413 x_user_id in varchar2,
414 x_custom_mode in varchar2,
415 x_last_update_date in varchar2);
416
417 --
418 -- UploadPrimaryKey (PUBLIC)) - Overloaded
419 -- Public procedure for afdict.lct to call when uploading primary key using
420 -- using afdict.lct. It calls InsertPrimary() when needed.
421 --
422 procedure UploadPrimaryKey (
423 x_application_short_name in varchar2,
424 x_table_name in varchar2,
425 x_primary_key_name in varchar2,
426 x_primary_key_type in varchar2,
427 x_audit_key_flag in varchar2,
428 x_description in varchar2,
429 x_enabled_flag in varchar2,
430 x_user_id in varchar2,
431 x_custom_mode in varchar2,
432 x_last_update_date in varchar2,
433 x_phase_mode in varchar2,
434 x_overwrite_PK in varchar2 DEFAULT 'N',
435 P_NZDT_MODE in VARCHAR2 default 'N'
436 );
437
438 --
439 -- UploadPrimaryKeyColumn (PUBLIC)) - Overloaded
440 -- Public procedure for afdict.lct to call when uploading primary key column
441 -- using afdict.lct.
442 --
443 procedure UploadPrimaryKeyColumn (
444 x_application_short_name in varchar2,
445 x_table_name in varchar2,
446 x_primary_key_name in varchar2,
447 x_primary_key_column_name in varchar2,
448 x_primary_key_column_sequence in varchar2,
449 x_user_id in varchar2,
450 x_custom_mode in varchar2,
451 x_last_update_date in varchar2,
452 P_NZDT_MODE in VARCHAR2 default 'N');
453
454 --
455 -- UploadForeignKey (PUBLIC)) - Overloaded
456 -- Public procedure for afdict.lct to call when uploading foreign key using
457 -- using afdict.lct. It calls InsertForeign() when needed.
458 --
459 procedure UploadForeignKey (
460 x_application_short_name in varchar2,
461 x_table_name in varchar2,
462 x_foreign_key_name in varchar2,
463 x_primary_key_application_name in varchar2,
464 x_primary_key_table_name in varchar2,
465 x_primary_key_name in varchar2,
466 x_description in varchar2,
467 x_cascade_behavior in varchar2,
468 x_foreign_key_relation in varchar2,
469 x_condition in varchar2,
470 x_enabled_flag in varchar2,
471 x_user_id in varchar2,
472 x_custom_mode in varchar2,
473 x_last_update_date in varchar2,
474 x_phase_mode in varchar2,
475 P_NZDT_MODE in VARCHAR2 default 'N');
476
477 --
478 -- UploadForeignKeyColumn (PUBLIC)) - Overloaded
479 -- Public procedure for afdict.lct to call when uploading foreign key column
480 -- using afdict.lct.
481 --
482 procedure UploadForeignKeyColumn (
483 x_application_short_name in varchar2,
484 x_table_name in varchar2,
485 x_foreign_key_name in varchar2,
486 x_foreign_key_column_name in varchar2,
487 x_foreign_key_column_sequence in varchar2,
488 x_cascade_value in varchar2,
489 x_user_id in varchar2,
490 x_custom_mode in varchar2,
491 x_last_update_date in varchar2,
492 P_NZDT_MODE in VARCHAR2 default 'N');
493
494 --
495 -- UploadSequence (PUBLIC)) - Overloaded
496 -- Public procedure for afdict.lct to call when uploading sequence
497 -- using afdict.lct. It calls InsertSequence when needed.
498 --
499 procedure UploadSequence (
500 x_application_short_name in varchar2,
501 x_sequence_name in varchar2,
502 x_start_value in varchar2,
503 x_description in varchar2,
504 x_increment_by in varchar2,
505 x_min_value in varchar2,
506 x_max_value in varchar2,
507 x_cache_size in varchar2,
508 x_cycle_flag in varchar2,
509 x_order_flag in varchar2,
510 x_user_id in varchar2,
511 x_custom_mode in varchar2,
512 x_last_update_date in varchar2);
513
514 --
515 -- UploadView (PUBLIC))- Overloaded
516 -- Public procedure for afdict.lct to call when uploading view
517 -- using afdict.lct. It calls InsertView when needed.
518 --
519 procedure UploadView (
520 x_application_short_name in varchar2,
521 x_view_name in varchar2,
522 x_text in varchar2,
523 x_description in varchar2,
524 x_user_id in varchar2,
525 x_custom_mode in varchar2,
526 x_last_update_date in varchar2,
527 x_phase_mode in varchar2,
528 P_NZDT_MODE in VARCHAR2 default 'N');
529
530 --
531 -- UploadViewColumn (PUBLIC))- Overloaded
532 -- Public procedure for afdict.lct to call when uploading view column
533 -- using afdict.lct.
534 --
535 procedure UploadViewColumn (
536 x_application_short_name in varchar2,
537 x_view_name in varchar2,
538 x_view_column_name in varchar2,
539 x_view_column_sequence in varchar2,
540 x_user_id in varchar2,
541 x_custom_mode in varchar2,
542 x_last_update_date in varchar2);
543
544 --
545 -- OWNER_ID
546 -- Return the user_id of the OWNER attribute
547 -- IN
548 -- p_name - OWNER attribute value from FNDLOAD data file
549 -- RETURNS
550 -- user_id of owner to use in who columns
551 --
552 function OWNER_ID(
553 p_name in varchar2)
554 return number;
555
556
557 --
558 -- UPLOAD_TEST
559 -- Test whether or not to over-write database row when uploading
560 -- data from FNDLOAD data file, based on owner attributes of both
561 -- database row and row in file being uploaded.
562 -- IN
563 -- p_file_id - OWNER_ID(<OWNER attribute from data file>)
564 -- p_file_lud - LAST_UPDATE_DATE attribute from data file
565 -- p_db_id - LAST_UPDATED_BY of db row
566 -- p_db_lud - LAST_UPDATE_DATE of db row
567 -- p_custom_mode - CUSTOM_MODE FNDLOAD parameter value
568 -- RETURNS
569 -- TRUE if safe to over-write.
570 --
571 function UPLOAD_TEST(
572 p_file_id in number,
573 p_file_lud in date,
574 p_db_id in number,
575 p_db_lud in date,
576 p_custom_mode in varchar2)
577 return boolean;
578
579 procedure INSERT_ROW (
580 X_ROWID in out NOCOPY VARCHAR2,
581 P_APPLICATION_ID in NUMBER,
582 P_OBJECT_NAME in VARCHAR2,
583 P_OBJECT_TYPE in VARCHAR2,
584 P_TABLESPACE_TYPE in VARCHAR2,
585 P_CUSTOM_TABLESPACE_TYPE in VARCHAR2,
586 P_OBJECT_SOURCE in VARCHAR2,
587 P_ORACLE_USERNAME in VARCHAR2,
588 P_CUSTOM_FLAG in VARCHAR2,
589 P_CREATION_DATE in DATE,
590 P_CREATED_BY in NUMBER,
591 P_LAST_UPDATE_DATE in DATE,
592 P_LAST_UPDATED_BY in NUMBER,
593 P_LAST_UPDATE_LOGIN in NUMBER);
594
595 procedure LOCK_ROW (
596 P_APPLICATION_ID in NUMBER,
597 P_OBJECT_NAME in VARCHAR2,
598 P_OBJECT_TYPE in VARCHAR2,
599 P_TABLESPACE_TYPE in VARCHAR2);
600
601 procedure UPDATE_ROW (
602 P_APPLICATION_ID in NUMBER,
603 P_OBJECT_NAME in VARCHAR2,
604 P_OBJECT_TYPE in VARCHAR2,
605 P_TABLESPACE_TYPE in VARCHAR2,
606 P_CUSTOM_TABLESPACE_TYPE in VARCHAR2,
607 P_OBJECT_SOURCE in VARCHAR2,
608 P_ORACLE_USERNAME in VARCHAR2,
609 P_CUSTOM_FLAG in VARCHAR2,
610 P_LAST_UPDATE_DATE in DATE,
611 P_LAST_UPDATED_BY in NUMBER,
612 P_LAST_UPDATE_LOGIN in NUMBER);
613
614 procedure LOAD_ROW (
615 P_APPLICATION_ID in NUMBER,
616 P_OBJECT_NAME in VARCHAR2,
617 P_OBJECT_TYPE in VARCHAR2,
618 P_TABLESPACE_TYPE in VARCHAR2,
619 P_CUSTOM_TABLESPACE_TYPE in VARCHAR2,
620 P_OBJECT_SOURCE in VARCHAR2,
621 P_ORACLE_USERNAME in VARCHAR2,
622 P_CUSTOM_FLAG in VARCHAR2,
623 P_LAST_UPDATED_BY in VARCHAR2,
624 P_CUSTOM_MODE in VARCHAR2,
625 P_LAST_UPDATE_DATE in VARCHAR2,
626 P_NZDT_MODE in VARCHAR2 default 'N');
627
628 end Fnd_XdfDictionary_Pkg;