1 PACKAGE BODY HR_XML_UTIL as
2 /* $Header: hrxmlutl.pkb 120.1 2005/10/03 12:50:24 smallina noship $*/
3 --
4 -- -------------------------------------------------------------------------
5 -- |----------------------------< valueOf >---------------------------------|
6 -- -------------------------------------------------------------------------
7 --
8 FUNCTION value_Of
9 (doc in xmldom.DOMDocument
10 ,xpath in varchar2
11 )return varchar2 is
12 retval varchar2(32767);
13 --
14 Begin
15 --
16 if (not xmldom.IsNull(doc)) then
17 xslprocessor.valueOf(xmlDom.makeNode(doc),xpath, retval);
18 end if;
19 return retval;
20 End value_Of;
21 --
22 -- -------------------------------------------------------------------------
23 -- |----------------------------< valueOf >---------------------------------|
24 -- -------------------------------------------------------------------------
25 --
26 FUNCTION value_Of
27 (doc in CLOB
28 ,xpath in varchar2
29 ) return varchar2 is
30 --
31 retval varchar2(32767);
32 xmldoc xmldom.DOMDocument;
33 parser xmlparser.parser;
34 --
35 Begin
36
37 parser:=xmlparser.newParser;
38 xmlparser.parseClob(parser,doc);
39 xmldoc:=xmlparser.getDocument(parser);
40 xmlparser.freeParser(parser);
41 retval:=hr_xml_util.value_Of(xmldoc,xpath);
42 xmldom.freeDocument(xmldoc);
43 return retval;
44 exception
45 when others then
46 xmlparser.freeParser(parser);
47 xmldom.freeDocument(xmldoc);
48 return null;
49 End value_Of;
50 --
51 -- -------------------------------------------------------------------------
52 -- |----------------------------< valueOf >---------------------------------|
53 -- -------------------------------------------------------------------------
54 --
55 FUNCTION value_Of
56 (doc in varchar2
57 ,xpath in varchar2
58 ) return varchar2 is
59 --
60 retval varchar2(32767);
61 xmldoc xmldom.DOMDocument;
62 parser xmlparser.parser;
63 --
64 Begin
65
66 parser:=xmlparser.newParser;
67 xmlparser.parseBuffer(parser,doc);
68 xmldoc:=xmlparser.getDocument(parser);
69 xmlparser.freeParser(parser);
70 retval:=hr_xml_util.value_Of(xmldoc,xpath);
71 xmldom.freeDocument(xmldoc);
72 return retval;
73 Exception
74 when others then
75 xmlparser.freeParser(parser);
76 xmldom.freeDocument(xmldoc);
77 return null;
78 End value_Of;
79
80 --
81 -- -------------------------------------------------------------------------
82 -- |----------------------------< convert_CLOB_To_XMLDocument >---------------------------------|
83 -- -------------------------------------------------------------------------
84 --
85
86 FUNCTION convert_CLOB_To_XMLDocument(
87 p_document in CLOB
88 )return xmldom.DOMDocument is
89 --
90 x_xmlDocument xmldom.DOMDocument;
91 l_parser xmlparser.Parser;
92 Begin
93 -- CLOB --> xmldom.DOMDocument
94 l_parser := xmlparser.newParser;
95 xmlparser.ParseCLOB(l_parser,p_document);
96 x_xmlDocument := xmlparser.getDocument(l_parser);
97 xmlparser.freeParser(l_parser);
98 return x_xmlDocument;
99 End convert_CLOB_To_XMLDocument;
100
101 --
102 -- -------------------------------------------------------------------------
103 -- |----------------------------< get_All_EOs_List >---------------------------------|
104 -- -------------------------------------------------------------------------
105 --
106
107 FUNCTION get_All_EOs_List
108 (p_transaction_document in CLOB
109 ) return xmlDOM.DOMNodeList is
110 --
111 l_parser xmlparser.Parser;
112 -- xmlDOM.DOMNodeList
113 l_TransCache_NodeList xmlDOM.DOMNodeList;
114 l_EO_NodeList xmlDOM.DOMNodeList;
115 -- xmlDOM.DOMNode
116 rootNode xmlDOM.DOMNode;
117 l_TransCache_Node xmlDOM.DOMNode;
118 l_AM_Node xmlDOM.DOMNode;
119 l_TXN_Node xmlDOM.DOMNode;
120 --
121 Begin
122 --
123 l_parser := xmlparser.newParser;
124 rootNode := xmldom.makeNode(xmldom.getDocumentElement(convert_CLOB_To_XMLDocument(p_transaction_document)));
125 -- Now get the <TransCache> Node
126 l_TransCache_NodeList :=xmldom.getChildrenByTagName(xmldom.makeElement(rootNode),'TransCache');
127 l_TransCache_Node :=xmldom.item(l_TransCache_NodeList,0);
128 -- Now get the <AM> Node
129 l_AM_Node :=xmldom.getFirstChild(l_TransCache_Node);
130 -- Now get the </cd> Node and get its Sibling --> <TXN>
131 l_TXN_Node :=xmldom.getNextSibling(xmldom.getFirstChild(l_AM_Node));
132 l_EO_NodeList := xmldom.getElementsByTagName(xmldom.makeElement(l_TXN_Node),'EO');
133 return l_EO_NodeList;
134 End get_All_EOs_List;
135
136 --
137 -- -------------------------------------------------------------------------
138 -- |----------------------------< test_Primary_Key >---------------------------------|
139 -- -------------------------------------------------------------------------
140 --
141 FUNCTION test_Primary_Key(
142 p_EO_Row_Node xmldom.DOMNode
143 ,p_primaryKey in varchar
144 ,p_primarykey_Value in varchar
145 ) return boolean is
146 x_match boolean;
147 l_primary_key_NodeList xmldom.DOMNodeList;
148 l_temp_Node xmldom.DOMNode;
149 l_node_Value varchar2(1024);
150 Begin
151 x_match :=false;
152 -- Get the list of children whose name is = p_primaryKey
153 l_primary_key_NodeList := xmldom.getChildrenByTagName(xmldom.makeElement(p_EO_Row_Node),p_primaryKey);
154 if (xmldom.getLength(l_primary_key_NodeList) > 0) then
155 for i in 1..xmldom.getLength(l_primary_key_NodeList) loop
156 -- For each node in list extract its Text & compare with expected value
157 l_temp_Node := xmldom.getFirstChild(xmldom.item(l_primary_key_NodeList,i-1));
158 l_node_Value := xmldom.getNodeValue(l_temp_Node);
159 -- if node's text matches expected value return true else continue
160 if l_node_Value = p_primarykey_Value then
161 return true;
162 end if; -- End of if l_Node_Value = p_primarykey_Value
163 end loop; -- End of for
164 end if; -- End of main if
165
166 return x_match;
167 End test_Primary_Key;
168
169 --
170 -- -------------------------------------------------------------------------
171 -- |----------------------------< check_Primary_Keys >---------------------------------|
172 -- -------------------------------------------------------------------------
173 --
174 FUNCTION check_Primary_Keys(
175 p_EO_Row_Node xmldom.DOMNode
176 ,p_pk_1 in varchar default null
177 ,p_value_1 in varchar default null
178 ,p_pk_2 in varchar default null
179 ,p_value_2 in varchar default null
180 ,p_pk_3 in varchar default null
181 ,p_value_3 in varchar default null
182 ,p_pk_4 in varchar default null
183 ,p_value_4 in varchar default null
184 ,p_pk_5 in varchar default null
185 ,p_value_5 in varchar default null)
186 return boolean is
187 --
188 TYPE primary_key_type IS TABLE of varchar2(1000) INDEX BY BINARY_INTEGER;
189 TYPE expected_value_type IS TABLE of varchar2(1000) INDEX BY BINARY_INTEGER;
190 --
191 l_primaryKey_table primary_key_type;
192 l_expected_value_table expected_value_type;
193 --
194 x_is_desiredNode boolean;
195 --
196 l_counter number(2);
197 Begin
198 x_is_desiredNode := true;
199 l_counter:=0;
200
201 if p_pk_1 is not null and p_value_1 is not null then
202 l_primaryKey_table(1):=p_pk_1;
203 l_expected_value_table(1):=p_value_1;
204 l_counter:= l_counter+1;
205 if p_pk_2 is not null and p_value_2 is not null then
206 l_primaryKey_table(2):=p_pk_2;
207 l_expected_value_table(2):=p_value_2;
208 l_counter:= l_counter+1;
209 if p_pk_3 is not null and p_value_3 is not null then
210 l_primaryKey_table(3):=p_pk_3;
211 l_expected_value_table(3):=p_value_3;
212 l_counter:= l_counter+1;
213 if p_pk_4 is not null and p_value_4 is not null then
214 l_primaryKey_table(4):=p_pk_4;
215 l_expected_value_table(4):=p_value_4;
216 l_counter:= l_counter+1;
217 if p_pk_5 is not null and p_value_5 is not null then
218 l_primaryKey_table(5):=p_pk_5;
219 l_expected_value_table(5):=p_value_5;
220 l_counter:= l_counter+1;
221 end if; --Pk5
222 end if; --Pk4
223 end if; --Pk3
224 end if;--Pk2
225 end if; --Pk1
226
227 for i in 1..l_counter loop
228 x_is_desiredNode :=test_Primary_Key(p_EO_Row_Node
229 ,l_primaryKey_table(i)
230 ,l_expected_value_table(i) );
231 exit when x_is_desiredNode=false;
232 end loop;
233
234 return x_is_desiredNode;
235 End check_Primary_Keys;
236
237 --
238 -- -------------------------------------------------------------------------
239 -- |----------------------------< get_Node_Value >---------------------------------|
240 -- -------------------------------------------------------------------------
241 --
242 FUNCTION get_Node_Value
243 (p_transaction_id in number
244 ,p_desired_node_value in varchar2
245 ,p_xpath in varchar2
246 ,p_EO_name in varchar default null
247 ,p_pk_1 in varchar default null
248 ,p_value_1 in varchar default null
249 ,p_pk_2 in varchar default null
250 ,p_value_2 in varchar default null
251 ,p_pk_3 in varchar default null
252 ,p_value_3 in varchar default null
253 ,p_pk_4 in varchar default null
254 ,p_value_4 in varchar default null
255 ,p_pk_5 in varchar default null
256 ,p_value_5 in varchar default null
257 )return varchar2 is
258 -- Cursor to fetch the TXN_DOCUMENT
259 cursor csr_trn is
260 select transaction_document
261 from hr_api_transactions
262 where transaction_id = p_transaction_id;
263 --
264 txn_row csr_trn%rowtype;
265 --
266 Begin
267 --
268 open csr_trn;
269 fetch csr_trn into txn_row;
270 close csr_trn;
271
272 if txn_row.transaction_document is not null then
273 return get_Node_Value(txn_row.transaction_document
274 ,p_desired_node_value
275 ,p_xpath
276 ,p_EO_name
277 ,p_pk_1
278 ,p_value_1
279 ,p_pk_2
280 ,p_value_2
281 ,p_pk_3
282 ,p_value_3
283 ,p_pk_4
284 ,p_value_4
285 ,p_pk_5
286 ,p_value_5);
287
288 end if;
289 return null; -- The TXN Document is null so we are returning NULL
290 End get_Node_Value;
291 --
292 -- -------------------------------------------------------------------------
293 -- |----------------------------< get_Node_Value >---------------------------------|
294 -- -------------------------------------------------------------------------
295 --
296 FUNCTION get_Node_Value
297 (p_transaction_document in CLOB
298 ,p_desired_node_value in varchar2
299 ,p_xpath in varchar2
300 ,p_EO_name in varchar default null
301 ,p_pk_1 in varchar default null
302 ,p_value_1 in varchar default null
303 ,p_pk_2 in varchar default null
304 ,p_value_2 in varchar default null
305 ,p_pk_3 in varchar default null
306 ,p_value_3 in varchar default null
307 ,p_pk_4 in varchar default null
308 ,p_value_4 in varchar default null
309 ,p_pk_5 in varchar default null
310 ,p_value_5 in varchar default null
311 )return varchar2 is
312 -- xmlDOM.DOMNodeList
313 l_EO_NodeList xmlDOM.DOMNodeList;
314 l_desired_NodeList xmlDOM.DOMNodeList;
315 -- xmlDOM.DOMNode
316 l_EO_Node xmlDOM.DOMNode;
317 l_EORowNode xmlDOM.DOMNode;
318 l_desired_Node xmlDOM.DOMNode;
319 -- varchar2
320 l_Node_Name varchar2(1024);
321 x_return_value varchar2(1024);
322 -- Boolean
323 l_is_desired_EORow boolean;
324
325 Begin
326 x_return_value :=null;
327 if p_transaction_document is not null and p_desired_node_value is not null then
328 if p_EO_name is not null then
329 -- get the list of all Children that are EOs
330 l_EO_NodeList := get_All_EOs_List(p_transaction_document);
331
332 if (xmldom.getLength(l_EO_NodeList) > 0) then -- Some EOs are retrieved
333 for i in 1..xmldom.getLength(l_EO_NodeList) loop
334 l_EO_Node :=xmldom.item(l_EO_NodeList,i-1);
335 -- Get the Name of the EO
336 l_Node_Name :=xmldom.getAttribute(xmldom.makeElement(l_EO_Node),'Name');
337 -- if it is the desired EO then proceed to filter else process next EONode
338 if l_Node_Name = p_EO_name then
339 -- We get the Row Node with the assumption that there is only 1 EORow for an EO
340 -- Later if we need to fetch Multiple EoRows we need do the following
341 -- 1. Get the EORow identifier
342 -- 2. xmldom.getChildrenByTagName(xmldom.makeElement(l_EO_Node),<mentiion the EORow identifier>);
343 -- 3. Repeat the following steps for every EORow node.
344 l_EORowNode := xmldom.getNextSibling(xmldom.getFirstChild(l_EO_Node));
345 l_is_desired_EORow := check_Primary_Keys(l_EORowNode
346 ,p_pk_1
347 ,p_value_1
348 ,p_pk_2
349 ,p_value_2
350 ,p_pk_3
351 ,p_value_3
352 ,p_pk_4
353 ,p_value_4
354 ,p_pk_5
355 ,p_value_5);
356 if l_is_desired_EORow = true then -- Checks if the EORow passes the primary key filter
357 l_desired_NodeList := xmldom.getChildrenByTagName(xmldom.makeElement(l_EORowNode),p_desired_node_value);
358
359 if (xmldom.getLength(l_desired_NodeList) > 0) then -- Some Desired Nodes are Present
360 l_desired_Node := xmldom.item(l_desired_NodeList,0);
361 l_desired_Node := xmldom.getFirstChild(l_desired_Node);
362 x_return_value := xmldom.getNodeValue(l_desired_Node);
366 end if; -- End of if that Checks if the EORow passes the primary key filter
363 return x_return_value;
364 end if; -- End of if that checks if we have any Desired Nodes inside this EORow
365
367 end if; -- End of if that checks if EO name matches
368 end loop; -- End of for loop
369 end if; -- Some EOs are retrieved
370 else -- EO Name is null
371
372 x_return_value:= value_Of(convert_CLOB_To_XMLDocument(p_transaction_document),(p_xpath ||'/' ||p_desired_node_value));
373 end if; -- If for the EO Name
374 end if; -- Main if
375
376 return x_return_value; -- This place will be encountered only when no matches are there
377
378 EXCEPTION
379 when others then
380 return null;
381 End get_Node_Value;
382
383 --
384 -- -------------------------------------------------------------------------
385 -- |----------------------------< get_Transaction_Id >---------------------------------|
386 -- -------------------------------------------------------------------------
387 --
388 FUNCTION get_Transaction_Id
389 (p_item_type in varchar2
390 ,p_item_key in varchar2
391 )return number is
392 -- Cursor to select the Txn id
393 -- From the table : HR_API_TRANSACTIONS
394 cursor csr_hat is
395 select transaction_id
396 from hr_api_transactions
397 where ITEM_KEY = p_item_key
398 and ITEM_TYPE = p_item_type;
399 -- RowType
400 hat_row csr_hat%rowType;
401 Begin
402 open csr_hat;
403 fetch csr_hat into hat_row;
404 close csr_hat;
405
406 return hat_row.transaction_id;
407 End get_Transaction_Id;
408
409 --
410 -- -------------------------------------------------------------------------
411 -- |----------------------------< get_Primary_Keys >---------------------------------|
412 -- -------------------------------------------------------------------------
413 --
414
415 FUNCTION get_Primary_Keys
416 (p_transaction_id in number
417 ,p_object_type in varchar2
418 ,p_object_name in varchar2
419 ,p_row_Count out nocopy number
420 )return Primary_Key_Rec is
421 -- Cursor to fetch the PKs
422 cursor csr_hats is
423 select pk1,pk2,pk3,pk4,pk5
424 from hr_api_transaction_steps
425 where transaction_id=p_transaction_id
426 and OBJECT_TYPE = p_object_type
427 and OBJECT_NAME = p_object_name;
428 -- Number
429 l_row_count number(10);
430 l_csr_hat csr_hats%rowtype;
431 Begin
432 p_row_Count :=null;
433 l_row_count :=0;
434 for l_csr_hat in csr_hats loop
435 pk_rec.primary_Key1(l_row_count+1) := l_csr_hat.pk1;
436 pk_rec.primary_Key2(l_row_count+1) := l_csr_hat.pk2;
437 pk_rec.primary_Key3(l_row_count+1) := l_csr_hat.pk3;
438 pk_rec.primary_Key4(l_row_count+1) := l_csr_hat.pk4;
439 pk_rec.primary_Key5(l_row_count+1) := l_csr_hat.pk5;
440 l_row_count :=l_row_count+1;
441 end loop;
442 -- Set the number of rows fetched in the out parameter
443 p_row_Count := l_row_count;
444 -- Return the Record of Primary Keys
445 return pk_rec;
446 End get_Primary_Keys;
447
448 END hr_xml_util;
[Home] [Help]