[Home] [Help]
PACKAGE BODY: APPS.FND_IREP_LOADER_PRIVATE
Source
1 PACKAGE BODY FND_IREP_LOADER_PRIVATE AS
2 /* $Header: AFIRLDRB.pls 120.5.12010000.5 2008/11/04 12:10:55 snalagan ship $ */
3 --
4 -- Function
5 -- COMPARE_VERSIONS
6 --
7 -- Purpose
8 -- Compare the version numbers of two files
9 --
10 -- RETURNS: The string "=" if p_version1 = p_version2
11 -- The string ">" if p_version1 > p_version2
12 -- The string "<" if p_version1 < p_version2
13
14 x_rowid VARCHAR2(64);
15 type t_fnd_grants IS TABLE OF fnd_grants % rowtype;
16 type t_fnd_menus IS TABLE OF fnd_menus % rowtype;
17 type t_fnd_menu_entries IS TABLE OF fnd_menu_entries % rowtype;
18 type t_fnd_form_functions IS TABLE OF fnd_form_functions % rowtype;
19
20 function_id fnd_form_functions.function_id%TYPE;
21 menu_id fnd_menus.menu_id%TYPE;
22
23 type t_fnd_menus_tl IS TABLE OF fnd_menus_tl % rowtype;
24 type t_fnd_menu_entries_tl IS TABLE OF fnd_menu_entries_tl % rowtype;
25 type t_fnd_form_functions_tl IS TABLE OF fnd_form_functions_tl % rowtype;
26
27
28 -- to store existing menus and grants
29 v_fnd_grants t_fnd_grants;
30 v_fnd_menus t_fnd_menus;
31 v_fnd_menu_entries t_fnd_menu_entries;
32 v_fnd_form_functions t_fnd_form_functions;
33 v_fnd_menus_tl t_fnd_menus_tl;
34 v_fnd_menu_entries_tl t_fnd_menu_entries_tl;
35 v_fnd_grants_new t_fnd_grants;
36 v_fnd_menus_new t_fnd_menus;
37 v_fnd_menu_entries_new t_fnd_menu_entries;
38 v_fnd_form_functions_new t_fnd_form_functions;
39 v_fnd_menus_tl_new t_fnd_menus_tl;
40 v_fnd_menu_entries_tl_new t_fnd_menu_entries_tl;
41
42 menu_entry_count number;
43 menu_entry_tl_count number;
44 menu_count number;
45 menu_tl_count number;
46 grants_count number;
47
48
49
50 FUNCTION COMPARE_VERSIONS(p_version1 IN VARCHAR2,
51 p_version2 IN VARCHAR2)
52 RETURN VARCHAR2 is
53
54 --variables to hold the input
55 version1 varchar2(30);
56 version2 varchar2(30);
57
58 --pointer to the dot(.)
59 ptr number;
60
61 --variables to hold the remaining string to be processed
62 version1A varchar2(30);
63 version2A varchar2(30);
64
65 BEGIN
66
67 version2 := p_version2;
68 version1 := p_version1;
69
70 IF(version1 is null and version2 is null) THEN
71 RETURN '=';
72 ELSIF (version1 is null)THEN
73 RETURN '<';
74 ELSIF (version2 is null)THEN
75 RETURN '>';
76 END IF;
77
78 --Get the location of the dot for each version strings.
79 --set the value after the dot in version1A and the value before
80 --the dot in version1 so in the string 115.6.8,
81 --version1A=6.8 version1=115
82 ptr := INSTR(version1,'.');
83 IF(ptr<>0) THEN
84 version1A := substr(version1,ptr+1,length(version1));
85 version1 := SUBSTR(version1,1,ptr-1);
86 ELSE
87 version1A := null;
88 END IF;
89
90 ptr := INSTR(version2,'.');
91 IF (ptr<>0) THEN
92 version2A := substr(version2,ptr+1,length(version2));
93 version2 := SUBSTR(version2,1,ptr-1);
94 ELSE
95 version2A := null;
96 END IF;
97 --If both the versions are equal call the function recursively.
98 --Else compare them and return the result accordingly
99 IF(version1=version2) THEN
100 RETURN compare_versions(version1A,version2A);
101 ELSIF (to_number(version1)>to_number(version2)) THEN
102 RETURN '>';
103 ELSIF (to_number(version1)<to_number(version2)) THEN
104 RETURN '<';
105 END IF;
106
107 EXCEPTION
108 WHEN VALUE_ERROR THEN
109 RAISE_APPLICATION_ERROR('-20002','Invalid String value in input: '
110 || version1||' , '|| version2);
111 END;
112
113 --
114 -- Function
115 -- OBJ_IS_OBSOLETE
116 --
117 -- Purpose
118 -- Compare the version numbers of file in db with current source file.
119 --
120 -- RETURNS: TRUE if current file precedes file version in DB, FALSE otherwise.
121 --
122 FUNCTION OBJ_IS_OBSOLETE(P_DEST_TABLE in VARCHAR2,
123 P_OBJECT_NAME in VARCHAR2,
124 P_SOURCE_FILE_VERSION in VARCHAR2) RETURN BOOLEAN is
125
126
127 DB_Version Varchar2(150);
128 key_id number;
129
130 begin
131 begin
132 if (P_DEST_TABLE = 'O') then
133 Select object_id, IREP_SOURCE_FILE_VERSION
134 into key_id, DB_Version
135 from FND_OBJECTS
136 where obj_name = P_OBJECT_NAME;
137 else
138 Select class_id, SOURCE_FILE_VERSION
139 into key_id, DB_Version
140 from FND_IREP_CLASSES
141 where class_name = P_OBJECT_NAME;
142 end if;
143 exception
144 when no_data_found then -- totally new object or class
145 return FALSE;
146 end;
147 if (COMPARE_VERSIONS(P_SOURCE_FILE_VERSION, DB_Version) <> '>') then
148 /* Mark flag to indicate that the file data is [O]bsolete */
149 if (P_DEST_TABLE = 'O') then
150 Update FND_OBJECTS
151 set IREP_LDR_INTERNAL_FLAG = 'O'
152 where obj_name = P_OBJECT_NAME;
153 else
154 Update FND_IREP_CLASSES
155 set IREP_LDR_INTERNAL_FLAG = 'O'
156 where class_name = P_OBJECT_NAME;
157 end if;
158
159 return TRUE;
160 else
161 /* Clear flag to indicate that the file data is not [O]bsolete */
162 if (P_DEST_TABLE = 'O') then
163 Update FND_OBJECTS
164 set IREP_LDR_INTERNAL_FLAG = null
165 where obj_name = P_OBJECT_NAME;
166 else
167 Update FND_IREP_CLASSES
168 set IREP_LDR_INTERNAL_FLAG = null
169 where class_name = P_OBJECT_NAME;
170 end if;
171
172 return FALSE;
173 end if;
174 end;
175
176 --
177 -- Function
178 -- PARENT_IS_OBSOLETE
179 --
180 -- Purpose
181 -- Compare the version numbers of file in db with current source file.
182 --
183 -- RETURNS: TRUE if current file precedes the file version in DB,
184 -- FALSE otherwise.
185 --
186 FUNCTION PARENT_IS_OBSOLETE(P_DEST_TABLE in VARCHAR2,
187 P_OBJECT_NAME in VARCHAR2) RETURN BOOLEAN is
188
189
190 DB_age number;
191 key_id number;
192 LDR_Flag varchar2(1);
193
194 begin
195 if (P_DEST_TABLE = 'O') then
196 Select object_id, sysdate - LAST_UPDATE_DATE, IREP_LDR_INTERNAL_FLAG
197 into key_id, DB_age, LDR_Flag
198 from FND_OBJECTS
199 where obj_name = P_OBJECT_NAME;
200 else
201 Select class_id, sysdate - LAST_UPDATE_DATE, IREP_LDR_INTERNAL_FLAG
202 into key_id, DB_age, LDR_Flag
203 from FND_IREP_CLASSES
204 where class_name = P_OBJECT_NAME;
205 end if;
206
207 if (LDR_Flag = 'O') then
208 return TRUE;
209 else
210 return FALSE;
211 end if;
212 end;
213
214
215 PROCEDURE DELETE_COLLECTION is
216
217 begin
218
219 -- Delete collections
220 if v_fnd_form_functions is not null then
221 v_fnd_form_functions.DELETE;
222 end if;
223 if v_fnd_menu_entries is not null then
224 v_fnd_menu_entries.DELETE;
225 end if;
226 if v_fnd_menu_entries_tl is not null then
227 v_fnd_menu_entries_tl.DELETE;
228 end if;
229 if v_fnd_menus is not null then
230 v_fnd_menus.DELETE;
231 end if;
232 if v_fnd_menus_tl is not null then
233 v_fnd_menus_tl.DELETE;
234 end if;
235 if v_fnd_grants is not null then
236 v_fnd_grants.DELETE;
237 end if;
238
239 end delete_Collection;
240
241 --
242 -- Procedure
243 --
244 --
245 -- Purpose
246 -- Upload irep object
247 --
248 --
249 PROCEDURE UPLOAD_IREP_OBJECT( P_UPLOAD_MODE IN VARCHAR2,
250 P_OBJECT_NAME IN VARCHAR2,
251 P_DEST_TABLE IN VARCHAR2,
252 P_OWNER IN VARCHAR2,
253 P_API_NAME IN VARCHAR2,
254 P_OBJ_TYPE IN VARCHAR2,
255 P_PRODUCT IN VARCHAR2,
256 P_IMP_NAME IN VARCHAR2,
257 P_COMPATABILITY IN VARCHAR2,
258 P_SCOPE IN VARCHAR2,
259 P_LIFECYCLE IN VARCHAR2,
260 P_SOURCE_FILE_PRODUCT IN VARCHAR2,
261 P_SOURCE_FILE_PATH IN VARCHAR2,
262 P_SOURCE_FILE_NAME IN VARCHAR2,
263 P_SOURCE_FILE_VERSION IN VARCHAR2,
264 P_DESCRIPTION IN VARCHAR2,
265 P_STANDARD IN VARCHAR2,
266 P_STANDARD_VERSION IN VARCHAR2,
267 P_STANDARD_SPEC IN VARCHAR2,
268 P_DISPNAME IN VARCHAR2,
269 P_SHORTDISC IN VARCHAR2,
270 P_TIMESTAMP IN VARCHAR2,
271 P_OI_FLAG IN VARCHAR2,
272 P_MAPCODE IN VARCHAR2,
273 P_PARSER_VERSION IN VARCHAR2,
274 P_SDO_DEF_CLASS IN VARCHAR2,
275 P_SDO_CLASS_NAME IN VARCHAR2,
276 P_SDO_IS_FILTER IN VARCHAR2,
277 P_SDO_FILTER_REQUIRED IN VARCHAR2,
278 P_SDO_IS_EXPRESSION IN VARCHAR2,
279 P_SB_INTERFACE_CLASS IN VARCHAR2,
280 P_CRAWL_CRAWLABLE IN VARCHAR2,
281 P_CRAWL_VISIBILITY_LEVEL IN VARCHAR2,
282 P_CRAWL_SEARCH_PLUGIN IN VARCHAR2,
283 P_CRAWL_UI_FUNCTION IN VARCHAR2,
284 P_CRAWL_CHANGE_EVENT_NAME IN VARCHAR2,
285 P_CRAWL_CHANGE_NTF IN VARCHAR2,
286 P_CRAWL_DRIVING_TABLE IN VARCHAR2) IS
287
288 app_id number := 0;
289 f_luby number; -- entity owner in file
290 f_ludate date; -- entity update date in file
291 key_id number;
292
293 nice_prod varchar2(8);
294 nice_compat varchar2(1);
295 nice_scope varchar2(30);
296 nice_lifecy varchar2(30);
297 nice_sfprod varchar2(8);
298 nice_oiflag varchar2(1);
299 begin
300 -- Translate owner to file_last_updated_by
301 f_luby := fnd_load_util.OWNER_ID(P_OWNER);
302
303 -- Translate char last_update_date to date
304 f_ludate := nvl(to_date(P_TIMESTAMP, 'YYYY/MM/DD'), sysdate);
305
306 select application_id into app_id
307 from fnd_application
308 where application_short_name = 'FND';
309
310 begin
311 /* if source file version predates the version already in db, quit */
312 if (OBJ_IS_OBSOLETE(P_DEST_TABLE, P_OBJECT_NAME, P_SOURCE_FILE_VERSION)
313 AND (P_UPLOAD_MODE IS NULL OR P_UPLOAD_MODE <> 'NLS')) then
314 return;
315 end if;
316
317 if (P_DEST_TABLE = 'C') then
318 select CLASS_ID
319 into key_id
320 from fnd_irep_classes
321 where CLASS_NAME = P_OBJECT_NAME;
322 else
323 select OBJECT_ID
324 into key_id
325 from fnd_objects
326 where OBJ_NAME = P_OBJECT_NAME;
327 end if;
328
329 exception
330 when no_data_found then
331 null;
332 end;
333
334 if (P_UPLOAD_MODE = 'NLS') then
335 if (P_DEST_TABLE = 'C') then
336 UPDATE FND_IREP_CLASSES_TL
337 SET source_lang=userenv('LANG'),
338 DISPLAY_NAME = nvl(P_DISPNAME, DISPLAY_NAME),
339 SHORT_DESCRIPTION = nvl(P_SHORTDISC, SHORT_DESCRIPTION),
340 LAST_UPDATED_BY = f_luby,
341 LAST_UPDATE_DATE = f_ludate,
342 LAST_UPDATE_LOGIN = 0
343 WHERE userenv('LANG') in (language, source_lang)
344 AND CLASS_ID = key_id;
345 else
346 UPDATE FND_OBJECTS_TL
347 SET source_lang=userenv('LANG'),
348 DISPLAY_NAME = nvl(P_DISPNAME, DISPLAY_NAME),
349 DESCRIPTION = nvl(P_SHORTDISC, DESCRIPTION),
350 LAST_UPDATED_BY = f_luby,
351 LAST_UPDATE_DATE = f_ludate,
352 LAST_UPDATE_LOGIN = 0
353 where userenv('LANG') in (language, source_lang)
354 and OBJECT_ID = key_id;
355 end if;
356 else
357 begin
358 if (P_DEST_TABLE = 'C') then
359 update fnd_irep_classes
360 set IREP_NAME = P_API_NAME,
361 CLASS_TYPE = P_OBJ_TYPE,
362 PRODUCT_CODE = NVL(LOWER(P_PRODUCT), 'fnd'),
363 DEPLOYED_FLAG = 'N',
364 GENERATED_FLAG = 'N',
365 IMPLEMENTATION_NAME = P_IMP_NAME,
366 COMPATIBILITY_FLAG = NVL(UPPER(P_COMPATABILITY), 'S'),
367 SCOPE_TYPE = NVL(UPPER(P_SCOPE), 'PUBLIC'),
368 LIFECYCLE_MODE = NVL(UPPER(P_LIFECYCLE), 'ACTIVE'),
369 SOURCE_FILE_PRODUCT = UPPER(P_SOURCE_FILE_PRODUCT),
370 SOURCE_FILE_PATH = P_SOURCE_FILE_PATH,
371 SOURCE_FILE_NAME = P_SOURCE_FILE_NAME,
372 SOURCE_FILE_VERSION = P_SOURCE_FILE_VERSION,
373 DESCRIPTION = P_DESCRIPTION,
374 STANDARD_TYPE = P_STANDARD,
375 STANDARD_VERSION = P_STANDARD_VERSION,
376 STANDARD_SPEC = P_STANDARD_SPEC,
377 OPEN_INTERFACE_FLAG = UPPER(NVL(P_OI_FLAG,'N')),
378 last_updated_by = f_luby,
379 last_update_date = sysdate,
380 last_update_login = 0,
381 LOAD_ERR = 'N',
382 LOAD_ERR_MSGS = NULL,
383 MAP_CODE = P_MAPCODE,
384 INTERFACE_CLASS = P_SB_INTERFACE_CLASS,
385 IREP_LDR_PP_FLAG = DECODE(P_OBJ_TYPE, 'SERVICEBEAN','Y',NULL),
386 XML_DESCRIPTION = NULL
387 where CLASS_ID = key_id;
388
389 if (SQL%NOTFOUND) then
390 raise no_data_found;
391 end if;
392
393 -- remove class subentities (except methods)
394 Delete from FND_IREP_CLASS_DATASOURCES
395 where CLASS_ID = key_id;
396
397 Delete from FND_IREP_CLASS_PARENT_ASSIGNS
398 where CLASS_NAME = P_OBJECT_NAME;
399
400 Delete from FND_LOOKUP_ASSIGNMENTS
401 where obj_name = 'FND_IREP_CLASSES'
402 and INSTANCE_PK1_VALUE = to_char(key_id);
403
404 Delete from FND_CHILD_ANNOTATIONS
405 where parent_id = key_id
406 and parent_flag = 'C';
407
408 Delete from FND_IREP_USES_TABLES
409 where CLASS_ID = key_id;
410
411 Delete from FND_IREP_USES_MAPS
412 where CLASS_ID = key_id;
413
414 -- remove method subentities
415 Delete from FND_LOOKUP_ASSIGNMENTS
416 where obj_name = 'FND_IREP_FUNCTION_FLAVORS'
417 and INSTANCE_PK1_VALUE in
418 (select function_id
419 from FND_FORM_FUNCTIONS
420 where irep_class_id = key_id);
421
422 Delete from FND_CHILD_ANNOTATIONS
423 where parent_flag = 'F'
424 and parent_id in
425 (select function_id
426 from FND_FORM_FUNCTIONS
427 where irep_class_id = key_id);
428
429 Delete from FND_PARAMETERS
430 where function_id in
431 (select function_id
432 from FND_FORM_FUNCTIONS
433 where irep_class_id = key_id);
434
435 -- remove flavors
436 Delete from FND_IREP_FUNCTION_FLAVORS
437 where FUNCTION_ID in
438 (select function_id
439 from FND_FORM_FUNCTIONS
440 where irep_class_id = key_id);
441
442
443
444 -- Remove derived entries classes and functions
445
446 Delete from fnd_irep_classes
447 where assoc_class_id = key_id;
448 Delete from fnd_form_functions
449 where irep_class_id =
450 (select class_id
451 from fnd_irep_classes
452 where assoc_class_id = key_id);
453
454 --Fetch and save menus and grants
455 GET_DELETE_GRANTS(key_id);
456
457 else
458 update fnd_objects
459 set IREP_NAME = P_API_NAME,
460 IREP_OBJECT_TYPE = P_OBJ_TYPE,
461 IREP_PRODUCT = LOWER(P_PRODUCT),
462 IREP_COMPATIBILITY = NVL(UPPER(P_COMPATABILITY), 'S'),
463 IREP_SCOPE = NVL(UPPER(P_SCOPE), 'PUBLIC'),
464 IREP_LIFECYCLE = NVL(UPPER(P_LIFECYCLE), 'ACTIVE'),
465 IREP_SOURCE_FILE_PRODUCT = UPPER(P_SOURCE_FILE_PRODUCT),
466 IREP_SOURCE_FILE_PATH = P_SOURCE_FILE_PATH,
467 IREP_SOURCE_FILE_NAME = P_SOURCE_FILE_NAME,
468 IREP_SOURCE_FILE_VERSION = P_SOURCE_FILE_VERSION,
469 IREP_DESCRIPTION = P_DESCRIPTION,
470 IREP_STANDARD = P_STANDARD,
471 IREP_STANDARD_VERSION = P_STANDARD_VERSION,
472 IREP_STANDARD_SPEC = P_STANDARD_SPEC,
473 PK1_COLUMN_NAME = 'DUMMY_IREP',
474 PK1_COLUMN_TYPE = 'NUMBER',
475 last_updated_by = f_luby,
476 last_update_date = sysdate,
477 last_update_login = 0,
478 LOAD_ERR = 'N',
479 LOAD_ERR_MSGS = NULL,
480 IREP_DEF_CLASS = P_SDO_DEF_CLASS,
481 IREP_CLASS_NAME = P_SDO_CLASS_NAME,
482 IREP_FILTER_REQUIRED = NVL(P_SDO_FILTER_REQUIRED, 'N'),
483 IREP_IS_FILTER = NVL(P_SDO_IS_FILTER, 'N'),
484 IREP_IS_EXPRESSION = NVL(P_SDO_IS_EXPRESSION, 'N'),
485 CRAWL_CRAWLABLE = P_CRAWL_CRAWLABLE,
486 CRAWL_VISIBILITY_LEVEL = P_CRAWL_VISIBILITY_LEVEL,
487 CRAWL_SEARCH_PLUGIN = P_CRAWL_SEARCH_PLUGIN,
488 CRAWL_UI_FUNCTION = P_CRAWL_UI_FUNCTION,
489 CRAWL_CHANGE_EVENT_NAME = P_CRAWL_CHANGE_EVENT_NAME,
490 CRAWL_CHANGE_NOTIFICATION = P_CRAWL_CHANGE_NTF,
491 CRAWL_DRIVING_TABLE = P_CRAWL_DRIVING_TABLE,
492 IREP_LDR_PP_FLAG =
493 DECODE(P_OBJ_TYPE, 'SERVICEDOCUMENT', 'Y', NULL),
494 IREP_XML_DESCRIPTION = NULL
495 where OBJECT_ID = key_id;
496
497 if (SQL%NOTFOUND) then
498 raise no_data_found;
499 end if;
500
501 -- remove object subentities
502 Delete from FND_OBJECT_KEY_SETS
503 where object_id = key_id;
504
505 Delete from FND_LOOKUP_ASSIGNMENTS
506 where obj_name = 'FND_OBJECTS'
507 and INSTANCE_PK1_VALUE = to_char(key_id);
508
509 Delete from FND_CHILD_ANNOTATIONS
510 where parent_id = key_id
511 and parent_flag = 'O';
512
513 Delete from FND_OBJECT_TYPE_MEMBERS
514 where object_id = key_id;
515
516 end if;
517
518 if (P_DEST_TABLE = 'C') then
519 UPDATE FND_IREP_CLASSES_TL
520 SET SOURCE_LANG = userenv('LANG'),
521 DISPLAY_NAME = nvl(P_DISPNAME, DISPLAY_NAME),
522 SHORT_DESCRIPTION = nvl(P_SHORTDISC, SHORT_DESCRIPTION),
523 LAST_UPDATED_BY = f_luby,
524 LAST_UPDATE_DATE = f_ludate,
525 LAST_UPDATE_LOGIN = 0
526 where userenv('LANG') in (language, source_lang)
527 and CLASS_ID = key_id;
528
529 else
530 UPDATE FND_OBJECTS_TL
531 SET SOURCE_LANG = userenv('LANG'),
532 DISPLAY_NAME = nvl(P_DISPNAME, DISPLAY_NAME),
533 DESCRIPTION = nvl(P_SHORTDISC, DESCRIPTION),
534 LAST_UPDATED_BY = f_luby,
535 LAST_UPDATE_DATE = f_ludate,
536 LAST_UPDATE_LOGIN = 0
537 WHERE userenv('LANG') in (language, source_lang)
538 AND OBJECT_ID = key_id;
539 end if;
540
541 exception
542 when no_data_found then
543
544 select fnd_objects_s.nextval, NVL(LOWER(P_PRODUCT), 'fnd'),
545 NVL(UPPER(P_COMPATABILITY),'S'), NVL(UPPER(P_SCOPE), 'PUBLIC'),
546 NVL(UPPER(P_LIFECYCLE),'ACTIVE'), UPPER(P_SOURCE_FILE_PRODUCT),
547 UPPER(NVL(P_OI_FLAG, 'N'))
548 into key_id, nice_prod,
549 nice_compat, nice_scope,
550 nice_lifecy, nice_sfprod,
551 nice_oiflag
552 from dual;
553
554 if (P_DEST_TABLE = 'C') then
555 insert into fnd_irep_classes (
556 IREP_NAME, CLASS_TYPE, PRODUCT_CODE, DEPLOYED_FLAG,
557 GENERATED_FLAG, IMPLEMENTATION_NAME, COMPATIBILITY_FLAG,
558 SCOPE_TYPE, MAP_CODE,
559 LIFECYCLE_MODE,
560 SOURCE_FILE_PRODUCT,
561 SOURCE_FILE_PATH, SOURCE_FILE_NAME, SOURCE_FILE_VERSION,
562 DESCRIPTION, STANDARD_TYPE, STANDARD_VERSION, STANDARD_SPEC,
563 OPEN_INTERFACE_FLAG, INTERFACE_CLASS, last_updated_by,
564 last_update_date, last_update_login,
565 CREATION_DATE, CREATED_BY, CLASS_ID, CLASS_NAME,
566 IREP_LDR_PP_FLAG
567 ) VALUES (
568 P_API_NAME, P_OBJ_TYPE, nice_prod, 'N',
569 'N', P_IMP_NAME, nice_compat,
570 nice_scope, P_MAPCODE,
571 nice_lifecy,
572 nice_sfprod,
573 P_SOURCE_FILE_PATH, P_SOURCE_FILE_NAME,P_SOURCE_FILE_VERSION,
574 P_DESCRIPTION, P_STANDARD,P_STANDARD_VERSION,P_STANDARD_SPEC,
575 nice_oiflag, P_SB_INTERFACE_CLASS, f_luby,
576 sysdate, 0,
577 f_ludate, f_luby, key_id, P_OBJECT_NAME,
578 DECODE(P_OBJ_TYPE, 'SERVICEBEAN', 'Y', NULL)
579 );
580
581 INSERT INTO FND_IREP_CLASSES_TL (
582 CLASS_ID, DISPLAY_NAME, SHORT_DESCRIPTION, LANGUAGE,
583 SOURCE_LANG, LAST_UPDATE_DATE, LAST_UPDATED_BY,
584 CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
585 ) SELECT
586 key_id, P_DISPNAME, P_SHORTDISC, L.LANGUAGE_CODE,
587 USERENV('LANG'), f_ludate, f_luby, f_luby, f_ludate, 0
588 FROM FND_LANGUAGES L
589 WHERE L.INSTALLED_FLAG IN ('I','B')
590 AND NOT EXISTS
591 (SELECT NULL
592 FROM FND_IREP_CLASSES_TL T
593 WHERE T.CLASS_ID = key_id
594 AND T.LANGUAGE = l.language_code);
595
596 else
597 INSERT INTO FND_OBJECTS (
598 IREP_NAME, IREP_OBJECT_TYPE, IREP_PRODUCT,
599 IREP_COMPATIBILITY,
600 IREP_SCOPE,
601 IREP_LIFECYCLE,
602 IREP_DEF_CLASS,
603 IREP_CLASS_NAME,
604 IREP_IS_FILTER,
605 IREP_FILTER_REQUIRED,
606 IREP_IS_EXPRESSION,
607 IREP_SOURCE_FILE_PRODUCT, IREP_SOURCE_FILE_PATH,
608 IREP_SOURCE_FILE_NAME, IREP_SOURCE_FILE_VERSION,
609 IREP_DESCRIPTION, IREP_STANDARD, IREP_STANDARD_VERSION,
610 IREP_STANDARD_SPEC, PK1_COLUMN_NAME, PK1_COLUMN_TYPE,
611 LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
612 CREATION_DATE, CREATED_BY, OBJECT_ID, OBJ_NAME,
613 APPLICATION_ID, DATABASE_OBJECT_NAME,
614 CRAWL_CRAWLABLE,
615 CRAWL_VISIBILITY_LEVEL,
616 CRAWL_SEARCH_PLUGIN,
617 CRAWL_UI_FUNCTION,
618 CRAWL_CHANGE_EVENT_NAME,
619 CRAWL_CHANGE_NOTIFICATION,
620 CRAWL_DRIVING_TABLE,
621 IREP_LDR_PP_FLAG
622 ) VALUES (
623 P_API_NAME, P_OBJ_TYPE, nice_prod,
624 nice_compat,
625 nice_scope,
626 nice_lifecy,
627 P_SDO_DEF_CLASS,
628 P_SDO_CLASS_NAME,
629 NVL(P_SDO_IS_FILTER,'N'),
630 NVL(P_SDO_FILTER_REQUIRED,'N'),
631 NVL(P_SDO_IS_EXPRESSION,'N'),
632 nice_sfprod, P_SOURCE_FILE_PATH,
633 P_SOURCE_FILE_NAME, P_SOURCE_FILE_VERSION,
634 P_DESCRIPTION, P_STANDARD, P_STANDARD_VERSION,
635 P_STANDARD_SPEC, 'DUMMY_IREP', 'NUMBER',
636 f_luby, sysdate, 0,
637 f_ludate, f_luby, key_id, P_OBJECT_NAME,
638 app_id, 'INTERFACE',
639 P_CRAWL_CRAWLABLE,
640 P_CRAWL_VISIBILITY_LEVEL,
641 P_CRAWL_SEARCH_PLUGIN,
642 P_CRAWL_UI_FUNCTION,
643 P_CRAWL_CHANGE_EVENT_NAME,
644 P_CRAWL_CHANGE_NTF,
645 P_CRAWL_DRIVING_TABLE,
646 DECODE(P_OBJ_TYPE, 'SERVICEDOCUMENT', 'Y', NULL)
647 );
648
649 INSERT INTO FND_OBJECTS_TL (
650 object_id, display_name, description, LANGUAGE,
651 SOURCE_LANG, LAST_UPDATE_DATE, LAST_UPDATED_BY,
652 CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
653 ) SELECT
654 key_id, P_DISPNAME,P_SHORTDISC, L.LANGUAGE_CODE,
655 USERENV('LANG'), f_ludate, f_luby, f_luby, f_ludate, 0
656 FROM FND_LANGUAGES L
657 WHERE L.INSTALLED_FLAG IN ('I','B')
658 AND NOT EXISTS
659 (SELECT NULL
660 FROM FND_OBJECTS_TL T
661 WHERE T.OBJECT_ID = key_id
662 AND T.LANGUAGE = l.language_code);
663
664 end if;
665 end; -- update or insert ?
666 end if; -- NLS_MODE
667 end;
668
669
670
671
672 --
673 -- Procedure
674 -- UPLOAD_Parents
675 --
676 -- Purpose
677 -- Upload parents
678 --
679 --
680 PROCEDURE UPLOAD_PARENTS(P_UPLOAD_MODE IN VARCHAR2,
681 P_OBJECT_NAME IN VARCHAR2,
682 P_DEST_TABLE IN VARCHAR2,
683 P_PARENT_NAME IN VARCHAR2) is
684
685 begin -- UPLOAD PARENTS
686 if PARENT_IS_OBSOLETE(P_DEST_TABLE, P_OBJECT_NAME) then
687 return;
688 end if;
689
690 Insert into FND_IREP_CLASS_PARENT_ASSIGNS
691 (CLASS_NAME, PARENT_CLASS_NAME)
692 select P_OBJECT_NAME, P_PARENT_NAME from dual;
693 end;
694
695
696
697
698 --
699 -- Procedure
700 -- UPLOAD_OBJECT_CATEGORY
701 --
702 -- Purpose
703 -- Upload Object Category
704 --
705 PROCEDURE UPLOAD_OBJECT_CATEGORY( P_UPLOAD_MODE IN VARCHAR2,
706 P_OBJECT_NAME IN VARCHAR2,
707 P_DEST_TABLE IN VARCHAR2,
708 P_TYPE IN VARCHAR2,
709 P_CODE IN VARCHAR2,
710 P_SEQUENCE IN VARCHAR2) is
711
712 key_id number;
713 f_luby number; -- entity owner in file
714 f_ludate date; -- entity update date in file
715 begin
716 if PARENT_IS_OBSOLETE(P_DEST_TABLE, P_OBJECT_NAME) then
717 return;
718 end if;
719
720 if (P_DEST_TABLE = 'O') then
721 Select object_id, last_updated_by, last_update_date
722 into key_id, f_luby, f_ludate
723 from FND_OBJECTS
724 where obj_name = P_OBJECT_NAME;
725 else
726 Select class_id, last_updated_by, last_update_date
727 into key_id, f_luby, f_ludate
728 from FND_IREP_CLASSES
729 where class_name = P_OBJECT_NAME;
730 end if;
731
732 Insert into FND_LOOKUP_ASSIGNMENTS
733 (OBJ_NAME, INSTANCE_PK1_VALUE,
734 LOOKUP_TYPE, LOOKUP_CODE, LOOKUP_ASSIGNMENT_ID,
735 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
736 LAST_UPDATE_LOGIN, DISPLAY_SEQUENCE)
737 select DECODE(P_DEST_TABLE, 'C', 'FND_IREP_CLASSES', 'FND_OBJECTS'),
738 key_id,
739 P_TYPE, P_CODE, FND_LOOKUP_ASSIGNMENTS_S.nextval,
740 f_luby, f_ludate, f_luby, f_ludate,
741 0, P_SEQUENCE
742 from dual;
743 end;
744
745
746
747
748
749 --
750 -- Procedure
751 -- UPLOAD_OBJ_CHILD_ANNOTATIONS
752 --
753 -- Purpose
754 -- Upload Object Child Annotations
755 --
756 PROCEDURE UPLOAD_OBJ_CHILD_ANNOTATIONS( P_UPLOAD_MODE IN VARCHAR2,
757 P_OBJECT_NAME IN VARCHAR2,
758 P_DEST_TABLE IN VARCHAR2,
759 P_CHILD_FLAG IN VARCHAR2,
760 P_VALUE IN VARCHAR2) is
761
762 key_id number;
763 begin
764 if PARENT_IS_OBSOLETE(P_DEST_TABLE, P_OBJECT_NAME) then
765 return;
766 end if;
767
768 if (P_DEST_TABLE = 'O') then
769 Select object_id
770 into key_id
771 from FND_OBJECTS
772 where obj_name = P_OBJECT_NAME;
773 else
774 Select class_id
775 into key_id
776 from FND_IREP_CLASSES
777 where class_name = P_OBJECT_NAME;
778 end if;
779
780 Insert into FND_CHILD_ANNOTATIONS
781 (PARENT_ID, PARENT_FLAG, CHILD_FLAG, ANNOTATION_VALUE)
782 select key_id, P_DEST_TABLE, UPPER(P_CHILD_FLAG), P_VALUE
783 from dual;
784 end;
785
786
787
788
789
790 --
791 -- Procedure
792 -- UPLOAD_TYPE_MEMBERS
793 --
794 -- Purpose
795 -- Upload Type Members
796 --
797 PROCEDURE UPLOAD_TYPE_MEMBERS( P_UPLOAD_MODE IN VARCHAR2,
798 P_OBJECT_NAME IN VARCHAR2,
799 P_DEST_TABLE IN VARCHAR2,
800 P_SEQUENCE IN VARCHAR2,
801 P_INNERTYPE_SEQUENCE IN VARCHAR2,
802 P_MEMBER_NAME IN VARCHAR2,
803 P_TYPE IN VARCHAR2,
804 P_PRECISION IN VARCHAR2,
805 P_SIZE IN VARCHAR2,
806 P_SCALE IN VARCHAR2,
807 P_NULL_ALLOWED IN VARCHAR2,
808 P_DESCRIPTION IN VARCHAR2,
809 P_ATTR_SET IN VARCHAR2,
810 P_PRIMARY_KEY IN VARCHAR2,
811 P_TRANSLATABLE IN VARCHAR2,
812 P_COMPOSITE IN VARCHAR2,
813 P_DOMAIN_NAME IN VARCHAR2,
814 P_MEMBER_TYPE_NAME IN VARCHAR2,
815 P_SEARCH_CRITERIA_TYPE IN VARCHAR2,
816 P_ATTACHMENT IN VARCHAR2,
817 P_MIME_TYPE IN VARCHAR2,
818 P_DOMAIN_IMPLEMENTATION IN VARCHAR2,
819 P_IS_SORTABLE IN VARCHAR2,
820 P_CRAWL_IS_DATE_BASED IN VARCHAR2,
821 P_CRAWL_MEMBER_VIS_LVL IN VARCHAR2,
822 P_CRAWL_IS_DISPLAYED IN VARCHAR2,
823 P_CRAWL_UI_FPARAM_NAME IN VARCHAR2,
824 P_CRAWL_INDEXED IN VARCHAR2,
825 P_CRAWL_STORED IN VARCHAR2,
826 P_CRAWL_IS_SECURE IN VARCHAR2,
827 P_CRAWL_IS_TITLE IN VARCHAR2,
828 P_CRAWL_WEIGHT IN VARCHAR2) is
829
830 obj_id number;
831
832 begin
833 if PARENT_IS_OBSOLETE(P_DEST_TABLE, P_OBJECT_NAME) then
834 return;
835 end if;
836
837 SELECT object_id
838 INTO obj_id
839 FROM FND_OBJECTS
840 WHERE OBJ_NAME = P_OBJECT_NAME;
841
842 Insert into FND_OBJECT_TYPE_MEMBERS
843 (OBJECT_ID, MEMBER_SEQUENCE, INNERTYPE_SEQUENCE, MEMBER_NAME,
844 MEMBER_TYPE, MEMBER_PRECISION, NULL_ALLOWED, DESCRIPTION,
845 ATTRIBUTE_SET, PRIMARY_KEY, TRANSLATABLE,
846 COMPOSITE, MEMBER_SCALE, MEMBER_TYPE_NAME, SEARCH_CRITERIA_TYPE,
847 ATTACHMENT, MIME_TYPE, DOMAIN_NAME, DOMAIN_IMPLEMENTATION,
848 IS_SORTABLE, IS_DATE_BASED, VISIBILITY_LEVEL,
849 IS_DISPLAYED, UI_FUNC_PARAMETER_NAME, CRAWL_INDEXED,
850 CRAWL_STORED, IS_SECURE, IS_TITLE, WEIGHT
851 ) VALUES (
852 obj_id, P_SEQUENCE, P_INNERTYPE_SEQUENCE, P_MEMBER_NAME,
853 P_TYPE, P_PRECISION, P_NULL_ALLOWED, P_DESCRIPTION,
854 P_ATTR_SET, NVL(P_PRIMARY_KEY,'N'), NVL(P_TRANSLATABLE,'N'),
855 NVL(P_COMPOSITE,'N'),P_SCALE,P_MEMBER_TYPE_NAME,P_SEARCH_CRITERIA_TYPE,
856 NVL(P_ATTACHMENT,'N'),P_MIME_TYPE,P_DOMAIN_NAME,P_DOMAIN_IMPLEMENTATION,
857 NVL(P_IS_SORTABLE, 'N'), P_CRAWL_IS_DATE_BASED, P_CRAWL_MEMBER_VIS_LVL,
858 P_CRAWL_IS_DISPLAYED, P_CRAWL_UI_FPARAM_NAME, P_CRAWL_INDEXED,
859 P_CRAWL_STORED, P_CRAWL_IS_SECURE, P_CRAWL_IS_TITLE, P_CRAWL_WEIGHT
860 );
861 end;
862
863
864 --
865 -- Procedure
866 -- UPLOAD_USES_TABLE
867 --
868 -- Purpose
869 -- Upload Uses Table
870 --
871 PROCEDURE UPLOAD_USES_TABLE( P_UPLOAD_MODE IN VARCHAR2,
872 P_OBJECT_NAME IN VARCHAR2,
873 P_DEST_TABLE IN VARCHAR2,
874 P_TABLE_NAME IN VARCHAR2,
875 P_UT_SEQ IN VARCHAR2,
876 P_UT_DIRECTION IN VARCHAR2) is
877
878 begin -- UPLOAD USES_TABLE
879 if PARENT_IS_OBSOLETE(P_DEST_TABLE, P_OBJECT_NAME) then
880 return;
881 end if;
882
883 Insert into FND_IREP_USES_TABLES
884 (CLASS_ID, TABLE_USED, DISPLAY_SEQUENCE,
885 TABLE_DIRECTION)
886 select class_id, UPPER(P_TABLE_NAME), P_UT_SEQ,
887 UPPER(NVL(P_UT_DIRECTION, 'I'))
888 from FND_IREP_CLASSES
889 where CLASS_NAME = P_OBJECT_NAME;
890 end;
891
892
893 --
894 -- Procedure
895 -- UPLOAD_USES_MAP
896 --
897 -- Purpose
898 -- Upload Uses Map
899 --
900 PROCEDURE UPLOAD_USES_MAP( P_UPLOAD_MODE IN VARCHAR2,
901 P_OBJECT_NAME IN VARCHAR2,
902 P_DEST_TABLE IN VARCHAR2,
903 P_MAP_NAME IN VARCHAR2,
904 P_UM_SEQ IN VARCHAR2) is
905
906 begin -- UPLOAD USES_MAP
907 if PARENT_IS_OBSOLETE(P_DEST_TABLE, P_OBJECT_NAME) then
908 return;
909 end if;
910
911 Insert into FND_IREP_USES_MAPS
912 (CLASS_ID, MAP_USED, DISPLAY_SEQ)
913 select class_id, UPPER(P_MAP_NAME), P_UM_SEQ
914 from FND_IREP_CLASSES
915 where CLASS_NAME = P_OBJECT_NAME;
916 end;
917
918
919
920 --
921 -- Procedure
922 -- UPLOAD_CLASS_DATASOURCES
923 --
924 -- Purpose
925 -- Upload Class Datasources
926 --
927 PROCEDURE UPLOAD_CLASS_DATASOURCES(P_UPLOAD_MODE IN VARCHAR2,
928 P_OBJECT_NAME IN VARCHAR2,
929 P_DEST_TABLE IN VARCHAR2,
930 P_DATASOURCE_NAME IN VARCHAR2,
931 P_DEF_CLASS IN VARCHAR2,
932 P_QUERYABLE IN VARCHAR2,
933 P_UPDATEABLE IN VARCHAR2,
934 P_INSERTABLE IN VARCHAR2,
935 P_MERGEABLE IN VARCHAR2,
936 P_DELETEABLE IN VARCHAR2,
937 P_PROCESS_QNAME IN VARCHAR2,
938 P_QUERY_QNAME IN VARCHAR2) is
939
940 key_id number;
941
942 begin
943 if PARENT_IS_OBSOLETE(P_DEST_TABLE, P_OBJECT_NAME) then
944 return;
945 end if;
946
947 Select class_id
948 into key_id
949 from FND_IREP_CLASSES
950 where class_name = P_OBJECT_NAME;
951
952 Insert into FND_IREP_CLASS_DATASOURCES
953 (CLASS_ID, DATASOURCE_NAME, DEF_CLASS, QUERYABLE,
954 UPDATEABLE, INSERTABLE, MERGEABLE,
955 DELETEABLE, PROCESS_CTRLPROP_QNAME, QUERY_CTRLPROP_QNAME
956 ) VALUES (
957 key_id, P_DATASOURCE_NAME, P_DEF_CLASS, NVL(P_QUERYABLE, 'N'),
958 NVL(P_UPDATEABLE, 'N'), NVL(P_INSERTABLE, 'N'), NVL(P_MERGEABLE, 'N'),
959 NVL(P_DELETEABLE, 'N'), P_PROCESS_QNAME, P_QUERY_QNAME
960 );
961 end;
962
963
964 --
965 -- Procedure
966 -- UPLOAD_OBJ_KEY_SET
967 --
968 -- Purpose
969 -- Upload Object Key Set
970 --
971 PROCEDURE UPLOAD_OBJ_KEY_SET(P_UPLOAD_MODE IN VARCHAR2,
972 P_OBJECT_NAME IN VARCHAR2,
973 P_DEST_TABLE IN VARCHAR2,
974 P_KEY_SET_NAME IN VARCHAR2,
975 P_KEY_SET_SEQUENCE IN VARCHAR2,
976 P_KEY1_MBR_NAME IN VARCHAR2,
977 P_KEY2_MBR_NAME IN VARCHAR2,
978 P_KEY3_MBR_NAME IN VARCHAR2,
979 P_KEY4_MBR_NAME IN VARCHAR2,
980 P_KEY5_MBR_NAME IN VARCHAR2,
981 P_ALT1_MBR_NAME IN VARCHAR2,
982 P_ALT2_MBR_NAME IN VARCHAR2,
983 P_ALT3_MBR_NAME IN VARCHAR2,
984 P_ALT4_MBR_NAME IN VARCHAR2,
985 P_ALT5_MBR_NAME IN VARCHAR2) is
986
987 obj_id number;
988
989 begin
990 if PARENT_IS_OBSOLETE(P_DEST_TABLE, P_OBJECT_NAME) then
991 return;
992 end if;
993
994 SELECT object_id
995 INTO obj_id
996 FROM FND_OBJECTS
997 WHERE OBJ_NAME = P_OBJECT_NAME;
998
999 Insert into FND_OBJECT_KEY_SETS
1000 (OBJECT_ID, KEY_SET_NAME, KEY_SET_SEQUENCE,
1001 KEY1_MEMBER_NAME, ALT1_MEMBER_NAME,
1002 KEY2_MEMBER_NAME, ALT2_MEMBER_NAME,
1003 KEY3_MEMBER_NAME, ALT3_MEMBER_NAME,
1004 KEY4_MEMBER_NAME, ALT4_MEMBER_NAME,
1005 KEY5_MEMBER_NAME, ALT5_MEMBER_NAME
1006 ) VALUES (
1007 obj_id, P_KEY_SET_NAME, P_KEY_SET_SEQUENCE,
1008 P_KEY1_MBR_NAME, P_ALT1_MBR_NAME,
1009 P_KEY2_MBR_NAME, P_ALT2_MBR_NAME,
1010 P_KEY3_MBR_NAME, P_ALT3_MBR_NAME,
1011 P_KEY4_MBR_NAME, P_ALT4_MBR_NAME,
1012 P_KEY5_MBR_NAME, P_ALT5_MBR_NAME
1013 );
1014 end;
1015
1016
1017
1018 --
1019 -- Procedure
1020 -- UPLOAD_IREP_METHOD
1021 --
1022 -- Purpose
1023 -- Upload iRep Method
1024 --
1025 PROCEDURE UPLOAD_IREP_METHOD( P_UPLOAD_MODE IN VARCHAR2,
1026 P_OBJECT_NAME IN VARCHAR2,
1027 P_DEST_TABLE IN VARCHAR2,
1028 P_FUNCTION_NAME IN VARCHAR2,
1029 P_METHOD_NAME IN VARCHAR2,
1030 P_OVERLOAD_SEQ IN VARCHAR2,
1031 P_SCOPE IN VARCHAR2,
1032 P_LIFECYCLE IN VARCHAR2,
1033 P_DESCRIPTION IN VARCHAR2,
1034 P_COMPATABILITY IN VARCHAR2,
1035 P_SYNCHRO IN VARCHAR2,
1036 P_DIRECTION IN VARCHAR2,
1037 P_CTX_DEPENDENCE IN VARCHAR2,
1038 P_USER_FN_NAME IN VARCHAR2,
1039 P_SHORT_DESCRIPTION IN VARCHAR2,
1040 P_PRIMARY_FLAG IN VARCHAR2,
1041 P_INDIRECT_OP_FLAG IN VARCHAR2) is
1042
1043
1044 f_luby number; -- entity owner in file
1045 f_ludate date; -- entity update date in file
1046 key_id number;
1047 fn_id number;
1048 new_fn number;
1049 primary_flavor number;
1050
1051 nice_ctx varchar2(8);
1052 nice_compat varchar2(1);
1053 nice_scope varchar2(30);
1054 nice_lifecy varchar2(30);
1055 nice_synch varchar2(1);
1056 nice_direct varchar2(1);
1057 nice_sdescr varchar2(240);
1058
1059 begin
1060 if (P_UPLOAD_MODE = 'NLS') then
1061
1062 Select c.class_id, c.last_updated_by, c.last_update_date, f.function_id
1063 into key_id, f_luby, f_ludate, fn_id
1064 from FND_IREP_CLASSES c,
1065 FND_FORM_FUNCTIONS f
1066 where c.class_name = P_OBJECT_NAME
1067 and f.irep_class_id = c.class_id
1068 and f.function_name = P_FUNCTION_NAME;
1069
1070 update FND_FORM_FUNCTIONS_TL
1071 set source_lang=userenv('LANG'),
1072 USER_FUNCTION_NAME = nvl(P_USER_FN_NAME, USER_FUNCTION_NAME),
1073 DESCRIPTION = nvl(P_SHORT_DESCRIPTION, DESCRIPTION),
1074 last_updated_by = f_luby,
1075 last_update_date = f_ludate,
1076 last_update_login = 0
1077 where userenv('LANG') in (language, source_lang)
1078 and function_id = fn_id;
1079
1080 else
1081 begin
1082 if PARENT_IS_OBSOLETE(P_DEST_TABLE, P_OBJECT_NAME) then
1083 return;
1084 end if;
1085 new_fn := 0;
1086 primary_flavor := 0;
1087
1088 SELECT UPPER(NVL(P_CTX_DEPENDENCE, 'NONE')),
1089 NVL(UPPER(P_SCOPE),'PUBLIC'), NVL(UPPER(P_LIFECYCLE),'ACTIVE'),
1090 NVL(UPPER(P_COMPATABILITY), 'S'), UPPER(P_SYNCHRO),
1091 UPPER(P_DIRECTION), NVL(P_SHORT_DESCRIPTION,' ')
1092 INTO nice_ctx,
1093 nice_scope, nice_lifecy,
1094 nice_compat, nice_synch,
1095 nice_direct, nice_sdescr
1096 FROM DUAL;
1097 SELECT C.class_id, C.last_updated_by, C.last_update_date,
1098 F.FUNCTION_ID
1099 INTO key_id, f_luby, f_ludate, fn_id
1100 FROM FND_IREP_CLASSES C, FND_FORM_FUNCTIONS F
1101 WHERE class_name = P_OBJECT_NAME
1102 AND F.FUNCTION_NAME = P_FUNCTION_NAME;
1103 exception
1104 when no_data_found then
1105 Select class_id, last_updated_by, last_update_date,
1106 FND_FORM_FUNCTIONS_S.nextval, 1, 1
1107 into key_id, f_luby, f_ludate, fn_id, new_fn, primary_flavor
1108 from FND_IREP_CLASSES
1109 where class_name = P_OBJECT_NAME;
1110 Insert into FND_FORM_FUNCTIONS
1111 (FUNCTION_ID, FUNCTION_NAME, CREATION_DATE, CREATED_BY,
1112 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1113 TYPE,
1114 IREP_CLASS_ID, MAINTENANCE_MODE_SUPPORT, CONTEXT_DEPENDENCE,
1115 IREP_METHOD_NAME, IREP_OVERLOAD_SEQUENCE, IREP_SCOPE,
1116 IREP_LIFECYCLE, IREP_DESCRIPTION,
1117 IREP_COMPATIBILITY, IREP_SYNCHRO, IREP_DIRECTION)
1118 VALUES (fn_id, P_FUNCTION_NAME, f_ludate, f_luby,
1119 f_ludate, f_luby, 0,
1120 DECODE(P_INDIRECT_OP_FLAG, 'Y', 'SB_INDIRECT_OP', 'INTERFACE'),
1121 key_id, 'NONE', nice_ctx,
1122 P_METHOD_NAME, P_OVERLOAD_SEQ, nice_scope,
1123 nice_lifecy, P_DESCRIPTION,
1124 nice_compat, nice_synch, nice_direct);
1125
1126 Insert into FND_FORM_FUNCTIONS_TL
1127 (FUNCTION_ID, USER_FUNCTION_NAME, DESCRIPTION, language,
1128 source_lang, last_update_date, last_updated_by,
1129 created_by, creation_date, last_update_login
1130 ) select
1131 fn_id, P_USER_FN_NAME, P_SHORT_DESCRIPTION, l.language_code,
1132 USERENV('LANG'), f_ludate, f_luby, f_luby, f_ludate, 0
1133 from fnd_languages l
1134 where l.installed_flag in ('I','B')
1135 and not exists
1136 (select null
1137 from FND_FORM_FUNCTIONS_TL t
1138 where t.FUNCTION_ID = fn_id
1139 and t.language = l.language_code);
1140
1141 -- Restore earlier saved grants
1142 RESTORE_GRANTS(fn_id, P_FUNCTION_NAME);
1143
1144 end;
1145
1146 if (new_fn = 0) then
1147 if ((P_PRIMARY_FLAG = 'Y') OR (P_OVERLOAD_SEQ = 1)) then
1148 primary_flavor := 1;
1149 else
1150 primary_flavor := 0;
1151 end if;
1152
1153 -- update function and tl
1154 Update FND_FORM_FUNCTIONS
1155 set FUNCTION_NAME = P_FUNCTION_NAME,
1156 LAST_UPDATE_DATE = f_ludate,
1157 LAST_UPDATED_BY = f_luby,
1158 LAST_UPDATE_LOGIN = 0,
1159 IREP_CLASS_ID = key_id,
1160 MAINTENANCE_MODE_SUPPORT = 'NONE',
1161 CONTEXT_DEPENDENCE = DECODE(primary_flavor, 1,
1162 UPPER(NVL(P_CTX_DEPENDENCE, 'NONE')),
1163 CONTEXT_DEPENDENCE),
1164 IREP_OVERLOAD_SEQUENCE = DECODE(primary_flavor, 1,
1165 P_OVERLOAD_SEQ,
1166 IREP_OVERLOAD_SEQUENCE),
1167 IREP_SCOPE = DECODE(primary_flavor, 1,
1168 NVL(UPPER(P_SCOPE), 'PUBLIC'),
1169 IREP_SCOPE),
1170 IREP_LIFECYCLE = DECODE(primary_flavor, 1,
1171 NVL(UPPER(P_LIFECYCLE), 'ACTIVE'),
1172 IREP_LIFECYCLE),
1173 IREP_COMPATIBILITY = DECODE(primary_flavor, 1,
1174 NVL(UPPER(P_COMPATABILITY), 'S'),
1175 IREP_COMPATIBILITY),
1176 IREP_SYNCHRO = DECODE(primary_flavor, 1,
1177 UPPER(P_SYNCHRO),
1178 IREP_SYNCHRO),
1179 IREP_DIRECTION = DECODE(primary_flavor, 1,
1180 UPPER(P_DIRECTION),
1181 IREP_DIRECTION),
1182 TYPE = DECODE(P_INDIRECT_OP_FLAG, 'Y',
1183 'SB_INDIRECT_OP',
1184 'INTERFACE')
1185 WHERE FUNCTION_ID = fn_id;
1186
1187 if (primary_flavor = 1) then
1188 /* try to avoid ora-01461 */
1189 UPDATE FND_FORM_FUNCTIONS
1190 SET IREP_DESCRIPTION = P_DESCRIPTION
1191 WHERE FUNCTION_ID = fn_id;
1192
1193 UPDATE FND_FORM_FUNCTIONS_TL
1194 SET USER_FUNCTION_NAME = P_USER_FN_NAME,
1195 DESCRIPTION = P_SHORT_DESCRIPTION,
1196 last_update_date = f_ludate,
1197 last_updated_by = f_luby,
1198 last_update_login = 0
1199 WHERE FUNCTION_ID = fn_id
1200 and source_lang = USERENV('LANG');
1201 end if;
1202 end if;
1203
1204 -- insert flavor
1205 Insert into FND_IREP_FUNCTION_FLAVORS (
1206 FUNCTION_ID, OVERLOAD_SEQ, SCOPE_TYPE, LIFECYCLE_MODE, DESCRIPTION,
1207 COMPATIBILITY_FLAG, USER_FLAVOR_NAME, SHORT_DESCRIPTION
1208 ) VALUES (
1209 fn_id, P_OVERLOAD_SEQ, nice_scope, nice_lifecy, P_DESCRIPTION,
1210 nice_compat, P_USER_FN_NAME, nice_sdescr);
1211
1212 end if;
1213 end;
1214
1215
1216 --
1217 -- Procedure
1218 -- UPLOAD_METHOD_CATEGORY
1219 --
1220 -- Purpose
1221 -- Upload Method Category
1222 --
1223 PROCEDURE UPLOAD_METHOD_CATEGORY( P_UPLOAD_MODE IN VARCHAR2,
1224 P_OBJECT_NAME IN VARCHAR2,
1225 P_DEST_TABLE IN VARCHAR2,
1226 P_FUNCTION_NAME IN VARCHAR2,
1227 P_OVERLOAD_SEQ IN VARCHAR2,
1228 P_TYPE IN VARCHAR2,
1229 P_CODE IN VARCHAR2,
1230 P_SEQUENCE IN VARCHAR2) is
1231
1232 begin -- UPLOAD METHOD_CATEGORY
1233 if PARENT_IS_OBSOLETE(P_DEST_TABLE, P_OBJECT_NAME) then
1234 return;
1235 end if;
1236 Insert into FND_LOOKUP_ASSIGNMENTS
1237 (OBJ_NAME, INSTANCE_PK1_VALUE, INSTANCE_PK2_VALUE,
1238 LOOKUP_TYPE, LOOKUP_CODE, LOOKUP_ASSIGNMENT_ID, DISPLAY_SEQUENCE,
1239 CREATED_BY, CREATION_DATE,
1240 LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
1241 select 'FND_IREP_FUNCTION_FLAVORS', f.function_id, P_OVERLOAD_SEQ,
1242 P_TYPE, P_CODE, FND_LOOKUP_ASSIGNMENTS_S.nextval, P_SEQUENCE,
1243 C.LAST_UPDATED_BY, C.LAST_UPDATE_DATE,
1244 C.LAST_UPDATED_BY, C.LAST_UPDATE_DATE, 0
1245 from FND_IREP_CLASSES C,
1246 FND_FORM_FUNCTIONS F
1247 where c.class_name = P_OBJECT_NAME
1248 and f.irep_class_id = c.class_id
1249 and f.function_name = P_FUNCTION_NAME;
1250 end;
1251
1252
1253
1254 --
1255 -- Procedure
1256 -- UPLOAD_METHOD_CHILD_ANNOTATION
1257 --
1258 -- Purpose
1259 -- Upload Method Child Annotation
1260 --
1261 PROCEDURE UPLOAD_METHOD_CHILD_ANNOTATION(P_UPLOAD_MODE IN VARCHAR2,
1262 P_OBJECT_NAME IN VARCHAR2,
1263 P_DEST_TABLE IN VARCHAR2,
1264 P_FUNCTION_NAME IN VARCHAR2,
1265 P_OVERLOAD_SEQ IN VARCHAR2,
1266 P_CHILD_FLAG IN VARCHAR2,
1267 P_VALUE IN VARCHAR2) is
1268
1269 begin -- UPLOAD METHOD_CHILD_ANNOTATIONS
1270 if PARENT_IS_OBSOLETE(P_DEST_TABLE, P_OBJECT_NAME) then
1271 return;
1272 end if;
1273
1274 Insert into FND_CHILD_ANNOTATIONS
1275 (PARENT_ID, PARENT_ID2, PARENT_FLAG, CHILD_FLAG, ANNOTATION_VALUE)
1276 select f.function_id, P_OVERLOAD_SEQ, 'F', UPPER(P_CHILD_FLAG), P_VALUE
1277 from FND_IREP_CLASSES C,
1278 FND_FORM_FUNCTIONS F
1279 where c.class_name = P_OBJECT_NAME
1280 and f.irep_class_id = c.class_id
1281 and f.function_name = P_FUNCTION_NAME;
1282 end;
1283
1284
1285 --
1286 -- Procedure
1287 -- UPLOAD_PARAMS
1288 --
1289 -- Purpose
1290 -- Upload Parameters
1291 --
1292 PROCEDURE UPLOAD_PARAMS( P_UPLOAD_MODE IN VARCHAR2,
1293 P_OBJECT_NAME IN VARCHAR2,
1294 P_DEST_TABLE IN VARCHAR2,
1295 P_FUNCTION_NAME IN VARCHAR2,
1296 P_OVERLOAD_SEQ IN VARCHAR2,
1297 P_SEQUENCE IN VARCHAR2,
1298 P_INNERTYPE_SEQUENCE IN VARCHAR2,
1299 P_NAME IN VARCHAR2,
1300 P_DIRECTION IN VARCHAR2,
1301 P_OPTIONAL IN VARCHAR2,
1302 P_TYPE IN VARCHAR2,
1303 P_PRECISION IN VARCHAR2,
1304 P_SIZE IN VARCHAR2,
1305 P_SCALE IN VARCHAR2,
1306 P_NULL_ALLOWED IN VARCHAR2,
1307 P_DESCRIPTION IN VARCHAR2,
1308 P_DEFAULT_VALUE IN VARCHAR2,
1309 P_DISPLAYED IN VARCHAR2,
1310 P_ATTRIBUTE_SET IN VARCHAR2) is
1311
1312 fn_id number;
1313 nice_direct varchar2(1);
1314 nice_option varchar2(1);
1315 nice_nullok varchar2(1);
1316 nice_dispfl varchar2(1);
1317
1318 begin
1319 if PARENT_IS_OBSOLETE(P_DEST_TABLE, P_OBJECT_NAME) then
1320 return;
1321 end if;
1322
1323 SELECT f.function_id, UPPER(P_DIRECTION), UPPER(NVL(P_OPTIONAL,'N')),
1324 UPPER(P_NULL_ALLOWED), NVL(P_DISPLAYED, 'Y')
1325 INTO fn_id, nice_direct, nice_option,
1326 nice_nullok, nice_dispfl
1327 FROM FND_IREP_CLASSES C,
1328 FND_FORM_FUNCTIONS F
1329 WHERE C.CLASS_NAME = P_OBJECT_NAME
1330 AND F.IREP_CLASS_ID = C.CLASS_ID
1331 AND F.FUNCTION_NAME = P_FUNCTION_NAME;
1332
1333 Insert into FND_PARAMETERS
1334 (FUNCTION_ID, PARAM_SEQUENCE, INNERTYPE_SEQUENCE, PARAM_NAME,
1335 PARAM_DIRECTION, PARAM_OPTIONAL, PARAMETER_TYPE, PARAM_PRECISION,
1336 NULL_ALLOWED, DESCRIPTION, DEFAULT_VALUE,
1337 DISPLAYED_FLAG, FN_OVERLOAD_SEQUENCE, PARAM_SCALE, ATTRIBUTE_SET
1338 ) VALUES (
1339 fn_id, P_SEQUENCE, P_INNERTYPE_SEQUENCE, P_NAME,
1340 nice_direct, nice_option, P_TYPE, P_PRECISION,
1341 nice_nullok, P_DESCRIPTION, P_DEFAULT_VALUE,
1342 nice_dispfl, P_OVERLOAD_SEQ, P_SCALE, P_ATTRIBUTE_SET
1343 );
1344 end;
1345
1346
1347 --
1348 -- Procedure
1349 -- iRepPostProcess
1350 --
1351 -- Purpose
1352 -- Do various post processing to irep data. Currently just denormalizes
1353 -- inherited methods into child classes. This is called from the post
1354 -- processing java code which does various other post processing (such as
1355 -- updating schema entries).
1356 --
1357
1358 PROCEDURE iRepPostProcess as
1359
1360 Begin
1361 /* Keep copying methods to inheriting classes until no more to copy */
1362 LOOP
1363 Insert into FND_FORM_FUNCTIONS (
1364 irep_class_id,
1365 function_id,
1366 function_name,
1367 CREATION_DATE, CREATED_BY,
1368 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1369 TYPE,
1370 MAINTENANCE_MODE_SUPPORT, CONTEXT_DEPENDENCE,
1371 IREP_METHOD_NAME, IREP_OVERLOAD_SEQUENCE,
1372 IREP_SCOPE, IREP_LIFECYCLE,
1373 IREP_DESCRIPTION, IREP_COMPATIBILITY,
1374 IREP_SYNCHRO, IREP_DIRECTION)
1375 select child_c.class_id,
1376 fnd_form_functions_s.nextval,
1377 child_c.class_name || ':' || parent_m.irep_method_name,
1378 sysdate, parent_m.CREATED_BY,
1379 sysdate, parent_m.LAST_UPDATED_BY, parent_m.LAST_UPDATE_LOGIN,
1380 parent_m.TYPE,
1381 parent_m.MAINTENANCE_MODE_SUPPORT, parent_m.CONTEXT_DEPENDENCE,
1382 parent_m.IREP_METHOD_NAME, parent_m.IREP_OVERLOAD_SEQUENCE,
1383 parent_m.IREP_SCOPE, parent_m.IREP_LIFECYCLE,
1384 parent_m.IREP_DESCRIPTION, parent_m.IREP_COMPATIBILITY,
1385 parent_m.IREP_SYNCHRO, parent_m.IREP_DIRECTION
1386 from fnd_irep_class_parent_assigns ass,
1387 fnd_irep_classes parent_c,
1388 fnd_irep_classes child_c,
1389 fnd_form_functions parent_m
1390 where ass.Parent_class_name = Parent_c.class_name
1391 and ass.Class_name = Child_c.class_name
1392 and parent_m.irep_class_id = parent_c.class_id
1393 and not exists (
1394 select 1
1395 from fnd_form_functions child_m
1396 where child_m.irep_class_id = child_c.class_id
1397 and child_m.function_name =
1398 child_c.class_name || ':' || parent_m.irep_method_name);
1399
1400 EXIT WHEN (SQL%ROWCOUNT=0);
1401 END LOOP;
1402
1403 /* Insert TL shadow rows (for newly created base rows) */
1404 Insert into FND_FORM_FUNCTIONS_TL (
1405 FUNCTION_ID, USER_FUNCTION_NAME,
1406 DESCRIPTION, language,
1407 source_lang, last_update_date,
1408 last_updated_by, created_by,
1409 creation_date, last_update_login)
1410 select child_m.function_id, parent_tl.user_function_name,
1411 parent_tl.description, parent_tl.language,
1412 parent_tl.source_lang, sysdate,
1413 parent_tl.last_updated_by, parent_tl.created_by,
1414 sysdate, parent_tl.last_update_login
1415 from fnd_irep_class_parent_assigns ass,
1416 fnd_irep_classes parent_c,
1417 fnd_irep_classes child_c,
1418 fnd_form_functions parent_m,
1419 fnd_form_functions child_m,
1420 fnd_form_functions_tl parent_tl
1421 where ass.Parent_class_name = Parent_c.class_name
1422 and ass.Class_name = Child_c.class_name
1423 and parent_m.irep_class_id = parent_c.class_id
1424 and child_m.irep_class_id = child_c.class_id
1425 and child_m.function_name =
1426 child_c.class_name || ':' || parent_m.irep_method_name
1427 and parent_tl.function_id = parent_m.function_id
1428 and not exists (
1429 select 1
1430 from fnd_form_functions_tl child_tl
1431 where child_tl.function_id = child_m.function_id
1432 and child_tl.source_lang = parent_tl.source_lang
1433 and child_tl.language = parent_tl.language);
1434
1435 DELETE_COLLECTION();
1436 end;
1437
1438 procedure ADD_LANGUAGE
1439 is
1440 begin
1441
1442 insert into FND_IREP_CLASSES_TL (
1443 CLASS_ID,
1444 DISPLAY_NAME,
1445 SHORT_DESCRIPTION,
1446 LANGUAGE,
1447 SOURCE_LANG,
1448 LAST_UPDATE_DATE,
1449 LAST_UPDATED_BY,
1450 CREATED_BY,
1451 CREATION_DATE,
1452 LAST_UPDATE_LOGIN
1453 ) select
1454 B.CLASS_ID,
1455 B.DISPLAY_NAME,
1456 B.SHORT_DESCRIPTION,
1457 L.LANGUAGE_CODE,
1458 B.SOURCE_LANG,
1459 B.LAST_UPDATE_DATE,
1460 B.LAST_UPDATED_BY,
1461 B.CREATED_BY,
1462 B.CREATION_DATE,
1463 B.LAST_UPDATE_LOGIN
1464 from FND_IREP_CLASSES_TL B, FND_LANGUAGES L
1465 where L.INSTALLED_FLAG in ('I', 'B')
1466 and B.LANGUAGE = userenv('LANG')
1467 and not exists
1468 (select NULL
1469 from FND_IREP_CLASSES_TL T
1470 where T.CLASS_ID = B.CLASS_ID
1471 and T.LANGUAGE = L.LANGUAGE_CODE);
1472
1473 end ADD_LANGUAGE;
1474
1475 PROCEDURE GET_DELETE_GRANTS(key_id IN NUMBER) IS
1476 error1 varchar2(300);
1477 BEGIN
1478 -- Start Initialize Collections
1479 v_fnd_menu_entries:= t_fnd_menu_entries();
1480 v_fnd_menu_entries_tl := t_fnd_menu_entries_tl();
1481 v_fnd_menus := t_fnd_menus();
1482 v_fnd_menus_tl := t_fnd_menus_tl();
1483 v_fnd_grants := t_fnd_grants();
1484
1485 SELECT *bulk collect
1486 INTO v_fnd_form_functions
1487 FROM fnd_form_functions
1488 WHERE irep_class_id = key_id;
1489
1490
1491 if v_fnd_form_functions is not null and v_fnd_form_functions.count > 0 then
1492 FOR i IN v_fnd_form_functions.FIRST .. v_fnd_form_functions.LAST
1493 LOOP
1494 function_id := v_fnd_form_functions(i).function_id;
1495
1496 IF v_fnd_form_functions(i).function_id IS NOT NULL THEN
1497
1498
1499 -- temp table values are in v_fnd_menu_entries_new
1500 SELECT *bulk collect
1501 INTO v_fnd_menu_entries_new
1502 FROM fnd_menu_entries
1503 WHERE function_id = v_fnd_form_functions(i).function_id;
1504
1505 -- appended table values are in v_fnd_menu_entries
1506 v_fnd_menu_entries.extend(1);
1507 menu_entry_count := v_fnd_menu_entries.last;
1508
1509 if v_fnd_menu_entries_new is not null and v_fnd_menu_entries_new.count > 0 then
1510
1511 FOR k IN v_fnd_menu_entries_new.FIRST .. v_fnd_menu_entries_new.LAST
1512 LOOP
1513 v_fnd_menu_entries(menu_entry_count) := v_fnd_menu_entries_new(k);
1514 v_fnd_menu_entries.extend(1);
1515 menu_entry_count :=menu_entry_count+1;
1516 end loop;
1517
1518 end if;
1519
1520 IF v_fnd_menu_entries_new.COUNT > 0 THEN
1521 FOR j IN v_fnd_menu_entries_new.FIRST .. v_fnd_menu_entries_new.LAST
1522 LOOP
1523 IF v_fnd_menu_entries_new(j).menu_id IS NOT NULL THEN
1524
1525 SELECT *bulk collect
1526 INTO v_fnd_menu_entries_tl_new
1527 FROM fnd_menu_entries_tl
1528 WHERE menu_id = v_fnd_menu_entries_new(j).menu_id;
1529
1530 v_fnd_menu_entries_tl.extend(1);
1531 menu_entry_tl_count := v_fnd_menu_entries_tl.last;
1532 if v_fnd_menu_entries_tl_new is not null and v_fnd_menu_entries_tl_new.count > 0 then
1533
1534 FOR k IN v_fnd_menu_entries_tl_new.FIRST .. v_fnd_menu_entries_tl_new.LAST
1535 LOOP
1536 v_fnd_menu_entries_tl(menu_entry_tl_count) := v_fnd_menu_entries_tl_new(k);
1537 v_fnd_menu_entries_tl.extend(1);
1538 menu_entry_tl_count :=menu_entry_tl_count+1;
1539 end loop;
1540
1541 end if;
1542
1543 SELECT *bulk collect
1544 INTO v_fnd_menus_new
1545 FROM fnd_menus
1546 WHERE menu_id = v_fnd_menu_entries_new(j).menu_id;
1547
1548 v_fnd_menus.extend(1);
1549 menu_count := v_fnd_menus.last;
1550
1551 if v_fnd_menus_new is not null and v_fnd_menus_new.count > 0 then
1552
1553 FOR k IN v_fnd_menus_new.FIRST .. v_fnd_menus_new.LAST
1554 LOOP
1555 v_fnd_menus(menu_count) := v_fnd_menus_new(k);
1556 v_fnd_menus.extend(1);
1557 menu_count :=menu_count+1;
1558 end loop;
1559
1560 end if;
1561 FOR l IN v_fnd_menus_new.FIRST .. v_fnd_menus_new.LAST
1562 LOOP
1563
1564 IF v_fnd_menus_new(l).menu_id IS NOT NULL THEN
1565
1566 SELECT *bulk collect
1567 INTO v_fnd_menus_tl_new
1568 FROM fnd_menus_tl
1569 WHERE menu_id = v_fnd_menus_new(l).menu_id;
1570
1571 v_fnd_menus_tl.extend(1);
1572 menu_tl_count := v_fnd_menus_tl.last;
1573
1574 if v_fnd_menus_tl_new is not null and v_fnd_menus_tl_new.count > 0 then
1575 FOR k IN v_fnd_menus_tl_new.FIRST .. v_fnd_menus_tl_new.LAST
1576 LOOP
1577 v_fnd_menus_tl(menu_tl_count) := v_fnd_menus_tl_new(k);
1578 v_fnd_menus_tl.extend(1);
1579 menu_tl_count :=menu_tl_count+1;
1580 end loop;
1581 end if ;
1582 SELECT *bulk collect
1583 INTO v_fnd_grants_new
1584 FROM fnd_grants
1585 WHERE menu_id = v_fnd_menus_new(l).menu_id;
1586
1587 v_fnd_grants.extend(1);
1588 grants_count := v_fnd_grants.last;
1589
1590 if v_fnd_grants_new is not null and v_fnd_grants_new.count > 0 then
1591 FOR k IN v_fnd_grants_new.FIRST .. v_fnd_grants_new.LAST
1592 LOOP
1593 v_fnd_grants(grants_count) := v_fnd_grants_new(k);
1594 v_fnd_grants.extend(1);
1595 grants_count :=grants_count+1;
1596 end loop;
1597 end if;
1598
1599 FOR k IN v_fnd_grants_new.FIRST .. v_fnd_grants_new.LAST
1600 LOOP
1601 -- remove grants
1602 fnd_grants_pkg.delete_row(v_fnd_grants_new(k).grant_guid);
1603 END LOOP;
1604
1605 END IF;
1606
1607 -- remove menus
1608 fnd_menus_pkg.delete_row(v_fnd_menus_new(l).menu_id);
1609
1610 END LOOP;
1611 END IF;
1612
1613 -- remove menu_entries
1614 fnd_menu_entries_pkg.delete_row(v_fnd_menu_entries_new(j).menu_id, v_fnd_menu_entries(j).entry_sequence);
1615
1616 END LOOP;
1617 END IF;
1618
1619 END IF;
1620
1621 -- remove fucntions
1622 fnd_form_functions_pkg.delete_row(v_fnd_form_functions(i).function_id);
1623
1624 END LOOP;
1625 end if;
1626
1627
1628 --EXCEPTION
1629 -- WHEN others THEN
1630 -- DBMS_OUTPUT.PUT_LINE(sqlerrm);
1631 --IF sqlerrm IS NOT NULL THEN
1632 -- ROLLBACK;
1633 --ELSE
1634 -- COMMIT;
1635 --END IF;
1636
1637 END GET_DELETE_GRANTS;
1638
1639 -- Restore grants,menus, menu entries
1640 PROCEDURE RESTORE_GRANTS(f_id IN NUMBER, f_name IN VARCHAR) IS
1641
1642 t_menu_id fnd_menus.menu_id%TYPE;
1643 l_grant_guid raw(16);
1644 error1 varchar2(300);
1645 BEGIN
1646 --t_menu_id := fnd_menus_s.nextval;
1647 select fnd_menus_s.nextval into t_menu_id from dual;
1648 IF v_fnd_form_functions is not null and v_fnd_form_functions.COUNT > 0 THEN
1649
1650 FOR i IN v_fnd_form_functions.FIRST .. v_fnd_form_functions.LAST
1651 LOOP
1652 IF v_fnd_form_functions(i).function_name = f_name THEN
1653
1654 IF v_fnd_menu_entries is not null and v_fnd_menu_entries.count > 0 then
1655
1656 FOR k IN v_fnd_menu_entries.FIRST .. v_fnd_menu_entries.LAST
1657 LOOP
1658
1659 IF v_fnd_menu_entries(k).function_id = v_fnd_form_functions(i).function_id THEN
1660
1661 FOR j IN v_fnd_menu_entries_tl.FIRST .. v_fnd_menu_entries_tl.LAST
1662 LOOP
1663
1664 IF v_fnd_menu_entries_tl(j).menu_id = v_fnd_menu_entries(k).menu_id THEN
1665
1666 fnd_menu_entries_pkg.insert_row(x_rowid,
1667 t_menu_id,
1668 v_fnd_menu_entries(k).entry_sequence,
1669 v_fnd_menu_entries(k).sub_menu_id,
1670 f_id,
1671 v_fnd_menu_entries(k).grant_flag,
1672 v_fnd_menu_entries_tl(j).PROMPT,
1673 v_fnd_menu_entries_tl(j).description,
1674 v_fnd_menu_entries(k).creation_date,
1675 v_fnd_menu_entries(k).created_by,
1676 v_fnd_menu_entries(k).last_update_date,
1677 v_fnd_menu_entries(k).last_updated_by,
1678 v_fnd_menu_entries(k).last_update_login);
1679 EXIT
1680 WHEN v_fnd_menu_entries_tl(j).menu_id = v_fnd_menu_entries(k).menu_id;
1681
1682 END IF;
1683
1684 END LOOP;
1685 IF v_fnd_menus is not null and v_fnd_menus.COUNT > 0 THEN
1686 FOR m IN v_fnd_menus.FIRST .. v_fnd_menus.LAST
1687 LOOP
1688
1689 IF v_fnd_menus(m).menu_id = v_fnd_menu_entries(k).menu_id THEN
1690
1691 FOR j IN v_fnd_menus_tl.FIRST .. v_fnd_menus_tl.LAST
1692 LOOP
1693
1694 IF v_fnd_menus_tl(j).menu_id = v_fnd_menus(m).menu_id THEN
1695
1696 fnd_menus_pkg.insert_row(x_rowid,
1697 t_menu_id,
1698 'FND_FUNCTION_'||f_id,
1699 v_fnd_menus_tl(j).user_menu_name,
1700 v_fnd_menus(m).type,
1701 v_fnd_menus_tl(j).description,
1702 v_fnd_menus(m).creation_date,
1703 v_fnd_menus(m).created_by,
1704 v_fnd_menus(m).last_update_date,
1705 v_fnd_menus(m).last_updated_by,
1706 v_fnd_menus(m).last_update_login);
1707
1708 EXIT
1709 WHEN v_fnd_menus_tl(j).menu_id = v_fnd_menus(m).menu_id;
1710
1711 END IF;
1712
1713 END LOOP;
1714
1715 IF v_fnd_grants is not null and v_fnd_grants.COUNT > 0 THEN
1716
1717 FOR l IN v_fnd_grants.FIRST .. v_fnd_grants.LAST
1718 LOOP
1719
1720 IF v_fnd_grants(l).menu_id = v_fnd_menus(m).menu_id THEN
1721
1722 SELECT sys_guid()
1723 INTO l_grant_guid
1724 FROM dual;
1725
1726 fnd_grants_pkg.insert_row(x_rowid,
1727 l_grant_guid,
1728 v_fnd_grants(l).grantee_type,
1729 v_fnd_grants(l).grantee_key,
1730 t_menu_id,
1731 v_fnd_grants(l).start_date,
1732 v_fnd_grants(l).end_date,
1733 v_fnd_grants(l).object_id,
1734 v_fnd_grants(l).instance_type,
1735 v_fnd_grants(l).instance_set_id,
1736 v_fnd_grants(l).instance_pk1_value,
1737 v_fnd_grants(l).instance_pk2_value,
1738 v_fnd_grants(l).instance_pk3_value,
1739 v_fnd_grants(l).instance_pk4_value,
1740 v_fnd_grants(l).instance_pk5_value,
1741 v_fnd_grants(l).program_name,
1742 v_fnd_grants(l).program_tag,
1743 v_fnd_grants(l).creation_date,
1744 v_fnd_grants(l).created_by,
1745 v_fnd_grants(l).last_update_date,
1746 v_fnd_grants(l).last_updated_by,
1747 v_fnd_grants(l).last_update_login,
1748 v_fnd_grants(l).parameter1,
1749 v_fnd_grants(l).parameter2,
1750 v_fnd_grants(l).parameter3,
1751 v_fnd_grants(l).parameter4,
1752 v_fnd_grants(l).parameter5,
1753 v_fnd_grants(l).parameter6,
1754 v_fnd_grants(l).parameter7,
1755 v_fnd_grants(l).parameter8,
1756 v_fnd_grants(l).parameter9,
1757 v_fnd_grants(l).parameter10,
1758 v_fnd_grants(l).ctx_secgrp_id,
1759 v_fnd_grants(l).ctx_resp_id,
1760 v_fnd_grants(l).ctx_resp_appl_id,
1761 v_fnd_grants(l).ctx_org_id,
1762 v_fnd_grants(l).name,
1763 v_fnd_grants(l).description);
1764
1765 END IF;
1766
1767 END LOOP;
1768 END IF;
1769
1770 -- GRANTS
1771
1772 END IF;
1773
1774 END LOOP; --END MENUS
1775 END IF;
1776
1777
1778 END IF;
1779
1780 END LOOP; -- MENU ENTRIES
1781 END IF;
1782
1783
1784 END IF;
1785
1786 END LOOP; --FUCNTION CHECK
1787
1788 END IF;
1789
1790
1791 -- FUNCTION
1792
1793 --EXCEPTION
1794 --WHEN others THEN
1795 --DBMS_OUTPUT.PUT_LINE(sqlerrm);
1796 --IF sqlerrm IS NOT NULL THEN
1797 --ROLLBACK;
1798 --ELSE
1799 --COMMIT;
1800 --END IF;
1801
1802 END RESTORE_GRANTS;
1803
1804
1805 end FND_IREP_LOADER_PRIVATE;
1806