[Home] [Help]
PACKAGE BODY: APPS.FND_ODF_GEN
Source
1 PACKAGE BODY fnd_odf_gen AS
2 /* $Header: fndpodfb.pls 120.8 2012/04/05 00:24:32 jwsmith ship $ */
3 PROCEDURE odfgen_xml (p_objType IN VARCHAR2,
4 p_objName IN VARCHAR2,
5 p_schemaName IN VARCHAR2,
6 p_concatVar IN VARCHAR2,
7 p_appshortName IN VARCHAR2,
8 p_objMode IN VARCHAR2,
9 p_includeTrigger IN VARCHAR2,
10 p_includeSequence IN VARCHAR2,
11 p_includePolicy IN VARCHAR2,
12 p_objInfo OUT NOCOPY VARCHAR2,
13 p_policyCtr OUT NOCOPY NUMBER,
14 p_triggerCtr OUT NOCOPY NUMBER,
15 p_sequenceCtr OUT NOCOPY NUMBER,
16 p_sysName OUT NOCOPY NUMBER,
17 p_retXML OUT NOCOPY CLOB)
18
19 IS
20 l_objOpenHandle NUMBER;
21 l_objXMLOpenHandle NUMBER;
22 l_indexOpenHandle NUMBER;
23 l_indexOpenHandle1 NUMBER;
24 l_triggerOpenHandle NUMBER;
25 l_commentOpenHandle NUMBER;
26 l_commentCtr NUMBER;
27 l_ctr NUMBER;
28 l_finalXML CLOB;
29 l_tmpXML CLOB;
30 l_indexNameXML sys.XMLtype;
31 l_objXMLs sys.XMLType;
32 l_eobjXMLs sys.XMLType;
33 l_indexXMLs sys.XMLType;
34 l_triggerXMLs sys.XMLType;
35 l_sysNameXML sys.XMLType;
36 l_commentXMLs sys.XMLType;
37 l_policyXMLs sys.XMLType; --added by adusange
38 parsedItems sys.ku$_parsed_items;
39 l_tableName VARCHAR2(30);
40 l_buf VARCHAR2(32000);
41 l_tmpBuf VARCHAR2(30);
42 l_indexName VARCHAR2(50);
43 l_triggerName VARCHAR2(50);
44 l_mlogName VARCHAR2(30);
45 l_objName VARCHAR2(30);
46 l_consName VARCHAR2(32000);
47 l_subqry VARCHAR2(2000);
48 l_seqNameExp VARCHAR2(200);
49 l_depInfo VARCHAR2(2000);
50 p_typ VARCHAR2(30);
51 l_seqListing VARCHAR2(100);
52 l_policyListing VARCHAR2(100);
53 l_indexCtr NUMBER;
54 l_sqlCond VARCHAR2(2000);
55
56 TYPE NameTab IS TABLE OF sys.obj$.name%TYPE;
57 l_triggNames NameTab;
58 l_triggList VARCHAR2(2000);
59
60 e_dbms_metadata_01 EXCEPTION;
61 PRAGMA EXCEPTION_INIT(e_dbms_metadata_01, -31603); -- see bug #3108046
62 e_dbms_metadata_02 EXCEPTION;
63 PRAGMA EXCEPTION_INIT(e_dbms_metadata_02, -31608); -- regular exception
64
65 begin
66 l_indexCtr := 0;
67 p_triggerCtr := 0;
68 p_sysName := 0;
69 l_objOpenHandle := sys.ad_dbms_metadata.open(p_objType);
70 l_ctr := 0;
71
72 /* l_ctr := NVL(p_includeTrigger, 1); */
73
74 if ( p_includeTrigger is NULL ) then
75 l_ctr := 1;
76 end if;
77
78 l_depInfo := 'Primary Object''s Application Short Name : ' || '
79 '|| ' ' || p_appshortName || '
80 ';
81 l_depInfo := l_depInfo || 'If this application short name is incorrect ' ||
82 'please regenerate the xdf file by providing the parameter '||
83 'owner_app_shortname with the correct value.
84 ';
85
86 l_depInfo := l_depInfo || 'Primary Object Schema Name : ' || '
87 '|| ' ' || p_schemaName || '
88 ';
89
90 if ( p_objMode = 'policy' ) then
91 l_depInfo := l_depInfo || 'Primary Object Name : ' || '
92 '|| ' ' || p_includePolicy || '
93 ';
94 else
95 l_depInfo := l_depInfo || 'Primary Object Name : ' || '
96 '|| ' ' || p_objName || '
97 ';
98 end if;
99
100 l_depInfo := l_depInfo || 'Primary Object Type : ' || '
101 '|| ' ' || p_objType || '
102 ';
103
104 if ( p_objMode = 'mviewlog' or p_objMode = 'policy' ) then
105 sys.ad_dbms_metadata.set_filter(l_objOpenHandle,'BASE_OBJECT_SCHEMA',p_schemaName);
106 sys.ad_dbms_metadata.set_filter(l_objOpenHandle,'BASE_OBJECT_NAME',p_objName);
107 elsif (p_objMode = 'sequence' or p_objMode= 'trigger' or p_objMode = 'type') then
108 l_seqNameExp := 'LIKE ''' || p_objName || '''';
109 sys.ad_dbms_metadata.set_filter(l_objOpenHandle, 'NAME_EXPR',l_seqNameExp);
110 sys.ad_dbms_metadata.set_filter(l_objOpenHandle, 'SCHEMA', p_schemaName);
111 elsif (p_objMode = 'context' ) then
112 sys.ad_dbms_metadata.set_filter(l_objOpenHandle, 'NAME', p_objName);
113 else /* mview, table, index, aq_table, policies etc. */
114 sys.ad_dbms_metadata.set_filter(l_objOpenHandle, 'NAME', p_objName);
115 sys.ad_dbms_metadata.set_filter(l_objOpenHandle, 'SCHEMA', p_schemaName);
116 end if;
117
118 l_finalXML := '<ROWSET>
119 ';
120 l_tmpXML := '<APPL_SHORT_NAME>' || p_appshortName || '</APPL_SHORT_NAME>
121 ';
122 dbms_lob.append(l_finalXML,l_tmpXML);
123
124 if (p_concatVar IS NOT NULL) then
125 l_tmpXML := '<TYPE_APPL_SHORT_NAME>' || p_concatVar || '</TYPE_APPL_SHORT_NAME>
126 ';
127 else
128 l_tmpXML := '<TYPE_APPL_SHORT_NAME>0</TYPE_APPL_SHORT_NAME>
129 ';
130 end if;
131 dbms_lob.append(l_finalXML,l_tmpXML);
132
133 /* vkhatri bug 4929568 */
134 if ( p_objMode = 'type' ) then
135 l_tmpXML := '<APPS_TYPE>
136 ';
137 dbms_lob.append(l_finalXML,l_tmpXML);
138 get_type_attr(p_objName,p_schemaName,p_appshortName,l_tmpXML);
139 dbms_lob.append(l_finalXML,l_tmpXML);
140 get_type_method(p_objName,p_schemaName,p_appshortName,l_tmpXML);
141 dbms_lob.append(l_finalXML,l_tmpXML);
142 l_tmpXML := '</APPS_TYPE>
143 ';
144 dbms_lob.append(l_finalXML,l_tmpXML);
145 end if;
146
147 /* Extracting aol metadata for table */
148 if ( p_objMode = 'table' ) then
149 /* is_temp_iot( p_objName, p_schemaName, p_typ); // added support for aol metadata for IOT and GTT
150 if(p_typ = 'N') then */
151 l_tmpXML := '<APPS_AOL_METADATA>';
152 dbms_lob.append(l_finalXML,l_tmpXML);
153 get_fnd_table_metadata(p_objName,p_schemaName,p_appshortName,l_tmpXML);
154 dbms_lob.append(l_finalXML,l_tmpXML);
155 l_tmpXML := '</APPS_AOL_METADATA>
156 ';
157 dbms_lob.append(l_finalXML,l_tmpXML);
158 --end if;
159 end if;
160
161
162 /* Extracting aol metadata for views */
163 if ( p_objMode = 'view' ) then
164 get_fnd_view_metadata(p_objName,p_schemaName,p_appshortName,l_tmpXML);
165 dbms_lob.append(l_finalXML,l_tmpXML);
166 end if;
167
168 /* Extracting AOL metadata for mview */
169
170 if ( p_objMode = 'mview' ) then
171 l_tmpXML := '<APPS_AOL_METADATA>';
172 dbms_lob.append(l_finalXML,l_tmpXML);
173 get_fnd_mview_metadata(p_objName,p_schemaName,p_appshortName,l_tmpXML);
174 dbms_lob.append(l_finalXML,l_tmpXML);
175 l_tmpXML := '</APPS_AOL_METADATA>';
176 dbms_lob.append(l_finalXML,l_tmpXML);
177 end if;
178
179
180
181 if ( p_objMode = 'policy' ) then
182 get_xml_policy(p_objName,p_schemaName,p_includePolicy,p_policyCtr, l_policyListing,l_tmpXML);
183 if ( l_tmpXML is not null ) then
184 dbms_lob.append(l_finalXML,l_tmpXML);
185 end if;
186
187 else
188
189 LOOP
190 /* Main loop for master objects */
191 l_objXMLs := sys.ad_dbms_metadata.fetch_xml(l_objOpenHandle);
192 EXIT WHEN l_objXMLs IS NULL;
193
194 l_eobjXMLs := XMLType.extract(l_objXMLs,'ROWSET/ROW');
195
196 if ( p_objMode = 'table' ) then
197 /* check if the constraints are system generated */
198 l_sysNameXML := XMLType.extract(l_objXMLs,'ROWSET/ROW/TABLE_T/CON1_LIST/CON1_LIST_ITEM/NAME');
199 if (NOT l_sysNameXML IS NULL ) then
200 l_consName := l_sysNameXML.getStringVal();
201
202 if (instr(l_consName,'>SYS_') <> 0) then
203 p_sysName := 1;
204 end if;
205 end if;
206
207 if ( p_sysName <> 1 ) then
208 l_sysNameXML := XMLType.extract(l_objXMLs,'ROWSET/ROW/TABLE_T/CON2_LIST/CON2_LIST_ITEM/NAME');
209 if (NOT l_sysNameXML IS NULL ) then
210 l_consName := l_sysNameXML.getStringVal();
211 if (instr(l_consName,'>SYS_') <> 0) then
212 p_sysName := 1;
213 end if;
214 end if;
215 end if;
216
217 end if;
218
219
220 l_tmpXML := l_eobjXMLs.getClobVal();
221 dbms_lob.append(l_finalXML,l_tmpXML);
222
223 if( p_objMode <> 'index' and
224 p_objMode <> 'policy' and
225 p_objMode <> 'context' and
226 p_objMode <> 'sequence' and
227 p_objMode <> 'type' ) then
228 l_depInfo := l_depInfo || 'Dependent Object Information : ' || '
229 ';
230 end if;
231
232 /* Mladena - for accurate MV Log name */
233
234 if ( p_objMode in ('mview', 'mviewlog') ) then
235
236 begin
237 select log_table
238 into l_mlogName
239 from dba_mview_logs
240 where master = p_objName
241 and log_owner = p_schemaName;
242 exception
243 when no_data_found then
244 l_mlogName := null;
245 WHEN OTHERS
246 THEN RAISE_APPLICATION_ERROR(
247 -20001, 'Internal data error', TRUE);
248 end;
249
250 end if;
251
252 /*--- End Mladena */
253
254 /* ================================================================ */
255 if ( p_objMode = 'mview' ) then
256 /* reusing the l_indexOpenHandle to get materialized view log */
257 l_indexOpenHandle := sys.ad_dbms_metadata.open('MATERIALIZED_VIEW_LOG');
258 sys.ad_dbms_metadata.set_filter(l_indexOpenHandle,'BASE_OBJECT_SCHEMA',p_schemaName);
259 sys.ad_dbms_metadata.set_filter(l_indexOpenHandle,'BASE_OBJECT_NAME',p_objName);
260 LOOP
261 -- Amit Code.
262 BEGIN
263 l_indexXMLs := sys.ad_dbms_metadata.fetch_xml(l_indexOpenHandle);
264 EXIT WHEN l_indexXMLs IS NULL;
265 EXCEPTION
266 WHEN e_dbms_metadata_01 OR e_dbms_metadata_02
267 THEN EXIT;
268 WHEN OTHERS
269 THEN RAISE_APPLICATION_ERROR(
270 -20001, 'Internal sys.ad_dbms_metadata.fetch_xml error', TRUE);
271 END;
272
273 l_depInfo := l_depInfo || 'Materialized view log : ' || '
274 ' || ' ' || l_mlogName
275 || '
276 ';
277
278 l_eobjXMLs := XMLType.extract(l_indexXMLs,'ROWSET/ROW');
279 l_tmpXML := l_eobjXMLs.getClobVal();
280 dbms_lob.append(l_finalXML,l_tmpXML);
281
282 /* get the indexes on mviewlogs */
283 l_indexOpenHandle1 := sys.ad_dbms_metadata.open('INDEX');
284 l_depInfo := l_depInfo || 'Indexes on Materialized view log of ' || p_objMode
285 || ' : ' || '
286 ';
287 sys.ad_dbms_metadata.set_filter(l_indexOpenHandle1,'BASE_OBJECT_SCHEMA',p_schemaName);
288 sys.ad_dbms_metadata.set_filter(l_indexOpenHandle1,'BASE_OBJECT_NAME', l_mlogName);
289 sys.ad_dbms_metadata.set_filter(l_indexOpenHandle1,'SYSTEM_GENERATED',false);
290 --Amit Code.
291
292 LOOP
293 BEGIN
294 l_indexXMLs := sys.ad_dbms_metadata.fetch_xml(l_indexOpenHandle1);
295 EXIT WHEN l_indexXMLs IS NULL;
296 EXCEPTION
297 WHEN e_dbms_metadata_01 OR e_dbms_metadata_02
298 THEN l_indexCtr := 0; EXIT;
299 WHEN OTHERS
300 THEN RAISE_APPLICATION_ERROR(
301 -20001, 'Internal sys.ad_dbms_metadata.fetch_xml error', TRUE);
302 END;
303
304 l_eobjXMLs := XMLType.extract(l_indexXMLs,'ROWSET/ROW');
305
306 SELECT extractValue(l_indexXMLs,'ROWSET/ROW/INDEX_T/SCHEMA_OBJ/NAME')
307 INTO l_indexName
308 FROM DUAL;
309 l_tmpXML := l_eobjXMLs.getClobVal();
310 if (instr(l_indexName,'I_SNAP$')= 0) then
311 l_indexCtr := l_indexCtr + 1;
312 l_depInfo := l_depInfo || ' ' || l_indexName || '
313 ';
314 dbms_lob.append(l_finalXML,l_tmpXML);
315 end if;
316 END LOOP; -- end of index loop.
317
318 if ( l_indexCtr = 0 ) then
319 l_depInfo := l_depInfo || ' None ' || '
320 ';
321 end if;
322
323 sys.ad_dbms_metadata.close(l_indexOpenHandle1);
324
325 END LOOP; --end of mview loop
326
327 sys.ad_dbms_metadata.close(l_indexOpenHandle);
328 end if; -- end of if(mview) stmt.
329 /* ================================================================ */
330 /* rsekaran
331 - 4486719
332 - Commented the check for qtable so that index objects are captured for Qtable
333 */
334 if ( p_objMode <> 'sequence' and
335 p_objMode <> 'trigger' and
336 -- p_objmode <> 'qtable' and
337 p_objmode <> 'queue' and
338 p_objmode <> 'context' and
339 -- p_objmode <> 'view' and
340 p_objmode <> 'index' and
341 p_objMode <> 'type' ) then
342 /* Get the index information */
343 l_indexOpenHandle := sys.ad_dbms_metadata.open('INDEX');
344 l_depInfo := l_depInfo || 'Indexes on ' || p_objName
345 || ' : ' || '
346 ';
347 sys.ad_dbms_metadata.set_filter(l_indexOpenHandle,'BASE_OBJECT_SCHEMA',p_schemaName);
348
349 if ( p_objMode <> 'mviewlog' ) then
350 sys.ad_dbms_metadata.set_filter(l_indexOpenHandle,'BASE_OBJECT_NAME',p_objName);
351 /* rsekaran - 4486719
352 Added additional checks for qtables to filter out index names starting with 'AQ$_'
353 */
354 if ( p_objMode = 'qtable' ) then
355 l_sqlCond := ' not in ( select index_name from dba_indexes where (index_type=''DOMAIN'' or index_name like ''AQ$_%'') and table_name='''||p_objName||''' and owner='''||p_schemaName||''' ) ';
356 else
357 l_sqlCond := ' not in ( select index_name from dba_indexes where index_type=''DOMAIN'' and table_name='''||p_objName||''' and owner='''||p_schemaName||''' ) ';
358 end if;
359 else
360 sys.ad_dbms_metadata.set_filter(l_indexOpenHandle,'BASE_OBJECT_NAME', l_mlogName);
361 l_sqlCond := ' not in ( select index_name from dba_indexes where index_type=''DOMAIN'' and table_name='''||l_mlogName||''' and owner='''||p_schemaName||''' ) ';
362 end if;
363
364 sys.ad_dbms_metadata.set_filter(l_indexOpenHandle,'SYSTEM_GENERATED',false);
365
366 /* Mladena - no domain indexes until bug#3225530 backport is available */
367
368 sys.ad_dbms_metadata.set_filter(l_indexOpenHandle,'NAME_EXPR', l_sqlCond);
369
370 /* end Mladena */
371
372 LOOP
373 -- Amit Code
374 begin
378 when others then
375 l_indexXMLs := sys.ad_dbms_metadata.fetch_xml(l_indexOpenHandle);
376 EXIT WHEN l_indexXMLs IS NULL;
377 EXCEPTION
379 l_indexCtr := 0;
380 EXIT;
381 end;
382
383 l_eobjXMLs := XMLType.extract(l_indexXMLs,'ROWSET/ROW');
384 SELECT extractValue(l_indexXMLs,'ROWSET/ROW/INDEX_T/SCHEMA_OBJ/NAME')
385 INTO l_indexName
386 FROM DUAL;
387
388
389 -- l_indexNameXML := XMLType.extract(l_indexXMLs,'ROWSET/ROW/INDEX_T/SCHEMA_OBJ/NAME');
390 -- l_indexName := l_indexNameXML.getStringVal();
391 l_tmpXML := l_eobjXMLs.getClobVal();
392 if (instr(l_indexName,'I_SNAP$')= 0) then
393 l_depInfo := l_depInfo || ' ' || l_indexName || '
394 ';
395 l_indexCtr := l_indexCtr + 1;
396 dbms_lob.append(l_finalXML,l_tmpXML);
397 end if;
398 END LOOP;
399
400 if ( l_indexCtr = 0 ) then
401 l_depInfo := l_depInfo || ' None ' || '
402 ';
403 end if;
404
405 sys.ad_dbms_metadata.close(l_indexOpenHandle);
406
407
408 /* Get the trigger information - Mladena */
409
410 if ( p_objMode <> 'mviewlog' ) then
411 l_objName := p_objName;
412 else
413 l_objName := l_mlogName;
414 end if;
415
416 Begin
417
418 select ot.name BULK COLLECT INTO l_triggNames
419 from sys.obj$ ot, sys.obj$ bo, sys.trigger$ t
420 where t.BASEOBJECT = bo.OBJ#
421 and bo.NAME = l_objName
422 and ot.OBJ# = t.OBJ#;
423
424 exception
425 when no_data_found then
426 l_triggList := 'Empty';
427 End;
428
429 if l_triggNames.COUNT > 0 then
430
431 l_triggList := ''''||l_triggNames(l_triggNames.FIRST)||'''';
432
433 for i in l_triggNames.FIRST+1 .. l_triggNames.LAST loop
434
435 l_triggList := l_triggList||','''||l_triggNames(i)||'''';
436
437 end loop;
438
439 l_subqry := 'IN ('|| l_triggList ||')';
440
441 l_triggerOpenHandle := sys.ad_dbms_metadata.open('TRIGGER');
442 l_depInfo := l_depInfo || 'Triggers on ' || l_objName || ' : ' || '';
443
444 sys.ad_dbms_metadata.set_filter(l_triggerOpenHandle,'NAME_EXPR',l_subqry);
445 sys.ad_dbms_metadata.set_filter(l_triggerOpenHandle,'SYSTEM_GENERATED',false);
446 sys.ad_dbms_metadata.set_parse_item(l_triggerOpenHandle,'NAME');
447
448 LOOP
449 -- Amit Code.
450 begin
451 l_triggerXMLs := sys.ad_dbms_metadata.fetch_xml(l_triggerOpenHandle);
452 EXIT WHEN l_triggerXMLs IS NULL;
453 EXCEPTION
454 when others then
455 p_triggerCtr := 0;
456 EXIT;
457 end;
458 l_eobjXMLs := XMLType.extract(l_triggerXMLs,'ROWSET/ROW');
459
460 SELECT extractValue(l_triggerXMLs,'ROWSET/ROW/TRIGGER_T/SCHEMA_OBJ/NAME')
461 INTO l_triggerName
462 FROM DUAL;
463
464 if ((l_ctr = 1) or (instr(p_includeTrigger,l_triggerName) <> 0)) then
465 l_tmpXML := l_eobjXMLs.getClobVal();
466 dbms_lob.append(l_finalXML,l_tmpXML);
467 l_depInfo := l_depInfo || ' ' || l_triggerName || '
468 ';
469 p_triggerCtr := p_triggerCtr + 1;
470 end if;
471 END LOOP;
472
473 if ( p_triggerCtr = 0 ) then
474 l_depInfo := l_depInfo || ' None ' || '
475 ';
476 end if;
477
478 sys.ad_dbms_metadata.close(l_triggerOpenHandle);
479
480 else
481
482 l_depInfo := l_depInfo || ' None ' || '
483 ';
484
485 end if;
486
487 end if;
488
489
490
491 /* If the objType is table then try to get the sequence on it */
492
493 if ( p_objMode = 'table' ) then
494 l_depInfo := l_depInfo || 'Sequence(s) on ' || p_objName || ' : ' ||'
495 ';
496 get_xml_sequence(p_includeSequence,p_schemaName,p_sequenceCtr, l_seqListing,l_tmpXML);
497 if(p_sequenceCtr = 0 ) then
498 l_depInfo := l_depInfo || ' None ' || '
499 ' ;
500 else
501 l_depInfo := l_depInfo || l_seqListing ;
502 end if;
503 if ( l_tmpXML is not null ) then
504 dbms_lob.append(l_finalXML,l_tmpXML);
505 end if;
506 end if;
507
508 -- start of adusange code for policies generation.
509
510 if ( p_objMode = 'table' or p_objMode = 'view' or p_objMode = 'synonym' ) then
511 l_depInfo := l_depInfo || 'Policy(ies) on ' || p_objName || ' : ' || '
512 ';
513
514 -- Added by bhuvana. The p_includePolicy will contain the list of policies to include
515 get_xml_policy(p_objName,p_schemaName,p_includePolicy,p_policyCtr, l_policyListing,l_tmpXML);
516 if(p_policyCtr = 0 ) then
517 l_depInfo := l_depInfo || ' None ' || ' ';
518 else
522 dbms_lob.append(l_finalXML,l_tmpXML);
519 l_depInfo := l_depInfo || l_policyListing || ' ';
520 end if;
521 if ( l_tmpXML is not null ) then
523 end if;
524 end if;
525 --end of adusange code.
526
527
528 /* get the comment Information */
529
530
531 l_commentOpenHandle := sys.ad_dbms_metadata.open('COMMENT');
532 sys.ad_dbms_metadata.set_filter(l_commentOpenHandle,'BASE_OBJECT_SCHEMA',p_schemaName);
533 sys.ad_dbms_metadata.set_filter(l_commentOpenHandle,'BASE_OBJECT_NAME',p_objName);
534 l_commentCtr := 0;
535
536 LOOP
537 begin
538 l_commentXMLs := sys.ad_dbms_metadata.fetch_xml(l_commentOpenHandle);
539 EXIT WHEN l_commentXMLs IS NULL;
540 EXCEPTION
541 when others then
542 EXIT;
543 end;
544 l_commentCtr := l_commentCtr + 1;
545 if ( l_commentCtr = 1 ) then
546 dbms_lob.append(l_finalXML,'<ROW>
547 ');
548 end if;
549 l_eobjXMLs := XMLType.extract(l_commentXMLs,'ROWSET/ROW/COMMENT_T');
550 l_tmpXML := l_eobjXMLs.getClobVal();
551 dbms_lob.append(l_finalXML,l_tmpXML);
552 END LOOP;
553
554 if ( l_commentCtr <> 0 ) then
555 dbms_lob.append(l_finalXML,'</ROW>
556 ');
557 end if;
558
559 sys.ad_dbms_metadata.close(l_commentOpenHandle);
560
561
562
563 /*
564 get_ddl_comment( p_objName,p_schemaName, l_tmpXML);
565 if ( l_tmpXML is not null ) then
566 dbms_lob.append(l_finalXML,l_tmpXML);
567 end if;
568 */
569
570 END LOOP;
571
572 end if;
573
574 l_tmpXML := '</ROWSET>
575 ';
576 dbms_lob.append(l_finalXML,l_tmpXML);
577 sys.ad_dbms_metadata.close(l_objOpenHandle);
578 p_objInfo := l_depInfo;
579 p_retXML := l_finalXML;
580
581
582 end odfgen_xml;
583
584
585 PROCEDURE get_xml_sequence(p_seqNameList IN VARCHAR2,
586 p_schemaName IN VARCHAR2,
587 p_seqCount OUT NOCOPY NUMBER,
588 p_SeqListing OUT NOCOPY VARCHAR2,
589 p_retVal OUT NOCOPY CLOB)
590 IS
591 l_objXMLOpenHandle NUMBER;
592 l_tablen BINARY_INTEGER;
593 l_seqList DBMS_UTILITY.UNCL_ARRAY;
594 finalInSt VARCHAR2(1000);
595 l_finalXML CLOB;
596 l_tmpXML CLOB;
597 l_eobjXMLs sys.XMLType;
598 l_sequenceXML sys.XMLType;
599 l_SeqName VARCHAR2(50);
600
601 BEGIN
602
603 /* the object name consists of comma separated sequence name */
604 /* Use it to form the in statement for set_filter method */
605 /* get in a pl/sql table */
606 p_seqCount := 0;
607 finalInSt := 'IN (';
608 DBMS_UTILITY.COMMA_TO_TABLE(p_seqNameList,l_tabLen,l_seqList);
609 for i in 1..l_tabLen loop
610 finalInSt := finalInSt || '''' || l_seqList(i) || '''';
611 if ( i <> l_tabLen ) then
612 finalInSt := finalInSt || ',';
613 end if;
614 end loop;
615 finalInSt := finalInSt || ')';
616
617 l_objXMLOpenHandle := sys.ad_dbms_metadata.open('SEQUENCE');
618 sys.ad_dbms_metadata.set_filter(l_objXMLOpenHandle,'SCHEMA',p_schemaName);
619 sys.ad_dbms_metadata.set_filter(l_objXMLOpenHandle,'NAME_EXPR',finalInSt);
620
621 LOOP
622 --Amit Code.
623 begin
624 l_sequenceXML := sys.ad_dbms_metadata.fetch_xml(l_objXMLOpenHandle);
625 EXIT WHEN l_sequenceXML IS NULL;
626 EXCEPTION
627 when others then
628 p_seqCount := 0;
629 EXIT;
630 end;
631 p_seqCount := p_seqCount + 1;
632 l_eobjXMLs := XMLType.extract(l_sequenceXML,'ROWSET/ROW');
633 SELECT extractValue(l_sequenceXML,'ROWSET/ROW/SEQUENCE_T/SCHEMA_OBJ/NAME')
634 INTO l_SeqName
635 FROM DUAL;
636 p_SeqListing := p_SeqListing || ' ' || l_SeqName || '
637 ';
638 l_tmpXML := l_eobjXMLs.getClobVal();
639 if ( l_finalXML is null ) then
640 l_finalXML := l_tmpXML;
641 else
642 dbms_lob.append(l_finalXML,l_tmpXML);
643 end if;
644 END LOOP;
645
646 sys.ad_dbms_metadata.close(l_objXMLOpenHandle);
647 p_retVal := l_finalXML;
648
649
650
651
652 END get_xml_sequence;
653
654 --ADusange code to get all policies associated with table.
655
656 PROCEDURE get_xml_policy(p_tableName IN VARCHAR2,
657 p_schemaName IN VARCHAR2,
658 p_includePolicy IN VARCHAR2,
659 p_policyCount OUT NOCOPY NUMBER,
660 p_PolicyListing OUT NOCOPY VARCHAR2,
661 p_retVal OUT NOCOPY CLOB)
662 IS
663 l_objXMLOpenHandle NUMBER;
664 l_tablen BINARY_INTEGER;
668 l_tmpXML CLOB;
665 l_policyList DBMS_UTILITY.UNCL_ARRAY;
666 finalInSt VARCHAR2(1000);
667 l_finalXML CLOB;
669 l_eobjXMLs sys.XMLType;
670 l_policyXML sys.XMLType;
671 l_ctr NUMBER;
672 l_policyName VARCHAR2(50);
673
674 BEGIN
675
676 l_ctr := 0;
677 if ( p_includePolicy is null ) then
678 l_ctr := 1;
679 end if;
680
681 p_policyCount := 0;
682 l_objXMLOpenHandle := sys.ad_dbms_metadata.open('RLS_POLICY');
683
684 sys.ad_dbms_metadata.set_filter(l_objXMLOpenHandle,'BASE_OBJECT_SCHEMA',p_schemaName);
685 sys.ad_dbms_metadata.set_filter(l_objXMLOpenHandle,'BASE_OBJECT_NAME',p_tableName);
686
687
688 LOOP
689 begin
690 l_policyXML := sys.ad_dbms_metadata.fetch_xml(l_objXMLOpenHandle);
691 EXIT WHEN l_policyXML IS NULL;
692 EXCEPTION
693 when others then
694 p_policyCount := 0;
695 EXIT;
696 end;
697
698 l_eobjXMLs := XMLType.extract(l_policyXML,'ROWSET/ROW');
699 SELECT extractValue(l_policyXML,'ROWSET/ROW/RLS_POLICY_T/NAME')
700 INTO l_policyName
701 FROM DUAL;
702
703 if ((l_ctr = 1) or (instr(p_includePolicy,l_policyName) <> 0)) then
704 p_policyCount := p_policyCount + 1;
705 p_PolicyListing := p_PolicyListing || ' ' || l_policyName || '
706 ';
707 l_tmpXML := l_eobjXMLs.getClobVal();
708 if ( l_finalXML is null ) then
709 l_finalXML := l_tmpXML;
710 else
711 dbms_lob.append(l_finalXML,l_tmpXML);
712 end if;
713 end if;
714 END LOOP;
715
716 sys.ad_dbms_metadata.close(l_objXMLOpenHandle);
717 p_retVal := l_finalXML;
718
719 END get_xml_policy;
720
721
722
723 PROCEDURE get_fnd_table_metadata(p_tableName IN VARCHAR2,
724 p_owner IN VARCHAR2,
725 p_ASNAME IN VARCHAR2,
726 p_retXml OUT NOCOPY CLOB)
727 IS
728 l_finalresult CLOB;
729 l_tmpresult CLOB;
730 BEGIN
731
732 /* Extracting metadata from fnd_tables and fnd_columns */
733 get_fnd_tab_col_metadata(p_tableName,p_owner,p_ASNAME,l_finalresult);
734
735 get_fnd_primary_key_metadata(p_tableName,p_owner,p_ASNAME,l_tmpresult);
736 dbms_lob.append(l_finalresult,l_tmpresult);
737
738 get_fnd_foreign_key_metadata(p_tableName,p_owner,p_ASNAME,l_tmpresult);
739 dbms_lob.append(l_finalresult,l_tmpresult);
740
741 get_fnd_histogram_metadata(p_tableName,p_owner,p_ASNAME,l_tmpresult);
742 dbms_lob.append(l_finalresult,l_tmpresult);
743
744 get_fnd_tablespace_metadata(p_tableName,p_owner,p_ASNAME,l_tmpresult);
745 dbms_lob.append(l_finalresult,l_tmpresult);
746
747 p_retXml := l_finalresult;
748
749 END get_fnd_table_metadata;
750
751 PROCEDURE get_fnd_view_metadata(p_viewName IN VARCHAR2,
752 p_owner IN VARCHAR2,
753 p_ASNAME IN VARCHAR2,
754 p_retXml OUT NOCOPY CLOB)
755 IS
756 l_queryCtx DBMS_XMLGEN.ctxType;
757 l_result0 XmlType;
758 l_finalresult CLOB;
759 l_str varchar2(2000);
760 l_refcur SYS_REFCURSOR;
761 l_noOfRows number;
762 l_ludate varchar2(30);
763 BEGIN
764 l_ludate := to_char(sysdate, 'YYYY/MM/DD');
765
766 l_str := 'select fnd_load_util.owner_name(a.LAST_UPDATED_BY) as APPS_OWNER, ' ||
767 ' to_char(a.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
768 ' from fnd_views a, fnd_application b where view_name = :1 ' ||
769 ' and a.application_id = b.application_id ' ||
770 ' and b.application_short_name = :2 ';
771
772
773 open l_refcur FOR l_str using p_viewName , p_asname;
774 l_queryCtx := DBMS_XMLGEN.newContext(l_refcur);
775 DBMS_XMLGEN.setRowsetTag(l_queryCtx,'APPS_AOL_METADATA');
776 DBMS_XMLGEN.setRowTag(l_queryCtx,'APPS_FND_VIEWS');
777
778
779 l_result0 := DBMS_XMLGEN.getXMLType(l_queryCtx);
780
781 l_noOfRows := DBMS_XMLGEN.getNumRowsProcessed(l_queryCtx);
782 if ( l_noOfRows = 0 ) then
783 l_result0 := xmltype('<APPS_AOL_METADATA>
784 <APPS_FND_VIEWS>
785 <APPS_OWNER>SEED</APPS_OWNER>
786 <APPS_LAST_UPDATE_DATE>' || l_ludate || '</APPS_LAST_UPDATE_DATE>
787 </APPS_FND_VIEWS>
788 </APPS_AOL_METADATA>
789 ');
790 end if;
791
792 l_finalresult := l_result0.getclobval();
793 --printClobOut(l_finalresult);
794 close l_refcur;
795 DBMS_XMLGEN.closeContext(l_queryCtx); -- you must close the query handle..
796
797 p_retXml := l_finalresult;
798
799 END get_fnd_view_metadata;
800
801
802 PROCEDURE get_fnd_mview_metadata(p_mviewName IN VARCHAR2,
806 IS
803 p_owner IN VARCHAR2,
804 p_ASNAME IN VARCHAR2,
805 p_retXml OUT NOCOPY CLOB)
807 l_finalresult CLOB;
808 l_tmpresult CLOB;
809 BEGIN
810
811 /* Extracting metadata from fnd_tables and fnd_columns */
812 get_fnd_tab_col_metadata(p_mviewName,p_owner,p_ASNAME,l_finalresult);
813
814 get_fnd_histogram_metadata(p_mviewName,p_owner,p_ASNAME,l_tmpresult);
815 dbms_lob.append(l_finalresult,l_tmpresult);
816
817 p_retXml := l_finalresult;
818
819 END get_fnd_mview_metadata;
820
821 PROCEDURE is_temp_iot ( p_object_name IN VARCHAR2,
822 p_schemaName IN VARCHAR2,
823 p_type OUT NOCOPY VARCHAR2) IS
824 l_str1 varchar2(50);
825 l_str2 varchar2(50);
826 ctr NUMBER := 0;
827 cursor c_cur is
828 select NVL(temporary, 'NO'), NVL(IOT_TYPE, 'NO')
829 from dba_tables where table_name = p_object_name
830 and owner = p_schemaName;
831 begin
832 open c_cur;
833 loop
834 fetch c_cur into l_str1 , l_str2;
835 exit when c_cur%notfound;
836 ctr := ctr + 1;
837 end loop;
838 close c_cur;
839 if(ctr >=2 ) then
840 p_type := '-1';
841 elsif(l_str1 = 'Y' ) then -- Original condition:"l_str1 = 'N' or l_str1 = 'NO'".
842 p_type := 'Y';
843 elsif(l_str2 = 'IOT' or l_str2 = 'IOT_OVERFLOW') then
844 -- Original condition : "l_str2 = 'NO'".
845 p_type := 'Y';
846 else
847 p_type := 'N';
848 end if;
849 end is_temp_iot;
850
851 PROCEDURE get_ddl_comment(p_ObjName IN VARCHAR2,
852 p_schemaName IN VARCHAR2,
853 p_retVal OUT NOCOPY CLOB)
854 IS
855 l_objXMLOpenHandle NUMBER;
856 transHandle NUMBER;
857 finalInSt VARCHAR2(1000);
858 l_finalXML CLOB;
859 l_tmpXML CLOB;
860 l_ddls sys.ku$_ddls;
861 l_ddl sys.ku$_ddl;
862 parsedItems sys.ku$_parsed_items;
863 l_ctr NUMBER;
864
865 BEGIN
866 l_finalXML := null;
867 l_ctr := 0;
868 l_objXMLOpenHandle := sys.ad_dbms_metadata.open('COMMENT');
869
870 sys.ad_dbms_metadata.set_filter(l_objXMLOpenHandle,'BASE_OBJECT_NAME',p_ObjName);
871 sys.ad_dbms_metadata.set_filter(l_objXMLOpenHandle,'BASE_OBJECT_SCHEMA',p_schemaName);
872 transHandle := sys.ad_dbms_metadata.add_transform(l_objXMLOpenHandle, 'DDL');
873 sys.ad_dbms_metadata.set_transform_param(transHandle,'SQLTERMINATOR', TRUE);
874
875
876
877 -- sys.ad_dbms_metadata.set_parse_item(l_objXMLOpenHandle, 'NAME');
878
879
880
881
882 LOOP
883 begin
884 l_ddls := sys.ad_dbms_metadata.fetch_ddl(l_objXMLOpenHandle);
885 EXIT WHEN l_ddls IS NULL;
886 -- EXCEPTION
887 -- when others then
888 -- l_ctr := 0;
889 -- EXIT;
890 end;
891
892 l_ddl := l_ddls(1);
893 if ( l_finalXML is null ) then
894 l_finalXML := l_ddl.ddltext;
895 else
896 dbms_lob.append(l_finalXML,l_ddl.ddltext);
897 end if;
898
899 END LOOP;
900 if ( l_finalXML is not null ) then
901 l_tmpXML := '<ROW>
902 <COMMENT_T>
903 ';
904 dbms_lob.append(l_tmpXML,l_finalXML);
905 dbms_lob.append(l_tmpXML,'</COMMENT_T>
906 ');
907 dbms_lob.append(l_tmpXML,'</ROW>
908 ');
909 end if;
910
911 sys.ad_dbms_metadata.close(l_objXMLOpenHandle);
912 p_retVal := l_tmpXML;
913
914 END get_ddl_comment;
915
916
917 PROCEDURE get_fnd_tab_col_metadata(p_objName IN VARCHAR2,
918 p_owner IN VARCHAR2,
919 p_ASNAME IN VARCHAR2,
920 p_retXml OUT NOCOPY CLOB)
921 IS
922 l_queryCtx DBMS_XMLGEN.ctxType;
923 l_queryCtx1 DBMS_XMLGEN.ctxType;
924 l_result0 XmlType;
925 l_result1 xmlType;
926 l_finalresult CLOB;
927 l_str varchar2(2500);
928 l_refcur SYS_REFCURSOR;
929 l_fndtabxml XMLType;
930 l_noOfRows number;
931 l_ludate varchar2(30);
932 l_comments varchar2(240);
933 BEGIN
934 l_ludate := to_char(sysdate, 'YYYY/MM/DD');
935 begin
936 select substr(ltrim(rtrim(comments)),1,240)
937 into l_comments
938 from dba_tab_comments
939 where owner = p_owner
940 and table_name = p_objName;
941 exception
942 when others then
943 l_comments := '';
944 end;
945
946 l_str := 'select table_type as apps_table_type, ' ||
947 ' NVL(a.description,:1) as APPS_DESCRIPTION, ' ||
948 ' fnd_load_util.owner_name(a.LAST_UPDATED_BY) as APPS_OWNER, ' ||
949 ' to_char(a.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
953
950 ' from fnd_tables a, fnd_application b where table_name = :2 ' ||
951 ' and a.application_id = b.application_id ' ||
952 ' and b.application_short_name = :3 ';
954 open l_refcur FOR l_str using l_comments, p_objName , p_asname;
955 l_queryCtx := DBMS_XMLGEN.newContext(l_refcur);
956 DBMS_XMLGEN.setRowsetTag(l_queryCtx,'APPS_AOL_METADATA');
957 DBMS_XMLGEN.setRowTag(l_queryCtx,'APPS_FND_TABLES');
958
959
960 l_result0 := DBMS_XMLGEN.getXMLType(l_queryCtx);
961
962 l_noOfRows := DBMS_XMLGEN.getNumRowsProcessed(l_queryCtx);
963 if ( l_noOfRows <> 0 ) then
964 l_fndtabxml := xmltype.extract(l_result0,'APPS_AOL_METADATA/APPS_FND_TABLES');
965 else
966 l_fndtabxml := xmltype('<APPS_FND_TABLES>
967 <APPS_TABLE_TYPE>T</APPS_TABLE_TYPE>
968 <APPS_DESCRIPTION>'||l_comments||'</APPS_DESCRIPTION>
969 <APPS_OWNER>SEED</APPS_OWNER>
970 <APPS_LAST_UPDATE_DATE>' || l_ludate || '</APPS_LAST_UPDATE_DATE>
971 </APPS_FND_TABLES>
972 ');
973 end if;
974
975 l_finalresult := l_fndtabxml.getclobval();
976 --printClobOut(l_finalresult);
977 close l_refcur;
978 DBMS_XMLGEN.closeContext(l_queryCtx); -- you must close the query handle..
979
980 if ( l_noOfRows = 0 ) then
981 l_str := 'select column_name as apps_column_name, ' ||
982 ' nvl((select nvl(substr(ltrim(rtrim(c.comments)), 1, 240),'' '') '||
983 ' from dba_col_comments c ' ||
984 ' where c.owner = a.owner ' ||
985 ' and c.table_name = a.table_name ' ||
986 ' and c.column_name = a.column_name), '' '') as APPS_DESCRIPTION, ' ||
987 '''N'' as APPS_TRANSLATE_FLAG , ' ||
988 '''N'' as APPS_FLEXFIELD_USAGE_CODE, ' ||
989 ''''' as APPS_FLEXFIELD_APP_ID, ' ||
990 ''' '' as APPS_FLEXFIELD_NAME, ' ||
991 '''SEED'' as APPS_OWNER, ' ||
992 ' to_char(sysdate, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
993 'FROM dba_tab_cols a where a.table_name = :1 ' ||
994 'and a.owner = :2 order by column_id';
995 open l_refcur for l_str using p_objName, p_owner;
996 else
997
998 /*
999 l_str := 'select column_name as apps_column_name, ' ||
1000 ' NVL(description,'' '') as APPS_DESCRIPTION, '
1001 'TRANSLATE_FLAG as APPS_TRANSLATE_FLAG , ' ||
1002 'FLEXFIELD_USAGE_CODE as APPS_FLEXFIELD_USAGE_CODE, ' ||
1003 'NVL(TO_CHAR(FLEXFIELD_APPLICATION_ID),'''') as APPS_FLEXFIELD_APP_ID, ' ||
1004 'NVL(FLEXFIELD_NAME,'' '') as APPS_FLEXFIELD_NAME, ' ||
1005 ' fnd_load_util.owner_name(a.LAST_UPDATED_BY) as APPS_OWNER, ' ||
1006 ' to_char(a.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
1007 'FROM fnd_tables a, fnd_columns b, fnd_application c ' ||
1008 ' where a.table_name = :1 ' ||
1009 ' and c.application_short_name = :2 ' ||
1010 'and a.table_id = b.table_id ' ||
1011 'and a.application_id = b.application_id ' ||
1012 'and b.application_id = c.application_id ' ||
1013 'order by column_sequence';
1014 open l_refcur for l_str using p_objName, p_asname;
1015 */
1016 -- start : rsekaran code
1017 -- Enhancement that will generate the AOL metadata for FND_COLUMNS with default values
1018 -- if the metadata doesn't exists in the DB.
1019
1020 -- Mladena - change for performance only
1021
1022 l_str := 'select column_name as apps_column_name, '||
1023 ' NVL(b.description,'' '') as APPS_DESCRIPTION, '||
1024 'TRANSLATE_FLAG as APPS_TRANSLATE_FLAG , '||
1028 ' fnd_load_util.owner_name(a.LAST_UPDATED_BY) as APPS_OWNER, '||
1025 'FLEXFIELD_USAGE_CODE as APPS_FLEXFIELD_USAGE_CODE, '||
1026 'NVL(TO_CHAR(FLEXFIELD_APPLICATION_ID),'''') as APPS_FLEXFIELD_APP_ID, '||
1027 'NVL(FLEXFIELD_NAME,'' '') as APPS_FLEXFIELD_NAME, '||
1029 ' to_char(a.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE '||
1030 ' FROM fnd_tables a, fnd_columns b, fnd_application c '||
1031 ' where a.table_name = :tabname '||
1032 ' and c.application_short_name = :tabowner '||
1033 ' and a.table_id = b.table_id '||
1034 ' and a.application_id = b.application_id '||
1035 ' and b.application_id = c.application_id '||
1036 'UNION '||
1037 ' select '||
1038 ' dbacol.column_name as apps_column_name, '||
1039 ' nvl((select nvl(substr(ltrim(rtrim(c.comments)),1,240),'' '') '||
1040 ' from dba_col_comments c '||
1041 ' where c.owner = dbacol.owner '||
1042 ' and c.table_name = dbacol.table_name '||
1043 ' and c.column_name = dbacol.column_name), '' '') as APPS_DESCRIPTION, '||
1044 ' ''N'' as APPS_TRANSLATE_FLAG, '||
1045 ' ''N'' AS APPS_FLEXFIELD_USAGE_CODE, '||
1046 ' '''' AS APPS_FLEXFIELD_APP_ID, '||
1047 ' '' '' AS APPS_FLEXFIELD_NAME, '||
1048 ' fnd_load_util.owner_name(fndtab.LAST_UPDATED_BY) AS APPS_OWNER, '||
1049 ' to_char(fndtab.LAST_UPDATE_DATE, ''YYYY/MM/DD'') AS APPS_LAST_UPDATE_DATE '||
1050 ' from '||
1051 ' dba_tab_columns dbacol, fnd_tables fndtab, fnd_application fndapp '||
1052 ' where '||
1053 ' dbacol.table_name = :tabname '||
1054 ' and dbacol.owner = :tabowner '||
1055 ' and dbacol.table_name = fndtab.table_name '||
1056 ' and fndtab.table_name = :tabname '||
1057 ' and fndapp.application_short_name = :tabowner '||
1058 ' and fndtab.application_id = fndapp.application_id '||
1059 ' and not exists '||
1060 ' (select 1 '||
1061 ' from fnd_columns fndcol, '||
1062 ' fnd_tables fndtab, '||
1063 ' fnd_application fndapp '||
1064 ' where dbacol.column_name = fndcol.column_name '||
1065 ' and fndcol.table_id = fndtab.table_id '||
1066 ' and fndcol.application_id = fndtab.application_id '||
1067 ' and fndtab.table_name = :tabname '||
1068 ' and fndtab.application_id = fndapp.application_id '||
1069 ' and fndapp.application_short_name = :tabowner )';
1070
1071
1072 open l_refcur for l_str using p_objName, p_asname, p_objName, p_asname, p_objName, p_asname, p_objName, p_asname;
1073
1074 -- end : rsekaran code
1075
1076 end if;
1077
1078
1079 l_queryCtx1 := DBMS_XMLGEN.newContext(l_refcur);
1080 DBMS_XMLGEN.setRowsetTag(l_queryCtx1,'APPS_FND_COLUMNS');
1081 DBMS_XMLGEN.setRowTag(l_queryCtx1,'APPS_FND_COLUMN_ITEM');
1082
1083
1084 -- get the result..!
1085 l_result1:= DBMS_XMLGEN.getXMLType(l_queryCtx1);
1086 l_noOfRows := DBMS_XMLGEN.getNumRowsProcessed(l_queryCtx1);
1087 if(l_noOfRows = 0) then
1088 l_fndtabxml := xmlType('<APPS_FND_COLUMNS>
1089 <APPS_FND_COLUMN_ITEMS>
1090 <APPS_FND_COLUMN_NAME></APPS_FND_COLUMN_NAME>
1091 <APPS_DESCRIPTION></APPS_DESCRIPTION>
1092 <APPS_FND_COLUMN_SEQUENCE></APPS_FND_COLUMN_SEQUENCE>
1093 <APPS_OWNER>SEED</APPS_OWNER>
1094 <APPS_FND_COLUMN_UPDATE_DATE>'|| l_ludate ||'</APPS_FND_COLUMN_UPDATE_DATE>
1095 </APPS_FND_COLUMN_ITEMS>
1096 </APPS_FND_COLUMNS> ');
1097 dbms_lob.append(l_finalresult,l_fndtabxml.getclobval());
1098 else
1099 l_fndtabxml := xmltype.extract(l_result1,'APPS_FND_COLUMNS');
1100 dbms_lob.append(l_finalresult,l_fndtabxml.getclobval());
1101 end if;
1102
1103 DBMS_XMLGEN.closeContext(l_queryCtx1); -- you must close the query handle..
1104 close l_refcur;
1105
1106 p_retXml := l_finalresult;
1107
1108 End get_fnd_tab_col_metadata;
1109
1110 PROCEDURE get_fnd_primary_key_metadata(p_objName IN VARCHAR2,
1111 p_owner IN VARCHAR2,
1112 p_ASNAME IN VARCHAR2,
1113 p_retXml OUT NOCOPY CLOB)
1114 IS
1115 l_queryCtx DBMS_XMLGEN.ctxType;
1116 l_result1 xmlType;
1117 l_finalresult CLOB;
1118 l_str varchar2(2500);
1119 l_refcur SYS_REFCURSOR;
1120 l_fndtabxml XMLType;
1121 l_noOfRows number;
1122 l_ludate varchar2(30);
1123 BEGIN
1124
1125 l_ludate := to_char(sysdate, 'YYYY/MM/DD');
1126 /* get the primary key info */
1127
1128 l_str := 'select PRIMARY_KEY_NAME as APPS_PRIMARY_KEY_NAME, ' ||
1129 'NVL(P.DESCRIPTION,'' '') as APPS_DESCRIPTION, ' ||
1130 'PRIMARY_KEY_TYPE as apps_primary_key_type, ' ||
1131 'AUDIT_KEY_FLAG as APPS_AUDIT_KEY_FLAG, ' ||
1132 ' fnd_load_util.owner_name(P.LAST_UPDATED_BY) as APPS_OWNER, ' ||
1133 ' to_char(P.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
1134 'from FND_TABLES T, ' ||
1135 'FND_APPLICATION A, ' ||
1136 'FND_PRIMARY_KEYS P ' ||
1137 'where A.APPLICATION_ID = T.APPLICATION_ID ' ||
1138 'and A.APPLICATION_SHORT_NAME = :1 ' ||
1139 'and T.TABLE_NAME = :2 ' ||
1140 'and P.TABLE_ID = T.TABLE_ID ' ||
1141 'and P.APPLICATION_ID = T.APPLICATION_ID ' ||
1142 'order by 1 ';
1143
1147 DBMS_XMLGEN.setRowTag(l_queryCtx,'APPS_FND_PRIMARY_KEY_ITEMS');
1144 open l_refcur for l_str using p_asname, p_objName;
1145 l_queryCtx := DBMS_XMLGEN.newContext(l_refcur);
1146 DBMS_XMLGEN.setRowsetTag(l_queryCtx,'APPS_FND_PRIMARY_KEYS');
1148 l_result1:= DBMS_XMLGEN.getXMLType(l_queryCtx);
1149 l_noOfRows := DBMS_XMLGEN.getNumRowsProcessed(l_queryCtx);
1150 if ( l_noOfRows <> 0 ) then
1151 l_fndtabxml := xmltype.extract(l_result1,'APPS_FND_PRIMARY_KEYS');
1152 l_finalresult := l_fndtabxml.getclobval();
1153 else
1154 l_fndtabxml := xmlType('<APPS_FND_PRIMARY_KEYS>
1155 <APPS_FND_PRIMARY_KEY_ITEMS>
1156 <APPS_PRIMARY_KEY_NAME></APPS_PRIMARY_KEY_NAME>
1157 <APPS_DESCRIPTION></APPS_DESCRIPTION>
1158 <APPS_AUDIT_KEY_FLAG>N</APPS_AUDIT_KEY_FLAG>
1159 <APPS_PRIMARY_KEY_TYPE>D</APPS_PRIMARY_KEY_TYPE>
1160 <APPS_OWNER>SEED</APPS_OWNER>
1161 <APPS_LAST_UPDATE_DATE>'|| l_ludate ||'</APPS_LAST_UPDATE_DATE>
1162 </APPS_FND_PRIMARY_KEY_ITEMS>
1163 </APPS_FND_PRIMARY_KEYS>
1164 ');
1165 l_finalresult := l_fndtabxml.getclobval();
1166 end if;
1167 --printClobOut(l_finalresult);
1168 DBMS_XMLGEN.closeContext(l_queryCtx); -- you must close the query handle..
1169 close l_refcur;
1170
1171
1172 /* get the primary key column info */
1173 if ( l_noOfRows <> 0 ) then
1174
1175 l_str := ' select P.PRIMARY_KEY_NAME AS APPS_PRIMARY_KEY_NAME, ' ||
1176 ' C.COLUMN_NAME AS APPS_PK_COLUMN_NAME, ' ||
1177 ' PC.PRIMARY_KEY_SEQUENCE AS APPS_PK_COLUMN_SEQUENCE, ' ||
1178 ' fnd_load_util.owner_name(PC.LAST_UPDATED_BY) as APPS_OWNER, ' ||
1179 ' to_char(PC.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
1180 ' from FND_COLUMNS C, ' ||
1181 ' FND_PRIMARY_KEYS P, ' ||
1182 ' FND_PRIMARY_KEY_COLUMNS PC, ' ||
1183 ' FND_TABLES T, ' ||
1184 ' FND_APPLICATION A ' ||
1185 'where A.APPLICATION_SHORT_NAME = :1 ' ||
1186 'and T.APPLICATION_ID = A.APPLICATION_ID ' ||
1187 'and T.TABLE_NAME = :2 ' ||
1188 'and P.TABLE_ID = T.TABLE_ID ' ||
1189 'and P.APPLICATION_ID = T.APPLICATION_ID ' ||
1190 'and PC.APPLICATION_ID = P.APPLICATION_ID ' ||
1191 'and PC.TABLE_ID = P.TABLE_ID ' ||
1192 'and PC.PRIMARY_KEY_ID = P.PRIMARY_KEY_ID ' ||
1193 'and C.APPLICATION_ID = PC.APPLICATION_ID ' ||
1194 'and C.TABLE_ID = PC.TABLE_ID ' ||
1195 'and C.COLUMN_ID = PC.COLUMN_ID ' ||
1196 'order by 1 ';
1197
1198
1199 open l_refcur for l_str using p_asname, p_objName;
1200 l_queryCtx := DBMS_XMLGEN.newContext(l_refcur);
1201 DBMS_XMLGEN.setRowsetTag(l_queryCtx,'APPS_FND_PK_COLUMNS');
1202 DBMS_XMLGEN.setRowTag(l_queryCtx,'APPS_FND_PK_COLUMN_ITEMS');
1203 l_result1:= DBMS_XMLGEN.getXMLType(l_queryCtx);
1204 l_noOfRows := DBMS_XMLGEN.getNumRowsProcessed(l_queryCtx);
1205 if ( l_noOfRows <> 0 ) then
1206 l_fndtabxml := xmltype.extract(l_result1,'APPS_FND_PK_COLUMNS');
1207 dbms_lob.append(l_finalresult,l_fndtabxml.getclobval());
1208 end if;
1209 DBMS_XMLGEN.closeContext(l_queryCtx); -- you must close the query handle..
1210 close l_refcur;
1211
1212 end if;
1213
1214 if ( l_noOfRows = 0 ) then
1215 l_fndtabxml := xmlType('<APPS_FND_PK_COLUMNS>
1216 <APPS_FND_PK_COLUMN_ITEMS>
1217 <APPS_PRIMARY_KEY_NAME></APPS_PRIMARY_KEY_NAME>
1218 <APPS_PK_COLUMN_NAME></APPS_PK_COLUMN_NAME>
1219 <APPS_PK_COLUMN_SEQUENCE></APPS_PK_COLUMN_SEQUENCE>
1220 <APPS_OWNER>SEED</APPS_OWNER>
1221 <APPS_LAST_UPDATE_DATE>'|| l_ludate ||'</APPS_LAST_UPDATE_DATE>
1222 </APPS_FND_PK_COLUMN_ITEMS>
1223 </APPS_FND_PK_COLUMNS>
1224 ');
1225 dbms_lob.append(l_finalresult,l_fndtabxml.getclobval());
1226
1227 end if;
1228
1229 --printClobOut(l_finalresult);
1230 p_retXml := l_finalresult;
1231
1232 End get_fnd_primary_key_metadata;
1233
1234 PROCEDURE get_fnd_foreign_key_metadata(p_objName IN VARCHAR2,
1235 p_owner IN VARCHAR2,
1236 p_ASNAME IN VARCHAR2,
1237 p_retXml OUT NOCOPY CLOB)
1238 IS
1239 l_queryCtx DBMS_XMLGEN.ctxType;
1240 l_result1 xmlType;
1241 l_finalresult CLOB;
1242 l_str varchar2(2500);
1243 l_refcur SYS_REFCURSOR;
1244 l_fndtabxml XMLType;
1245 l_noOfRows number;
1246 l_ludate varchar2(30);
1247 BEGIN
1248
1249 l_ludate := to_char(sysdate, 'YYYY/MM/DD');
1250
1251 /* Get the foreign key info */
1252
1253 l_str := 'select F.FOREIGN_KEY_NAME as APPS_FOREIGN_KEY_NAME, ' ||
1254 ' PA.APPLICATION_SHORT_NAME as APPS_PK_APP_SHORT_NAME, ' ||
1255 ' PT.TABLE_NAME as APPS_PK_TABLE_NAME, ' ||
1256 ' P.PRIMARY_KEY_NAME as APPS_PK_NAME, ' ||
1257 ' NVL(F.DESCRIPTION,'' '') as APPS_DESCRIPTION, ' ||
1258 ' F.CASCADE_BEHAVIOR as APPS_CASCADE_BEHAVIOR, ' ||
1259 ' F.FOREIGN_KEY_RELATION as APPS_FK_RELATION, ' ||
1260 ' F.CONDITION AS APPS_CONDITION, ' ||
1261 ' fnd_load_util.owner_name(F.LAST_UPDATED_BY) as APPS_OWNER, ' ||
1262 ' to_char(F.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
1263 ' from FND_TABLES T, ' ||
1264 ' FND_APPLICATION A, ' ||
1265 ' FND_FOREIGN_KEYS F, ' ||
1266 ' FND_APPLICATION PA, ' ||
1270 ' and A.APPLICATION_SHORT_NAME = :1 ' ||
1267 ' FND_TABLES PT, ' ||
1268 ' FND_PRIMARY_KEYS P ' ||
1269 ' where A.APPLICATION_ID = T.APPLICATION_ID ' ||
1271 ' and T.TABLE_NAME = :2 ' ||
1272 ' and F.TABLE_ID = T.TABLE_ID ' ||
1273 ' and F.APPLICATION_ID = T.APPLICATION_ID ' ||
1274 ' and F.PRIMARY_KEY_APPLICATION_ID = PA.APPLICATION_ID ' ||
1275 ' and F.PRIMARY_KEY_TABLE_ID = PT.TABLE_ID ' ||
1276 ' and F.PRIMARY_KEY_APPLICATION_ID = PT.APPLICATION_ID ' ||
1277 ' and F.PRIMARY_KEY_TABLE_ID = P.TABLE_ID ' ||
1278 ' and F.PRIMARY_KEY_APPLICATION_ID = P.APPLICATION_ID ' ||
1279 ' and F.PRIMARY_KEY_ID = P.PRIMARY_KEY_ID ' ||
1280 ' order by 1 ';
1281
1282 open l_refcur for l_str using p_asname, p_objName;
1283 l_queryCtx := DBMS_XMLGEN.newContext(l_refcur);
1284 DBMS_XMLGEN.setRowsetTag(l_queryCtx,'APPS_FND_FOREIGN_KEYS');
1285 DBMS_XMLGEN.setRowTag(l_queryCtx,'APPS_FND_FOREIGN_KEY_ITEMS');
1286 l_result1:= DBMS_XMLGEN.getXMLType(l_queryCtx);
1287 l_noOfRows := DBMS_XMLGEN.getNumRowsProcessed(l_queryCtx);
1288 if ( l_noOfRows <> 0 ) then
1289 l_fndtabxml := xmltype.extract(l_result1,'APPS_FND_FOREIGN_KEYS');
1290 l_finalresult := l_fndtabxml.getclobval();
1291 else
1292 l_fndtabxml := xmlType('<APPS_FND_FOREIGN_KEYS>
1293 <APPS_FND_FOREIGN_KEY_ITEMS>
1294 <APPS_FOREIGN_KEY_NAME></APPS_FOREIGN_KEY_NAME>
1295 <APPS_PK_APP_SHORT_NAME></APPS_PK_APP_SHORT_NAME>
1296 <APPS_PK_TABLE_NAME></APPS_PK_TABLE_NAME>
1297 <APPS_PK_NAME></APPS_PK_NAME>
1298 <APPS_DESCRIPTION></APPS_DESCRIPTION>
1299 <APPS_CASCADE_BEHAVIOR></APPS_CASCADE_BEHAVIOR>
1300 <APPS_FK_RELATION></APPS_FK_RELATION>
1301 <APPS_CONDITION></APPS_CONDITION>
1302 <APPS_OWNER>SEED</APPS_OWNER>
1303 <APPS_LAST_UPDATE_DATE>'|| l_ludate ||'</APPS_LAST_UPDATE_DATE>
1304 </APPS_FND_FOREIGN_KEY_ITEMS>
1305 </APPS_FND_FOREIGN_KEYS>
1306 ');
1307 l_finalresult := l_fndtabxml.getclobval();
1308 end if;
1309 DBMS_XMLGEN.closeContext(l_queryCtx); -- you must close the query handle..
1310 close l_refcur;
1311
1312
1313 if ( l_noOfRows <> 0 ) then
1314 /* Get the foreign key columns */
1315
1316 l_str := ' select F.FOREIGN_KEY_NAME AS APPS_FK_NAME, ' ||
1317 ' C.COLUMN_NAME as APPS_PK_COLUMN_NAME, ' ||
1318 ' FC.FOREIGN_KEY_SEQUENCE as APPS_FK_SEQUENCE, ' ||
1319 ' FC.CASCADE_VALUE as APPS_CASCADE_VALUE, ' ||
1320 ' fnd_load_util.owner_name(FC.LAST_UPDATED_BY) as APPS_OWNER, ' ||
1321 ' to_char(FC.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
1322 ' from FND_COLUMNS C, ' ||
1323 ' FND_FOREIGN_KEYS F, ' ||
1324 ' FND_FOREIGN_KEY_COLUMNS FC, ' ||
1325 ' FND_TABLES T, ' ||
1326 ' FND_APPLICATION A ' ||
1327 ' where A.APPLICATION_SHORT_NAME = :1 ' ||
1328 ' and T.APPLICATION_ID = A.APPLICATION_ID ' ||
1329 ' and T.TABLE_NAME = :2 ' ||
1330 ' and F.TABLE_ID = T.TABLE_ID ' ||
1331 ' and F.APPLICATION_ID = T.APPLICATION_ID ' ||
1332 ' and FC.APPLICATION_ID = F.APPLICATION_ID ' ||
1333 ' and FC.TABLE_ID = F.TABLE_ID ' ||
1334 ' and FC.FOREIGN_KEY_ID = F.FOREIGN_KEY_ID ' ||
1335 ' and C.APPLICATION_ID = FC.APPLICATION_ID ' ||
1336 ' and C.TABLE_ID = FC.TABLE_ID ' ||
1337 ' and C.COLUMN_ID = FC.COLUMN_ID ' ||
1338 ' order by 1 ';
1339
1340 open l_refcur for l_str using p_asname, p_objName;
1341 l_queryCtx := DBMS_XMLGEN.newContext(l_refcur);
1342 DBMS_XMLGEN.setRowsetTag(l_queryCtx,'APPS_FND_FK_COLUMNS');
1343 DBMS_XMLGEN.setRowTag(l_queryCtx,'APPS_FND_FK_COLUMN_ITEMS');
1344 l_result1:= DBMS_XMLGEN.getXMLType(l_queryCtx);
1345 l_noOfRows := DBMS_XMLGEN.getNumRowsProcessed(l_queryCtx);
1346 if ( l_noOfRows <> 0 ) then
1347 l_fndtabxml := xmltype.extract(l_result1,'APPS_FND_FK_COLUMNS');
1348 dbms_lob.append(l_finalresult,l_fndtabxml.getclobval());
1349 end if;
1350 DBMS_XMLGEN.closeContext(l_queryCtx); -- you must close the query handle..
1351 close l_refcur;
1352
1353 end if;
1354
1355 if ( l_noOfRows = 0 ) then
1356 l_fndtabxml := xmlType('<APPS_FND_FK_COLUMNS>
1357 <APPS_FND_FK_COLUMN_ITEMS>
1358 <APPS_FK_NAME></APPS_FK_NAME>
1359 <APPS_FK_SEQUENCE></APPS_FK_SEQUENCE>
1360 <APPS_CASCADE_VALUE></APPS_CASCADE_VALUE>
1361 <APPS_OWNER>SEED</APPS_OWNER>
1362 <APPS_LAST_UPDATE_DATE>'|| l_ludate ||'</APPS_LAST_UPDATE_DATE>
1363 </APPS_FND_FK_COLUMN_ITEMS>
1364 </APPS_FND_FK_COLUMNS>
1365 ');
1366 dbms_lob.append(l_finalresult,l_fndtabxml.getclobval());
1367 end if;
1368
1369
1370 p_retXml := l_finalresult;
1371
1372 End get_fnd_foreign_key_metadata;
1373
1374 PROCEDURE get_fnd_histogram_metadata(p_objName IN VARCHAR2,
1375 p_owner IN VARCHAR2,
1376 p_ASNAME IN VARCHAR2,
1377 p_retXml OUT NOCOPY CLOB)
1378 IS
1379 l_queryCtx DBMS_XMLGEN.ctxType;
1380 l_result1 xmlType;
1381 l_finalresult CLOB;
1382 l_str varchar2(2500);
1383 l_refcur SYS_REFCURSOR;
1384 l_fndtabxml XMLType;
1385 l_noOfRows number;
1386 l_ludate varchar2(30);
1387 --l_comments varchar2(240);
1388 BEGIN
1389
1390 l_ludate := to_char(sysdate, 'YYYY/MM/DD');
1391
1395 ' fnd_load_util.owner_name(C.LAST_UPDATED_BY) as APPS_OWNER, ' ||
1392 l_str := ' select C.COLUMN_NAME AS APPS_COLUMN_NAME, ' ||
1393 ' C.PARTITION AS APPS_PARTITION, ' ||
1394 ' C.HSIZE AS APPS_HSIZE, ' ||
1396 ' to_char(C.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
1397 ' from FND_HISTOGRAM_COLS C, ' ||
1398 ' FND_TABLES T, ' ||
1399 ' FND_APPLICATION A ' ||
1400 ' where A.APPLICATION_ID = T.APPLICATION_ID ' ||
1401 ' and A.APPLICATION_SHORT_NAME = :1 ' ||
1402 ' and T.TABLE_NAME = C.TABLE_NAME ' ||
1403 ' and T.TABLE_NAME = :2 ' ||
1404 ' and C.APPLICATION_ID = T.APPLICATION_ID ' ||
1405 ' order by 1 ';
1406
1407 open l_refcur for l_str using p_asname, p_objName;
1408 l_queryCtx := DBMS_XMLGEN.newContext(l_refcur);
1409 DBMS_XMLGEN.setRowsetTag(l_queryCtx,'APPS_FND_HISTOGRAM');
1410 DBMS_XMLGEN.setRowTag(l_queryCtx,'APPS_FND_HISTOGRAM_ITEMS');
1411 l_result1:= DBMS_XMLGEN.getXMLType(l_queryCtx);
1412 l_noOfRows := DBMS_XMLGEN.getNumRowsProcessed(l_queryCtx);
1413 if ( l_noOfRows <> 0 ) then
1414 l_fndtabxml := xmltype.extract(l_result1,'APPS_FND_HISTOGRAM');
1415 l_finalresult := l_fndtabxml.getclobval();
1416 else
1417 l_fndtabxml := xmlType('<APPS_FND_HISTOGRAM>
1418 <APPS_FND_HISTOGRAM_ITEMS>
1419 <APPS_COLUMN_NAME></APPS_COLUMN_NAME>
1420 <APPS_PARTITION></APPS_PARTITION>
1421 <APPS_HSIZE></APPS_HSIZE>
1422 <APPS_OWNER>SEED</APPS_OWNER>
1423 <APPS_LAST_UPDATE_DATE>'|| l_ludate ||'</APPS_LAST_UPDATE_DATE>
1424 </APPS_FND_HISTOGRAM_ITEMS>
1425 </APPS_FND_HISTOGRAM>
1426 ');
1427 l_finalresult := l_fndtabxml.getclobval();
1428 end if;
1429
1430 DBMS_XMLGEN.closeContext(l_queryCtx); -- you must close the query handle..
1431 close l_refcur;
1432
1433 -- printClobOut(l_finalresult);
1434
1435 p_retXml := l_finalresult;
1436
1437 End get_fnd_histogram_metadata;
1438
1439
1440 PROCEDURE get_fnd_tablespace_metadata(p_objName IN VARCHAR2,
1441 p_owner IN VARCHAR2,
1442 p_ASNAME IN VARCHAR2,
1443 p_retXml OUT NOCOPY CLOB)
1444 IS
1445 l_queryCtx DBMS_XMLGEN.ctxType;
1446 l_result1 xmlType;
1447 l_finalresult CLOB;
1448 l_str varchar2(2500);
1449 l_refcur SYS_REFCURSOR;
1450 l_fndtabxml XMLType;
1451 l_noOfRows number;
1452 BEGIN
1453 /* get the tablespace info */
1454
1455 l_finalresult := TO_CLOB('<!-- Choose from one of the following tablespace '||
1456 'types to classify storage -
1457 TRANSACTION_TABLES
1458 REFERENCE
1459 INTERFACE
1460 SUMMARY
1461 NOLOGGING
1462 TRANSACTION_INDEXES
1463 ARCHIVE
1464 MEDIA
1465 TOOLS
1466 -->');
1467
1468 l_str := 'select tablespace_type as APPS_TABLESPACE_CLASSIFICATION ' ||
1469 ' FROM FND_OBJECT_TABLESPACES T, ' ||
1470 ' FND_APPLICATION A ' ||
1471 ' where A.APPLICATION_ID = T.APPLICATION_ID ' ||
1472 ' and A.APPLICATION_SHORT_NAME = :1 ' ||
1473 ' and object_name = :2 ';
1474
1475 /*
1476 l_str := 'select decode(TABLESPACE_TYPE,''TRANSACTION_TABLES'',''Y'',''N'') '||
1477 ' as APPS_TSPACE_TX_TABLES , ' ||
1478 ' decode(TABLESPACE_TYPE,''REFERENCE'',''Y'',''N'') '||
1479 ' as APPS_TSPACE_REFERENCE , ' ||
1480 ' decode(TABLESPACE_TYPE,''INTERFACE'',''Y'',''N'') '||
1481 ' as APPS_TSPACE_INTERFACE , ' ||
1482 ' decode(TABLESPACE_TYPE,''SUMMARY'',''Y'',''N'') '||
1483 ' as APPS_TSPACE_SUMMARY , ' ||
1484 ' decode(TABLESPACE_TYPE,''NOLOGGING'',''Y'',''N'') '||
1485 ' as APPS_TSPACE_NOLOGGING , ' ||
1486 ' decode(TABLESPACE_TYPE,''TRANSACTION_INDEXES'',''Y'',''N'') '||
1487 ' as APPS_TSPACE_TX_INDEXES , ' ||
1488 ' decode(TABLESPACE_TYPE,''ARCHIVE'',''Y'',''N'') '||
1489 ' as APPS_TSPACE_ARCHIVE ' ||
1490 ' FROM FND_OBJECT_TABLESPACES T, ' ||
1491 ' FND_APPLICATION A ' ||
1492 ' where A.APPLICATION_ID = T.APPLICATION_ID ' ||
1493 ' and A.APPLICATION_SHORT_NAME = :1 ' ||
1494 ' and object_name = :2 ';
1495 */
1496
1497 open l_refcur for l_str using p_asname, p_objName;
1498 l_queryCtx := DBMS_XMLGEN.newContext(l_refcur);
1499 DBMS_XMLGEN.setRowsetTag(l_queryCtx,'APPS_FND_OBJECT_TS');
1500 DBMS_XMLGEN.setRowTag(l_queryCtx,'APPS_FND_OBJECT_TABLESPACE');
1501 l_result1:= DBMS_XMLGEN.getXMLType(l_queryCtx);
1502 l_noOfRows := DBMS_XMLGEN.getNumRowsProcessed(l_queryCtx);
1503 if ( l_noOfRows <> 0 ) then
1504 l_fndtabxml := xmltype.extract(l_result1,'APPS_FND_OBJECT_TS/APPS_FND_OBJECT_TABLESPACE');
1505 dbms_lob.append(l_finalresult,l_fndtabxml.getclobval());
1506 else
1507 /*
1508 l_fndtabxml := xmlType('<APPS_FND_OBJECT_TABLESPACE>
1509 <APPS_TSPACE_TX_TABLES>N</APPS_TSPACE_TX_TABLES>
1510 <APPS_TSPACE_REFERENCE>N</APPS_TSPACE_REFERENCE>
1511 <APPS_TSPACE_INTERFACE>N</APPS_TSPACE_INTERFACE>
1512 <APPS_TSPACE_SUMMARY>N</APPS_TSPACE_SUMMARY>
1513 <APPS_TSPACE_NOLOGGING>N</APPS_TSPACE_NOLOGGING>
1514 <APPS_TSPACE_TX_INDEXES>N</APPS_TSPACE_TX_INDEXES>
1515 <APPS_TSPACE_ARCHIVE>N</APPS_TSPACE_ARCHIVE>
1516 </APPS_FND_OBJECT_TABLESPACE>
1517 ');
1518 */
1519
1520 l_fndtabxml := xmlType('<APPS_FND_OBJECT_TABLESPACE>
1524 dbms_lob.append(l_finalresult,l_fndtabxml.getclobval());
1521 <APPS_TABLESPACE_CLASSIFICATION>TRANSACTION_TABLES</APPS_TABLESPACE_CLASSIFICATION>
1522 </APPS_FND_OBJECT_TABLESPACE>
1523 ');
1525
1526 end if;
1527
1528 DBMS_XMLGEN.closeContext(l_queryCtx); -- you must close the query handle..
1529 close l_refcur;
1530
1531 p_retXml := l_finalresult;
1532
1533 End get_fnd_tablespace_metadata;
1534
1535
1536 /* vkhatri bug 4929568 */
1537 PROCEDURE get_type_attr(p_typeName IN VARCHAR2,
1538 p_owner IN VARCHAR2,
1539 p_ASNAME IN VARCHAR2,
1540 p_retXml OUT NOCOPY CLOB)
1541 IS
1542 l_queryCtx DBMS_XMLGEN.ctxType;
1543 l_result XmlType;
1544 l_finalresult CLOB;
1545 l_str varchar2(2500);
1546 l_refcur SYS_REFCURSOR;
1547 l_fndtypexml XMLType;
1548 l_noOfRows number;
1549 BEGIN
1550
1551 l_str := 'select ATTR_NAME as ATTRIBUTE_NAME, ' ||
1552 ' NVL(ATTR_TYPE_OWNER,'' '') as ATTR_TYPE_OWNER, ' ||
1553 ' ATTR_TYPE_NAME as ATTR_TYPE_NAME, ' ||
1554 ' NVL(TO_CHAR(LENGTH),'' '') as LENGTH, ' ||
1555 ' NVL(TO_CHAR(PRECISION),'' '') as PRECISION, ' ||
1556 ' NVL(TO_CHAR(SCALE),'' '') as SCALE, ' ||
1557 ' INHERITED as INHERITED ' ||
1558 ' from ALL_TYPE_ATTRS where owner=:1 and type_name=:2 ';
1559
1560 open l_refcur FOR l_str using p_owner, p_typeName;
1561 l_queryCtx := DBMS_XMLGEN.newContext(l_refcur);
1562 DBMS_XMLGEN.setRowsetTag(l_queryCtx,'APPS_TYPE_ATTRIBUTE');
1563 DBMS_XMLGEN.setRowTag(l_queryCtx,'APPS_TYPE_ATTRIBUTE_ITEMS');
1564
1565 l_result := DBMS_XMLGEN.getXMLType(l_queryCtx);
1566
1567 l_noOfRows := DBMS_XMLGEN.getNumRowsProcessed(l_queryCtx);
1568 if ( l_noOfRows <> 0 ) then
1569 l_fndtypexml := xmltype.extract(l_result,'APPS_TYPE_ATTRIBUTE');
1570 else
1571 l_fndtypexml := xmlType('<APPS_TYPE_ATTRIBUTE></APPS_TYPE_ATTRIBUTE>
1572 ');
1573 end if;
1574
1575 l_finalresult := l_fndtypexml.getclobval();
1576
1577 close l_refcur;
1578 DBMS_XMLGEN.closeContext(l_queryCtx); -- you must close the query handle..
1579
1580 p_retXml := l_finalresult;
1581
1582 END get_type_attr;
1583
1584
1585 /* vkhatri bug 4929568 */
1586 PROCEDURE get_type_method(p_typeName IN VARCHAR2,
1587 p_owner IN VARCHAR2,
1588 p_ASNAME IN VARCHAR2,
1589 p_retXml OUT NOCOPY CLOB)
1590 IS
1591 l_queryCtx DBMS_XMLGEN.ctxType;
1592 l_result XmlType;
1593 l_finalresult CLOB;
1594 l_tmpXML CLOB;
1595 l_str varchar2(2500);
1596 l_refcur SYS_REFCURSOR;
1597 l_fndtypexml XMLType;
1598 l_noOfRows number;
1599 BEGIN
1600
1601 l_str := 'select METHOD_NAME as METHOD_NAME, ' ||
1602 ' METHOD_NO as METHOD_NO, ' ||
1603 ' METHOD_TYPE as METHOD_TYPE, ' ||
1604 ' PARAMETERS as PARAMETERS, ' ||
1605 ' RESULTS as RESULTS, ' ||
1606 ' FINAL as FINAL, ' ||
1607 ' INSTANTIABLE as INSTANTIABLE, ' ||
1608 ' OVERRIDING as OVERRIDING, ' ||
1609 ' INHERITED as INHERITED ' ||
1610 ' from ALL_TYPE_METHODS where owner=:1 and type_name=:2';
1611
1612 open l_refcur FOR l_str using p_owner, p_typeName;
1613 l_queryCtx := DBMS_XMLGEN.newContext(l_refcur);
1614 DBMS_XMLGEN.setRowsetTag(l_queryCtx,'APPS_TYPE_METHOD');
1615 DBMS_XMLGEN.setRowTag(l_queryCtx,'APPS_TYPE_METHOD_ITEMS');
1616
1617 l_result := DBMS_XMLGEN.getXMLType(l_queryCtx);
1618
1619 l_noOfRows := DBMS_XMLGEN.getNumRowsProcessed(l_queryCtx);
1620 if ( l_noOfRows <> 0 ) then
1621 l_fndtypexml := xmltype.extract(l_result,'APPS_TYPE_METHOD');
1622 else
1623 l_fndtypexml := xmlType('<APPS_TYPE_METHOD></APPS_TYPE_METHOD>
1624 ');
1625 end if;
1626
1627 l_finalresult := l_fndtypexml.getclobval();
1628
1629 close l_refcur;
1630 DBMS_XMLGEN.closeContext(l_queryCtx); -- you must close the query handle..
1631
1632 if ( l_noOfRows <> 0 ) then
1633 get_type_method_params_results(p_typeName,p_owner,p_ASNAME,l_tmpXML);
1634 dbms_lob.append(l_finalresult,l_tmpXML);
1635 end if;
1636
1637 p_retXml := l_finalresult;
1638
1639 END get_type_method;
1640
1641
1642 /* vkhatri bug 4929568 */
1643 PROCEDURE get_type_method_params_results(p_typeName IN VARCHAR2,
1644 p_owner IN VARCHAR2,
1645 p_ASNAME IN VARCHAR2,
1646 p_retXml OUT NOCOPY CLOB)
1647 IS
1648 l_queryCtx DBMS_XMLGEN.ctxType;
1649 l_result XmlType;
1650 l_finalresult CLOB;
1651 l_tmpXML CLOB;
1652 l_str varchar2(2500);
1653 l_refcur SYS_REFCURSOR;
1654 l_fndtypexml XMLType;
1655 l_noOfRows number;
1656 BEGIN
1657
1658 l_str := 'select METHOD_NAME as METHOD_NAME, ' ||
1659 ' METHOD_NO as METHOD_NO, ' ||
1660 ' PARAM_NAME as PARAM_NAME, ' ||
1661 ' PARAM_NO as PARAM_NO, ' ||
1662 ' PARAM_MODE as PARAM_MODE, ' ||
1663 ' PARAM_TYPE_OWNER as PARAM_TYPE_OWNER, ' ||
1664 ' PARAM_TYPE_NAME as PARAM_TYPE_NAME ' ||
1665 ' from ALL_METHOD_PARAMS where owner=:1 and type_name=:2';
1666
1667 open l_refcur FOR l_str using p_owner, p_typeName;
1668 l_queryCtx := DBMS_XMLGEN.newContext(l_refcur);
1669 DBMS_XMLGEN.setRowsetTag(l_queryCtx,'APPS_TYPE_METHOD_PARAM');
1670 DBMS_XMLGEN.setRowTag(l_queryCtx,'APPS_TYPE_METHOD_PARAM_ITEMS');
1671
1672 l_result := DBMS_XMLGEN.getXMLType(l_queryCtx);
1673
1674 l_noOfRows := DBMS_XMLGEN.getNumRowsProcessed(l_queryCtx);
1675 if ( l_noOfRows <> 0 ) then
1676 l_fndtypexml := xmltype.extract(l_result,'APPS_TYPE_METHOD_PARAM');
1677 else
1678 l_fndtypexml := xmlType('<APPS_TYPE_METHOD_PARAM></APPS_TYPE_METHOD_PARAM>
1679 ');
1680 end if;
1681
1682 l_finalresult := l_fndtypexml.getclobval();
1683
1684 close l_refcur;
1685 DBMS_XMLGEN.closeContext(l_queryCtx); -- you must close the query handle..
1686
1687
1688 l_str := 'select METHOD_NAME as METHOD_NAME, ' ||
1689 ' METHOD_NO as METHOD_NO, ' ||
1690 ' NVL(RESULT_TYPE_OWNER,'' '') as RESULT_TYPE_OWNER, ' ||
1691 ' RESULT_TYPE_NAME as RESULT_TYPE_NAME ' ||
1692 ' from ALL_METHOD_RESULTS where owner=:1 and type_name=:2';
1693
1694 open l_refcur FOR l_str using p_owner, p_typeName;
1695 l_queryCtx := DBMS_XMLGEN.newContext(l_refcur);
1696 DBMS_XMLGEN.setRowsetTag(l_queryCtx,'APPS_TYPE_METHOD_RESULT');
1697 DBMS_XMLGEN.setRowTag(l_queryCtx,'APPS_TYPE_METHOD_RESULT_ITEMS');
1698
1699 l_result := DBMS_XMLGEN.getXMLType(l_queryCtx);
1700
1701 l_noOfRows := DBMS_XMLGEN.getNumRowsProcessed(l_queryCtx);
1702 if ( l_noOfRows <> 0 ) then
1703 l_fndtypexml := xmltype.extract(l_result,'APPS_TYPE_METHOD_RESULT');
1704 else
1705 l_fndtypexml := xmlType('<APPS_TYPE_METHOD_RESULT></APPS_TYPE_METHOD_RESULT>
1706 ');
1707 end if;
1708
1709 l_tmpXML := l_fndtypexml.getclobval();
1710
1711 close l_refcur;
1712 DBMS_XMLGEN.closeContext(l_queryCtx); -- you must close the query handle
1713
1714 dbms_lob.append(l_finalresult,l_tmpXML);
1715
1716 p_retXml := l_finalresult;
1717
1718 END get_type_method_params_results;
1719
1720 end fnd_odf_gen;
1721