1 PACKAGE BODY IES_META_DATA_SOURCE AS
2 /* $Header: iesmdscb.pls 115.3 2003/01/06 20:41:16 appldev noship $ */
3
4 xml_clob CLOB;
5 xml_buffer VARCHAR2(32000);
6
7 /************************ Private methods *******************************/
8
9 -- **********************************************************************
10 -- API name : convertNullString
11 -- Type : Private
12 -- Function : This function checks if the arg IS NULL and returns ''
13 -- if the arg is NULL.
14 -- **********************************************************************
15
16 FUNCTION convertNullString(s VARCHAR2) RETURN VARCHAR2 IS
17 BEGIN
18 IF (S IS NULL) THEN
19 RETURN '';
20 ELSE
21 RETURN s;
22 END IF;
23 END convertNullString;
24
25 -- **********************************************************************
26 -- API name : appendXMLClob
27 -- Type : Private
28 -- Function : This procedure appends XML Doc string to the CLOB,
29 -- Buffer used to cache the string until it reaches 32K
30 -- before appending to the CLOB.
31 -- **********************************************************************
32
33 PROCEDURE appendXMLClob(buffer IN VARCHAR2) IS
34
35 BEGIN
36 if (nvl(length(xml_buffer), 0) + length(buffer) <= 32000) then
37 xml_buffer := xml_buffer || buffer;
38 else
39 DBMS_LOB.WRITEAPPEND(xml_clob, LENGTH(xml_buffer), xml_buffer);
40 xml_buffer := buffer;
41 end if;
42
43 END appendXMLClob;
44
45
46 -- **********************************************************************
47 -- API name : convertObjPropertiesToXML
48 -- Type : Private
49 -- Function : This procedure queries the metadata tables and retrieves
50 -- properties of the given object and appends it to the
51 -- xml_clob
52 -- **********************************************************************
53
54 PROCEDURE convertObjPropertiesToXML(objId IN NUMBER) IS
55 TYPE obj_prop_type IS REF CURSOR;
56 obj_prop obj_prop_type;
57
58 key VARCHAR2(256);
59 type_name VARCHAR2(256);
60 str_value VARCHAR2(2000);
61 BEGIN
62 OPEN obj_prop FOR
63 'SELECT a.name key,
64 NVL(b.string_val, d.lookup_key) value,
65 e.type_name
66 FROM ies_meta_properties a,
67 ies_meta_property_values b,
68 ies_meta_object_propvals c,
69 ies_meta_property_lookups d,
70 ies_meta_prop_datatypes e
71 WHERE c.object_id = :object_Id
72 AND c.propval_id = b.propval_id
73 AND b.property_id = a.property_id
74 AND a.datatype_id = e.type_id
75 AND b.lookup_id = d.prop_lookup_id (+)' USING objId;
76
77 LOOP
78 FETCH obj_prop INTO key, str_value, type_name;
79 EXIT WHEN obj_prop%NOTFOUND;
80 appendXMLClob(' <Property NAME="'|| key || '" DATATYPE="' || type_name ||'"><![CDATA['||convertNullString(str_value)||']]></Property>"'||fnd_global.local_chr(10));
81 END LOOP;
82 CLOSE obj_prop;
83 END convertObjPropertiesToXML;
84
85
86 /************************ Public methods ********************************/
87
88 -- **********************************************************************
89 -- API name : getObjectAsXML
90 -- Type : Public
91 -- Function : This function returns CLOB object with XML Doc for the
92 -- given objId
93 -- **********************************************************************
94
95 FUNCTION getObjectAsXML(objId IN NUMBER) return CLOB IS
96 len number;
97 BEGIN
98 xml_buffer := null;
99 xml_clob := null;
100 DBMS_LOB.CreateTemporary(xml_clob, TRUE, DBMS_LOB.CALL);
101
102 convertObjectToXML(objId);
103 DBMS_LOB.WRITEAPPEND(xml_clob, LENGTH(xml_buffer), xml_buffer);
104 --dbms_output.put_line('length is ' || to_char(dbms_lob.getlength(xml_clob)));
105 return xml_clob;
106 END getObjectAsXML;
107
108 -- **********************************************************************
109 -- API name : convertObjectToXML
110 -- Type : Public
111 -- Function : This procedure queries metadata tables for all the object
112 -- properties and creates a XML Document with these props
113 -- **********************************************************************
114
115 PROCEDURE convertObjectToXML(objId IN NUMBER) IS
116 TYPE obj_type IS REF CURSOR;
117 obj obj_type;
118
119 buffer VARCHAR2(2000);
120 objName VARCHAR2(256);
121 objType VARCHAR2(256);
122 objectId VARCHAR2(256);
123 root VARCHAR2(256);
124 uid VARCHAR2(256);
125 BEGIN
126 OPEN obj FOR
127 'SELECT a.object_id object_id,
128 a.name name,
129 b.type_name type_name,
130 a.object_uid obj_uid
131 FROM ies_meta_objects a,
132 ies_meta_object_types b
133 WHERE a.object_id = :obj_id
134 AND a.type_id = b.type_id' using objId;
135
136
137 LOOP
138 FETCH obj INTO objectId, objName, objType, uid;
139 EXIT WHEN obj%NOTFOUND;
140
141 appendXMLClob('<JavaBean CLASS="' || objType || '">'||fnd_global.local_chr(10));
142 appendXMLClob(' <Properties>'||fnd_global.local_chr(10));
143 appendXMLClob(' <Property NAME="name" DATATYPE="String"><![CDATA['||objName||']]></Property>'||fnd_global.local_chr(10));
144 appendXMLClob(' <Property NAME="objectId" DATATYPE="Integer"><![CDATA['||objectId||']]></Property>'||fnd_global.local_chr(10));
145 appendXMLClob(' <Property NAME="UID" DATATYPE="String"><![CDATA['||uid||']]></Property>'||fnd_global.local_chr(10));
146 END LOOP;
147 CLOSE obj;
148
149
150 IF (objectid IS NOT null) THEN
151 convertObjPropertiesToXML(objectId);
152 convertChildObjectsToXML(objectId);
153
154 appendXMLClob('</Properties>'|| fnd_global.local_chr(10));
155 appendXMLClob('</JavaBean>'|| fnd_global.local_chr(10));
156 ELSE
157 appendXMLClob('<DummyTag><Dummy></Dummy></DummyTag>');
158 END IF;
159 END convertObjectToXML;
160
161 -- **********************************************************************
162 -- API name : convertChildObjectsToXML
163 -- Type : Public
164 -- Function : This procedure queries metadata tables for all the Child
165 -- objects and its object properties, creates a XML Document
166 -- with these props
167 -- **********************************************************************
168
169 PROCEDURE convertChildObjectsToXML(objId IN NUMBER) IS
170 TYPE child_obj_type IS REF CURSOR;
171 child_obj child_obj_type;
172
173 secObjId NUMBER;
174 type_name VARCHAR2(256);
175 relId NUMBER;
176 BEGIN
177 OPEN child_obj FOR
178 ' SELECT a.secondary_obj_id, c.type_name, c.list_relationship
179 FROM ies_meta_obj_relationships a,
180 ies_meta_obj_relationships b,
181 ies_meta_relationship_types c
182 WHERE a.deleted_status = 0
183 AND a.primary_obj_id = :object_id
184 AND a.primary_obj_id <> b.secondary_obj_id
185 AND a.objrel_id = b.objrel_id
186 AND a.type_id = c.type_id
187 ORDER BY a.obj_order, a.secondary_obj_id' using objId;
188
189 LOOP
190 FETCH child_obj INTO secObjId, type_name, relId;
191 EXIT WHEN child_obj%NOTFOUND;
192
193 if (relId = 1) then
194 appendXMLClob('<CCTPropertyList NAME="' || type_name || '">'|| fnd_global.local_chr(10));
195 elsif (relId = 2) then
196 appendXMLClob('<CCTPropertyMapList NAME="' || type_name || '">'|| fnd_global.local_chr(10));
197 end if;
198
199 appendXMLClob('<Property NAME="' || type_name || '">'|| fnd_global.local_chr(10));
200 convertObjectToXML(secObjid);
201 appendXMLClob('</Property>'||fnd_global.local_chr(10));
202
203 if (relId = 1) then
204 appendXMLClob('</CCTPropertyList>'|| fnd_global.local_chr(10));
205 elsif (relId = 2) then
206 appendXMLClob('</CCTPropertyMapList>'|| fnd_global.local_chr(10));
207 end if;
208
209 END LOOP;
210 CLOSE child_obj;
211 END convertChildObjectsToXML;
212
213 END IES_META_DATA_SOURCE;
[Home] [Help]